setup/postgres/psql_create_tables.sql

-- 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,
    node            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,
    node            varchar(20)     not null,
    ordinal         integer         not null default 0,
    status          version_status  not null default 'Future'
);

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            varchar(20)     not null,
    node            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 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         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
);