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

setup/postgres/psql_create_tables.sql@947d0f6a6a83 (annotated)

setup/postgres/psql_create_tables.sql

Thu, 15 Oct 2020 13:31:52 +0200

author: Mike Becker <universe@uap-core.de>
date: Thu, 15 Oct 2020 13:31:52 +0200
changeset 128: 947d0f6a6a83
parent 127: 6105ee2cceaf
child 138: e2aa673dd473
permissions: -rw-r--r--

changes the way how to deal with child entities + adds component lead

 -- This script creates the module management tables
 --
 create table lpit_user (
     userid          serial          primary key,
     username        varchar(50)     not null unique,
     mail            varchar(50),
     lastname        varchar(50),
     givenname       varchar(50)
 );
 create table lpit_project (
     projectid       serial          primary key,
     name            varchar(20)     not null unique,
     description     varchar(200),
     repoUrl         varchar(50),
     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            varchar(20)     not null,
     ordinal         integer         not null default 0,
     status          version_status  not null default 'Future'
 );
 create table lpit_component (
     id              serial          primary key,
     project         integer         not null references lpit_project(projectid),
     name            varchar(20)     not null,
     color           char(6)         not null default '000000',
     ordinal         integer         not null default 0,
     description     text,
     lead            integer         references lpit_user(userid)
 );
 create type issue_status as enum (
     'InSpecification',
     'ToDo',
     'Scheduled',
     'InProgress',
     'InReview',
     '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         varchar(200)    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
 );
 create table lpit_issue_affected_version (
     issueid         integer         references lpit_issue(issueid),
     versionid       integer         references lpit_version(versionid),
     primary key (issueid, versionid)
 );
 create table lpit_issue_resolved_version (
     issueid         integer         references lpit_issue(issueid),
     versionid       integer         references lpit_version(versionid),
     primary key (issueid, versionid)
 );
 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
 );

Mercurial > hg > LightPIT / annotate

setup/postgres/psql_create_tables.sql@947d0f6a6a83 (annotated)

setup/postgres/psql_create_tables.sql