-- Procedura che genera sempre un'eccezione

DROP PROCEDURE IF EXISTS eccezione;

DELIMITER $$

CREATE PROCEDURE eccezione ()
BEGIN 
	DECLARE c1 CHAR(16);
	DECLARE crs CURSOR FOR SELECT * FROM mysql.user WHERE 0=1;
	OPEN crs;
	FETCH crs INTO c1;
END $$

DELIMITER ;


-- Funzione che verifica se il voto di laurea soddisfa il vincolo secondo il quale 
-- deve essere compreso tra 70 e 110

DROP FUNCTION IF EXISTS `VotoLaurea`;

DELIMITER $$

CREATE FUNCTION `VotoLaurea`(votoLaurea int(3)) RETURNS tinyint(1)
BEGIN
	DECLARE result bool;
	SET result = 1;
	IF votoLaurea < 70 OR votoLaurea > 110 THEN SET result = 0;
		ELSE SET result = 1;
	END IF;
	RETURN result;
END $$

DELIMITER ;

-- Funzione che verifica se tutti gli esami previsti dal piano di studi di uno
-- studente che discute la tesi sono stati superati con profitto

DROP FUNCTION IF EXISTS PianoDiStudiCompletato;

DELIMITER $$
-- condizione che impone che il corso sia tra quelli previsti dal piano 
-- di studio dello studente che discute la relazione finale*/
-- condizione che impone che il corso non sia tra quelli superati con successo  
-- dallo studente che discute la relazione finale*/

CREATE FUNCTION PianoDiStudiCompletato(RelazioneFinale INT(15)) RETURNS bool
BEGIN
	DECLARE esamiPrevistiNonSuperati INT(10);
	SELECT count(*) INTO esamiPrevistiNonSuperati 
	FROM prevede
	WHERE prevede.pianoDiStudio=RelazioneFinale 
	AND prevede.corso IN (SELECT corso FROM prevede WHERE prevede.pianoDiStudio=RelazioneFinale) 
	AND prevede.corso NOT IN (SELECT corso FROM esame WHERE esame.studente=RelazioneFinale AND esame.voto>18);
	IF esamiPrevistiNonSuperati=0 THEN RETURN TRUE; ELSE RETURN FALSE; END IF;
END $$

DELIMITER ;

-- Trigger che blocca gli inserimenti in discussione che porterebbero alla violazioni dei vincoli
-- secondo i quali un voto di laurea deve essere compreso tra 70 e 110, e uno studente che
-- discute deve aver superato con successo tutti gli esami previsti dal suo piano di studi

DROP TRIGGER IF EXISTS trig_ins_discussione;

DELIMITER $$

CREATE  TRIGGER trig_ins_discussione 
BEFORE INSERT ON discussione 
FOR EACH ROW 
BEGIN
	IF @DIS_TRIGGER IS NULL AND (VotoLaurea(NEW.voto)=FALSE OR PianoDiStudiCompletato(NEW.relazioneFinale)=FALSE) THEN CALL eccezione();
	END IF;
END $$

DELIMITER ;

-- Procedura che verifica se un piano di studio compare nella tabella prevede (vincolo di inclusione)

DROP PROCEDURE IF EXISTS proc_incl_pianodistudio_prevede;

DELIMITER $$

CREATE PROCEDURE proc_incl_pianodistudio_prevede(IN ps int(15), OUT isPresent bool)
BEGIN
SELECT count(*) INTO isPresent FROM prevede WHERE prevede.pianoDiStudio = ps;
END $$

DELIMITER ;

-- Trigger che blocca l'inserimento di un piano di studi che violerebbe il vincolo di inclusione
-- secondo il quale ogni piano di studi deve comparire nella tabella prevede

DROP TRIGGER IF EXISTS trig_ins_prevede;

DELIMITER $$

CREATE  TRIGGER trig_ins_prevede 
BEFORE INSERT ON pianodistudio 
FOR EACH ROW 
BEGIN
	DECLARE soddisfatto bool;
	IF @DIS_TRIGGER IS NULL THEN 
	BEGIN CALL proc_incl_pianodistudio_prevede(NEW.studente,soddisfatto); IF soddisfatto=FALSE THEN CALL eccezione(); END IF; END;
	END IF;
END $$

DELIMITER ;


-- Transazione per permettere il popolamento consistente di studente e pianodistudio
-- Funziona solo se si disabilita (o cambia) il trigger che blocca l'inserimento su pianodistudio
-- per implementare il vincolo di inclusione tra pianodistudio e prevede

DROP PROCEDURE IF EXISTS trans_insert_studente_pianodistudio;

DELIMITER $$

CREATE PROCEDURE trans_insert_studente_pianodistudio (s INT(15), t VARCHAR(50))
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET foreign_key_checks=1; SET AUTOCOMMIT=1; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; END;
	SET AUTOCOMMIT = 0;
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
	SET foreign_key_checks=0;
	INSERT INTO studente VALUES (s);
	INSERT INTO pianodistudio VALUES (s,t);
	SET foreign_key_checks=1;
	COMMIT;
	SET AUTOCOMMIT = 1;	
	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END $$

DELIMITER ;

-- Transazione per permettere il popolamento consistente di studente e pianodistudio
-- Funziona solo se si disabilita (o cambia) il trigger che blocca l'inserimento su pianodistudio
-- per implementare il vincolo di inclusione tra pianodistudio e prevede

DROP PROCEDURE IF EXISTS trans_insert_studente_pianodistudio;

DELIMITER $$

CREATE PROCEDURE trans_insert_studente_pianodistudio (s INT(15), t VARCHAR(50))
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET foreign_key_checks=1; SET AUTOCOMMIT=1; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; END;
	SET AUTOCOMMIT = 0;
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
	SET foreign_key_checks=0;
	INSERT INTO studente VALUES (s);
	INSERT INTO pianodistudio VALUES (s,t);
	SET foreign_key_checks=1;
	COMMIT;
	SET AUTOCOMMIT = 1;	
	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END $$

DELIMITER ;

-- Transazione per permettere il popolamento consistente di argomento e relazionefinale
--

DROP PROCEDURE IF EXISTS trans_insert_argomento_relazionefinale;

DELIMITER $$

CREATE PROCEDURE trans_insert_argomento_relazionefinale (r INT(15), c VARCHAR(50), p INT(4))
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET foreign_key_checks=1; SET AUTOCOMMIT=1; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; END;
	SET AUTOCOMMIT = 0;
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
	SET foreign_key_checks=0;
	INSERT INTO argomento VALUES (r,c);
	INSERT INTO relazionefinale VALUES (r,p);
	SET foreign_key_checks=1;
	COMMIT;
	SET AUTOCOMMIT = 1;	
	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END $$

DELIMITER ;

-- Funzione che verifica se uno studente è presente nella tabella pianodistudio
-- Procedura che verifica se un piano di studio compare nella tabella prevede (vincolo di inclusione)

DROP FUNCTION IF EXISTS pianoStudioPresente;

DELIMITER $$

CREATE FUNCTION pianoStudioPresente(s int(15)) RETURNS bool
BEGIN
	DECLARE isPresent bool;
	SELECT count(*) INTO isPresent FROM pianodistudio WHERE pianodiStudio.studente=s;
	RETURN (isPresent=TRUE);
END $$

DELIMITER ;

-- Trigger che a fronte dell'inserimento di uno studente che violerebbe il vincolo di foreign key
-- secondo il quale ogni studente deve comparire nella tabella pianodistudio inserisce una tupla
-- appropriata nella tabella pianodistudio, assegnando il valore 'PLM' al campo tipo (soluzione 2).
-- Funziona solo se si disabilita (o cambia) il trigger che blocca l'inserimento su pianodistudio
-- per implementare il vincolo di inclusione tra pianodistudio e prevede
-- N.B. Per effettuare l'inserimento, non possiamo usare la procedura 
-- trans_insert_studente_pianodistudio perché non è consentito l'uso dell'istruzione SET AUCOMMIT 
-- all'interno di un trigger

DROP TRIGGER IF EXISTS trig_ins_studente;

DELIMITER $$

CREATE TRIGGER trig_ins_studente 
BEFORE INSERT ON studente 
FOR EACH ROW 
BEGIN
	IF @DIS_TRIGGER IS NULL THEN 
	BEGIN IF pianoStudioPresente(NEW.matricola)=FALSE THEN BEGIN SET foreign_key_checks=0; INSERT INTO pianodistudio VALUES (NEW.matricola,'PLM'); SET foreign_key_checks=1; END; END IF; END;
	END IF;
END $$

DELIMITER ;


-- Transazione per permettere il popolamento consistente di pianodistudio e prevede

DROP PROCEDURE IF EXISTS trans_insert_pianodistudio_prevede;

DELIMITER $$

CREATE PROCEDURE trans_insert_pianodistudio_prevede (ps INT(15), t VARCHAR(50), c VARCHAR(50))
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET @DIS_TRIGGER=NULL; SET AUTOCOMMIT=1; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; END;
	SET AUTOCOMMIT=0;
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
	SET @DIS_TRIGGER=1;
	INSERT INTO pianodistudio VALUES(ps,t);
	INSERT INTO prevede VALUES(n);
	SET @DIS_TRIGGER=NULL;
	COMMIT;
	SET AUTOCOMMIT=1;
	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END $$

DELIMITER ;