-- @@@ 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 @@@
------------------------------------------------------
--- Testmv237a
--- 2.13.1
--- PURPOSE: duplicate elimination tests 
---          with range inserts 
--
--- The same as TestMV237, but enforces full DE
------------------------------------------------------

set schema catmvs.mvschm;
obey TESTMV237A(CLEAN_UP);
log LOGMV237A clear;

obey TESTMV237A(SET_UP);
-- Run Tests
set schema catmvs.mvschm;
obey TESTMV237A(TEST1);

-- Clean and exit
obey TESTMV237A(CLEAN_UP);
log;
exit;

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

--Activate the VSBB inserts
control query default insert_vsbb 'USER';
control query default vsbb_test_mode 'ON';
control query default pos 'off';

set PARSERFLAGS 3;

set schema catmvs.mvschm;

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;

obey CREATERANGE(CREAT);
-- Create the base tables
obey DUPENV(CREATE_SIMPLE);
obey DUPENV(CREATE_DUP_MV1);

delete from Dup_A where prim between 400 and 1000;
delete from Dup_B where prim between 400 and 1000;

insert into Dup_A values (487,'c7');
insert into Dup_B values (487,'7','c7');

refresh MVGroup Dup_MVG1 with  MVLOG cleanup OUTFILE REFRESH.LOG;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV237A;
log LOGMV237A;


CREATE TANDEM_CAT_REQUEST&1 16 3 @catmvs.mvschm.Dup_A@ @0@ @202@ ;
CREATE TANDEM_CAT_REQUEST&1 16 3 @catmvs.mvschm.Dup_B@ @0@ @202@ ;

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"
	, "@RANGE_SIZE"
	, prim, ch
 from table(iud_log_table dup_a)
 where 
 (prim = 487) or ("@OPERATION_TYPE" > 3)
 order by prim desc, "@TS";

prepare select_range_log_a from
 select "@EPOCH"
		, case "@RANGE_TYPE" 
			when 3 then '[...]'
			when 2 then '(...]'
			when 1 then '[...)'
			when 0 then '(...)'
		  end
        , "@BR_PRIM"
        , "@ER_PRIM"
 from table(range_log_table dup_a)
 order by "@EPOCH", "@RANGE_ID", "@BR_PRIM" desc;

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"
	, "@RANGE_SIZE"
	, prim,prim2, ch
 from table(iud_log_table dup_b)
 where
 (prim = 487) or ("@OPERATION_TYPE" > 3)
 order by prim, prim2 desc ,"@TS";

prepare select_range_log_b from
 select "@EPOCH"
		, case "@RANGE_TYPE" 
			when 3 then '[...]'
			when 2 then '(...]'
			when 1 then '[...)'
			when 0 then '(...)'
		  end
        , "@BR_PRIM"
        , "@BR_PRIM2"
        , "@ER_PRIM"
        , "@ER_PRIM2"
 from table(range_log_table dup_b)
 order by "@EPOCH", "@RANGE_ID", "@BR_PRIM", "@BR_PRIM2" desc;

?section TEST1

  Delete from Dup_A
    where ch='c7';

  Delete from Dup_B
    where ch='c7';

  Insert into Dup_A
    select 920 - RunningCount(*) as a,'R1'
    from Range_Factor100 p100,Range_Factor5 p5
    sequence by p100.ts,p5.ts;
	
  Insert into Dup_B
    select RunningCount(*) + 420 as a,'7','R1'
    from Range_Factor100 p100,Range_Factor5 p5
    sequence by p100.ts,p5.ts;

  Delete from Dup_A
	where prim between 400 and 600;

  Delete from Dup_B
	where prim between 400 and 600;

CREATE TANDEM_CAT_REQUEST&1 16 3 @catmvs.mvschm.Dup_A@ @1@ @0@ ;
CREATE TANDEM_CAT_REQUEST&1 16 3 @catmvs.mvschm.Dup_B@ @1@ @0@ ; -- epoch 202

CREATE TANDEM_CAT_REQUEST&1 16 3 @catmvs.mvschm.Dup_A@ @1@ @0@ ;
CREATE TANDEM_CAT_REQUEST&1 16 3 @catmvs.mvschm.Dup_B@ @1@ @0@ ; -- epoch 203

  Insert into Dup_A
    select 547 - RunningCount(*) as a,'R2'
    from Range_Factor100 p100
    sequence by p100.ts;
	
  Insert into Dup_B
    select RunningCount(*) + 447 as a,'7','R2'
    from Range_Factor100 p100
    sequence by p100.ts;

-- IUD LOG 
execute select_iud_log_a;
execute select_iud_log_b;

Refresh MVGroup Dup_MVG3  without  MVLOG cleanup debug 55; -- epoch 204
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK | sort >> LOGMV237A;
log LOGMV237A;
-- IUD LOG 
execute select_iud_log_a;
execute select_iud_log_b;
-- RANGE LOG
execute select_range_log_a;
execute select_range_log_b;

Refresh MVGroup Dup_MVG3  do only  MVLOG cleanup OUTFILE REFRESH.LOG;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV237A;
log LOGMV237A;

-- End Phase 1

  Delete from Dup_A
	where prim between 370 and 530;

  Delete from Dup_B
	where prim between 370 and 530;

CREATE TANDEM_CAT_REQUEST&1 16 3 @catmvs.mvschm.Dup_A@ @1@ @0@ ;
CREATE TANDEM_CAT_REQUEST&1 16 3 @catmvs.mvschm.Dup_B@ @1@ @0@ ; -- epoch 205

  Insert into Dup_A
   Values(487,'I1');

  Insert into Dup_B
   Values(487,'6','I1');

CREATE TANDEM_CAT_REQUEST&1 16 3 @catmvs.mvschm.Dup_A@ @1@ @0@ ;
CREATE TANDEM_CAT_REQUEST&1 16 3 @catmvs.mvschm.Dup_B@ @1@ @0@ ; -- epoch 206

  Delete from Dup_A
	where prim between 370 and 530;

  Delete from Dup_B
	where prim between 370 and 530;

CREATE TANDEM_CAT_REQUEST&1 16 3 @catmvs.mvschm.Dup_A@ @1@ @0@ ;
CREATE TANDEM_CAT_REQUEST&1 16 3 @catmvs.mvschm.Dup_B@ @1@ @0@ ; -- epoch 207


CREATE TANDEM_CAT_REQUEST&1 16 3 @catmvs.mvschm.Dup_A@ @1@ @0@ ;
CREATE TANDEM_CAT_REQUEST&1 16 3 @catmvs.mvschm.Dup_B@ @1@ @0@ ; -- epoch 208

  Insert into Dup_A
    select 500 - 2*RunningCount(*) as a,'R2'
    from Range_Factor10 p10, Range_Factor5 p5
    sequence by p10.ts, p5.ts;
	
  Insert into Dup_B
    select 2*RunningCount(*) + 400 as a,'7','R2'
    from Range_Factor10 p10, Range_Factor5 p5
    sequence by p10.ts, p5.ts;

  Insert into Dup_A
   Values(487,'I2');

  Insert into Dup_B
   Values(487,'6','I2');

-- IUD LOG 
execute select_iud_log_a;
execute select_iud_log_b;

Refresh MVGroup Dup_MVG1 without  MVLOG cleanup debug 55; -- epoch 209
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK | sort >> LOGMV237A;
log LOGMV237A;

-- IUD LOG 
execute select_iud_log_a;
execute select_iud_log_b;
-- RANGE LOG
execute select_range_log_a;
execute select_range_log_b;

-- End Phase 2

-- Correctness check

obey DUPENV(CHECK_DUP_MV3);
log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV237A ;
log LOGMV237A;

obey DUPENV(CHECK_DUP_MV2);
log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV237A ;
log LOGMV237A;

obey DUPENV(CHECK_DUP_MV1);
log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV237A ;
log LOGMV237A;

---------------------------------------
?section CLEAN_UP
---------------------------------------
set schema catmvs.mvschm;
Refresh mvgroup Dup_MVG1 cancel OUTFILE REFRESH.LOG;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV237A;
log LOGMV237A;
obey DUPENV(DROP_SIMPLE);
set schema catmvs.mvschm;
obey CREATERANGE(DROPALL);

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;
