Les générateurs purs Interbase

Romain TOUTAIN

Création d'un générateur sans trou (générateur pur) pour Interbase/Firebird. ♪

- Titre : Les générateurs purs Interbase

- Auteur : Romain TOUTAIN

- Parution : 1 novembre 2005

- Licence : Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright ® 2005 Romain TOUTAIN. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. Introduction

- II. Les générateurs Interbase

- III. les générateurs continus

- IV. Une séquence pure

- V. Présentation des tables

- VI. Conclusion

I - Introduction

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.

II - Les générateurs Interbase

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.

III - les générateurs continus

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.

IV - Une séquence pure

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.

V - Présentation des tables

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

VI - Conclusion

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.