Historisation en mode ligne

Cette liste n’étant pas limitative. Afin de distinguer les noms des colonnes correspondant à ces attributs, nous allons préfixer leur nom par un blanc souligné dans la table d’historisation. Notons qu’il est nécessaire de s’affranchir de toute contrainte de la table d’origine.

Par exemple les contraintes NOT NULL, PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE n’on pas à figurer dans la table d’historisation. Si votre SGBDR permet de créer des déclencheurs DDL alors il est facile de capturer un événement de création de table et de créer dans la foulée une table d’historisation. Exemple pour SQL Server 2005 : voir listing 3. On devra faire de même avec des déclencheurs DDL ALTER TABLE afin de répercuter les évolutions du schéma de la table.

Mais là, notre affaire se complique. En effet, étudions les différents cas de figure et leurs solutions : voir tableau 2. Un tel déclencheur pourrait s’écrire : voir listing 4. Vous noterez que nous avons laissé en commentaire la partie de code qui doit réaliser la modification de la table d’historisation. Le problème n’est pas trivial mais le code serait trop long à présenter dans cet article Il va falloir maintenant implanter le jeu de déclencheurs permettant de capturer les INSERT et les UPDATE. Ces déclencheurs peuvent eux aussi être réalisés dans le déclencheur DDL.

Listing 3
USE DB_PROD;
GO

CREATE TRIGGER E_DB_CRETAB
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN

SET NOCOUNT ON;

— récupération des informations du "paquet" d’événement du tgrigger DDL
DECLARE @XML XML, @SCH sysname, @TAB sysname;
SET @XML = EVENTDATA();
extraction à l’aide d’XQuery/XPath du nom du schema et du nom de table
SELECT @SCH = @XML.value(‘(/EVENT_INSTANCE/SchemaName)[1]’, ‘sysname’),
       @TAB = @XML.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘sysname’);

— génération d’une requête de création de la table d’historisation

DECLARE @SQL VARCHAR(max);
— un schéma existe-il avec ce nom là ?
IF NOT EXISTS (SELECT *
               FROM   DB_HST_LIGNE.INFORMATION_SCHEMA.SCHEMATA
               WHERE  SCHEMA_NAME = @SCH)
BEGIN
— non : on le créé
   SET @SQL = ‘CREATE SCHEMA ‘ + @SCH;
   EXEC (@SQL);
END;

— création de la table
SET @SQL = ‘CREATE TABLE DB_HST_LIGNE.’ + @SCH+’.’+@TAB + ‘ (‘
    + ‘_ID BIGINT NOT NULL IDENTITY PRIMARY KEY, _MD CHAR(1), ‘
    + ‘_DH DATETIME DEFAULT CURRENT_TIMESTAMP, ‘
    + ‘_SU NVARCHAR(128) DEFAULT USER, _MA NCHAR(40), ‘;
SELECT @SQL = @SQL + COLUMN_NAME + ‘ ‘ + DATA_TYPE +
       CASE
          WHEN DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’, ‘nvarchar’)
             THEN ‘ (‘ + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(16))
                  + ‘) COLLATE ‘ + COLLATION_NAME
          WHEN DATA_TYPE IN (‘decimal’, ‘numeric’)
             THEN ‘ (‘ + CAST(NUMERIC_PRECISION AS VARCHAR(16)) +’, ‘
                  + CAST(NUMERIC_SCALE AS VARCHAR(16)) + ‘)’
          ELSE  »
       END + ‘, ‘
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA = @SCH
  AND  TABLE_NAME = @TAB;
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) – 1) + ‘)’;
EXEC (@SQL);

END;

Listing 4
USE DB_PROD;
GO
CREATE TRIGGER E_DB_ALTTAB
ON DATABASE
FOR ALTER_TABLE
AS
BEGIN
SET NOCOUNT ON;
— récupération des informations du "paquet" d’événement du
tgrigger DDL
DECLARE @XML XML, @SCH sysname, @TAB sysname;
SET @XML = EVENTDATA();
— extraction à l’aide d’XQuery/XPath du nom du schema et du nom
de table
SELECT @SCH = @XML.value(‘(/EVENT_INSTANCE/SchemaName)[1]’,
‘sysname’),
@TAB = @XML.value(‘(/EVENT_INSTANCE/ObjectName)[1]’,
‘sysname’);
— génération d’une requête de création de la table
d’historisation
DECLARE @SQL VARCHAR(max);
/*
— cette modification a t-elle ajoutée des colonnes de la table ?
— supression d’une colonne => la colonne est renommée en #001,
#002, etc…
— changement de type d’une colonne => l’ancienne colonne est
renomée _#001 et la nouvelle ajoutée
— ajout d’une colonne,
*/
— c’est une modification de type, voici comment on la détecte :
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE
FROM DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS AS T
INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,
DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME,
NUMERIC_PRECISION, NUMERIC_SCALE
FROM
DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SCH
AND TABLE_NAME = @TAB
EXCEPT
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,
DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME,
NUMERIC_PRECISION, NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SCH
AND TABLE_NAME = @TAB) AS TE
ON T.TABLE_SCHEMA = TE.TABLE_SCHEMA
AND T.TABLE_NAME = TE.TABLE_NAME
AND T.COLUMN_NAME = TE.COLUMN_NAME
WHERE NOT EXISTS(SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME–,
DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME,
NUMERIC_PRECISION, NUMERIC_SCALE
FROM DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SCH
AND TABLE_NAME = @TAB
EXCEPT
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME–
, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME,
NUMERIC_PRECISION, NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SCH
AND TABLE_NAME = @TAB
UNION
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME–
, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME,
NUMERIC_PRECISION, NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SCH
AND TABLE_NAME = @TAB
EXCEPT
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME–
, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME,
NUMERIC_PRECISION, NUMERIC_SCALE
FROM DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SCH
AND TABLE_NAME = @TAB)
IF @@ROWCOUNT > 0
BEGIN
### faire le boulot !!!
END
— c’est un ajout, voici comment on le détecte :
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS AS T
INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME–
, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME,
NUMERIC_PRECISION, NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SCH
AND TABLE_NAME = @TAB
EXCEPT
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME-
-, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME,
NUMERIC_PRECISION, NUMERIC_SCALE
FROM
DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SCH
AND TABLE_NAME = @TAB) AS TE
ON T.TABLE_SCHEMA = TE.TABLE_SCHEMA
AND T.TABLE_NAME = TE.TABLE_NAME
AND T.COLUMN_NAME = TE.COLUMN_NAME
IF @@ROWCOUNT > 0
BEGIN
### faire le boulot !!!
END
— c’est une suppression, voici comment on la détecte :
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
COLLATION_NAME, NUMERIC_PRECISION, NUMERIC_SCALE
FROM DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS AS T
INNER JOIN (SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME–
, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME,
NUMERIC_PRECISION, NUMERIC_SCALE
FROM
DB_HST_LIGNE.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SCH
AND TABLE_NAME = @TAB
EXCEPT
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME-
-, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME,
NUMERIC_PRECISION, NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SCH
AND TABLE_NAME = @TAB) AS TE
ON T.TABLE_SCHEMA = TE.TABLE_SCHEMA
AND T.TABLE_NAME = TE.TABLE_NAME
AND T.COLUMN_NAME = TE.COLUMN_NAME
IF @@ROWCOUNT > 0
BEGIN
### faire le boulot !!!
END
### modification à reprendre :
— il faut ensuite modifier la table d’historisation suivant les
différents cas de figure :
SET @SQL = ‘ALTER TABLE DB_HST_LIGNE.’ + @SCH+’.’+@TAB + ‘ ADD
###’;
SELECT @SQL = @SQL + COLUMN_NAME + ‘ ‘ + DATA_TYPE +
CASE
WHEN DATA_TYPE IN (‘char’, ‘varchar’, ‘nchar’,
‘nvarchar’)
THEN ‘ (‘ + CAST(CHARACTER_MAXIMUM_LENGTH AS
VARCHAR(16))
+ ‘) COLLATE ‘ + COLLATION_NAME
WHEN DATA_TYPE IN (‘decimal’, ‘numeric’)
THEN ‘ (‘ + CAST(NUMERIC_PRECISION AS VARCHAR(16))
+’, ‘
+ CAST(NUMERIC_SCALE AS VARCHAR(16)) + ‘)’
ELSE  »
END + ‘, ‘
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @SCH
AND TABLE_NAME = @TAB;
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) – 1) + ‘)’;
EXEC (@SQL);
END;