-- 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.6 2018/03/25 20:56:30 hectorr Exp $
-- Note: These tests use the semi-ported utPLSQL regression package
\i utest-create.sql
CREATE OR REPLACE FUNCTION ut__setup() RETURNS integer AS $$
BEGIN
raise notice 'Setting up acs-events-test...';
-- create copies of the tables
-- No need for execute 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 OR REPLACE 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
-- added
select define_function_args('recur_every3','date,interval');
--
-- procedure recur_every3/2
--
CREATE OR REPLACE FUNCTION recur_every3(
recur_every3__date timestamptz,
recur_every3__interval integer
) RETURNS timestamptz AS $$
DECLARE
BEGIN
return recur_every3__date + to_interval(3*recur_every3__interval,'days');
END;
$$ LANGUAGE plpgsql;
-- The test of insert_instances has been augmented to test
-- other routines. Specifically new, delete, delete_all,
-- timespan_set, activity_set, get_name, get_description,
-- party_map, party_unmap, recurs_p, instances_exist_p
--
-- procedure ut__insert_instances/0
--
CREATE OR REPLACE FUNCTION ut__insert_instances(
) RETURNS integer AS $$
DECLARE
date1 timestamptz := '2000-03-23 13:00';
date2 timestamptz := '2000-03-23 14:00';
insert_instances__timespan_id acs_events.timespan_id%TYPE;
insert_instances__activity_id acs_events.activity_id%TYPE;
insert_instances__recurrence_id acs_events.recurrence_id%TYPE;
insert_instances__event_id acs_events.event_id%TYPE;
v_instance_count integer;
rec_events record;
v_dummy_id integer;
BEGIN
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',
null,
'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,
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,
timestamptz '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(timestamptz '2001-10-21 09:00:00',
timestamptz '2001-10-23 10:00:00');
-- Check month by date (recur for the same date of the month specified in time interval)
insert_instances__recurrence_id := recurrence__new('week',
1,
'4 6',
to_date('2001-12-01','YYYY-MM-DD'),
null);
insert_instances__event_id := acs_event__new(null,'Weekly',null, null, null,
insert_instances__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,
timestamptz '2001-12-25'
);
-- There should be 13 instances of the weekly event
PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES',
'select count(*) from acs_events
where recurrence_id = ' || insert_instances__recurrence_id,
'select 13 from dual'
);
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(timestamptz '2001-03-21 09:00:00',
timestamptz '2001-03-23 10:00:00');
-- Check month by date (recur for the same date of the month specified in time interval)
insert_instances__recurrence_id := recurrence__new('month_by_date',
1,
null, -- irrelevant
to_date('2001-05-01','YYYY-MM-DD'),
null);
insert_instances__event_id := acs_event__new(null,'month_by_date',null, null, null,
insert_instances__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,
timestamptz '2001-04-25 00:00:00'
);
-- There should be two instances (including the original), even if the cut-off date is between
-- the last event.
PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES',
'select count(*) from acs_events
where recurrence_id = ' || insert_instances__recurrence_id,
'select 2 from dual'
);
-- 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(timestamptz '2001-10-21 09:00:00',
timestamptz '2001-10-23 10:00:00');
-- Check month by date (recur for the same date of the month specified in time interval)
insert_instances__recurrence_id := recurrence__new('month_by_date',
1,
null, -- irrelevant
to_date('2002-02-01','YYYY-MM-DD'),
null);
insert_instances__event_id := acs_event__new(null,'month_by_date',null, null, null,
insert_instances__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,
timestamptz '2002-04-25 00:00:00'
);
-- There should be four instances (including the original), even if the cut-off date is between
-- the last event.
PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES',
'select count(*) from acs_events
where recurrence_id = ' || insert_instances__recurrence_id,
'select 4 from dual'
);
-- 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(timestamptz '2001-03-26 09:00:00',
timestamptz '2001-03-26 10:00:00');
-- Check month by date (recur every day, skip every second interval)
insert_instances__recurrence_id := recurrence__new('day',
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, null, 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,
timestamptz '2001-04-05 00:00:00'
);
-- There should be six instances (including the original)
-- JS: Note that 4/01/2001 is the DST switch back date, which is one of the dates in the recurrence.
-- JS: The time format that Postres reports is still the DST format, but if we convert to non-DST
-- JS: then the time is ok. In particular, Postgres reports 10:00am GMT-4, which converts to
-- JS: the expected 9:00 GMT-5 in the non-DST format that should apply on 4/01/2001.
PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES',
'select count(*) from acs_events
where recurrence_id = ' || insert_instances__recurrence_id,
'select 6 from dual'
);
-- 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(timestamptz '2001-10-26 09:00:00',
timestamptz '2001-10-26 10:00:00');
-- Check month by date (recur every day, skip every second interval)
insert_instances__recurrence_id := recurrence__new('day',
2,
null, -- Irrelevant
to_date('2001-11-13','YYYY-MM-DD'),
null);
insert_instances__event_id := acs_event__new(null,'every 2 days',null, null, null,
insert_instances__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,
timestamptz '2001-11-05 00:00:00'
);
-- There should be five instances (including the original)
-- JS: roblem here. The recurrence includes 10/28/2001, which is the switchover to
-- JS: DST in the US.
PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES',
'select count(*) from acs_events
where recurrence_id = ' || insert_instances__recurrence_id,
'select 6 from dual'
);
-- 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(timestamptz '2001-04-01 09:00:00',
timestamptz '2001-04-01 10:00:00');
-- Check month by date (recur every day, skip every second interval)
insert_instances__recurrence_id := recurrence__new('year',
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, null, 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,
timestamptz '2002-04-05 00:00:00'
);
-- There should be two instance (including the original).
PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES',
'select count(*) from acs_events
where recurrence_id = ' || insert_instances__recurrence_id,
'select 2 from dual'
);
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(timestamptz '2001-04-03 09:00:00',
timestamptz '2001-04-03 10:00:00');
-- Check month by date (recur every day, skip every second interval)
insert_instances__recurrence_id := recurrence__new('year',
1,
null, -- Irrelevant
to_date('2002-04-10','YYYY-MM-DD'),
null);
insert_instances__event_id := acs_event__new(null,'yearly (non-DST)',null, null, null,
insert_instances__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,
timestamptz '2002-04-05 00:00:00'
);
-- There should be two instance (including the original).
PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES',
'select count(*) from acs_events
where recurrence_id = ' || insert_instances__recurrence_id,
'select 2 from dual'
);
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(timestamptz '2001-10-28 09:00:00',
timestamptz '2001-10-28 10:00:00');
-- Check month by date (recur every day, skip every second interval)
insert_instances__recurrence_id := recurrence__new('year',
1,
null, -- Irrelevant
to_date('2002-10-30','YYYY-MM-DD'),
null);
insert_instances__event_id := acs_event__new(null,'yearly (DST)',null, null, null,
insert_instances__timespan_id,
insert_instances__activity_id,
insert_instances__recurrence_id,
'acs_event',now(),null,null,null
);
-- Cut-off date should have no effect
PERFORM acs_event__insert_instances (insert_instances__event_id,
timestamptz '2002-10-30 00:00:00'
);
-- There should be two instance (including the original).
PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES',
'select count(*) from acs_events
where recurrence_id = ' || insert_instances__recurrence_id,
'select 2 from dual'
);
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(timestamptz '2001-02-06 09:00:00',
timestamptz '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, null, 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,
timestamptz '2001-12-10 00:00:00'
);
-- There should be three instances (including the original).
PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES',
'select count(*) from acs_events
where recurrence_id = ' || insert_instances__recurrence_id,
'select 10 from dual'
);
-- 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(timestamptz '2001-08-06 09:00:00',
timestamptz '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, null, 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,
timestamptz '2002-05-20 00:00:00'
);
-- There should be three instances (including the original).
PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES',
'select count(*) from acs_events
where recurrence_id = ' || insert_instances__recurrence_id,
'select 9 from dual'
);
-- 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(timestamptz '2001-08-06 09:00:00',
timestamptz '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, null, 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,
timestamptz '2001-08-30 00:00:00'
);
-- There should be three instances (including the original).
PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES',
'select count(*) from acs_events
where recurrence_id = ' || insert_instances__recurrence_id,
'select 5 from dual'
);
-- 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(timestamptz '2001-02-06 09:00:00',
timestamptz '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, null, 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,
timestamptz '2001-12-20 00:00:00'
);
-- There should be three instances (including the original).
PERFORM ut_assert__eqquery ('Test count of instances in INSERT_INSTANCES',
'select count(*) from acs_events
where recurrence_id = ' || insert_instances__recurrence_id,
'select 11 from dual'
);
-- 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(timestamptz '2001-02-06 09:00:00',
timestamptz '2001-02-07 10:00:00');
-- Insert one recurrence so that recurrence__delete will have something to delete (since recurrences
-- are deleted if associated with an event).
insert_instances__recurrence_id := recurrence__new('month_by_day',
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,null,null,'acs_event',now(),null,null,null);
insert_instances__event_id := acs_event__new(null,'Another event','Yet another event description', null, null,
insert_instances__timespan_id,null,null,'acs_event',now(),null,null,null);
-- If so, we should now be able to get the activity name
PERFORM ut_assert__eq ('Test of GET_NAME from activity within INSERT_INSTANCES',
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;
--
-- procedure ut__delete_instances/0
--
CREATE OR REPLACE FUNCTION ut__delete_instances(
) RETURNS integer AS $$
DECLARE
rec_timespans record;
rec_recurrences record;
rec_activities record;
rec_events record;
v_dummy integer;
BEGIN
-- 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
OpenACS – The Toolkit for Online Communities : ACS API Browser : ACS Events 0.5 : acs-events-test.sql
Begin main content
