-- @@@ 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 @@@
------------------------------------------------------
--- TestMV650
------------------------------------------------------

control query default pos 'OFF';
set schema catmvs.mvschm;
set PARSERFLAGS 3;

obey TESTMV650(CLEAN_UP);
log LOGMV650 clear;

obey TESTMV650(SET_UP);
--obey PREPARE_DDL_SELECTS;

-- Run Tests
	set schema catmvs.mvschm;
	obey TESTMV650(TEST1);
	obey TESTMV650(TEST2);
	obey TESTMV650(TEST3);
	obey TESTMV650(TEST4);
	obey TESTMV650(TEST5);
	obey TESTMV650(TEST6);
	obey TESTMV650(TEST7);
	obey TESTMV650(TEST8);
	obey TESTMV650(TEST9); 
	obey TESTMV650(TEST10_AUTOMATIC_RANGELOG);
	obey TESTMV650(TEST10_NO_RANGELOG);
	obey TESTMV650(TEST10_MIXED_RANGELOG);
	obey TESTMV650(TEST10_MANUAL_RANGELOG);
	obey TESTMV650(TEST11);

obey TESTMV650(CLEAN_UP);
log;
exit;
--------------------------------------
?section SET_UP
--------------------------------------
control query default insert_vsbb 'USER';
control query default vsbb_test_mode 'ON';

--insert into HP_SYSTEM_CATALOG.SYSTEM_DEFAULTS_SCHEMA.SYSTEM_DEFAULTS
--(attribute, attr_value) values ('MV_REFRESH_MAX_PARALLELISM', '4');

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 the base tables
obey DUPENV(CREATE_SIMPLE);
obey DUPENV(CREATE_MV_DUMMY);

refresh MVGroup Dup_MVG1 with MVLOG cleanup;

prepare select_iud_log_b from
 select "@EPOCH"
        , case "@OPERATION_TYPE" when 0 then 'INSERT' 
				 when 1 then 'DELETE' 
				 when 2 then 'I-UPDATE'
				 when 3 then 'D-UPDATE'
				 when 4 then 'END-RANGE'
				 when 12 then 'BEGIN-RANGE'
          end
	, "@IGNORE"
	, prim
 from table(iud_log_table dup_b)
 order by prim asc,prim2 desc, "@TS";


prepare select_iud_log_a from
 select "@EPOCH"
        , case "@OPERATION_TYPE" when 0 then 'INSERT' 
				 when 1 then 'DELETE' 
				 when 2 then 'I-UPDATE'
				 when 3 then 'D-UPDATE'
				 when 4 then 'END-RANGE'
				 when 12 then 'BEGIN-RANGE'
          end
	, "@IGNORE"
	, prim
 from table(iud_log_table dup_a)
 order by prim desc, "@TS";

---------------------------------------------
?section CLEAN_UP
---------------------------------------------
Refresh mvgroup Dup_MVG1 cancel;
obey DUPENV(DROP_MV_DUMMY);
obey DUPENV(DROP_SIMPLE);
obey CREATERANGE(DROPALL);

drop table t1_650;
drop table t650_source cascade;
drop table t650_target cascade;

delete from HP_SYSTEM_CATALOG.SYSTEM_DEFAULTS_SCHEMA.SYSTEM_DEFAULTS
where attribute = 'MV_REFRESH_MAX_PIPELINING';
delete from HP_SYSTEM_CATALOG.SYSTEM_DEFAULTS_SCHEMA.SYSTEM_DEFAULTS
where attribute = 'MV_REFRESH_MAX_PARALLELISM';


set param ?schema_name 'MVSCHM';
obey COMPARE_NUM_OF_OBJ;

drop table catmvs.mvschm.Num_Obj;

---------------------------------------------
?section compare_a
---------------------------------------------
PREPARE stat1 FROM
select PRIM ,CH from dup_a
order by prim;
PREPARE stat2 FROM
select PRIM ,CH from table(index_table i1)
order by prim;
obey COMPARE; 
sh diff TEMP1 TEMP2>> LOGMV650 ;
log LOGMV650;

---------------------------------------------
?section compare_b
---------------------------------------------
PREPARE stat1 FROM
select PRIM ,PRIM2 ,CH from dup_b
order by prim;
PREPARE stat2 FROM
select PRIM ,PRIM2 ,CH from table(index_table i2)
order by prim;
obey COMPARE; 
sh diff TEMP1 TEMP2>> LOGMV650 ;
log LOGMV650;

---------------------------------------------
?section TEST1
---------------------------------------------

CREATE INDEX I2 on DUP_b (ch);

control query default insert_vsbb 'OFF';

 Insert NOMVLOG into Dup_B
   Values (1001,'www','I1'),(1004,'wwt','aa'),(1005,'rrr','B1'),(1007,'qqq','ff'),(1010,'ppp','gg'),
   (1011,'nlmlm','zz'),(1013,'naaa','ff'),(1015,'lll','nn'),(1016,'iii','uu'),(1017,'hhh','PP'),(1026,'gggggg','y'),
   (1035,'fff','ii'),(1037,'eeee','w'),(1040,'ddd','kk');

control query default insert_vsbb 'USER';

delete NOMVLOG from Dup_B where prim = 1004;

update NOMVLOG Dup_B 
set ch = 'up'
where prim = 1015;

execute select_iud_log_b;

obey TESTMV650(compare_b);

delete from table(iud_log_table dup_b);

drop index I2;
---------------------------------------------
?section TEST2
---------------------------------------------
control query default insert_vsbb 'USER';
alter table Dup_B  Attributes automatic RANGELOG ;

CREATE INDEX I2 on DUP_b (ch);

Insert NOMVLOG into Dup_B
	Values (1101,'www','I1'),(1104,'wwt','aa'),(1105,'rrr','B1'),(1107,'qqq','ff'),(1110,'ppp','gg'),
	(1111,'nlmlm','zz'),(1113,'naaa','ff'),(1115,'lll','nn'),(1116,'iii','uu'),(1117,'hhh','PP'),(1126,'gggggg','y'),
	(1135,'fff','ii'),(1137,'eeee','w'),(1140,'ddd','kk');

execute select_iud_log_b;

obey TESTMV650(compare_b);

delete from table(iud_log_table dup_b);
drop index I2;
---------------------------------------------
?section TEST3
---------------------------------------------
delete from  Dup_A;
delete from table(iud_log_table dup_a);

set parserflags 1024; -- allow no audit
ALTER table Dup_A attribute no audit;
reset parserflags 1024;

insert using sideinserts  NOMVLOG into Dup_A(prim,ch) 
Values  (2035,'ii'),(2034,'w'),(2033,'kk'),
		(2023,'PP'),(2022,'y'),(2016,'uu'),(2014,'nn'),(2013,'ff'),(2011,'zz'),
		(2006,'I1'),(2005,'aa'),(2003,'B1'),(2002,'ff'),(2001,'gg');

set parserflags 1024; -- allow no audit
ALTER table Dup_A attribute audit;
reset parserflags 1024;

execute select_iud_log_a;


delete from table(iud_log_table dup_a);

-------------------------------------------------
?section TEST4
-------------------------------------------------

control query default insert_vsbb 'USER';
alter table Dup_B  Attributes manual RANGELOG ;

CREATE INDEX I2 on DUP_b (ch);

Insert NOMVLOG into Dup_B
	Values (1201,'www','I1'),(1204,'wwt','aa'),(1205,'rrr','B1'),(1207,'qqq','ff'),(1210,'ppp','gg'),
	(1211,'nlmlm','zz'),(1213,'naaa','ff'),(1215,'lll','nn'),(1216,'iii','uu'),(1217,'hhh','PP'),(1226,'gggggg','y'),
	(1235,'fff','ii'),(1237,'eeee','w'),(1240,'ddd','kk');

delete NOMVLOG from Dup_B where prim = 1213;

update NOMVLOG Dup_B 
set ch = 'up'
where prim = 1237;

execute select_iud_log_b;

obey TESTMV650(compare_b);
delete from table(iud_log_table dup_b);
drop index I2;
-------------------------------------------------
?section TEST5
-------------------------------------------------
control query default insert_vsbb 'USER';
alter table Dup_B  Attributes mixed RANGELOG ;
CREATE INDEX I2 on DUP_b (ch);


Insert NOMVLOG into Dup_B
	Values (1301,'www','I1'),(1304,'wwt','aa'),(1305,'rrr','B1'),(1307,'qqq','ff'),(1310,'ppp','gg'),
	(1311,'nlmlm','zz'),(1313,'naaa','ff'),(1315,'lll','nn'),(1316,'iii','uu'),(1317,'hhh','PP'),(1326,'gggggg','y'),
	(1335,'fff','ii'),(1337,'eeee','w'),(1340,'ddd','kk');

delete NOMVLOG from Dup_B where prim = 1313;

update NOMVLOG Dup_B 
set ch = 'up'
where prim = 1337;

execute select_iud_log_b;

Insert into Dup_B
	Values (3301,'www','I1'),(3304,'wwt','aa'),(3305,'rrr','B1'),(3307,'qqq','ff'),(3310,'ppp','gg'),
	(3311,'nlmlm','zz'),(3313,'naaa','ff'),(3315,'lll','nn'),(3316,'iii','uu'),(3317,'hhh','PP'),(3326,'gggggg','y'),
	(3335,'fff','ii'),(3337,'eeee','w'),(3340,'ddd','kk');

execute select_iud_log_b;
obey TESTMV650(compare_b);
delete from table(iud_log_table dup_b);
drop index I2;
-------------------------------------------------
?section TEST6
-------------------------------------------------

create table t1_650
like Dup_B;
ALTER TABLE t1_650 attribute all mvs allowed;


Insert NOMVLOG into t1_650
	Values (1301,'www','I1'),(1304,'wwt','aa'),(1305,'rrr','B1'),(1307,'qqq','ff'),(1310,'ppp','gg'),
	(1311,'nlmlm','zz'),(1313,'naaa','ff'),(1315,'lll','nn'),(1316,'iii','uu'),(1317,'hhh','PP'),(1326,'gggggg','y'),
	(1335,'fff','ii'),(1337,'eeee','w'),(1340,'ddd','kk');

delete NOMVLOG from t1_650 where prim = 1313;

update NOMVLOG t1_650 
set ch = 'up'
where prim = 1337;

select * from table(iud_log_table t1_650);

alter table t1_650 Attributes automatic RANGELOG ;

Insert NOMVLOG into t1_650
	Values (1401,'www','I1'),(1404,'wwt','aa'),(1405,'rrr','B1'),(1407,'qqq','ff'),(1410,'ppp','gg'),
	(1411,'nlmlm','zz'),(1413,'naaa','ff'),(1415,'lll','nn'),(1416,'iii','uu'),(1417,'hhh','PP'),(1426,'gggggg','y'),
	(1435,'fff','ii'),(1437,'eeee','w'),(1440,'ddd','kk');

select * from table(iud_log_table t1_650);

delete NOMVLOG from t1_650;

set parserflags 1024; -- allow no audit
ALTER table t1_650 attribute no audit;
reset parserflags 1024;

insert using sideinserts  NOMVLOG into t1_650(prim,prim2,ch) 
Values   (1501,'www','I1'),(1504,'wwt','aa'),(1505,'rrr','B1'),(1507,'qqq','ff'),(1510,'ppp','gg'),
	(1511,'nlmlm','zz'),(1513,'naaa','ff'),(1515,'lll','nn'),(1516,'iii','uu'),(1517,'hhh','PP'),(1526,'gggggg','y'),
	(1535,'fff','ii'),(1537,'eeee','w'),(1540,'ddd','kk');
set parserflags 1024; -- allow no audit
ALTER table t1_650 attribute audit;
reset parserflags 1024;


select * from table(iud_log_table t1_650);

drop table t1_650;


-------------------------------------------------
?section TEST7
-------------------------------------------------

control query default insert_vsbb 'USER';
alter table Dup_B  Attributes manual RANGELOG ;

CREATE INDEX I2 on DUP_b (ch);

Insert into Dup_B
	Values (1401,'www','I1'),(1404,'wwt','aa'),(1405,'rrr','B1'),(1407,'qqq','ff'),(1410,'ppp','gg'),
	(1411,'nlmlm','zz'),(1413,'naaa','ff'),(1415,'lll','nn'),(1416,'iii','uu'),(1417,'hhh','PP'),(1426,'gggggg','y'),
	(1435,'fff','ii'),(1437,'eeee','w'),(1440,'ddd','kk');

delete from Dup_B where prim = 1413;

update Dup_B 
set ch = 'up'
where prim = 1437;

execute select_iud_log_b;
obey TESTMV650(compare_b);
delete from table(iud_log_table dup_b);

drop index I2;
-------------------------------------------------
?section TEST8
-------------------------------------------------
Alter table Dup_B Attributes automatic RANGELOG;
Alter table Dup_B Attributes insertlog;

CREATE INDEX I2 on DUP_b (ch);

Insert into Dup_B
	Values (4501,'www','I1'),(4504,'wwt','aa'),(4505,'rrr','B1'),(4507,'qqq','ff'),(4510,'ppp','gg'),
	(4511,'nlmlm','zz'),(4513,'naaa','ff'),(4515,'lll','nn'),(4516,'iii','uu'),(4517,'hhh','PP'),(4526,'gggggg','y'),
	(4535,'fff','ii'),(4537,'eeee','w'),(4540,'ddd','kk');

delete from Dup_B where prim = 4513;

update Dup_B 
set ch = 'up'
where prim = 4537;

execute select_iud_log_b;
obey TESTMV650(compare_b);
delete from table(iud_log_table dup_b);

drop index I2;
-------------------------------------------------
?section TEST9
-------------------------------------------------
refresh Dup_MV2 recompute;

create mv Dup_MV5
	refresh on request
	initialize on create
	as
	select count(*) cnt from Dup_MV2;
ALTER MV Dup_MV5 attribute all mvs allowed;

Alter mv Dup_MV2 MVAttribute NO AUDITONREFRESH;

delete from Dup_MV2;

select "@EPOCH"
        , case "@OPERATION_TYPE" when 0 then 'INSERT' 
				 when 1 then 'DELETE' 
				 when 2 then 'I-UPDATE'
				 when 3 then 'D-UPDATE'
				 when 4 then 'END-RANGE'
				 when 12 then 'BEGIN-RANGE'
        end
	, "@IGNORE"
from table(iud_log_table dup_mv2)
order by "@TS";

delete from table(iud_log_table dup_mv2);

drop mv Dup_MV5;

---------------------------------------------
?section TEST10_AUTOMATIC_RANGELOG
---------------------------------------------
delete from  Dup_A;
delete from table(iud_log_table dup_a);

set parserflags 1024; -- allow no audit
ALTER table Dup_A attribute no audit;
reset parserflags 1024;

insert using sideinserts  into Dup_A(prim,ch) 
Values  (2035,'ii'),(2034,'w'),(2033,'kk'),
		(2023,'PP'),(2022,'y'),(2016,'uu'),(2014,'nn'),(2013,'ff'),(2011,'zz'),
		(2006,'I1'),(2005,'aa'),(2003,'B1'),(2002,'ff'),(2001,'gg');

set parserflags 1024; -- allow no audit
ALTER table Dup_A attribute audit;
reset parserflags 1024;

execute select_iud_log_a;
delete from table(iud_log_table dup_a);

---------------------------------------------
?section TEST10_NO_RANGELOG
---------------------------------------------
delete from  Dup_A;
delete from table(iud_log_table dup_a);

alter table Dup_A  Attributes no RANGELOG ;

set parserflags 1024; -- allow no audit
ALTER table Dup_A attribute no audit;
reset parserflags 1024;

insert using sideinserts into Dup_A(prim,ch) 
Values  (2035,'ii'),(2034,'w'),(2033,'kk'),
		(2023,'PP'),(2022,'y'),(2016,'uu'),(2014,'nn'),(2013,'ff'),(2011,'zz'),
		(2006,'I1'),(2005,'aa'),(2003,'B1'),(2002,'ff'),(2001,'gg');

set parserflags 1024; -- allow no audit
ALTER table Dup_A attribute audit;
reset parserflags 1024;

execute select_iud_log_a;
delete from table(iud_log_table dup_a);

---------------------------------------------
?section TEST10_MIXED_RANGELOG
---------------------------------------------
delete from  Dup_A;
delete from table(iud_log_table dup_a);

alter table Dup_A  Attributes mixed RANGELOG ;

set parserflags 1024; -- allow no audit
ALTER table Dup_A attribute no audit;
reset parserflags 1024;

insert using sideinserts into Dup_A(prim,ch) 
Values  (2035,'ii'),(2034,'w'),(2033,'kk'),
		(2023,'PP'),(2022,'y'),(2016,'uu'),(2014,'nn'),(2013,'ff'),(2011,'zz'),
		(2006,'I1'),(2005,'aa'),(2003,'B1'),(2002,'ff'),(2001,'gg');

set parserflags 1024; -- allow no audit
ALTER table Dup_A attribute audit;
reset parserflags 1024;

execute select_iud_log_a;
delete from table(iud_log_table dup_a);

---------------------------------------------
?section TEST10_MANUAL_RANGELOG
---------------------------------------------
delete from  Dup_A;
delete from table(iud_log_table dup_a);

alter table Dup_A  Attributes manual RANGELOG ;

set parserflags 1024; -- allow no audit
ALTER table Dup_A attribute no audit;
reset parserflags 1024;

insert using sideinserts  into Dup_A(prim,ch) 
Values  (2035,'ii'),(2034,'w'),(2033,'kk'),
		(2023,'PP'),(2022,'y'),(2016,'uu'),(2014,'nn'),(2013,'ff'),(2011,'zz'),
		(2006,'I1'),(2005,'aa'),(2003,'B1'),(2002,'ff'),(2001,'gg');

set parserflags 1024; -- allow no audit
ALTER table Dup_A attribute audit;
reset parserflags 1024;

execute select_iud_log_a;
delete from table(iud_log_table dup_a);

---------------------------------------------
?section TEST11
-- Make sure that VSBB works fine even when VSBB_TEST_MODE is turned off.
---------------------------------------------
	
control query default vsbb_test_mode 'OFF';
set parserflags 3;

obey CREATERANGE(CREAT);

create table t650_source
  ( pk   int not null primary key,
    data int)
    store by primary key
    attribute automatic rangelog, all mvs allowed;

create table t650_target like t650_source;
alter  table t650_target attribute all mvs allowed;

insert into t650_source
  select runningcount(*), runningcount(*)
  from Range_Factor1000 t1, Range_Factor2 t2
  sequence by t1.ts, t2.ts;

update statistics for table t650_source on every column;

create mv mv650
  refresh on request
  initialize on create
  as  select pk, count(data) as cd
      from t650_target
      group by pk;

-- Using VSBB_USER mode from defaults table
control query default insert_vsbb 'USER';
insert into t650_target select * from t650_source;
select count(*) from table (iud_log_table t650_target);
delete from t650_target;
delete from table (iud_log_table t650_target);

-- Using VSBB_SYSTEM mode from defaults table
control query default insert_vsbb 'SYSTEM';
insert into t650_target select * from t650_source;
select count(*) from table (iud_log_table t650_target);
delete from t650_target;
delete from table (iud_log_table t650_target);

-- Using command line override to VSBB
insert using vsbb into t650_target select * from t650_source;
select count(*) from table (iud_log_table t650_target);
delete from t650_target;
delete from table (iud_log_table t650_target);

-- Using command line override to SideTree Insert.
set parserflags 1024; -- allow no audit
alter table t650_target attribute no audit;
reset parserflags 1024;
insert using sideinserts into t650_target select * from t650_source;
set parserflags 1024; -- allow no audit
alter table t650_target attribute audit;
reset parserflags 1024;
select count(*) from table (iud_log_table t650_target);
delete from t650_target;
delete from table (iud_log_table t650_target);

?section TEST11_IMPORT
-- This section is skipped until import is ready.
--Using Import with the -S switch (the data is already sorted)
sh sh -c '$mxcidir/import catmvs.mvschm.t650_target -S -I importdata650.tbl' | tee -a LOGMV650;
select count(*) from table (iud_log_table t650_target);
delete from t650_target;
delete from table (iud_log_table t650_target);

--Using Import without the -S switch ( the data will be sorted again by the optimizer).
sh sh -c '$mxcidir/import catmvs.mvschm.t650_target -I importdata650.tbl' | tee -a LOGMV650;
select count(*) from table (iud_log_table t650_target);
delete from t650_target;
delete from table (iud_log_table t650_target);

