

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_actions (
  `creation_time` datetime NOT NULL,
  `action_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category` varchar(256) NOT NULL,
  `type` varchar(256) NOT NULL,
  `start_time` datetime DEFAULT NULL,
  `node_list` varchar(256) DEFAULT '|',
  `mutex` varchar(256) DEFAULT NULL,
  `task_id` int(10) unsigned DEFAULT NULL,
  `database_id` int(10) unsigned DEFAULT NULL,
  `status` varchar(256) DEFAULT NULL,
  `command` blob,
  PRIMARY KEY (`action_id`),
  KEY `ymactions_database` (`database_id`)
) ENGINE=SPIDER AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='wrapper "mysql", table "ym_actions", srv "ticket_tokudb_1 ticket_tokudb_2 ticket_tokudb_3 ticket_tokudb_4", mbk "1", mkd "1", link_status  "1 1 1 1"';


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


select spider_flush_table_mon_cache();

DELIMITER $$
CREATE PROCEDURE ticket_action_event_loop()
BEGIN
    DECLARE x  INT;
    DECLARE nb_actions  INT;
    DECLARE nothing_todo BOOLEAN;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT @last_query;
        SHOW ERRORS;
    END;

    SELECT "" INTO @last_query;

    SET x = 1;
    WHILE x  <= 50000 DO
        # Start the process
        SELECT 1 INTO @wakeup FROM dual;
        SELECT now() INTO @new_date;
        SELECT @@hostname INTO @host;
        SELECT CONCAT('Spider-',@host,'-',connection_id()) INTO @mutex;
        
        SELECT "IF EXISTS (SELECT action_id FROM ym_actions WHERE status='READY' AND mutex IS NULL AND category='EXECUTION' AND type IN ('OLD_DATAPUMP','DATAPUMP','YIELDMONITOR','YIELDMONITOR_RDB','SPECMONITOR','REPORTING','STATUS','CONVERTER','OUTLIER_REMOVAL','AUTOADMIN','TRIGGERPUMP','PATPUMP','INCREMENTAL_UPDATE') )" INTO @last_query;

        IF EXISTS (SELECT action_id FROM ym_actions WHERE status='READY' AND mutex IS NULL AND category='EXECUTION' AND type IN ('OLD_DATAPUMP','DATAPUMP','YIELDMONITOR','YIELDMONITOR_RDB','SPECMONITOR','REPORTING','STATUS','CONVERTER','OUTLIER_REMOVAL','AUTOADMIN','TRIGGERPUMP','PATPUMP','INCREMENTAL_UPDATE') )
        THEN
            # Have some action to do
            SET nothing_todo = FALSE;
            WHILE nothing_todo = FALSE DO
                SELECT 1 INTO @wakeup FROM dual;
                SELECT now() INTO @new_date;
                SELECT "IF EXISTS (SELECT action_id FROM ym_actions WHERE status='READY' AND mutex IS NULL AND category='EXECUTION' AND type IN ('OLD_DATAPUMP','DATAPUMP','YIELDMONITOR','YIELDMONITOR_RDB','SPECMONITOR','REPORTING','STATUS','CONVERTER','OUTLIER_REMOVAL','AUTOADMIN','TRIGGERPUMP','PATPUMP','INCREMENTAL_UPDATE') ORDER BY action_id LIMIT 1)" INTO @last_query;

                IF EXISTS (SELECT action_id FROM ym_actions WHERE status='READY' AND mutex IS NULL AND category='EXECUTION' AND type IN ('OLD_DATAPUMP','DATAPUMP','YIELDMONITOR','YIELDMONITOR_RDB','SPECMONITOR','REPORTING','STATUS','CONVERTER','OUTLIER_REMOVAL','AUTOADMIN','TRIGGERPUMP','PATPUMP','INCREMENTAL_UPDATE') ORDER BY action_id LIMIT 1)
                THEN
                    # Have an action
                    # try to lock it
                    SELECT "UPDATE ym_actions SET start_time=@new_date,node_list=concat(node_list,'|',@mutex),mutex=@mutex,status='RUNNING' WHERE status='READY' AND mutex IS NULL AND category='EXECUTION' AND type IN ('OLD_DATAPUMP','DATAPUMP','YIELDMONITOR','YIELDMONITOR_RDB','SPECMONITOR','REPORTING','STATUS','CONVERTER','OUTLIER_REMOVAL','AUTOADMIN','TRIGGERPUMP','PATPUMP','INCREMENTAL_UPDATE')  LIMIT 1;" INTO @last_query;

                    UPDATE ym_actions SET start_time=@new_date,node_list=concat(node_list,'|',@mutex),mutex=@mutex,status='RUNNING' WHERE status='READY' AND mutex IS NULL AND category='EXECUTION' AND type IN ('OLD_DATAPUMP','DATAPUMP','YIELDMONITOR','YIELDMONITOR_RDB','SPECMONITOR','REPORTING','STATUS','CONVERTER','OUTLIER_REMOVAL','AUTOADMIN','TRIGGERPUMP','PATPUMP','INCREMENTAL_UPDATE')  LIMIT 1;
                    COMMIT;
                    # Check if the action was locked
                    SELECT NULL INTO @new_action;
                    SELECT concat('%',@mutex,'%') INTO @like_node;
                    SELECT "SELECT action_id INTO @new_action FROM ym_actions WHERE mutex=@mutex AND node_list LIKE @like_node AND status='RUNNING' LIMIT 1;" INTO @last_query;

                    SELECT action_id INTO @new_action FROM ym_actions WHERE mutex=@mutex AND node_list LIKE @like_node AND status='RUNNING' LIMIT 1;
                    IF (@new_action IS NOT NULL)
                    THEN
                        # Get some info
                        SELECT "SELECT command INTO @new_command FROM ym_actions WHERE action_id=@new_action;" INTO @last_query;

                        SELECT command INTO @new_command FROM ym_actions WHERE action_id=@new_action;
                        SELECT "INSERT INTO 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,CONNECTION_ID(),null,4,2,@new_command,'START','Start','');" INTO @last_query;

                        INSERT INTO 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,CONNECTION_ID(),null,4,2,@new_command,'START','Start','');

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

                        SELECT LAST_INSERT_ID() INTO @new_link;
                        
                        # Instances executes some other process here
                        # Wait random between 0 and 5s
                        SELECT SLEEP((0.1+RAND())*5) INTO @sleep;
                        
                        # Add a INFO
                        SELECT "INSERT INTO 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,0,4516786,CONNECTION_ID(),null,4,2,@new_command,'INFO','Info','');" INTO @last_query;

                        INSERT INTO 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,0,4516786,CONNECTION_ID(),null,4,2,@new_command,'INFO','Info','');

                        
                        # Then update and remove the action
                        SELECT 1 INTO @wakeup FROM dual;
                        SELECT "UPDATE ym_actions SET command=concat(command,'|SUCCESS') WHERE action_id=@new_action;" INTO @last_query;

                        UPDATE ym_actions SET command=concat(command,'|SUCCESS') WHERE action_id=@new_action;
                        COMMIT;
                        SELECT 1 INTO @wakeup FROM dual;
                        SELECT "UPDATE ym_actions SET mutex=NULL, status='PASS' WHERE action_id=@new_action;" INTO @last_query;

                        UPDATE ym_actions SET mutex=NULL, status='PASS' WHERE action_id=@new_action;
                        COMMIT;
                        SELECT "DELETE FROM ym_actions WHERE action_id=@new_action;" INTO @last_query;

                        DELETE FROM ym_actions WHERE action_id=@new_action;
                        COMMIT;
                        # Wait random between 0 and 1s Move the file
                        SELECT SLEEP((0.1+RAND())) INTO @sleep;

                        SELECT "INSERT INTO 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,CONNECTION_ID(),null,4,2,@new_command,'PASS','End','');" INTO @last_query;

                        INSERT INTO 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,CONNECTION_ID(),null,4,2,@new_command,'PASS','End','');

                    END IF;
                ELSE
                    SET nothing_todo = TRUE;
                END IF;
            END WHILE;
        ELSE
            # Nothing to do
            # Check if the first line is present
            SELECT 0 INTO @new_value;
            SELECT "SELECT count(*) INTO @new_value FROM ym_actions WHERE category='ADMINISTRATION';" INTO @last_query;

            SELECT count(*) INTO @new_value FROM ym_actions WHERE category='ADMINISTRATION';
            IF (@new_value = 0)
            THEN
                SELECT "INSERT IGNORE INTO ym_actions VALUES(@new_date,1,'ADMINISTRATION','MASTER_YM',@new_date,'|',null,null,null,'READY','MASTER_YM');" INTO @last_query;

                INSERT IGNORE INTO ym_actions VALUES(@new_date,1,'ADMINISTRATION','MASTER_YM',@new_date,'|',null,null,null,'READY','MASTER_YM');
                SELECT "INSERT IGNORE INTO ym_actions VALUES(@new_date,2,'ADMINISTRATION','MASTER_PAT',@new_date,'|',null,null,null,'READY','MASTER_PAT');" INTO @last_query;

                INSERT IGNORE INTO ym_actions VALUES(@new_date,2,'ADMINISTRATION','MASTER_PAT',@new_date,'|',null,null,null,'READY','MASTER_PAT');
            END IF;
            # Select all mutex and check if always active
            SELECT "SELECT count(DISTINCT mutex) INTO @new_value FROM ym_actions WHERE category='ADMINISTRATION' AND mutex IS NOT NULL;" INTO @last_query;

            SELECT count(DISTINCT mutex) INTO @new_value FROM ym_actions WHERE category='ADMINISTRATION' AND mutex IS NOT NULL;
            # Then try to update and get the master role
            SELECT "UPDATE ym_actions SET mutex=@mutex,status='RUNNING',start_time=@new_date,node_list=concat('|',@mutex) WHERE mutex IS NULL AND status='READY' AND category='ADMINISTRATION' AND type='MASTER_YM';" INTO @last_query;

            UPDATE ym_actions SET mutex=@mutex,status='RUNNING',start_time=@new_date,node_list=concat('|',@mutex) WHERE mutex IS NULL AND status='READY' AND category='ADMINISTRATION' AND type='MASTER_YM';
            # Check if have the MASTER role
            SELECT 0 INTO @new_value;
            SELECT "SELECT count(*) INTO @new_value FROM ym_actions WHERE mutex=@mutex AND status='RUNNING' AND type='MASTER_YM';" INTO @last_query;

            SELECT count(*) INTO @new_value FROM ym_actions WHERE mutex=@mutex AND status='RUNNING' AND type='MASTER_YM';
            
            IF (@new_value > 0)
            THEN
                # Clean actions
                SELECT "SELECT count(DISTINCT mutex) INTO @new_value FROM ym_actions WHERE mutex IS NOT NULL AND category='EXECUTION';" INTO @last_query;

                SELECT count(DISTINCT mutex) INTO @new_value FROM ym_actions WHERE mutex IS NOT NULL AND category='EXECUTION';
                SELECT "SELECT count(*) INTO @new_value from ym_actions where category='EXECUTION';" INTO @last_query;

                SELECT count(*) INTO @new_value from ym_actions where category='EXECUTION';
                COMMIT;

                # Wait random between 0 and 10s to retreive the list of files
                SELECT SLEEP((0.1+RAND())*10) INTO @sleep;

                # Insert 100 new actions
                SET nb_actions = 1;
                WHILE nb_actions <= 100 DO
                    SELECT CONCAT('Command-',nb_actions,'-Action') INTO @new_command;
                    IF (nb_actions < @new_value)
                    THEN
                        # Try to insert a file already into the list
                        SELECT CONCAT('Command-',101-nb_actions,'-Action') INTO @new_command;
                    END IF;
                    
                    SELECT "IF NOT EXISTS (SELECT status FROM ym_actions WHERE command=@new_command)" INTO @last_query;

                    IF NOT EXISTS (SELECT status FROM ym_actions WHERE command=@new_command)
                    THEN
                        SELECT "INSERT INTO ym_actions(creation_time,category,type,start_time,node_list,mutex,task_id,database_id,status,command) VALUES(@new_date,'EXECUTION','DATAPUMP',null,'|',null,1,1,'READY',@new_command);" INTO @last_query;

                        INSERT INTO ym_actions(creation_time,category,type,start_time,node_list,mutex,task_id,database_id,status,command)
                            VALUES(@new_date,'EXECUTION','DATAPUMP',null,'|',null,1,1,'READY',@new_command);
                        COMMIT;
                    END IF;
                    SET nb_actions = nb_actions + 1;
                END WHILE;
                SELECT "INSERT IGNORE INTO ym_actions VALUES(@new_date,1,'ADMINISTRATION','MASTER_YM',@new_date,'|',null,null,null,'READY','MASTER_YM');" INTO @last_query;

                INSERT IGNORE INTO ym_actions VALUES(@new_date,1,'ADMINISTRATION','MASTER_YM',@new_date,'|',null,null,null,'READY','MASTER_YM');
                SELECT "INSERT IGNORE INTO ym_actions VALUES(@new_date,2,'ADMINISTRATION','MASTER_PAT',@new_date,'|',null,null,null,'READY','MASTER_PAT');" INTO @last_query;

                INSERT IGNORE INTO ym_actions VALUES(@new_date,2,'ADMINISTRATION','MASTER_PAT',@new_date,'|',null,null,null,'READY','MASTER_PAT');
                SELECT "UPDATE ym_actions SET mutex=null,status='READY',creation_time=now() WHERE mutex=@mutex AND status='RUNNING' AND type='MASTER_YM' AND category='ADMINISTRATION';" INTO @last_query;

                UPDATE ym_actions SET mutex=null,status='READY',creation_time=now() WHERE mutex=@mutex AND status='RUNNING' AND type='MASTER_YM' AND category='ADMINISTRATION';
                COMMIT;
            END IF;
        END IF; 
    
        SELECT SLEEP(1) INTO @sleep;

        SET  x = x + 1; 
    END WHILE;

END$$
DELIMITER ;

