-- @@@ 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 @@@
--
------------------------------------------------------
--- TestMV215m
--- 2.4.4.
--- PURPOSE: Multy transaction Refresh tests with TPCD database         
------------------------------------------------------



obey TESTMV215M(CLEAN_UP);

log LOGMV215M clear;

obey TESTMV215M(SET_UP);


-- Run Tests
    set schema cat.tpcd;
    obey TESTMV215M(TEST3);
	obey TESTMV215M(TEST4);
--	obey TESTMV215M(TEST5); Bug 276
	obey TESTMV215M(TEST6);
	obey TESTMV215M(TEST7);



exit;
-------------------------------------------------------
?section SET_UP
-------------------------------------------------------
control query default insert_vsbb 'USER';
set PARSERFLAGS 3;
set schema cat.tpcd;

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

log LOGMV215M;



-- 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 TEST3
-----------------------------------------------------
log LOGMV215M;

alter table lineitem  Attributes automatic RANGELOG ;

create mv mv4q1
    refresh on request
  	initialize on create
	MVAttributes COMMIT Refresh EACH 50
	  as
    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;



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

-- insert delta
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 >>logmv215m;
log LOGMV215M;


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

refresh mv4q1 debug 54;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215M;
log LOGMV215M;
     

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


--------------------------------------------------------------
?section TEST4
--------------------------------------------------------------
alter table lineitem  Attributes automatic RANGELOG ;

create mv mv4q3
    refresh on request
	ignore changes on customer,orders
    initialize on create
	MVAttributes COMMIT Refresh EACH 50
    as
    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;


-- 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 >> LOGMV215M ;
log LOGMV215M;


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 >>logmv215m;
log LOGMV215M;

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

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


refresh mv4q3 debug 54;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215M;
log LOGMV215M;
     
log;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV215M;
delete from table (iud_log_table cat.tpcd.lineitem);
log LOGMV215M;

----------------------------------------------------
?section TEST5
----------------------------------------------------
alter table lineitem  Attributes automatic RANGELOG ;

create mv mv4q6
    refresh on request
    initialize on create
	MVAttributes COMMIT Refresh EACH 50	
    as
    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;

-- 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 l_shipdate;

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

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

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 >>logmv215m;
log LOGMV215M;

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

delete from lineitem1 where l_partkey < 10;


refresh mv4q6 debug 54;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215M;
log LOGMV215M;

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

----------------------------------------------------
?section TEST6
----------------------------------------------------
alter table customer  Attributes automatic RANGELOG ;

create mv mv4q10
    refresh on request
	ignore changes on orders,lineitem, nation
	initialize on create	
    MVAttributes COMMIT Refresh EACH 50	
	as
    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;
    
-- 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 >> LOGMV215M;
log LOGMV215M;

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 >>logmv215m;
log LOGMV215M;

update customer
set c_nationkey = 12 -- 'JAPAN'
	where c_nationkey = 10 and c_acctbal < 4000;

refresh mv4q10 debug 54;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215M;
log LOGMV215M;

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

log;
delete from table (iud_log_table cat.tpcd.customer);
log LOGMV215M;

----------------------------------------------------
?section TEST7
----------------------------------------------------
alter table partsupp  Attributes automatic RANGELOG ;

create mv mv4q11
    refresh on request
	ignore changes on supplier,nation
	initialize on create	
    MVAttributes COMMIT Refresh EACH 50	
    as
    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;

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 ps_partkey desc;

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

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


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 >>logmv215m;
log LOGMV215M;

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

refresh mv4q11 debug 54;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV215M;
log LOGMV215M;

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