Types complexes

De plus en plus on est amené à stocker des types plus compliqués dans nos bases. Dans ce chapitre, on explore l'utilisation de quelques-uns de ses types.

Dates

Une date est à la fois une donnée composée (année, mois, jour, heure, minute, secondes) à la fois une donnée simple (le nombre de secondes depuis 01/01/1970).

Pour les raisons de lisibilité, on préfère le format complexe. Mais, pour les raisons d’utilisation (trier, par exemple, ou bien les opérations du genre supérieur à ou inférieur à) le format simple est préférable.

Par exemple, en France, on présente une date avec le format jj/mm/aaa.

Ce formant n'est pas très pratique pour trier ! Si on a un ensemble de dates de plusieurs années, comment on peut trier les dates en ordre croissant ?

Un format plus pratique serait pour trier sera : aaaa/mm/jj (l’unité le plus grande vers l’unité le moins grande).

Mais, le format est plutôt précisé par la culture, pas par son côté pratique. Aux Etats Unis, c'est encore pire : mm/jj/aaaa !

En SQL, il y a des types Date, Time, DateTime, TimeStamp et Year qui stocke la date et/ou l’heure dans un format normalisé et indépendant de la culture. Lorsqu’on récupère cette donnée coté client, c'est à nous de le traduire dans le format local pour l'affichage.

Attention au fuseau horaire !

Imaginons, votre appli est internationale. Mais, les serveurs se trouvent en France (GMT+2). Vous calez des rendez-vous de vos salariés partout dans le monde. L’appli doit envoyer un e-mail 10 minutes avant le rendez-vous.

Une personne en Australie cale un rendez-vous à 10h00 le matin.

• Nous stockons quelle heure dans la base de données ?

• Quelle est l’heure sur le serveur (avec le SGBDR) ?

Son gérant, aux Etats Unis, veut voir l’heure de son rendez-vous :

• Elle va voir l’heure locale australienne ?

• Ou elle va voir l’heure locale à New York ?

Aurez-vous une stratégie pour régler tous ces problèmes ?

UTC au secours !

Coordinated Universal Time

Nous convertissons toujours vers l’heure à GMT-0 avant de le stocker dans la base de données.

Donc 10h00 en Australie (GMT+11) = 23h00 de la veille (GMT-0).

Donc le rdv du 2022-02-14 à 10h en Australie est stocké comme 2022-02-13 à 23h dans la base de données.

Ensuite, l’affichage et/ou raisonnement sur cette date dépend d’un point-de-vue, c’est-à-dire, il faut préciser le fuseau horaire de notre point de vue, afin de faire la conversion :

• Dans l’heure australienne, ajouter 11 heures : donc 2022-02-13 à 23h + 11 heures = 2022-02-14 à 10h

• Dans l’heure de New York, soustraire 5h: donc 2022-02-13 à 23h – 5 heures = 2022-02-13 à 18h

Blob

Un blob permet de stocker des grandes quantités de données (des fichiers/images/…) comme séquence d’octets : https://mariadb.com/kb/en/blob/

Mais, est-ce que c'est une bonne pratique ?

Avantages :

• Facile à accéder, via son ID ou dans un join avec d’autres tables

• On peut sauvegarder la base et tous les fichiers en une seule action

• Les règles d’accès de la SGBDR sont applicables aussi aux fichiers

Désavantages :

• Il faut être fastidieux dans les clauses where : on risque de transférer les fichiers entiers dans nos - requêtes !

• Sauvegarde de la base de données très lourde et longue

• Pas très optimisé : notre SGBDR doit passer beaucoup de temps à accéder au disque et transférer des données, alors d’autres systèmes existe (CDN par exemple) qui optimise le stockage et transfert des fichiers

N’oubliez pas qu’un fichier ou image est souvent statique et ne change pas souvent, donc son stockage et diffusion peut-être optimisée en dehors de notre SGBDR.

Personnellement, j'ai eu des très mauvaises expériences avec le stockage des fichiers directement dans la base de données. Atteint une lourdeur de 70Go, avec 99% de cet espace pris par des photos.

Les sauvegardes sont devenues très compliquées, voire impossibles. Des mauvaises formulations des requêtes (des clauses where trop ouvertes, le manque de pagination, etc) rendaient la plateforme instable et difficile à gérer.

Aujourd'hui, dans l'âge du cloud, je préfère stocker des images dans une solution de stockage (type CDN), et seulement stocker une référence qui pointe vers l'image dans le SGBDR.

JSON

On peut stocker des colonnes qui contiennent du JSON, et valider que le JSON est bien formaté :

create table livre (
  id int not null auto_increment,
  page JSON CHECK (JSON_VALID(page)),
  primary key (id)
);


insert into livre (page) values 
('{"number": 1, "text": "Lorem ipsum", "chapter": "And then there was one"}');

Il existe de nombreuses fonctions qui permettent de travailler avec le format JSON dans MariaDB : https://mariadb.com/kb/en/json-functions/.

Stocker du JSON dans un SGBDR casse la logique de normalisation (pas en 1NF – donnée composée).

Donc il n'y a pas de garantie de non-redondance ni intégrité.

Créer une colonne de type JSON paraît simple, facile à implémenter, et facile à faire évoluer - surtout face à un domaine qu'on ne maîtrise pas.

Mais, à terme, cela pourrait être un piège :

• il n'y a pas de validation des contraintes sur les sous-informations stockées dans le JSON

• il serait impossible de raisonner sur les données stockées dans une colonne type JSON (faire des select dessus)

• on ne peut pas assurer l'intégrité des données en format JSON.

Les bases de données type NoSQL offrent quelques solutions limitées à ces problèmes. Par exemple, MongoDB stocke un format quasi identique à JSON (qui s'appelle BSON), et permet d'imposer un schéma de validation sur les données. En revanche, il n'y a aucune notion d'intégrité référentielle entre les collections (dans une base NoSQL, cette intégrité est plutôt implicite via la relation hiérarchique d'un document et ses sous-documents.)