setup/postgres/psql_create_tables.sql@33b6843fdf8a
setup/postgres/psql_create_tables.sql
Fri, 22 May 2020 21:23:57 +0200
- author
- Mike Becker <universe@uap-core.de>
- date
- Fri, 22 May 2020 21:23:57 +0200
- changeset 75
- 33b6843fdf8a
- parent 62
- 833e0385572a
- child 81
- 1a2e7b5d48f7
- permissions
- -rw-r--r--
adds the ability to create and edit 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 );
47 create type issue_category as enum (
48 'Feature',
49 'Improvement',
50 'Bug',
51 'Task',
52 'Test'
53 );
55 create table lpit_issue (
56 issueid serial primary key,
57 project integer not null references lpit_project(projectid),
58 status issue_status not null default 'InSpecification',
59 category issue_category not null default 'Feature',
60 subject varchar(20) not null,
61 description text,
62 assignee integer references lpit_user(userid),
63 created timestamp with time zone not null default now(),
64 updated timestamp with time zone not null default now(),
65 eta date
66 );
68 create table lpit_issue_affected_version (
69 issueid integer references lpit_issue(issueid),
70 versionid integer references lpit_version(versionid),
71 primary key (issueid, versionid)
72 );
74 create table lpit_issue_scheduled_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 );
