Index: openacs-4/contrib/obsolete-packages/ticket-tracker-lite/sql/postgresql/ticket-tracker-lite-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker-lite/sql/postgresql/ticket-tracker-lite-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker-lite/sql/postgresql/ticket-tracker-lite-create.sql 28 May 2001 20:41:57 -0000 1.1 @@ -0,0 +1,182 @@ +-- /packages/ticket-tracker-lite/sql/ticket-tracker-lite-create.sql +-- +-- @author dvr@arsdigita.com +-- @created 2001-02-18 +-- +--------------------------------------------------- + +select acs_object_type__create_type ( + 'ticket_tracker_lite_area', + 'Ticket Tracker Lite Feature Area', + 'Ticket Tracker Lite Feature Area', + 'acs_object', + 'ttl_areas', + 'area_id', + null, + 'f', + null, + null +); + +select acs_object_type__create_type ( + 'ticket_tracker_lite_ticket', + 'Ticket Tracker Lite Ticket', + 'Ticket Tracker Lite Tickets', + 'acs_object', + 'ttl_tickets', + 'ticket_id', + null, + 'f', + null, + null +); + +--begin +-- +-- acs_object_type.create_type ( +-- object_type => 'ticket_tracker_lite_area', +-- pretty_name => 'Ticket Tracker Lite Feature Area', +-- pretty_plural => 'Ticket Tracker Lite Feature Area', +-- table_name => 'ttl_areas', +-- id_column => 'area_id' +-- ); +-- +-- acs_object_type.create_type ( +-- object_type => 'ticket_tracker_lite_ticket', +-- pretty_name => 'Ticket Tracker Lite Ticket', +-- pretty_plural => 'Ticket Tracker Lite Tickets', +-- table_name => 'ttl_tickets', +-- id_column => 'ticket_id' +-- ); +--end; +--/ + +create table ttl_areas ( + area_id integer + constraint ttl_areas_object_fk + references acs_objects + constraint ttl_areas_pk + primary key, + active_p boolean default 't', + name varchar(200) + constraint ttl_areas_name_nn + not null +); + +create table ttl_status ( + status varchar(20) + constraint ttl_status_pk + primary key, + status_name varchar(100) + constraint ttl_status_name_nn + not null, + sort_key integer +); + +insert into ttl_status (status, status_name, sort_key) + values ('queued', 'Queued', 1); + +insert into ttl_status (status, status_name, sort_key) + values ('dev', 'Development', 2); + +insert into ttl_status (status, status_name, sort_key) + values ('staging', 'Staging', 3); + +insert into ttl_status (status, status_name, sort_key) + values ('completed', 'Completed', 4); + +insert into ttl_status (status, status_name, sort_key) + values ('revision', 'Needs revision', 5); + +insert into ttl_status (status, status_name, sort_key) + values ('deferred', 'Deferred', 6); + +create table ttl_tickets ( + ticket_id integer + constraint ttl_tickets_fk + references acs_objects + constraint ttl_tickets_pk + primary key, + active_p boolean default 't', + area_id integer + constraint ttl_tickets_area_fk + references ttl_areas, + name varchar(200) + constraint ttl_tickets_name_nn + not null, + type varchar(20), + status varchar(20) + constraint ttl_tickets_status_fk + references ttl_status, + url varchar(400), + deadline timestamp, + message text, + message_format varchar(10) default 'formatted' + constraint ttl_tickets_message_format_ck + check(message_format in ('plain','formatted','html')), + priority integer default 2 +); + +create table ttl_comments ( + comment_id integer + constraint ttl_comments_pk + primary key, + active_p boolean default 't', + ticket_id integer + constraint ttl_comments_ticket_nn + not null + constraint ttl_comments_ticket_fk + references ttl_tickets, + creation_user integer + constraint ttl_comments_user_fk + references users + constraint ttl_comments_user_nn + not null, + creation_date timestamp default current_timestamp, + message text, + message_format varchar(10) default 'formatted' + constraint ttl_comments_message_format_ck + check(message_format in ('plain','formatted','html')) +); + +create table ttl_assignments ( + ticket_id integer + constraint ttl_assignments_ticket_fk + references ttl_tickets, + user_id integer + constraint ttl_assignments_user_fk + references users, + constraint ttl_assignments_pk + primary key(ticket_id, user_id), + date_assigned timestamp default current_timestamp +); + +create table ttl_area_assignments ( + area_id integer + constraint ttl_area_assignments_ticket_fk + references ttl_areas, + user_id integer + constraint ttl_area_assignments_user_fk + references users, + constraint ttl_area_assignments_pk + primary key(area_id, user_id), + date_assigned timestamp default current_timestamp +); + +create table ttl_prefs ( + user_id integer + constraint ttl_prefs_user_ref + references users + constraint ttl_prefs_user_pk + primary key, + sort varchar(100), + mine_p boolean, + status varchar(100), + area_id integer + constraint ttl_pref_area_fk + references ttl_areas +); + +\i views-and-plsql.sql + + Index: openacs-4/contrib/obsolete-packages/ticket-tracker-lite/sql/postgresql/ticket-tracker-lite-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker-lite/sql/postgresql/ticket-tracker-lite-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker-lite/sql/postgresql/ticket-tracker-lite-drop.sql 28 May 2001 20:41:57 -0000 1.1 @@ -0,0 +1,38 @@ +-- /packages/ticket-tracker-lite/sql/ticket-tracker-lite-drop.sql +-- +-- @author dvr@arsdigita.com +-- @created 2001-02-18 +-- +--------------------------------------------------- + + +drop view ttl_v_areas; +drop view ttl_va_areas; +drop view ttl_v_tickets; +drop view ttl_va_tickets; +drop view ttl_va_comments; +drop view ttl_v_comments_latest; +drop view ttl_v_users; + +drop function ttl_relative_date (timestamp); +drop function ttl_top_privilege_for_object (integer, integer); + +drop table ttl_prefs; +drop table ttl_area_assignments; +drop table ttl_assignments; +drop table ttl_comments; +drop table ttl_tickets; +drop table ttl_status; +drop table ttl_areas; + +delete from acs_permissions +where object_id in (select package_id + from apm_packages + where package_key = 'ticket-tracker-lite'); + +--FIXME - use the API instead +delete from acs_objects where object_type like 'ticket_tracker_lite%'; + +select acs_object_type__drop_type('ticket_tracker_lite_area', 't'); +select acs_object_type__drop_type('ticket_tracker_lite_ticket', 't'); + Index: openacs-4/contrib/obsolete-packages/ticket-tracker-lite/sql/postgresql/views-and-plsql.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/obsolete-packages/ticket-tracker-lite/sql/postgresql/views-and-plsql.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/obsolete-packages/ticket-tracker-lite/sql/postgresql/views-and-plsql.sql 28 May 2001 20:41:57 -0000 1.1 @@ -0,0 +1,100 @@ +-- /packages/ticket-tracker-lite/sql/views-and-plsql.sql +-- +-- @author dvr@arsdigita.com +-- @created 2001-02-18 +-- +--------------------------------------------------- + +-- All areas + +create view ttl_v_areas as +select o.*, a.* +from acs_objects o, ttl_areas a +where o.object_id = a.area_id; + +-- All active areas + +create view ttl_va_areas as +select * from ttl_v_areas where active_p = 't'; + +-- All tickets + +create view ttl_v_tickets as +select o.*, t.* +from acs_objects o, ttl_tickets t +where t.ticket_id = o.object_id; + +-- All active tickets + +create view ttl_va_tickets as +select * from ttl_v_tickets where active_p = 't'; + +-- All active comments + +create view ttl_va_comments +as select * from ttl_comments where active_p = 't'; + +-- The time of the last comment for each ticket + +create view ttl_v_comments_latest as +select * from ttl_va_comments c +where c.comment_id = (select max(c2.comment_id) + from ttl_comments c2 + where c.ticket_id = c2.ticket_id); + +-- All users who assignable to tickets + +create view ttl_v_users as +select + object_id as package_id, + grantee_id as user_id, + privilege +from acs_permissions_all +where privilege = 'write' or + privilege = 'admin'; + + -- + -- if the date is today, show just the time. Otherwise + -- return the date. + -- +create function ttl_relative_date (timestamp) +returns varchar as ' +declare + relative_date alias for $1; +begin + if date_trunc(''day'', relative_date) = date_trunc(''day'', current_timestamp) then + return to_char(relative_date, ''fmhh:fmmi am''); + else + return to_char(relative_date, ''month fmdd, yyyy''); + end if; +end;' language 'plpgsql'; + + -- + -- Return the top privilege the user has for this object + -- At the moment this is either 'admin' or 'write' + -- +create function ttl_top_privilege_for_object (integer, integer) +returns varchar as ' +declare + object_id alias for $1; + user_id alias for $2; + v_privilege acs_privileges.privilege%TYPE; + v_admin_p integer; +begin + select count(*) into v_admin_p + from dual + where acs_permission__permission_p(object_id, user_id, ''admin'') = ''t''; + + if v_admin_p = 1 then + v_privilege := ''admin''; + else + select privilege into v_privilege + from acs_permissions + where object_id = object_id + and grantee_id = user_id + and privilege = ''write''; + end if; + + return v_privilege; +end;' language 'plpgsql'; +