-- @@@ 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 @@@
set schema catmvs.mvschm;
--obey PREPARE_DDL_SELECTS;

--obey ENVIRON;
delete from HP_SYSTEM_CATALOG.SYSTEM_DEFAULTS_SCHEMA.SYSTEM_DEFAULTS
where attribute = 'MV_REFRESH_MAX_PARALLELISM';
delete from HP_SYSTEM_CATALOG.SYSTEM_DEFAULTS_SCHEMA.SYSTEM_DEFAULTS
where attribute = 'MV_REFRESH_MAX_PIPELINING';
insert into HP_SYSTEM_CATALOG.SYSTEM_DEFAULTS_SCHEMA.SYSTEM_DEFAULTS
(attribute, attr_value) values ('MV_REFRESH_MAX_PARALLELISM', '1');
insert into HP_SYSTEM_CATALOG.SYSTEM_DEFAULTS_SCHEMA.SYSTEM_DEFAULTS
(attribute, attr_value) values ('MV_REFRESH_MAX_PIPELINING', '1');
obey TESTMV251(CLEAN_UP);
-- Disable automatic creation of MVGroup objects
control query default MVGROUP_AUTOMATIC_CREATION 'OFF';
log LOGMV251 clear;

obey TESTMV251(SETUP);

-- Run Tests	
	obey TESTMV251(TEST9_1);
	sh sh runmxci.ksh  -i "TESTMV251(TEST9_2)" -u $$SQLUSER7X$$;	
	log LOGMV251;
	obey TESTMV251(TEST10_1);
	sh sh runmxci.ksh  -i "TESTMV251(TEST10_2)" -u $$SQLUSER7X$$;	
	log LOGMV251;

		obey TESTMV251(TEST23_1);
	sh sh runmxci.ksh  -i "TESTMV251(TEST23_2)" -u $$SQLUSER7X$$;	
	log LOGMV251;
	obey TESTMV251(TEST24_1);
	sh sh runmxci.ksh  -i "TESTMV251(TEST24_2)" -u $$SQLUSER7X$$;	
	log LOGMV251;
	obey TESTMV251(TEST24_3);


	sh sh runmxci.ksh  -i "TESTMV251(TEST34)" -u $$SQLUSER7X$$;	
	log LOGMV251;


-- Check the drop statement on the way out
obey TESTMV251(CLEAN_UP);
reset envvar MV_UNSUPPORTED_FEATURE_DEBUG;
log;
delete from HP_SYSTEM_CATALOG.SYSTEM_DEFAULTS_SCHEMA.SYSTEM_DEFAULTS
where attribute = 'MV_REFRESH_MAX_PARALLELISM';
delete from HP_SYSTEM_CATALOG.SYSTEM_DEFAULTS_SCHEMA.SYSTEM_DEFAULTS
where attribute = 'MV_REFRESH_MAX_PIPELINING';
exit;

-----------------------------------------------------------------
?section SETUP
-----------------------------------------------------------------
set envvar MV_UNSUPPORTED_FEATURE_DEBUG 1;
create table Num_Obj(schema_name CHARACTER(50), num_of_objects int);
ALTER TABLE Num_Obj attribute all mvs allowed;

set param ?schema_name 'MVSCHM';
obey INSERT_NUM_OF_OBJ;

create mvgroup MVG251_cancel;

create table T1_251 ( a int not null not droppable,
			  b int not null not droppable, 
			  c int not null not droppable, primary key (a))
			  store by primary key;
ALTER TABLE T1_251 attribute all mvs allowed;

insert into T1_251 values ( 1,1,1),(2,1,2),(3,4,3),(5,4,4);


PREPARE select_index FROM
  select  RUNNINGCOUNT(*) num,valid_data
				   from HP_DEFINITION_SCHEMA.ACCESS_PATHS
				   where ACCESS_PATH_UID <> table_uid 
				   and table_uid =( select OBJECT_UID from
				    HP_DEFINITION_SCHEMA.OBJECTS
					where OBJECT_NAME = ?t_name
				    for read uncommitted access)
SEQUENCE BY ACCESS_PATH_UID ;

PREPARE select_audit_status FROM
 select  AUDITED 
				   from HP_DEFINITION_SCHEMA.ACCESS_PATHS
				   where ACCESS_PATH_UID = table_uid 
				   and table_uid =( select OBJECT_UID from
				    HP_DEFINITION_SCHEMA.OBJECTS
					where OBJECT_NAME = ?t_name
				    for read uncommitted access);

PREPARE select_audited_index_status FROM
select  RUNNINGCOUNT(*) num,audited
				from HP_DEFINITION_SCHEMA.access_paths a
				where table_uid =( select OBJECT_UID from
				    HP_DEFINITION_SCHEMA.OBJECTS
					where OBJECT_NAME = ?t_name
				    for read uncommitted access)
				and access_path_uid <> table_uid
SEQUENCE BY ACCESS_PATH_UID ;



create index another_index on T1_251 ( a );

create table employee 
like mvtestenv.persnl.employee;
ALTER TABLE employee attribute all mvs allowed;

create table orders
like mvtestenv.sales.Orders;
ALTER TABLE orders attribute all mvs allowed;

log;
insert into orders 
select * from mvtestenv.sales.Orders;
log LOGMV251;


create mv MV_251_test34
	Refresh on request
	initialize on create 
	as 
	select Employee.empnum,Employee.last_name,Employee.deptnum,sum(Employee.salary) sal,
	count(orders.salesrep) num_of_deals        
		from Employee,orders
		where Employee.empnum=orders.salesrep
	 group by Employee.empnum,Employee.last_name,Employee.deptnum;
ALTER MV MV_251_test34 attribute all mvs allowed;
ALTER mvgroup catmvs.mvschm.MVG251_cancel ADD MV_251_test34;


-----------------------------------------------------------------
?section TEST9_1
-----------------------------------------------------------------
create mv T_251_MV11
refresh on request
initialize on refresh
--MVAttributes NO AUDIT
store by (b)
as
select b, c, sum(a) sa
from T1_251
group by b, c;
ALTER MV T_251_MV11 attribute all mvs allowed;

ALTER mvgroup MVG251_cancel ADD T_251_MV11;

refresh  T_251_MV11 outfile REFRESH.LOG;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV251;
log LOGMV251;

insert into T1_251 values (91,1, 3),(92,2, 5),(93,3, 7);

grant select, insert on T1_251 to $$SQLUSER7Q$$;
grant select, insert on T_251_MV11 to $$SQLUSER7Q$$;
log;

-----------------------------------------------------------------
?section TEST9_2
-----------------------------------------------------------------
obey cidefs;
log LOGMV251;

set schema catmvs.mvschm;

-- no Purgedata executed
refresh T_251_MV11 recompute debug 51,65,66;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV251;
log LOGMV251;


PREPARE select_audit_status FROM
 select  AUDITED 
				   from HP_DEFINITION_SCHEMA.ACCESS_PATHS
				   where ACCESS_PATH_UID = table_uid 
				   and table_uid =( select OBJECT_UID from
				    HP_DEFINITION_SCHEMA.OBJECTS
					where OBJECT_NAME = ?t_name
				    for read uncommitted access);


PREPARE select_audited_index_status FROM
select  RUNNINGCOUNT(*) num,audited
				from HP_DEFINITION_SCHEMA.access_paths a
				where table_uid =( select OBJECT_UID from
				    HP_DEFINITION_SCHEMA.OBJECTS
					where OBJECT_NAME = ?t_name
				    for read uncommitted access)
				and access_path_uid <> table_uid
SEQUENCE BY ACCESS_PATH_UID ;

set param ?t_name 'T_251_MV11';
-- expected - MV11 no audit
execute select_audit_status;
execute select_audited_index_status;


log;

-----------------------------------------------------------------
?section TEST10_1
-----------------------------------------------------------------
create mv T_251_MV12
refresh on request
initialize on refresh
--MVAttributes NO AUDITONREFRESH
store by (b)
as
select b, c, sum(a) sa
from T1_251
group by b, c;
ALTER MV T_251_MV12 attribute all mvs allowed;

ALTER mvgroup MVG251_cancel ADD T_251_MV12;

refresh T_251_MV12 outfile REFRESH.LOG;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV251;
log LOGMV251;

insert into T1_251 values (95,1, 34),(96,2, 45),(97,3, 56);

grant select, insert,delete on T1_251 to $$SQLUSER7Q$$;
grant select, insert,delete on T_251_MV12 to $$SQLUSER7Q$$;
log;
-----------------------------------------------------------------
?section TEST10_2
-----------------------------------------------------------------
obey cidefs;
log LOGMV251;
set schema catmvs.mvschm;

-- Purgedata executed,NoDelete
refresh T_251_MV12 recompute debug 51,65,66;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV251;
log LOGMV251;


log;


-----------------------------------------------------------------
?section TEST10_3
-----------------------------------------------------------------
log LOGMV251;

prepare stat1 from
select b, c, sum(a) sa
from T1_251
group by b, c
order by b, c;
prepare stat2 from
select * from T_251_MV12
order by b, c;


obey COMPARE; 
sh diff TEMP1 TEMP2>> LOGMV251 ;
log LOGMV251;

set param ?t_name 'T_251_MV12';
-- expected - MV12  audit
execute select_audit_status;


-----------------------------------------------------------------
?section TEST23_1
-----------------------------------------------------------------
create mv T_251_MV25
refresh on request
initialize on refresh
--MVAttributes NO AUDIT
store by (b)
as
select b, sum(a) sa
from T1_251
group by b;
ALTER MV T_251_MV25 attribute all mvs allowed;
ALTER mvgroup MVG251_cancel ADD T_251_MV25;

refresh T_251_MV25 outfile REFRESH.LOG;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV251;
log LOGMV251;

insert into T1_251 values (242,1, 232),(243,2, 324),(244,3,534);

grant select, insert , delete on T_251_MV25 to $$SQLUSER7Q$$;
grant select on T1_251 to $$SQLUSER7Q$$;

log;
-----------------------------------------------------------------
?section TEST23_2
-----------------------------------------------------------------
obey cidefs;
log LOGMV251;

set schema catmvs.mvschm;

-- no Purgedata executed
refresh T_251_MV25 recompute debug 51,65;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV251;
log LOGMV251;

prepare stat1 from
select b, sum(a) sa
from T1_251
group by b
order by b;
cqd ALLOW_DML_ON_NONAUDITED_TABLE 'ON';
prepare stat2 from
select * from T_251_MV25
order by b;
cqd ALLOW_DML_ON_NONAUDITED_TABLE reset;
begin work;
obey COMPARE; 
sh diff TEMP1 TEMP2>> LOGMV251 ;
log LOGMV251;
commit;


PREPARE select_audit_status FROM
 select  AUDITED 
				   from HP_DEFINITION_SCHEMA.ACCESS_PATHS
				   where ACCESS_PATH_UID = table_uid 
				   and table_uid =( select OBJECT_UID from
				    HP_DEFINITION_SCHEMA.OBJECTS
					where OBJECT_NAME = ?t_name
				    for read uncommitted access);


PREPARE select_audited_index_status FROM
select  RUNNINGCOUNT(*) num,audited
				from HP_DEFINITION_SCHEMA.access_paths a
				where table_uid =( select OBJECT_UID from
				    HP_DEFINITION_SCHEMA.OBJECTS
					where OBJECT_NAME = ?t_name
				    for read uncommitted access)
				and access_path_uid <> table_uid
SEQUENCE BY ACCESS_PATH_UID ;

set param ?t_name 'T_251_MV25';
-- expected - MV24 no audit
execute select_audit_status;
execute select_audited_index_status;

log;

-----------------------------------------------------------------
?section TEST24_1
-----------------------------------------------------------------
create mv T_251_MV26
refresh on request
initialize on refresh
--MVAttributes NO AUDIT
store by (b)
as
select b, sum(a) sa
from T1_251
group by b;
ALTER MV T_251_MV26 attribute all mvs allowed;
ALTER mvgroup MVG251_cancel ADD T_251_MV26;

refresh T_251_MV26 outfile REFRESH.LOG;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV251;
log LOGMV251;

insert into T1_251 values (251,1,435),(252,2,64),(253,3,69);

grant select, insert on T_251_MV26 to $$SQLUSER7Q$$;
grant select on T1_251 to $$SQLUSER7Q$$;
log;
-----------------------------------------------------------------
?section TEST24_2
-----------------------------------------------------------------
obey cidefs;
log LOGMV251;

set schema catmvs.mvschm;

-- no Purgedata executed
refresh T_251_MV26 recompute debug 51,65;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV251;
log LOGMV251;


log;

-----------------------------------------------------------------
?section TEST24_3
-----------------------------------------------------------------

log LOGMV251;

set param ?t_name 'T_251_MV26';
-- expected - MV26 no audit
execute select_audit_status;




------------------------------------------------------------------
?section TEST34
------------------------------------------------------------------
obey cidefs;
log LOGMV251;

set schema catmvs.mvschm;
-- expected failure - no privilege
refresh MV_251_test34 outfile REFRESH.LOG;
log;
--sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV251;
log LOGMV251;

-- expected - 0
select count(*) from HP_DEFINITION_SCHEMA.ddl_locks;
log;


-----------------------------------------------------------------
?section CLEAN_UP
-----------------------------------------------------------------

refresh mvgroup MVG251_cancel cancel outfile REFRESH.LOG;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV251;
log LOGMV251;



drop table T1_251 cascade;
drop mv MV_251_test34;

drop mvgroup MVG251_cancel;
drop table employee;
drop table orders;

set param ?schema_name 'MVSCHM';
obey COMPARE_NUM_OF_OBJ;

drop table catmvs.mvschm.Num_Obj;
