{"id":634,"date":"2009-06-24T12:51:56","date_gmt":"2009-06-24T11:51:56","guid":{"rendered":"http:\/\/dasini.net\/blog\/?p=634"},"modified":"2009-06-29T15:31:05","modified_gmt":"2009-06-29T14:31:05","slug":"le-programmateur-devenements-event-scheduler-part-56","status":"publish","type":"post","link":"https:\/\/dasini.net\/blog\/2009\/06\/24\/le-programmateur-devenements-event-scheduler-part-56\/","title":{"rendered":"Le programmateur d’\u00e9v\u00e9nements ( Event Scheduler ) (part 5\/6)"},"content":{"rendered":"

<\/p>\n

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

<\/p>\n

<\/p>\n

Appel d’une proc\u00e9dure stock\u00e9e<\/h2>\n

\n

Il est possible d’appeler des proc\u00e9dures stock\u00e9es dans un \u00e9v\u00e9nement. <\/span><\/p>\n

\n

mysql> CREATE EVENT `_event`.`appel_ps`<\/span><\/address>\n
ON SCHEDULE AT CURRENT_TIMESTAMP<\/span><\/address>\n
DO<\/span><\/address>\n
\/*Appel de la proc\u00e9dure stock\u00e9e*\/<\/span><\/address>\n
CALL pro_stock.insert_profil(‘Olivier’, ‘DASINI’, ‘Orange’);<\/span><\/address>\n

\n

\n

\n

Archivage p\u00e9riodique des donn\u00e9es<\/span><\/h2>\n

Beaucoup d’entreprises sont oblig\u00e9es de conserver d’\u00e9normes volumes de donn\u00e9es historiques sur leurs serveurs de bases de donn\u00e9es. Un moyen de r\u00e9duire l’espace occup\u00e9 est d’utiliser le moteur de stockage ARCHIVE. Ce moteur de table, compresse automatiquement les donn\u00e9es qui n’occupent alors plus que 30% de leur taille originale.<\/p>\n

\n

mysql> CREATE TABLE _event.archive_event (<\/address>\n
moment timestamp,<\/address>\n
fois tinyint unsigned,<\/address>\n
action char(50)<\/address>\n
) ENGINE = ARCHIVE;<\/address>\n
<\/address>\n
mysql> DELIMITER \u20ac\u20ac<\/address>\n
mysql> CREATE EVENT _event.archive_base<\/address>\n
ON SCHEDULE EVERY 1 DAY<\/address>\n
DO<\/address>\n
BEGIN<\/address>\n
\/*Insertion des donn\u00e9es \u00e0 archiver*\/<\/address>\n
INSERT INTO _event.archive_event<\/address>\n
SELECT moment, fois, action<\/address>\n
FROM _event.insert_event<\/address>\n
WHERE moment < SUBDATE(now(), INTERVAL 1 DAY);<\/address>\n
\/*Suppressions des donn\u00e9es archiv\u00e9es dans la table originale*\/<\/address>\n
DELETE FROM _event.insert_event WHERE moment < SUBDATE(now(), INTERVAL 1 DAY);<\/address>\n
END \u20ac\u20ac<\/address>\n
mysql> DELIMITER ;<\/address>\n
\n<\/address>\n


\n
\n<\/p>\n

(\u00e0 suivre… event scheduler: <\/a><\/em>Limitations et restrictions<\/a>)<\/em><\/address>\n

\n","protected":false},"excerpt":{"rendered":"

Appel d’une proc\u00e9dure stock\u00e9e<\/p>\n

Il est possible d’appeler des proc\u00e9dures stock\u00e9es dans un \u00e9v\u00e9nement.<\/p>\n

mysql> CREATE EVENT `_event`.`appel_ps`
\nON SCHEDULE AT CURRENT_TIMESTAMP
\nDO
\n\/*Appel de la proc\u00e9dure stock\u00e9e*\/
\nCALL pro_stock.insert_profil(‘Olivier’, ‘DASINI’, ‘Orange’);<\/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-634","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-ae","jetpack-related-posts":[{"id":641,"url":"https:\/\/dasini.net\/blog\/2009\/06\/29\/le-programmateur-d%e2%80%99evenements-event-scheduler-part-66\/","url_meta":{"origin":634,"position":0},"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":[]},{"id":622,"url":"https:\/\/dasini.net\/blog\/2009\/06\/16\/le-programmateur-devenements-event-scheduler-part-46\/","url_meta":{"origin":634,"position":1},"title":"Le programmateur d’\u00e9v\u00e9nements ( Event Scheduler ) (part 4\/6)","author":"Olivier DASINI","date":"16 juin 2009","format":false,"excerpt":"Insertions de donn\u00e9es dans une table, toutes les 30 secondes mysql> SELECT * FROM _event.insert_event ; mysql> DELIMITER \u20ac\u20ac mysql> CREATE EVENT `_event`.`second_event` ON SCHEDULE EVERY 30 SECOND DO BEGIN \/*selectionne la val maximale de la colonne fois. Si elle vaut NULL alors retourne la valeur 0 dans la variable\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":26,"url":"https:\/\/dasini.net\/blog\/2008\/10\/29\/mysql-50-un-sgbdr-mature-part-34\/","url_meta":{"origin":634,"position":2},"title":"MySQL 5.0 : Un SGBDR mature ? — (part 3\/4)","author":"Olivier DASINI","date":"29 octobre 2008","format":false,"excerpt":"Les proc\u00e9dures stock\u00e9es sont des listes de commandes qui peuvent \u00eatre compil\u00e9es et stock\u00e9es sur le serveur. Elles permettent de d\u00e9placer une partie de la logique m\u00e9tier d'une application de base de donn\u00e9es du client vers le serveur. Les clients n\u2019ont plus besoin de soumettre \u00e0 nouveau toute la commande,\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":595,"url":"https:\/\/dasini.net\/blog\/2009\/04\/20\/le-programmateur-devenements-event-scheduler-part-16-2\/","url_meta":{"origin":634,"position":3},"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":41,"url":"https:\/\/dasini.net\/blog\/2008\/10\/30\/mysql-50-un-sgbdr-mature-part-44\/","url_meta":{"origin":634,"position":4},"title":"MySQL 5.0 : Un SGBDR mature ? — (part 4\/4)","author":"Olivier DASINI","date":"30 octobre 2008","format":false,"excerpt":"Les d\u00e9clencheurs (triggers) sont des ordres de d\u00e9clenchement d'op\u00e9rations quand un \u00e9v\u00e8nement survient sur une table.","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":350,"url":"https:\/\/dasini.net\/blog\/2008\/12\/19\/mysql-5-les-vues-part-57\/","url_meta":{"origin":634,"position":5},"title":"MySQL 5 : Les vues — (part 5\/7)","author":"Olivier DASINI","date":"19 d\u00e9cembre 2008","format":false,"excerpt":"Masquer la complexit\u00e9 du sch\u00e9ma L'\u00e9quipe de d\u00e9veloppement doit \u00e9crire un moteur de recherche pour une application de commerce \u00e9lectronique. Voici un extrait des tables de la base de donn\u00e9es impliqu\u00e9es dans la recherche des produits du site.","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\/634","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=634"}],"version-history":[{"count":10,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/634\/revisions"}],"predecessor-version":[{"id":652,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/posts\/634\/revisions\/652"}],"wp:attachment":[{"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/media?parent=634"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/categories?post=634"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dasini.net\/blog\/wp-json\/wp\/v2\/tags?post=634"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}