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 --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-events/sql/postgresql/test/acs-events-test.sql 13 Jul 2001 03:16:32 -0000 1.1 @@ -0,0 +1,1157 @@ +-- packages/acs-events/sql/postgres/test/acs-events-test.sql +-- +-- Regression tests for ACS Events +-- +-- @author jowell@jsabino.com +-- @creation-date 2001-06-26 +-- +-- $Id: acs-events-test.sql,v 1.1 2001/07/13 03:16:32 jowells Exp $ + +-- Note: These tests use the semi-ported utPLSQL regression package +\i utest-create.sql + +create function ut__setup() +returns integer as ' +begin + + raise notice ''Setting up acs-events-test...''; + + -- create copies of the tables + -- No need for excute here? + create table ut_acs_events as + select * from acs_events; + create table ut_acs_event_party_map as + select * from acs_event_party_map; + + -- Auxiliary tables, so we do not mess up existing data (but.. why would you want to run + -- a regression test on a site with important data?) + create table ut_timespan_ids as + select timespan_id from timespans; + create table ut_activity_ids as + select activity_id from acs_activities; + create table ut_recurrence_ids as + select recurrence_id from recurrences; + create table ut_event_ids as + select event_id from acs_events; + + + return 0; + +end;' language 'plpgsql'; + + +create function ut__teardown() +returns integer as ' +begin + + raise notice ''Tearing down acs-events-test...''; + + -- remove copies of the tables + -- cascade does not work? + drop table ut_acs_events; + drop table ut_acs_event_party_map; + drop table ut_timespan_ids; + drop table ut_activity_ids; + drop table ut_recurrence_ids; + drop table ut_event_ids; + + return 0; + +end;' language 'plpgsql'; + +-- This is an example of a simple custom recurrence function: recur every three days +create function recur_every3(timestamp,integer) +returns timestamp 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'; + + + +-- 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 timestamp := ''2000-03-23 13:00''; + date2 timestamp := ''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 + + raise notice ''Testing INSERT_INSTANCES...''; + + -- Create event components + insert_instances__timespan_id := timespan__new(date1, date2); + insert_instances__recurrence_id := recurrence__new(''week'', + 1, + ''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'', + ''acs_activity'', + now(), + null, + null, + null + ); + + -- Check acs_activity__name + PERFORM ut_assert__eq (''Test of activity__name'', + acs_activity__name(insert_instances__activity_id), + ''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'', + acs_activity__name(insert_instances__activity_id), + ''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)'', + rec_events.description, + ''Making sure the acs_activity code works (pre-edit)''); + + PERFORM ut_assert__eq (''Test of activity__edit (html_p)'', + rec_events.html_p, + ''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''); + + PERFORM ut_assert__eq (''Test of activity__edit'', + acs_activity__name(insert_instances__activity_id), + ''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)'', + rec_events.description, + ''Making sure the acs_activity code works''); + + PERFORM ut_assert__eq (''Test of activity__edit (html_p)'', + rec_events.html_p, + ''f''); + end loop; + + + -- We test mapping of objects. We choose some object from acs_objects table to map. + -- Since we know that the activity object was just created, we might as well pick that one + -- (i.e., map activity to itself). + PERFORM acs_activity__object_map(insert_instances__activity_id, + insert_instances__activity_id); + + + -- 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'' + ); + + -- Create a null event for test of existence functions + insert_instances__event_id := acs_event__new(null, + null, + null, + null, + null, + null, + ''acs_event'', + now(), + null, + null, + null + ); + + + + -- Do some testing while we are here + PERFORM ut_assert__eq (''Test of INSTANCES_EXIST_P f within INSERT_INSTANCES'', + acs_event__instances_exist_p(insert_instances__recurrence_id), + ''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__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'', + acs_event__get_description(insert_instances__event_id) + ); + + PERFORM ut_assert__eq (''Test of RECURS_P f within INSERT_INSTANCES'', + acs_event__recurs_p(insert_instances__event_id), + ''f'' + ); + + + -- We now put values into the acs_events table + PERFORM acs_event__timespan_set(insert_instances__event_id, insert_instances__timespan_id); + PERFORM acs_event__activity_set(insert_instances__event_id, insert_instances__activity_id); + + -- No acs_event__recurrence_set? + update acs_events + set recurrence_id = insert_instances__recurrence_id + where event_id = insert_instances__event_id; + + -- Fill up the shadow table + update ut_acs_events + set timespan_id = insert_instances__timespan_id, + activity_id = insert_instances__activity_id, + recurrence_id = insert_instances__recurrence_id + 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'' + ); + + -- 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'', + acs_event__get_name(insert_instances__event_id), + ''Testing'' + ); + + -- and the description + 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'' + ); + + + -- More testing of acs-events value insertion + update acs_events + 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'', + acs_event__get_name(insert_instances__event_id), + ''Further Testing'' + ); + + 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.'' + ); + + -- Insert instances + PERFORM acs_event__insert_instances (insert_instances__event_id, + timestamp ''2000-06-02'' + ); + + -- Test for instances + PERFORM ut_assert__eq (''Test of RECURS_P t within INSERT_INSTANCES'', + acs_event__recurs_p(insert_instances__event_id), + ''t'' + ); + + PERFORM ut_assert__eq (''Test of INSTANCES_EXIST_P t within INSERT_INSTANCES'', + acs_event__instances_exist_p(insert_instances__recurrence_id), + ''t'' + ); + + + + -- Count instances + select count(*) + into v_instance_count + from acs_events + where recurrence_id = insert_instances__recurrence_id; + + 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'' + ); + + -- Check that instances match except for dates + PERFORM ut_assert__eqquery (''Test instances in INSERT_INSTANCES'', + ''select count(*) from (select name, description, activity_id + from acs_events + where recurrence_id = '' || + insert_instances__recurrence_id || + '' 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.''; + + 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; + end loop; + + + + -- Another test of weekly recurrence + insert_instances__timespan_id := timespan__new(timestamp ''2001-10-21 09:00:00'', + timestamp ''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'', + 1, + ''4 6'', + to_date(''2001-12-01'',''YYYY-MM-DD''), + null); + + insert_instances__event_id := acs_event__new(null,''Weekly'',null, + insert_instances__timespan_id, + insert_instances__activity_id, + insert_instances__recurrence_id, + ''acs_event'',now(),null,null,null + ); + + + + PERFORM acs_event__insert_instances (insert_instances__event_id, + timestamp ''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'' + ); + + + 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, + rec_events.start_date,rec_events.end_date; + end loop; + + ---------------------------------------------------------------------------------------------------------- + + + -- Test month_by_date recurrence + insert_instances__timespan_id := timespan__new(timestamp ''2001-03-21 09:00:00'', + timestamp ''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'', + 1, + null, -- irrelevant + to_date(''2001-05-01'',''YYYY-MM-DD''), + null); + + insert_instances__event_id := acs_event__new(null,''month_by_date'',null, + insert_instances__timespan_id, + insert_instances__activity_id, + insert_instances__recurrence_id, + ''acs_event'',now(),null,null,null + ); + + + + PERFORM acs_event__insert_instances (insert_instances__event_id, + timestamp ''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'' + ); + + + -- Check dates + -- Just print them out and eyeball them for now. + + + 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; + end loop; + + -- Test month_by_date recurrence + insert_instances__timespan_id := timespan__new(timestamp ''2001-10-21 09:00:00'', + timestamp ''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'', + 1, + null, -- irrelevant + to_date(''2002-02-01'',''YYYY-MM-DD''), + null); + + insert_instances__event_id := acs_event__new(null,''month_by_date'',null, + insert_instances__timespan_id, + insert_instances__activity_id, + insert_instances__recurrence_id, + ''acs_event'',now(),null,null,null + ); + + + + PERFORM acs_event__insert_instances (insert_instances__event_id, + timestamp ''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'' + ); + + + -- Check dates + -- Just print them out and eyeball them for now. + + 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; + end loop; + + + + ---------------------------------------------------------------------------------------------------------- + + + + -- Check another recurrence type (daily recurrence) + -- First, we need a new timespan,recurrence and activity + insert_instances__timespan_id := timespan__new(timestamp ''2001-03-26 09:00:00'', + timestamp ''2001-03-26 10:00:00''); + + -- Check month by date (recur every day, skip every second interval) + insert_instances__recurrence_id := recurrence__new(''day'', + 2, -- skip a day + null, -- Irrelevant + to_date(''2001-04-13'',''YYYY-MM-DD''), + null); + + + insert_instances__event_id := acs_event__new(null,''every 2 days'',null, + insert_instances__timespan_id, + insert_instances__activity_id, + insert_instances__recurrence_id, + ''acs_event'',now(),null,null,null + ); + + + -- Cut-off date should have no effect + PERFORM acs_event__insert_instances (insert_instances__event_id, + timestamp ''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'' + ); + + + -- Check dates + -- Just print them out and eyeball them for now. + + 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, + 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(timestamp ''2001-10-26 09:00:00'', + timestamp ''2001-10-26 10:00:00''); + + -- Check month by date (recur every day, skip every second interval) + insert_instances__recurrence_id := recurrence__new(''day'', + 2, + null, -- Irrelevant + to_date(''2001-11-13'',''YYYY-MM-DD''), + null); + + + insert_instances__event_id := acs_event__new(null,''every 2 days'',null, + insert_instances__timespan_id, + insert_instances__activity_id, + insert_instances__recurrence_id, + ''acs_event'',now(),null,null,null + ); + + + -- Cut-off date should have no effect + PERFORM acs_event__insert_instances (insert_instances__event_id, + timestamp ''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'' + ); + + + -- Check dates + -- Just print them out and eyeball them for now. + + 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, + 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(timestamp ''2001-04-01 09:00:00'', + timestamp ''2001-04-01 10:00:00''); + + -- Check month by date (recur every day, skip every second interval) + insert_instances__recurrence_id := recurrence__new(''year'', + 1, + null, -- Irrelevant + to_date(''2002-04-10'',''YYYY-MM-DD''), + null); + + + insert_instances__event_id := acs_event__new(null,''yearly (one DST day)'',null, + insert_instances__timespan_id, + insert_instances__activity_id, + insert_instances__recurrence_id, + ''acs_event'',now(),null,null,null + ); + + + -- Cut-off date should have no effect + PERFORM acs_event__insert_instances (insert_instances__event_id, + timestamp ''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'' + ); + + + 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, + 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(timestamp ''2001-04-03 09:00:00'', + timestamp ''2001-04-03 10:00:00''); + + -- Check month by date (recur every day, skip every second interval) + insert_instances__recurrence_id := recurrence__new(''year'', + 1, + null, -- Irrelevant + to_date(''2002-04-10'',''YYYY-MM-DD''), + null); + + + insert_instances__event_id := acs_event__new(null,''yearly (non-DST)'',null, + insert_instances__timespan_id, + insert_instances__activity_id, + insert_instances__recurrence_id, + ''acs_event'',now(),null,null,null + ); + + + -- Cut-off date should have no effect + PERFORM acs_event__insert_instances (insert_instances__event_id, + timestamp ''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'' + ); + + + + 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, + 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(timestamp ''2001-10-28 09:00:00'', + timestamp ''2001-10-28 10:00:00''); + + -- Check month by date (recur every day, skip every second interval) + insert_instances__recurrence_id := recurrence__new(''year'', + 1, + null, -- Irrelevant + to_date(''2002-10-30'',''YYYY-MM-DD''), + null); + + + insert_instances__event_id := acs_event__new(null,''yearly (DST)'',null, + insert_instances__timespan_id, + insert_instances__activity_id, + insert_instances__recurrence_id, + ''acs_event'',now(),null,null,null + ); + + + -- Cut-off date should have no effect + PERFORM acs_event__insert_instances (insert_instances__event_id, + timestamp ''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'' + ); + + 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, + 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(timestamp ''2001-02-06 09:00:00'', + timestamp ''2001-02-07 10:00:00''); + + insert_instances__recurrence_id := recurrence__new(''last_of_month'', + 1, + null, -- Irrelevant + to_date(''2001-12-10'',''YYYY-MM-DD''), + null); + + + insert_instances__event_id := acs_event__new(null,''last_of_month'',null, + insert_instances__timespan_id, + insert_instances__activity_id, + insert_instances__recurrence_id, + ''acs_event'',now(),null,null,null + ); + + + -- Cut-off date should have no effect + PERFORM acs_event__insert_instances (insert_instances__event_id, + timestamp ''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'' + ); + + + -- 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.''; + + 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; + end loop; + + + + -- First, we need a new timespan,recurrence and activity + insert_instances__timespan_id := timespan__new(timestamp ''2001-08-06 09:00:00'', + timestamp ''2001-08-07 10:00:00''); + + insert_instances__recurrence_id := recurrence__new(''last_of_month'', + 1, + null, -- Irrelevant + to_date(''2002-05-10'',''YYYY-MM-DD''), + null); + + + insert_instances__event_id := acs_event__new(null,''last_of_month'',null, + insert_instances__timespan_id, + insert_instances__activity_id, + insert_instances__recurrence_id, + ''acs_event'',now(),null,null,null + ); + + + -- Cut-off date should have no effect + PERFORM acs_event__insert_instances (insert_instances__event_id, + timestamp ''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'' + ); + + + -- 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.''; + + 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; + end loop; + + + ---------------------------------------------------------------------------------------------------------- + -- First, we need a new timespan,recurrence and activity + insert_instances__timespan_id := timespan__new(timestamp ''2001-08-06 09:00:00'', + timestamp ''2001-08-07 10:00:00''); + + insert_instances__recurrence_id := recurrence__new(''custom'', + 1, + null, -- Irrelevant + to_date(''2001-08-20'',''YYYY-MM-DD''), + ''recur_every3''); + + + insert_instances__event_id := acs_event__new(null,''custom'',null, + insert_instances__timespan_id, + insert_instances__activity_id, + insert_instances__recurrence_id, + ''acs_event'',now(),null,null,null + ); + + + -- Cut-off date should have no effect + PERFORM acs_event__insert_instances (insert_instances__event_id, + timestamp ''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'' + ); + + + -- Check dates + -- Just print them out and eyeball them for now. + + 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; + end loop; + + + ---------------------------------------------------------------------------------------------------------- + + -- First, we need a new timespan,recurrence and activity + insert_instances__timespan_id := timespan__new(timestamp ''2001-02-06 09:00:00'', + timestamp ''2001-02-07 10:00:00''); + + insert_instances__recurrence_id := recurrence__new(''month_by_day'', + 1, + null, -- Irrelevant + to_date(''2001-12-10'',''YYYY-MM-DD''), + null); + + + insert_instances__event_id := acs_event__new(null,''month_by_day'',null, + insert_instances__timespan_id, + insert_instances__activity_id, + insert_instances__recurrence_id, + ''acs_event'',now(),null,null,null + ); + + + -- Cut-off date should have no effect + PERFORM acs_event__insert_instances (insert_instances__event_id, + timestamp ''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'' + ); + + + -- 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''; + + 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, + rec_events.event_id,rec_events.recurrence_id; + end loop; + + + -- While we are here, let us test shift_all + -- Let us shift the start date of event by two days, end date by two days + PERFORM acs_event__shift_all(insert_instances__event_id,2,3); + + + -- Let us eyeball for now. + 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; + end loop; + + + + ---------------------------------------------------------------------------------------------------------- + + -- Timespan to shift + insert_instances__timespan_id := timespan__new(timestamp ''2001-02-06 09:00:00'', + timestamp ''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'', + 1, + null, -- Irrelevant + 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,''acs_event'',now(),null,null,null); + insert_instances__event_id := acs_event__new(null,''Another event'',''Yet another event description'', + 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'', + acs_event__get_name(insert_instances__event_id), + ''Another event'' + ); + + -- and the description + 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'' + ); + + -- Let us eyeball for now. + 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; + 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.''; + + 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; + end loop; + + + + -- We test mapping of events to parties. We choose some party from parties table to map. + -- Since we know that the party with party_id of -1 always exists, we map this. + PERFORM acs_event__party_map(insert_instances__event_id,-1); + + + -- 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'' + ); + + + return 0; + +end;' language 'plpgsql'; + + +create function ut__delete_instances() +returns integer as ' +declare + rec_timespans record; + rec_recurrences record; + rec_activities record; + rec_events record; + v_dummy integer; +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. + select activity_id into v_dummy + from acs_activity_object_map + where activity_id = object_id; + + PERFORM acs_activity__object_unmap(v_dummy,v_dummy); + + + -- 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'' + ); + + -- Remember the event-party mapping? Unfortunately, we can only do the unmapping in a + -- separate transaction. Since we inserted only one mapping, we expect only one entry. + select event_id into v_dummy + from acs_event_party_map + where party_id = -1; + + PERFORM acs_event__party_unmap(v_dummy,-1); + + + -- 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'' + ); + + + + -- Clean up recurring events. Note that we need to subset the events to only nonrecurring events + -- since acs_event__delete_all will do nothing for non-recurring events (and this the test will fail + -- if we also test acs_event__delete_all for non-recurring events). + FOR rec_events IN + select * + from acs_events + where recurrence_id is not null + and event_id not in (select event_id from ut_event_ids) + LOOP + -- 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'' + ); + + END LOOP; + + + -- Clean up non-recurring events (all recurring events should be deleted above) + FOR rec_events IN + select * + from acs_events + where event_id not in (select event_id from ut_event_ids) + LOOP + -- This should delete recurring and nonrecurring events + PERFORM acs_event__delete(rec_events.event_id); + + -- 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'' + ); + + END LOOP; + + + + -- Clean up remaining activities in the regression + FOR rec_activities IN + select * + from acs_activities + where activity_id not in (select activity_id from ut_activity_ids) + LOOP + 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'' + ); + END LOOP; + + + + -- Clean up regression recurrences + FOR rec_recurrences IN + select * + from recurrences + where recurrence_id not in (select recurrence_id from ut_recurrence_ids) + LOOP + 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'' + ); + + + END LOOP; + + -- Clean up regression timespans. Note that timespans API is regression-tested separately, + -- so no need to redo it here. + FOR rec_timespans IN + select * + from timespans + where timespan_id not in (select timespan_id from ut_timespan_ids) + LOOP + PERFORM timespan__delete(rec_timespans.timespan_id); + + END LOOP; + + + + return 0; + +end;' language 'plpgsql'; + + +-- Call the regression test +select (case when ut__setup() = 0 + then + 'Set up a success.' + end) as setup_result; + +select (case when ut__insert_instances() = 0 + then + 'Insert instances a success.' + end) as insert_instances_result; + +select (case when ut__delete_instances() = 0 + then + 'Delete instances a success.' + end) as delete_instances_result; + +select (case when ut__teardown() = 0 + then + 'Tear down a success.' + end) as teardown_result; + +drop function recur_every3(timestamp,integer); + +-- This depends on openacs4 installed. +select drop_package('ut'); + +-- End of regression test +\i utest-drop.sql + 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 --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-events/sql/postgresql/test/time_intervals-test.sql 13 Jul 2001 03:16:32 -0000 1.1 @@ -0,0 +1,607 @@ +-- packages/acs-events/sql/postgres/test/time_interval-test.sql +-- +-- Regression tests for time_interval API +-- +-- @author jowell@jsabino.com +-- @creation-date 2001-06-26 +-- +-- $Id: time_intervals-test.sql,v 1.1 2001/07/13 03:16:32 jowells Exp $ + +-- Note: These tests use the semi-ported utPLSQL regression package +\i utest-create.sql + +-- Set-up the regression test +create function ut__setup() +returns integer as ' +begin + + 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'' + ); + + -- 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'; + + +-- Clean up the mess that regression testing did +create function ut__teardown() +returns integer as ' +begin + + raise notice ''Tearing down time_intervals test...''; + + -- Delete intervals added by tests + -- cascade delete in timespans should delete corresponding entries in that table + -- Note that we exclude deleting rows that existed prior to regression test + delete from ut_time_intervals + where interval_id not in (select interval_id + from ut_interval_ids); + + + + -- Drop test tables + -- cascade option does not work? + drop table ut_time_intervals; + drop table ut_interval_ids; + + return 0; + +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( + timestamp, + timestamp +) +returns integer as ' +declare + new__date1 alias for $1; + new__date2 alias for $2; + new__interval_id time_intervals.interval_id%TYPE; + v_result integer; +begin + + 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:'', + 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; + 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'' + ); + + -- If successful, interval id is correct + return new__interval_id; + +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...''; + + -- Delete entry from shadow table + delete from ut_time_intervals + where interval_id = delete__interval_id; + + -- Delete the row from actual table + PERFORM time_interval__delete(delete__interval_id); + + + -- Verify time interval not there. + PERFORM ut_assert__eqtable (''Delete verification'', + ''ut_time_intervals'', + ''time_intervals'' + ); + + -- If successful, interval id is correct + return 0; + +end;' language 'plpgsql'; + +create function ut__edit ( + integer, -- time_intervals.interval_id%TYPE; + timestamp, + timestamp +) +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...''; + + -- Edit the time interval + PERFORM time_interval__edit(edit__interval_id,edit__start_date,edit__end_date); + + -- Verify + if edit__start_date is not null and edit__end_date is not null + then + update ut_time_intervals + set start_date = edit__start_date, + end_date = edit__end_date + where interval_id = edit__interval_id; + end if; + + if edit__start_date is null and edit__end_date is not null + then + update ut_time_intervals + set end_date = edit__end_date + where interval_id = edit__interval_id; + end if; + + if edit__start_date is not null and edit__end_date is null + then + update ut_time_intervals + set start_date = edit__start_date + where interval_id = edit__interval_id; + end if; + + PERFORM ut_assert__eqtable (''Comparing edited data for time interval'', + ''time_intervals'', + ''ut_time_intervals'' + ); + + return 0; + +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 + + PERFORM ut_assert__eq (eq__msg, + time_interval__eq(eq__interval_id_1, eq__interval_id_2), + eq__result + ); + return 0; + +end;' language 'plpgsql'; + + +create function ut__shift ( + integer, -- in time_intervals.interval_id%TYPE; + integer, + integer, + timestamp, + timestamp +) +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...''; + + -- Shift the time interval + PERFORM time_interval__shift(shift__interval_id, shift__offset_1, shift__offset_2); + + -- Verify + update ut_time_intervals + set start_date = shift__date1, + 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'' + ); + + return 0; + +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 + -- 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), + overlaps_p__result + ); + + return 0; + +end;' language 'plpgsql'; + +create function ut__overlaps_p( + varchar, + integer, -- time_intervals.interval_id%TYPE; + timestamp, -- time_intervals.start_date%TYPE; + timestamp, -- 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 + -- Test the time interval + PERFORM ut_assert__eq (overlaps_p__msg, + time_interval__overlaps_p(overlaps_p__interval_id, + overlaps_p__start_date, + overlaps_p__end_date), + overlaps_p__result + ); + + return 0; + +end;' language 'plpgsql'; + +create function ut__overlaps_p( + varchar, + timestamp, + timestamp, + timestamp, -- time_intervals.start_date%TYPE; + timestamp, -- 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 + -- Test the time interval + PERFORM ut_assert__eq (overlaps_p__msg, + time_interval__overlaps_p(overlaps_p__start_date_1, + overlaps_p__end_date_1, + overlaps_p__start_date_2, + overlaps_p__end_date_2), + overlaps_p__result + ); + + return 0; + +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; + v_interval_id time_intervals.interval_id%TYPE; + interval_row record; +begin + + raise notice ''Testing time_interval__copy...''; + + + -- Copy the time interval + v_interval_id := time_interval__copy(copy__interval_id,copy__offset); + + -- Get the copied start and end dates, before the offset + select * into interval_row + from time_intervals + where interval_id = copy__interval_id; + + -- Insert for testing + insert into ut_time_intervals (interval_id, start_date, end_date) + 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'' + ); + + return v_interval_id; + +end;' language 'plpgsql'; + + +create 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 + + raise notice ''Regression test, part 1 (creates and edits).''; + + -- We first check if the creation of time intervals work + v_interval_id := ut__new(timestamp ''2001-01-01'',timestamp ''2001-01-02''); + + -- Try to edit, putting new values for start date and end dates + PERFORM ut__edit(v_interval_id,timestamp ''2001-01-02'',timestamp ''2001-01-30''); + + -- Edit, but this time, change only the start date + PERFORM ut__edit(v_interval_id,timestamp ''2001-01-07'',null); + + -- Edit, but this time, change only the end date + PERFORM ut__edit(v_interval_id,null,timestamp ''2001-01-08''); + + -- We now test equality of (identical) intervals + PERFORM ut__eq(''Equal (same) intervals'',v_interval_id,v_interval_id,true); + + -- Create another interval for comparison + v_interval_id_ck := ut__new(timestamp ''2001-01-07'',timestamp ''2001-01-08''); + + -- We now test equality of (nonidentical) intervals + 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,timestamp ''2001-01-08'', timestamp ''2001-01-10''); + + -- Now test inequality of time intervals + 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,timestamp ''2001-01-07'', timestamp ''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); + + -- Shift the second interval, start date same, but the end date BACK by two days + PERFORM ut__shift(v_interval_id_ck,0,-2,timestamp ''2001-01-07'', timestamp ''2001-01-08''); + + -- Should be equal again + 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,timestamp ''2001-01-05'', timestamp ''2001-01-07''); + + -- Should be unequal again + 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); + + -- Ok, shift the dtart and end dates by one so that intervals do not overlap + PERFORM ut__shift(v_interval_id_ck,-1,-1,timestamp ''2001-01-04'', timestamp ''2001-01-06''); + + -- They should not overlap now. + 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'', + v_interval_id, + timestamp ''2001-01-06'', + timestamp ''2001-01-09'', + true); + + -- How about an interval next month? + PERFORM ut__overlaps_p(''Non-overlapping intervals'', + v_interval_id, + timestamp ''2001-02-06'', + timestamp ''2001-02-09'', + false); + + -- Try a null starting interval + PERFORM ut__overlaps_p(''Overlapping intervals (null start)'', + v_interval_id, + null, + timestamp ''2001-01-09'', + true); + + -- Try a null starting interval + PERFORM ut__overlaps_p(''Overlapping intervals (null end)'', + v_interval_id, + timestamp ''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)'', + v_interval_id, + timestamp ''2001-02-09'', + timestamp ''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)'', + v_interval_id, + timestamp ''2001-01-09'', + timestamp ''2001-01-06'', + false); + + -- Yet another overloaded definition + PERFORM ut__overlaps_p(''Overlapping intervals (not in time_intervals)'', + timestamp ''2001-01-06'', + timestamp ''2001-01-09'', + timestamp ''2001-01-07'', + timestamp ''2001-01-08'', + true); + + + -- Yet another overloaded definition + PERFORM ut__overlaps_p(''Overlapping intervals (not in time_intervals)'', + timestamp ''2001-01-06'', + timestamp ''2001-01-09'', + timestamp ''2001-01-09'', + timestamp ''2001-01-10'', + true); + + -- Yet another overloaded definition + PERFORM ut__overlaps_p(''Overlapping intervals (not in time_intervals)'', + timestamp ''2001-01-06'', + timestamp ''2001-01-09'', + null, + timestamp ''2001-01-10'', + true); + PERFORM ut__overlaps_p(''Overlapping intervals (not in time_intervals)'', + timestamp ''2001-01-06'', + timestamp ''2001-01-09'', + timestamp ''2001-01-10'', + null, + false); + + -- Yet another overloaded definition + PERFORM ut__overlaps_p(''Non-overlapping intervals (not in time_intervals)'', + timestamp ''2001-02-06'', + timestamp ''2001-02-09'', + timestamp ''2001-01-07'', + timestamp ''2001-01-08'', + false); + + + + -- Overwrite the check interval a copy, with zero offset + v_interval_id_ck := ut__copy(v_interval_id,0); + + + -- Should be equal + -- Now test equality of time intervals + 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); + + + -- 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'; + +create function ut__regression2() +returns integer as ' +declare + v_result integer := 0; + rec_interval record; +begin + + raise notice ''Regression test, part 2 (deletes).''; + + -- Remove all entries made by regression test + -- This also tests the deletion mechanism + FOR rec_interval IN + select * from time_intervals + where interval_id not in (select interval_id from ut_interval_ids) + LOOP + PERFORM ut__delete(rec_interval.interval_id); + END LOOP; + + -- 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'; + +-------------------------------------------------------------------------------- +-- Main regression test. PostgreSQL does not allow multiple changes made to a +-- primary key inside a transaction if the primary key is referenced by another +-- table (e.g., insert and delete). As a fix, we break down the regression test +-- so that row creations and edits are separate from row deletions +-------------------------------------------------------------------------------- +select (case when ut__setup() = 0 + then + 'Regression test properly set up.' + end) as setup_result; + +select (case when ut__regression1() = 0 + then + 'Regression test, part 1 successful.' + end) as test_result; + +select (case when ut__regression2() = 0 + then + 'Regression test, part 2 successful.' + end) as test_result; + +select (case when ut__teardown() = 0 + then + 'Regression test properly torn down.' + end) as teardown_result; + +-- Clean up created functions. +-- This depends on openacs4 installed. +select drop_package('ut'); + +-------------------------------------------------------------------------------- +-- End of regression test +-------------------------------------------------------------------------------- +\i utest-drop.sql + + + + + + + + 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 --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-events/sql/postgresql/test/timespan-test.sql 13 Jul 2001 03:16:32 -0000 1.1 @@ -0,0 +1,622 @@ +-- packages/acs-events/sql/postgresql/test/timespan-test.sql +-- +-- Regression tests for timespan API +-- Separated from time_interval-test.sql +-- +-- @author jowell@jsabino.com +-- @creation-date 2001-06-26 +-- +-- $Id: timespan-test.sql,v 1.1 2001/07/13 03:16:32 jowells Exp $ + +-- Note: These tests use the semi-ported utPLSQL regression package +\i utest-create.sql + +-- Set-up the regression test +create function ut__setup() +returns integer as ' +begin + + 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'' + ); + + -- Store keys that are in the table prior to the regresion test + create table ut_interval_ids as select interval_id from time_intervals; + create table ut_timespan_ids as select timespan_id from timespans; + + return 0; + +end;' language 'plpgsql'; + + +-- Clean up the mess that regression testing did +create function ut__teardown() +returns integer as ' +begin + + raise notice ''Tearing down timespans test...''; + + -- Delete intervals added by tests + -- cascade delete in timespans should delete corresponding entries in that table + -- Note that we exclude deleting rows that existed prior to regression test + delete from timespans + where timespan_id not in (select timespan_id + from ut_timespan_ids); + + -- This is sufficient, actually. + delete from time_intervals + where interval_id not in (select interval_id + from ut_interval_ids); + + + + -- Drop test tables + -- cascade option does not work? + drop table ut_timespans; + drop table ut_interval_ids; + drop table ut_timespan_ids; + + return 0; + +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; + v_interval_id time_intervals.interval_id%TYPE; + v_timespan_id timespans.timespan_id%TYPE; +begin + + -- The new function will create a copy on the time_intervals table + v_timespan_id := timespan__new(new__interval_id); + + -- Since the timespan__new function creates a copy of the interval + -- we need the copied interval_id + select interval_id into v_interval_id + from timespans + where timespan_id = v_timespan_id; + + -- Create shadow entries, too. + insert into ut_timespans (timespan_id,interval_id) + values (v_timespan_id,v_interval_id); + + -- 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: '', + 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'' + ); + + -- If successful, interval id is correct + return v_timespan_id; + +end;' language 'plpgsql'; + +-- We test the creation of a time interval entry +create function ut__new( + timestamp, + timestamp +) +returns integer as ' +declare + new__date1 alias for $1; + new__date2 alias for $2; + v_interval_id time_intervals.interval_id%TYPE; +begin + + -- We first want to create an entry in the time interval table + -- because the timespan_new function copies this interval + v_interval_id := time_interval__new(new__date1, new__date2); + + -- Create a new timespan using the function above + return ut__new(v_interval_id); + +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 + + -- Delete the row from actual table + PERFORM timespan__delete(delete__timespan_id); + + PERFORM ut_assert__eqtable (''Testing timespan__delete: '', + ''ut_timespans'', + ''timespans'' + ); + + -- Delete entry from shadow table + -- JS: Aha, a demonstration of the effect of transactions to foreign keys + -- JS: It seems that while timespan__delete would remove the row from + -- JS: time_intervals, the cascade delete removal of the corresponding row + -- JS: in timespans is not yet done until the transation is complete. Thus, + -- JS: deleting the row in the shadow table within this function/transaction + -- JS: will cause the comparison of the timespans table and the shadow table + -- JS: to fail (since delete will immediately remove the row from the shadow + -- JS: table). We do the delete outside this function/transaction instead. + -- Delete from shadow table + -- delete from ut_timespans + -- where timespan_id = delete__timespan_id; + + + -- If successful, interval id is correct + return 0; + +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; + 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 + + -- Get interval id of orginal interval (before join) + select interval_id into v_interval_id + from timespans + where timespan_id = join_interval__timespan_id; + + -- Join the supplied interval with existing interval + -- Return the interval_id being joined (will be different if copy_p = true) + v_interval_id_cp := timespan__join_interval(join_interval__timespan_id, + join_interval__interval_id, + join_interval__copy_p); + + -- Dont forget to put the newly created timepsan into the shadow table + insert into ut_timespans (timespan_id,interval_id) + 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(*) + from timespans + 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'' + ); + + + -- Did not do the interval check since it is dependent upon join_interval__copy_p + -- Besides, it seems silly to me: since there are only two intervals, checking table equality + -- AND checking that only two intervals are in the time span should be enough! + return 0; + +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; + rec_timespan record; +begin + + PERFORM timespan__join(join__timespan_id_1,join__timespan_id_2); + + -- Joining means that the intervals in join__timespan_id_2 are + -- included in the intervals in join__timespan_id_1 + FOR rec_timespan IN + select * + from timespans + where timespan_id = join__timespan_id_2 + LOOP + insert into ut_timespans (timespan_id,interval_id) + values (join__timespan_id_1,rec_timespan.interval_id); + END LOOP; + + + -- Check equality of tables + PERFORM ut_assert__eqtable (''Testing timespan__join by specifying timespan_id: '', + ''ut_timespans'', + ''timespans'' + ); + return 0; +end;' language 'plpgsql'; + +create function ut__join( + integer, -- timespans.timespan_id%TYPE; + timestamp, -- time_intervals.start_date%TYPE; + timestamp -- 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; + v_interval_id time_intervals.interval_id%TYPE; +begin + + + + v_interval_id := timespan__join(join__timespan_id,join__start_date,join__end_date); + + -- Joining means that the interval becomes part + -- of the timespan specified by join__timespan_id + insert into ut_timespans (timespan_id,interval_id) + 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'' + ); + return 0; +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; + 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 + + v_timespan_id := timespan__copy(copy__timespan_id,copy__offset); + + -- Put copy in shadow table. There may be more than one interval in a + -- time interval so we need to loop through all + for rec_timespan in + select * + from timespans + where timespan_id = v_timespan_id + loop + -- Populate the shadow table + insert into ut_timespans (timespan_id,interval_id) + values (rec_timespan.timespan_id,rec_timespan.interval_id); + end loop; + + -- Check proper population of shadow table + PERFORM ut_assert__eqtable (''Testing timespan__copy: '', + ''ut_timespans'', + ''timespans'' + ); + + return v_timespan_id; + +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: '', + timespan__exists_p(exists_p__timespan_id), + exists_p__result + ); + + return 0; + +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: '', + timespan__multi_interval_p(multi_interval_p__timespan_id), + multi_interval_p__result + ); +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: '', + timespan__overlaps_interval_p(overlaps_interval_p__timespan_id, + overlaps_interval_p__interval_id), + overlaps_interval_p__result + ); +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: '', + timespan__overlaps_p(overlaps_p__timespan_1_id, + overlaps_p__timespan_2_id), + overlaps_p__result + ); +end;' language 'plpgsql'; + +create function ut__overlaps_p( + integer, -- timespans.timespan_id%TYPE; + timestamp, -- time_intervals.start_date%TYPE; + timestamp, -- 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: '', + timespan__overlaps_p(overlaps_p__timespan_id, + overlaps_p__start_date, + overlaps_p__end_date), + overlaps_p__result + ); +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 + + PERFORM timespan__interval_delete(interval_delete__timespan_id,interval_delete__interval_id); + + -- Remove from shadow table + delete from ut_timespans + where timespan_id = interval_delete__timespan_id + and + interval_id = interval_delete__interval_id; + + return ut_assert__eqtable(''Testing timespan__interval_delete: '', + ''ut_timespans'', + ''timespans'' + ); +end;' language 'plpgsql'; + + +create 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 + + raise notice ''Regression test, part 1 (creates and edits).''; + + -- First create an interval + v_interval_id := time_interval__new(timestamp ''2001-01-01'',timestamp ''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(timestamp ''2001-01-25'',timestamp ''2001-02-02''); + + -- Test if timespan exists + PERFORM ut__exists_p(v_timespan_id,true); + + -- Unfortunately, we cannot delete the timespan and then check its non-existence + -- (transactions). So we check for a known non-existent timespan + PERFORM ut__exists_p(v_timespan_id+100,false); + + -- Check if multi-interval (obviously not) + PERFORM ut__multi_interval_p(v_timespan_id,false); + + -- The interval does not overlap the timespan + PERFORM ut__overlaps_interval_p(v_timespan_id,v_interval_id,false); + + -- Join the first interval with the second, without making a copy + PERFORM ut__join_interval(v_timespan_id,v_interval_id,false); + + -- Should now be a multi-interval timespan + PERFORM ut__multi_interval_p(v_timespan_id,true); + + -- Now that the interval is part of the timespan, they should overlap + PERFORM ut__overlaps_interval_p(v_timespan_id,v_interval_id,true); + + -- A new timespans + v_timespan_id := ut__new(timestamp ''2001-03-05'',timestamp ''2001-03-31''); + v_timespan_id_ck := ut__new(timestamp ''2001-06-05'',timestamp ''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,timestamp ''2001-02-06'',timestamp ''2001-03-25'',true); + PERFORM ut__overlaps_p(v_timespan_id,timestamp ''2001-03-07'',timestamp ''2001-04-01'',true); + PERFORM ut__overlaps_p(v_timespan_id,timestamp ''2001-01-01'',timestamp ''2001-03-20'',true); + PERFORM ut__overlaps_p(v_timespan_id,timestamp ''2001-01-01'',null,true); + PERFORM ut__overlaps_p(v_timespan_id,null,timestamp ''2001-04-01'',true); + PERFORM ut__overlaps_p(v_timespan_id,timestamp ''2001-04-01'',timestamp ''2001-04-30'',false); + PERFORM ut__overlaps_p(v_timespan_id,timestamp ''2001-02-01'',timestamp ''2001-02-27'',false); + + + -- Join the first interval with the second, making a copy + PERFORM ut__join_interval(v_timespan_id,v_interval_id,true); + + -- Join the two (the joined interval is longer) + PERFORM ut__join(v_timespan_id_ck,v_timespan_id); + + -- These timespans should now overlap + PERFORM ut__overlaps_p(v_timespan_id,v_timespan_id_ck,true); + + -- Join an interval instead + PERFORM ut__join(v_timespan_id_ck,timestamp ''2001-12-01'',timestamp ''2001-12-31''); + + -- Copy a timespan (will only contain two) + 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); + + + -- 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'; + +create function ut__regression2() +returns integer as ' +declare + v_result integer := 0; + rec_timespan record; +begin + + raise notice ''Regression test, part 2 (deletes).''; + + -- Remove all entries made by regression test + -- This also tests the deletion mechanism + FOR rec_timespan IN + select * from timespans + where timespan_id not in (select timespan_id from ut_timespan_ids) + LOOP + PERFORM ut__delete(rec_timespan.timespan_id); + + END LOOP; + + -- 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'; + +-------------------------------------------------------------------------------- +-- Main regression test. PostgreSQL does not allow multiple changes made to a +-- primary key inside a transaction if the primary key is referenced by another +-- table (e.g., insert and delete). As a fix, we break down the regression test +-- so that row creations and edits are separate from row deletions +-------------------------------------------------------------------------------- +select (case when ut__setup() = 0 + then + 'Regression test properly set up.' + end) as setup_result; + +select (case when ut__regression1() = 0 + then + 'Regression test, part 1 successful.' + end) as test_result; + + select * from time_intervals; + select * from timespans; + select * from ut_timespans; + +select (case when ut__regression2() = 0 + then + 'Regression test, part 2 successful.' + end) as test_result; + +-- Unfortunately, we need to recheck the deletion since we cannot put +-- actual deletion of entries in the shadow table inside the ut__delete +-- function due to the transactional nature of the functions +delete from ut_timespans +where timespan_id not in (select timespan_id from ut_timespan_ids); + +select (case when ut_assert__eqtable('Recheck of deletion','timespans','ut_timespans') = 0 + then + 'Recheck of deletion successful.' + end) as recheck_result; + + +select (case when ut__teardown() = 0 + then + 'Regression test properly torn down.' + end) as teardown_result; + +-- Clean up created functions. +-- This depends on openacs4 installed. +select drop_package('ut'); + +-------------------------------------------------------------------------------- +-- End of regression test +-------------------------------------------------------------------------------- +\i utest-drop.sql + + + + + + + + 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 --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-events/sql/postgresql/test/utest-create.sql 13 Jul 2001 03:16:32 -0000 1.1 @@ -0,0 +1,603 @@ +-- packages/acs-events/sql/postgresql/test/utest-create.sql +-- +-- Regression tests for timespan API +-- Separated from time_interval-test.sql +-- +-- @author jowell@jsabino.com +-- +-- @creation-date 2001-06-26 +-- +-- $Id: utest-create.sql,v 1.1 2001/07/13 03:16:32 jowells Exp $ + +-- /* +-- GNU General Public License for utPLSQL +-- +-- Copyright (C) 2000 +-- Steven Feuerstein, steven@stevenfeuerstein.com +-- Chris Rimmer, chris@sunset.force9.co.uk +-- +-- This program is free software; you can redistribute it and/or modify +-- it under the terms of the GNU General Public License as published by +-- the Free Software Foundation; either version 2 of the License, or +-- (at your option) any later version. +-- +-- This program is distributed in the hope that it will be useful, +-- but WITHOUT ANY WARRANTY; without even the implied warranty of +-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +-- GNU General Public License for more details. +-- +-- You should have received a copy of the GNU General Public License +-- along with this program (see license.txt); if not, write to the Free Software +-- Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA +-- */ + +-- 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 + + return expected__msg || + '': expected '' || + '''''''' || + expected__against_this || + '''''''' || + '', got '' || + '''''''' || + expected__check_this || + ''''''''; + +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 + + -- We always output the message (usually the result of the test) + raise notice ''%'',this__msg; + + if not this__check_this + or ( this__check_this is null + and not this__null_ok ) + then + + -- Raise an exception if a failure + if this__raise_exc + then + -- We should make the message more informative. + raise exception ''FAILURE''; + else + raise notice ''FAILURE, but forced to continue.''; + end if; + + end if; + + -- Continue if success; + return 0; + +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''); + +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 + 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'; + +-- 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'; + + +create function ut_assert__b2v ( + boolean -- IN BOOLEAN +) +returns varchar as ' +declare + bool_exp alias for $1; +begin + + if bool_exp + then + return ''true''; + else if not bool_exp + then + return ''false''; + else + return ''null''; + end if; + end if; + +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 + + return ut_assert__this ( + ut_assert__expected ( + eq__msg, + ut_assert__b2v(eq__check_this), + ut_assert__b2v(eq__against_this) + ), + ut_assert__b2v (eq__check_this) = ut_assert__b2v (eq__against_this), + eq__null_ok, + eq__raise_exc + ); + + +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'; + +create function ut_assert__eq ( + varchar, + timestamp, + timestamp, + 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''; + v_check varchar; + v_against varchar; +begin + + v_check := to_char (eq__check_this, c_format); + v_against := to_char (eq__against_this, c_format); + + return ut_assert__this ( + ut_assert__expected (eq__msg, v_check, v_against), + v_check = v_against, + eq__null_ok, + eq__raise_exc + ); + +end;' language 'plpgsql'; + +-- Overload for calls with default values +create function ut_assert__eq ( + varchar, -- IN VARCHAR2, + timestamp, + timestamp +) +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'; + +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; + v_query varchar; + rec_tableminus record; + v_eq boolean := ''t''; + +begin + + v_query := '' ( '' || + ieqminus__query1 || + '' except '' || + ieqminus__query2 || + '' ) '' || + '' union '' || + '' ( '' || + ieqminus__query2 || + '' 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''; + end if; + + -- One is enough + exit; + + end loop; + + return ut_assert__this ( + ut_assert__expected (ieqminus__msg || '' '' || ieqminus__minus_desc, + ieqminus__query1, + ieqminus__query2 + ), + v_eq, + ''f'', + ieqminus__raise_exc + ); + +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 + 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'', + eqtable__raise_exc + ); +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'; + + +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 + 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'', + eqtabcount__raise_exc + ); +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'; + +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__ieqminus (eqquery__msg, + eqquery__check_this, + eqquery__against_this, + ''Query Equality'', + eqquery__raise_exc + ); +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'; + +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__this ( + ''IS NOT NULL: '' || isnotnull__msg, + isnotnull__check_this IS NOT NULL, + isnotnull__null_ok, + isnotnull__raise_exc + ); +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'; + + +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 + return ut_assert__this ( + ''IS NULL: '' || isnull__msg, + isnull__check_this IS NULL, + isnull__null_ok, + isnull__raise_exc + ); +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'; + +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__this ( + ''IS NOT NULL: '' || isnotnull__msg, + isnotnull__check_this IS NOT NULL, + isnotnull__null_ok, + isnotnull__raise_exc + ); +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'; + +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__this ( + ''IS NULL: '' || isnull__msg, + isnull__check_this IS NULL, + isnull__null_ok, + isnull__raise_exc + ); +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'; + + + + + + Index: openacs-4/packages/acs-events/sql/postgresql/test/utest-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-events/sql/postgresql/test/utest-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-events/sql/postgresql/test/utest-drop.sql 13 Jul 2001 03:16:32 -0000 1.1 @@ -0,0 +1,15 @@ +-- packages/acs-events/sql/postgresql/test/utest-drop.sql +-- +-- Drop the unit test package +-- +-- @author jowell@jsabino.com +-- @creation-date 2001-06-26 +-- +-- $Id: utest-drop.sql,v 1.1 2001/07/13 03:16:32 jowells Exp $ + +-- For now, we require openacs4 installed. +select drop_package('ut_assert'); + + + + 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 --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-events/sql/postgresql/test/utest.sql 13 Jul 2001 03:16:32 -0000 1.1 @@ -0,0 +1,353 @@ +-- packages/acs-events/sql/postgresql/test/utest.sql +-- +-- Regression test of the unit test package (aha, recursion ;-). +-- +-- @author jowell@jsabino.com +-- @creation-date 2001-06-26 +-- +-- $Id: utest.sql,v 1.1 2001/07/13 03:16:32 jowells Exp $ + +create function inline_0 () +returns integer as ' +declare + v_str varchar; + v_datetest timestamp; + v_dateref timestamp; +begin + + PERFORM ut_assert__eq( + ''Test of ut_assert__eq (equality).'', + ''1'', + ''1'', + ''f'', + ''t'' + ); + + PERFORM ut_assert__eq( + ''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 + ); + + + PERFORM ut_assert__eq( + ''Test of ut_assert__eq (inequality).'', + ''1'', + ''0'' + ); + + PERFORM ut_assert__eq( + ''Test of ut_assert__b2v (true).'', + ut_assert__b2v(1+1 = 2), + ''true'', + ''f'', + ''t'' + ); + + PERFORM ut_assert__eq( + ''Test of ut_assert__b2v (true).'', + ut_assert__b2v(1+1 = 2), + ''true'' + ); + + PERFORM ut_assert__eq( + ''Test of ut_assert__b2v (false).'', + ut_assert__b2v(1+1 = 1), + ''false'', + ''f'', + ''t'' + ); + + PERFORM ut_assert__eq( + ''Test of ut_assert__b2v (false).'', + ut_assert__b2v(1+1 = 1), + ''false'' + ); + + PERFORM ut_assert__eq( + ''Test of ut_assert__eq (boolean,boolean).'', + 1+1 = 2, + ''true'', + ''f'', + ''t'' + ); + + + PERFORM ut_assert__eq( + ''Test of ut_assert__eq (boolean,boolean).'', + 1+1 = 2, + ''true'' + ); + + PERFORM ut_assert__eq( + ''Test of ut_assert__eq (boolean,boolean).'', + 1+1 = 1, + ''false'', + ''f'', + ''t'' + ); + + PERFORM ut_assert__eq( + ''Test of ut_assert__eq (boolean,boolean).'', + 1+1 = 1, + ''false'' + ); + + + select now() into v_dateref; + v_datetest := v_dateref; + + PERFORM ut_assert__eq( + ''Test of ut_assert__eq (timestamp,timestamp).'', + v_datetest, + v_dateref, + ''f'', + ''f'' + ); + + PERFORM ut_assert__eq( + ''Test of ut_assert__eq (timestamp,timestamp).'', + v_datetest, + v_dateref + ); + + v_datetest := now() + 1; + + PERFORM ut_assert__eq( + ''Test of ut_assert__eq (timestamp,timestamp).'', + v_datetest, + v_dateref, + ''f'', + ''f'' -- do not raise exception + ); + + PERFORM ut_assert__eq( + ''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'' + ); + + 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 + ); + + create table ut_temp ( + an_integer integer, + a_varchar varchar); + + 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'' + ); + + 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'' + ); + + create table ut_another as select * from ut_temp; + + PERFORM ut_assert__eqtable( + ''Test of simple table equality.'', + ''ut_another'', + ''ut_temp'', + null, + null, + ''t'' + ); + + PERFORM ut_assert__eqtable( + ''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'' + ); + + PERFORM ut_assert__eqtable( + ''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'', + null, + null, + ''t'' + ); + + PERFORM ut_assert__eqtabcount( + ''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'', + null, + ''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'' + ); + + PERFORM ut_assert__eqquery( + ''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'' + ); + + PERFORM ut_assert__eqquery( + ''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'', + null, + null, + ''f'' + ); + + PERFORM ut_assert__eqtable( + ''Test of simple table inequality.'', + ''ut_another'', + ''ut_temp'' + ); + + PERFORM ut_assert__isnotnull( + ''Degenerate test of non-null'', + ''1'', + ''f'', + ''t'' + ); + + PERFORM ut_assert__isnotnull( + ''Degenerate test of non-null'', + ''1'' + ); + + PERFORM ut_assert__isnull( + ''Degenerate test of null'', + null, + ''f'', + ''t'' + ); + PERFORM ut_assert__isnull( + ''Degenerate test of null'', + null + ); + + + -- We already deleted this, so v_str should be null + select into v_str a_varchar from ut_another where an_integer = 2; + + PERFORM ut_assert__isnull( + ''Degenerate test of null'', + v_str, + ''f'', + ''t'' + ); + + PERFORM ut_assert__isnull( + ''Degenerate test of null'', + v_str + ); + + + -- Still in table, so should be non-null. + select into v_str a_varchar from ut_another where an_integer = 1; + + PERFORM ut_assert__isnotnull( + ''Degenerate test of null'', + v_str, + ''f'', + ''t'' + ); + + PERFORM ut_assert__isnotnull( + ''Degenerate test of null'', + v_str + ); + + drop table ut_temp; + drop table ut_another; + + return 0; + +end;' language 'plpgsql'; + + +select (case when inline_0 () = 0 + then + 'Regression test is a success.' + end) as test_result; +drop function inline_0 (); + + + +