-- packages/acs-events/sql/acs-events-create.sql -- -- @author smeeks@arsdigita.com -- @creation-date 2000-11-16 -- @cvs-id $Id: acs-events-create.sql,v 1.29 2024/09/11 06:15:47 gustafn Exp $ -- Create the objects and packages for the ACS Events service \i oracle-compat-create.sql \i activity-create.sql \i timespan-create.sql \i recurrence-create.sql -- -- procedure inline_0/0 -- CREATE OR REPLACE FUNCTION inline_0( ) RETURNS integer AS $$ DECLARE attr_id acs_attributes.attribute_id%TYPE; BEGIN -- Event object PERFORM acs_object_type__create_type ( 'acs_event', -- object_type 'ACS Event', -- pretty_name 'ACS Events', -- pretty_plural 'acs_object', -- supertype 'acs_events', -- table_name 'event_id', -- id_column null, -- package_name (default) 'f', -- abstract_p (default) null, -- type_extension_table (default) null -- name_method (default) ); -- Event attributes attr_id := acs_attribute__create_attribute ( 'acs_event', -- object_type 'timespan_id', -- attribute_name 'integer', -- datatype 'Timespan', -- pretty_name 'Timespans', -- pretty_plural null, -- table_name (default) null, -- column_name (default) null, -- default_value (default) 1, -- min_n_values (default) 1, -- max_n_values (default) null, -- sort_order (default) 'type_specific', -- storage (default) 'f' -- static_p (default) ); attr_id := acs_attribute__create_attribute ( 'acs_event', -- object_type 'activity_id', -- attribute_name 'integer', -- datatype 'Activity', -- pretty_name 'Activities', -- pretty_plural null, -- table_name (default) null, -- column_name (default) null, -- default_value (default) 1, -- min_n_values (default) 1, -- max_n_values (default) null, -- sort_order (default) 'type_specific', -- storage (default) 'f' -- static_p (default) ); attr_id := acs_attribute__create_attribute ( 'acs_event', -- object_type 'recurrence_id', -- attribute_name 'integer', -- datatype 'Recurrence', -- pretty_name 'Recurrences', -- pretty_plural null, -- table_name (default) null, -- column_name (default) null, -- default_value (default) 1, -- min_n_values (default) 1, -- max_n_values (default) null, -- sort_order (default) 'type_specific', -- storage (default) 'f' -- static_p (default) ); attr_id := acs_attribute__create_attribute ( 'acs_event', -- object_type 'name', -- attribute_name 'string', -- datatype 'Name', -- pretty_name 'Names', -- pretty_plural null, -- table_name (default) null, -- column_name (default) null, -- default_value (default) 1, -- min_n_values (default) 1, -- max_n_values (default) null, -- sort_order (default) 'type_specific', -- storage (default) 'f' -- static_p (default) ); attr_id := acs_attribute__create_attribute ( 'acs_event', -- object_type 'description', -- attribute_name 'string', -- datatype 'Description', -- pretty_name 'Descriptions', -- pretty_plural null, -- table_name (default) null, -- column_name (default) null, -- default_value (default) 1, -- min_n_values (default) 1, -- max_n_values (default) null, -- sort_order (default) 'type_specific', -- storage (default) 'f' -- static_p (default) ); attr_id := acs_attribute__create_attribute ( 'acs_event', -- object_type 'status_summary', -- attribute_name 'string', -- datatype 'Status Summary', -- pretty_name 'Status Summaries', -- pretty_plural null, -- table_name (default) null, -- column_name (default) null, -- default_value (default) 1, -- min_n_values (default) 1, -- max_n_values (default) null, -- sort_order (default) 'type_specific', -- storage (default) 'f' -- static_p (default) ); attr_id := acs_attribute__create_attribute ( 'acs_event', -- object_type 'html_p', -- attribute_name 'boolean', -- datatype 'HTML?', -- pretty_name null, -- pretty_plural null, -- table_name (default) null, -- column_name (default) null, -- default_value (default) 1, -- min_n_values (default) 1, -- max_n_values (default) null, -- sort_order (default) 'type_specific', -- storage (default) 'f' -- static_p (default) ); attr_id := acs_attribute__create_attribute ( 'acs_event', -- object_type 'related_link_url', -- attribute_name 'string', -- datatype 'Related Link URL', -- pretty_name 'Related Link URLs', -- pretty_plural null, -- table_name (default) null, -- column_name (default) null, -- default_value (default) 1, -- min_n_values (default) 1, -- max_n_values (default) null, -- sort_order (default) 'type_specific', -- storage (default) 'f' -- static_p (default) ); attr_id := acs_attribute__create_attribute ( 'acs_event', -- object_type 'related_link_text', -- attribute_name 'string', -- datatype 'Related Link Text', -- pretty_name 'Related Link Texts', -- pretty_plural null, -- table_name (default) null, -- column_name (default) null, -- default_value (default) 1, -- min_n_values (default) 1, -- max_n_values (default) null, -- sort_order (default) 'type_specific', -- storage (default) 'f' -- static_p (default) ); attr_id := acs_attribute__create_attribute ( 'acs_event', -- object_type 'redirect_to_rel_link_p', -- attribute_name 'boolean', -- datatype 'Redirect to Related Link?', -- pretty_name null, -- pretty_plural null, -- table_name (default) null, -- column_name (default) null, -- default_value (default) 1, -- min_n_values (default) 1, -- max_n_values (default) null, -- sort_order (default) 'type_specific', -- storage (default) 'f' -- static_p (default) ); attr_id := acs_attribute__create_attribute ( 'acs_event', -- object_type 'location', -- attribute_name 'string', -- datatype 'Location', -- pretty_name 'Locations', -- pretty_plural null, -- table_name (default) null, -- column_name (default) null, -- default_value (default) 1, -- min_n_values (default) 1, -- max_n_values (default) null, -- sort_order (default) 'type_specific', -- storage (default) 'f' -- static_p (default) ); return 0; END; $$ LANGUAGE plpgsql; -- Do the transaction, then clean up select inline_0 (); drop function inline_0 (); -- Events table create table acs_events ( event_id integer constraint acs_events_event_id_fk references acs_objects(object_id) on delete cascade constraint acs_events_event_id_pk primary key, -- -- Need additional columns for attributes not inherited from activity, e.g. -- activity.name = "Bootcamp" and event.name = "December Bootcamp" -- -- The Event API supports methods to retrieve name/description from -- either the event (if defined) or the underlying activity (if not defined) -- -- acs_event__get_name() -- acs_event__get_description() -- acs_event__get_html_p() -- acs_event__get_status_summary() -- name varchar(255), description text, -- -- is the event description written in html? html_p boolean, status_summary varchar(255), -- -- The following four columns encapsulate the remaining attributes of an Event: -- the activity that takes place during the event, its timespan (a collection of time -- intervals during which the event occurs), and an optional recurrence specification -- that identifies how events repeat in time, and its location. -- activity_id integer constraint acs_events_activity_id_fk references acs_activities on delete set null, -- -- Can't reference timespans since it doesn't have a primary key -- When referencing, need to call timespan.exists_p in case timespan -- was deleted out from under event. -- timespan_id integer, recurrence_id integer constraint acs_events_recurrence_id_fk references recurrences, location varchar(255), -- -- a link which points to a page related to the event -- this could be either additional detail or a substitution -- for the link in some application view, e.g. drill-down from -- calendar. -- related_link_url text, related_link_text text, -- -- Interpretation of this column is application dependent, but it is -- suggested that if this is 't', then related_link_{url|text} should be -- used as the link in summary views in an application. Otherwise, -- related_link_{url|text} should be available in a detail view -- of the event. redirect_to_rel_link_p boolean ); -- This is important to prevent locking on update of master table. -- See http://www.arsdigita.com/bboard/q-and-a-fetch-msg.tcl?msg_id=000KOh create index acs_events_activity_id_idx on acs_events(activity_id); -- This is useful for looking up instances of an event create index acs_events_recurrence_id_idx on acs_events(recurrence_id); create index acs_events_timespan_id_idx on acs_events(timespan_id); comment on table acs_events is ' A relationship between a time span and an activity. '; comment on column acs_events.name is ' The name of the event. '; comment on column acs_events.description is ' The description of the event. '; comment on column acs_events.html_p is ' Whether or not the description is in HTML. '; comment on column acs_events.status_summary is ' Additional information to display along with the name. '; comment on column acs_events.timespan_id is ' The time span associated with this event. '; comment on column acs_events.location is ' The location associated with this event. '; comment on column acs_events.activity_id is ' The activity associated with this event. '; comment on column acs_events.recurrence_id is ' A description of how this event recurs. If null, then this event does not recur. '; -- A table to create associations between events and parties create table acs_event_party_map ( event_id integer constraint acs_evnt_party_map_evnt_id_fk references acs_events on delete cascade, party_id integer constraint acs_evnt_party_map_party_id_fk references parties on delete cascade, constraint acs_evnt_party_map_pk primary key(event_id, party_id) ); comment on table acs_event_party_map is ' Maps a many-to-many relationship between events and parties. '; -- ACS Event Views -- This view makes the temporal information easier to access create view acs_events_dates as select e.*, start_date, end_date from acs_events e, timespans s, time_intervals t where e.timespan_id = s.timespan_id and s.interval_id = t.interval_id; -- Postgres is very strict: we must specify 'comment on view', if not a real table comment on view acs_events_dates is ' This view produces a separate row for each time interval in the timespan associated with an event. '; -- This view provides an alternative to the get_name and get_description -- functions create view acs_events_activities as select event_id, coalesce(e.name, a.name) as name, coalesce(e.description, a.description) as description, coalesce(e.html_p, a.html_p) as html_p, coalesce(e.status_summary, a.status_summary) as status_summary, e.activity_id, timespan_id, recurrence_id, location from acs_events e, acs_activities a where e.activity_id = a.activity_id; comment on view acs_events_activities is ' This view pulls the event name and description from the underlying activity if necessary. '; -- These views should make it easier to find recurrences that -- need to be populated further, e.g. -- -- select recurrence_id -- from partially_populated_events p, acs_event_party_map m -- where db_populated_until < :current_date -- and p.event_id = m.event_id -- and party_id = :party_id -- group by recurrence_id -- create view partially_populated_event_ids as select min(event_id) as event_id, db_populated_until from acs_events e, recurrences r where e.recurrence_id = r.recurrence_id and (recur_until > db_populated_until or recur_until is null) group by r.recurrence_id, db_populated_until; comment on view partially_populated_event_ids is ' This view returns the first event_id and db_populated_until column for any recurrences that have not been completely populated. '; create view partially_populated_events as select e.event_id, timespan_id, activity_id, recurrence_id, db_populated_until from acs_events e, partially_populated_event_ids p where e.event_id = p.event_id; comment on view partially_populated_events is ' This view returns information about recurring events that have not been completely populated (such as indefinitely recurring events.) '; -- ACS Event API -- -- Quick reference for the API supported for the Event object. Note that every procedure -- takes event_id as the first argument, we're just leave it out for compactness. -- -- new (...) -- delete () -- -- get_name () -- get_description () -- get_html_p () -- get_status_summary () -- -- timespan_set (timespan_id) -- activity_set (activity_id) -- -- party_map (party_id) -- party_unmap (party_id) -- -- insert_instances (cutoff_date) -- -- delete_all () -- delete_all (recurrence_id) -- -- shift (start_offset, end_offset) -- shift_all (start_offset, end_offset) -- -- recurs_p () -- backwards compatible 13 param version CREATE OR REPLACE FUNCTION acs_event__new ( integer, varchar, text, boolean, text, integer, integer, integer, varchar, timestamptz, integer, varchar, integer ) RETURNS integer AS $$ BEGIN return acs_event__new($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,null); END; $$ LANGUAGE plpgsql; -- -- procedure acs_event__new/14-18 -- -- Creates a new event (20.10.10) -- -- @author W. Scott Meeks -- -- @param event_id id to use for new event -- @param name Name of the new event -- @param description Description of the new event -- @param html_p Is the description HTML? -- @param status_summary Optional additional status line to display -- @param timespan_id initial time interval set -- @param activity_id initial activity -- @param recurrence_id id of recurrence information -- @param object_type 'acs_event' -- @param creation_date default now() -- @param creation_user acs_object param -- @param creation_ip acs_object param -- @param context_id acs_object param -- @param package_id acs_object param -- @param location location -- @param related_link_url URL -- @param related_link_text anchor for URL -- @param redirect_to_rel_link_p activation flag -- -- @return The id of the new event. select define_function_args('acs_event__new','event_id;null,name;null,description;null,html_p;null,status_summary;null,timespan_id;null,activity_id;null,recurrence_id;null,object_type;acs_event,creation_date;now(),creation_user;null,creation_ip;null,context_id;null,package_id;null,location;null,related_link_url;null,related_link_text;null,redirect_to_rel_link_p;false'); CREATE OR REPLACE FUNCTION acs_event__new( new__event_id integer, -- default null, new__name varchar, -- default null, new__description text, -- default null, new__html_p boolean, -- default null new__status_summary text, -- default null new__timespan_id integer, -- default null, new__activity_id integer, -- default null, new__recurrence_id integer, -- default null, new__object_type varchar, -- default 'acs_event', new__creation_date timestamptz, -- default now(), new__creation_user integer, -- default null, new__creation_ip varchar, -- default null, new__context_id integer, -- default null new__package_id integer, -- default null new__location varchar default NULL, new__related_link_url varchar default NULL, new__related_link_text varchar default NULL, new__redirect_to_rel_link_p boolean default NULL ) RETURNS integer AS $$ DECLARE v_event_id acs_events.event_id%TYPE; BEGIN v_event_id := acs_object__new( new__event_id, -- object_id new__object_type, -- object_type new__creation_date, -- creation_date new__creation_user, -- creation_user new__creation_ip, -- creation_ip new__context_id, -- context_id 't', -- security_inherit_p new__name, -- title new__package_id -- package_id ); insert into acs_events (event_id, name, description, html_p, status_summary, activity_id, timespan_id, recurrence_id, location, related_link_url, related_link_text, redirect_to_rel_link_p) values (v_event_id, new__name, new__description, new__html_p, new__status_summary, new__activity_id, new__timespan_id, new__recurrence_id, new__location, new__related_link_url, new__related_link_text, new__redirect_to_rel_link_p); return v_event_id; END; $$ LANGUAGE plpgsql; -- -- procedure acs_event__delete/1 -- -- Deletes an event (20.10.40) -- Also deletes party mappings (via on delete cascade). -- If this is the last instance of a recurring event, the recurrence -- info is deleted as well -- -- @author W. Scott Meeks -- -- @param event_id id of event to delete -- -- @return 0 (procedure dummy) select define_function_args('acs_event__delete','event_id'); CREATE OR REPLACE FUNCTION acs_event__delete( delete__event_id integer ) RETURNS integer AS $$ DECLARE v_recurrence_id acs_events.recurrence_id%TYPE; BEGIN select recurrence_id into v_recurrence_id from acs_events where event_id = delete__event_id; -- acs_events and acs_event_party_map deleted via on delete cascade PERFORM acs_object__delete(delete__event_id); -- Check for no more instances and delete recurrence if exists if not acs_event__instances_exist_p(v_recurrence_id) then PERFORM recurrence__delete(v_recurrence_id); end if; return 0; END; $$ LANGUAGE plpgsql; -- added select define_function_args('acs_event__delete_all_recurrences','recurrence_id;null'); -- -- procedure acs_event__delete_all_recurrences/1 -- -- Deletes all instances of an event with the same (non-null) recurrence_id. -- -- @author W. Scott Meeks -- -- @param recurrence_id All events with this recurrence_id will be deleted. -- -- @return 0 (procedure dummy) CREATE OR REPLACE FUNCTION acs_event__delete_all_recurrences( delete_all_recurrences__recurrence_id integer -- default null ) RETURNS integer AS $$ DECLARE rec_event record; BEGIN if delete_all_recurrences__recurrence_id is not null then for rec_event in select event_id from acs_events where recurrence_id = delete_all_recurrences__recurrence_id loop PERFORM acs_event__delete(rec_event.event_id); end loop; end if; return 0; END; $$ LANGUAGE plpgsql; -- added select define_function_args('acs_event__delete_all','event_id'); -- -- procedure acs_event__delete_all/1 -- -- -- Deletes all instances of a recurring event with this event_id -- Use acs_event__delete for events with no recurrence -- -- -- @author W. Scott Meeks -- -- @param event_id All events with the same recurrence_id as this one will be deleted. -- -- @return 0 (procedure dummy) -- CREATE OR REPLACE FUNCTION acs_event__delete_all( delete_all__event_id integer ) RETURNS integer AS $$ DECLARE v_recurrence_id acs_events.recurrence_id%TYPE; BEGIN select recurrence_id into v_recurrence_id from acs_events where event_id = delete_all__event_id; PERFORM acs_event__delete_all_recurrences(v_recurrence_id); return 0; END; $$ LANGUAGE plpgsql; -- added select define_function_args('acs_event__get_name','event_id'); -- -- procedure acs_event__get_name/1 -- -- -- Returns the name or the name of the activity associated with the event if -- name is null. -- Equivalent functionality to get_name provided by acs_event_activity view -- -- @author W. Scott Meeks -- -- @param event_id id of event to get name for -- -- @return The name or the name of the activity associated with the event if name is null. -- CREATE OR REPLACE FUNCTION acs_event__get_name( get_name__event_id integer ) RETURNS varchar AS $$ DECLARE v_name acs_events.name%TYPE; BEGIN select coalesce(e.name, a.name) into v_name from acs_events e left join acs_activities a on (e.activity_id = a.activity_id) where e.event_id = get_name__event_id; return v_name; END; $$ LANGUAGE plpgsql; -- added select define_function_args('acs_event__get_description','event_id'); -- -- procedure acs_event__get_description/1 -- -- -- Returns the description or the description of the activity associated -- with the event if description is null. -- Equivalent functionality to get_description provided by acs_event_activity view -- -- @author W. Scott Meeks -- -- @param event_id id of event to get description for -- -- @return The description or the description of the activity associated with the event if description is null. -- CREATE OR REPLACE FUNCTION acs_event__get_description( get_description__event_id integer ) RETURNS text AS $$ DECLARE v_description acs_events.description%TYPE; BEGIN select coalesce(e.description, a.description) into v_description from acs_events e left join acs_activities a on (e.activity_id = a.activity_id) where e.event_id = get_description__event_id; return v_description; END; $$ LANGUAGE plpgsql; -- added select define_function_args('acs_event__get_html_p','event_id'); -- -- procedure acs_event__get_html_p/1 -- -- -- Returns html_p or html_p of the activity associated with the event if -- html_p is null. -- -- @author W. Scott Meeks -- -- @param event_id id of event to get html_p for -- -- @return The html_p or html_p of the activity associated with the event if html_p is null. -- CREATE OR REPLACE FUNCTION acs_event__get_html_p( get_html_p__event_id integer ) RETURNS boolean AS $$ DECLARE v_html_p acs_events.html_p%TYPE; BEGIN select coalesce(e.html_p, a.html_p) into v_html_p from acs_events e left join acs_activities a on (e.activity_id = a.activity_id) where e.event_id = get_html_p__event_id; return v_html_p; END; $$ LANGUAGE plpgsql; -- added select define_function_args('acs_event__get_status_summary','event_id'); -- -- procedure acs_event__get_status_summary/1 -- -- Returns status_summary or status_summary of the activity associated with the event if -- status_summary is null. -- -- @author W. Scott Meeks -- -- @param event_id id of event to get status_summary for -- -- @return The status_summary or status_summary of the activity associated with the event if status_summary is null. -- CREATE OR REPLACE FUNCTION acs_event__get_status_summary( get_status_summary__event_id integer ) RETURNS boolean AS $$ DECLARE v_status_summary acs_events.status_summary%TYPE; BEGIN select coalesce(e.status_summary, a.status_summary) into v_status_summary from acs_events e left join acs_activities a on (e.activity_id = a.activity_id) where e.event_id = get_status_summary__event_id; return v_status_summary; END; $$ LANGUAGE plpgsql; -- added select define_function_args('acs_event__timespan_set','event_id,timespan_id'); -- -- procedure acs_event__timespan_set/2 -- -- Sets the time span for an event (20.10.15) -- -- @author W. Scott Meeks -- -- @param event_id id of event to update -- @param timespan_id new time interval set -- -- @return 0 (procedure dummy) -- CREATE OR REPLACE FUNCTION acs_event__timespan_set( timespan_set__event_id integer, timespan_set__timespan_id integer ) RETURNS integer AS $$ DECLARE BEGIN update acs_events set timespan_id = timespan_set__timespan_id where event_id = timespan_set__event_id; return 0; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION acs_event__recurrence_timespan_edit ( p_event_id integer, p_start_date timestamptz, p_end_date timestamptz ) RETURNS integer AS $$ DECLARE BEGIN return acs_event__recurrence_timespan_edit ( p_event_id, p_start_date, p_end_date, 't'); END; $$ LANGUAGE plpgsql; select define_function_args('acs_event__recurrence_timespan_edit','event_id,start_date,end_date,edit_past_events_p'); CREATE OR REPLACE FUNCTION acs_event__recurrence_timespan_edit ( p_event_id integer, p_start_date timestamptz, p_end_date timestamptz, p_edit_past_events_p boolean ) RETURNS integer AS $$ DECLARE v_timespan RECORD; v_one_start_date timestamptz; v_one_end_date timestamptz; BEGIN -- get the initial offsets select start_date, end_date into v_one_start_date, v_one_end_date from time_intervals, timespans, acs_events where time_intervals.interval_id = timespans.interval_id and timespans.timespan_id = acs_events.timespan_id and event_id=p_event_id; raise notice 'v_one_start_date = %',v_one_start_date; FOR v_timespan in select * from time_intervals where interval_id in (select interval_id from timespans where timespan_id in (select timespan_id from acs_events where recurrence_id = (select recurrence_id from acs_events where event_id = p_event_id))) and (p_edit_past_events_p = 't' or start_date >= v_one_start_date) LOOP PERFORM time_interval__edit(v_timespan.interval_id, (to_char(v_timespan.start_date,'yyyy-mm-dd') || ' ' || to_char(p_start_date,'hh24:mi:ss')) :: timestamptz, (to_char(v_timespan.end_date,'yyyy-mm-dd') || ' ' || to_char(p_end_date,'hh24:mi:ss')) :: timestamptz); END LOOP; return p_event_id; END; $$ LANGUAGE plpgsql; -- added select define_function_args('acs_event__activity_set','event_id,activity_id'); -- -- procedure acs_event__activity_set/2 -- -- Sets the activity for an event (20.10.20) -- -- @author W. Scott Meeks -- -- @param event_id id of event to update -- @param timespan_id new time interval set -- -- @return 0 (procedure dummy) CREATE OR REPLACE FUNCTION acs_event__activity_set( activity_set__event_id integer, activity_set__activity_id integer ) RETURNS integer AS $$ DECLARE BEGIN update acs_events set activity_id = activity_set__activity_id where event_id = activity_set__event_id; return 0; END; $$ LANGUAGE plpgsql; -- added select define_function_args('acs_event__party_map','event_id,party_id'); -- -- procedure acs_event__party_map/2 -- -- Adds a party mapping to an event (20.10.30) -- -- @author W. Scott Meeks -- -- @param event_id event to add mapping to -- @param party_id party to add mapping for CREATE OR REPLACE FUNCTION acs_event__party_map( party_map__event_id integer, party_map__party_id integer ) RETURNS integer AS $$ DECLARE BEGIN insert into acs_event_party_map (event_id, party_id) values (party_map__event_id, party_map__party_id); return 0; END; $$ LANGUAGE plpgsql; -- added select define_function_args('acs_event__party_unmap','event_id,party_id'); -- -- procedure acs_event__party_unmap/2 -- -- Deletes a party mapping from an event (20.10.30) -- -- @author W. Scott Meeks -- -- @param event_id id of event to delete mapping from -- @param party_id id of party to delete mapping for -- -- @return 0 (procedure dummy) CREATE OR REPLACE FUNCTION acs_event__party_unmap( party_unmap__event_id integer, party_unmap__party_id integer ) RETURNS integer AS $$ DECLARE BEGIN delete from acs_event_party_map where event_id = party_unmap__event_id and party_id = party_unmap__party_id; return 0; END; $$ LANGUAGE plpgsql; -- added select define_function_args('acs_event__recurs_p','event_id'); -- -- procedure acs_event__recurs_p/1 -- -- Returns true if event recurs, false otherwise (20.50.40) -- -- @author W. Scott Meeks -- -- @param event_id id of event to check -- -- @return true if event recurs, otherwise false CREATE OR REPLACE FUNCTION acs_event__recurs_p( recurs_p__event_id integer ) RETURNS boolean AS $$ DECLARE v_result boolean; BEGIN select (case when recurrence_id is null then false else true end) into v_result from acs_events where event_id = recurs_p__event_id; return v_result; END; $$ LANGUAGE plpgsql; -- added select define_function_args('acs_event__instances_exist_p','recurrence_id'); -- -- procedure acs_event__instances_exist_p/1 -- -- Returns true if events with the given recurrence_id exist, false otherwise -- -- @author W. Scott Meeks -- -- @param recurrence_id id of recurrence to check -- -- @return true if events with the given recurrence_id exist, false otherwise CREATE OR REPLACE FUNCTION acs_event__instances_exist_p( instances_exist_p__recurrence_id integer ) RETURNS boolean AS $$ DECLARE v_result integer; BEGIN -- Only need to check if any rows exist. select count(*) into v_result from dual where exists (select recurrence_id from acs_events where recurrence_id = instances_exist_p__recurrence_id); if v_result = 0 then return false; else return true; end if; END; $$ LANGUAGE plpgsql; -- added select define_function_args('acs_event__get_value','parameter_name'); -- -- procedure acs_event__get_value/1 -- -- This function is used internally by insert_instances -- -- JS: The only time this function is used is to get the -- JS: EventFutureLimit parameter from APM. However, -- JS: the original acs-events package does not define -- JS: the EventFutureLimit parameter, so I had to create -- JS: it (in APM). -- -- @author W. Scott Meeks -- -- @param parameter_string Parameter to be extracted from acs-events package -- -- @return Value of parameter CREATE OR REPLACE FUNCTION acs_event__get_value( get_value__parameter_name varchar ) RETURNS varchar AS $$ DECLARE v_package_id apm_packages.package_id%TYPE; BEGIN select package_id into v_package_id from apm_packages where package_key = 'acs-events'; return apm__get_value(v_package_id, get_value__parameter_name); END; $$ LANGUAGE plpgsql; -- -- procedure acs_event__new_instance/2 -- -- Create a new instance of an event, with dateoffset from the start_date -- and end_date of event identified by event_id. Note that dateoffset -- is an interval, not an integer. This function is used internally by -- insert_instances. Since this function is internal, there is no need -- to overload a function that has an integer for the dateoffset. -- -- @author W. Scott Meeks -- -- @param event_id Id of event to reference -- @param date_offset Offset from reference event, in date interval -- -- @return event_id of new event created. select define_function_args('acs_event__new_instance','event_id,date_offset'); CREATE OR REPLACE FUNCTION acs_event__new_instance( new_instance__event_id integer, new_instance__date_offset interval ) RETURNS integer AS $$ DECLARE event_row acs_events%ROWTYPE; object_row acs_objects%ROWTYPE; v_event_id acs_events.event_id%TYPE; v_timespan_id acs_events.timespan_id%TYPE; BEGIN -- Get event parameters select * into event_row from acs_events where event_id = new_instance__event_id; -- Get object parameters select * into object_row from acs_objects where object_id = new_instance__event_id; -- We allow non-zero offset, so we copy v_timespan_id := timespan__copy(event_row.timespan_id, new_instance__date_offset); -- Create a new instance v_event_id := acs_event__new( null, -- event_id (default) event_row.name, -- name event_row.description, -- description event_row.html_p, -- html_p event_row.status_summary, -- status_summary v_timespan_id, -- timespan_id event_row.activity_id, -- activity_id` event_row.recurrence_id, -- recurrence_id 'acs_event', -- object_type (default) now(), -- creation_date (default) object_row.creation_user, -- creation_user object_row.creation_ip, -- creation_ip object_row.context_id, -- context_id object_row.package_id, -- package_id event_row.location, -- location event_row.related_link_url, -- related_link_url event_row.related_link_text, -- related_link_text event_row.redirect_to_rel_link_p -- redirect_to_rel_link_p ); return v_event_id; END; $$ LANGUAGE plpgsql; -- -- procedure acs_event__insert_instances/2 -- -- This is the key procedure creating recurring events. This procedure -- uses the interval set and recurrence information referenced by the event -- to insert additional information to represent the recurrences. -- Events will be added up until the earlier of recur_until and -- cutoff_date. The procedure enforces a hard internal -- limit of adding no more than 10,000 recurrences at once to reduce the -- risk of demolishing the DB because of application bugs. The date of the -- last recurrence added is marked as the db_populated_until date. -- -- The application is responsible for calling this function again if -- necessary to populate to a later date. -- -- JS: Note that the following Oracle functions do not have any equivalent -- JS: (at least in an obvious way) in Postgres: next_day, add_months, last_day. -- JS: Ports of these functions are in oracle-compat-create.sql. -- JS: -- JS: To understand the port, it is important to keep in mind the subtle but -- JS: important differences in the way Oracle and Postgres do date arithmetic. -- JS: Compatibility with Oracle requires that all integers involved in date arithmetic -- JS: be converted to Postgres day intervals, hence the typecasting. The typecasting -- JS: function to_interval (also in oracle-compat-create.sql) is simply a convenience -- JS: so that the code will not be littered by escaped quotes. -- JS: -- JS: NOTE: There seems to be some weirdness going on with recurrence -- JS: when moving from non-DST to DST dates (email me for the gory details). -- JS: Not sure if a Postgres bug or feature. -- -- @author W. Scott Meeks -- -- @param event_id The id of the event to recur. If the -- event's recurrence_id is null, nothing happens. -- @param cutoff_date Determines how far out to prepopulate the DB. -- Default is now() plus the value of the -- EventFutureLimit site parameter. -- -- @return 0 (procedure dummy) select define_function_args('acs_event__insert_instances','event_id,cutoff_date;null'); CREATE OR REPLACE FUNCTION acs_event__insert_instances( insert_instances__event_id integer, insert_instances__cutoff_date timestamptz -- default null ) RETURNS integer AS $$ DECLARE event_row acs_events%ROWTYPE; recurrence_row recurrences%ROWTYPE; v_event_id acs_events.event_id%TYPE; v_interval_name recurrence_interval_types.interval_name%TYPE; v_n_intervals recurrences.every_nth_interval%TYPE; v_days_of_week recurrences.days_of_week%TYPE; v_last_date_done timestamptz; v_stop_date timestamptz; v_start_date timestamptz; v_event_date timestamptz; v_diff integer; v_current_date timestamptz; v_last_day timestamptz; v_week_date timestamptz; v_instance_count integer; v_days_length integer; v_days_index integer; v_day_num integer; rec_execute record; v_new_current_date timestamptz; v_offset_notice interval; BEGIN -- Get event parameters select * into event_row from acs_events where event_id = insert_instances__event_id; -- Get recurrence information select * into recurrence_row from recurrences where recurrence_id = event_row.recurrence_id; -- Set cutoff date to stop populating the DB with recurrences -- EventFutureLimit is in years. (a parameter of the service) if insert_instances__cutoff_date is null then v_stop_date := add_months(now(), 12 * to_number(acs_event__get_value('EventFutureLimit'),'99999')::INT); else v_stop_date := insert_instances__cutoff_date; end if; -- Events only populated until max(cutoff_date, recur_until) -- If recur_until null, then defaults to cutoff_date if recurrence_row.recur_until < v_stop_date then v_stop_date := recurrence_row.recur_until; end if; -- Figure out the date to start from. -- JS: I do not understand why the date must be truncated to the midnight of the event date select min(start_date) into v_event_date from acs_events_dates where event_id = insert_instances__event_id; if recurrence_row.db_populated_until is null then v_start_date := v_event_date; else v_start_date := recurrence_row.db_populated_until; end if; v_current_date := v_start_date; v_last_date_done := v_start_date; v_n_intervals := recurrence_row.every_nth_interval; -- Case off of the interval_name to make code easier to read select interval_name into v_interval_name from recurrences r, recurrence_interval_types t where recurrence_id = recurrence_row.recurrence_id and r.interval_type = t.interval_type; -- Week has to be handled specially. -- Start with the beginning of the week containing the start date. if v_interval_name = 'week' then v_current_date := next_day(v_current_date - to_interval(7,'days'),'SUNDAY'); v_days_of_week := recurrence_row.days_of_week; v_days_length := char_length(v_days_of_week); end if; -- Check count to prevent runaway in case of error v_instance_count := 0; -- A feature: we only care about the date when populating the database for reccurrence. while v_instance_count < 10000 and (date_trunc('day',v_last_date_done) <= date_trunc('day',v_stop_date)) loop v_instance_count := v_instance_count + 1; -- Calculate next date based on interval type -- Add next day, skipping every v_n_intervals if v_interval_name = 'day' then v_current_date := v_current_date + to_interval(v_n_intervals,'days'); end if; -- Add a full month, skipping by v_n_intervals months if v_interval_name = 'month_by_date' then v_current_date := add_months(v_current_date, v_n_intervals); end if; -- Add days so that the next date will have the same day of the week, and week of the month if v_interval_name = 'month_by_day' then -- Find last day of month before correct month v_last_day := add_months(last_day(v_current_date), v_n_intervals - 1); -- Find correct week and go to correct day of week v_current_date := next_day(v_last_day + to_interval(7 * (to_number(to_char(v_current_date,'W'),'99')::INT - 1), 'days'), to_char(v_current_date, 'DAY')); end if; -- Add days so that the next date will have the same day of the week on the last week of the month if v_interval_name = 'last_of_month' then -- Find last day of correct month v_last_day := last_day(add_months(v_current_date, v_n_intervals)); -- Back up one week and find correct day of week v_current_date := next_day(v_last_day ::timestamp - to_interval(7,'days') :: timestamptz, to_char(v_current_date, 'DAY')); end if; -- Add a full year (12 months) If v_interval_name = 'year' then v_current_date := add_months(v_current_date, 12 * v_n_intervals); end if; -- Deal with custom function if v_interval_name = 'custom' then -- JS: Execute a dynamically created query on the fly... FOR rec_execute IN EXECUTE 'select ' || recurrence_row.custom_func || '(' || quote_literal(v_current_date) || ',' || v_n_intervals || ') as current_date' LOOP v_current_date := rec_execute.current_date; END LOOP; end if; -- Check to make sure we are not going past Trunc because dates are not integral exit when date_trunc('day',v_current_date) > date_trunc('day',v_stop_date); -- Have to handle week specially if v_interval_name = 'week' then -- loop over days_of_week extracting each day number -- add day number and insert v_days_index := 1; v_week_date := v_current_date; while v_days_index <= v_days_length loop v_day_num := SUBSTR(v_days_of_week, v_days_index, 1); v_week_date := (v_current_date ::timestamp + to_interval(v_day_num,'days')) :: timestamptz; if date_trunc('day',v_week_date) > date_trunc('day',v_start_date) and date_trunc('day',v_week_date) <= date_trunc('day',v_stop_date) then -- This is where we add the event v_event_id := acs_event__new_instance( insert_instances__event_id, -- event_id date_trunc('day',v_week_date :: timestamp) - date_trunc('day',v_event_date :: timestamp) -- offset ); v_last_date_done := v_week_date; else if date_trunc('day',v_week_date) > date_trunc('day',v_stop_date) then -- Gone too far exit; end if; end if; v_days_index := v_days_index + 2; end loop; -- Now move to next week with repeats. v_current_date := (v_current_date :: timestamp + to_interval(7 * v_n_intervals,'days')) :: timestamptz; else -- All other interval types -- This is where we add the event v_event_id := acs_event__new_instance( insert_instances__event_id, -- event_id date_trunc('day',v_current_date ::timestamp) - date_trunc('day',v_event_date ::timestamp) -- offset ); v_last_date_done := v_current_date; end if; end loop; update recurrences set db_populated_until = v_last_date_done where recurrence_id = recurrence_row.recurrence_id; return 0; END; $$ LANGUAGE plpgsql; -- -- procedure acs_event__shift/3 -- -- Shifts the timespan of an event by the given offsets. -- -- @author W. Scott Meeks -- -- @param event_id Event to shift. -- @param start_offset_interval Adds this date interval to the -- start_dates of the timespan of the event. -- No effect on any null start_date. -- @param end_offset_interval Adds this date interval to the -- end_dates of the timespan of the event. -- No effect on any null end_date. -- -- @return 0 (procedure dummy) CREATE OR REPLACE FUNCTION acs_event__shift( shift__event_id integer, -- default null shift__start_offset_interval interval, -- default 0 shift__end_offset_interval interval -- default 0 ) RETURNS integer AS $$ DECLARE rec_events record; BEGIN -- update acs_events_dates -- set start_date = start_date + shift__start_offset_interval, -- end_date = end_date + shift__end_offset_interval -- where event_id = shift__event_id; -- Can not update view, so we do it the hard way -- (as if we make the rule anyways) for rec_events in select t.* from acs_events e, timespans s, time_intervals t where e.event_id = shift__event_id and e.timespan_id = s.timespan_id and s.interval_id = t.interval_id loop update time_intervals set start_date = start_date + shift__start_offset_interval, end_date = end_date + shift__end_offset_interval where interval_id = rec_events.interval_id; end loop; return 0; END; $$ LANGUAGE plpgsql; -- -- procedure acs_event__shift/3 -- -- Shifts the timespan of an event by the given offsets. -- -- Overloaded function to make above compatible with Oracle behavior -- -- @param event_id Event to shift. -- @param start_offset Adds this number of days to the -- start_dates of the timespan of the event. -- No effect on any null start_date. -- @param end_offset Adds this number of days to the -- end_dates of the timespan of the event. -- No effect on any null end_date. -- -- @return 0 (procedure dummy) select define_function_args('acs_event__shift','event_id;null,start_offset;0,end_offset;0'); CREATE OR REPLACE FUNCTION acs_event__shift( shift__event_id integer, -- default null shift__start_offset integer, -- default 0 shift__end_offset integer -- default 0 ) RETURNS integer AS $$ DECLARE BEGIN return acs_event__shift ( shift__event_id, to_interval(shift__start_offset,'days'), to_interval(shift__end_offset,'days') ); END; $$ LANGUAGE plpgsql; -- -- procedure acs_event__shift_all/3 -- -- Shifts the timespan of all instances of a recurring event -- by the given offsets. -- -- @author W. Scott Meeks -- -- @param event_id All events with the same -- recurrence_id as this one will be shifted. -- @param start_offset_inverval Adds this date interval to the -- start_dates of the timespan of the event -- instances. No effect on any null start_date. -- @param end_offset_inverval Adds this date interval to the -- end_dates of the timespan of the event -- instances. No effect on any null end_date. -- -- @return 0 (procedure dummy) CREATE OR REPLACE FUNCTION acs_event__shift_all( shift_all__event_id integer, -- default null shift_all__start_offset_inverval interval, -- default 0 shift_all__end_offset_inverval interval -- default 0 ) RETURNS integer AS $$ DECLARE rec_events record; BEGIN -- update acs_events_dates -- set start_date = start_date + shift_all__start_offset_inverval, -- end_date = end_date + shift_all__end_offset_inverval -- where recurrence_id = (select recurrence_id -- from acs_events -- where event_id = shift_all__event_id); -- Can not update views for rec_events in select * from acs_events_dates where recurrence_id = (select recurrence_id from acs_events where event_id = shift_all__event_id) loop PERFORM acs_event__shift( rec_events.event_id, shift_all__start_offset_inverval, shift_all__end_offset_inverval ); end loop; return 0; END; $$ LANGUAGE plpgsql; -- -- procedure acs_event__shift_all/3 -- -- Shifts the timespan of all instances of a recurring event -- by the given offsets. -- -- JS: Overloaded function to make above compatible with Oracle behavior -- -- -- @param event_id All events with the same -- recurrence_id as this one will be shifted. -- @param start_offset Adds this number of days to the -- start_dates of the timespan of the event -- instances. No effect on any null start_date. -- @param end_offset Adds this number of days to the -- end_dates of the timespan of the event -- instances. No effect on any null end_date. -- -- @return 0 (procedure dummy) select define_function_args('acs_event__shift_all','event_id;null,start_offset;0,end_offset;0'); CREATE OR REPLACE FUNCTION acs_event__shift_all( shift_all__event_id integer, -- default null shift_all__start_offset integer, -- default 0 shift_all__end_offset integer -- default 0 ) RETURNS integer AS $$ DECLARE BEGIN return acs_event__shift_all ( shift_all__event_id, to_interval(shift_all__start_offset,'days'), to_interval(shift_all__end_offset,'days') ); END; $$ LANGUAGE plpgsql;