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
======== == =======  ===== ======== ================= ===================================