· 5 years ago · May 14, 2020, 07:48 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 AND (DataMassaggio > current_date OR
321 DataMassaggio = current_date AND
322 OraInizio > current_time)
323 LOOP
324 SELECT Massaggiatore INTO MassaggiatoreLibero FROM (
325 SELECT S.Massaggiatore FROM Specializzazione S
326 WHERE S.TipoMassaggio = r.TipoMassaggio
327 EXCEPT
328 SELECT M.Massaggiatore FROM Massaggio M
329 WHERE M.datamassaggio = r.datamassaggio
330 AND M.oraFine>r.oraInizio AND M.oraInizio<r.oraFine ) AS foo
331 ORDER BY random() LIMIT 1;
332
333 IF FOUND THEN
334 UPDATE Massaggio SET Massaggiatore = MassaggiatoreLibero
335 WHERE DataMassaggio = r.DataMassaggio AND
336 OraInizio = r.OraInizio AND
337 Cliente = r.Cliente;
338 ELSE
339 RAISE NOTICE 'Non è stato possibile sostituire il massaggiatore. La prenotazione del cliente % per il giorno % alle ore %
340sarà cancellata', r.cliente, r.datamassaggio, r.orainizio;
341 END IF;
342 END LOOP;
343
344 RETURN OLD;
345 END;
346$$ LANGUAGE plpgsql;
347
348CREATE TRIGGER cancella_massaggiatore
349AFTER DELETE
350ON massaggiatore
351FOR EACH ROW
352EXECUTE PROCEDURE cancellazione_massaggiatore();
353
354CREATE OR REPLACE FUNCTION cancellazione_sala() RETURNS TRIGGER AS $$
355 DECLARE
356 r massaggio%rowtype;
357 SalaLibera varchar;
358 BEGIN
359
360 FOR r IN
361 SELECT * FROM Massaggio
362 WHERE Sala = OLD.numerosala
363 AND (DataMassaggio > current_date OR
364 DataMassaggio = current_date AND
365 OraInizio > current_time)
366 LOOP
367 SELECT S.NumeroSala INTO SalaLibera FROM Sala S
368 WHERE S.numeroSala NOT IN (SELECT DISTINCT sala FROM Massaggio) AND S.NumeroLettini > 0
369 OR S.NumeroLettini > (
370 SELECT count(*) FROM Massaggio M
371 WHERE M.datamassaggio = r.datamassaggio
372 AND M.orafine > r.orainizio AND M.orainizio < r.oraFine
373 AND M.Sala = S.numeroSala)
374 ORDER BY random() LIMIT 1;
375
376 IF FOUND THEN
377 UPDATE Massaggio SET Sala = SalaLibera
378 WHERE DataMassaggio = r.DataMassaggio AND
379 OraInizio = r.OraInizio AND
380 Cliente = r.Cliente;
381 ELSE
382 RAISE NOTICE 'Non è stato possibile sostituire la sala. La prenotazione del cliente % per il giorno % alle ore %
383sarà cancellata', r.cliente, r.datamassaggio, r.orainizio;
384 END IF;
385 END LOOP;
386
387 RETURN OLD;
388 END;
389$$ LANGUAGE plpgsql;
390
391CREATE TRIGGER cancella_sala
392AFTER DELETE ON sala
393FOR EACH ROW
394EXECUTE PROCEDURE cancellazione_sala();
395
396CREATE OR REPLACE FUNCTION modifica_quantita_lettini() RETURNS TRIGGER AS $$
397 DECLARE
398 counter int;
399 r RECORD;
400 t RECORD;
401 SalaLibera varchar;
402 BEGIN
403 IF NEW.numerolettini < OLD.numerolettini THEN
404 EXECUTE format(
405 'CREATE TABLE IF NOT EXISTS NUOVA_TABELLA (
406 cliente CHAR(16),
407 "data" DATE,
408 orainizio TIME,
409 orafine TIME,
410 PRIMARY KEY(cliente, data, orainizio, orafine)
411 );'
412 );
413 counter := NEW.numerolettini;
414 FOR r IN
415 SELECT cliente, datamassaggio "data", OraInizio AS Ora, OI AS Orainizio, OraFine, tipo FROM (
416 SELECT Cliente, DataMassaggio, OraInizio, OraInizio AS OI, OraFine, -1 tipo FROM Massaggio
417 WHERE Sala = NEW.NumeroSala
418 AND (DataMassaggio > current_date OR
419 DataMassaggio = current_date AND
420 OraInizio > current_time)
421 UNION ALL
422 SELECT Cliente, DataMassaggio, OraFine, OraInizio, OraFine, 1 tipo FROM Massaggio
423 WHERE Sala = NEW.NumeroSala
424 AND (DataMassaggio > current_date OR
425 DataMassaggio = current_date AND
426 OraInizio > current_time)
427 ) AS O
428 ORDER BY "data", Ora
429 LOOP
430 IF r.Tipo = -1 THEN
431 INSERT INTO NUOVA_TABELLA VALUES (r.cliente, r.data, r.orainizio, r.orafine);
432 ELSE
433 DELETE FROM NUOVA_TABELLA N WHERE N.cliente = r.cliente
434 AND N.data = r.data
435 AND N.orainizio = r.oraInizio
436 AND N.orafine = r.oraFine;
437 END IF;
438
439 counter := counter + r.Tipo;
440 IF counter < 0 THEN
441 FOR t IN SELECT * FROM NUOVA_TABELLA
442 LOOP
443 SELECT S.NumeroSala INTO SalaLibera FROM Sala S
444 WHERE S.numeroSala <> NEW.numerosala AND
445 S.NumeroLettini > (
446 SELECT count(*) FROM Massaggio M
447 WHERE M.datamassaggio = t.data
448 AND M.orafine > t.orainizio AND M.orainizio < t.oraFine
449 AND M.Sala = S.numeroSala)
450 ORDER BY random() LIMIT 1;
451
452 IF FOUND THEN
453 UPDATE Massaggio SET Sala = SalaLibera
454 WHERE DataMassaggio = t.Data AND
455 OraInizio = t.OraInizio AND
456 Cliente = t.Cliente;
457 DELETE FROM NUOVA_TABELLA N WHERE N.cliente = r.cliente
458 AND N.data = r.data
459 AND N.orainizio = r.oraInizio
460 AND N.orafine = r.oraFine;
461 EXIT;
462 END IF;
463 END LOOP;
464 IF SalaLibera IS NULL THEN
465 EXECUTE format(
466 'DROP TABLE IF EXISTS NUOVA_TABELLA;'
467 );
468
469 RAISE EXCEPTION 'Operazione non valida perché ci sono troppi massaggi prenotati collocati in questa sala.';
470 END IF;
471 END IF;
472 END LOOP;
473 END IF;
474 EXECUTE format(
475 'DROP TABLE IF EXISTS NUOVA_TABELLA;'
476 );
477 RETURN NEW;
478 END;
479$$ LANGUAGE plpgsql;
480
481CREATE TRIGGER checkUpdateQuantitaLettini
482BEFORE UPDATE OF numerolettini ON sala
483FOR EACH ROW
484EXECUTE PROCEDURE modifica_quantita_lettini();
485
486CREATE OR REPLACE FUNCTION modifica_quantita_macchinari() RETURNS TRIGGER AS $$
487 DECLARE
488 counter int;
489 r RECORD;
490 BEGIN
491 /*
492 DATAMASSAGGIO, ORAINIZIO, ORAFINE
493 DATA, ORA, TIPO
494 DATAMASSAGGIO, ORAINIZIO o ORAFINE, -1(ORAINIZIO) o +1(ORAFINE)
495 ORDINO PER DATA e se è uguale
496 ORDINO PER ORA
497 SE è un'ora di inizio sottraggo 1, altrimenti sommo 1
498 Se vado sotto 0 ritorno un'eccezione
499 */
500 IF NEW.quantita < OLD.quantita THEN
501 counter := NEW.quantita;
502 FOR r IN
503 SELECT datamassaggio "data", orainizio ora, tipo FROM (
504 SELECT DataMassaggio, OraInizio, -1 tipo FROM Massaggio
505 WHERE (DataMassaggio > current_date OR
506 DataMassaggio = current_date AND
507 OraInizio > current_time) AND tipomassaggio IN (
508 SELECT tipo FROM tipomassaggio
509 WHERE tipomassaggio.macchinario = NEW.tipo)
510 UNION ALL
511 SELECT DataMassaggio, OraFine, 1 tipo FROM Massaggio
512 WHERE (DataMassaggio > current_date OR
513 DataMassaggio = current_date AND
514 OraInizio > current_time) AND tipomassaggio IN (
515 SELECT tipo FROM tipomassaggio
516 WHERE tipomassaggio.macchinario = NEW.tipo)
517 ) AS O
518 ORDER BY "data", ora
519 LOOP
520 counter := counter + r.Tipo;
521 IF counter < 0 THEN
522 RAISE EXCEPTION 'Operazione non valida perché ci sono troppi massaggi prenotati che utilizzano il macchinario.';
523 END IF;
524 END LOOP;
525 END IF;
526
527 RETURN NEW;
528 END;
529$$ LANGUAGE plpgsql;
530
531CREATE TRIGGER checkUpdateQuantitaMacchinario
532BEFORE UPDATE OF quantita ON macchinario
533FOR EACH ROW
534EXECUTE PROCEDURE modifica_quantita_macchinari();
535
536CREATE OR REPLACE FUNCTION checkcodicefiscale() RETURNS TRIGGER AS $$
537 DECLARE
538 temp1 int;
539 temp2 int;
540 temp3 int;
541 temp4 int;
542 BEGIN
543 SELECT count(*) INTO temp1 FROM Cliente
544 WHERE codicefiscale = NEW.codicefiscale;
545 SELECT count(*) INTO temp2 FROM Massaggiatore
546 WHERE codicefiscale = new.codicefiscale;
547 SELECT count(*) INTO temp3 FROM Receptionist
548 WHERE codicefiscale = new.codicefiscale;
549 temp4 = temp1 + temp2 + temp3;
550 IF (temp4 > 0) THEN
551 RAISE EXCEPTION 'Codice Fiscale già presente';
552 END IF;
553 RETURN NEW;
554 END;
555$$ LANGUAGE plpgsql;
556
557CREATE TRIGGER checkInsertCodiceFiscaleCliente
558BEFORE INSERT OR UPDATE OF codicefiscale ON cliente
559FOR EACH ROW
560EXECUTE PROCEDURE checkcodicefiscale();
561
562CREATE TRIGGER checkInsertCodiceFiscaleMassaggiatore
563BEFORE INSERT OR UPDATE OF codicefiscale ON massaggiatore
564FOR EACH ROW
565EXECUTE PROCEDURE checkcodicefiscale();
566
567CREATE TRIGGER checkInsertCodiceFiscaleReceptionist
568BEFORE INSERT OR UPDATE OF codicefiscale ON receptionist
569FOR EACH ROW
570EXECUTE PROCEDURE checkcodicefiscale();
571
572-- NEW = cliente, dataMassaggio, oraInizio, tipoMassaggio
573CREATE OR REPLACE FUNCTION prenota() RETURNS TRIGGER AS $$
574 DECLARE
575 durata int;
576 NewOraFine time;
577 MassaggiatoreLibero varchar;
578 MacchinarioRichiesto varchar;
579 SalaLibera varchar;
580 BEGIN
581 -- Selezione durata del tipoMassaggio
582 IF EXISTS(SELECT tipomassaggio.durata FROM tipomassaggio WHERE tipo = NEW.tipomassaggio) THEN
583 SELECT tipomassaggio.durata INTO durata FROM tipomassaggio WHERE tipo = NEW.tipomassaggio;
584 ELSE
585 RAISE EXCEPTION 'Il tipo massaggio indicato non è disponibile';
586 END IF;
587
588 -- Calcola l'ora di fine
589 NewOraFine := NEW.orainizio + interval '1m' * durata;
590
591
592 -- Verifica massaggiatori disponibili per tutta la durata
593
594 -- Seleziona tutti i massaggiatori che sanno fare il tipo massaggio richiesto
595 SELECT Risultato.massaggiatore INTO massaggiatoreLibero from (
596 SELECT S.massaggiatore FROM Specializzazione S WHERE S.tipomassaggio = NEW.tipomassaggio
597 EXCEPT
598 -- Seleziona tutti i massaggiatori che sono impegnati in quel giorno in quell intervallo orario
599 SELECT M.massaggiatore FROM Massaggio M
600 WHERE M.dataMassaggio = NEW.dataMassaggio AND
601 M.orafine > NEW.orainizio AND M.orainizio < NewOraFine) AS Risultato
602 ORDER BY random()
603 LIMIT 1;
604
605 IF NOT FOUND THEN
606 RAISE EXCEPTION 'Non c è nessun massaggiatore disponibile';
607 END IF;
608
609 -- Verifica se è richiesto il macchinario per quel tipo massaggio
610 SELECT T.macchinario INTO MacchinarioRichiesto FROM TipoMassaggio T
611 WHERE T.tipo = NEW.tipomassaggio;
612
613
614 IF MacchinarioRichiesto IS NOT NULL THEN
615 -- Verifica macchinari disponibili per tutta la durata
616 -- Prendi tutti i macchinari la cui quantità è maggiore delle volte che sono usati nell'intervallo orario desiderato
617 PERFORM macchinario.tipo FROM macchinario
618 WHERE macchinario.tipo = MacchinarioRichiesto
619 AND macchinario.quantita >
620 -- Le volte che lo stesso macchinario è usato
621 (select count(*) FROM massaggio M
622 -- Nel giorno scelto
623 WHERE M.datamassaggio = NEW.datamassaggio
624 -- È impegnato in quell'ora
625 AND M.orafine > NEW.orainizio AND M.orainizio < NewOraFine
626 -- Il macchinario utilizzato è quello richiesto
627 -- Il tipo massaggio prevede di utilizzare il macchinario richiesto
628 AND M.tipomassaggio IN (
629 SELECT tipo FROM tipomassaggio
630 WHERE tipomassaggio.macchinario = MacchinarioRichiesto)
631 );
632 IF NOT FOUND THEN
633 RAISE EXCEPTION 'Non c è nessun macchinario disponibile';
634 END IF;
635 END IF;
636
637 -- Verifica sale con lettini disponibili per tutta la durata
638 -- Prendi tutte le sale i cui lettini sono maggiori di quelli usati nell''intervallo orario desiderato
639 SELECT sala.numeroSala INTO SalaLibera FROM sala
640 WHERE sala.numeroLettini >
641 ( -- Sale usate nell'intervallo desiderato
642 SELECT count(*) FROM Massaggio M
643 WHERE M.datamassaggio = NEW.datamassaggio
644 AND M.orafine > NEW.orainizio AND M.orainizio < NewOraFine
645 AND M.Sala = sala.numeroSala
646 )
647 ORDER BY random()
648 LIMIT 1;
649
650 IF NOT FOUND THEN
651 RAISE EXCEPTION 'Non c è nessuna sala disponibile';
652 END IF;
653
654 -- Verifica cliente con unico massaggio in quell'intervallo
655 PERFORM M.cliente FROM massaggio M
656 WHERE M.cliente = NEW.cliente
657 AND M.datamassaggio = NEW.datamassaggio
658 AND M.orafine > NEW.orainizio AND M.orainizio < NewOraFine;
659
660 IF FOUND THEN
661 RAISE EXCEPTION 'Il cliente è già impegnato';
662 END IF;
663
664 IF TG_OP = 'INSERT' THEN
665 INSERT INTO Massaggio
666 VALUES(NEW.Cliente, NEW.DataMassaggio, NEW.OraInizio, NewOraFine, now(), massaggiatoreLibero, Salalibera, NEW.TipoMassaggio);
667 ELSE
668 UPDATE Massaggio SET
669 Cliente = NEW.Cliente,
670 DataMassaggio = NEW.DataMassaggio,
671 OraInizio = NEW.OraInizio,
672 OraFine = NewOraFine,
673 dataPrenotazione = now(),
674 massaggiatore = massaggiatoreLibero,
675 Sala = Salalibera,
676 TipoMassaggio = NEW.TipoMassaggio
677 WHERE
678 Cliente = OLD.Cliente AND
679 DataMassaggio = OLD.DataMassaggio AND
680 OraInizio = OLD.OraInizio AND
681 OraFine = OLD.OraFine;
682 END IF;
683 RETURN NEW;
684 END;
685$$ LANGUAGE plpgsql;
686
687CREATE TRIGGER richiesta_massaggio
688INSTEAD OF INSERT OR UPDATE ON prenotazione
689FOR EACH ROW
690EXECUTE PROCEDURE prenota();
691
692grant all privileges on Prenotazione to tonino;
693grant all privileges on Massaggio to tonino;
694grant all privileges on RecapitoMassaggiatore to tonino;
695grant all privileges on RecapitoCliente to tonino;
696grant all privileges on RecapitoReceptionist to tonino;
697grant all privileges on Specializzazione to tonino;
698grant all privileges on TipoMassaggio to tonino;
699grant all privileges on Cliente to tonino;
700grant all privileges on Sala to tonino;
701grant all privileges on Macchinario to tonino;
702grant all privileges on Receptionist to tonino;
703grant all privileges on Massaggiatore to tonino;