setup/postgres/psql_create_tables.sql@8066895cc57e
setup/postgres/psql_create_tables.sql
Tue, 03 Aug 2021 14:46:04 +0200
- author
- Mike Becker <universe@uap-core.de>
- date
- Tue, 03 Aug 2021 14:46:04 +0200
- changeset 211
- 8066895cc57e
- parent 206
- fe4de34822a5
- child 225
- 87328572e36f
- permissions
- -rw-r--r--
adds pretty font
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 );
42 create unique index lpit_version_node_unique on lpit_version (project, node);
44 create table lpit_component
45 (
46 id serial primary key,
47 project integer not null references lpit_project (projectid),
48 name text not null,
49 node text not null,
50 color char(6) not null default '000000',
51 ordinal integer not null default 0,
52 description text,
53 lead integer references lpit_user (userid)
54 );
56 create unique index lpit_component_node_unique on lpit_component (project, node);
58 create type issue_status as enum (
59 'InSpecification',
60 'ToDo',
61 'Scheduled',
62 'InProgress',
63 'InReview',
64 'Done',
65 'Rejected',
66 'Withdrawn',
67 'Duplicate'
68 );
70 create type issue_category as enum (
71 'Feature',
72 'Improvement',
73 'Bug',
74 'Task',
75 'Test'
76 );
78 create table lpit_issue_phases
79 (
80 status issue_status primary key,
81 phase integer not null
82 );
84 create table lpit_issue
85 (
86 issueid serial primary key,
87 project integer not null references lpit_project (projectid),
88 component integer references lpit_component (id),
89 status issue_status not null default 'InSpecification',
90 category issue_category not null default 'Feature',
91 subject text not null,
92 description text,
93 assignee integer references lpit_user (userid),
94 created timestamp with time zone not null default now(),
95 updated timestamp with time zone not null default now(),
96 eta date
97 );
99 create table lpit_issue_affected_version
100 (
101 issueid integer references lpit_issue (issueid),
102 versionid integer references lpit_version (versionid),
103 primary key (issueid, versionid)
104 );
106 create table lpit_issue_resolved_version
107 (
108 issueid integer references lpit_issue (issueid),
109 versionid integer references lpit_version (versionid),
110 primary key (issueid, versionid)
111 );
113 create table lpit_issue_comment
114 (
115 commentid serial primary key,
116 issueid integer not null references lpit_issue (issueid),
117 userid integer references lpit_user (userid),
118 created timestamp with time zone not null default now(),
119 updated timestamp with time zone not null default now(),
120 updatecount integer not null default 0,
121 comment text not null
122 );
