-- @@@ 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 @@@
--
------------------------------------------------------
--- TestMV215
--- 2.4.4.
--- PURPOSE: Internal Refresh tests with TPCD database         
------------------------------------------------------

obey TESTMV215(CLEAN_UP);
obey TESTMV215(SET_UP);

log LOGMV215 clear;
-- Run Tests
	set schema cat.tpcd;
	obey TESTMV215(TEST1);
	obey TESTMV215(TEST2);
	obey TESTMV215(TEST3);
	obey TESTMV215(TEST4);
	obey TESTMV215(TEST5);
-- Clean and exit
log;
obey TESTMV215(CLEAN_UP);
exit;

?section SET_UP
control query default insert_vsbb 'OFF';
set PARSERFLAGS 1;
set schema cat.tpcd;
obey PREPARE_DDL_SELECTS;
obey ./TPCD/CREATEALL.OBEY;
obey ./TPCD/CREATEMVS(CREATEALL);

?section CLEAN_UP
set schema cat.tpcd;
obey ./TPCD/CREATEMVS(DROPALL);


-- ---------------------------------------------------------------------------

?section TEST1
-- 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;


refresh mv4q5;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215;
log LOGMV215;
log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215 ;
log LOGMV215;

--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 31 -C 5 >>logmv215;
log LOGMV215;
--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);

REFRESH mv4q5;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215;
log LOGMV215;

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

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

--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 >>logmv215;
log LOGMV215;

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;

Refresh mv4q5;
log;sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK;LOG LOGMV215;

--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 >> LOGMV215 ;
delete from table (iud_log_table cat.tpcd.customer);
log LOGMV215;

?section TEST2
-- 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@ @1@ @0@ ;
--internal refresh mv4q9 
--      from singleDelta supplier
--		between 10 and 10 no de;
REFRESH mv4q9;
log;sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK;LOG LOGMV215;

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

--parts
--CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.part@ @0@ @10@ ;
log;
sh import cat.tpcd.part -I ./tpcd/tpcdtemp/part.tbl -U ./tpcd/tpcdtemp/part.fmt -r -F 600 -C 100 >>logmv215;
log LOGMV215;

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

--internal refresh mv4q9 
--        from singleDelta part
--		between 10 and 10 no de;
REFRESH mv4q9;
log;sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK;LOG LOGMV215;
log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;

-- partsupp
--CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.partsupp@ @0@ @10@ ;
log;
sh import cat.tpcd.partsupp -I ./tpcd/tpcdtemp/partsupp.tbl -U ./tpcd/tpcdtemp/partsupp.fmt -r -F 2400 -C 400 >>logmv215;
log LOGMV215;

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

--internal refresh mv4q9 
--        from singleDelta partsupp
--		between 10 and 10 no de;
REFRESH mv4q9;
log;sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK;LOG LOGMV215;
log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;

-- lineitem
--CREATE TANDEM_CAT_REQUEST&1 16 3 @cat.tpcd.lineitem@ @0@ @10@ ;
log;
sh import cat.tpcd.lineitem -I ./tpcd/tpcdtemp/lineitem.tbl -U ./tpcd/tpcdtemp/lineitem.fmt -r -F 17000 -C 2500 >>logmv215;
log LOGMV215;

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

--internal refresh mv4q9 from
--        singleDelta lineitem
--		between 10 and 10 no de;
REFRESH mv4q9;
log;sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK;LOG LOGMV215;

log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215;
log LOGMV215;
======================================================================
