Nwy5nmC8

1
2-- materialized view
3CREATE TABLE Report (
4	Specializzazione CHAR(50) NOT NULL,
5    NumeroNuoviPazienti INT NOT NULL,
6    MedicoMinorVisite CHAR(50) NOT NULL,
7    NumeroCitta INT NOT NULL,
8    PRIMARY KEY(Specializzazione)
9) ENGINE=InnoDB DEFAULT CHARSET=latin1;
10
11-- log table
12CREATE TABLE Report_LOG (
13	Istante TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
14    Medico CHAR(50) NOT NULL,
15    Specializzazione CHAR(50) NOT NULL,
16    Paziente CHAR(50) NOT NULL,
17    NewPaziente TINYINT(1) NOT NULL, -- 0 se non è nuovo, 1 se nuovo
18    PRIMARY KEY (Istante)
19) ENGINE=InnoDB DEFAULT CHARSET=latin1;
20
21DROP TRIGGER IF EXISTS push_log_Report;
22DELIMITER $$
23CREATE TRIGGER push_log_Report
24AFTER INSERT ON visita
25FOR EACH ROW
26BEGIN
27	DECLARE specialization CHAR(50);
28    DECLARE nuovo TINYINT DEFAULT 0;
29
30    SET specialization = ( 
31			SELECT	Specializzazione
32            FROM	medico
33            WHERE	Matricola = NEW.Medico
34	);
35
36    SET nuovo = (
37        -- controlla se il paziente è in una visita precedente della stessa specializzazione
38        IF (NEW.Paziente NOT IN (
39                                    SELECT  DISTINCT V.Paziente
40                                    FROM    visita V INNER JOIN medico M ON V.Medico = M.Matricola
41                                    WHERE   M.Specializzazione = NEW.Specializzazione
42                                            AND V.Data < CURRENT_DATE()
43                                ), 1, 0)
44        );
45
46    INSERT INTO Report_LOG
47		VALUES(CURRENT_TIMESTAMP, NEW.Medico, specialization, NEW.Paziente, nuovo);
48END $$
49
50DROP PROCEDURE IF EXISTS partial_refresh_Report $$
51
52CREATE PROCEDURE partial_refresh_Report (IN TimeSoglia TIMESTAMP)
53BEGIN
54
55    -- temp table per il numero di nuovi pazienti
56    DROP TEMPORARY TABLE IF EXISTS NumeroNuoviPazPerSpec;
57    CREATE TEMPORARY TABLE NumeroNuoviPazPerSpec;
58    INSERT INTO NumeroNuoviPazPerSpec
59        SELECT  RL.Specializzazione, COUNT(*) AS NumeroNuoviPazienti
60        FROM    Report_LOG RL
61        WHERE   RL.NewPaziente = 1
62                AND RL.Istante <= TimeSoglia
63        GROUP BY RL.Specializzazione;
64
65
66    -- prima temporary table per il medico minor visite
67    DROP TEMPORARY TABLE IF EXISTS NumeroVisiteMedico;
68    CREATE TEMPORARY TABLE NumeroVisiteMedico;
69    INSERT INTO NumeroVisiteMedico
70        SELECT  RL.Specializzazione, RL.Medico, COUNT(*) AS NumeroPrimeVisitePerMedico
71        FROM    Report_LOG RL
72        WHERE   RL.NewPaziente = 1
73                AND RL.Istante <= TimeSoglia
74        GROUP BY RL.Specializzazione, RL.Medico;
75        
76    -- seconda temporary table per il medico minor visite
77    DROP TEMPORARY TABLE IF EXISTS MedicoLessPerSpec;
78    CREATE TEMPORARY TABLE MedicoLessPerSpec;
79    INSERT INTO MedicoLessPerSpec
80        SELECT  NVM.Specializzazione, NVM.Medico AS MedicoMinorVisite
81        FROM    NumeroVisiteMedico NVM
82        WHERE   NVM.NumeroPrimeVisitePerMedico = ( -- non so come farla senza subquery
83                        SELECT  MIN(NVM2.NumeroPrimeVisitePerMedico)
84                        FROM    NumeroVisiteMedico NVM2
85                        )
86        GROUP BY
87                NVM.Specializzazione, NVM.Medico;
88
89    -- temp table per il numero di citta prime visite
90    DROP TEMPORARY TABLE IF EXISTS NumeroCittaPerSpec;
91    CREATE TEMPORARY TABLE NumeroCittaPerSpec;
92    INSERT INTO NumeroCittaPerSpec
93        SELECT  RL.Specializzazione, COUNT(DISTINCT P.Citta) AS NumeroCitta
94        FROM    Report_LOG RL
95                INNER JOIN
96                paziente P ON RL.Paziente = P.CodFiscale
97        WHERE   RL.NewPaziente = 1
98                AND RL.Istante <= TimeSoglia
99        GROUP BY RL.Specializzazione;
100    
101    REPLACE INTO Report
102        SELECT  NPAZ.Specializzazione, NPAZ.NumeroNuoviPazienti, MED.MedicoMinorVisite, CIT.NumeroCitta
103        FROM    NumeroNuoviPazPerSpec NPAZ
104                NATURAL JOIN
105                MedicoLessPerSpec MED
106                NATURAL JOIN
107                NumeroCittaPerSpec CIT
108        GROUP BY NPAZ.Specializzazione, MED.MedicoMinorVisite; -- perchè potrebbero esserci ex aequo
109
110    DELETE  RL.*
111    FROM    Report_LOG RL
112    WHERE   RL.Istante < TimeSoglia;
113    
114END $$