-- Ticket tracker v3.0
--
-- Data model
--
--This table holds all the states, severities, etc
create sequence ticket_code_id_sequence;
--
-- The master table of ticket codes
--
create table ticket_codes_i (
code_id integer not null primary key,
-- ascii key for the code set constrain since code depends on it
code_type varchar2(20) constraint c_tkt_code_type check (code_type in ('severity','priority','status','type','cause','source')),
-- the short name: Displayed in tables usually
code varchar2(100) not null,
-- the long name: Displayed in reports and selects
code_long varchar2(400) not null,
-- sort key for ordering non alphabetically
code_seq integer not null,
-- html fragment defining code...
code_help varchar2(4000),
unique (code_type, code)
);
-- Code set collections
-- a code set is the collection of codes that a Project or set of
-- projects use on their tickets (so for example aD can have a
-- small set suited to small groups and HP can have a large set
-- which reflects their formalized ticket resolution process.
--
create table ticket_code_sets (
code_set varchar2(20) not null,
code_id integer not null references ticket_codes_i on delete cascade,
primary key (code_set, code_id)
);
-- Specifically for dealing with "status" field
-- equivalence ticket status as active defered closed
create table ticket_status_info (
code_id integer references ticket_codes_i primary key,
-- this is active closed defered.
status_class varchar(30),
-- tickets requiring action have this set (eg clarify and approve)
status_subclass varchar(30),
-- who is responsible for a ticket in this state
-- code supports user admin assignees and none (for terminal states)
responsibility varchar(30)
);
-- a view of convenience, remove maybe?
create or replace view ticket_codes
as
select tcs.code_set,
tci.code_id,
tci.code_type,
tci.code,
tci.code_long,
tci.code_seq,
tci.code_help
from ticket_codes_i tci, ticket_code_sets tcs
where tcs.code_id = tci.code_id;
-- The difference between a project and a domain in these tables
-- is that a project is something like minipress or acs
-- and domain is a feature area like layout, content, admin.
-- the ticket_project_domain_map is a many-to-many relationship.
-- This will fail on an upgrade since we do not drop it
-- so we can preserve ticket IDs
create sequence ticket_project_id_sequence start with 100;
create table ticket_projects (
project_id integer not null primary key,
title varchar2(30) not null,
title_long varchar2(100) not null,
version varchar2(100),
-- person who request the project and will be the owner
created_by integer not null references users,
start_date date not null,
end_date date,
-- group responsible for this project
group_id references user_groups,
public_p char(1) default('f') check (public_p in ('t','f')),
description varchar2(700),
-- the set of codes used for this project
code_set varchar2(80) not null,
-- the ticket entry mode
default_mode varchar2(80) not null,
message_template varchar2(4000)
);
-- SHould have named this ticket_project_milestones, nobody likes deadlines
create table ticket_deadlines (
project_id integer not null references ticket_projects,
name varchar2(100) not null,
deadline date not null,
primary key (project_id, name)
);
create sequence ticket_domain_id_sequence start with 100;
create table ticket_domains (
domain_id integer not null primary key,
title varchar2(30) not null,
title_long varchar2(100) not null,
-- person who request the project and will be the owner
created_by integer not null references users,
-- person who gets defaultly assigned to new tickets in the project
default_assignee integer references users,
-- group responsible for this domain
group_id references user_groups,
-- Should this domain be visible to non project members
public_p char(1) default('f') check (public_p in ('t','f')),
description varchar2(700),
-- The default notifications for this domain
notify_admin_p char(1) default('f') check(notify_admin_p in ('t','f')),
notify_comment_p char(1) default('t') check(notify_comment_p in ('t','f')),
notify_status_p char(1) default('t') check(notify_status_p in ('t','f')),
-- date after which this domain is inactive.
end_date date,
-- the new message template for the given domain
-- takes precedence over the template for the project
-- in the event both exist
message_template varchar2(4000)
);
-- This is the table which maps strings to projects and domains. The mapping_key
-- can be a module_key (references acs_modules table) so that we can log
-- tickets for a particular module.
create table ticket_domain_project_map (
project_id integer not null references ticket_projects,
domain_id integer not null references ticket_domains,
-- takes precedence over ticket_domains
default_assignee integer references users,
-- The group from which assignments are made.
-- typically the same as owning group on feature area
assignment_group_id references user_groups,
mapping_key varchar(200) unique,
primary key (project_id, domain_id)
);
create or replace function ticket_admin_group_id
return integer
as
v_group_id integer;
begin
select group_id into v_group_id
from administration_info
where module = 'ticket'
and submodule is null;
return v_group_id;
end;
/
-- A table to assign people to feature areas -- NOT USED
-- use groups instead, rate, purpose, et al should be from
-- intranet.
create sequence ticket_assignment_id_seq;
create table ticket_assignments (
assignment_id integer not null primary key,
domain_id integer references ticket_domains,
user_id integer references users,
rate integer, -- e.g. 125
purpose varchar2(400), -- e.g. "HTML, Java, etc..."
-- we add this active flag in case someone gets taken off the
-- project.
active_p char(1) default('t') check (active_p in ('t','f'))
);
create sequence ticket_issue_id_sequence start with 1000;
create table ticket_issues_i (
msg_id integer not null primary key,
project_id integer not null references ticket_projects,
-- so we can track bug identified in version x.
version varchar2(100),
domain_id integer not null references ticket_domains,
-- the submitting user
user_id integer references users,
-- will only work acs 3.0 where we have a keyed address_book
-- address_book_id integer references address_book,
-- bug report
one_line varchar2(200),
comment_id integer references general_comments,
--
-- stuff for remotely submitted tickets
from_host varchar2(200),
from_url varchar2(700),
from_query varchar2(4000),
from_project varchar2(80),
-- the browser string of the user submitting the ticket
from_user_agent varchar2(300),
from_ip varchar2(50),
--
-- Various state variables
ticket_type_id integer references ticket_codes_i(code_id),
priority_id integer references ticket_codes_i(code_id),
status_id integer references ticket_codes_i(code_id),
severity_id integer references ticket_codes_i(code_id),
source_id integer references ticket_codes_i(code_id),
cause_id integer references ticket_codes_i(code_id),
--
posting_time date not null,
last_status_change date,
closed_date date,
closed_by integer references users,
deadline date,
-- When was the last "nag" notification sent
last_notification date,
-- is this ticket visible to non project group members?
public_p char(1) default('t') check(public_p in ('t','f')),
-- if notify_p is 't', member of that project will receive
-- notification email. NB: NO LONGER USED
notify_p char(1) default('t') check(notify_p in ('t','f')),
--
-- The auditing information
-- the user ID and IP address of the last modifier of the product
last_modified date not null,
last_modifying_user not null references users(user_id),
modified_ip_address varchar2(20) not null
);
--
-- The audit table
--
create table ticket_issues_i_audit as select * from ticket_issues_i where 1 = 0;
alter table ticket_issues_i_audit add (
delete_p char(1) default('f') check (delete_p in ('t','f'))
);
--
-- create this outside ticket_issues_i since
-- the we can say ticket_issues_i.* in queries and not
-- have terabytes of dup information coming back to the server
-- I know I could just change the view but the we would have to list
-- all the columns on the view which I hate
--
-- After creating it I promptly discarded it since I think it
-- is better to hit general_comments directly.
--
-- Still here since it might be useful later (and is populated on
-- insert but not on comment add
--
create table ticket_index (
msg_id integer not null primary key references ticket_issues_i,
indexed_stuff clob default empty_clob(),
last_modified date not null
);
create sequence ticket_alert_id_sequence;
create table ticket_email_alerts (
alert_id integer not null primary key,
user_id not null references users,
msg_id references ticket_issues_i,
domain_id references ticket_domains,
project_id references ticket_projects,
established date,
active_p char(1) default 't' check (active_p in ('t','f'))
);
--
-- The two views to hide the code table lookups
-- and get us our fancy versions of the output data
--
create or replace view ticket_issues
as
select
ti.*, -- FIX THIS IN PROD no select star
to_char(ti.last_modified, 'mm/dd/yy') as modification_mdy,
to_char(ti.posting_time, 'mm/dd/yy') as creation_mdy,
to_char(ti.closed_date, 'mm/dd/yy') as close_mdy,
to_char(ti.deadline, 'mm/dd/yy') as deadline_mdy,
to_char(trunc(sysdate - deadline)) as pastdue_days,
sev.code as severity, sev.code_seq as severity_seq, sev.code_long as severity_long,
pri.code as priority, pri.code_seq as priority_seq, pri.code_long as priority_long,
stat.code as status, stat.code_seq as status_seq, stat.code_long as status_long,
src.code as source, src.code_seq as source_seq, src.code_long as source_long,
def.code as cause, def.code_seq as cause_seq, def.code_long as cause_long,
type.code as ticket_type, type.code_seq as ticket_type_seq, type.code_long as ticket_type_long,
tsi.status_class, tsi.status_subclass, tsi.responsibility
from
ticket_issues_i ti,
ticket_codes_i sev,
ticket_codes_i pri,
ticket_codes_i stat,
ticket_codes_i src,
ticket_codes_i def,
ticket_codes_i type,
ticket_status_info tsi
where
sev.code_id(+) = ti.severity_id
and pri.code_id(+) = ti.priority_id
and stat.code_id(+) = ti.status_id
and src.code_id(+) = ti.source_id
and def.code_id(+) = ti.cause_id
and type.code_id(+) = ti.ticket_type_id
and tsi.code_id(+) = ti.status_id;
create or replace view ticket_issues_audit
as
select
ti.*, -- FIX THIS IN PROD no select star
to_char(ti.last_modified, 'mm/dd/yy') as modification_mdy,
to_char(ti.posting_time, 'mm/dd/yy') as creation_mdy,
to_char(ti.closed_date, 'mm/dd/yy') as close_mdy,
to_char(ti.deadline, 'mm/dd/yy') as deadline_mdy,
to_char(trunc(sysdate - deadline)) as pastdue_days,
sev.code as severity, sev.code_seq as severity_seq, sev.code_long as severity_long,
pri.code as priority, pri.code_seq as priority_seq, pri.code_long as priority_long,
stat.code as status, stat.code_seq as status_seq, stat.code_long as status_long,
src.code as source, src.code_seq as source_seq, src.code_long as source_long,
def.code as cause, def.code_seq as cause_seq, def.code_long as cause_long,
type.code as ticket_type, type.code_seq as ticket_type_seq, type.code_long as ticket_type_long,
tsi.status_class, tsi.status_subclass, tsi.responsibility
from
ticket_issues_i_audit ti,
ticket_codes_i sev,
ticket_codes_i pri,
ticket_codes_i stat,
ticket_codes_i src,
ticket_codes_i def,
ticket_codes_i type,
ticket_status_info tsi
where
sev.code_id(+) = ti.severity_id
and pri.code_id(+) = ti.priority_id
and stat.code_id(+) = ti.status_id
and src.code_id(+) = ti.source_id
and def.code_id(+) = ti.cause_id
and type.code_id(+) = ti.ticket_type_id
and tsi.code_id(+) = ti.status_id;
create or replace trigger ticket_modification_time
before insert or update on ticket_issues_i
for each row
when (new.last_modified is null)
begin
:new.last_modified := SYSDATE;
end;
/
--- a table to assign users to issues
--- the selection list for this will be the
--- ticket_assignments table constrained by the appropriate project
create table ticket_issue_assignments (
msg_id integer not NULL references ticket_issues_i,
user_id integer not null references users,
-- why assigned e.g. "code review, spec compliance, etc."
purpose varchar2(4000),
-- we add this active flag in case someone gets taken off the
-- issue. Not really used now.
active_p char(1) default 't' check (active_p in ('t','f')),
primary key (msg_id, user_id)
);
-- cross reference table mapping issues to other issues
create table ticket_xrefs (
from_ticket references ticket_issues_i(msg_id),
to_ticket references ticket_issues_i(msg_id)
);
-- Nuke this and use general comments instead
--
-- create sequence ticket_response_id_sequence;
--
-- create table ticket_issue_responses (
-- response_id integer not null primary key,
-- response_to integer not null references ticket_issues_i,
-- user_id references users,
-- posting_time date not null,
-- public_p char(1) default('t') check(public_p in ('t','f')),
-- message clob default empty_clob(),
-- html_p char(1) default('f') check(html_p in ('t','f'))
-- );
--
-- update the tickets comment timestamp
create or replace trigger ticket_response_mod_time
after insert or update on general_comments
for each row
begin
update ticket_index set last_modified = SYSDATE
where msg_id = :new.on_what_id and :new.on_which_table = 'ticket_issues';
end;
/
show errors
-- NOT USED YET...current notify scheme embeded in code.
create table ticket_issue_notifications (
msg_id integer not null references ticket_issues_i,
user_id integer not null references users,
role varchar2(60) default 'assignee',
notify_on varchar2(200) default 'all',
primary key (msg_id, user_id)
);
-- -- Cant triggerize this?
-- -- I tried but got:
-- -- ORA-04091: table PS.GENERAL_COMMENTS is mutating, trigger/function may not see it
-- --
-- -- in any case I just discarded this since it turned out to be as easy
-- -- to search directly in the general_comments table.
--
-- create or replace procedure ticket_build_index(v_response_id IN integer)
-- AS
-- v_response_row general_comments%ROWTYPE;
-- v_indexed_stuff clob;
-- BEGIN
-- select general_comments.* into v_response_row
-- from general_comments
-- where comment_id = v_response_id;
--
-- if v_response_row.content is not null then
-- select indexed_stuff into v_indexed_stuff
-- from ticket_index
-- where msg_id = v_response_row.on_what_id
-- for update;
-- dbms_lob.append(v_indexed_stuff, v_response_row.content);
-- end if;
-- END;
-- /
-- show errors
create or replace function ticket_one_if_high_priority (priority IN integer, status IN varchar)
return integer
is
BEGIN
IF ((priority = 1) AND (status <> 'closed') AND (status <> 'deferred')) THEN
return 1;
ELSE
return 0;
END IF;
END ticket_one_if_high_priority;
/
show errors
create or replace function ticket_one_if_blocker (severity IN varchar, status IN varchar)
return integer
is
BEGIN
IF ((severity = 'showstopper') AND (status <> 'closed') AND (status <> 'deferred')) THEN
return 1;
ELSE
return 0;
END IF;
END ticket_one_if_blocker;
/
show errors
create or replace trigger TICKET_ISSUES_I_audit_tr
before update or delete on TICKET_ISSUES_I
for each row
begin
insert into TICKET_ISSUES_I_audit (
MSG_ID,
PROJECT_ID,
VERSION,
DOMAIN_ID,
USER_ID,
ONE_LINE,
COMMENT_ID,
FROM_HOST,
FROM_URL,
FROM_QUERY,
FROM_PROJECT,
FROM_USER_AGENT,
FROM_IP,
TICKET_TYPE_ID,
PRIORITY_ID,
STATUS_ID,
SEVERITY_ID,
SOURCE_ID,
CAUSE_ID,
POSTING_TIME,
LAST_STATUS_CHANGE,
CLOSED_DATE,
CLOSED_BY,
DEADLINE,
LAST_NOTIFICATION,
PUBLIC_P,
NOTIFY_P,
LAST_MODIFIED,
LAST_MODIFYING_USER,
MODIFIED_IP_ADDRESS
) values (
:old.MSG_ID,
:old.PROJECT_ID,
:old.VERSION,
:old.DOMAIN_ID,
:old.USER_ID,
:old.ONE_LINE,
:old.COMMENT_ID,
:old.FROM_HOST,
:old.FROM_URL,
:old.FROM_QUERY,
:old.FROM_PROJECT,
:old.FROM_USER_AGENT,
:old.FROM_IP,
:old.TICKET_TYPE_ID,
:old.PRIORITY_ID,
:old.STATUS_ID,
:old.SEVERITY_ID,
:old.SOURCE_ID,
:old.CAUSE_ID,
:old.POSTING_TIME,
:old.LAST_STATUS_CHANGE,
:old.CLOSED_DATE,
:old.CLOSED_BY,
:old.DEADLINE,
:old.LAST_NOTIFICATION,
:old.PUBLIC_P,
:old.NOTIFY_P,
:old.LAST_MODIFIED,
:old.LAST_MODIFYING_USER,
:old.MODIFIED_IP_ADDRESS);
end;
/
show errors
-- thats all
--
-- This is to get human decipherable audit trails.
--
create or replace view ticket_pretty
as
select
t.msg_id,
u.email as submitted_by,
tp.title_long as project,
td.title_long as feature_area,
t.version,
t.one_line as subject,
t.from_url as from_url,
t.severity_long as severity,
t.priority_long as priority,
t.status_long as status,
t.cause_long as cause,
t.LAST_MODIFIED,
t.LAST_MODIFYING_USER,
t.MODIFIED_IP_ADDRESS
from ticket_issues t, users u, ticket_domains td, ticket_projects tp
where t.user_id = u.user_id
and td.domain_id = t.domain_id
and tp.project_id = t.project_id;
create or replace view ticket_pretty_audit
as
select
t.msg_id,
u.email as submitted_by,
tp.title_long as project,
td.title_long as feature_area,
t.version,
t.one_line as subject,
t.from_url as from_url,
t.severity_long as severity,
t.priority_long as priority,
t.status_long as status,
t.cause_long as cause,
t.LAST_MODIFIED,
t.LAST_MODIFYING_USER,
t.MODIFIED_IP_ADDRESS,
t.delete_p
from ticket_issues_audit t, users u, ticket_domains td, ticket_projects tp
where t.user_id = u.user_id
and td.domain_id = t.domain_id
and tp.project_id = t.project_id;
-- view public tickets or tickets for which user is in project or
-- domain group.
create or replace view ticket_viewable as
select u.user_id, ti.msg_id
from ticket_issues_i ti, ticket_domains td, ticket_projects tp, users u
where tp.project_id = ti.project_id
and td.domain_id = ti.domain_id
and ((tp.public_p = 't' and ti.public_p = 't' and td.public_p = 't')
or exists (select 1
from user_group_map
where (group_id = tp.group_id or group_id = ticket_admin_group_id)
and user_id = u.user_id)
or exists (select 1
from user_group_map
where group_id = td.group_id
and user_id = u.user_id));
-- edit for which user is in project or domain group.
create or replace view ticket_editable as
select u.user_id, ti.msg_id
from ticket_issues_i ti, ticket_domains td, ticket_projects tp, users u
where tp.project_id = ti.project_id
and td.domain_id = ti.domain_id
and (exists (select 1
from user_group_map
where (group_id = tp.group_id or group_id = ticket_admin_group_id)
and user_id = u.user_id)
or exists (select 1
from user_group_map
where group_id = td.group_id
and user_id = u.user_id));