-- @@@ 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 @@@
------------------------------------------------------
--- TestMV612
--- NOMVLOG situations.
------------------------------------------------------

set schema catmvs.mvschm;
obey TESTMV612(CLEAN_UP);

log LOGMV612 clear;

obey TESTMV612(SET_UP);

-- Run Tests

obey TESTMV612(TEST1);
obey TESTMV612(TEST2);
obey TESTMV612(TEST3);
obey TESTMV612(TEST4);
obey TESTMV612(TEST5);

obey TESTMV612(CLEAN_UP);

-- Clean and exit
log;
exit;

--=================================================================================
?section CLEAN_UP
drop mv    T_612_MV3;
drop mv    T_612_MV2;
drop mv    T_612_MV1;
drop table T_612_A;
drop table T_612_B;

set param ?schema_name 'MVSCHM';

obey COMPARE_NUM_OF_OBJ;

drop table Num_Obj;


--=================================================================================
?section SET_UP
--=================================================================================

set PARSERFLAGS 3;

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 table T_612_A (a int, b int);
ALTER TABLE T_612_A attribute all mvs allowed;

create mv T_612_MV1 
	refresh on request
	initialize on create
	as  select b, sum(a) sum_a 
		from T_612_A 
		group by b;
ALTER MV T_612_MV1 attribute all mvs allowed;

create mv T_612_MV2
	refresh on request 
	initialize on create
	as  select b, sum(sum_a) sum_sum_a 
		from T_612_MV1
		group by b;
ALTER MV T_612_MV2 attribute all mvs allowed;

--=================================================================================
?section TEST1
-- Explicit NOMVLOG use
--=================================================================================

-- Verify normal logging.
insert into T_612_A values (1,2);
-- Should show 1 row only.
select count(*) from table (iud_log_table T_612_A);

-- Do some NOMVLOG operations
insert NOMVLOG into T_612_A values (3,4);
update NOMVLOG T_612_A set b=b+2;
delete NOMVLOG from T_612_A;
-- Should still show one row.
select count(*) from table (iud_log_table T_612_A);

--=================================================================================
?section TEST2
-- Recompute
--=================================================================================

delete from table (iud_log_table T_612_A);

internal refresh T_612_MV1 recompute;

-- Should be empty.
select count(*) from table (iud_log_table T_612_MV1);

--=================================================================================
?section TEST3
-- Delete from MV
--=================================================================================

delete from T_612_MV1;

-- Should be empty.
select count(*) from table (iud_log_table T_612_MV1);

--=================================================================================
?section TEST4
-- Pipelined refresh
--=================================================================================

delete NOMVLOG from T_612_A;
-- All tables/mvs are empty now

CREATE TANDEM_CAT_REQUEST&1 16 3 @catmvs.mvschm.t_612_a@ @0@ @200@ ;
CREATE TANDEM_CAT_REQUEST&1 16 3 @catmvs.mvschm.t_612_mv1@ @0@ @200@ ;
CREATE TANDEM_CAT_REQUEST&1 16 3 @catmvs.mvschm.t_612_mv2@ @0@ @200@ ;

-- Do some inserts
insert into T_612_A values (1,1), (2,2), (3,3), (4,4), (5,5);

CREATE TANDEM_CAT_REQUEST&1 16 3 @catmvs.mvschm.t_612_a@ @1@ @0@ ;
CREATE TANDEM_CAT_REQUEST&1 16 3 @catmvs.mvschm.t_612_mv1@ @1@ @0@ ;
CREATE TANDEM_CAT_REQUEST&1 16 3 @catmvs.mvschm.t_612_mv2@ @1@ @0@ ;

internal refresh T_612_MV1
	from singledelta T_612_A between 200 and 200 de level 0 use no rangelog use iudlog
	pipeline (T_612_MV2);

select * from T_612_MV1;
select * from T_612_MV2;

-- Should be empty.
select count(*) from table (iud_log_table T_612_MV1);

-- Now check updates and deletes
update T_612_A set a=a+10 where b<3;
delete from T_612_A where b>3;

CREATE TANDEM_CAT_REQUEST&1 16 3 @catmvs.mvschm.t_612_a@ @1@ @0@ ;
CREATE TANDEM_CAT_REQUEST&1 16 3 @catmvs.mvschm.t_612_mv1@ @1@ @0@ ;
CREATE TANDEM_CAT_REQUEST&1 16 3 @catmvs.mvschm.t_612_mv2@ @1@ @0@ ;

internal refresh T_612_MV1
	from singledelta T_612_A between 201 and 201 de level 0 use no rangelog use iudlog
	pipeline (T_612_MV2);

select * from T_612_MV1;
select * from T_612_MV2;

-- Should be empty.
select count(*) from table (iud_log_table T_612_MV1);

--=================================================================================
?section TEST5
-- INSERTLOG tables
--=================================================================================

create table T_612_B (a int, b int)
	attribute insertlog;
ALTER TABLE T_612_B attribute all mvs allowed;

create mv T_612_MV3 
	refresh on request 
	initialize on create
	as  select b, sum(a) sum_a 
		from T_612_B 
		group by b;
ALTER MV T_612_MV3 attribute all mvs allowed;

-- Do some operations on T_612_B
insert into T_612_B values (1,2);
-- The insert should be logged.
select count(*) from table (iud_log_table T_612_B);

update T_612_B set b=b+2;
delete from T_612_B;
-- The update and delete should not be logged.
select count(*) from table (iud_log_table T_612_B);

-- Do some NOMVLOG operations - should not be logged.
insert NOMVLOG into T_612_B values (3,4);
update NOMVLOG T_612_B set b=b+2;
delete NOMVLOG from T_612_B;
-- The update and delete should not be logged.
select count(*) from table (iud_log_table T_612_B);
