setup/postgres/psql_create_tables.sql

Sat, 23 May 2020 13:52:04 +0200

author
Mike Becker <universe@uap-core.de>
date
Sat, 23 May 2020 13:52:04 +0200
changeset 78
bb4c52bf3439
parent 75
33b6843fdf8a
child 81
1a2e7b5d48f7
permissions
-rw-r--r--

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

mercurial