Mercurial > hg > LightPIT / changeset

--- a/setup/postgres/psql_create_tables.sql	Sun Aug 01 18:56:28 2021 +0200
+++ b/setup/postgres/psql_create_tables.sql	Mon Aug 02 15:13:04 2021 +0200
@@ -1,22 +1,24 @@
 -- This script creates the module management tables
 --
 
-create table lpit_user (
-    userid          serial          primary key,
-    username        varchar(50)     not null unique,
-    mail            varchar(50),
-    lastname        varchar(50),
-    givenname       varchar(50)
+create table lpit_user
+(
+    userid    serial primary key,
+    username  text not null unique,
+    mail      text,
+    lastname  text,
+    givenname text
 );
 
-create table lpit_project (
-    projectid       serial          primary key,
-    name            varchar(50)     not null unique,
-    node            varchar(20)     not null unique,
-    ordinal         integer         not null default 0,
-    description     varchar(200),
-    repoUrl         varchar(50),
-    owner           integer         references lpit_user(userid)
+create table lpit_project
+(
+    projectid   serial primary key,
+    name        text    not null unique,
+    node        text    not null unique,
+    ordinal     integer not null default 0,
+    description text,
+    repoUrl     text,
+    owner       integer references lpit_user (userid)
 );
 
 create type version_status as enum (
@@ -25,31 +27,33 @@
     'Released',
     'LTS',
     'Deprecated'
-);
+    );
 
-create table lpit_version (
-    versionid       serial          primary key,
-    project         integer         not null references lpit_project(projectid),
-    name            varchar(20)     not null,
-    node            varchar(20)     not null,
-    ordinal         integer         not null default 0,
-    status          version_status  not null default 'Future'
+create table lpit_version
+(
+    versionid serial primary key,
+    project   integer        not null references lpit_project (projectid),
+    name      text           not null,
+    node      text           not null,
+    ordinal   integer        not null default 0,
+    status    version_status not null default 'Future'
 );
 
-create unique index lpit_version_node_unique on lpit_version(project, node);
+create unique index lpit_version_node_unique on lpit_version (project, node);
 
-create table lpit_component (
-    id              serial          primary key,
-    project         integer         not null references lpit_project(projectid),
-    name            varchar(20)     not null,
-    node            varchar(20)     not null,
-    color           char(6)         not null default '000000',
-    ordinal         integer         not null default 0,
-    description     text,
-    lead            integer         references lpit_user(userid)
+create table lpit_component
+(
+    id          serial primary key,
+    project     integer not null references lpit_project (projectid),
+    name        text    not null,
+    node        text    not null,
+    color       char(6) not null default '000000',
+    ordinal     integer not null default 0,
+    description text,
+    lead        integer references lpit_user (userid)
 );
 
-create unique index lpit_component_node_unique on lpit_component(project, node);
+create unique index lpit_component_node_unique on lpit_component (project, node);
 
 create type issue_status as enum (
     'InSpecification',
@@ -61,7 +65,7 @@
     'Rejected',
     'Withdrawn',
     'Duplicate'
-);
+    );
 
 create type issue_category as enum (
     'Feature',
@@ -69,45 +73,50 @@
     'Bug',
     'Task',
     'Test'
-);
+    );
 
-create table lpit_issue_phases (
-    status          issue_status    primary key,
-    phase           integer         not null
+create table lpit_issue_phases
+(
+    status issue_status primary key,
+    phase  integer not null
 );
 
-create table lpit_issue (
-    issueid         serial          primary key,
-    project         integer         not null references lpit_project(projectid),
-    component       integer         references lpit_component(id),
-    status          issue_status    not null default 'InSpecification',
-    category        issue_category  not null default 'Feature',
-    subject         varchar(200)    not null,
-    description     text,
-    assignee        integer         references lpit_user(userid),
-    created         timestamp       with time zone not null default now(),
-    updated         timestamp       with time zone not null default now(),
-    eta             date
+create table lpit_issue
+(
+    issueid     serial primary key,
+    project     integer                  not null references lpit_project (projectid),
+    component   integer references lpit_component (id),
+    status      issue_status             not null default 'InSpecification',
+    category    issue_category           not null default 'Feature',
+    subject     text                     not null,
+    description text,
+    assignee    integer references lpit_user (userid),
+    created     timestamp with time zone not null default now(),
+    updated     timestamp with time zone not null default now(),
+    eta         date
 );
 
-create table lpit_issue_affected_version (
-    issueid         integer         references lpit_issue(issueid),
-    versionid       integer         references lpit_version(versionid),
+create table lpit_issue_affected_version
+(
+    issueid   integer references lpit_issue (issueid),
+    versionid integer references lpit_version (versionid),
     primary key (issueid, versionid)
 );
 
-create table lpit_issue_resolved_version (
-    issueid         integer         references lpit_issue(issueid),
-    versionid       integer         references lpit_version(versionid),
+create table lpit_issue_resolved_version
+(
+    issueid   integer references lpit_issue (issueid),
+    versionid integer references lpit_version (versionid),
     primary key (issueid, versionid)
 );
 
-create table lpit_issue_comment (
-    commentid       serial          primary key,
-    issueid         integer         not null references lpit_issue(issueid),
-    userid          integer         references lpit_user(userid),
-    created         timestamp       with time zone not null default now(),
-    updated         timestamp       with time zone not null default now(),
-    updatecount     integer         not null default 0,
-    comment         text            not null
+create table lpit_issue_comment
+(
+    commentid   serial primary key,
+    issueid     integer                  not null references lpit_issue (issueid),
+    userid      integer references lpit_user (userid),
+    created     timestamp with time zone not null default now(),
+    updated     timestamp with time zone not null default now(),
+    updatecount integer                  not null default 0,
+    comment     text                     not null
 );