Gestion générique des tables de référence

Toute application informatique utilisant une base de données est appelée à travailler avec des tables de références. Ces tables contiennent des informations quasi statiques nécessaire au paramétrage du fonctionnement de l’application ou à l’enrichissement des données. Voici un article qui explique comment utilise de manière génériques de telle tables.

Code Fr̩d̩ric Brouard Рhttp://sqlpro.developpez.comwww.sqlspot.com Р2009-06-03

Une table de référence, c’est par exemple la table des civilités, celles des villes et de leur code postal, une table de noms de mois, de jours de semaine… Bref des listes de données quasi statiques dont on identifie les valeurs par des clefs.

Bien que les exemples de cet article soient écrits sous forme de code propre à SQL Server, il est facile de les adapter à tout bon serveur de bases de données relationnel à condition qu’il soit capable d’utiliser des vues « misajourbales » au travers de déclencheurs INSTEAD OF.

Dans notre cas, afin de rendre générique le codes des procédures, nous devons faire en sorte que toutes les tables de références soient crées strictement de la même manière. Pour cela nous utilisant une norme interne de développement (visible sur mon site d’entreprise SQL spot) qui indique que les tables de références doivent être codifiées comme suit :
1) leur nom doit commencer par « T_R_ » suivi d’un libellé explicite, et doit se terminer par un trigramme (3 lettres) précédé d’un caractère blanc souligné. Le trigramme doit être unique dans la base de données, c’est à dire qu’aucune autre table ne doit porter le même trigramme. Bien entendu les caractères utilisés doivent être ceux de la norme SQL (voir à ce propos l’article que j’ai écrit) et en particulier le trigramme ne doit pas comporter de blanc souligné.
2) la table de référence doit comporter en tout et pour tout 5 colonnes, chacune commençant par le trigramme en suffixe du nom de table. Voici la description de ces colonnes :


XXX_ID        INT NOT NULL PRIMARY KEY,   -- auto incrément si possible
XXX_CODE      CHAR(16) NOT NULL,          -- par exemple, servira de clef subrogée
XXX_LIBELLE   VARCHAR(128) NOT NULL,      -- par exemple, pour une description
XXX_BASE      BOOLEAN NOT NULL DEFAULT 0, -- sert à interdire toute modification de la ligne
XXX_ORDRE     INT                         -- sert à définir un ordre pré établi

Exemple :


CREATE TABLE T_R_CIVILITE_CVT
(CVT_ID        INT NOT NULL IDENTITY PRIMARY KEY,
 CVT_CODE      CHAR(16) NOT NULL,
 CVT_LIBELLE   VARCHAR(128) NOT NULL,
 CVT_BASE      BIT NOT NULL DEFAULT 0,
 CVT_ORDRE     INT)

Nous vous conseillons de placer toutes vos tables de référence dans un seul et même schéma SQL distinct des autres objets de la base, par exemple un schéma SQL de nom S_REF, à l’aide de l’ordre SQL :
CREATE SCHEMA S_REF;

La colonne CVT_BASE si elle vaut 1 lors de l’insertion, indique que cette donnée ne peut plus être modifiée ni supprimée. Imaginez ce qui se passerait si vos lignes de code client attendent une telle valeur et qu’un utilisateur peu averti la supprime. Pour assurer cette protection, il faut réaliser un trigger dans chacune des tables. Travail fastidieux… sauf s’il est entrepris par SQL lui même. En fait nous allons réaliser une procédure stockée qui va dynamiquement créer ce trigger sur chacune des tables de référence. Vous commencez maintenant à comprendre pourquoi le nommage des tables et des colonnes doit être si précis !

De la même façon pour faire en sorte que la colonne XXX_CODE soit une clef subrogée, donc dotée d’une contrainte d’unicité, nous allons utiliser une procédure stockée qui réalise l’implantation de cette contraintes sur toutes les tables en une seule passe, à l’aide d’un code SQL dynamique.

Enfin, pour travailler de manière ensembliste toutes nos tables de référence d’une seul coup, nous allons créer une troisième procédure stockée qui va créer une vue en concaténant toutes les tables de référence à l’aide de l’opérateur SQL UNION. Mais pour distinguer dans la vue les lignes d’une table et celle d’une autre, nous allons rajouter à cette vue une colonne contenant le trigramme propre à chaque table. Ainsi les lignes d’une table de référence ou d’une autre seront, par ce biais, facilement distinguées dans la vue.

Pour manipuler cette vue, c’est à dire réaliser des INSERT, UPDATE et DELETE directement dans la vue, nous allons coder 3 déclencheurs INSTAED OF dont la particularité est de faire croire à la vue qu’elle peut être mise à jour. Dans ces triggers, nous dérouterons le code vers des procédures spécialisées l’une pour l’insertion ou la modification, l’autre pour la suppression. Mais comme par nature le code des déclencheurs est ensembliste dans SQL Server, nous devrons utiliser des curseurs… Mais rassurez vous ce ne sont pas des curseurs sur des tables de production, mais sur les pseudo tables contenant les images avant et après des données manipulées dans l’ordre SQL qui à déclenché le trigger… Bref, des curseurs indolores car portant sur des tables locales ayant en général peu de lignes !

Enfin, pour couronner ,le tout, nous y ajouterons une procédure de vidage de table de référence, forçant le déclencheur qui interdit de toucher aux lignes dont la colonne XXX_BASE vaut 1, et quelques fonctions pour accéder aux valeurs, tantôt en passant le code ou l’id…

Récapitulons la liste des outils qu’il nous faut créer :
– Une procédure « one shot » pour créer les triggers interdisant la modification ou la suppression des lignes avec BASE = 1
– Une procédure « one shot » pour créer les contraintes d’unicité sur la colonne CODE
РUne proc̩dure cr̩ant la vue de concat̩nation de toutes les tables de r̩f̩rence
РUne proc̩dure INSERT/UPDATE pour les r̩f̩rences
РUne proc̩dure DELETE pour les r̩f̩rences
РUn d̩clencheur INSTEAD OF INSERT sur la vue des r̩f̩rences reroutant vers la proc̩dure INSERT/UPDATE
РUn d̩clencheur INSTEAD OF UPDATE sur la vue des r̩f̩rences reroutant vers la proc̩dure INSERT/UPDATE
РUn d̩clencheur INSTEAD OF DELETE sur la vue des r̩f̩rences reroutant vers la proc̩dure DELETE
– Une procédure de vidage d’une table de référence
РUne fonction de r̩cup̩ration de libelle ref par ID + trigramme
РUne fonction de r̩cup̩ration de code ref par ID + trigramme
РUne fonction de r̩cup̩ration de libelle ref par code + trigramme
– Une fonction de récupération de l’id ref par code + trigramme
C’est partit !

Procédure « one shot » pour créer les triggers interdisant la modification ou la suppression des lignes avec BASE = 1 :


CREATE PROCEDURE dbo.P_DDL_REF_CREATE_TRIGGER
AS
/******************************************************************************  
* Procédure de création automatique des trigger des tables de référence pour  *  
* la gestion de l'interdiction de modification ou suppression de ligne        *
* lorsque xxx_BASE vaut 1                                                     *
*******************************************************************************  
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *  
******************************************************************************/
SET NOCOUNT ON;  
DECLARE @SQL NVARCHAR(max), @TABLE_NAME NVARCHAR(261), @TRG NCHAR(3);
DECLARE C CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY  
FOR  
   SELECT '[' + TABLE_SCHEMA +'].[' + TABLE_NAME +']', SUBSTRING(TABLE_NAME, LEN(TABLE_NAME) -2, 3)
   FROM   INFORMATION_SCHEMA.TABLES
   WHERE  TABLE_NAME LIKE 'T?_R?_%' ESCAPE '?'
   AND    TABLE_TYPE = 'BASE TABLE';
 
OPEN C;
 
FETCH C INTO @TABLE_NAME, @TRG
 
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @SQL = 'CREATE TRIGGER E_IU_REF_' + @TRG + CHAR(13) + CHAR(10)  
       + 'ON ' + @TABLE_NAME + CHAR(13) + CHAR(10)
       + 'FOR UPDATE, DELETE ' + CHAR(13) + CHAR(10)
       + 'AS ' + CHAR(13) + CHAR(10)
       + '/****************************************************************************** ' + CHAR(13) + CHAR(10)
       + '* Déclencheur créé automatiquement par procédure P_DDL_REF_CREATE_TRIGGER     * ' + CHAR(13) + CHAR(10)
       + '* pour la gestion de l''interdiction de modification/suppression des lignes    * ' + CHAR(13) + CHAR(10)
       + '* marquées BASE = 1 dans les tables de référence                              * ' + CHAR(13) + CHAR(10)
       + '******************************************************************************* ' + CHAR(13) + CHAR(10)
       + '* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 * ' + CHAR(13) + CHAR(10)
       + '******************************************************************************/ ' + CHAR(13) + CHAR(10)
       + 'SET NOCOUNT ON;'+ CHAR(13) + CHAR(10)  
       + 'BEGIN ' + CHAR(13) + CHAR(10)
       + 'IF EXISTS(SELECT * ' + CHAR(13) + CHAR(10)
       + '             FROM   INSERTED ' + CHAR(13) + CHAR(10)
       + '             WHERE ' + @TRG +'_BASE = 1) ' + CHAR(13) + CHAR(10)
       + '   BEGIN ' + CHAR(13) + CHAR(10)
       + '      ROLLBACK; ' + CHAR(13) + CHAR(10)
       + '      RAISERROR(''Table de référence ('+ @TABLE_NAME+') : La ligne que vous avez tenté de mettre à jour ne peut pas être modifié ni supprimée.'', 16, 1); ' + CHAR(13) + CHAR(10)
       + '   END ' + CHAR(13) + CHAR(10)
       + 'END ' + CHAR(13) + CHAR(10)  
       
   PRINT @SQL; --> pour information
   EXEC (@SQL);    
       
   FETCH C INTO @TABLE_NAME, @TRG
 
END;        
 
CLOSE C;
 
DEALLOCATE C;
 
GO

Exécution de ladite procédure :


EXEC dbo.P_DDL_REF_CREATE_TRIGGER;
 
GO

Procédure « one shot » pour créer les contraintes d’unicité sur la colonne CODE :


CREATE PROCEDURE dbo.P_DDL_REF_CREATE_UNIQUE
AS
/******************************************************************************  
* Procédure de création automatique des contrainte d'unicité des tables de    *
* référence pour la colonne xxx_CODE                                          *  
*******************************************************************************  
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *  
******************************************************************************/  
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(max), @TABLE_NAME NVARCHAR(261), @TRG NCHAR(3);
DECLARE C CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY  
FOR  
   SELECT '[' + TABLE_SCHEMA +'].[' + TABLE_NAME +']', SUBSTRING(TABLE_NAME, LEN(TABLE_NAME) -2, 3)
   FROM   INFORMATION_SCHEMA.TABLES
   WHERE  TABLE_NAME LIKE 'T?_R?_%' ESCAPE '?'
   AND    TABLE_TYPE = 'BASE TABLE';
 
OPEN C;
 
FETCH C INTO @TABLE_NAME, @TRG
 
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @SQL = 'ALTER TABLE ' + @TABLE_NAME + ' ADD CONSTRAINT UK_' + @TRG + '_CODE UNIQUE (' + @TRG + '_CODE);'
       
   PRINT @SQL; --> pour information
   EXEC (@SQL);    
       
   FETCH C INTO @TABLE_NAME, @TRG
 
END;        
 
CLOSE C;
 
DEALLOCATE C;
 
GO

Exécution de ladite procédure

EXEC dbo.P_DDL_REF_CREATE_UNIQUE;
 
GO

Procédure créant la vue de concaténation de toutes les tables de référence :


CREATE PROCEDURE dbo.P_DDL_REF_CREATE_VIEW
AS
/******************************************************************************  
* Procédure de création automatique de la vue concaténant toutes les tables   *
* de référence de la base                                                     *  
*******************************************************************************  
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *  
******************************************************************************/  
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(max);
SET @SQL = 'CREATE VIEW S_REF.V_REF AS '+ CHAR(13) + CHAR(10);
 
SELECT @SQL = @SQL + 'SELECT ''' + SUBSTRING(TABLE_NAME, LEN(TABLE_NAME) -2, 3) +''' AS REF, ' + CHAR(13) + CHAR(10)
                               + SUBSTRING(TABLE_NAME, LEN(TABLE_NAME) -2, 3) +'_ID AS REF_ID, ' + CHAR(13) + CHAR(10)
                               + SUBSTRING(TABLE_NAME, LEN(TABLE_NAME) -2, 3) +'_CODE AS REF_CODE, ' + CHAR(13) + CHAR(10)                                
                               + SUBSTRING(TABLE_NAME, LEN(TABLE_NAME) -2, 3) +'_LIBELLE AS REF_LIBELLE, ' + CHAR(13) + CHAR(10)                                                              
                               + SUBSTRING(TABLE_NAME, LEN(TABLE_NAME) -2, 3) +'_BASE AS REF_BASE, ' + CHAR(13) + CHAR(10)  
                               + SUBSTRING(TABLE_NAME, LEN(TABLE_NAME) -2, 3) +'_ORDRE AS REF_ORDRE ' + CHAR(13) + CHAR(10)  
                   + 'FROM   [' + TABLE_SCHEMA +'].[' + TABLE_NAME +']' + CHAR(13) + CHAR(10)  
                   + 'UNION ALL '  + CHAR(13) + CHAR(10)  
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_NAME LIKE 'T?_R?_%' ESCAPE '?'
AND    TABLE_TYPE = 'BASE TABLE';
 
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) -12);
 
PRINT @SQL; --> pour information
EXEC (@SQL);    
 
GO

Exécution de ladite procédure

EXEC dbo.P_DDL_REF_CREATE_VIEW;
 
GO

Procédure de gestion des insertions et modifications dans la vue S_REF.V_REF :


CREATE PROCEDURE S_REF.P_IU_REF @REF      CHAR(3),  
                                @ID       INT,  
                                @CODE     CHAR(16),  
                                @LIBELLE  VARCHAR(128)
                                @BASE     bit,
                                @ORDRE    int
AS
/******************************************************************************  
* Procédure de gestion de la mise à jour INSERT / UPDATE des tables de ref.   *
*******************************************************************************  
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *  
******************************************************************************/  
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(max);
DECLARE @TBL NVARCHAR(261);
SELECT @TBL = '[' + TABLE_SCHEMA + '].[' + TABLE_NAME +']'
FROM   INFORMATION_SCHEMA.TABLES  
WHERE  TABLE_NAME LIKE '%?_' + @REF ESCAPE '?'
AND    TABLE_TYPE = 'BASE TABLE';
 
IF @ID IS NULL
   -- insertion
   SET @SQL = 'INSERT INTO ' + @TBL + ' VALUES ('''  
              + REPLACE(@CODE, '''', '''''') + ''', '''
              + REPLACE(@LIBELLE, '''', '''''') + ''', '
              + CAST(@BASE AS NVARCHAR(32)) + ', '              
              + CAST(@ORDRE AS NVARCHAR(32)) + ');'
ELSE
   -- modification
   SET @SQL = 'UPDATE ' + @TBL + ' SET '  
              + @REF + '_CODE    = ''' + REPLACE(@CODE, '''', '''''') + ''', '
              + @REF + '_LIBELLE = ''' + REPLACE(@LIBELLE, '''', '''''') + ''', '              
              + @REF + '_BASE    = ' + CAST(@BASE AS NVARCHAR(32)) + ', '              
              + @REF + '_ORDRE   = ' +CAST(@ORDRE AS NVARCHAR(32)) + ') '
              + 'WHERE ' + @REF + '_ID = ' + CAST(@ID AS NVARCHAR(32)) +';'    
EXEC (@SQL);                                        
GO

Procédures de gestion des suppressions à travers la vue S_REF.V_REF :


CREATE PROCEDURE S_REF.P_D_REF @REF      CHAR(3),  
                               @ID       INT
AS
/******************************************************************************  
* Procédure de gestion de la suppression des lignes des tables de reference   *
*******************************************************************************  
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *  
******************************************************************************/  
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(max);
DECLARE @TBL NVARCHAR(261);
SELECT @TBL = '[' + TABLE_SCHEMA + '].[' + TABLE_NAME +']'
FROM   INFORMATION_SCHEMA.TABLES  
WHERE  TABLE_NAME LIKE '%?_' + @REF ESCAPE '?'
AND    TABLE_TYPE = 'BASE TABLE';
 
SET @SQL = 'DELETE FROM ' + @TBL + ' '
         + 'WHERE ' + @REF + '_ID = ' + CAST(@ID AS NVARCHAR(32)) +';'
 
EXEC (@SQL);                                        
GO

Déclencheur INSTEAD OF INSERT sur la vue S_REF.V_REF :


CREATE TRIGGER E_REF_I
ON S_REF.V_REF
INSTEAD OF INSERT
AS
/******************************************************************************  
* Déclencheur INSTEAD OF INSERT sur la vue des références                     *
*******************************************************************************  
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *  
******************************************************************************/  
BEGIN
SET NOCOUNT ON;
DECLARE @REF CHAR(3),@CODE CHAR(16),  
        @LIBELLE VARCHAR(128), @BASE bit, @ORDRE int;
DECLARE CEI CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY  
FOR  
   SELECT REF, REF_CODE, REF_LIBELLE, REF_BASE, REF_ORDRE
   FROM   INSERTED;
OPEN CEI;    
FETCH CEI INTO @REF, @CODE, @LIBELLE, @BASE, @ORDRE;
WHILE @@FETCH_STATUS = 0
BEGIN
   EXEC S_REF.P_IU_REF @REF, NULL, @CODE, @LIBELLE, @BASE, @ORDRE;
   FETCH CEI INTO @REF, @CODE, @LIBELLE, @BASE, @ORDRE;
END;
CLOSE CEI
DEALLOCATE CEI;
END;
GO

Déclencheur INSTEAD OF UPDATE sur la vue S_REF.V_REF :


CREATE TRIGGER E_REF_U
ON S_REF.V_REF
INSTEAD OF UPDATE
AS
/******************************************************************************  
* Déclencheur INSTEAD OF UPDATE sur la vue des références                     *
*******************************************************************************  
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *  
******************************************************************************/  
BEGIN
SET NOCOUNT ON;
DECLARE @REF CHAR(3), @ID INT, @CODE CHAR(16),  
        @LIBELLE VARCHAR(128), @BASE bit, @ORDRE int;
DECLARE CEU CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY  
FOR  
   SELECT REF, REF_ID, REF_CODE, REF_LIBELLE, REF_BASE, REF_ORDRE
   FROM   INSERTED;
OPEN CEU;    
FETCH CEU INTO @REF, @ID, @CODE, @LIBELLE, @BASE, @ORDRE;
WHILE @@FETCH_STATUS = 0
BEGIN
   EXEC S_REF.P_IU_REF @REF, @ID, @CODE, @LIBELLE, @BASE, @ORDRE;
   FETCH CEU INTO @REF, @ID, @CODE, @LIBELLE, @BASE, @ORDRE;
END;
CLOSE CEU
DEALLOCATE CEU;
END;
GO

Déclencheur INSTEAD OF DELETE sur la vue S_REF.V_REF :


CREATE TRIGGER E_REF_D
ON S_REF.V_REF
INSTEAD OF DELETE
AS
/******************************************************************************  
* Déclencheur INSTEAD OF UPDATE sur la vue des références                     *
*******************************************************************************  
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *  
******************************************************************************/  
BEGIN
SET NOCOUNT ON;
DECLARE @REF CHAR(3), @ID INT;
DECLARE CED CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY  
FOR  
   SELECT REF, REF_ID
   FROM   INSERTED;
OPEN CED;    
FETCH CED INTO @REF, @ID;
WHILE @@FETCH_STATUS = 0
BEGIN
   EXEC S_REF.P_D_REF @REF, @ID;
   FETCH CED INTO @REF, @ID;
END;
CLOSE CED
DEALLOCATE CED;
END;
GO

Procédure de vidage d’une table outrepassant les lignes avec XXX_BASE = 1 :


CREATE PROCEDURE S_REF.P_TRUNCATE_REF @REF CHAR(3)
AS
/******************************************************************************  
* Procédure de vidage d'une table de références                               *
*******************************************************************************  
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *  
******************************************************************************/  
   SET NOCOUNT ON;
   DECLARE @SQL NVARCHAR(max);
   DECLARE @TBL NVARCHAR(261);
   SELECT @TBL = '[' + TABLE_SCHEMA + '].[' + TABLE_NAME +']'
   FROM   INFORMATION_SCHEMA.TABLES  
   WHERE  TABLE_NAME LIKE '%?_' + @REF ESCAPE '?'
   AND    TABLE_TYPE = 'BASE TABLE';
   
   SET @SQL = 'ALTER TABLE ' + @TBL + ' DISABLE TRIGGER ALL; '
             +'DELETE FROM ' + @TBL + '; '
             +'ALTER TABLE ' + @TBL + ' ENABLE TRIGGER ALL; ';
   EXEC (@SQL);  
 
GO

Fonction récupérant un libellé à partir du trigramme + ID :


CREATE FUNCTION S_REF.F_A_PARAM_GET_VAL_BY_ID (@TRG CHAR(3), @ID INT)
   RETURNS VARCHAR(128)
AS
/******************************************************************************  
* Fonction de récupération d'un libellé de référence par son ID               *
* et son trigramme                                                            *
*******************************************************************************  
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *  
******************************************************************************/  
BEGIN
   DECLARE @OUT VARCHAR(128);
   IF @TRG IS NULL OR @ID IS NULL RETURN NULL;
   SELECT  @OUT = REF_LIBELLE
   FROM    S_REF.V_REF    
   WHERE   REF = @TRG
     AND   REF_ID = @ID;
   RETURN (@OUT);
END;
GO

Fonction récupérant un code à partir du trigramme + ID :


CREATE FUNCTION S_REF.F_A_PARAM_GET_COD_BY_ID (@TRG CHAR(3), @ID INT)
   RETURNS CHAR(16)
AS
/******************************************************************************  
* Fonction de récupération d'un code de référence par son ID                  *
* et son trigramme                                                            *
*******************************************************************************  
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *  
******************************************************************************/  
BEGIN
   DECLARE @OUT CHAR(16);
   IF @TRG IS NULL OR @ID IS NULL RETURN NULL;
   SELECT  @OUT = REF_CODE
   FROM    S_REF.V_REF    
   WHERE   REF = @TRG
     AND   REF_ID = @ID;
   RETURN (@OUT);
END;
GO

Fonction récupérant un libellé à partir du trigramme + code :


CREATE FUNCTION S_REF.F_A_PARAM_GET_VAL_BY_CODE (@TRG CHAR(3), @CODE CHAR(16))
   RETURNS VARCHAR(128)
AS
/******************************************************************************  
* Fonction de récupération d'un libellé de référence par son code             *
* et son trigramme                                                            *
*******************************************************************************  
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *  
******************************************************************************/  
BEGIN
   DECLARE @OUT VARCHAR(128)
   IF @TRG IS NULL OR @CODE IS NULL RETURN NULL;
   SELECT  @OUT = REF_LIBELLE
   FROM    S_REF.V_REF    
   WHERE   REF = @TRG
     AND   REF_CODE = @CODE;
   RETURN (@OUT);
END;
GO

Fonction récupérant un ID à partir du trigramme + code :


CREATE FUNCTION S_REF.F_A_PARAM_GET_ID_BY_CODE (@TRG CHAR(3), @CODE CHAR(16))
   RETURNS INT
AS
/******************************************************************************  
* Fonction de récupération d'un id de référence par son code                  *
* et son trigramme                                                            *
*******************************************************************************  
* Fred. Brouard - http://sqlpro.developpez.com - www.sqlspot.com - 2009-06-03 *  
******************************************************************************/  
BEGIN
   DECLARE @OUT INT;
   IF @TRG IS NULL OR @CODE IS NULL RETURN NULL;
   SELECT  @OUT = REF_ID
   FROM    S_REF.V_REF    
   WHERE   REF = @TRG
     AND   REF_CODE = @CODE;
   RETURN (@OUT);
END;
GO

Maintenant à vous de jouer.
Voici par exemple comment insérer des lignes dans la table de référence des civilités à travers la vue :


INSERT INTO S_REF.V_REF ( REF,   REF_CODE, REF_LIBELLE,   REF_BASE, REF_ORDRE)
       VALUES           ('CVT', 'M.',     'Monsieur',     0,        1        );
INSERT INTO S_REF.V_REF ( REF,   REF_CODE, REF_LIBELLE,   REF_BASE, REF_ORDRE)
       VALUES           ('CVT', 'Mme.',   'Madame',       0,        2        );
INSERT INTO S_REF.V_REF ( REF,   REF_CODE, REF_LIBELLE,   REF_BASE, REF_ORDRE)
       VALUES           ('CVT', 'Mlle.',  'Mademoiselle', 0,        3        );

Et comment constater qu’elle y sont bien :

SELECT *
FROM   S_REF.V_REF;

Conclusion :
Au final dans votre code client, vous n’avez plus besoin que d’une seule interface pour gérer toutes vos tables de références grâce à la généricité de cette structuration de votre base !
Le code est concis et s’exécute directement sur le serveur. Il y a peu d’aller retour, et c’est en gain important car un réseau est très lent en comparaison des processeurs d’un serveur.
C’est donc par nature déjà optimisé… Contrairement aux solutions purement clientes…
Cela réduit aussi drastiquement le nombre de lignes de code, c’est donc par nature plus fiable (moins il y a de lignes, moins il y a de bugs… c’est mathématique !).
La maintenance de l’ensemble est grandement facilité… puisqu’il n’y a à intervenir qu’en un seul point du code pour tout problème…

Y verriez-vous un seul inconvénient ?


--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *

MVP Microsoft SQL Server

9 réflexions au sujet de « Gestion générique des tables de référence »

  1. Avatar de Julian50Julian50

    Bonjour,

    Dans le cas de la civilité (M.,Mme) on pourrait aussi utiliser un check avec la liste des possibilités.

    Ma question est donc la suivante :
    Quand faut-il utiliser un check ou quand faut-il utiliser une table référence ?

  2. Avatar de sqlprosqlpro Auteur de l’article

    Sur le fait de faire l’inverse, c’est à dire une seule table du référentiel « divisée » en sous tables, cela revient au même, mais pose un autre problème. Pour des raisons de performances, il est intéressant de stocker les tables de référence les moins mise à jour (par exemple civilité, codes postaux…) sur un espace de stockage qui soit en lecture seule. Cela évite toute mise en place de verrous sur ces tables lors des requêtes (rappelons que même un simple SELECT pose des verrous). De plus, pour des besoins fonctionnels, certaines tables de références peuvent figurer dans des schémas SQL Différents. C’est la raison pour laquelle je préfère de multiples tables physique et une seule vue logique.
    Quant au chargement préalable en mémoire, je l’ai indiqué dans mon document sur la norme de nommage http://www.sqlspot.com/Norme-de-developpement.html.

    Sur les suppressions logiques, le fait de rajouter une telle colonne pose plus de problème qu’il n’en résout :
    il faut dans la vue éliminer les lignes ayant été supprimées logiquement
    il faut dans les requêtes avec la vue étudier si l’on doit faire des jointures internes ou externes
    Pour ma part, je considère qu’il serait préférable de mettre une date d’obsolescence et de gérer dans l’IHM le fait que ce qui est obsolète ne plus être saisi.

    Quand à la colonne ordre, avec un peu d’astuce elle peut servir à différentes choses. Par exemple, dans une applications, nous avions besoin de prédéfinir des granularité de temps, telles que :

    INSERT INTO S_REF.V_REF ( REF,   REF_CODE, REF_LIBELLE,     REF_BASE, REF_ORDRE)&nbsp;<br />
    &nbsp;      VALUES             -- les pas de temps de 5 minutes à 1 an (REF_ORDRE est en minute)&nbsp;<br />
    &nbsp;                         --&gt; année grégorienne = 365,2425 j (soit = 365 j, 5h  49m) &nbsp;<br />
    &nbsp;                         --&gt; que nous avons arrondis 365 j, 5h  50m pour les calculs. &nbsp;<br />
    &nbsp;                         --&gt; Soit encore : 543120 + 300 + 50 = 543470&nbsp;<br />
    &nbsp;                         --&gt; Si bien qu'un mois fait : 45289,166666666666666666666666667 minutes&nbsp;<br />
    &nbsp;                       ('PDT', '5 MN',    '5 minutes',     1,         5        ),&nbsp;<br />
    &nbsp;                       ('PDT', '6 MN',    '6 minutes',     1,         6        ),&nbsp;<br />
    &nbsp;                       ('PDT', '10 MN',   '10 minutes',    1,         10       ),&nbsp;<br />
    &nbsp;                       ('PDT', '12 MN',   '12 minutes',    1,         12       ),&nbsp;<br />
    &nbsp;                       ('PDT', '15 MN',   '15 minutes',    1,         15       ),&nbsp;<br />
    &nbsp;                       ('PDT', '20 MN',   '20 minutes',    1,         20       ),&nbsp;<br />
    &nbsp;                       ('PDT', '30 MN',   '30 minutes',    1,         30       ),&nbsp;<br />
    &nbsp;                       ('PDT', '1 HR',    '1 heure',       1,         60       ),&nbsp;<br />
    &nbsp;                       ('PDT', '2 HR',    '2 heures',      1,         120      ),&nbsp;<br />
    &nbsp;                       ('PDT', '4 HR',    '4 heures',      1,         240      ),&nbsp;<br />
    &nbsp;                       ('PDT', '6 HR',    '6 heures',      1,         360      ),&nbsp;<br />
    &nbsp;                       ('PDT', '12 HR',   '12 heures',     1,         720      ),&nbsp;<br />
    &nbsp;                       ('PDT', '1 JR',    '1 jour',        1,         1440     ),&nbsp;<br />
    &nbsp;                       ('PDT', '2 JR',    '2 jours',       1,         2880     ),&nbsp;<br />
    &nbsp;                       ('PDT', '4 JR',    '4 jours',       1,         5760     ),&nbsp;<br />
    &nbsp;                       ('PDT', '1 SM',    '1 semaine',     1,         10080    ),&nbsp;<br />
    &nbsp;                       ('PDT', '8 JR',    '8 jours',       1,         11520    ),&nbsp;<br />
    &nbsp;                       ('PDT', '15 JR',   '15 jours',      1,         21600    ),&nbsp;<br />
    &nbsp;                       ('PDT', '1 MS',    '1 mois',        1,         45289    ),&nbsp;<br />
    &nbsp;                       ('PDT', '2 MS',    '2 mois',        1,         90578    ),&nbsp;<br />
    &nbsp;                       ('PDT', '3 MS',    '3 mois',        1,         135868   ),&nbsp;<br />
    &nbsp;                       ('PDT', '6 MS',    '6 mois',        1,         271735   ),&nbsp;<br />
    &nbsp;                       ('PDT', '1 AN',    '1 an',          1,         543470   );

    Dans cette saisie, la colonne ordre sert à stocker les multiples de minutes pour la granularité de temps choisit.

  3. Avatar de emc51emc51

    Intéressant.

    Tout à fait d’accord sur la nécessité des normes de nommage. En les respectant, on n’a généralement plus à chercher quel peut être le nom de tel champ.

    J’ai juste pris l’habitude de suffixer au lieu de prefixer ;)

    Une petite remarque : il arrive que dans ces tables on ait à faire des suppressions logiques : « on ne va plus gérer tel type de xxx »…

    Bien entendu on souhaite dans ce cas conserver l’intégrité de l’historique, mais ne plus lister l’élément désactivé.

    On pourrait faire ça en ajoutant une colonne XXX_ACTIF

  4. Avatar de PerrichPerrich

    J’utilise une solution sensiblement identique mais directement avec une seule table, sans vue ou trigger et avec des codes internes en plus (pour pouvoir coder en dur dans l’application des constantes sans s’occuper des ids).

    La table est de ce type :

    CREATE TABLE REFERENTIAL
    (REF_ID INT NOT NULL IDENTITY PRIMARY KEY,
    REF_CODE CHAR(16) NULL,
    REF_LIBELLE VARCHAR(128) NOT NULL,

    REF_TYPE VARCHAR(20) NOT NULL, — Peut être un entier référençant une autre table des types de données
    REF_INTERN VARCHAR(50) NOT NULL,

    REF_BASE BIT NOT NULL DEFAULT 0,
    REF_ORDRE INT)

    INSERT INTO REFERENTIAL ( REF_TYPE, REF_CODE, REF_LIBELLE, REF_INTERN, REF_BASE, REF_ORDRE)
    VALUES (‘CVT’, ‘M.’, ‘Monsieur’, 0, ‘CVT_MR’, 1 );
    INSERT INTO REFERENTIAL ( REF_TYPE, REF_CODE, REF_LIBELLE, REF_INTERN, REF_BASE, REF_ORDRE)
    VALUES (‘CVT’, ‘Mme.’, ‘Madame’, 0, ‘CVT_MRS’, 2 );
    INSERT INTO REFERENTIAL ( REF_TYPE, REF_CODE, REF_LIBELLE, REF_INTERN, REF_BASE, REF_ORDRE)
    VALUES (‘CVT’, ‘Mlle.’, ‘Mademoiselle’, 0, ‘CVT_MS’, 3 );

    Après, il faut une procédure pour insérer, une pour supprimer et une pour la sélection. L’application charge la table en mémoire, au chargement (si besoin un refresh en cas de modification) et se charge de filtrer les combobox par REF_TYPE. L’application se charge de toutes les requêtes de recherche/conversion en mémoire, il n’y a plus de transfert réseau de ces données.

    1. Avatar de mediminuemediminue

      Commentaire un peu tardif…
      S’il faut ajouter une date d’obsolescence, il faut donc déroger au « dogme » de n’avoir que 5 colonnes dans une table de référence.
      De plus, peut-être qu’il faudrait également ajouter une date de début de validité d’où encore une entorse à la règle.
      Je ne suis pas sûr d’avoir un éclaircissement tant de temps après l’écriture de votre article, mais quoiqu’il en soit je vous remercie de toute la doc que vous avez mise en ligne et qui est une source précieuse.

Laisser un commentaire