Meilleur moyen de tester les requêtes SQL

Donc, j’ai rencontré un problème dans le fait que nous continuons à avoir des requêtes SQL complexes avec des erreurs.

Cela se traduit essentiellement par l’envoi de courrier aux clients incorrects et d’autres «problèmes» comme celui-là.

Quelle est l’expérience de chacun dans la création de requêtes SQL comme celle-ci, essentiellement nous créons de nouvelles cohortes de données toutes les deux semaines.

alors voici quelques unes de mes pensées et les limites qui leur sont imposées.

Créer des données de test – bien que cela prouve que nous disposons de toutes les données correctes, cela n’impose pas l’exclusion d’anomalies dans la production. Il s’agit de données qui seraient considérées comme erronées aujourd’hui, mais qui ont peut-être été correctes il ya 10 ans. Elles n’étaient pas documentées et nous ne les connaissons donc qu’une fois les données extraites.

Créez des diagrammes et des cartes de données Venn – cela semble être un moyen solide de tester la conception d’une requête, mais cela ne garantit pas que la mise en œuvre est correcte. Cela amène les développeurs à aller de l’avant et à penser à ce qui se passe lorsqu’ils écrivent.

Merci pour toute consortingbution que vous pouvez donner à mon problème.

Vous n’écririez pas une application avec des fonctions de 200 lignes. Vous pouvez décomposer ces longues fonctions en fonctions plus petites, chacune avec une seule responsabilité clairement définie.

Pourquoi écrire votre SQL comme ça?

Décomposez vos requêtes, tout comme vous décomposez vos fonctions. Cela les rend plus courts, plus simples, plus faciles à comprendre, plus faciles à tester et plus faciles à restructurer. Et cela vous permet d’append des “shims” entre eux et des “wrappers” autour d’eux, comme vous le faites dans le code procédural.

Comment est-ce que tu fais ça? En faisant chaque chose importante qu’une requête fait dans une vue. Ensuite, vous composez des requêtes plus complexes à partir de ces vues plus simples, tout comme vous composez des fonctions plus complexes à partir de fonctions plus primitives.

Et la grande chose est que, pour la plupart des compositions de vues, vous obtiendrez exactement les mêmes performances avec votre SGBDR. (Pour certains, vous ne le ferez pas, alors quoi? L’optimisation prématurée est la racine de tout le mal. Codez d’abord correctement, puis optimisez si nécessaire.)

Voici un exemple d’utilisation de plusieurs vues pour décomposer une requête compliquée.

Dans l’exemple, comme chaque vue ajoute une seule transformation, chacune peut être testée indépendamment pour rechercher des erreurs et les tests sont simples.

Voici la table de base dans l’exemple:

create table month_value( eid int not null, m int, y int, v int ); 

Cette table est erronée car elle utilise deux colonnes, month et year, pour représenter un datum, un mois absolu. Voici notre spécification pour la nouvelle colonne calculée:

Nous ferons cela comme une transformation linéaire, de telle sorte qu’elle sortinge de la même manière que (y, m), et que pour tout (y, m) tuple, il existe une seule et unique valeur, et toutes les valeurs sont consécutives:

 create view cm_abs_month as select *, y * 12 + m as am from month_value; 

Maintenant, ce que nous devons tester est inhérent à notre spécification, à savoir que pour tout tuple (y, m), il y en a un et un (am), et que (am) s sont consécutifs. Écrivons quelques tests.

Notre test sera une requête SQL select , avec la structure suivante: un nom de test et une instruction de cas conciliée. Le nom du test est juste une chaîne arbitraire. L’énoncé de cas est juste le case when les instructions de test ont then 'passed' else 'false' end .

Les instructions de test ne seront que des sélections SQL (sous-requêtes) qui doivent être vraies pour que le test réussisse.

Voici notre premier test:

 --a select statement that catenates the test name and the case statement select concat( -- the test name 'For every (y,m) there is one and only one (am): ', -- the case statement case when -- one or more subqueries -- in this case, an expected value and an actual value -- that must be equal for the test to pass ( select count(distinct y, m) from month_value) --expected value, = ( select count(distinct am) from cm_abs_month) -- actual value -- the then and else twigs of the case statement then 'passed' else 'failed' end -- close the concat function and terminate the query ); -- test result. 

L’exécution de cette requête produit ce résultat: For every (y,m) there is one and only one (am): passed

Tant qu’il y a suffisamment de données de test dans month_value, ce test fonctionne.

Nous pouvons également append un test pour des données de test suffisantes:

 select concat( 'Sufficient and sufficiently varied month_value test data: ', case when ( select count(distinct y, m) from month_value) > 10 and ( select count(distinct y) from month_value) > 3 and ... more tests then 'passed' else 'failed' end ); 

Maintenant, testons, il est consécutif:

 select concat( '(am)s are consecutive: ', case when ( select count(*) from cm_abs_month a join cm_abs_month b on (( am + 1 = bm and ay = by) or (am = 12 and bm = 1 and ay + 1 = by) ) where a.am + 1 <> b.am ) = 0 then 'passed' else 'failed' end ); 

Maintenant, mettons nos tests, qui ne sont que des requêtes, dans un fichier et exécutons ce script sur la firebase database. En effet, si nous stockons nos définitions de vues dans un script (ou des scripts, je recommande un fichier par vues associées) à exécuter sur la firebase database, nous pouvons append nos tests pour chaque vue au même script, afin que -) la création de notre vue exécute également les tests de la vue. De cette façon, nous obtenons tous les deux des tests de régression lorsque nous recréons des vues et, lorsque la création de la vue est exécutée en production, la vue sera également testée en production.

Créez une firebase database de système de test que vous pouvez recharger aussi souvent que vous le souhaitez. Chargez vos données ou créez vos données et enregistrez-les. Produire un moyen facile de le recharger. Connectez votre système de développement à cette firebase database et validez votre code avant de passer à la production. Kick vous-même chaque fois que vous parvenez à laisser un problème entrer en production. Créez une suite de tests pour vérifier les problèmes connus et développer votre suite de tests au fil du temps.

Vous pourriez vouloir vérifier DbUnit , ainsi vous pouvez essayer d’écrire des tests unitaires pour vos programmes avec un ensemble fixe de données. De cette façon, vous devriez pouvoir écrire des requêtes avec des résultats plus ou moins prévisibles.

L’autre chose que vous pouvez faire est de profiler votre stack d’exécution SQL Server et de savoir si toutes les requêtes sont bien les bonnes, par exemple, si vous utilisez une seule requête qui renvoie à la fois des résultats corrects et incorrects, utilisé est en question, mais qu’en est-il si votre application envoie des requêtes différentes à différents points du code?

Toute tentative de réparer votre requête serait alors futile … les requêtes malhonnêtes pourraient toujours être celles qui déclenchent les mauvais résultats de toute façon.

Re: tpdi

 case when ( select count(*) from cm_abs_month a join cm_abs_month b on (( am + 1 = bm and ay = by) or (am = 12 and bm = 1 and ay + 1 = by) ) where a.am + 1 <> b.am ) = 0 

Notez que cela ne vérifie que les valeurs am pour les mois consécutifs seront consécutives, pas que des données consécutives existent (ce qui est probablement ce que vous vouliez initialement). Cela passera toujours si aucune de vos données source n’est consécutive (par exemple, vous n’avez que des mois pairs), même si votre calcul est totalement désactivé.

Est-ce que quelque chose me manque également ou est-ce que la seconde moitié de la clause ON fait sauter la mauvaise valeur de mois? (c’est-à-dire que le 12/2011 arrive après le 1/2010)

Pire encore, si je me souviens bien, SQL Server vous permet au moins moins de 10 niveaux de vues avant que l’optimiseur ne lève les mains virtuelles et commence à effectuer des parsings de table complètes sur chaque requête.

N’oubliez pas de tester le test de vos cas de test!

Sinon, créer un ensemble très large de données pour englober la plupart ou toutes les formes d’intrants possibles, en utilisant SqlUnit ou DbUnit ou toute autre unité * pour automatiser la vérification des résultats attendus par rapport à ces données, réviser, maintenir et mettre à jour le chemin à parcourir.