LightPIT: setup/postgres/psql_create_tables.sql@aa22103809cd (annotated)

setup/postgres/psql_create_tables.sql@aa22103809cd (annotated)

setup/postgres/psql_create_tables.sql

Fri, 30 Dec 2022 19:04:34 +0100

author: Mike Becker <universe@uap-core.de>
date: Fri, 30 Dec 2022 19:04:34 +0100
changeset 263: aa22103809cd
parent 252: 90f5e12c7623
child 268: ca5501d851fa
permissions: -rw-r--r--

#29 add possibility to relate issues

 -- This script creates the module management tables
 --
 create table lpit_user
 (
     userid    serial primary key,
     username  text not null unique,
     mail      text,
     lastname  text,
     givenname text
 );
 create table lpit_project
 (
     projectid   serial primary key,
     name        text    not null unique,
     node        text    not null unique,
     ordinal     integer not null default 0,
     description text,
     repoUrl     text,
     owner       integer references lpit_user (userid)
 );
 create type version_status as enum (
     'Future',
     'Unreleased',
     'Released',
     'LTS',
     'Deprecated'
     );
 create table lpit_version
 (
     versionid serial primary key,
     project   integer        not null references lpit_project (projectid),
     name      text           not null,
     node      text           not null,
     ordinal   integer        not null default 0,
     status    version_status not null default 'Future',
     release   date,
     eol       date
 );
 create unique index lpit_version_node_unique on lpit_version (project, node);
 create table lpit_component
 (
     id          serial primary key,
     project     integer not null references lpit_project (projectid),
     name        text    not null,
     node        text    not null,
     color       char(6) not null default '000000',
     ordinal     integer not null default 0,
     description text,
     lead        integer references lpit_user (userid),
     active      boolean not null default true
 );
 create unique index lpit_component_node_unique on lpit_component (project, node);
 create type issue_status as enum (
     'InSpecification',
     'ToDo',
     'Scheduled',
     'InProgress',
     'InReview',
     'Ready',
     'Done',
     'Rejected',
     'Withdrawn',
     'Duplicate'
     );
 create type issue_category as enum (
     'Feature',
     'Improvement',
     'Bug',
     'Task',
     'Test'
     );
 create table lpit_issue_phases
 (
     status issue_status primary key,
     phase  integer not null
 );
 create table lpit_issue
 (
     issueid     serial primary key,
     project     integer                  not null references lpit_project (projectid),
     component   integer references lpit_component (id),
     status      issue_status             not null default 'InSpecification',
     category    issue_category           not null default 'Feature',
     subject     text                     not null,
     description text,
     assignee    integer references lpit_user (userid),
     created     timestamp with time zone not null default now(),
     updated     timestamp with time zone not null default now(),
     eta         date,
     affected    integer references lpit_version (versionid),
     resolved    integer references lpit_version (versionid)
 );
 create type issue_history_event as enum (
     'New',
     'Update',
     'NewComment',
     'UpdateComment'
     );
 create table lpit_issue_history_event
 (
     eventid serial primary key,
     issueid integer                  not null references lpit_issue (issueid) on delete cascade,
     subject text                     not null,
     time    timestamp with time zone not null default now(),
     type    issue_history_event      not null
 );
 create table lpit_issue_history_data
 (
     eventid           integer        not null references lpit_issue_history_event (eventid) on delete cascade,
     component         text,
     status            issue_status   not null,
     category          issue_category not null,
     description       text,
     assignee          text,
     eta               date,
     affected          text,
     resolved          text
 );
 create table lpit_issue_comment
 (
     commentid   serial primary key,
     issueid     integer                  not null references lpit_issue (issueid),
     userid      integer references lpit_user (userid),
     created     timestamp with time zone not null default now(),
     updated     timestamp with time zone not null default now(),
     updatecount integer                  not null default 0,
     comment     text                     not null
 );
 create table lpit_issue_comment_history
 (
     commentid integer not null references lpit_issue_comment (commentid) on delete cascade,
     eventid   integer not null references lpit_issue_history_event (eventid) on delete cascade,
     comment   text    not null
 );
 create type relation_type as enum (
     'RelatesTo',
     'TogetherWith',
     'Before',
     'SubtaskOf',
     'Blocks',
     'Tests',
     'Duplicates'
     );
 create table lpit_issue_relation
 (
     from_issue integer       not null references lpit_issue (issueid) on delete cascade,
     to_issue   integer       not null references lpit_issue (issueid) on delete cascade,
     type       relation_type not null
 );
 create unique index lpit_issue_relation_unique on lpit_issue_relation (from_issue, to_issue, type);

Mercurial > hg > LightPIT / annotate

setup/postgres/psql_create_tables.sql@aa22103809cd (annotated)

setup/postgres/psql_create_tables.sql