setup/postgres/psql_create_tables.sql@c8e1b5282f69
setup/postgres/psql_create_tables.sql
Wed, 18 Aug 2021 15:04:59 +0200
- author
- Mike Becker <universe@uap-core.de>
- date
- Wed, 18 Aug 2021 15:04:59 +0200
- changeset 226
- c8e1b5282f69
- parent 225
- 87328572e36f
- child 227
- f0ede8046b59
- permissions
- -rw-r--r--
adds mailto link to issue-view.jsp
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 );
58 create unique index lpit_component_node_unique on lpit_component (project, node);
60 create type issue_status as enum (
61 'InSpecification',
62 'ToDo',
63 'Scheduled',
64 'InProgress',
65 'InReview',
66 'Done',
67 'Rejected',
68 'Withdrawn',
69 'Duplicate'
70 );
72 create type issue_category as enum (
73 'Feature',
74 'Improvement',
75 'Bug',
76 'Task',
77 'Test'
78 );
80 create table lpit_issue_phases
81 (
82 status issue_status primary key,
83 phase integer not null
84 );
86 create table lpit_issue
87 (
88 issueid serial primary key,
89 project integer not null references lpit_project (projectid),
90 component integer references lpit_component (id),
91 status issue_status not null default 'InSpecification',
92 category issue_category not null default 'Feature',
93 subject text not null,
94 description text,
95 assignee integer references lpit_user (userid),
96 created timestamp with time zone not null default now(),
97 updated timestamp with time zone not null default now(),
98 eta date
99 );
101 create table lpit_issue_affected_version
102 (
103 issueid integer references lpit_issue (issueid),
104 versionid integer references lpit_version (versionid),
105 primary key (issueid, versionid)
106 );
108 create table lpit_issue_resolved_version
109 (
110 issueid integer references lpit_issue (issueid),
111 versionid integer references lpit_version (versionid),
112 primary key (issueid, versionid)
113 );
115 create table lpit_issue_comment
116 (
117 commentid serial primary key,
118 issueid integer not null references lpit_issue (issueid),
119 userid integer references lpit_user (userid),
120 created timestamp with time zone not null default now(),
121 updated timestamp with time zone not null default now(),
122 updatecount integer not null default 0,
123 comment text not null
124 );
