D4-C03 : Première approche de la programmation au sein d’un SGBD - La gestion d'événements en SQL

On peut utiliser un langage de programmation associé à un SGBD, la programmation d’événements (events) en est une illustration. Elle permet de planifier dans le temps l'exécution d'un traitement à renouveler éventuellement de façon périodique.

Objectifs

  • revoir la gestion des dates
  • découvrir la gestion d'évènements avec le SGBD MariaDB
  • automatiser des traitements planifiés
  • mettre en oeuvre aves des exercices pratiques

Les formats de dates avec MariaDB

MariaDB propose plusieurs types de données pour gérer les dates et heures :

  • DATE : date (AAAA-MM-JJ)
  • DATETIME : date + heure (AAAA-MM-JJ HH:MM:SS)
  • TIME : durée ou heure du jour
  • TIMESTAMP : similaire à DATETIME mais dépend du fuseau horaire
  • YEAR : année sur 4 chiffres

Note: Ressource utile : https://mariadb.com/kb/en/date-time-functions/

Manipuler les dates

Quelques fonctions utiles :

  • SELECT NOW(); – date et heure actuelles
  • SELECT CURDATE(); – date actuelle
  • SELECT DATE_ADD(NOW(), INTERVAL 100 DAY); – +100 jours
  • SELECT DATE_FORMAT(NOW(), '%W %d %M %Y'); – mise en forme
  • SELECT YEAR(champDate);
  • SELECT MONTH(champDate);
  • …

Exemples avec des valeurs de dates précises :

  • SELECT YEAR('2017-03-20') as Année; → 2017
  • SELECT MONTH('2017-03-20') as Mois; → 3
  • SELECT DAY('2017-03-20') as Jour; → 20
  • SELECT WEEKDAY('2017-03-20') as JourSemaine; → 0 (=lundi, 6=dimanche)
  • SELECT DAYOFWEEK('2017-03-20') as JourSemaine; → 2 (=lundi,1=dimanche)
  • SELECT HOUR('2017-03-20 00:47:49') as Heures; → 0
  • SELECT MINUTE('2017-03-20 00:47:49') as Minutes; → 47
  • SELECT SECOND('2017-03-20 00:47:49') as Secondes; → 49
  • Mettre en forme une valeur de date : SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y') → Sunday October 2009 (jour mois année)

Exercices sur les dates

Gestion commerciale

schéma bd

  1. Quels clients ont acheté un produit l'année dernière ?
  2. Quels clients ont acheté un produit depuis moins de 180 jours ?
  3. Quels clients n'ont rien acheté ce mois-ci ?
  4. Trouver tous les achats réalisés au cours des 30 derniers jours.
  5. Calculer combien de jours se sont écoulés depuis la dernière commande de chaque client.

Indépendamment de la base précédente :

  1. Écrire une requête qui ajoute 6 mois à la date actuelle.
  2. Afficher la date du premier jour du mois précédent (Par exemple, si nous sommes le 20 mars 2017, la requête doit afficher 1er février 2017 - cette réponse est éligible à la qualité de réponse parfaite si elle fonctionne même en janvier)
  3. Afficher la date du prochain jeudi (le langage SQL peut utiliser des structures conditionnelles, effectuez des recherches !)

La programmation d’événements

La programmation d’événements (events) permet de planifier dans le temps l'exécution d'un traitement à renouveler éventuellement de façon périodique. Ils nécessitent que le planificateur d’événements (event_scheduler) soit activé.

Utilité

Voici quelques exemples de tâches que vous pourrez mettre en œuvre, sans avoir à passer par des artifices de programmation de plus ou moins bonnes qualités. Vous pourrez, par exemple :

  • Programmer des requêtes de suppression pour délester de vieilles discussions sur un forum.
  • Calcul annuel d'intérêt.
  • Programmer des requêtes de sauvegardes automatiques chaque nuit.
  • Différer l'exécution d'un traitement gourmand en ressources aux heures creuses de la prochaine nuit.
  • Analyser et optimiser l'ensemble des tables mises à jour dans la journée
  • …

Prérequis

Attention:

  • Le programmateur d’événements (event scheduler) doit être actif
  • L'utilisateur doit avoir le privilège SUPER (au niveau global) pour avoir l'autorisation d'exécuter des commandes d'administration et de débogage.
  • Pour définir un événement dans une base, l'utilisateur doit avoir le privilège EVENT.

Vérification : SHOW VARIABLES LIKE 'event_scheduler';

Activation : SET GLOBAL event_scheduler = ON;

Syntaxe de création d’un événement

CREATE EVENT [IF NOT EXISTS] [nom_base].nom_evenement
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE]
    [COMMENT 'commentaire']
    DO instructions_sql;
=====  =====
--Exemple
CREATE EVENT nom_evenement
ON SCHEDULE EVERY 1 DAY STARTS CURRENT_TIMESTAMP
DO
   instruction_sql;

schedule précise la planification (instant initial, période, instant final) selon le modèle suivant :

  • AT timestamp [+ INTERVAL intervalle] 🡪 exécution unique (événement ponctuel)
  • EVERY intervalle 🡪 répéter l'exécution périodiquement (événement périodique)
  • [STARTS timestamp] : début événement
  • [ENDS timestamp] : fin événement
    • timestamp désigne un instant au format TIMESTAMP ou DATETIME(précision à la seconde)
    • intervalle est le délai exprimé sous la forme d'une ou plusieurs expressions séparées par '+' quantite {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
  • ON COMPLETION renseigne sur le fait de supprimer l'événement après son exécution, par défaut NOT PRESERVE, l'événement expiré est détruit.

Exemple de planification (schedule)

N° clause ON SCHEDULE l'événement s'exécute … (à compléter)
1 AT '2019-10-2 10:59:00'
2 AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
3 AT CURRENT_TIMESTAMP + INTERVAL 3 WEEK + INTERVAL 2 DAY
4 EVERY 1 DAY STARTS '2019-10-2 4:00:00'
5 EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK
6 EVERY 12 HOUR STARTS CURRENT_TIMESTAMP + INTERVAL 30 MINUTE ENDS CURRENT_TIMESTAMP + INTERVAL 4 WEEK

Activité

A04