Sun, 21 Jun 2020 11:10:30 +0200
smaller issue summary progress bars
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, |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
15 | description varchar(200), |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
16 | repoUrl varchar(50), |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
17 | owner integer references lpit_user(userid) |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
18 | ); |
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 | create type version_status as enum ( |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
21 | 'Future', |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
22 | 'Unreleased', |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
23 | 'Released', |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
24 | 'LTS', |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
25 | 'Deprecated' |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
26 | ); |
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 | create table lpit_version ( |
75
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
29 | versionid serial primary key, |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
30 | 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
|
31 | name varchar(20) not null, |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
32 | ordinal integer not null default 0, |
fecda0f466e6
adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents:
36
diff
changeset
|
33 | 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
|
34 | ); |
62
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
35 | |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
36 | create type issue_status as enum ( |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
37 | 'InSpecification', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
38 | 'ToDo', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
39 | 'Scheduled', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
40 | 'InProgress', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
41 | 'InReview', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
42 | 'Done', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
43 | 'Rejected', |
81 | 44 | 'Withdrawn', |
45 | 'Duplicate' | |
62
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
46 | ); |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
47 | |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
48 | create type issue_category as enum ( |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
49 | 'Feature', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
50 | 'Improvement', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
51 | 'Bug', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
52 | 'Task', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
53 | 'Test' |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
54 | ); |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
55 | |
81 | 56 | create table lpit_issue_phases ( |
57 | status issue_status primary key, | |
58 | phase integer not null | |
59 | ); | |
60 | ||
62
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
61 | create table lpit_issue ( |
75
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
62 | issueid serial primary key, |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
63 | project integer not null references lpit_project(projectid), |
62
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
64 | status issue_status not null default 'InSpecification', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
65 | 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
|
66 | subject varchar(200) not null, |
62
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
67 | description text, |
75
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
68 | assignee integer references lpit_user(userid), |
62
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
69 | 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
|
70 | 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
|
71 | eta date |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
72 | ); |
75
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
73 | |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
74 | 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
|
75 | 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
|
76 | 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
|
77 | primary key (issueid, versionid) |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
78 | ); |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
79 | |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
80 | create table lpit_issue_scheduled_version ( |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
81 | 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
|
82 | 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
|
83 | primary key (issueid, versionid) |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
84 | ); |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
85 | |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
86 | 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
|
87 | 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
|
88 | 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
|
89 | primary key (issueid, versionid) |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
90 | ); |
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 |