setup/postgres/psql_create_tables.sql@f0ede8046b59
setup/postgres/psql_create_tables.sql
Wed, 18 Aug 2021 15:30:49 +0200
- author
- Mike Becker <universe@uap-core.de>
- date
- Wed, 18 Aug 2021 15:30:49 +0200
- changeset 227
- f0ede8046b59
- parent 225
- 87328572e36f
- child 231
- dcb1d5a7ea3a
- permissions
- -rw-r--r--
#162 adds active flag to component
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 );
102 create table lpit_issue_affected_version
103 (
104 issueid integer references lpit_issue (issueid),
105 versionid integer references lpit_version (versionid),
106 primary key (issueid, versionid)
107 );
109 create table lpit_issue_resolved_version
110 (
111 issueid integer references lpit_issue (issueid),
112 versionid integer references lpit_version (versionid),
113 primary key (issueid, versionid)
114 );
116 create table lpit_issue_comment
117 (
118 commentid serial primary key,
119 issueid integer not null references lpit_issue (issueid),
120 userid integer references lpit_user (userid),
121 created timestamp with time zone not null default now(),
122 updated timestamp with time zone not null default now(),
123 updatecount integer not null default 0,
124 comment text not null
125 );
