setup/postgres/psql_create_tables.sql

Tue, 03 Aug 2021 15:10:43 +0200

author
Mike Becker <universe@uap-core.de>
date
Tue, 03 Aug 2021 15:10:43 +0200
changeset 212
c50da26a6d31
parent 206
fe4de34822a5
child 225
87328572e36f
permissions
-rw-r--r--

#154 improves issue view

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
206
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
4 create table lpit_user
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
5 (
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
6 userid serial primary key,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
7 username text not null unique,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
8 mail text,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
9 lastname text,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
10 givenname text
20
bd1a76c91d5b module synchronization with database
Mike Becker <universe@uap-core.de>
parents: 16
diff changeset
11 );
37
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
12
206
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
13 create table lpit_project
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
14 (
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
15 projectid serial primary key,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
16 name text not null unique,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
17 node text not null unique,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
18 ordinal integer not null default 0,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
19 description text,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
20 repoUrl text,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
21 owner integer references lpit_user (userid)
37
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
22 );
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
23
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
24 create type version_status as enum (
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
25 'Future',
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
26 'Unreleased',
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
27 'Released',
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
28 'LTS',
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
29 'Deprecated'
206
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
30 );
37
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
31
206
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
32 create table lpit_version
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
33 (
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
34 versionid serial primary key,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
35 project integer not null references lpit_project (projectid),
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
36 name text not null,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
37 node text not null,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
38 ordinal integer not null default 0,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
39 status version_status not null default 'Future'
37
fecda0f466e6 adds data model for projects and versions
Mike Becker <universe@uap-core.de>
parents: 36
diff changeset
40 );
62
833e0385572a adds data model for issues
Mike Becker <universe@uap-core.de>
parents: 37
diff changeset
41
206
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
42 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
43
206
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
44 create table lpit_component
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
45 (
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
46 id serial primary key,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
47 project integer not null references lpit_project (projectid),
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
48 name text not null,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
49 node text not null,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
50 color char(6) not null default '000000',
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
51 ordinal integer not null default 0,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
52 description text,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
53 lead integer references lpit_user (userid)
127
6105ee2cceaf adds component entity
Mike Becker <universe@uap-core.de>
parents: 124
diff changeset
54 );
6105ee2cceaf adds component entity
Mike Becker <universe@uap-core.de>
parents: 124
diff changeset
55
206
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
56 create unique index lpit_component_node_unique on lpit_component (project, node);
138
e2aa673dd473 adds custom node names - fixes #27
Mike Becker <universe@uap-core.de>
parents: 128
diff changeset
57
62
833e0385572a adds data model for issues
Mike Becker <universe@uap-core.de>
parents: 37
diff changeset
58 create type issue_status as enum (
833e0385572a adds data model for issues
Mike Becker <universe@uap-core.de>
parents: 37
diff changeset
59 'InSpecification',
833e0385572a adds data model for issues
Mike Becker <universe@uap-core.de>
parents: 37
diff changeset
60 'ToDo',
833e0385572a adds data model for issues
Mike Becker <universe@uap-core.de>
parents: 37
diff changeset
61 'Scheduled',
833e0385572a adds data model for issues
Mike Becker <universe@uap-core.de>
parents: 37
diff changeset
62 'InProgress',
833e0385572a adds data model for issues
Mike Becker <universe@uap-core.de>
parents: 37
diff changeset
63 'InReview',
833e0385572a adds data model for issues
Mike Becker <universe@uap-core.de>
parents: 37
diff changeset
64 'Done',
833e0385572a adds data model for issues
Mike Becker <universe@uap-core.de>
parents: 37
diff changeset
65 'Rejected',
81
1a2e7b5d48f7 adds issue summaries
Mike Becker <universe@uap-core.de>
parents: 75
diff changeset
66 'Withdrawn',
1a2e7b5d48f7 adds issue summaries
Mike Becker <universe@uap-core.de>
parents: 75
diff changeset
67 'Duplicate'
206
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
68 );
62
833e0385572a adds data model for issues
Mike Becker <universe@uap-core.de>
parents: 37
diff changeset
69
833e0385572a adds data model for issues
Mike Becker <universe@uap-core.de>
parents: 37
diff changeset
70 create type issue_category as enum (
833e0385572a adds data model for issues
Mike Becker <universe@uap-core.de>
parents: 37
diff changeset
71 'Feature',
833e0385572a adds data model for issues
Mike Becker <universe@uap-core.de>
parents: 37
diff changeset
72 'Improvement',
833e0385572a adds data model for issues
Mike Becker <universe@uap-core.de>
parents: 37
diff changeset
73 'Bug',
833e0385572a adds data model for issues
Mike Becker <universe@uap-core.de>
parents: 37
diff changeset
74 'Task',
833e0385572a adds data model for issues
Mike Becker <universe@uap-core.de>
parents: 37
diff changeset
75 'Test'
206
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
76 );
62
833e0385572a adds data model for issues
Mike Becker <universe@uap-core.de>
parents: 37
diff changeset
77
206
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
78 create table lpit_issue_phases
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
79 (
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
80 status issue_status primary key,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
81 phase integer not null
81
1a2e7b5d48f7 adds issue summaries
Mike Becker <universe@uap-core.de>
parents: 75
diff changeset
82 );
1a2e7b5d48f7 adds issue summaries
Mike Becker <universe@uap-core.de>
parents: 75
diff changeset
83
206
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
84 create table lpit_issue
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
85 (
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
86 issueid serial primary key,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
87 project integer not null references lpit_project (projectid),
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
88 component integer references lpit_component (id),
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
89 status issue_status not null default 'InSpecification',
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
90 category issue_category not null default 'Feature',
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
91 subject text not null,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
92 description text,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
93 assignee integer references lpit_user (userid),
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
94 created timestamp with time zone not null default now(),
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
95 updated timestamp with time zone not null default now(),
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
96 eta date
62
833e0385572a adds data model for issues
Mike Becker <universe@uap-core.de>
parents: 37
diff changeset
97 );
75
33b6843fdf8a adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents: 62
diff changeset
98
206
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
99 create table lpit_issue_affected_version
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
100 (
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
101 issueid integer references lpit_issue (issueid),
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
102 versionid integer references lpit_version (versionid),
75
33b6843fdf8a adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents: 62
diff changeset
103 primary key (issueid, versionid)
33b6843fdf8a adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents: 62
diff changeset
104 );
33b6843fdf8a adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents: 62
diff changeset
105
206
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
106 create table lpit_issue_resolved_version
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
107 (
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
108 issueid integer references lpit_issue (issueid),
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
109 versionid integer references lpit_version (versionid),
75
33b6843fdf8a adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents: 62
diff changeset
110 primary key (issueid, versionid)
33b6843fdf8a adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents: 62
diff changeset
111 );
33b6843fdf8a adds the ability to create and edit issues
Mike Becker <universe@uap-core.de>
parents: 62
diff changeset
112
206
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
113 create table lpit_issue_comment
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
114 (
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
115 commentid serial primary key,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
116 issueid integer not null references lpit_issue (issueid),
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
117 userid integer references lpit_user (userid),
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
118 created timestamp with time zone not null default now(),
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
119 updated timestamp with time zone not null default now(),
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
120 updatecount integer not null default 0,
fe4de34822a5 #133 changes all varchar columns to text
Mike Becker <universe@uap-core.de>
parents: 187
diff changeset
121 comment text not null
124
ed2e7aef2a3e adds issue comments
Mike Becker <universe@uap-core.de>
parents: 88
diff changeset
122 );

mercurial