setup/postgres/psql_create_tables.sql@ed2e7aef2a3e
setup/postgres/psql_create_tables.sql
Fri, 09 Oct 2020 19:07:05 +0200
- author
- Mike Becker <universe@uap-core.de>
- date
- Fri, 09 Oct 2020 19:07:05 +0200
- changeset 124
- ed2e7aef2a3e
- parent 88
- 1438e5a22c55
- child 127
- 6105ee2cceaf
- permissions
- -rw-r--r--
adds issue comments
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 );
36 create type issue_status as enum (
37 'InSpecification',
38 'ToDo',
39 'Scheduled',
40 'InProgress',
41 'InReview',
42 'Done',
43 'Rejected',
44 'Withdrawn',
45 'Duplicate'
46 );
48 create type issue_category as enum (
49 'Feature',
50 'Improvement',
51 'Bug',
52 'Task',
53 'Test'
54 );
56 create table lpit_issue_phases (
57 status issue_status primary key,
58 phase integer not null
59 );
61 create table lpit_issue (
62 issueid serial primary key,
63 project integer not null references lpit_project(projectid),
64 status issue_status not null default 'InSpecification',
65 category issue_category not null default 'Feature',
66 subject varchar(200) not null,
67 description text,
68 assignee integer references lpit_user(userid),
69 created timestamp with time zone not null default now(),
70 updated timestamp with time zone not null default now(),
71 eta date
72 );
74 create table lpit_issue_affected_version (
75 issueid integer references lpit_issue(issueid),
76 versionid integer references lpit_version(versionid),
77 primary key (issueid, versionid)
78 );
80 create table lpit_issue_resolved_version (
81 issueid integer references lpit_issue(issueid),
82 versionid integer references lpit_version(versionid),
83 primary key (issueid, versionid)
84 );
86 create table lpit_issue_comment (
87 commentid serial primary key,
88 issueid integer not null references lpit_issue(issueid),
89 userid integer references lpit_user(userid),
90 created timestamp with time zone not null default now(),
91 updated timestamp with time zone not null default now(),
92 updatecount integer not null default 0,
93 comment text not null
94 );
