Les générateurs purs Interbase

Il est parfois nécessaire de fournir des compteurs sans trou pour certains documents (ex. : documents comptables) ou pour certains produits industriels (numéros de série). Ce document présente un moyen fiable de maintenir à jour un compteur lors de la suppression de documents ou l'annulation de ces derniers.

Les générateurs Interbase sont utilisés en général afin de fabriquer des clefs primaires pour les tables. En effet, on peut être assuré de l'unicité du numéro généré même lors d'un accès concurrentiel, la génération du numéro se faisant hors transaction.

Il est nécessaire pour les développeurs de pouvoir générer des numéros uniques pour, par exemple, des numéros de facture. Ces numéros doivent êtres chronologiques et sans trou (nous parlerons d'une séquence pure).

Le problème majeur de l'utilisation des générateurs pour créer des numéros de documents est que si un document est annulé ou supprimé, le numéro généré ne sera jamais retrouvé, le générateur ne pouvant pas revenir en arrière sans modification des Metadata de la base de données. La séquence est donc interrompue. Il est bien évidemment trop dangereux de modifier la valeur du générateur sous peine de ne plus avoir une clef primaire valide.

Pour obtenir une séquence pure, une solution est de créer une table dans laquelle le nouveau numéro sera disponible ainsi que les numéros recyclés.

Le problème est que l'accès a la table demande l'ouverture d'une transaction ce qui risque, lors d'un accès concurrent de fournir un numéro erroné. La solution présentée combine un générateur ainsi qu'une table de log afin de conserver les numéros a recyclé ainsi qu'un système de verrou sur les enregistrements dont nous souhaitons obtenir la valeur.

CREATE TABLE FACTURES(
 ID INTEGER NOT NULL,
 DATE_FAC DATE NOT NULL,
 NUM_FAC INTEGER,/*Cette séquence devra être pure*/
 CONSTRAINT PK_INV_HEADER PRIMARY KEY(ID));

Cette table correspond à la liste des factures. L'ID est un générateur standard tandis que NUM_FAC doit être un séquenceur pur.

CREATE TABLE LOG_FACTURES(
 LOG_ID INTEGER NOT NULL,
 NUM_FACTURE INTEGER,/*Les numéros de facture a recycler*/
 ID_FACTURE INTEGER,
 STATUS VARCHAR(20),
 MOTIF VARCHAR(50),
 GEN_DATE DATE,
 LOGDATE DATE,
 CONSTRAINT PK_INV_LOG PRIMARY KEY(LOG_ID));

La table de log. C'est dans cette table que nous allons rechercher les informations du compteur ainsi que les numéros à recycler. Nous verrons tout ça plus en détail lors de la description des triggers.

CREATE GENERATOR GEN_INV_HEADER_ID;

 CREATE GENERATOR GEN_INV_LOG_ID;

 CREATE GENERATOR GEN_INV_NUMBER;

Les deux générateurs pour les clefs primaires et le numéro de document.

CREATE TRIGGER BI_INSERT_INV_HDR FOR FACTURES ACTIVE BEFORE INSERT
 POSITION 8 AS BEGIN
 IF (NEW.ID IS NULL) THEN
 NEW.ID = GEN_ID(GEN_INV_HEADER_ID, 1);
 IF (NEW.DATE_FAC IS NULL) THEN
 NEW.DATE_FAC='NOW';
 END

CREATE TRIGGER BI_INSERT_INV_LOG FOR LOG_FACTURES ACTIVE BEFORE
 INSERT POSITION 9 AS BEGIN
 IF (NEW.LOG_ID IS NULL) THEN
 NEW.LOG_ID = GEN_ID(GEN_INV_LOG_ID, 1);
 END

Ces triggers permettent simplement de créer les clefs primaires. Le trigger suivant permet de récupérer le numéro de document correct. On commence par rechercher la liste des numéros à recycler dans la table de LOG et on prend le plus petit. Si un numéro est disponible, on le verrouille grâce au Update pour être sure de l'atomicité de l'opération. Cette étape est très importante ça c'est ce Update qui va permettre de garantir les accès concurrents a la table contenant les numéros. Une fois le trigger terminé, la transaction est libérée et les verrous avec. Si aucun numéro n'est disponible, on prend simplement la valeur du générateur.

CREATE TRIGGER BI_INSERT_INV_NUM FOR FACTURES ACTIVE BEFORE INSERT
 POSITION 9 AS
 DECLARE VARIABLE INV_NO INTEGER;
 BEGIN
 INV_NO = 0;
 SELECT MIN(INV_NUMBER) FROM INV_LOG
 WHERE STATUS='AVAILABLE'
 INTO INV_NO;
 IF (INV_NO > 0) THEN
 UPDATE INV_LOG SET STATUS=STATUS /* locks the record */
 WHERE INV_NUMBER= :INV_NO;
 ELSE
 BEGIN
 INV_NO = GEN_ID(GEN_INV_NUMBER, 1);
 INSERT INTO LOG_FACTURES(INV_NUMBER, GEN_DATE)
 VALUES(:INV_NO, 'NOW');
 END
 NEW.INV_NUMBER = :INV_NO;
 END

Une fois l'insertion terminée, on peut mettre à jour la table de log pour confirmer l'état 'USED' du numéro. Ceci sera fait à travers le trigger AfterInsert de la table FACTURES.

CREATE TRIGGER AI_INSERT_INV_NUM FOR FACTURES ACTIVE AFTER INSERT
 10 AS BEGIN
 UPDATE LOG_FACTURES SET STATUS='USED',
 ID_FACTURE = NEW.ID_FACTURE,
 MOTIF ='',
 LOGDATE = 'NOW'
 WHERE NUM_FACTURE = NEW.NUM_FACTURE;
 END

Nous allons ajouter deux procédures afin de supprimer ou d'annuler la création d'un nouveau document.

CREATE PROCEDURE SP_CANCEL_FACTURE (NUM_FACTURE INTEGER, MOTIF
 VARCHAR(50)) AS BEGIN
 UPDATE LOG_FACTURES
 SET STATUS = 'CANCELLED',
 MOTIF = :MOTIF
 WHERE NUM_FACTURE = :NUM_FACTURE
 END

CREATE PROCEDURE SP_ANNULE_NUM_FACT(NUM_FACTURE INTEGER) AS BEGIN
 UPDATE INV_LOG
 SET NUM_FACTURE = NULL,
 STATUS = 'AVAILABLE',
 REASON = 'INVOICE ABORTED',
 LOGDATE = 'NOW'
 WHERE NUM_FACTURE = :NUM_FACTURE;
 END

Enfin, le trigger appelé lors de la suppression d'un document.

CREATE TRIGGER AD_DELETE_INV_HDR FOR FACTURES ACTIVE AFTER DELETE
 POSITION 9 AS BEGIN
 UPDATE LOG_FACTURES
 SET NUM_FACTURE = NULL,
 STATUS='AVAILABLE',
 REASON='INVOICE DELETED',
 LOGDATE='NOW'
 WHERE NUM_FACTURE=OLD.NUM_FACTURE;
 END

Grâce à ces quelques scripts SQL, il est possible de créer des générateurs continus paramétrables acceptant des accès concurrentiels très facilement. Ces exemples sont en effet adaptables à quasiment tous les projets.

Merci à http://www.ibobjects.com, à yobenzen pour ses conseils, à bouyao pour la correction et à toute l'équipe developpez.com.