setup/postgres/psql_create_tables.sql@b7f3e972b13c
setup/postgres/psql_create_tables.sql
Sat, 27 Nov 2021 13:03:57 +0100
- author
- Mike Becker <universe@uap-core.de>
- date
- Sat, 27 Nov 2021 13:03:57 +0100
- changeset 242
- b7f3e972b13c
- parent 241
- 1ca4f27cefe8
- child 252
- 90f5e12c7623
- permissions
- -rw-r--r--
#109 add comment history
1 -- This script creates the module management tables
2 --
4 create table lpit_user
5 (
6 userid serial primary key,
7 username text not null unique,
8 mail text,
9 lastname text,
10 givenname text
11 );
13 create table lpit_project
14 (
15 projectid serial primary key,
16 name text not null unique,
17 node text not null unique,
18 ordinal integer not null default 0,
19 description text,
20 repoUrl text,
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 'Done',
68 'Rejected',
69 'Withdrawn',
70 'Duplicate'
71 );
73 create type issue_category as enum (
74 'Feature',
75 'Improvement',
76 'Bug',
77 'Task',
78 'Test'
79 );
81 create table lpit_issue_phases
82 (
83 status issue_status primary key,
84 phase integer not null
85 );
87 create table lpit_issue
88 (
89 issueid serial primary key,
90 project integer not null references lpit_project (projectid),
91 component integer references lpit_component (id),
92 status issue_status not null default 'InSpecification',
93 category issue_category not null default 'Feature',
94 subject text not null,
95 description text,
96 assignee integer references lpit_user (userid),
97 created timestamp with time zone not null default now(),
98 updated timestamp with time zone not null default now(),
99 eta date,
100 affected integer references lpit_version (versionid),
101 resolved integer references lpit_version (versionid)
102 );
104 create type issue_history_event as enum (
105 'New',
106 'Update',
107 'NewComment',
108 'UpdateComment'
109 );
111 create table lpit_issue_history_event
112 (
113 eventid serial primary key,
114 issueid integer not null references lpit_issue (issueid) on delete cascade,
115 subject text not null,
116 time timestamp with time zone not null default now(),
117 type issue_history_event not null
118 );
120 create table lpit_issue_history_data
121 (
122 eventid integer not null references lpit_issue_history_event (eventid) on delete cascade,
123 component text,
124 status issue_status not null,
125 category issue_category not null,
126 description text,
127 assignee text,
128 eta date,
129 affected text,
130 resolved text
131 );
133 create table lpit_issue_comment
134 (
135 commentid serial primary key,
136 issueid integer not null references lpit_issue (issueid),
137 userid integer references lpit_user (userid),
138 created timestamp with time zone not null default now(),
139 updated timestamp with time zone not null default now(),
140 updatecount integer not null default 0,
141 comment text not null
142 );
144 create table lpit_issue_comment_history
145 (
146 commentid integer not null references lpit_issue_comment (commentid) on delete cascade,
147 eventid integer not null references lpit_issue_history_event (eventid) on delete cascade,
148 comment text not null
149 );
