-- 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.7 2018/10/20 11:55:29 hectorr Exp $
-- Note: These tests use the semi-ported utPLSQL regression package
\i utest-create.sql
-- Set-up the regression test
CREATE OR REPLACE 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 regression 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 OR REPLACE 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
-- added
select define_function_args('ut__new','interval_id');
--
-- procedure ut__new/1
--
CREATE OR REPLACE FUNCTION ut__new(
new__interval_id integer
) RETURNS integer AS $$
DECLARE
v_interval_id time_intervals.interval_id%TYPE;
v_timespan_id timespans.timespan_id%TYPE;
BEGIN
-- 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
--
-- procedure ut__new/2
--
CREATE OR REPLACE FUNCTION ut__new(
new__date1 timestamptz,
new__date2 timestamptz
) RETURNS integer AS $$
DECLARE
v_interval_id time_intervals.interval_id%TYPE;
BEGIN
-- 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
-- added
select define_function_args('ut__delete','timespan_id');
--
-- procedure ut__delete/1
--
CREATE OR REPLACE FUNCTION ut__delete(
delete__timespan_id integer
) RETURNS integer AS $$
DECLARE
BEGIN
-- Delete the row from actual table
PERFORM timespan__delete(delete__timespan_id);
PERFORM ut_assert__eqtable ('Testing timespan__delete: ',
'ut_timespans',
'timespans'
);
-- 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 transaction 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;
-- added
select define_function_args('ut__join_interval','timespan_id,interval_id,copy_p');
--
-- procedure ut__join_interval/3
--
CREATE OR REPLACE FUNCTION ut__join_interval(
join_interval__timespan_id integer,
join_interval__interval_id integer,
join_interval__copy_p boolean
) RETURNS integer AS $$
DECLARE
v_interval_id time_intervals.interval_id%TYPE;
v_interval_id_ck time_intervals.interval_id%TYPE;
v_interval_id_cp time_intervals.interval_id%TYPE;
BEGIN
-- Get interval id of original 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);
-- Don't 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;
-- added
--
-- procedure ut__join/2
--
CREATE OR REPLACE FUNCTION ut__join(
join__timespan_id_1 integer,
join__timespan_id_2 integer
) RETURNS integer AS $$
DECLARE
rec_timespan record;
BEGIN
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;
-- added
select define_function_args('ut__join','timespan_id,start_date,end_date');
--
-- procedure ut__join/3
--
CREATE OR REPLACE FUNCTION ut__join(
join__timespan_id integer,
join__start_date timestamptz,
join__end_date timestamptz
) RETURNS integer AS $$
DECLARE
v_interval_id time_intervals.interval_id%TYPE;
BEGIN
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;
-- added
select define_function_args('ut__copy','timespan_id,offset');
--
-- procedure ut__copy/2
--
CREATE OR REPLACE FUNCTION ut__copy(
copy__timespan_id integer,
copy__offset interval
) RETURNS integer AS $$
DECLARE
v_timespan_id timespans.timespan_id%TYPE;
v_interval_id time_intervals.interval_id%TYPE;
v_interval_id_ck time_intervals.interval_id%TYPE;
rec_timespan record;
BEGIN
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;
-- added
select define_function_args('ut__exists_p','timespan_id,result');
--
-- procedure ut__exists_p/2
--
CREATE OR REPLACE FUNCTION ut__exists_p(
exists_p__timespan_id integer,
exists_p__result boolean
) RETURNS integer AS $$
DECLARE
BEGIN
PERFORM ut_assert__eq ('Testing timespan__exists_p: ',
timespan__exists_p(exists_p__timespan_id),
exists_p__result
);
return 0;
END;
$$ LANGUAGE plpgsql;
-- added
select define_function_args('ut__multi_interval_p','timespan_id,result');
--
-- procedure ut__multi_interval_p/2
--
CREATE OR REPLACE FUNCTION ut__multi_interval_p(
multi_interval_p__timespan_id integer,
multi_interval_p__result boolean
) RETURNS integer AS $$
DECLARE
BEGIN
return ut_assert__eq ('Testing timespan__multi_interval_p: ',
timespan__multi_interval_p(multi_interval_p__timespan_id),
multi_interval_p__result
);
END;
$$ LANGUAGE plpgsql;
-- added
select define_function_args('ut__overlaps_interval_p','timespan_id,interval_id,result');
--
-- procedure ut__overlaps_interval_p/3
--
CREATE OR REPLACE FUNCTION ut__overlaps_interval_p(
overlaps_interval_p__timespan_id integer,
overlaps_interval_p__interval_id integer,
overlaps_interval_p__result boolean
) RETURNS integer AS $$
DECLARE
BEGIN
return ut_assert__eq ('Testing timespan__overlaps_interval_p: ',
timespan__overlaps_interval_p(overlaps_interval_p__timespan_id,
overlaps_interval_p__interval_id),
overlaps_interval_p__result
);
END;
$$ LANGUAGE plpgsql;
-- added
--
-- procedure ut__overlaps_p/3
--
CREATE OR REPLACE FUNCTION ut__overlaps_p(
overlaps_p__timespan_1_id integer,
overlaps_p__timespan_2_id integer,
overlaps_p__result boolean
) RETURNS integer AS $$
DECLARE
BEGIN
return ut_assert__eq ('Testing timespan__overlaps_p, timespan vs. timespan: ',
timespan__overlaps_p(overlaps_p__timespan_1_id,
overlaps_p__timespan_2_id),
overlaps_p__result
);
END;
$$ LANGUAGE plpgsql;
-- added
select define_function_args('ut__overlaps_p','timespan_id,start_date,end_date,result');
--
-- procedure ut__overlaps_p/4
--
CREATE OR REPLACE FUNCTION ut__overlaps_p(
overlaps_p__timespan_id integer,
overlaps_p__start_date timestamptz,
overlaps_p__end_date timestamptz,
overlaps_p__result boolean
) RETURNS integer AS $$
DECLARE
BEGIN
return ut_assert__eq ('Test of timespan__overlaps_p, timespan vs. start and end dates: ',
timespan__overlaps_p(overlaps_p__timespan_id,
overlaps_p__start_date,
overlaps_p__end_date),
overlaps_p__result
);
END;
$$ LANGUAGE plpgsql;
-- added
select define_function_args('ut__interval_delete','timespan_id,interval_id');
--
-- procedure ut__interval_delete/2
--
CREATE OR REPLACE FUNCTION ut__interval_delete(
interval_delete__timespan_id integer,
interval_delete__interval_id integer
) RETURNS integer AS $$
DECLARE
BEGIN
PERFORM timespan__interval_delete(interval_delete__timespan_id,interval_delete__interval_id);
-- Remove from shadow table
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;
--
-- procedure ut__regression1/0
--
CREATE OR REPLACE FUNCTION ut__regression1(
) RETURNS integer AS $$
DECLARE
v_result integer := 0;
v_interval_id time_intervals.interval_id%TYPE;
v_interval_id_ck time_intervals.interval_id%TYPE;
v_timespan_id timespans.timespan_id%TYPE;
v_timespan_id_ck timespans.timespan_id%TYPE;
BEGIN
raise notice 'Regression test, part 1 (creates and edits).';
-- First create an interval
v_interval_id := time_interval__new(timestamptz '2001-01-01',timestamptz '2001-01-20');
--Check if creation of timespans work by supplying an interval id to be copied
PERFORM ut__new(v_interval_id);
-- We first check if the creation of timespans work
-- This should be equivalent to what we have above
v_timespan_id := ut__new(timestamptz '2001-01-25',timestamptz '2001-02-02');
-- 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(timestamptz '2001-03-05',timestamptz '2001-03-31');
v_timespan_id_ck := ut__new(timestamptz '2001-06-05',timestamptz '2001-06-30');
-- These timespans should not overlap
PERFORM ut__overlaps_p(v_timespan_id,v_timespan_id_ck,false);
-- Check overlaps against these known dates
PERFORM ut__overlaps_p(v_timespan_id,timestamptz '2001-02-06',timestamptz '2001-03-25',true);
PERFORM ut__overlaps_p(v_timespan_id,timestamptz '2001-03-07',timestamptz '2001-04-01',true);
PERFORM ut__overlaps_p(v_timespan_id,timestamptz '2001-01-01',timestamptz '2001-03-20',true);
PERFORM ut__overlaps_p(v_timespan_id,timestamptz '2001-01-01',null,true);
PERFORM ut__overlaps_p(v_timespan_id,null,timestamptz '2001-04-01',true);
PERFORM ut__overlaps_p(v_timespan_id,timestamptz '2001-04-01',timestamptz '2001-04-30',false);
PERFORM ut__overlaps_p(v_timespan_id,timestamptz '2001-02-01',timestamptz '2001-02-27',false);
-- 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,timestamptz '2001-12-01',timestamptz '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;
--
-- procedure ut__regression2/0
--
CREATE OR REPLACE 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
Begin main content
