setup/postgres/psql_create_tables.sql@0a2ad22ac656
setup/postgres/psql_create_tables.sql
Thu, 13 May 2021 18:01:56 +0200
- author
- Mike Becker <universe@uap-core.de>
- date
- Thu, 13 May 2021 18:01:56 +0200
- changeset 197
- 0a2ad22ac656
- parent 187
- 0bf35031a918
- child 206
- fe4de34822a5
- permissions
- -rw-r--r--
removes useless guard
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(50) not null unique,
15 node varchar(20) not null unique,
16 ordinal integer not null default 0,
17 description varchar(200),
18 repoUrl varchar(50),
19 owner integer references lpit_user(userid)
20 );
22 create type version_status as enum (
23 'Future',
24 'Unreleased',
25 'Released',
26 'LTS',
27 'Deprecated'
28 );
30 create table lpit_version (
31 versionid serial primary key,
32 project integer not null references lpit_project(projectid),
33 name varchar(20) not null,
34 node varchar(20) not null,
35 ordinal integer not null default 0,
36 status version_status not null default 'Future'
37 );
39 create unique index lpit_version_node_unique on lpit_version(project, node);
41 create table lpit_component (
42 id serial primary key,
43 project integer not null references lpit_project(projectid),
44 name varchar(20) not null,
45 node varchar(20) not null,
46 color char(6) not null default '000000',
47 ordinal integer not null default 0,
48 description text,
49 lead integer references lpit_user(userid)
50 );
52 create unique index lpit_component_node_unique on lpit_component(project, node);
54 create type issue_status as enum (
55 'InSpecification',
56 'ToDo',
57 'Scheduled',
58 'InProgress',
59 'InReview',
60 'Done',
61 'Rejected',
62 'Withdrawn',
63 'Duplicate'
64 );
66 create type issue_category as enum (
67 'Feature',
68 'Improvement',
69 'Bug',
70 'Task',
71 'Test'
72 );
74 create table lpit_issue_phases (
75 status issue_status primary key,
76 phase integer not null
77 );
79 create table lpit_issue (
80 issueid serial primary key,
81 project integer not null references lpit_project(projectid),
82 component integer references lpit_component(id),
83 status issue_status not null default 'InSpecification',
84 category issue_category not null default 'Feature',
85 subject varchar(200) not null,
86 description text,
87 assignee integer references lpit_user(userid),
88 created timestamp with time zone not null default now(),
89 updated timestamp with time zone not null default now(),
90 eta date
91 );
93 create table lpit_issue_affected_version (
94 issueid integer references lpit_issue(issueid),
95 versionid integer references lpit_version(versionid),
96 primary key (issueid, versionid)
97 );
99 create table lpit_issue_resolved_version (
100 issueid integer references lpit_issue(issueid),
101 versionid integer references lpit_version(versionid),
102 primary key (issueid, versionid)
103 );
105 create table lpit_issue_comment (
106 commentid serial primary key,
107 issueid integer not null references lpit_issue(issueid),
108 userid integer references lpit_user(userid),
109 created timestamp with time zone not null default now(),
110 updated timestamp with time zone not null default now(),
111 updatecount integer not null default 0,
112 comment text not null
113 );
