setup/postgres/psql_create_tables.sql@1438e5a22c55
setup/postgres/psql_create_tables.sql
Sun, 21 Jun 2020 11:38:16 +0200
- author
- Mike Becker <universe@uap-core.de>
- date
- Sun, 21 Jun 2020 11:38:16 +0200
- changeset 88
- 1438e5a22c55
- parent 85
- 3d16ad54b3dc
- child 124
- ed2e7aef2a3e
- permissions
- -rw-r--r--
simplifies version overviews by removing "scheduled issues"
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 );
