Comment PostgreSQL peut vous sauver la vie

Parfois il y a des données dont on est certain qu'il ne faudra jamais les supprimer. C'est par exemple le cas des factures qui sont soumises à des règles strictes et, d'une manière générale, de l'ensemble de la comptabilité que l'on doit être en mesure de présenter à l'administration fiscale pendant 10 ans sous peine de lourdes amendes. Malheureusement, le fait est que ce n'est pas si simple que ça d'empêcher ces données d'être supprimées. Je citerai en exemple le ON DELETE CASCADE qui peut faire des ravages dans certaines situations. Heureusement, PostgreSQL nous offre une solution bien pratique.

Les règles de réécriture

PostgreSQL propose une extension du langage SQL qui permet d'altérer les requêtes SQL : les règles. Une règle est créée avec CREATE RULE de la manière suivante :

CREATE [ OR REPLACE ] RULE name AS ON event
    TO table_name [ WHERE condition ]
    DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }

Ceci nous donne la possibilité d'empêcher certaines requêtes, de les remplacer ou les compléter par une ou plusieurs nouvelles requêtes.

Un exemple concret

Supposons que vous disposez d'un base de données dans laquelle :

  • la table invoices contient toutes vos factures ;
  • la table orders contient toutes vos commandes ;
  • la table order dispose d'un champ invoice qui, s'il est non null, contient l'identifiant d'une entrée de la table invoices.

On va commencer par empêcher toute suppression de facture :

CREATE RULE invoices_del_protect AS ON DELETE TO invoices DO INSTEAD NOTHING;

Mais ce n'est pas forcément suffisant, il peut être utile d'également empêcher la suppression des commandes qui ont été facturées :

CREATE RULE orders_del_protect AS ON DELETE TO orders WHERE invoice IS NOT NULL DO INSTEAD NOTHING;

Ça marche vraiment ?

Plutôt oui:

derp=# SELECT COUNT(id) FROM invoices;
 count
-------
     4
(1 row)

derp=# SELECT COUNT(id) FROM orders;
 count
-------
    10
(1 row)

derp=# SELECT COUNT(id) FROM orders WHERE invoice IS NULL;
 count
-------
     6
(1 row)

derp=# DELETE FROM invoices;
DELETE 0
derp=# SELECT COUNT(id) FROM invoices;
 count
-------
     4
(1 row)

derp=# DELETE FROM orders;
DELETE 6
derp=# SELECT COUNT(id) FROM orders WHERE invoice IS NULL;
 count
-------
     0
(1 row)

derp=# SELECT COUNT(id) FROM orders WHERE invoice IS NOT NULL;
 count
-------
     4
(1 row)

Souriez, vous êtes protégé contre certaines erreurs bêtes vraiment critiques ! :)

Tags