1.1 --- a/setup/postgres/psql_create_tables.sql Thu Aug 19 14:51:04 2021 +0200
1.2 +++ b/setup/postgres/psql_create_tables.sql Thu Aug 19 17:20:43 2021 +0200
1.3 @@ -101,6 +101,35 @@
1.4 resolved integer references lpit_version (versionid)
1.5 );
1.6
1.7 +create type issue_history_event as enum (
1.8 + 'New',
1.9 + 'Update',
1.10 + 'NewComment',
1.11 + 'UpdateComment'
1.12 + );
1.13 +
1.14 +create table lpit_issue_history_event
1.15 +(
1.16 + eventid serial primary key,
1.17 + issueid integer not null references lpit_issue (issueid) on delete cascade,
1.18 + time timestamp with time zone not null default now(),
1.19 + type issue_history_event not null
1.20 +);
1.21 +
1.22 +create table lpit_issue_history_data
1.23 +(
1.24 + eventid integer not null references lpit_issue_history_event (eventid) on delete cascade,
1.25 + component text,
1.26 + status issue_status not null,
1.27 + category issue_category not null,
1.28 + subject text not null,
1.29 + description text,
1.30 + assignee text,
1.31 + eta date,
1.32 + affected text,
1.33 + resolved text
1.34 +);
1.35 +
1.36 create table lpit_issue_comment
1.37 (
1.38 commentid serial primary key,
1.39 @@ -111,3 +140,11 @@
1.40 updatecount integer not null default 0,
1.41 comment text not null
1.42 );
1.43 +
1.44 +create table lpit_issue_comment_history
1.45 +(
1.46 + commentid integer not null references lpit_issue_comment (commentid) on delete cascade,
1.47 + eventid integer not null references lpit_issue_history_event (eventid) on delete cascade,
1.48 + comment text not null
1.49 +);
1.50 +
