universe@20: -- This script creates the module management tables
universe@20: --
universe@2: 
universe@37: create table lpit_user (
universe@20:     userid          serial          primary key,
universe@20:     username        varchar(50)     not null unique,
universe@37:     mail            varchar(50),
universe@20:     lastname        varchar(50),
universe@20:     givenname       varchar(50)
universe@20: );
universe@37: 
universe@37: create table lpit_project (
universe@75:     projectid       serial          primary key,
universe@37:     name            varchar(20)     not null unique,
universe@37:     description     varchar(200),
universe@37:     repoUrl         varchar(50),
universe@37:     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@37: );
universe@37: 
universe@37: create table lpit_version (
universe@75:     versionid       serial          primary key,
universe@75:     project         integer         not null references lpit_project(projectid),
universe@37:     name            varchar(20)     not null,
universe@37:     ordinal         integer         not null default 0,
universe@37:     status          version_status  not null default 'Future'
universe@37: );
universe@62: 
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@62: );
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@62: );
universe@62: 
universe@81: create table lpit_issue_phases (
universe@81:     status          issue_status    primary key,
universe@81:     phase           integer         not null
universe@81: );
universe@81: 
universe@62: create table lpit_issue (
universe@75:     issueid         serial          primary key,
universe@75:     project         integer         not null references lpit_project(projectid),
universe@62:     status          issue_status    not null default 'InSpecification',
universe@62:     category        issue_category  not null default 'Feature',
universe@85:     subject         varchar(200)    not null,
universe@62:     description     text,
universe@75:     assignee        integer         references lpit_user(userid),
universe@62:     created         timestamp       with time zone not null default now(),
universe@62:     updated         timestamp       with time zone not null default now(),
universe@62:     eta             date
universe@62: );
universe@75: 
universe@75: create table lpit_issue_affected_version (
universe@75:     issueid         integer         references lpit_issue(issueid),
universe@75:     versionid       integer         references lpit_version(versionid),
universe@75:     primary key (issueid, versionid)
universe@75: );
universe@75: 
universe@75: create table lpit_issue_resolved_version (
universe@75:     issueid         integer         references lpit_issue(issueid),
universe@75:     versionid       integer         references lpit_version(versionid),
universe@75:     primary key (issueid, versionid)
universe@75: );
universe@75: 
universe@75: