Sat, 15 Jul 2023 13:49:36 +0200
Added tag v1.0.1 for changeset 6971db7b18ab
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@225 | 39 | status version_status not null default 'Future', |
universe@225 | 40 | release date, |
universe@225 | 41 | eol date |
universe@37 | 42 | ); |
universe@37 | 43 | |
universe@206 | 44 | create unique index lpit_version_node_unique on lpit_version (project, node); |
universe@206 | 45 | |
universe@206 | 46 | create table lpit_component |
universe@206 | 47 | ( |
universe@206 | 48 | id serial primary key, |
universe@206 | 49 | project integer not null references lpit_project (projectid), |
universe@206 | 50 | name text not null, |
universe@206 | 51 | node text not null, |
universe@206 | 52 | color char(6) not null default '000000', |
universe@206 | 53 | ordinal integer not null default 0, |
universe@206 | 54 | description text, |
universe@227 | 55 | lead integer references lpit_user (userid), |
universe@227 | 56 | active boolean not null default true |
universe@37 | 57 | ); |
universe@62 | 58 | |
universe@206 | 59 | create unique index lpit_component_node_unique on lpit_component (project, node); |
universe@138 | 60 | |
universe@62 | 61 | create type issue_status as enum ( |
universe@62 | 62 | 'InSpecification', |
universe@62 | 63 | 'ToDo', |
universe@62 | 64 | 'Scheduled', |
universe@62 | 65 | 'InProgress', |
universe@62 | 66 | 'InReview', |
universe@252 | 67 | 'Ready', |
universe@62 | 68 | 'Done', |
universe@62 | 69 | 'Rejected', |
universe@81 | 70 | 'Withdrawn', |
universe@81 | 71 | 'Duplicate' |
universe@206 | 72 | ); |
universe@62 | 73 | |
universe@62 | 74 | create type issue_category as enum ( |
universe@62 | 75 | 'Feature', |
universe@62 | 76 | 'Improvement', |
universe@62 | 77 | 'Bug', |
universe@62 | 78 | 'Task', |
universe@62 | 79 | 'Test' |
universe@206 | 80 | ); |
universe@206 | 81 | |
universe@206 | 82 | create table lpit_issue_phases |
universe@206 | 83 | ( |
universe@206 | 84 | status issue_status primary key, |
universe@206 | 85 | phase integer not null |
universe@62 | 86 | ); |
universe@62 | 87 | |
universe@206 | 88 | create table lpit_issue |
universe@206 | 89 | ( |
universe@206 | 90 | issueid serial primary key, |
universe@206 | 91 | project integer not null references lpit_project (projectid), |
universe@206 | 92 | component integer references lpit_component (id), |
universe@206 | 93 | status issue_status not null default 'InSpecification', |
universe@206 | 94 | category issue_category not null default 'Feature', |
universe@206 | 95 | subject text not null, |
universe@206 | 96 | description text, |
universe@206 | 97 | assignee integer references lpit_user (userid), |
universe@206 | 98 | created timestamp with time zone not null default now(), |
universe@206 | 99 | updated timestamp with time zone not null default now(), |
universe@231 | 100 | eta date, |
universe@231 | 101 | affected integer references lpit_version (versionid), |
universe@231 | 102 | resolved integer references lpit_version (versionid) |
universe@75 | 103 | ); |
universe@75 | 104 | |
universe@232 | 105 | create type issue_history_event as enum ( |
universe@232 | 106 | 'New', |
universe@232 | 107 | 'Update', |
universe@232 | 108 | 'NewComment', |
universe@232 | 109 | 'UpdateComment' |
universe@232 | 110 | ); |
universe@232 | 111 | |
universe@232 | 112 | create table lpit_issue_history_event |
universe@232 | 113 | ( |
universe@232 | 114 | eventid serial primary key, |
universe@232 | 115 | issueid integer not null references lpit_issue (issueid) on delete cascade, |
universe@242 | 116 | subject text not null, |
universe@232 | 117 | time timestamp with time zone not null default now(), |
universe@232 | 118 | type issue_history_event not null |
universe@232 | 119 | ); |
universe@232 | 120 | |
universe@232 | 121 | create table lpit_issue_history_data |
universe@232 | 122 | ( |
universe@239 | 123 | eventid integer not null references lpit_issue_history_event (eventid) on delete cascade, |
universe@239 | 124 | component text, |
universe@239 | 125 | status issue_status not null, |
universe@239 | 126 | category issue_category not null, |
universe@239 | 127 | description text, |
universe@239 | 128 | assignee text, |
universe@239 | 129 | eta date, |
universe@239 | 130 | affected text, |
universe@239 | 131 | resolved text |
universe@232 | 132 | ); |
universe@232 | 133 | |
universe@206 | 134 | create table lpit_issue_comment |
universe@206 | 135 | ( |
universe@206 | 136 | commentid serial primary key, |
universe@206 | 137 | issueid integer not null references lpit_issue (issueid), |
universe@206 | 138 | userid integer references lpit_user (userid), |
universe@206 | 139 | created timestamp with time zone not null default now(), |
universe@206 | 140 | updated timestamp with time zone not null default now(), |
universe@206 | 141 | updatecount integer not null default 0, |
universe@206 | 142 | comment text not null |
universe@124 | 143 | ); |
universe@232 | 144 | |
universe@232 | 145 | create table lpit_issue_comment_history |
universe@232 | 146 | ( |
universe@232 | 147 | commentid integer not null references lpit_issue_comment (commentid) on delete cascade, |
universe@232 | 148 | eventid integer not null references lpit_issue_history_event (eventid) on delete cascade, |
universe@232 | 149 | comment text not null |
universe@232 | 150 | ); |
universe@232 | 151 | |
universe@263 | 152 | create type relation_type as enum ( |
universe@263 | 153 | 'RelatesTo', |
universe@263 | 154 | 'TogetherWith', |
universe@263 | 155 | 'Before', |
universe@263 | 156 | 'SubtaskOf', |
universe@268 | 157 | 'DefectOf', |
universe@263 | 158 | 'Blocks', |
universe@263 | 159 | 'Tests', |
universe@263 | 160 | 'Duplicates' |
universe@263 | 161 | ); |
universe@263 | 162 | |
universe@263 | 163 | create table lpit_issue_relation |
universe@263 | 164 | ( |
universe@263 | 165 | from_issue integer not null references lpit_issue (issueid) on delete cascade, |
universe@263 | 166 | to_issue integer not null references lpit_issue (issueid) on delete cascade, |
universe@263 | 167 | type relation_type not null |
universe@263 | 168 | ); |
universe@263 | 169 | |
universe@263 | 170 | create unique index lpit_issue_relation_unique on lpit_issue_relation (from_issue, to_issue, type); |