setup/postgres/psql_create_tables.sql

Sat, 31 Oct 2020 09:50:15 +0100

author
Mike Becker <universe@uap-core.de>
date
Sat, 31 Oct 2020 09:50:15 +0100
changeset 154
3d10f2a390a1
parent 138
e2aa673dd473
child 175
1e6f2aace666
permissions
-rw-r--r--

fixes issues that are assigned no version and no component are listed under all projects

     1 -- This script creates the module management tables
     2 --
     4 create table lpit_user (
     5     userid          serial          primary key,
     6     username        varchar(50)     not null unique,
     7     mail            varchar(50),
     8     lastname        varchar(50),
     9     givenname       varchar(50)
    10 );
    12 create table lpit_project (
    13     projectid       serial          primary key,
    14     name            varchar(20)     not null unique,
    15     node            varchar(20)     not null unique,
    16     description     varchar(200),
    17     repoUrl         varchar(50),
    18     owner           integer         references lpit_user(userid)
    19 );
    21 create type version_status as enum (
    22     'Future',
    23     'Unreleased',
    24     'Released',
    25     'LTS',
    26     'Deprecated'
    27 );
    29 create table lpit_version (
    30     versionid       serial          primary key,
    31     project         integer         not null references lpit_project(projectid),
    32     name            varchar(20)     not null,
    33     node            varchar(20)     not null,
    34     ordinal         integer         not null default 0,
    35     status          version_status  not null default 'Future'
    36 );
    38 create unique index lpit_version_node_unique on lpit_version(project, node);
    40 create table lpit_component (
    41     id              serial          primary key,
    42     project         integer         not null references lpit_project(projectid),
    43     name            varchar(20)     not null,
    44     node            varchar(20)     not null,
    45     color           char(6)         not null default '000000',
    46     ordinal         integer         not null default 0,
    47     description     text,
    48     lead            integer         references lpit_user(userid)
    49 );
    51 create unique index lpit_component_node_unique on lpit_component(project, node);
    53 create type issue_status as enum (
    54     'InSpecification',
    55     'ToDo',
    56     'Scheduled',
    57     'InProgress',
    58     'InReview',
    59     'Done',
    60     'Rejected',
    61     'Withdrawn',
    62     'Duplicate'
    63 );
    65 create type issue_category as enum (
    66     'Feature',
    67     'Improvement',
    68     'Bug',
    69     'Task',
    70     'Test'
    71 );
    73 create table lpit_issue_phases (
    74     status          issue_status    primary key,
    75     phase           integer         not null
    76 );
    78 create table lpit_issue (
    79     issueid         serial          primary key,
    80     project         integer         not null references lpit_project(projectid),
    81     component       integer         references lpit_component(id),
    82     status          issue_status    not null default 'InSpecification',
    83     category        issue_category  not null default 'Feature',
    84     subject         varchar(200)    not null,
    85     description     text,
    86     assignee        integer         references lpit_user(userid),
    87     created         timestamp       with time zone not null default now(),
    88     updated         timestamp       with time zone not null default now(),
    89     eta             date
    90 );
    92 create table lpit_issue_affected_version (
    93     issueid         integer         references lpit_issue(issueid),
    94     versionid       integer         references lpit_version(versionid),
    95     primary key (issueid, versionid)
    96 );
    98 create table lpit_issue_resolved_version (
    99     issueid         integer         references lpit_issue(issueid),
   100     versionid       integer         references lpit_version(versionid),
   101     primary key (issueid, versionid)
   102 );
   104 create table lpit_issue_comment (
   105     commentid       serial          primary key,
   106     issueid         integer         not null references lpit_issue(issueid),
   107     userid          integer         references lpit_user(userid),
   108     created         timestamp       with time zone not null default now(),
   109     updated         timestamp       with time zone not null default now(),
   110     updatecount     integer         not null default 0,
   111     comment         text            not null
   112 );

mercurial