· 5 years ago · May 12, 2020, 03:30 PM
1--create user tonino with password 'Pippo';
2
3DROP TABLE IF EXISTS Massaggio CASCADE;
4DROP TABLE IF EXISTS RecapitoMassaggiatore CASCADE;
5DROP TABLE IF EXISTS RecapitoCliente CASCADE;
6DROP TABLE IF EXISTS RecapitoReceptionist CASCADE;
7DROP TABLE IF EXISTS Specializzazione CASCADE;
8DROP TABLE IF EXISTS TipoMassaggio CASCADE;
9DROP TABLE IF EXISTS Cliente CASCADE;
10DROP TABLE IF EXISTS Sala CASCADE;
11DROP TABLE IF EXISTS Macchinario CASCADE;
12DROP TABLE IF EXISTS Receptionist CASCADE;
13DROP TABLE IF EXISTS Massaggiatore CASCADE;
14DROP TYPE IF EXISTS Orario;
15
16
17CREATE TABLE Cliente (
18 CodiceFiscale CHAR(16) PRIMARY KEY CHECK( CHAR_LENGTH(CodiceFiscale) = 16 ),
19 Cognome VARCHAR(20) NOT NULL,
20 Nome VARCHAR(20) NOT NULL
21);
22
23
24CREATE TABLE Sala(
25 NumeroSala VARCHAR(20) PRIMARY KEY,
26 NumeroLettini SMALLINT NOT NULL CHECK( NumeroLettini >= 0 )
27);
28
29
30CREATE TABLE Macchinario(
31 Tipo VARCHAR(20) PRIMARY KEY,
32 Quantita SMALLINT NOT NULL CHECK( Quantita >= 0 )
33);
34
35
36CREATE TABLE Receptionist(
37 CodiceFiscale CHAR(16) PRIMARY KEY CHECK( CHAR_LENGTH(CodiceFiscale) = 16 ),
38 Cognome VARCHAR(20) NOT NULL,
39 Nome VARCHAR(20) NOT NULL,
40 via VARCHAR(20) NOT NULL,
41 CAP CHAR(5) NOT NULL CHECK( CHAR_LENGTH(CAP) = 5 ),
42 Citta VARCHAR(20) NOT NULL,
43 Stipendio DECIMAL(8,2) NOT NULL
44);
45
46
47CREATE TABLE Massaggiatore(
48 CodiceFiscale CHAR(16) PRIMARY KEY CHECK( CHAR_LENGTH(CodiceFiscale) = 16 ),
49 Cognome VARCHAR(20) NOT NULL,
50 Nome VARCHAR(20) NOT NULL,
51 Via VARCHAR(20) NOT NULL,
52 CAP CHAR(5) NOT NULL CHECK( CHAR_LENGTH(CAP) = 5 ),
53 Citta VARCHAR(20) NOT NULL,
54 StipendioBase DECIMAL(8,2) NOT NULL
55);
56
57
58CREATE TABLE TipoMassaggio(
59 Tipo VARCHAR(20) PRIMARY KEY,
60 Prezzo DECIMAL(8,2) NOT NULL,
61 Durata SMALLINT NOT NULL,
62 Macchinario VARCHAR(20) NULL,
63
64 FOREIGN KEY (Macchinario) REFERENCES Macchinario(Tipo)
65 ON DELETE CASCADE
66 ON UPDATE CASCADE
67);
68
69
70CREATE TABLE Specializzazione(
71 Massaggiatore CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Massaggiatore) = 16 ),
72 TipoMassaggio VARCHAR(20) NOT NULL,
73
74 PRIMARY KEY (Massaggiatore, TipoMassaggio),
75 FOREIGN KEY (Massaggiatore) REFERENCES Massaggiatore(CodiceFiscale)
76 ON DELETE CASCADE
77 ON UPDATE CASCADE
78 DEFERRABLE INITIALLY DEFERRED,
79 FOREIGN KEY (TipoMassaggio) REFERENCES TipoMassaggio(Tipo)
80 ON DELETE CASCADE
81 ON UPDATE CASCADE
82);
83
84
85CREATE TABLE RecapitoCliente(
86 Cliente CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Cliente) = 16 ),
87 Telefono VARCHAR(13) NOT NULL,
88
89 PRIMARY KEY (Cliente, Telefono),
90 FOREIGN KEY (Cliente) REFERENCES Cliente(CodiceFiscale)
91 ON DELETE CASCADE
92 ON UPDATE CASCADE
93 DEFERRABLE INITIALLY DEFERRED
94);
95
96
97CREATE TABLE RecapitoMassaggiatore(
98 Massaggiatore CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Massaggiatore) = 16 ),
99 Telefono VARCHAR(13) NOT NULL,
100
101 PRIMARY KEY (Massaggiatore, Telefono),
102 FOREIGN KEY (Massaggiatore) REFERENCES Massaggiatore(CodiceFiscale)
103 ON DELETE CASCADE
104 ON UPDATE CASCADE
105 DEFERRABLE INITIALLY DEFERRED
106);
107
108
109CREATE TABLE RecapitoReceptionist(
110 Receptionist CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Receptionist) = 16 ),
111 Telefono VARCHAR(13) NOT NULL,
112
113 PRIMARY KEY (Receptionist, Telefono),
114 FOREIGN KEY (Receptionist) REFERENCES Receptionist(CodiceFiscale)
115 ON DELETE CASCADE
116 ON UPDATE CASCADE
117 DEFERRABLE INITIALLY DEFERRED
118);
119
120
121CREATE TABLE Massaggio(
122 Cliente CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Cliente) = 16 ),
123 DataMassaggio DATE NOT NULL CHECK( DataMassaggio > CURRENT_DATE OR DataMassaggio = CURRENT_DATE AND OraInizio > CURRENT_TIME ),
124 OraInizio TIME NOT NULL CHECK( OraInizio >= TIME '09:00:00' ),
125 OraFine TIME NOT NULL CHECK( OraFine <= TIME '21:00:00' ), -- Trigger per altro costraint
126 DataPrenotazione TIMESTAMP NOT NULL,
127 Massaggiatore CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Massaggiatore) = 16 ),
128 Sala VARCHAR(20) NOT NULL,
129 TipoMassaggio VARCHAR(20) NOT NULL,
130
131 PRIMARY KEY (Cliente, DataMassaggio, OraInizio),
132 FOREIGN KEY (Massaggiatore) REFERENCES Massaggiatore(CodiceFiscale)
133 ON UPDATE CASCADE,
134 FOREIGN KEY (Sala) REFERENCES Sala(NumeroSala)
135 ON UPDATE CASCADE,
136 FOREIGN KEY (TipoMassaggio) REFERENCES TipoMassaggio(Tipo)
137 ON DELETE CASCADE
138 ON UPDATE CASCADE
139);
140
141CREATE TYPE Orario AS (
142 Data DATE,
143 Ora TIME,
144 Tipo INT
145);
146
147CREATE OR REPLACE FUNCTION modifica_quantita() RETURNS TRIGGER AS $$
148 DECLARE
149 counter int;
150 r Orario%rowtype;
151 BEGIN
152 /*
153 DATAMASSAGGIO, ORAINIZIO, ORAFINE
154 DATA, ORA, TIPO
155 DATAMASSAGGIO, ORAINIZIO o ORAFINE, -1(ORAINIZIO) o +1(ORAFINE)
156 ORDINO PER DATA e se è uguale
157 ORDINO PER ORA
158 SE è un'ora di inizio sottraggo 1, altrimenti sommo 1
159 Se vado sotto 0 ritorno un'eccezione
160 */
161 IF NEW.quantita < OLD.quantita THEN
162 counter := NEW.quantita;
163 FOR r IN
164 SELECT datamassaggio "data", orainizio ora, tipo FROM (
165 SELECT DataMassaggio, OraInizio, -1 tipo FROM Massaggio
166 WHERE tipomassaggio IN (
167 SELECT tipo FROM tipomassaggio
168 WHERE tipomassaggio.macchinario = 'clgvqgssoql')
169 UNION
170 SELECT DataMassaggio, OraFine, 1 tipo FROM Massaggio
171 WHERE tipomassaggio IN (
172 SELECT tipo FROM tipomassaggio
173 WHERE tipomassaggio.macchinario = 'clgvqgssoql')
174 ) AS O
175 ORDER BY "data", ora
176 LOOP
177 counter := counter + r.Tipo;
178 IF counter < 0 THEN
179 RAISE EXCEPTION 'Operazione non valida perché ci sono troppi massaggi prenotati che utilizzano il macchinario.';
180 END IF;
181 END LOOP;
182 END IF;
183
184 RETURN NEW;
185 END;
186$$ LANGUAGE plpgsql;
187
188CREATE TRIGGER checkUpdateQuantitaMacchinario
189BEFORE UPDATE OF quantita ON macchinario
190FOR EACH ROW
191EXECUTE PROCEDURE modifica_quantita();
192
193CREATE OR REPLACE FUNCTION checkcodicefiscale() RETURNS TRIGGER AS $$
194 DECLARE
195 temp1 int;
196 temp2 int;
197 temp3 int;
198 temp4 int;
199 BEGIN
200 SELECT count(*) INTO temp1 FROM Cliente
201 WHERE codicefiscale = NEW.codicefiscale;
202 SELECT count(*) INTO temp2 FROM Massaggiatore
203 WHERE codicefiscale = new.codicefiscale;
204 SELECT count(*) INTO temp3 FROM Receptionist
205 WHERE codicefiscale = new.codicefiscale;
206 temp4 = temp1 + temp2 + temp3;
207 IF (temp4 > 0) THEN
208 RAISE EXCEPTION 'Codice Fiscale già presente';
209 END IF;
210 RETURN NEW;
211 END;
212$$ LANGUAGE plpgsql;
213
214CREATE TRIGGER checkInsertCodiceFiscaleCliente
215BEFORE INSERT OR UPDATE OF codicefiscale ON cliente
216FOR EACH ROW
217EXECUTE PROCEDURE checkcodicefiscale();
218
219CREATE TRIGGER checkInsertCodiceFiscaleMassaggiatore
220BEFORE INSERT OR UPDATE OF codicefiscale ON massaggiatore
221FOR EACH ROW
222EXECUTE PROCEDURE checkcodicefiscale();
223
224CREATE TRIGGER checkInsertCodiceFiscaleReceptionist
225BEFORE INSERT OR UPDATE OF codicefiscale ON receptionist
226FOR EACH ROW
227EXECUTE PROCEDURE checkcodicefiscale();
228
229-- Vista massaggi per tonino
230-- cliente, dataMassaggio, oraInizio, oraFine, tipoMassaggio
231CREATE OR REPLACE VIEW Prenotazione AS
232SELECT Cliente, DataMassaggio, OraInizio, TipoMassaggio
233FROM Massaggio;
234
235-- NEW = cliente, dataMassaggio, oraInizio, tipoMassaggio
236CREATE OR REPLACE FUNCTION prenota() RETURNS TRIGGER AS $$
237 DECLARE
238 durata int;
239 NewOraFine time;
240 MassaggiatoreLibero varchar;
241 MacchinarioRichiesto varchar;
242 SalaLibera varchar;
243 BEGIN
244 -- Selezione durata del tipoMassaggio
245 IF EXISTS(SELECT tipomassaggio.durata FROM tipomassaggio WHERE tipo = NEW.tipomassaggio) THEN
246 SELECT tipomassaggio.durata INTO durata FROM tipomassaggio WHERE tipo = NEW.tipomassaggio;
247 ELSE
248 RAISE EXCEPTION 'Il tipo massaggio indicato non è disponibile';
249 END IF;
250
251 -- Calcola l'ora di fine
252 NewOraFine := NEW.orainizio + interval '1m' * durata;
253
254
255 -- Verifica massaggiatori disponibili per tutta la durata
256
257 -- Seleziona tutti i massaggiatori che sanno fare il tipo massaggio richiesto
258 SELECT Risultato.massaggiatore INTO massaggiatoreLibero from (
259 SELECT S.massaggiatore FROM Specializzazione S WHERE S.tipomassaggio = NEW.tipomassaggio
260 EXCEPT
261 -- Seleziona tutti i massaggiatori che sono impegnati in quel giorno in quell intervallo orario
262 SELECT M.massaggiatore FROM Massaggio M
263 WHERE M.dataMassaggio = NEW.dataMassaggio AND
264 M.orafine > NEW.orainizio AND M.orainizio < NewOraFine) AS Risultato
265 ORDER BY random()
266 LIMIT 1;
267
268 IF NOT FOUND THEN
269 RAISE EXCEPTION 'Non c è nessun massaggiatore disponibile';
270 END IF;
271
272 -- Verifica se è richiesto il macchinario per quel tipo massaggio
273 SELECT T.macchinario INTO MacchinarioRichiesto FROM TipoMassaggio T
274 WHERE T.tipo = NEW.tipomassaggio;
275
276
277 IF MacchinarioRichiesto IS NOT NULL THEN
278 -- Verifica macchinari disponibili per tutta la durata
279 -- Prendi tutti i macchinari la cui quantità è maggiore delle volte che sono usati nell'intervallo orario desiderato
280 PERFORM macchinario.tipo FROM macchinario
281 WHERE macchinario.tipo = MacchinarioRichiesto
282 AND macchinario.quantita >
283 -- Le volte che lo stesso macchinario è usato
284 (select count(*) FROM massaggio M
285 -- Nel giorno scelto
286 WHERE M.datamassaggio = NEW.datamassaggio
287 -- È impegnato in quell'ora
288 AND M.orafine > NEW.orainizio AND M.orainizio < NewOraFine
289 -- Il macchinario utilizzato è quello richiesto
290 -- Il tipo massaggio prevede di utilizzare il macchinario richiesto
291 AND M.tipomassaggio IN (
292 SELECT tipo FROM tipomassaggio
293 WHERE tipomassaggio.macchinario = MacchinarioRichiesto)
294 );
295 IF NOT FOUND THEN
296 RAISE EXCEPTION 'Non c è nessun macchinario disponibile';
297 END IF;
298 END IF;
299
300 -- Verifica sale con lettini disponibili per tutta la durata
301 -- Prendi tutte le sale i cui lettini sono maggiori di quelli usati nell''intervallo orario desiderato
302 SELECT sala.numeroSala INTO SalaLibera FROM sala
303 WHERE sala.numeroLettini >
304 ( -- Sale usate nell'intervallo desiderato
305 SELECT count(*) FROM Massaggio M
306 WHERE M.datamassaggio = NEW.datamassaggio
307 AND M.orafine > NEW.orainizio AND M.orainizio < NewOraFine
308 AND M.Sala = sala.numeroSala
309 )
310 ORDER BY random()
311 LIMIT 1;
312
313 IF NOT FOUND THEN
314 RAISE EXCEPTION 'Non c è nessuna sala disponibile';
315 END IF;
316
317 -- Verifica cliente con unico massaggio in quell'intervallo
318 PERFORM M.cliente FROM massaggio M
319 WHERE M.cliente = NEW.cliente
320 AND M.datamassaggio = NEW.datamassaggio
321 AND M.orafine > NEW.orainizio AND M.orainizio < NewOraFine;
322
323 IF FOUND THEN
324 RAISE EXCEPTION 'Il cliente è già impegnato';
325 END IF;
326
327 IF TG_OP = 'INSERT' THEN
328 INSERT INTO Massaggio
329 VALUES(NEW.Cliente, NEW.DataMassaggio, NEW.OraInizio, NewOraFine, now(), massaggiatoreLibero, Salalibera, NEW.TipoMassaggio);
330 ELSE
331 UPDATE Massaggio SET
332 Cliente = NEW.Cliente,
333 DataMassaggio = NEW.DataMassaggio,
334 OraInizio = NEW.OraInizio,
335 OraFine = NewOraFine,
336 dataPrenotazione = now(),
337 massaggiatore = massaggiatoreLibero,
338 Sala = Salalibera,
339 TipoMassaggio = NEW.TipoMassaggio
340 WHERE
341 Cliente = OLD.Cliente AND
342 DataMassaggio = OLD.DataMassaggio AND
343 OraInizio = OLD.OraInizio AND
344 OraFine = OLD.OraFine;
345 END IF;
346 RETURN NEW;
347 END;
348$$ LANGUAGE plpgsql;
349
350CREATE TRIGGER richiesta_massaggio INSTEAD OF INSERT OR UPDATE
351ON prenotazione
352FOR EACH ROW
353EXECUTE PROCEDURE prenota();
354
355/*CREATE OR REPLACE FUNCTION cancella_prenotazione() RETURNS TRIGGER AS $$
356 DECLARE
357 durata int;
358 OldOraFine time;
359 BEGIN
360 RAISE NOTICE 'Il tipo massaggio indicato non trovato';
361 -- Selezione durata del tipoMassaggio
362 IF EXISTS(SELECT tipomassaggio.durata FROM tipomassaggio WHERE tipo = OLD.tipomassaggio) THEN
363 SELECT tipomassaggio.durata INTO durata FROM tipomassaggio WHERE tipo = OLD.tipomassaggio;
364 ELSE
365 RAISE NOTICE 'Il tipo massaggio indicato non trovato';
366 RETURN NULL;
367 END IF;
368
369 -- Calcola l'ora di fine
370 OldOraFine := OLD.orainizio + interval '1m' * durata;
371
372 DELETE FROM Massaggio
373 WHERE cliente = OLD.cliente AND
374 datamassaggio = OLD.datamassaggio AND
375 orainizio = OLD.orainizio AND
376 orafine = OldOraFine;
377
378 RETURN OLD;
379 END;
380$$ LANGUAGE plpgsql;
381
382CREATE TRIGGER cancellazione_massaggio INSTEAD OF DELETE
383ON prenotazione
384FOR EACH ROW
385EXECUTE PROCEDURE cancella_prenotazione();*/
386
387grant all privileges on Prenotazione to tonino;
388grant all privileges on Massaggio to tonino;
389grant all privileges on RecapitoMassaggiatore to tonino;
390grant all privileges on RecapitoCliente to tonino;
391grant all privileges on RecapitoReceptionist to tonino;
392grant all privileges on Specializzazione to tonino;
393grant all privileges on TipoMassaggio to tonino;
394grant all privileges on Cliente to tonino;
395grant all privileges on Sala to tonino;
396grant all privileges on Macchinario to tonino;
397grant all privileges on Receptionist to tonino;
398grant all privileges on Massaggiatore to tonino;