setup/postgres/psql_create_tables.sql@dcb1d5a7ea3a
setup/postgres/psql_create_tables.sql
Thu, 19 Aug 2021 14:51:04 +0200
- author
- Mike Becker <universe@uap-core.de>
- date
- Thu, 19 Aug 2021 14:51:04 +0200
- changeset 231
- dcb1d5a7ea3a
- parent 227
- f0ede8046b59
- child 232
- 296e12ff8d1c
- permissions
- -rw-r--r--
#163 removes multi selection for versions
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 table lpit_issue_comment
105 (
106 commentid serial primary key,
107 issueid integer not null references lpit_issue (issueid),
108 userid integer references lpit_user (userid),
109 created timestamp with time zone not null default now(),
110 updated timestamp with time zone not null default now(),
111 updatecount integer not null default 0,
112 comment text not null
113 );
