-- @@@ 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 @@@
-- @@@ START COPYRIGHT @@@
--
------------------------------------------------------
--- TestMV215IR
--- 2.4.4.
--- PURPOSE: Internal Refresh tests with TPCD database         
------------------------------------------------------
-- **** Change all TestMV215IR names to TestMV215 ****


obey TESTMV215IR(CLEAN_UP);

log LOGMV215IR clear;

obey TESTMV215IR(SET_UP);

-- Run Tests
	set schema cat.tpcd;
	obey TESTMV215IR(TEST1);
	obey TESTMV215IR(TEST2);
  obey TESTMV215IR(TEST3);
    obey TESTMV215IR(TEST4);
--  obey TESTMV215IR(TEST5); -- Bug 276
--  obey TESTMV215IR(TEST6); -- Bug 276
    obey TESTMV215IR(TEST7);
    obey TESTMV215IR(TEST8);
	  obey TESTMV215IR(TEST9);
    obey TESTMV215IR(TEST10);
-- Clean and exit

obey TESTMV215IR(CLEAN_UP);
log LOGMV215IR;
-- The objects that were left in the system:
obey ./TPCD/SHOW_OBJECTS_TPCD;
log;
exit;

-------------------------------------------------------
?section SET_UP
-------------------------------------------------------
control query default insert_vsbb 'OFF';
set PARSERFLAGS 1;
set schema cat.tpcd;

obey ./TPCD/CREATEALL.OBEY(SET_UP);

log LOGMV215IR;

-- insert only selected data
delete from lineitem;
delete from supplier;
delete from orders;
delete from part;
delete from partsupp;
delete from customer;
delete from nation;
delete from region;

cd tpcd/tpcdtemp;
sh import cat.tpcd.nation -I nation.tbl -U nation.fmt;
sh import cat.tpcd.region -I region.tbl -U region.fmt;
sh import cat.tpcd.supplier -I supplier.tbl -U supplier.fmt -r -C 30;
sh import cat.tpcd.lineitem -I lineitem.tbl -U lineitem.fmt -r -C 17000;
sh import cat.tpcd.orders -I orders.tbl -U orders.fmt -r -C 4500;
sh import cat.tpcd.part -I part.tbl -U part.fmt -r -C 600;
sh import cat.tpcd.partsupp -I partsupp.tbl -U partsupp.fmt -r -C 2400;
sh import cat.tpcd.customer -I customer.tbl -U customer.fmt -r -C 450;
cd ../..;

obey PREPARE_DDL_SELECTS;

-----------------------------------------------------
?section CLEAN_UP
-----------------------------------------------------

set schema cat.tpcd;
obey ./TPCD/CREATEMVS(DROPALL);
drop table cat3.details.partsupp;
drop table cat3.details.supplier;
drop table cat3.geography.nation;
drop schema cat3.details;
drop schema cat3.geography;
drop schema cat3.testing;
drop catalog cat3;
obey ./TPCD/CREATEALL.OBEY(CLEAN_UP);

-- ---------------------------------------------------------------------------
?section TEST1
-----------------------------------------------------
log LOGMV215IR;

obey ./TPCD/CREATEMVS(MV5);

-- Basic Q5
-- for compare 
PREPARE stat1 FROM 
	select n_name, sum(l_extendedprice*(1-l_discount)) as revenue,count(*) rows_in_group
	from customer,orders,lineitem,supplier,nation, region
	where
	    c_custkey = o_custkey
	    and o_orderkey = l_orderkey
	    and l_suppkey = s_suppkey
	    and c_nationkey= s_nationkey
	    and s_nationkey = n_nationkey
	    and n_regionkey = r_regionkey
	    and r_name in ('ASIA','AMERICA','EUROPE','MIDDLE EAST','AFRICA')
	    and o_orderdate >= date '1994-01-01'
	    and o_orderdate < date '1994-01-01' + interval '3' year
	group by n_name
	order by n_name,revenue desc ;

PREPARE stat2 FROM 
	select n_name,revenue,rows_in_group
	from mv4q5 
	order by n_name,revenue desc;

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

CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.supplier@ @0@ @10@ ;
SET PARAM ?table_name 'SUPPLIER';
execute select_mvs_table_info;
log;
sh import cat.tpcd.supplier -I ./tpcd/tpcdtemp/supplier.tbl -U ./tpcd/tpcdtemp/supplier.fmt -r -F 30 -C 5 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215IR;
log LOGMV215IR;
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.supplier@ @1@ @0@ ;

-- should be equal to 5
select count(*) from table(iud_log_table cat.tpcd.supplier);

internal refresh mv4q5 
        from singleDelta supplier
		between 10 and 10 no de insert only;

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

CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.customer@ @0@ @10@ ;
log;
sh import cat.tpcd.customer -I ./tpcd/tpcdtemp/customer.tbl -U ./tpcd/tpcdtemp/customer.fmt -r -F 450 -C 75 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215IR;
log LOGMV215IR;

update customer
set c_nationkey = 12 -- 'JAPAN'
where c_custkey in (
	select c_custkey
	from customer,nation
	where c_nationkey = n_nationkey 
	      and c_acctbal < 4000
	      and n_name = 'IRAN'
	);

CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.customer@ @1@ @0@ ;	
update customer
set c_nationkey = 10 -- 'IRAN'
where c_custkey in (
	select c_custkey
	from customer,nation
	where c_nationkey = n_nationkey 
	      and c_acctbal > 3000
	      and n_name = 'JAPAN'
	);

update customer
set c_nationkey = 12 -- 'IRAN'
where c_custkey in (
	select c_custkey
	from customer,nation
	where c_nationkey = n_nationkey 
	      and c_acctbal < 2500
	      and n_name = 'JAPAN'
	);

--select count(*),c_custkey 
--from table(iud_log_table customer) 
--group by c_custkey
--order by c_custkey;
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.customer@ @1@ @0@;
internal refresh mv4q5 
        from singleDelta customer
		between 10 and 11 no de;
log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215IR ;
delete from table (iud_log_table cat.tpcd.customer);
log LOGMV215IR;

-------------------------------------------------------------
?section TEST2
-------------------------------------------------------------

obey ./TPCD/CREATEMVS(MV9);
-- Basic Q9
PREPARE stat1 FROM 
	select nation, yr, sum(amount) as sum_profit
	from (select
         	n_name as nation, 
         	extract(year from o_orderdate) as yr,
         	((l_extendedprice*(1-l_discount)) - (ps_supplycost*l_quantity)) as amount
     from part,supplier,lineitem,partsupp,orders, nation
     where 
         s_suppkey  = l_suppkey
         and ps_suppkey = l_suppkey
         and ps_partkey = l_partkey
         and p_partkey  = l_partkey
         and o_orderkey = l_orderkey
         and s_nationkey = n_nationkey
         and p_name like '%green%'
    ) as profit
	group by nation, yr
	order by nation, yr;

PREPARE stat2 FROM 
	select nation, yr,sum_profit
	from mv4q9 
	order by nation, yr;

CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.supplier@ @0@ @20@ ;
internal refresh mv4q9 
        from singleDelta supplier
		between 20 and 20 no de;

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

--parts
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.part@ @0@ @20@ ;
log;
sh import cat.tpcd.part -I ./tpcd/tpcdtemp/part.tbl -U ./tpcd/tpcdtemp/part.fmt -r -F 600 -C 100 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215IR;
log LOGMV215IR;

CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.part@ @1@ @0@ ;

internal refresh mv4q9 
        from singleDelta part
		between 20 and 20 no de;

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

-- partsupp
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.partsupp@ @0@ @20@ ;
log;
sh import cat.tpcd.partsupp -I ./tpcd/tpcdtemp/partsupp.tbl -U ./tpcd/tpcdtemp/partsupp.fmt -r -F 2400 -C 400 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215IR;
log LOGMV215IR;

CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.partsupp@ @1@ @0@ ;

internal refresh mv4q9 
        from singleDelta partsupp
		between 20 and 20 no de;

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

-- lineitem
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.lineitem@ @0@ @20@ ;
log;
sh import cat.tpcd.lineitem -I ./tpcd/tpcdtemp/lineitem.tbl -U ./tpcd/tpcdtemp/lineitem.fmt -r -F 17000 -C 2500 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215IR;
log LOGMV215IR;

CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.lineitem@ @1@ @0@ ;

internal refresh mv4q9 from
        singleDelta lineitem
		between 20 and 20 no de;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215IR;
delete from table (iud_log_table cat.tpcd.part);
delete from table (iud_log_table cat.tpcd.partsupp);
delete from table (iud_log_table cat.tpcd.lineitem);
log LOGMV215IR;

----------------------------------------------------------
?section TEST3
----------------------------------------------------------

obey ./TPCD/CREATEMVS(MV1);
-- Basic Q1
-- for compare
PREPARE stat1 FROM
    select
        l_returnflag,
        l_linestatus,
        sum(l_quantity) as sum_qty,
        sum(l_extendedprice) as sum_base_price,
        sum( l_extendedprice * (1-l_discount)) as sum_disc_price,
        sum(l_extendedprice * (1-l_discount) * (1 + l_tax) )as sum_charge,
        avg(l_quantity) as avg_qty,
        avg(l_extendedprice) as avg_price,
        avg(l_discount) as avg_disc,
        count(*) as count_order
    from lineitem
    where 
        l_shipdate <= date '1998-12-01' - interval '90' day
    group by l_returnflag, l_linestatus
    order by l_returnflag, l_linestatus;

PREPARE stat2 FROM
    select l_returnflag, l_linestatus, sum_qty, sum_base_price, sum_disc_price,
           sum_charge, avg_qty, avg_price, avg_disc, count_order
    from mv4q1
    order by l_returnflag, l_linestatus;

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

-- insert delta
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.lineitem@ @0@ @30@ ;
log;
sh import cat.tpcd.lineitem -I ./tpcd/tpcdtemp/lineitem.tbl -U ./tpcd/tpcdtemp/lineitem.fmt -r -F 19500 -C 500 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215IR;
log LOGMV215IR;

internal refresh mv4q1
        from singledelta lineitem
		between 30 and 30 no de insert only;

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

CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.lineitem@ @1@ @0@ ;

update lineitem
set l_quantity = 1000
where l_partkey between 30.00 and 39.00;

internal refresh mv4q1
        from singledelta lineitem
		between 31 and 31 no de;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215IR ;
delete from table (iud_log_table cat.tpcd.lineitem);
log LOGMV215IR;

--------------------------------------------------------------
?section TEST4
--------------------------------------------------------------
-- Irefresh from multidelta
obey ./TPCD/CREATEMVS(MV3);
-- Basic Q3
-- for compare
PREPARE stat1 FROM
    select
        l_orderkey, 
        cast(sum(l_extendedprice*(1-l_discount)) as numeric(18,2)) as revenue,
        o_orderdate, o_shippriority
    from customer,orders,lineitem
    where
        c_mktsegment = 'BUILDING'
        and c_custkey = o_custkey
        and l_orderkey = o_orderkey
        and o_orderdate < date '1995-03-15'
        and l_shipdate >  date '1995-03-15'
    group by l_orderkey, o_orderdate, o_shippriority
    order by revenue desc,o_orderdate;

PREPARE stat2 FROM
    select l_orderkey, revenue, o_orderdate, o_shippriority
    from mv4q3
    order by revenue desc, o_orderdate;        

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

-- iud deltas
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.lineitem@ @0@ @40@ ;
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.orders@ @0@ @40@ ;
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.customer@ @0@ @40@ ;
log;
sh import cat.tpcd.lineitem -I ./tpcd/tpcdtemp/lineitem.tbl -U ./tpcd/tpcdtemp/lineitem.fmt -r -F 20000 -C 2500 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215IR;
sh import cat.tpcd.orders -I ./tpcd/tpcdtemp/orders.tbl -U ./tpcd/tpcdtemp/orders.fmt -r -F 4500 -C 2500 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215IR;
sh import cat.tpcd.customer -I ./tpcd/tpcdtemp/customer.tbl -U ./tpcd/tpcdtemp/customer.fmt -r -F 525 -C 75 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215IR;
log LOGMV215IR;

update lineitem
set l_comment = 'broken'
where l_suppkey > 30 and l_suppkey < 40;

update customer
set c_comment = 'good'
where c_mktsegment = 'AUTOMOBILE';

CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.lineitem@ @1@ @0@ ;
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.orders@ @1@ @0@ ;
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.customer@ @1@ @0@ ;

internal refresh mv4q3 from
        multidelta lineitem between 40 and 40 no de,
                   orders   between 40 and 40 no de,
                   customer between 40 and 40 no de
        phase 0;

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

delete from table (iud_log_table cat.tpcd.orders);
delete from table (iud_log_table cat.tpcd.customer);
delete from table (iud_log_table cat.tpcd.lineitem);
log LOGMV215IR;

----------------------------------------------------
?section TEST5
----------------------------------------------------

obey ./TPCD/CREATEMVS(MV6);
-- Basic Q6
-- for compare
PREPARE stat1 FROM
    select
       l_shipdate,
       cast(sum(l_extendedprice*l_discount)as numeric(18,2)) as revenue
    from lineitem
    where 
        l_shipdate >= date '1994-01-01'
        and l_shipdate < date '1994-01-01' + interval '1' year
        and l_discount between .06 - 0.01 and .06 + 0.01
        and l_quantity < 24
    group by l_shipdate
    order by revenue;

PREPARE stat2 FROM
    select l_shipdate, revenue
    from mv4q6
    order by revenue;

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

CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.lineitem@ @0@ @50@ ;
log;
sh import cat.tpcd.lineitem -I ./tpcd/tpcdtemp/lineitem.tbl -U ./tpcd/tpcdtemp/lineitem.fmt -r -F 22500 -C 2500 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215IR;
log LOGMV215IR;

delete from lineitem where l_partkey < 10;
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.lineitem@ @1@ @0@ ;

--refresh mv4q6
--internal refresh mv4q6 from
--        singleDelta lineitem
--		between 50 and 50 no de;

  INTERNAL REFRESH CAT.TPCD.MV4Q6
 FROM SINGLEDELTA 
	CAT.TPCD.LINEITEM BETWEEN 42 AND 51 NO DE ;
log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215IR;
delete from table (iud_log_table cat.tpcd.lineitem);
log LOGMV215IR;

----------------------------------------------------
?section TEST6
----------------------------------------------------

obey ./TPCD/CREATEMVS(MV10);
-- Basic Q10
-- for compare
PREPARE stat1 FROM
    select 
        c_custkey, c_name,
        cast (sum(l_extendedprice*(1-l_discount)) 
        as numeric(18,2)) as revenue,
        c_acctbal,  n_name, c_address, c_phone, c_comment
    from customer,orders,lineitem, nation
    where 
        c_custkey = o_custkey
        and l_orderkey = o_orderkey
        and o_orderdate >= date '1993-10-01'
        and o_orderdate < date '1993-10-01' + interval '3' month
        and l_returnflag = 'R'
        and c_nationkey = n_nationkey
    group by c_custkey, c_name, c_acctbal, c_phone, n_name,
        c_address, c_comment
    order by revenue desc;

PREPARE stat2 FROM
    select c_custkey, c_name, revenue, c_acctbal, n_name, c_address, c_phone, c_comment
    from mv4q10
    order by revenue desc;

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

-- customer
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.customer@ @0@ @60@ ;
log;
sh import cat.tpcd.customer -I ./tpcd/tpcdtemp/customer.tbl -U ./tpcd/tpcdtemp/customer.fmt -r -F 600 -C 75 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215IR;
log LOGMV215IR;

update customer
set c_nationkey = 12 -- 'JAPAN'
where c_custkey in (
	select c_custkey
	from customer,nation
	where c_nationkey = n_nationkey 
	      and c_acctbal < 4000
	      and n_name = 'IRAN'
	);

CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.customer@ @1@ @0@ ;

internal refresh mv4q10 from
        singleDelta customer
		between 60 and 60 no de;

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

-- orders
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.orders@ @0@ @60@ ;
log;
sh import cat.tpcd.orders -I ./tpcd/tpcdtemp/orders.tbl -U ./tpcd/tpcdtemp/orders.fmt -r -F 7000 -C 1000 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215IR;
log LOGMV215IR;

delete from orders where (o_orderkey between 4 and 15) or (o_orderkey between 20 and 28);

CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.orders@ @1@ @0@ ;

internal refresh mv4q10 from
        singleDelta orders
		between 60 and 60 no de;

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

-- lineitem
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.lineitem@ @0@ @60@ ;
log;
sh import cat.tpcd.lineitem -I ./tpcd/tpcdtemp/lineitem.tbl -U ./tpcd/tpcdtemp/lineitem.fmt -r -F 25000 -C 2500 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215IR;
log LOGMV215IR;

update lineitem
set l_quantity = 1050
where l_partkey = 30.00 or l_partkey = 35.00;

CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.lineitem@ @1@ @0@ ;

internal refresh mv4q10 from
        singleDelta lineitem
		between 60 and 60 no de;

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

-- nation
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.nation@ @0@ @60@ ;
insert into nation values (40, 1, 'ISRAEL', ''), (41, 3, 'PERU', 'no');
delete from nation where n_name = 'ISRAEL';
insert into nation values (40, 1, 'ISRAEL', 'OK');
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.nation@ @1@ @0@ ;

internal refresh mv4q10 from
        singleDelta nation
		between 60 and 60 no de;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215IR;
delete from table (iud_log_table cat.tpcd.orders);
delete from table (iud_log_table cat.tpcd.customer);
delete from table (iud_log_table cat.tpcd.lineitem);
delete from table (iud_log_table cat.tpcd.nation);
log LOGMV215IR;

----------------------------------------------------
?section TEST7
----------------------------------------------------
-- Irefresh from multidelta, one of them is self-cancelling
obey ./TPCD/CREATEMVS(MV11);
-- Basic Q11
-- for compare
PREPARE stat1 FROM
    select
        ps_partkey, sum(ps_supplycost*ps_availqty) as pvalue
    from partsupp,supplier,nation
    where ps_suppkey = s_suppkey
        and s_nationkey = n_nationkey
        and n_name = 'GERMANY'
    group by ps_partkey
    order by pvalue desc;

PREPARE stat2 FROM
    select 
        ps_partkey, pvalue
    from mv4q11
    order by pvalue desc;

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

-- iud deltas
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.partsupp@ @0@ @70@ ;
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.supplier@ @0@ @70@ ;
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.nation@ @0@ @70@ ;
log;
sh import cat.tpcd.partsupp -I ./tpcd/tpcdtemp/partsupp.tbl -U ./tpcd/tpcdtemp/partsupp.fmt -r -F 2800 -C 400 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215IR;
sh import cat.tpcd.supplier -I ./tpcd/tpcdtemp/supplier.tbl -U ./tpcd/tpcdtemp/supplier.fmt -r -F 35 -C 10 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215IR;
log LOGMV215IR;

update partsupp
set ps_comment = 'not available'
where ps_partkey between 52 and 57;

-- nation ----> self cancelling delta
insert into nation values (45, 2, 'BOLIVIA', 'yes'), (46, 3, 'ICELAND', '');
delete from nation where n_name = 'ICELAND';
insert into nation values (87, 3, 'HOLLAND','yes');
delete from nation where n_name = 'HOLLAND' or n_name = 'BOLIVIA';

CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.partsupp@ @1@ @0@ ;
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.supplier@ @1@ @0@ ;
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.nation@ @1@ @0@ ;

internal refresh mv4q11 from
        multidelta partsupp between 70 and 70 no de,
                   supplier between 70 and 70 no de,
                   nation   between 70 and 70 no de
        phase 0;

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

delete from table (iud_log_table cat.tpcd.supplier);
delete from table (iud_log_table cat.tpcd.partsupp);
delete from table (iud_log_table cat.tpcd.nation);
log LOGMV215IR;

-------------------------------------------------------
?section TEST8
-------------------------------------------------------
-- Irefresh from multidelta
obey ./TPCD/CREATEMVS(MV12);
-- Basic Q12
-- for compare
PREPARE stat1 FROM
    select
        l_shipmode, 
        sum (case when o_orderpriority ='1-URGENT'
        or o_orderpriority ='2-HIGH'
        then 1 else 0 end) as high_line_count,
        sum (case when o_orderpriority <> '1-URGENT'
        and o_orderpriority <> '2-HIGH'
        then 1 else 0 end) as low_line_count
    from orders,lineitem
    where 
        o_orderkey = l_orderkey
        and l_shipmode in ('MAIL','SHIP')
        and l_commitdate < l_receiptdate
        and l_shipdate < l_commitdate
        and l_receiptdate >= date '1994-01-01'
        and l_receiptdate < date '1994-01-01' + interval '1' year
    group by l_shipmode
    order by l_shipmode;

PREPARE stat2 FROM
    select
        l_shipmode, high_line_count, low_line_count
    from mv4q12
    order by l_shipmode;

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

-- iud deltas
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.orders@ @0@ @80@ ;
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.lineitem@ @0@ @80@ ;
log;
sh import cat.tpcd.orders -I ./tpcd/tpcdtemp/orders.tbl -U ./tpcd/tpcdtemp/orders.fmt -r -F 8000 -C 1000 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215IR;
sh import cat.tpcd.lineitem -I ./tpcd/tpcdtemp/lineitem.tbl -U ./tpcd/tpcdtemp/lineitem.fmt -r -F 27500 -C 2500 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215IR;
log LOGMV215IR;

update orders
set o_totalprice = 1099.00 where o_custkey < 100;

CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.orders@ @1@ @0@ ;
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.lineitem@ @1@ @0@ ;

internal refresh mv4q12 from
        multidelta orders   between 80 and 80 no de,
                   lineitem between 80 and 80 no de
        phase 0;

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

delete from table (iud_log_table cat.tpcd.orders);
delete from table (iud_log_table cat.tpcd.lineitem);
log LOGMV215IR;

-------------------------------------------------------
?section TEST9
-------------------------------------------------------

obey ./TPCD/CREATEMVS(MV15);
-- Basic Q15
-- for compare
PREPARE stat1 FROM
    select
        l_suppkey , sum(l_extendedprice * (1-l_discount)) as total
    from lineitem
    where
        l_shipdate >= date '1996-01-01'
        and l_shipdate < date '1996-01-01' + interval '3' month
    group by l_suppkey
    order by l_suppkey;

PREPARE stat2 FROM
    select l_suppkey, total
    from mv4q15
    order by l_suppkey;

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

-- lineitem
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.lineitem@ @0@ @90@ ;
log;
sh import cat.tpcd.lineitem -I ./tpcd/tpcdtemp/lineitem.tbl -U ./tpcd/tpcdtemp/lineitem.fmt -r -F 30000 -C 2500 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215IR;
log LOGMV215IR;
delete from lineitem where l_partkey < 100;

CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.lineitem@ @1@ @0@ ;

internal refresh mv4q15 from
        singleDelta lineitem
		between 90 and 90 no de;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215IR;
delete from table (iud_log_table cat.tpcd.lineitem);
log LOGMV215IR;

-------------------------------------------------------
?section TEST10
-------------------------------------------------------
-- This test checks MV that is based on tables from different schemas

-- Creating new schemas
create catalog cat3;
create schema cat3.details;
create schema cat3.geography;
create schema cat3.testing;
-- Creating new tables
set schema cat3.details;
Create table partsupp  (
   ps_partkey          int                not null not droppable, 
   ps_suppkey          int                not null not droppable, 
   ps_availqty         int                not null not droppable, 
   ps_supplycost       numeric(12,2)      not null not droppable, 
   ps_comment          varchar(199)       not null not droppable, 
primary key (ps_partkey,ps_suppkey) not droppable) 
store by primary key 
location $$partition1$$
partition ( 
   add first key (          1001) location $$partition2$$);

Create table supplier  (
   s_suppkey           int                not null not droppable, 
   s_nationkey         int                not null not droppable, 
   s_acctbal           numeric(12,2)      not null not droppable, 
   s_phone             char(15)           not null not droppable, 
   s_name              char(25)           not null not droppable, 
   s_address           varchar(40)        not null not droppable, 
   s_comment           varchar(101)       not null not droppable, 
primary key (s_suppkey) not droppable) 
store by primary key 
location $$partition1$$
partition ( 
   add first key (            501) location $$partition2$$);

set schema cat3.geography;

Create table nation  (
   n_nationkey         int                not null not droppable, 
   n_regionkey         int                not null not droppable, 
   n_name              char(25)           not null not droppable, 
   n_comment           varchar(152)       not null not droppable, 
primary key (n_nationkey) not droppable) 
store by primary key 
location $$partition1$$;

-- Inserting data
cd ./tpcd/tpcdtemp;
sh import cat3.details.supplier -I supplier.tbl -U supplier.fmt -r -C 60;
sh import cat3.details.partsupp -I partsupp.tbl -U partsupp.fmt -r -C 6000;
sh import cat3.geography.nation -I nation.tbl -U nation.fmt;
cd ../..;

-- Creating MV67
set schema cat3.testing;
obey ./TPCD/CREATEMVS(MV67);
-- Basic Q67
-- for compare
PREPARE stat1 FROM
    select
    ps_suppkey,
    sum(ps_supplycost*ps_availqty) as total
    from cat3.details.partsupp,
         cat3.details.supplier,
         cat3.geography.nation
    where 
        ps_suppkey = s_suppkey
        and s_nationkey = n_nationkey
        and n_name = 'GERMANY'
    group by ps_suppkey
    order by total;

PREPARE stat2 FROM
    select ps_suppkey, total
    from cat3.testing.mv4q67
    order by total;

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

-- partsupp
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat3.details.partsupp@ @0@ @100@ ;
log;
sh import cat3.details.partsupp -I ./tpcd/tpcdtemp/partsupp.tbl -U ./tpcd/tpcdtemp/partsupp.fmt -r -F 6000 -C 400 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215IR;
log LOGMV215IR;

CREATE TANDEM_CAT_REQUEST&1 16 3 @cat3.details.partsupp@ @1@ @0@ ;

internal refresh mv4q67 from
        singleDelta cat3.details.partsupp
		between 100 and 100 no de;

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

-- supplier
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat3.details.supplier@ @0@ @100@ ;
log;
sh import cat3.details.supplier -I ./tpcd/tpcdtemp/supplier.tbl -U ./tpcd/tpcdtemp/supplier.fmt -r -F 60 -C 10 >temp.tmp;
sh egrep "Import|Rows" temp.tmp >>logmv215IR;
log LOGMV215IR;

update cat3.details.supplier
set s_comment = 'not trustable'
where s_suppkey < 15 and s_suppkey > 3;

CREATE TANDEM_CAT_REQUEST&1 16 3 @cat3.details.supplier@ @1@ @0@ ;

internal refresh mv4q67 from
        singleDelta cat3.details.supplier
		between 100 and 100 no de;

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

-- nation
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat3.geography.nation@ @0@ @100@ ;
insert into cat3.geography.nation values (50, 1, 'CANADA', 'yes'), (51, 2, 'MEXICO', 'yes');
CREATE TANDEM_CAT_REQUEST&1 16 3 @cat3.geography.nation@ @1@ @0@ ;

internal refresh mv4q67 from
        singleDelta cat3.geography.nation
        between 100 and 100 no de;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215IR;
delete from table (iud_log_table cat3.details.partsupp);
delete from table (iud_log_table cat3.details.supplier);
delete from table (iud_log_table cat3.geography.nation);
log LOGMV215IR;

