Index: openacs-4/packages/acs-events/sql/postgresql/acs-events-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/postgresql/acs-events-create.sql,v diff -u -N -r1.18 -r1.19 --- openacs-4/packages/acs-events/sql/postgresql/acs-events-create.sql 8 Nov 2010 13:10:35 -0000 1.18 +++ openacs-4/packages/acs-events/sql/postgresql/acs-events-create.sql 30 Mar 2013 13:00:29 -0000 1.19 @@ -12,181 +12,188 @@ \i recurrence-create.sql -create or replace function inline_0 () -returns integer as ' -declare + + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( + +) RETURNS integer AS $$ +DECLARE attr_id acs_attributes.attribute_id%TYPE; -begin +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 + '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) + '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 + '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) + '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 + '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) + '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 + '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) + '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 + '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) + '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 + '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) + '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 + '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) + 'type_specific', -- storage (default) + 'f' -- static_p (default) ); attr_id := acs_attribute__create_attribute ( - ''acs_event'', -- object_type - ''html_p'', -- attribute_name - ''string'', -- datatype - ''HTML?'', -- pretty_name + 'acs_event', -- object_type + 'html_p', -- attribute_name + 'string', -- 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) + '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 + '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) + '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 + '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) + '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 - ''string'', -- datatype - ''Redirect to Related Link?'', -- pretty_name + 'acs_event', -- object_type + 'redirect_to_rel_link_p', -- attribute_name + 'string', -- 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) + 'type_specific', -- storage (default) + 'f' -- static_p (default) ); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- Do the transaction, then clean up select inline_0 (); @@ -417,7 +424,7 @@ -- backwards compatible 13 param version -create or replace function acs_event__new ( +CREATE OR REPLACE FUNCTION acs_event__new ( integer, varchar, text, @@ -431,75 +438,69 @@ integer, varchar, integer -) -returns integer as ' -begin +) 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'; +END; +$$ LANGUAGE plpgsql; -create or replace function acs_event__new ( - -- - -- 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 - -- - -- @return The id of the new event. - -- - integer, -- acs_events.event_id%TYPE, - varchar, -- acs_events.name%TYPE, - text, -- acs_events.description%TYPE, - boolean, -- acs_events.html_p%TYPE, - text, -- acs_events.status_summary%TYPE, - integer, -- acs_events.timespan_id%TYPE, - integer, -- acs_events.activity_id%TYPE, - integer, -- acs_events.recurrence_id%TYPE, - varchar, -- acs_object_types.object_type%TYPE, - timestamptz, -- acs_objects.creation_date%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar, -- acs_objects.creation_ip%TYPE, - integer, -- acs_objects.context_id%TYPE, - integer -- acs_objects.package_id%TYPE, -) -returns integer as ' -- acs_events.event_id%TYPE -declare - new__event_id alias for $1; -- default null, - new__name alias for $2; -- default null, - new__description alias for $3; -- default null, - new__html_p alias for $4; -- default null - new__status_summary alias for $5; -- default null - new__timespan_id alias for $6; -- default null, - new__activity_id alias for $7; -- default null, - new__recurrence_id alias for $8; -- default null, - new__object_type alias for $9; -- default ''acs_event'', - new__creation_date alias for $10; -- default now(), - new__creation_user alias for $11; -- default null, - new__creation_ip alias for $12; -- default null, - new__context_id alias for $13; -- default null - new__package_id alias for $14; -- default null + + +-- added +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'); + +-- +-- procedure acs_event__new/14 + + -- 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 + -- + -- @return The id of the new event. + +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 + +) RETURNS integer AS $$ + -- acs_events.event_id%TYPE +DECLARE v_event_id acs_events.event_id%TYPE; -begin +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 + 't', -- security_inherit_p new__name, -- title new__package_id -- package_id ); @@ -512,11 +513,18 @@ return v_event_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function acs_event__delete ( - -- + + +-- added +select define_function_args('acs_event__delete','event_id'); + +-- +-- 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 @@ -527,13 +535,13 @@ -- @param event_id id of event to delete -- -- @return 0 (procedure dummy) - -- - integer -- acs_events.event_id%TYPE -) returns integer as ' -declare - delete__event_id alias for $1; + +CREATE OR REPLACE FUNCTION acs_event__delete( + delete__event_id integer +) RETURNS integer AS $$ +DECLARE v_recurrence_id acs_events.recurrence_id%TYPE; -begin +BEGIN select recurrence_id into v_recurrence_id from acs_events where event_id = delete__event_id; @@ -548,26 +556,33 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function acs_event__delete_all_recurrences ( - -- - -- 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) - -- - integer -- recurrences.recurrence_id%TYPE default null -) -returns integer as ' -declare - delete_all_recurrences__recurrence_id alias for $1; -- default null + + +-- 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 +BEGIN if delete_all_recurrences__recurrence_id is not null then for rec_event in select event_id @@ -580,28 +595,38 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function acs_event__delete_all ( - -- - -- 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) - -- - integer -- acs_events.event_id%TYPE -) -returns integer as ' -declare - delete_all__event_id alias for $1; + + +-- 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 +BEGIN select recurrence_id into v_recurrence_id from acs_events @@ -611,28 +636,38 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function acs_event__get_name ( - -- - -- 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. - -- - integer -- acs_events.event_id%TYPE -) -returns varchar as ' -- acs_events.name%TYPE -declare - get_name__event_id alias for $1; + + +-- 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 +BEGIN select coalesce(e.name, a.name) into v_name from acs_events e @@ -642,28 +677,35 @@ return v_name; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function acs_event__get_description ( - -- - -- 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. - -- - integer -- acs_events.event_id%TYPE -) -returns text as ' -- acs_events.description%TYPE -declare - get_description__event_id alias for $1; + + +-- 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 +BEGIN select coalesce(e.description, a.description) into v_description from acs_events e @@ -673,27 +715,35 @@ return v_description; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function acs_event__get_html_p ( - -- - -- 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. - -- - integer -- acs_events.event_id%TYPE -) -returns boolean as ' -- acs_events.html_p%TYPE -declare - get_html_p__event_id alias for $1; -- in acs_events.event_id%TYPE + + +-- 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 +BEGIN select coalesce(e.html_p, a.html_p) into v_html_p from acs_events e left join acs_activities a @@ -702,26 +752,32 @@ return v_html_p; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function acs_event__get_status_summary ( - -- - -- 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. - -- - integer -- acs_events.event_id%TYPE -) -returns boolean as ' -declare - get_status_summary__event_id alias for $1; -- acs_events.event_id%TYPE + + +-- 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 +BEGIN select coalesce(e.status_summary, a.status_summary) into v_status_summary from acs_events e left join acs_activities a @@ -730,64 +786,66 @@ return v_status_summary; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function acs_event__timespan_set ( - -- - -- 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) - -- - integer, -- acs_events.event_id%TYPE, - integer -- timespans.timespan_id%TYPE -) -returns integer as ' -declare - timespan_set__event_id alias for $1; - timespan_set__timespan_id alias for $2; -begin + + +-- 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'; +END; +$$ LANGUAGE plpgsql; -create or replace function acs_event__recurrence_timespan_edit ( - integer, - timestamptz, - timestamptz -) returns integer as ' +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 - p_event_id alias for $1; - p_start_date alias for $2; - p_end_date alias for $3; BEGIN return acs_event__recurrence_timespan_edit ( p_event_id, p_start_date, p_end_date, - ''t''); -END;' language 'plpgsql'; + 't'); +END; +$$ LANGUAGE plpgsql; -create or replace function acs_event__recurrence_timespan_edit ( - integer, - timestamptz, - timestamptz, - boolean -) returns integer as ' +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 - p_event_id alias for $1; - p_start_date alias for $2; - p_end_date alias for $3; - p_edit_past_events_p alias for $4; v_timespan RECORD; v_one_start_date timestamptz; v_one_end_date timestamptz; @@ -802,7 +860,7 @@ 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; +raise notice 'v_one_start_date = %',v_one_start_date; FOR v_timespan in select * from time_intervals @@ -812,119 +870,140 @@ 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) + 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); + (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'; +$$ LANGUAGE plpgsql; -create or replace function acs_event__activity_set ( - -- - -- 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) - -- - integer, -- acs_events.event_id%TYPE, - integer -- acs_activities.activity_id%TYPE -) -returns integer as ' -declare - activity_set__event_id alias for $1; - activity_set__activity_id alias for $2; -begin + + +-- 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'; +END; +$$ LANGUAGE plpgsql; -create or replace function acs_event__party_map ( - -- - -- 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 - -- - -- @return 0 (procedure dummy) - -- - integer, -- acs_events.event_id%TYPE, - integer -- parties.party_id%TYPE -) -returns integer as ' -declare - party_map__event_id alias for $1; - party_map__party_id alias for $2; -begin + + +-- 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'; +END; +$$ LANGUAGE plpgsql; -create or replace function acs_event__party_unmap ( - -- - -- 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) - -- - integer, -- acs_events.event_id%TYPE, - integer -- parties.party_id%TYPE -) -returns integer as ' -declare - party_unmap__event_id alias for $1; - party_unmap__party_id alias for $2; -begin + + +-- 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'; +END; +$$ LANGUAGE plpgsql; -create or replace function acs_event__recurs_p ( - -- - -- 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 - -- - integer -- in acs_events.event_id%TYPE -) -returns boolean as ' -declare - recurs_p__event_id alias for $1; + + +-- 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 +BEGIN select (case when recurrence_id is null then false else true @@ -934,26 +1013,33 @@ return v_result; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function acs_event__instances_exist_p ( - -- - -- 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 - -- - integer -- acs_events.recurrence_id%TYPE -) -returns boolean as ' -declare - instances_exist_p__recurrence_id alias for $1; + + +-- 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 +BEGIN -- Only need to check if any rows exist. select count(*) into v_result from dual @@ -967,68 +1053,79 @@ return true; end if; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function acs_event__get_value ( - -- - -- 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 - -- - varchar -- in apm_parameters.parameter_name%TYPE -) -returns varchar as ' -- return apm_parameter_values.attr_value%TYPE -declare - get_value__parameter_name alias for $1; + + +-- 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 +BEGIN select package_id into v_package_id from apm_packages - where package_key = ''acs-events''; + where package_key = 'acs-events'; return apm__get_value(v_package_id, get_value__parameter_name); +END; +$$ LANGUAGE plpgsql; -end;' language 'plpgsql'; -create or replace function acs_event__new_instance ( - -- - -- 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. - -- - integer, -- acs_events.event_id%TYPE, - interval -) -returns integer as ' -- acs_events.event_id%TYPE -declare - new_instance__event_id alias for $1; - new_instance__date_offset alias for $2; + +-- added +select define_function_args('acs_event__new_instance','event_id,date_offset'); + +-- +-- 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. + +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 - +BEGIN -- Get event parameters select * into event_row from acs_events @@ -1052,7 +1149,7 @@ v_timespan_id, -- timespan_id event_row.activity_id, -- activity_id` event_row.recurrence_id, -- recurrence_id - ''acs_event'', -- object_type (default) + 'acs_event', -- object_type (default) now(), -- creation_date (default) object_row.creation_user, -- creation_user object_row.creation_ip, -- creation_ip @@ -1061,57 +1158,62 @@ ); return v_event_id; +END; +$$ LANGUAGE plpgsql; -end;' language 'plpgsql'; -create or replace function acs_event__insert_instances ( - -- - -- 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) - -- - integer, -- acs_events.event_id%TYPE, - timestamptz -- default null -) -returns integer as ' -declare - insert_instances__event_id alias for $1; - insert_instances__cutoff_date alias for $2; -- default null + +-- added +select define_function_args('acs_event__insert_instances','event_id,cutoff_date;null'); + +-- +-- 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) + +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; @@ -1133,8 +1235,7 @@ rec_execute record; v_new_current_date timestamptz; v_offset_notice interval; -begin - +BEGIN -- Get event parameters select * into event_row from acs_events @@ -1149,7 +1250,7 @@ -- 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); + 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; @@ -1186,9 +1287,9 @@ -- Week has to be handled specially. -- Start with the beginning of the week containing the start date. - if v_interval_name = ''week'' + if v_interval_name = 'week' then - v_current_date := next_day(v_current_date - to_interval(7,''days''),''SUNDAY''); + 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; @@ -1197,84 +1298,84 @@ 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)) + 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'' + if v_interval_name = 'day' then - v_current_date := v_current_date + to_interval(v_n_intervals,''days''); + 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'' + 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 + 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'')); + 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 + 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'')); + 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 + 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 + 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'' + 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); + 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 + 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 + 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 + 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) + else if date_trunc('day',v_week_date) > date_trunc('day',v_stop_date) then -- Gone too far exit; @@ -1287,13 +1388,13 @@ end loop; -- Now move to next week with repeats. - v_current_date := (v_current_date :: timestamp + to_interval(7 * v_n_intervals,''days'')) :: timestamptz; + 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 + date_trunc('day',v_current_date ::timestamp) - date_trunc('day',v_event_date ::timestamp) -- offset ); v_last_date_done := v_current_date; end if; @@ -1304,37 +1405,42 @@ where recurrence_id = recurrence_row.recurrence_id; return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function acs_event__shift ( - -- - -- Shifts the timespan of an event by the given offsets. - -- - -- @author W. Scott Meeks - -- - -- @param event_id Event to shift. - -- @param start_offset Adds this date interval to the - -- start_dates of the timespan of the event. - -- No effect on any null start_date. - -- @param end_offset 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) - -- - integer, -- acs_events.event_id%TYPE default null, - interval, - interval -) -returns integer as ' -declare - shift__event_id alias for $1; -- default null, - shift__start_offset alias for $2; -- default 0, - shift__end_offset alias for $3; -- default 0 + + +-- added +select define_function_args('acs_event__shift','event_id;null,start_offset;0,end_offset;0'); + +-- +-- 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 Adds this date interval to the + -- start_dates of the timespan of the event. + -- No effect on any null start_date. + -- @param end_offset 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, -- default 0 + shift__end_offset interval -- default 0 + +) RETURNS integer AS $$ +DECLARE rec_events record; -begin +BEGIN -- update acs_events_dates -- set start_date = start_date + shift__start_offset, @@ -1358,76 +1464,83 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function acs_event__shift ( - -- - -- Shifts the timespan of an event by the given offsets. - -- - -- JS: Overloaded function to make above compatible with Oracle behavior - -- JS: when an integer (for number of days) is supplied as a parameter. - -- - -- - -- @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) - -- - integer, -- acs_events.event_id%TYPE default null, - integer, - integer -) -returns integer as ' -declare - shift__event_id alias for $1; -- default null, - shift__start_offset alias for $2; -- default 0, - shift__end_offset alias for $3; -- default 0 -begin + + +-- +-- procedure acs_event__shift/3 +-- + -- Shifts the timespan of an event by the given offsets. + -- + -- JS: Overloaded function to make above compatible with Oracle behavior + -- JS: when an integer (for number of days) is supplied as a parameter. + -- + -- + -- @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) + +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'') + to_interval(shift__start_offset,'days'), + to_interval(shift__end_offset,'days') ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function acs_event__shift_all ( - -- - -- 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 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 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) - -- - integer, -- in acs_events.event_id%TYPE default null, - interval, - interval -) -returns integer as ' -declare - shift_all__event_id alias for $1; -- default null, - shift_all__start_offset alias for $2; -- default 0, - shift_all__end_offset alias for $3; -- default 0 + + +-- added +select define_function_args('acs_event__shift_all','event_id;null,start_offset;0,end_offset;0'); + +-- +-- 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 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 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 interval, -- default 0 + shift_all__end_offset interval -- default 0 + +) RETURNS integer AS $$ +DECLARE rec_events record; -begin +BEGIN -- update acs_events_dates @@ -1455,46 +1568,49 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- end shift_all; -create or replace function acs_event__shift_all ( - -- - -- Shifts the timespan of all instances of a recurring event - -- by the given offsets. - -- - -- JS: Overloaded function to make above compatible with Oracle behavior - -- JS: when an integer (for number of days) is supplied as a parameter. - -- - -- - -- @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) - -- - integer, -- acs_events.event_id%TYPE default null, - integer, - integer -) -returns integer as ' -declare - shift_all__event_id alias for $1; -- default null, - shift_all__start_offset alias for $2; -- default 0, - shift_all__end_offset alias for $3; -- default 0 -begin + + +-- +-- 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 + -- JS: when an integer (for number of days) is supplied as a parameter. + -- + -- + -- @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) + +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'') + to_interval(shift_all__start_offset,'days'), + to_interval(shift_all__end_offset,'days') ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/acs-events/sql/postgresql/activity-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/postgresql/activity-create.sql,v diff -u -N -r1.6 -r1.7 --- openacs-4/packages/acs-events/sql/postgresql/activity-create.sql 12 Mar 2004 18:48:48 -0000 1.6 +++ openacs-4/packages/acs-events/sql/postgresql/activity-create.sql 30 Mar 2013 13:00:29 -0000 1.7 @@ -7,95 +7,102 @@ -- -- $Id$ -create function inline_0 () -returns integer as ' -declare + + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( + +) RETURNS integer AS $$ +DECLARE attr_id acs_attributes.attribute_id%TYPE; -begin +BEGIN -- Event object PERFORM acs_object_type__create_type ( - ''acs_activity'', -- object_type - ''Activity'', -- pretty_name - ''Activities'', -- pretty_plural - ''acs_object'', -- supertype - ''ACS_ACTIVITIES'', -- table_name - ''ACTIVITY_ID'', -- id_column - ''null'', -- package_name (default) - ''f'', -- abstract_p (default) - null, -- type_extension_table (default) - null -- name_method (default) + 'acs_activity', -- object_type + 'Activity', -- pretty_name + 'Activities', -- pretty_plural + 'acs_object', -- supertype + 'ACS_ACTIVITIES', -- table_name + 'ACTIVITY_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_activity'', -- object_type - ''name'', -- attribute_name - ''string'', -- data_type - ''Name'', -- pretty_name - ''Names'', -- pretty_plural + 'acs_activity', -- object_type + 'name', -- attribute_name + 'string', -- data_type + '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) + 'type_specific', -- storage (default) + 'f' -- static_p (default) ); attr_id := acs_attribute__create_attribute ( - ''acs_activity'', -- object_type - ''description'', -- attribute_name - ''string'', -- data_type - ''Description'', -- pretty_name - ''Descriptions'', -- pretty_plural + 'acs_activity', -- object_type + 'description', -- attribute_name + 'string', -- data_type + '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) + 'type_specific', -- storage (default) + 'f' -- static_p (default) ); attr_id := acs_attribute__create_attribute ( - ''acs_activity'', -- object_type - ''html_p'', -- attribute_name - ''string'', -- data_type - ''HTML?'', -- pretty_name - ''HTML?'', -- pretty_plural + 'acs_activity', -- object_type + 'html_p', -- attribute_name + 'string', -- data_type + 'HTML?', -- pretty_name + 'HTML?', -- pretty_plural null, -- table_name (default) null, -- column_name (default) - null, -- default_value (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) + 'type_specific', -- storage (default) + 'f' -- static_p (default) ); attr_id := acs_attribute__create_attribute ( - ''acs_activity'', -- object_type - ''status_summary'', -- attribute_name - ''string'', -- data_type - ''Status Summary'', -- pretty_name - ''Status Summaries'', -- pretty_plural + 'acs_activity', -- object_type + 'status_summary', -- attribute_name + 'string', -- data_type + '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) + 'type_specific', -- storage (default) + 'f' -- static_p (default) ); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select inline_0 (); drop function inline_0 (); @@ -148,58 +155,56 @@ -- object_unmap (object_id) -create function acs_activity__new ( - -- - -- Create a new activity - -- - -- @author W. Scott Meeks - -- - -- @param activity_id Id to use for new activity - -- @param name Name of the activity - -- @param description Description of the activity - -- @param html_p Is the description HTML? - -- @param status_summary Additional status note (optional) - -- @param object_type 'acs_activity' - -- @param creation_date default now() - -- @param creation_user acs_object param - -- @param creation_ip acs_object param - -- @param context_id acs_object param - -- - -- @return The id of the new activity. - -- - integer, -- in acs_activities.activity_id%TYPE - varchar, -- in acs_activities.name%TYPE, - text, -- in acs_activities.description%TYPE - boolean, -- in acs_activities.html_p%TYPE - text, -- in acs_activities.status_summary%TYPE - varchar, -- in acs_object_types.object_type%TYPE - timestamptz, -- in acs_objects.creation_date%TYPE - integer, -- in acs_objects.creation_user%TYPE - varchar, -- in acs_objects.creation_ip%TYPE - integer -- in acs_objects.context_id%TYPE -) -returns integer as ' -- return acs_activities.activity_id%TYPE -declare - new__activity_id alias for $1; -- default null, - new__name alias for $2; - new__description alias for $3; -- default null, - new__html_p alias for $4; -- default ''f'', - new__status_summary alias for $5; -- default null, - new__object_type alias for $6; -- default ''acs_activity'' - new__creation_date alias for $7; -- default now(), - new__creation_user alias for $8; -- default null, - new__creation_ip alias for $9; -- default null, - new__context_id alias for $10; -- default null + + +-- added +select define_function_args('acs_activity__new','activity_id;null,name,description;null,html_p;f,status_summary;null,object_type;acs_activity,creation_date;now(),creation_user;null,creation_ip;null,context_id;null'); + +-- +-- procedure acs_activity__new/10 +-- + -- + -- Create a new activity + -- + -- @author W. Scott Meeks + -- + -- @param activity_id Id to use for new activity + -- @param name Name of the activity + -- @param description Description of the activity + -- @param html_p Is the description HTML? + -- @param status_summary Additional status note (optional) + -- @param object_type 'acs_activity' + -- @param creation_date default now() + -- @param creation_user acs_object param + -- @param creation_ip acs_object param + -- @param context_id acs_object param + -- + -- @return The id of the new activity. + +CREATE OR REPLACE FUNCTION acs_activity__new( + new__activity_id integer, -- default null, + new__name varchar, + new__description text, -- default null, + new__html_p boolean, -- default 'f', + new__status_summary text, -- default null, + new__object_type varchar, -- default 'acs_activity' + new__creation_date timestamptz, -- default now(), + new__creation_user integer, -- default null, + new__creation_ip varchar, -- default null, + new__context_id integer -- default null + +) RETURNS integer AS $$ +DECLARE v_activity_id acs_activities.activity_id%TYPE; -begin +BEGIN v_activity_id := acs_object__new( new__activity_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 + 't', -- security_inherit_p new__name, -- title null -- package_id ); @@ -211,91 +216,110 @@ return v_activity_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function acs_activity__delete ( - -- - -- Deletes an activity - -- - -- @author W. Scott Meeks - -- - -- @param activity_id Id of activity to delete - -- - -- @return 0 (procedure dummy) - -- - integer -- in acs_activities.activity_id%TYPE -) -returns integer as ' -declare - delete__activity_id alias for $1; -begin + +-- added +select define_function_args('acs_activity__delete','activity_id'); + +-- +-- procedure acs_activity__delete/1 +-- + -- Deletes an activity + -- + -- @author W. Scott Meeks + -- + -- @param activity_id Id of activity to delete + -- + -- @return 0 (procedure dummy) + -- + +CREATE OR REPLACE FUNCTION acs_activity__delete( + delete__activity_id integer + +) RETURNS integer AS $$ +DECLARE +BEGIN -- Cascade will cause delete from acs_activities -- and acs_activity_object_map PERFORM acs_object__delete(delete__activity_id); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function acs_activity__name ( - -- - -- Get name of this activity - -- - -- @author gjin@arsdigita.com - -- - -- @param activity_id - -- - -- @return Name of activity - -- - integer -- acs_activities.activity_id%TYPE -) -returns varchar as ' -- acs_activities.name%TYPE -declare - name__activity_id alias for $1; + + +-- added +select define_function_args('acs_activity__name','activity_id'); + +-- +-- procedure acs_activity__name/1 +-- + -- + -- Get name of this activity + -- + -- @author gjin@arsdigita.com + -- + -- @param activity_id + -- + -- @return Name of activity + -- + +CREATE OR REPLACE FUNCTION acs_activity__name( + name__activity_id integer + +) RETURNS varchar AS $$ +DECLARE v_activity_name acs_activities.name%TYPE; -begin +BEGIN select name into v_activity_name from acs_activities where activity_id = name__activity_id; return v_activity_name; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function acs_activity__edit ( - -- - -- Update the name or description of an activity - -- - -- @author W. Scott Meeks - -- - -- @param activity_id activity to update - -- @param name optional New name for this activity - -- @param description optional New description for this activity - -- @param html_p optional New value of html_p for this activity - -- @param status_summary optional New value of status_summary for this activity - -- - -- @return 0 (procedure dummy) - -- - integer, -- acs_activities.activity_id%TYPE, - varchar, -- acs_activities.name%TYPE default null, - text, -- acs_activities.description%TYPE default null, - boolean, -- acs_activities.html_p%TYPE default null - text -- acs_activities.status_summary%TYPE default null, -) returns integer as ' -declare - edit__activity_id alias for $1; - edit__name alias for $2; -- default null, - edit__description alias for $3; -- default null, - edit__html_p alias for $4; -- default null - edit__status_summary alias for $5; -- default null -begin + +-- added +select define_function_args('acs_activity__edit','activity_id,name;null,description;null,html_p;null,status_summary;null'); + +-- +-- procedure acs_activity__edit/5 +-- + -- Update the name or description of an activity + -- + -- @author W. Scott Meeks + -- + -- @param activity_id activity to update + -- @param name optional New name for this activity + -- @param description optional New description for this activity + -- @param html_p optional New value of html_p for this activity + -- @param status_summary optional New value of status_summary for this activity + -- + -- @return 0 (procedure dummy) + -- +CREATE OR REPLACE FUNCTION acs_activity__edit( + edit__activity_id integer, + edit__name varchar, -- default null, + edit__description text, -- default null, + edit__html_p boolean, -- default null + edit__status_summary text -- default null + +) RETURNS integer AS $$ +DECLARE +BEGIN update acs_activities set name = coalesce(edit__name, name), description = coalesce(edit__description, description), @@ -309,66 +333,79 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function acs_activity__object_map ( - -- - -- Adds an object mapping to an activity - -- - -- @author W. Scott Meeks - -- - -- @param activity_id id of activity to add mapping to - -- @param object_id id of object to add mapping for - -- - -- @return 0 (procedure dummy) - -- - integer, -- acs_activities.activity_id%TYPE, - integer -- acs_objects.object_id%TYPE -) -returns integer as ' -declare - object_map__activity_id alias for $1; - object_map__object_id alias for $2; -begin +-- added +select define_function_args('acs_activity__object_map','activity_id,object_id'); + +-- +-- procedure acs_activity__object_map/2 +-- + -- Adds an object mapping to an activity + -- + -- @author W. Scott Meeks + -- + -- @param activity_id id of activity to add mapping to + -- @param object_id id of object to add mapping for + -- + -- @return 0 (procedure dummy) + -- + +CREATE OR REPLACE FUNCTION acs_activity__object_map( + object_map__activity_id integer, + object_map__object_id integer + +) RETURNS integer AS $$ +DECLARE +BEGIN insert into acs_activity_object_map (activity_id, object_id) values (object_map__activity_id, object_map__object_id); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function acs_activity__object_unmap ( - -- - -- Removes an object mapping to an activity - -- - -- @author W. Scott Meeks - -- - -- @param activity_id id of activity to add mapping to - -- @param object_id id of object to add mapping for - -- - -- @return 0 (procedure dummy) - -- - integer, -- acs_activities.activity_id%TYPE, - integer -- acs_objects.object_id%TYPE -) -returns integer as ' -declare - object_unmap__activity_id alias for $1; - object_unmap__object_id alias for $2; -begin + +-- added +select define_function_args('acs_activity__object_unmap','activity_id,object_id'); + +-- +-- procedure acs_activity__object_unmap/2 +-- + -- + -- Removes an object mapping to an activity + -- + -- @author W. Scott Meeks + -- + -- @param activity_id id of activity to add mapping to + -- @param object_id id of object to add mapping for + -- + -- @return 0 (procedure dummy) + -- +CREATE OR REPLACE FUNCTION acs_activity__object_unmap( + object_unmap__activity_id integer, + object_unmap__object_id integer + +) RETURNS integer AS $$ +DECLARE +BEGIN + delete from acs_activity_object_map where activity_id = object_unmap__activity_id and object_id = object_unmap__object_id; return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/acs-events/sql/postgresql/oracle-compat-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/postgresql/oracle-compat-create.sql,v diff -u -N -r1.4 -r1.5 --- openacs-4/packages/acs-events/sql/postgresql/oracle-compat-create.sql 17 May 2003 09:46:57 -0000 1.4 +++ openacs-4/packages/acs-events/sql/postgresql/oracle-compat-create.sql 30 Mar 2013 13:00:29 -0000 1.5 @@ -7,116 +7,139 @@ -- -- $Id$ -create function dow_to_int ( - -- - -- Convert string to day of the week - -- - -- Note that the output of extract(dow from timestamp) and to_char(timestamp,'D') - -- are different! to_char is more consistent with Oracle, so we only use to_char. - -- - -- @author jowell@jsabino.com - -- - -- @param weekday Day of the week string to be converted to Postgres int representation - -- - -- @return integer corresponding to Postgres representation of day of the week (Sunday = 0) - -- - varchar -) -returns integer as ' -declare - dow_to_int__weekday alias for $1; + + +-- added +select define_function_args('dow_to_int','weekday'); + +-- +-- procedure dow_to_int/1 +-- + -- + -- Convert string to day of the week + -- + -- Note that the output of extract(dow from timestamp) and to_char(timestamp,'D') + -- are different! to_char is more consistent with Oracle, so we only use to_char. + -- + -- @author jowell@jsabino.com + -- + -- @param weekday Day of the week string to be converted to Postgres int representation + -- + -- @return integer corresponding to Postgres representation of day of the week (Sunday = 0) + -- + +CREATE OR REPLACE FUNCTION dow_to_int( + dow_to_int__weekday varchar + +) RETURNS integer AS $$ +DECLARE v_dow integer; -begin +BEGIN -- Brute force (what can I say?). select (case trim(upper(dow_to_int__weekday)) - when ''SUNDAY'' then 1 - when ''SUN'' then 1 - when ''MONDAY'' then 2 - when ''MON'' then 2 - when ''TUESDAY'' then 3 - when ''TUES'' then 3 - when ''TUE'' then 3 - when ''WEDNESDAY'' then 4 - when ''WED'' then 4 - when ''WEDS'' then 4 - when ''THURSDAY'' then 5 - when ''THURS'' then 5 - when ''THUR'' then 5 - when ''THU'' then 5 - when ''FRIDAY'' then 6 - when ''FRI'' then 6 - when ''SATURDAY'' then 7 - when ''SAT'' then 7 + when 'SUNDAY' then 1 + when 'SUN' then 1 + when 'MONDAY' then 2 + when 'MON' then 2 + when 'TUESDAY' then 3 + when 'TUES' then 3 + when 'TUE' then 3 + when 'WEDNESDAY' then 4 + when 'WED' then 4 + when 'WEDS' then 4 + when 'THURSDAY' then 5 + when 'THURS' then 5 + when 'THUR' then 5 + when 'THU' then 5 + when 'FRIDAY' then 6 + when 'FRI' then 6 + when 'SATURDAY' then 7 + when 'SAT' then 7 else -1 end) into v_dow from dual; if v_dow < 0 then - raise exception ''Day of the week unknown''; + raise exception 'Day of the week unknown'; end if; return v_dow; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function to_interval ( - -- - -- Convert an integer to the specified interval - -- - -- Utility function so we do not have to remember how to escape - -- double quotes when we typecast an integer to an interval - -- - -- @author jowell@jsabino.com - -- - -- @param interval_number Integer to convert to interval - -- @param interval_units Interval units - -- - -- @return interval equivalent of interval_number, in interval_units units - -- - integer, - varchar -) -returns interval as ' -declare - interval__number alias for $1; - interval__units alias for $2; -begin + +-- added +select define_function_args('to_interval','number,units'); + +-- +-- procedure to_interval/2 +-- + -- + -- Convert an integer to the specified interval + -- + -- Utility function so we do not have to remember how to escape + -- double quotes when we typecast an integer to an interval + -- + -- @author jowell@jsabino.com + -- + -- @param interval_number Integer to convert to interval + -- @param interval_units Interval units + -- + -- @return interval equivalent of interval_number, in interval_units units + -- + +CREATE OR REPLACE FUNCTION to_interval( + interval__number integer, + interval__units varchar + +) RETURNS interval AS $$ + +DECLARE +BEGIN -- We should probably do unit checking at some point - return ('''''''' || interval__number || '' '' || interval__units || '''''''')::interval; + return ('''' || interval__number || ' ' || interval__units || '''')::interval; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function next_day ( - -- - -- Equivalent of Oracle next_day function - -- - -- @author jowell@jsabino.com - -- - -- @param somedate Reference date - -- @param weekday Day of the week to find - -- - -- @return The date of the next weekday that is later than somedate - -- - timestamptz, -- somedate - varchar -- weekday -) -returns timestamptz as ' -declare - next_day__somedate alias for $1; - next_day__weekday alias for $2; + + +-- added +select define_function_args('next_day','somedate,weekday'); + +-- +-- procedure next_day/2 +-- + -- + -- Equivalent of Oracle next_day function + -- + -- @author jowell@jsabino.com + -- + -- @param somedate Reference date + -- @param weekday Day of the week to find + -- + -- @return The date of the next weekday that is later than somedate + -- +CREATE OR REPLACE FUNCTION next_day( + next_day__somedate timestamptz, + next_day__weekday varchar + +) RETURNS timestamptz AS $$ +DECLARE v_dow integer; v_ref_dow integer; v_add_days integer; -begin +BEGIN -- I cant find a function that converts days of the week to -- the corresponding integer value, so I roll my own (above) -- We avoid extract(dow from timestamp) because of incompatible output with to_char. v_ref_dow := dow_to_int(next_day__weekday); - v_dow := to_number(to_char(next_day__somedate,''D''),''9''); + v_dow := to_number(to_char(next_day__somedate,'D'),'9'); -- If next_day___weekday is the same day of the week as -- next_day__somedate, we add a full week. @@ -128,55 +151,69 @@ end if; -- Do date math - return next_day__somedate + to_interval(v_add_days,''days''); + return next_day__somedate + to_interval(v_add_days,'days'); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function add_months ( - -- - -- Equivalent of Oracle add_months function - -- - -- @author jowell@jsabino.com - -- - -- @param somedate Reference date - -- @param n_months Day of the week to find - -- - -- @return The date plus n_months full months - -- - timestamptz, - integer -) -returns timestamptz as ' -declare - add_months__somedate alias for $1; - add_months__n_months alias for $2; -begin + +-- added +select define_function_args('add_months','somedate,n_months'); + +-- +-- procedure add_months/2 +-- + -- + -- Equivalent of Oracle add_months function + -- + -- @author jowell@jsabino.com + -- + -- @param somedate Reference date + -- @param n_months Day of the week to find + -- + -- @return The date plus n_months full months + -- +CREATE OR REPLACE FUNCTION add_months( + add_months__somedate timestamptz, + add_months__n_months integer + +) RETURNS timestamptz AS $$ +DECLARE +BEGIN -- Date math magic - return add_months__somedate + to_interval(add_months__n_months,''months''); + return add_months__somedate + to_interval(add_months__n_months,'months'); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function last_day ( - -- - -- Equivalent of Oracle last_day function - -- - -- @author jowell@jsabino.com - -- - -- @param somedate Reference date - -- - -- @return The last day of the month containing somedate - -- - timestamptz -) -returns timestamptz as ' -declare - last_day__somedate alias for $1; + + +-- added +select define_function_args('last_day','somedate'); + +-- +-- procedure last_day/1 +-- + -- + -- Equivalent of Oracle last_day function + -- + -- @author jowell@jsabino.com + -- + -- @param somedate Reference date + -- + -- @return The last day of the month containing somedate + -- + +CREATE OR REPLACE FUNCTION last_day( + last_day__somedate timestamptz +) RETURNS timestamptz AS $$ +DECLARE v_month integer; v_targetmonth integer; v_date timestamptz; v_targetdate timestamptz; -begin +BEGIN -- Initial values v_targetdate := last_day__somedate; @@ -185,7 +222,7 @@ -- Add up to 31 days to the given date, stop if month changes. FOR i IN 1..31 LOOP - v_date := last_day__somedate + to_interval(i,''days''); + v_date := last_day__somedate + to_interval(i,'days'); v_month := extract(month from v_date); if v_month != v_targetmonth @@ -199,7 +236,8 @@ return v_targetdate; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/acs-events/sql/postgresql/recurrence-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/postgresql/recurrence-create.sql,v diff -u -N -r1.3 -r1.4 --- openacs-4/packages/acs-events/sql/postgresql/recurrence-create.sql 8 Nov 2010 13:10:35 -0000 1.3 +++ openacs-4/packages/acs-events/sql/postgresql/recurrence-create.sql 30 Mar 2013 13:00:29 -0000 1.4 @@ -122,38 +122,42 @@ -- Currently supports only new and delete methods. -- -create function recurrence__new ( - -- - -- Creates a new recurrence - -- - -- @author W. Scott Meeks - -- - -- @param interval_type Sets interval_type of new recurrence - -- @param every_nth_interval Sets every_nth_interval of new recurrence - -- @param days_of_week Sets days_of_week of new recurrence - -- @param recur_until Sets recur_until of new recurrence - -- @param custom_func Sets name of custom recurrence function - -- - -- @return id of new recurrence - -- - varchar, -- recurrence_interval_types.interval_name%TYPE, - integer, -- recurrences.every_nth_interval%TYPE, - varchar, -- recurrences.days_of_week%TYPE default null, - timestamptz, -- recurrences.recur_until%TYPE default null, - varchar -- recurrences.custom_func%TYPE default null -) -returns integer as ' -- recurrences.recurrence_id%TYPE -declare - new__interval_name alias for $1; - new__every_nth_interval alias for $2; - new__days_of_week alias for $3; -- default null, - new__recur_until alias for $4; -- default null, - new__custom_func alias for $5; -- default null + + +-- added +select define_function_args('recurrence__new','interval_name,every_nth_interval,days_of_week;null,recur_until;null,custom_func;null'); + +-- +-- procedure recurrence__new/5 +-- + -- + -- Creates a new recurrence + -- + -- @author W. Scott Meeks + -- + -- @param interval_type Sets interval_type of new recurrence + -- @param every_nth_interval Sets every_nth_interval of new recurrence + -- @param days_of_week Sets days_of_week of new recurrence + -- @param recur_until Sets recur_until of new recurrence + -- @param custom_func Sets name of custom recurrence function + -- + -- @return id of new recurrence + -- + +CREATE OR REPLACE FUNCTION recurrence__new( + new__interval_name varchar, + new__every_nth_interval integer, + new__days_of_week varchar, -- default null, + new__recur_until timestamptz, -- default null, + new__custom_func varchar -- default null + +) RETURNS integer AS $$ +DECLARE v_recurrence_id recurrences.recurrence_id%TYPE; v_interval_type_id recurrence_interval_types.interval_type%TYPE; -begin +BEGIN - select nextval(''recurrence_sequence'') into v_recurrence_id from dual; + select nextval('recurrence_sequence') into v_recurrence_id from dual; select interval_type into v_interval_type_id @@ -177,33 +181,40 @@ return v_recurrence_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function recurrence__delete ( - -- - -- Deletes the recurrence - -- Note: this will fail if there are any events_with this recurrence - -- because of foreign key constraints. use acs-events__delete instead - -- - -- @author W. Scott Meeks - -- - -- @param recurrence_id id of recurrence to delete - -- - -- @return 0 (procedure dummy) - -- - integer -- in recurrences.recurrence_id%TYPE -) -returns integer as ' -declare - delete__recurrence_id alias for $1; -begin + +-- added +select define_function_args('recurrence__delete','recurrence_id'); + +-- +-- procedure recurrence__delete/1 +-- + -- + -- Deletes the recurrence + -- Note: this will fail if there are any events_with this recurrence + -- because of foreign key constraints. use acs-events__delete instead + -- + -- @author W. Scott Meeks + -- + -- @param recurrence_id id of recurrence to delete + -- + -- @return 0 (procedure dummy) + -- + +CREATE OR REPLACE FUNCTION recurrence__delete( + delete__recurrence_id integer +) RETURNS integer AS $$ +DECLARE +BEGIN delete from recurrences where recurrence_id = delete__recurrence_id; return 0; +END; +$$ LANGUAGE plpgsql; -end;' language 'plpgsql'; - Index: openacs-4/packages/acs-events/sql/postgresql/timespan-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/postgresql/timespan-create.sql,v diff -u -N -r1.5 -r1.6 --- openacs-4/packages/acs-events/sql/postgresql/timespan-create.sql 8 Nov 2010 13:10:35 -0000 1.5 +++ openacs-4/packages/acs-events/sql/postgresql/timespan-create.sql 30 Mar 2013 13:00:29 -0000 1.6 @@ -50,27 +50,33 @@ -- -create function time_interval__new ( - -- - -- Creates a new time interval - -- - -- @author W. Scott Meeks - -- - -- @param start_date Sets this as start_date of new interval - -- @param end_date Sets this as end_date of new interval - -- - -- @return id of new time interval - -- - timestamptz, -- time_intervals.start_date%TYPE default null, - timestamptz -- time_intervals.end_date%TYPE default null -) -returns integer as ' -- time_intervals.interval_id%TYPE -declare - new__start_date alias for $1; -- default null, - new__end_date alias for $2; -- default null + + +-- added +select define_function_args('time_interval__new','start_date;null,end_date;null'); + +-- +-- procedure time_interval__new/2 +-- + -- + -- Creates a new time interval + -- + -- @author W. Scott Meeks + -- + -- @param start_date Sets this as start_date of new interval + -- @param end_date Sets this as end_date of new interval + -- + -- @return id of new time interval + -- +CREATE OR REPLACE FUNCTION time_interval__new( + new__start_date timestamptz, -- default null + new__end_date timestamptz -- default null + +) RETURNS integer AS $$ +DECLARE v_interval_id time_intervals.interval_id%TYPE; -begin - select nextval(''timespan_sequence'') into v_interval_id from dual; +BEGIN + select nextval('timespan_sequence') into v_interval_id from dual; insert into time_intervals (interval_id, start_date, end_date) @@ -79,59 +85,72 @@ return v_interval_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function time_interval__delete ( - -- - -- Deletes the given time interval - -- - -- @author W. Scott Meeks - -- - -- @param interval_id id of the interval to delete - -- - -- @return 0 (procedure dummy) - -- - integer -- time_intervals.interval_id%TYPE -) -returns integer as ' -declare - delete__interval_id alias for $1; -begin - + + +-- added +select define_function_args('time_interval__delete','interval_id'); + +-- +-- procedure time_interval__delete/1 +-- + -- + -- Deletes the given time interval + -- + -- @author W. Scott Meeks + -- + -- @param interval_id id of the interval to delete + -- + -- @return 0 (procedure dummy) + -- +CREATE OR REPLACE FUNCTION time_interval__delete( + delete__interval_id integer + +) RETURNS integer AS $$ +DECLARE +BEGIN delete from time_intervals where interval_id = delete__interval_id; return 0; +END; +$$ LANGUAGE plpgsql; -end;' language 'plpgsql'; - -create function time_interval__edit ( - -- - -- Updates the start_date or end_date of an interval - -- - -- @author W. Scott Meeks - -- - -- @param interval_id id of the interval to update - -- @param start_date Sets this as the new - -- start_date of the interval. - -- @param end_date Sets this as the new - -- end_date of the interval. - -- - -- @return 0 (procedure dummy) - -- - integer, -- time_intervals.interval_id%TYPE, - timestamptz, -- time_intervals.start_date%TYPE default null, - timestamptz -- time_intervals.end_date%TYPE default null -) -returns integer as ' -declare - edit__interval_id alias for $1; - edit__start_date alias for $2; -- default null, - edit__end_date alias for $3; -- default null -begin + +-- added +select define_function_args('time_interval__edit','interval_id,start_date;null,end_date;null'); + +-- +-- procedure time_interval__edit/3 +-- + -- + -- Updates the start_date or end_date of an interval + -- + -- @author W. Scott Meeks + -- + -- @param interval_id id of the interval to update + -- @param start_date Sets this as the new + -- start_date of the interval. + -- @param end_date Sets this as the new + -- end_date of the interval. + -- + -- @return 0 (procedure dummy) + -- + +CREATE OR REPLACE FUNCTION time_interval__edit( + edit__interval_id integer, + edit__start_date timestamptz, -- default null + edit__end_date timestamptz -- default null + +) RETURNS integer AS $$ +DECLARE +BEGIN + -- JS: I hate deeply nested if-else-ifs!!! -- Null for start_date or end_date means dont change. @@ -160,105 +179,119 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function time_interval__shift ( - -- - -- Updates the start_date or end_date of an interval based on offsets (general) - -- - -- @author W. Scott Meeks - -- @param interval_id The interval to update. - -- @param start_offset Adds this date interval to the - -- start_date of the interval. No effect if - -- start_date is null. - -- @param end_offset Adds this date interval to the - -- end_date of the interval. No effect if - -- end_date is null. - -- - -- @return 0 (procedure dummy) - -- - integer, -- time_intervals.interval_id%TYPE, - interval, - interval -) -returns integer as ' -declare - shift__interval_id alias for $1; - shift__start_offset alias for $2; -- default 0, - shift__end_offset alias for $3; -- default 0 -begin + + +-- added +select define_function_args('time_interval__shift','interval_id,start_offset;0,end_offset;0'); + +-- +-- procedure time_interval__shift/3 +-- + -- + -- Updates the start_date or end_date of an interval based on offsets (general) + -- + -- @author W. Scott Meeks + -- @param interval_id The interval to update. + -- @param start_offset Adds this date interval to the + -- start_date of the interval. No effect if + -- start_date is null. + -- @param end_offset Adds this date interval to the + -- end_date of the interval. No effect if + -- end_date is null. + -- + -- @return 0 (procedure dummy) + -- +CREATE OR REPLACE FUNCTION time_interval__shift( + shift__interval_id integer, + shift__start_offset interval, -- default 0, + shift__end_offset interval -- default 0 + +) RETURNS integer AS $$ +DECLARE +BEGIN update time_intervals set start_date = start_date + shift__start_offset, end_date = end_date + shift__end_offset where interval_id = shift__interval_id; return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function time_interval__shift ( - -- - -- Updates the start_date or end_date of an interval based on offsets of - -- fractional days. - -- - -- JS: Overloaded function to make above compatible with Oracle behavior - -- JS: when an integer (for number of days) is supplied as a parameter. - -- - -- @param interval_id The interval to update. - -- @param start_offset Adds this number of days to the - -- start_date of the interval. No effect if - -- start_date is null. - -- @param end_offset Adds this number of days to the - -- end_date of the interval. No effect if - -- end_date is null. - -- - -- @return 0 (procedure dummy) - -- - integer, -- time_intervals.interval_id%TYPE, - integer, - integer -) -returns integer as ' -declare - shift__interval_id alias for $1; - shift__start_offset alias for $2; -- default 0, - shift__end_offset alias for $3; -- default 0 -begin + +-- +-- procedure time_interval__shift/3 +-- + -- + -- Updates the start_date or end_date of an interval based on offsets of + -- fractional days. + -- + -- JS: Overloaded function to make above compatible with Oracle behavior + -- JS: when an integer (for number of days) is supplied as a parameter. + -- + -- @param interval_id The interval to update. + -- @param start_offset Adds this number of days to the + -- start_date of the interval. No effect if + -- start_date is null. + -- @param end_offset Adds this number of days to the + -- end_date of the interval. No effect if + -- end_date is null. + -- + -- @return 0 (procedure dummy) +CREATE OR REPLACE FUNCTION time_interval__shift( + shift__interval_id integer, + shift__start_offset integer, -- default 0, + shift__end_offset integer -- default 0 + +) RETURNS integer AS $$ +DECLARE +BEGIN + return time_interval__shift( shift__interval_id, - to_interval(shift__start_offset,''days''), - to_interval(shift__end_offset,''days'') + to_interval(shift__start_offset,'days'), + to_interval(shift__end_offset,'days') ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function time_interval__overlaps_p ( - -- - -- Returns true if the two intervals overlap, false otherwise. - -- - -- @author W. Scott Meeks - -- - -- @param interval_1_id - -- @param interval_2_id - -- - -- @return true if the two intervals overlap, false otherwise. - -- - integer, -- time_intervals.interval_id%TYPE, - integer -- time_intervals.interval_id%TYPE -) -returns boolean as ' -declare - overlaps_p__interval_id_1 alias for $1; - overlaps_p__interval_id_2 alias for $2; + + +-- added + +-- +-- procedure time_interval__overlaps_p/2 +-- + -- + -- Returns true if the two intervals overlap, false otherwise. + -- + -- @author W. Scott Meeks + -- + -- @param interval_1_id + -- @param interval_2_id + -- + -- @return true if the two intervals overlap, false otherwise. + -- +CREATE OR REPLACE FUNCTION time_interval__overlaps_p( + overlaps_p__interval_id_1 integer, + overlaps_p__interval_id_2 integer + +) RETURNS boolean AS $$ +DECLARE v_start_1 timestamptz; v_start_2 timestamptz; v_end_1 timestamptz; v_end_2 timestamptz; -begin +BEGIN -- Pull out the start and end dates and call the main overlaps_p. select start_date, end_date into v_start_1, v_end_1 @@ -277,33 +310,39 @@ v_end_2 ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function time_interval__overlaps_p ( - -- - -- Returns true if the interval bounded by the given start_date or - -- end_date overlaps the given interval, false otherwise. - -- - -- @author W. Scott Meeks - -- - -- @param start_date See if it overlaps the interval starting from this date. - -- @param end_date See if it overlaps the interval ending on this date. - -- - -- @return true if the interval bounded by start_date through end_date, false otherwise. - -- - integer, -- time_intervals.interval_id%TYPE, - timestamptz, -- time_intervals.start_date%TYPE default null, - timestamptz -- time_intervals.end_date%TYPE default null -) -returns boolean as ' -declare - overlaps_p__interval_id alias for $1; - overlaps_p__start_date alias for $2; -- default null, - overlaps_p__end_date alias for $3; -- default null + + +-- added +select define_function_args('time_interval__overlaps_p','interval_id,start_date;null,end_date;null'); + +-- +-- procedure time_interval__overlaps_p/3 +-- + -- + -- Returns true if the interval bounded by the given start_date or + -- end_date overlaps the given interval, false otherwise. + -- + -- @author W. Scott Meeks + -- + -- @param start_date See if it overlaps the interval starting from this date. + -- @param end_date See if it overlaps the interval ending on this date. + -- + -- @return true if the interval bounded by start_date through end_date, false otherwise. + -- +CREATE OR REPLACE FUNCTION time_interval__overlaps_p( + overlaps_p__interval_id integer, + overlaps_p__start_date timestamptz, -- default null, + overlaps_p__end_date timestamptz -- default null + +) RETURNS boolean AS $$ +DECLARE v_interval_start time_intervals.start_date%TYPE; v_interval_end time_intervals.end_date%TYPE; -begin +BEGIN -- Pull out the start and end date and call the main overlaps_p. select start_date, end_date into v_interval_start, v_interval_end @@ -317,35 +356,37 @@ overlaps_p__end_date ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function time_interval__overlaps_p ( - -- - -- Checks if two intervals overlaps - -- JS: There is a simpler way to evaluate whether intervals overlap, - -- JS: so this function can be optimized. - -- - -- @author W. Scott Meeks - -- - -- @param interval_1_id First interval - -- @param interval_2_id Second interval - -- - -- @return true if intervals overlap, otherwise false. - -- - timestamptz, -- time_intervals.start_date%TYPE, - timestamptz, -- time_intervals.end_date%TYPE, - timestamptz, -- time_intervals.start_date%TYPE, - timestamptz -- time_intervals.end_date%TYPE -) -returns boolean as ' -declare - overlaps_p__start_1 alias for $1; - overlaps_p__end_1 alias for $2; - overlaps_p__start_2 alias for $3; - overlaps_p__end_2 alias for $4; -begin + +-- +-- procedure time_interval__overlaps_p/4 +-- + -- + -- Checks if two intervals overlaps + -- JS: There is a simpler way to evaluate whether intervals overlap, + -- JS: so this function can be optimized. + -- + -- @author W. Scott Meeks + -- + -- @param interval_1_id First interval + -- @param interval_2_id Second interval + -- + -- @return true if intervals overlap, otherwise false. + -- +CREATE OR REPLACE FUNCTION time_interval__overlaps_p( + overlaps_p__start_1 timestamptz, + overlaps_p__end_1 timestamptz, + overlaps_p__start_2 timestamptz, + overlaps_p__end_2 timestamptz + +) RETURNS boolean AS $$ +DECLARE +BEGIN + -- JS: Modified yet another deeply nested if-else-if -- JS: Note that null date is the representation for infinite -- (positive or negative) time. @@ -398,30 +439,38 @@ end if; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function time_interval__eq ( - -- - -- Checks if two intervals are equal - -- - -- @author W. Scott Meeks - -- - -- @param interval_1_id First interval - -- @param interval_2_id Second interval - -- - -- @return true if intervals are equal, otherwise false. - -- - integer, -- time_intervals.interval_id%TYPE, - integer -- time_intervals.interval_id%TYPE -) -returns boolean as ' -- return boolean -declare - eq__interval_1_id alias for $1; - eq__interval_2_id alias for $2; + + +-- added +select define_function_args('time_interval__eq','interval_1_id,interval_2_id'); + +-- +-- procedure time_interval__eq/2 +-- + -- + -- Checks if two intervals are equal + -- + -- @author W. Scott Meeks + -- + -- @param interval_1_id First interval + -- @param interval_2_id Second interval + -- + -- @return true if intervals are equal, otherwise false. + -- +CREATE OR REPLACE FUNCTION time_interval__eq( + eq__interval_1_id integer, + eq__interval_2_id integer + +) RETURNS boolean AS $$ +-- return boolean +DECLARE interval_1_row time_intervals%ROWTYPE; interval_2_row time_intervals%ROWTYPE; -begin +BEGIN select * into interval_1_row from time_intervals where interval_id = eq__interval_1_id; @@ -438,50 +487,56 @@ return false; end if; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function time_interval__copy( - -- - -- Creates a new copy of a time interval, offset by optional offset - -- - -- JS: We need to be careful in interpreting the copy offset. - -- JS: Oracle interprets integers as full days when doing - -- JS: date arithmetic. Thus, - -- JS: - -- JS: select sysdate()+1 from dual; - -- JS: - -- JS: will yield the next date, correct up to the second of the next day - -- JS: that the query was run. - -- JS: - -- JS: In PostgreSQL, we need to specify the type of interval when - -- JS: doing date arithmetic. if, say, an integer is used in date arithmetic, - -- JS: the results are weird. For example, - -- JS: - -- JS: select now()+1 from dual; - -- JS: - -- JS: will yield the MIDNIGHT of the next date that the query was run, i.e., - -- JS: the timestamp is typecasted as a date with a day granularity. To get the - -- JS: same effect as Oracle, we need to use explicitly typecast the integer into - -- JS: a day interval. - -- - -- @author W. Scott Meeks - -- - -- @param interval_id Interval to copy - -- @param offset Interval is offset by this date interval - -- - -- @return interval_id of the copied interval - -- - integer, -- time_intervals.interval_id%TYPE, - interval -) -returns integer as ' -- time_intervals.interval_id%TYPE -declare - copy__interval_id alias for $1; - copy__offset alias for $2; -- default 0 + + +-- added +select define_function_args('time_interval__copy','interval_id,offset;0'); + +-- +-- procedure time_interval__copy/2 +-- + -- + -- Creates a new copy of a time interval, offset by optional offset + -- + -- JS: We need to be careful in interpreting the copy offset. + -- JS: Oracle interprets integers as full days when doing + -- JS: date arithmetic. Thus, + -- JS: + -- JS: select sysdate()+1 from dual; + -- JS: + -- JS: will yield the next date, correct up to the second of the next day + -- JS: that the query was run. + -- JS: + -- JS: In PostgreSQL, we need to specify the type of interval when + -- JS: doing date arithmetic. if, say, an integer is used in date arithmetic, + -- JS: the results are weird. For example, + -- JS: + -- JS: select now()+1 from dual; + -- JS: + -- JS: will yield the MIDNIGHT of the next date that the query was run, i.e., + -- JS: the timestamp is typecasted as a date with a day granularity. To get the + -- JS: same effect as Oracle, we need to use explicitly typecast the integer into + -- JS: a day interval. + -- + -- @author W. Scott Meeks + -- + -- @param interval_id Interval to copy + -- @param offset Interval is offset by this date interval + -- + -- @return interval_id of the copied interval +CREATE OR REPLACE FUNCTION time_interval__copy( + copy__interval_id integer, + copy__offset interval -- default 0 + +) RETURNS integer AS $$ +DECLARE interval_row time_intervals%ROWTYPE; v_foo timestamptz; -begin +BEGIN select * into interval_row from time_intervals where interval_id = copy__interval_id; @@ -491,61 +546,72 @@ (interval_row.end_date ::timestamp + copy__offset) :: timestamptz ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function time_interval__copy ( - -- - -- Creates a new copy of a time interval. - -- JS: Overloaded versaion of above, no offset - -- - -- @param interval_id Interval to copy - -- - -- @return interval_id of the copied interval - -- - integer -) -returns integer as ' -- return time_intervals.interval_id%TYPE -declare - copy__interval_id alias for $1; + + +-- +-- procedure time_interval__copy/1 +-- + -- + -- Creates a new copy of a time interval. + -- JS: Overloaded versaion of above, no offset + -- + -- @param interval_id Interval to copy + -- + -- @return interval_id of the copied interval + -- +CREATE OR REPLACE FUNCTION time_interval__copy( + copy__interval_id integer + +) RETURNS integer AS $$ +-- return time_intervals.interval_id%TYPE +DECLARE v_query varchar; v_result time_intervals.interval_id%TYPE; rec_datecalc record; -begin +BEGIN return time_interval__copy( copy__interval_id, - interval ''0 days'' + interval '0 days' ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function time_interval__copy( - -- - -- Creates a new copy of a time interval, offset by optional offset - -- - -- JS: Overloaded function to make above compatible with Oracle behavior - -- JS: when an integer (for number of days) is supplied as a parameter. - -- - -- @param interval_id Interval to copy - -- @param offset Interval is offset by this number of days - -- - -- @return interval_id of the copied interval - -- - integer, -- time_intervals.interval_id%TYPE, - integer -) -returns integer as ' -- time_intervals.interval_id%TYPE -declare - copy__interval_id alias for $1; - copy__offset alias for $2; -- default 0 -begin + +-- +-- procedure time_interval__copy/2 +-- + -- + -- Creates a new copy of a time interval, offset by optional offset + -- + -- JS: Overloaded function to make above compatible with Oracle behavior + -- JS: when an integer (for number of days) is supplied as a parameter. + -- + -- @param interval_id Interval to copy + -- @param offset Interval is offset by this number of days + -- + -- @return interval_id of the copied interval + -- +CREATE OR REPLACE FUNCTION time_interval__copy( + copy__interval_id integer, + copy__offset integer -- default 0 + +) RETURNS integer AS $$ +DECLARE +BEGIN + return time_interval__copy( copy__interval_id, - to_interval(copy__offset,''days'') + to_interval(copy__offset,'days') ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; @@ -607,35 +673,41 @@ -- multi_interval_p () -create function timespan__new ( - -- - -- Creates a new timespan (20.20.10) - -- given a time_interval - -- - -- JS: Allow user to specify whether the itme interval is to be copied or not - -- JS: This gives more flexibility of not making a copy instead of requiring - -- JS: the caller responsible for deleting the copy. - -- - -- @author W. Scott Meeks - -- - -- @param interval_id Id of interval to be included/copied in timespan, - -- @param copy_p If true, make another copy of the interval, - -- else simply include the interval in the timespan - -- - -- @return Id of new timespan - -- - integer, -- time_intervals.interval_id%TYPE - boolean -) -returns integer as ' -- timespans.timespan_id%TYPE -declare - new__interval_id alias for $1; - new__copy_p alias for $2; + + +-- added + +-- +-- procedure timespan__new/2 +-- + -- + -- Creates a new timespan (20.20.10) + -- given a time_interval + -- + -- JS: Allow user to specify whether the itme interval is to be copied or not + -- JS: This gives more flexibility of not making a copy instead of requiring + -- JS: the caller responsible for deleting the copy. + -- + -- @author W. Scott Meeks + -- + -- @param interval_id Id of interval to be included/copied in timespan, + -- @param copy_p If true, make another copy of the interval, + -- else simply include the interval in the timespan + -- + -- @return Id of new timespan + -- +CREATE OR REPLACE FUNCTION timespan__new( + new__interval_id integer, + new__copy_p boolean + +) RETURNS integer AS $$ +-- timespans.timespan_id%TYPE +DECLARE v_timespan_id timespans.timespan_id%TYPE; v_interval_id time_intervals.interval_id%TYPE; -begin +BEGIN -- get a new id; - select nextval(''timespan_sequence'') into v_timespan_id from dual; + select nextval('timespan_sequence') into v_timespan_id from dual; if new__copy_p then @@ -652,84 +724,104 @@ return v_timespan_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- end new; -create function timespan__new ( - -- - -- Creates a new timespan (20.20.10) - -- given a time_interval - -- - -- JS: I understand why we want to copy here (since interval_id - -- JS: may be used by another), but see note on time_span__copy - -- JS: below. THE ONLY REASON WHY DEFAULT IS TRUE IS TO MAINTAIN - -- JS: COMPATIBILITY WITH ORIGINAL VERSION. I DO NOT THINK TRUE - -- JS: SHOULD BE THE DEFAULT. - -- - -- @param interval_id Id of interval to be copied in timespan, - -- - -- @return Id of new timespan - -- - integer -- time_intervals.interval_id%TYPE -) -returns integer as ' -- timespans.timespan_id%TYPE -declare - new__interval_id alias for $1; -begin + + +-- +-- procedure timespan__new/1 +-- + -- + -- Creates a new timespan (20.20.10) + -- given a time_interval + -- + -- JS: I understand why we want to copy here (since interval_id + -- JS: may be used by another), but see note on time_span__copy + -- JS: below. THE ONLY REASON WHY DEFAULT IS TRUE IS TO MAINTAIN + -- JS: COMPATIBILITY WITH ORIGINAL VERSION. I DO NOT THINK TRUE + -- JS: SHOULD BE THE DEFAULT. + -- + -- @param interval_id Id of interval to be copied in timespan, + -- + -- @return Id of new timespan + -- +CREATE OR REPLACE FUNCTION timespan__new( + new__interval_id integer + +) RETURNS integer AS $$ +DECLARE +BEGIN return timespan__new( new__interval_id, true ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function timespan__new ( - -- - -- Creates a new timespan (20.20.10) - -- given a start date and end date. A new time interval with the - -- start and end dates is automatically created. - -- - -- @param start_date Start date of interval to be included/copied in timespan, - -- @param end_date End date of interval to be included/copied in timespan, - -- - -- @return Id of new timespan - -- - timestamptz, -- time_intervals.start_date%TYPE default null, - timestamptz -- time_intervals.end_date%TYPE default null -) -returns integer as ' -- timespans.timespan_id%TYPE -declare - new__start_date alias for $1; -- default null, - new__end_date alias for $2; -- default null -begin + +-- added +select define_function_args('timespan__new','start_date;null,end_date;null'); + +-- +-- procedure timespan__new/2 +-- + -- + -- Creates a new timespan (20.20.10) + -- given a start date and end date. A new time interval with the + -- start and end dates is automatically created. + -- + -- @param start_date Start date of interval to be included/copied in timespan, + -- @param end_date End date of interval to be included/copied in timespan, + -- + -- @return Id of new timespan + -- +CREATE OR REPLACE FUNCTION timespan__new( + new__start_date timestamptz, -- default null, + new__end_date timestamptz -- default null + +) RETURNS integer AS $$ +DECLARE +BEGIN + -- JS: If we simply call timespan__new with default copy_p = true, -- JS: there will be two new time intervals that will be created -- JS: everytime this function is called. The first one will never be used!!! -- JS: To fix, we use the timespan__new with copy_p parameter and -- JS: setting copy_p to false. return timespan__new(time_interval__new(new__start_date, new__end_date),false); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function timespan__delete ( - -- - -- Deletes the timespan and any contained intervals - -- - -- @author W. Scott Meeks - -- - -- @param timespan_id Id of timespan to delete - -- - -- @return 0 (procedure dummy) - -- - integer -- timespans.timespan_id%TYPE -) -returns integer as ' -declare - delete__timespan_id alias for $1; -begin + + +-- added +select define_function_args('timespan__delete','timespan_id'); + +-- +-- procedure timespan__delete/1 +-- + -- + -- Deletes the timespan and any contained intervals + -- + -- @author W. Scott Meeks + -- + -- @param timespan_id Id of timespan to delete + -- + -- @return 0 (procedure dummy) + -- +CREATE OR REPLACE FUNCTION timespan__delete( + delete__timespan_id integer + +) RETURNS integer AS $$ +DECLARE +BEGIN -- Delete intervals, corresponding timespan entries deleted by -- cascading constraints @@ -739,38 +831,45 @@ where timespan_id = delete__timespan_id); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function timespan__join_interval ( - -- - -- Join a time interval to an existing timespan - -- - -- JS: Slight changes from original - -- JS: Return the interval_id being joined, since it will not be the - -- JS: same as join_interval__interval_id if join_interval__copy_p is true - -- JS: The Oracle version is a procedure, so this change is completely free. - -- - -- @author W. Scott Meeks - -- - -- @param timespan_id Id of timespan to join to - -- @param interval_id Id of interval to include/copy into timespan - -- @param copy_p If true, make a new copy of he interval for inclusion - -- into the timespan, otherwise simply include the interval - -- - -- @return Id of interval being joined - -- - integer, -- timespans.timespan_id%TYPE, - integer, -- time_intervals.interval_id%TYPE, - boolean -) -returns integer as ' -- time_intervals.interval_id%TYPE -declare - join_interval__timespan_id alias for $1; - join_interval__interval_id alias for $2; - join_interval__copy_p alias for $3; -- default true + + +-- added +select define_function_args('timespan__join_interval','timespan_id,interval_id,copy_p;true'); + +-- +-- procedure timespan__join_interval/3 +-- + -- + -- Join a time interval to an existing timespan + -- + -- JS: Slight changes from original + -- JS: Return the interval_id being joined, since it will not be the + -- JS: same as join_interval__interval_id if join_interval__copy_p is true + -- JS: The Oracle version is a procedure, so this change is completely free. + -- + -- @author W. Scott Meeks + -- + -- @param timespan_id Id of timespan to join to + -- @param interval_id Id of interval to include/copy into timespan + -- @param copy_p If true, make a new copy of he interval for inclusion + -- into the timespan, otherwise simply include the interval + -- + -- @return Id of interval being joined + -- +CREATE OR REPLACE FUNCTION timespan__join_interval( + join_interval__timespan_id integer, + join_interval__interval_id integer, + join_interval__copy_p boolean -- default true + +) RETURNS integer AS $$ +-- time_intervals.interval_id%TYPE +DECLARE v_interval_id time_intervals.interval_id%TYPE; -begin +BEGIN if join_interval__copy_p then v_interval_id := time_interval__copy(join_interval__interval_id); else @@ -785,35 +884,41 @@ -- JS: We might as well return the interval id being joined, instead of returning a dummy integer return v_interval_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function timespan__join ( - -- - -- Join a new interval with start and end dates to an existing timespan - -- - -- JS: Slight change from original - -- JS: Return the interval_id being joined (Oracle version is a procedure) - -- - -- @author W. Scott Meeks - -- - -- @param timespan_id Id of timespan to join new interval - -- @param start_date Start date of new interval to join to timespan - -- @param end_date End date of new interval to join to timespan - -- - -- @return Id of interval being joined - -- - integer, -- timespans.timespan_id%TYPE, - timestamptz, -- time_intervals.start_date%TYPE - timestamptz -- time_intervals.end_date%TYPE -) -returns integer as ' -- time_intervals.interval_id%TYPE -declare - join__timespan_id alias for $1; - join__start_date alias for $2; -- default null, - join__end_date alias for $3; -- default null -begin + +-- added +select define_function_args('timespan__join','timespan_id,start_date;null,end_date;null'); + +-- +-- procedure timespan__join/3 +-- + -- + -- Join a new interval with start and end dates to an existing timespan + -- + -- JS: Slight change from original + -- JS: Return the interval_id being joined (Oracle version is a procedure) + -- + -- @author W. Scott Meeks + -- + -- @param timespan_id Id of timespan to join new interval + -- @param start_date Start date of new interval to join to timespan + -- @param end_date End date of new interval to join to timespan + -- + -- @return Id of interval being joined + -- +CREATE OR REPLACE FUNCTION timespan__join( + join__timespan_id integer, + join__start_date timestamptz, -- default null, + join__end_date timestamptz -- default null + +) RETURNS integer AS $$ +DECLARE +BEGIN + -- JS: This will create a new interval with start_date and end_date -- JS: so we might as well return the interval id return timespan__join_interval( @@ -822,35 +927,39 @@ false ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function timespan__join ( - -- - -- Join a new timespan or time interval to an existing timespan - -- - -- JS: Slight changes from original - -- JS: Return the last interval_id being joined. Although probably not useful - -- JS: we return the interval_id anyways to make the function consisted with - -- JS: the rest. Oracle version is a procedure. - -- - -- @author W. Scott Meeks - -- - -- @param timespan_id Id of timespan to join to - -- @param timespan_id Id of timespan to join from - -- - -- @return Id of last interval in timespan being joined - -- - integer, -- timespans.timespan_id%TYPE, - integer -- timespans.timespan_id%TYPE -) -returns integer as ' -- time_intervals.interval_id%TYPE -declare - join__timespan_1_id alias for $1; - join__timespan_2_id alias for $2; + + +-- +-- procedure timespan__join/2 +-- + -- + -- Join a new timespan or time interval to an existing timespan + -- + -- JS: Slight changes from original + -- JS: Return the last interval_id being joined. Although probably not useful + -- JS: we return the interval_id anyways to make the function consisted with + -- JS: the rest. Oracle version is a procedure. + -- + -- @author W. Scott Meeks + -- + -- @param timespan_id Id of timespan to join to + -- @param timespan_id Id of timespan to join from + -- + -- @return Id of last interval in timespan being joined + -- +CREATE OR REPLACE FUNCTION timespan__join( + join__timespan_1_id integer, + join__timespan_2_id integer + +) RETURNS integer AS $$ +DECLARE v_interval_id time_intervals.interval_id%TYPE; rec_timespan record; -begin +BEGIN -- Loop over intervals in 2nd timespan, join with 1st. for rec_timespan in select * @@ -868,57 +977,72 @@ -- JS: more than one interval joined return v_interval_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function timespan__interval_delete ( - -- - -- Deletes an interval from the given timespan - -- - -- @author W. Scott Meeks - -- - -- @param timespan_id timespan to delete from - -- @param interval_id delete this interval from the set - -- - -- @return 0 (procedure dummy) - -- - integer, -- timespans.timespan_id%TYPE, - integer -- time_intervals.interval_id%TYPE -) -returns integer as ' -declare - interval_delete__timespan_id alias for $1; - interval_delete__interval_id alias for $2; -begin + +-- added +select define_function_args('timespan__interval_delete','timespan_id,interval_id'); + +-- +-- procedure timespan__interval_delete/2 +-- + -- + -- Deletes an interval from the given timespan + -- + -- @author W. Scott Meeks + -- + -- @param timespan_id timespan to delete from + -- @param interval_id delete this interval from the set + -- + -- @return 0 (procedure dummy) + -- +CREATE OR REPLACE FUNCTION timespan__interval_delete( + interval_delete__timespan_id integer, + interval_delete__interval_id integer + +) RETURNS integer AS $$ +DECLARE +BEGIN + delete from timespans where timespan_id = interval_delete__timespan_id and interval_id = interval_delete__interval_id; return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function timespan__exists_p ( - -- - -- If its contained intervals are all deleted, then a timespan will - -- automatically be deleted. This checks a timespan_id to make sure it is - -- still valid. - -- - -- @author W. Scott Meeks - -- - -- @param timespan_id id of timespan to check - -- - -- @return true if interval is in timespan set, otherwise false. - -- - integer -- timespans.timespan_id%TYPE -) -returns boolean as ' -declare - exists_p__timespan_id alias for $1; + + +-- added +select define_function_args('timespan__exists_p','timespan_id'); + +-- +-- procedure timespan__exists_p/1 +-- + -- + -- If its contained intervals are all deleted, then a timespan will + -- automatically be deleted. This checks a timespan_id to make sure it is + -- still valid. + -- + -- @author W. Scott Meeks + -- + -- @param timespan_id id of timespan to check + -- + -- @return true if interval is in timespan set, otherwise false. + -- +CREATE OR REPLACE FUNCTION timespan__exists_p( + exists_p__timespan_id integer + +) RETURNS boolean AS $$ +DECLARE v_result integer; -begin +BEGIN -- Only need to check if any rows exist. select count(*) into v_result @@ -933,27 +1057,35 @@ return true; end if; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function timespan__multi_interval_p ( - -- - -- Checks if timespan contains more than one interval - -- - -- @author W. Scott Meeks - -- - -- @param timespan_id id of timespan to check - -- - -- @return true if timespan has more than one interval, otherwise false. - -- - integer -- timespans.timespan_id%TYPE -) -returns boolean as ' -declare - multi_interval_p__timespan_id alias for $1; + + +-- added +select define_function_args('timespan__multi_interval_p','timespan_id'); + +-- +-- procedure timespan__multi_interval_p/1 +-- + -- + -- Checks if timespan contains more than one interval + -- + -- @author W. Scott Meeks + -- + -- @param timespan_id id of timespan to check + -- + -- @return true if timespan has more than one interval, otherwise false. + -- +CREATE OR REPLACE FUNCTION timespan__multi_interval_p( + multi_interval_p__timespan_id integer + +) RETURNS boolean AS $$ +DECLARE v_result boolean; -begin - -- ''f'' if 0 or 1 intervals, ''t'' otherwise +BEGIN + -- 'f' if 0 or 1 intervals, 't' otherwise -- use the simple case syntax select (case count(timespan_id) when 0 then false @@ -966,30 +1098,37 @@ return v_result; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function timespan__overlaps_interval_p ( - -- - -- Checks to see interval overlaps any of the intervals in the timespan. - -- - -- @author W. Scott Meeks - -- - -- @param timespan_id id of timespan as reference - -- @param timespan_id id of timespan to check - -- - -- @return true if interval overlaps with anyinterval in timespan, otherwise false. - -- - integer, -- timespans.timespan_id%TYPE, - integer -- time_intervals.interval_id%TYPE default null -) -returns boolean as ' -declare - overlaps_interval_p__timespan_id alias for $1; - overlaps_interval_p__interval_id alias for $2; -- default null + + +-- added +select define_function_args('timespan__overlaps_interval_p','timespan_id,interval_id;null'); + +-- +-- procedure timespan__overlaps_interval_p/2 +-- + -- + -- Checks to see interval overlaps any of the intervals in the timespan. + -- + -- @author W. Scott Meeks + -- + -- @param timespan_id id of timespan as reference + -- @param timespan_id id of timespan to check + -- + -- @return true if interval overlaps with anyinterval in timespan, otherwise false. + -- +CREATE OR REPLACE FUNCTION timespan__overlaps_interval_p( + overlaps_interval_p__timespan_id integer, + overlaps_interval_p__interval_id integer -- default null + +) RETURNS boolean AS $$ +DECLARE v_start_date timestamptz; v_end_date timestamptz; -begin +BEGIN select start_date, end_date into v_start_date, v_end_date from time_intervals @@ -1001,31 +1140,35 @@ v_end_date ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function timespan__overlaps_p ( - -- - -- Checks to see if any intervals in a timespan overlap any of the intervals - -- in the second timespan. - -- - -- @author W. Scott Meeks - -- - -- @param timespan_id id of timespan as reference - -- @param timespan_id id of timespan to check - -- - -- @return true if timespan overlaps with second timespan, otherwise false. - -- - integer, -- timespans.timespan_id%TYPE, - integer -- timespans.timespan_id%TYPE -) -returns boolean as ' -declare - overlaps_p__timespan_1_id alias for $1; - overlaps_p__timespan_2_id alias for $2; + + +-- added + +-- +-- procedure timespan__overlaps_p/2 +-- + -- + -- Checks to see if any intervals in a timespan overlap any of the intervals + -- in the second timespan. + -- + -- @author W. Scott Meeks + -- + -- @param timespan_id id of timespan as reference + -- @param timespan_id id of timespan to check + -- +CREATE OR REPLACE FUNCTION timespan__overlaps_p( + overlaps_p__timespan_1_id integer, + overlaps_p__timespan_2_id integer + +) RETURNS boolean AS $$ +DECLARE v_result boolean; rec_timespan record; -begin +BEGIN -- Loop over 2nd timespan, checking each interval against 1st for rec_timespan in select * @@ -1043,34 +1186,40 @@ return false; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function timespan__overlaps_p ( - -- - -- Checks to see if interval with start and end dates overlap any of the intervals - -- in the timespan. - -- - -- @author W. Scott Meeks - -- - -- @param timespan_id Id of timespan as reference - -- @param start_date Start date of interval - -- @param end_date End date of interval - -- - -- @return true if interval with start and end dates overlaps with second timespan, otherwise false. - -- - integer, -- timespans.timespan_id%TYPE, - timestamptz, -- time_intervals.start_date%TYPE - timestamptz -- time_intervals.end_date%TYPE -) -returns boolean as ' -declare - overlaps_p__timespan_id alias for $1; - overlaps_p__start_date alias for $2; -- default null, - overlaps_p__end_date alias for $3; -- default null + + +-- added +select define_function_args('timespan__overlaps_p','timespan_id,start_date;null,end_date;null'); + +-- +-- procedure timespan__overlaps_p/3 +-- + -- + -- Checks to see if interval with start and end dates overlap any of the intervals + -- in the timespan. + -- + -- @author W. Scott Meeks + -- + -- @param timespan_id Id of timespan as reference + -- @param start_date Start date of interval + -- @param end_date End date of interval + -- + -- @return true if interval with start and end dates overlaps with second timespan, otherwise false. + -- +CREATE OR REPLACE FUNCTION timespan__overlaps_p( + overlaps_p__timespan_id integer, + overlaps_p__start_date timestamptz, -- default null, + overlaps_p__end_date timestamptz -- default null + +) RETURNS boolean AS $$ +DECLARE v_result boolean; rec_timespan record; -begin +BEGIN -- Loop over each interval in timespan, checking against dates. for rec_timespan in select * @@ -1090,32 +1239,39 @@ return false; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function timespan__copy ( - -- - -- Creates a new copy of a timespan, offset by optional offset - -- JS: See note on intervals on time_interval__copy - -- - -- @author W. Scott Meeks - -- - -- @param timespan_id Timespan to copy - -- @param offset Offset al dates in timespan by this date interval - -- - -- @return Id of copied timespan - -- - integer, -- timespans.timespan_id%TYPE, - interval -) -returns integer as ' -- timespans.timespan_id%TYPE -declare - copy__timespan_id alias for $1; - copy__offset alias for $2; -- default 0 + + +-- added +select define_function_args('timespan__copy','timespan_id,offset'); + +-- +-- procedure timespan__copy/2 +-- + -- + -- Creates a new copy of a timespan, offset by optional offset + -- JS: See note on intervals on time_interval__copy + -- + -- @author W. Scott Meeks + -- + -- @param timespan_id Timespan to copy + -- @param offset Offset al dates in timespan by this date interval + -- + -- @return Id of copied timespan + -- +CREATE OR REPLACE FUNCTION timespan__copy( + copy__timespan_id integer, + copy__offset interval -- default 0 + +) RETURNS integer AS $$ +DECLARE rec_timespan record; v_interval_id timespans.interval_id%TYPE; v_timespan_id timespans.timespan_id%TYPE; -begin +BEGIN v_timespan_id := null; -- Loop over each interval in timespan, creating a new copy @@ -1145,56 +1301,66 @@ return v_timespan_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function timespan__copy ( - -- - -- Creates a new copy of a timespan, no offset - -- - -- @param timespan_id Timespan to copy - -- @param offset Offset al dates in timespan by this date interval - -- - -- @return Id of copied timespan - -- - integer -- timespans.timespan_id%TYPE, -) -returns integer as ' -- timespans.timespan_id%TYPE -declare - copy__timespan_id alias for $1; -begin + +-- +-- procedure timespan__copy/1 +-- + -- + -- Creates a new copy of a timespan, no offset + -- + -- @param timespan_id Timespan to copy + -- @param offset Offset al dates in timespan by this date interval + -- + -- @return Id of copied timespan + -- +CREATE OR REPLACE FUNCTION timespan__copy( + copy__timespan_id integer + +) RETURNS integer AS $$ +-- timespans.timespan_id%TYPE +DECLARE +BEGIN + return timespan__copy( copy__timespan_id, - interval ''0 days'' + interval '0 days' ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function timespan__copy ( - -- - -- Creates a new copy of a timespan, offset by optional offset - -- JS: Overloaded function to make above compatible with Oracle behavior - -- JS: when an integer (for number of days) is supplied as a parameter. - -- - -- @param timespan_id Timespan to copy - -- @param offset Offset all dates in timespan by this number of days - -- - -- @return Id of copied timespan - -- - integer, -- timespans.timespan_id%TYPE, - integer -) -returns integer as ' -- timespans.timespan_id%TYPE -declare - copy__timespan_id alias for $1; - copy__offset alias for $2; -begin + +-- +-- procedure timespan__copy/2 +-- + -- + -- Creates a new copy of a timespan, offset by optional offset + -- JS: Overloaded function to make above compatible with Oracle behavior + -- JS: when an integer (for number of days) is supplied as a parameter. + -- + -- @param timespan_id Timespan to copy + -- @param offset Offset all dates in timespan by this number of days + -- + -- @return Id of copied timespan + -- +CREATE OR REPLACE FUNCTION timespan__copy( + copy__timespan_id integer, + copy__offset integer + +) RETURNS integer AS $$ +DECLARE +BEGIN return timespan__copy( copy__timespan_id, - to_interval(copy__offset,''days'') + to_interval(copy__offset,'days') ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/acs-events/sql/postgresql/test/acs-events-test.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/postgresql/test/acs-events-test.sql,v diff -u -N -r1.4 -r1.5 --- openacs-4/packages/acs-events/sql/postgresql/test/acs-events-test.sql 17 May 2003 09:47:26 -0000 1.4 +++ openacs-4/packages/acs-events/sql/postgresql/test/acs-events-test.sql 30 Mar 2013 13:00:30 -0000 1.5 @@ -10,11 +10,10 @@ -- Note: These tests use the semi-ported utPLSQL regression package \i utest-create.sql -create function ut__setup() -returns integer as ' -begin +CREATE OR REPLACE FUNCTION ut__setup() RETURNS integer AS $$ +BEGIN - raise notice ''Setting up acs-events-test...''; + raise notice 'Setting up acs-events-test...'; -- create copies of the tables -- No need for excute here? @@ -37,14 +36,14 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut__teardown() -returns integer as ' -begin +CREATE OR REPLACE FUNCTION ut__teardown() RETURNS integer AS $$ +BEGIN - raise notice ''Tearing down acs-events-test...''; + raise notice 'Tearing down acs-events-test...'; -- remove copies of the tables -- cascade does not work? @@ -57,114 +56,130 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- This is an example of a simple custom recurrence function: recur every three days -create function recur_every3(timestamptz,integer) -returns timestamptz as ' -declare - recur_every3__date alias for $1; - recur_every3__interval alias for $2; -begin - return recur_every3__date + to_interval(3*recur_every3__interval,''days''); -end;' language 'plpgsql'; +-- added +select define_function_args('recur_every3','date,interval'); +-- +-- procedure recur_every3/2 +-- +CREATE OR REPLACE FUNCTION recur_every3( + recur_every3__date timestamptz, + recur_every3__interval integer +) RETURNS timestamptz AS $$ +DECLARE +BEGIN + return recur_every3__date + to_interval(3*recur_every3__interval,'days'); +END; +$$ LANGUAGE plpgsql; + + + -- The test of insert_instances has been augmented to test -- other routines. Specifically new, delete, delete_all, -- timespan_set, activity_set, get_name, get_description, -- party_map, party_unmap, recurs_p, instances_exist_p -create function ut__insert_instances() -returns integer as ' -declare - date1 timestamptz := ''2000-03-23 13:00''; - date2 timestamptz := ''2000-03-23 14:00''; + + +-- +-- procedure ut__insert_instances/0 +-- +CREATE OR REPLACE FUNCTION ut__insert_instances( + +) RETURNS integer AS $$ +DECLARE + date1 timestamptz := '2000-03-23 13:00'; + date2 timestamptz := '2000-03-23 14:00'; insert_instances__timespan_id acs_events.timespan_id%TYPE; insert_instances__activity_id acs_events.activity_id%TYPE; insert_instances__recurrence_id acs_events.recurrence_id%TYPE; insert_instances__event_id acs_events.event_id%TYPE; v_instance_count integer; rec_events record; v_dummy_id integer; -begin +BEGIN - raise notice ''Testing INSERT_INSTANCES...''; + raise notice 'Testing INSERT_INSTANCES...'; -- Create event components insert_instances__timespan_id := timespan__new(date1, date2); - insert_instances__recurrence_id := recurrence__new(''week'', + insert_instances__recurrence_id := recurrence__new('week', 1, - ''1 3'', - to_date(''2000-04-21'',''YYYY-MM-DD''), + '1 3', + to_date('2000-04-21','YYYY-MM-DD'), null ); -- Note to self: we still need to test acs-activity API insert_instances__activity_id := acs_activity__new(null, - ''Testing (pre-edit)'', - ''Making sure the acs_activity code works (pre-edit)'', - ''t'', + 'Testing (pre-edit)', + 'Making sure the acs_activity code works (pre-edit)', + 't', null, - ''acs_activity'', + 'acs_activity', now(), null, null, null ); -- Check acs_activity__name - PERFORM ut_assert__eq (''Test of activity__name'', + PERFORM ut_assert__eq ('Test of activity__name', acs_activity__name(insert_instances__activity_id), - ''Testing (pre-edit)'' + 'Testing (pre-edit)' ); -- Check acs_activity__edit - PERFORM acs_activity__edit(insert_instances__activity_id,''Testing (edited)'',null,null); - PERFORM ut_assert__eq (''Test of activity__edit'', + PERFORM acs_activity__edit(insert_instances__activity_id,'Testing (edited)',null,null); + PERFORM ut_assert__eq ('Test of activity__edit', acs_activity__name(insert_instances__activity_id), - ''Testing (edited)'' + 'Testing (edited)' ); -- Since there is no API for getting the description and html_p... for rec_events in select * from acs_activities where activity_id = insert_instances__activity_id loop - PERFORM ut_assert__eq (''Test of activity__edit (description)'', + PERFORM ut_assert__eq ('Test of activity__edit (description)', rec_events.description, - ''Making sure the acs_activity code works (pre-edit)''); + 'Making sure the acs_activity code works (pre-edit)'); - PERFORM ut_assert__eq (''Test of activity__edit (html_p)'', + PERFORM ut_assert__eq ('Test of activity__edit (html_p)', rec_events.html_p, - ''t''); + 't'); end loop; -- Try to edit everything instead PERFORM acs_activity__edit(insert_instances__activity_id, - ''Testing'', - ''Making sure the acs_activity code works'', - ''f''); + 'Testing', + 'Making sure the acs_activity code works', + 'f'); - PERFORM ut_assert__eq (''Test of activity__edit'', + PERFORM ut_assert__eq ('Test of activity__edit', acs_activity__name(insert_instances__activity_id), - ''Testing'' + 'Testing' ); -- Since there is no API for getting the description and html_p... for rec_events in select * from acs_activities where activity_id = insert_instances__activity_id loop - PERFORM ut_assert__eq (''Test of activity__edit (description)'', + PERFORM ut_assert__eq ('Test of activity__edit (description)', rec_events.description, - ''Making sure the acs_activity code works''); + 'Making sure the acs_activity code works'); - PERFORM ut_assert__eq (''Test of activity__edit (html_p)'', + PERFORM ut_assert__eq ('Test of activity__edit (html_p)', rec_events.html_p, - ''f''); + 'f'); end loop; @@ -176,10 +191,10 @@ -- There should be one entry in the mapping table - PERFORM ut_assert__eqquery (''Test count of object mappings in acs_activity_object_map'', - ''select count(*) from acs_activity_object_map - where activity_id = '' || insert_instances__activity_id, - ''select 1 from dual'' + PERFORM ut_assert__eqquery ('Test count of object mappings in acs_activity_object_map', + 'select count(*) from acs_activity_object_map + where activity_id = ' || insert_instances__activity_id, + 'select 1 from dual' ); -- Create a null event for test of existence functions @@ -191,7 +206,7 @@ null, null, null, - ''acs_event'', + 'acs_event', now(), null, null, @@ -201,30 +216,30 @@ -- Do some testing while we are here - PERFORM ut_assert__eq (''Test of INSTANCES_EXIST_P f within INSERT_INSTANCES'', + PERFORM ut_assert__eq ('Test of INSTANCES_EXIST_P f within INSERT_INSTANCES', acs_event__instances_exist_p(insert_instances__recurrence_id), - ''f'' + 'f' ); insert into ut_acs_events (event_id) values (insert_instances__event_id); - PERFORM ut_assert__eqtable (''Test of NEW within INSERT_INSTANCES'', - ''ut_acs_events'', - ''acs_events'' + PERFORM ut_assert__eqtable ('Test of NEW within INSERT_INSTANCES', + 'ut_acs_events', + 'acs_events' ); - PERFORM ut_assert__isnull (''Test of GET_NAME null within INSERT_INSTANCES'', + PERFORM ut_assert__isnull ('Test of GET_NAME null within INSERT_INSTANCES', acs_event__get_name(insert_instances__event_id) ); - PERFORM ut_assert__isnull (''Test of GET_DESCRIPTION null within INSERT_INSTANCES'', + PERFORM ut_assert__isnull ('Test of GET_DESCRIPTION null within INSERT_INSTANCES', acs_event__get_description(insert_instances__event_id) ); - PERFORM ut_assert__eq (''Test of RECURS_P f within INSERT_INSTANCES'', + PERFORM ut_assert__eq ('Test of RECURS_P f within INSERT_INSTANCES', acs_event__recurs_p(insert_instances__event_id), - ''f'' + 'f' ); @@ -245,54 +260,54 @@ where event_id = insert_instances__event_id; -- Check if functions performed accordingly - PERFORM ut_assert__eqtable (''Test of SET procedures within INSERT_INSTANCES'', - ''ut_acs_events'', - ''acs_events'' + PERFORM ut_assert__eqtable ('Test of SET procedures within INSERT_INSTANCES', + 'ut_acs_events', + 'acs_events' ); -- If so, we should now be able to get the activity name - PERFORM ut_assert__eq (''Test of GET_NAME from activity within INSERT_INSTANCES'', + PERFORM ut_assert__eq ('Test of GET_NAME from activity within INSERT_INSTANCES', acs_event__get_name(insert_instances__event_id), - ''Testing'' + 'Testing' ); -- and the description - PERFORM ut_assert__eq (''Test of GET_DESCRIPTION from activity within INSERT_INSTANCES'', + PERFORM ut_assert__eq ('Test of GET_DESCRIPTION from activity within INSERT_INSTANCES', acs_event__get_description(insert_instances__event_id), - ''Making sure the acs_activity code works'' + 'Making sure the acs_activity code works' ); -- More testing of acs-events value insertion update acs_events - set name = ''Further Testing'', - description = ''Making sure the code works correctly.'' + set name = 'Further Testing', + description = 'Making sure the code works correctly.' where event_id = insert_instances__event_id; - PERFORM ut_assert__eq (''Test of GET_NAME from event within INSERT_INSTANCES'', + PERFORM ut_assert__eq ('Test of GET_NAME from event within INSERT_INSTANCES', acs_event__get_name(insert_instances__event_id), - ''Further Testing'' + 'Further Testing' ); - PERFORM ut_assert__eq (''Test of GET_DESCRIPTION from event within INSERT_INSTANCES'', + PERFORM ut_assert__eq ('Test of GET_DESCRIPTION from event within INSERT_INSTANCES', acs_event__get_description(insert_instances__event_id), - ''Making sure the code works correctly.'' + 'Making sure the code works correctly.' ); -- Insert instances PERFORM acs_event__insert_instances (insert_instances__event_id, - timestamptz ''2000-06-02'' + timestamptz '2000-06-02' ); -- Test for instances - PERFORM ut_assert__eq (''Test of RECURS_P t within INSERT_INSTANCES'', + PERFORM ut_assert__eq ('Test of RECURS_P t within INSERT_INSTANCES', acs_event__recurs_p(insert_instances__event_id), - ''t'' + 't' ); - PERFORM ut_assert__eq (''Test of INSTANCES_EXIST_P t within INSERT_INSTANCES'', + PERFORM ut_assert__eq ('Test of INSTANCES_EXIST_P t within INSERT_INSTANCES', acs_event__instances_exist_p(insert_instances__recurrence_id), - ''t'' + 't' ); @@ -303,118 +318,118 @@ from acs_events where recurrence_id = insert_instances__recurrence_id; - raise notice ''Instances: %'',v_instance_count; + raise notice 'Instances: %',v_instance_count; - PERFORM ut_assert__eqquery (''Test count of instances in INSERT_INSTANCES'', - ''select count(*) from acs_events - where recurrence_id = '' || insert_instances__recurrence_id, - ''select 9 from dual'' + PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES', + 'select count(*) from acs_events + where recurrence_id = ' || insert_instances__recurrence_id, + 'select 9 from dual' ); -- Check that instances match except for dates - PERFORM ut_assert__eqquery (''Test instances in INSERT_INSTANCES'', - ''select count(*) from (select name, description, activity_id + PERFORM ut_assert__eqquery ('Test instances in INSERT_INSTANCES', + 'select count(*) from (select name, description, activity_id from acs_events - where recurrence_id = '' || + where recurrence_id = ' || insert_instances__recurrence_id || - '' group by name, description, activity_id) as temp'', - ''select 1 from dual'' + ' group by name, description, activity_id) as temp', + 'select 1 from dual' ); ---------------------------------------------------------------------------------------------------- -- Check date recurrence by the week -- Just print them out and eyeball them for now. - raise notice ''Check of recurrence: same day of the week (Mon and Wed), every week ''; - raise notice ''Do not forget DST starts on first Sunday in April and ends last Sunday in October.''; + raise notice 'Check of recurrence: same day of the week (Mon and Wed), every week '; + raise notice 'Do not forget DST starts on first Sunday in April and ends last Sunday in October.'; for rec_events in select * from acs_events_dates where recurrence_id = insert_instances__recurrence_id loop - raise notice '' % : % through %'',rec_events.name, + raise notice ' % : % through %',rec_events.name, rec_events.start_date,rec_events.end_date; end loop; -- Another test of weekly recurrence - insert_instances__timespan_id := timespan__new(timestamptz ''2001-10-21 09:00:00'', - timestamptz ''2001-10-23 10:00:00''); + insert_instances__timespan_id := timespan__new(timestamptz '2001-10-21 09:00:00', + timestamptz '2001-10-23 10:00:00'); -- Check month by date (recur for the same date of the month specified in time interval) - insert_instances__recurrence_id := recurrence__new(''week'', + insert_instances__recurrence_id := recurrence__new('week', 1, - ''4 6'', - to_date(''2001-12-01'',''YYYY-MM-DD''), + '4 6', + to_date('2001-12-01','YYYY-MM-DD'), null); - insert_instances__event_id := acs_event__new(null,''Weekly'',null, null, null, + insert_instances__event_id := acs_event__new(null,'Weekly',null, null, null, insert_instances__timespan_id, insert_instances__activity_id, insert_instances__recurrence_id, - ''acs_event'',now(),null,null,null + 'acs_event',now(),null,null,null ); PERFORM acs_event__insert_instances (insert_instances__event_id, - timestamptz ''2001-12-25'' + timestamptz '2001-12-25' ); -- There should be 13 instances of the weekly event - PERFORM ut_assert__eqquery (''Test count of instances in INSERT_INSTANCES'', - ''select count(*) from acs_events - where recurrence_id = '' || insert_instances__recurrence_id, - ''select 13 from dual'' + PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES', + 'select count(*) from acs_events + where recurrence_id = ' || insert_instances__recurrence_id, + 'select 13 from dual' ); - raise notice ''Check of recurrence: same day of the week (Thursday and Saturday), every week ''; - raise notice ''Do not forget DST starts on first Sunday in April and ends last Sunday in October.''; + raise notice 'Check of recurrence: same day of the week (Thursday and Saturday), every week '; + raise notice 'Do not forget DST starts on first Sunday in April and ends last Sunday in October.'; for rec_events in select * from acs_events_dates where recurrence_id = insert_instances__recurrence_id loop - raise notice '' % : % through % '',rec_events.name, + raise notice ' % : % through % ',rec_events.name, rec_events.start_date,rec_events.end_date; end loop; ---------------------------------------------------------------------------------------------------------- -- Test month_by_date recurrence - insert_instances__timespan_id := timespan__new(timestamptz ''2001-03-21 09:00:00'', - timestamptz ''2001-03-23 10:00:00''); + insert_instances__timespan_id := timespan__new(timestamptz '2001-03-21 09:00:00', + timestamptz '2001-03-23 10:00:00'); -- Check month by date (recur for the same date of the month specified in time interval) - insert_instances__recurrence_id := recurrence__new(''month_by_date'', + insert_instances__recurrence_id := recurrence__new('month_by_date', 1, null, -- irrelevant - to_date(''2001-05-01'',''YYYY-MM-DD''), + to_date('2001-05-01','YYYY-MM-DD'), null); - insert_instances__event_id := acs_event__new(null,''month_by_date'',null, null, null, + insert_instances__event_id := acs_event__new(null,'month_by_date',null, null, null, insert_instances__timespan_id, insert_instances__activity_id, insert_instances__recurrence_id, - ''acs_event'',now(),null,null,null + 'acs_event',now(),null,null,null ); PERFORM acs_event__insert_instances (insert_instances__event_id, - timestamptz ''2001-04-25 00:00:00'' + timestamptz '2001-04-25 00:00:00' ); -- There should be two instances (including the original), even if the cut-off date is between -- the last event. - PERFORM ut_assert__eqquery (''Test count of instances in INSERT_INSTANCES'', - ''select count(*) from acs_events - where recurrence_id = '' || insert_instances__recurrence_id, - ''select 2 from dual'' + PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES', + 'select count(*) from acs_events + where recurrence_id = ' || insert_instances__recurrence_id, + 'select 2 from dual' ); @@ -426,39 +441,39 @@ select * from acs_events_dates where recurrence_id = insert_instances__recurrence_id loop - raise notice '' % : % through % '',rec_events.name, rec_events.start_date,rec_events.end_date; + raise notice ' % : % through % ',rec_events.name, rec_events.start_date,rec_events.end_date; end loop; -- Test month_by_date recurrence - insert_instances__timespan_id := timespan__new(timestamptz ''2001-10-21 09:00:00'', - timestamptz ''2001-10-23 10:00:00''); + insert_instances__timespan_id := timespan__new(timestamptz '2001-10-21 09:00:00', + timestamptz '2001-10-23 10:00:00'); -- Check month by date (recur for the same date of the month specified in time interval) - insert_instances__recurrence_id := recurrence__new(''month_by_date'', + insert_instances__recurrence_id := recurrence__new('month_by_date', 1, null, -- irrelevant - to_date(''2002-02-01'',''YYYY-MM-DD''), + to_date('2002-02-01','YYYY-MM-DD'), null); - insert_instances__event_id := acs_event__new(null,''month_by_date'',null, null, null, + insert_instances__event_id := acs_event__new(null,'month_by_date',null, null, null, insert_instances__timespan_id, insert_instances__activity_id, insert_instances__recurrence_id, - ''acs_event'',now(),null,null,null + 'acs_event',now(),null,null,null ); PERFORM acs_event__insert_instances (insert_instances__event_id, - timestamptz ''2002-04-25 00:00:00'' + timestamptz '2002-04-25 00:00:00' ); -- There should be four instances (including the original), even if the cut-off date is between -- the last event. - PERFORM ut_assert__eqquery (''Test count of instances in INSERT_INSTANCES'', - ''select count(*) from acs_events - where recurrence_id = '' || insert_instances__recurrence_id, - ''select 4 from dual'' + PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES', + 'select count(*) from acs_events + where recurrence_id = ' || insert_instances__recurrence_id, + 'select 4 from dual' ); @@ -469,7 +484,7 @@ select * from acs_events_dates where recurrence_id = insert_instances__recurrence_id loop - raise notice '' % : % through % '',rec_events.name, rec_events.start_date,rec_events.end_date; + raise notice ' % : % through % ',rec_events.name, rec_events.start_date,rec_events.end_date; end loop; @@ -480,39 +495,39 @@ -- Check another recurrence type (daily recurrence) -- First, we need a new timespan,recurrence and activity - insert_instances__timespan_id := timespan__new(timestamptz ''2001-03-26 09:00:00'', - timestamptz ''2001-03-26 10:00:00''); + insert_instances__timespan_id := timespan__new(timestamptz '2001-03-26 09:00:00', + timestamptz '2001-03-26 10:00:00'); -- Check month by date (recur every day, skip every second interval) - insert_instances__recurrence_id := recurrence__new(''day'', + insert_instances__recurrence_id := recurrence__new('day', 2, -- skip a day null, -- Irrelevant - to_date(''2001-04-13'',''YYYY-MM-DD''), + to_date('2001-04-13','YYYY-MM-DD'), null); - insert_instances__event_id := acs_event__new(null,''every 2 days'',null, null, null, + insert_instances__event_id := acs_event__new(null,'every 2 days',null, null, null, insert_instances__timespan_id, insert_instances__activity_id, insert_instances__recurrence_id, - ''acs_event'',now(),null,null,null + 'acs_event',now(),null,null,null ); -- Cut-off date should have no effect PERFORM acs_event__insert_instances (insert_instances__event_id, - timestamptz ''2001-04-05 00:00:00'' + timestamptz '2001-04-05 00:00:00' ); -- There should be six instances (including the original) -- JS: Note that 4/01/2001 is the DST switch back date, which is one of the dates in the recurrence. -- JS: The time format that Postres reports is still the DST format, but if we convert to non-DST -- JS: then the time is ok. In particular, Postgres reports 10:00am GMT-4, which converts to -- JS: the expected 9:00 GMT-5 in the non-DST format that should apply on 4/01/2001. - PERFORM ut_assert__eqquery (''Test count of instances in INSERT_INSTANCES'', - ''select count(*) from acs_events - where recurrence_id = '' || insert_instances__recurrence_id, - ''select 6 from dual'' + PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES', + 'select count(*) from acs_events + where recurrence_id = ' || insert_instances__recurrence_id, + 'select 6 from dual' ); @@ -523,44 +538,44 @@ select * from acs_events_dates where recurrence_id = insert_instances__recurrence_id loop - raise notice '' % : % through % (%,%)'',rec_events.name, rec_events.start_date,rec_events.end_date, + raise notice ' % : % through % (%,%)',rec_events.name, rec_events.start_date,rec_events.end_date, rec_events.event_id,rec_events.recurrence_id; end loop; -- Check another recurrence type (daily recurrence) -- First, we need a new timespan,recurrence and activity - insert_instances__timespan_id := timespan__new(timestamptz ''2001-10-26 09:00:00'', - timestamptz ''2001-10-26 10:00:00''); + insert_instances__timespan_id := timespan__new(timestamptz '2001-10-26 09:00:00', + timestamptz '2001-10-26 10:00:00'); -- Check month by date (recur every day, skip every second interval) - insert_instances__recurrence_id := recurrence__new(''day'', + insert_instances__recurrence_id := recurrence__new('day', 2, null, -- Irrelevant - to_date(''2001-11-13'',''YYYY-MM-DD''), + to_date('2001-11-13','YYYY-MM-DD'), null); - insert_instances__event_id := acs_event__new(null,''every 2 days'',null, null, null, + insert_instances__event_id := acs_event__new(null,'every 2 days',null, null, null, insert_instances__timespan_id, insert_instances__activity_id, insert_instances__recurrence_id, - ''acs_event'',now(),null,null,null + 'acs_event',now(),null,null,null ); -- Cut-off date should have no effect PERFORM acs_event__insert_instances (insert_instances__event_id, - timestamptz ''2001-11-05 00:00:00'' + timestamptz '2001-11-05 00:00:00' ); -- There should be five instances (including the original) -- JS: roblem here. The recurrence includes 10/28/2001, which is the switchover to -- JS: DST in the US. - PERFORM ut_assert__eqquery (''Test count of instances in INSERT_INSTANCES'', - ''select count(*) from acs_events - where recurrence_id = '' || insert_instances__recurrence_id, - ''select 6 from dual'' + PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES', + 'select count(*) from acs_events + where recurrence_id = ' || insert_instances__recurrence_id, + 'select 6 from dual' ); @@ -571,7 +586,7 @@ select * from acs_events_dates where recurrence_id = insert_instances__recurrence_id loop - raise notice '' % : % through % (%,%)'',rec_events.name, rec_events.start_date,rec_events.end_date, + raise notice ' % : % through % (%,%)',rec_events.name, rec_events.start_date,rec_events.end_date, rec_events.event_id,rec_events.recurrence_id; end loop; @@ -580,78 +595,78 @@ -- Check another recurrence type (same date every year) -- First, we need a new timespan,recurrence and activity - insert_instances__timespan_id := timespan__new(timestamptz ''2001-04-01 09:00:00'', - timestamptz ''2001-04-01 10:00:00''); + insert_instances__timespan_id := timespan__new(timestamptz '2001-04-01 09:00:00', + timestamptz '2001-04-01 10:00:00'); -- Check month by date (recur every day, skip every second interval) - insert_instances__recurrence_id := recurrence__new(''year'', + insert_instances__recurrence_id := recurrence__new('year', 1, null, -- Irrelevant - to_date(''2002-04-10'',''YYYY-MM-DD''), + to_date('2002-04-10','YYYY-MM-DD'), null); - insert_instances__event_id := acs_event__new(null,''yearly (one DST day)'',null, null, null, + insert_instances__event_id := acs_event__new(null,'yearly (one DST day)',null, null, null, insert_instances__timespan_id, insert_instances__activity_id, insert_instances__recurrence_id, - ''acs_event'',now(),null,null,null + 'acs_event',now(),null,null,null ); -- Cut-off date should have no effect PERFORM acs_event__insert_instances (insert_instances__event_id, - timestamptz ''2002-04-05 00:00:00'' + timestamptz '2002-04-05 00:00:00' ); -- There should be two instance (including the original). - PERFORM ut_assert__eqquery (''Test count of instances in INSERT_INSTANCES'', - ''select count(*) from acs_events - where recurrence_id = '' || insert_instances__recurrence_id, - ''select 2 from dual'' + PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES', + 'select count(*) from acs_events + where recurrence_id = ' || insert_instances__recurrence_id, + 'select 2 from dual' ); for rec_events in select * from acs_events_dates where recurrence_id = insert_instances__recurrence_id loop - raise notice '' % : % through % (%,%)'',rec_events.name, rec_events.start_date,rec_events.end_date, + raise notice ' % : % through % (%,%)',rec_events.name, rec_events.start_date,rec_events.end_date, rec_events.event_id,rec_events.recurrence_id; end loop; -- Check another recurrence type (same date every year) -- First, we need a new timespan,recurrence and activity - insert_instances__timespan_id := timespan__new(timestamptz ''2001-04-03 09:00:00'', - timestamptz ''2001-04-03 10:00:00''); + insert_instances__timespan_id := timespan__new(timestamptz '2001-04-03 09:00:00', + timestamptz '2001-04-03 10:00:00'); -- Check month by date (recur every day, skip every second interval) - insert_instances__recurrence_id := recurrence__new(''year'', + insert_instances__recurrence_id := recurrence__new('year', 1, null, -- Irrelevant - to_date(''2002-04-10'',''YYYY-MM-DD''), + to_date('2002-04-10','YYYY-MM-DD'), null); - insert_instances__event_id := acs_event__new(null,''yearly (non-DST)'',null, null, null, + insert_instances__event_id := acs_event__new(null,'yearly (non-DST)',null, null, null, insert_instances__timespan_id, insert_instances__activity_id, insert_instances__recurrence_id, - ''acs_event'',now(),null,null,null + 'acs_event',now(),null,null,null ); -- Cut-off date should have no effect PERFORM acs_event__insert_instances (insert_instances__event_id, - timestamptz ''2002-04-05 00:00:00'' + timestamptz '2002-04-05 00:00:00' ); -- There should be two instance (including the original). - PERFORM ut_assert__eqquery (''Test count of instances in INSERT_INSTANCES'', - ''select count(*) from acs_events - where recurrence_id = '' || insert_instances__recurrence_id, - ''select 2 from dual'' + PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES', + 'select count(*) from acs_events + where recurrence_id = ' || insert_instances__recurrence_id, + 'select 2 from dual' ); @@ -660,236 +675,236 @@ select * from acs_events_dates where recurrence_id = insert_instances__recurrence_id loop - raise notice '' % : % through % (%,%)'',rec_events.name, rec_events.start_date,rec_events.end_date, + raise notice ' % : % through % (%,%)',rec_events.name, rec_events.start_date,rec_events.end_date, rec_events.event_id,rec_events.recurrence_id; end loop; -- Check another recurrence type (same date every year) -- First, we need a new timespan,recurrence and activity - insert_instances__timespan_id := timespan__new(timestamptz ''2001-10-28 09:00:00'', - timestamptz ''2001-10-28 10:00:00''); + insert_instances__timespan_id := timespan__new(timestamptz '2001-10-28 09:00:00', + timestamptz '2001-10-28 10:00:00'); -- Check month by date (recur every day, skip every second interval) - insert_instances__recurrence_id := recurrence__new(''year'', + insert_instances__recurrence_id := recurrence__new('year', 1, null, -- Irrelevant - to_date(''2002-10-30'',''YYYY-MM-DD''), + to_date('2002-10-30','YYYY-MM-DD'), null); - insert_instances__event_id := acs_event__new(null,''yearly (DST)'',null, null, null, + insert_instances__event_id := acs_event__new(null,'yearly (DST)',null, null, null, insert_instances__timespan_id, insert_instances__activity_id, insert_instances__recurrence_id, - ''acs_event'',now(),null,null,null + 'acs_event',now(),null,null,null ); -- Cut-off date should have no effect PERFORM acs_event__insert_instances (insert_instances__event_id, - timestamptz ''2002-10-30 00:00:00'' + timestamptz '2002-10-30 00:00:00' ); -- There should be two instance (including the original). - PERFORM ut_assert__eqquery (''Test count of instances in INSERT_INSTANCES'', - ''select count(*) from acs_events - where recurrence_id = '' || insert_instances__recurrence_id, - ''select 2 from dual'' + PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES', + 'select count(*) from acs_events + where recurrence_id = ' || insert_instances__recurrence_id, + 'select 2 from dual' ); for rec_events in select * from acs_events_dates where recurrence_id = insert_instances__recurrence_id loop - raise notice '' % : % through % (%,%)'',rec_events.name, rec_events.start_date,rec_events.end_date, + raise notice ' % : % through % (%,%)',rec_events.name, rec_events.start_date,rec_events.end_date, rec_events.event_id,rec_events.recurrence_id; end loop; ---------------------------------------------------------------------------------------------------------- -- First, we need a new timespan,recurrence and activity - insert_instances__timespan_id := timespan__new(timestamptz ''2001-02-06 09:00:00'', - timestamptz ''2001-02-07 10:00:00''); + insert_instances__timespan_id := timespan__new(timestamptz '2001-02-06 09:00:00', + timestamptz '2001-02-07 10:00:00'); - insert_instances__recurrence_id := recurrence__new(''last_of_month'', + insert_instances__recurrence_id := recurrence__new('last_of_month', 1, null, -- Irrelevant - to_date(''2001-12-10'',''YYYY-MM-DD''), + to_date('2001-12-10','YYYY-MM-DD'), null); - insert_instances__event_id := acs_event__new(null,''last_of_month'',null, null, null, + insert_instances__event_id := acs_event__new(null,'last_of_month',null, null, null, insert_instances__timespan_id, insert_instances__activity_id, insert_instances__recurrence_id, - ''acs_event'',now(),null,null,null + 'acs_event',now(),null,null,null ); -- Cut-off date should have no effect PERFORM acs_event__insert_instances (insert_instances__event_id, - timestamptz ''2001-12-10 00:00:00'' + timestamptz '2001-12-10 00:00:00' ); -- There should be three instances (including the original). - PERFORM ut_assert__eqquery (''Test count of instances in INSERT_INSTANCES'', - ''select count(*) from acs_events - where recurrence_id = '' || insert_instances__recurrence_id, - ''select 10 from dual'' + PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES', + 'select count(*) from acs_events + where recurrence_id = ' || insert_instances__recurrence_id, + 'select 10 from dual' ); -- Check dates -- Just print them out and eyeball them for now. - raise notice ''Check of recurrence: every end of the month, same day as event, starting next month.''; + raise notice 'Check of recurrence: every end of the month, same day as event, starting next month.'; for rec_events in select * from acs_events_dates where recurrence_id = insert_instances__recurrence_id loop - raise notice '' % : % through % '',rec_events.name, rec_events.start_date,rec_events.end_date; + raise notice ' % : % through % ',rec_events.name, rec_events.start_date,rec_events.end_date; end loop; -- First, we need a new timespan,recurrence and activity - insert_instances__timespan_id := timespan__new(timestamptz ''2001-08-06 09:00:00'', - timestamptz ''2001-08-07 10:00:00''); + insert_instances__timespan_id := timespan__new(timestamptz '2001-08-06 09:00:00', + timestamptz '2001-08-07 10:00:00'); - insert_instances__recurrence_id := recurrence__new(''last_of_month'', + insert_instances__recurrence_id := recurrence__new('last_of_month', 1, null, -- Irrelevant - to_date(''2002-05-10'',''YYYY-MM-DD''), + to_date('2002-05-10','YYYY-MM-DD'), null); - insert_instances__event_id := acs_event__new(null,''last_of_month'',null, null, null, + insert_instances__event_id := acs_event__new(null,'last_of_month',null, null, null, insert_instances__timespan_id, insert_instances__activity_id, insert_instances__recurrence_id, - ''acs_event'',now(),null,null,null + 'acs_event',now(),null,null,null ); -- Cut-off date should have no effect PERFORM acs_event__insert_instances (insert_instances__event_id, - timestamptz ''2002-05-20 00:00:00'' + timestamptz '2002-05-20 00:00:00' ); -- There should be three instances (including the original). - PERFORM ut_assert__eqquery (''Test count of instances in INSERT_INSTANCES'', - ''select count(*) from acs_events - where recurrence_id = '' || insert_instances__recurrence_id, - ''select 9 from dual'' + PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES', + 'select count(*) from acs_events + where recurrence_id = ' || insert_instances__recurrence_id, + 'select 9 from dual' ); -- Check dates -- Just print them out and eyeball them for now. - raise notice ''Check of recurrence: every end of the month, same day as event, starting next month.''; + raise notice 'Check of recurrence: every end of the month, same day as event, starting next month.'; for rec_events in select * from acs_events_dates where recurrence_id = insert_instances__recurrence_id loop - raise notice '' % : % through % '',rec_events.name, rec_events.start_date,rec_events.end_date; + raise notice ' % : % through % ',rec_events.name, rec_events.start_date,rec_events.end_date; end loop; ---------------------------------------------------------------------------------------------------------- -- First, we need a new timespan,recurrence and activity - insert_instances__timespan_id := timespan__new(timestamptz ''2001-08-06 09:00:00'', - timestamptz ''2001-08-07 10:00:00''); + insert_instances__timespan_id := timespan__new(timestamptz '2001-08-06 09:00:00', + timestamptz '2001-08-07 10:00:00'); - insert_instances__recurrence_id := recurrence__new(''custom'', + insert_instances__recurrence_id := recurrence__new('custom', 1, null, -- Irrelevant - to_date(''2001-08-20'',''YYYY-MM-DD''), - ''recur_every3''); + to_date('2001-08-20','YYYY-MM-DD'), + 'recur_every3'); - insert_instances__event_id := acs_event__new(null,''custom'',null, null, null, + insert_instances__event_id := acs_event__new(null,'custom',null, null, null, insert_instances__timespan_id, insert_instances__activity_id, insert_instances__recurrence_id, - ''acs_event'',now(),null,null,null + 'acs_event',now(),null,null,null ); -- Cut-off date should have no effect PERFORM acs_event__insert_instances (insert_instances__event_id, - timestamptz ''2001-08-30 00:00:00'' + timestamptz '2001-08-30 00:00:00' ); -- There should be three instances (including the original). - PERFORM ut_assert__eqquery (''Test count of instances in INSERT_INSTANCES'', - ''select count(*) from acs_events - where recurrence_id = '' || insert_instances__recurrence_id, - ''select 5 from dual'' + PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES', + 'select count(*) from acs_events + where recurrence_id = ' || insert_instances__recurrence_id, + 'select 5 from dual' ); -- Check dates -- Just print them out and eyeball them for now. - raise notice ''Check of recurrence: custom''; + raise notice 'Check of recurrence: custom'; for rec_events in select * from acs_events_dates where recurrence_id = insert_instances__recurrence_id loop - raise notice '' % : % through % '',rec_events.name, rec_events.start_date,rec_events.end_date; + raise notice ' % : % through % ',rec_events.name, rec_events.start_date,rec_events.end_date; end loop; ---------------------------------------------------------------------------------------------------------- -- First, we need a new timespan,recurrence and activity - insert_instances__timespan_id := timespan__new(timestamptz ''2001-02-06 09:00:00'', - timestamptz ''2001-02-07 10:00:00''); + insert_instances__timespan_id := timespan__new(timestamptz '2001-02-06 09:00:00', + timestamptz '2001-02-07 10:00:00'); - insert_instances__recurrence_id := recurrence__new(''month_by_day'', + insert_instances__recurrence_id := recurrence__new('month_by_day', 1, null, -- Irrelevant - to_date(''2001-12-10'',''YYYY-MM-DD''), + to_date('2001-12-10','YYYY-MM-DD'), null); - insert_instances__event_id := acs_event__new(null,''month_by_day'',null, null, null, + insert_instances__event_id := acs_event__new(null,'month_by_day',null, null, null, insert_instances__timespan_id, insert_instances__activity_id, insert_instances__recurrence_id, - ''acs_event'',now(),null,null,null + 'acs_event',now(),null,null,null ); -- Cut-off date should have no effect PERFORM acs_event__insert_instances (insert_instances__event_id, - timestamptz ''2001-12-20 00:00:00'' + timestamptz '2001-12-20 00:00:00' ); -- There should be three instances (including the original). - PERFORM ut_assert__eqquery (''Test count of instances in INSERT_INSTANCES'', - ''select count(*) from acs_events - where recurrence_id = '' || insert_instances__recurrence_id, - ''select 11 from dual'' + PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES', + 'select count(*) from acs_events + where recurrence_id = ' || insert_instances__recurrence_id, + 'select 11 from dual' ); -- Check dates -- Just print them out and eyeball them for now. - raise notice ''Check of recurrence: every month, same week and day of the month''; + raise notice 'Check of recurrence: every month, same week and day of the month'; for rec_events in select * from acs_events_dates where recurrence_id = insert_instances__recurrence_id loop - raise notice '' % : % through % (%,%)'',rec_events.name, rec_events.start_date,rec_events.end_date, + raise notice ' % : % through % (%,%)',rec_events.name, rec_events.start_date,rec_events.end_date, rec_events.event_id,rec_events.recurrence_id; end loop; @@ -900,73 +915,73 @@ -- Let us eyeball for now. - raise notice ''Test of shift: after shift of start date by one day, end date by three days.''; + raise notice 'Test of shift: after shift of start date by one day, end date by three days.'; for rec_events in select * from acs_events_dates where recurrence_id = insert_instances__recurrence_id loop - raise notice '' % : % through % '',rec_events.name, rec_events.start_date,rec_events.end_date; + raise notice ' % : % through % ',rec_events.name, rec_events.start_date,rec_events.end_date; end loop; ---------------------------------------------------------------------------------------------------------- -- Timespan to shift - insert_instances__timespan_id := timespan__new(timestamptz ''2001-02-06 09:00:00'', - timestamptz ''2001-02-07 10:00:00''); + insert_instances__timespan_id := timespan__new(timestamptz '2001-02-06 09:00:00', + timestamptz '2001-02-07 10:00:00'); -- Insert one recurrence so that recurrence__delete will have something to delete (since recurrences -- are deleted if associated with an event). - insert_instances__recurrence_id := recurrence__new(''month_by_day'', + insert_instances__recurrence_id := recurrence__new('month_by_day', 1, null, -- Irrelevant - to_date(''2000-06-01'',''YYYY-MM-DD''), + to_date('2000-06-01','YYYY-MM-DD'), null ); -- Insert two non-recurring event to test acs_event__delete, using acs_event__new alone - PERFORM acs_event__new(null,null,null,null,null,null,null,null,''acs_event'',now(),null,null,null); - insert_instances__event_id := acs_event__new(null,''Another event'',''Yet another event description'', null, null, - insert_instances__timespan_id,null,null,''acs_event'',now(),null,null,null); + PERFORM acs_event__new(null,null,null,null,null,null,null,null,'acs_event',now(),null,null,null); + insert_instances__event_id := acs_event__new(null,'Another event','Yet another event description', null, null, + insert_instances__timespan_id,null,null,'acs_event',now(),null,null,null); -- If so, we should now be able to get the activity name - PERFORM ut_assert__eq (''Test of GET_NAME from activity within INSERT_INSTANCES'', + PERFORM ut_assert__eq ('Test of GET_NAME from activity within INSERT_INSTANCES', acs_event__get_name(insert_instances__event_id), - ''Another event'' + 'Another event' ); -- and the description - PERFORM ut_assert__eq (''Test of GET_DESCRIPTION from activity within INSERT_INSTANCES'', + PERFORM ut_assert__eq ('Test of GET_DESCRIPTION from activity within INSERT_INSTANCES', acs_event__get_description(insert_instances__event_id), - ''Yet another event description'' + 'Yet another event description' ); -- Let us eyeball for now. - raise notice ''Test of shift: before''; + raise notice 'Test of shift: before'; for rec_events in select * from acs_events_dates where event_id = insert_instances__event_id loop - raise notice '' % : % through % '',rec_events.name, rec_events.start_date,rec_events.end_date; + raise notice ' % : % through % ',rec_events.name, rec_events.start_date,rec_events.end_date; end loop; -- Let us shift the start date of event by one day, end date by two days PERFORM acs_event__shift(insert_instances__event_id,1,2); -- Let us eyeball for now. - raise notice ''Test of shift: after shift of start date by one day, end date by two days.''; + raise notice 'Test of shift: after shift of start date by one day, end date by two days.'; for rec_events in select * from acs_events_dates where event_id = insert_instances__event_id loop - raise notice '' % : % through % '',rec_events.name, rec_events.start_date,rec_events.end_date; + raise notice ' % : % through % ',rec_events.name, rec_events.start_date,rec_events.end_date; end loop; @@ -977,27 +992,34 @@ -- There should be one entry in the mapping table - PERFORM ut_assert__eqquery (''Test count of party mappings in acs_event_party_map'', - ''select count(*) from acs_event_party_map - where event_id = '' || insert_instances__event_id, - ''select 1 from dual'' + PERFORM ut_assert__eqquery ('Test count of party mappings in acs_event_party_map', + 'select count(*) from acs_event_party_map + where event_id = ' || insert_instances__event_id, + 'select 1 from dual' ); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut__delete_instances() -returns integer as ' -declare + + +-- +-- procedure ut__delete_instances/0 +-- +CREATE OR REPLACE FUNCTION ut__delete_instances( + +) RETURNS integer AS $$ +DECLARE rec_timespans record; rec_recurrences record; rec_activities record; rec_events record; v_dummy integer; -begin +BEGIN -- Remember the activity object mapping? Unfortunately, we can only do the unmapping in a -- separate transaction. Since we inserted only one mapping, we expect only one entry. @@ -1009,10 +1031,10 @@ -- There should be no entry in the mapping table - PERFORM ut_assert__eqquery (''Test count of object unmappings in acs_activity_object_map'', - ''select count(*) from acs_activity_object_map - where activity_id = '' || v_dummy, - ''select 0 from dual'' + PERFORM ut_assert__eqquery ('Test count of object unmappings in acs_activity_object_map', + 'select count(*) from acs_activity_object_map + where activity_id = ' || v_dummy, + 'select 0 from dual' ); -- Remember the event-party mapping? Unfortunately, we can only do the unmapping in a @@ -1025,10 +1047,10 @@ -- There should be no entry in the mapping table - PERFORM ut_assert__eqquery (''Test count of party unmappings in acs_event_party_map'', - ''select count(*) from acs_event_party_map - where event_id = '' || v_dummy, - ''select 0 from dual'' + PERFORM ut_assert__eqquery ('Test count of party unmappings in acs_event_party_map', + 'select count(*) from acs_event_party_map + where event_id = ' || v_dummy, + 'select 0 from dual' ); @@ -1045,10 +1067,10 @@ -- This should delete only recurring events PERFORM acs_event__delete_all(rec_events.event_id); - PERFORM ut_assert__eqquery (''Test deletion of events by acs_event__delete_all'', - ''select count(*) from acs_events - where event_id ='' || rec_events.event_id, - ''select 0 from dual'' + PERFORM ut_assert__eqquery ('Test deletion of events by acs_event__delete_all', + 'select count(*) from acs_events + where event_id =' || rec_events.event_id, + 'select 0 from dual' ); END LOOP; @@ -1065,10 +1087,10 @@ -- There should be no entry in the events table with this event_id -- Unlike the test above, there is no deletion of recurrences here. - PERFORM ut_assert__eqquery (''Test deletion of events by acs_event__delete'', - ''select count(*) from acs_events - where event_id ='' || rec_events.event_id, - ''select 0 from dual'' + PERFORM ut_assert__eqquery ('Test deletion of events by acs_event__delete', + 'select count(*) from acs_events + where event_id =' || rec_events.event_id, + 'select 0 from dual' ); END LOOP; @@ -1084,10 +1106,10 @@ PERFORM acs_activity__delete(rec_activities.activity_id); -- There should be no entry in the activities table with this activity_od - PERFORM ut_assert__eqquery (''Test deletion of events by acs_activity__delete'', - ''select count(*) from acs_activities - where activity_id = '' || rec_activities.activity_id, - ''select 0 from dual'' + PERFORM ut_assert__eqquery ('Test deletion of events by acs_activity__delete', + 'select count(*) from acs_activities + where activity_id = ' || rec_activities.activity_id, + 'select 0 from dual' ); END LOOP; @@ -1102,10 +1124,10 @@ PERFORM recurrence__delete(rec_recurrences.recurrence_id); -- There should be no entry in the recurrence table associated with this recurrence_id - PERFORM ut_assert__eqquery (''Test deletion of recurrences by recurrence__delete'', - ''select count(*) from recurrences - where recurrence_id = '' || rec_recurrences.recurrence_id, - ''select 0 from dual'' + PERFORM ut_assert__eqquery ('Test deletion of recurrences by recurrence__delete', + 'select count(*) from recurrences + where recurrence_id = ' || rec_recurrences.recurrence_id, + 'select 0 from dual' ); @@ -1126,7 +1148,8 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- Call the regression test Index: openacs-4/packages/acs-events/sql/postgresql/test/time_intervals-test.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/postgresql/test/time_intervals-test.sql,v diff -u -N -r1.2 -r1.3 --- openacs-4/packages/acs-events/sql/postgresql/test/time_intervals-test.sql 17 May 2003 09:47:26 -0000 1.2 +++ openacs-4/packages/acs-events/sql/postgresql/test/time_intervals-test.sql 30 Mar 2013 13:00:30 -0000 1.3 @@ -11,36 +11,35 @@ \i utest-create.sql -- Set-up the regression test -create function ut__setup() -returns integer as ' -begin +CREATE OR REPLACE FUNCTION ut__setup() RETURNS integer AS $$ +BEGIN - raise notice ''Setting up time_intervals test...''; + raise notice 'Setting up time_intervals test...'; -- create copies of the tables (shadow tables) to verify API operations -- No need for execute here? create table ut_time_intervals as select * from time_intervals; -- For testing purposes, both tables should still be empty - PERFORM ut_assert__eqtable (''Comparing copied data for time interval'', - ''time_intervals'', - ''ut_time_intervals'' + PERFORM ut_assert__eqtable ('Comparing copied data for time interval', + 'time_intervals', + 'ut_time_intervals' ); -- Store keys that are in the table prior to the regresion test create table ut_interval_ids as select interval_id from time_intervals; return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- Clean up the mess that regression testing did -create function ut__teardown() -returns integer as ' -begin +CREATE OR REPLACE FUNCTION ut__teardown() RETURNS integer AS $$ +BEGIN - raise notice ''Tearing down time_intervals test...''; + raise notice 'Tearing down time_intervals test...'; -- Delete intervals added by tests -- cascade delete in timespans should delete corresponding entries in that table @@ -58,62 +57,75 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- Postgres has this weird behavior that you cannot change a row twice -- within a transaction. -- We test the creation of a time interval entry -create function ut__new( - timestamptz, - timestamptz -) -returns integer as ' -declare - new__date1 alias for $1; - new__date2 alias for $2; + + +-- added +select define_function_args('ut__new','date1,date2'); + +-- +-- procedure ut__new/2 +-- +CREATE OR REPLACE FUNCTION ut__new( + new__date1 timestamptz, + new__date2 timestamptz +) RETURNS integer AS $$ +DECLARE new__interval_id time_intervals.interval_id%TYPE; v_result integer; -begin +BEGIN - raise notice ''Testing time_interval__new...''; + raise notice 'Testing time_interval__new...'; -- create a time interval, and check if entry is made - v_result := ut_assert__isnotnull (''Creating a new test time interval:'', + v_result := ut_assert__isnotnull ('Creating a new test time interval:', time_interval__new(new__date1, new__date2) ); -- Verify that the API does the correct insert by manually entering -- an entry in the shadow table -- Note that we did not port the currval in the timepsan_seq view - select currval(''timespan_sequence'') into new__interval_id; + select currval('timespan_sequence') into new__interval_id; insert into ut_time_intervals(interval_id, start_date, end_date) values(new__interval_id, new__date1, new__date2); - PERFORM ut_assert__eqtable (''Comparing created data for time interval :'', - ''time_intervals'', - ''ut_time_intervals'' + PERFORM ut_assert__eqtable ('Comparing created data for time interval :', + 'time_intervals', + 'ut_time_intervals' ); -- If successful, interval id is correct return new__interval_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- Check the deletion of a time interval -create function ut__delete( - integer -) -returns integer as ' -declare - delete__interval_id alias for $1; -begin - raise notice ''Testing time interval delete...''; +-- added +select define_function_args('ut__delete','interval_id'); + +-- +-- procedure ut__delete/1 +-- +CREATE OR REPLACE FUNCTION ut__delete( + delete__interval_id integer +) RETURNS integer AS $$ +DECLARE +BEGIN + + raise notice 'Testing time interval delete...'; + -- Delete entry from shadow table delete from ut_time_intervals where interval_id = delete__interval_id; @@ -123,29 +135,34 @@ -- Verify time interval not there. - PERFORM ut_assert__eqtable (''Delete verification'', - ''ut_time_intervals'', - ''time_intervals'' + PERFORM ut_assert__eqtable ('Delete verification', + 'ut_time_intervals', + 'time_intervals' ); -- If successful, interval id is correct return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut__edit ( - integer, -- time_intervals.interval_id%TYPE; - timestamptz, - timestamptz -) -returns integer as ' -declare - edit__interval_id alias for $1; - edit__start_date alias for $2; - edit__end_date alias for $3; -begin - raise notice ''Testing time_interval__edit...''; + +-- added +select define_function_args('ut__edit','interval_id,start_date,end_date'); + +-- +-- procedure ut__edit/3 +-- +CREATE OR REPLACE FUNCTION ut__edit( + edit__interval_id integer, + edit__start_date timestamptz, + edit__end_date timestamptz +) RETURNS integer AS $$ +DECLARE +BEGIN + raise notice 'Testing time_interval__edit...'; + -- Edit the time interval PERFORM time_interval__edit(edit__interval_id,edit__start_date,edit__end_date); @@ -172,56 +189,63 @@ where interval_id = edit__interval_id; end if; - PERFORM ut_assert__eqtable (''Comparing edited data for time interval'', - ''time_intervals'', - ''ut_time_intervals'' + PERFORM ut_assert__eqtable ('Comparing edited data for time interval', + 'time_intervals', + 'ut_time_intervals' ); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut__eq( - varchar, - integer, - integer, - boolean -) -returns integer as ' -declare - eq__msg alias for $1; - eq__interval_id_1 alias for $2; - eq__interval_id_2 alias for $3; - eq__result alias for $4; -begin +-- added +select define_function_args('ut__eq','msg,interval_id_1,interval_id_2,result'); + +-- +-- procedure ut__eq/4 +-- +CREATE OR REPLACE FUNCTION ut__eq( + eq__msg varchar, + eq__interval_id_1 integer, + eq__interval_id_2 integer, + eq__result boolean +) RETURNS integer AS $$ +DECLARE + +BEGIN + PERFORM ut_assert__eq (eq__msg, time_interval__eq(eq__interval_id_1, eq__interval_id_2), eq__result ); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut__shift ( - integer, -- in time_intervals.interval_id%TYPE; - integer, - integer, - timestamptz, - timestamptz -) -returns integer as ' -declare - shift__interval_id alias for $1; - shift__offset_1 alias for $2; - shift__offset_2 alias for $3; - shift__date1 alias for $4; - shift__date2 alias for $5; -begin - raise notice ''Testing shift...''; + +-- added +select define_function_args('ut__shift','interval_id,offset_1,offset_2,date1,date2'); + +-- +-- procedure ut__shift/5 +-- +CREATE OR REPLACE FUNCTION ut__shift( + shift__interval_id integer, + shift__offset_1 integer, + shift__offset_2 integer, + shift__date1 timestamptz, + shift__date2 timestamptz +) RETURNS integer AS $$ +DECLARE +BEGIN + + raise notice 'Testing shift...'; -- Shift the time interval PERFORM time_interval__shift(shift__interval_id, shift__offset_1, shift__offset_2); @@ -232,29 +256,32 @@ end_date = shift__date2 where interval_id = shift__interval_id; - PERFORM ut_assert__eqtable (''Comparing shifted data for time intervals'', - ''time_intervals'', - ''ut_time_intervals'' + PERFORM ut_assert__eqtable ('Comparing shifted data for time intervals', + 'time_intervals', + 'ut_time_intervals' ); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut__overlaps_p( - varchar, - integer, -- time_intervals.interval_id%TYPE; - integer, -- time_intervals.interval_id%TYPE; - boolean -) -returns integer as ' -declare - overlaps_p__msg alias for $1; - overlaps_p__interval_id_1 alias for $2; - overlaps_p__interval_id_2 alias for $3; - overlaps_p__result alias for $4; -begin + + +-- added + +-- +-- procedure ut__overlaps_p/4 +-- +CREATE OR REPLACE FUNCTION ut__overlaps_p( + overlaps_p__msg varchar, + overlaps_p__interval_id_1 integer, + overlaps_p__interval_id_2 integer, + overlaps_p__result boolean +) RETURNS integer AS $$ +DECLARE +BEGIN -- Test the time interval PERFORM ut_assert__eq (overlaps_p__msg, time_interval__overlaps_p(overlaps_p__interval_id_1, overlaps_p__interval_id_2), @@ -263,23 +290,25 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut__overlaps_p( - varchar, - integer, -- time_intervals.interval_id%TYPE; - timestamptz, -- time_intervals.start_date%TYPE; - timestamptz, -- time_intervals.end_date%TYPE; - boolean -) -returns integer as ' -declare - overlaps_p__msg alias for $1; - overlaps_p__interval_id alias for $2; - overlaps_p__start_date alias for $3; - overlaps_p__end_date alias for $4; - overlaps_p__result alias for $5; -begin + + +-- added + +-- +-- procedure ut__overlaps_p/5 +-- +CREATE OR REPLACE FUNCTION ut__overlaps_p( + overlaps_p__msg varchar, + overlaps_p__interval_id integer, + overlaps_p__start_date timestamptz, + overlaps_p__end_date timestamptz, + overlaps_p__result boolean +) RETURNS integer AS $$ +DECLARE +BEGIN -- Test the time interval PERFORM ut_assert__eq (overlaps_p__msg, time_interval__overlaps_p(overlaps_p__interval_id, @@ -290,25 +319,27 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut__overlaps_p( - varchar, - timestamptz, - timestamptz, - timestamptz, -- time_intervals.start_date%TYPE; - timestamptz, -- time_intervals.end_date%TYPE; - boolean -) -returns integer as ' -declare - overlaps_p__msg alias for $1; - overlaps_p__start_date_1 alias for $2; - overlaps_p__end_date_1 alias for $3; - overlaps_p__start_date_2 alias for $4; - overlaps_p__end_date_2 alias for $5; - overlaps_p__result alias for $6; -begin + + +-- added +select define_function_args('ut__overlaps_p','msg,start_date_1,end_date_1,start_date_2,end_date_2,result'); + +-- +-- procedure ut__overlaps_p/6 +-- +CREATE OR REPLACE FUNCTION ut__overlaps_p( + overlaps_p__msg varchar, + overlaps_p__start_date_1 timestamptz, + overlaps_p__end_date_1 timestamptz, + overlaps_p__start_date_2 timestamptz, + overlaps_p__end_date_2 timestamptz, + overlaps_p__result boolean +) RETURNS integer AS $$ +DECLARE +BEGIN -- Test the time interval PERFORM ut_assert__eq (overlaps_p__msg, time_interval__overlaps_p(overlaps_p__start_date_1, @@ -320,22 +351,28 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut__copy( - integer, -- time_intervals.interval_id%TYPE; - integer -) -returns integer as ' -declare - copy__interval_id alias for $1; - copy__offset alias for $2; + + +-- added +select define_function_args('ut__copy','interval_id,offset'); + +-- +-- procedure ut__copy/2 +-- +CREATE OR REPLACE FUNCTION ut__copy( + copy__interval_id integer, + copy__offset integer +) RETURNS integer AS $$ +DECLARE v_interval_id time_intervals.interval_id%TYPE; interval_row record; -begin +BEGIN - raise notice ''Testing time_interval__copy...''; + raise notice 'Testing time_interval__copy...'; -- Copy the time interval @@ -351,165 +388,172 @@ values (v_interval_id, interval_row.start_date + copy__offset, interval_row.end_date + copy__offset); -- Verify copies - PERFORM ut_assert__eqtable (''Comparing copied data for time intervals'', - ''time_intervals'', - ''ut_time_intervals'' + PERFORM ut_assert__eqtable ('Comparing copied data for time intervals', + 'time_intervals', + 'ut_time_intervals' ); return v_interval_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut__regression1() -returns integer as ' -declare + + +-- +-- procedure ut__regression1/0 +-- +CREATE OR REPLACE FUNCTION ut__regression1( + +) RETURNS integer AS $$ +DECLARE v_result integer := 0; v_interval_id time_intervals.interval_id%TYPE; v_interval_id_ck time_intervals.interval_id%TYPE; -begin +BEGIN - raise notice ''Regression test, part 1 (creates and edits).''; + raise notice 'Regression test, part 1 (creates and edits).'; -- We first check if the creation of time intervals work - v_interval_id := ut__new(timestamptz ''2001-01-01'',timestamptz ''2001-01-02''); + v_interval_id := ut__new(timestamptz '2001-01-01',timestamptz '2001-01-02'); -- Try to edit, putting new values for start date and end dates - PERFORM ut__edit(v_interval_id,timestamptz ''2001-01-02'',timestamptz ''2001-01-30''); + PERFORM ut__edit(v_interval_id,timestamptz '2001-01-02',timestamptz '2001-01-30'); -- Edit, but this time, change only the start date - PERFORM ut__edit(v_interval_id,timestamptz ''2001-01-07'',null); + PERFORM ut__edit(v_interval_id,timestamptz '2001-01-07',null); -- Edit, but this time, change only the end date - PERFORM ut__edit(v_interval_id,null,timestamptz ''2001-01-08''); + PERFORM ut__edit(v_interval_id,null,timestamptz '2001-01-08'); -- We now test equality of (identical) intervals - PERFORM ut__eq(''Equal (same) intervals'',v_interval_id,v_interval_id,true); + PERFORM ut__eq('Equal (same) intervals',v_interval_id,v_interval_id,true); -- Create another interval for comparison - v_interval_id_ck := ut__new(timestamptz ''2001-01-07'',timestamptz ''2001-01-08''); + v_interval_id_ck := ut__new(timestamptz '2001-01-07',timestamptz '2001-01-08'); -- We now test equality of (nonidentical) intervals - PERFORM ut__eq(''Equal (distinct) intervals'',v_interval_id,v_interval_id_ck,true); + PERFORM ut__eq('Equal (distinct) intervals',v_interval_id,v_interval_id_ck,true); -- Shift the second interval start date by one day, the end date by two days - PERFORM ut__shift(v_interval_id_ck,1,2,timestamptz ''2001-01-08'', timestamptz ''2001-01-10''); + PERFORM ut__shift(v_interval_id_ck,1,2,timestamptz '2001-01-08', timestamptz '2001-01-10'); -- Now test inequality of time intervals - PERFORM ut__eq(''Unequal (distinct) intervals'',v_interval_id,v_interval_id_ck,false); + PERFORM ut__eq('Unequal (distinct) intervals',v_interval_id,v_interval_id_ck,false); -- Shift the second interval start date BACK by one day, the end date same - PERFORM ut__shift(v_interval_id_ck,-1,0,timestamptz ''2001-01-07'', timestamptz ''2001-01-10''); + PERFORM ut__shift(v_interval_id_ck,-1,0,timestamptz '2001-01-07', timestamptz '2001-01-10'); -- Now test inequality of time intervals - PERFORM ut__eq(''Unequal (distinct) intervals: start date equal'',v_interval_id,v_interval_id_ck,false); + PERFORM ut__eq('Unequal (distinct) intervals: start date equal',v_interval_id,v_interval_id_ck,false); -- Shift the second interval, start date same, but the end date BACK by two days - PERFORM ut__shift(v_interval_id_ck,0,-2,timestamptz ''2001-01-07'', timestamptz ''2001-01-08''); + PERFORM ut__shift(v_interval_id_ck,0,-2,timestamptz '2001-01-07', timestamptz '2001-01-08'); -- Should be equal again - PERFORM ut__eq(''Equal again, (distinct) intervals'',v_interval_id,v_interval_id_ck,true); + PERFORM ut__eq('Equal again, (distinct) intervals',v_interval_id,v_interval_id_ck,true); -- For fun, shift start date BACK by two days, the end date BACK by 1 day - PERFORM ut__shift(v_interval_id_ck,-2,-1,timestamptz ''2001-01-05'', timestamptz ''2001-01-07''); + PERFORM ut__shift(v_interval_id_ck,-2,-1,timestamptz '2001-01-05', timestamptz '2001-01-07'); -- Should be unequal again - PERFORM ut__eq(''For fun, unequal (distinct) intervals'',v_interval_id,v_interval_id_ck,false); + PERFORM ut__eq('For fun, unequal (distinct) intervals',v_interval_id,v_interval_id_ck,false); -- Note that at this point, interval pointed to by v_interval_id is from 2001-01-07 through 2001-01-08 -- while interval pointed to by v_interval_id_ck is from 2001-01-05 through 2001-01-07. -- They overlap. - PERFORM ut__overlaps_p(''Overlapping intervals'',v_interval_id,v_interval_id_ck,true); + PERFORM ut__overlaps_p('Overlapping intervals',v_interval_id,v_interval_id_ck,true); -- Ok, shift the dtart and end dates by one so that intervals do not overlap - PERFORM ut__shift(v_interval_id_ck,-1,-1,timestamptz ''2001-01-04'', timestamptz ''2001-01-06''); + PERFORM ut__shift(v_interval_id_ck,-1,-1,timestamptz '2001-01-04', timestamptz '2001-01-06'); -- They should not overlap now. - PERFORM ut__overlaps_p(''Non-overlapping intervals'',v_interval_id,v_interval_id_ck,false); + PERFORM ut__overlaps_p('Non-overlapping intervals',v_interval_id,v_interval_id_ck,false); -- We test the overloaded function definitions of time_interval__overlaps_p -- Note that we are comparing with 2001-01-07 through 2001-01-08 - PERFORM ut__overlaps_p(''Overlapping intervals'', + PERFORM ut__overlaps_p('Overlapping intervals', v_interval_id, - timestamptz ''2001-01-06'', - timestamptz ''2001-01-09'', + timestamptz '2001-01-06', + timestamptz '2001-01-09', true); -- How about an interval next month? - PERFORM ut__overlaps_p(''Non-overlapping intervals'', + PERFORM ut__overlaps_p('Non-overlapping intervals', v_interval_id, - timestamptz ''2001-02-06'', - timestamptz ''2001-02-09'', + timestamptz '2001-02-06', + timestamptz '2001-02-09', false); -- Try a null starting interval - PERFORM ut__overlaps_p(''Overlapping intervals (null start)'', + PERFORM ut__overlaps_p('Overlapping intervals (null start)', v_interval_id, null, - timestamptz ''2001-01-09'', + timestamptz '2001-01-09', true); -- Try a null starting interval - PERFORM ut__overlaps_p(''Overlapping intervals (null end)'', + PERFORM ut__overlaps_p('Overlapping intervals (null end)', v_interval_id, - timestamptz ''2001-01-06'', + timestamptz '2001-01-06', null, true); -- What if the interval is not an allowable interval? -- By definition, any interval should be non-overlapping with a non-existent interval - PERFORM ut__overlaps_p(''Non-overlapping intervals (non-allowed interval, outside month)'', + PERFORM ut__overlaps_p('Non-overlapping intervals (non-allowed interval, outside month)', v_interval_id, - timestamptz ''2001-02-09'', - timestamptz ''2001-02-06'', + timestamptz '2001-02-09', + timestamptz '2001-02-06', false); -- What if the interval is not an allowable interval? -- By definition, any interval should be non-overlapping with a non-existent interval - PERFORM ut__overlaps_p(''Non-overlapping intervals (non-allowed interval, in month)'', + PERFORM ut__overlaps_p('Non-overlapping intervals (non-allowed interval, in month)', v_interval_id, - timestamptz ''2001-01-09'', - timestamptz ''2001-01-06'', + timestamptz '2001-01-09', + timestamptz '2001-01-06', false); -- Yet another overloaded definition - PERFORM ut__overlaps_p(''Overlapping intervals (not in time_intervals)'', - timestamptz ''2001-01-06'', - timestamptz ''2001-01-09'', - timestamptz ''2001-01-07'', - timestamptz ''2001-01-08'', + PERFORM ut__overlaps_p('Overlapping intervals (not in time_intervals)', + timestamptz '2001-01-06', + timestamptz '2001-01-09', + timestamptz '2001-01-07', + timestamptz '2001-01-08', true); -- Yet another overloaded definition - PERFORM ut__overlaps_p(''Overlapping intervals (not in time_intervals)'', - timestamptz ''2001-01-06'', - timestamptz ''2001-01-09'', - timestamptz ''2001-01-09'', - timestamptz ''2001-01-10'', + PERFORM ut__overlaps_p('Overlapping intervals (not in time_intervals)', + timestamptz '2001-01-06', + timestamptz '2001-01-09', + timestamptz '2001-01-09', + timestamptz '2001-01-10', true); -- Yet another overloaded definition - PERFORM ut__overlaps_p(''Overlapping intervals (not in time_intervals)'', - timestamptz ''2001-01-06'', - timestamptz ''2001-01-09'', + PERFORM ut__overlaps_p('Overlapping intervals (not in time_intervals)', + timestamptz '2001-01-06', + timestamptz '2001-01-09', null, - timestamptz ''2001-01-10'', + timestamptz '2001-01-10', true); - PERFORM ut__overlaps_p(''Overlapping intervals (not in time_intervals)'', - timestamptz ''2001-01-06'', - timestamptz ''2001-01-09'', - timestamptz ''2001-01-10'', + PERFORM ut__overlaps_p('Overlapping intervals (not in time_intervals)', + timestamptz '2001-01-06', + timestamptz '2001-01-09', + timestamptz '2001-01-10', null, false); -- Yet another overloaded definition - PERFORM ut__overlaps_p(''Non-overlapping intervals (not in time_intervals)'', - timestamptz ''2001-02-06'', - timestamptz ''2001-02-09'', - timestamptz ''2001-01-07'', - timestamptz ''2001-01-08'', + PERFORM ut__overlaps_p('Non-overlapping intervals (not in time_intervals)', + timestamptz '2001-02-06', + timestamptz '2001-02-09', + timestamptz '2001-01-07', + timestamptz '2001-01-08', false); @@ -520,32 +564,39 @@ -- Should be equal -- Now test equality of time intervals - PERFORM ut__eq(''Copied intervals (zero offset)'',v_interval_id,v_interval_id_ck,true); + PERFORM ut__eq('Copied intervals (zero offset)',v_interval_id,v_interval_id_ck,true); -- Overwrite the check interval a copy, with non-zero offset v_interval_id_ck := ut__copy(v_interval_id,1); -- Should be unequal -- Now test inequality of time intervals - PERFORM ut__eq(''Copied intervals (non-zero offset)'',v_interval_id,v_interval_id_ck,false); + PERFORM ut__eq('Copied intervals (non-zero offset)',v_interval_id,v_interval_id_ck,false); -- We will improve the regression test so there is reporting -- of individual test results. For now, reaching this far is -- enough to declare success. return v_result; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut__regression2() -returns integer as ' -declare + + +-- +-- procedure ut__regression2/0 +-- +CREATE OR REPLACE FUNCTION ut__regression2( + +) RETURNS integer AS $$ +DECLARE v_result integer := 0; rec_interval record; -begin +BEGIN - raise notice ''Regression test, part 2 (deletes).''; + raise notice 'Regression test, part 2 (deletes).'; -- Remove all entries made by regression test -- This also tests the deletion mechanism @@ -561,7 +612,8 @@ -- enough to declare success. return v_result; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -------------------------------------------------------------------------------- -- Main regression test. PostgreSQL does not allow multiple changes made to a Index: openacs-4/packages/acs-events/sql/postgresql/test/timespan-test.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/postgresql/test/timespan-test.sql,v diff -u -N -r1.2 -r1.3 --- openacs-4/packages/acs-events/sql/postgresql/test/timespan-test.sql 17 May 2003 09:47:26 -0000 1.2 +++ openacs-4/packages/acs-events/sql/postgresql/test/timespan-test.sql 30 Mar 2013 13:00:30 -0000 1.3 @@ -12,20 +12,19 @@ \i utest-create.sql -- Set-up the regression test -create function ut__setup() -returns integer as ' -begin +CREATE OR REPLACE FUNCTION ut__setup() RETURNS integer AS $$ +BEGIN - raise notice ''Setting up timespans test...''; + raise notice 'Setting up timespans test...'; -- create copies of the tables (shadow tables) to verify API operations -- No need for execute here? create table ut_timespans as select * from timespans; -- For testing purposes, both tables should still be empty - PERFORM ut_assert__eqtable (''Comparing copied data for time interval'', - ''timespans'', - ''ut_timespans'' + PERFORM ut_assert__eqtable ('Comparing copied data for time interval', + 'timespans', + 'ut_timespans' ); -- Store keys that are in the table prior to the regresion test @@ -34,15 +33,15 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- Clean up the mess that regression testing did -create function ut__teardown() -returns integer as ' -begin +CREATE OR REPLACE FUNCTION ut__teardown() RETURNS integer AS $$ +BEGIN - raise notice ''Tearing down timespans test...''; + raise notice 'Tearing down timespans test...'; -- Delete intervals added by tests -- cascade delete in timespans should delete corresponding entries in that table @@ -66,23 +65,30 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- Postgres has this weird behavior that you cannot change a row twice -- within a transaction. -- We test the creation of a time interval entry -create function ut__new( - integer -- time_intervals.interval_id%TYPE; -) -returns integer as ' -declare - new__interval_id alias for $1; + + +-- added +select define_function_args('ut__new','interval_id'); + +-- +-- procedure ut__new/1 +-- +CREATE OR REPLACE FUNCTION ut__new( + new__interval_id integer +) RETURNS integer AS $$ +DECLARE v_interval_id time_intervals.interval_id%TYPE; v_timespan_id timespans.timespan_id%TYPE; -begin +BEGIN -- The new function will create a copy on the time_intervals table v_timespan_id := timespan__new(new__interval_id); @@ -99,33 +105,36 @@ -- The new function will create a copy on the time_intervals table -- We do two test. First, we check whether the copying mechanism is ok - PERFORM ut_assert__eq (''Test of timespan__new copying mechanism: '', + PERFORM ut_assert__eq ('Test of timespan__new copying mechanism: ', time_interval__eq(v_interval_id, new__interval_id), true ); -- Second, we check whether the timespans table is properly populated - PERFORM ut_assert__eqtable (''Test of timespan__new entry in timespans table: '', - ''ut_timespans'', - ''timespans'' + PERFORM ut_assert__eqtable ('Test of timespan__new entry in timespans table: ', + 'ut_timespans', + 'timespans' ); -- If successful, interval id is correct return v_timespan_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- We test the creation of a time interval entry -create function ut__new( - timestamptz, - timestamptz -) -returns integer as ' -declare - new__date1 alias for $1; - new__date2 alias for $2; + + +-- +-- procedure ut__new/2 +-- +CREATE OR REPLACE FUNCTION ut__new( + new__date1 timestamptz, + new__date2 timestamptz +) RETURNS integer AS $$ +DECLARE v_interval_id time_intervals.interval_id%TYPE; -begin +BEGIN -- We first want to create an entry in the time interval table -- because the timespan_new function copies this interval @@ -134,24 +143,31 @@ -- Create a new timespan using the function above return ut__new(v_interval_id); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- Check the deletion of a time interval -create function ut__delete( - integer -- timespans.timespan_id%TYPE -) -returns integer as ' -declare - delete__timespan_id alias for $1; -begin + +-- added +select define_function_args('ut__delete','timespan_id'); + +-- +-- procedure ut__delete/1 +-- +CREATE OR REPLACE FUNCTION ut__delete( + delete__timespan_id integer +) RETURNS integer AS $$ +DECLARE +BEGIN + -- Delete the row from actual table PERFORM timespan__delete(delete__timespan_id); - PERFORM ut_assert__eqtable (''Testing timespan__delete: '', - ''ut_timespans'', - ''timespans'' + PERFORM ut_assert__eqtable ('Testing timespan__delete: ', + 'ut_timespans', + 'timespans' ); -- Delete entry from shadow table @@ -171,23 +187,28 @@ -- If successful, interval id is correct return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut__join_interval ( - integer, -- timespans.timespan_id%TYPE; - integer, -- time_intervals.interval_id%TYPE; - boolean -- copy_p (if false, create new) -) -returns integer as ' -declare - join_interval__timespan_id alias for $1; - join_interval__interval_id alias for $2; - join_interval__copy_p alias for $3; + + +-- added +select define_function_args('ut__join_interval','timespan_id,interval_id,copy_p'); + +-- +-- procedure ut__join_interval/3 +-- +CREATE OR REPLACE FUNCTION ut__join_interval( + join_interval__timespan_id integer, + join_interval__interval_id integer, + join_interval__copy_p boolean +) RETURNS integer AS $$ +DECLARE v_interval_id time_intervals.interval_id%TYPE; v_interval_id_ck time_intervals.interval_id%TYPE; v_interval_id_cp time_intervals.interval_id%TYPE; -begin +BEGIN -- Get interval id of orginal interval (before join) select interval_id into v_interval_id @@ -205,17 +226,17 @@ values (join_interval__timespan_id,v_interval_id_cp); -- Check if there are now two intervals with the same timespan_id in timespans table - PERFORM ut_assert__eqquery (''Testing timespan__join with two intervals (2 entries): '', - ''select count(*) + PERFORM ut_assert__eqquery ('Testing timespan__join with two intervals (2 entries): ', + 'select count(*) from timespans - where timespan_id = '' || join_interval__timespan_id, - ''select 2 from dual'' + where timespan_id = ' || join_interval__timespan_id, + 'select 2 from dual' ); -- This is probably a more robust check, since we want to compare the resulting timespan table - PERFORM ut_assert__eqtable (''Testing timespan__join: table comparison test: '', - ''ut_timespans'', - ''timespans'' + PERFORM ut_assert__eqtable ('Testing timespan__join: table comparison test: ', + 'ut_timespans', + 'timespans' ); @@ -224,18 +245,23 @@ -- AND checking that only two intervals are in the time span should be enough! return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut__join( - integer, -- timespans.timespan_id%TYPE; - integer -- timespans.timespan_id%TYPE; -) -returns integer as ' -declare - join__timespan_id_1 alias for $1; - join__timespan_id_2 alias for $2; + + +-- added + +-- +-- procedure ut__join/2 +-- +CREATE OR REPLACE FUNCTION ut__join( + join__timespan_id_1 integer, + join__timespan_id_2 integer +) RETURNS integer AS $$ +DECLARE rec_timespan record; -begin +BEGIN PERFORM timespan__join(join__timespan_id_1,join__timespan_id_2); @@ -252,25 +278,30 @@ -- Check equality of tables - PERFORM ut_assert__eqtable (''Testing timespan__join by specifying timespan_id: '', - ''ut_timespans'', - ''timespans'' + PERFORM ut_assert__eqtable ('Testing timespan__join by specifying timespan_id: ', + 'ut_timespans', + 'timespans' ); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut__join( - integer, -- timespans.timespan_id%TYPE; - timestamptz, -- time_intervals.start_date%TYPE; - timestamptz -- time_intervals.end_date%TYPE; -) -returns integer as ' -declare - join__timespan_id alias for $1; - join__start_date alias for $2; - join__end_date alias for $3; + + +-- added +select define_function_args('ut__join','timespan_id,start_date,end_date'); + +-- +-- procedure ut__join/3 +-- +CREATE OR REPLACE FUNCTION ut__join( + join__timespan_id integer, + join__start_date timestamptz, + join__end_date timestamptz +) RETURNS integer AS $$ +DECLARE v_interval_id time_intervals.interval_id%TYPE; -begin +BEGIN @@ -282,26 +313,32 @@ values (join__timespan_id,v_interval_id); -- Check equality of tables - PERFORM ut_assert__eqtable (''Testing timespan__join by specifying start and end dates: '', - ''ut_timespans'', - ''timespans'' + PERFORM ut_assert__eqtable ('Testing timespan__join by specifying start and end dates: ', + 'ut_timespans', + 'timespans' ); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut__copy( - integer, -- timespans.timespan_id%TYPE - interval -- offset -) -returns integer as ' -declare - copy__timespan_id alias for $1; - copy__offset alias for $2; + + +-- added +select define_function_args('ut__copy','timespan_id,offset'); + +-- +-- procedure ut__copy/2 +-- +CREATE OR REPLACE FUNCTION ut__copy( + copy__timespan_id integer, + copy__offset interval +) RETURNS integer AS $$ +DECLARE v_timespan_id timespans.timespan_id%TYPE; v_interval_id time_intervals.interval_id%TYPE; v_interval_id_ck time_intervals.interval_id%TYPE; rec_timespan record; -begin +BEGIN v_timespan_id := timespan__copy(copy__timespan_id,copy__offset); @@ -318,122 +355,153 @@ end loop; -- Check proper population of shadow table - PERFORM ut_assert__eqtable (''Testing timespan__copy: '', - ''ut_timespans'', - ''timespans'' + PERFORM ut_assert__eqtable ('Testing timespan__copy: ', + 'ut_timespans', + 'timespans' ); return v_timespan_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut__exists_p ( - integer, -- timespans.timespan_id%TYPE; - boolean -) -returns integer as ' -declare - exists_p__timespan_id alias for $1; - exists_p__result alias for $2; -begin - PERFORM ut_assert__eq (''Testing timespan__exists_p: '', + +-- added +select define_function_args('ut__exists_p','timespan_id,result'); + +-- +-- procedure ut__exists_p/2 +-- +CREATE OR REPLACE FUNCTION ut__exists_p( + exists_p__timespan_id integer, + exists_p__result boolean +) RETURNS integer AS $$ +DECLARE +BEGIN + + PERFORM ut_assert__eq ('Testing timespan__exists_p: ', timespan__exists_p(exists_p__timespan_id), exists_p__result ); return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut__multi_interval_p ( - integer, -- timespans.timespan_id%TYPE - boolean -- result -) -returns integer as ' -declare - multi_interval_p__timespan_id alias for $1; - multi_interval_p__result alias for $2; -begin - return ut_assert__eq (''Testing timespan__multi_interval_p: '', + +-- added +select define_function_args('ut__multi_interval_p','timespan_id,result'); + +-- +-- procedure ut__multi_interval_p/2 +-- +CREATE OR REPLACE FUNCTION ut__multi_interval_p( + multi_interval_p__timespan_id integer, + multi_interval_p__result boolean +) RETURNS integer AS $$ +DECLARE +BEGIN + + return ut_assert__eq ('Testing timespan__multi_interval_p: ', timespan__multi_interval_p(multi_interval_p__timespan_id), multi_interval_p__result ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut__overlaps_interval_p( - integer, -- timespans.timespan_id%TYPE; - integer, -- time_intervals.interval_id%TYPE; - boolean -) -returns integer as ' -declare - overlaps_interval_p__timespan_id alias for $1; - overlaps_interval_p__interval_id alias for $2; - overlaps_interval_p__result alias for $3; -begin - return ut_assert__eq (''Testing timespan__overlaps_interval_p: '', + +-- added +select define_function_args('ut__overlaps_interval_p','timespan_id,interval_id,result'); + +-- +-- procedure ut__overlaps_interval_p/3 +-- +CREATE OR REPLACE FUNCTION ut__overlaps_interval_p( + overlaps_interval_p__timespan_id integer, + overlaps_interval_p__interval_id integer, + overlaps_interval_p__result boolean +) RETURNS integer AS $$ +DECLARE +BEGIN + + return ut_assert__eq ('Testing timespan__overlaps_interval_p: ', timespan__overlaps_interval_p(overlaps_interval_p__timespan_id, overlaps_interval_p__interval_id), overlaps_interval_p__result ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut__overlaps_p( - integer, -- timespans.timespan_id%TYPE; - integer, -- timespans.timespan_id%TYPE; - boolean -) -returns integer as ' -declare - overlaps_p__timespan_1_id alias for $1; - overlaps_p__timespan_2_id alias for $2; - overlaps_p__result alias for $3; -begin - return ut_assert__eq (''Testing timespan__overlaps_p, timespan vs. timespan: '', + +-- added + +-- +-- procedure ut__overlaps_p/3 +-- +CREATE OR REPLACE FUNCTION ut__overlaps_p( + overlaps_p__timespan_1_id integer, + overlaps_p__timespan_2_id integer, + overlaps_p__result boolean +) RETURNS integer AS $$ +DECLARE +BEGIN + + return ut_assert__eq ('Testing timespan__overlaps_p, timespan vs. timespan: ', timespan__overlaps_p(overlaps_p__timespan_1_id, overlaps_p__timespan_2_id), overlaps_p__result ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut__overlaps_p( - integer, -- timespans.timespan_id%TYPE; - timestamptz, -- time_intervals.start_date%TYPE; - timestamptz, -- time_intervals.end_date%TYPE; - boolean -) -returns integer as ' -declare - overlaps_p__timespan_id alias for $1; - overlaps_p__start_date alias for $2; - overlaps_p__end_date alias for $3; - overlaps_p__result alias for $4; -begin - return ut_assert__eq (''Test of timespan__overlaps_p, timespan vs. start and end dates: '', + +-- added +select define_function_args('ut__overlaps_p','timespan_id,start_date,end_date,result'); + +-- +-- procedure ut__overlaps_p/4 +-- +CREATE OR REPLACE FUNCTION ut__overlaps_p( + overlaps_p__timespan_id integer, + overlaps_p__start_date timestamptz, + overlaps_p__end_date timestamptz, + overlaps_p__result boolean +) RETURNS integer AS $$ +DECLARE +BEGIN + + return ut_assert__eq ('Test of timespan__overlaps_p, timespan vs. start and end dates: ', timespan__overlaps_p(overlaps_p__timespan_id, overlaps_p__start_date, overlaps_p__end_date), overlaps_p__result ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut__interval_delete ( - integer, -- timespans.timespan_id%TYPE; - integer -- time_intervals.interval_id%TYPE; -) -returns integer as ' -declare - interval_delete__timespan_id alias for $1; - interval_delete__interval_id alias for $2; -begin + +-- added +select define_function_args('ut__interval_delete','timespan_id,interval_id'); + +-- +-- procedure ut__interval_delete/2 +-- +CREATE OR REPLACE FUNCTION ut__interval_delete( + interval_delete__timespan_id integer, + interval_delete__interval_id integer +) RETURNS integer AS $$ +DECLARE +BEGIN + PERFORM timespan__interval_delete(interval_delete__timespan_id,interval_delete__interval_id); -- Remove from shadow table @@ -442,34 +510,41 @@ and interval_id = interval_delete__interval_id; - return ut_assert__eqtable(''Testing timespan__interval_delete: '', - ''ut_timespans'', - ''timespans'' + return ut_assert__eqtable('Testing timespan__interval_delete: ', + 'ut_timespans', + 'timespans' ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut__regression1() -returns integer as ' -declare + + +-- +-- procedure ut__regression1/0 +-- +CREATE OR REPLACE FUNCTION ut__regression1( + +) RETURNS integer AS $$ +DECLARE v_result integer := 0; v_interval_id time_intervals.interval_id%TYPE; v_interval_id_ck time_intervals.interval_id%TYPE; v_timespan_id timespans.timespan_id%TYPE; v_timespan_id_ck timespans.timespan_id%TYPE; -begin +BEGIN - raise notice ''Regression test, part 1 (creates and edits).''; + raise notice 'Regression test, part 1 (creates and edits).'; -- First create an interval - v_interval_id := time_interval__new(timestamptz ''2001-01-01'',timestamptz ''2001-01-20''); + v_interval_id := time_interval__new(timestamptz '2001-01-01',timestamptz '2001-01-20'); --Check if creation of timespans work by supplying an interval id to be copied PERFORM ut__new(v_interval_id); -- We first check if the creation of timespans work -- This should be equivalent to what we have above - v_timespan_id := ut__new(timestamptz ''2001-01-25'',timestamptz ''2001-02-02''); + v_timespan_id := ut__new(timestamptz '2001-01-25',timestamptz '2001-02-02'); -- Test if timespan exists PERFORM ut__exists_p(v_timespan_id,true); @@ -494,20 +569,20 @@ PERFORM ut__overlaps_interval_p(v_timespan_id,v_interval_id,true); -- A new timespans - v_timespan_id := ut__new(timestamptz ''2001-03-05'',timestamptz ''2001-03-31''); - v_timespan_id_ck := ut__new(timestamptz ''2001-06-05'',timestamptz ''2001-06-30''); + v_timespan_id := ut__new(timestamptz '2001-03-05',timestamptz '2001-03-31'); + v_timespan_id_ck := ut__new(timestamptz '2001-06-05',timestamptz '2001-06-30'); -- These timespans should not overlap PERFORM ut__overlaps_p(v_timespan_id,v_timespan_id_ck,false); -- Check overlaps against these known dates - PERFORM ut__overlaps_p(v_timespan_id,timestamptz ''2001-02-06'',timestamptz ''2001-03-25'',true); - PERFORM ut__overlaps_p(v_timespan_id,timestamptz ''2001-03-07'',timestamptz ''2001-04-01'',true); - PERFORM ut__overlaps_p(v_timespan_id,timestamptz ''2001-01-01'',timestamptz ''2001-03-20'',true); - PERFORM ut__overlaps_p(v_timespan_id,timestamptz ''2001-01-01'',null,true); - PERFORM ut__overlaps_p(v_timespan_id,null,timestamptz ''2001-04-01'',true); - PERFORM ut__overlaps_p(v_timespan_id,timestamptz ''2001-04-01'',timestamptz ''2001-04-30'',false); - PERFORM ut__overlaps_p(v_timespan_id,timestamptz ''2001-02-01'',timestamptz ''2001-02-27'',false); + PERFORM ut__overlaps_p(v_timespan_id,timestamptz '2001-02-06',timestamptz '2001-03-25',true); + PERFORM ut__overlaps_p(v_timespan_id,timestamptz '2001-03-07',timestamptz '2001-04-01',true); + PERFORM ut__overlaps_p(v_timespan_id,timestamptz '2001-01-01',timestamptz '2001-03-20',true); + PERFORM ut__overlaps_p(v_timespan_id,timestamptz '2001-01-01',null,true); + PERFORM ut__overlaps_p(v_timespan_id,null,timestamptz '2001-04-01',true); + PERFORM ut__overlaps_p(v_timespan_id,timestamptz '2001-04-01',timestamptz '2001-04-30',false); + PERFORM ut__overlaps_p(v_timespan_id,timestamptz '2001-02-01',timestamptz '2001-02-27',false); -- Join the first interval with the second, making a copy @@ -520,10 +595,10 @@ PERFORM ut__overlaps_p(v_timespan_id,v_timespan_id_ck,true); -- Join an interval instead - PERFORM ut__join(v_timespan_id_ck,timestamptz ''2001-12-01'',timestamptz ''2001-12-31''); + PERFORM ut__join(v_timespan_id_ck,timestamptz '2001-12-01',timestamptz '2001-12-31'); -- Copy a timespan (will only contain two) - PERFORM ut__copy(v_timespan_id,interval ''0 days''); + PERFORM ut__copy(v_timespan_id,interval '0 days'); -- Now try to delete the interval just joined PERFORM ut__interval_delete(v_timespan_id,v_interval_id); @@ -534,16 +609,23 @@ -- enough to declare success. return v_result; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut__regression2() -returns integer as ' -declare + + +-- +-- procedure ut__regression2/0 +-- +CREATE OR REPLACE FUNCTION ut__regression2( + +) RETURNS integer AS $$ +DECLARE v_result integer := 0; rec_timespan record; -begin +BEGIN - raise notice ''Regression test, part 2 (deletes).''; + raise notice 'Regression test, part 2 (deletes).'; -- Remove all entries made by regression test -- This also tests the deletion mechanism @@ -560,7 +642,8 @@ -- enough to declare success. return v_result; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -------------------------------------------------------------------------------- -- Main regression test. PostgreSQL does not allow multiple changes made to a Index: openacs-4/packages/acs-events/sql/postgresql/test/utest-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/postgresql/test/utest-create.sql,v diff -u -N -r1.2 -r1.3 --- openacs-4/packages/acs-events/sql/postgresql/test/utest-create.sql 17 May 2003 09:47:26 -0000 1.2 +++ openacs-4/packages/acs-events/sql/postgresql/test/utest-create.sql 30 Mar 2013 13:00:30 -0000 1.3 @@ -34,46 +34,55 @@ -- JS: Ported/copied shamelessly from the utplsql package. -- JS: This package is grossly incomplete, but quite useful (for me, anyways). -create function ut_assert__expected ( - varchar, -- IN VARCHAR, - varchar, -- IN VARCHAR, - varchar -- IN VARCHAR -) -returns varchar as ' -declare - expected__msg alias for $1; - expected__check_this alias for $2; - expected__against_this alias for $3; -begin + +-- added +select define_function_args('ut_assert__expected','msg,check_this,against_this'); + +-- +-- procedure ut_assert__expected/3 +-- +CREATE OR REPLACE FUNCTION ut_assert__expected( + expected__msg varchar, + expected__check_this varchar, + expected__against_this varchar +) RETURNS varchar AS $$ +DECLARE +BEGIN + return expected__msg || - '': expected '' || - '''''''' || + ': expected ' || + '''' || expected__against_this || - '''''''' || - '', got '' || - '''''''' || + '''' || + ', got ' || + '''' || expected__check_this || - ''''''''; + ''''; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut_assert__this ( - varchar, -- IN VARCHAR, - boolean, -- IN BOOLEAN, - boolean, -- IN BOOLEAN default false, - boolean -- IN BOOLEAN default false -) -returns integer as ' -declare - this__msg alias for $1; - this__check_this alias for $2; - this__null_ok alias for $3; -- default FALSE - this__raise_exc alias for $4; -- default FALSE -begin + +-- added +select define_function_args('ut_assert__this','msg,check_this,null_ok;FALSE,raise_exc;FALSE'); + +-- +-- procedure ut_assert__this/4 +-- +CREATE OR REPLACE FUNCTION ut_assert__this( + this__msg varchar, + this__check_this boolean, + this__null_ok boolean, -- default FALSE + this__raise_exc boolean -- default FALSE + +) RETURNS integer AS $$ +DECLARE +BEGIN + -- We always output the message (usually the result of the test) - raise notice ''%'',this__msg; + raise notice '%',this__msg; if not this__check_this or ( this__check_this is null @@ -84,111 +93,127 @@ if this__raise_exc then -- We should make the message more informative. - raise exception ''FAILURE''; + raise exception 'FAILURE'; else - raise notice ''FAILURE, but forced to continue.''; + raise notice 'FAILURE, but forced to continue.'; end if; end if; -- Continue if success; return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- Overload for calls with default values -create function ut_assert__this ( - varchar, -- IN VARCHAR, - boolean -- IN BOOLEAN, -) -returns integer as ' -declare - this__msg alias for $1; - this__check_this alias for $2; -begin - return ut_assert__this(this_msg,this_check_this,''f'',''f''); + +-- +-- procedure ut_assert__this/2 +-- +CREATE OR REPLACE FUNCTION ut_assert__this( + this__msg varchar, + this__check_this boolean +) RETURNS integer AS $$ +DECLARE +BEGIN + + return ut_assert__this(this_msg,this_check_this,'f','f'); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut_assert__eq ( - varchar, -- IN VARCHAR2, - varchar, -- IN VARCHAR2, - varchar, -- IN VARCHAR2, - boolean, -- IN VARCHAR := FALSE, - boolean -- IN BOOLEAN := FALSE -) -returns integer as ' -declare - eq__msg alias for $1; - eq__check_this alias for $2; - eq__against_this alias for $3; - eq__null_ok alias for $4; -- default FALSE, - eq__raise_exc alias for $5; -- defaultFALSE -begin + + +-- added + +-- +-- procedure ut_assert__eq/5 +-- +CREATE OR REPLACE FUNCTION ut_assert__eq( + eq__msg varchar, + eq__check_this varchar, + eq__against_this varchar, + eq__null_ok boolean, -- default FALSE, + eq__raise_exc boolean -- defaultFALSE + +) RETURNS integer AS $$ +DECLARE +BEGIN return ut_assert__this ( ut_assert__expected (eq__msg, eq__check_this, eq__against_this), eq__check_this = eq__against_this, eq__null_ok, eq__raise_exc ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- Overload for calls with default values -create function ut_assert__eq ( - varchar, -- IN VARCHAR2, - varchar, -- IN VARCHAR2, - varchar -- IN VARCHAR2, -) -returns integer as ' -declare - eq__msg alias for $1; - eq__check_this alias for $2; - eq__against_this alias for $3; -begin - return ut_assert__eq(eq__msg,eq__check_this,eq__against_this,''f'',''f''); -end;' language 'plpgsql'; +-- +-- procedure ut_assert__eq/3 +-- +CREATE OR REPLACE FUNCTION ut_assert__eq( + eq__msg varchar, + eq__check_this varchar, + eq__against_this varchar +) RETURNS integer AS $$ +DECLARE +BEGIN + return ut_assert__eq(eq__msg,eq__check_this,eq__against_this,'f','f'); -create function ut_assert__b2v ( - boolean -- IN BOOLEAN -) -returns varchar as ' -declare - bool_exp alias for $1; -begin +END; +$$ LANGUAGE plpgsql; + + + +-- added +select define_function_args('ut_assert__b2v','bool_exp'); + +-- +-- procedure ut_assert__b2v/1 +-- +CREATE OR REPLACE FUNCTION ut_assert__b2v( + bool_exp boolean +) RETURNS varchar AS $$ +DECLARE +BEGIN + if bool_exp then - return ''true''; + return 'true'; else if not bool_exp then - return ''false''; + return 'false'; else - return ''null''; + return 'null'; end if; end if; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut_assert__eq ( - varchar, - boolean, - boolean, - boolean, - boolean -) -returns integer as ' -declare - eq__msg alias for $1; - eq__check_this alias for $2; - eq__against_this alias for $3; - eq__null_ok alias for $4; -- default false - eq__raise_exc alias for $5; -- defualt false -begin + + +-- +-- procedure ut_assert__eq/5 +-- +CREATE OR REPLACE FUNCTION ut_assert__eq( + eq__msg varchar, + eq__check_this boolean, + eq__against_this boolean, + eq__null_ok boolean, -- default false + eq__raise_exc boolean -- defualt false + +) RETURNS integer AS $$ +DECLARE +BEGIN return ut_assert__this ( ut_assert__expected ( @@ -202,43 +227,49 @@ ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- Overload for calls with default values -create function ut_assert__eq ( - varchar, -- IN VARCHAR2, - boolean, - boolean -) -returns integer as ' -declare - eq__msg alias for $1; - eq__check_this alias for $2; - eq__against_this alias for $3; -begin - return ut_assert__eq(eq__msg,eq__check_this,eq__against_this,''f'',''f''); -end;' language 'plpgsql'; +-- +-- procedure ut_assert__eq/3 +-- +CREATE OR REPLACE FUNCTION ut_assert__eq( + eq__msg varchar, + eq__check_this boolean, + eq__against_this boolean +) RETURNS integer AS $$ +DECLARE +BEGIN -create function ut_assert__eq ( - varchar, - timestamptz, - timestamptz, - boolean, - boolean -) -returns integer as ' -declare - eq__msg alias for $1; - eq__check_this alias for $2; - eq__against_this alias for $3; - eq__null_ok alias for $4; -- default false - eq__raise_exc alias for $5; -- default false - c_format constant varchar := ''MONTH DD, YYYY HH24MISS''; + return ut_assert__eq(eq__msg,eq__check_this,eq__against_this,'f','f'); + +END; +$$ LANGUAGE plpgsql; + + + +-- added +select define_function_args('ut_assert__eq','msg,check_this,against_this,null_ok;false,raise_exc;false'); + +-- +-- procedure ut_assert__eq/5 +-- +CREATE OR REPLACE FUNCTION ut_assert__eq( + eq__msg varchar, + eq__check_this timestamptz, + eq__against_this timestamptz, + eq__null_ok boolean, -- default false + eq__raise_exc boolean -- default false + +) RETURNS integer AS $$ +DECLARE + c_format constant varchar := 'MONTH DD, YYYY HH24MISS'; v_check varchar; v_against varchar; -begin +BEGIN v_check := to_char (eq__check_this, c_format); v_against := to_char (eq__against_this, c_format); @@ -250,64 +281,69 @@ eq__raise_exc ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- Overload for calls with default values -create function ut_assert__eq ( - varchar, -- IN VARCHAR2, - timestamptz, - timestamptz -) -returns integer as ' -declare - eq__msg alias for $1; - eq__check_this alias for $2; - eq__against_this alias for $3; -begin - return ut_assert__eq(eq__msg,eq__check_this,eq__against_this,''f'',''f''); -end;' language 'plpgsql'; +-- +-- procedure ut_assert__eq/3 +-- +CREATE OR REPLACE FUNCTION ut_assert__eq( + eq__msg varchar, + eq__check_this timestamptz, + eq__against_this timestamptz +) RETURNS integer AS $$ +DECLARE +BEGIN -create function ut_assert__ieqminus ( - varchar, - varchar, - varchar, - varchar, - boolean -) -returns varchar as ' -declare - ieqminus__msg alias for $1; - ieqminus__query1 alias for $2; - ieqminus__query2 alias for $3; - ieqminus__minus_desc alias for $4; - ieqminus__raise_exc alias for $5; + return ut_assert__eq(eq__msg,eq__check_this,eq__against_this,'f','f'); + +END; +$$ LANGUAGE plpgsql; + + + +-- added +select define_function_args('ut_assert__ieqminus','msg,query1,query2,minus_desc,raise_exc'); + +-- +-- procedure ut_assert__ieqminus/5 +-- +CREATE OR REPLACE FUNCTION ut_assert__ieqminus( + ieqminus__msg varchar, + ieqminus__query1 varchar, + ieqminus__query2 varchar, + ieqminus__minus_desc varchar, + ieqminus__raise_exc boolean +) RETURNS varchar AS $$ +DECLARE v_query varchar; rec_tableminus record; - v_eq boolean := ''t''; + v_eq boolean := 't'; -begin +BEGIN - v_query := '' ( '' || + v_query := ' ( ' || ieqminus__query1 || - '' except '' || + ' except ' || ieqminus__query2 || - '' ) '' || - '' union '' || - '' ( '' || + ' ) ' || + ' union ' || + ' ( ' || ieqminus__query2 || - '' except '' || + ' except ' || ieqminus__query1 || - '' ) ''; + ' ) '; for rec_tableminus in execute v_query; -- Will not go in this loop if v_query result is null, so -- we need to set the default value of v_eq to true. if found then - v_eq := ''f''; + v_eq := 'f'; end if; -- One is enough @@ -316,288 +352,336 @@ end loop; return ut_assert__this ( - ut_assert__expected (ieqminus__msg || '' '' || ieqminus__minus_desc, + ut_assert__expected (ieqminus__msg || ' ' || ieqminus__minus_desc, ieqminus__query1, ieqminus__query2 ), v_eq, - ''f'', + 'f', ieqminus__raise_exc ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create function ut_assert__eqtable ( - varchar, - varchar, - varchar, - varchar, - varchar, - boolean -) -returns integer as ' -declare - eqtable__msg alias for $1; - eqtable__check_this alias for $2; - eqtable__against_this alias for $3; - eqtable__check_where alias for $4; -- default null - eqtable__against_where alias for $5; -- default null - eqtable__raise_exc alias for $6; -- default false -begin + + +-- added +select define_function_args('ut_assert__eqtable','msg,check_this,against_this,check_where;null,against_where;null,raise_exc;false'); + +-- +-- procedure ut_assert__eqtable/6 +-- +CREATE OR REPLACE FUNCTION ut_assert__eqtable( + eqtable__msg varchar, + eqtable__check_this varchar, + eqtable__against_this varchar, + eqtable__check_where varchar, -- default null + eqtable__against_where varchar, -- default null + eqtable__raise_exc boolean -- default false + +) RETURNS integer AS $$ +DECLARE +BEGIN return ut_assert__ieqminus (eqtable__msg, - ''SELECT * FROM '' || eqtable__check_this || '' WHERE '' || - coalesce (eqtable__check_where, ''1=1''), - ''SELECT * FROM '' || eqtable__against_this || '' WHERE '' || - coalesce (eqtable__against_where, ''1=1''), - ''Table Equality'', + 'SELECT * FROM ' || eqtable__check_this || ' WHERE ' || + coalesce (eqtable__check_where, '1=1'), + 'SELECT * FROM ' || eqtable__against_this || ' WHERE ' || + coalesce (eqtable__against_where, '1=1'), + 'Table Equality', eqtable__raise_exc ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- Overload for calls with default values -create function ut_assert__eqtable ( - varchar, - varchar, - varchar -) -returns integer as ' -declare - eqtable__msg alias for $1; - eqtable__check_this alias for $2; - eqtable__against_this alias for $3; -begin - return ut_assert__eqtable(eqtable__msg,eqtable__check_this,eqtable__against_this,null,null,''f''); -end;' language 'plpgsql'; +-- +-- procedure ut_assert__eqtable/3 +-- +CREATE OR REPLACE FUNCTION ut_assert__eqtable( + eqtable__msg varchar, + eqtable__check_this varchar, + eqtable__against_this varchar +) RETURNS integer AS $$ +DECLARE +BEGIN + return ut_assert__eqtable(eqtable__msg,eqtable__check_this,eqtable__against_this,null,null,'f'); -create function ut_assert__eqtabcount ( - varchar, - varchar, - varchar, - varchar, - varchar, - boolean -) -returns integer as ' -declare - eqtabcount__msg alias for $1; - eqtabcount__check_this alias for $2; - eqtabcount__against_this alias for $3; - eqtabcount__check_where alias for $4; -- default null - eqtabcount__against_where alias for $5; -- default null - eqtabcount__raise_exc alias for $6; -- default false -begin +END; +$$ LANGUAGE plpgsql; + + + + +-- added +select define_function_args('ut_assert__eqtabcount','msg,check_this,against_this,check_where;null,against_where;null,raise_exc;false'); + +-- +-- procedure ut_assert__eqtabcount/6 +-- +CREATE OR REPLACE FUNCTION ut_assert__eqtabcount( + eqtabcount__msg varchar, + eqtabcount__check_this varchar, + eqtabcount__against_this varchar, + eqtabcount__check_where varchar, -- default null + eqtabcount__against_where varchar, -- default null + eqtabcount__raise_exc boolean -- default false + +) RETURNS integer AS $$ +DECLARE +BEGIN return ut_assert__ieqminus (eqtabcount__msg, - ''SELECT COUNT(*) FROM '' || eqtabcount__check_this || '' WHERE '' || - coalesce (eqtabcount__check_where, ''1=1''), - ''SELECT COUNT(*) FROM '' || eqtabcount__against_this || '' WHERE '' || - coalesce (eqtabcount__against_where, ''1=1''), - ''Table Count Equality'', + 'SELECT COUNT(*) FROM ' || eqtabcount__check_this || ' WHERE ' || + coalesce (eqtabcount__check_where, '1=1'), + 'SELECT COUNT(*) FROM ' || eqtabcount__against_this || ' WHERE ' || + coalesce (eqtabcount__against_where, '1=1'), + 'Table Count Equality', eqtabcount__raise_exc ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- Overload for calls with default values -create function ut_assert__eqtabcount ( - varchar, - varchar, - varchar -) -returns integer as ' -declare - eqtabcount__msg alias for $1; - eqtabcount__check_this alias for $2; - eqtabcount__against_this alias for $3; -begin - return ut_assert__eqtabcount(eqtabcount__msg,eqtabcount__check_this,eqtabcount__against_this,null,null,''f''); -end;' language 'plpgsql'; +-- +-- procedure ut_assert__eqtabcount/3 +-- +CREATE OR REPLACE FUNCTION ut_assert__eqtabcount( + eqtabcount__msg varchar, + eqtabcount__check_this varchar, + eqtabcount__against_this varchar +) RETURNS integer AS $$ +DECLARE +BEGIN -create function ut_assert__eqquery ( - varchar, - varchar, - varchar, - boolean -) -returns integer as ' -declare - eqquery__msg alias for $1; - eqquery__check_this alias for $2; - eqquery__against_this alias for $3; - eqquery__raise_exc alias for $4; -- default null -begin + return ut_assert__eqtabcount(eqtabcount__msg,eqtabcount__check_this,eqtabcount__against_this,null,null,'f'); + +END; +$$ LANGUAGE plpgsql; + + + +-- added +select define_function_args('ut_assert__eqquery','msg,check_this,against_this,raise_exc;null'); + +-- +-- procedure ut_assert__eqquery/4 +-- +CREATE OR REPLACE FUNCTION ut_assert__eqquery( + eqquery__msg varchar, + eqquery__check_this varchar, + eqquery__against_this varchar, + eqquery__raise_exc boolean -- default null + +) RETURNS integer AS $$ +DECLARE +BEGIN return ut_assert__ieqminus (eqquery__msg, eqquery__check_this, eqquery__against_this, - ''Query Equality'', + 'Query Equality', eqquery__raise_exc ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- Overload for calls with default values -create function ut_assert__eqquery ( - varchar, - varchar, - varchar -) -returns integer as ' -declare - eqquery__msg alias for $1; - eqquery__check_this alias for $2; - eqquery__against_this alias for $3; -begin - return ut_assert__eqquery(eqquery__msg,eqquery__check_this,eqquery__against_this,''f''); -end;' language 'plpgsql'; +-- +-- procedure ut_assert__eqquery/3 +-- +CREATE OR REPLACE FUNCTION ut_assert__eqquery( + eqquery__msg varchar, + eqquery__check_this varchar, + eqquery__against_this varchar +) RETURNS integer AS $$ +DECLARE +BEGIN -create function ut_assert__isnotnull ( - varchar, - varchar, - boolean, - boolean -) returns integer as ' -declare - isnotnull__msg alias for $1; - isnotnull__check_this alias for $2; - isnotnull__null_ok alias for $3; -- default false - isnotnull__raise_exc alias for $4; -- default false -begin + return ut_assert__eqquery(eqquery__msg,eqquery__check_this,eqquery__against_this,'f'); + +END; +$$ LANGUAGE plpgsql; + + + +-- added +select define_function_args('ut_assert__isnotnull','msg,check_this,null_ok;false,raise_exc;false'); + +-- +-- procedure ut_assert__isnotnull/4 +-- +CREATE OR REPLACE FUNCTION ut_assert__isnotnull( + isnotnull__msg varchar, + isnotnull__check_this varchar, + isnotnull__null_ok boolean, -- default false + isnotnull__raise_exc boolean -- default false + +) RETURNS integer AS $$ +DECLARE +BEGIN return ut_assert__this ( - ''IS NOT NULL: '' || isnotnull__msg, + 'IS NOT NULL: ' || isnotnull__msg, isnotnull__check_this IS NOT NULL, isnotnull__null_ok, isnotnull__raise_exc ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- Overload for calls with default values -create function ut_assert__isnotnull ( - varchar, - varchar -) -returns integer as ' -declare - isnotnull__msg alias for $1; - isnotnull__check_this alias for $2; -begin - return ut_assert__isnotnull(isnotnull__msg,isnotnull__check_this,''f'',''f''); -end;' language 'plpgsql'; +-- +-- procedure ut_assert__isnotnull/2 +-- +CREATE OR REPLACE FUNCTION ut_assert__isnotnull( + isnotnull__msg varchar, + isnotnull__check_this varchar +) RETURNS integer AS $$ +DECLARE +BEGIN + return ut_assert__isnotnull(isnotnull__msg,isnotnull__check_this,'f','f'); -create function ut_assert__isnull ( - varchar, - varchar, - boolean, - boolean -) returns integer as ' -declare - isnull__msg alias for $1; - isnull__check_this alias for $2; - isnull__null_ok alias for $3; -- default false - isnull__raise_exc alias for $4; -- default false -begin +END; +$$ LANGUAGE plpgsql; + + + + +-- added +select define_function_args('ut_assert__isnull','msg,check_this,null_ok;false,raise_exc;false'); + +-- +-- procedure ut_assert__isnull/4 +-- +CREATE OR REPLACE FUNCTION ut_assert__isnull( + isnull__msg varchar, + isnull__check_this varchar, + isnull__null_ok boolean, -- default false + isnull__raise_exc boolean -- default false + +) RETURNS integer AS $$ +DECLARE +BEGIN return ut_assert__this ( - ''IS NULL: '' || isnull__msg, + 'IS NULL: ' || isnull__msg, isnull__check_this IS NULL, isnull__null_ok, isnull__raise_exc ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- Overload for calls with default values -create function ut_assert__isnull ( - varchar, - varchar -) -returns integer as ' -declare - isnull__msg alias for $1; - isnull__check_this alias for $2; -begin - return ut_assert__isnull(isnull__msg,isnull__check_this,''f'',''f''); -end;' language 'plpgsql'; +-- +-- procedure ut_assert__isnull/2 +-- +CREATE OR REPLACE FUNCTION ut_assert__isnull( + isnull__msg varchar, + isnull__check_this varchar +) RETURNS integer AS $$ +DECLARE +BEGIN -create function ut_assert__isnotnull ( - varchar, - boolean, - boolean, - boolean -) returns integer as ' -declare - isnotnull__msg alias for $1; - isnotnull__check_this alias for $2; - isnotnull__null_ok alias for $3; -- default false - isnotnull__raise_exc alias for $4; -- default false -begin + return ut_assert__isnull(isnull__msg,isnull__check_this,'f','f'); + +END; +$$ LANGUAGE plpgsql; + + + +-- +-- procedure ut_assert__isnotnull/4 +-- +CREATE OR REPLACE FUNCTION ut_assert__isnotnull( + isnotnull__msg varchar, + isnotnull__check_this boolean, + isnotnull__null_ok boolean, -- default false + isnotnull__raise_exc boolean -- default false + +) RETURNS integer AS $$ +DECLARE +BEGIN return ut_assert__this ( - ''IS NOT NULL: '' || isnotnull__msg, + 'IS NOT NULL: ' || isnotnull__msg, isnotnull__check_this IS NOT NULL, isnotnull__null_ok, isnotnull__raise_exc ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- Overload for calls with default values -create function ut_assert__isnotnull ( - varchar, - boolean -) -returns integer as ' -declare - isnotnull__msg alias for $1; - isnotnull__check_this alias for $2; -begin - return ut_assert__isnotnull(isnotnull__msg,isnotnull__check_this,''f'',''f''); -end;' language 'plpgsql'; +-- +-- procedure ut_assert__isnotnull/2 +-- +CREATE OR REPLACE FUNCTION ut_assert__isnotnull( + isnotnull__msg varchar, + isnotnull__check_this boolean +) RETURNS integer AS $$ +DECLARE +BEGIN -create function ut_assert__isnull ( - varchar, - boolean, - boolean, - boolean -) returns integer as ' -declare - isnull__msg alias for $1; - isnull__check_this alias for $2; - isnull__null_ok alias for $3; -- default false - isnull__raise_exc alias for $4; -- default false -begin + return ut_assert__isnotnull(isnotnull__msg,isnotnull__check_this,'f','f'); + +END; +$$ LANGUAGE plpgsql; + + + +-- +-- procedure ut_assert__isnull/4 +-- +CREATE OR REPLACE FUNCTION ut_assert__isnull( + isnull__msg varchar, + isnull__check_this boolean, + isnull__null_ok boolean, -- default false + isnull__raise_exc boolean -- default false + +) RETURNS integer AS $$ +DECLARE +BEGIN return ut_assert__this ( - ''IS NULL: '' || isnull__msg, + 'IS NULL: ' || isnull__msg, isnull__check_this IS NULL, isnull__null_ok, isnull__raise_exc ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- Overload for calls with default values -create function ut_assert__isnull ( - varchar, - boolean -) -returns integer as ' -declare - isnull__msg alias for $1; - isnull__check_this alias for $2; -begin - return ut_assert__isnull(isnull__msg,isnull__check_this,''f'',''f''); -end;' language 'plpgsql'; +-- +-- procedure ut_assert__isnull/2 +-- +CREATE OR REPLACE FUNCTION ut_assert__isnull( + isnull__msg varchar, + isnull__check_this boolean +) RETURNS integer AS $$ +DECLARE +BEGIN + return ut_assert__isnull(isnull__msg,isnull__check_this,'f','f'); +END; +$$ LANGUAGE plpgsql; + + Index: openacs-4/packages/acs-events/sql/postgresql/test/utest.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/postgresql/test/utest.sql,v diff -u -N -r1.3 -r1.4 --- openacs-4/packages/acs-events/sql/postgresql/test/utest.sql 17 May 2003 09:47:26 -0000 1.3 +++ openacs-4/packages/acs-events/sql/postgresql/test/utest.sql 30 Mar 2013 13:00:30 -0000 1.4 @@ -7,298 +7,304 @@ -- -- $Id$ -create function inline_0 () -returns integer as ' -declare + + +-- +-- procedure inline_0/0 +-- +CREATE OR REPLACE FUNCTION inline_0( + +) RETURNS integer AS $$ +DECLARE v_str varchar; v_datetest timestamp; v_dateref timestamp; -begin +BEGIN PERFORM ut_assert__eq( - ''Test of ut_assert__eq (equality).'', - ''1'', - ''1'', - ''f'', - ''t'' + 'Test of ut_assert__eq (equality).', + '1', + '1', + 'f', + 't' ); PERFORM ut_assert__eq( - ''Test of ut_assert__eq (equality).'', - ''1'', - ''1'' + 'Test of ut_assert__eq (equality).', + '1', + '1' ); PERFORM ut_assert__eq( - ''Test of ut_assert__eq (inequality).'', - ''1'', - ''0'', - ''f'', - ''f'' -- we dont want to raise an exception here + 'Test of ut_assert__eq (inequality).', + '1', + '0', + 'f', + 'f' -- we dont want to raise an exception here ); PERFORM ut_assert__eq( - ''Test of ut_assert__eq (inequality).'', - ''1'', - ''0'' + 'Test of ut_assert__eq (inequality).', + '1', + '0' ); PERFORM ut_assert__eq( - ''Test of ut_assert__b2v (true).'', + 'Test of ut_assert__b2v (true).', ut_assert__b2v(1+1 = 2), - ''true'', - ''f'', - ''t'' + 'true', + 'f', + 't' ); PERFORM ut_assert__eq( - ''Test of ut_assert__b2v (true).'', + 'Test of ut_assert__b2v (true).', ut_assert__b2v(1+1 = 2), - ''true'' + 'true' ); PERFORM ut_assert__eq( - ''Test of ut_assert__b2v (false).'', + 'Test of ut_assert__b2v (false).', ut_assert__b2v(1+1 = 1), - ''false'', - ''f'', - ''t'' + 'false', + 'f', + 't' ); PERFORM ut_assert__eq( - ''Test of ut_assert__b2v (false).'', + 'Test of ut_assert__b2v (false).', ut_assert__b2v(1+1 = 1), - ''false'' + 'false' ); PERFORM ut_assert__eq( - ''Test of ut_assert__eq (boolean,boolean).'', + 'Test of ut_assert__eq (boolean,boolean).', 1+1 = 2, - ''true'', - ''f'', - ''t'' + 'true', + 'f', + 't' ); PERFORM ut_assert__eq( - ''Test of ut_assert__eq (boolean,boolean).'', + 'Test of ut_assert__eq (boolean,boolean).', 1+1 = 2, - ''true'' + 'true' ); PERFORM ut_assert__eq( - ''Test of ut_assert__eq (boolean,boolean).'', + 'Test of ut_assert__eq (boolean,boolean).', 1+1 = 1, - ''false'', - ''f'', - ''t'' + 'false', + 'f', + 't' ); PERFORM ut_assert__eq( - ''Test of ut_assert__eq (boolean,boolean).'', + 'Test of ut_assert__eq (boolean,boolean).', 1+1 = 1, - ''false'' + 'false' ); select now() into v_dateref; v_datetest := v_dateref; PERFORM ut_assert__eq( - ''Test of ut_assert__eq (timestamp,timestamp).'', + 'Test of ut_assert__eq (timestamp,timestamp).', v_datetest, v_dateref, - ''f'', - ''f'' + 'f', + 'f' ); PERFORM ut_assert__eq( - ''Test of ut_assert__eq (timestamp,timestamp).'', + 'Test of ut_assert__eq (timestamp,timestamp).', v_datetest, v_dateref ); - v_datetest := now() + interval ''1 days''; + v_datetest := now() + interval '1 days'; PERFORM ut_assert__eq( - ''Test of ut_assert__eq (timestamp,timestamp).'', + 'Test of ut_assert__eq (timestamp,timestamp).', v_datetest, v_dateref, - ''f'', - ''f'' -- do not raise exception + 'f', + 'f' -- do not raise exception ); PERFORM ut_assert__eq( - ''Test of ut_assert__eq (timestamp,timestamp).'', + 'Test of ut_assert__eq (timestamp,timestamp).', v_datetest, v_dateref ); PERFORM ut_assert__ieqminus( - ''Test of query equality.'', - ''select 1 from dual'', - ''select 1 from dual'', - ''Simple select from dual.'', - ''t'' + 'Test of query equality.', + 'select 1 from dual', + 'select 1 from dual', + 'Simple select from dual.', + 't' ); PERFORM ut_assert__ieqminus( - ''Test of query inequality.'', - ''select 1 from dual'', - ''select 2 from dual'', - ''simple select from dual '', - ''f'' -- do not raise exception since this will fail + 'Test of query inequality.', + 'select 1 from dual', + 'select 2 from dual', + 'simple select from dual ', + 'f' -- do not raise exception since this will fail ); create table ut_temp ( an_integer integer, a_varchar varchar); - insert into ut_temp values (1,''a''); - insert into ut_temp values (2,''b''); + insert into ut_temp values (1,'a'); + insert into ut_temp values (2,'b'); PERFORM ut_assert__ieqminus( - ''Test of query equality.'', - ''select * from ut_temp where an_integer = 1'', - ''select * from ut_temp where a_varchar = '' || '''''''' || ''a'' || '''''''', - ''Simple comparison of two tables.'', - ''t'' + 'Test of query equality.', + 'select * from ut_temp where an_integer = 1', + 'select * from ut_temp where a_varchar = ' || '''' || 'a' || '''', + 'Simple comparison of two tables.', + 't' ); PERFORM ut_assert__ieqminus( - ''Test of query inequality.'', - ''select * from ut_temp where an_integer = 2'', - ''select * from ut_temp'', - ''Simple comparison of two unequal tables.'', - ''f'' + 'Test of query inequality.', + 'select * from ut_temp where an_integer = 2', + 'select * from ut_temp', + 'Simple comparison of two unequal tables.', + 'f' ); create table ut_another as select * from ut_temp; PERFORM ut_assert__eqtable( - ''Test of simple table equality.'', - ''ut_another'', - ''ut_temp'', + 'Test of simple table equality.', + 'ut_another', + 'ut_temp', null, null, - ''t'' + 't' ); PERFORM ut_assert__eqtable( - ''Test of simple table equality.'', - ''ut_another'', - ''ut_temp'' + 'Test of simple table equality.', + 'ut_another', + 'ut_temp' ); PERFORM ut_assert__eqtable( - ''Test of simple table equality.'', - ''ut_another'', - ''ut_temp'', - ''an_integer = 1'', - ''a_varchar = '' || '''''''' || ''a'' || '''''''', - ''t'' + 'Test of simple table equality.', + 'ut_another', + 'ut_temp', + 'an_integer = 1', + 'a_varchar = ' || '''' || 'a' || '''', + 't' ); PERFORM ut_assert__eqtable( - ''Test of simple table inequality.'', - ''ut_another'', - ''ut_temp'', - ''an_integer = 1'', - ''a_varchar = '' || '''''''' || ''b'' || '''''''', - ''f'' + 'Test of simple table inequality.', + 'ut_another', + 'ut_temp', + 'an_integer = 1', + 'a_varchar = ' || '''' || 'b' || '''', + 'f' ); PERFORM ut_assert__eqtabcount( - ''Test of simple table count equality.'', - ''ut_another'', - ''ut_temp'', + 'Test of simple table count equality.', + 'ut_another', + 'ut_temp', null, null, - ''t'' + 't' ); PERFORM ut_assert__eqtabcount( - ''Test of simple table count equality.'', - ''ut_another'', - ''ut_temp'', - ''an_integer = 1'', - ''a_varchar = '' || '''''''' || ''a'' || '''''''', - ''t'' + 'Test of simple table count equality.', + 'ut_another', + 'ut_temp', + 'an_integer = 1', + 'a_varchar = ' || '''' || 'a' || '''', + 't' ); PERFORM ut_assert__eqtabcount( - ''Test of simple table inequality.'', - ''ut_another'', - ''ut_temp'', + 'Test of simple table inequality.', + 'ut_another', + 'ut_temp', null, - ''a_varchar = '' || '''''''' || ''b'' || '''''''', - ''f'' + 'a_varchar = ' || '''' || 'b' || '''', + 'f' ); PERFORM ut_assert__eqquery( - ''Test of query equality.'', - ''select * from ut_temp where an_integer = 1'', - ''select * from ut_temp where a_varchar = '' || '''''''' || ''a'' || '''''''', - ''t'' + 'Test of query equality.', + 'select * from ut_temp where an_integer = 1', + 'select * from ut_temp where a_varchar = ' || '''' || 'a' || '''', + 't' ); PERFORM ut_assert__eqquery( - ''Test of query equality.'', - ''select * from ut_temp where an_integer = 1'', - ''select * from ut_temp where a_varchar = '' || '''''''' || ''a'' || '''''''' + 'Test of query equality.', + 'select * from ut_temp where an_integer = 1', + 'select * from ut_temp where a_varchar = ' || '''' || 'a' || '''' ); PERFORM ut_assert__eqquery( - ''Test of query equality.'', - ''select * from ut_temp where an_integer = 2'', - ''select * from ut_temp'', - ''f'' + 'Test of query equality.', + 'select * from ut_temp where an_integer = 2', + 'select * from ut_temp', + 'f' ); PERFORM ut_assert__eqquery( - ''Test of query equality.'', - ''select * from ut_temp where an_integer = 2'', - ''select * from ut_temp'' + 'Test of query equality.', + 'select * from ut_temp where an_integer = 2', + 'select * from ut_temp' ); delete from ut_another where an_integer=2; PERFORM ut_assert__eqtable( - ''Test of simple table inequality.'', - ''ut_another'', - ''ut_temp'', + 'Test of simple table inequality.', + 'ut_another', + 'ut_temp', null, null, - ''f'' + 'f' ); PERFORM ut_assert__eqtable( - ''Test of simple table inequality.'', - ''ut_another'', - ''ut_temp'' + 'Test of simple table inequality.', + 'ut_another', + 'ut_temp' ); PERFORM ut_assert__isnotnull( - ''Degenerate test of non-null'', - ''1'', - ''f'', - ''t'' + 'Degenerate test of non-null', + '1', + 'f', + 't' ); PERFORM ut_assert__isnotnull( - ''Degenerate test of non-null'', - ''1'' + 'Degenerate test of non-null', + '1' ); PERFORM ut_assert__isnull( - ''Degenerate test of null'', + 'Degenerate test of null', null, - ''f'', - ''t'' + 'f', + 't' ); PERFORM ut_assert__isnull( - ''Degenerate test of null'', + 'Degenerate test of null', null ); @@ -307,14 +313,14 @@ select into v_str a_varchar from ut_another where an_integer = 2; PERFORM ut_assert__isnull( - ''Degenerate test of null'', + 'Degenerate test of null', v_str, - ''f'', - ''t'' + 'f', + 't' ); PERFORM ut_assert__isnull( - ''Degenerate test of null'', + 'Degenerate test of null', v_str ); @@ -323,14 +329,14 @@ select into v_str a_varchar from ut_another where an_integer = 1; PERFORM ut_assert__isnotnull( - ''Degenerate test of null'', + 'Degenerate test of null', v_str, - ''f'', - ''t'' + 'f', + 't' ); PERFORM ut_assert__isnotnull( - ''Degenerate test of null'', + 'Degenerate test of null', v_str ); @@ -339,7 +345,8 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; select (case when inline_0 () = 0 Index: openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.1d-0.2d.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.1d-0.2d.sql,v diff -u -N -r1.4 -r1.5 --- openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.1d-0.2d.sql 21 Dec 2002 22:31:27 -0000 1.4 +++ openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.1d-0.2d.sql 30 Mar 2013 13:00:30 -0000 1.5 @@ -5,7 +5,7 @@ integer, timestamp, timestamp -) returns integer as ' +) returns integer as $$ DECLARE p_event_id alias for $1; p_start_date alias for $2; @@ -51,35 +51,31 @@ return p_event_id; END; -' language 'plpgsql'; +$$ LANGUAGE plpgsql; + -- to_interval() now returns 'timespan' not 'interval' -create or replace function to_interval ( - -- - -- Convert an integer to the specified interval - -- - -- Utility function so we do not have to remember how to escape - -- double quotes when we typecast an integer to an interval - -- - -- @author jowell@jsabino.com - -- - -- @param interval_number Integer to convert to interval - -- @param interval_units Interval units - -- - -- @return interval equivalent of interval_number, in interval_units units - -- - integer, - varchar -) -returns interval as ' -declare - interval__number alias for $1; - interval__units alias for $2; -begin + +-- added +select define_function_args('to_interval','number,units'); + +-- +-- procedure to_interval/2 +-- +CREATE OR REPLACE FUNCTION to_interval( + interval__number integer, + interval__units varchar + +) RETURNS interval AS $$ + +DECLARE +BEGIN + -- We should probably do unit checking at some point - return ('''''''' || interval__number || '' '' || interval__units || '''''''')::interval; + return ('''' || interval__number || ' ' || interval__units || '''')::interval; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.4d-0.4d1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.4d-0.4d1.sql,v diff -u -N -r1.3 -r1.4 --- openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.4d-0.4d1.sql 12 Mar 2004 18:48:49 -0000 1.3 +++ openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.4d-0.4d1.sql 30 Mar 2013 13:00:30 -0000 1.4 @@ -13,67 +13,42 @@ drop function acs_event__new (integer,varchar,text,boolean,text,integer,integer,integer,varchar,timestamptz,integer,varchar,integer); -create function acs_event__new ( - -- - -- 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 - -- - -- @return The id of the new event. - -- - integer, -- acs_events.event_id%TYPE, - varchar, -- acs_events.name%TYPE, - text, -- acs_events.description%TYPE, - boolean, -- acs_events.html_p%TYPE, - text, -- acs_events.status_summary%TYPE, - integer, -- acs_events.timespan_id%TYPE, - integer, -- acs_events.activity_id%TYPE, - integer, -- acs_events.recurrence_id%TYPE, - varchar, -- acs_object_types.object_type%TYPE, - timestamptz, -- acs_objects.creation_date%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar, -- acs_objects.creation_ip%TYPE, - integer -- acs_objects.context_id%TYPE, -) -returns integer as ' -- acs_events.event_id%TYPE -declare - new__event_id alias for $1; -- default null, - new__name alias for $2; -- default null, - new__description alias for $3; -- default null, - new__html_p alias for $4; -- default null - new__status_summary alias for $5; -- default null - new__timespan_id alias for $6; -- default null, - new__activity_id alias for $7; -- default null, - new__recurrence_id alias for $8; -- default null, - new__object_type alias for $9; -- default ''acs_event'', - new__creation_date alias for $10; -- default now(), - new__creation_user alias for $11; -- default null, - new__creation_ip alias for $12; -- default null, - new__context_id alias for $13; -- default null + + +-- added +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'); + +-- +-- procedure acs_event__new/13 +-- +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 + +) RETURNS integer AS $$ + -- acs_events.event_id%TYPE +DECLARE v_event_id acs_events.event_id%TYPE; -begin +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 + 't', -- security_inherit_p new__name, -- title null -- package_id ); @@ -86,64 +61,46 @@ return v_event_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; drop function acs_activity__new (integer,varchar,text,boolean,text,varchar,timestamptz,integer,varchar,integer); -create function acs_activity__new ( - -- - -- Create a new activity - -- - -- @author W. Scott Meeks - -- - -- @param activity_id Id to use for new activity - -- @param name Name of the activity - -- @param description Description of the activity - -- @param html_p Is the description HTML? - -- @param status_summary Additional status note (optional) - -- @param object_type 'acs_activity' - -- @param creation_date default now() - -- @param creation_user acs_object param - -- @param creation_ip acs_object param - -- @param context_id acs_object param - -- - -- @return The id of the new activity. - -- - integer, -- in acs_activities.activity_id%TYPE - varchar, -- in acs_activities.name%TYPE, - text, -- in acs_activities.description%TYPE - boolean, -- in acs_activities.html_p%TYPE - text, -- in acs_activities.status_summary%TYPE - varchar, -- in acs_object_types.object_type%TYPE - timestamptz, -- in acs_objects.creation_date%TYPE - integer, -- in acs_objects.creation_user%TYPE - varchar, -- in acs_objects.creation_ip%TYPE - integer -- in acs_objects.context_id%TYPE -) -returns integer as ' -- return acs_activities.activity_id%TYPE -declare - new__activity_id alias for $1; -- default null, - new__name alias for $2; - new__description alias for $3; -- default null, - new__html_p alias for $4; -- default ''f'', - new__status_summary alias for $5; -- default null, - new__object_type alias for $6; -- default ''acs_activity'' - new__creation_date alias for $7; -- default now(), - new__creation_user alias for $8; -- default null, - new__creation_ip alias for $9; -- default null, - new__context_id alias for $10; -- default null + + +-- added +select define_function_args('acs_activity__new','activity_id;null,name,description;null,html_p;f,status_summary;null,object_type;acs_activity,creation_date;now(),creation_user;null,creation_ip;null,context_id;null'); + +-- +-- procedure acs_activity__new/10 +-- +CREATE OR REPLACE FUNCTION acs_activity__new( + new__activity_id integer, -- default null, + new__name varchar, + new__description text, -- default null, + new__html_p boolean, -- default 'f', + new__status_summary text, -- default null, + new__object_type varchar, -- default 'acs_activity' + new__creation_date timestamptz, -- default now(), + new__creation_user integer, -- default null, + new__creation_ip varchar, -- default null, + new__context_id integer -- default null + +) RETURNS integer AS $$ + -- return acs_activities.activity_id%TYPE +DECLARE v_activity_id acs_activities.activity_id%TYPE; -begin +BEGIN v_activity_id := acs_object__new( new__activity_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 + 't', -- security_inherit_p new__name, -- title null -- package_id ); @@ -155,39 +112,31 @@ return v_activity_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; drop function acs_activity__edit (integer,varchar,text,boolean,text); -create function acs_activity__edit ( - -- - -- Update the name or description of an activity - -- - -- @author W. Scott Meeks - -- - -- @param activity_id activity to update - -- @param name optional New name for this activity - -- @param description optional New description for this activity - -- @param html_p optional New value of html_p for this activity - -- @param status_summary optional New value of status_summary for this activity - -- - -- @return 0 (procedure dummy) - -- - integer, -- acs_activities.activity_id%TYPE, - varchar, -- acs_activities.name%TYPE default null, - text, -- acs_activities.description%TYPE default null, - boolean, -- acs_activities.html_p%TYPE default null - text -- acs_activities.status_summary%TYPE default null, -) returns integer as ' -declare - edit__activity_id alias for $1; - edit__name alias for $2; -- default null, - edit__description alias for $3; -- default null, - edit__html_p alias for $4; -- default null - edit__status_summary alias for $5; -- default null -begin + +-- added +select define_function_args('acs_activity__edit','activity_id,name;null,description;null,html_p;null,status_summary;null'); + +-- +-- procedure acs_activity__edit/5 +-- +CREATE OR REPLACE FUNCTION acs_activity__edit( + edit__activity_id integer, + edit__name varchar, -- default null, + edit__description text, -- default null, + edit__html_p boolean, -- default null + edit__status_summary text -- default null + +) RETURNS integer AS $$ +DECLARE +BEGIN + update acs_activities set name = coalesce(edit__name, name), description = coalesce(edit__description, description), @@ -201,4 +150,5 @@ return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.5d1-0.5d2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.5d1-0.5d2.sql,v diff -u -N -r1.2 -r1.3 --- openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.5d1-0.5d2.sql 8 Aug 2006 21:26:13 -0000 1.2 +++ openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.5d1-0.5d2.sql 30 Mar 2013 13:00:30 -0000 1.3 @@ -9,11 +9,10 @@ -- -- @return The html_p or html_p of the activity associated with the event if html_p is null. -- - integer -- acs_events.event_id%TYPE + get_html_p__event_id integer ) -returns boolean as ' -- acs_events.html_p%TYPE +returns boolean as $$ declare - get_html_p__event_id alias for $1; -- in acs_events.event_id%TYPE v_html_p acs_events.html_p%TYPE; begin select coalesce(e.html_p, a.html_p) into v_html_p @@ -24,27 +23,25 @@ return v_html_p; -end;' language 'plpgsql'; +end; +$$ language plpgsql; -create or replace function acs_event__get_status_summary ( - -- - -- 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. - -- - integer -- acs_events.event_id%TYPE -) -returns boolean as ' -declare - get_status_summary__event_id alias for $1; -- acs_events.event_id%TYPE + + +-- added +select define_function_args('acs_event__get_status_summary','event_id'); + +-- +-- procedure acs_event__get_status_summary/1 +-- +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 +BEGIN select coalesce(e.status_summary, a.status_summary) into v_status_summary from acs_events e left join acs_activities a @@ -53,5 +50,6 @@ return v_status_summary; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.5d2-0.5d3.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.5d2-0.5d3.sql,v diff -u -N -r1.2 -r1.3 --- openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.5d2-0.5d3.sql 5 Apr 2005 19:47:39 -0000 1.2 +++ openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.5d2-0.5d3.sql 30 Mar 2013 13:00:30 -0000 1.3 @@ -1,5 +1,5 @@ -- backwards compatible 13 param version -create or replace function acs_event__new ( +CREATE OR REPLACE FUNCTION acs_event__new ( integer, varchar, text, @@ -13,75 +13,49 @@ integer, varchar, integer -) -returns integer as ' -begin +) 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'; +END; +$$ LANGUAGE plpgsql; -create or replace function acs_event__new ( - -- - -- 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 - -- - -- @return The id of the new event. - -- - integer, -- acs_events.event_id%TYPE, - varchar, -- acs_events.name%TYPE, - text, -- acs_events.description%TYPE, - boolean, -- acs_events.html_p%TYPE, - text, -- acs_events.status_summary%TYPE, - integer, -- acs_events.timespan_id%TYPE, - integer, -- acs_events.activity_id%TYPE, - integer, -- acs_events.recurrence_id%TYPE, - varchar, -- acs_object_types.object_type%TYPE, - timestamptz, -- acs_objects.creation_date%TYPE, - integer, -- acs_objects.creation_user%TYPE, - varchar, -- acs_objects.creation_ip%TYPE, - integer, -- acs_objects.context_id%TYPE, - integer -- acs_objects.package_id%TYPE, -) -returns integer as ' -- acs_events.event_id%TYPE -declare - new__event_id alias for $1; -- default null, - new__name alias for $2; -- default null, - new__description alias for $3; -- default null, - new__html_p alias for $4; -- default null - new__status_summary alias for $5; -- default null - new__timespan_id alias for $6; -- default null, - new__activity_id alias for $7; -- default null, - new__recurrence_id alias for $8; -- default null, - new__object_type alias for $9; -- default ''acs_event'', - new__creation_date alias for $10; -- default now(), - new__creation_user alias for $11; -- default null, - new__creation_ip alias for $12; -- default null, - new__context_id alias for $13; -- default null - new__package_id alias for $14; -- default null + + +-- added +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'); + +-- +-- procedure acs_event__new/14 +-- +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 + +) RETURNS integer AS $$ + -- acs_events.event_id%TYPE +DECLARE v_event_id acs_events.event_id%TYPE; -begin +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 + 't', -- security_inherit_p new__name, -- title new__package_id -- package_id ); @@ -94,35 +68,28 @@ return v_event_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function acs_event__new_instance ( - -- - -- 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. - -- - integer, -- acs_events.event_id%TYPE, - interval -) -returns integer as ' -- acs_events.event_id%TYPE -declare - new_instance__event_id alias for $1; - new_instance__date_offset alias for $2; + + +-- added +select define_function_args('acs_event__new_instance','event_id,date_offset'); + +-- +-- procedure acs_event__new_instance/2 +-- +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 +BEGIN -- Get event parameters select * into event_row @@ -147,7 +114,7 @@ v_timespan_id, -- timespan_id event_row.activity_id, -- activity_id event_row.recurrence_id, -- recurrence_id - ''acs_event'', -- object_type (default) + 'acs_event', -- object_type (default) now(), -- creation_date (default) object_row.creation_user, -- creation_user object_row.creation_ip, -- creation_ip @@ -157,5 +124,6 @@ return v_event_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; Index: openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.6d1-0.6d2.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.6d1-0.6d2.sql,v diff -u -N -r1.2 -r1.3 --- openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.6d1-0.6d2.sql 15 May 2007 20:14:15 -0000 1.2 +++ openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.6d1-0.6d2.sql 30 Mar 2013 13:00:30 -0000 1.3 @@ -1,50 +1,18 @@ -create or replace function acs_event__insert_instances ( - -- - -- 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) - -- - integer, -- acs_events.event_id%TYPE, - timestamptz -- default null -) -returns integer as ' -declare - insert_instances__event_id alias for $1; - insert_instances__cutoff_date alias for $2; -- default null + + +-- added +select define_function_args('acs_event__insert_instances','event_id,cutoff_date;null'); + +-- +-- procedure acs_event__insert_instances/2 +-- +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; @@ -66,7 +34,7 @@ rec_execute record; v_new_current_date timestamptz; v_offset_notice interval; -begin +BEGIN -- Get event parameters select * into event_row @@ -82,7 +50,7 @@ -- 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); + 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; @@ -119,9 +87,9 @@ -- Week has to be handled specially. -- Start with the beginning of the week containing the start date. - if v_interval_name = ''week'' + if v_interval_name = 'week' then - v_current_date := next_day(v_current_date - to_interval(7,''days''),''SUNDAY''); + 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; @@ -130,84 +98,84 @@ 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)) + 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'' + if v_interval_name = 'day' then - v_current_date := v_current_date + to_interval(v_n_intervals,''days''); + 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'' + 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 + 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'')); + 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 + 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'')); + 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 + 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 + 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'' + 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); + 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 + 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 + 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) - date_trunc(''day'',v_event_date) -- offset + date_trunc('day',v_week_date) - date_trunc('day',v_event_date) -- offset ); v_last_date_done := v_week_date; - else if date_trunc(''day'',v_week_date) > date_trunc(''day'',v_stop_date) + else if date_trunc('day',v_week_date) > date_trunc('day',v_stop_date) then -- Gone too far exit; @@ -220,13 +188,13 @@ end loop; -- Now move to next week with repeats. - v_current_date := (v_current_date :: timestamp + to_interval(7 * v_n_intervals,''days'')) :: timestamptz; + 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 + date_trunc('day',v_current_date ::timestamp) - date_trunc('day',v_event_date ::timestamp) -- offset ); v_last_date_done := v_current_date; end if; @@ -237,49 +205,27 @@ where recurrence_id = recurrence_row.recurrence_id; return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function time_interval__copy( - -- - -- Creates a new copy of a time interval, offset by optional offset - -- - -- JS: We need to be careful in interpreting the copy offset. - -- JS: Oracle interprets integers as full days when doing - -- JS: date arithmetic. Thus, - -- JS: - -- JS: select sysdate()+1 from dual; - -- JS: - -- JS: will yield the next date, correct up to the second of the next day - -- JS: that the query was run. - -- JS: - -- JS: In PostgreSQL, we need to specify the type of interval when - -- JS: doing date arithmetic. if, say, an integer is used in date arithmetic, - -- JS: the results are weird. For example, - -- JS: - -- JS: select now()+1 from dual; - -- JS: - -- JS: will yield the MIDNIGHT of the next date that the query was run, i.e., - -- JS: the timestamp is typecasted as a date with a day granularity. To get the - -- JS: same effect as Oracle, we need to use explicitly typecast the integer into - -- JS: a day interval. - -- - -- @author W. Scott Meeks - -- - -- @param interval_id Interval to copy - -- @param offset Interval is offset by this date interval - -- - -- @return interval_id of the copied interval - -- - integer, -- time_intervals.interval_id%TYPE, - interval -) -returns integer as ' -- time_intervals.interval_id%TYPE -declare - copy__interval_id alias for $1; - copy__offset alias for $2; -- default 0 + + +-- added +select define_function_args('time_interval__copy','interval_id,offset;0'); + +-- +-- procedure time_interval__copy/2 +-- +CREATE OR REPLACE FUNCTION time_interval__copy( + copy__interval_id integer, + copy__offset interval -- default 0 + +) RETURNS integer AS $$ +-- time_intervals.interval_id%TYPE +DECLARE interval_row time_intervals%ROWTYPE; v_foo timestamptz; -begin +BEGIN select * into interval_row from time_intervals where interval_id = copy__interval_id; @@ -289,38 +235,33 @@ (interval_row.end_date ::timestamp + copy__offset) :: timestamptz ); -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -- Allow editing only future recurrences create or replace function acs_event__recurrence_timespan_edit ( - integer, - timestamptz, - timestamptz -) returns integer as ' + p_event_id integer, + p_start_date timestamptz, + p_end_date timestamptz +) returns integer as $$ DECLARE - p_event_id alias for $1; - p_start_date alias for $2; - p_end_date alias for $3; BEGIN return acs_event__recurrence_timespan_edit ( p_event_id, p_start_date, p_end_date, - ''t''); -END;' language 'plpgsql'; + 't'); +END; +$$ LANGUAGE plpgsql; create or replace function acs_event__recurrence_timespan_edit ( - integer, - timestamptz, - timestamptz, - boolean -) returns integer as ' + p_event_id integer, + p_start_date timestamptz, + p_end_date timestamptz, + p_edit_past_events_p boolean +) returns integer as $$ DECLARE - p_event_id alias for $1; - p_start_date alias for $2; - p_end_date alias for $3; - p_edit_past_events_p alias for $4; v_timespan RECORD; v_one_start_date timestamptz; v_one_end_date timestamptz; @@ -335,7 +276,7 @@ where time_intervals.interval_id = timespans.interval_id and timespans.timespan_id = acs_events.timespan_id and event_id=p_event_id; -raise notice ''DAVEB RECURRENCE edit_past_events_p = % start date = %'',p_edit_past_events_p,p_start_date; +raise notice 'DAVEB RECURRENCE edit_past_events_p = % start date = %',p_edit_past_events_p,p_start_date; FOR v_timespan in select * from time_intervals @@ -345,7 +286,7 @@ 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) + and (p_edit_past_events_p = 't' or start_date >= v_one_start_date) LOOP PERFORM time_interval__edit(v_timespan.interval_id, v_timespan.start_date + (p_start_date - v_one_start_date), @@ -354,4 +295,5 @@ return p_event_id; END; -' language 'plpgsql'; + +$$ LANGUAGE plpgsql; Index: openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.6d2-0.6d3.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.6d2-0.6d3.sql,v diff -u -N -r1.2 -r1.3 --- openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.6d2-0.6d3.sql 29 Aug 2008 15:13:49 -0000 1.2 +++ openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.6d2-0.6d3.sql 30 Mar 2013 13:00:30 -0000 1.3 @@ -7,52 +7,20 @@ -- -- Fix Daylight Saving Time bug when creating recurring events -create or replace function acs_event__insert_instances ( - -- - -- 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) - -- - integer, -- acs_events.event_id%TYPE, - timestamptz -- default null -) -returns integer as ' -declare - insert_instances__event_id alias for $1; - insert_instances__cutoff_date alias for $2; -- default null + + +-- added +select define_function_args('acs_event__insert_instances','event_id,cutoff_date;null'); + +-- +-- procedure acs_event__insert_instances/2 +-- +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; @@ -74,7 +42,7 @@ rec_execute record; v_new_current_date timestamptz; v_offset_notice interval; -begin +BEGIN -- Get event parameters select * into event_row @@ -90,7 +58,7 @@ -- 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); + 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; @@ -127,9 +95,9 @@ -- Week has to be handled specially. -- Start with the beginning of the week containing the start date. - if v_interval_name = ''week'' + if v_interval_name = 'week' then - v_current_date := next_day(v_current_date - to_interval(7,''days''),''SUNDAY''); + 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; @@ -138,84 +106,84 @@ 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)) + 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'' + if v_interval_name = 'day' then - v_current_date := v_current_date + to_interval(v_n_intervals,''days''); + 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'' + 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 + 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'')); + 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 + 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'')); + 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 + 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 + 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'' + 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); + 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 + 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 + 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 + 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) + else if date_trunc('day',v_week_date) > date_trunc('day',v_stop_date) then -- Gone too far exit; @@ -228,13 +196,13 @@ end loop; -- Now move to next week with repeats. - v_current_date := (v_current_date :: timestamp + to_interval(7 * v_n_intervals,''days'')) :: timestamptz; + 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 + date_trunc('day',v_current_date ::timestamp) - date_trunc('day',v_event_date ::timestamp) -- offset ); v_last_date_done := v_current_date; end if; @@ -245,19 +213,16 @@ where recurrence_id = recurrence_row.recurrence_id; return 0; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; create or replace function acs_event__recurrence_timespan_edit ( - integer, - timestamptz, - timestamptz, - boolean -) returns integer as ' + p_event_id integer, + p_start_date timestamptz, + p_end_date timestamptz, + p_edit_past_events_p boolean +) returns integer as $$ DECLARE - p_event_id alias for $1; - p_start_date alias for $2; - p_end_date alias for $3; - p_edit_past_events_p alias for $4; v_timespan RECORD; v_one_start_date timestamptz; v_one_end_date timestamptz; @@ -281,13 +246,14 @@ 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) + 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); + (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'; + +$$ LANGUAGE plpgsql; Index: openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.6d3-0.6d4.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.6d3-0.6d4.sql,v diff -u -N -r1.1 -r1.2 --- openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.6d3-0.6d4.sql 8 Nov 2010 13:10:35 -0000 1.1 +++ openacs-4/packages/acs-events/sql/postgresql/upgrade/upgrade-0.6d3-0.6d4.sql 30 Mar 2013 13:00:30 -0000 1.2 @@ -13,27 +13,23 @@ drop view timespan_seq; drop view recurrence_seq; -create or replace function time_interval__new ( - -- - -- Creates a new time interval - -- - -- @author W. Scott Meeks - -- - -- @param start_date Sets this as start_date of new interval - -- @param end_date Sets this as end_date of new interval - -- - -- @return id of new time interval - -- - timestamptz, -- time_intervals.start_date%TYPE default null, - timestamptz -- time_intervals.end_date%TYPE default null -) -returns integer as ' -- time_intervals.interval_id%TYPE -declare - new__start_date alias for $1; -- default null, - new__end_date alias for $2; -- default null + + +-- added +select define_function_args('time_interval__new','start_date;null,end_date;null'); + +-- +-- procedure time_interval__new/2 +-- +CREATE OR REPLACE FUNCTION time_interval__new( + new__start_date timestamptz, -- default null, + new__end_date timestamptz -- default null + +) RETURNS integer AS $$ +DECLARE v_interval_id time_intervals.interval_id%TYPE; -begin - select nextval(''timespan_sequence'') into v_interval_id from dual; +BEGIN + select nextval('timespan_sequence') into v_interval_id from dual; insert into time_intervals (interval_id, start_date, end_date) @@ -42,37 +38,28 @@ return v_interval_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function timespan__new ( - -- - -- Creates a new timespan (20.20.10) - -- given a time_interval - -- - -- JS: Allow user to specify whether the itme interval is to be copied or not - -- JS: This gives more flexibility of not making a copy instead of requiring - -- JS: the caller responsible for deleting the copy. - -- - -- @author W. Scott Meeks - -- - -- @param interval_id Id of interval to be included/copied in timespan, - -- @param copy_p If true, make another copy of the interval, - -- else simply include the interval in the timespan - -- - -- @return Id of new timespan - -- - integer, -- time_intervals.interval_id%TYPE - boolean -) -returns integer as ' -- timespans.timespan_id%TYPE -declare - new__interval_id alias for $1; - new__copy_p alias for $2; + + +-- added + +-- +-- procedure timespan__new/2 +-- +CREATE OR REPLACE FUNCTION timespan__new( + new__interval_id integer, + new__copy_p boolean + +) RETURNS integer AS $$ +-- timespans.timespan_id%TYPE +DECLARE v_timespan_id timespans.timespan_id%TYPE; v_interval_id time_intervals.interval_id%TYPE; -begin +BEGIN -- get a new id; - select nextval(''timespan_sequence'') into v_timespan_id from dual; + select nextval('timespan_sequence') into v_timespan_id from dual; if new__copy_p then @@ -89,40 +76,32 @@ return v_timespan_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql; -create or replace function recurrence__new ( - -- - -- Creates a new recurrence - -- - -- @author W. Scott Meeks - -- - -- @param interval_type Sets interval_type of new recurrence - -- @param every_nth_interval Sets every_nth_interval of new recurrence - -- @param days_of_week Sets days_of_week of new recurrence - -- @param recur_until Sets recur_until of new recurrence - -- @param custom_func Sets name of custom recurrence function - -- - -- @return id of new recurrence - -- - varchar, -- recurrence_interval_types.interval_name%TYPE, - integer, -- recurrences.every_nth_interval%TYPE, - varchar, -- recurrences.days_of_week%TYPE default null, - timestamptz, -- recurrences.recur_until%TYPE default null, - varchar -- recurrences.custom_func%TYPE default null -) -returns integer as ' -- recurrences.recurrence_id%TYPE -declare - new__interval_name alias for $1; - new__every_nth_interval alias for $2; - new__days_of_week alias for $3; -- default null, - new__recur_until alias for $4; -- default null, - new__custom_func alias for $5; -- default null + + +-- added +select define_function_args('recurrence__new','interval_name,every_nth_interval,days_of_week;null,recur_until;null,custom_func;null'); + +-- +-- procedure recurrence__new/5 +-- +CREATE OR REPLACE FUNCTION recurrence__new( + new__interval_name varchar, + new__every_nth_interval integer, + new__days_of_week varchar, -- default null, + new__recur_until timestamptz, -- default null, + new__custom_func varchar -- default null + +) RETURNS integer AS $$ + -- recurrences.recurrence_id%TYPE +DECLARE v_recurrence_id recurrences.recurrence_id%TYPE; v_interval_type_id recurrence_interval_types.interval_type%TYPE; -begin +BEGIN - select nextval(''recurrence_sequence'') into v_recurrence_id from dual; + select nextval('recurrence_sequence') into v_recurrence_id from dual; select interval_type into v_interval_type_id @@ -146,4 +125,5 @@ return v_recurrence_id; -end;' language 'plpgsql'; +END; +$$ LANGUAGE plpgsql;