Discussion :

[al1_24]

En effet les règles de gestion s'implémentent sous la forme de contraintes d'intégrité.
Quant à la sécurité, c'est celle de la cohérence des données. Effectuer des contrôles à la saisie (en façade) et permettre tout et n'importe quoi dans la base de données, c'est bien dangereux pour toute l'application.
Imagine une comptabilité dans laquelle on pourrait réaffecter des écritures à un compte qui n'existe pas...

[plemaire]

En effet les règles de gestion s'implémentent sous la forme de contraintes d'intégrité.

J'ais peur que l'on ne parle pas de la même chose ... Ce que je nomme des contraintes d'intégrités, gérés ou non par le SGBD, ne représente que 10% et encore des Règles de Gestion d'une application. c'est ce point qui me semble important et peut pris en compte dans le débat.

Quant à la sécurité, c'est celle de la cohérence des données. Effectuer des contrôles à la saisie (en façade) et permettre tout et n'importe quoi dans la base de données, c'est bien dangereux pour toute l'application.
Imagine une comptabilité dans laquelle on pourrait réaffecter des écritures à un compte qui n'existe pas...

Dans ce cas on ne parle pas de sécurité, mais d'intégrité, à ne pas confondre.
J'ai déjà argumenté sur le sujet. Je ne ferait donc que répéter ce que j'ais déjà dit précédemment ...
Si une telle comptabilité existe, il faut changer sur le champs de logiciel.

[CinePhil]

Soit une entreprise fictive de travaux.
Un commercial enregistre dans le système d'information avec le logiciel Champagne la commande qu'il vient d'obtenir :
- Client = Toto and Co
- Chantier = Ajout de prises informatiques dans la salle de réunion
- Montant = 2 000 euros HT
- Délai de réalisation = 31/01/2010
- Heures prévues = 50
- Taux moyen = 30 euros
(je ne sais pas si ces chiffres sont réalistes, c'est pour l'exemple et je n'ai plus chiffré de devis depuis 1991 !)

Le système attribue un numéro d'affaire : 2010-01-05

Le chef de production, voyant qu'une commande est à réaliser rapidement, affecte du personnel à cette nouvelle affaire, à l'aide du logiciel Team :
- Affaire : 2010-01-05
- Matricule : 154 (un chargé d'affaire qui va gérer)
- Date début : 11/01/2010
- Budget heures : 10
- Matricule : 65 (un technicien d'étude)
- Budget heures : 8
- Matricule : 120
- Budget heures : 32

Le système enregistre cette affectation de personnel.

Le patron rentre du golf et regarde comment tourne la boîte et voit cette affaire d'un montant ridicule chez un client habituel. Il va voir le commercial qui est tout jeune dans la boîte et lui explique (gentiment parce c'est la première fois et qu'il a battu son concurrent au golf) qu'avec les clients habituels il est d'usage de mettre toutes ces petites commandes dans une affaire "Divers travaux". Il lui demande de modifier sa commande pour l'affecter à l'affaire n° 2010-01-01.
Le commercial rouvre donc le Champagne , réaffecte sa commande et supprime l'affaire 2010-01-05, ce que lui permet le logiciel.

Les données sont centralisées mais deux logiciels sont dans cet exemple utilisés.

Si l'intégrité référentielle (contrainte de clé étrangère) est utilisée, la suppression de l'affaire 2010-01-05 sera impossible avec un simple bout de ligne de SQL (ON DELETE RESTRICT) et la gestion de l'erreur par le programme Champagne (traduction d'une erreur SGBD du genre "suppression impossible pour cause de viol d'une contrainte de clé étrangère dans la table affecter_personnel" en "Du personnel a été affecté sur cette affaire, vous ne pouvez la supprimer).

S'il n'y a pas d'intégrité référentielle, il faudra que le programme Champagne :
- Interroge la BDD pour vérifier que l'affaire n'a pas déjà du personnel affecté (mais aussi des achats et autres engagements comptables) ;
- Interprète le résultat des requêtes pour adapter le message à envoyer à l'utilisateur.

Qu'est-ce qui est le plus simple et le plus robuste ?

[Pierre Fauconnier]

...

Qu'est-ce qui est le plus simple et le plus robuste ?

Pour moi, la réponse est évidente, et je ne comprends toujours pas comment on peut préférer pondre du code pour garantir l'intégrité, alors qu'il suffit de placer l'intégrité au niveau des tables...

C'est tellement évident pour des bases attaquées par plusieurs applis, mais même dans le cas d'une appli fermée, propriétaire, et qui n'autorise aucun accès à ses données autre que par l'appli elle-même, je ne comprends pas comment on préfère le code à l'intégrité native... Et je n'ose même pas imaginer ce code avec un jeu conséquent de tables, ni la mise à jour dudit code lors de l'évolution du logiciel...

[Jester]

Qu'est-ce qui est le plus simple et le plus robuste ?

Dans les deux cas ça ne va pas. Le commercial voit qu'il ne peux pas supprimer, il va appeler le service info qui va supprimer à la main (ce qui est toujours une erreur). Dans l'autre cas, la prod voit des missions fictives et va les supprimer ou demander à les supprimer.

La seule solution valable c'est de ne rien supprimer et de mettre une colonne annulée sur l'affaire qui invalide les missions.

Les contraintes d'intégrité sont en effet bien faibles face à toutes les règles qui doivent être vérifiées. Il faut les utiliser si on peut, mais ce n'est pas plus qu'un petit bouclier face à un volcan en éruption.

Si les contraintes suffisaient, il n'y aura pas un tel essor du domaine de la qualité des données. Le problème est bien plus vaste.

[CinePhil]

Dans les deux cas ça ne va pas. Le commercial voit qu'il ne peux pas supprimer, il va appeler le service info qui va supprimer à la main

Ou plus intelligemment il va appeler le chef de prod pour lui dire que le boss lui a demandé de réaffecter la commande à une autre affaire et lui demander de supprimer ses affectations.

Enfin bref, ce n'était qu'un exemple fictif mais c'est pour tenter de montrer que la contrainte sur la donnée est permanente quel que soit le logiciel qui l'utilise alors que la même contrainte dans le logiciel laisse la porte ouverte à d'autres logiciels de faire des bêtises avec les données.

Les mots cohérence, intégrité, sécurité, qualité des données ont été employés. Tout ça relève d'une même préoccupation et l'objectif de l'article de SQLPro, certe avec son style parfois un peu agressif, était d'expliquer qu'il vaut mieux gérer ça au plus près des données c'est à dire au maximum dans le SGBD.

Je n'ai pas une énorme expérience en BDD mais j'ai eu à mettre en oeuvre à l'INRA une BDD pour une étude sur les bovins, à partir de données en fichiers textes extraites de la BDD officielle des bovins. Sur seulement deux ans de données, j'ai trouvé des incohérences risibles et heureusement sans conséquence (des mâles ayant vêlé, des veaux nés avant leur mère, des vaches ayant vêlé le jour de leur naissance ou à un âge bien trop jeune pour pouvoir le faire...) mais qui démontraient que la BDD n'était pas robuste et que les statistiques faites au niveau national sur les bovins pouvaient s'en trouver faussées. J'y détectais aussi quelques soupçons de ruses pour obtenir des aides financières, autrement dit de la fraude !

[plemaire]

... (des mâles ayant vêlé, des veaux nés avant leur mère, des vaches ayant vêlé le jour de leur naissance ou à un âge bien trop jeune pour pouvoir le faire...) ...!

C'est interressants comme cas de figure ... Mais si je ne me trompe, ce sont typiquement des dérèglements de type fonctionnel non ?

Comment traduiriez vous ceci en ADD CONSTRAINT ? je suis curieux ...

En attendant votre réponse, voici un petit copier coller du contenu de la doc officielle SQLSERVER ... Si vous n'y voyez rien de ... choquant, alors soit, restons en là ...

Les contraintes CHECK rejettent les valeurs qui donnent FALSE. Comme les valeurs nulles donnent UNKNOWN, il se peut que leur présence dans les expressions supplante une contrainte.

Si une table venant d'être créée ne comporte aucune ligne, les éventuelles contraintes CHECK sur cette table sont considérées comme valides. Cette situation peut produire des résultats inattendus, comme l'illustre l'exemple suivant.

Les contraintes CHECK ne sont pas validées pendant les instructions DELETE. Par conséquent, l'exécution d'instructions DELETE sur des tables avec certains types de contraintes de vérification peuvent produire des résultats inattendus.

Personellement, en tant que developpeur, je préfère subir des bugs dans MON code, que de devoir "jouer" avec çà. Mais chacun fait comme il le sent. Encore heureux.

Quand au langage de l'auteur, s'il ne voulais pas dire ce qu'il a dit, il ne fallait pas employer les mots qu'il a employé.

[CinePhil]

C'est interressants comme cas de figure ... Mais si je ne me trompe, ce sont typiquement des dérèglements de type fonctionnel non ?

Non pour moi ce sont des contraintes à mettre sur les données !
La fonction consiste à renseigner la date de premier vêlage d'un bovin. La contrainte sur la donnée est que ça doit être rejeté pour un mâle.
La contrainte fonctionnelle pourrait être de renseigner, sur l'interface utilisateur, d'abord le champ Sexe et le champ date de naissance puis d'afficher le champ Date de premier vêlage seulement si c'est une femelle et qu'elle a au moins un certain âge acceptable.
Ca fait un petit paquet de lignes de code je pense.

Une autre façon de faire est d'afficher un formulaire de saisie complet, d'injecter les données saisies et de traiter les erreurs renvoyées par le SGBD, ce qui peut partiellement être fait par trigger dans le SGBD au passage.

Comment traduiriez vous ceci en ADD CONSTRAINT ? je suis curieux ...

Avec un CHECK... encore faut-il que le SGBD l'implémente, ce qui n'est pas le cas de MySQL !

En attendant votre réponse, voici un petit copier coller du contenu de la doc officielle SQLSERVER

Un logiciel Microsoft ! Pas étonnant qu'il y a ait des comportements bizarres !

... Si vous n'y voyez rien de ... choquant,

Si ça me choque !

Les contraintes CHECK rejettent les valeurs qui donnent FALSE. Comme les valeurs nulles donnent UNKNOWN, il se peut que leur présence dans les expressions supplante une contrainte.

Sauf que le CHECK peut prévoir le cas NULL. Je l'ai fait sur une BDD perso sous Postgresql. Je n'ai plus le cas exact en tête mais en gros c'était :

Code SQL :

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

CHECK (date_sortie > '1895-01-01' AND date_sortie <= CURRENT_DATE) OR date_sortie IS NULL

Si une table venant d'être créée ne comporte aucune ligne, les éventuelles contraintes CHECK sur cette table sont considérées comme valides. Cette situation peut produire des résultats inattendus, comme l'illustre l'exemple suivant.

Ca c'est très folklo !
Quand on crée une table (CREATE TABLE), il n'y a généralement pas encore de ligne dedans (sauf avec CREATE TABLE x SELECT..., et encore, il n'est pas impossible que le SGBD fasse d'abord un CREATE TABLE x puis un INSERT INTO x SELECT...).
Comme les contraintes CHECK sont sur les lignes de la table, il ne peuvent ni être valides ni être invalides à la création d'une table vide. En fait, elles n'ont même pas à être testées !
Qu'il existe un exemple produisant un phénomène curieux ici est clairement un bug de Microsoft !

Les contraintes CHECK ne sont pas validées pendant les instructions DELETE. Par conséquent, l'exécution d'instructions DELETE sur des tables avec certains types de contraintes de vérification peuvent produire des résultats inattendus.

C'est logique puisque CHECK n'est à tester que pendant un INSERT ou un UPDATE !
Encore une fois, que ç apuisse produire des résultats inattendus est un bug !
Et je serais curieux de voir ces cas bizarres !

Personellement, en tant que developpeur, je préfère subir des bugs dans MON code, que de devoir "jouer" avec çà.

Et tu n'as jamais été confronté à un bug du langage ?
J'ai lu récemment quelques trucs sur le caractère soi-disant aléatoire d'une fonction rand qui n'était pas si aléatoire que ça. Corrigé depuis mais quand même ; tu n'es pas à l'abri des bugs du langage que tu utilises, voire des bugs du compilateur.

Quand au langage de l'auteur, s'il ne voulais pas dire ce qu'il a dit, il ne fallait pas employer les mots qu'il a employé.

Je pense qu'il a bien dit ce qu'il voulait dire mais avec parfois un style qui peut choquer, je le reconnais. C'est un peu sa marque de fabrique.

[fsmrel]

Je ne parlerais que des historiques pour ne citer qu’un seul cas.

C’est comme pour une BDD, on ne peut dénormaliser que si on est capable de normaliser 5 d’abord, et ensuite d’expliquer la dénormalisation.

Et je dirai même plus : dans le cas de l'historisation des données, si vous ne normalisez pas en sixième forme normale et ne respectez pas les "Nine Requirements" associés, vous risquez tous les dangers et vous vous embarquez en tout cas dans des développements fort compliqués. Je vous renvoie à ce sujet à l'ouvrage de référence de Lorentzos, Darwen et Date, qui traitent du sujet en 400 pages (Temporal Data and the Relational Model).

Et je le répète, la cohérence « technique » n’est pas forcement la meilleure, de plus elle représente BEAUCOUP moins de complexité que la cohérence fonctionnelle.
Un exemple simple : Avoir une entête de commande sans ligne ne pose aucun problème de contrainte, mais peut être catastrophique pour le fonctionnel, et là vos contraintes ne servent à rien !

Je suppose que par cohérence technique, vous voulez parler de la cohérence assurée par le SGBD. Je suppose encore que par « moins de complexité » vous voulez dire que les contraintes déclarées par exemple par CREATE ASSERTION, ne sont pas capables d'exprimer les contraintes fonctionnelles, c'est-à-dire l’ensemble des règles de gestion des données.

Mais l'objet est bien de sous-traiter ces règles de gestion au SGBD. Pour reprendre l’exemple que vous donnez, dans un premier temps, c’est au niveau du MCD (ou du diagramme de classe UML) que vous exprimez la règle qui veut qu’une commande comporte au moins une ligne de commande : à cet effet, la patte connectant l’entité-type Commande et l’association-type B est porteuse d’une cardinalité minimale 1.



Pour exprimer ceci dans le cadre de la théorie relationnelle, vous définissez la contrainte traduisant cette cardinalité minimale :

CONSTRAINT CommandeConst1 IS_NOT_EMPTY (Commande JOIN LigneCommande) ;

Il est clair que cette contrainte remplace un bon paquet de lignes de code. Du point de vue des opérations, aucun problème, toujours dans le cadre de la théorie relationnelle, les contrôles sont déclenchés à des frontières d’instructions (statement boundaries), c'est-à-dire que vous pouvez empaqueter des INSERT (et autres instructions) que l’on les délimite par des virgules, tandis que les contrôles ne sont déclenchés qu’à la détection d’un point virgule, marquant la fin d’un paquet :

Code :

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

1
2
3
4
5
6
7
8
9
10
INSERT Commande RELATION {Tuple  {CommandeId      CommandeId       3,
                                  CommandeDate    Date             "2010-01-06",
                                  ClientId        ClientId         123,
                                  ... }} ,   /* virgule : séparateur d’opérations */
INSERT LigneCommande RELATION  {Tuple {CommandeId      CommandeId       3,
                                       LigneCdeId      Integer          1,
                                       ProduitId       ProduitId        314
                                ...    Quantite        Quantite         12
                                     }}  ;     /* point-virgule : fin de liste */

Si l’on, passe à SQL, vous pouvez utiliser l’instruction CREATE ASSERTION, par exemple :

Code SQL :

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

1
2
3
4
5
6
7
CREATE ASSERTION CommandeConst1 CHECK(NOT EXISTS 
        (SELECT * 
         FROM   Commande As X 
         WHERE  NOT EXISTS (SELECT * 
                           FROM LigneCommande As Y 
                           WHERE X.CommandeId = Y.CommandeId) ) )
      DEFERRABLE INITIALLY DEFERRED ;

Toutefois, cette façon de procéder est nettement moins satisfaisante car, comme en SQL on ne peut pas empaqueter les INSERT et autres, on est conduit à utiliser la clause DEFERRABLE et déclencher les contrôles au moyen de l’instruction SET CONSTRAINTS ALL IMMEDIATE une fois créée une première ligne de commande (à défaut, d’attendre le prochain COMMIT).

Une solution plus intéressante consiste à imposer la création simultanée d’une commande et d’une première ligne de commande, ce qui conduit à créer une vue à cet effet, ainsi que le trigger chargé de ventiler les données (au cas où le SGBD ne sait pas encore le faire tout seul) :

Code SQL :

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

1
2
3
4
CREATE VIEW CommandeV (CommandeId, CommandeDate, ClientId, LigneCommandeId, ProduitId, Quantite) AS
    SELECT  x.CommandeId, x.CommandeDate, x.ClientId, y.LigneCommandeId, y.ProduitId, y.Quantite 
    FROM    Commande AS x INNER JOIN LigneCommande AS y 
              ON x.CommandeId = y.CommandeId ;

Puis, avec SQL Server :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TRIGGER CommandeInsert ON CommandeV INSTEAD OF INSERT AS
INSERT INTO Commande
    SELECT    CommandeId
            , CommandeDate
            , ClientId
     FROM     INSERTED ;
 
INSERT INTO LigneCommande
    SELECT    CommandeId
            , LigneCommandeId
            , ProduitId
            , Quantite
     FROM     INSERTED ;

Et bien entendu, il faut prévoir le cas où à force de supprimer des lignes de commande, on pourrait se retrouver avec une commande sans aucune ligne :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TRIGGER LigneCommandeDelete ON LigneCommande AFTER DELETE AS
    SELECT ''
    FROM   Commande AS x, DELETED AS y
    WHERE x.CommandeId = y.CommandeId
      AND   NOT EXISTS (SELECT *
                        FROM   LigneCommande AS z
                        WHERE  x.CommandeId = z.CommandeId)
    IF @@Rowcount > 0
       BEGIN
        INSERT INTO LigneCommande 
            SELECT * from deleted ;
        Raiserror ('Delete ligne facture : une commande ne peut pas rester sans ligne',16,1) 
        RETURN
      END

Tout cela est-il compliqué ? Pour prendre un autre exemple, si une règle de gestion des données stipule que le montant total des salaires des employés des départements de l’entreprise ne doit pas dépasser tel pourcentage par rapport au budget des départements, rien de plus simple à garantir dans le cadre du Modèle Relationnel de Données, c'est-à-dire de la théorie relationnelle. Même chose si l’on utilise une assertion SQL. En revanche, si l’on utilise un trigger SQL ou du code applicatif, on devra faire des tests exhaustifs pour s’assurer qu’on ne laissera pas passer le cas auquel on n’aura pas pensé tout de suite.

Cela dit, si les programmes que vous développez doivent « tourner » sur plusieurs SGBD, je conviens qu’il est agaçant de devoir modifier le codage des triggers parce que la syntaxe peut hélas varier d’un SGBD à l’autre. Mais concernant l’intégrité d’entité (définition des clés primaires et alternatives), ainsi que l’intégrité référentielle, un changement de SGBD ne devrait pas poser de problème et une fois de plus, il faut la mettre en oeuvre.

La BDD est un outil GENERALISTE, qu'il convient d'utiliser à bon escient dans une application SPECIFIQUE. et si cela imlpique de NE PAS utiliser les FK, je ne vois pas en quoi cela choque. Et je ne comprend pas non plus, comment sans connaitre le besoin fonctionnel et Technique, on peut être aussi catégorique.

Une base de données n’est pas un outil. C’est une collection de données persistantes utilisées par les applications dans les entreprises.

Une donnée est persistante quand, une fois que le SGBD a validé son entrée dans la base de données, elle n’en sera retirée qu’au moyen d’une requête explicite faite au SGBD, par qui de droit.

A leur tour, le SGBD (disons relationnel) fournit le langage pour :

— Décrire la structure des données, sous forme de types et de variables relationnelles (ou de tables dans le cas de SQL) ;

— Affecter des valeurs de relations à ces variables (à cet effet, INSERT, UPDATE et DELETE sont des raccourcis pratiques, mais seulement des raccourcis) ;

— Dériver des valeurs de relations à partir d’autres valeurs de relations, au moyen d’opérateurs génériques (liste non limitée), qui constituent l’algèbre relationnelle (UNION, JOINTURE, RESTRICTION, PROJECTION, INTERSECTION, etc.)

— Garantir l’intégrité des données.

Ceci pour la partie fondamentale. Bien sûr, le SGBD doit fournir tout ce qui est nécessaire pour permettre les reprises sur incident, c'est-à-dire faire en sorte que les données restent dans un état cohérent quoi qu’il advienne, pour permettre la concurrence des accès à la base des données (mécanisme de verrouillage), pour assurer la sécurité des données, etc.

Quel que soit le support sur le quel reposent des données persistantes (y-compris sous forme de cartes perforées de la mécanographie des années cinquante, soixante), leur perte est irréparable. Souvenez-vous de l’incendie dans lequel Publicis a perdu ses données sauf, fort heureusement, le cœur de celles-ci. La leçon a été retenue par les autres entreprises et les sites de backup ont poussé comme des champignons. Mais ceci est une autre histoire.

[SQLpro]

Pour compléter les propos de fsmrel, si le SGBDR ne dispose pas de la déférabilité des contraintes on peut cependant le simuler à l'aide des transactions et de la gestion des privilèges en interdisant les INSERT/UPDATE/DELETE directes dans les tables et en passant par des procédures transactionnées.

Dans un cours que je donne à Orsys, voici un exemple d'un tel contournement pour simuler une contrainte circulaire (sous SQL Server 2005/5008) :

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
 
-- soit les tables suivantes, créées au sein du schéma S_CIR :
CREATE SCHEMA S_CIR
 
CREATE TABLE T_CLIENT_CLI
(CLI_ID           INT NOT NULL IDENTITY CONSTRAINT PK_CLI PRIMARY KEY,
 CLI_NOM          CHAR(32) NOT NULL,
 CMD_ID           INT NOT NULL CONSTRAINT FK_CLI_CMD FOREIGN KEY  
                               REFERENCES T_COMMANDE_CMD (CMD_ID))
 
CREATE TABLE T_COMMANDE_CMD
(CMD_ID           INT NOT NULL IDENTITY CONSTRAINT PK_CMD PRIMARY KEY,
 CLI_ID           INT NOT NULL CONSTRAINT FK_CMD_CLI FOREIGN KEY  
                               REFERENCES T_CLIENT_CLI (CLI_ID),
 CMD_DATE         DATE NOT NULL DEFAULT GETDATE());

Notez la contrainte circulaire : un client doit pointer vers la dernière commande active (obligatoirement car NOT NULL) et toute commande doit avoir un client.

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
--> tentative d'insertion
 
BEGIN TRANSACTION
 
DECLARE @CLI_ID INT, 
        @CMD_ID INT;
 
INSERT INTO S_CIR.T_CLIENT_CLI 
VALUES ('DUPONT', NULL);
 
SET @CLI_ID = SCOPE_IDENTITY();
 
INSERT INTO S_CIR.T_COMMANDE_CMD 
VALUES (@CLI_ID, GETDATE());
 
SET @CMD_ID = SCOPE_IDENTITY();
 
UPDATE S_CIR.T_CLIENT_CLI 
SET    CMD_ID = @CMD_ID 
WHERE  CLI_ID = @CLI_ID;
 
COMMIT TRANSACTION
 
--> échec.

Code :

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

1
2
3
4
5
6
-- suppression des objets
ALTER TABLE S_CIR.T_CLIENT_CLI DROP CONSTRAINT FK_CLI_CMD;
DROP TABLE S_CIR.T_COMMANDE_CMD;
DROP TABLE S_CIR.T_CLIENT_CLI;
DROP SCHEMA S_CIR
GO

Nouvelle donne, notez que désormais CMD_ID dans T_CLIENT_CLI est nullable :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE SCHEMA S_CIR
 
CREATE TABLE T_CLIENT_CLI
(CLI_ID           INT NOT NULL IDENTITY CONSTRAINT PK_CLI PRIMARY KEY,
 CLI_NOM          CHAR(32) NOT NULL,
 CMD_ID           INT          CONSTRAINT FK_CLI_CMD FOREIGN KEY  
                               REFERENCES T_COMMANDE_CMD (CMD_ID))
 
CREATE TABLE T_COMMANDE_CMD
(CMD_ID           INT NOT NULL IDENTITY CONSTRAINT PK_CMD PRIMARY KEY,
 CLI_ID           INT NOT NULL CONSTRAINT FK_CMD_CLI FOREIGN KEY  
                               REFERENCES T_CLIENT_CLI (CLI_ID),
 CMD_DATE         DATE NOT NULL DEFAULT GETDATE());
GO

Un client n'est pas un client s'il ne passe pas une première commande. Création d'une procédure d'insertion simultanée d'un client et sa commande au sein d'une transaction :

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
CREATE PROCEDURE S_CIR.P_I_CLI_CMD @NOM CHAR(32),
                                   @DATE DATE
WITH EXECUTE AS OWNER                             
AS
 
DECLARE @CLI_ID INT, 
        @CMD_ID INT;
 
BEGIN TRANSACTION;
 
INSERT INTO S_CIR.T_CLIENT_CLI 
VALUES (@NOM, NULL);
IF @@ERROR <> 0 GOTO LBL_ERROR;
 
SET @CLI_ID = SCOPE_IDENTITY();
 
INSERT INTO S_CIR.T_COMMANDE_CMD 
VALUES (@CLI_ID, @DATE);
IF @@ERROR <> 0 GOTO LBL_ERROR;
 
SET @CMD_ID = SCOPE_IDENTITY();
 
UPDATE S_CIR.T_CLIENT_CLI 
SET    CMD_ID = @CMD_ID 
WHERE  CLI_ID = @CLI_ID;
IF @@ERROR <> 0 GOTO LBL_ERROR;
 
COMMIT TRANSACTION;
 
RETURN;
 
LBL_ERROR:
ROLLBACK TRANSACTION;
 
GO

Création de l'utilisateur qui va traiter les données

Code :

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

1
2
3
4
5
6
7
--> assurez vous d'être en authentification mixte (donc SQL)
CREATE LOGIN CNX_CERCLE 
   WITH PASSWORD = 'rantanplan2001', 
        DEFAULT_DATABASE = DB_SQD;
CREATE USER USR_CERCLE
   FOR LOGIN CNX_CERCLE;
GRANT EXECUTE ON S_CIR.P_I_CLI_CMD TO USR_CERCLE;

On l'a doté de l'unique privilège d'exécution de la procédure

Test :

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
--> emprunt d'identité
EXECUTE AS LOGIN = 'CNX_CERCLE';
-- qui suis-je ?
SELECT USER, SYSTEM_USER;
 
-- puis je voir la table des clients ?
SELECT * FROM S_CIR.T_CLIENT_CLI;
 
-- puis-je insérer dans les deux tables ?
EXECUTE S_CIR.P_I_CLI_CMD 'DUVAL', '20100101'
-- OUI !
-- mais toujours pas voir !
SELECT * FROM S_CIR.T_CLIENT_CLI;
 
REVERT; --> retour à moi !
SELECT * FROM S_CIR.T_CLIENT_CLI;
SELECT * FROM S_CIR.T_COMMANDE_CMD;

CQFD !

A +

[SQLpro]

Dans les deux cas ça ne va pas. Le commercial voit qu'il ne peux pas supprimer, il va appeler le service info qui va supprimer à la main (ce qui est toujours une erreur). Dans l'autre cas, la prod voit des missions fictives et va les supprimer ou demander à les supprimer.

Vous oubliez qu'il existe différent mode de gestion de la référence tel que le DELETE CASCADE qui permet de supprimer le client et ses lignes filles, petites filles...etc d'un seule coup, ou (plus intelligent) le DELETE SET NULL / SET DEFAULT, conçu spécialement pour la gestion des VLDB avec déport des suppression physique aux heures creuses.
Relisez : http://sqlpro.developpez.com/cours/s...partie2#L7.3.2

Bref, Jester, vous avez encore beaucoup de choses à apprendre sur SQL et son fonctionnement !

A +

[_skip]

Je suis sûr qu'une majorité de développeurs préféreront tout de même gérer une contrainte nécessitant l'insertion simultanée côté client, en laissant les tables utiliser des contraintes 0;n, 1:1...

On estimera qu'il est plus simple d'encapsuler l'ajout d'une entête et de sa ligne de commande dans une seule transaction (à la fois au sens métier et SGBD).
Il restera le cas de la suppression de la ligne de commande qui laisserait l'entête orpheline. Ceci peut être géré côté client et rendu obligatoire par un trigger pour une sécurité optimale.

Perso j'aurai tendance à rejoindre ceux qui estiment que si une seule application cliente est amenée à taper dans la base, pour un cas de ce genre, autant l'assurer en partie côté client. Le compromis étant sinon d'utiliser des artifices sqlserver-guru-level complexes ou des ordres SQL qui ne sont pas forcément bien pris en charge par les API de requêtage standards (JDBC ou autres). Désolé si je me met à dos la moitié des gens ayant posté sur ce topic.

Si la contrainte devait impérativement émaner de la base, j'estime que la solution de la procédure stockée serait la plus acceptable et la plus facile à maintenir.

[CinePhil]

Je suis sûr qu'une majorité de développeurs préféreront tout de même gérer une contrainte nécessitant l'insertion simultanée côté client, en laissant les tables utiliser des contraintes 0;n, 1:1...

Peut-être parce qu'une majorité de développeurs n'ont pas de connaissances suffisantes sur les possibilités des SGBDR ?
Moi y compris d'ailleurs mais tous les jours j'apprends et j'essaie de me perfectionner.

Quand je vois la simplicité de certaines contraintes en SQL par rapport à ce qu'il faudrait faire en langage de programmation, ça me pousse vraiment à chercher à mettre la contrainte dans le SGBD. Mais il doit y avoir des cas où le SQL sera plus compliqué que l'application utilisatrice.

[Jester]

Vous oubliez qu'il existe différent mode de gestion de la référence tel que le DELETE CASCADE qui permet de supprimer le client et ses lignes filles, petites filles...etc d'un seule coup, ou (plus intelligent) le DELETE SET NULL / SET DEFAULT, conçu spécialement pour la gestion des VLDB avec déport des suppression physique aux heures creuses.
Relisez : http://sqlpro.developpez.com/cours/s...partie2#L7.3.2

Bref, Jester, vous avez encore beaucoup de choses à apprendre sur SQL et son fonctionnement !

Vous savez que je connais cela. Vous avez du mal à vivre hors de la technique, le delete cascade ne résout rien en lui seul. Le service commercial n'a pas vocation à avoir un impact sur les planning de la prod. Si les planning se détricotent tout seul il y aura sans doutes des impacts forts. Soit il faudra sans doute y aller en manuel soit il y aura de nombreuses lignes de code derrière.

Les choses sont souvent compliquées et ne peuvent pas se réduire à des contraintes simples de type not null ou reference. Pour ma par je dirais que toutes les règles de gestion doivent être centralisée pour aider la maintenance. Soit c'est dans le SGBD et alors il faut utiliser les outils de ce type soit c'est dans une couche au dessus du SGBD et alors ça aura moins d'impact. J'aurais tendance à préférer dans le SGBD si possible, mais c'est une décision qui dépend du contexte.

[SQLpro]

Je suis sûr qu'une majorité de développeurs préféreront tout de même gérer une contrainte nécessitant l'insertion simultanée côté client, en laissant les tables utiliser des contraintes 0;n, 1:1...

On estimera qu'il est plus simple d'encapsuler l'ajout d'une entête et de sa ligne de commande dans une seule transaction (à la fois au sens métier et SGBD).
Il restera le cas de la suppression de la ligne de commande qui laisserait l'entête orpheline. Ceci peut être géré côté client et rendu obligatoire par un trigger pour une sécurité optimale.

Perso j'aurai tendance à rejoindre ceux qui estiment que si une seule application cliente est amenée à taper dans la base, pour un cas de ce genre, autant l'assurer en partie côté client. Le compromis étant sinon d'utiliser des artifices sqlserver-guru-level complexes ou des ordres SQL qui ne sont pas forcément bien pris en charge par les API de requêtage standards (JDBC ou autres). Désolé si je me met à dos la moitié des gens ayant posté sur ce topic.

Si la contrainte devait impérativement émaner de la base, j'estime que la solution de la procédure stockée serait la plus acceptable et la plus facile à maintenir.

le problème est qu'il n'est ABSOLUMENT PAS POSSIBLE de garantir que cela marchera dans tous les cas. Il suffit d'une micro coupure de réseau pour que votre code client se plante et que votre pseudo contrainte cliente ne fonctionne pas correctement et rende la base incohérente. C'est pour cela qu'existe les transactions (et tout ordre SQL est une transaction) et un journal de transaction dont le but est que la base retombe toujours sur ses pattes quelque soit les avaries du système.

A +

[_skip]

le problème est qu'il n'est ABSOLUMENT PAS POSSIBLE de garantir que cela marchera dans tous les cas. Il suffit d'une micro coupure de réseau pour que votre code client se plante et que votre pseudo contrainte cliente ne fonctionne pas correctement et rende la base incohérente. C'est pour cela qu'existe les transactions (et tout ordre SQL est une transaction) et un journal de transaction dont le but est que la base retombe toujours sur ses pattes quelque soit les avaries du système.

Pas exactement en fait, car les 2 requêtes d'insertions faites par mon code le seront dans une même transaction, ce qui fait qu'avant que mon code client balance le commit à la fin des opérations, rien ne sera définitif et disparaitera en cas d'avarie.

La seule chose qui me semble poser problème, c'est l'application de cette contrainte si des opérations sont faites sur la base par un autre code que celui de mon programme, voire à la main.

[_skip]

Peut-être parce qu'une majorité de développeurs n'ont pas de connaissances suffisantes sur les possibilités des SGBDR ?
Moi y compris d'ailleurs mais tous les jours j'apprends et j'essaie de me perfectionner.

Oui pour certaines choses c'est un manque de connaissance.

Perso, comme je l'ai dit dans mes précédents posts, j'utilise volontiers les contraintes CHECK et les FOREIGN CONSTRAINT car je les estime faciles à mettre en oeuvre et très utiles pour servir de rempart contre une mauvaise opération faite par mon code client.

Comme cela a été dit, quand ceci n'est pas fait, il suffit généralement d'une ou deux requêtes SQL sur les bases pour détecter des enregistrements à problème.

Quand je vois la simplicité de certaines contraintes en SQL par rapport à ce qu'il faudrait faire en langage de programmation, ça me pousse vraiment à chercher à mettre la contrainte dans le SGBD. Mais il doit y avoir des cas où le SQL sera plus compliqué que l'application utilisatrice.

Et bien pour moi justement, l'exemple cité précédemment d'insertions simultanées avec vérifications différées, du code SQL non standard, le passage par une vue ou le reste, ça peut commencer à devenir un cas où la gestion côté client est plus simple.

Cela représente bien entendu un compromis. Autant je n'approuve pas les développeurs qui disent "je suis trop malin et mon code est trop super pour avoir recours à des vérifications coté SGBDR car je fais jamais d'erreurs et je testes à fond etc..." autant un moment donné je préfère éviter de surcomplexifier les choses au niveau de la base pour des problèmes qui sont gérables sans trop de risques coté client.

[plemaire]

Tout cela est-il compliqué ?

En fait oui énormement.
Si je construit une application, je vais tacher de centraliser au maximum, et de donnée une cohérence maximum à ce que je construit. Et pour faire cela il n'existe pas 50000 solutions, il faut standardiser l'intelligence de l'application, et la centraliser dans un seul outil. Pour des raisons évidentes de maintenabilité d'une application, de passage de connaissance, de documentation etc, il est Absolument incohérent d'éparpiller les cibles potentielles de recherche en cas de BUG de l'application.
Dailleurs je n'en mettrait pas ma main au feu mais presque, trouvez moi une application sérieuse qui utilise ne serait ce que les TRIGGER ... Ne me faites pas rire.

De plus, il faudrait arreter de diaboliser cette gestion des contraintes d'intégrité dans le code. Dans un code bien fait, il existe une fonction, écrite, codée, qui permet de faire la mise à jour des données dans une table, et cette fonction embarque tous les controles, fontionnels ou techniques. Et si je veux mettre à jour cette table, je DOIS passer par cette fonction. Ainsi TOUT est au même endroit !! C'est incomparable en terme de maintenance.

Maintenant, si vous arrivez à mettre au point un ERP complet, sans autre code que des contraintes d'intégrités, ... pourquoi pas, mais je ne sait pas pourquoi, j'ai un leger doute ... Bref.

Pour en revenir au developement.
Sans doute que je suis pas aussi calé que vous en SGBD, mais laissez moi mes 20 ans de dev de mon coté, et si vous en aviez autant, je pense que la discussion prendrait une tournure différente. Pas forcement négative, mais en prenant en compte un milliers de problématiques que dans votre petit univers vous n'appercevez même pas ... enfin à vous lire.

Quand à celui qui se dit développeur alors qu'il utilise ACCESS ou WINDEV ... je crois que nous ne nous comprendrons pas non plus, donc inutile.
Ces outils de développement sont idéaux pour de petites applications à forte valeur ajoutée en matière de stockage de l'information, mais avec des regles de gestion quasiment inexsitantes ... Enfin en général, et si j'en parle c'est que je les ai utilisé ... merci.

le problème est qu'il n'est ABSOLUMENT PAS POSSIBLE de garantir que cela marchera dans tous les cas. Il suffit d'une micro coupure de réseau pour que votre code client se plante et que votre pseudo contrainte cliente ne fonctionne pas correctement et rende la base incohérente. C'est pour cela qu'existe les transactions (et tout ordre SQL est une transaction) et un journal de transaction dont le but est que la base retombe toujours sur ses pattes quelque soit les avaries du système.
A +

Je peut donc en déduire que les micro coupures n'affectent pas les moteur de SGBD. Me voici donc rassuré , j'avais un doute .... Excusez mon ironie, et choisissez mieux vos arguments.

Là où par contre je vous rejoins, et je crains que votre intervention ne se fasse pas suite à la lecture complete de ce topic, ce qui est dommage, c'est que la TRANSACTION SQL, elle par contre, est une arme redoutable et incontournable pour garantir l'intégrité des données en effet. Mais je ne crois pas avoir lu le contraire, ici nous parlons des contraintes d'intégrités ...

[Jester]

Si je construit une application, je vais tacher de centraliser au maximum, et de donnée une cohérence maximum à ce que je construit. Et pour faire cela il n'existe pas 50000 solutions, il faut standardiser l'intelligence de l'application, et la centraliser dans un seul outil. Pour des raisons évidentes de maintenabilité d'une application, de passage de connaissance, de documentation etc, il est Absolument incohérent d'éparpiller les cibles potentielles de recherche en cas de BUG de l'application.

C'est exactement mon avis. Mais il faut rajouter qu'on peut tout centraliser dans le SGBD ce qui sera l'avis majoritaire ici. Ca a l'avantage de pouvoir utiliser les contraintes et autre outils du SGBD. Par contre, la lisibilité n'est pas optimale. Les performances sont discutables aussi. Vous avez du coup la persistance et le metier sur le même système.

C'est un peu le même débat sur les ETL, ça fait perdre en performance (on ne peut plus utiliser les requêtes SQL avancées, beaucoup de travail est fait sur le serveur ETL au lieu du SGBD, ...) mais c'est plus clair, plus maintenable donc avec potentiellement avec moins d'erreurs à terme et donc des données de qualité.

La centralisation est primordiale, parce que quand vous avez deux bases de données c'est pas vos contraintes qui vont servir à grand chose pour avoir des données de qualité.

[Pierre Fauconnier]

...Dailleurs je n'en mettrait pas ma main au feu mais presque, trouvez moi une application sérieuse qui utilise ne serait ce que les TRIGGER ... Ne me faites pas rire....

C'est le type même de l'argument nul et inutile... C'est quoi une application sérieuse? A te lire, ce sera une application qui respecte tes principes, et la non-sérieuse sera celle qui utilise les triggers... Un argumentaire plus fouillé serait bienvenu...

...
Quand à celui qui se dit développeur alors qu'il utilise ACCESS ou WINDEV ... je crois que nous ne nous comprendrons pas non plus, donc inutile....

Ah, cette condescendance mal placée, alors qu'il faudrait déjà s'entendre sur le terme "développeur" et ce qu'il recouvre...

C'est peut-être justement parce que je ne suis pas "développeur" selon TA définition du terme, que j'apprécie énormément partager au maximum les données entre diverses applications, de manière à ne pas dépendre du "génie" qui va développer l'erp miracle en me rendant totalement dépendant de lui...

Et dans cette conception du partage des données, je vais évidemment m'appuyer sur les outils à ma disposition pour garantir au mieux l'intégrité de mes données, et donc utiliser les outils mis à ma disposition par le SGDB (oui, j'ai l'outrecuidance de ranger Access dans les SGDB), plutôt que d'essayer de pondre du code qui au mieux, aurait pu être évité, et qui au pire ne me prémunira pas de problèmes...

Et même lorsque je développe une toute petite application en Access avec des données qui ne seront jamais partagées, je mets systématiquement l'intégrité référentielle, gagnant ainsi pas mal de temps qui aurait été gaspillé à pondre du code...