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

setup/postgres/psql_create_tables.sql@87328572e36f (annotated)

setup/postgres/psql_create_tables.sql

Wed, 18 Aug 2021 14:57:45 +0200

author: Mike Becker <universe@uap-core.de>
date: Wed, 18 Aug 2021 14:57:45 +0200
changeset 225: 87328572e36f
parent 206: fe4de34822a5
child 227: f0ede8046b59
permissions: -rw-r--r--

#159 adds release and eol dates

 -- 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)
 );
 create unique index lpit_component_node_unique on lpit_component (project, node);
 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     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
 );
 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@87328572e36f (annotated)

setup/postgres/psql_create_tables.sql