Gérer les compteurs auto-incrémentés avec Interbase

| Pascal Barnouin | Sylvain James |

Cet article a pour but de vous aider à gérer de façon automatique les compteurs uniques auto-incrémentés avec Interbase.

- Titre : Gérer les compteurs auto-incrémentés avec Interbase

- Auteurs : Pascal Barnouin, Sylvain James

- Parution : 9 mai 2001

- Mise à jour : 31 décembre 2019

- Licence : Ce document est issu de https://www.developpez.com et reste la propriété exclusive de son auteur. La copie, modification et/ou distribution par quelque moyen que ce soit est soumise à l'obtention préalable de l'autorisation de l'auteur.

Sommaire

- I. Introduction/problématique

- I-A. Premier cas d'étude

- I-B. Second cas d'étude

- I-C. Troisième cas d'étude

- II. Techniques officielles et autres…

- II-A. Mauvais exemple 1

- II-B. Mauvais exemple 2

- III. La méthode officielle

I - Introduction/problématique

Si vous débutez avec Interbase, veuillez auparavant lire l'article de Henry ainsi que le nouveau tutoriel de jjm : Installez et configurez InterBase 6- Un excellent Serveur de base de données, par JJM.

Dans un contexte d'analyse portant sur de multiples tables, nous travaillons sur ce qu'on appelle un modèle relationnel. D'où l'acronyme SGBDR (Système de Gestion de Bases de Données Relationnelles). Ainsi certains champs d'une table peuvent faire référence à des champs d'autres tables. Exemple d'école, un client et ses commandes. Nous allons parler d'une table pour enregistrer les clients, et d'une table pour enregistrer les commandes de ces clients.

Pour chaque enregistrement de la table Commandes, nous devons indiquer de qui provient la commande. C'est la manière d'implémenter cette référence dont nous discutons ici. Alors, comment faire référence à un client particulier lors de l'enregistrement d'une commande ?

I-A - Premier cas d'étude

Nous pouvons répliquer le nom et le prénom de ce client dans la ligne Commandes. Méthode extrêmement déconseillée, car la moindre modification du nom du client (en cas d'erreur…) devra être répercutée sur tous les enregistrements Commandes où apparaît le nom initial.

Ce qui donne en modèle logique :

On voit bien sur le modèle physique qu'il y a redondance d'information sur les champs NOM et PRENOM, d'où redondance et gloutonnerie d'espace disque… et sachant également que les risques de doublons Nom/Prénom demeurent !

I-B - Second cas d'étude

La table Client peut comporter un champ qui contiendra une information dont on est certain de son unicité. Par exemple son numéro de sécurité sociale. Ainsi chaque ligne commande peut comporter un champ pour enregistrer le n° SS du client concerné par cette commande.

Converti en modèle logique (physique) :

I-C - Troisième cas d'étude

Prenons un cas où, malheureusement, on ne dispose pas d'informations dans la table client dont l'unicité est garantie (Nom, Prénom sont par exemple des valeurs qui peuvent comporter des doublons, même en créant une clé composée). Donc le moyen de garantir l'unicité de l'enregistrement est de créer soi-même un identifiant unique (on s'approche du but…) pour chaque enregistrement.

Il y a deux manières à ma connaissance de concevoir un identifiant unique :

1. Soit l'identifiant est un GUID (Globally Unique Identifier) style Microsoft - Merci Béranger pour le rappel :-) - , généré par une fonction dédiée au moment de l'insertion du nouvel enregistrement (solution SQL-SERVER avec son type de donnée uniqueidentifier) ;

2. Soit l'identifiant est un entier incrémenté à chaque création d'enregistrement - INSERT - (solution Paradox avec son type de champ Autoinc).

C'est cette deuxième technique que nous allons présenter dans le cadre d'Interbase. Voici déjà ce que ça donne sur le plan analyse :

et en modèle logique :

Donc le but est d'incrémenter un compteur à chaque insertion d'enregistrement et d'associer ce compteur à l'identifiant du client, soit ID_CLIENT.

II - Techniques officielles et autres…

Après cette simple présentation théorique vient le temps de l'implémentation. Interbase ne propose pas de type de données auto-incrémenté ou GUID, mais une feature particulièrement utile : les GÉNÉRATEURS (Generators in english). Avant de démontrer leur utilité, voyons ce qu'il ne faut pas faire et pourquoi.

II-A - Mauvais exemple 1

Récupérer à l'aide d'une requête SQL la valeur maxi du champ respectif à l'identifiant, incrémenter de un, et enregistrer le nouvel identifiant avec cette valeur.

with monQuery do
begin
SQL.Clear;
SQL.Add('SELECT MAX(Champ_Identifiant) + 1 FROM maTable');
Open;
maTable.Append;
maTable.Fields[0].AsInteger := monQuery.Fields[0].AsInteger;
// renseignement des autres champs de la table
maTable.Post;
end;

Dans un contexte mono-utilisateur ça marche, mais… déjà le SELECT MAX est extrêmement défavorisant en termes de puissance, ressources, car il est obligé de parcourir toute la table pour chercher la valeur maxi du champ. Ensuite, en client-serveur, rien ne dit que deux utilisateurs ne vont pas lancer cette requête en même temps, avant que chacun n'ait pu « poster » son enregistrement. Il y a des risques pour que ces deux utilisateurs se retrouvent avec le même identifiant !

Par rapport au problème de rapidité, d'autres ont créé une table Compteurs avec deux champs : un pour la table concernée, un pour le compteur. Ainsi à chaque demande d'identifiant, on consulte l'enregistrement correspondant à la table voulue, on extrait la valeur du compteur, on l'incrémente, on l'utilise, on enregistre la nouvelle valeur du compteur. Nettement plus efficace qu'un SELECT MAX certes, mais le problème de concurrence d'accès n'est toujours pas résolu.

II-B - Mauvais exemple 2

with monQuery do
begin
SQL.Clear;
SQL.Add('SELECT Champ_Compteur FROM maTableCompteurs WHERE NomCompteur = 'NomTable');
Open;
cpt := Fields[0].AsInteger + 1;
SQL.Clear;
SQL.Add('INSERT INTO maTableCompteurs (Champ_Compteur) VALUES ' + inttostr(cpt) + ' WHERE NomCompteur = 'NomTable');
Open;
maTable.Append;
maTable.Fields[0].AsInteger := monQuery.Fields[0].AsInteger + 1;
// renseignement des autres champs de la table
maTable.Post;
end;

Schéma UML d'accès asynchrone en lecture/écriture d'un compteur :

On voit bien sur ce diagramme de séquence UML qu'un compteur peut être lu par plusieurs utilisateurs avant d'être mis à jour. C'est relativement gênant lorsque l'on souhaite obtenir un identifiant unique, gasp :-(

III - La méthode officielle

Un générateur est une variable gérée et stockée par InterBase, à laquelle on peut accéder en lecture et en écriture via des opérations d'incrémentation ou de décrémentation. On utilise les générateurs pour produire des identifiants uniques. Interbase fournit une fonction Gen_Id (Nom générateur, pas d'incrémentation) pour lire et modifier la valeur d'un générateur. Il ne peut y avoir de conflits lors d'appels concurrents à cette fonction.

Syntaxe de création d'un générateur : Create Generator NomGenerateur;

La méthode officielle va être de créer une procédure stockée qui va renvoyer la valeur d'un générateur incrémentée de 1 :

Create Procedure Table_Pkey_Gen returns (avalue INTEGER)
as
begin
avalue = gen_id(NomGenerateur,1);
end^

Ainsi, avant chaque enregistrement dans la table (exactement dans le BeforePost du TQuery), on récupère le nouvel identifiant via la procédure stockée précédente :

if (MonQuery.State = dsInsert) then

begin
// Le compo StoredProc fait référence à la procédure stockée précédente
StoredProc1.ExecProc;

MonQuery.FieldByName('Identifiant').AsInteger := StoredProc1.FieldByName('avalue').AsInteger;

end;

Il est recommandé également de prendre ses précautions et de laisser le soin à InterBase de renseigner automatiquement le champ identifiant en cas d'omission du développeur. Il suffit de tester au moment de l'enregistrement (Before Insert) si le champ identifiant est renseigné.
Pour cela, on crée un trigger :

Create Trigger MaTable_Trig_BI for MaTable
active before Insert position 0
as

begin
if (new.Identifiant is NULL) then
new.Identifiant = gen_id(NomGenerateur,1);
end^

Notons que seul le trigger pourrait suffire, mais nous avons quasiment tout le temps besoin de récupérer immédiatement le nouvel identifiant créé.
Donc voilà, vous disposez d'une structure fiable de gestion d'identifiants auto-incrémentés !
Mais…
Cette méthode est tout de même controversée, car elle utilise les procédures stockées, qui, si trop nombreuses peuvent enrayer les performances du système. C'est pourquoi Kloo nous présente sa méthode très astucieuse et performante.

Voici la solution que j'ai (je=kloo) choisie pour pouvoir récupérer le compteur lors d'un Insert dans une table, afin de pouvoir passer ce compteur dans une autre table (jointure).

Developpez ;-)

Téléchargez et installez le composant IBGen qui se trouve ici.

Dans la Form Principale, placez ce composant et écrivez la procédure suivante :

function TForm1.GetCpt(Gen:String):LongInt;
begin
with IBGenerator1 do
begin
GeneratorName:=Gen;
Result:=Increment;
end;
end;

Quand vous voulez faire un INSERT dans une table et que vous avez besoin du compteur ensuite, il suffit d'écrire le code suivant :

procedure TForm1.Button1Click(Sender: TObject);
var LeCpt:LongInt;
begin
with Query1 do
begin
SQL.Text:='INSERT INTO MATABLE (CPT_MATABLE,MONCHAMP) VALUES (:LeCpt,:MonChamp);';
LeCpt:=GetCpt('GEN_MATABLE'); // Nom du générateur pour MATABLE
ParamByName('LeCpt').AsInteger:=LeCpt;
ParamByName('MonChamp').AsString:='Ma valeur'
try
ExecSQL;
except
ShowMessage('Problème lors de l''insertion dans MATABLE');
end;
// Dans AUTRETABLE je n'ai pas besoin de récupérer le compteur, je laisse le TRIGGER le générer
SQL.Text:='INSERT INTO AUTRETABLE (AUTRECHAMP,CPT_MATABLE) VALUES (:AutreChamp,:LeCpt);';
ParamByName('AutreChamp').AsString:='Autre valeur'
ParamByName('LeCpt').AsInteger:=LeCpt; // Clé étrangère vers MATABLE
dans AUTRETABLE
try
ExecSQL;
except
ShowMessage('Problème lors de l''insertion dans MATABLE');
end;
end;
end;

Dans ma base de données, j'ai (je=kloo) pris la peine de créer les Generateurs (Generator) et Declencheurs (Trigger) suivant :

CREATE GENERATOR GEN_MATABLE;

SET TERM ^;

CREATE TRIGGER BEFORE_INSERT_MATABLE FOR MATABLE
ACTIVE BEFORE INSERT AS
BEGIN
IF ( NEW.CPT_MATABLE IS NULL ) THEN
BEGIN
NEW.CPT_MATABLE=GEN_ID(GEN_MATABLE,1);
END

CREATE GENERATOR GEN_AUTRETABLE;

SET TERM ^;

CREATE TRIGGER BEFORE_INSERT_AUTRETABLE FOR AUTRETABLE
ACTIVE BEFORE INSERT AS
BEGIN
IF ( NEW.CPT_AUTRETABLE IS NULL ) THEN
BEGIN
NEW.CPT_AUTRETABLE=GEN_ID(GEN_AUTRETABLE,1);
END

Donc, dans le premier Insert, le TRIGGER ne fait pas NEW.CPT_MATABLE=GEN_ID(GEN_MATABLE,1); puisque CPT_MATABLE n'est pas null, dans le deuxième Insert CPT_AUTRETABLE est initialisé par NEW.CPT_AUTRETABLE=GEN_ID(GEN_AUTRETABLE,1)

Maintenant, si vous avez 50 tables dans votre base, il faudra créer 50 générateurs et 50 déclencheurs. L'utilitaire de Sylvain va vous permettre d'automatiser tout cela. Il est téléchargeable ici.

Pour tout complément d'information, n'hésitez pas à poser une question sur le forum interbase ou à contacter les auteurs de cet article :

Sylvain James et Kloo (Pascal Barnouin)

copyright Interbasenautes ( news://news.vienneinfo.org/nzn.fr.interbase )- 2001 - France - All rights reserved.