/****************************************************************************
Function Description:
	_test_setup(): Initialize the environment
	_test_teardown(): Clean up
	_test_object_insert(): Insert records into "object"
	_test_object_update(): Update records of "object"
	_test_object_delete(): Delete records from "object"
	_test_containment_insert(): Insert records into "containment"
	_test_containmnet_delete(): Delete records from "containment"
	_trigger_update_containment(): The trigger on table "object" 
		that is used to update table "containment"
	_trigger_update_object(): The trigger on table "containment" 
		that is used to update table "object"
	_test_delete_and_drop(): Delete records from "object" and drop table "object"

Usage:
	Before running any test, you should reload test.sql use the command 
	"\i test.sql" for the function will be analysed and cached once it run. 
	If the table was deleted, there will be errors.
	See the results use the command "SELECCT * FROM object;" and 
	"SELECT * FROM containment" to examine the records in table "object"
	and table "containment".
	
	Test inserting records into table "object":
	\i test.sql;
	SELECT _test_setup();
	SELECT _test_object_insert();
	SELECT * FROM object;
	SELECT * FROM containment;
	
	Test updating table "object":
	\i test.sql;
	SELECT _test_setup();
	SELECT _test_object_insert();
	SELECT _test_object_update();
	SELECT * FROM object;
	SELECT * FROM containment;
	
	Test deleting records from "object":
	\i test.sql;
	SELECT _test_setup();
	SELECT _test_object_insert();
	SELECT _test_object_delete();
	SELECT * FROM object;
	SELECT * FROM containment;

	Test inserting records into table "containment":
	\i test.sql;
	SELECT _test_setup();
	SELECT _test_object_insert();
	SELECT _test_containment_insert();
	SELECT * FROM object;
	SELECT * FROM containment;
	
	Test deleting records from table "containment":
	\i test.sql;
	SELECT _test_setup();
	SELECT _test_object_insert();
	SELECT _test_containment_delete();
	SELECT * FROM object;
	SELECT * FROM containment;

!!!!!!	All above test cases can pass. But the following is an exception:
	\i test.sql;
	SELECT _test_setup();
	SELECT _test_object_insert();
	SELECT _test_delete_and_drop();

****************************************************************************/

-----------------------------------------------------------------------------
-- Tests
-----------------------------------------------------------------------------

-- Initialize the test environment
CREATE OR REPLACE FUNCTION _test_setup()
RETURNS void AS $$
BEGIN
	PERFORM _helper_drop_table('object');
	CREATE TABLE object
	(
		name name,
		pid oid
	);
	PERFORM _helper_drop_table('containment');
	CREATE TABLE containment
	(
		parent oid,
		child oid
	);
	CREATE TRIGGER _trigger_update_containment AFTER DELETE OR INSERT OR UPDATE ON object 
		FOR EACH ROW EXECUTE PROCEDURE _trigger_update_containment();
	CREATE TRIGGER _trigger_update_object AFTER DELETE OR INSERT ON containment 
		FOR EACH ROW EXECUTE PROCEDURE _trigger_update_object();	
	RETURN;
END;
$$ LANGUAGE plpgsql;

-- Clean up
CREATE OR REPLACE FUNCTION _test_teardown()
RETURNS void AS $$
BEGIN
	DROP TABLE object;
	DROP TABLE containment;
	RETURN;
END;
$$ LANGUAGE plpgsql;

-- Insert records into "object"
CREATE OR REPLACE FUNCTION _test_object_insert()
RETURNS void AS $$
DECLARE
	l_pid oid;
BEGIN
	INSERT INTO object(name) VALUES('obj1');
	GET DIAGNOSTICS l_pid = RESULT_OID;
	INSERT INTO object(name, pid) VALUES('obj11', l_pid);
	INSERT INTO object(name, pid) VALUES('obj12', l_pid);
	INSERT INTO object(name) VALUES('obj21');
	INSERT INTO object(name, pid) VALUES('obj22', l_pid);
	INSERT INTO object(name) VALUES('obj23');
	INSERT INTO object(name, pid) VALUES('obj24', l_pid);
	INSERT INTO object(name, pid) VALUES('obj25', l_pid);
	RETURN;
END;
$$ LANGUAGE plpgsql;

-- Update records of "object"
CREATE OR REPLACE FUNCTION _test_object_update()
RETURNS void AS $$
DECLARE
	l_obj1 oid;
	l_obj21 oid;
BEGIN
	SELECT oid INTO l_obj1 FROM object WHERE name = 'obj1';
	SELECT oid INTO l_obj21 FROM object WHERE name = 'obj21';

	-- NEW.pid = OLD.pid = NULL
	UPDATE object SET pid = NULL WHERE name = 'obj21';

	-- NEW.pid = OLD.pid
	UPDATE object SET pid = l_obj1 WHERE name = 'obj22';

	-- OLD.pid = NULL AND NEW.pid <> NULL
	UPDATE object SET pid = l_obj21 WHERE name = 'obj23';

	-- OLD.pid <> NULL AND NEW.pid = NULL
	UPDATE object SET pid = NULL WHERE name = 'obj24';

	-- OLD.pid <> NULL AND NEW.pid <> NULL
	UPDATE object SET pid = l_obj21 WHERE name = 'obj25';

	RETURN;
END;
$$ LANGUAGE plpgsql;

-- Delete records of "object"
CREATE OR REPLACE FUNCTION _test_object_delete()
RETURNS void AS $$
BEGIN
	DELETE FROM object WHERE name = 'obj1';
	RETURN;
END;
$$ LANGUAGE plpgsql;

-- Delete records in "containment"
CREATE OR REPLACE FUNCTION _test_containment_delete()
RETURNS void AS $$
DECLARE
	l_obj12 oid;
BEGIN
	SELECT oid INTO l_obj12 FROM object WHERE name = 'obj12';
	DELETE FROM containment WHERE child = l_obj12;
	RETURN;
END;
$$ LANGUAGE plpgsql;

-- Insert records to "containment"
CREATE OR REPLACE FUNCTION _test_containment_insert()
RETURNS void AS $$
DECLARE
	l_obj11 oid;
	l_obj12 oid;
BEGIN
	SELECT oid INTO l_obj11 FROM object WHERE name = 'obj11';
	SELECT oid INTO l_obj12 FROM object WHERE name = 'obj12';
	INSERT INTO containment(parent, child) VALUES(l_obj11, l_obj12);
	RETURN;
END;
$$ LANGUAGE plpgsql;

--
-- A special test
-- 
CREATE OR REPLACE FUNCTION _test_delete_and_drop()
RETURNS void AS $$
BEGIN
	DELETE FROM object;
	DROP TABLE object;
	RETURN;
END;
$$ LANGUAGE plpgsql;

-----------------------------------------------------------------------------
-- Triggers
-----------------------------------------------------------------------------
-- The trigger on table "object" that is used to update table "containment"
CREATE OR REPLACE FUNCTION _trigger_update_containment()
RETURNS trigger AS $$
DECLARE
	l_rec RECORD;
BEGIN
	IF TG_OP = 'DELETE' THEN
		-- clear all descendants
		FOR l_rec IN SELECT child FROM containment WHERE parent = OLD.oid LOOP
			DELETE FROM object WHERE oid = l_rec.child;
		END LOOP;
		-- delete corresponding record in table containment
		DELETE FROM containment WHERE child = OLD.oid;
	ELSIF TG_OP = 'INSERT' THEN
		-- add record in table containment
		IF NEW.pid NOTNULL THEN
			INSERT INTO containment(parent, child) VALUES(NEW.pid, NEW.oid);
		END IF;		
	ELSIF TG_OP = 'UPDATE' THEN
		-- NEW is equral to OLD
		IF (NEW.pid ISNULL AND OLD.pid ISNULL) OR NEW.pid = OLD.pid THEN
			RETURN null;
		END IF;
	
		IF OLD.pid ISNULL THEN
			IF NOT EXISTS(SELECT * FROM containment WHERE child = NEW.oid) THEN
				INSERT INTO containment(parent, child) VALUES(NEW.pid, NEW.oid);
			END IF;
		ELSE
			IF NEW.pid IS NULL THEN
				DELETE FROM containment WHERE child = NEW.oid;
			ELSE
				UPDATE containment SET parent = NEW.pid WHERE child = NEW.oid;
			END IF;
		END IF;			
	END IF;
	RETURN null;
END;
$$ LANGUAGE plpgsql;

-- The trigger on table "containment" that is used to update table "object"
CREATE OR REPLACE FUNCTION _trigger_update_object()
RETURNS trigger AS $$
BEGIN
	IF TG_OP = 'DELETE' THEN
		UPDATE object SET pid = null WHERE oid = OLD.child;
	ELSIF TG_OP = 'INSERT' THEN
		UPDATE object SET pid = NEW.parent WHERE oid = NEW.child;
	END IF;
	RETURN null;
END;
$$ LANGUAGE plpgsql;

-----------------------------------------------------------------------------
-- Helper functions
-----------------------------------------------------------------------------
/*
Judge a table exists or not
*/
CREATE OR REPLACE FUNCTION _helper_is_table_existent(name)
RETURNS boolean AS $$
BEGIN
	RETURN EXISTS(SELECT * FROM pg_class WHERE relname = $1);
END;
$$ LANGUAGE plpgsql;

/*
Drop a table and its descendants by name
*/
CREATE OR REPLACE FUNCTION _helper_drop_table(name)
RETURNS void AS $$
BEGIN
	IF _helper_is_table_existent($1)
	THEN
		EXECUTE 'DROP TABLE ' || quote_ident($1) || ' CASCADE';
	END IF;
	RETURN;
END;
$$ LANGUAGE plpgsql;