IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)

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

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

Article lu   fois.

Les deux auteurs

Liens sociaux

Viadeo Twitter Facebook Share on Google+   

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 .

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.

Image non disponible

Ce qui donne en modèle logique :

Image non disponible

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.

Image non disponible

Converti en modèle logique (physique) :

Image non disponible

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 :

Image non disponible

et en modèle logique :

Image non disponible

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.

 
Sélectionnez
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

 
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 :

Image non disponible

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 :

 
Sélectionnez
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 :

 
Sélectionnez
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 :

 
Sélectionnez
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 :

 
Sélectionnez
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 :

 
Sélectionnez
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 :

 
Sélectionnez
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.

Vous avez aimé ce tutoriel ? Alors partagez-le en cliquant sur les boutons suivants : Viadeo Twitter Facebook Share on Google+   

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.