/*
MySQL events are executed by a special event-scheduler thread. 
Display event-scheduler current state (must have 'super' user, or DBA privileges): 
SHOW PROCESSLIST; 

Default state of event-scheduler thread is OFF. 
Before creating event, event-scheduler must be enabled (i.e., turned on):
SET GLOBAL event_scheduler = ON;

Two Types:
AT clause (one-time): timestamp used for a one-time event. 
Specifies event executes one time only at date and time given by timestamp.

EVERY clause (recurring):  may contain optional STARTS and optional ENDS clauses. 
Both clauses followed by timestamp value that indicates when the action should begin/stop repeating.

NOTE: Statements such as SELECT or SHOW that only return a result set have no effect in events; 
output is not sent to MySQL Monitor, nor stored. 
*/

-- 1) Important: invoke following statements *BEFORE* creating events!
-- check if event scheduler on 
SHOW VARIABLES LIKE 'event_scheduler';

-- 2) If not, turn on event scheduler (using "set" applies only until server restarted)
-- must change configuration file to make change permanent 
SET GLOBAL event_scheduler = ON;

-- generic syntax:
CREATE EVENT [IF NOT EXISTS ] event_name 
ON SCHEDULE schedule 
[ON COMPLETION [ NOT ] PRESERVE ] 
[ENABLED | DISABLED ] 
[COMMENT 'comment' ] 
DO sql_statement;

-- NOTES: 
-- Normally, once an event has expired, it is immediately dropped. 
-- Override behavior by specifying ON COMPLETION PRESERVE. Using NOT explicitly makes event nonpersistent (default)

-- Prevent event from being active using DISABLE keyword. 
-- Alternatively, ENABLE to make explicit default status, which is active.

-- 3) create my_table
DROP TABLE IF EXISTS my_table;
CREATE TABLE IF NOT EXISTS my_table 
(created TIMESTAMP);

-- 4) Create stored procedure to be called in event (makes testing and debugging easier)
drop procedure if exists my_proc;
DELIMITER //
CREATE PROCEDURE my_proc()
BEGIN
  insert into my_table values (now());
END //
DELIMITER ;

CALL my_proc();
-- drop procedure if exists my_proc;


-- 5) AT event example: one-time event
-- Demo: display current time in 5 seconds only once
DROP EVENT IF EXISTS my_single_event;

-- temporarily redefine delimiter
DELIMITER //
CREATE EVENT IF NOT EXISTS my_single_event
ON SCHEDULE 
  AT NOW() + INTERVAL 5 SECOND
COMMENT 'inserts current time in 5 seconds'
DO 
  BEGIN 
    CALL my_proc();
  END//
-- change delimiter back 
DELIMITER ;

select * from my_table;
delete from my_table;
select * from my_table;

-- SHOW EVENTS FROM database_name;
SHOW EVENTS FROM test; -- empty when completed
show processlist; --  Waiting for next activation or  Waiting on empty queue when completed

-- 6) EVERY event example: recurring event
-- Demo: inserts current time every 5 seconds, beginning 5 seconds after creation, ending in 30 seconds (total: 6 times)
DROP EVENT IF EXISTS my_recurring_event;
-- temporarily redefine delimiter
DELIMITER //
CREATE EVENT IF NOT EXISTS my_recurring_event 
ON SCHEDULE 
  EVERY 5 SECOND
STARTS NOW() + INTERVAL 5 SECOND
ENDS NOW() + INTERVAL 30 SECOND
COMMENT 'inserts current time every 5 seconds' 
DO 
  BEGIN 
    CALL my_proc();
  END//

-- change delimiter back 
DELIMITER ;

-- SHOW EVENTS FROM database_name;
SHOW EVENTS FROM test; -- empty when completed
show processlist; --  Waiting for next activation or  Waiting on empty queue when completed

delete from my_table;
select * from my_table;

/*
-- sample result set
mysql> select * from my_table;
+---------------------+
| created             |
+---------------------+
| 2014-06-19 07:49:05 |
| 2014-06-19 07:49:10 |
| 2014-06-19 07:49:15 |
| 2014-06-19 07:49:20 |
| 2014-06-19 07:49:25 |
| 2014-06-19 07:49:30 |
+---------------------+
6 rows in set (0.00 sec)
*/