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

--source suite/systems/include/system_1_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`;

#---------------------------------------------------
# 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
#---------------------------------------------------

SET SESSION SQL_MODE = 'NO_ENGINE_SUBSTITUTION';
--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;

CREATE TABLE tb0_master (
   f1 INT,
   f2 CHAR(15),
   f3 DECIMAL (5,3),
   f4 DATETIME
);

CREATE TABLE tb1_master (
   f1 INT,
   f2 CHAR(15),
   f3 DECIMAL (5,3),
   f4 DATETIME
);

# This table should just hold a set of dense integer numbers
CREATE TABLE tb1_aux ( f1 INTEGER ) ENGINE = MEMORY;
delimiter //;
CREATE PROCEDURE fill_tb1_aux (row_count INTEGER)
BEGIN
  DECLARE my_val INTEGER DEFAULT 1;
  TRUNCATE tb1_aux;
  wl_loop: WHILE my_val <= row_count DO
     INSERT INTO tb1_aux SET f1 = my_val;
     SET my_val = my_val + 1;
  END WHILE wl_loop;
END//
delimiter ;//
CALL fill_tb1_aux (@IUDS_UNIT * 4);

--replace_result $ENG_LOG <ENG_LOG>
eval CREATE TABLE tb0_logs (
   i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1),
   dt1 DATETIME NOT NULL,
   entry_dsc CHAR(100),
   f4 INT
) ENGINE = $ENG_LOG;

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

--replace_result $ENG1 <ENG1>
eval CREATE TABLE tb0_eng1 (
   i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1),
   f1 INT,
   f2 CHAR(15),
   f3 DECIMAL (5,3),
   f4 DATETIME
) ENGINE = $ENG1;

--replace_result $ENG1 <ENG1>
eval CREATE TABLE tb1_eng1 (
   i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1),
   f1 INT,
   f2 CHAR(15),
   f3 DECIMAL (5,3),
   f4 DATETIME
) ENGINE = $ENG1;

--replace_result $ENG2 <ENG2>
eval CREATE TABLE tb0_eng2 (
   i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1),
   f1 INT,
   f2 CHAR(15),
   f3 DECIMAL (5,3),
   f4 DATETIME
) ENGINE = $ENG2;

--replace_result $ENG2 <ENG2>
eval CREATE TABLE tb1_eng2 (
   i1 INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (i1),
   f1 INT,
   f2 CHAR(15),
   f3 DECIMAL (5,3),
   f4 DATETIME
) ENGINE = $ENG2;

CREATE TABLE test_stat (
   dt1 DATETIME,
   table_name CHAR(20),
   row_count INT,
   start_row INT,
   end_row INT
);

# Loading data to tb0_*
--replace_result $STRESS_TEST_SUITEDIR <STRESS_TEST_SUITEDIR>
eval LOAD DATA LOCAL INFILE '$STRESS_TEST_SUITEDIR/data/tb0.txt' INTO TABLE tb0_master;
--replace_result $STRESS_TEST_SUITEDIR <STRESS_TEST_SUITEDIR>
eval LOAD DATA LOCAL INFILE '$STRESS_TEST_SUITEDIR/data/tb0.txt' 
     INTO TABLE tb0_eng1 (f1, f2, f3, f4);
--replace_result $STRESS_TEST_SUITEDIR <STRESS_TEST_SUITEDIR>
eval LOAD DATA LOCAL INFILE '$STRESS_TEST_SUITEDIR/data/tb0.txt' 
     INTO TABLE tb0_eng2 (f1, f2, f3, f4);

# Loading data to tb1_*
--replace_result $STRESS_TEST_SUITEDIR <STRESS_TEST_SUITEDIR>
eval LOAD DATA LOCAL INFILE '$STRESS_TEST_SUITEDIR/data/tb1.txt' INTO TABLE tb1_master;
--replace_result $STRESS_TEST_SUITEDIR <STRESS_TEST_SUITEDIR>
eval LOAD DATA LOCAL INFILE '$STRESS_TEST_SUITEDIR/data/tb1.txt' 
     INTO TABLE tb1_eng1 (f1, f2, f3, f4);
--replace_result $STRESS_TEST_SUITEDIR <STRESS_TEST_SUITEDIR>
eval LOAD DATA LOCAL INFILE '$STRESS_TEST_SUITEDIR/data/tb1.txt' 
     INTO TABLE tb1_eng2 (f1, f2, f3, f4);

if (`SELECT @WITH_TRIGGERS = 1`)
{

# Triggers on tb0_*

CREATE TRIGGER tb0_eng1_ins AFTER INSERT ON tb0_eng1 FOR EACH ROW 
   INSERT INTO tb0_logs (dt1, entry_dsc, f4)
   VALUES (NOW(), CONCAT('Insert row  ', NEW.f1,' ',
           NEW.f2, ' ', NEW.f3, ' (tb0_eng1)'), NEW.f1);

CREATE TRIGGER tb0_eng1_upd AFTER UPDATE ON tb0_eng1 FOR EACH ROW 
   INSERT INTO tb0_logs (dt1, entry_dsc, f4)
   VALUES (NOW(), CONCAT('Update row ', OLD.f1,' ', OLD.f2, '->', 
           NEW.f2, ' ', OLD.f3, '->', NEW.f3, ' (tb0_eng1)'), NEW.f1);

CREATE TRIGGER tb0_eng1_del AFTER DELETE ON tb0_eng1 FOR EACH ROW 
   INSERT INTO tb0_logs (dt1, entry_dsc, f4)
   VALUES (NOW(), CONCAT('Delete row ', OLD.f1,' ', OLD.f2, ' ', 
           OLD.f3, ' (tb0_eng1)'), OLD.f1);

CREATE TRIGGER tb0_eng2_ins AFTER INSERT ON tb0_eng2 FOR EACH ROW 
   INSERT INTO tb0_logs (dt1, entry_dsc, f4)
   VALUES (NOW(), CONCAT('Insert row ', NEW.f1,' ',
           NEW.f2, ' ', NEW.f3, ' (tb0_eng2)'), NEW.f1);

CREATE TRIGGER tb0_eng2_upd AFTER update ON tb0_eng2 FOR EACH ROW 
   INSERT INTO tb0_logs (dt1, entry_dsc, f4)
   VALUES (NOW(), CONCAT('Update row ', OLD.f1,' ', OLD.f2, '->', 
           NEW.f2, ' ', OLD.f3, '->', NEW.f3, ' (tb0_eng2)'), NEW.f1);

CREATE TRIGGER tb0_eng2_del AFTER DELETE ON tb0_eng2 FOR EACH ROW 
   INSERT INTO tb0_logs (dt1, entry_dsc, f4)
   VALUES (NOW(), CONCAT('Delete row ', OLD.f1,' ', OLD.f2, ' ', 
           OLD.f3, ' (tb0_eng2)'), OLD.f1);


# Triggers on tb1_*

CREATE TRIGGER tb1_eng1_ins AFTER INSERT ON tb1_eng1 FOR EACH ROW 
   INSERT INTO tb1_logs (dt1, entry_dsc, f4)
   VALUES (NOW(), CONCAT('Insert row  ', NEW.f1,' ',
           NEW.f2, ' ', NEW.f3, ' (tb1_eng1)'), NEW.f1);

CREATE TRIGGER tb1_eng1_upd AFTER UPDATE ON tb1_eng1 FOR EACH ROW 
   INSERT INTO tb1_logs (dt1, entry_dsc, f4)
   VALUES (NOW(), CONCAT('Update row ', OLD.f1,' ', OLD.f2, '->', 
           NEW.f2, ' ', OLD.f3, '->', NEW.f3, ' (tb1_eng1)'), NEW.f1);

CREATE TRIGGER tb1_eng1_del AFTER DELETE ON tb1_eng1 FOR EACH ROW 
   INSERT INTO tb1_logs (dt1, entry_dsc, f4)
   VALUES (NOW(), CONCAT('Delete row ', OLD.f1,' ', OLD.f2, ' ', 
           OLD.f3, ' (tb1_eng1)'), OLD.f1);

CREATE TRIGGER tb1_eng2_ins AFTER INSERT ON tb1_eng2 FOR EACH ROW 
   INSERT INTO tb1_logs (dt1, entry_dsc, f4)
   VALUES (NOW(), CONCAT('Insert row ', NEW.f1,' ',
           NEW.f2, ' ', NEW.f3, ' (tb1_eng2)'), NEW.f1);

CREATE TRIGGER tb1_eng2_upd AFTER update ON tb1_eng2 FOR EACH ROW 
   INSERT INTO tb1_logs (dt1, entry_dsc, f4)
   VALUES (NOW(), CONCAT('Update row ', OLD.f1,' ', OLD.f2, '->', 
           NEW.f2, ' ', OLD.f3, '->', NEW.f3, ' (tb1_eng2)'), NEW.f1);

CREATE TRIGGER tb1_eng2_del AFTER DELETE ON tb1_eng2 FOR EACH ROW 
   INSERT INTO tb1_logs (dt1, entry_dsc, f4)
   VALUES (NOW(), CONCAT('Delete row ', OLD.f1,' ', OLD.f2, ' ', 
           OLD.f3, ' (tb1_eng2)'), OLD.f1);

}

#----------------------------------
# Creating functions and procedures
#----------------------------------
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 'tb0_master' table for rows where f1 = 'num_pr'
# and for each row INSERTs a row in 'tb0_eng1'
#---------------------------------------------------------------------
CREATE PROCEDURE ins_tb0_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 tb0_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 tb0_eng1 (f1, f2, f3, f4) 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 scans 'tb1_master' table for rows where f1 = 'num_pr'
# and for each row INSERTs a row in 'tb1_eng1'
#---------------------------------------------------------------------
CREATE PROCEDURE ins_tb1_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 tb1_eng1 (f1, f2, f3, f4) 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 scans 'tb0_master' table for rows where f1 = 'num_pr'
# and for each row inserts a row in 'tb0_eng2'
#---------------------------------------------------------------------
CREATE PROCEDURE ins_tb0_eng2 (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 tb0_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 tb0_eng2 (f1, f2, f3, f4) 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 scans 'tb1_master' table for rows where f1 = 'num_pr'
# and for each row inserts a row in 'tb1_eng2'
#---------------------------------------------------------------------
CREATE PROCEDURE ins_tb1_eng2 (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 tb1_eng2 (f1, f2, f3, f4) 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 'tb0_logs' and inserts the 
# count into the table
#------------------------------------------------------------------
CREATE PROCEDURE slct_tb0_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 tb0_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 tb0_logs (dt1, entry_dsc, f4)
     VALUES (NOW(), CONCAT('Number of \'', str_val, '\' rows is: ', 
             v_count, ' (tb1_log)'),0);
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//

# This procedure is used to trim the log tables to a constant size  
# by removing 'del_rows' lines every time the number of rows exceeds 
# 'threshold' rows
#------------------------------------------------------------------
CREATE PROCEDURE remove_log_rows (threshold INT, shrink_to INT)
BEGIN
  DECLARE cur_row_num INT;
  DECLARE del_row_id INT;
  DECLARE cur_min INT;

  SELECT COUNT(*) INTO cur_row_num FROM tb0_logs;
  IF cur_row_num > threshold THEN
     SELECT MIN(i1) INTO cur_min FROM tb0_logs;
     SET del_row_id = cur_row_num - shrink_to + cur_min;
     DELETE FROM tb0_logs WHERE i1 < del_row_id;
  END IF;

  SELECT COUNT(*) INTO cur_row_num FROM tb1_logs;
  IF cur_row_num > threshold THEN
     SELECT MIN(i1) INTO cur_min FROM tb1_logs;
     SET del_row_id = cur_row_num - shrink_to + cur_min;
     DELETE FROM tb1_logs WHERE i1 < del_row_id;
  END IF;
END//

# This procedure reports the summary of rows in the tb1 tables
#------------------------------------------------------------------
CREATE PROCEDURE report_summary ()
BEGIN
  DECLARE v_row_count INT;
  DECLARE v_row_start INT;
  DECLARE v_row_end INT;

  SELECT COUNT(*) INTO v_row_count FROM tb0_logs;
  SELECT MIN(i1)  INTO v_row_start FROM tb0_logs;
  SELECT MAX(i1)  INTO v_row_end   FROM tb0_logs;
  INSERT INTO test_stat VALUES (NOW(), "tb0_logs", v_row_count, v_row_start, v_row_end);
  SELECT COUNT(*) INTO v_row_count FROM tb0_eng1;
  SELECT MIN(i1)  INTO v_row_start FROM tb0_eng1;
  SELECT MAX(i1)  INTO v_row_end   FROM tb0_eng1;
  INSERT INTO test_stat VALUES (NOW(), "tb0_eng1", v_row_count, v_row_start, v_row_end);
  SELECT COUNT(*) INTO v_row_count FROM tb0_eng2;
  SELECT MIN(i1)  INTO v_row_start FROM tb0_eng2;
  SELECT MAX(i1)  INTO v_row_end   FROM tb0_eng2;
  INSERT INTO test_stat VALUES (NOW(), "tb0_eng2", v_row_count, v_row_start, v_row_end);

  SELECT COUNT(*) INTO v_row_count FROM tb1_logs;
  SELECT MIN(i1)  INTO v_row_start FROM tb1_logs;
  SELECT MAX(i1)  INTO v_row_end   FROM tb1_logs;
  INSERT INTO test_stat VALUES (NOW(), "tb1_logs", v_row_count, v_row_start, v_row_end);
  SELECT COUNT(*) INTO v_row_count FROM tb1_eng1;
  SELECT MIN(i1)  INTO v_row_start FROM tb1_eng1;
  SELECT MAX(i1)  INTO v_row_end   FROM tb1_eng1;
  INSERT INTO test_stat VALUES (NOW(), "tb1_eng1", v_row_count, v_row_start, v_row_end);
  SELECT COUNT(*) INTO v_row_count FROM tb1_eng2;
  SELECT MIN(i1)  INTO v_row_start FROM tb1_eng2;
  SELECT MAX(i1)  INTO v_row_end   FROM tb1_eng2;
  INSERT INTO test_stat VALUES (NOW(), "tb1_eng2", v_row_count, v_row_start, v_row_end);
END//

delimiter ;//