universe@20: -- This script creates the module management tables
universe@20: --
universe@2: 
universe@206: create table lpit_user
universe@206: (
universe@206:     userid    serial primary key,
universe@206:     username  text not null unique,
universe@206:     mail      text,
universe@206:     lastname  text,
universe@206:     givenname text
universe@20: );
universe@37: 
universe@206: create table lpit_project
universe@206: (
universe@206:     projectid   serial primary key,
universe@206:     name        text    not null unique,
universe@206:     node        text    not null unique,
universe@206:     ordinal     integer not null default 0,
universe@206:     description text,
universe@206:     repoUrl     text,
universe@206:     owner       integer references lpit_user (userid)
universe@37: );
universe@37: 
universe@37: create type version_status as enum (
universe@37:     'Future',
universe@37:     'Unreleased',
universe@37:     'Released',
universe@37:     'LTS',
universe@37:     'Deprecated'
universe@206:     );
universe@206: 
universe@206: create table lpit_version
universe@206: (
universe@206:     versionid serial primary key,
universe@206:     project   integer        not null references lpit_project (projectid),
universe@206:     name      text           not null,
universe@206:     node      text           not null,
universe@206:     ordinal   integer        not null default 0,
universe@225:     status    version_status not null default 'Future',
universe@225:     release   date,
universe@225:     eol       date
universe@37: );
universe@37: 
universe@206: create unique index lpit_version_node_unique on lpit_version (project, node);
universe@206: 
universe@206: create table lpit_component
universe@206: (
universe@206:     id          serial primary key,
universe@206:     project     integer not null references lpit_project (projectid),
universe@206:     name        text    not null,
universe@206:     node        text    not null,
universe@206:     color       char(6) not null default '000000',
universe@206:     ordinal     integer not null default 0,
universe@206:     description text,
universe@206:     lead        integer references lpit_user (userid)
universe@37: );
universe@62: 
universe@206: create unique index lpit_component_node_unique on lpit_component (project, node);
universe@138: 
universe@62: create type issue_status as enum (
universe@62:     'InSpecification',
universe@62:     'ToDo',
universe@62:     'Scheduled',
universe@62:     'InProgress',
universe@62:     'InReview',
universe@62:     'Done',
universe@62:     'Rejected',
universe@81:     'Withdrawn',
universe@81:     'Duplicate'
universe@206:     );
universe@62: 
universe@62: create type issue_category as enum (
universe@62:     'Feature',
universe@62:     'Improvement',
universe@62:     'Bug',
universe@62:     'Task',
universe@62:     'Test'
universe@206:     );
universe@206: 
universe@206: create table lpit_issue_phases
universe@206: (
universe@206:     status issue_status primary key,
universe@206:     phase  integer not null
universe@62: );
universe@62: 
universe@206: create table lpit_issue
universe@206: (
universe@206:     issueid     serial primary key,
universe@206:     project     integer                  not null references lpit_project (projectid),
universe@206:     component   integer references lpit_component (id),
universe@206:     status      issue_status             not null default 'InSpecification',
universe@206:     category    issue_category           not null default 'Feature',
universe@206:     subject     text                     not null,
universe@206:     description text,
universe@206:     assignee    integer references lpit_user (userid),
universe@206:     created     timestamp with time zone not null default now(),
universe@206:     updated     timestamp with time zone not null default now(),
universe@206:     eta         date
universe@81: );
universe@81: 
universe@206: create table lpit_issue_affected_version
universe@206: (
universe@206:     issueid   integer references lpit_issue (issueid),
universe@206:     versionid integer references lpit_version (versionid),
universe@75:     primary key (issueid, versionid)
universe@75: );
universe@75: 
universe@206: create table lpit_issue_resolved_version
universe@206: (
universe@206:     issueid   integer references lpit_issue (issueid),
universe@206:     versionid integer references lpit_version (versionid),
universe@75:     primary key (issueid, versionid)
universe@75: );
universe@75: 
universe@206: create table lpit_issue_comment
universe@206: (
universe@206:     commentid   serial primary key,
universe@206:     issueid     integer                  not null references lpit_issue (issueid),
universe@206:     userid      integer references lpit_user (userid),
universe@206:     created     timestamp with time zone not null default now(),
universe@206:     updated     timestamp with time zone not null default now(),
universe@206:     updatecount integer                  not null default 0,
universe@206:     comment     text                     not null
universe@124: );