-- @@@ START COPYRIGHT @@@
--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.
--
-- @@@ END COPYRIGHT @@@
-- DDL Metadata (glass-box) testing
-- scenario #1;
-- Plain "create trigger"

obey TEST_2_1_1(clean_up);
obey TEST_2_1_1(set_up);
log   LOG_2_1_1 clear;
obey TEST_2_1_1(test1);
obey TEST_2_1_1(test2);
obey TEST_2_1_1(test3);
obey TEST_2_1_1(test4);
obey TEST_2_1_1(test5);
obey TEST_2_1_1(test6);
obey TEST_2_1_1(test7);
-- test 8 is to repeat test 3
obey TEST_2_1_1(test3);
LOG;
obey TEST_2_1_1(clean_up);
exit;

?section clean_up
set schema CAT1.SCHM;
 
DROP TRIGGER cat1.schm.tr1;
DROP TRIGGER cat2.schm.tr2;
DROP TRIGGER cat1.schm.tr3;
DROP TRIGGER cat1.schm.tr4;
DROP TRIGGER cat1.schm.tr5;
DROP TRIGGER cat3.schm.tab3A;
DROP VIEW    cat1.schm.v211;

obey clearTables;

?section set_up
SET SCHEMA cat1.schm;
CREATE VIEW v211 AS SELECT * FROM  cat3.schm.tab3A;

?section tests

------------------------------------------------------------------
-- 	TEST CASE  2.1.1   "Simple trigger created"
------------------------------------------------------------------
?section test1

CREATE TRIGGER tr1 AFTER UPDATE
	ON tab1A
	REFERENCING OLD AS oldrow, NEW AS newrow
	FOR EACH ROW
		WHEN (oldrow.a > newrow.b)
		INSERT INTO tab1B VALUES (oldrow.b, 0);

set schema cat1.HP_DEFINITION_SCHEMA;

-------------------------
-- cat1, TRIGGERS      --
-------------------------
select activation_time,operation,granularity,columns_implicit,enabled 
from triggers where
trigger_uid in (select object_uid from objects where object_name='TR1');

------------------------------
-- cat1, TRIGGERS_CAT_USAGE --
------------------------------
select count(*) from triggers_cat_usage where
trigger_uid in (select object_uid from objects where object_name='TR1');

-------------------------
-- cat1, TRIGGER_USED  --
-------------------------
select 	SUBSTRING( obj1.object_name FROM 1 FOR 8 ) TRIGGER_NAME, 
       	SUBSTRING( obj2.object_name FROM 1 FOR 8 ) OBJ_NAME, 
	used_col_num, operation, is_subject_table
from trigger_used, objects obj1, objects obj2
where trigger_uid = obj1.object_uid and used_object_uid = obj2.object_uid;

-------------------------
-- OBJECTS --
-------------------------
select 	SUBSTRING( object_name FROM 1 FOR 8 ) TRIGGER_NAME
from objects, triggers
where object_uid = trigger_uid;

-------------------------
-- TEXT --
-------------------------
select SUBSTRING( text FROM 1 FOR 220 ) TEXT
from text, triggers
where object_uid = trigger_uid;

------------------------------------------------------------------
-- 	TEST CASE  2.1.2  "Used object is in another catalog"
------------------------------------------------------------------
?section test2

SET SCHEMA cat2.schm;

CREATE TRIGGER tr2 AFTER DELETE ON tab2A
REFERENCING OLD AS oldrow 
FOR EACH ROW
INSERT INTO cat1.schm.tab1B VALUES (oldrow.a, oldrow.b);

set schema cat2.HP_DEFINITION_SCHEMA;

-- Some sanity checks in local catalog

select activation_time,operation,granularity,columns_implicit,enabled 
from triggers where
trigger_uid in (select object_uid from objects where object_name='TR2');

select count(*) from triggers_cat_usage where
trigger_uid in (select object_uid from objects where object_name='TR2');

select SUBSTRING( object_name FROM 1 FOR 8 ) TRIGGER_NAME
from objects, triggers
where object_uid = trigger_uid;

select SUBSTRING( text FROM 1 FOR 156 ) TEXT
from text, triggers
where object_uid = trigger_uid;

select 	SUBSTRING( obj1.object_name FROM 1 FOR 8 ) TRIGGER_NAME, 
	SUBSTRING( obj2.object_name FROM 1 FOR 8 ) OBJ_NAME, 
	used_col_num, operation, is_subject_table
from trigger_used, objects obj1, objects obj2
where trigger_uid = obj1.object_uid and used_object_uid = obj2.object_uid;

-- Check the "other catalog" (cat1) where the "used object" is located

select 	SUBSTRING( obj1.object_name FROM 1 FOR 8 ) OBJ_NAME,
	SUBSTRING( obj2.object_name FROM 1 FOR 8 ) TRIGGER_NAME,
	used_col_num, operation, is_subject_table
from cat1.HP_DEFINITION_SCHEMA.trigger_used,
     cat1.HP_DEFINITION_SCHEMA.objects obj1,
     cat2.HP_DEFINITION_SCHEMA.objects obj2
where trigger_uid = obj2.object_uid and used_object_uid = obj1.object_uid;

------------------------------------------------------------------
-- 	TEST CASE  2.1.3  "The Subject Table is in another catalog"
------------------------------------------------------------------
?section test3

SET SCHEMA cat1.schm;

CREATE TRIGGER tr3 AFTER UPDATE OF (a) ON cat2.schm.tab2A
REFERENCING NEW AS newrow
FOR EACH ROW
WHEN (newrow.b = 3)
INSERT INTO tab1B VALUES (newrow.a, 22);

set schema cat1.HP_DEFINITION_SCHEMA;

-- Some sanity checks in local catalog

select activation_time,operation,granularity,columns_implicit,enabled 
from triggers where
trigger_uid in (select object_uid from objects where object_name='TR3');

select count(*) from triggers_cat_usage where
trigger_uid in (select object_uid from objects where object_name='TR3');

select SUBSTRING( object_name FROM 1 FOR 8 ) TRIGGER_NAME
from objects, triggers
where object_uid = trigger_uid;

select SUBSTRING( text FROM 1 for 220 ) TEXT
from text, triggers
where object_uid = trigger_uid;

select 	SUBSTRING( obj1.object_name FROM 1 FOR 8 ) TRIGGER_NAME,
	SUBSTRING( obj2.object_name FROM 1 FOR 8 ) OBJ_NAME,
	used_col_num, operation, is_subject_table
from trigger_used, objects obj1, objects obj2
where trigger_uid = obj1.object_uid and used_object_uid = obj2.object_uid;

-- Check the "other catalog" (cat2) where the subject table is located

select count(*) from cat2.HP_DEFINITION_SCHEMA.triggers_cat_usage
where
trigger_uid in (select object_uid from objects where object_name='TR3');
-- should return 0

-- Check the "used by triggers" MD table in cat2 

select count(*) from cat2.HP_DEFINITION_SCHEMA.trigger_used
where
trigger_uid in (select object_uid from objects where object_name='TR3');
-- should return the value 2

?section test4
----------------------------------------------------------------------------
--    TEST CASE  2.1.4  "The trigger has THE SAME NAME as the Subject Table"
----------------------------------------------------------------------------

SET SCHEMA cat3.schm;

CREATE TRIGGER tab3A BEFORE UPDATE OF (b) ON cat3.schm.tab3A
REFERENCING OLD AS oldrow, NEW AS newrow
FOR EACH ROW
WHEN (newrow.a > oldrow.a)
SET newrow.b = oldrow.b;

set schema cat3.HP_DEFINITION_SCHEMA;

-- some checks in local catalog

select count(*) from objects where object_name='TAB3A';
-- should return the value 2 (one for the trigger TAB3A, one for the table)

select count(*) from triggers_cat_usage;
-- should return 1 line 

select used_col_num, operation, is_subject_table from trigger_used;
-- should return 2 lines

------------------------------------------------------------------
--    TEST CASE  2.1.5  "Complex triggers accross 3 catalogs, with nested SQL"
------------------------------------------------------------------
?section test5

SET SCHEMA cat1.schm;

CREATE TRIGGER tr4 AFTER UPDATE OF (b) ON cat2.schm.tab2A
REFERENCING OLD AS oldtab, NEW AS newtab
FOR EACH STATEMENT
UPDATE cat3.schm.tab3A
 SET a=3 WHERE (a,b) IN 
    (SELECT t1.b, t2.a FROM cat1.schm.tab1A t1, cat1.schm.tab1A t2);

CREATE TRIGGER tr5 AFTER DELETE ON cat2.schm.tab2B
REFERENCING OLD AS oldrow
FOR EACH ROW
UPDATE cat1.schm.v211
  SET b=oldrow.a, a=(SELECT MAX(b) from cat1.schm.tab1B);

set schema cat1.HP_DEFINITION_SCHEMA;

-- some checks in local catalog

select SUBSTRING( object_name FROM 1 FOR 8 ) TRIGGER_NAME, operation, granularity
from objects obj, triggers trg
where obj.object_uid = trg.trigger_uid ;
-- Should return 4 rows (for tr1, tr3, tr4, tr5)

select count(*) from triggers_cat_usage;
-- Should return the value 7

select SUBSTRING( TEXT FROM 1 FOR 300 ) TEXT from TEXT where
object_uid in (select object_uid from objects where object_name='TR4');

-- and in the other catalogs

select tu2.used_col_num, tu2.operation , tu2.is_subject_table,
       tu3.used_col_num, tu3.operation , tu3.is_subject_table
from cat2.HP_DEFINITION_SCHEMA.trigger_used tu2,
     cat3.HP_DEFINITION_SCHEMA.trigger_used tu3
where tu2.trigger_uid = tu3.trigger_uid ;
-- Should return 1 rows (for tr4 )

select tu1.used_col_num, tu1.operation , tu1.is_subject_table,
       tu2.used_col_num, tu2.operation , tu2.is_subject_table
from cat1.HP_DEFINITION_SCHEMA.trigger_used tu1,
     cat2.HP_DEFINITION_SCHEMA.trigger_used tu2
where tu1.trigger_uid = tu2.trigger_uid ;
-- Should return 11 rows, for tr2, tr3, tr4, tr5 )

select tu1.used_col_num, tu1.operation , tu1.is_subject_table,
       tu3.used_col_num, tu3.operation , tu3.is_subject_table
from cat1.HP_DEFINITION_SCHEMA.trigger_used tu1,
     cat3.HP_DEFINITION_SCHEMA.trigger_used tu3
where tu1.trigger_uid = tu3.trigger_uid ;
-- Should return 1 rows (for tr4)

------------------------------------------------------------------
--    TEST CASE  2.1.6  "Drop trigger"
------------------------------------------------------------------
?section test6

SET SCHEMA cat3.schm;

DROP TRIGGER cat1.schm.tr1;

set schema cat1.HP_DEFINITION_SCHEMA;

--  some tests in cat1

select activation_time,operation,granularity,columns_implicit,enabled 
from triggers where
trigger_uid in (select object_uid from objects where object_name='TR1');

select count(*) from triggers_cat_usage where
trigger_uid in (select object_uid from objects where object_name='TR1');

select 	SUBSTRING( obj1.object_name FROM 1 FOR 8 ) TRIGGER_NAME,
	SUBSTRING( obj2.object_name FROM 1 FOR 8 ) USED_OBJ,
	used_col_num, operation, is_subject_table
from trigger_used, objects obj1, objects obj2
where trigger_uid = obj1.object_uid and used_object_uid = obj2.object_uid;

select SUBSTRING( object_name FROM 1 FOR 8 ) TRIGGER_NAME
from objects, triggers
where object_uid = trigger_uid;

-- was the trigger's text erased ?
select text from text, triggers
where object_uid in (select object_uid from objects where object_name='TR1');

------------------------------------------------------------------
--    TEST CASE  2.1.7  "Drop the rest of the triggers"
------------------------------------------------------------------
?section test7

DROP TRIGGER cat2.schm.tr2;
DROP TRIGGER cat1.schm.tr3;
DROP TRIGGER cat1.schm.tr4;
DROP TRIGGER cat1.schm.tr5;
DROP TRIGGER cat3.schm.tab3A;

--------------------
-- cat1, TRIGGERS --
--------------------
select * from cat1.HP_DEFINITION_SCHEMA.triggers;

------------------------------
-- cat1, TRIGGERS_CAT_USAGE --
------------------------------
select * from cat1.HP_DEFINITION_SCHEMA.triggers_cat_usage;

------------------------------
-- cat2, TRIGGERS_CAT_USAGE --
------------------------------
select * from cat2.HP_DEFINITION_SCHEMA.triggers_cat_usage;

------------------------------
-- cat3, TRIGGERS_CAT_USAGE --
------------------------------
select * from cat3.HP_DEFINITION_SCHEMA.triggers_cat_usage;

-------------------------
-- cat1, TRIGGERS_USED --
-------------------------
select * from cat1.HP_DEFINITION_SCHEMA.trigger_used;

-------------------------
-- cat2, TRIGGERS_USED --
-------------------------
select * from cat2.HP_DEFINITION_SCHEMA.trigger_used;

-------------------------
-- cat3, TRIGGERS_USED --
-------------------------
select * from cat3.HP_DEFINITION_SCHEMA.trigger_used;

