lucidiot's cybrecluster

Aller à la bibliothèque avec SQLite, partie 2

Lucidiot Informatique 2023-05-02
Un script pour remplir la base de données depuis des calendriers de vacances et de jours fériés au format iCalendar.


Dans l'épisode précédent, j'ai décrit comment j'ai choisi de structurer une base de données SQLite pour gérer les livres que je veux emprunter à la bibliothèque et comment j'insère la plupart des données.

Dans d'autres articles précédents, j'ai aussi expliqué comment j'ai construit un script permettant de convertir des calendriers iCalendar en JSON, pour pouvoir ensuite transformer ce JSON et l'importer dans SQLite. J'ai fait tout ça en sachant pertinemment que c'était inutile vu que j'aurais pu utiliser des données CSV déjà fournies, ce qui aurait été bien plus rapide.

On va donc maintenant voir comment j'ai importé les périodes de vacances et les jours fériés depuis des calendriers au format iCal vers ma base de données de livres à l'aide de mon convertisseur JSON.

Importer les périodes de vacances

J'ai récupéré ce calendrier scolaire, qui contient toutes les périodes de vacances scolaires pour toutes les académies sur les prochaines années. Pour chaque période de vacances dans chaque académie, il y a un composant VEVENT, avec une date de début et de fin (DTSTART et DTEND). Chaque événement a un nom (SUMMARY) qui représente juste le nom de la période de vacances, par exemple Vacances d'hiver. On a aussi une DESCRIPTION qui contient des métadonnées sur les vacances, et dont on aura besoin pour déterminer de quelle académie il s'agit :

BEGIN:VEVENT
DTSTART:20220211T230000Z
DTEND:20220227T230000Z
UID:a327eb055a1e636168d61757c293e618a4e05cff
DESCRIPTION:description: Vacances d'Hiver\nlocation: Grenoble\nstart_date: 2022-02-11 23:00:00+00:00\nend_date: 2022-02-27 23:00:00+00:00\npopulation: -\nzones: Zone A
SUMMARY:Vacances d'Hiver
END:VEVENT

Ces données structurées fourrées dans un champ texte au milieu d'un format déjà structuré ont l'air assez bêtes, mais ayant déjà eu droit à beaucoup d'infâmies de la part d'institutions publiques dans mon propre travail, ça ne me fait pas grand chose. De ce que j'ai compris, ce calendrier est généré automatiquement à partir d'un tableau où quelqu'un a simplement indiqué qu'une colonne était une date de début et une autre colonne une date de fin, et un logiciel d'open data s'est chargé de produire le calendrier. Du coup, toutes les autres colonnes qui n'ont pas été associées à des types simples se retrouvent dans cette description, sans traitement particulier. On aurait pu utiliser LOCATION pour indiquer l'académie et CATEGORIES pour les zones et la population, mais ce serait trop demander d'un logiciel générique.

Après la conversion en JSON, cet événement ressemblera à ça, contenu dans le calendrier :

{
  "_type": "VCALENDAR",
  "_components": [
    {
      "_type": "VEVENT",
      "dtstart": {
        "": ["20220211T230000Z"]
      },
      "dtend": {
        "": ["20220227T230000Z"]
      },
      "uid": {
        "": ["a327eb055a1e636168d61757c293e618a4e05cff"]
      },
      "description": {
        "": ["description: Vacances d'Hiver\\nlocation: Grenoble\\nstart_date: 2022-02-11 23:00:00+00:00\\nend_date: 2022-02-27 23:00:00+00:00\\npopulation: -\\nzones: Zone A"]
      },
      "summary": {
        "": ["Vacances d'Hiver"]
      }
    }
  ]
}

Puisque la description contient toutes les métadonnées dont on aura besoin, y compris des dates dans un format ISO 8601 qui correspond au format dans ma base de données, je vais me contenter d'aller récupérer cette description seule pour chaque événement.

curl -s 'https://data.education.gouv.fr/api/explore/v2.1/catalog/datasets/fr-en-calendrier-scolaire/exports/ical?lang=fr&timezone=Europe%2FParis' |
  parse_ics |
  jq -r '[._components[].description[][] | gsub("\\\\n"; "\n")] | join("\n\n")'

Dans tous les composants, je récupère le contenu de toutes les descriptions. Je n'essaie pas de vérifier le type des composants, les paramètres des descriptions ou le nombre de celles-ci, parce que je pars du principe que le calendrier est bien structuré vu qu'il est très simple et généré automatiquement.

J'utilise ensuite gsub pour effectuer un remplacement du texte : je transforme les \n en véritables sauts de ligne. L'expression a l'air un peu étrange : \\\\n deviendra \\n après la résolution des échappements JSON, ce qui permet de spécifier dans l'expression régulière que je veux vraiment rechercher une controblique suivi de la lettre N, et pas un vrai saut de ligne.

Cela me permet de régler les problèmes d'échappement de caractères, vu que j'avais décidé de les ignorer en lisant le format iCalendar. Là aussi, je pars du principe que le générateur du calendrier ne me joue pas des tours avec ces histoires d'échappement de caractères et que ce sera gérable.

Lire les descriptions

Je collecte toutes les descriptions dans un grand tableau unique qui est ensuite relié par des sauts de ligne doubles. On se retrouve ainsi avec quelque chose de ce genre :

description: Vacances d'Hiver
location: Grenoble
start_date: 2022-02-11 23:00:00+00:00
end_date: 2022-02-27 23:00:00+00:00
population: -
zones: Zone A

description: Vacances d'Hiver
location: Montpellier
start_date: 2022-02-18 23:00:00+00:00
end_date: 2022-03-06 23:00:00+00:00
population: -
zones: Zone C

Cela ressemble beaucoup à du YAML. En remplaçant ces lignes vides par ---, le marqueur qui sépare plusieurs documents dans un même fichier YAML, le fichier devrait être lisible. Mais le champ population, sur lequel on reviendra plus tard, utilise un tiret cadratin (-) pour indiquer qu'il est vide, ce qui correspond en YAML au début d'un tableau. Cela cause des erreurs de lecture et il n'est donc pas possible de lire ces descriptions ainsi.

J'aurais pu modifier les tirets pour qu'ils deviennent '-' et en faire des chaînes de caractères, mais j'ai trouvé une méthode plus simple : recutils. Ce logiciel définit des recfiles, des fichiers où des données sont stockées sous forme d'enregistrements dans un format très similaire à ce qu'on a là : chaque enregistrement est séparé par une ligne vide.

Par chance, cette technique a fonctionné du premier coup sans traitement supplémentaire. Avec recsel, l'outil permettant de récupérer, filtrer ou grouper des données depuis un recfile, j'ai ainsi pu récupérer toutes les vacances de l'académie de Grenoble :

jq -r '…' | recsel -e 'location = "Grenoble"'

Filtrer les populations

Et c'est maintenant qu'on peut parler de la population. Le calendrier inclut des cas particuliers, car les vacances sont définies de façon différente pour les enseignants ou en outre-mer. Je n'ai pas envie d'aller me noyer dans les détails de comment les vacances scolaires sont définies, donc on va se limiter à Grenoble :

$ … | recsel -e 'location = "Grenoble"' -G population -CP population
-
Enseignants
Élèves

Avec -e, j'ai filtré pour ne rechercher que dans les descriptions des vacances de l'académie de Grenoble. Avec -G, je regroupe tous ces enregistrements par population, ce qui me donnera des valeurs uniques. Avec -P, j'affiche uniquement la population et pas le reste des enregistrements. Avec -C, ou --collapse, je supprime des lignes vides entre chaque résultat.

Pour Grenoble, on a seulement des vacances spécifiques aux enseignants ou aux élèves. Ces vacances correspondent aux vacances d'été, car les enseignants font leur rentrée un poil plus tôt pour pouvoir préparer la rentrée des élèves. Les bibliothèques auront des horaires qui dépendent des vacances des élèves, puisque ce sont les seules dont on entend vraiment parler, donc je vais exclure les enseignants.

recsel -e 'location = "Grenoble" && population != "Enseignants"'

On va en profiter pour également gérer un autre cas particulier : les vacances d'été de la dernière année indiquée dans le calendrier n'ont que leur date de début, pas de vraie date de fin, et donc on ne peut pas utiliser l'intervalle. On va donc aussi retirer les événements dont les dates de début et de fin sont égales.

recsel -e 'location = "Grenoble" && population != "Enseignants" && start_date != end_date'

Import dans SQLite

Il ne me reste plus maintenant qu'à transformer ça en quelque chose qui pourra rentrer dans SQLite. Puisque je suis déjà lancé dans les mauvaises idées, j'ai choisi d'utiliser recfmt, un autre outil de recutils qui permet de répéter une chaîne de caractères pour chaque enregistrement en remplaçant des valeurs par celles de l'enregistrement, pour générer directement des requêtes SQL.

recfmt "INSERT OR REPLACE INTO holidays (start, end, closed) SELECT SUBSTR('{{start_date}}', 0, 11), SUBSTR('{{end_date}}', 0, 11), FALSE; "

Dans ma table holidays, je n'ai que trois colonnes : les dates de début et de fin, et un booléen closed indiquant si ce jour est un jour férié (donc fermé) ou une période de vacances scolaires. J'ai une contrainte interdisant d'insérer des vacances en double, donc pour éviter des erreurs, j'utilise un INSERT OR REPLACE qui fera un remplacement si une ligne existe déjà.

Au lieu d'utiliser un plus classique VALUES ('{{start_date}}', '{{end_date}}', FALSE) pour indiquer la valeur d'une ligne, j'utilise à la place un SELECT. Cela me permet de découper la date avec SUBSTR, car elle est trop longue et recutils ne dispose pas de moyen de découper une chaîne de caractères. C'est une date et heure complète avec le fuseau horaire (2022-02-11 23:00:00+00:00), mais je n'autorise dans mes données qu'une date (2022-02-11). Ces dates font 11 caractères, donc je prends juste les 11 premiers caractères avec SUBSTR(…, 0, 11).

Il reste enfin un petit détail. Vu que les dates sont exprimées en UTC (+00:00), mais qu'elles sont au fuseau horaire de Paris, il peut y avoir un décalage d'un ou deux heures. Quand il est minuit en France, il est 23 heures UTC en hiver et 22 heures UTC en été. Par conséquent, si je découpe toutes les dates sans réfléchir, j'ai un jour de décalage avec la réalité sur la date de début. La date de fin est quand même correcte ; à minuit, c'est le jour de la reprise, donc avec 1 ou 2 heures de moins on a le dernier jour des vacances.

On peut utiliser le second argument optionnel des fonctions de date de SQLite, qui permet de spécifier des modificateurs qui changent la date. DATE(…, '1 days') ajoute un jour, ce qui restaure les bonnes dates.

On obtient donc ce script pour importer les vacances directement dans la base de données :

curl -s 'https://data.education.gouv.fr/api/explore/v2.1/catalog/datasets/fr-en-calendrier-scolaire/exports/ical?lang=fr&timezone=Europe%2FParis' |
    parse_ics |
    jq -r '[._components[].description[][] | gsub("\\\\n"; "\n")] | join("\n\n")' |
    recsel -e 'location = "Grenoble" && population != "Enseignants" && start_date != end_date' |
    recfmt "INSERT OR REPLACE INTO holidays (start, end, closed) SELECT DATE(SUBSTR('{{start_date}}', 0, 11), '1 days'), SUBSTR('{{end_date}}', 0, 11), FALSE; " |
    sqlite3 booksql.sqlite

Importer les jours fériés

Pour ce qui est des jours fériés, on va utiliser un calendrier proposé par Etalab qui est plus simple. Les jours fériés s'appliquent à tout le monde et partout, donc il n'y a pas de filtrage par académie ou population, et ils ne durent par définition qu'un seul jour, donc il n'y a pas non plus à gérer de date de fin. Les événements sont plus simples à lire :

BEGIN:VEVENT
DTSTART;VALUE=DATE:20210524
DTSTAMP:20230101T000000Z
SUMMARY:Lundi de Pentecôte
UID:29bd64b591fb6044b6579673f13d241a
END:VEVENT

Le DTSTAMP ne semble servir à rien vu qu'il a une valeur constante, donc on utilisera le DTSTART, qui est ici une date simple. Tout ce qu'on a à faire pour avoir des dates utilisables dans SQLite, c'est de rajouter deux tirets pour séparer l'année, le mois et le jour. On peut utiliser pour cela une bonne vieille regex.

curl -s https://etalab.github.io/jours-feries-france-data/ics/jours_feries_metropole.ics |
    parse_ics |
    jq -r '._components[].dtstart[][]' |
    sed -E 's/^([[:digit:]]{4})([[:digit:]]{2})([[:digit:]]{2})$/\1-\2-\3/; s/.*/INSERT OR REPLACE INTO holidays (start, end, closed) VALUES ('"'"'&'"'"', '"'"'&'"'"', TRUE);/' |
    sqlite3 booksql.sqlite

Dans cette expression régulière, je récupère 4 chiffres dans un groupe capturant, puis 2 autres dans un autre groupe, et les deux derniers dans un dernier groupe, ce qui me donne ainsi ma date. Je les réassemble en ajoutant les tirets. Ensuite, une deuxième expression vient prendre cette date et la répéter deux fois dans une commande INSERT OR REPLACE pour ajouter une période de jour férié avec la même date de début et de fin.

J'aurais pu utiliser une seule expression, ou utiliser jq qui dispose aussi d'expressions régulières, mais cette option m'a semblé être la plus directe pour convertir de JSON à SQL.

Afin de pouvoir plus facilement réexécuter l'import des vacances et des jours fériés, il y a un script fetch_holidays dans le dépôt Git de booksql, que je peux exécuter comme fetch_holidays | sqlite3 booksql.sqlite pour récupérer des données fraîches. J'ai aussi ajouté au dépôt un fichier d'initialisation statique qui permet de remplir la base de données avec les bibliothèques, leurs horaires, les jours de la semaine, ainsi que les vacances et les jours fériés tels que décrits quand je les ai importé pour la dernière fois.

Conclusion

Quand j'ai commencé à écrire au sujet de cette base de données, je m'attendais à peut-être en faire deux ou trois articles tout au plus ; un pour l'import de calendriers et un ou deux pour la structure de la base et son utilisation. Je me retrouve finalement à avoir besoin d'au moins cinq articles, un grand classique sur ce blog.

Dans le cinquième article donc, je vais détailler des requêtes qui me permettent cette fois d'exploiter la base et de décider quand aller à la bibliothèque et dans laquelle.


Commentaires

Il n'y a pour l'instant aucun commentaire. Soyez le premier !