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@284: create type vcstype as enum ('None', 'Mercurial', 'Git'); universe@284: 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@284: vcs vcstype not null default 'None'::vcstype, 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@227: lead integer references lpit_user (userid), universe@227: active boolean not null default true 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@252: 'Ready', 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@231: eta date, universe@231: affected integer references lpit_version (versionid), universe@231: resolved integer references lpit_version (versionid) universe@75: ); universe@75: universe@232: create type issue_history_event as enum ( universe@232: 'New', universe@232: 'Update', universe@232: 'NewComment', universe@232: 'UpdateComment' universe@232: ); universe@232: universe@232: create table lpit_issue_history_event universe@232: ( universe@232: eventid serial primary key, universe@232: issueid integer not null references lpit_issue (issueid) on delete cascade, universe@242: subject text not null, universe@232: time timestamp with time zone not null default now(), universe@232: type issue_history_event not null universe@232: ); universe@232: universe@232: create table lpit_issue_history_data universe@232: ( universe@239: eventid integer not null references lpit_issue_history_event (eventid) on delete cascade, universe@239: component text, universe@239: status issue_status not null, universe@239: category issue_category not null, universe@239: description text, universe@239: assignee text, universe@239: eta date, universe@239: affected text, universe@239: resolved text universe@232: ); universe@232: 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: ); universe@232: universe@232: create table lpit_issue_comment_history universe@232: ( universe@232: commentid integer not null references lpit_issue_comment (commentid) on delete cascade, universe@232: eventid integer not null references lpit_issue_history_event (eventid) on delete cascade, universe@232: comment text not null universe@232: ); universe@232: universe@263: create type relation_type as enum ( universe@263: 'RelatesTo', universe@263: 'TogetherWith', universe@263: 'Before', universe@263: 'SubtaskOf', universe@268: 'DefectOf', universe@263: 'Blocks', universe@263: 'Tests', universe@263: 'Duplicates' universe@263: ); universe@263: universe@263: create table lpit_issue_relation universe@263: ( universe@263: from_issue integer not null references lpit_issue (issueid) on delete cascade, universe@263: to_issue integer not null references lpit_issue (issueid) on delete cascade, universe@263: type relation_type not null universe@263: ); universe@263: universe@263: create unique index lpit_issue_relation_unique on lpit_issue_relation (from_issue, to_issue, type); universe@284: universe@284: create table lpit_commit_ref universe@284: ( universe@284: issueid integer not null references lpit_issue (issueid) on delete cascade, universe@284: commit_hash text not null, universe@284: commit_brief text not null universe@284: ); universe@284: universe@284: create unique index lpit_commit_ref_unique on lpit_commit_ref (issueid, commit_hash);