TRIGGER LOG ACTION - Suggestion de modification de la fonction "fct_trg_log_changes"

[andriacap]

Bonjour,

Dans le cadre d'une prestation pour l'Agence Régionale de la Biodiversité en île de France, il a été suggéré de pouvoir récupérer les informations liées à des tables avec clé étrangère.

Le module concerné dans la discussion est le module monitoring pour lequel la fonction fct_trg_log_changes appelé par les trigger lors d'insertions, d'update, et de suppression ne retourne que les données liées aux tables t_base_site et non les informations stockées dans t_site_complements (même chose pour les visites et observations) .

L'idée que j'ai eu pour le moment serait de changer la fonction fct_trg_log_changes en ajoutant deux paramètres (par défaut null pour garder le comportement actuel de la fonction) . Ces deux paramètres seraient le nom de la table et de la column qui vont servir à la jointure .
Ainsi si les deux paramètres ne sont pas NULL alors on réaliserait la jointure avant d'utiliser la fonction row_to_json .

On peut être aussi passer par l'ajout de tables dans bib_tables_location pour réaliser les jointures , (exemple ajout de colonne fk_field à l'instar de la colonne pk_field), à voir / discuter.

Je ne suis pas expert en sql, donc si des personnes avec une vision un peu plus aiguisée en sql ont des propositions plus pertinentes je suis tout ouïe .

Voici le code actuel de la fonction :

CREATE OR REPLACE FUNCTION gn_commons.fct_trg_log_changes()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
DECLARE
	theschema text := quote_ident(TG_TABLE_SCHEMA);
	thetable text := quote_ident(TG_TABLE_NAME);
	theidtablelocation int;
	theuuidfieldname character varying(50);
	theuuid uuid;
	theoperation character(1);
	thecontent json;
BEGIN
	--Retrouver l'id de la table source stockant l'enregistrement à tracer
	SELECT INTO theidtablelocation gn_commons.get_table_location_id(theschema,thetable);
	--Retouver le nom du champ stockant l'uuid de l'enregistrement à tracer
	SELECT INTO theuuidfieldname gn_commons.get_uuid_field_name(theschema,thetable);
	--Retrouver la première lettre du type d'opération (C, U, ou D)
	SELECT INTO theoperation LEFT(TG_OP,1);
	--Construction du JSON du contenu de l'enregistrement tracé
	IF(TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
		--Construction du JSON
	select new.*,
		thecontent :=  row_to_json(NEW.*);
		--Récupérer l'uuid de l'enregistrement à tracer
		EXECUTE format('SELECT $1.%I', theuuidfieldname) INTO theuuid USING NEW;
	ELSIF (TG_OP = 'DELETE') THEN
		--Construction du JSON
		thecontent :=  row_to_json(OLD.*);
		--Récupérer l'uuid de l'enregistrement à tracer
		EXECUTE format('SELECT $1.%I', theuuidfieldname) INTO theuuid USING OLD;
	END IF;
  --Insertion du statut de validation et des informations associées dans t_validations
  INSERT INTO gn_commons.t_history_actions (id_table_location,uuid_attached_row,operation_type,operation_date,table_content)
  VALUES(
    theidtablelocation,
    theuuid,
    theoperation,
    NOW(),
    thecontent
  );
  RETURN NEW;
END;
$function$

[camillemonchicourt]

Ne faut-il pas simplement ajouter le trigger sur la table "t_site_complements" pour aussi suivre les modifications dans cette table ?

[andriacap]

Ah oui si ça convient de logger les données qui concerne un même objet avec deux uuid différents c'est plus simple effectivement

[andriacap]

Hello,

Du coup , pas trop d'avis supplémentaires ? On peut partir sur la création d'une revision alembic qui dépend du HEAD de la branche "geonature" dans laquelle on ajoute les tables site, visit complément dans bib_tables_location ainsi que l'ajout du trigger à chacune de ces tables ?

[amandine-sahl]

C'est peu être plutôt dans gn_monitoring que la révision doit être réalisée avec la prise en compte l'ensemble des tables.

[andriacap]

Oui effectivement ça semble plus logique

[andriacap]

Du coup ça impliquerait d'ajouter une colonne uuid à chacune de ces tables et de les peupler dans ces revisions alembic car elle est nécessaire à la table t_history_actions (uuid_attached_row not null)

[camillemonchicourt]

C'est peut-être un sujet plus global, mais de ce que j'avais compris les tables "complements" avaient vocation a être supprimées.
Elles n'ont été créées à l'époque que pour ajouter un champs JSON additionnel aux tables des sites, des visites et observations sans toucher au cœur de GeoNature.

Mais ces champs JSON auraient plutôt vocation à être intégrés dans les tables de base créées et gérées dans gn_monitoring, dans le cœur de GeoNature.

Car en l'état cela complexifie et éclate le modèle de données sans intérêt.

J'avais en tête que cela serait revu et intégré au cœur de GeoNature à l'occasion de l'évolution du modèle de Monitoring - PnX-SI/gn_module_monitoring#117

Peut-être que c'est un sujet plus global et complexe, et qu'on peut laisser comme ça pour le moment, mais on voit qu'on va devoir complexifier l'historisation des données pour des tables qui ont été éclatées sans apport, mais pas mal de complexité.