· 5 years ago · May 13, 2020, 12:34 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;
14
15
16CREATE TABLE Cliente (
17 CodiceFiscale CHAR(16) PRIMARY KEY CHECK( CHAR_LENGTH(CodiceFiscale) = 16 ),
18 Cognome VARCHAR(20) NOT NULL,
19 Nome VARCHAR(20) NOT NULL
20);
21
22
23CREATE TABLE Sala(
24 NumeroSala VARCHAR(20) PRIMARY KEY,
25 NumeroLettini SMALLINT NOT NULL CHECK( NumeroLettini >= 0 )
26);
27
28
29CREATE TABLE Macchinario(
30 Tipo VARCHAR(20) PRIMARY KEY,
31 Quantita SMALLINT NOT NULL CHECK( Quantita >= 0 )
32);
33
34
35CREATE TABLE Receptionist(
36 CodiceFiscale CHAR(16) PRIMARY KEY CHECK( CHAR_LENGTH(CodiceFiscale) = 16 ),
37 Cognome VARCHAR(20) NOT NULL,
38 Nome VARCHAR(20) NOT NULL,
39 via VARCHAR(20) NOT NULL,
40 CAP CHAR(5) NOT NULL CHECK( CHAR_LENGTH(CAP) = 5 ),
41 Citta VARCHAR(20) NOT NULL,
42 Stipendio DECIMAL(8,2) NOT NULL
43);
44
45
46CREATE TABLE Massaggiatore(
47 CodiceFiscale CHAR(16) PRIMARY KEY CHECK( CHAR_LENGTH(CodiceFiscale) = 16 ),
48 Cognome VARCHAR(20) NOT NULL,
49 Nome VARCHAR(20) NOT NULL,
50 Via VARCHAR(20) NOT NULL,
51 CAP CHAR(5) NOT NULL CHECK( CHAR_LENGTH(CAP) = 5 ),
52 Citta VARCHAR(20) NOT NULL,
53 StipendioBase DECIMAL(8,2) NOT NULL
54);
55
56
57CREATE TABLE TipoMassaggio(
58 Tipo VARCHAR(20) PRIMARY KEY,
59 Prezzo DECIMAL(8,2) NOT NULL,
60 Durata SMALLINT NOT NULL,
61 Macchinario VARCHAR(20) NULL,
62
63 FOREIGN KEY (Macchinario) REFERENCES Macchinario(Tipo)
64 ON DELETE CASCADE
65 ON UPDATE CASCADE
66);
67
68
69CREATE TABLE Specializzazione(
70 Massaggiatore CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Massaggiatore) = 16 ),
71 TipoMassaggio VARCHAR(20) NOT NULL,
72
73 PRIMARY KEY (Massaggiatore, TipoMassaggio),
74 FOREIGN KEY (Massaggiatore) REFERENCES Massaggiatore(CodiceFiscale)
75 ON DELETE CASCADE
76 ON UPDATE CASCADE
77 DEFERRABLE INITIALLY DEFERRED,
78 FOREIGN KEY (TipoMassaggio) REFERENCES TipoMassaggio(Tipo)
79 ON DELETE CASCADE
80 ON UPDATE CASCADE
81);
82
83
84CREATE TABLE RecapitoCliente(
85 Cliente CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Cliente) = 16 ),
86 Telefono VARCHAR(13) NOT NULL,
87
88 PRIMARY KEY (Cliente, Telefono),
89 FOREIGN KEY (Cliente) REFERENCES Cliente(CodiceFiscale)
90 ON DELETE CASCADE
91 ON UPDATE CASCADE
92 DEFERRABLE INITIALLY DEFERRED
93);
94
95
96CREATE TABLE RecapitoMassaggiatore(
97 Massaggiatore CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Massaggiatore) = 16 ),
98 Telefono VARCHAR(13) NOT NULL,
99
100 PRIMARY KEY (Massaggiatore, Telefono),
101 FOREIGN KEY (Massaggiatore) REFERENCES Massaggiatore(CodiceFiscale)
102 ON DELETE CASCADE
103 ON UPDATE CASCADE
104 DEFERRABLE INITIALLY DEFERRED
105);
106
107
108CREATE TABLE RecapitoReceptionist(
109 Receptionist CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Receptionist) = 16 ),
110 Telefono VARCHAR(13) NOT NULL,
111
112 PRIMARY KEY (Receptionist, Telefono),
113 FOREIGN KEY (Receptionist) REFERENCES Receptionist(CodiceFiscale)
114 ON DELETE CASCADE
115 ON UPDATE CASCADE
116 DEFERRABLE INITIALLY DEFERRED
117);
118
119
120CREATE TABLE Massaggio(
121 Cliente CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Cliente) = 16 ),
122 DataMassaggio DATE NOT NULL CHECK( DataMassaggio > CURRENT_DATE OR DataMassaggio = CURRENT_DATE AND OraInizio > CURRENT_TIME ),
123 OraInizio TIME NOT NULL CHECK( OraInizio >= TIME '09:00:00' ),
124 OraFine TIME NOT NULL CHECK( OraFine <= TIME '21:00:00' ), -- Trigger per altro costraint
125 DataPrenotazione TIMESTAMP NOT NULL,
126 Massaggiatore CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Massaggiatore) = 16 ),
127 Sala VARCHAR(20) NOT NULL,
128 TipoMassaggio VARCHAR(20) NOT NULL,
129
130 PRIMARY KEY (Cliente, DataMassaggio, OraInizio),
131 FOREIGN KEY (Massaggiatore) REFERENCES Massaggiatore(CodiceFiscale)
132 ON DELETE CASCADE
133 ON UPDATE CASCADE,
134 FOREIGN KEY (Sala) REFERENCES Sala(NumeroSala)
135 ON DELETE CASCADE
136 ON UPDATE CASCADE,
137 FOREIGN KEY (TipoMassaggio) REFERENCES TipoMassaggio(Tipo)
138 ON DELETE CASCADE
139 ON UPDATE CASCADE
140);
141
142-- Vista massaggi per tonino
143-- cliente, dataMassaggio, oraInizio, tipoMassaggio
144CREATE OR REPLACE VIEW Prenotazione AS
145SELECT Cliente, DataMassaggio, OraInizio, TipoMassaggio
146FROM Massaggio;
147
148CREATE OR REPLACE FUNCTION inserimento_persona() RETURNS TRIGGER AS $$
149 BEGIN
150 IF TG_TABLE_NAME = 'cliente' THEN
151 IF (SELECT count(*) FROM recapitoCliente R where NEW.CodiceFiscale = R.Cliente) = 0 THEN
152 RAISE EXCEPTION 'Impossibile eseguire l operazione, il cliente deve avere almeno un recapito telefonico';
153 END IF;
154
155 ELSIF TG_TABLE_NAME = 'massaggiatore' THEN
156 IF (SELECT count(*) FROM recapitoMassaggiatore R where NEW.CodiceFiscale = R.Massaggiatore) = 0 THEN
157 RAISE EXCEPTION 'Impossibile eseguire l operazione, il massaggiatore deve avere almeno un recapito telefonico';
158 END IF;
159 IF (SELECT count(*) FROM specializzazione S where NEW.CodiceFiscale = S.Massaggiatore) = 0 THEN
160 RAISE EXCEPTION 'Impossibile eseguire l operazione, il massaggiatore deve avere almeno una specializzazione';
161 END IF;
162
163 ELSE
164 IF (SELECT count(*) FROM recapitoReceptionist R WHERE NEW.CodiceFiscale = R.Receptionist) = 0 THEN
165 RAISE EXCEPTION 'Impossibile eseguire l operazione, il receptionist deve avere almeno un recapito telefonico';
166 END IF;
167
168 END IF;
169 RETURN NEW;
170 END;
171$$ LANGUAGE plpgsql;
172
173CREATE TRIGGER inserisci_cliente
174BEFORE INSERT ON cliente
175FOR EACH ROW
176EXECUTE PROCEDURE inserimento_persona();
177
178CREATE TRIGGER inserisci_massaggiatore
179BEFORE INSERT ON massaggiatore
180FOR EACH ROW
181EXECUTE PROCEDURE inserimento_persona();
182
183CREATE TRIGGER inserisci_receptionist
184BEFORE INSERT ON receptionist
185FOR EACH ROW
186EXECUTE PROCEDURE inserimento_persona();
187
188CREATE OR REPLACE FUNCTION modifica_recapiti() RETURNS TRIGGER AS $$
189 BEGIN
190 IF TG_TABLE_NAME = 'recapitocliente' THEN
191 IF (SELECT count(*) FROM recapitoCliente R where OLD.Cliente = R.Cliente) = 1 THEN
192 RAISE EXCEPTION 'Impossibile eseguire l operazione, il cliente deve avere almeno un recapito telefonico';
193 END IF;
194
195 ELSIF TG_TABLE_NAME = 'recapitomassaggiatore' THEN
196 IF (SELECT count(*) FROM recapitoMassaggiatore R where OLD.Massaggiatore = R.Massaggiatore) = 1 THEN
197 RAISE EXCEPTION 'Impossibile eseguire l operazione, il massaggiatore deve avere almeno un recapito telefonico';
198 END IF;
199
200 ELSE
201 IF (SELECT count(*) FROM recapitoReceptionist R WHERE OLD.Receptionist = R.Receptionist) = 1 THEN
202 RAISE EXCEPTION 'Impossibile eseguire l operazione, il receptionist deve avere almeno un recapito telefonico';
203 END IF;
204
205 END IF;
206 IF TG_OP = 'DELETE' THEN
207 RETURN OLD;
208 ELSE
209 RETURN NEW;
210 END IF;
211 END;
212$$ LANGUAGE plpgsql;
213
214CREATE TRIGGER modifica_recapito_cliente
215BEFORE UPDATE OF cliente OR DELETE ON recapitocliente
216FOR EACH ROW
217EXECUTE PROCEDURE modifica_recapiti();
218
219CREATE TRIGGER modifica_recapito_massaggiatore
220BEFORE UPDATE OF massaggiatore OR DELETE ON recapitomassaggiatore
221FOR EACH ROW
222EXECUTE PROCEDURE modifica_recapiti();
223
224CREATE TRIGGER modifica_recapito_receptionist
225BEFORE UPDATE OF receptionist OR DELETE ON recapitoreceptionist
226FOR EACH ROW
227EXECUTE PROCEDURE modifica_recapiti();
228
229CREATE OR REPLACE FUNCTION modifica_durata_tipomassaggio() RETURNS TRIGGER AS $$
230 BEGIN
231 IF NEW.durata > OLD.durata THEN
232 IF NEW.Tipo IN (
233 SELECT TipoMassaggio FROM Massaggio WHERE
234 DataMassaggio > current_date OR
235 DataMassaggio = current_date AND
236 OraInizio > current_time)
237 THEN
238 RAISE EXCEPTION 'Impossibile aumentare la durata dei massaggi perchè
239sono stati prenotati altri massaggi per quel tipo di massaggio';
240 END IF;
241 ELSE
242 UPDATE Massaggio SET OraFine = OraInizio + interval '1m' * NEW.durata
243 WHERE TipoMassaggio = NEW.Tipo AND (
244 DataMassaggio > current_date OR
245 DataMassaggio = current_date AND
246 OraInizio > current_time);
247 END IF;
248 RETURN NEW;
249 END;
250$$ LANGUAGE plpgsql;
251
252CREATE TRIGGER modifica_durata_massaggio
253BEFORE UPDATE OF durata ON tipomassaggio
254FOR EACH ROW
255EXECUTE PROCEDURE modifica_durata_tipomassaggio();
256
257CREATE OR REPLACE FUNCTION modifica_specializzazione() RETURNS TRIGGER AS $$
258 DECLARE
259 r massaggio%rowtype;
260 MassaggiatoreLibero varchar;
261 BEGIN
262 IF (SELECT count(*) FROM specializzazione S where OLD.massaggiatore = S.massaggiatore) = 0 THEN
263 RAISE EXCEPTION 'Impossibile eseguire l operazione, il massaggiatore deve avere almeno una specializzazione';
264 END IF;
265
266 FOR r IN
267 SELECT * FROM Massaggio
268 WHERE Massaggiatore = OLD.massaggiatore
269 AND TipoMassaggio = OLD.TipoMassaggio AND (
270 datamassaggio > current_date
271 OR datamassaggio = current_date
272 AND oraInizio > current_time)
273 LOOP
274 SELECT Massaggiatore INTO MassaggiatoreLibero FROM (
275 SELECT S.Massaggiatore FROM Specializzazione S
276 WHERE S.TipoMassaggio = r.TipoMassaggio
277 EXCEPT
278 SELECT M.Massaggiatore FROM Massaggio M
279 WHERE M.datamassaggio = r.datamassaggio
280 AND M.oraFine>r.oraInizio AND M.oraInizio<r.oraFine ) AS foo
281 ORDER BY random() LIMIT 1;
282
283 RAISE NOTICE 'MassaggiatoreLibero: %', MassaggiatoreLibero;
284 IF FOUND THEN
285 UPDATE Massaggio SET Massaggiatore = MassaggiatoreLibero
286 WHERE DataMassaggio = r.DataMassaggio AND
287 OraInizio = r.OraInizio AND
288 Cliente = r.Cliente;
289 ELSE
290 RAISE NOTICE 'Non è stato possibile sostituire il massaggiatore. La prenotazione del cliente % per il giorno % alle ore %
291sarà cancellata', r.cliente, r.datamassaggio, r.orainizio;
292 DELETE FROM Massaggio M
293 WHERE M.DataMassaggio = r.DataMassaggio
294 AND M.OraInizio = r.OraInizio
295 AND M.Cliente = r.Cliente;
296 END IF;
297 END LOOP;
298 IF TG_OP = 'DELETE' THEN
299 RETURN OLD;
300 ELSE
301 RETURN NEW;
302 END IF;
303 END;
304$$ LANGUAGE plpgsql;
305
306CREATE TRIGGER modifica_specializzazione
307AFTER UPDATE OR DELETE ON specializzazione
308FOR EACH ROW
309EXECUTE PROCEDURE modifica_specializzazione();
310
311CREATE OR REPLACE FUNCTION cancellazione_massaggiatore() RETURNS TRIGGER AS $$
312 DECLARE
313 r massaggio%rowtype;
314 MassaggiatoreLibero varchar;
315 BEGIN
316
317 FOR r IN
318 SELECT * FROM Massaggio
319 WHERE Massaggiatore = OLD.codicefiscale
320 LOOP
321 SELECT Massaggiatore INTO MassaggiatoreLibero FROM (
322 SELECT S.Massaggiatore FROM Specializzazione S
323 WHERE S.Massaggiatore <> OLD.codicefiscale AND S.TipoMassaggio = r.TipoMassaggio
324 EXCEPT
325 SELECT M.Massaggiatore FROM Massaggio M
326 WHERE M.datamassaggio = r.datamassaggio
327 AND M.oraFine>r.oraInizio AND M.oraInizio<r.oraFine ) AS foo
328 ORDER BY random() LIMIT 1;
329
330 IF FOUND THEN
331 UPDATE Massaggio SET Massaggiatore = MassaggiatoreLibero
332 WHERE DataMassaggio = r.DataMassaggio AND
333 OraInizio = r.OraInizio AND
334 Cliente = r.Cliente;
335 ELSE
336 RAISE NOTICE 'Non è stato possibile sostituire il massaggiatore. La prenotazione del cliente % per il giorno % alle ore %
337sarà cancellata', r.cliente, r.datamassaggio, r.orainizio;
338 END IF;
339 END LOOP;
340
341 RETURN OLD;
342 END;
343$$ LANGUAGE plpgsql;
344
345CREATE TRIGGER cancella_massaggiatore BEFORE DELETE
346ON massaggiatore
347FOR EACH ROW
348EXECUTE PROCEDURE cancellazione_massaggiatore();
349
350CREATE OR REPLACE FUNCTION cancellazione_sala() RETURNS TRIGGER AS $$
351 DECLARE
352 r massaggio%rowtype;
353 SalaLibera varchar;
354 BEGIN
355
356 FOR r IN
357 SELECT * FROM Massaggio
358 WHERE Sala = OLD.numerosala
359 LOOP
360 SELECT S.NumeroSala INTO SalaLibera FROM Sala S
361 WHERE S.NumeroSala <> OLD.numerosala
362 AND (S.numeroSala NOT IN (SELECT DISTINCT sala FROM Massaggio)
363 OR S.NumeroLettini > (
364 SELECT count(*) FROM Massaggio M
365 WHERE M.datamassaggio = r.datamassaggio
366 AND M.orafine > r.orainizio AND M.orainizio < r.oraFine
367 AND M.Sala = S.numeroSala))
368 ORDER BY random() LIMIT 1;
369
370 IF FOUND THEN
371 UPDATE Massaggio SET Sala = SalaLibera
372 WHERE DataMassaggio = r.DataMassaggio AND
373 OraInizio = r.OraInizio AND
374 Cliente = r.Cliente;
375 ELSE
376 RAISE NOTICE 'Non è stato possibile sostituire la sala. La prenotazione del cliente % per il giorno % alle ore %
377sarà cancellata', r.cliente, r.datamassaggio, r.orainizio;
378 END IF;
379 END LOOP;
380
381 RETURN OLD;
382 END;
383$$ LANGUAGE plpgsql;
384
385CREATE TRIGGER cancella_sala BEFORE DELETE
386ON sala
387FOR EACH ROW
388EXECUTE PROCEDURE cancellazione_sala();
389
390CREATE OR REPLACE FUNCTION modifica_quantita_lettini() RETURNS TRIGGER AS $$
391 DECLARE
392 counter int;
393 r RECORD;
394 t RECORD;
395 SalaLibera varchar;
396 BEGIN
397 IF NEW.numerolettini < OLD.numerolettini THEN
398 EXECUTE format(
399 'CREATE TABLE IF NOT EXISTS NUOVA_TABELLA (
400 cliente CHAR(16),
401 "data" DATE,
402 orainizio TIME,
403 orafine TIME,
404 PRIMARY KEY(cliente, data, orainizio, orafine)
405 );'
406 );
407 counter := NEW.numerolettini;
408 FOR r IN
409 SELECT cliente, datamassaggio "data", OraInizio AS Ora, OI AS Orainizio, OraFine, tipo FROM (
410 SELECT Cliente, DataMassaggio, OraInizio, OraInizio AS OI, OraFine, -1 tipo FROM Massaggio
411 WHERE Sala = NEW.NumeroSala
412 UNION
413 SELECT Cliente, DataMassaggio, OraFine, OraInizio, OraFine, 1 tipo FROM Massaggio
414 WHERE Sala = NEW.NumeroSala
415 ) AS O
416 ORDER BY "data", Ora
417 LOOP
418 IF r.Tipo = -1 THEN
419 INSERT INTO NUOVA_TABELLA VALUES (r.cliente, r.data, r.orainizio, r.orafine);
420 ELSE
421 DELETE FROM NUOVA_TABELLA N WHERE N.cliente = r.cliente
422 AND N.data = r.data
423 AND N.orainizio = r.oraInizio
424 AND N.orafine = r.oraFine;
425 END IF;
426
427 counter := counter + r.Tipo;
428 IF counter < 0 THEN
429 FOR t IN SELECT * FROM NUOVA_TABELLA
430 LOOP
431 SELECT S.NumeroSala INTO SalaLibera FROM Sala S
432 WHERE S.numeroSala <> NEW.numerosala AND
433 S.NumeroLettini > (
434 SELECT count(*) FROM Massaggio M
435 WHERE M.datamassaggio = t.data
436 AND M.orafine > t.orainizio AND M.orainizio < t.oraFine
437 AND M.Sala = S.numeroSala)
438 ORDER BY random() LIMIT 1;
439
440 IF FOUND THEN
441 UPDATE Massaggio SET Sala = SalaLibera
442 WHERE DataMassaggio = t.Data AND
443 OraInizio = t.OraInizio AND
444 Cliente = t.Cliente;
445 DELETE FROM NUOVA_TABELLA N WHERE N.cliente = r.cliente
446 AND N.data = r.data
447 AND N.orainizio = r.oraInizio
448 AND N.orafine = r.oraFine;
449 EXIT;
450 END IF;
451 END LOOP;
452 IF SalaLibera IS NULL THEN
453 EXECUTE format(
454 'DROP TABLE IF EXISTS NUOVA_TABELLA;'
455 );
456
457 RAISE EXCEPTION 'Operazione non valida perché ci sono troppi massaggi prenotati collocati in questa sala.';
458 END IF;
459 END IF;
460 END LOOP;
461 END IF;
462 EXECUTE format(
463 'DROP TABLE IF EXISTS NUOVA_TABELLA;'
464 );
465 RETURN NEW;
466 END;
467$$ LANGUAGE plpgsql;
468
469CREATE TRIGGER checkUpdateQuantitaLettini
470BEFORE UPDATE OF numerolettini ON sala
471FOR EACH ROW
472EXECUTE PROCEDURE modifica_quantita_lettini();
473
474CREATE OR REPLACE FUNCTION modifica_quantita_macchinari() RETURNS TRIGGER AS $$
475 DECLARE
476 counter int;
477 r RECORD;
478 BEGIN
479 /*
480 DATAMASSAGGIO, ORAINIZIO, ORAFINE
481 DATA, ORA, TIPO
482 DATAMASSAGGIO, ORAINIZIO o ORAFINE, -1(ORAINIZIO) o +1(ORAFINE)
483 ORDINO PER DATA e se è uguale
484 ORDINO PER ORA
485 SE è un'ora di inizio sottraggo 1, altrimenti sommo 1
486 Se vado sotto 0 ritorno un'eccezione
487 */
488 IF NEW.quantita < OLD.quantita THEN
489 counter := NEW.quantita;
490 FOR r IN
491 SELECT datamassaggio "data", orainizio ora, tipo FROM (
492 SELECT DataMassaggio, OraInizio, -1 tipo FROM Massaggio
493 WHERE tipomassaggio IN (
494 SELECT tipo FROM tipomassaggio
495 WHERE tipomassaggio.macchinario = NEW.tipo)
496 UNION
497 SELECT DataMassaggio, OraFine, 1 tipo FROM Massaggio
498 WHERE tipomassaggio IN (
499 SELECT tipo FROM tipomassaggio
500 WHERE tipomassaggio.macchinario = NEW.tipo)
501 ) AS O
502 ORDER BY "data", ora
503 LOOP
504 counter := counter + r.Tipo;
505 IF counter < 0 THEN
506 RAISE EXCEPTION 'Operazione non valida perché ci sono troppi massaggi prenotati che utilizzano il macchinario.';
507 END IF;
508 END LOOP;
509 END IF;
510
511 RETURN NEW;
512 END;
513$$ LANGUAGE plpgsql;
514
515CREATE TRIGGER checkUpdateQuantitaMacchinario
516BEFORE UPDATE OF quantita ON macchinario
517FOR EACH ROW
518EXECUTE PROCEDURE modifica_quantita_macchinari();
519
520CREATE OR REPLACE FUNCTION checkcodicefiscale() RETURNS TRIGGER AS $$
521 DECLARE
522 temp1 int;
523 temp2 int;
524 temp3 int;
525 temp4 int;
526 BEGIN
527 SELECT count(*) INTO temp1 FROM Cliente
528 WHERE codicefiscale = NEW.codicefiscale;
529 SELECT count(*) INTO temp2 FROM Massaggiatore
530 WHERE codicefiscale = new.codicefiscale;
531 SELECT count(*) INTO temp3 FROM Receptionist
532 WHERE codicefiscale = new.codicefiscale;
533 temp4 = temp1 + temp2 + temp3;
534 IF (temp4 > 0) THEN
535 RAISE EXCEPTION 'Codice Fiscale già presente';
536 END IF;
537 RETURN NEW;
538 END;
539$$ LANGUAGE plpgsql;
540
541CREATE TRIGGER checkInsertCodiceFiscaleCliente
542BEFORE INSERT OR UPDATE OF codicefiscale ON cliente
543FOR EACH ROW
544EXECUTE PROCEDURE checkcodicefiscale();
545
546CREATE TRIGGER checkInsertCodiceFiscaleMassaggiatore
547BEFORE INSERT OR UPDATE OF codicefiscale ON massaggiatore
548FOR EACH ROW
549EXECUTE PROCEDURE checkcodicefiscale();
550
551CREATE TRIGGER checkInsertCodiceFiscaleReceptionist
552BEFORE INSERT OR UPDATE OF codicefiscale ON receptionist
553FOR EACH ROW
554EXECUTE PROCEDURE checkcodicefiscale();
555
556-- NEW = cliente, dataMassaggio, oraInizio, tipoMassaggio
557CREATE OR REPLACE FUNCTION prenota() RETURNS TRIGGER AS $$
558 DECLARE
559 durata int;
560 NewOraFine time;
561 MassaggiatoreLibero varchar;
562 MacchinarioRichiesto varchar;
563 SalaLibera varchar;
564 BEGIN
565 -- Selezione durata del tipoMassaggio
566 IF EXISTS(SELECT tipomassaggio.durata FROM tipomassaggio WHERE tipo = NEW.tipomassaggio) THEN
567 SELECT tipomassaggio.durata INTO durata FROM tipomassaggio WHERE tipo = NEW.tipomassaggio;
568 ELSE
569 RAISE EXCEPTION 'Il tipo massaggio indicato non è disponibile';
570 END IF;
571
572 -- Calcola l'ora di fine
573 NewOraFine := NEW.orainizio + interval '1m' * durata;
574
575
576 -- Verifica massaggiatori disponibili per tutta la durata
577
578 -- Seleziona tutti i massaggiatori che sanno fare il tipo massaggio richiesto
579 SELECT Risultato.massaggiatore INTO massaggiatoreLibero from (
580 SELECT S.massaggiatore FROM Specializzazione S WHERE S.tipomassaggio = NEW.tipomassaggio
581 EXCEPT
582 -- Seleziona tutti i massaggiatori che sono impegnati in quel giorno in quell intervallo orario
583 SELECT M.massaggiatore FROM Massaggio M
584 WHERE M.dataMassaggio = NEW.dataMassaggio AND
585 M.orafine > NEW.orainizio AND M.orainizio < NewOraFine) AS Risultato
586 ORDER BY random()
587 LIMIT 1;
588
589 IF NOT FOUND THEN
590 RAISE EXCEPTION 'Non c è nessun massaggiatore disponibile';
591 END IF;
592
593 -- Verifica se è richiesto il macchinario per quel tipo massaggio
594 SELECT T.macchinario INTO MacchinarioRichiesto FROM TipoMassaggio T
595 WHERE T.tipo = NEW.tipomassaggio;
596
597
598 IF MacchinarioRichiesto IS NOT NULL THEN
599 -- Verifica macchinari disponibili per tutta la durata
600 -- Prendi tutti i macchinari la cui quantità è maggiore delle volte che sono usati nell'intervallo orario desiderato
601 PERFORM macchinario.tipo FROM macchinario
602 WHERE macchinario.tipo = MacchinarioRichiesto
603 AND macchinario.quantita >
604 -- Le volte che lo stesso macchinario è usato
605 (select count(*) FROM massaggio M
606 -- Nel giorno scelto
607 WHERE M.datamassaggio = NEW.datamassaggio
608 -- È impegnato in quell'ora
609 AND M.orafine > NEW.orainizio AND M.orainizio < NewOraFine
610 -- Il macchinario utilizzato è quello richiesto
611 -- Il tipo massaggio prevede di utilizzare il macchinario richiesto
612 AND M.tipomassaggio IN (
613 SELECT tipo FROM tipomassaggio
614 WHERE tipomassaggio.macchinario = MacchinarioRichiesto)
615 );
616 IF NOT FOUND THEN
617 RAISE EXCEPTION 'Non c è nessun macchinario disponibile';
618 END IF;
619 END IF;
620
621 -- Verifica sale con lettini disponibili per tutta la durata
622 -- Prendi tutte le sale i cui lettini sono maggiori di quelli usati nell''intervallo orario desiderato
623 SELECT sala.numeroSala INTO SalaLibera FROM sala
624 WHERE sala.numeroLettini >
625 ( -- Sale usate nell'intervallo desiderato
626 SELECT count(*) FROM Massaggio M
627 WHERE M.datamassaggio = NEW.datamassaggio
628 AND M.orafine > NEW.orainizio AND M.orainizio < NewOraFine
629 AND M.Sala = sala.numeroSala
630 )
631 ORDER BY random()
632 LIMIT 1;
633
634 IF NOT FOUND THEN
635 RAISE EXCEPTION 'Non c è nessuna sala disponibile';
636 END IF;
637
638 -- Verifica cliente con unico massaggio in quell'intervallo
639 PERFORM M.cliente FROM massaggio M
640 WHERE M.cliente = NEW.cliente
641 AND M.datamassaggio = NEW.datamassaggio
642 AND M.orafine > NEW.orainizio AND M.orainizio < NewOraFine;
643
644 IF FOUND THEN
645 RAISE EXCEPTION 'Il cliente è già impegnato';
646 END IF;
647
648 IF TG_OP = 'INSERT' THEN
649 INSERT INTO Massaggio
650 VALUES(NEW.Cliente, NEW.DataMassaggio, NEW.OraInizio, NewOraFine, now(), massaggiatoreLibero, Salalibera, NEW.TipoMassaggio);
651 ELSE
652 UPDATE Massaggio SET
653 Cliente = NEW.Cliente,
654 DataMassaggio = NEW.DataMassaggio,
655 OraInizio = NEW.OraInizio,
656 OraFine = NewOraFine,
657 dataPrenotazione = now(),
658 massaggiatore = massaggiatoreLibero,
659 Sala = Salalibera,
660 TipoMassaggio = NEW.TipoMassaggio
661 WHERE
662 Cliente = OLD.Cliente AND
663 DataMassaggio = OLD.DataMassaggio AND
664 OraInizio = OLD.OraInizio AND
665 OraFine = OLD.OraFine;
666 END IF;
667 RETURN NEW;
668 END;
669$$ LANGUAGE plpgsql;
670
671CREATE TRIGGER richiesta_massaggio INSTEAD OF INSERT OR UPDATE
672ON prenotazione
673FOR EACH ROW
674EXECUTE PROCEDURE prenota();
675
676grant all privileges on Prenotazione to tonino;
677grant all privileges on Massaggio to tonino;
678grant all privileges on RecapitoMassaggiatore to tonino;
679grant all privileges on RecapitoCliente to tonino;
680grant all privileges on RecapitoReceptionist to tonino;
681grant all privileges on Specializzazione to tonino;
682grant all privileges on TipoMassaggio to tonino;
683grant all privileges on Cliente to tonino;
684grant all privileges on Sala to tonino;
685grant all privileges on Macchinario to tonino;
686grant all privileges on Receptionist to tonino;
687grant all privileges on Massaggiatore to tonino;