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