Bonsoir Del,
A propos de la spécialisation des entités-types.
Reprenons cette partie de votre MCD (cf. post #62) :
Intéressons-nous aux prestations, et situons-nous au niveau de la théorie relationnelle, auquel cas PRESTATION, PRODUCTION et DIFFUSION sont des relvars (variables relationnelles), qu’on déclare de la façon suivante (je ne reprends pas forcément tous les attributs des entités-types, quant à leur type, on n’en sait pas assez, mais en l’occurrence peu importe) :
VAR PRESTATION BASE RELATION
{
PrestationId INT
, PrestationTitre VARCHAR
, PrestationTerminee BOOLEAN
}
KEY {PrestationId}
;
VAR PRODUCTION BASE RELATION
{
PrestationId INT
, ProductionJour INT
, ProductionUpdate INT
}
KEY {PrestationId}
FOREIGN KEY {PrestationId} REFERENCES PRESTATION {PrestationId} ON DELETE CASCADE
;
VAR DIFFUSION BASE RELATION
{
PrestationId INT
, DiffusionContexte VARCHAR
}
KEY {PrestationId}
FOREIGN KEY {PrestationId} REFERENCES PRESTATION {PrestationId} ON DELETE CASCADE
;
Dans votre MCD, la lettre « P » au sein d’un triangle marque la volonté d’imposer une contrainte de partitionnement : ainsi, une prestation est, au moins et au plus, soit une production, soit une diffusion. Rappelons que le partitionnement représente à la fois l’exclusion et la totalité :
A) Exclusion : l’ensemble (au sens de la théorie des ensembles) des productions est disjoint de l’ensemble des diffusions (une prestation est soit une production, soit une diffusion, elle ne peut pas être les deux à la fois).
Toujours dans le cadre de la théorie relationnelle, cette contrainte fait l’objet d’une instruction CONSTRAINT :
CONSTRAINT DELARITA_PRESTATION_EXCLUSION
IS_EMPTY
(
PRODUCTION {PrestationId} INTERSECT DIFFUSION {PrestationId}
)
;
Ce qui se lit : l’intersection (a) de la projection sur l’attribut PrestationId de la relvar PRODUCTION et (b) de la projection sur l’attribut PrestationId de la relvar DIFFUSION doit être égale à l’ensemble vide.
B) Totalité : il ne peut y avoir de prestation qui ne soit ni une production ni une diffusion. Contrainte correspondante :
CONSTRAINT DELARITA_PRESTATION_INCLUSION
(
PRESTATION {PrestationId} = PRODUCTION {PrestationId} UNION DIFFUSION {PrestationId}
)
;
Ce qui se lit : La projection sur l’attribut PrestationId de la relvar PRESTATION doit être égale à l’union (a) de la projection sur l’attribut PrestationId de la relvar PRODUCTION et (b) de la projection sur l’attribut PrestationId de la relvar DIFFUSION.
Dans la théorie relationnelle, les contrôles sont immédiats. Si donc on tentait de créer une production de la façon suivante :
INSERT PRESTATION RELATION {TUPLE {PrestationId 2,
PrestationTitre 'Y a du mou dans la corde à noeuds',
PrestationTerminee TRUE
}
} ;
INSERT PRODUCTION RELATION {TUPLE {PrestationId 2
ProductionJour 20,
ProductionUpdate 0
}
} ;
INSERT PRESTATION RELATION {TUPLE {PrestationId 2,
PrestationTitre 'Y a du mou dans la corde à noeuds',
PrestationTerminee TRUE
}
} ,
INSERT PRODUCTION RELATION {TUPLE {PrestationId 2
ProductionJour 20,
ProductionUpdate 0
}
} ;
Qu’en est-il avec PostgreSQL ? Comme dans le cas de l’exemple précédent (post #63), on va simuler l’affectation multiple et mettre en oeuvre des triggers et des fonctions.
Commençons par déclarer les tables (sans oublier les « ON DELETE CASCADE » et « INITIALLY DEFERRED » :
DROP TABLE IF EXISTS DIFFUSION CASCADE ;
DROP TABLE IF EXISTS PRODUCTION CASCADE ;
DROP TABLE IF EXISTS PRESTATION CASCADE ;
CREATE TABLE PRESTATION
(
PrestationId INT NOT NULL
, PrestationTitre VARCHAR(32) NOT NULL
, PrestationTerminee BOOLEAN NOT NULL
, CONSTRAINT PRESTATION_PK PRIMARY KEY (PrestationId)
) ;
CREATE TABLE PRODUCTION
(
PrestationId INT NOT NULL
, ProductionJour INT NOT NULL
, ProductionUpdate INT NOT NULL
, CONSTRAINT PRODUCTION_PK PRIMARY KEY (PrestationId)
, CONSTRAINT PRODUCTION_PRESTATION_FK FOREIGN KEY (PrestationId)
REFERENCES PRESTATION (PrestationId)
ON DELETE CASCADE
INITIALLY DEFERRED
) ;
CREATE TABLE DIFFUSION
(
PrestationId INT NOT NULL
, DiffusionContexte VARCHAR(32) NOT NULL
, CONSTRAINT DIFFUSION_PK PRIMARY KEY (PrestationId)
, CONSTRAINT DIFFUSION_PRESTATION_FK FOREIGN KEY (PrestationId)
REFERENCES PRESTATION (PrestationId)
ON DELETE CASCADE
INITIALLY DEFERRED
) ;
Une transaction pour créer une prestation (diffusion en l’occurrence) :
START TRANSACTION ;
INSERT INTO DIFFUSION (PrestationId, DiffusionContexte) VALUES (1, 'contexte a) ;
INSERT INTO PRESTATION (PrestationId, PrestationTitre, PrestationTerminee) VALUES (1, 'prestation a (diffusion)', TRUE) ;
COMMIT ;
Pour éviter de coder à chaque fois la paire d’INSERT, on peut préférer utiliser les services d’une fonction.
Déclarons cette fonction :
CREATE OR REPLACE FUNCTION DELARITA_PRESTATION_DIFFUSION_INSERT(PrestaId INT, PrestaTitre VARCHAR, PrestaTerminee BOOLEAN, DiffusContexte VARCHAR)
RETURNS VOID
AS $$
BEGIN
INSERT INTO DIFFUSION (PrestationId, DiffusionContexte) VALUES (PrestaId, DiffusContexte) ;
INSERT INTO PRESTATION (PrestationId, PrestationTitre, PrestationTerminee) VALUES (PrestaId, PrestaTitre, PrestaTerminee) ;
END
$$
LANGUAGE PLPGSQL
;
START TRANSACTION ;
SELECT DELARITA_PRESTATION_DIFFUSION_INSERT (1, 'prestation a (diffusion)', TRUE, 'contexte a') ;
SELECT DELARITA_PRESTATION_DIFFUSION_INSERT (3, 'prestation c (diffusion)', TRUE, 'contexte c') ;
SELECT DELARITA_PRESTATION_DIFFUSION_INSERT (5, 'prestation e (diffusion)', FALSE, 'contexte e') ;
COMMIT ;
Ce qui vaut pour les diffusions vaut pour les productions :
CREATE OR REPLACE FUNCTION DELARITA_PRESTATION_PRODUCTION_INSERT(PrestaId INT, PrestaTitre VARCHAR, PrestaTerminee BOOLEAN, ProdJour INT, ProdUpdate INT)
RETURNS VOID
AS $$
BEGIN
INSERT INTO PRODUCTION (PrestationId, ProductionJour, ProductionUpdate) VALUES (PrestaId, ProdJour, ProdUpdate) ;
INSERT INTO PRESTATION (PrestationId, PrestationTitre, PrestationTerminee) VALUES (PrestaId, PrestaTitre, PrestaTerminee) ;
END
$$
LANGUAGE PLPGSQL
;
START TRANSACTION ;
SELECT DELARITA_PRESTATION_PRODUCTION_INSERT (2, 'prestation b (production)', TRUE, 20, 0) ;
SELECT DELARITA_PRESTATION_PRODUCTION_INSERT (4, 'prestation d (production)', TRUE, 40, 1) ;
SELECT DELARITA_PRESTATION_PRODUCTION_INSERT (6, 'prestation f (production)', FALSE,60, 0) ;
COMMIT ;
Venons-en aux contraintes de partitionnement. Comme on a pu le voir, en relationnel leur mise en oeuvre est simple. Cette fois-ci (à moins que PostgreSQL ne propose des techniques que je connais pas encore), il va falloir en passer par des triggers, chacun d’eux étant affecté à une table (PRESTATION, DIFFUSION, PRODUCTION) et il aussi falloir se poser les bonnes questions en relation avec les opérations effectuées (INSERT, UPDATE, DELETE)...
Cas de la table PRESTATION
— Si on effectue un INSERT directement dans cette table et que passe à l’as l’INSERT correspondant dans la table DIFFUSION ou dans la table PRODUCTION, il y aura viol de la règle de totalité.
— Si on effectue un UPDATE, si celui-ci change la valeur de la clé primaire, il faut rejeter l’opération, sinon on risque de s’embarquer dans une galère pas possible pour garantir la cohérence avec la table DIFFUSION ou PRODUCTION. Et puis, quand on a modélisé proprement, on n’a pas de raison d’avoir à modifier une clé primaire. Si l’UPDATE modifie autre chose que la clé primaire : on donne le feu vert.
— Si on effectue un DELETE, grâce à la clause ON DELETE CASCADE (cf. CREATE TABLE DIFFUSION et CREATE TABLE PRODUCTION), la prestation diffusion ou production) est totalement supprimée : la contrainte de partitionnement est respectée, donc on donne le feu vert.
Le trigger (et sa fonction) correspondant :
CREATE OR REPLACE FUNCTION DELARITA_PRESTATION()
RETURNS TRIGGER
AS $$
DECLARE Erreur VARCHAR ;
DECLARE theKount INTEGER ;
DECLARE thePrestation INTEGER ;
DECLARE thePrestationOld INTEGER ;
DECLARE theOperation VARCHAR ;
BEGIN
thePrestation = NEW.PrestationId ;
theOperation = UPPER(TG_OP) ;
IF theOperation = 'INSERT' THEN
theKount =
(SELECT COUNT(*)
FROM
(SELECT PrestationId
FROM DIFFUSION
WHERE PrestationId = thePrestation
UNION
SELECT PrestationId
FROM PRODUCTION
WHERE PrestationId = thePrestation
) as t) ;
IF theKount = 0
THEN
Erreur = 'Table ' || UPPER(TG_TABLE_NAME) || ' ; ' || 'Trigger ' || UPPER(TG_NAME)
|| '. Viol de la contrainte de totalité. Ajout impossible de la prestation de clé '''
|| thePrestation || ''', il manque une production ou une diffusion.' ;
RAISE EXCEPTION SQLSTATE '45003' USING MESSAGE = Erreur ;
END IF ;
RETURN NEW ;
ELSE
thePrestationOld = OLD.PrestationId ;
IF thePrestationOld = thePrestation THEN
RETURN NEW ;
ELSE
Erreur = 'Table ' || UPPER(TG_TABLE_NAME) || ' ; ' || 'Trigger ' || UPPER(TG_NAME)
|| '. Remplacement refusé de la clé primaire ''' || thePrestationOld || ''' par ''' || thePrestation || ''' (procéder par DELETE/INSERT de la prestation).'
;
RAISE EXCEPTION SQLSTATE '45003' USING MESSAGE = Erreur ;
END IF ;
END IF ;
END ;
$$
LANGUAGE PLPGSQL ;
CREATE TRIGGER DELARITA_PRESTATION AFTER INSERT OR UPDATE ON PRESTATION
FOR EACH ROW EXECUTE PROCEDURE DELARITA_PRESTATION() ;
Dès que je peux, je passe aux triggers affectés aux tables DIFFUSION et PRODUCTION.
Mais déjà, vous constaterez que pour une contrainte qui, en relationnel s’écrit en une ligne, sans avoir à se poser de question sur la nature des opérations, dès qu’on en passe par des triggers, alors il faut envisager toutes les situations, en espérant ne rien avoir oublié...
Une avancée
Répondre avec citation
Envoyé par fsmrel
Partager