-- @@@ 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 @@@
-------------------------------------------------------------
-- TESTMV334:
-- IGNORE CHANGES tests
-------------------------------------------------------------
set schema catmvs.mvschm;
obey TESTMV334(clean_up);

log LOGMV334 clear;
obey TESTMV334(set_up);

-- Run Tests
  obey TESTMV334(Test1);
  obey TESTMV334(Test2);
  obey TESTMV334(Test3);

-- Clean and Exit
obey TESTMV334(clean_up);
exit;

------------------------------------------------------------
?section Test1 - test IUD log and Range log are dropped
------------------------------------------------------------

set param ?objname T_334_B;
execute st1;

alter mv T_334_MV1 add ignore changes on T_334_B;

execute st1;

alter mv T_334_MV1 remove ignore changes on T_334_B;

execute st1;

------------------------------------------------------------
?section Test2 - 
--
--  MVGROUP is dropped when added to ignore
--	changes list 
--	and added back after table is dropped
--	and remove ignore changes is used
------------------------------------------------------------
set param ?objname T_334_A;

create mv T_334_MV2
        refresh on request
        initialized on refresh
        as
        select T_334_A.a,sum(T_334_B.b) sumb
        from T_334_A,T_334_B
        where T_334_A.a=T_334_B.a
        group by T_334_A.a; 

execute st1;

alter mv T_334_MV2 add ignore changes on T_334_A;
execute st1;

drop mv T_334_MV1;
execute st1;

alter mv T_334_MV2 remove ignore changes on T_334_A;
execute st1;

-- recreate the dropped mv
create mv T_334_MV1
        refresh on request
        initialized on refresh
        as
        select T_334_A.a,sum(T_334_B.b) sumb
        from T_334_A,T_334_B
        where T_334_A.a=T_334_B.a
        group by T_334_A.a;
 
------------------------------------------------------------
?section Test3 - 
--
-- When table is in ignore changes list at mv creation time
-- the mvgroup is not created
------------------------------------------------------------
drop mv T_334_MV1;
drop mv T_334_MV2;

create mv T_334_MV3
 refresh on request
 ignore changes on T_334_B
 initialize on refresh
 as
 select T_334_A.a,sum(T_334_B.b) sumb
        from T_334_A,T_334_B
        where T_334_A.a=T_334_B.a
        group by T_334_A.a;

-- MVGroup T_334_B does not exist
drop mvgroup T_334_B;       

-- MVGroup T_334_A should exist 
drop mvgroup T_334_A;


------------------------------------------------------------
?section set_up
------------------------------------------------------------
create table T_334_A 
(a int not null, b char(2) not null,primary key(a));

create table T_334_B
(a int not null,b numeric (2) not null, c date not null,primary key(a));

create mv T_334_MV1
        refresh on request
        initialized on refresh
        as
        select T_334_A.a,sum(T_334_B.b) sumb
        from T_334_A,T_334_B
        where T_334_A.a=T_334_B.a
        group by T_334_A.a;             
        
prepare st1 from
    select substring(object_name,1,50) obj, object_type ,object_name_space
    from HP_DEFINITION_SCHEMA.objects
    where object_name=upper(?objname) order by 1,3;        

------------------------------------------------------------
?section clean_up
------------------------------------------------------------
drop mv T_334_MV1;
drop mv T_334_MV2;
drop mv T_334_MV3;
drop table T_334_A;
drop table T_334_B;



