CREATE DATABASE systest1;
USE systest1;

DELIMITER \\;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `fill_tb1_aux`(row_count INTEGER)
BEGIN
DECLARE my_val INTEGER DEFAULT 1;
TRUNCATE tb1_aux;
TRUNCATE tb1_aux;
TRUNCATE tb1_aux;
##################SELECT 0;
END */\\
DELIMITER ;\\

# rest works if there are three # before "SELECT 0" above. Adding more # causes it to fail
# with different symptomps.

DELIMITER \\;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 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 */\\
DELIMITER ;\\

DELIMITER \\;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 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 */\\
DELIMITER ;\\

DELIMITER \\;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 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 */\\
DELIMITER ;\\

DELIMITER \\;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 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 */\\
DELIMITER ;\\

DELIMITER \\;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 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 */\\
DELIMITER ;\\

DELIMITER \\;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 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 ;\\

BACKUP DATABASE systest1 TO 'systest1.bak';

RESTORE FROM 'systest1.bak' OVERWRITE;