-- @@@ 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 @@@
--
--=======================================================================
-- TestMV640 - This test checks correctness of Majv over 3 tables,
--             where iud changes effect rows in the majv. It uses 
--             TPC-D database.
--=======================================================================

set PARSERFLAGS 3;

obey TESTMV640(CLEAN_UP);
obey TESTMV640(SET_UP);
log LOGMV640 clear;

-- Run Tests

    obey TESTMV640(TEST1);

-- Clean and Exit
log;
obey TESTMV640(CLEAN_UP);
exit;

--=======================================================================
?section TEST1
--=======================================================================

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

-- inserting rows to lineitem that have corresponding keys in part and supplier
insert into lineitem select * from lineitemp;

-- deleting rows from part
delete from part where p_partkey > 1000;

-- deleting rows from supplier
delete from supplier where s_suppkey < 6;

-- deleting some rows from lineitem
delete from lineitem where l_partkey between 400 and 500
    and l_suppkey between 1 and 10;

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

internal refresh T_640_MV1
    from multidelta lineitem between 10 and 10 de level 0 use no rangelog use iudlog,
                    supplier between 10 and 10 de level 0 use no rangelog use iudlog,
                    part     between 10 and 10 de level 0 use no rangelog use iudlog
         phase 0;

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

--=======================================================================
?section SET_UP
--=======================================================================
create catalog cat;
create schema cat.tpcd;
cd ./TPCD;

set schema CAT.TPCD;
set envvar DSS_CONFIG . ;
-- change all of these lines together if needed
#ifNSK
set envvar DSS_TEMP ./TPCDTEMP;
sh mkdir $DSS_TEMP;
#ifNSK
#ifNT
set envvar DSS_TEMP .\tpcdtemp;
sh mkdir %DSS_TEMP% ;
sh cp %DSS_CONFIG%\dbgen.exe %DSS_TEMP% ;
sh cp %DSS_CONFIG%\Dists.dss %DSS_TEMP% ;
sh cp %DSS_CONFIG%\*.fmt %DSS_TEMP% ;
sh cp %DSS_CONFIG%\generateTpcd.ksh %DSS_TEMP% ;
sh cp %DSS_CONFIG%\*.awk %DSS_TEMP% ;
sh cp %DSS_CONFIG%\runGen.pl %DSS_TEMP% ;
#ifNT

cd TPCDTEMP;

-- ******************************************************************
-- * Create the tables                                              *
-- ******************************************************************

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) 
location $$partition1$$;

Create table suppliertemp  (
   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) 
location $$partition1$$;

Create table lineitem  (
   l_orderkey          int                not null not droppable, 
   l_linenumber        int                not null not droppable, 
   l_partkey           int                not null not droppable, 
   l_suppkey           int                not null not droppable, 
   l_quantity          numeric(12,2)      not null not droppable, 
   l_extendedprice     numeric(12,2)      not null not droppable, 
   l_discount          numeric(12,2)      not null not droppable, 
   l_tax               numeric(12,2)      not null not droppable, 
   l_shipdate          date               not null not droppable, 
   l_commitdate        date               not null not droppable, 
   l_receiptdate       date               not null not droppable, 
   l_returnflag        char(1)            not null not droppable, 
   l_linestatus        char(1)            not null not droppable, 
   l_shipinstruct      char(25)           not null not droppable, 
   l_shipmode          char(10)           not null not droppable, 
   l_comment           varchar(44)        not null not droppable, 
primary key (l_orderkey,l_linenumber) not droppable) 
store by primary key 
location $$partition1$$;

Create table lineitemp  (
   l_orderkey          int                not null not droppable, 
   l_linenumber        int                not null not droppable, 
   l_partkey           int                not null not droppable, 
   l_suppkey           int                not null not droppable, 
   l_quantity          numeric(12,2)      not null not droppable, 
   l_extendedprice     numeric(12,2)      not null not droppable, 
   l_discount          numeric(12,2)      not null not droppable, 
   l_tax               numeric(12,2)      not null not droppable, 
   l_shipdate          date               not null not droppable, 
   l_commitdate        date               not null not droppable, 
   l_receiptdate       date               not null not droppable, 
   l_returnflag        char(1)            not null not droppable, 
   l_linestatus        char(1)            not null not droppable, 
   l_shipinstruct      char(25)           not null not droppable, 
   l_shipmode          char(10)           not null not droppable, 
   l_comment           varchar(44)        not null not droppable)
location $$partition1$$;

Create table part  (
   p_partkey           int                not null not droppable, 
   p_size              int                not null not droppable, 
   p_retailprice       numeric(12,2)      not null not droppable, 
   p_mfgr              char(25)           not null not droppable, 
   p_brand             char(10)           not null not droppable, 
   p_container         char(10)           not null not droppable, 
   p_name              varchar(55)        not null not droppable, 
   p_type              varchar(25)        not null not droppable, 
   p_comment           varchar(23)        not null not droppable)
location $$partition1$$;

Create table partemp  (
   p_partkey           int                not null not droppable, 
   p_size              int                not null not droppable, 
   p_retailprice       numeric(12,2)      not null not droppable, 
   p_mfgr              char(25)           not null not droppable, 
   p_brand             char(10)           not null not droppable, 
   p_container         char(10)           not null not droppable, 
   p_name              varchar(55)        not null not droppable, 
   p_type              varchar(25)        not null not droppable, 
   p_comment           varchar(23)        not null not droppable)
location $$partition1$$;

sh perl runGen.pl;

-- ******************************************************************
-- * Import into all base tables.                                   *
-- ******************************************************************
sh awk -f ./FILTER_3KEY.AWK -v  downLimit1=0 -v downLimit2=0 -v downLimit3=0 -v upLimit1=10000 -v upLimit2=1500 -v upLimit3=80  lineitem.tbl > lineitem.tbl1 ;

sh import cat.tpcd.supplier -I supplier.tbl -U supplier.fmt  ;
sh import cat.tpcd.part     -I part.tbl     -U part.fmt      ;
sh import cat.tpcd.lineitem -I lineitem.tbl1 -U lineitem.fmt  ;

insert into partemp select * from part;
insert into part select p_partkey, p_size + 3, p_retailprice - 1, p_mfgr, p_brand, p_container, p_name, p_type, p_comment from partemp;

insert into suppliertemp select * from supplier;
insert into supplier select s_suppkey, s_nationkey + 1, s_acctbal, s_phone, s_name, s_address, s_comment from suppliertemp;

insert into lineitemp select * from lineitem where l_partkey < 20;
update lineitemp set l_partkey = 63 , l_suppkey = 85 where l_partkey < 6;
update lineitemp set l_partkey = 63 , l_suppkey = 86 where l_partkey between 6 and 10;
update lineitemp set l_partkey = 16 , l_suppkey = 85 where l_partkey between 11 and 15;
update lineitemp set l_partkey = 16 , l_suppkey = 85 where l_partkey between 16 and 20;
update lineitemp set l_orderkey = l_orderkey + 10000;

cd ../..;

create mv T_640_MV1
    refresh on request
    initialize on create
    as
        select l_partkey as pkey,
               l_suppkey as skey,
               count(*) as cnt,
               sum(l_quantity) as sum_qty,
               avg(l_orderkey) as avg_key
        from lineitem, part, supplier
        where l_partkey = p_partkey
          and l_suppkey = s_suppkey
        group by l_partkey, l_suppkey;

-- for compare
PREPARE stat1 FROM 
    select l_partkey as pkey,
           l_suppkey as skey,
           count(*) as cnt,
           sum(l_quantity) as sum_qty,
           avg(l_orderkey) as avg_key
    from lineitem, part, supplier
    where l_partkey = p_partkey
      and l_suppkey = s_suppkey
    group by l_partkey, l_suppkey
    order by pkey, skey;

PREPARE stat2 FROM 
    select pkey, skey, cnt, sum_qty, avg_key
    from T_640_MV1
    order by pkey, skey;

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

drop mv T_640_MV1;
drop table partemp;
drop table suppliertemp;
drop table lineitemp;
drop table lineitem;
drop table supplier;
drop table part;

