IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

SQLite Discussion :

SQLite/SpatiaLite - Triggers - Des déclencheurs à exécuter dans un ordre précis


Sujet :

SQLite

  1. #1
    Nouveau Candidat au Club
    Inscrit en
    Mars 2010
    Messages
    2
    Détails du profil
    Informations forums :
    Inscription : Mars 2010
    Messages : 2
    Points : 1
    Points
    1
    Par défaut SQLite/SpatiaLite - Triggers - Des déclencheurs à exécuter dans un ordre précis
    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 :

    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 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
     
    /*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";
    Pour que l'ensemble fonctionne, j'ai besoin de plusieurs triggers :
    * 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 :

    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;
    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".

    Voici l'exemple de triggers pour les tables t_fait et t_us que j'ai écrits :

    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;
    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
     
    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)
    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.
    Y a-t-il un moyen de résoudre ce problème ?
    Je vous remercie pour votre aide.
    Caroline

  2. #2
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 084
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : Mars 2010
    Messages : 10 084
    Points : 38 381
    Points
    38 381
    Billets dans le blog
    9
    Par défaut
    Bonjour,

    J'ai du mal à comprendre le problème, il faut évidemment calculer en premier la valeur dont on a besoin pour insérer dans les différentes tables, ensuite, les triggers se déclenchent lors d'un insert ou d'un update, donc peu importe l'ordre du moment que la transaction est cohérente.


    À ce sujet :

    Citation Envoyé par Bilou020285 Voir le message
    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é.
    Parmi les clefs candidates d'une table, une seule peut être choisie comme "clef primaire".
    Par contre, on peut avoir plusieurs contraintes unique sur une même table, contraintes qui peuvent s'appliquer sur une ou plusieurs colonnes.
    Une clef primaire doit être à la fois unique, "not null" et irréductible.
    Egalement, il est fortement recommandé que son contenu soit stable, d'où l'intérêt d'une valeur asémantique type chrono attribué par le SGBD.

  3. #3
    Nouveau Candidat au Club
    Inscrit en
    Mars 2010
    Messages
    2
    Détails du profil
    Informations forums :
    Inscription : Mars 2010
    Messages : 2
    Points : 1
    Points
    1
    Par défaut
    J'ai finalement résolu le problème en ne passant pas par des vues. La requêtes sur le trigger est bien plus complexe, mais ça semble fonctionner.
    Merci à vous.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Exécuter des scripts php dans un ordre précis
    Par cebabonet dans le forum Langage
    Réponses: 9
    Dernier message: 18/10/2012, 12h13
  2. Extraire des champs dans un ordre précis
    Par karimDevelopper dans le forum Shell et commandes GNU
    Réponses: 2
    Dernier message: 02/06/2009, 15h19
  3. Effectuer des calculs dans un ordre précis
    Par missoly dans le forum VBA Access
    Réponses: 5
    Dernier message: 17/07/2008, 16h57
  4. Réponses: 1
    Dernier message: 01/12/2007, 16h18
  5. Réponses: 1
    Dernier message: 04/01/2007, 23h52

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo