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