Procédures stockées et logique métier

Version imprimable

07/03/2018, 21h24
JackIsJack

Procédures stockées et logique métier

Bonjour,

J'utilise SQL Server dans le cadre d'application web et je me pose la question suivante :

-> Faut-il insérer de la logique métier dans des procédures stockées ?

Il me semble que le schéma sécurisé pour modifier des données dans une base est d'avoir :
- une application qui interroge une base de données,
- puis elle instancie des objets avec les données qu'elle a récupéré
- fait des modifications sur ces objets
- l'application contrôle à l'enregistrement que les données sont cohérentes vis-à-vis du modèle
- et si c'est ok : l'application ordonne les modifications de données dans la base.

Sauf que voilà, à mon travail, je vois des procédures stockées qui font des update, des delete,...
Rien n'indique que ces modifications n'altèrent pas la cohérence des données.

1/ Je suppose que c'est pour des raisons de performance que ces traitements sont situés côté serveur
Cela prend du temps de peupler des objets, de transférer des données, de contrôler que l'état final de chaque objet est clean.
J'ignore s'il existe des façons rapides et propre de réaliser des actions de masse par le biais d'une application.

2/ Ou alors cela signifie qu'il n'y a pas de contrôle de cohérence dans l'application supplémentaire à ceux qui sont dans la base de données : type des données, clef unique, intégrité relationnelle, valeur.
Dans ce cas-là, il me semble inutile d'avoir un quelconque modèle objet dans l'application ; autant faire du clic bouton = requête.

Comment gérer-vous ces problématiques ? (si vous les gérer)

Merci...

Citation:

Envoyé par JackIsJack

Bonjour,

J'utilise SQL Server dans le cadre d'application web et je me pose la question suivante :

-> Faut-il insérer de la logique métier dans des procédures stockées ?

C'est une question mainte fois débattue, qui dépend de ce que vous voulez faire :

obtenir d'excellentes performances
ou vous faire plaisir avec un modèle idéologiquement parfait...

Malheureusement l'un ne va pas avec l'autre !

Citation:

Il me semble que le schéma sécurisé pour modifier des données dans une base est d'avoir :
- une application qui interroge une base de données,
- puis elle instancie des objets avec les données qu'elle a récupéré
- fait des modifications sur ces objets
- l'application contrôle à l'enregistrement que les données sont cohérentes vis-à-vis du modèle
- et si c'est ok : l'application ordonne les modifications de données dans la base.

ça c'est l'idéologie... Mais elle ne résiste pas une minute à une analyse sérieuse... En effet vous indiquez que c'est à l'application de contrôler la cohérence. Ceci est strictement impossible à faire dans un monde ensembliste et multi utilisateur (le SGBDR) par l'application (qui est un monde itératif et mono utilisateur).
En effet, en dehors des contraintes de domaine (vérifier qu'une information unique et atomique est dans les clous), tous les autres contrôles sont impossible à établir dans une application....En d'autres termes, la cohérence est du ressort du SGBDR et de rien d'autre, si vous voulez une base intègre.

Un seul exemple...
L'utilisateur A visionne des données de début et de fin d'un process (id 456) avec les données suivantes :
Code:

1 2 3 USER A... Début : 20h56 Fin : 23h21
L'utilisateur B, visionne les mêmes données :
Code:

1 2 3 USER B... Début : 20h56 Fin : 23h21
A modifie maintenant la date de fin :
Code:

1 2 3 USER A... Début : 20h56 Fin : 22h48
B modifie maintenant la date de début :
Code:

1 2 3 USER A... Début : 22h51 Fin : 23h21
L'application contrôle bien que la date de fin vue dans le formulaire de A est supérieure à la date de début et la commande SQL Suivante est lancée :

Code:

UPDATE MaTable SET Fin = 22h48 WHERE ProcessID = 456

L'application contrôle bien que la date de début vue dans le formulaire de B est inférieure à la date de début et la commande SQL Suivante est lancée :

Code:

UPDATE MaTable SET Fin = 22h48 WHERE ProcessID = 456

Nous nous retrouvons dans la base avec une incohérence qui fait que la date de début est ultérieure à la date de fin...

Le seul moyen de garder la cohérence des données est d'intégrer les règles dans la base puisque c'est le point focal de toutes les données, sous la forme de contraintes. Dans notre cas :

Code:

ALTER TABLE MaTable ADD CONSTRAINT CK_DEB_FIN CHECK(Début <= Fin);

Il n'existe pas d'autre solution viable...

Citation:

Sauf que voilà, à mon travail, je vois des procédures stockées qui font des update, des delete,...
Rien n'indique que ces modifications n'altèrent pas la cohérence des données.

Bien au contraire ! Car ce sont les contraintes qui protège la base des incohérences. En sus, exécuter du code manipulant les données au sein même du SGBDR est incommensurablement plus rapide que de passer à travers un réseau de multiples fois pour vérifier unitairement telle ou telle condition... En fait 99% du temps serait dans ce cas des "round trips". C'est d'ailleurs ce qui fait les choux gras de notre métier d'expert et nous permet de vendre moult audits dans lequel on réapprends aux développeurs à travailler proprement !!!

Citation:

1/ Je suppose que c'est pour des raisons de performance que ces traitements sont situés côté serveur
Cela prend du temps de peupler des objets, de transférer des données, de contrôler que l'état final de chaque objet est clean.

Oui

Citation:

J'ignore s'il existe des façons rapides et propre de réaliser des actions de masse par le biais d'une application.

Aucune... Les application fonctionnement de manière itératives c'est à dire ligne par ligne. Les SGBDR fonctionnement de manière ensembliste, c'est à dire par bloc (les mêmes valeurs ne sont traitées qu'une seule fois dans le lot) voire en parallèle...
Et c'est souvent même pire que ce que vous imaginez car si en plus on a emballé le tout dans une transaction, alors les blocages sont maintenus pendant tout le traitement et comme il prendre 99 fois plus de temps que s'il était encapsulé dans une procédure, alors la concurrence d'accès en prend un coup plus que sévère se traduisant par des blocages, de la contention (files d'attente) voir des verrous mortels (interblocages)... bref, l'horreur !

Citation:

2/ Ou alors cela signifie qu'il n'y a pas de contrôle de cohérence dans l'application supplémentaire à ceux qui sont dans la base de données : type des données, clef unique, intégrité relationnelle, valeur.

OUI !!!! ça commence à faire tilt dans votre tête...

Citation:

Dans ce cas-là, il me semble inutile d'avoir un quelconque modèle objet dans l'application ; autant faire du clic bouton = requête.

Vous arrivez à la bonne conclusion !

Citation:

Comment gérer-vous ces problématiques ? (si vous les gérer)

Merci...

Il suffit d'implémenter le maximum de contrainte dans la base, envoyer les requêtes ou appeler les procédures et attendre la réponse...

Notez bien que sis les SGBDR ne sont pas objet, on peut s'en approcher de très près par le biais des vues, des procédures et des déclencheurs "INSETAD OF". Bref, faire du mapping relationnnel objet (ORM) à l'intérieur du SGBDR... Pour continuez cette analyse je vous propose la lecture d'un article écrit il y a déjà longtemps sur le sujet :
http://sqlpro.developpez.com/cours/b...s-epaisses.pdf

Je vais vous poser une petite question pour vous montrer que les développeurs oublient toujours quelque chose... Voici un code que l'on va dire applicatif :
Code:

1 2 3 4 5 6 7 ObjQuery.QueryText('SELECT * FROM MaTable WHERE Clef = 234'); ObjQuery.exec; if ObjQuery.DataSet.IsEmpty then ObjQuery.QueryText('INSERT INTO MaTable (Clef) VALUE (234)'); ObjQuery.exec; end;
Qui est censé ne rentrer que des valeurs de clef sans doublon... Ce code va t-il fonctionner correctement ?

A +

07/03/2018, 23h38
JackIsJack

Merci pour votre réponse précise, intéressante et pédagogique.

J'essaie de répondre à la devinette :)

L'id doit être calculé au moment où l'insert est réalisé (soit par un MAX, soit par le fait d'avoir une colonne autoincrement), et de non de façon dissocié comme c'est le cas dans le code présenté.
=> Sinon il peut y avoir un conflit ; dans la mesure où il n'y a pas de lock.

Le même code situé dans une procédure stockée n'aurait pas plus de succès je pense.
Et y compris si ces deux requêtes étaient située au sein d'une même transaction : le conflit peut intervenir et serait résolu par le rollback d'une des transactions.

J'ai bon ? :)
08/03/2018, 08h33
JackIsJack

Eh bien finalement votre réponse (et l'article associé) a bien perturbé ma nuit !

Il me reste quelques contradictions à résoudre avant d'accepter la mort du modèle objet applicatif et d'imaginer des applications 'clic-bouton = requête' :)

Pouvez-vous continuer à m'aider peut-être ?

1/ A mon travail, j'ai de la visibilité sur les bases de données de plusieurs progiciels gérés par des éditeurs externes (Sage, DimoSoftware, iValua...).

Ces bases contiennent très peu de procédures stockées... je ne les vois pas en tout cas et ce n'est pas un problème de droit.

Je m'étonne que ces éditeurs n'y recourent pas davantage si c'est une pratique aussi dangereuse de ne pas le faire. Les données des plus grosses sociétés en France sont gérés avec ces logiciels ; je sais que ce n'est pas un argument en soi, c'est juste une présomption.
Ou alors ces applications génèrent des procédures stockées à la volée ? J'ignore si c'est possible.

2/ La logique de base de données m'irrite un peu pour plusieurs raisons :

a) Sa clarté. Les jointures et autres accessoires SQL sont des logiques techniques.
Je n'ai jamais entendu un client final exprimer sa logique métier en terme ensembliste ; au mieux, il le fait en terme algorithmique/itératif.

Si l'on a uniquement du code SQL, est-ce que l'on ne risque pas de créer des développeurs/applications 'zombies' qui ne savent plus ce qu'ils traitent en terme métier ?
La couche 'Objet' nuit forcément à la performance, mais sa logique algorithmique nous garantie une forme de proximité avec le raisonnement métier, et donc de maintenabilité (et encore, elle n'est pas gagnée...).

Là il y a surement des bonnes pratiques pour développer de façon 'compréhensible/métier' en SQL ; mais je ne vous demande pas lesquels, ce serait trop long :)

b) La gestion des exceptions.

Si une traitement SQL rencontre une exception ; j'ai toujours été surpris de l'imprécision avec laquelle survient cette exception.
On ne connait pas la ligne ni la colonne qui pose problème ; on connait juste la contrainte qui a provoqué l'anomalie.
Ensuite, il faut chercher la donnée qui pose problème ; et cela requiert un travail d'analyse plus conséquent.
Est-ce une réalité ou une incompétence de ma part ?

c) La gestion du temps de chargement

En cas de traitement long, il est utile d'avoir l'information du 'temps restant' : combien de temps va prendre le traitement ?
Dois-je rester devant mon écran ou puis-je faire autre chose ?
Avec la logique itérative, on peut facilement avoir une idée de l'avancement ; je préfère qu'un traitement dure 10 minutes et m'informe de cela, plutôt qu'un traitement de 20 secondes qui ne m'aurait pas prévenu.
Là aussi c'est peut-être une méconnaissance de ma part.

3/ Pour la gestion des conflits (avec l'exemple de la modification d'heure que vous proposez), je sais que Entity Framework gère cela en natif.
Une exception surviendra si l'état de l'objet a été modifié en temps.
Du coup, il est effectivement possible d'atteindre la 'cohérence' multi-utilisateurs avec le modèle objet.

... Hélàs je manque de temps, je dois filer au travail ; j'aurai aimer pouvoir parler des tests unitaires sql, du vrai problème de la performance ou pas, du cache serveur côté application,...

Bonne journée !
10/03/2018, 04h51
SQLpro

Citation:

Envoyé par JackIsJack

Merci pour votre réponse précise, intéressante et pédagogique.

J'essaie de répondre à la devinette :)

L'id doit être calculé au moment où l'insert est réalisé (soit par un MAX, soit par le fait d'avoir une colonne autoincrement), et de non de façon dissocié comme c'est le cas dans le code présenté.
=> Sinon il peut y avoir un conflit ; dans la mesure où il n'y a pas de lock.

Le même code situé dans une procédure stockée n'aurait pas plus de succès je pense.
Et y compris si ces deux requêtes étaient située au sein d'une même transaction : le conflit peut intervenir et serait résolu par le rollback d'une des transactions.

J'ai bon ? :)

Ce même code à toutes les chances d'être un jour lancé exactement en même temps par deux utilisateurs différents conduisant au même calcul de clef et introduisant donc un doublon.
Il n'y a donc pas d'autres méthode que de créer une contrainte d'unicité...

A +
10/03/2018, 04h56
SQLpro

Citation:

Envoyé par JackIsJack

Eh bien finalement votre réponse (et l'article associé) a bien perturbé ma nuit !...

Beaucoup de questions intéressantes que j'aimerais développer avec vous... Malheureusement je suis aux US chez MS en ce moment et vais rentrer mardi matin...

Sincèrement je voudrais répondre point par point, car il y a beaucoup de choses à dire.

Pouvez-vous me contacter mercredi dans la journée ?
Si oui, envoyez moi un email privé avec vos coordonnées

Bien entendu je posterais un résumé de nos conversations ici même afin que chacun puisse en prendre connaissances.

A +
11/03/2018, 01h28
SQLpro

Citation:

Envoyé par JackIsJack

E1/ A mon travail, j'ai de la visibilité sur les bases de données de plusieurs progiciels gérés par des éditeurs externes (Sage, DimoSoftware, iValua...).

Ces bases contiennent très peu de procédures stockées... je ne les vois pas en tout cas et ce n'est pas un problème de droit.

Je m'étonne que ces éditeurs n'y recourent pas davantage si c'est une pratique aussi dangereuse de ne pas le faire. Les données des plus grosses sociétés en France sont gérés avec ces logiciels ; je sais que ce n'est pas un argument en soi, c'est juste une présomption.
Ou alors ces applications génèrent des procédures stockées à la volée ? J'ignore si c'est possible.

S'il n'y a pas de procédures stockées il y a différentes raisons :
1) l'ignorance.... Plus fréquente qu'on le croit
2) des logiciels simplement "upgradés" en SGBD Relationnels qui avant était dans des SGBD fichiers
3) une supposée interopérabilité pour soit disant être compatible avec plusieurs SGBDR (ce qui n'est même pas possible au niveau des simples requêtes SQL)
4) l'intégrisme du 3 ou 4 tiers imposant la logique métier dans un serveur applicatif
N'oubliez pas que chez les grands éditeurs, de nombreux logiciels viennent d'appli resucées en SQL ! Par exemple, en compta, seule la ligne 1000 de Sage a réellement été écrite pour un SGBD Relationnel... Les autres sont des resucées.

Citation:

2/ La logique de base de données m'irrite un peu pour plusieurs raisons :

a) Sa clarté. Les jointures et autres accessoires SQL sont des logiques techniques.

Pas du tout, c'est de l'algèbre donc des math, pas de la technique au sens "bas" du terme. Mais là n'est pas la question

Citation:

Je n'ai jamais entendu un client final exprimer sa logique métier en terme ensembliste ; au mieux, il le fait en terme algorithmique/itératif.

Bien des logiques métiers se conçoivent au niveau de la modélisation et non au niveau des requêtes. Attention, je n'ai pas dit qu'il fllait que le modèle colle à la logique métier. Mais un modèle s'exprime à divers niveaux :
MCD => conception (Modèle Conceptuel de Données) : les entités (ou classes) et les associations (+ contraintes de modèle ou apparaissent déjà des règles métiers)
MLD => normalisation (Modèle Logique de Données) : les relations (au sens mathématique du terme), + des contraintes de modèle multitabulaires (c'est à dire des règles plus complètes portant sur plusieurs tables, qui seront réalisées à l'aide de déclencheurs)
MPD => réalisation (Modèle Physique de Données) : choix du SGBDR, des types, du stockage des index....
MED => présentation (Modèle Externe de Données) le grand oublié, permettant de présenter correctement l'information à l'aide des vues, des procédures et en codant les déclencheurs métiers....

Citation:

Si l'on a uniquement du code SQL, est-ce que l'on ne risque pas de créer des développeurs/applications 'zombies' qui ne savent plus ce qu'ils traitent en terme métier ?

Il suffit que les développeurs apprennent le langage SQL et la programmation des routines SQL... Ce qui me fait marrer c'est que le SQL perdure depuis plus de 30 ans, que même le noSQL est revenu au tout SQL (voir Google Big Query par exemple...)... mais que l'on continue à torcher les cours de SQL en quelques heures pour former les ingénieurs......

Citation:

La couche 'Objet' nuit forcément à la performance, mais sa logique algorithmique nous garantie une forme de proximité avec le raisonnement métier, et donc de maintenabilité (et encore, elle n'est pas gagnée...).

La couche objet à ses limites... Essayez donc de me représenter des objets ayant des évolutions complexes dans le temps ou avec des étapes temporelles importantes nécessitant des contraintes temporelles.... L'objet c'est bien pour des éléments statique. Dès que le temps est introduit, ça ne marche pas.
Avez vous remarqué aussi que les langages purement objet n'existent pas ? Seule les langages hybrides, mêlant scalaires (nombre, chaines de caractères...) et objet ont résistés ? De la même manière on a bien tenté l'approche base de données tout objet... ça a été une cata... De ce fait et sous l'impulsion du "third manifesto" de Chris Date on a introduit la nécessité des objets dans les SGBDR

Citation:

Là il y a surement des bonnes pratiques pour développer de façon 'compréhensible/métier' en SQL ; mais je ne vous demande pas lesquels, ce serait trop long :)

Oui ! Cela s'apprend....

Citation:

b) La gestion des exceptions.

Si une traitement SQL rencontre une exception ; j'ai toujours été surpris de l'imprécision avec laquelle survient cette exception.
On ne connait pas la ligne ni la colonne qui pose problème ; on connait juste la contrainte qui a provoqué l'anomalie.

C'est amusant, car c'est une question qui vient d'être débattue en partie au MVP Summit de Microsoft à Redmond. Comme c'est sous NDA, je ne vous révèlerait rien. Mais il est évident que cela est lié au fonctionnement ensembliste des requêtes SQL. Il n'est pas toujours possible de savoir quelle est la ligne visé par une exception venant d'une contrainte, tout simplement par ce qu'il peut y avoir plusieurs lignes en faute (hors la première rencontrée - qui n'est pas forcement la première dans la collection ! - provoque l'exception) comme il e=se peut qu'il n'y ait pas de possibilité de repérer cette ligne (les SGBDR et SQL permettant des tables sans clef...).
Mais si vous avez encapsulé votre mise à jour dans une procédure alors il est extrêmement facile de rajouter au message d'erreur les paramètres permettant de rejouer la requête afin de trouver ce qui cloche.

Citation:

Ensuite, il faut chercher la donnée qui pose problème ; et cela requiert un travail d'analyse plus conséquent.
Est-ce une réalité ou une incompétence de ma part ?

Comme dit, dans une procédure c'est beaucoup plus facile.... À condition de rajouter une gestion d'exception personnalisée.

Citation:

c) La gestion du temps de chargement

En cas de traitement long, il est utile d'avoir l'information du 'temps restant' : combien de temps va prendre le traitement ?
Dois-je rester devant mon écran ou puis-je faire autre chose ?
Avec la logique itérative, on peut facilement avoir une idée de l'avancement ; je préfère qu'un traitement dure 10 minutes et m'informe de cela, plutôt qu'un traitement de 20 secondes qui ne m'aurait pas prévenu.
Là aussi c'est peut-être une méconnaissance de ma part.

Non, vous avez parfaitement raison. Mais entre 10 mn et 20 secondes je ne vous dit pas ce que je ferais. Il m'est arrivé une fois dans ma vie de virer un développeur de la boîte parce qu'il m'avait fait ça et en plus passé une semaine au lieu d'écrire une requête qui lui aurait demandé au plus une heure et moins de 5 minutes à moi même...
Reste que lorsque l'on sait à l'avance que le traitement va être long on le fait en mode batch.
De plus compte tenu des nombreuses couches on sait de moins en moins montrer la progression. Application avec code C + Web + appel à d'autres services.... et c'est mort. C'est la raison pour laquelle les objets qui montrent la progression sont de plus en plus circulaires afin de ne pas avoir de bout !!!!

Citation:

3/ Pour la gestion des conflits (avec l'exemple de la modification d'heure que vous proposez), je sais que Entity Framework gère cela en natif.
Une exception surviendra si l'état de l'objet a été modifié en temps.

Faites des tests, mais je pense pas que cela fonctionne du tout... !

Citation:

Du coup, il est effectivement possible d'atteindre la 'cohérence' multi-utilisateurs avec le modèle objet.

Ne croyez pas... testez en multi concurrence !

Citation:

... Hélàs je manque de temps, je dois filer au travail ; j'aurai aimer pouvoir parler des tests unitaires sql, du vrai problème de la performance ou pas, du cache serveur côté application,...

Bonne journée !pas le temps de vous répondre pour la suite, je corrigerais ou amenderais mon email plus tard, car j'ai un certain AP et un autre JP qui me réclament à cor et à cri sur une scène de crime à Las Vegas..... !:lol:

A +

A +