{"id":614,"date":"2009-05-06T17:58:41","date_gmt":"2009-05-06T16:58:41","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=614"},"modified":"2009-06-16T10:59:36","modified_gmt":"2009-06-16T09:59:36","slug":"le-programmateur-devenements-event-scheduler-part-36","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2009\/05\/06\/le-programmateur-devenements-event-scheduler-part-36\/","title":{"rendered":"Le programmateur d’\u00e9v\u00e9nements ( Event Scheduler ) (part 3\/6)"},"content":{"rendered":"

<\/p>\n

(<- pr\u00e9c\u00e9dent<\/a>)<\/address>\n

<\/p>\n

G\u00e9rer les \u00e9v\u00e8nements<\/h4>\n

MySQL propose plusieurs m\u00e9thodes pour visualiser les \u00e9v\u00e8nements. La m\u00e9thode la plus pratique consiste \u00e0 aller chercher l’information dans la table event<\/em> de la base de donn\u00e9es des m\u00e9ta-donn\u00e9es information_schema<\/em>:<\/p>\n

mysql> SELECT * FROM information_schema.EVENTS;<\/address>\n
Empty set (0.00 sec)<\/address>\n

L’\u00e9v\u00e8nement premier_event<\/em> cr\u00e9\u00e9 pr\u00e9c\u00e9demment n’y appara\u00eet pas. C’est tout \u00e0 fait normal, un \u00e9v\u00e9nement \u00e0 ex\u00e9cution unique n’est par d\u00e9faut pas conserv\u00e9 par MySQL. Modifions notre \u00e9v\u00e9nement pour qu’il puisse en rester une trace apr\u00e8s son ex\u00e9cution:
\n<\/p>\n

mysql> CREATE EVENT _event.premier_event_persistant<\/address>\n
ON SCHEDULE AT CURRENT_TIMESTAMP<\/address>\n
ON COMPLETION PRESERVE<\/address>\n
COMMENT ‘Insertion des donn\u00e9es dans la table’<\/address>\n
DO<\/address>\n
INSERT INTO _event.insert_event ( moment, fois, action ) VALUES (now(), 0, ‘Mon premier event persistant’);<\/address>\n
<\/address>\n
<\/address>\n
mysql> SELECT * FROM information_schema.EVENTS \\G<\/address>\n
*************************** 1. row ***************************<\/address>\n
EVENT_CATALOG: NULL<\/address>\n
EVENT_SCHEMA: _event<\/address>\n
EVENT_NAME: premier_event_persistant<\/address>\n
DEFINER: daz@localhost<\/address>\n
TIME_ZONE: SYSTEM<\/address>\n
EVENT_BODY: SQL<\/address>\n
EVENT_DEFINITION: INSERT INTO _event.insert_event ( moment, fois, action ) VALUES (now(), 0, ‘Mon premier event persistant’)<\/address>\n
EVENT_TYPE: ONE TIME<\/address>\n
EXECUTE_AT: 2008-05-29 21:10:04<\/address>\n
INTERVAL_VALUE: NULL<\/address>\n
INTERVAL_FIELD: NULL<\/address>\n
SQL_MODE:<\/address>\n
STARTS: NULL<\/address>\n
ENDS: NULL<\/address>\n
STATUS: ENABLED<\/address>\n
ON_COMPLETION: PRESERVE<\/address>\n
CREATED: 2008-05-29 21:10:04<\/address>\n
LAST_ALTERED: 2008-05-29 21:10:04<\/address>\n
LAST_EXECUTED: NULL<\/address>\n
EVENT_COMMENT: Insertion des donn\u00e9es dans la table<\/address>\n
ORIGINATOR: 3307<\/address>\n
CHARACTER_SET_CLIENT: utf8<\/address>\n
COLLATION_CONNECTION: utf8_general_ci<\/address>\n
DATABASE_COLLATION: latin1_swedish_ci<\/address>\n


\nMySQL propose \u00e9galement deux autres commandes pour avoir des informations sur les \u00e9v\u00e8nements:<\/p>\n

SHOW EVENTS et SHOW CREATE EVENT:
\n<\/p>\n

mysql> SHOW EVENTS IN _event;<\/address>\n
*************************** 1. row ***************************<\/address>\n
Db: _event<\/address>\n
Name: premier_event_persistant<\/address>\n
Definer: daz@localhost<\/address>\n
Time zone: SYSTEM<\/address>\n
Type: ONE TIME<\/address>\n
Execute at: 2008-05-29 21:10:04<\/address>\n
Interval value: NULL<\/address>\n
Interval field: NULL<\/address>\n
Starts: NULL<\/address>\n
Ends: NULL<\/address>\n
Status: ENABLED<\/address>\n
Originator: 3307<\/address>\n
character_set_client: utf8<\/address>\n
collation_connection: utf8_general_ci<\/address>\n
Database Collation: latin1_swedish_ci<\/address>\n
<\/address>\n
<\/address>\n
<\/address>\n
mysql> SHOW CREATE EVENT _event.premier_event_persistant;<\/address>\n
*************************** 1. row ***************************<\/address>\n
Event: premier_event_persistant<\/address>\n
sql_mode:<\/address>\n
time_zone: SYSTEM<\/address>\n
Create Event: CREATE EVENT ‘premier_event_persistant’ ON SCHEDULE AT ‘2008-<\/address>\n
05-29 21:10:04′ ON COMPLETION PRESERVE ENABLE COMMENT ‘Insertion des donn\u00e9es dans la table DO INSERT INTO _event.insert_event ( moment,<\/address>\n
fois, action ) VALUES (now(), 0, ‘Mon premier event persistant’)<\/address>\n
character_set_client: utf8<\/address>\n
collation_connection: utf8_general_ci<\/address>\n
Database Collation: latin1_swedish_ci<\/address>\n


\nA noter, qu’il est \u00e9galement possible de lire l’information dans la table event<\/em> de la base de donn\u00e9es mysql<\/em> avec la requ\u00eate suivante: SELECT * FROM event .<\/p>\n

Vous avez aussi la possibilit\u00e9 de modifier la structure d’un \u00e9v\u00e9nement, le renommer, ou encore changer son code avec la commande: ALTER EVENT<\/em>
\n<\/p>\n

ALTER EVENT<\/address>\n
[DEFINER = { user | CURRENT_USER }]<\/address>\n
event_name<\/address>\n
[ON SCHEDULE schedule]<\/address>\n
[RENAME TO new_event_name]<\/address>\n
[ON COMPLETION [NOT] PRESERVE]<\/address>\n
[ENABLE | DISABLE | DISABLE ON SLAVE]<\/address>\n
[COMMENT ‘comment’]<\/address>\n
[DO sql_statement]<\/address>\n


\nOn retrouve les clauses de la commande CREATE EVENT<\/em>, que l’on peut donc modifier ais\u00e9ment avec ALTER EVENT<\/em>. Le dernier utilisateur qui modifie un \u00e9v\u00e9nement devient son DEFINER. Par exemple si un \u00e9v\u00e8nement \u00e0 pour DEFINER<\/em> daz@localhost<\/strong>. Une modification de cet \u00e9v\u00e9nement, par exemple le d\u00e9sactiver (DISABLE<\/em>), avec l’utilisateur root@localhost<\/strong> mettra le DEFINER<\/em> de l’\u00e9v\u00e8nement \u00e0 la valeur root@localhost<\/em>.<\/p>\n

D\u00e9sactiver un \u00e9v\u00e8nement<\/span>
\n<\/p>\n

mysql> ALTER EVENT _event.vue_materialisee DISABLE;<\/address>\n


\nIl est bien entendu possible d’effacer un \u00e9v\u00e9nement. Ceci se fait avec la commande DROP EVENT<\/em>.
\n<\/p>\n

DROP EVENT [IF EXISTS] nom_\u00e9v\u00e8nement<\/address>\n


\nEffacer un \u00e9v\u00e8nement<\/span>
\n<\/p>\n

mysql> DROP EVENT _event.vue_materialisee;<\/address>\n


\n(\u00e0 suivre… event scheduler: Quelques exemples d’\u00e9v\u00e8nements<\/a>)<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"

G\u00e9rer les \u00e9v\u00e8nements<\/p>\n

MySQL propose plusieurs m\u00e9thodes pour visualiser les \u00e9v\u00e8nements. La m\u00e9thode la plus pratique consiste \u00e0 aller chercher l’information dans la table event de la base de donn\u00e9es des m\u00e9ta-donn\u00e9es information_schema:
\nmysql> SELECT * FROM information_schema.EVENTS;<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","jetpack_post_was_ever_published":false},"categories":[8],"tags":[104,65,122],"class_list":["post-614","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-event","tag-event-scheduler","tag-programmateur-devenements"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t