setup/postgres/psql_create_tables.sql

create table lpit_user
(
    userid              serial primary key,
    username            text                     not null unique,
    mail                text,
    lastname            text,
    givenname           text,
    knows_updates_until timestamp with time zone
);

create type vcstype as enum ('None', 'Mercurial', 'Git');

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,
    vcs         vcstype not null default 'None'::vcstype,
    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 table lpit_variant
(
    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,
    active      boolean not null default true
);

create unique index lpit_variant_node_unique on lpit_variant (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,
    userid  integer                      null references lpit_user (userid) on delete set null,
    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',
    'DefectOf',
    '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);

create table lpit_commit_ref
(
    issueid      integer                  not null references lpit_issue (issueid) on delete cascade,
    commit_hash  text                     not null,
    commit_brief text                     not null,
    commit_time  timestamp with time zone null -- optional feature added with Lightpit 1.5.0
);

create unique index lpit_commit_ref_unique on lpit_commit_ref (issueid, commit_hash);

create table lpit_issue_variant_status
(
    issueid   integer        not null references lpit_issue (issueid),
    variant   integer        not null references lpit_variant (id),
    status    issue_status   not null default 'InSpecification',
    outdated  boolean        not null default false,
    primary key (issueid, variant)
);