Discussion :

[jojo86]

Bonjour,
Je fais une appli avec SQLite en base de données.
J'ai une table utilisateurs (Nom, Prenom, Adresse, ID_Ville), et une table Villes.
La table ville contient : ID, Code Postal, Nom Commune.
Dans cette table, j'ai théoriquement toutes les communes de France. Mais comme ça change très souvent, l'utilisateur de l'appli doit pouvoir saisir une commune qui n'existe pas dans la table Villes.
Je pensais initialement récupérer l'ID de la ville sélectionnée et le stocker dans ma table utilisateur. Mais si l'utilisateur saisit une commune qui n'existe pas dans la table des villes, alors je n'aurais pas d'ID.

La première idée qui me vient, c'est de gérer ça côté appli client en ayant une variable qui stockerait l'ID de la ville sélectionnée (en cas de sélection d'une ville connue dans la base), et avant de faire l'insert/Update de la table utilisateur, il suffirait de tester si la variable est null ou pas.
Si la variable est null alors => Insert into ville (code_postal, Nom_Commune) Values (codePostalSaisi, NomVilleSaisi)

Mais n'y aurait-il pas un moyen de faire ça directement dans la base de données ?

Merci d'avance,

[acaumes]

Bonjour,

Pour SQLITE la fonction à utiliser est last_insert_rowid()

De cette manière (pas testé) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
Begin transaction;
 
insert into tablemaitre (Listedescolonnes) values (valeursdescolonnes);
 
insert into tableref (Colonneversmaitre, listedescolonnes) values(last_insert_rowid(), valeursdescolonnes);
 
commit;

immédiatement l'un après l'autre et c'est encore mieux à l'intérieur d'une transaction

Cordialement

[jojo86]

Salut, merci mais j’ai bien peur que cela ne me permette pas de répondre à mon besoin.

[acaumes]

Oui, je n 'avais vu que la création.

Lorsque il n'y a pas de création de commune tu peux essayer quelque chose comme ça

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
INSERT INTO utilisateurs (nom, prenom, idville) VALUES
('Lemaitre', 'Claude', (SELECT id FROM communes WHERE nom = 'Paris'))

Dans la clause where il te faut les critères qui définissent exactement la ville recherchée (comment traites tu les cas des villes avec plus d'un code postal? Et le cas des homonymes?)

[SergioMaster]

Bonjour,

Pourquoi ne pas utiliser un Trigger ? https://www.tutorialspoint.com/sqlit...e_triggers.htm
[Edit] Zut, j'ai écrit trop vite ! Cela demanderait quand même de faire une "fausse" redondance de données : j'ajouterai les colonnes CDP et VILLE pour pouvoir les remplir si l'iD n'existe pas dans le trigger, une fois l'enregistrement CDP VILLE effectuer je remettrai à NULL ces deux colonnes, pas très propre mais c'est ça ou faire faire le travail par l'application

UTILISATEURS(Nom, Prenom, Adresse, ID_Ville,WCDP,WVILLE),
VILLES(ID, CodePostal,Commune)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
CREATE TRIGGER AJOUT_VILLE BEFORE INSERT OR UPDATE 
ON UTILISATEURS
BEGIN
IF (NEW.ID_VILLE IS NULL) THEN INSERT INTO VILLES(CODEPOSTAL,COMMUNE) VALUES (NEW.WCDP,NEW.WVILLE) ;
NEW.ID=SELECT LAST_INSERT_ROW_ID FROM VILLES;
NEW.WCDP=NULL;
NEW.WVILLE=NULL;
END;

[jojo86]

T'es malin toi !
lol au départ, j'avais ajouté directement dans ma table utilisateurs le code postal et la ville. Du coup tu me fais un mix des deux !
Effectivement repasser à null c'est peut être moyen mais ça soulage la partie application. C'est enorme en fait !

Et en Firebird la solution aurait-elle été la même ?

[SergioMaster]

Et en Firebird la solution aurait-elle été la même ?

à peu de choses près je pense que oui.
Rapidement sans essais, je pense que je serais passé par une table temporaire pour l'affichage d'un utilisateur, ce qui permettrait de faire un peu de "lessive" pour rendre la structure de la table UTILISATEURS plus "propre"
bien sûr du coup, plus besoin d'effacer les wcdp et wville puisque dans la table temporaire et j'utiliserai RETURNING pour l'obtention de l'ID au lieu de LAST_INSERT_ROW_ID.
Le seul "truc" qu'il me faut vérifier est : "peut-on faire des triggers sur une table temporaire ?"

[jojo86]

Et en firebird, ne serait-il pas possible de faire l'ajout au travers d'une procédure stockée ? Plutôt qu'une table temporaire ?
La procédure aurait besoin des paramètres nécessaires à l'insertion des données dans la table client et aussi l'ID de la commune sélectionnée (ou pas) et le code postal ainsi que la ville.
Ensuite, la procédure se débrouille pour :
1 tester si IDVille passé en paramètres est null ou pas, s'il est null, alors => Faire Insert Into TVilles (CodePostal, Ville) Values (ParamCODEPOSTAL, ParamVille) returning id
2Insérer l'utilisateur dans la table utilisateurs

Est-ce possible ?

L'appli que je dois faire est pour système mac, et firebird sur mac c'est la misère... Sur windows, un simple dll dans le dossier de l'appli est suffisant pour faire tourner la base en embedded, mais sur mac il faut obligatoirement installer le superserveur... Donc pour la portabilité de l'appli je trouve ça lourd... je suis donc parti sur SQLite.

[SergioMaster]

Et en firebird, ne serait-il pas possible de faire l'ajout au travers d'une procédure stockée ? Plutôt qu'une table temporaire ?

bien sûr que oui et ce serait nettement plus simple c'est juste que je proposai une solution quasi identique

L'appli que je dois faire est pour système mac, et firebird sur mac c'est la misère... Sur windows, un simple dll dans le dossier de l'appli est suffisant pour faire tourner la base en embedded,

pas seulement une dll si l'on veut quelque chose de correct (il y a aussi quelques fichiers/répertoires qui peuvent s'avérer utiles)

mais sur mac il faut obligatoirement installer le superserveur... Donc pour la portabilité de l'appli je trouve ça lourd...

je ne connais pas assez les trucs à la pomme et n'en ai pas sous la main
cette FAQ y répond pour FB 2.5 mais FB 3 est légèrement différent lire http://www.ibphoenix.com/files/Embedded_fb3.pdf

[jojo86]

Merci de ton aide Sergio, ce sujet fait quelque peu doublon avec mon autre post dans le forum firebird.

Après avoir tenté de faire fonctionner firebird embedded sur mac sans succès, je vais me contenter de SQLite et de ses limitations... Ca me permettra tout de même de developper mon appli, mais j'aurais aimé pouvoir exploiter les procédures stockées et beaucoup plus de souplesse sur les triggers...
Peut être qu'un jour nous aurons ces fonctionnalités dans SQLite ?

[jojo86]

Je reviens sur la proposition de Sergio car je ne comprends pas le comportement que je constate avec le trigger sous SQLite...

J'ai ajouté à ma table des Utilisateurs, 3 champs temporaires : "tmpCP, tmpVille, tmpVilleRmveDiac"
Lors de l'insertion de mon utilisateur depuis l'applicaiton, je renseigne ces 3 champs.

Sur ma base j'ai le trigger suivant qui devrait si id_ville est null, insérer la nouvelle ville dans la table des villes.
Sauf qu'initialement dans ma table, j'ai 36700 villes. Après l'insertion d'un utilisateur sans id_ville, je me retrouve avec 36700 lignes multipliées par 2, et toutes les nouvelles lignes sont vides...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE TRIGGER Add_User_Insert AFTER INSERT 
ON TClients
	WHEN (NEW.id_ville IS NULL) BEGIN
		INSERT INTO villes_france(ville_code_postal,ville_nom_reel, ville_nom) 
		SELECT NEW.tmpCP, NEW.tmpVILLE, NEW.tmpVilleRmveDiac FROM villes_france WHERE
		NOT EXISTS (Select 1 from villes_france WHERE ville_code_postal=NEW.tmpCP AND ville_nom= NEW.tmpVilleRmveDiac);
END

Qu'est-ce qui ne va pas dans mon trigger ?

Merci d'avance,

[jojo86]

Oups auto réponse...
J'ai trouvé mon erreur, il fallait rajouter DISTINCT :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE TRIGGER Add_User_Insert AFTER INSERT 
ON TClients
	WHEN (NEW.id_ville IS NULL) BEGIN
		INSERT INTO villes_france(ville_code_postal,ville_nom_reel, ville_nom) 
		SELECT DISTINCT NEW.tmpCP, NEW.tmpVILLE, NEW.tmpVilleRmveDiac FROM villes_france WHERE
		NOT EXISTS (Select 1 from villes_france WHERE ville_code_postal=NEW.tmpCP AND ville_nom= NEW.tmpVilleRmveDiac);
END

[SergioMaster]

Bonjour,

c'est vrai que de ma proposition de trigger j'avais oublié de tester si le couple CDP-Ville n'existait pas déjà
Deux raisons à cela :
1- je pensai que le programme (ou plutôt la requête pour obtenir le client s'en chargerai)
2- je comptai sur la structure de la table villes_france avec un index CDP+VILLE unique pour refusere toute insertion inadéquate

ce qui me parait étrange c'est le besoin de refaire un select et, accessoirement, l'utilisation de NEW alors que l'on a à faire à un trigger after insert

sans tester, je ne suis pas un as SQLite non plus donc : indulgence, ce trigger ne ferait pas la même chose ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE TRIGGER Add_User_Insert AFTER INSERT 
ON TClients
 
	WHEN (NEW.id_ville IS NULL AND NOT EXISTS (Select 1 from villes_france WHERE ville_code_postal=NEW.tmpCP AND ville_nom= NEW.tmpVilleRmveDiac)
            BEGIN
		INSERT INTO villes_france(ville_code_postal,ville_nom_reel, ville_nom) 
		VALUES (NEW.tmpCP, NEW.tmpVILLE, NEW.tmpVilleRmveDiac);
END

[jojo86]

Salut Sergio,

Ok pour la création d'un Index, qui permettrait de garantir le couple CP+Ville unique. Mais par contre, comment faire pour que l'utilisateur ne reçoive pas le message d'erreur renvoyé par la base si jamais le couple existe déjà ?
Car en faisant des tests, je reçois le message : 'Erreur lors du changement de données UNIQUE contraint failed: Test.Ville, Test.CP

Merci,

[SergioMaster]

Re,

je suis meilleur avec Firebird que SQLite
déjà cela voudrait dire que dans le WHEN le double test

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

WHEN (NEW.id_ville IS NULL AND NOT EXISTS (Select 1 from villes_france WHERE ville_code_postal=NEW.tmpCP AND ville_nom= NEW.tmpVilleRmveDiac) )

ne fonctionnerait pas ?

[acaumes]

Bonjour,

Mais par contre, comment faire pour que l'utilisateur ne reçoive pas le message d'erreur renvoyé par la base si jamais le couple existe déjà ?

https://www.sqlite.org/lang_insert.html
Dans ton cas INESRT OR IGNORE

Cordialement

[SergioMaster]

Génial, le INSERT OR IGNORE, que j'avais complètement zappé, éviterai le test (inutile) d'existence

[jojo86]

Cool, merci à vous deux !

[jojo86]

Bonjour,

Pourquoi ne pas utiliser un Trigger ? https://www.tutorialspoint.com/sqlit...e_triggers.htm
[Edit] Zut, j'ai écrit trop vite ! Cela demanderait quand même de faire une "fausse" redondance de données : j'ajouterai les colonnes CDP et VILLE pour pouvoir les remplir si l'iD n'existe pas dans le trigger, une fois l'enregistrement CDP VILLE effectuer je remettrai à NULL ces deux colonnes, pas très propre mais c'est ça ou faire faire le travail par l'application

UTILISATEURS(Nom, Prenom, Adresse, ID_Ville,WCDP,WVILLE),
VILLES(ID, CodePostal,Commune)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
CREATE TRIGGER AJOUT_VILLE BEFORE INSERT OR UPDATE 
ON UTILISATEURS
BEGIN
IF (NEW.ID_VILLE IS NULL) THEN INSERT INTO VILLES(CODEPOSTAL,COMMUNE) VALUES (NEW.WCDP,NEW.WVILLE) ;
NEW.ID=SELECT LAST_INSERT_ROW_ID FROM VILLES;
NEW.WCDP=NULL;
NEW.WVILLE=NULL;
END;

En fait c'est la grosse galère sous SQLite pourquoi ? :
- Il n'est pas possible de faire un trigger pour Insert et Update en même temps. Il faut donc 2 triggers
- Il n'est pas possible de modifier les valeurs NEW.XXXX il faut repasser par un Update

Mon idée de départ mais qui ne fonctionne pas :
- Trigger_Before_Insert
- Trigger_Before_Update
- Trigger_Clear_After_Update
- Trigger_Clear_After_Insert

Contenu des triggers Before_Insert et Update :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE TRIGGER Trigger_Before_Insert BEFORE INSERT 
ON TClients
	WHEN ((NEW.id_ville IS NULL)  OR (NEW.id_ville = 0)) 
	AND (NOT EXISTS (Select 1 from villes_france WHERE ville_code_postal=NEW.tmpCP AND ville_nom=NEW.tmpVILLECollate)) BEGIN
		INSERT INTO villes_france(ville_code_postal,ville_nom_reel,ville_nom) VALUES (NEW.tmpCP, NEW.tmpVILLE, NEW.tmpVILLECollate);
END

Contenu des triggers After_Insert et Update

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE TRIGGER Clear_User_City_Update AFTER UPDATE 
ON TClients
BEGIN
		Update TClients Set id_ville = (Select ville_id from villes_france WHERE ville_code_postal=NEW.tmpCP AND Upper(ville_nom )=Upper(NEW.tmpVILLECollate)),
	    tmpCP=NULL, tmpVILLE=NULL, tmpVILLECollate=NULL WHERE id=NEW.id;
END

Mon problème, c'est que l'insert dans TClients déclenche le After Insert qui lui même enclenche le Update... Et au final, mes données ne sont pas mises à jour comme je l'attends...
J'ai donc câblé tous les triggers en After Insert/Update, dans l'ordre suivant :
1 : Si id_ville inséré dans TClient est null alors insérer si pas déjà existant, la ville + CP
2 : Mise à jour de TClient avec l'ID de la ville + mise à zéro des champs temporaires CP, Ville...

Mais là encore j'ai des problèmes car les triggers s'appellent entres-eux...

SQLite reste assez limité sur ce point là... Je pense que je devrais peut être gérer tout ça dans l'appli...

[jojo86]

Bon j'ai finalement réussi à bidouiller en ajoutant des critères sur mon trigger After Update :
Mais ca reste une gymnastique d'esprit très différente des autres languages SQL !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
CREATE TRIGGER Clear_User_City_Update AFTER UPDATE 
ON TClients
  WHEN ((NEW.tmpCP IS NOT NULL) AND (NEW.tmpVILLE IS NOT NULL) AND (NEW.tmpVILLECollate IS NOT NULL))  BEGIN
		Update TClients Set id_ville = (Select ville_id from villes_france WHERE ville_code_postal=NEW.tmpCP AND Upper(ville_nom )=Upper(NEW.tmpVILLECollate)),
	    tmpCP=NULL, tmpVILLE=NULL, tmpVILLECollate=NULL WHERE id=NEW.id;
END