Thu, 13 May 2021 18:01:56 +0200
removes useless guard
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@187 | 14 | name varchar(50) not null unique, |
universe@138 | 15 | node varchar(20) not null unique, |
universe@175 | 16 | ordinal integer not null default 0, |
universe@37 | 17 | description varchar(200), |
universe@37 | 18 | repoUrl varchar(50), |
universe@37 | 19 | owner integer references lpit_user(userid) |
universe@37 | 20 | ); |
universe@37 | 21 | |
universe@37 | 22 | create type version_status as enum ( |
universe@37 | 23 | 'Future', |
universe@37 | 24 | 'Unreleased', |
universe@37 | 25 | 'Released', |
universe@37 | 26 | 'LTS', |
universe@37 | 27 | 'Deprecated' |
universe@37 | 28 | ); |
universe@37 | 29 | |
universe@37 | 30 | create table lpit_version ( |
universe@75 | 31 | versionid serial primary key, |
universe@75 | 32 | project integer not null references lpit_project(projectid), |
universe@37 | 33 | name varchar(20) not null, |
universe@138 | 34 | node varchar(20) not null, |
universe@37 | 35 | ordinal integer not null default 0, |
universe@37 | 36 | status version_status not null default 'Future' |
universe@37 | 37 | ); |
universe@62 | 38 | |
universe@138 | 39 | create unique index lpit_version_node_unique on lpit_version(project, node); |
universe@127 | 40 | |
universe@127 | 41 | create table lpit_component ( |
universe@127 | 42 | id serial primary key, |
universe@127 | 43 | project integer not null references lpit_project(projectid), |
universe@127 | 44 | name varchar(20) not null, |
universe@138 | 45 | node varchar(20) not null, |
universe@127 | 46 | color char(6) not null default '000000', |
universe@127 | 47 | ordinal integer not null default 0, |
universe@128 | 48 | description text, |
universe@128 | 49 | lead integer references lpit_user(userid) |
universe@127 | 50 | ); |
universe@127 | 51 | |
universe@138 | 52 | create unique index lpit_component_node_unique on lpit_component(project, node); |
universe@138 | 53 | |
universe@62 | 54 | create type issue_status as enum ( |
universe@62 | 55 | 'InSpecification', |
universe@62 | 56 | 'ToDo', |
universe@62 | 57 | 'Scheduled', |
universe@62 | 58 | 'InProgress', |
universe@62 | 59 | 'InReview', |
universe@62 | 60 | 'Done', |
universe@62 | 61 | 'Rejected', |
universe@81 | 62 | 'Withdrawn', |
universe@81 | 63 | 'Duplicate' |
universe@62 | 64 | ); |
universe@62 | 65 | |
universe@62 | 66 | create type issue_category as enum ( |
universe@62 | 67 | 'Feature', |
universe@62 | 68 | 'Improvement', |
universe@62 | 69 | 'Bug', |
universe@62 | 70 | 'Task', |
universe@62 | 71 | 'Test' |
universe@62 | 72 | ); |
universe@62 | 73 | |
universe@81 | 74 | create table lpit_issue_phases ( |
universe@81 | 75 | status issue_status primary key, |
universe@81 | 76 | phase integer not null |
universe@81 | 77 | ); |
universe@81 | 78 | |
universe@62 | 79 | create table lpit_issue ( |
universe@75 | 80 | issueid serial primary key, |
universe@75 | 81 | project integer not null references lpit_project(projectid), |
universe@127 | 82 | component integer references lpit_component(id), |
universe@62 | 83 | status issue_status not null default 'InSpecification', |
universe@62 | 84 | category issue_category not null default 'Feature', |
universe@85 | 85 | subject varchar(200) not null, |
universe@62 | 86 | description text, |
universe@75 | 87 | assignee integer references lpit_user(userid), |
universe@62 | 88 | created timestamp with time zone not null default now(), |
universe@62 | 89 | updated timestamp with time zone not null default now(), |
universe@62 | 90 | eta date |
universe@62 | 91 | ); |
universe@75 | 92 | |
universe@75 | 93 | create table lpit_issue_affected_version ( |
universe@75 | 94 | issueid integer references lpit_issue(issueid), |
universe@75 | 95 | versionid integer references lpit_version(versionid), |
universe@75 | 96 | primary key (issueid, versionid) |
universe@75 | 97 | ); |
universe@75 | 98 | |
universe@75 | 99 | create table lpit_issue_resolved_version ( |
universe@75 | 100 | issueid integer references lpit_issue(issueid), |
universe@75 | 101 | versionid integer references lpit_version(versionid), |
universe@75 | 102 | primary key (issueid, versionid) |
universe@75 | 103 | ); |
universe@75 | 104 | |
universe@124 | 105 | create table lpit_issue_comment ( |
universe@124 | 106 | commentid serial primary key, |
universe@124 | 107 | issueid integer not null references lpit_issue(issueid), |
universe@124 | 108 | userid integer references lpit_user(userid), |
universe@124 | 109 | created timestamp with time zone not null default now(), |
universe@124 | 110 | updated timestamp with time zone not null default now(), |
universe@124 | 111 | updatecount integer not null default 0, |
universe@124 | 112 | comment text not null |
universe@124 | 113 | ); |