-- ==============================================================================
-- Soluzione esercitazione 3: vincoli procedurali e gestione dei vincoli ciclici
-- ==============================================================================

-- Procedure che generano sempre un'eccezione

DROP PROCEDURE IF EXISTS eccezione;

DELIMITER $$

CREATE PROCEDURE eccezione ()
BEGIN
	UPDATE eccezione SET X=1;
END $$

DELIMITER ;

--
--

DROP PROCEDURE IF EXISTS eccezione2;

DELIMITER $$

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

DELIMITER ;

-- ==================================================================================
-- TASK 3
-- 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 ;

-- TASK 5
-- 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 ;


-- TASK 4 e 6
-- 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 ;

-- ==================================================================================
-- TASK 7
-- Procedura che verifica se un sessione di laurea compare nella tabella discussione (vincolo di inclusione)

DROP PROCEDURE IF EXISTS proc_incl_sessione_discussione;

DELIMITER $$

CREATE PROCEDURE proc_incl_sessione_discussione(IN d DATE, OUT isPresent bool)
BEGIN
SELECT count(*) INTO isPresent FROM discussione WHERE discussione.relazioneFinale = d;
END $$

DELIMITER ;

-- ==================================================================================
-- TASK 8
-- Trigger che blocca l'inserimento di un piano di studi se questo viola il vincolo di inclusione
-- secondo il quale ogni piano di studi deve comparire nella tabella prevede

DROP TRIGGER IF EXISTS trig_ins_sessione;

DELIMITER $$

CREATE  TRIGGER trig_ins_sessione
BEFORE INSERT ON sessionedilaurea
FOR EACH ROW
BEGIN
	DECLARE soddisfatto bool;
	IF @DIS_TRIGGER IS NULL THEN
	BEGIN CALL proc_incl_sessione_discussione(NEW.`data`,soddisfatto); IF soddisfatto=FALSE THEN CALL eccezione();
  END IF; END;
	END IF;
END $$

DELIMITER ;

-- ==============================================================================================
-- GESTIONE VINCOLI CICLICI
-- ==============================================================================================

-- TASK 9

-- 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;
      CALL eccezione();
    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 ;


-- Procedura che permette il popolamento consistente in Sessione di Laurea e Discussione
DROP PROCEDURE IF EXISTS trans_insert_sessionelaurea_discussione;

DELIMITER $$

CREATE PROCEDURE trans_insert_sessionelaurea_discussione (d DATE, rf INT(50), v INT(50))
BEGIN
	DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
    ROLLBACK;
    SET @DIS_TRIGGER=NULL;
    SET AUTOCOMMIT=1;
    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
    CALL eccezione();
    END;
  SET AUTOCOMMIT=0;
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
	SET @DIS_TRIGGER=1;
	INSERT INTO sessionediLaurea values (d);
	INSERT INTO discussione VALUES(rf,d,v);
	SET @DIS_TRIGGER=NULL;
	COMMIT;
	SET AUTOCOMMIT=1;
	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
END $$

DELIMITER ;

-- =================================================================================================
-- FINE
-- =================================================================================================