Mon, 04 Jan 2021 17:30:10 +0100
automatically select version/component when creating new issues under active filters
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, |
175
1e6f2aace666
adds project ordering - fixes #34
Mike Becker <universe@uap-core.de>
parents:
138
diff
changeset
|
16 | ordinal integer not null default 0, |
37
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
17 | description varchar(200), |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
18 | repoUrl varchar(50), |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
19 | owner integer references lpit_user(userid) |
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 | |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
22 | create type version_status as enum ( |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
23 | 'Future', |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
24 | 'Unreleased', |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
25 | 'Released', |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
26 | 'LTS', |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
27 | 'Deprecated' |
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 | |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
30 | create table lpit_version ( |
75
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
31 | versionid serial primary key, |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
32 | 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
|
33 | name varchar(20) not null, |
138
e2aa673dd473
adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents:
128
diff
changeset
|
34 | node varchar(20) not null, |
37
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
35 | ordinal integer not null default 0, |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
36 | 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
|
37 | ); |
62
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
38 | |
138
e2aa673dd473
adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents:
128
diff
changeset
|
39 | create unique index lpit_version_node_unique on lpit_version(project, node); |
127 | 40 | |
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, | |
138
e2aa673dd473
adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents:
128
diff
changeset
|
45 | node varchar(20) not null, |
127 | 46 | color char(6) not null default '000000', |
47 | 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
|
48 | 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
|
49 | lead integer references lpit_user(userid) |
127 | 50 | ); |
51 | ||
138
e2aa673dd473
adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents:
128
diff
changeset
|
52 | 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
|
53 | |
62
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
54 | create type issue_status as enum ( |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
55 | 'InSpecification', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
56 | 'ToDo', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
57 | 'Scheduled', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
58 | 'InProgress', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
59 | 'InReview', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
60 | 'Done', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
61 | 'Rejected', |
81 | 62 | 'Withdrawn', |
63 | 'Duplicate' | |
62
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 | |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
66 | create type issue_category as enum ( |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
67 | 'Feature', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
68 | 'Improvement', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
69 | 'Bug', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
70 | 'Task', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
71 | 'Test' |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
72 | ); |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
73 | |
81 | 74 | create table lpit_issue_phases ( |
75 | status issue_status primary key, | |
76 | phase integer not null | |
77 | ); | |
78 | ||
62
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
79 | create table lpit_issue ( |
75
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
80 | issueid serial primary key, |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
81 | project integer not null references lpit_project(projectid), |
127 | 82 | component integer references lpit_component(id), |
62
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
83 | status issue_status not null default 'InSpecification', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
84 | 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
|
85 | subject varchar(200) not null, |
62
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
86 | description text, |
75
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
87 | assignee integer references lpit_user(userid), |
62
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
88 | 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
|
89 | 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
|
90 | eta date |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
91 | ); |
75
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
92 | |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
93 | 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
|
94 | 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
|
95 | 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
|
96 | primary key (issueid, versionid) |
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 | |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
99 | 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
|
100 | 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
|
101 | 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
|
102 | primary key (issueid, versionid) |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
103 | ); |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
104 | |
124 | 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 | ); |