V2 - Table d'archivage (stockage vertical / transversal)

Hello,

J'ouvre à mon tour une issue pour proposer un système permettant d'avoir un historiques/archivages des infos de contacts.

Contexte Chez les entomos (mais pas certainement pas que), selon les groupes plus ou moins connus, il est quasiment primordial d'avoir un suivi des modifications ou des évolutions de la nomenclature. Ça peut être le cas si un groupe est peu connu, ou si la détermination d'un taxon est difficile…

Pour répondre à cette demande j'avais dans un premier temps réfléchi à une structuration de BDD ajoutant une notion d'identification d'une observation. (Une observation pouvant être identifiée une ou plusieurs fois par des déterminateurs différents). En plus du suivi des identifications, l'intérêt que je voyais à cette structuration était celui d'avoir une traçabilité de la nomenclature dans l'évolution du référentiel taxonomique (le fait d'avoir un suivi des identifications d'une observation aurait permis de savoir qu'à une version précédente du référentiel l'identification était différente de la nouvelle...). Finalement après réflexion, cette notion d'une ou plusieurs identifications pour une observation correspond juste à de l'archivage. Sauf qu'en plus, ce système apporte de la galère tant dans la BD qu'à l'interface... Seulement c'est une notion nécessaire !

Proposition Je me suis dit qu'un petit triggers déclenché lors d'un UPDATE ferait impeccablement bien le taf d'archivage ! Il suffit de créer une nouvelle table "h_occurences_contact" (h pour historique). Cette table est basée sur la structure de la table t_occurences_contact, on lui ajoute sa propre PK et deux champs pour stocker l'info de la date et de l'utilisateur qui a fait la modification.

Je pense que dans cette table les valeurs et non les clés des tables jointes doivent être stockées (quid du counting ?). Je pense également qu'il faut permettre le listing des champs qui, s'ils sont modifiés, permettent l'archivage de la ligne. (Ca peut peut-être passer par une table de paramétrage.) Idem, la table relevé serait-elle a archiver de cette manière ?

Nous sommes en train de travailler avec le PNCévennes sur une notion générique d'archivage des actions sur les tables (insert, update, delete). La piste serait qq chose de totalement générique reposant sur une sérialisation en json des enregistrements concernés et un stockage dans 2 tables (gn_meta ou un nouveau gn_history ou gn_tracking) Par contre dans ce cas, l'usage serait, au moins dans un premier temps, uniquement un usage administrateur de données.

Si l'historique doit être accessible en interface, c'est une autre histoire.

On va mettre ici ou ailleurs avec un renvoi, les premiers éléments et échanges à ce propos.

Oui l'idée de tracé, log, historique abordée de manière générique dans une table centralisée permettrait de partir sur un mécanisme plus générique et global, pas lié à un module. Il permettrait d'historiser des actions sur n'importe quel objet en y associant des actions (modification, suppression, validation, identification....).

Le fait de sérialiser les modifications dans un champs JSON unique permet de stocker les modifications de n'importe quel objet sans avoir à recréer les champs de sa structure de BDD. De plus PostgreSQL gère de mieux en mieux les infos JSON.

L'approche par trigger semble la plus intéressante car elle permet de ne pas alourdir l'interface. En ayant un mécanisme et une table unique, il serait plus facile d'afficher ces infos au niveau de l'interface. Pour chaque log d'une action sur un objet, il serait nécessaire de stocker le type d'objet, son ID, le type d'action, l'utilisateur et les valeurs.

Voici la première modélisation faire par le PnCevennes sur laquelle nous discutons actuellement :

tracked_objects

Ça veut dire que l'on ne gère plus les dates de modif/creation dans chaque table mais dans cette table centralisée
Idem pour le booleen DELETED. Ça aussi c'est une bonne chose. Ainsi quand on supprime un objet dans une table, on le supprime vraiment de la table mais on garde sa trace dans ces tables d'historique, en sérialisant dans un champs JSON ses attributs. Ainsi on n'aura plus à faire WHERE DELETED=False à chaque fois qu'on veut lister les objets d'une table

C'est à discuter/affiner car on n'est pas encore au clair sur la modélisation et le fonctionnement.

Le stockage en json declenché par triggers me semble être une bonne idée, à voir les capacité de postgres à exécuter des recherche sur un champ json... (trouve moi dans l'historique concernant la table XX les données dont l'ID_YYY = ZZZ ?) Par contre sauvegarder l'état d'une ligne brute avec ses FK ne me semble pas intéressant, si la valeur d'une FK change, si l'on a que la clé, on perd l'historique. Si on stocke la valeur associée à la FK, là c'est bonheur ! Après ces différentes choses dépendent du besoin qui justifie cet archivage.

Dans ce qui est prévu, seul les attributs sont prévus d'être stockés dans un champs en JSON. Les ID des objets, leurs tables, types etc... seraient dans des champs dédiés.

Le PNC a pensé a un mécanisme plus global, plus propre et plus générique pour les tables de stockage transversal. Cela permet notamment de ne pas répéter les noms de schéma et de table dans chaque enregistrement. Ca pourra être utilisé pour le suivi des actions sur un objet mais aussi pour les médias, la validation :

tracked_object

Une table centralise la liste de tous les objets existants dans GeoNature et leur attribut un id_object unique. Une BIB liste les types d'objets et leur localisation dans la BDD.

Problème de performances car pas de clé étrangère ? On fait un index mais on a pas de clé étrangère. A tester.
uuid_source plutôt que id_source qui est le même dans la table source et la synthèse. Il peut même remplacer id_object. C'est la clé primaire donc indexé.

tracked_uuid

Retour des tests de performance réalisés par @amandine-sahl :

Après de brefs tests et quelques lectures (cf lien ci dessous) il en ressort que :

Point positif des uuid :

unique et non séquentiel ce qui est bien car ça évite les collisions dans le cas des interbases
plus sécure au niveau des urls car on ne peut pas deviner le numéro de l'enregistrement suivant
bien supporté dans PostgreSQL avec uuid-ossp et pris en compte dans les index de types btree_gist dans PostgreSQL 10 sinon c'est juste un index hash qu'il faut vu que c'est du =

Point négatif :

plus volumineux : int : 4 bit, bigint 8bit et uuid : 16bit donc tables plus lourdes et hash plus long
impossible à mémorisé
plus couteux à l'insertion [1]

Pour faire les tests j'ai créé une base avec des tables avec 1 000 000 d'enregistrements la seule différence lors des jointures sur des serial ou des uuid est le coup du Hash -> UUID : (cost=35731.00..35731.00 rows=2000100 width=16); INT Hash (cost=32741.00..32741.00 rows=2000100 width=4)

CREATE EXTENSION "uuid-ossp";

CREATE TABLE ma_table1 (
 id serial PRIMARY KEY,
 value int,
 uuid uuid NOT NULL DEFAULT uuid_generate_v4()
);

INSERT INTO ma_table1(value)
SELECT a.n
from generate_series(1, 1000000) as a(n);

CREATE TABLE ma_table2 (
 id serial PRIMARY KEY,
 value int,
 uuid uuid NOT NULL DEFAULT uuid_generate_v4()
);

INSERT INTO ma_table2(value)
SELECT a.n
from generate_series(1, 1000000) as a(n);

CREATE TABLE tracked_objects (
 uuid uuid PRIMARY KEY,
 nom_table varchar(250),
 meta_create_date timestamp without time zone DEFAULT now()
);

INSERT INTO tracked_objects(uuid, nom_table)
SELECT uuid, 'ma_table1'
FROM ma_table1
UNION 
SELECT uuid, 'ma_table2'
FROM ma_table2;

CREATE TABLE cross_validation(
    uuid uuid PRIMARY KEY,
    statut varchar(250),
    meta_create_date timestamp without time zone DEFAULT now()
);

INSERT INTO cross_validation(uuid, statut)
SELECT uuid, 'valide'
FROM ma_table1
UNION 
SELECT uuid, 'non valide'
FROM ma_table2;

CREATE TABLE synthese (
 id_synthese serial PRIMARY KEY,
 id_table_source int,
 nom_table_source varchar(250),
 uuid uuid UNIQUE NOT NULL,
 meta_create_date timestamp without time zone DEFAULT now()
);

INSERT INTO synthese( id_table_source, nom_table_source, uuid)
SELECT id, 'ma_table1', uuid
FROM ma_table1
UNION 
SELECT id, 'ma_table2', uuid
FROM ma_table2;

CREATE TABLE cross_validation_synthese(
    id_synthese int PRIMARY KEY,
    statut varchar(250),
    meta_create_date timestamp without time zone DEFAULT now()
);

INSERT INTO cross_validation_synthese(id_synthese, statut)
SELECT id_synthese, 'valide'
FROM synthese;

-- ############## TEST REQUETES ############### --
-- 1.8 s
SELECT count(*)
FROM synthese s
JOIN cross_validation c
ON s.uuid = c.uuid

"Aggregate  (cost=173496.00..173496.01 rows=1 width=0)"
"  ->  Hash Join  (cost=70499.25..168495.75 rows=2000100 width=0)"
"        Hash Cond: (s.uuid = c.uuid)"
"        ->  Seq Scan on synthese s  (cost=0.00..38694.00 rows=2000100 width=16)"
"        ->  Hash  (cost=35731.00..35731.00 rows=2000100 width=16)"
"              ->  Seq Scan on cross_validation c  (cost=0.00..35731.00 rows=2000100 width=16)"

-- 1.6 s
SELECT count(*)
FROM synthese s
JOIN cross_validation_synthese c
ON s.id_synthese = c.id_synthese

"Aggregate  (cost=162690.00..162690.01 rows=1 width=0)"
"  ->  Hash Join  (cost=65555.25..157689.75 rows=2000100 width=0)"
"        Hash Cond: (s.id_synthese = c.id_synthese)"
"        ->  Seq Scan on synthese s  (cost=0.00..38694.00 rows=2000100 width=4)"
"        ->  Hash  (cost=32741.00..32741.00 rows=2000100 width=4)"
"              ->  Seq Scan on cross_validation_synthese c  (cost=0.00..32741.00 rows=2000100 width=4)"

-- AVEC INDEX HASH
CREATE INDEX index_synthese_uuid ON synthese USING hash (uuid);
CREATE INDEX index_cross_validation_uuid  ON cross_validation USING hash (uuid);

--1.8s
SELECT count(*)
FROM synthese s
JOIN cross_validation c
ON s.uuid = c.uuid

"Aggregate  (cost=173496.00..173496.01 rows=1 width=0)"
"  ->  Hash Join  (cost=70499.25..168495.75 rows=2000100 width=0)"
"        Hash Cond: (s.uuid = c.uuid)"
"        ->  Seq Scan on synthese s  (cost=0.00..38694.00 rows=2000100 width=16)"
"        ->  Hash  (cost=35731.00..35731.00 rows=2000100 width=16)"
"              ->  Seq Scan on cross_validation c  (cost=0.00..35731.00 rows=2000100 width=16)"

DROP INDEX index_synthese_uuid ;
DROP INDEX index_cross_validation_uuid;

-- ##### AVEC INDEX B-tree
CREATE INDEX index_synthese_uuid ON synthese USING btree (uuid);
CREATE INDEX index_cross_validation_uuid  ON cross_validation USING btree (uuid);

--1.8s
SELECT count(*)
FROM synthese s
JOIN cross_validation c
ON s.uuid = c.uuid

"Aggregate  (cost=173496.00..173496.01 rows=1 width=0)"
"  ->  Hash Join  (cost=70499.25..168495.75 rows=2000100 width=0)"
"        Hash Cond: (s.uuid = c.uuid)"
"        ->  Seq Scan on synthese s  (cost=0.00..38694.00 rows=2000100 width=16)"
"        ->  Hash  (cost=35731.00..35731.00 rows=2000100 width=16)"
"              ->  Seq Scan on cross_validation c  (cost=0.00..35731.00 rows=2000100 width=16)"

SELECT count(*)
FROM synthese s, cross_validation c
WHERE s.uuid = c.uuid

DROP INDEX index_synthese_uuid ;
DROP INDEX index_cross_validation_uuid;

Donc pour 2*10^6 enregistrements :

jointure sur id : 1.6 s
jointure sur uuid : 1.8s

Conclusion : il faudrait encore un peu tester mais il semblerait que ça aura très peu d'incidence, en tout cas pas sur 2*10^6 données.

1 : http://gosimple.me/postgresql-primary-key-type-analysis/ 2 : http://clearcove.ca/2017/08/postgres-uuid-as-primary-key-in-rails-5-1 3 : https://tomharrisonjr.com/uuid-or-guid-as-primary-keys-be-careful-7b2aa3dcb439 4 : https://stackoverflow.com/questions/33836749/postgresql-using-uuid-vs-text-as-primary-key 5 : https://stackoverflow.com/questions/22720130/how-to-use-uuid-with-postgresql-gist-index-type

Un schéma central gn_commons avec tracked_objects, media, validation...

bib_types_medias devient une nomenclature.

Mise à jour du modèle :

gn_commons

Encore plein de commits de malade, y en a trop pour les lister mais une bonne partie du résultat niveau BDD est visible ici : https://github.com/PnX-SI/GeoNature/blob/develop/data/core/commons.sql

FAIT PAR GIL :

création du schéma gn_commons (suppression de gn_medias)
mise en place de la validation générique (fonctions génériques et fonction trigger)
validation par défaut en place pour occtax (avec statut en attente de validation)
mise en place de mécanisme générique des logs dans gn_commons (bib_tables_location et t_history_actions + fonctions génériques et fonction trigger)
on logue tout occtax (4 tables) + les medias
on ne logue pas les validations car elle se fait par empilement des enregistrements des validations successives
suppression des deleted + meta_create_date + meta_update_date dans t_medias et tout Occtax
suppression des deleted dans Synthese mais j'ai gardé les meta_create_date + meta_update_date (pas très cohérent en y réfléchissant)
passage de la bib_types_medias en nomenclature
Traduction de tout ça dans le backend et un peu dans le front (peu d'impact à prioiri). Le backend n'est pas testé. La partie médias qui n'est pas encore utilisée n'est peut-être pas super fonctionnelle (affiner le comportement pour ce qui est Delete d'un média notamment)

TODO :

tester le backend pour valider son fonctionnement après toutes ces modifs (+ le front bien sur)
tester des insert, update delete depuis le front et vérifier si tout est bien logué dans t_history_actions
capturer les exceptions dans les fonctions génériques de log et de validation.
installer les logs sur d'autres tables : c'est très simple maintenant (ajout d'un enregistrement dans bib_tables_location et du trigger déclenchant le log pour la table à loguer, les fonctions génériques font le reste). Voir pour les méta notamment : AF, datasets surtout, voir la pertinence de ça pour le reste. Faut-il loguer les modif en nomenclature par exemple ?
vérifier les delete cascade en base sur Occtax
voir ce qu'on fait concernant le log du id_digitizer (non implémenté car la base n'a pas cette info)
faire les triggers t_validations --> Synthese
commencer les triggers Occtax --> Synthese

#############################################################################

Ci-dessous, un récap pour mémoire des discussions et choix :

HISTORIQUE DES VALIDATIONS :

GIL : On trace l'historique des médias, donc on doit avoir un uuid par média (idem dans validation)

CAMILLE : C'est pas plutôt qu'on trace des médias d'un objet donc on stocke ça par rapport à l'uuid de l'objet (occurrence, site de suivi...) dans l'historique ? Et idem pour la validation, ce qu'on historise c'est la validation d'un objet, non ?

AMANDINE : Non, c'est l'enregistrement média ou validation qui est tracé car dans le champ content il y a uniquement les données de la table et pas des enregistrements associés

THEO : Oui mais on trace l'historisation de la validation ou les médias par rapport à un objet. Donc dans tracked_object il faut stocker l'uuid de l'objet. L'objectif de cette table pour la validation c'est de se dire "pour tel occurrence je veux connaître toutes les validations qui ont été effectuées", non ?

AMANDINE : Dans les fait il y aura l'uuid de l'occurrence dans le champ content mais pour les perfs... Ca risque de pas être bon

GIL : En fait on n'a pas besoin d'historiser les validations dans t_history_actions (nouveau nom pour tracked_objects_actions). Ça va se faire tout seul. En effet, chaque fois qu'on enregistre une validation, on ajoute un enregistrement dans la table t_validations, avec un éventuel commentaire et l'uuid de l'enregistrement validé. pour connaitre le statut de validation on prend la validation_date la plus récente. Pour connaitre l'historique on consulte cette table. Donc de mon point de vue on n'a pas besoin de l'historiser. Si vous pensez qu'on doit avoir un seul enregistrement par occurrence dans t_validations, il faut boucler avec le module Validation, car dans les maquettes du module en cours de création il affichent plusieurs commentaires correspondant à des validations successives. Il faut qu'on reboucle sur ses 2 options car je suis en train d'implémenter plutôt le fait d'enregistrer plusieurs validations et non modifier la seule validation existante. Pour les perfs on travaillera dans la synthèse où il n'y aura que le dernier état.

CAMILLE : Je stockerai pas plusieurs validations par objet car ça va être plus galère à requeter, en devant à chaque fois vérifier la date... Si on veut un historique de validation (cas rare), on peut passer par les tracked_objects_actions. Les commentaires qu'ils affichent dans la maquette, j'ai compris que CT les commentaires de l'occurrence, pas de la validation. A rediscuter en effet, car en l’état ça ne va pas d'afficher que le commentaire, si cela concerne la validation, il faut aussi le statut.

GIL : En résumé, 2 options : 1/ à chaque validation on ajoute un enregistrement dans la table t_validations : Avantage :

on garde l'historique facilement accessible et notamment les commentaires de validation. SELECT id_nomenclature_valid_status, validation_comment, validation_date FROM gn_commons.t_validations WHERE uuid_attached_row = mon_uuid ORDER BY validation_date DESC
il est facile donc de retrouver les validations successives dans t_validations sans jointure et avec un simple WHERE sur l'uuid de l'occurrence
il est facile d'enregistrer les validations car on ne fait que des insert et dans une seule table
pour mettre à jour la synthèse il suffit d'un trigger insert qui update la synthèse

Inconvénients

il y a plusieurs états de validation successifs donc on doit rechercher le dernier mais :
- un max(validation_date) c'est pas très complexe. SELECT id_nomenclature_valid_status, id_validator, validation_comment, max(validation_date) FROM gn_commons.t_validations WHERE uuid_attached_row = mon_uuid GROUP BY id_nomenclature_valid_status, id_validator, validation_comment
- on a le dernier état en synthèse et je ne vois pas trop de cas où on doit lire en masse dans la source avec le max(validation_date)

2/ à chaque validation on écrase l'état précédent qu'on garde en json dans la table générale des historiques Avantage :

on accède facilement à l'état de validation dans t_validations car il n'y en a qu'un par enregistrement

Inconvénients

s'il faut historiser les validations, les état précédents sont peu accessibles (dans une autre table, avec des lectures à faire dans plusieurs json)
si les états successifs de validation doivent être consultable dans l'interface de validation ce n'est donc pas adapté
à chaque changement du statut de validation, il faut retrouver l'enregistrement dans t_validations puis le modifier (ou delete + insert)
pour mettre à jour la synthèse il faut donc un trigger insert et un trigger update

Je trouve l'option 1 plus souple, plus riche, plus lisible et plus simple à mettre en oeuvre. J'ai déjà commité la fonction trigger qui met le statut de validation à "en attente de validation" lors de l'ajout d'un enregistrement + le trigger sur After insert dans cor_counting_occtax. Au passage ce statut "en attente de validation" n'existe pas au SINP. Mais on pourrait considérer que s'il n'y pas de statut de validation dans t_validations pour un enregistrement c'est qu'il en attente de validation = null dans la synthèse.

AMANDINE : Je suis assez partisane de l'option 1 au vu des attentes sur la validation. Pour obtenir le dernier état, je conseille d'utiliser DISTINCT ON plutôt qu'un group by pour les perfs

CAM : OK merci pour ce recap, j'avais tous ces éléments en tête, j'avais pas pensé au max pour les dates et en effet on aura aussi l'info à plat dans la synthèse. Donc OK pour l'option 1. Mais du coup dans la table tracked_objects_action, il faudrait un id_digitizer pour savoir qui a réalisé la modif.

GIL : Il n'y a que le frontend qui peut passer cette info. On peut pas le faire avec un trigger. Si on le fait ça complique l'affaire car il serait bien d'automatiser ça uniquement coté bdd.

RETROUVER LES MEDIAS ET VALIDATIONS D'UN OBJET :

GIL : On n'a aucune info sur le média ou la validation dans la table de rattachement, c'est dans t_medias ou t_validations via uuid_attached_row qu'on sait si l'enregistrement dans la table de rattachement dispose d'un ou plusieurs média/validation (ou aucun).

CAMILLE : Oui à voir si c'est lourd à chaque fois qu'on veut savoir si un objet a un média ou non. Faire des fonctions pour faciliter ça ?

AMANDINE : J'aurai tendance à dire non car c'est déjà le cas à l'heure d'aujourd'hui. Il faut faire une jointure entre la table source et la table média pour savoir si un enregistrement a un média associé. Coté perf, je ne pense pas que ce soit énorme car c'est juste une jointure

ETATS HISTORISES :

GIL : On trace dans le json le NEW. C'est logique car pas de OLD pour le create et au premier update le OLD serait = au create. C'est la hiérarchie des dates qui donne l'état précédent (= avant dernière date). Si une seule date donc pas de modif. Pour le Delete il n'y a pas de NEW, on enregistre le OLD ou on enregistre un json vide car le OLD = la trace la plus récente.

CAMILLE : Oui. Pas compris pour le DELETE

AMANDINE : Pas d'avis, d'un coté c'est pratique d'avoir la valeur lors de la suppression sans avoir à se compliquer la vie avec les requêtes, d'un autre ce serait plus cohérent de stoker toujours le NEW et je ne suis pas sur qu'on fasse ce genre de requête tous les jours.

CLÉS COMPOSITES ET FONCTIONS :

GIL : Petite complexité sur les clés composites : pour tracer par exemple les modifications des observateurs d'un relevé occtax qui ont une clé primaire composite de 2 champs (id_releve et id_role), plusieurs options :

pour tracer la clé :
- changer le type de gn_commons.bib_tables_location.pk_field de varchar en array ou en text (on sérialise les clés composites)
- ajouter une pk non composite en serial (on l'a fait ailleurs il me semble)
pour tracer l'enregistrement
- ajouter un champ uuid dans pr_occtax.cor_role_releves_occtax (obligatoire si on veut tracer avec la fonction générique).
pour tracer les 2 en un seul champ
- On utilise l'uuid comme PK

on ne trace pas les modifications d'observateurs

Petite préférence pour tracer les 2 en un seul champ (uuid comme PK). Idem pour Théo.

Quelques explications :

La table tracked_objects_actions que j'ai nommée t_history_actions s'appuie sur l'unique table bib_tables_location qui a un id puis un champ par info importante pour décrire la structure des tables sources des enregistrements modifiés : c'est à dire schema + table pour retrouver la table de l'enregistrement tracé, le champ de la pk et le champ de l'uuid pour retrouver l'enregistrement tracé.

2 fonctions génériques créées ce matin permettent :

de retrouver d'abord l'id de la table 'sources' dans bib_tables_location
de retrouver à partir de ça le nom des champs pk et/ou uuid dans la table 'sources'. Avec ça on localise l'enregistrement n'importe où dans la base et on peut tracer les actions sur celui-ci mais à plusieurs conditions :
1. que la table source soit enregistrée dans bib_tables_location
2. qu'il y ait un uuid dans la table source
3. qu'on connaisse sa PK si on veut y revenir (par ex en l'ouvrant dans un formulaire)

Une fonction de log (qui utilise les 2 fonctions génériques ci-dessus) est utilisée par les triggers INSERT, UPDATE et DELETE. Donc pour la condition 3, le cas de la PK composite est problématique.

OK donc c'est bien avancé. Reste le TODO évoqué par @gildeluermoz :

tester le backend pour valider son fonctionnement après toutes ces modifs (+ le front bien sur)
tester des insert, update delete depuis le front et vérifier si tout est bien logué dans t_history_actions
capturer les exceptions dans les fonctions génériques de log et de validation.
installer les logs sur d'autres tables : c'est très simple maintenant (ajout d'un enregistrement dans bib_tables_location et du trigger déclenchant le log pour la table à loguer, les fonctions génériques font le reste). Voir pour les méta notamment : AF, datasets surtout, voir la pertinence de ça pour le reste. Faut-il loguer les modif en nomenclature par exemple ?
vérifier les delete cascade en base sur Occtax
voir ce qu'on fait concernant le log du id_digitizer (non implémenté car la base n'a pas cette info)
faire les triggers t_validations --> Synthese

Alors que @TheoLechemia travaille sur l'intégration du module Validation dans le coeur de GeoNature, petit retour sur les tables de stockage vertical / transversal :

En faisant de la doc sur le module de validation, on se rend compte que le mécanisme est assez complexe.

Pour valider une donnée, on écrit dans la table gn_commons.t_validations. Cette table comporte des champs obligatoires, notamment :

l'UUID
l'id_table_location

Quand on fait un POST dans t_validations, on poste l'UUID et l'id_nomenclature_validation de l'occurrence. La route doit donc retrouver l'id_table_location, pour cela on doit :

retrouver l'id_source synthese de la données à partir de l'UUID
faire une jointure avec gn_synthese.t_sources pour trouver le table_name et schema_name
utiliser la fonction gn_commons.get_table_location_id(schema_name, table_name) pour retrouver l'id_table_location
écrire enfin dans la table t_validations

Ce qui veut dire :

qu'une donnée n'ayant pas d'id_source ne peut pas être validée (car on ne retrouve pas son id_table_location)
que si on intègre des données externes dans la synthese, on doit renseigner les deux tables gn_synthese.t_sources et gn_commons.bib_tables_location, en ayant bien les mêmes noms de colonne pourschema_nameettable_name``

Tous ça est lourd et compliqué à comprendre.

On s'interroge donc sur l’intérêt de ce id_table_location dans la table t_validations, et sur le doublon entre gn_synthese.t_sources et gn_commons.bib_tables_location qui sont vraiment très proches.

Après discussion la solution retenue est :

On supprime l'id_table_location de la table t_validations et la contrainte vers gn_commons.bib_tables_location
Il reste possible de valider une donnée source sans passer par la synthèse (au niveau BDD)
Cela simplifie l'écriture de la validation
Cela nécessite de passer par l'id_source de la synthèse pour retrouver la localisation de la table d'une occurrence depuis t_validations en utilisant son UUID_SINP

Se posera la même question pour les médias, la sensibilité et l'historisation.

PnX-SI / GeoNature