Bonjour à tous,
Je développe actuellement une base de données relationnelles sur SQLite avec l'extension SpatiaLite pour une application en archéologie. La structure est un peu complexe est j'inscrirai ici uniquement les quelques tables qui concernent mon problème actuel que je vais essayer de décrire du mieux possible.
Cette base de données doit pouvoir fonctionner sur des interfaces mobiles, comme QField par exemple (application Android SIG) et sur des interfaces PC sur QGIS (logiciel SIG).
Pour accélérer la création d'enregistrement dans les multiples tables qui composent cette base de données, j'ai développé plusieurs tables, vues et triggers qui permettent depuis une seule table d'ajouter des enregistrements dans des tables ciblées grâce au déclenchement de triggers. Il y a huit tables cibles possibles, j'inscris ici le schéma de deux d'entre elles, sur le principe, ça ne devrait pas changer grand chose.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72 --Exemple de tables cibles --Une table d'enregistrement de faits archéologiques DROP TABLE IF EXISTS t_fait; CREATE TABLE t_fait( "id_fait" INTEGER PRIMARY KEY, "numfait" INTEGER UNIQUE, "interpret_alter" TEXT, "interpret" TEXT, "douteux" INTEGER, "equiv_diag" TEXT, "statut" TEXT, "rais_annule" TEXT, "fouille" TEXT, "enr_fini" INTEGER, "relev_fini" INTEGER, "photo_fini" INTEGER, "topo_fini" INTEGER, "profil" TEXT, "forme" TEXT, "orient" TEXT, "orient_calc" FLOAT, "descrip" TEXT, "prof_app" FLOAT, "diam" FLOAT, "dim_max" FLOAT, "dim_min" FLOAT, "epais" FLOAT, "prof_haut" FLOAT, "periode" TEXT, "note" TEXT ); SELECT AddGeometryColumn ('t_fait','geometry',2154,'MULTIPOLYGON','XY',0); --Une table d'enregistrement des unités stratigraphiques archéologiques DROP TABLE IF EXISTS t_us; CREATE TABLE t_us( "id_us" INTEGER PRIMARY KEY, "numus" INTEGER UNIQUE, "numfait" INTEGER REFERENCES "t_fait"("numfait") DEFAULT NULL, "type_us" TEXT, "nature_us" TEXT, "interpret" TEXT, -- liste de valeurs : avec notamment les valeurs OTD 'dépôt inhumation primaire' et 'dépôt inhumation non primaire' et 'contenant inhumation' "description" TEXT, "datsup_interpret" INTEGER, "datinf_interpret" INTEGER, "datsup_mobilier" INTEGER, "datinf_mobilier" INTEGER, "datsup_14c" INTEGER, "datinf_14c" INTEGER, "note_dat" TEXT, "forme" TEXT, "diam" FLOAT, "dim_max" FLOAT, "dim_min" FLOAT, "prof_app" FLOAT, "zmin" FLOAT, "zmax" FLOAT, "epais" FLOAT, "compo_sediment" TEXT, "texture" TEXT, "couleur" TEXT, "valeur_couleur" TEXT, "creator" TEXT, "datcreation" DATE, "note" TEXT, "num_seq" INTEGER REFERENCES "t_seq"("numseq") DEFAULT NULL, "ordre_seq" INTEGER ); SELECT AddGeometryColumn ('t_us','geometry',2154,'MULTIPOLYGON','XY',0);
Il y a une double clé primaire sur les tables : la clé primaire informatique (avec le préfixe 'id_') et une clé primaire scientifique définie par l'utilisateur, ce qui permet une meilleure souplesse dans l'enregistrement (avec le préfixe 'num') avec une contrainte d'unicité.
Les tables cibles possèdent chacune une géométrie.
Les tables qui doivent permettre la création d'enregistrements rapides sont structurées ainsi :
Pour faciliter l'emploi ultérieur des triggers, et comme je veux pouvoir continuer à saisir depuis les tables (t_fait et t_us notamment) normalement j'ai ajouté deux vues, dont la structure est la suivante :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 --Table de création d'enregistrement rapide DROP TABLE IF EXISTS entite_rapide; CREATE TABLE entite_rapide( "id_entite" INTEGER PRIMARY KEY, "id_serie" INTEGER REFERENCES "serie"("id_serie") DEFAULT NULL, --clé étrangère de la table serie "numentite" INTEGER, -- l'incrément créé à partir de la série sélectionnée dans "id_serie" "interpret" TEXT, -- permet d'ajouter une interprétation rapide "note" TEXT ); --Table de création de séries d'incrémentation par spécification du nombre de départ et du pas DROP TABLE IF EXISTS serie; CREATE TABLE serie( "id_serie" INTEGER PRIMARY KEY, "nombre_depart" INTEGER, --le nombre à partir duquel l'incrémentation se fait "nombre_arrivee" INTEGER, --le nombre qui finit l'incrémentation "pas" INTEGER, --le pas d'incrémentation "tablentite" TEXT, --la table ciblée par la série "note" TEXT );
Pour que l'ensemble fonctionne, j'ai besoin de plusieurs triggers :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 /*Vue d'aggrégation des valeurs maximales des numéros d'entité déjà saisies dans les tables ciblées par séries définies dans la table série*/ CREATE VIEW "vue_numaximum_serie" AS SELECT "id_serie", 't_us' AS "tablentite", max("numus") AS numaximum, "pas", max("numus")+"pas" as "new_numentite" FROM t_us JOIN serie ON "numus" >= "nombre_depart" AND "numus" <= "nombre_arrivee" AND "tablentite" LIKE 't_us' GROUP BY "id_serie" UNION SELECT "id_serie", 't_fait' AS "tablentite", max("numfait") AS numaximum, "pas", max("numfait")+"pas" as "new_numentite" FROM t_fait JOIN serie ON "numfait" >= "nombre_depart" AND "numfait" <= "nombre_arrivee" AND "tablentite" LIKE 't_fait' GROUP BY "id_serie" ; /*Vue de jointure entre la table entite_rapide, vue_numaximum_serie et serie pour récupérer les infos de la table serie. Cette table sert de support aux triggers liés aux créations rapide*/ CREATE VIEW "vue_entite_rapide_serie" AS SELECT e."id_entite",e."id_serie",e."numentite",e."interpret", s."nombre_depart",s."nombre_arrivee",s."pas",s."tablentite", CASE WHEN v."new_numentite" IS NULL THEN s."nombre_depart" ELSE v."new_numentite" END AS "new_numentite" -- permet de générer le nouveau numéro à attribuer à l'enregistrement. Si aucune entité n'est déjà présente dans les tables cibles, alors le premier numéro de la série sera attribué FROM serie AS s LEFT JOIN entite_rapide AS e ON e."id_serie" = s."id_serie" --LEFT JOIN pour peupler la vue dès que les séries sont crées LEFT JOIN vue_numaximum_serie AS v ON e."id_serie" = v."id_serie";
* un trigger pour générer le nouveau numéro de l'enregistrement selon la série et donc selon la table cible
* une série de triggers qui distribuent les enregistrements avec les attributs créés dans la table entite_rapide, à savoir le numéro et éventuellement une interprétation
A l'heure actuelle, le premier trigger semble fonctionner avec cette syntaxe :
Les triggers qui suivent posent problème parce que leur exécution ne semble pas se faire au bon moment. Normalement, c'est ce à quoi je m'attendais, l'insertion des entités doit se produire après la création du nouveau numéro par le trigger précédent. Ce problème engendre des erreurs de commit à cause de la contrainte d'unicité sur le champ t_us."numus" et le champ t_fait."numfait".
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10 /*Trigger associé aux tables de création rapide pour l'ajout du nouveau numéro d'entité selon la vue vue_numaximum_serie*/ CREATE TRIGGER trgai_entite_rapide_newnumentite AFTER INSERT ON entite_rapide FOR EACH ROW WHEN NEW."id_serie" IS NOT NULL BEGIN UPDATE entite_rapide SET "numentite" = (SELECT "new_numentite" FROM vue_entite_rapide_serie WHERE NEW."id_entite" = "id_entite"); END;
Voici l'exemple de triggers pour les tables t_fait et t_us que j'ai écrits :
Le message d'erreur retourné par la base de données :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21 /*Trigger associé à la table de création d'entité rapide qui permet d'ajouter les enregistrements dans les tables ciblées*/ --pour t_us CREATE TRIGGER trgau_t_us_maj_entite_rapide AFTER UPDATE OF "numentite" ON entite_rapide FOR EACH ROW WHEN NEW."numentite" IS NOT NULL AND (SELECT "tablentite" FROM entite_rapide AS e JOIN vue_entite_rapide_serie AS v ON e."id_entite" = v."id_entite") like 't_us' BEGIN INSERT INTO t_us("numus","nature_us","geometry") VALUES (NEW."numentite", NEW."interpret",NULL); END; --pour t_fait CREATE TRIGGER trgau_t_fait_maj_entite_rapide AFTER UPDATE OF "numentite" ON entite_rapide FOR EACH ROW WHEN NEW."numentite" IS NOT NULL AND (SELECT "tablentite" FROM entite_rapide AS e JOIN vue_entite_rapide_serie AS v ON e."id_entite" = v."id_entite") like 't_fait' BEGIN INSERT INTO t_fait("numfait","interpret","geometry") VALUES (NEW."numentite", NEW."interpret",NULL); END;
Je pense que SQLite exécute les triggers dans le mauvais ordre et déclenche l'insertion des enregistrements avant d'avoir généré le numéro de l'enregistrement.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8 Impossible de valider les changements pour la couche entite_rapide Erreurs : ERROR: 1 feature(s) not added. Erreur du fournisseur de données : Erreur OGR lors de la création de l'entité -28 : sqlite3_step() failed: UNIQUE constraint failed: t_us.numus (19)
Y a-t-il un moyen de résoudre ce problème ?
Je vous remercie pour votre aide.
Caroline
Partager