/*Création CDC pour un table sur PostgreSQL
Pour créer une nouvelle table remplacer 'TABLE_LOG' par le nom de la table journal à créer et 'TABLE_SRC' par le nom de la table source à journaliser, et cela dans tout le script !*/
CREATE TABLE TABLE_LOG 
AS
/*Création de la table de journalisation avec un select pour utiliser le modèle de la table source*/
SELECT
	/*clé pour les opération effectué*/
	0::BIGINT AS operation_id,
	/*date de début de l'enregistrement (date d'insertion ou de modification)*/
	current_timestamp::timestamp AS date_debut,
	/*date de fin d'enregistrement (date de modification ou date de suppression)*/
	null::timestamp AS date_fin,
	/*modèle de la table cible*/
	TABLE_SRC.*
FROM TABLE_SRC
/*Pour conserver uniquement le schéma lors de cette création on utilise une condition fausse, cela permettra de créer une table vide avec les champs saisis, ici 'WHERE 1 = 0'*/
WHERE 1 = 0;

/*Gestion de la clé du journal*/
ALTER TABLE TABLE_LOG ADD PRIMARY KEY(operation_id);
CREATE SEQUENCE TABLE_LOG_operation_id_seq ;
ALTER TABLE TABLE_LOG ALTER operation_id SET DEFAULT nextval('TABLE_LOG_operation_id_seq'::regclass);
/*Gestion des dates de début et fin pour recupérer le moment des modifications et une date vide pour la date fin (en valeur par défaut)*/
ALTER TABLE TABLE_LOG ALTER date_debut SET DEFAULT current_timestamp;
ALTER TABLE TABLE_LOG ALTER date_fin SET DEFAULT null;

/*Fonction audit_TABLE_SRC() qui va tester les différents type d'opérations et ajouter les différents logs dans la table journal*/
/*Pensez à modifier les nom des champs 'id' en fonction de la table source, ici on a mis 'id' car la clé de la table source s'appelle 'id', sinon changer.*/

/*Aspect du journal :*/
/*INSERT*/
/*|   operation_id     |   date_debut      |   date_fin      |SRC_id |SRC_data |*/
/*|         1          |<date_d'insertion> |NULL             |   1   |   ...   |*/
/*...*/

/*UPDATE*/
/*Reprenons le dernier INSERT :*/
/*|   operation_id     |   date_debut      |   date_fin      |SRC_id |SRC_data |*/
/*|         2          |<date_d'insertion> |<date_de_modif_1>|   1   |   ...   |*/
/*|         3          |<date_de_modif_1>  |<date_de_modif_2>|   1   |<modif_1>|*/
/*|         4          |<date_de_modif_2>  |NULL             |   1   |<modif_2>|*/
/*...*/
/*NOTE : L'enregistrement actif est celui ayant une date de fin nulle*/

/*DELETE*/
/*Reprenons le dernier UPDATE :*/
/*|   operation_id     |   date_debut      |   date_fin      |SRC_id |SRC_data |*/
/*|         5          |<date_d'insertion> |<date_de_modif_1>|   1   |   ...   |*/
/*|         6          |<date_de_modif_1>  |<date_de_modif_2>|   1   |<modif_1>|*/
/*|         7          |<date_de_modif_2>  |<date_de_supp>   |   1   |<modif_2>|*/
/*...*/

/*L'idée est de ne prendre que les enregistrements ayant une date de fin nulle...
...pour avoir tous les enregistrements présent dans la table source et donc tous les enregistrements actifs.*/

CREATE OR REPLACE FUNCTION audit_TABLE_SRC_i() RETURNS TRIGGER AS $TABLE_LOG$
BEGIN
	/*Le cas de l'insertion...
	...Par défaut le journal aura donc la clé (en auto-increment), la date de début (en current_timestamp), la date de fin (en null), puis les champs de l'enregistrement modifié...
	...NEW est l'enregistrement modifié après la modification*/
    INSERT INTO TABLE_LOG VALUES (DEFAULT,DEFAULT, DEFAULT, NEW.*);
    RETURN NEW;
END;
$TABLE_LOG$ language plpgsql;

CREATE OR REPLACE FUNCTION audit_TABLE_SRC_u() RETURNS TRIGGER AS $TABLE_LOG$
BEGIN
	/*A la modification...
	...On va modifier l'enregistrement du journal correspondant à l'insertion ou à la dernière modification uniquement en modifiant la date de fin avec le current_timestamp...
	...NEW est l'enregistrement modifié après la modification*/
    UPDATE TABLE_LOG SET date_fin = current_timestamp WHERE <id> = NEW.<id> AND date_fin is null;
	/*...Par défaut le journal aura donc la clé (en auto-increment), la date de début (en current_timestamp), la date de fin (en null), puis les champs de l'enregistrement modifié */
    INSERT INTO TABLE_LOG VALUES (DEFAULT, DEFAULT, DEFAULT,  NEW.*);
    RETURN NEW;
END;
$TABLE_LOG$ language plpgsql;

CREATE OR REPLACE FUNCTION audit_TABLE_SRC_d() RETURNS TRIGGER AS $TABLE_LOG$
BEGIN
	/*DELETE*/
	/*OLD est l'enregistrement modifié avant la modification*/
    UPDATE TABLE_LOG SET date_fin = current_timestamp WHERE <id> = OLD.<id> AND date_fin is null;
    RETURN OLD;
END;
$TABLE_LOG$ language plpgsql;

/*Création du déclencheur après les INSERT, UPDATE et DELETE sur la table TABLE_SRC pour appliquer la fonction audit_TABLE_SRC_i(), audit_TABLE_SRC_u() et audit_TABLE_SRC_d()*/
CREATE TRIGGER TABLE_LOG_i AFTER INSERT ON TABLE_SRC FOR EACH ROW EXECUTE PROCEDURE audit_TABLE_SRC_i();
CREATE TRIGGER TABLE_LOG_u AFTER UPDATE ON TABLE_SRC FOR EACH ROW EXECUTE PROCEDURE audit_TABLE_SRC_u();
CREATE TRIGGER TABLE_LOG_d AFTER DELETE ON TABLE_SRC FOR EACH ROW EXECUTE PROCEDURE audit_TABLE_SRC_d();