Fri, 23 Oct 2020 11:14:37 +0200
renames sort criteria PHASE to DONE, because phase 0 and 1 are combined
20
bd1a76c91d5b
module synchronization with database
Mike Becker <universe@uap-core.de>
parents:
16
diff
changeset
|
1 | -- This script creates the module management tables |
bd1a76c91d5b
module synchronization with database
Mike Becker <universe@uap-core.de>
parents:
16
diff
changeset
|
2 | -- |
2
fcb452578142
adds create database setup script
Mike Becker <universe@uap-core.de>
parents:
diff
changeset
|
3 | |
37
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
4 | create table lpit_user ( |
20
bd1a76c91d5b
module synchronization with database
Mike Becker <universe@uap-core.de>
parents:
16
diff
changeset
|
5 | userid serial primary key, |
bd1a76c91d5b
module synchronization with database
Mike Becker <universe@uap-core.de>
parents:
16
diff
changeset
|
6 | username varchar(50) not null unique, |
37
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
7 | mail varchar(50), |
20
bd1a76c91d5b
module synchronization with database
Mike Becker <universe@uap-core.de>
parents:
16
diff
changeset
|
8 | lastname varchar(50), |
bd1a76c91d5b
module synchronization with database
Mike Becker <universe@uap-core.de>
parents:
16
diff
changeset
|
9 | givenname varchar(50) |
bd1a76c91d5b
module synchronization with database
Mike Becker <universe@uap-core.de>
parents:
16
diff
changeset
|
10 | ); |
37
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
11 | |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
12 | create table lpit_project ( |
75
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
13 | projectid serial primary key, |
37
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
14 | name varchar(20) not null unique, |
138
e2aa673dd473
adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents:
128
diff
changeset
|
15 | node varchar(20) not null unique, |
37
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
16 | description varchar(200), |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
17 | repoUrl varchar(50), |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
18 | owner integer references lpit_user(userid) |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
19 | ); |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
20 | |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
21 | create type version_status as enum ( |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
22 | 'Future', |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
23 | 'Unreleased', |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
24 | 'Released', |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
25 | 'LTS', |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
26 | 'Deprecated' |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
27 | ); |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
28 | |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
29 | create table lpit_version ( |
75
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
30 | versionid serial primary key, |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
31 | project integer not null references lpit_project(projectid), |
37
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
32 | name varchar(20) not null, |
138
e2aa673dd473
adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents:
128
diff
changeset
|
33 | node varchar(20) not null, |
37
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
34 | ordinal integer not null default 0, |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
35 | status version_status not null default 'Future' |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
36 | ); |
62
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
37 | |
138
e2aa673dd473
adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents:
128
diff
changeset
|
38 | create unique index lpit_version_node_unique on lpit_version(project, node); |
127 | 39 | |
40 | create table lpit_component ( | |
41 | id serial primary key, | |
42 | project integer not null references lpit_project(projectid), | |
43 | name varchar(20) not null, | |
138
e2aa673dd473
adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents:
128
diff
changeset
|
44 | node varchar(20) not null, |
127 | 45 | color char(6) not null default '000000', |
46 | ordinal integer not null default 0, | |
128
947d0f6a6a83
changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents:
127
diff
changeset
|
47 | description text, |
947d0f6a6a83
changes the way how to deal with child entities + adds component lead
Mike Becker <universe@uap-core.de>
parents:
127
diff
changeset
|
48 | lead integer references lpit_user(userid) |
127 | 49 | ); |
50 | ||
138
e2aa673dd473
adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents:
128
diff
changeset
|
51 | create unique index lpit_component_node_unique on lpit_component(project, node); |
e2aa673dd473
adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents:
128
diff
changeset
|
52 | |
62
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
53 | create type issue_status as enum ( |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
54 | 'InSpecification', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
55 | 'ToDo', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
56 | 'Scheduled', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
57 | 'InProgress', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
58 | 'InReview', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
59 | 'Done', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
60 | 'Rejected', |
81 | 61 | 'Withdrawn', |
62 | 'Duplicate' | |
62
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
63 | ); |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
64 | |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
65 | create type issue_category as enum ( |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
66 | 'Feature', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
67 | 'Improvement', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
68 | 'Bug', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
69 | 'Task', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
70 | 'Test' |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
71 | ); |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
72 | |
81 | 73 | create table lpit_issue_phases ( |
74 | status issue_status primary key, | |
75 | phase integer not null | |
76 | ); | |
77 | ||
62
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
78 | create table lpit_issue ( |
75
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
79 | issueid serial primary key, |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
80 | project integer not null references lpit_project(projectid), |
127 | 81 | component integer references lpit_component(id), |
62
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
82 | status issue_status not null default 'InSpecification', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
83 | category issue_category not null default 'Feature', |
85
3d16ad54b3dc
significantly increases length of subject field
Mike Becker <universe@uap-core.de>
parents:
81
diff
changeset
|
84 | subject varchar(200) not null, |
62
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
85 | description text, |
75
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
86 | assignee integer references lpit_user(userid), |
62
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
87 | created timestamp with time zone not null default now(), |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
88 | updated timestamp with time zone not null default now(), |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
89 | eta date |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
90 | ); |
75
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
91 | |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
92 | create table lpit_issue_affected_version ( |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
93 | issueid integer references lpit_issue(issueid), |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
94 | versionid integer references lpit_version(versionid), |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
95 | primary key (issueid, versionid) |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
96 | ); |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
97 | |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
98 | create table lpit_issue_resolved_version ( |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
99 | issueid integer references lpit_issue(issueid), |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
100 | versionid integer references lpit_version(versionid), |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
101 | primary key (issueid, versionid) |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
102 | ); |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
103 | |
124 | 104 | create table lpit_issue_comment ( |
105 | commentid serial primary key, | |
106 | issueid integer not null references lpit_issue(issueid), | |
107 | userid integer references lpit_user(userid), | |
108 | created timestamp with time zone not null default now(), | |
109 | updated timestamp with time zone not null default now(), | |
110 | updatecount integer not null default 0, | |
111 | comment text not null | |
112 | ); |