setup/postgres/psql_create_tables.sql@3ace475224f7 (annotated)
setup/postgres/psql_create_tables.sql
Sat, 31 Oct 2020 09:56:00 +0100
- author
- Mike Becker <universe@uap-core.de>
- date
- Sat, 31 Oct 2020 09:56:00 +0100
- changeset 155
- 3ace475224f7
- parent 138
- e2aa673dd473
- child 175
- 1e6f2aace666
- permissions
- -rw-r--r--
fixes issue sorter not recognizing issues as "equal" if their ETAs are both null
| universe@20 | 1 | -- This script creates the module management tables |
| universe@20 | 2 | -- |
| universe@2 | 3 | |
| universe@37 | 4 | create table lpit_user ( |
| universe@20 | 5 | userid serial primary key, |
| universe@20 | 6 | username varchar(50) not null unique, |
| universe@37 | 7 | mail varchar(50), |
| universe@20 | 8 | lastname varchar(50), |
| universe@20 | 9 | givenname varchar(50) |
| universe@20 | 10 | ); |
| universe@37 | 11 | |
| universe@37 | 12 | create table lpit_project ( |
| universe@75 | 13 | projectid serial primary key, |
| universe@37 | 14 | name varchar(20) not null unique, |
| universe@138 | 15 | node varchar(20) not null unique, |
| universe@37 | 16 | description varchar(200), |
| universe@37 | 17 | repoUrl varchar(50), |
| universe@37 | 18 | owner integer references lpit_user(userid) |
| universe@37 | 19 | ); |
| universe@37 | 20 | |
| universe@37 | 21 | create type version_status as enum ( |
| universe@37 | 22 | 'Future', |
| universe@37 | 23 | 'Unreleased', |
| universe@37 | 24 | 'Released', |
| universe@37 | 25 | 'LTS', |
| universe@37 | 26 | 'Deprecated' |
| universe@37 | 27 | ); |
| universe@37 | 28 | |
| universe@37 | 29 | create table lpit_version ( |
| universe@75 | 30 | versionid serial primary key, |
| universe@75 | 31 | project integer not null references lpit_project(projectid), |
| universe@37 | 32 | name varchar(20) not null, |
| universe@138 | 33 | node varchar(20) not null, |
| universe@37 | 34 | ordinal integer not null default 0, |
| universe@37 | 35 | status version_status not null default 'Future' |
| universe@37 | 36 | ); |
| universe@62 | 37 | |
| universe@138 | 38 | create unique index lpit_version_node_unique on lpit_version(project, node); |
| universe@127 | 39 | |
| universe@127 | 40 | create table lpit_component ( |
| universe@127 | 41 | id serial primary key, |
| universe@127 | 42 | project integer not null references lpit_project(projectid), |
| universe@127 | 43 | name varchar(20) not null, |
| universe@138 | 44 | node varchar(20) not null, |
| universe@127 | 45 | color char(6) not null default '000000', |
| universe@127 | 46 | ordinal integer not null default 0, |
| universe@128 | 47 | description text, |
| universe@128 | 48 | lead integer references lpit_user(userid) |
| universe@127 | 49 | ); |
| universe@127 | 50 | |
| universe@138 | 51 | create unique index lpit_component_node_unique on lpit_component(project, node); |
| universe@138 | 52 | |
| universe@62 | 53 | create type issue_status as enum ( |
| universe@62 | 54 | 'InSpecification', |
| universe@62 | 55 | 'ToDo', |
| universe@62 | 56 | 'Scheduled', |
| universe@62 | 57 | 'InProgress', |
| universe@62 | 58 | 'InReview', |
| universe@62 | 59 | 'Done', |
| universe@62 | 60 | 'Rejected', |
| universe@81 | 61 | 'Withdrawn', |
| universe@81 | 62 | 'Duplicate' |
| universe@62 | 63 | ); |
| universe@62 | 64 | |
| universe@62 | 65 | create type issue_category as enum ( |
| universe@62 | 66 | 'Feature', |
| universe@62 | 67 | 'Improvement', |
| universe@62 | 68 | 'Bug', |
| universe@62 | 69 | 'Task', |
| universe@62 | 70 | 'Test' |
| universe@62 | 71 | ); |
| universe@62 | 72 | |
| universe@81 | 73 | create table lpit_issue_phases ( |
| universe@81 | 74 | status issue_status primary key, |
| universe@81 | 75 | phase integer not null |
| universe@81 | 76 | ); |
| universe@81 | 77 | |
| universe@62 | 78 | create table lpit_issue ( |
| universe@75 | 79 | issueid serial primary key, |
| universe@75 | 80 | project integer not null references lpit_project(projectid), |
| universe@127 | 81 | component integer references lpit_component(id), |
| universe@62 | 82 | status issue_status not null default 'InSpecification', |
| universe@62 | 83 | category issue_category not null default 'Feature', |
| universe@85 | 84 | subject varchar(200) not null, |
| universe@62 | 85 | description text, |
| universe@75 | 86 | assignee integer references lpit_user(userid), |
| universe@62 | 87 | created timestamp with time zone not null default now(), |
| universe@62 | 88 | updated timestamp with time zone not null default now(), |
| universe@62 | 89 | eta date |
| universe@62 | 90 | ); |
| universe@75 | 91 | |
| universe@75 | 92 | create table lpit_issue_affected_version ( |
| universe@75 | 93 | issueid integer references lpit_issue(issueid), |
| universe@75 | 94 | versionid integer references lpit_version(versionid), |
| universe@75 | 95 | primary key (issueid, versionid) |
| universe@75 | 96 | ); |
| universe@75 | 97 | |
| universe@75 | 98 | create table lpit_issue_resolved_version ( |
| universe@75 | 99 | issueid integer references lpit_issue(issueid), |
| universe@75 | 100 | versionid integer references lpit_version(versionid), |
| universe@75 | 101 | primary key (issueid, versionid) |
| universe@75 | 102 | ); |
| universe@75 | 103 | |
| universe@124 | 104 | create table lpit_issue_comment ( |
| universe@124 | 105 | commentid serial primary key, |
| universe@124 | 106 | issueid integer not null references lpit_issue(issueid), |
| universe@124 | 107 | userid integer references lpit_user(userid), |
| universe@124 | 108 | created timestamp with time zone not null default now(), |
| universe@124 | 109 | updated timestamp with time zone not null default now(), |
| universe@124 | 110 | updatecount integer not null default 0, |
| universe@124 | 111 | comment text not null |
| universe@124 | 112 | ); |
