comparison: setup/postgres/psql_create_tables.sql
setup/postgres/psql_create_tables.sql
- changeset 206
- fe4de34822a5
- parent 187
- 0bf35031a918
- child 225
- 87328572e36f
equal
deleted
inserted
replaced
| 205:7725a79416f3 | 206:fe4de34822a5 |
|---|---|
| 1 -- This script creates the module management tables | 1 -- This script creates the module management tables |
| 2 -- | 2 -- |
| 3 | 3 |
| 4 create table lpit_user ( | 4 create table lpit_user |
| 5 userid serial primary key, | 5 ( |
| 6 username varchar(50) not null unique, | 6 userid serial primary key, |
| 7 mail varchar(50), | 7 username text not null unique, |
| 8 lastname varchar(50), | 8 mail text, |
| 9 givenname varchar(50) | 9 lastname text, |
| 10 givenname text | |
| 10 ); | 11 ); |
| 11 | 12 |
| 12 create table lpit_project ( | 13 create table lpit_project |
| 13 projectid serial primary key, | 14 ( |
| 14 name varchar(50) not null unique, | 15 projectid serial primary key, |
| 15 node varchar(20) not null unique, | 16 name text not null unique, |
| 16 ordinal integer not null default 0, | 17 node text not null unique, |
| 17 description varchar(200), | 18 ordinal integer not null default 0, |
| 18 repoUrl varchar(50), | 19 description text, |
| 19 owner integer references lpit_user(userid) | 20 repoUrl text, |
| 21 owner integer references lpit_user (userid) | |
| 20 ); | 22 ); |
| 21 | 23 |
| 22 create type version_status as enum ( | 24 create type version_status as enum ( |
| 23 'Future', | 25 'Future', |
| 24 'Unreleased', | 26 'Unreleased', |
| 25 'Released', | 27 'Released', |
| 26 'LTS', | 28 'LTS', |
| 27 'Deprecated' | 29 'Deprecated' |
| 30 ); | |
| 31 | |
| 32 create table lpit_version | |
| 33 ( | |
| 34 versionid serial primary key, | |
| 35 project integer not null references lpit_project (projectid), | |
| 36 name text not null, | |
| 37 node text not null, | |
| 38 ordinal integer not null default 0, | |
| 39 status version_status not null default 'Future' | |
| 28 ); | 40 ); |
| 29 | 41 |
| 30 create table lpit_version ( | 42 create unique index lpit_version_node_unique on lpit_version (project, node); |
| 31 versionid serial primary key, | 43 |
| 32 project integer not null references lpit_project(projectid), | 44 create table lpit_component |
| 33 name varchar(20) not null, | 45 ( |
| 34 node varchar(20) not null, | 46 id serial primary key, |
| 35 ordinal integer not null default 0, | 47 project integer not null references lpit_project (projectid), |
| 36 status version_status not null default 'Future' | 48 name text not null, |
| 49 node text not null, | |
| 50 color char(6) not null default '000000', | |
| 51 ordinal integer not null default 0, | |
| 52 description text, | |
| 53 lead integer references lpit_user (userid) | |
| 37 ); | 54 ); |
| 38 | 55 |
| 39 create unique index lpit_version_node_unique on lpit_version(project, node); | 56 create unique index lpit_component_node_unique on lpit_component (project, node); |
| 40 | |
| 41 create table lpit_component ( | |
| 42 id serial primary key, | |
| 43 project integer not null references lpit_project(projectid), | |
| 44 name varchar(20) not null, | |
| 45 node varchar(20) not null, | |
| 46 color char(6) not null default '000000', | |
| 47 ordinal integer not null default 0, | |
| 48 description text, | |
| 49 lead integer references lpit_user(userid) | |
| 50 ); | |
| 51 | |
| 52 create unique index lpit_component_node_unique on lpit_component(project, node); | |
| 53 | 57 |
| 54 create type issue_status as enum ( | 58 create type issue_status as enum ( |
| 55 'InSpecification', | 59 'InSpecification', |
| 56 'ToDo', | 60 'ToDo', |
| 57 'Scheduled', | 61 'Scheduled', |
| 59 'InReview', | 63 'InReview', |
| 60 'Done', | 64 'Done', |
| 61 'Rejected', | 65 'Rejected', |
| 62 'Withdrawn', | 66 'Withdrawn', |
| 63 'Duplicate' | 67 'Duplicate' |
| 64 ); | 68 ); |
| 65 | 69 |
| 66 create type issue_category as enum ( | 70 create type issue_category as enum ( |
| 67 'Feature', | 71 'Feature', |
| 68 'Improvement', | 72 'Improvement', |
| 69 'Bug', | 73 'Bug', |
| 70 'Task', | 74 'Task', |
| 71 'Test' | 75 'Test' |
| 76 ); | |
| 77 | |
| 78 create table lpit_issue_phases | |
| 79 ( | |
| 80 status issue_status primary key, | |
| 81 phase integer not null | |
| 72 ); | 82 ); |
| 73 | 83 |
| 74 create table lpit_issue_phases ( | 84 create table lpit_issue |
| 75 status issue_status primary key, | 85 ( |
| 76 phase integer not null | 86 issueid serial primary key, |
| 87 project integer not null references lpit_project (projectid), | |
| 88 component integer references lpit_component (id), | |
| 89 status issue_status not null default 'InSpecification', | |
| 90 category issue_category not null default 'Feature', | |
| 91 subject text not null, | |
| 92 description text, | |
| 93 assignee integer references lpit_user (userid), | |
| 94 created timestamp with time zone not null default now(), | |
| 95 updated timestamp with time zone not null default now(), | |
| 96 eta date | |
| 77 ); | 97 ); |
| 78 | 98 |
| 79 create table lpit_issue ( | 99 create table lpit_issue_affected_version |
| 80 issueid serial primary key, | 100 ( |
| 81 project integer not null references lpit_project(projectid), | 101 issueid integer references lpit_issue (issueid), |
| 82 component integer references lpit_component(id), | 102 versionid integer references lpit_version (versionid), |
| 83 status issue_status not null default 'InSpecification', | |
| 84 category issue_category not null default 'Feature', | |
| 85 subject varchar(200) not null, | |
| 86 description text, | |
| 87 assignee integer references lpit_user(userid), | |
| 88 created timestamp with time zone not null default now(), | |
| 89 updated timestamp with time zone not null default now(), | |
| 90 eta date | |
| 91 ); | |
| 92 | |
| 93 create table lpit_issue_affected_version ( | |
| 94 issueid integer references lpit_issue(issueid), | |
| 95 versionid integer references lpit_version(versionid), | |
| 96 primary key (issueid, versionid) | 103 primary key (issueid, versionid) |
| 97 ); | 104 ); |
| 98 | 105 |
| 99 create table lpit_issue_resolved_version ( | 106 create table lpit_issue_resolved_version |
| 100 issueid integer references lpit_issue(issueid), | 107 ( |
| 101 versionid integer references lpit_version(versionid), | 108 issueid integer references lpit_issue (issueid), |
| 109 versionid integer references lpit_version (versionid), | |
| 102 primary key (issueid, versionid) | 110 primary key (issueid, versionid) |
| 103 ); | 111 ); |
| 104 | 112 |
| 105 create table lpit_issue_comment ( | 113 create table lpit_issue_comment |
| 106 commentid serial primary key, | 114 ( |
| 107 issueid integer not null references lpit_issue(issueid), | 115 commentid serial primary key, |
| 108 userid integer references lpit_user(userid), | 116 issueid integer not null references lpit_issue (issueid), |
| 109 created timestamp with time zone not null default now(), | 117 userid integer references lpit_user (userid), |
| 110 updated timestamp with time zone not null default now(), | 118 created timestamp with time zone not null default now(), |
| 111 updatecount integer not null default 0, | 119 updated timestamp with time zone not null default now(), |
| 112 comment text not null | 120 updatecount integer not null default 0, |
| 121 comment text not null | |
| 113 ); | 122 ); |
