~drizzle-trunk/drizzle/development

#--------------------------------------------------
# Initialize system_3 test variables 
#--------------------------------------------------

--source suite/system_3/include/system_3_init.inc

let $NUM_VAL=`SELECT @NUM_VAL`;
let $LOAD_LINES=`SELECT @LOAD_LINES`;
let $LOG_UPPER=`SELECT @LOG_UPPER`;
let $LOG_LOWER=`SELECT @LOG_LOWER`;
#let $ENG1=`SELECT @ENG1`;
let $ENG2=`SELECT @ENG2`;
let $ENG_LOG=`SELECT @ENG_LOG`;
let $CLIENT_HOST=`SELECT @CLIENT_HOST`;
let $ENG=innodb;
let $ENG1=innodb;
#---------------------------------------------------------
# Column list with definition for all tables to be checked
#---------------------------------------------------------

let $column_list= f1 int,
f2 char (15),
f3 decimal (5,3),
f4 datetime;

let $col_access_list = f1,f2,f3,f4 ;
let $col_new_list    = new.f1,new.f2,new.f3 new.f4 ;

#---------------------------------------------------
# Setting the parameters to use during testing
#---------------------------------------------------
# Set number of variations of the f1 variable (used to segment the rows
# being updated/deleted by a user at a time. The higher the number, the
# more smaller segments used with each query.
--replace_result $NUM_VAL NUM_VAL
eval set @f1_nums=$NUM_VAL;

# The following sets the number controls the size of the log table.
# Once a size of '@threshold' is reached, the first rows are removed
# sunch that the table is down to '@shrink_to' lines
--replace_result $LOG_LOWER LOG_LOWER
eval set @shrink_to=$LOG_LOWER;
--replace_result $LOG_UPPER LOG_UPPER
eval set @threshold=$LOG_UPPER;

#---------------------------------------------------
# Creating the database tables and loading the data
#---------------------------------------------------

--disable_warnings
drop database if exists systest1;
--enable_warnings

create database systest1;

--disable_abort_on_error
--replace_result $CLIENT_HOST CLIENT_HOST
eval create user systuser@'$CLIENT_HOST';
--enable_abort_on_error
--replace_result $CLIENT_HOST CLIENT_HOST
eval set password for systuser@'$CLIENT_HOST' = password('systpass');
--replace_result $CLIENT_HOST CLIENT_HOST
eval grant ALL on systest1.* to systuser@'$CLIENT_HOST';
use systest1;
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
connect (systuser,localhost,systuser,systpass,systest1,$MASTER_MYPORT,$MASTER_MYSOCK);

create table tb1_master (
   f1 int,
   f2 char(15),
   f3 decimal (5,3),
   f4 datetime
);

#--replace_result $ENG_LOG ENG_LOG
eval create table tb1_logs (
   i1 int NOT NULL auto_increment, primary key (i1),
   dt1 datetime NOT NULL,
   entry_dsc char(100),
   f4 int
) engine=$ENG_LOG
;
#PARTITION BY HASH (i1) PARTITIONS 8;

if ($debug)
{
SHOW CREATE TABLE tb1_logs;
}

#--replace_result $ENG_LOG ENG_LOG
eval create table ddl_logs (
   i1 int NOT NULL auto_increment, primary key (i1),
   dt1 datetime NOT NULL,
   entry_dsc char(100),
   errno int
) engine=$ENG_LOG;
#PARTITION BY HASH (i1) PARTITIONS 8;

if ($debug)
{
SHOW CREATE TABLE tb1_logs;
}
create table test_stat (
   dt1 datetime,
   table_name char(20),
   row_count int,
   start_row int,
   end_row int
);

#----------------------------------------------------------------------
# tb3_eng1: key partitioning
#----------------------------------------------------------------------

#--replace_result $ENG1 ENG1
eval create table tb3_eng1 (
   i1 int NOT NULL auto_increment, primary key (i1),
   $column_list
) engine=$ENG1
PARTITION BY KEY (i1) PARTITIONS 4
(PARTITION part1,
PARTITION part2,
PARTITION part3,
PARTITION part4);

#--replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR
eval load data local infile '$MYSQL_TEST_DIR/suite/system_3/data/tb1.txt'
     into table tb3_eng1 ($col_access_list);

if ($WITH_TRIGGERS)
{
delimiter //;

Create trigger tb3_eng1_ins after insert on tb3_eng1 for each row
BEGIN
   insert into tb1_logs (dt1, entry_dsc, f4)
   values (now(), concat('Insert row ', new.f1,' ',
           new.f2, ' ', new.f3, ' (tb3_eng1)'), new.f1);
END//

Create trigger tb3_eng1_upd after update on tb3_eng1 for each row
BEGIN
   insert into tb1_logs (dt1, entry_dsc, f4)
   values (now(), concat('Update row ', old.f1,' ', old.f2, '->',
           new.f2, ' ', old.f3, '->', new.f3, ' (tb3_eng1)'), new.f1);
END//

Create trigger tb3_eng1_del after delete on tb3_eng1 for each row
BEGIN
   insert into tb1_logs (dt1, entry_dsc, f4)
   values (now(), concat('Delete row ', old.f1,' ', old.f2, ' ',
           old.f3, ' (tb3_eng1)'), old.f1);
END//

delimiter ;//
}
delimiter //;

# This functions returns a random integer number 
# between zero and 'num'
#-----------------------------------------------
create function int_rand(num int) returns int
BEGIN
   return round(num*rand()+0.5); 
END//

# This function returns a string in the length 'len' of 
# random letters (ascii range of 65-122)
#------------------------------------------------------
create function str_rand (len int) returns char(12)
BEGIN
  declare tmp_letter char(1);
  declare tmp_word char(12);
  declare word_str char(12) default '';
  wl_loop: WHILE len DO
     set tmp_letter=char(round(57*rand()+65)); 
     set tmp_word=concat(word_str,tmp_letter);
     set word_str=tmp_word;
     set len=len-1;
  END WHILE wl_loop;
  return word_str;
END//


# This procedure scans 'tb1_master' table for rows where f1='num_pr'
# and for each row inserts a row in 'tb3_eng1'
#------------------------------------------------------------------
eval create procedure ins_tb3_eng1 (num_pr int, str_pr char(15))
BEGIN
  declare done int default 0;
  declare v3 decimal(5,3);
  declare cur1 cursor for 
     select f3 from tb1_master where f1=num_pr;
  declare continue handler for sqlstate '01000' set done = 1;
  declare continue handler for sqlstate '02000' set done = 1;
  open cur1;
  fetch cur1 into v3;
  wl_loop: WHILE NOT done DO
     insert into tb3_eng1 ($col_access_list) values
        (int_rand(@f1_nums), concat('I:',str_pr,'-',num_pr), v3, now());
     fetch cur1 into v3;
  END WHILE wl_loop;
  close cur1;
END//


# This procedure does selects from the 'tb1_logs' and inserts the 
# count into the table
#------------------------------------------------------------------
create procedure slct_tb1_logs ()
BEGIN
  declare done int default 0;
  declare v4 int;
  declare v_count int default 0;
  declare str_val char(15) default ELT(int_rand(3), 
     'Insert', 'Update', 'Delete');
  declare cur1 cursor for 
     select f4 from tb1_logs where entry_dsc like concat('%',str_val,'%'); 
  declare continue handler for sqlstate '01000' set done = 1;
  declare continue handler for sqlstate '02000' set done = 1;
  open cur1;
  fetch cur1 into v4;
  wl_loop: WHILE NOT done DO
     set v_count=v_count+1;
     fetch cur1 into v4;
  END WHILE wl_loop;
  close cur1;
  insert into tb1_logs (dt1, entry_dsc, f4)
     values (now(), concat('Number of \'', str_val, '\' rows is: ', 
             v_count, ' (tb1_log)'),0);
END//

delimiter ;//

--disable_abort_on_error
insert into systest1.tb3_eng1 values (NULL,50,'init_val',12.345,'2005-01-01 00:00:00');
insert into systest1.tb3_eng1 values (NULL,70,'init_val',12.345,'2005-01-01 00:00:00');
--enable_abort_on_error

connection default;0.
--disable_abort_on_error
--replace_result $CLIENT_HOST CLIENT_HOST
eval create user syst1user@'$CLIENT_HOST';
--enable_abort_on_error
--replace_result $CLIENT_HOST CLIENT_HOST
eval set password for syst1user@'$CLIENT_HOST' = password('systpass');
--replace_result $CLIENT_HOST CLIENT_HOST
eval grant ALL on systest1.* to syst1user@'$CLIENT_HOST';
use systest1;
--replace_result $MASTER_MYPORT MASTER_MYPORT $MASTER_MYSOCK MASTER_MYSOCK
connect (syst1user,localhost,syst1user,systpass,systest1,$MASTER_MYPORT,$MASTER_MYSOCK);

--source suite/system_3/include/system_3_init.inc
use systest1;
let $NUM_VAL=`SELECT @NUM_VAL`;
eval SET @f1_nums=$NUM_VAL;
SET @tmp_num=int_rand(@f1_nums);
SET @tmp_word=str_rand(4);

# DEBUG select @tmp_num, @tmp_word;

# Insert rows replacing the deleted rows using a strored procedure
# that reads the rows from a master table
CALL ins_tb3_eng1 (@tmp_num, @tmp_word);

connection syst1user;
--source suite/system_3/include/system_3_init.inc
use systest1;
let $NUM_VAL=`SELECT @NUM_VAL`;
eval SET @f1_nums=$NUM_VAL;
SET @tmp_num=int_rand(@f1_nums);
SET @tmp_word=str_rand(4);

# DEBUG select @tmp_num, @tmp_word;

# Insert rows replacing the deleted rows using a strored procedure
# that reads the rows from a master table
CALL ins_tb3_eng1 (@tmp_num, @tmp_word);

connection systuser;
--source suite/system_3/include/system_3_init.inc
use systest1;
call slct_tb1_logs();

connection syst1user;
--source suite/system_3/include/system_3_init.inc
use systest1;
let $NUM_VAL=`SELECT @NUM_VAL`;
eval set @f1_nums=$NUM_VAL;
set @tmp_num=int_rand(@f1_nums);
set @tmp_word=str_rand(4);

select @tmp_num, @tmp_word;

# Update all rows in the table where f1 is one less the random number
update tb3_eng1  
   set f2=concat('U:',@tmp_word,'-',@tmp_num), f3=f3+1 
   where f1=@tmp_num-1;

connection systuser;
--source suite/system_3/include/system_3_init.inc
use systest1;
let $NUM_VAL=`SELECT @NUM_VAL`;
eval set @f1_nums=$NUM_VAL;
set @tmp_num=int_rand(@f1_nums);
set @tmp_word=str_rand(4);

select @tmp_num, @tmp_word;

# Update all rows in the table where f1 is one less the random number
update tb3_eng1  
   set f2=concat('U:',@tmp_word,'-',@tmp_num), f3=f3+1 
   where f1=@tmp_num-1;

connection syst1user;
--source suite/system_3/include/system_3_init.inc
use systest1;
call slct_tb1_logs();

connection systuser;
--source suite/system_3/include/system_3_init.inc
use systest1;
let $NUM_VAL=`SELECT @NUM_VAL`;
eval set @f1_nums=$NUM_VAL;
set @tmp_num=int_rand(@f1_nums);
set @tmp_word=str_rand(4);

select @tmp_num, @tmp_word;

# Update all rows in the table where f1 is one less the random number
update tb3_eng1  
   set f2=concat('U:',@tmp_word,'-',@tmp_num), f3=f3+1 
   where f1=@tmp_num-1;


connection syst1user;
--source suite/system_3/include/system_3_init.inc
use systest1;
#--replace_result $NUM_VAL <NUM_VAL>
let $NUM_VAL=`SELECT @NUM_VAL`;
eval set @f1_nums=$NUM_VAL;
set @tmp_num=int_rand(@f1_nums);
select @tmp_num;

# DEBUG select @tmp_num, @tmp_word;

# Delete all rows from the table where f1 is equal to the above number
delete from tb3_eng1 where f1=@tmp_num;

connection systuser;
--source suite/system_3/include/system_3_init.inc
use systest1;
select * from tb3_eng1 where f1>40;


connection syst1user;
--source suite/system_3/include/system_3_init.inc
use systest1;
let $NUM_VAL=`SELECT @NUM_VAL`;
eval set @f1_nums=$NUM_VAL;
set @tmp_num=int_rand(@f1_nums);
select @tmp_num;

# DEBUG select @tmp_num, @tmp_word;

# Delete all rows from the table where f1 is equal to the above number
delete from tb3_eng1 where f1=@tmp_num;

connection systuser;
--source suite/system_3/include/system_3_init.inc
use systest1;
select * from tb3_eng1 where f1>40;

connection syst1user;
--source suite/system_3/include/system_3_init.inc
use systest1;
let $NUM_VAL=`SELECT @NUM_VAL`;
eval set @f1_nums=$NUM_VAL;
set @tmp_num=int_rand(@f1_nums);
select @tmp_num;

select @tmp_num, @tmp_word;

# Delete all rows from the table where f1 is equal to the above number
delete from tb3_eng1 where f1=@tmp_num;

connection systuser;
--source suite/system_3/include/system_3_init.inc
use systest1;
select * from tb3_eng1 where f1>40;