SET SCHEMA 'temp' ;
DROP FUNCTION IF EXISTS COMMENT_EXCLUSION_FN() CASCADE ;
DROP TABLE IF EXISTS COMMENT_MQ CASCADE ;
DROP TABLE IF EXISTS COMMENT_ASS ;
DROP TABLE IF EXISTS COMMENTAIRE CASCADE ;
DROP TABLE IF EXISTS METRIC_QUALITY ;
DROP TABLE IF EXISTS ASSIGNEMENT CASCADE ;
CREATE TABLE ASSIGNEMENT
(
assId SERIAL,
assCode CHAR(4) NOT NULL,
CONSTRAINT ASSIGNMENT_PK PRIMARY KEY(assId)
);
INSERT INTO ASSIGNEMENT (assCode)
VALUES
('a1'), ('a2'), ('a3'), ('a3') ;
CREATE TABLE METRIC_QUALITY
(
assId INTEGER,
mqDate TIMESTAMP,
mqClosed BOOLEAN NOT NULL,
mqResolved BOOLEAN NOT NULL,
CONSTRAINT METRIC_QUALITY_PK PRIMARY KEY(assId, mqDate),
CONSTRAINT METRIC_QUALITY_ASSIGNMENT_FK FOREIGN KEY(assId)
REFERENCES ASSIGNEMENT(assId)
);
INSERT INTO METRIC_QUALITY (assId, mqDate, mqClosed, mqResolved)
VALUES
((SELECT assid FROM ASSIGNEMENT WHERE assCode = 'a1'), '2020-01-01 00:01:00', FALSE, FALSE)
, ((SELECT assid FROM ASSIGNEMENT WHERE assCode = 'a1'), '2020-02-01 00:02:00', FALSE, FALSE)
, ((SELECT assid FROM ASSIGNEMENT WHERE assCode = 'a1'), '2020-03-01 00:03:00', FALSE, FALSE)
, ((SELECT assid FROM ASSIGNEMENT WHERE assCode = 'a2'), '2020-01-01 00:01:00', FALSE, FALSE)
, ((SELECT assid FROM ASSIGNEMENT WHERE assCode = 'a2'), '2020-02-01 00:02:00', FALSE, FALSE)
;
CREATE TABLE COMMENTAIRE
(
commentId SERIAL,
commentaire VARCHAR(96) NOT NULL,
CONSTRAINT COMMENTAIRE_PK PRIMARY KEY(commentId)
);
CREATE TABLE COMMENT_ASS
(
commentId INTEGER,
assId INTEGER NOT NULL,
CONSTRAINT COMMENT_ASS_PK PRIMARY KEY(commentId),
CONSTRAINT COMMENT_ASS_COMMENTAIRE_FK FOREIGN KEY(commentId)
REFERENCES COMMENTAIRE(commentId) ON DELETE CASCADE
, CONSTRAINT COMMENT_ASS_ASSIGNMENT_FK FOREIGN KEY(assId)
REFERENCES ASSIGNEMENT(assId)
);
CREATE TABLE COMMENT_MQ
(
commentId INTEGER,
assId INTEGER NOT NULL,
mqDate TIMESTAMP NOT NULL,
CONSTRAINT COMMENT_MQ_PK PRIMARY KEY(commentId),
CONSTRAINT COMMENT_MQ_COMMENTAIRE_FK FOREIGN KEY(commentId)
REFERENCES COMMENTAIRE(commentId) ON DELETE CASCADE
, CONSTRAINT COMMENT_MQ_METRIC_QUALITY_FK FOREIGN KEY(assId, mqDate)
REFERENCES METRIC_QUALITY(assId, mqDate)
);
CREATE FUNCTION COMMENT_EXCLUSION_FN()
RETURNS TRIGGER AS
$$
DECLARE n INT ;
DECLARE Erreur VARCHAR ;
DECLARE leCoupable VARCHAR ;
BEGIN
leCoupable =
(
SELECT commentaire
FROM COMMENT_ASS as x
JOIN COMMENT_MQ as y ON x.commentId = y.commentId
JOIN commentaire as z ON x.commentId = z.commentId
WHERE x.commentId = NEW.commentId
) ;
IF leCoupable IS NOT NULL
THEN
erreur = 'Le commentaire ''' || leCoupable || ''' ne peut pas être à la fois ASS et MQ' ;
RAISE EXCEPTION SQLSTATE '45002' USING MESSAGE = erreur ;
END IF ;
RETURN NEW ;
END
$$
LANGUAGE plpgsql ;
CREATE TRIGGER COMMENT_EXCLUSION_ASS_TR after INSERT OR UPDATE ON COMMENT_ASS
FOR EACH ROW EXECUTE PROCEDURE COMMENT_EXCLUSION_FN() ;
CREATE TRIGGER COMMENT_EXCLUSION_MQ_TR after INSERT OR UPDATE ON COMMENT_MQ
FOR EACH ROW EXECUTE PROCEDURE COMMENT_EXCLUSION_FN() ;
INSERT INTO COMMENTAIRE (commentaire)
VALUES
('comment 1')
, ('comment 2')
;
INSERT INTO COMMENT_ASS (commentId, assId)
VALUES
((SELECT commentId FROM COMMENTAIRE WHERE commentaire = 'comment 1')
, (SELECT assid FROM ASSIGNEMENT WHERE assCode = 'a1'))
;
INSERT INTO COMMENT_ASS (commentId, assId)
VALUES
((SELECT commentId FROM COMMENTAIRE WHERE commentaire = 'comment 2')
, (SELECT assid FROM ASSIGNEMENT WHERE assCode = 'a1'))
;
SELECT * FROM COMMENT_ASS ;
/* Exclusion des commentaires => ça doit planter ! */
INSERT INTO COMMENT_MQ (commentId, assId, mqDate)
VALUES
((SELECT commentId FROM COMMENTAIRE WHERE commentaire = 'comment 1')
, (SELECT assid FROM ASSIGNEMENT WHERE assCode = 'a1')
, '2020-01-01 00:01:00')
;
SELECT * FROM COMMENT_MQ ;
