-- @@@ 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 @@@
--
-- Testmv850: basic operation of all different IUD operations of ON REQUEST MJV
--

set schema catmvs.mvschm;
obey TESTMV850(clean_up);
log LOGMV850 clear;
obey TESTMV850(set_up);
obey TESTMV850(setup_statements);
obey TESTMV850(test1);
obey TESTMV850(test2);
obey TESTMV850(test3);
obey TESTMV850(test4);
obey TESTMV850(test5);
obey TESTMV850(clean_up);
exit;

?section set_up
create table T850_T1 (
          id int no default not null
	, name char(30) no default not null
	, age smallint no default not null
	, primary key(id)
)
store by (id)
attribute all mvs allowed
;

create table T850_T2 (
	  owner int no default not null
	, car char(30) no default not null
	, year_model smallint no default not null
)
attribute all mvs allowed
;

create materialized view T850_MV1
  refresh on request
  initialize on create
  as select name, car
     from T850_T1 a, T850_T2 b
     where a.id = b.owner;

?section setup_statements
prepare stat1 from
  select name, car
  from T850_MV1
  order by name,car;

prepare stat2 from
  select name, car
  from T850_T1 a, T850_T2 b
  where a.id = b.owner
  order by name,car;

?section clean_up
drop materialized view T850_MV1;
drop table T850_T1;
drop table T850_T2;

?section test1
insert into T850_T1 values (1,'Mickey Mouse',5);

-- expected: MV still empty.
refresh T850_MV1;
obey compare;
sh diff TEMP1 TEMP2 >> LOGMV850;
log LOGMV850;

insert into T850_T2 values (1,'Mustang',1956);

-- expected: Now Mickey has a Mustang
refresh T850_MV1;
obey compare;
sh diff TEMP1 TEMP2 >> LOGMV850;
log LOGMV850;

insert into T850_T2 values (1,'Schevrolet Sunbird',2850);

-- expected: Mickey has both Mustang and Schevrolet Sunbird
refresh T850_MV1;
obey compare;
sh diff TEMP1 TEMP2 >> LOGMV850;
log LOGMV850;

-- Massive updates
insert into T850_T1
values  (2,'Donald',3)
       ,(3,'Bugs Bunny',43)
       ,(4,'Roger Rabbit',21)
       ,(5,'Snow white',178)
       ,(6,'Sinderella',18)
       ,(7,'Homer Simpson',3)
       ;

-- This is just to avoid multi-delta refresh.
--refresh T850_MV1;
--obey compare;
--sh diff TEMP1 TEMP2 >> LOGMV850;
--log LOGMV850;

insert into T850_T2
values  (2,'Mitzubishi',1986)
       ,(3,'Megan',2850)
       ,(4,'Subaru',2000)
       ,(5,'Honda Accord',1988)
       ,(6,'Rolls Roise',1972)
       ,(8,'Lantis',1968)
       ,(8,'Lada',2002)
       ,(3,'Suzuki Swift',1980)
       ,(4,'Opel Astra',1999)
       ,(8,'Ford Fiesta',1980)
       ;

refresh T850_MV1;
obey compare;
sh diff TEMP1 TEMP2 >> LOGMV850;
log LOGMV850;

?section test2
-- direct update of MV ('name' is not part of the join predicate)
update T850_T1 set name = 'Donald Duck'
where id = 2;

-- expected: 'Donald Duck' owns 'Mitzubishi'
refresh T850_MV1;
obey compare;
sh diff TEMP1 TEMP2 >> LOGMV850;
log LOGMV850;

-- direct update of MV ('car' is not part of the join predicate)
update T850_T2 set car = 'Mazda 626'
where owner = 6;

-- expected: 'Sinderella' owns 'Mazda 626'
refresh T850_MV1;
obey compare;
sh diff TEMP1 TEMP2 >> LOGMV850;
log LOGMV850;

-- direct update of MV. Only 'car' column should be updated in MV.
-- 'year' column is not in use (irelevant).
update T850_T2 set car = 'Rover', year_model = 2850
where owner = 4;

-- expected: 'Roger Rabbit' owns 2 'Rover' cars
refresh T850_MV1;
obey compare;
sh diff TEMP1 TEMP2 >> LOGMV850;
log LOGMV850;

?section test3
-- indirect update. owner is part of the join predicate.
update T850_T2 set owner = 7
where owner = 8 and year_model = 1980;

-- expected: 'Homer Simpson' owns now 'Ford Fiesta'
refresh T850_MV1;
obey compare;
sh diff TEMP1 TEMP2 >> LOGMV850;
log LOGMV850;

?section test4
-- irrelevant update.

update T850_T2 set year_model = 2002
  where owner = 8 and car = 'Lantis';

--expected: 0
refresh T850_MV1;
obey compare;
sh diff TEMP1 TEMP2 >> LOGMV850;
log LOGMV850;

?section test5
-- delete records from base tables. MV updated accordingly.
delete from T850_T2 where owner = 3 and car = 'Suzuki Swift';

-- expected: 'Bugs Bunny' sold his 'Suzuki Swift' (doesn't own it anymore)
refresh T850_MV1;
obey compare;
sh diff TEMP1 TEMP2 >> LOGMV850;
log LOGMV850;

?section dev.null
set schema catmvs.mvschm;
set PARSERFLAGS 3;

INTERNAL REFRESH CATMVS.MVSCHM.T850_MV1
  FROM MULTIDELTA
        CATMVS.MVSCHM.T850_T1 BETWEEN 104 AND 104 DE LEVEL 3
         USE NO RANGELOG
        USE IUDLOG
        6 ROWS_INSERTED
        0 ROWS_DELETED
        0 ROWS_UPDATED ,
        CATMVS.MVSCHM.T850_T2 BETWEEN 104 AND 104 DE LEVEL 3
         USE NO RANGELOG
        USE IUDLOG
        10 ROWS_INSERTED
        0 ROWS_DELETED
        0 ROWS_UPDATED
        PHASE 0;

INTERNAL REFRESH CATMVS.MVSCHM.T850_MV1
  FROM SINGLEDELTA
        CATMVS.MVSCHM.T850_T2 BETWEEN 106 AND 106 DE LEVEL 3
         USE NO RANGELOG
        USE IUDLOG
        0 ROWS_INSERTED
        0 ROWS_DELETED
        2 ROWS_UPDATED COLUMNS (2);
