# 20 instances of tools that run on a SPIDER/TOKUDB environment
# 3 SPIDER nodes
#                HEAD(192.168.1.100)
#    COMP01(192.168.1.101)                  COMP02(192.168.1.102)
# HEAD for SPIDER monitoring
# COMP01 and COMP02 run each 10 instances of the tools
#
# 4 TOKUDB nodes
#    DB01(192.168.1.111)    DB02(192.168.1.112)    DB03(192.168.1.113)    DB04(192.168.1.114)
# contains each the replication of the table

CREATE DATABASE ticket_db;
use ticket_db;



GRANT GRANT OPTION ON *.* TO 'user_spider'@'localhost';
GRANT GRANT OPTION ON *.* TO 'user_spider'@'%';


GRANT GRANT OPTION ON ticket_db.* TO 'user_spider'@'localhost';
GRANT GRANT OPTION ON ticket_db.* TO 'user_spider'@'%';


FLUSH PRIVILEGES;
    
    
DROP SERVER IF EXISTS ticket_spider_1;

DROP SERVER IF EXISTS ticket_spider_2;

DROP SERVER IF EXISTS ticket_spider_3;


DROP SERVER IF EXISTS ticket_tokudb_1;

DROP SERVER IF EXISTS ticket_tokudb_2;

DROP SERVER IF EXISTS ticket_tokudb_3;

DROP SERVER IF EXISTS ticket_tokudb_4;




CREATE TABLE `ym_events` (
  `creation_time` datetime NOT NULL,
  `event_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `link` bigint(20) unsigned DEFAULT NULL,
  `category` varchar(256) NOT NULL,
  `type` varchar(256) NOT NULL,
  `start_time` datetime DEFAULT NULL,
  `duration` int(10) unsigned DEFAULT '0',
  `size` int(10) unsigned DEFAULT '0',
  `node_id` int(10) unsigned DEFAULT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `task_id` int(10) unsigned DEFAULT NULL,
  `database_id` int(10) unsigned DEFAULT NULL,
  `command` varchar(1024) DEFAULT NULL,
  `status` varchar(256) DEFAULT NULL,
  `summary` mediumblob,
  `comment` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`event_id`,`creation_time`),
  KEY `ymevents_database` (`database_id`),
  KEY `ymevents_node` (`node_id`),
  KEY `ymevents_user` (`user_id`),
  KEY `ymevents_task` (`task_id`),
  KEY `ymevents_status` (`status`),
  KEY `ymevents_link` (`link`),
  KEY `ymevents_category` (`category`),
  KEY `ymevents_type` (`type`),
  KEY `ymevents_starttime` (`start_time`)
) ENGINE=SPIDER AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='wrapper "mysql", table "ym_events", srv "ticket_tokudb_1 ticket_tokudb_2 ticket_tokudb_3 ticket_tokudb_4", mbk "1", mkd "1", link_status  "1 1 1 1"';

# use ticket_db;
# DROP PROCEDURE ticket_event_loop;
DELIMITER $$
CREATE PROCEDURE ticket_event_loop()
BEGIN
    DECLARE x  INT;
    SET x = 1;
    WHILE x  <= 50000 DO
        # Start the process
        SELECT now() INTO @new_date;
        SELECT @@hostname INTO @host;
        SELECT CONCAT('Spider-',@host,'-',connection_id(),'-',x) INTO @new_file;
        INSERT INTO ticket_db.ym_events(creation_time,link,category,type,start_time,duration,size,node_id,user_id,task_id,database_id,command,status,summary,comment) 
            VALUES(NOW(),NULL,'EXECUTION','DATAPUMP',@new_date,0,4516786,3,null,4,2,@new_file,'START','Start','');

        # Get the AUTO-INCREMENT to store into the next query
        SELECT LAST_INSERT_ID() INTO @new_link;

        # Instances executes some other process here
        # Wait random between 0 and 5s
        SELECT SLEEP(RAND()) INTO @seelp;

        # Finalize the process
        INSERT INTO ticket_db.ym_events(creation_time,link,category,type,start_time,duration,size,node_id,user_id,task_id,database_id,command,status,summary,comment) 
            VALUES(NOW(),@new_link,'EXECUTION','DATAPUMP',@new_date,UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(@new_date),4516786,3,null,4,2,@new_file,'PASS','End','');

        SELECT SLEEP(RAND()) INTO @seelp;

        SET  x = x + 1; 
    END WHILE;

END$$
DELIMITER ;
