-- @@@ 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 @@@
control query default pos 'OFF';
set schema catmvs.mvschm;
set PARSERFLAGS 3;

obey TESTMV511(CLEAN_UP);
insert into HP_SYSTEM_CATALOG.SYSTEM_DEFAULTS_SCHEMA.SYSTEM_DEFAULTS
(attribute, attr_value) values ('MV_REFRESH_MAX_PARALLELISM', '1');
insert into HP_SYSTEM_CATALOG.SYSTEM_DEFAULTS_SCHEMA.SYSTEM_DEFAULTS
(attribute, attr_value) values ('MV_REFRESH_MAX_PIPELINING', '1');
log LOGMV511 clear;

----------------
obey TESTMV511(SETUP);


-- insert using sideinserts into audited tables is not supported.
--	obey TESTMV511(TEST1);

	obey TESTMV511(TEST2); 
	obey TESTMV511(TEST3);
	obey TESTMV511(TEST4);
	obey TESTMV511(TEST5); 

obey TESTMV511(CLEAN_UP);

log;
delete from HP_SYSTEM_CATALOG.SYSTEM_DEFAULTS_SCHEMA.SYSTEM_DEFAULTS
where attribute = 'MV_REFRESH_MAX_PARALLELISM';
delete from HP_SYSTEM_CATALOG.SYSTEM_DEFAULTS_SCHEMA.SYSTEM_DEFAULTS
where attribute = 'MV_REFRESH_MAX_PIPELINING';
exit;

------------------------------------------------------
?section SETUP
------------------------------------------------------
control query default insert_vsbb 'USER';

create table Num_Obj(schema_name CHARACTER(50), num_of_objects int);
ALTER TABLE Num_Obj attribute all mvs allowed;

set param ?schema_name 'MVSCHM';
obey INSERT_NUM_OF_OBJ;


create mvgroup MVG1_511;


create table T_511_T1
	(a         int not null not droppable, 
	 b         int not null not droppable,
	 long_str  CHARACTER (3000) ,
	 primary key (a,b))
	 attribute automatic rangelog
	 store by primary key 
	 location $$partition0$$
	 PARTITION (add first key (101,   1) location $$partition1$$,
	            add first key (201,1000) location $$partition2$$,
				add first key (300, 200) location $$partition2$$,
	            add first key (701,  10) location $$partition0$$);
ALTER TABLE T_511_T1 attribute all mvs allowed;

create table T_511_T1_temp
	(a         int not null not droppable, 
	 b         int not null not droppable, 
	 long_str  CHARACTER (3000) ,
	 primary key (a,b)); 
ALTER TABLE T_511_T1_temp attribute all mvs allowed;


create mv T_511_MV1
	refresh on request
	initialize on create
	as select b ,count (a) cnt  
	   from T_511_T1
	   group by b;
ALTER MV T_511_MV1 attribute all mvs allowed;

ALTER  mvgroup MVG1_511 ADD T_511_MV1 ;


prepare select_iud_log from
 select "@EPOCH"
        , case "@OPERATION_TYPE" when 0 then 'INSERT'
                                 when 1 then 'DELETE'
                                 when 2 then 'I-UPDATE'
                                 when 3 then 'D-UPDATE'
                                 when 4 then 'END-RANGE'
                                 when 12 then 'BEGIN-RANGE'
          end
        , "@RANGE_SIZE"
        , a, b
 from table(iud_log_table T_511_T1)
 order by a,b,"@TS" ;


create table T_511_T3 (	a int not null not droppable,
			str char(50) not null not droppable,
		primary key(a  ASC , str DESC))
		attribute automatic rangelog
		store by primary key 
		location $$partition0$$
		PARTITION (add first key (101,'a')  location $$partition1$$,
			   add first key (201,'de') location $$partition2$$,
			   add first key (300,'b')  location $$partition2$$,
			   add first key (701,'e')  location $$partition0$$);
ALTER TABLE T_511_T3 attribute all mvs allowed;

create table T_511_T3_temp (	a int not null not droppable,  
			str char(50) not null not droppable,
			primary key(a , str));
ALTER TABLE T_511_T3_temp attribute all mvs allowed;

create mv T_511_MV3
	refresh on request
	initialize on create
	as  select a, count(str) cnt 
            from T_511_T3
            group by a;
ALTER MV T_511_MV3 attribute all mvs allowed;

ALTER  mvgroup MVG1_511 ADD T_511_MV3;

prepare select_from_log_of_T3 from
	select "@EPOCH",
		   case "@OPERATION_TYPE" when  0 then 'INSERT'
		                          when  4 then 'END-RANGE'
		                          when 12 then 'BEGIN-RANGE'
   					  else         '???'
		   end,
		   a,str
	from table (iud_log_table T_511_T3)
	order by a, "@TS";


create table T_511_T4
	(a         int not null not droppable, 
	 b         int not null not droppable,
	 long_str  CHARACTER (3000) ,
	 primary key (a,b))
    attribute automatic rangelog
    store by primary key 
    location $$partition0$$
    PARTITION  (add first key (101,   1) location $$partition1$$,
		add first key (201,1000) location $$partition2$$,
		add first key (300, 200) location $$partition2$$,
		add first key (701,  10) location $$partition0$$);
ALTER TABLE T_511_T4 attribute all mvs allowed;

create table T_511_T4_temp
	(a         int not null not droppable, 
	 b         int not null not droppable, 
	 long_str  CHARACTER (3000), 
	 primary key (a,b)); 
ALTER TABLE T_511_T4_temp attribute all mvs allowed;

create mv T_511_MV4
	refresh on request
	initialize on create
	as select b , sum (a) sum_a 
           from T_511_T4
           group by b;
ALTER MV T_511_MV4 attribute all mvs allowed;

ALTER  mvgroup MVG1_511 ADD T_511_MV4;

prepare select_from_log_of_T4 from
	select "@EPOCH",
		   case "@OPERATION_TYPE" when  0 then 'INSERT'
		                          when  4 then 'END-RANGE'
		                          when 12 then 'BEGIN-RANGE'
   					  else         '???'
		   end,
		   a
	from table (iud_log_table T_511_T4)
	order by a, "@TS";

----------------------------------------------------------------------------------
?section TEST1
----------------------------------------------------------------------------------
insert using sideinserts into T_511_T1(a,b,long_str) select * from T_511_T1_temp order by a,b;
execute select_iud_log;

----------------------------------------------------------------------------------
?section TEST2
----------------------------------------------------------------------------------
insert into T_511_T1_temp values 
	(1,1,'ahfsdh'),(2,1,'adfh'),(3,1,'yturiur'),(15,2,'erwy'),(16,7,'etywrt'),
	(17,7,'fdsgsd'),(20,7,'sdfgSDFH'),(50,7,'fgdhsfgj'),(51,12,'gfsgjmfjd'),(52,1,'sfgjsgsjgf'),
	(201,1001,'fdshfdsjgd'),(202,1004,'dfjdfj'),(203,1004,'dfhfhf'),(215,1200,'dfghkjhk'),(216,1007,'fsgjtr'),
	(217,1070,'YtfTRE'),(220,1070,'fgjRTD'),(250,1070,'REHSshgRD'),(251,1112,'weah'),(252,1111,'adfh'),
	(254,2103,'DHjhgfFGH'),(256,1405,'RASafdh'),(258,1406,'fds'),(259,1089,'uyk'),(260,1089,'hkj'),
	(261,1089,'fdsh'),(262,1046,'sadg'),(263,1047,'sdtrTr'),(264,48,'treR'),(265,1,'rtyhER'),
	(285,85,'dsfhg'),(286,1046,'sdf'),(287,1047,'dssd'),(288,1048,'sh'),(289,1049,'sdhf'),
	(290,1050,'asdg'),(291,1051,'dfhERdfg'),(292,1048,'dfsRE'),(293,1047,'sWsf'),(295,85,'sdag'),
	(295,1983,'dsf'),(295,1986,'ets32rf'),(295,1987,'sdj'),(295,3432,'sag'),(295,5656,'dhrewe'),
	(296,234,'dhn'),(298,23,'df'),(298,34,'weq'),(298,45,'fgad'),(298,98,'weqhhfds'),
	(705,235,'erh'),(705,237,'34rwer'),(705,56,'fgh'),(705,89,'dsag'),(705,789,'efw'),
	(719,346,'fgdhE'),(719,71,'ewt'),(735,68,'hgfdj'),(735,658,'fds'),(735,34,'dah'),
	(736,34,'DFggs'),(736,3245,'dsfhsdh'),(736,235,'sda'),(736,372,'dweqw'),(736,33,'erw'),
	(790,23,'asd'),(791,22,'dsfsd'),(792,23,'dfg'),(792,234,'fds'),(792,33,'asdg');

set parserflags 1024; -- allow no audit
ALTER table T_511_T1 attribute no audit;
reset parserflags 1024;

insert using sideinserts into T_511_T1(a,b,long_str) select * from T_511_T1_temp order by a,b;
execute select_iud_log;

set parserflags 1024; -- allow no audit
ALTER table T_511_T1 attribute audit;
reset parserflags 1024;

select count(*) cnt from T_511_T1;

----------------------------------------------------------------------------------
?section TEST3
----------------------------------------------------------------------------------
delete from T_511_T1_temp;

insert into T_511_T1_temp values 
	(101,1,'gdfs'),(102,1,'sadg'),(103,1,'asdg'),(115,2,'adsg'),(116,7,'asg'),
	(117,7,'asdg'),(120,7,'dsg'),(150,7,'dsa'),(151,12,'asd'),(152,1,'ag'),
	(154,23,'gds'),(156,45,'ag'),(158,46,'gaew'),(159,89,'DSffdsS'),(160,89,'THFfg'),
	(161,89,'gfddf'),(162,46,'sdg'),(163,47,'sdgS'),(164,48,'dfEfd'),(165,1,'sdgESfds'),
	(185,85,'sdfg'),(186,46,'dsfg'),(187,47,'sdfg'),(188,48,'dsfg'),(189,49,'sdfg'),
	(190,50,'sad'),(191,51,'asda'),(192,48,'dsaQWdsa'),(193,47,'sASda'),(195,85,'asSAas'),
	(301,1,'fdweEw'),(302,1,'SDFwa'),(303,1,'asF'),(315,2,'saSsd'),(316,7,'SsdfD'),
	(317,7,'FGcbnvc'),(320,7,'FdGfg'),(350,7,'sdfSA'),(551,12,'asdwq'),(552,1,'fdS'),
	(554,23,'asdf'),(556,45,'ASdfa'),(558,46,'jfg'),(559,89,'ghj'),(560,89,'ghj'),
	(561,89,'dfgh'),(562,46,'sdgj'),(563,47,'sdgj'),(564,48,'sdfh'),(565,1,'sdfh'),
	(585,85,'sdfh'),(586,46,'sdfh'),(587,47,'sdfh'),(588,48,'dfgh'),(589,49,'fgh'),
	(590,50,'fdgh'),(591,51,'dfgh'),(592,48,'ghDFfd'),(593,47,'hFGdg'),(595,85,'DHdas');

set parserflags 1024; -- allow no audit
ALTER table T_511_T1 attribute no audit;
reset parserflags 1024;

insert using sideinserts into T_511_T1(a,b,long_str) select * from T_511_T1_temp order by a,b;
execute select_iud_log;

set parserflags 1024; -- allow no audit
ALTER table T_511_T1 attribute audit;
reset parserflags 1024;

select count(*) cnt from T_511_T1;

refresh T_511_MV1 outfile REFRESH.LOG;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV511;
log LOGMV511;

PREPARE stat1 FROM
	select * from T_511_MV1
	order by b;
PREPARE stat2 FROM
	select b , count (a) cnt 
	from T_511_T1
	group by b
	order by b;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV511;
log LOGMV511;


----------------------------------------------------------------------------------
?section TEST4
--------------------------------------------------------------------------------
-- Expected :  hash partitioning and automatic or mixed rangelog cannot coexist
create table T_511_T2 ( a int not null not droppable, 
			b int, 
			primary key(a))
                store by primary key
		attribute automatic rangelog
                hash PARTITION by (a)
                     (add location $$partition1$$,
                      add location $$partition2$$);

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

insert into T_511_T3_temp values (101, 'b'),(1,'fdg'),(23,'dsga'),(300,'b'), (300,'a'),(350, 'gjhg'),(380, 'dghg');

set parserflags 1024; -- allow no audit
ALTER table T_511_T3 attribute no audit;
reset parserflags 1024;

-- expected error or regular insert 
insert using sideinserts into T_511_T3(a,str) 
	select * from T_511_T3_temp order by a,str; 

set parserflags 1024; -- allow no audit
ALTER table T_511_T3 attribute audit;
reset parserflags 1024;

select count(*) cnt from T_511_T3;

execute select_from_log_of_T3;
			
refresh T_511_MV3 outfile REFRESH.LOG;
log;
sh cat REFRESH.LOG | awk -f FILTER_TIME.AWK >> LOGMV511;
log LOGMV511;

PREPARE stat1 FROM
	select * from T_511_MV3
	order by a;
PREPARE stat2 FROM
	   select a,count(str) cnt  
        from T_511_T3
        group by a
	order by a;
obey COMPARE;
sh diff TEMP1 TEMP2 >> LOGMV511;
log LOGMV511;

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

refresh mvgroup MVG1_511 cancel outfile REFRESH.LOG ;

 drop mv T_511_MV1;
 drop mv T_511_MV3;
 drop mv T_511_MV4;
 drop table T_511_T1_temp;
 drop table T_511_T1;
 drop table T_511_T3_temp;
 drop table T_511_T3;
 drop table T_511_T4_temp;
 drop table T_511_T4;

drop mvgroup MVG1_511;

set param ?schema_name 'MVSCHM';
obey COMPARE_NUM_OF_OBJ;

drop table Num_Obj;



