Base de données versionnée - Partie 1
Bonjour,
Nous allons voir aujourd’hui comment créer et utiliser une base de données versionnée.
Pourquoi une base versionnée ?
La base de données versionnée telle qu’on va la voir dans cet article est un cas particulier de base de données temporelle. J’en ai eu besoin pour un projet de versionnage de configuration pour lequel je souhaitais que la base de données contienne l’ensemble de l’historique de la configuration afin de pouvoir consulter et revenir à une configuration antérieure simplement.
Qu’est-ce qu’une base de données temporelle ?
C’est une base de données dont les enregistrements contiennent une date de début de validité et une date de fin de validité. L’intérêt est de pouvoir avoir l’historique des valeurs de nos enregistrements. Vous ne supprimerez jamais réellement de données, vous ajouterez des nouveaux enregistrements avec les nouvelles informations et invaliderez les anciens enregistrements. Certains moteurs de base de données intègrent cette notion de base de données temporelle comme PostgreSQL avec son plugin temporal_tables ou encore SQL Server 2016 qui le gère nativement.
Les bases de données temporelles sont dans la norme SQL:2011. Dans mon cas j’ai eu besoin d’utiliser SQLite qui ne gère pas ce type de base.
La base
Il est possible d’utiliser n’importe quelle base de données relationnelle pour en faire une base temporelle ou versionnée. Il faut alors gérer nous-même les champs indiquant le début et la fin de validité et les requêtes associées. Nous générerons une nouvelle version lors de l’opération de validation, les enregistrements ne seront pas valides selon un référentiel de date mais selon des numéros de version, cela permet de regrouper un ensemble de modification sous une version.
Les principales contraintes sont l’impossibilité d’utiliser des clés étrangères et l’impossibilité d’avoir un champ en AUTO INCREMENT, pour ce dernier point il est possible de faire autrement.
Structure de la base
Pour gérer notre base versionnée nous allons utiliser une table _version qui contiendra un numéro de version ainsi que des
informations supplémentaires comme une description de la version, un date de création, etc…
Nous allons également ajouter les champs utiles à nos différentes tables gérant le versionnage. Ces champs seront :
_vs: pour la version de départ_ve: pour la version de fin de validité_id_*: pour chaque identifiant d’un enregistrement, l’ensemble des champs_id_*doivent former une clé unique
Prenons l’exemple d’une base destinée à contenir des enregistrements DNS. Nous allons nous concentrer sur la table qui contient les zones (nom de zone + numéro de série).
Nous allons créer la table zone suivante :
| Champs | Type |
|---|---|
| _id_zone | VARCHAR(256) |
| serial | INT |
| _vs | INT |
| _ve | INT |
Nous ajoutons une contrainte d’unicité sur l’ensemble (_id_zone, _vs, _ve).
Les colonnes _id_*, _vs et _ve ne doivent pas autoriser la valeur NULL, en effet dans une base SQL, la valeur NULL n’est pas prise en compte
par les contraintes d’unicité, vous pourriez donc avoir plusieurs enregistrements identiques avec _ve à NULL ce qui
ne nous conviendra pas pour la suite.
Opération sur la base
Nous allons maintenant voir comment réaliser les différentes requêtes classiques (sélection, ajout, modification, suppression) sur notre base versionnée.
Pour chaque opération nous supposerons que la base est dans la version 1 et que la prochaine validation créera une version 2. Partons de la table suivante :
| _id_zone | serial | _vs | _ve |
|---|---|---|---|
| example.com | 202101001 | 1 | -1 |
| example.fr | 202201001 | 1 | -1 |
| example2.fr | 202201001 | 1 | -1 |
Ajout d’un enregistrement
Si nous souhaitons ajouter une nouvelle zone DNS example2.com avec un numéro de série à 202202001 dans notre table il suffit d’ajouter l’enregistrement suivant :
| _id_zone | serial | _vs | _ve |
|---|---|---|---|
| example2.com | 202202001 | -1 | -1 |
Nous avons inséré un nouvel enregistrement dont la version de départ est -1 et la version de fin est -1. Nous allons utiliser les valeurs -1 comme équivalent de
valeur NULL qui ne casse pas la contrainte d’unicité.
Modification d’un enregistrement
La modification d’un enregistrement est identique à l’ajout. Partons de la table où la zone example2.fr existe déjà en version 1:
| _id_zone | serial | _vs | _ve |
|---|---|---|---|
| example2.fr | 202201001 | 1 | -1 |
Si nous voulons modifier le numéro de série de la zone nous ajoutons notre nouvel enregistrement contenant ce nouveau numéro dans la table, qui devient alors :
| _id_zone | serial | _vs | _ve |
|---|---|---|---|
| example2.fr | 202201001 | 1 | -1 |
| example2.fr | 202201002 | -1 | -1 |
Nous avons alors 2 enregistrements pour la même zone, celui valide à partir de la version 1 (_vs = 1) et qui est toujours
valide (_ve = -1) et notre nouvelle valeur sur la seconde ligne qui n’est pas encore valide (_vs = -1).
Suppression d’un enregistrement
La suppression d’un enregistrement passe par la modification de l’enregistrement actuel. Si nous partons de la table suivante :
| _id_zone | serial | _vs | _ve |
|---|---|---|---|
| example.com | 202101001 | 1 | -1 |
et que nous souhaitons supprimer la zone example.com, nous passons la valeur du champ _ve à -2 (marquage pour suppression).
| _id_zone | serial | _vs | _ve |
|---|---|---|---|
| example.com | 202101001 | 1 | -2 |
Validation d’une version
Une fois que nous avons réalisé nos modifications sur les enregistrements des différentes tables nous allons valider cette version de la configuration.
Pour cela il va falloir que nous rendions valides les nouveaux enregistrements et que nous invalidions les enregistrements qui ont été supprimés ou qui sont écrasés par de nouvelles valeurs.
Nous allons commencer par ajouter un nouvel enregistrement dans notre table qui contient les versions et qui ressemble actuellement à ça :
| id | description |
|---|---|
| 1 | Ajout example.fr et example.com |
Nous ajoutons la nouvelle version (2) et la table devient alors :
| id | description |
|---|---|
| 1 | Ajout example.fr et example.com |
| 2 | Suppression de la zone example.com, ajout d’example2.com et modification d’example2.fr |
Passons ensuite aux enregistrements, pour ajouter les nouveaux, il faut passer le champ _vs à 2 (notre nouvelle version)
pour tous les enregistrements pour lesquels _vs est égal à -1.
La table zone qui est comme ceci :
| _id_zone | serial | _vs | _ve |
|---|---|---|---|
| example.com | 202101001 | 1 | -2 |
| example.fr | 202201001 | 1 | -1 |
| example2.fr | 202201001 | 1 | -1 |
| example2.com | 202202001 | -1 | -1 |
| example2.fr | 202201002 | -1 | -1 |
devient donc
| _id_zone | serial | _vs | _ve |
|---|---|---|---|
| example.com | 202101001 | 1 | -2 |
| example.fr | 202201001 | 1 | -1 |
| example2.fr | 202201001 | 1 | -1 |
| example2.com | 202202001 | 2 | -1 |
| example2.fr | 202201002 | 2 | -1 |
Nous avons rendu valides nos enregistrements.
Pour la modification cela ajoute simplement une étape supplémentaire à l’étape précédente, il faut modifier
l’enregistrement qui a le même identifiant (_id_zone) et _ve égal à -1.
La table zone devient donc :
| _id_zone | serial | _vs | _ve |
|---|---|---|---|
| example.com | 202101001 | 1 | -2 |
| example.fr | 202201001 | 1 | -1 |
| example2.fr | 202201001 | 1 | 2 |
| example2.com | 202202001 | 2 | -1 |
| example2.fr | 202201002 | 2 | -1 |
Pour la suppression, c’est très simple également, on met à jour la valeur de _ve à 2 pour tous les enregistrements qui ont _ve égal à -2.
La table zone devient :
| _id_zone | serial | _vs | _ve |
|---|---|---|---|
| example.com | 202101001 | 1 | 2 |
| example.fr | 202201001 | 1 | -1 |
| example2.fr | 202201001 | 1 | 2 |
| example2.com | 202202001 | 2 | -1 |
| example2.fr | 202201002 | 2 | -1 |
Il faut répéter ces opérations pour chaque table.
Sélection simple
Pour récupérer les enregistrements de notre table zone valides en version 1, il faut utiliser la clause suivante dans
notre requête WHERE _vs <= 1 AND _vs > 0 AND (_ve < 0 OR _ve > 1), pour obtenir ceux valides en version 2 il faut remplacer
les 1 par des 2 dans la clause qui devient donc WHERE _vs <= 2 AND _vs > 0 AND (_ve < 0 OR _ve > 2). Cela permet de sélectionner
les enregistrements qui sont déjà valides en version 2 (_vs <= 2 AND _vs > 0) et qui sont toujours valides en version 2 (_ve < 0 OR _ve > 2).
Si nous reprenons notre table précédente en :
- ajoutant un autre domaine test.fr en version 3 et en supprimant example2.com dans cette même version
- en supprimant test.fr en version 4
- en ajoutant un enregistrement hello.eu pas encore validé et une suppression d’example.fr pas encore validée:
| _id_zone | serial | _vs | _ve |
|---|---|---|---|
| example.com | 202101001 | 1 | 2 |
| example.fr | 202201001 | 1 | -2 |
| example2.fr | 202201001 | 1 | 2 |
| example2.com | 202202001 | 2 | 3 |
| example2.fr | 202201002 | 2 | -1 |
| test.fr | 202202001 | 3 | 4 |
| hello.eu | 202202001 | -1 | -1 |
Pour récupérer tous les enregistrements en version 1 cela donne :
| _id_zone | serial | _vs | _ve | _vs <= 1 | _vs > 0 | _ve < 0 | _ve > 1 | Valide en version 1 |
|---|---|---|---|---|---|---|---|---|
| example.com | 202101001 | 1 | 2 | Oui | Oui | Non | Oui | Oui |
| example.fr | 202201001 | 1 | -2 | Oui | Oui | Oui | Non | Oui |
| example2.fr | 202201001 | 1 | 2 | Oui | Oui | Non | Oui | Oui |
| example2.com | 202202001 | 2 | 3 | Non | Oui | Non | Oui | Non |
| example2.fr | 202201002 | 2 | -1 | Non | Oui | Oui | Non | Non |
| test.fr | 202202001 | 3 | 4 | Non | Oui | Non | Oui | Non |
| hello.eu | 202202001 | -1 | -1 | Oui | Non | Oui | Non | Non |
Pour récupérer tous les enregistrements en version 2 cela donne :
| _id_zone | serial | _vs | _ve | _vs <= 2 | _vs > 0 | _ve < 0 | _ve > 2 | Valide en version 2 |
|---|---|---|---|---|---|---|---|---|
| example.com | 202101001 | 1 | 2 | Oui | Oui | Non | Non | Non |
| example.fr | 202201001 | 1 | -2 | Oui | Oui | Oui | Non | Oui |
| example2.fr | 202201001 | 1 | 2 | Oui | Oui | Non | Non | Non |
| example2.com | 202202001 | 2 | 3 | Oui | Oui | Non | Oui | Oui |
| example2.fr | 202201002 | 2 | -1 | Oui | Oui | Oui | Non | Oui |
| test.fr | 202202001 | 3 | 4 | Non | Oui | Non | Oui | Non |
| hello.eu | 202202001 | -1 | -1 | Oui | Non | Oui | Non | Non |
Pour récupérer tous les enregistrements en version 3 cela donne :
| _id_zone | serial | _vs | _ve | _vs <= 3 | _vs > 0 | _ve < 0 | _ve > 3 | Valide en version 3 |
|---|---|---|---|---|---|---|---|---|
| example.com | 202101001 | 1 | 2 | Oui | Oui | Non | Non | Non |
| example.fr | 202201001 | 1 | -2 | Oui | Oui | Oui | Non | Oui |
| example2.fr | 202201001 | 1 | 2 | Oui | Oui | Non | Non | Non |
| example2.com | 202202001 | 2 | 3 | Oui | Oui | Non | Non | Non |
| example2.fr | 202201002 | 2 | -1 | Oui | Oui | Oui | Non | Oui |
| test.fr | 202202001 | 3 | 4 | Oui | Oui | Non | Oui | Oui |
| hello.eu | 202202001 | -1 | -1 | Oui | Non | Oui | Non | Non |
Pour récupérer tous les enregistrements en version 4 cela donne :
| _id_zone | serial | _vs | _ve | _vs <= 4 | _vs > 0 | _ve < 0 | _ve > 4 | Valide en version 4 |
|---|---|---|---|---|---|---|---|---|
| example.com | 202101001 | 1 | 2 | Oui | Oui | Non | Non | Non |
| example.fr | 202201001 | 1 | -2 | Oui | Oui | Oui | Non | Oui |
| example2.fr | 202201001 | 1 | 2 | Oui | Oui | Non | Non | Non |
| example2.com | 202202001 | 2 | 3 | Oui | Oui | Non | Non | Non |
| example2.fr | 202201002 | 2 | -1 | Oui | Oui | Oui | Non | Oui |
| test.fr | 202202001 | 3 | 4 | Oui | Oui | Non | Non | Non |
| hello.eu | 202202001 | -1 | -1 | Oui | Non | Oui | Non | Non |
Reset
L’opération de reset (annulation des modifications non appliquées) consiste simplement à supprimer les enregistrements
pour lesquels _vs est égal à -1 et remettre les _ve qui sont à -2 à -1.
Suite
Dans un prochain article, nous verrons comment gérer des tables associatives, des champs AUTO INCREMENT et des opérations plus avancées sur notre base.
A bientôt!