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

set schema catmvs.mvschm;
set PARSERFLAGS 3;

obey TESTMV231A(CLEAN_UP);

log LOGMV231A clear;

obey TESTMV231A(SET_UP);
-- Run Tests
obey TESTMV231A(TEST1);

 obey TESTMV231A(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');

--Neutralize the VSBB inserts
control query default insert_vsbb 'OFF';
control query default pos 'off';

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_DUP_MV1);

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


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"
	, prim
 from table(iud_log_table dup_a)
 order by prim desc, "@TS";

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, prim2
 from table(iud_log_table dup_b)
 order by prim asc, prim2 desc, "@TS";

--------------------------------------
?section CLEAN_UP
--------------------------------------
Refresh mvgroup Dup_MVG1 cancel OUTFILE REFRESH.LOG;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV231A;
log LOGMV231A;
obey DUPENV(DROP_SIMPLE);

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 TEST1
--------------------------------------------------------
-- Test 1, phase 1
--------------------------------------------------------


  Insert into Dup_A
   Values(1000,'I1');
  
  Delete from Dup_A
   where ch='I1';

  Insert into Dup_B
   Values(1000,'1','I1');

  Delete from Dup_B
   where ch='I1';

  Delete from Dup_A
   where prim=2000;

  Delete from Dup_B
   where prim=2000;

-- IUD LOG 
execute select_iud_log_a;

-- During recompute DE is not performed
-- DUP_A - IUD-log must remain unchanged
Refresh Dup_MV3 recompute without  MVLOG cleanup OUTFILE REFRESH.LOG; 
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV231A;
log LOGMV231A;

-- During recompute DE is not performed
-- DUP_B - IUD-log must remain unchanged
Refresh Dup_MV2 recompute without  MVLOG cleanup OUTFILE REFRESH.LOG; 

log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK | sort >> LOGMV231A;
log LOGMV231A;

-- Dup_MV3 has passed epochs 200-202
-- DUP_A - IUD-log must remain unchanged
Refresh Dup_MV3 without  MVLOG cleanup OUTFILE REFRESH.LOG;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV231A;
log LOGMV231A;
-- Dup_MV2 has passed epochs 200-202
-- DUP_B - IUD-log must remain unchanged
Refresh Dup_MV2 without  MVLOG cleanup OUTFILE REFRESH.LOG;

log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK | sort >> LOGMV231A;
log LOGMV231A;

-- IUD LOG 
execute select_iud_log_a;
execute select_iud_log_b;

Insert into Dup_A
  Values(2000,'XX');

Insert into Dup_B
  Values(2000, '2', 'XX');

Refresh  Dup_MV1 without  MVLOG cleanup debug 55; -- epoch 204

log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK | sort >> LOGMV231A;
log LOGMV231A;

-- IUD LOG 
-- Duplicate elimination must be performed on the logs
-- of DUP_A and DUP_B, between epochs 200-204
execute select_iud_log_a;
execute select_iud_log_b;

--------------------------------------------------------
-- Test 1, phase 2
--------------------------------------------------------

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

  Insert into Dup_B
   Values(1000,'1','I2');

  Delete from Dup_A
   where prim=2000;

  Delete from Dup_B
   where prim=2000;

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

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

  UPDATE Dup_A
  SET ch = 'U1'
  WHERE prim=1000;

  UPDATE Dup_B
  SET ch = 'U1'
  WHERE prim=1000;

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

  Delete from Dup_A
   where prim=2000;

  Insert into Dup_B
   Values(2000, '2', 'I2');

  Delete from Dup_B
   where prim=2000;

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

 Insert into Dup_A
  Values(2000,'I3');

 Insert into Dup_B
  Values(2000,'2', 'I3');

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

  Delete from Dup_A
   where ch='U1';

  Delete from Dup_B
   where ch='U1';

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 209

  Insert into Dup_A
   Values(1000,'I4');

  Delete from Dup_B
   where prim=2000;

  Insert into Dup_B
   Values(1000,'1','I4');

  Delete from Dup_A
   where prim=2000;

-- IUD LOG 
execute select_iud_log_a;
execute select_iud_log_b;

-- Refresh Dup_MV2 and Dup_MV3 

Refresh MVGroup Dup_MVG3 without  MVLOG cleanup debug 55; -- epoch 210
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK | sort >> LOGMV231A;
log LOGMV231A;

-- IUD LOG 

-- The duplicate elimination must sweep the epochs 200-210
-- in the IUD logs of DUP_A and DUP_B
execute select_iud_log_a;
execute select_iud_log_b;

Refresh Dup_MV1 without  MVLOG cleanup debug 55; -- epoch 211
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK |sort >> LOGMV231A;
log LOGMV231A;

-- IUD LOG 
-- The duplicate elimination must sweep the epochs 205-210
-- in the IUD logs of DUP_A and DUP_B
execute select_iud_log_a;
execute select_iud_log_b;

-- Correctness check

obey DUPENV(CHECK_DUP_MV3);

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

obey DUPENV(CHECK_DUP_MV2);

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

obey DUPENV(CHECK_DUP_MV1);

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