Bonsoir jejerem et Philippe,
Envoyé par
jejerem
Je m'excuse d'avance si mes questions paraissent un peu bêtes.
On vous excuse d’office car vos questions sont pertinentes, et puis il vaut mieux paraître bête pendant 5 minutes que rester idiot toute sa vie (proverbe chinois) ^^
Envoyé par
jejerem
j'ai aussi DBMain
Concernant DB-MAIN, vous pouvez vous référer à ce que j’ai décrit ici concernant la façon de mettre en oeuvre l’héritage : j’y fais figurer les spécialisations CLIENT et FOURNISSEUR de l’entité-type TIERS.
Envoyé par
jejerem
J'utilise PostgreSQL
Ça tombe bien, voici le code SQL que j’avais généré pour quelques uns de mes tests :
CREATE TABLE SERVICE
(
ServiceId SERIAL NOT NULL
, ServiceCode VARCHAR(8) NOT NULL
, ServiceLibelle VARCHAR(32) NOT NULL
, CONSTRAINT SERVICE_PK PRIMARY KEY (ServiceId)
, CONSTRAINT SERVICE_CODE_AK UNIQUE (ServiceCode)
);
CREATE TABLE PERSONNE
(
PersonneId SERIAL NOT NULL
, PersonneCode CHAR(8) NOT NULL
, AdresseCourriel VARCHAR(64) NOT NULL
, CONSTRAINT PERSONNE_PK PRIMARY KEY (PersonneId)
, CONSTRAINT PERSONNE_CODE_AK UNIQUE (PersonneCode)
, CONSTRAINT PERSONNE_COURRIEL_AK UNIQUE (AdresseCourriel)
);
CREATE TABLE ADRESSE
(
PersonneId INT NOT NULL
, AdresseId SERIAL NOT NULL
, AdresseLigne2 VARCHAR(64) NOT NULL
, AdresseLigne3 VARCHAR(64) NOT NULL DEFAULT ''
, AdresseLigne4 VARCHAR(64) NOT NULL DEFAULT ''
, AdresseCodePostal VARCHAR(6) NOT NULL
, CONSTRAINT ADRESSE_PK PRIMARY KEY (PersonneId, AdresseId)
, CONSTRAINT ADRESSE_PERSONNE_FK FOREIGN KEY (PersonneId)
REFERENCES PERSONNE (PersonneId) ON DELETE CASCADE
);
CREATE TABLE TIERS
(
PersonneId INT NOT NULL
, NoSiret CHAR(14) NOT NULL
, RaisonSociale VARCHAR(64) NOT NULL
, CONSTRAINT TIERS_PK PRIMARY KEY (PersonneId)
, CONSTRAINT TIERS_SIRET_AK UNIQUE (NoSiret)
, CONSTRAINT TIERS_PERSONNE_FK FOREIGN KEY (PersonneId)
REFERENCES PERSONNE (PersonneId) ON DELETE CASCADE
);
CREATE TABLE COLLABORATEUR
(
PersonneId INT NOT NULL
, ServiceId INT NOT NULL
, Matricule CHAR(8) NOT NULL
, NIR CHAR(13) NOT NULL
, Nom VARCHAR(32) NOT NULL
, Prenom VARCHAR(32) NOT NULL
, DateEmbauche DATE NOT NULL
, Salaire INT NOT NULL
, CONSTRAINT COLLABORATEUR_PK PRIMARY KEY (PersonneId)
, CONSTRAINT COLLABORATEUR_MATRICULE_AK UNIQUE (Matricule)
, CONSTRAINT COLLABORATEUR_NIR_AK UNIQUE (NIR)
, CONSTRAINT COLLABORATEUR_SERVICE_FK FOREIGN KEY (ServiceId)
REFERENCES SERVICE (ServiceId) ON DELETE NO ACTION
, CONSTRAINT COLLABORATEUR_PERSONNE_FK FOREIGN KEY (PersonneId)
REFERENCES PERSONNE (PersonneId) ON DELETE CASCADE
);
CREATE TABLE DIRECTEUR
(
PersonneId INT NOT NULL
, PrimeBilan INT NOT NULL
, CONSTRAINT DIRECTEUR_PK PRIMARY KEY (PersonneId)
, CONSTRAINT DIRECTEUR_COLLABORATEUR_FK FOREIGN KEY (PersonneId)
REFERENCES COLLABORATEUR (PersonneId) ON DELETE CASCADE
);
CREATE TABLE EMPLOYE
(
PersonneId INT NOT NULL
, Profil VARCHAR(128) NOT NULL
, CONSTRAINT EMPLOYE_PK PRIMARY KEY (PersonneId)
, CONSTRAINT EMPLOYE_COLLABORATEUR_FK FOREIGN KEY (PersonneId)
REFERENCES COLLABORATEUR (PersonneId) ON DELETE CASCADE
);
Pour simplifier les SELECT, on peut créer des vues par type d’utilisateur. Par exemple, pour les tiers :
CREATE VIEW TIERS_V (PersonneCode, AdresseCourriel, NoSiret, RaisonSociale)
AS
SELECT PersonneCode, AdresseCourriel, NoSiret, RaisonSociale
FROM PERSONNE AS x JOIN TIERS AS y ON x.PersonneId = y.PersonneId
;
Dans mon message auquel je vous avais renvoyé, JMerise a généré un code SQL correct, mais les actions de compensation y sont absentes, c'est-à-dire le déclenchement automatique de la suppression en cascade, ce dont j’ai parlé dans le message qui suit :
Envoyé par
fsmrel
Commentaires sur le script SQL :
Pour les entités-types spécialisées, à savoir COLLABORATEUR, TIERS, EMPLOYE, DIRECTEUR, vous pouvez générer l’action de compensation ON DELETE CASCADE (clause FOREIGN KEY). En effet, si on supprime la personne Raoul, on le supprime forcément à tous les étages, par définition les spécialisations n’ont pas à s’opposer aux stimuli qui leur parviennent.
Vous noterez que l’action de compensation ON DELETE CASCADE est bien présente dans le code que j’utilise.
Pour illustrer cela, créons maintenant de façon basique le tiers 'T0000001' :
INSERT INTO PERSONNE (PersonneCode, AdresseCourriel) VALUES ('T0000001', 'dépannage.naudin@abc.fr') ;
INSERT INTO TIERS (PersonneId, NoSiret, RaisonSociale)
SELECT PersonneId, '12345678901234', 'Naudin dépannage' FROM PERSONNE WHERE PersonneCode = 'T0000001' ;
Pour retrouver ce tiers :
SELECT * FROM TIERS_V WHERE PersonneCode = 'T0000001' ;
Pour le supprimer :
DELETE FROM PERSONNE WHERE PersonneCode = 'T0000001' ;
La suppression de ce tiers dans la table PERSONNE a entraîné sa suppression automatique dans la table TIERS. Ceci est la conséquence de l’action de compensation ON DELETE CASCADE prévue dans la déclaration de la table TIERS (CREATE TABLE) ; ainsi il est inutile de procéder en deux temps, en commençant par un DELETE dans la table TIERS puis un DELETE dans la table PERSONNE.
INSERT par le biais de la vue TIERS_V :
La vue TIERS_V est une « vue de jointure ». La norme SQL prévoit que l’on puisse effectuer un insert dans ce genre de vue, mais PostgreSQL (version 10) ne le permet pas : pour pallier, on met en oeuvre un trigger ad-hoc, lequel ventile les données dans les tables (mais pas façon puzzle ^^) :
CREATE FUNCTION TIERS_VUE_INSERT_FN()
RETURNS TRIGGER
AS
$$
BEGIN
INSERT INTO PERSONNE (PersonneCode, AdresseCourriel)
VALUES (NEW.PersonneCode, NEW.AdresseCourriel) ;
INSERT INTO TIERS (PersonneId, NoSiret, RaisonSociale)
SELECT MAX(PersonneId), NEW.NoSiret, NEW.RaisonSociale FROM PERSONNE ;
RETURN NULL ; -- on n'a rien à renvoyer
END
$$ LANGUAGE plpgsql ;
CREATE TRIGGER TIERS_VUE_INSERT_TR INSTEAD OF INSERT ON TIERS_V
FOR EACH ROW EXECUTE PROCEDURE TIERS_VUE_INSERT_FN() ;
Reprenons les deux inserts précédents :
INSERT INTO PERSONNE (PersonneCode, AdresseCourriel) VALUES ('T0000001', 'dépannage.naudin@abc.fr') ;
INSERT INTO TIERS (PersonneId, NoSiret, RaisonSociale)
SELECT PersonneId, '12345678901234', 'Naudin dépannage' FROM PERSONNE WHERE PersonneCode = 'T0000001' ;
Ces inserts peuvent maintenant être remplacés par celui-ci :
INSERT INTO TIERS_V (PersonneCode, AdresseCourriel, NoSiret, RaisonSociale)
VALUES ('T0000001', 'dépannage.naudin@abc.fr', '12345678901234', 'Naudin dépannage') ;
Même principe pour EMPLOYE qui hérite de COLLABORATEUR qui hérite de PERSONNE :
CREATE VIEW EMPLOYE_V (PersonneCode, AdresseCourriel, ServiceCode, Matricule, NIR, Nom, Prenom, DateEmbauche, Salaire, Profil)
AS
SELECT PersonneCode, AdresseCourriel, ServiceCode, Matricule, NIR, Nom, Prenom, DateEmbauche, Salaire, Profil
FROM PERSONNE AS x JOIN COLLABORATEUR AS y ON x.PersonneId = y.PersonneId
JOIN SERVICE AS z ON y.ServiceId = z.ServiceId
JOIN EMPLOYE AS t ON y.PersonneId = t.PersonneId
;
CREATE FUNCTION EMPLOYE_VUE_INSERT_FN()
RETURNS TRIGGER
AS
$$
BEGIN
INSERT INTO PERSONNE (PersonneCode, AdresseCourriel)
VALUES (NEW.PersonneCode, NEW.AdresseCourriel) ;
INSERT INTO COLLABORATEUR (PersonneId, ServiceId, Matricule, NIR, Nom, Prenom, DateEmbauche, Salaire)
SELECT MAX(PersonneId),(SELECT ServiceId FROM SERVICE WHERE ServiceCode = NEW.ServiceCode)
, NEW.Matricule, NEW.NIR, NEW.Nom, NEW.Prenom, NEW.DateEmbauche, NEW.Salaire
FROM PERSONNE ;
INSERT INTO EMPLOYE (PersonneId, Profil)
SELECT MAX(PersonneId), NEW.Profil
FROM PERSONNE ;
RETURN NULL ;
END
$$
LANGUAGE plpgsql ;
CREATE TRIGGER EMPLOYE_VUE_INSERT_TR INSTEAD OF INSERT ON EMPLOYE_V
FOR EACH ROW EXECUTE PROCEDURE EMPLOYE_VUE_INSERT_FN() ;
Pour créer l’employé 'P0000001', au choix :
Soit :
INSERT INTO PERSONNE (PersonneCode, AdresseCourriel) VALUES ('P0000001', 'pascal@citron.fr') ;
INSERT INTO COLLABORATEUR (PersonneId, ServiceId, Matricule, NIR, Nom, Prenom, DateEmbauche, Salaire)
SELECT PersonneId, (SELECT ServiceId FROM SERVICE WHERE ServiceCode = 'DSI'), 'MAT00056', '1300450012003', '', 'Pascal', '1963-12-05', 6000
FROM PERSONNE WHERE PersonneCode = 'P0000001' ;
INSERT INTO EMPLOYE (PersonneId, Profil)
SELECT PersonneId, 'Première gâchette' FROM PERSONNE WHERE PersonneCode = 'P0000001' ;
Soit :
INSERT INTO EMPLOYE_V (PersonneCode, AdresseCourriel, ServiceCode, Matricule, NIR, Nom, Prenom, DateEmbauche, Salaire, Profil)
VALUES ('P0000001', 'pascal@citron.fr', 'DSI', 'MAT00056', '1300450012003', '', 'Pascal', '1963-12-05', 6000, 'Première gâchette') ;
Pour supprimer l’employé 'P0000001' :
DELETE FROM PERSONNE WHERE PersonneCode = 'P0000001' ;
Envoyé par
jejerem
la doc indique que le support de l'héritage est limité. Il y a t'il un moyen pour contourner ce problème (niveau MCD ou SQL, jointures ou autres) ?
Le MCD que je vous avais présenté et ceux auxquels je vous renvoie ne sont pas concernés par les problèmes d’héritage, il y a tout ce qu’il faut. Quant à SQL, les scripts ci-dessus montrent qu’on traite sans problème de l’héritage tant en ce qui concerne les consultations que les mises à jour. Quant au support natif par PostgreSQL, je n’ai jamais testé, mais je vais essayer de regarder ça.
Partager