setup/postgres/psql_create_tables.sql

-- 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
);