Tester une insertion

Version imprimable

03/02/2010, 15h57
passie

Tester une insertion
Bonjour à tous,
Sur les champs des tables, je met certaines contraintes (NULL/NOT NULL mais aussi des contraintes via CHECK) en plus des contraintes liées à la déclaration du type de données.
Je suis en train de développer une interface php pour alimenter ma base via des formulaires, je me pose donc forcement la question de la vérification de la saisie utilisateur (aprés post ou pas d'ailleur !), je me demande donc pourquoi je serait contraint de refaire des tests sur la bonne saisie sachant que les contraintes sont réellement fixée lors de la création de la table et éventuellement modifiées mais toujours côté SQL.
peut t'on par requête récupérer les contraintes de colonnes et/ou tester en sql ce que donnerai l'insertion en base, ceci pour les null par expl mais aussi pour les check.
Expl si mon champ est déclaré de type integer NOT NULL avec un check en tre 0 et 10. puis je soit :
1. récupérer le détail de ses contraintes
2. tester l'insertion par requête
Je cherche à éviter d'avoir à refaire le boulot côté php ou javascript avec des critéres identiques mais non synchronisés, si changement il faut aussi le faire dans les deux sources (SQL et PHP).
Merci d'avance pour votre avis sur la question et sur qlq pistes éventuelles.

Peut être via PLPGSQL pour me dire si oui le champ est valide ou pas, sans forcement savoir laquelles des contraintes n'est pas respéctée mais au moins que ce champ est problématique 8O
03/02/2010, 16h12
CinePhil

Dans ton cas, si les contraintes sont déjà implémentées en BDD, il faut que le code de l'application récupère et traite les erreurs retournées par le SGBD, mais il est inutile de faire un double contrôle préalablement dans le code.

Il y a eu un débat récemment (légèrement houleux par moments) sur la situation des contraintes en BDD ou dans le code.

Un avis mi figue mi raisin y avait été donné, suggérant de ne mettre en BDD que les contraintes fortes applicables quelle que soit l'application qui utilise la BDD Les contraintes d'intégrité référentielle en premier lieu, mais aussi les impossibilités logiques telles que le fait d'enregistrer une date de décès antérieure à une date de naissance.

Les contraintes propres à l'application seraient plutôt à mettre dans le code de l'appli car ce sont davantage des contraintes fonctionnelles. Par exemple le fait qu'une mise à jour de quantité soit impossible si la quantité est inférieure à un certain seuil, comme cela pourrait se rencontrer dans une gestion de stock.

En gros il faut distinguer les contraintes propre à la nature même de la donnée de celles dépendant de l'utilisation qui est faite de la donnée.

Un CHECK entre 0 et 10 est peut-être dans cette seconde catégorie ?

Après si la BDD n'est destinée à être utilisée que par une seule application, on peut tout mettre en BDD.
03/02/2010, 16h41
passie

Merci pour la réponse,
dans mon cas ce sont surtout des contraintes logiques du type la largeur de l'objet doit être comprise entre 0 et 200, du coup je pense plutôt aux contraintes déclarées côté SQL
mais aussi la seule contrainte NULL ou NOT NULL, c'est bien une contrainte mise aprés une décision des utlisateurs du coup puis je vérifier pour un champ si il est prévu NULL ou NOT NULL en SQL ?
ou alors faut il envoyer la requête d'insertion pour tout un enregistrement, attendre le résultat et traiter un éventuel message d'erreur qui ne sera pas forcement explicite.
Ma première idée est de le faire champ par champ, du style je récupére la saisie d'un seul et je teste si il "rentrerai" dans la table (je ne parle ici que des contraintes sur le type du champ et sur des plages de valeurs par expl).
Mais c'est juste une idée je ne sais pas encore comment le faire.
J'imagine une fonction plpgsql à qui l'on donne par expl la table, le champ et sa valeur saisie par l'utilisateur qui permettrai de tester si il peut être accepter ou pas !
Bon champ par champ c'est peut être un peu lourding mais pas plus que de le faire deux fois.
03/02/2010, 17h22
CinePhil

Citation:

Envoyé par passie

dans mon cas ce sont surtout des contraintes logiques du type la largeur de l'objet doit être comprise entre 0 et 200, du coup je pense plutôt aux contraintes déclarées côté SQL

Moi je dirais plutôt une contrainte fonctionnelle !
Qui dit que dans l'avenir la largeur ne pourra pas être supérieure à 200 ?
Enfin il faudrait des précisions sur le contexte pour en juger plus profondément.

Citation:

mais aussi la seule contrainte NULL ou NOT NULL, c'est bien une contrainte mise aprés une décision des utlisateurs du coup puis je vérifier pour un champ si il est prévu NULL ou NOT NULL en SQL ?

Ca c'est plus une contrainte de modélisation de données !
Une clé primaire est systématiquement NOT NULL par exemple.
De même que peut l'être une clé alternative telle que le login utilisateur : sans login, l'utilisateur ne peut pas exister.
Par contre on peut ne pas connaître une information sur l'utilisateur, telle que sa date de naissance, et autoriser ainsi la colonne date_naissance a être NULL.

Les contraintes figurent dans les métadonnées de la BDD, lesquelles sont dans le schema information_schema. Parcours ses vues et tu y trouveras tes contraintes. Tu pourras ainsi en déduire les requêtes qui te permettront de les extraire.

Citation:

ou alors faut il envoyer la requête d'insertion pour tout un enregistrement, attendre le résultat et traiter un éventuel message d'erreur qui ne sera pas forcement explicite.

C'est généralement comme ça qu'on fait et c'est au logiciel de présenter un message explicite ou de réagir de la manière appropriée au message.
Le SGBD va seulement dire, avec sa propre syntaxe, que telle contrainte n'a pas été respectée. Tu peux récupérer le code et le texte en clair de l'erreur.
Je n'en suis pas sûr mais je crois que tu peux spécifier un texte explicite avec les contraintes CHECK. A vérifier.

Pour ce qui est des contraintes NOT NULL, la meilleure méthode est de définir une valeur par défaut correspondant au type de la colonne. Il y en a une standard pour chaque type mais tu peux définir les tiennes, par exemple DEFAULT CURRENT_TIMESTAMP sur une colonne de type TIMESTAMP ou DEFAULT TRUE sur une colonne booléenne...

Citation:

Ma première idée est de le faire champ par champ, du style je récupére la saisie d'un seul et je teste si il "rentrerai" dans la table (je ne parle ici que des contraintes sur le type du champ et sur des plages de valeurs par expl).
Mais c'est juste une idée je ne sais pas encore comment le faire.
J'imagine une fonction plpgsql à qui l'on donne par expl la table, le champ et sa valeur saisie par l'utilisateur qui permettrai de tester si il peut être accepter ou pas !
Bon champ par champ c'est peut être un peu lourding mais pas plus que de le faire deux fois.

Si tu essaies de faire un truc de ce style dans le logiciel, ça fera deux envois de requêtes au serveur, donc deux fois plus de temps. Mauvaise idée !

Il faut se dire que 95% des requêtes passeront sans douleur et que les messages d'erreur ne sont là que pour traiter les cas d'erreurs de saisie la plupart du temps et que ces cas doivent être rares, à moins que les utilisateurs tapent sur le clavier avec des moufles ! :D

Les tests qui sont à mettre dans le logiciel sont :
- la présence de données dans les champs obligatoires des formulaires ;
- la cohérence de certaines saisies (mot de passe et sa répétition lors d'une inscription à un site par exemple)
- le traitement des cas d'erreur retrounés par le SGBD.
03/02/2010, 21h41
estofilo

On peut trouver quelles sont les contraintes CHECK sur les colonnes via la table information_schema.constraint_column_usage et en obtenir les clauses testées via la table information_schema.check_constraints.

Techniquement il est possible de générer une requête qui fasse que le SGBD va calculer chaque clause et produire un champ à true ou false pour chacune. Par exemple si on a une clause col>=1 and col<=10 et col2 is not null et les valeurs 12 et 'abc' à insérer, il faudrait générer et lancer:

Code:

SELECT col>=1 AND col<=10, col2 is not null FROM (values(12,'abc')) AS s(col,col2)

Dans le résultat de cette requête, un champ à la valeur false indique que la contrainte correspondante serait violée si on insérait la donnée.
04/02/2010, 09h56
passie
Déjà merci pour vos réponses :ccool:

Citation:

Moi je dirais plutôt une contrainte fonctionnelle !

:D c'est juste

Citation:

Qui dit que dans l'avenir la largeur ne pourra pas être supérieure à 200 ?

C'est justement ce que je me dit, mais ce que je pense c'est qu'en cas de changement de cette plage, il se fera d'abord côté SQL et qu'il faut aussi le faire dans le code de l'appli cliente si on veut garder une corrélation entre les deux, ou alors on tente de traiter le message d'erreur mais c'est peut être moins simple pour spécifier à l'utilisateur que la valeur doit être comprise entre 0 et 210 avant la saisie.

Citation:

Je n'en suis pas sûr mais je crois que tu peux spécifier un texte explicite avec les contraintes CHECK. A vérifier.

Ca c'est une vraie piste intéressante :ccool: faut creuser mais je pense que cela peut être la bonne solution pour avoir des retours d'erreur utilisables.

Le but principal étant bien de pouvoir assister l'utilisateur lors du remplissage en lui donnant si possible ce qu'on attend de lui sur chacun des champ de formulaire. Cela n'empêche pas de faire un retour explicite des messages d'erreurs éventuels mais en amont j'aimerai lui dire ce champ doit être par expl :
1. NOT NULL
2. De type integer
3. Compris en tre 0 et 220
Et c'est dans ce cas que je trouve dommage d'avoir à stocker les contraintes côté application alors qu'elles sont définies en SQL et qu'un changement quelconque se ferait d'abord en SQL.

Citation:

Les tests qui sont à mettre dans le logiciel sont :
- la présence de données dans les champs obligatoires des formulaires ;

voila mais pour moi un champ obligatoire l'est parce qu'il reçoit une contrainte NOT NULL en SQL.

Bon c'est sur que c'est aussi de la fainéantise d'avoir à faire l'effort de bien synchroniser le formulaire avec la table dans le temps mais bon :mouarf:

Citation:

Envoyé par estofilo
On peut trouver quelles sont les contraintes CHECK sur les colonnes via la table information_schema.constraint_column_usage et en obtenir les clauses testées via la table information_schema.check_constraints.

Techniquement il est possible de générer une requête qui fasse que le SGBD va calculer chaque clause et produire un champ à true ou false pour chacune

Voila c'est l'idée mais c'est vrai que c'est peut être lourd à faire et gourmand en ressources suivant le nombre de vérif à faire.

Reste la possibilité d'utiliser une table descriptive qui contient les infos sur chaque champ (un genre de dico de données) et s'en servir pour faire l'info utilisateur (au survol du libéllé du champ de formulaire par expl) et d'utiliser les valeurs de cette table pour faire l'affichage du formulaire (mettre en rouge avec une étoile si NOT NULL) et les vérifs (connaitre min / max etc...).
Mais le PB reste le même il faut s'assurer de la cohérence entre les containtes réelles (SQL) et celles décrites dans cette table, mais au moins on a toutes les infos dans une même table, c'est plus simple à maintenir.

Merci
04/02/2010, 12h32
CinePhil
Ma vision des choses...

1) On modélise les données
Les entités et leurs associations dans un MCD, lequel se dérive en MLD. On vérifie les noms des tables et des colonnes pour que la sémantique soit claire et sans ambiguïté. On type les colonnes. Par exemple :
- ref_produit est de type CHAR(7) NOT NULL DEFAULT 'A_CODER'
- prix_unitaire est de type DECIMAL (6,2) NOT NULL DEFAULT 0.00
- date_creation est de type DATE DEFAULT CURRENT_DATE
...

La seule chose à faire côté logiciel est de tenir compte du type et de fournir le bon format au SGBD, par exemple en transformant une date saisie '03/02/2010' en '2010-02-03'.
=> C'est au logiciel de s'adapter aux données, pas l'inverse !

2) On complète avec des contraintes sur les données.
Les contraintes de clé étrangère ne devraient pas poser de problème particulier côté logiciel parce que c'est le logiciel qui :
- affiche dans des listes les choix possibles ;
- prévoit l'ajout possible d'un nouvel élément dans la liste et le traite en tant que tel en réactualisant la liste après ajout.

Les contraintes d'intégrité telles que ON DELETE CASCADE ne posent pas de problème non plus et facilite le boulot puisque c'est le SGBD qui s'en débrouille. Il faut juste récupérer et traiter le cas d'ereur renvoyé par le SGBD sur un ON DELETE RESTRICT.

Les contraintes CHECK ne concernent que la logique des données (date_deces >= date_naissance OR date_deces IS NULL) et peuvent renvoyer un texte explicite de message qui sera à traiter par le logiciel.
=> C'est au logiciel de s'adapter aux données, pas l'inverse !

3) La base de données étant implantée, on développe l'appli qui doit l'utiliser.
L'appli aura des contraintes qui doivent être cohérentes avec les contraintes de données. Par exemple, vérifier que ce qui a été saisi dans le champ date_naissance du formulaire X est bien une date pour envoyer une donnée correcte au SGBD.
Petit test fait dans phpMyAdmin :
Code:

1 2 3 4 5 6 INSERT INTO `test`.`table_billing` ( `client` , `billing_date` , `price` ) VALUES ( '1', 'khgkdfgh', '12' );
La donnée voulant être insérée dans la colonnte billing_date de type DATE n'est pas une date.
PhpMyAdmin a renvoyé un Warning :

Citation:

1 enregistrement(s) inséré(s).
Warning: #1265 Data truncated for column 'billing_date' at row 1

Mais la ligne a bien été créée avec une date à 0000-00-00 !

Je ne sais pas comment Posgresql réagit avec un tel cas de figure, je ne l'ai pas installé au boulot.
=> C'est au logiciel de s'adapter aux données, pas l'inverse !

L'application pourra aussi avoir des contraintes qui peuvent être plus contraignantes que celles de la BDD.
Par exemple, si l'appli ne gère que les individus nés après 2009, la date saisie doit être supérieure à '2009-12-31'. Le SGBD ne renverra pas d'erreur si cette contrainte n'est pas respectée par ce que la date sera valide mais c'est au logiciel de traiter cette contrainte fonctionnelle.

Si une autre appli utilise la même BDD et gère des individus nés n'importe quand, cette contrainte n'a pas à être implémentée en BDD.
=> C'est au logiciel de traiter ces propres contraintes fonctionnelles.

L'idée de stocker des contraintes dans une table est intéressante dans certains cas.
En reprenant ta contrainte sur la largeur, j'imagine le cas suivant...
Une entreprise a une machine permettant de traiter des pièces d'une certaine largeur X et une autre permettant de traiter des pièces plus grandes jusqu'à une largeur Y.
Une table pourra stocker les caractéristiques des machines et le logiciel se servira de ces caractéristiques pour valider ou non le choix de la machine en fonction de la pièce à fabriquer, certaines pièces pouvant être fabriquées sur les deux machines, d'autres non. Il y a plusieurs manières de traiter ce cas mais la table des contraintes, ou plutôt des caractéristiques des machines, en est une.
Il est bon de prendre du recul quant à la définition trop stricte des contraintes et qui pourrait par la suite engendrer des problèmes difficiles à pister.
04/02/2010, 13h39
passie
Bien bien la discussion est trés intéressante :)

Ta vision des choses est claire, bien détaillée et je comprend tout à fait tes arguments.
Concernant l'affirmation :

Citation:

=> C'est au logiciel de s'adapter aux données, pas l'inverse !

La encore je suis d'accord, mon souhait serait simplement de donner le moyen au logiciel de récupérer les contraintes imposées par la bdd sans avoir à en refaire la liste dans du code applicatif et en prime que les changements de contraintes (de type NULL/NOT NULL et CHECK par expl) soient appliquées automatiquement par l'appli.

Si je prend un expl que tu doit connaitre il me semble : les bovins (si si j'ai lu tes posts).

Imaginons une table qui sert à décrire un animal qui peut être alimentée par une interface web (formulaire ou upload de fichiers) ou par des requêtes INSERT.

On veut bien sûr faire confiance à notre SGBD sur la cohérence des données stockées et ne pas se contenter de laisser ce travail à un quelconque applicatif (d'autant plus que si on le fait via INSERT -> pas d'applicatif) c'est aussi son boulot non ?

Alors revenons à notre bovin, on souhaite connaitre sa date de naissance, son nom etc.. mais aussi des informations sur sa taille au garrot, son poids et la longueur de ses oreilles par expl avec des contraintes qui semblent pertinentes sur le moment et qui sont donc définies côté SQL avec des contraintes de colonnes.
- Poids entre 500 et 2500 kg (belle bête)
- Hauteur garrot entre 80 et 250 cm
- oreilles entre 10 et 50 cm
Pour moi le fait de mettre des contraintes de colonnes sur la table présente l'avantage de contrôler la cohérence qlq soit le moyen d'insertion et ces contraintes sont celles que je juge "véritables" car définies pas expertise cliente.
Alors que faire pour les contrôle formulaire ou contrôle fichiers d'upload, ou doit on stocker le détail de nos contraintes ?
on peut le refaire dans le code de l'applicatif mais je trouve ça dommage voir dangereux car on n'a pas l'assurance qu'une modif faire côté SQL sera bien reprise par l'applicatif ou alors et c'est l'idée faire en sorte que l'applicatif puisse retrouver ces contraintes en les demandant sur la base avant l'insertion ce qui me permet d'offrir un service plus réactif à l'utilisateur (ici je parle soit de javascript ou de php par expl).

Hélas ou pas on parvient à cloner de nouveaux animaux avec une girafe (on sait jamais :mouarf:) conséquence on aura besoin d'augmenter les limites max par expl, alors OK on augment la taille limite max à 400 cm et le reste aussi dans nos contraintes de colonnes,

Si je fait un INSERT pas de pb la nouvelle limite est appliquée mais mon formulaire ou mon code qui contrôle le fichier d'upload, ils ne réagissent pas à cela si j'ai codé en dur que la valeur du champ doit être comprise entre 80 et 250 cm, sinon à modifier aussi mon code applicatif
D'ou mon idée de passage d'information entre les deux,

mais on est d'accord la bdd impose et l'applicatif s'adapte, l'enjeu étant de relier les deux de manière utile et adaptative si possible
04/02/2010, 14h47
CinePhil
Citation:

mon souhait serait simplement de donner le moyen au logiciel de récupérer les contraintes imposées par la bdd sans avoir à en refaire la liste dans du code applicatif et en prime que les changements de contraintes (de type NULL/NOT NULL et CHECK par expl) soient appliquées automatiquement par l'appli.

N'empêche qu'il faudra bien associer du code à ces contraintes !

Remarque au passage :
A des contraintes (NOT) NULL sont associées généralement des valeurs par défaut qui, si elles ne sont pas spécifiées dans la description de la table, sont automatiques (chaîne vide, zéro... à voir avec le SGBD utilisé).
==> L'application n'a donc potentiellement rien à faire.

A des contraintes CHECK peuvent être associés (au moins grâce à PGAdmin, je ne sais pas autrement) des messages d'erreur explicites définis par celui qui implémente la BDD.
==> L'application n'a qu'à récupérer les messages explicites pour les afficher si besoin quand ils surviennent.

Dans les deux cas, la contrainte n'a pas besoin d'être réimplémentée dans l'application. Par contre, si la description du processus mis en oeuvre à l'aide du logiciel interdit que telle colonne ne soit pas renseignée, alors il s'agit d'une contrainte fonctionnelle (par exemple sur un formulaire de saisie) qui doit être ajoutée dans le logiciel en plus de la contrainte NOT NULL dans la BDD et qui vérifiera qu'on a bien saisit du texte, éventuellement formaté d'une certaine façon, et pas une chaîne vide ou non conforme au standard.

Citation:

On veut bien sûr faire confiance à notre SGBD sur la cohérence des données stockées et ne pas se contenter de laisser ce travail à un quelconque applicatif (d'autant plus que si on le fait via INSERT -> pas d'applicatif) c'est aussi son boulot non ?

Je suis bien d'accord avec ça et dans un précédent message, j'avais parlé des contraintes logiques sur les données.
Citation:
Poids entre 500 et 2500 kg (belle bête)
Hauteur garrot entre 80 et 250 cm
oreilles entre 10 et 50 cm
Ce sont effectivement des contraintes sur les données à mettre en BDD car il serait illogique de voir un bovin avec des oreilles de 300 cm.
Mais j'ai dit dans mon précédent message de prendre du recul sur les données. Un veau tout juste né ne fait probablement pas 500 kg mais ça reste un bovin qui aura très rapidement un numéro d'identification dans la base de données nationale d'identification des bovins (la BDNI).
Alors peut-être que l'applicatif que tu es chargé de développer à l'instant T ne traite que les bovins adultes supposant un poids logiquement minimal mais ce poids minimal est alors une contrainte imposée par la nature de l'applicatif et non pas par la nature de la donnée.

Je vais prendre un autre exemple que je connais bien. J'ai commencé à développer une appli associée à une BDD pour ma documentation cinématographique.
J'ai mis une contrainte sur la date de sortie du film > 1895-12-27 puisque la première projection publique d'un film eut lieu le 28 décembre 1895.
Si dans mon appli je prévois un formulaire pour enregistrer les nouveaux films, j'ajouterai une contrainte logicielle pour m'interdire la soumission au SGBD d'une date inférieure à aujourd'hui, laquelle serait acceptée par le SGBD (car supérieure à 1895-12-27) mais fausse.

Citation:

Pour moi le fait de mettre des contraintes de colonnes sur la table présente l'avantage de contrôler la cohérence qlq soit le moyen d'insertion et ces contraintes sont celles que je juge "véritables" car définies pas expertise cliente.

OK mais en prenant du recul sur les valeurs.

Citation:

Alors que faire pour les contrôle formulaire ou contrôle fichiers d'upload, ou doit on stocker le détail de nos contraintes ?

Le principe que je viens de décrire. En résumé :
- contraintes de type, de nullité, de clés étrangères, d'intégrité référentielle, logiques dans la BDD ;
- contraintes fonctionnelles liées à l'application dans l'application.

Citation:

on peut le refaire dans le code de l'applicatif mais je trouve ça dommage voir dangereux car on n'a pas l'assurance qu'une modif faire côté SQL sera bien reprise par l'applicatif

Bien d'accord avec ça.

Citation:

faire en sorte que l'applicatif puisse retrouver ces contraintes en les demandant sur la base avant l'insertion

Donc faire deux requêtes dans presque tous les cas :
- requête pour interroger les contraintes et les prendre en compte dans l'applicatif ;
- requête d'insertion ou de mise à jour avec des valeurs que le SGBD n'aura a priori même plus à contrôler alors que c'est une partie de son boulot et qu'il le fera quand même.

Avec ma méthode, on gère les erreurs (en principe rares) retournées par le SGBD et on n'a à renvoyer une seconde requête que s'il y a rejet (donc rarement).
==> Moins de transcation avec le SGBD ==> plus de performance.

Citation:

Hélas ou pas on parvient à cloner de nouveaux animaux avec une girafe (on sait jamais :mouarf:) conséquence on aura besoin d'augmenter les limites max par expl, alors OK on augment la taille limite max à 400 cm et le reste aussi dans nos contraintes de colonnes,

Cet événement, s'il arrive un jour, ce que je ne souhaite pas, entraînera probablement de nouveaux besoins pour l'applicatif. Par exemple l'ajout d'une case à cocher indiquant qu'il s'agit d'un hybride obtenu par clonage.

Il faut se méfier énormément des logiciels anciens pour traiter des données ayant des paramètres totalement nouveaux. L'histoire du vol 501 d'Ariane en est un bon exemple !
04/02/2010, 15h05
estofilo

En théorie, il ne faut pas dupliquer une même information à deux endroits différents, donc centraliser les contraintes de colonnes dans les clauses CHECK va dans ce sens.

L'ennui, c'est qu'en pratique ce sera trop frustre pour satisfaire les besoins des applications.
Par exemple si dans un formulaire on veut ajouter une phrase pour indiquer explicitement une contrainte à l'utilisateur, genre "Le poids ne peut pas dépasser 2500kg". Automatiser et généraliser la génération de tels messages à partir de la contrainte SQL est une mission impossible.

Il y aussi un problème d'architecture. Un test fait dans la page en javascript ne peut pas accéder directement à la base de données, il faut passer par des appels asynchrones de type service web qui ajoutent de la complexité et du coût de développement. Au final ça peut être beaucoup plus pénible que le problème qu'on cherchait à éviter initialement.