Historisation sous PostgreSql¶

Il n’est pas rare d’avoir comme réflexion d’un utilisateur: mais ce n’est pas moi qui a changée cette donnée ... qui a changé cette donnée ou pire mias c’est toi l’informaticien qui change nos données (oui oui juste pour embêter les utilisateurs)

Quand on utilise une base de donnée il est relativement simple de créer une gestion de l’historisation des changements via l’utilisation de triggers

Nous allons voir comment nous pouvons mettre en place cette gestion sur la table CPTE sur une base PostgreSql

Le principe est de créer:

une table d’historisation contenant les éléments de la table à historiser plus
- date de l’historisation
- id de la table d’historisation
- user: personne qui a lancé la modification
trigger de create sur la table à historiser
trigger de delete sur la table à historiser
trigger de update sur la table à historiser
les n users

Warning

notre gestion utilise comme user qui a modifié la table le user de connexion à la base il faut donc pour avoir un suivi fin avoir un user de connexion par utilisateur

ce qui donne au niveau sql

CREATE TABLE cpte (
    id        serial CONSTRAINT key_cpte PRIMARY KEY,
    lib       varchar(100) NOT NULL,
    actif     boolean DEFAULT true
);

CREATE TABLE cpte_histo (
    id_histo  serial CONSTRAINT key_cpte_histo PRIMARY KEY,
    id          integer,
    lib       varchar(100),
    actif     boolean,
    userm      varchar(100),
    action      varchar(100),
    date      varchar(100) not null default 'timeofday'
);

CREATE ROLE "UserA" LOGIN PASSWORD 'usera'
    VALID UNTIL 'infinity';

CREATE ROLE "UserB" LOGIN PASSWORD 'userb'
    VALID UNTIL 'infinity';

CREATE OR REPLACE FUNCTION  histo_cpte_insert_update() RETURNS TRIGGER AS
 $BODY$
BEGIN
    INSERT INTO cpte_histo(id, lib, actif, userm, action)
            VALUES (NEW.id, NEW.lib, NEW.actif, CURRENT_USER,'INSERT OR UPDATE');
    RETURN NEW;
END ;
 $BODY$
 LANGUAGE 'plpgsql' ;

CREATE TRIGGER histo_cpte_insert_update_trg AFTER INSERT OR UPDATE ON cpte
    FOR EACH ROW EXECUTE PROCEDURE histo_cpte_insert_update();

CREATE OR REPLACE FUNCTION  histo_cpte_delete() RETURNS TRIGGER AS
 $BODY$
BEGIN
    INSERT INTO cpte_histo(id, lib, actif, userm, action)
            VALUES (OLD.id, OLD.lib, OLD.actif, CURRENT_USER, 'DELETE');
    RETURN NEW;
END ;
 $BODY$
 LANGUAGE 'plpgsql' ;

CREATE TRIGGER histo_cpte_delete_trg AFTER DELETE ON cpte
    FOR EACH ROW EXECUTE PROCEDURE histo_cpte_delete();

Un peu d’utilisation:

avec le user postgres

INSERT INTO cpte(lib) VALUES ('coucou');
commit;

avec le userA

UPDATE cpte SET lib='coucou2' where id=1;
commit;
UPDATE cpte SET lib='coucou3' where id=1;
commit;

avec le userB

DELETE FROM cpte WHERE id=1;
commit;

le résultat obtenu est le suivant

SELECT * FROM cpte_histo WHERE id=1;

id_histo	id	lib	actif	userm	action	date
6	3	coucou	t	postgres	INSERT OR UPDATE	Mon Dec 26 11:09:36.380000 2011 CET
7	3	coucou2	t	postgres	INSERT OR UPDATE	Mon Dec 26 11:09:38.380000 2011 CET
8	3	coucou3	t	postgres	INSERT OR UPDATE	Mon Dec 26 11:09:42.380000 2011 CET
9	3	coucou3	t	postgres	DELETE	Mon Dec 26 11:09:44.380000 2011 CET