Sat, 23 May 2020 13:52:04 +0200
bloat removal 2/3 - moduleInfo
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', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
44 | 'Withdrawn' |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
45 | ); |
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 | create type issue_category as enum ( |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
48 | 'Feature', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
49 | 'Improvement', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
50 | 'Bug', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
51 | 'Task', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
52 | 'Test' |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
53 | ); |
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 | create table lpit_issue ( |
75
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
56 | issueid serial primary key, |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
57 | 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
|
58 | status issue_status not null default 'InSpecification', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
59 | category issue_category not null default 'Feature', |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
60 | subject varchar(20) not null, |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
61 | description text, |
75
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
62 | assignee integer references lpit_user(userid), |
62
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
63 | 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
|
64 | 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
|
65 | eta date |
833e0385572a
adds data model for issues
Mike Becker <universe@uap-core.de>
parents:
37
diff
changeset
|
66 | ); |
75
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
67 | |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
68 | 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
|
69 | 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
|
70 | 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
|
71 | primary key (issueid, versionid) |
33b6843fdf8a
adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents:
62
diff
changeset
|
72 | ); |
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_scheduled_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_resolved_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 |