9kEcZRfp

1/* Con cadenza imprevedibile, la direzione della clinica è interessata a conoscere, per ciascuna specializzazione, il
2numero di nuovi pazienti visitati, il medico che effettua il numero minore di prime visite, e il numero di città
3diverse dalle quali provengono i pazienti visitati per la prima volta. Si desidera creare uno snapshot REPORT aggiornato mediante partial refresh con tecnica on demand. Pertanto, si richiede: i) la creazione dello snapshot; ii)
4la creazione della log table e il codice di gestione della stessa; iii) una o più stored procedure per implementare
5il partial refresh. Le stored procedure non devono fare uso di subquery né view. 
6*/
7
8/*********** CREAZIONE MATERIALIZED VIEW ***********/
9CREATE TABLE Report (
10	Specializzazione VARCHAR(50) NOT NULL,
11    NewPazienti INTEGER DEFAULT 0,
12    MedicoInf VARCHAR(50) DEFAULT '',
13    NumCittaDiverse INTEGER DEFAULT 0,
14	PRIMARY KEY (Specializzazione)
15) ENGINE = InnoDB CHARSET = latin1;
16
17/******** CREAZIONE LOG TABLE ************/
18CREATE TABLE Report_Log (
19	Istante TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
20    Medico VARCHAR(50) NOT NULL,
21    Paziente VARCHAR(50) NOT NULL,
22    Data DATE NOT NULL,
23    PRIMARY KEY (Istante)
24) ENGINE = InnoDB CHARSET = latin1;
25
26/******* TRIGGER DI PUSH *************/
27DELIMITER $$
28DROP TRIGGER IF EXISTS pushVisita;
29CREATE TRIGGER pushVisita
30AFTER INSERT ON Visita
31BEGIN 
32	INSERT INTO Report_Log
33    VALUES (CURRENT_TIMESTAMP, NEW.Medico, NEW.Paziente, NEW.Data)
34END $$
35
36/* Controllo se il paziente è un nuovo paziente*/
37DROP PROCEDURE IF EXISTS checkPaziente $$
38CREATE PROCEDURE checkPaziente ( IN _specializzazione VARCHAR(50),
39								 IN _soglia DATE,
40								 OUT numPazienti_ INTEGER,
41                                 OUT ncitta_ INTEGER
42                                )
43BEGIN
44		SELECT COUNT(*) AS NewPaz,
45			   COUNT(DISTINCT P.Citta) INTO numPazienti_, ncitta_
46        FROM Report_Log RL
47			 LEFT OUTER JOIN 
48             Visita V ON ( RL.Paziente = V.Paziente
49						   AND RL.Medico = V.Medico
50                           AND V.Data < RL.Data
51						 )
52			INNER JOIN
53            Paziente P ON P.CodFiscale = RL.Paziente
54			 WHERE V.Paziente IS NULL 	-- se non joina vuol dire che non ci sono visite precedenti
55				AND RL.Specializzazione = _spec   
56                AND RL.Data <= _soglia;
57END $$
58
59/* Medico che effettua il numero minore di prime visite */
60DROP PROCEDURE IF EXISTS MedicoMin;
61CREATE PROCEDURE MedicoMin ( IN _specializzazione VARCHAR(50),
62							 IN _soglia DATE,
63							 OUT medico_ VARCHAR(50)
64						   )
65BEGIN
66		-- variabili
67			DECLARE medico VARCHAR(50) DEFAULT '';
68            DECLARE visite INTEGER DEFAULT 0;
69            DECLARE finito INTEGER DEFAULT 0;
70            DECLARE flag INTEGER DEFAULT 0;
71        
72        -- Cursore
73        DECLARE primeVisite CURSOR FOR
74			SELECT RL.Medico, IFNULL(RL.Medico, 0, COUNT(*)) AS NumVisite
75            FROM Medico M
76				 LEFT OUTER JOIN
77                 Report_Log RL ON RL.Medico = M.Matricola
78			WHERE M.Specializzazione = _spec
79				AND RL.Data <= _soglia
80			GROUP BY RL.Medico;
81		
82        -- Handler
83        DECLARE CONTINUE HANDLER FOR NOT FOUND SET finito = 0;
84        
85        OPEN primeVisite;
86        scan: LOOP
87			FETCH primeVisite INTO medico, visite;
88            
89            IF finito = 1 THEN
90				LEAVE scan;
91			END IF;
92            
93            IF (flag = 0 OR visite <= minimo) THEN
94			SET minimo = visite;
95            SET medicoMin = medico;
96		END IF;
97        
98        SET flag = flag+1;    
99    END LOOP scan;
100	CLOSE primeVisite;
101    SET medico_ = medicoMin;       
102END $$
103
104/************ REFRESH ****************/
105DROP PROCEDURE IF EXISTS partial_refresh $$
106CREATE PROCEDURE partial_refresh( IN _soglia DATE)
107BEGIN
108	DECLARE specializzazione VARCHAR(100) DEFAULT '';
109    DECLARE numPrimeVisite INTEGER DEFAULT 0;
110    DECLARE numCittaDiverse INTEGER DEFAULT 0;
111    DECLARE MedicoMinorVisite VARCHAR(100) DEFAULT '';
112    DECLARE finito INTEGER DEFAULT 0;
113        
114    -- Cursore
115    DECLARE specializzazioni CURSOR FOR
116		SELECT DISTINCT M.Specializzazione
117        FROM Medico M;
118        
119	-- Handler
120	DECLARE CONTINUE HANDLER FOR NOT FOUND SET finito = 1;
121
122
123	OPEN specializzazioni;
124    scan: LOOP
125		FETCH specializzazioni INTO specializzazione;
126        
127        IF finito = 1 THEN
128			LEAVE scan;
129		END IF;
130        
131        CALL checkPaziente (specializzazione, soglia, numPrimeVisite, numCittaDiverse);
132        CALL medicoMin (specializzazione, soglia, MedicoMinorVisite);
133        
134        REPLACE INTO Report
135		VALUES (specializzazione, numPrimeVisite, MedicoMinorVisite, numCittaDiverse);
136		
137        DELETE FROM Report_Log
138        WHERE Data <= _soglia;
139        
140	END LOOP scan;    
141    CLOSE specializzazioni;
142        
143END $$
144DELIMITER ;