Aller à la bibliothèque avec SQLite, partie 1

Lucidiot — Informatique — 2023-04-17
La structure d'une base de données pour lister des livres qui m'intéressent.

J'ai expliqué dans mes articles précédents sur la conversion de iCal à JSON comment je me suis retrouvé à utiliser SQLite pour emprunter des livres à la bibliothèque.

Aller à la bibliothèque fait partie d'un bon nombre de moyens que j'ai d'essayer de me faire sortir de chez moi plus souvent. Les livres que je veux emprunter sont éparpillés parmi les 12 bibliothèques du réseau de Grenoble, qui ont chacune des horaires distincts et qui varient selon les vacances scolaires et les jours fériés. Le catalogue en ligne de la bibliothèque est très lourd en JavaScript et la navigation y est donc lente, et les horaires des bibliothèques n'y sont pas directement indiqués. Ma motivation à lire des livres est assez aléatoire.

En tenant compte de tous ces faits, j'avais besoin d'une solution pour pouvoir facilement voir quels livres je peux emprunter quand, et où, et ainsi me motiver un peu plus à sortir pour aller à une bibliothèque particulière. Si je sais qu'une bibliothèque est ouverte et que je vais y trouver un bon nombre de livres intéressants, c'est bien plus facile de me dire que je devrais y aller. Et réduire la friction à juste obtenir cette information veut dire que j'ai beaucoup moins d'effort à faire pour aller à la bibliothèque.

J'ai donc décidé de faire une base de données SQLite, puisque j'aime bien jouer avec du SQL, donc je n'ai pas besoin de beaucoup me motiver à le faire. Une fois le projet complété, ça me fera en plus légèrement culpabiliser : j'ai passé du temps dessus, ce serait bête de ne pas m'en servir et donc de ne pas emprunter des livres !

Dans cet article, je vais donc décrire la structure de la base de données et l'origine des données qu'elle contient.

Bibliothèques

Le réseau des BMG se compose de 12 bibliothèques principales, où la carte d'abonné permettra d'emprunter, et des bibliothèques associées. Ces dernières ne sont pas vraiment des bibliothèques municipales mais juste des bibliothèques d'autres lieux, comme des musées ou écoles d'art ou les archives municipales, dont le catalogue est disponible en ligne, et pour lesquelles l'emprunt est occasionnellement aussi possible. Certaines des bibliothèques ont des restrictions particulières, par exemple celle des Relais Lecture n'est que pour les collectivités ou les écoles.

Dans le contexte de ma base de données, tout ce qui va m'intéresser, c'est le nom de la bibliothèque. On verra plus tard la question des horaires. Les conditions d'emprunt sont indiquées sur chaque livre, donc on n'a pas à le spécifier sur chaque bibliothèque, et je peux facilement me renseigner sur les détails d'une bibliothèque en particulier et décider de si elle est pertinente ou non.

CREATE TABLE library (
  name TEXT PRIMARY KEY NOT NULL
);

Je peux saisir manuellement les noms des bibliothèques ; trouver un moyen de le faire automatiquement prendrait beaucoup plus de temps pour un faible gain.

INSERT INTO library (name) VALUES
  ('Abbaye-les-Bains'),
  ('Alliance'),
  ('Arlequin'),
  ('Bibliothèque d''étude et du patrimoine'),
  ('Kateb Yacine'),
  ('Bibliothèque municipale internationale'),
  ('Centre-Ville'),
  ('Eaux-Claires'),
  ('Fonds commun'),
  ('Jardin de Ville'),
  ('Saint-Bruno'),
  ('Tesseire-Malherbe'),
  ('Archives municipales'),
  ('Musée de Grenoble');

Je n'ai inclus que les bibliothèques que je pense potentiellement fréquenter, pas toutes celles du réseau. Je n'ai donc pas inclus les Relais Lecture par exemple. Le « Fonds commun » est un fonds auquel on peut avoir accès dans n'importe quelle bibliothèque en le demandant aux bibliothécaires. J'aime imaginer qu'ils ouvrent un portail vers une autre dimension pour matérialiser le livre dans la bibliothèque.

Livres

Le principe de ma base de données est de permettre de lister rapidement les livres, pas d'inclure une grande quantité de détails à leur sujet. L'auteur et le titre seront suffisants. Pour pouvoir accéder aux détails d'un livre particulier, il pourrait être pratique d'avoir une URL que je pourrais cliquer pour accéder au livre dans le catalogue en ligne immédiatement. Fort heureusement, il y a déjà un système disponible pour ça : les URL Archival Resource Key.

Les URL ark:/ sont assez simples : elles contiennent d'abord un numéro associé à chaque bibliothèque, et ensuite chaque bibliothèque met un peu ce qu'elle veut derrière pour indiquer un ouvrage particulier, ou une page d'un ouvrage. Dans le cas des bibliothèques grenobloises, les URL ARK commencent toujours par ark:/66349.

InMedia V6, le système de gestion de contenu utilisé par les BMG, utilise des URL ARK pour identifier de façon unique chaque document du catalogue. Je peux donc utiliser comme clé primaire, comme identifiant unique, l'URL ARK, et générer l'URL du catalogue en ligne.

CREATE TABLE work (
  ark TEXT NOT NULL PRIMARY KEY
    CHECK (ark LIKE 'ark:/%'),
  author TEXT NOT NULL,
  title TEXT NOT NULL,
  read INTEGER NOT NULL
    DEFAULT FALSE
    CHECK (read IS TRUE OR read IS FALSE),
  url TEXT NOT NULL
    GENERATED ALWAYS AS ('https://catalogue.bm-grenoble.fr/' || ark) VIRTUAL
);

La colonne ark contient une URL ARK. J'ai inclus une contrainte CHECK qui assure que je ne tape pas quelque chose qui n'est pas une URL ARK. On a ensuite l'auteur et le titre du livre, puis un nombre entier appelé read. Par défaut, read vaut FALSE pour indiquer que je n'ai pas encore lu ce livre.

Dans SQLite, les booléens n'existent pas. Le fonctionnement est à la place similaire à Python : un zéro veut dire faux, et tout le reste veut dire vrai, mais officiellement le vrai est un 1. Pour garder une base assez propre, j'utilise donc un nombre entier qui ne peut être que TRUE ou FALSE, c'est-à-dire 0 ou 1.

On a enfin une colonne générée. GENERATED ALWAYS AS … VIRTUAL signifie que la colonne sera générée à chaque fois qu'on y accède, un peu comme un getter dans un langage de programmation orienté objet. Il existe également GENERATED ALWAYS AS … STORED, qui régénère la colonne à chaque fois qu'on insère ou met à jour une ligne et la stocke dans la base de données. Je n'ai aucune contrainte de performance dans ma base qui reste assez petite, donc j'opte pour une génération à la volée à chaque accès.

Je construis l'URL du catalogue en ligne assez simplement en utilisant l'opérateur de concaténation de chaînes de caractères ||. Le catalogue en ligne est d'une complexité extrême au niveau de son API et de son code JavaScript, mais au moins les URLs sont simples.

J'insére des livres dans cette table à la main à chaque fois que j'en trouve un intéressant, en tapant à chaque fois des INSERT INTO work (ark, author, title) VALUES …;. J'ai longuement expérimenté avec l'API de InMedia avant d'abandonner en raison de son inutile complexité, mais peut-être que je me ferai au minimum quelques scripts pour m'aider à tout saisir plus vite plus tard si je me lasse des INSERT.

Exemplaires d'un livre

Dans la table précédente, je ne fais pas mention d'une bibliothèque dans laquelle se trouve un livre, et j'ai baptisé la table work et non book. La table book est une table séparée, qui représente pour moi un exemplaire d'un livre. Un livre peut exister en plusieurs exemplaires dans plusieurs bibliothèques, donc cette table me permettra d'associer à un livre moult exemplaires sans me répéter.

CREATE TABLE book (
  ark TEXT NOT NULL
    REFERENCES work (ark) ON UPDATE CASCADE ON DELETE CASCADE,
  library TEXT NOT NULL
    REFERENCES library (name) ON UPDATE CASCADE ON DELETE CASCADE,
  location TEXT NOT NULL,
  dewey TEXT NOT NULL,
  borrowable INTEGER NOT NULL
    DEFAULT TRUE
    CHECK (borrowable IS TRUE OR borrowable IS FALSE),
  PRIMARY KEY (ark, library)
);

La première colonne référence l'URL ARK du livre concerné. Ensuite, on a le nom de la bibliothèque. Ces deux colonnes forment la clé primaire, ce qui veut dire que je ne peux pas avoir plusieurs exemplaires du même livre dans la même blbiothèque, alors que c'est un cas qu'on peut occasionnellement rencontrer. J'ai décidé de ne pas en tenir compte, car dans le contexte de mon projet, ce qui compte c'est de savoir qu'un livre est au moins une fois dans une bibliothèque.

Ensuite, location indique une subdivision au sein de la bibliothèque. Dans certaines bibliothèques, cela permet de savoir à quel étage se trouve le livre, ou dans quel groupe de rayonnages. Certaines subdivisions impliqueront qu'il faut demander aux bibliothècaires l'accès ou que l'emprunt n'est pas possible.

dewey est le numéro de classification du livre. La numérotation des livres en dehors du catalogue numérique est assez incohérente entre les bibliothèques, parfois même au sein même d'une seule bibliothèque, mais le plus souvent elle suivra la classification Dewey, celle que les collégiens ont peut-être appris au CDI : quelque chose comme 778 FOR ou 629.13 POL. C'est le numéro que je vais probablement devoir utiliser pour retrouver le livre une fois arrivé à la bibliothèque.

borrowable est un booléen indiquant si je peux emprunter ce livre, ou s'il est disponible uniquement pour la lecture sur place. J'aurai tendance à préférer l'emprunt parce que ça me donne plus de temps pour lire le livre, mais si le livre n'existe que sur place, je n'aurai pas vraiment le choix. C'est mieux de savoir à l'avance que je dois prévoir plus de temps passé sur place pour lire le livre et pas seulement partir avec.

Horaires

On rentre dans la partie la plus compliquée de la base. Chaque bibliothèque a ses propres horaires. Ces horaires peuvent s'appliquer uniquement pendant les vacances scolaires, en dehors de ces vacances, ou les deux. Il peut y avoir plusieurs périodes d'ouverture par jour, lorsque la bibliothèque ferme le midi par exemple. Et les horaires dépendent aussi du jour de la semaine.

CREATE TABLE schedule (
  library TEXT NOT NULL
    REFERENCES library (name) ON UPDATE CASCADE ON DELETE CASCADE,
  dow INTEGER NOT NULL
    REFERENCES weekdays (dow) ON UPDATE CASCADE ON DELETE RESTRICT,
  holidays INTEGER
    CHECK (holidays IS NULL OR holidays IS TRUE OR holidays IS FALSE), 
  start TEXT NOT NULL
    CHECK (start GLOB '[01][0-9]:[0-5][0-9]:[0-5][0-9]' OR start GLOB '2[0-3]:[0-5][0-9]:[0-5][0-9]'),
  end TEXT NOT NULL
    CHECK (end GLOB '[01][0-9]:[0-5][0-9]:[0-5][0-9]' OR end GLOB '2[0-3]:[0-5][0-9]:[0-5][0-9]'),
  UNIQUE (library, dow, start, end),
  CONSTRAINT start_before_end CHECK (julianday(start) < julianday(end))
);

D'abord, on a une colonne assez prévisible, library qui contient le nom de la bibliothèque.

Ensuite, dow est un entier entre zéro et six qui représente le jour de la semaine. Il fait référence à une table que je mentionnerai plus tard et qui contient les jours de la semaine. Un zéro représente le dimanche, un 1 le lundi, etc. jusqu'à 6 pour le samedi. Cette convention est la même que celle utilisée dans les fonctions de SQLite pour référencer le jour de la semaine, et le zéro le dimanche est dû à l'influence américaine sur l'informatique, vu que le dimanche y est souvent considéré comme le premier jour de la semaine.

holidays est un booléen nullable, c'est-à-dire qu'il y a trois valeurs possibles : TRUE indiquera que cet horaire ne s'applique que pendant des vacances scolaires, FALSE uniquement en dehors des vacances scolaires, et NULL les deux. Je discuterai plus longuement de ce choix de représentation plus tard quand on exploitera cette colonne, car utiliser un NULL ici est un acte délibéré lié à un aspect peu compris de SQL.

start et end représentent les heures de début et de fin. Dans SQLite, il n'existe aucun type représentant une date, une heure, ou les deux. On peut à la place choisir une des trois représentations recommandées par SQLite :

Un nombre à virgule flottante correspondant au nombre de jours dans le calendrier julien à partir du 24 novembre en l'an 4174 avant Jésus-Christ à 12h UTC (calendrier grégorien) ;
Un timestamp UNIX, un nombre entier correspondant au nombre de secondes écoulées depuis le 1 janvier 1970 à minuit UTC ;
Une chaîne de caractères correspondant à la représentation ISO 8601.

Ces trois types de valeurs sont tous acceptés par les fonctions de date et d'heure de SQLite, et on peut choisir celui qui nous va le mieux. J'ai choisi la dernière option, du texte, car ça facilite l'insertion et la lecture des horaires à la main. Je privilégie ici mon propre confort à la performance ou l'espace de stockage.

On notera que j'utilise une contrainte CHECK et l'opérateur GLOB pour vérifier que les heures correspondent au format d'heure accepté par SQLite. Il existe un opérateur REGEXP pour faire cette validation avec des expressions régulières, mais cela nécessite d'utiliser SQLite dans un autre langage de programmation pour y connecter un moteur d'expressions régulières de son choix ; l'interpréteur SQLite seul n'en dispose pas.

Je déclare également une contrainte pour interdire de déclarer des horaires à l'envers, par exemple de 16h à 12h. Si une bibliothèque décidait d'ouvrir au milieu de la nuit, j'ajouterais un horaire le jour précédent jusque 23:59:59, et un horaire le jour suivant à partir de minuit pile. Cela m'évitera de complexifier inutilement tous mes calculs par la suite. La contrainte utilise la fonction julianday, qui convertira le texte en le nombre de jours du calendrier julien et permet ainsi de simplement comparer deux nombres à virgule pour déterminer lequel vient avant l'autre.

J'ai également ajouté une contrainte UNIQUE pour éviter de dupliquer mes horaires. Toutes les colonnes sauf holidays sont incluses, car déclarer deux fois le même horaire pour des valeurs différentes de cette colonne n'a pas de sens. Si des horaires s'appliquent à la fois pendant et hors des vacances, alors il faut utiliser NULL.

J'ai saisi ces horaires dans une feuille de calcul LibreOffice Calc, avant de l'exporter en CSV et de l'importer dans SQLite. Les horaires n'étaient écrits dans aucun format structuré de façon cohérente et fiable, et n'étaient pas forcément présents ou à jour sur OpenStreetMap, donc c'était la façon la plus rapide de procéder.

Vacances et jours fériés

Il me serait possible d'indiquer dans mes requêtes les horaires à la fois pendant et en dehors des vacances, et de me laisser déterminer moi-même quels horaires s'appliquent, ou à l'inverse de me demander si on est en période de vacances ou non avant d'affiche des résultats. Cependant, comme je ne suis plus à l'école et que je n'ai pas non plus d'enfants à emmener à l'école, j'oublie complètement quand sont les vacances. Je me souviens cela dit généralement des jours fériés vu que je ne travaille pas.

C'est pour ça que j'ai commencé par rechercher des calendriers de vacances scolaires et de jours fériés et les convertir en JSON. En les ajoutant dans une table dédiée, je vais pouvoir filtrer les horaires selon les jours de vacances et les jours fériés et ainsi toujours afficher les bonnes données pour un jour choisi.

CREATE TABLE holidays (
  start TEXT NOT NULL
    CHECK (start GLOB '[0-9][0-9][0-9][0-9]-[01][0-9]-[0-3][0-9]'),
  end TEXT NOT NULL
    CHECK (end GLOB '[0-9][0-9][0-9][0-9]-[01][0-9]-[0-3][0-9]'),
  closed INTEGER NOT NULL
    DEFAULT FALSE
    CHECK (closed IS FALSE OR closed IS TRUE),
  PRIMARY KEY (start, end),
  CONSTRAINT start_before_end CHECK (julianday(start) <= julianday(end))
);

Cette fois-ci, start et end correspondent à des dates, toujours au format ISO 8601. closed est un booléen indiquant si la période donnée est une période de fermeture (jour férié) ou d'ouverture avec des horaires particuliers (vacances). TRUE indique une fermeture.

La contrainte CHECK est toujours là pour éviter qu'on déclare une période commençant après qu'elle se termine. À noter que j'autorise que le début soit égal à la fin, pour permettre des périodes d'une seule journée qui correspondront probablement à un jour férié.

Jours de la semaine

Il me reste une dernière table à mentionner, dont l'existence semble idiote : la table associant un numéro de jour de la semaine au nom de ce jour.

CREATE TABLE weekdays (
  dow INTEGER NOT NULL PRIMARY KEY
    CHECK (dow BETWEEN 0 AND 6),
  name TEXT NOT NULL
);

INSERT INTO weekdays (dow, name) VALUES
  (0, 'Dimanche'),
  (1, 'Lundi'),
  (2, 'Mardi'),
  (3, 'Mercredi'),
  (4, 'Jeudi'),
  (5, 'Vendredi'),
  (6, 'Samedi');

Contrairement à la majorité des implémentations de strftime, celle de SQLite ne permet pas d'obtenir le nom d'un jour de la semaine, car SQLite ne gère pas la notion de langue de l'utilisateur ou de paramètres régionaux quelconques. Pour pouvoir afficher lisiblement les jours de la semaine dans mes tableaux sans faire appel à un script externe, j'ai donc besoin de cette table supplémentaire. Les jours de la semaine peuvent être assez importants puisque je serai probablement au travail en dehors des week-ends.

Conclusion

Dans cet article, j'ai donc pu détailler plus précisément mes besoins et les limites de cette base de données. Mes choix ne suivent pas toujours les recommandations qu'on voit souvent dans les structures de base de données, par exemple de préférer en tant que clé primaire des nombres entiers et des UUID générés automatiquement et non liés aux données métier. Ces choix sont vraiment orientés vers la simplicité d'utilisation pour quelqu'un qui interagira directement avec la base en écrivant des requêtes, et pas en réinventant Microsoft Access dans le navigateur comme c'est le cas pour beaucoup d'applications en entreprise.

On verra dans un article suivant comment l'utiliser, notamment pour importer et gérer les horaires d'ouverture et les vacances.

Commentaires

ydreniv, 2023-04-17

J’ai hâte de voir la suite. J’ai déjà eu plusieurs idées d’outils centrés autour d’une base de données, donc c’est cool d’avoir un exemple.