{"id":622,"date":"2009-06-16T10:58:03","date_gmt":"2009-06-16T09:58:03","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=622"},"modified":"2009-06-24T12:59:58","modified_gmt":"2009-06-24T11:59:58","slug":"le-programmateur-devenements-event-scheduler-part-46","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2009\/06\/16\/le-programmateur-devenements-event-scheduler-part-46\/","title":{"rendered":"Le programmateur d’\u00e9v\u00e9nements ( Event Scheduler ) (part 4\/6)"},"content":{"rendered":"

<\/p>\n

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


\n<\/p>\n

Quelques exemples d’\u00e9v\u00e8nements<\/span><\/h4>\n

\n

Insertions de donn\u00e9es dans une table, toutes les 30 secondes<\/span> <\/span><\/p>\n

\n

mysql> SELECT * FROM _event.insert_event ;<\/span><\/address>\n
<\/address>\n
mysql> DELIMITER \u20ac\u20ac<\/span><\/address>\n
mysql> CREATE EVENT `_event`.`second_event`<\/span><\/address>\n
ON SCHEDULE EVERY 30 SECOND<\/span><\/address>\n
DO<\/span><\/address>\n
BEGIN<\/span><\/address>\n
\/*selectionne la val maximale de la colonne fois. Si elle vaut NULL alors retourne la valeur 0 dans la variable @fois*\/<\/span><\/address>\n
SELECT if(max(fois),max(fois)+1,1) INTO @fois FROM _event.insert_event;<\/span><\/address>\n
<\/span><\/address>\n
\/*Insertion des donn\u00e9es dans la table*\/<\/span><\/address>\n
INSERT INTO _event.insert_event (moment, fois, action) VALUES (now(), @fois, concat(‘Execution ‘, @fois, \u00a0\u00bb de l’event\u00a0\u00bb));<\/span><\/address>\n
END \u20ac\u20ac<\/span><\/address>\n
mysql> DELIMITER ;<\/span><\/address>\n

\n

\n

Cr\u00e9er une vue mat\u00e9rialis\u00e9e<\/span><\/span><\/p>\n

Le concept de vue mat\u00e9rialis\u00e9e n’est pas impl\u00e9ment\u00e9 dans MySQL. Une vue mat\u00e9rialis\u00e9e est un \u00ab\u00a0snapshot\u00a0\u00bb. Ce type de vue permet de stocker le r\u00e9sultat d’une requ\u00eate, contrairement aux vues classiques qui elles, ne stockent que la d\u00e9finition de la requ\u00eate. Les vues mat\u00e9rialis\u00e9es ne r\u00e9executent donc pas la requ\u00eate \u00e0 chaque interrogation. Par contre la gestion de la fra\u00eecheur des donn\u00e9es est \u00e0 la charge de la vue.<\/span><\/p>\n

\n

mysql> DELIMITER \u20ac\u20ac<\/span><\/address>\n
mysql> CREATE EVENT `_event`.`vue_materialisee`<\/span><\/address>\n
ON SCHEDULE EVERY 10 SECOND<\/span><\/address>\n
STARTS ‘2008-03-11 16:32:34’<\/span><\/address>\n
ON COMPLETION NOT PRESERVE ENABLE<\/span><\/address>\n
DO<\/span><\/address>\n
BEGIN<\/span><\/address>\n
\/*Effacement de la table _event.City_fra *\/<\/span><\/address>\n
TRUNCATE TABLE _event.City_fra;<\/span><\/address>\n
\/*Remplassiage de la table _event.City_fra \u00e0 partir de la table _event.City*\/<\/span><\/address>\n
INSERT INTO _event.City_fra<\/span><\/address>\n
SELECT * FROM _event.City WHERE CountryCode=’FRA’<\/span><\/address>\n
ORDER BY name;<\/span><\/address>\n
END \u20ac\u20ac<\/span><\/address>\n
mysql> DELIMITER ;<\/span><\/address>\n

\n

A noter que les commandes TRUNCATE et INSERT ne sont pas atomiques. Il est donc possible qu’une interrogation sur la table _event.City_fra ne renvoie pas de r\u00e9sultat.<\/p>\n

(\u00e0 suivre… event scheduler: Appel d’une proc\u00e9dure stock\u00e9e<\/a>)<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"

Insertions de donn\u00e9es dans une table, toutes les 30 secondes<\/p>\n

mysql> SELECT * FROM _event.insert_event ;
\nmysql> DELIMITER \u20ac\u20ac
\nmysql> CREATE EVENT `_event`.`second_event`
\nON SCHEDULE EVERY 30 SECOND
\nDO
\nBEGIN
\n\/*selectionne la val maximale de la colonne fois. Si elle vaut NULL alors retourne la valeur 0 dans la variable @fois*\/
\nSELECT if(max(fois),max(fois)+1,1) INTO @fois FROM _event.insert_event;
\n\/*Insertion des donn\u00e9es dans la table*\/
\nINSERT INTO _event.insert_event (moment, fois, action) VALUES (now(), @fois, concat(‘Execution ‘, @fois, \u00a0\u00bb de l’event\u00a0\u00bb));
\nEND \u20ac\u20ac
\nmysql> DELIMITER ;<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"categories":[8],"tags":[104,65,122],"class_list":["post-622","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-event","tag-event-scheduler","tag-programmateur-devenements"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p9LfWW-a2","jetpack-related-posts":[{"id":634,"url":"https:\/\/dasini.net\/blog\/2009\/06\/24\/le-programmateur-devenements-event-scheduler-part-56\/","url_meta":{"origin":622,"position":0},"title":"Le programmateur d’\u00e9v\u00e9nements ( Event Scheduler ) (part 5\/6)","author":"Olivier DASINI","date":"24 juin 2009","format":false,"excerpt":"Appel d'une proc\u00e9dure stock\u00e9e Il est possible d'appeler des proc\u00e9dures stock\u00e9es dans un \u00e9v\u00e9nement. mysql> CREATE EVENT `_event`.`appel_ps` ON SCHEDULE AT CURRENT_TIMESTAMP DO \/*Appel de la proc\u00e9dure stock\u00e9e*\/ CALL pro_stock.insert_profil('Olivier', 'DASINI', 'Orange');","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":614,"url":"https:\/\/dasini.net\/blog\/2009\/05\/06\/le-programmateur-devenements-event-scheduler-part-36\/","url_meta":{"origin":622,"position":1},"title":"Le programmateur d’\u00e9v\u00e9nements ( Event Scheduler ) (part 3\/6)","author":"Olivier DASINI","date":"6 mai 2009","format":false,"excerpt":"G\u00e9rer les \u00e9v\u00e8nements 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: mysql> SELECT * FROM information_schema.EVENTS;","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":606,"url":"https:\/\/dasini.net\/blog\/2009\/04\/30\/le-programmateur-devenements-event-scheduler-part-26\/","url_meta":{"origin":622,"position":2},"title":"Le programmateur d’\u00e9v\u00e9nements ( Event Scheduler ) (part 2\/6)","author":"Olivier DASINI","date":"30 avril 2009","format":false,"excerpt":"Cr\u00e9er un \u00e9v\u00e9nement Pour cr\u00e9er notre premier \u00e9v\u00e9nement, un coup d'oeil dans la documentation de MySQL nous donne les informations suivantes:","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":275,"url":"https:\/\/dasini.net\/blog\/2008\/12\/03\/les-nouveautes-de-mysql-51-part-25\/","url_meta":{"origin":622,"position":3},"title":"Les nouveaut\u00e9s de MySQL 5.1 — (part 2\/5)","author":"Olivier DASINI","date":"3 d\u00e9cembre 2008","format":false,"excerpt":"Pouvoir automatiser ses t\u00e2ches de mani\u00e8re fiable et simple est le r\u00eave de tout administrateur de base de donn\u00e9es. Le programmateur d'\u00e9v\u00e8nements (Event Scheduler) est un planificateur de t\u00e2ches (CRON-like) embarqu\u00e9 dans MySQL 5.1.","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":595,"url":"https:\/\/dasini.net\/blog\/2009\/04\/20\/le-programmateur-devenements-event-scheduler-part-16-2\/","url_meta":{"origin":622,"position":4},"title":"Le programmateur d’\u00e9v\u00e9nements ( Event Scheduler ) (part 1\/6)","author":"Olivier DASINI","date":"20 avril 2009","format":false,"excerpt":"Poursuivons l'exploration des fonctionnalit\u00e9s phares de MySQL 5.1, et penchons-nous sur le programmateur d'\u00e9v\u00e8nements (Event Scheduler) pr\u00e9sent depuis MySQL 5.1.6 . Cet article est r\u00e9dig\u00e9 avec la version 5.1.22 de MySQL. Qu'est-ce qu'un programmateur d'\u00e9v\u00e8nements ? Le programmateur d'\u00e9v\u00e9nements ou \u00ab event scheduler \u00bb offre la possibilit\u00e9, \u00e0 l'administrateur de\u2026","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":641,"url":"https:\/\/dasini.net\/blog\/2009\/06\/29\/le-programmateur-d%e2%80%99evenements-event-scheduler-part-66\/","url_meta":{"origin":622,"position":5},"title":"Le programmateur d\u2019\u00e9v\u00e9nements ( Event Scheduler ) (part 6\/6)","author":"Olivier DASINI","date":"29 juin 2009","format":false,"excerpt":"Limitations et restrictions Certaines commandes MySQL ne sont pas permises dans un \u00e9v\u00e9nement. Verrouiller une table est interdit: LOCK TABLES, UNLOCK TABLES. Il n'est malheureusement pas possible de charger des donn\u00e9es avec la commande LOAD DATA INFILE. Les requ\u00eates pr\u00e9par\u00e9es (PREPARED STATEMENT) ne peuvent \u00eatre cr\u00e9\u00e9es dans un \u00e9v\u00e9nement.","rel":"","context":"Dans "MySQL"","block_context":{"text":"MySQL","link":"https:\/\/dasini.net\/blog\/category\/mysql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/622","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/comments?post=622"}],"version-history":[{"count":6,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/622\/revisions"}],"predecessor-version":[{"id":625,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/622\/revisions\/625"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=622"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=622"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=622"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}