-- 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.3 2013/03/30 13:00:30 gustafn 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). 



-- added
select define_function_args('ut_assert__expected','msg,check_this,against_this');

--
-- procedure ut_assert__expected/3
--
CREATE OR REPLACE FUNCTION ut_assert__expected(
   expected__msg varchar,
   expected__check_this varchar,
   expected__against_this varchar
) RETURNS varchar AS $$
DECLARE
BEGIN

      return expected__msg || 
	     ': expected ' ||
	     '''' ||
	     expected__against_this ||
	     '''' ||
	     ', got ' || 
	     '''' ||
	     expected__check_this ||
	     '''';

END;
$$ LANGUAGE plpgsql;



-- added
select define_function_args('ut_assert__this','msg,check_this,null_ok;FALSE,raise_exc;FALSE');

--
-- procedure ut_assert__this/4
--
CREATE OR REPLACE FUNCTION ut_assert__this(
   this__msg varchar,
   this__check_this boolean,
   this__null_ok boolean,  -- default FALSE
   this__raise_exc boolean -- default FALSE

) RETURNS integer AS $$
DECLARE
BEGIN

      -- We always output the message (usually the result of the test)
      raise notice '%',this__msg;

      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


--
-- procedure ut_assert__this/2
--
CREATE OR REPLACE FUNCTION ut_assert__this(
   this__msg varchar,
   this__check_this boolean
) RETURNS integer AS $$
DECLARE
BEGIN

      return ut_assert__this(this_msg,this_check_this,'f','f');
     
END;
$$ LANGUAGE plpgsql;



-- added

--
-- procedure ut_assert__eq/5
--
CREATE OR REPLACE FUNCTION ut_assert__eq(
   eq__msg varchar,
   eq__check_this varchar,
   eq__against_this varchar,
   eq__null_ok boolean,  -- default FALSE,
   eq__raise_exc boolean -- defaultFALSE

) RETURNS integer AS $$
DECLARE
BEGIN
	return ut_assert__this (
		 ut_assert__expected (eq__msg, eq__check_this, eq__against_this),
		 eq__check_this = eq__against_this,
		 eq__null_ok,
		 eq__raise_exc
		 );
	
END;
$$ LANGUAGE plpgsql;

-- Overload for calls with default values


--
-- procedure ut_assert__eq/3
--
CREATE OR REPLACE FUNCTION ut_assert__eq(
   eq__msg varchar,
   eq__check_this varchar,
   eq__against_this varchar
) RETURNS integer AS $$
DECLARE
BEGIN

      return ut_assert__eq(eq__msg,eq__check_this,eq__against_this,'f','f');

END;
$$ LANGUAGE plpgsql;




-- added
select define_function_args('ut_assert__b2v','bool_exp');

--
-- procedure ut_assert__b2v/1
--
CREATE OR REPLACE FUNCTION ut_assert__b2v(
   bool_exp boolean
) RETURNS varchar AS $$
DECLARE
BEGIN

      if bool_exp
      then
         return 'true';
      else if not bool_exp
           then
              return 'false';
           else
              return 'null';
           end if;
      end if;

END;
$$ LANGUAGE plpgsql;



--
-- procedure ut_assert__eq/5
--
CREATE OR REPLACE FUNCTION ut_assert__eq(
   eq__msg varchar,
   eq__check_this boolean,
   eq__against_this boolean,
   eq__null_ok boolean,  -- default false
   eq__raise_exc boolean -- defualt false

) RETURNS integer AS $$
DECLARE
BEGIN
     
     return  ut_assert__this (
		       ut_assert__expected (
				 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


--
-- procedure ut_assert__eq/3
--
CREATE OR REPLACE FUNCTION ut_assert__eq(
   eq__msg varchar,
   eq__check_this boolean,
   eq__against_this boolean
) RETURNS integer AS $$
DECLARE
BEGIN

      return ut_assert__eq(eq__msg,eq__check_this,eq__against_this,'f','f');

END;
$$ LANGUAGE plpgsql;



-- added
select define_function_args('ut_assert__eq','msg,check_this,against_this,null_ok;false,raise_exc;false');

--
-- procedure ut_assert__eq/5
--
CREATE OR REPLACE FUNCTION ut_assert__eq(
   eq__msg varchar,
   eq__check_this timestamptz,
   eq__against_this timestamptz,
   eq__null_ok boolean,  -- default false
   eq__raise_exc boolean -- default false

) RETURNS integer AS $$
DECLARE
      c_format		constant varchar := 'MONTH DD, YYYY HH24MISS';
      v_check		varchar;
      v_against		varchar;
BEGIN

      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


--
-- procedure ut_assert__eq/3
--
CREATE OR REPLACE FUNCTION ut_assert__eq(
   eq__msg varchar,
   eq__check_this timestamptz,
   eq__against_this timestamptz
) RETURNS integer AS $$
DECLARE
BEGIN

      return ut_assert__eq(eq__msg,eq__check_this,eq__against_this,'f','f');

END;
$$ LANGUAGE plpgsql;



-- added
select define_function_args('ut_assert__ieqminus','msg,query1,query2,minus_desc,raise_exc');

--
-- procedure ut_assert__ieqminus/5
--
CREATE OR REPLACE FUNCTION ut_assert__ieqminus(
   ieqminus__msg varchar,
   ieqminus__query1 varchar,
   ieqminus__query2 varchar,
   ieqminus__minus_desc varchar,
   ieqminus__raise_exc boolean
) RETURNS varchar AS $$
DECLARE
      v_query		      varchar;
      rec_tableminus	      record;
      v_eq		      boolean := 't';

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;



-- added
select define_function_args('ut_assert__eqtable','msg,check_this,against_this,check_where;null,against_where;null,raise_exc;false');

--
-- procedure ut_assert__eqtable/6
--
CREATE OR REPLACE FUNCTION ut_assert__eqtable(
   eqtable__msg varchar,
   eqtable__check_this varchar,
   eqtable__against_this varchar,
   eqtable__check_where varchar,   -- default null
   eqtable__against_where varchar, -- default null
   eqtable__raise_exc boolean      -- default false

) RETURNS integer AS $$
DECLARE
BEGIN
      return ut_assert__ieqminus (eqtable__msg,
			  'SELECT * FROM ' || eqtable__check_this || '  WHERE ' ||
			  coalesce (eqtable__check_where, '1=1'),
			  'SELECT * FROM ' || eqtable__against_this || '  WHERE ' ||
			  coalesce (eqtable__against_where, '1=1'),
			  'Table Equality',
			  eqtable__raise_exc
			  );
END;
$$ LANGUAGE plpgsql;

-- Overload for calls with default values


--
-- procedure ut_assert__eqtable/3
--
CREATE OR REPLACE FUNCTION ut_assert__eqtable(
   eqtable__msg varchar,
   eqtable__check_this varchar,
   eqtable__against_this varchar
) RETURNS integer AS $$
DECLARE
BEGIN

      return ut_assert__eqtable(eqtable__msg,eqtable__check_this,eqtable__against_this,null,null,'f');

END;
$$ LANGUAGE plpgsql;




-- added
select define_function_args('ut_assert__eqtabcount','msg,check_this,against_this,check_where;null,against_where;null,raise_exc;false');

--
-- procedure ut_assert__eqtabcount/6
--
CREATE OR REPLACE FUNCTION ut_assert__eqtabcount(
   eqtabcount__msg varchar,
   eqtabcount__check_this varchar,
   eqtabcount__against_this varchar,
   eqtabcount__check_where varchar,   -- default null
   eqtabcount__against_where varchar, -- default null
   eqtabcount__raise_exc boolean      -- default false

) RETURNS integer AS $$
DECLARE
BEGIN
      return ut_assert__ieqminus (eqtabcount__msg,
			  'SELECT COUNT(*) FROM ' || eqtabcount__check_this || '  WHERE ' ||
			  coalesce (eqtabcount__check_where, '1=1'),
			  'SELECT COUNT(*) FROM ' || eqtabcount__against_this || '  WHERE ' ||
			  coalesce (eqtabcount__against_where, '1=1'),
			  'Table Count Equality',
			  eqtabcount__raise_exc
			  );
END;
$$ LANGUAGE plpgsql;

-- Overload for calls with default values


--
-- procedure ut_assert__eqtabcount/3
--
CREATE OR REPLACE FUNCTION ut_assert__eqtabcount(
   eqtabcount__msg varchar,
   eqtabcount__check_this varchar,
   eqtabcount__against_this varchar
) RETURNS integer AS $$
DECLARE
BEGIN

      return ut_assert__eqtabcount(eqtabcount__msg,eqtabcount__check_this,eqtabcount__against_this,null,null,'f');

END;
$$ LANGUAGE plpgsql;



-- added
select define_function_args('ut_assert__eqquery','msg,check_this,against_this,raise_exc;null');

--
-- procedure ut_assert__eqquery/4
--
CREATE OR REPLACE FUNCTION ut_assert__eqquery(
   eqquery__msg varchar,
   eqquery__check_this varchar,
   eqquery__against_this varchar,
   eqquery__raise_exc boolean -- default null

) RETURNS integer AS $$
DECLARE
BEGIN
      return ut_assert__ieqminus (eqquery__msg,
			          eqquery__check_this,
				  eqquery__against_this,
				  'Query Equality',
				  eqquery__raise_exc
				  );
END;
$$ LANGUAGE plpgsql;

-- Overload for calls with default values


--
-- procedure ut_assert__eqquery/3
--
CREATE OR REPLACE FUNCTION ut_assert__eqquery(
   eqquery__msg varchar,
   eqquery__check_this varchar,
   eqquery__against_this varchar
) RETURNS integer AS $$
DECLARE
BEGIN

      return ut_assert__eqquery(eqquery__msg,eqquery__check_this,eqquery__against_this,'f');

END;
$$ LANGUAGE plpgsql;



-- added
select define_function_args('ut_assert__isnotnull','msg,check_this,null_ok;false,raise_exc;false');

--
-- procedure ut_assert__isnotnull/4
--
CREATE OR REPLACE FUNCTION ut_assert__isnotnull(
   isnotnull__msg varchar,
   isnotnull__check_this varchar,
   isnotnull__null_ok boolean,  -- default false
   isnotnull__raise_exc boolean -- default false

) RETURNS integer AS $$
DECLARE
BEGIN
      return ut_assert__this (
	        'IS NOT NULL: ' || isnotnull__msg,
		isnotnull__check_this IS NOT NULL,
		isnotnull__null_ok,
		isnotnull__raise_exc
		);
END;
$$ LANGUAGE plpgsql;

-- Overload for calls with default values


--
-- procedure ut_assert__isnotnull/2
--
CREATE OR REPLACE FUNCTION ut_assert__isnotnull(
   isnotnull__msg varchar,
   isnotnull__check_this varchar
) RETURNS integer AS $$
DECLARE
BEGIN

      return ut_assert__isnotnull(isnotnull__msg,isnotnull__check_this,'f','f');

END;
$$ LANGUAGE plpgsql;




-- added
select define_function_args('ut_assert__isnull','msg,check_this,null_ok;false,raise_exc;false');

--
-- procedure ut_assert__isnull/4
--
CREATE OR REPLACE FUNCTION ut_assert__isnull(
   isnull__msg varchar,
   isnull__check_this varchar,
   isnull__null_ok boolean,  -- default false
   isnull__raise_exc boolean -- default false

) RETURNS integer AS $$
DECLARE
BEGIN
      return ut_assert__this (
	        'IS NULL: ' || isnull__msg,
		isnull__check_this IS NULL,
		isnull__null_ok,
		isnull__raise_exc
		);
END;
$$ LANGUAGE plpgsql;


-- Overload for calls with default values


--
-- procedure ut_assert__isnull/2
--
CREATE OR REPLACE FUNCTION ut_assert__isnull(
   isnull__msg varchar,
   isnull__check_this varchar
) RETURNS integer AS $$
DECLARE
BEGIN

      return ut_assert__isnull(isnull__msg,isnull__check_this,'f','f');

END;
$$ LANGUAGE plpgsql;



--
-- procedure ut_assert__isnotnull/4
--
CREATE OR REPLACE FUNCTION ut_assert__isnotnull(
   isnotnull__msg varchar,
   isnotnull__check_this boolean,
   isnotnull__null_ok boolean,  -- default false
   isnotnull__raise_exc boolean -- default false

) RETURNS integer AS $$
DECLARE
BEGIN
      return ut_assert__this (
	        'IS NOT NULL: ' || isnotnull__msg,
		isnotnull__check_this IS NOT NULL,
		isnotnull__null_ok,
		isnotnull__raise_exc
		);
END;
$$ LANGUAGE plpgsql;

-- Overload for calls with default values


--
-- procedure ut_assert__isnotnull/2
--
CREATE OR REPLACE FUNCTION ut_assert__isnotnull(
   isnotnull__msg varchar,
   isnotnull__check_this boolean
) RETURNS integer AS $$
DECLARE
BEGIN

      return ut_assert__isnotnull(isnotnull__msg,isnotnull__check_this,'f','f');

END;
$$ LANGUAGE plpgsql;



--
-- procedure ut_assert__isnull/4
--
CREATE OR REPLACE FUNCTION ut_assert__isnull(
   isnull__msg varchar,
   isnull__check_this boolean,
   isnull__null_ok boolean,  -- default false
   isnull__raise_exc boolean -- default false

) RETURNS integer AS $$
DECLARE
BEGIN
      return ut_assert__this (
	        'IS NULL: ' || isnull__msg,
		isnull__check_this IS NULL,
		isnull__null_ok,
		isnull__raise_exc
		);
END;
$$ LANGUAGE plpgsql;

-- Overload for calls with default values


--
-- procedure ut_assert__isnull/2
--
CREATE OR REPLACE FUNCTION ut_assert__isnull(
   isnull__msg varchar,
   isnull__check_this boolean
) RETURNS integer AS $$
DECLARE
BEGIN

      return ut_assert__isnull(isnull__msg,isnull__check_this,'f','f');

END;
$$ LANGUAGE plpgsql;






