--
-- acs-kernel/sql/acs-objects-test.sql
--
-- PL/SQL regression tests for the acs-objects system
--
-- Note: These tests use the utPLSQL regression package available at:
-- ftp://ftp.oreilly.com/published/oreilly/oracle/utplsql/utplsql.zip
--
-- @author Richard Li (richardl@arsdigita.com)
--
-- @creation-date 19 September 2000
--
-- @cvs-id $Id: acs-objects-test.sql,v 1.3 2011/07/07 10:46:02 gustafn Exp $

-- In order for utPLSQL to work, you need to grant 
-- specific permissions to your user:
--
-- grant create public synonym to servicename;
-- grant drop public synonym to servicename;
-- grant execute on dbms_pipe to servicename;
-- grant drop any table to servicename;
-- grant create any table to servicename;

-- In order to execute the test, you need to set things up
-- in your SQL*PLUS session. First type:
-- 
--     set serveroutput on size 1000000 format wrapped
--
-- Now, if you have the UTL_FILE PL/SQL package installed, type:
--
--     exec utplsql.setdir('/web/richard/packages/acs-kernel/sql');
--
-- Otherwise, you'll have to disable autocompilation and manually
-- compile:
--
--     exec utplsql.autocompile (false);
--     @acs-objects-test
--
-- To actually execute the test, type:
--
--     exec utplsql.test('acs_object');


-- we need these here or else the PL/SQL won't compile.
-- drop table ut_acs_objects;
-- create table ut_acs_objects as select * from acs_objects;
-- create table test_objects (test_id integer primary key, data varchar2(100));

-- create or replace package ut#acs_object
-- as

--     procedure setup;

--     procedure teardown;

--     procedure new;

--     procedure delete;

--     procedure name;

--     procedure default_name;

--     procedure set_attribute;

--     procedure get_attribute;

-- end ut#acs_object;
-- /
-- show errors

-- create or replace package body ut#acs_object
-- as



--
-- procedure ut_acs_object__setup/0
--
CREATE OR REPLACE FUNCTION ut_acs_object__setup(

) RETURNS integer AS $$
DECLARE
        attr_id acs_attributes.attribute_id%TYPE;
BEGIN
        raise NOTICE 'Setting up...';

        -- create the test_object type
        PERFORM acs_object_type__create_type (
     	    'test_object',
	    'Test Object',
	    'Test Objects',
	    'acs_object',
            'test_objects',
	    'test_id',
            null,
            'f',
            null,
            null            
	);

	-- no API available for this yet
	insert into acs_object_type_tables 
               (object_type, table_name, id_column)
               values 
               ('test_object','test_objects','test_id');

	-- create the attribute
	attr_id := acs_attribute__create_attribute (
	    'test_object',
	    'data',
	    'string',
	    'Data',
	    'Mo Data',
            'test_objects',
	    'data',
            null,
            0,
            1,
            null,
            'type_specific',
            'f'
	);

        return null;

END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION ut_acs_object__teardown() RETURNS integer AS $$
BEGIN
        raise NOTICE 'Tearing down...';

	-- delete the test object
	delete from acs_attributes where object_type = 'test_object';
	delete from acs_object_type_tables where object_type = 'test_object';
	delete from acs_objects where object_type = 'test_object';

    	drop table test_objects;

 	-- clean out the test data
  	drop table ut_acs_objects;

	-- delete the object_type
	delete from acs_object_types where object_type = 'test_object';

        return null;

END;
$$ LANGUAGE plpgsql;



--
-- procedure ut_acs_object__new/0
--
CREATE OR REPLACE FUNCTION ut_acs_object__new(

) RETURNS integer AS $$
DECLARE
        result  boolean;
BEGIN
        raise NOTICE 'Testing new...';

        -- Tests just the common functionality of the API.

        if acs_object__new(-1000, 'test_object') <> -1000 then
           raise NOTICE 'Creating a new test object failed';
        end if;

	-- create a new object to delete; note that this test assumes that
	-- the .new operator works.

        if acs_object__new(-1001, 'test_object') <> -1001 then
           raise NOTICE 'Creating a new test object failed';
        end if;

        if acs_object__new(-1003, 'test_object') <> -1003 then
           raise NOTICE 'Creating a new test object failed';
        end if;

	-- create an object

	insert into ut_acs_objects
                                (object_id, object_type, creation_date, 
                                 security_inherit_p, last_modified)
                                values
                                (-1000, 'test_object', now(), 't', now());
	
	-- Verify that the API does the correct insert.

        select 't' into result 
          from ut_acs_objects uo, acs_objects o  
         where uo.object_id = o.object_id 
           and uo.object_id = -1000;

        if NOT FOUND then 
           raise NOTICE 'Comparing created data for object failed';
        end if;

        return null;
	
END;
$$ LANGUAGE plpgsql;



--
-- procedure ut_acs_object__delete/0
--
CREATE OR REPLACE FUNCTION ut_acs_object__delete(

) RETURNS integer AS $$
DECLARE
        v_rec   record;
BEGIN
        raise NOTICE 'Testing delete...';

	-- delete the row.
	PERFORM acs_object__delete(-1001);

 	-- verify object not there.

        select * into v_rec 
          from acs_objects where object_id = -1001;

        if FOUND then 
           raise NOTICE 'Delete verification failed';
        end if;

        return null;

END;
$$ LANGUAGE plpgsql; 

CREATE OR REPLACE FUNCTION ut_acs_object__name() RETURNS integer AS $$
BEGIN
	raise NOTICE 'Testing name...';

        if acs_object__name(-1001) <> 'Test Object -1000' then
           raise NOTICE 'Creating a name failed';
        end if;

        return null;

END;
$$ LANGUAGE plpgsql; 

CREATE OR REPLACE FUNCTION ut_acs_object__default_name() RETURNS integer AS $$
BEGIN
	raise NOTICE 'Testing default_name...';

        if acs_object__default_name(-1001) <> 'Test Object -1000' then
           raise NOTICE 'Creating a default name failed';
        end if;

        return null;

END;
$$ LANGUAGE plpgsql; 



--
-- procedure ut_acs_object__set_attribute/0
--
CREATE OR REPLACE FUNCTION ut_acs_object__set_attribute(

) RETURNS integer AS $$
DECLARE
        v_sql_result test_objects.data%TYPE;
BEGIN
        raise NOTICE 'Testing set_attribute';

	-- since we did not create a test object new constructor
	-- were going to insert into attributes here.
	insert into test_objects(test_id) values(-1003);

	PERFORM acs_object__set_attribute(-1003, 'data', '2702');

	-- since utassert is not powerful enough right now, we do this
	-- comparison manually
	select data into v_sql_result 
          from test_objects 
         where test_id = -1003;

	if v_sql_result = 2702 then
	    raise NOTICE 'SUCCESS: set_attribute';
        else
            raise NOTICE 'Verifying attribute data FAILED';
        end if;

	return null;

END;
$$ LANGUAGE plpgsql;



--
-- procedure ut_acs_object__get_attribute/0
--
CREATE OR REPLACE FUNCTION ut_acs_object__get_attribute(

) RETURNS integer AS $$
DECLARE
        v_attr_value varchar(4000);
BEGIN
        raise NOTICE 'Testing get_attribute';

	-- we assume that set attribute works. since im lazy
	-- im going to recycle the -1003 object.

	PERFORM acs_object__set_attribute(-1003, 'data', 'sugarwen');

	v_attr_value := acs_object__get_attribute(-1003, 'data');

	if v_attr_value = 'sugarwen' then
    	    raise NOTICE 'SUCCESS: get_attribute';
        else
            raise NOTICE 'Verifying get attribute data FAILED';
        end if;

	return null;

END;
$$ LANGUAGE plpgsql;

create table test_objects (
       test_id integer primary key, 
       data varchar(100)
);

select ut_acs_object__setup();

create table ut_acs_objects as
select * from acs_objects;

select ut_acs_object__new();
select ut_acs_object__delete();
select ut_acs_object__name();
select ut_acs_object__default_name();
select ut_acs_object__set_attribute();
select ut_acs_object__get_attribute();

select ut_acs_object__teardown();

drop function ut_acs_object__setup();
drop function ut_acs_object__teardown();
drop function ut_acs_object__new();
drop function ut_acs_object__delete();
drop function ut_acs_object__name();
drop function ut_acs_object__default_name();
drop function ut_acs_object__set_attribute();
drop function ut_acs_object__get_attribute();