V2 - Table d'archivage (stockage vertical / transversal) #339
Comments
|
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 ( 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. 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. L'approche par trigger semble la plus intéressante car elle permet de ne pas alourdir l'interface. Voici la première modélisation faire par le PnCevennes sur laquelle nous discutons actuellement :
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 ! |
|
Dans ce qui est prévu, seul les attributs sont prévus d'être stockés dans un champs en JSON. |
|
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 :
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. |
|
|
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 :
Point négatif :
Pour faire les tests j'ai créé une base avec des tables avec 1 000 000 d'enregistrements 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 :
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/ |
|
Un schéma central
|
|
Mise à jour du modèle :
|
|
Premier commit de traduction en SQL : 3cd9d69 |
|
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 :
TODO :
############################################################################# 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 THEO : Oui mais on trace l'historisation de la validation ou les médias par rapport à un objet. Donc dans AMANDINE : Dans les fait il y aura l'uuid de l'occurrence dans le champ GIL : En fait on n'a pas besoin d'historiser les validations dans CAMILLE : Je stockerai pas plusieurs validations par objet car ça va être plus galère à requeter, en devant à chaque fois vérifier la date... GIL : En résumé, 2 options :
Inconvénients
2/ à chaque validation on écrase l'état précédent qu'on garde en json dans la table générale des historiques
Inconvénients
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 AMANDINE : Je suis assez partisane de l'option 1 au vu des attentes sur la validation. 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. 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 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 : 1/
2/
Petite préférence pour tracer les 2 en un seul champ (uuid comme PK). Idem pour Théo. Quelques explications : La table 2 fonctions génériques créées ce matin permettent :
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. |
camillemonchicourt
modified the milestones:
V2 - 2018 - Sprint April,
V2 - 2018 - Sprint July
|
OK donc c'est bien avancé.
|
|
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
Quand on fait un POST dans
Ce qui veut dire :
Tous ça est lourd et compliqué à comprendre. On s'interroge donc sur l’intérêt de ce Après discussion la solution retenue est :
Se posera la même question pour les médias, la sensibilité et l'historisation. |
camillemonchicourt
changed the title
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 ?
The text was updated successfully, but these errors were encountered: