11. Tables temporelles : WITH SYSTEM VERSIONING

Une table à versionnement système garde l'historique de ses lignes : un UPDATE ou un DELETE conserve l'ancienne version, avec la date de sa disparition. On peut ensuite répondre à « quelle était la valeur au 31 décembre ? » sans journal d'audit écrit à la main. La syntaxe est celle de MariaDB.

11.1 Créer une table versionnée#

CREATE TABLE stock (
    article INT PRIMARY KEY,
    qte     INT NOT NULL
) WITH SYSTEM VERSIONING;

MIRAJ ajoute deux colonnes invisibles, row_start et row_end, qui délimitent la période de validité de chaque version. SELECT * ne les montre pas, INSERT sans liste de colonnes ne leur donne pas de valeur, SHOW CREATE TABLE et DESCRIBE les omettent ; on les désigne par leur nom :

SELECT article, qte, row_start, row_end FROM stock;

Une ligne courante a pour row_end le maximum, 2038-01-19 03:14:07. On peut écrire soi-même les colonnes de période, qui restent alors visibles :

CREATE TABLE stock (
    article INT PRIMARY KEY,
    qte     INT NOT NULL,
    debut   TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
    fin     TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (debut, fin)
) WITH SYSTEM VERSIONING;

Les colonnes de période sont de type TIMESTAMP ou DATETIME (sinon erreur 4146). MIRAJ écrase toute valeur qu'un INSERT ou un UPDATE leur donne.

11.2 Ce que conservent les écritures#

Instruction	Effet sur l'historique
`INSERT`	aucune version passée ; `row_start` = instant de l'instruction
`UPDATE`	l'ancienne image est gardée, `row_end` = instant de l'instruction
`DELETE`	l'ancienne image est gardée, `row_end` = instant de l'instruction
`REPLACE`	suppression puis insertion : la ligne remplacée est gardée
`INSERT … ON DUPLICATE KEY UPDATE`	mise à jour : l'ancienne image est gardée
`TRUNCATE TABLE`	vide la table et son historique

L'historique suit la transaction : un ROLLBACK le défait, un arrêt brutal ne garde que les transactions validées. Les lectures ordinaires (SELECT * FROM stock) ne voient que les lignes courantes ; les clés (PRIMARY KEY, UNIQUE) ne portent que sur elles.

Une table versionnée se combine avec les autres objets du moteur. Une colonne qui tire sa valeur d'une séquence (id BIGINT DEFAULT NEXTVAL(ids), voir le chapitre 6) fonctionne comme sur une table ordinaire, y compris après ALTER TABLE … ADD SYSTEM VERSIONING : l'historique garde l'identifiant tel qu'il a été écrit, et les copies d'archive ne consomment pas la séquence. Une table sur disque (ENGINE = Aria) peut être versionnée (voir 11.7).

11.3 Lire le passé : `FOR SYSTEM_TIME`#

SELECT * FROM stock FOR SYSTEM_TIME AS OF '2025-12-31 23:59:59';
SELECT * FROM stock FOR SYSTEM_TIME AS OF TIMESTAMP '2025-12-31 23:59:59';
SELECT * FROM stock FOR SYSTEM_TIME FROM '2025-01-01' TO '2026-01-01';
SELECT * FROM stock FOR SYSTEM_TIME BETWEEN '2025-01-01' AND '2025-12-31';
SELECT * FROM stock FOR SYSTEM_TIME ALL;

Clause	Versions renvoyées
`AS OF t`	celles valides à l'instant `t` : `row_start <= t < row_end`
`FROM a TO b`	celles valides à un moment de `[a, b)`
`BETWEEN a AND b`	celles valides à un moment de `[a, b]`
`ALL`	toutes, courante comprise

La clause se place après le nom de la table, avant l'alias, et vaut pour chaque table d'une jointure, d'une sous-requête, d'une table dérivée, d'une UNION et pour la source d'un INSERT … SELECT :

SELECT c.nom, s.qte
FROM clients c JOIN stock FOR SYSTEM_TIME AS OF '2025-12-31' s ON s.article = c.article;

Les bornes sont des expressions (NOW() - INTERVAL 1 DAY, @instant, ?). Une table qui n'est pas versionnée donne l'erreur 4174. AS OF TRANSACTION (versionnement par identifiant de transaction) n'est pas pris en charge (1235).

11.4 Supprimer de l'historique : `DELETE HISTORY`#

DELETE HISTORY FROM stock;                                   -- tout l'historique
DELETE HISTORY FROM stock BEFORE SYSTEM_TIME '2024-01-01';   -- versions terminées avant cette date

Les lignes courantes ne sont jamais touchées. L'instruction rend le nombre de versions supprimées.

11.5 Ajouter ou retirer le versionnement#

ALTER TABLE stock ADD SYSTEM VERSIONING;
ALTER TABLE stock DROP SYSTEM VERSIONING;

À l'ajout, les lignes existantes démarrent à l'instant de l'ALTER. Au retrait, l'historique est supprimé et les colonnes de période implicites disparaissent. Les autres ALTER TABLE d'une table versionnée (colonne ajoutée, supprimée, modifiée, renommée) s'appliquent aussi à l'historique ; une colonne ajoutée y prend sa valeur par défaut. Les colonnes de période ne se modifient pas (1235). RENAME TABLE, DROP TABLE, CREATE TABLE … LIKE et CREATE OR REPLACE TABLE suivent la table.

11.6 Sous le capot#

L'historique vit dans une table compagnon __sv_<table> de la même base, sans clé ni contrainte, que SHOW TABLES et information_schema cachent. Elle se lit comme une table ordinaire (SELECT * FROM __sv_stock), avec les droits de sa table. information_schema.TABLES.TABLE_TYPE vaut SYSTEM VERSIONED. BACKUP / RESTORE la copient avec la table ; miraj-dump n'exporte que les lignes courantes (le script recrée une table versionnée, sans historique). Un nom de table qui commence par __sv_ est réservé.

11.7 Écarts avec MariaDB#

Résolution à la seconde : NOW() et les colonnes de période n'ont pas de fraction de seconde. Deux versions écrites dans la même seconde ont le même row_start ; la version remplacée est gardée avec une période vide, que AS OF ne voit jamais mais que ALL liste.
Une même transaction qui modifie deux fois une ligne y laisse deux versions (MariaDB n'en laisse qu'une).
Fin de période courante 2038-01-19 03:14:07 (MariaDB : 2038-01-19 03:14:07.999999).
Non pris en charge : versionnement par identifiant de transaction (BIGINT UNSIGNED, AS OF TRANSACTION), PARTITION BY SYSTEM_TIME, WITH / WITHOUT SYSTEM VERSIONING par colonne, tables temporaires et partitionnées, FOR SYSTEM_TIME dans le corps d'un déclencheur.
Pas de périodes applicatives ni de tables bitemporelles : PERIOD FOR nom (début, fin) (autre que SYSTEM_TIME) et ADD PERIOD FOR rendent l'erreur 1235 ; WITHOUT OVERLAPS et FOR PORTION OF ne sont pas reconnus. Le versionnement système est donc la seule dimension temporelle disponible.
Les colonnes de période sont des TIMESTAMP : elles suivent le time_zone de la session comme toute colonne TIMESTAMP, FOR SYSTEM_TIME compare des instants et non des heures affichées. La fin de période d'une ligne courante, écrite comme 2038-01-19 03:14:07 dans le fuseau de la session qui écrit, s'affiche donc décalée sous un autre fuseau. Les colonnes de période écrites en TIMESTAMP(6) s'affichent avec six décimales nulles.
Une table sur disque (ENGINE = Aria…) peut être versionnée ; sa table compagnon reste une table en mémoire.
Les suppressions et modifications faites par une action de clé étrangère (ON DELETE CASCADE…) ne laissent pas d'historique : elles ne déclenchent rien, comme pour les déclencheurs.
Les numéros d'erreur 4146, 4174 et 4185 sont ceux de MariaDB 10.11 relevés de mémoire, à confirmer.