Sun, 26 Sep 2021 14:59:36 +0200
fix assignee not respected in hasChanged()
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@62 | 67 | 'Done', |
universe@62 | 68 | 'Rejected', |
universe@81 | 69 | 'Withdrawn', |
universe@81 | 70 | 'Duplicate' |
universe@206 | 71 | ); |
universe@62 | 72 | |
universe@62 | 73 | create type issue_category as enum ( |
universe@62 | 74 | 'Feature', |
universe@62 | 75 | 'Improvement', |
universe@62 | 76 | 'Bug', |
universe@62 | 77 | 'Task', |
universe@62 | 78 | 'Test' |
universe@206 | 79 | ); |
universe@206 | 80 | |
universe@206 | 81 | create table lpit_issue_phases |
universe@206 | 82 | ( |
universe@206 | 83 | status issue_status primary key, |
universe@206 | 84 | phase integer not null |
universe@62 | 85 | ); |
universe@62 | 86 | |
universe@206 | 87 | create table lpit_issue |
universe@206 | 88 | ( |
universe@206 | 89 | issueid serial primary key, |
universe@206 | 90 | project integer not null references lpit_project (projectid), |
universe@206 | 91 | component integer references lpit_component (id), |
universe@206 | 92 | status issue_status not null default 'InSpecification', |
universe@206 | 93 | category issue_category not null default 'Feature', |
universe@206 | 94 | subject text not null, |
universe@206 | 95 | description text, |
universe@206 | 96 | assignee integer references lpit_user (userid), |
universe@206 | 97 | created timestamp with time zone not null default now(), |
universe@206 | 98 | updated timestamp with time zone not null default now(), |
universe@231 | 99 | eta date, |
universe@231 | 100 | affected integer references lpit_version (versionid), |
universe@231 | 101 | resolved integer references lpit_version (versionid) |
universe@75 | 102 | ); |
universe@75 | 103 | |
universe@232 | 104 | create type issue_history_event as enum ( |
universe@232 | 105 | 'New', |
universe@232 | 106 | 'Update', |
universe@232 | 107 | 'NewComment', |
universe@232 | 108 | 'UpdateComment' |
universe@232 | 109 | ); |
universe@232 | 110 | |
universe@232 | 111 | create table lpit_issue_history_event |
universe@232 | 112 | ( |
universe@232 | 113 | eventid serial primary key, |
universe@232 | 114 | issueid integer not null references lpit_issue (issueid) on delete cascade, |
universe@232 | 115 | time timestamp with time zone not null default now(), |
universe@232 | 116 | type issue_history_event not null |
universe@232 | 117 | ); |
universe@232 | 118 | |
universe@232 | 119 | create table lpit_issue_history_data |
universe@232 | 120 | ( |
universe@232 | 121 | eventid integer not null references lpit_issue_history_event (eventid) on delete cascade, |
universe@232 | 122 | component text, |
universe@232 | 123 | status issue_status not null, |
universe@232 | 124 | category issue_category not null, |
universe@232 | 125 | subject text not null, |
universe@232 | 126 | description text, |
universe@232 | 127 | assignee text, |
universe@232 | 128 | eta date, |
universe@232 | 129 | affected text, |
universe@232 | 130 | resolved text |
universe@232 | 131 | ); |
universe@232 | 132 | |
universe@206 | 133 | create table lpit_issue_comment |
universe@206 | 134 | ( |
universe@206 | 135 | commentid serial primary key, |
universe@206 | 136 | issueid integer not null references lpit_issue (issueid), |
universe@206 | 137 | userid integer references lpit_user (userid), |
universe@206 | 138 | created timestamp with time zone not null default now(), |
universe@206 | 139 | updated timestamp with time zone not null default now(), |
universe@206 | 140 | updatecount integer not null default 0, |
universe@206 | 141 | comment text not null |
universe@124 | 142 | ); |
universe@232 | 143 | |
universe@232 | 144 | create table lpit_issue_comment_history |
universe@232 | 145 | ( |
universe@232 | 146 | commentid integer not null references lpit_issue_comment (commentid) on delete cascade, |
universe@232 | 147 | eventid integer not null references lpit_issue_history_event (eventid) on delete cascade, |
universe@232 | 148 | comment text not null |
universe@232 | 149 | ); |
universe@232 | 150 |