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,
    hidden      boolean not null default false,
    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)
);