setup/postgres/psql_create_tables.sql@947d0f6a6a83
setup/postgres/psql_create_tables.sql
Thu, 15 Oct 2020 13:31:52 +0200
- author
- Mike Becker <universe@uap-core.de>
- date
- Thu, 15 Oct 2020 13:31:52 +0200
- changeset 128
- 947d0f6a6a83
- parent 127
- 6105ee2cceaf
- child 138
- e2aa673dd473
- permissions
- -rw-r--r--
changes the way how to deal with child entities + adds component lead
1 -- This script creates the module management tables
2 --
4 create table lpit_user (
5 userid serial primary key,
6 username varchar(50) not null unique,
7 mail varchar(50),
8 lastname varchar(50),
9 givenname varchar(50)
10 );
12 create table lpit_project (
13 projectid serial primary key,
14 name varchar(20) not null unique,
15 description varchar(200),
16 repoUrl varchar(50),
17 owner integer references lpit_user(userid)
18 );
20 create type version_status as enum (
21 'Future',
22 'Unreleased',
23 'Released',
24 'LTS',
25 'Deprecated'
26 );
28 create table lpit_version (
29 versionid serial primary key,
30 project integer not null references lpit_project(projectid),
31 name varchar(20) not null,
32 ordinal integer not null default 0,
33 status version_status not null default 'Future'
34 );
37 create table lpit_component (
38 id serial primary key,
39 project integer not null references lpit_project(projectid),
40 name varchar(20) not null,
41 color char(6) not null default '000000',
42 ordinal integer not null default 0,
43 description text,
44 lead integer references lpit_user(userid)
45 );
47 create type issue_status as enum (
48 'InSpecification',
49 'ToDo',
50 'Scheduled',
51 'InProgress',
52 'InReview',
53 'Done',
54 'Rejected',
55 'Withdrawn',
56 'Duplicate'
57 );
59 create type issue_category as enum (
60 'Feature',
61 'Improvement',
62 'Bug',
63 'Task',
64 'Test'
65 );
67 create table lpit_issue_phases (
68 status issue_status primary key,
69 phase integer not null
70 );
72 create table lpit_issue (
73 issueid serial primary key,
74 project integer not null references lpit_project(projectid),
75 component integer references lpit_component(id),
76 status issue_status not null default 'InSpecification',
77 category issue_category not null default 'Feature',
78 subject varchar(200) not null,
79 description text,
80 assignee integer references lpit_user(userid),
81 created timestamp with time zone not null default now(),
82 updated timestamp with time zone not null default now(),
83 eta date
84 );
86 create table lpit_issue_affected_version (
87 issueid integer references lpit_issue(issueid),
88 versionid integer references lpit_version(versionid),
89 primary key (issueid, versionid)
90 );
92 create table lpit_issue_resolved_version (
93 issueid integer references lpit_issue(issueid),
94 versionid integer references lpit_version(versionid),
95 primary key (issueid, versionid)
96 );
98 create table lpit_issue_comment (
99 commentid serial primary key,
100 issueid integer not null references lpit_issue(issueid),
101 userid integer references lpit_user(userid),
102 created timestamp with time zone not null default now(),
103 updated timestamp with time zone not null default now(),
104 updatecount integer not null default 0,
105 comment text not null
106 );
