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 .. mpd:: A [User="UserA"]; B [User="UserB"]; C [Table="CPTE"]; D [Table="CPTE_HISTO"]; A -> C; B -> C; C .> D [label="create"]; C .> D [label="update"]; C .> D [label="delete"]; 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 .. code-block:: 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 .. code-block:: sql INSERT INTO cpte(lib) VALUES ('coucou'); commit; * avec le userA .. code-block:: sql UPDATE cpte SET lib='coucou2' where id=1; commit; UPDATE cpte SET lib='coucou3' where id=1; commit; * avec le userB .. code-block:: sql DELETE FROM cpte WHERE id=1; commit; le résultat obtenu est le suivant .. code-block:: sql 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 ======== == ======= ===== ======== ================= ===================================