setup/postgres/psql_create_tables.sql@c7ee4cbfb86d
setup/postgres/psql_create_tables.sql
Sat, 06 Jan 2024 20:32:56 +0100
- author
- Mike Becker <universe@uap-core.de>
- date
- Sat, 06 Jan 2024 20:32:56 +0100
- changeset 300
- c7ee4cbfb86d
- parent 284
- 671c1c8fbf1c
- permissions
- -rw-r--r--
Added tag v1.2.2 for changeset 238de141d189
1 create table lpit_user
2 (
3 userid serial primary key,
4 username text not null unique,
5 mail text,
6 lastname text,
7 givenname text
8 );
10 create type vcstype as enum ('None', 'Mercurial', 'Git');
12 create table lpit_project
13 (
14 projectid serial primary key,
15 name text not null unique,
16 node text not null unique,
17 ordinal integer not null default 0,
18 description text,
19 repoUrl text,
20 vcs vcstype not null default 'None'::vcstype,
21 owner integer references lpit_user (userid)
22 );
24 create type version_status as enum (
25 'Future',
26 'Unreleased',
27 'Released',
28 'LTS',
29 'Deprecated'
30 );
32 create table lpit_version
33 (
34 versionid serial primary key,
35 project integer not null references lpit_project (projectid),
36 name text not null,
37 node text not null,
38 ordinal integer not null default 0,
39 status version_status not null default 'Future',
40 release date,
41 eol date
42 );
44 create unique index lpit_version_node_unique on lpit_version (project, node);
46 create table lpit_component
47 (
48 id serial primary key,
49 project integer not null references lpit_project (projectid),
50 name text not null,
51 node text not null,
52 color char(6) not null default '000000',
53 ordinal integer not null default 0,
54 description text,
55 lead integer references lpit_user (userid),
56 active boolean not null default true
57 );
59 create unique index lpit_component_node_unique on lpit_component (project, node);
61 create type issue_status as enum (
62 'InSpecification',
63 'ToDo',
64 'Scheduled',
65 'InProgress',
66 'InReview',
67 'Ready',
68 'Done',
69 'Rejected',
70 'Withdrawn',
71 'Duplicate'
72 );
74 create type issue_category as enum (
75 'Feature',
76 'Improvement',
77 'Bug',
78 'Task',
79 'Test'
80 );
82 create table lpit_issue_phases
83 (
84 status issue_status primary key,
85 phase integer not null
86 );
88 create table lpit_issue
89 (
90 issueid serial primary key,
91 project integer not null references lpit_project (projectid),
92 component integer references lpit_component (id),
93 status issue_status not null default 'InSpecification',
94 category issue_category not null default 'Feature',
95 subject text not null,
96 description text,
97 assignee integer references lpit_user (userid),
98 created timestamp with time zone not null default now(),
99 updated timestamp with time zone not null default now(),
100 eta date,
101 affected integer references lpit_version (versionid),
102 resolved integer references lpit_version (versionid)
103 );
105 create type issue_history_event as enum (
106 'New',
107 'Update',
108 'NewComment',
109 'UpdateComment'
110 );
112 create table lpit_issue_history_event
113 (
114 eventid serial primary key,
115 issueid integer not null references lpit_issue (issueid) on delete cascade,
116 subject text not null,
117 time timestamp with time zone not null default now(),
118 type issue_history_event not null
119 );
121 create table lpit_issue_history_data
122 (
123 eventid integer not null references lpit_issue_history_event (eventid) on delete cascade,
124 component text,
125 status issue_status not null,
126 category issue_category not null,
127 description text,
128 assignee text,
129 eta date,
130 affected text,
131 resolved text
132 );
134 create table lpit_issue_comment
135 (
136 commentid serial primary key,
137 issueid integer not null references lpit_issue (issueid),
138 userid integer references lpit_user (userid),
139 created timestamp with time zone not null default now(),
140 updated timestamp with time zone not null default now(),
141 updatecount integer not null default 0,
142 comment text not null
143 );
145 create table lpit_issue_comment_history
146 (
147 commentid integer not null references lpit_issue_comment (commentid) on delete cascade,
148 eventid integer not null references lpit_issue_history_event (eventid) on delete cascade,
149 comment text not null
150 );
152 create type relation_type as enum (
153 'RelatesTo',
154 'TogetherWith',
155 'Before',
156 'SubtaskOf',
157 'DefectOf',
158 'Blocks',
159 'Tests',
160 'Duplicates'
161 );
163 create table lpit_issue_relation
164 (
165 from_issue integer not null references lpit_issue (issueid) on delete cascade,
166 to_issue integer not null references lpit_issue (issueid) on delete cascade,
167 type relation_type not null
168 );
170 create unique index lpit_issue_relation_unique on lpit_issue_relation (from_issue, to_issue, type);
172 create table lpit_commit_ref
173 (
174 issueid integer not null references lpit_issue (issueid) on delete cascade,
175 commit_hash text not null,
176 commit_brief text not null
177 );
179 create unique index lpit_commit_ref_unique on lpit_commit_ref (issueid, commit_hash);
