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

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 .

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.

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▲

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;

II-B. Mauvais exemple 2▲

 

Sélectionnez

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 :-(

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= 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 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 :

et

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