setup/postgres/psql_create_tables.sql@1438e5a22c55 (annotated)
setup/postgres/psql_create_tables.sql
Sun, 21 Jun 2020 11:38:16 +0200
- author
- Mike Becker <universe@uap-core.de>
- date
- Sun, 21 Jun 2020 11:38:16 +0200
- changeset 88
- 1438e5a22c55
- parent 85
- 3d16ad54b3dc
- child 124
- ed2e7aef2a3e
- permissions
- -rw-r--r--
simplifies version overviews by removing "scheduled issues"
| universe@20 | 1 | -- This script creates the module management tables |
| universe@20 | 2 | -- |
| universe@2 | 3 | |
| universe@37 | 4 | create table lpit_user ( |
| universe@20 | 5 | userid serial primary key, |
| universe@20 | 6 | username varchar(50) not null unique, |
| universe@37 | 7 | mail varchar(50), |
| universe@20 | 8 | lastname varchar(50), |
| universe@20 | 9 | givenname varchar(50) |
| universe@20 | 10 | ); |
| universe@37 | 11 | |
| universe@37 | 12 | create table lpit_project ( |
| universe@75 | 13 | projectid serial primary key, |
| universe@37 | 14 | name varchar(20) not null unique, |
| universe@37 | 15 | description varchar(200), |
| universe@37 | 16 | repoUrl varchar(50), |
| universe@37 | 17 | owner integer references lpit_user(userid) |
| universe@37 | 18 | ); |
| universe@37 | 19 | |
| universe@37 | 20 | create type version_status as enum ( |
| universe@37 | 21 | 'Future', |
| universe@37 | 22 | 'Unreleased', |
| universe@37 | 23 | 'Released', |
| universe@37 | 24 | 'LTS', |
| universe@37 | 25 | 'Deprecated' |
| universe@37 | 26 | ); |
| universe@37 | 27 | |
| universe@37 | 28 | create table lpit_version ( |
| universe@75 | 29 | versionid serial primary key, |
| universe@75 | 30 | project integer not null references lpit_project(projectid), |
| universe@37 | 31 | name varchar(20) not null, |
| universe@37 | 32 | ordinal integer not null default 0, |
| universe@37 | 33 | status version_status not null default 'Future' |
| universe@37 | 34 | ); |
| universe@62 | 35 | |
| universe@62 | 36 | create type issue_status as enum ( |
| universe@62 | 37 | 'InSpecification', |
| universe@62 | 38 | 'ToDo', |
| universe@62 | 39 | 'Scheduled', |
| universe@62 | 40 | 'InProgress', |
| universe@62 | 41 | 'InReview', |
| universe@62 | 42 | 'Done', |
| universe@62 | 43 | 'Rejected', |
| universe@81 | 44 | 'Withdrawn', |
| universe@81 | 45 | 'Duplicate' |
| universe@62 | 46 | ); |
| universe@62 | 47 | |
| universe@62 | 48 | create type issue_category as enum ( |
| universe@62 | 49 | 'Feature', |
| universe@62 | 50 | 'Improvement', |
| universe@62 | 51 | 'Bug', |
| universe@62 | 52 | 'Task', |
| universe@62 | 53 | 'Test' |
| universe@62 | 54 | ); |
| universe@62 | 55 | |
| universe@81 | 56 | create table lpit_issue_phases ( |
| universe@81 | 57 | status issue_status primary key, |
| universe@81 | 58 | phase integer not null |
| universe@81 | 59 | ); |
| universe@81 | 60 | |
| universe@62 | 61 | create table lpit_issue ( |
| universe@75 | 62 | issueid serial primary key, |
| universe@75 | 63 | project integer not null references lpit_project(projectid), |
| universe@62 | 64 | status issue_status not null default 'InSpecification', |
| universe@62 | 65 | category issue_category not null default 'Feature', |
| universe@85 | 66 | subject varchar(200) not null, |
| universe@62 | 67 | description text, |
| universe@75 | 68 | assignee integer references lpit_user(userid), |
| universe@62 | 69 | created timestamp with time zone not null default now(), |
| universe@62 | 70 | updated timestamp with time zone not null default now(), |
| universe@62 | 71 | eta date |
| universe@62 | 72 | ); |
| universe@75 | 73 | |
| universe@75 | 74 | create table lpit_issue_affected_version ( |
| universe@75 | 75 | issueid integer references lpit_issue(issueid), |
| universe@75 | 76 | versionid integer references lpit_version(versionid), |
| universe@75 | 77 | primary key (issueid, versionid) |
| universe@75 | 78 | ); |
| universe@75 | 79 | |
| universe@75 | 80 | create table lpit_issue_resolved_version ( |
| universe@75 | 81 | issueid integer references lpit_issue(issueid), |
| universe@75 | 82 | versionid integer references lpit_version(versionid), |
| universe@75 | 83 | primary key (issueid, versionid) |
| universe@75 | 84 | ); |
| universe@75 | 85 | |
| universe@75 | 86 |
