setup/postgres/psql_create_tables.sql

Thu, 05 Nov 2020 13:37:48 +0100

author
Mike Becker <universe@uap-core.de>
date
Thu, 05 Nov 2020 13:37:48 +0100
changeset 157
1e6f16fad3a5
parent 138
e2aa673dd473
child 175
1e6f2aace666
permissions
-rw-r--r--

removes ResponseType enum

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
6105ee2cceaf adds component entity
Mike Becker <universe@uap-core.de>
parents: 124
diff changeset
39
6105ee2cceaf adds component entity
Mike Becker <universe@uap-core.de>
parents: 124
diff changeset
40 create table lpit_component (
6105ee2cceaf adds component entity
Mike Becker <universe@uap-core.de>
parents: 124
diff changeset
41 id serial primary key,
6105ee2cceaf adds component entity
Mike Becker <universe@uap-core.de>
parents: 124
diff changeset
42 project integer not null references lpit_project(projectid),
6105ee2cceaf adds component entity
Mike Becker <universe@uap-core.de>
parents: 124
diff changeset
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
6105ee2cceaf adds component entity
Mike Becker <universe@uap-core.de>
parents: 124
diff changeset
45 color char(6) not null default '000000',
6105ee2cceaf adds component entity
Mike Becker <universe@uap-core.de>
parents: 124
diff changeset
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
6105ee2cceaf adds component entity
Mike Becker <universe@uap-core.de>
parents: 124
diff changeset
49 );
6105ee2cceaf adds component entity
Mike Becker <universe@uap-core.de>
parents: 124
diff changeset
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
1a2e7b5d48f7 adds issue summaries
Mike Becker <universe@uap-core.de>
parents: 75
diff changeset
61 'Withdrawn',
1a2e7b5d48f7 adds issue summaries
Mike Becker <universe@uap-core.de>
parents: 75
diff changeset
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
1a2e7b5d48f7 adds issue summaries
Mike Becker <universe@uap-core.de>
parents: 75
diff changeset
73 create table lpit_issue_phases (
1a2e7b5d48f7 adds issue summaries
Mike Becker <universe@uap-core.de>
parents: 75
diff changeset
74 status issue_status primary key,
1a2e7b5d48f7 adds issue summaries
Mike Becker <universe@uap-core.de>
parents: 75
diff changeset
75 phase integer not null
1a2e7b5d48f7 adds issue summaries
Mike Becker <universe@uap-core.de>
parents: 75
diff changeset
76 );
1a2e7b5d48f7 adds issue summaries
Mike Becker <universe@uap-core.de>
parents: 75
diff changeset
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
6105ee2cceaf adds component entity
Mike Becker <universe@uap-core.de>
parents: 124
diff changeset
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
ed2e7aef2a3e adds issue comments
Mike Becker <universe@uap-core.de>
parents: 88
diff changeset
104 create table lpit_issue_comment (
ed2e7aef2a3e adds issue comments
Mike Becker <universe@uap-core.de>
parents: 88
diff changeset
105 commentid serial primary key,
ed2e7aef2a3e adds issue comments
Mike Becker <universe@uap-core.de>
parents: 88
diff changeset
106 issueid integer not null references lpit_issue(issueid),
ed2e7aef2a3e adds issue comments
Mike Becker <universe@uap-core.de>
parents: 88
diff changeset
107 userid integer references lpit_user(userid),
ed2e7aef2a3e adds issue comments
Mike Becker <universe@uap-core.de>
parents: 88
diff changeset
108 created timestamp with time zone not null default now(),
ed2e7aef2a3e adds issue comments
Mike Becker <universe@uap-core.de>
parents: 88
diff changeset
109 updated timestamp with time zone not null default now(),
ed2e7aef2a3e adds issue comments
Mike Becker <universe@uap-core.de>
parents: 88
diff changeset
110 updatecount integer not null default 0,
ed2e7aef2a3e adds issue comments
Mike Becker <universe@uap-core.de>
parents: 88
diff changeset
111 comment text not null
ed2e7aef2a3e adds issue comments
Mike Becker <universe@uap-core.de>
parents: 88
diff changeset
112 );

mercurial