· 5 years ago · May 17, 2020, 05:46 PM
1-- Cancellazione dell'utente usato per popolare se esiste
2DO $$
3 DECLARE
4 count int;
5 BEGIN
6 SELECT count(*) INTO count FROM pg_roles WHERE rolname = 'tonino';
7 IF count > 0 THEN
8 EXECUTE 'REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM tonino;';
9 EXECUTE 'DROP USER IF EXISTS tonino';
10 END IF;
11END $$;
12
13-- Cancellazione le tabelle se esistono
14DROP TABLE IF EXISTS Massaggio CASCADE;
15DROP TABLE IF EXISTS RecapitoMassaggiatore CASCADE;
16DROP TABLE IF EXISTS RecapitoCliente CASCADE;
17DROP TABLE IF EXISTS RecapitoReceptionist CASCADE;
18DROP TABLE IF EXISTS Specializzazione CASCADE;
19DROP TABLE IF EXISTS TipoMassaggio CASCADE;
20DROP TABLE IF EXISTS Cliente CASCADE;
21DROP TABLE IF EXISTS Sala CASCADE;
22DROP TABLE IF EXISTS Macchinario CASCADE;
23DROP TABLE IF EXISTS Receptionist CASCADE;
24DROP TABLE IF EXISTS Massaggiatore CASCADE;
25
26-- Creazione tabelle
27CREATE TABLE Cliente (
28 CodiceFiscale CHAR(16) PRIMARY KEY CHECK( CHAR_LENGTH(CodiceFiscale) = 16 ),
29 Cognome VARCHAR(20) NOT NULL,
30 Nome VARCHAR(20) NOT NULL
31);
32
33
34CREATE TABLE Sala(
35 NumeroSala VARCHAR(20) PRIMARY KEY,
36 NumeroLettini SMALLINT NOT NULL CHECK( NumeroLettini >= 0 )
37);
38
39
40CREATE TABLE Macchinario(
41 Tipo VARCHAR(20) PRIMARY KEY,
42 Quantita SMALLINT NOT NULL CHECK( Quantita >= 0 )
43);
44
45
46CREATE TABLE Receptionist(
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 Stipendio DECIMAL(8,2) NOT NULL
54);
55
56
57CREATE TABLE Massaggiatore(
58 CodiceFiscale CHAR(16) PRIMARY KEY CHECK( CHAR_LENGTH(CodiceFiscale) = 16 ),
59 Cognome VARCHAR(20) NOT NULL,
60 Nome VARCHAR(20) NOT NULL,
61 Via VARCHAR(20) NOT NULL,
62 CAP CHAR(5) NOT NULL CHECK( CHAR_LENGTH(CAP) = 5 ),
63 Citta VARCHAR(20) NOT NULL,
64 StipendioBase DECIMAL(8,2) NOT NULL
65);
66
67
68CREATE TABLE TipoMassaggio(
69 Tipo VARCHAR(20) PRIMARY KEY,
70 Prezzo DECIMAL(8,2) NOT NULL,
71 Durata SMALLINT NOT NULL,
72 Macchinario VARCHAR(20) NULL,
73
74 FOREIGN KEY (Macchinario) REFERENCES Macchinario(Tipo)
75 ON DELETE CASCADE
76 ON UPDATE CASCADE
77);
78
79
80CREATE TABLE Specializzazione(
81 Massaggiatore CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Massaggiatore) = 16 ),
82 TipoMassaggio VARCHAR(20) NOT NULL,
83
84 PRIMARY KEY (Massaggiatore, TipoMassaggio),
85 FOREIGN KEY (Massaggiatore) REFERENCES Massaggiatore(CodiceFiscale)
86 ON DELETE CASCADE
87 ON UPDATE CASCADE
88 DEFERRABLE INITIALLY DEFERRED,
89 FOREIGN KEY (TipoMassaggio) REFERENCES TipoMassaggio(Tipo)
90 ON DELETE CASCADE
91 ON UPDATE CASCADE
92);
93
94
95CREATE TABLE RecapitoCliente(
96 Cliente CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Cliente) = 16 ),
97 Telefono VARCHAR(13) NOT NULL,
98
99 PRIMARY KEY (Cliente, Telefono),
100 FOREIGN KEY (Cliente) REFERENCES Cliente(CodiceFiscale)
101 ON DELETE CASCADE
102 ON UPDATE CASCADE
103 DEFERRABLE INITIALLY DEFERRED
104);
105
106
107CREATE TABLE RecapitoMassaggiatore(
108 Massaggiatore CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Massaggiatore) = 16 ),
109 Telefono VARCHAR(13) NOT NULL,
110
111 PRIMARY KEY (Massaggiatore, Telefono),
112 FOREIGN KEY (Massaggiatore) REFERENCES Massaggiatore(CodiceFiscale)
113 ON DELETE CASCADE
114 ON UPDATE CASCADE
115 DEFERRABLE INITIALLY DEFERRED
116);
117
118
119CREATE TABLE RecapitoReceptionist(
120 Receptionist CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Receptionist) = 16 ),
121 Telefono VARCHAR(13) NOT NULL,
122
123 PRIMARY KEY (Receptionist, Telefono),
124 FOREIGN KEY (Receptionist) REFERENCES Receptionist(CodiceFiscale)
125 ON DELETE CASCADE
126 ON UPDATE CASCADE
127 DEFERRABLE INITIALLY DEFERRED
128);
129
130
131CREATE TABLE Massaggio(
132 Cliente CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Cliente) = 16 ),
133 DataMassaggio DATE NOT NULL CHECK( DataMassaggio > CURRENT_DATE OR DataMassaggio = CURRENT_DATE AND OraInizio > CURRENT_TIME ),
134 OraInizio TIME NOT NULL CHECK( OraInizio >= TIME '09:00:00' ),
135 OraFine TIME NOT NULL CHECK( OraFine <= TIME '21:00:00' ),
136 DataPrenotazione TIMESTAMP NOT NULL,
137 Massaggiatore CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Massaggiatore) = 16 ),
138 Sala VARCHAR(20) NOT NULL,
139 TipoMassaggio VARCHAR(20) NOT NULL,
140
141 PRIMARY KEY (Cliente, DataMassaggio, OraInizio),
142 FOREIGN KEY (Massaggiatore) REFERENCES Massaggiatore(CodiceFiscale)
143 ON DELETE CASCADE
144 ON UPDATE CASCADE,
145 FOREIGN KEY (Sala) REFERENCES Sala(NumeroSala)
146 ON DELETE CASCADE
147 ON UPDATE CASCADE,
148 FOREIGN KEY (TipoMassaggio) REFERENCES TipoMassaggio(Tipo)
149 ON DELETE CASCADE
150 ON UPDATE CASCADE
151);
152
153
154-- Creazione View per la gestione della tabella massaggio
155CREATE OR REPLACE VIEW Prenotazione AS
156SELECT Cliente, DataMassaggio, OraInizio, TipoMassaggio
157FROM Massaggio;
158
159
160
161/*
162 Prima di inserire un nuovo cliente, massaggiatore o receptionist verifica che gli sia già stato attribuito un recapito telefonico.
163 Nel caso di un massaggiatore verifica inoltre che gli sia stata attribuita almeno una specializzazione.
164 Se la verifica non va a buon fine impedisce l'inserimento.
165*/
166CREATE OR REPLACE FUNCTION inserimento_persona() RETURNS TRIGGER AS $$
167 BEGIN
168 IF TG_TABLE_NAME = 'cliente' THEN
169 IF (SELECT count(*) FROM recapitoCliente R where NEW.CodiceFiscale = R.Cliente) = 0 THEN
170 RAISE EXCEPTION 'Impossibile eseguire l operazione: il cliente deve avere almeno un recapito telefonico';
171 END IF;
172
173 ELSIF TG_TABLE_NAME = 'massaggiatore' THEN
174 IF (SELECT count(*) FROM recapitoMassaggiatore R where NEW.CodiceFiscale = R.Massaggiatore) = 0 THEN
175 RAISE EXCEPTION 'Impossibile eseguire l operazione: il massaggiatore deve avere almeno un recapito telefonico';
176 END IF;
177 IF (SELECT count(*) FROM specializzazione S where NEW.CodiceFiscale = S.Massaggiatore) = 0 THEN
178 RAISE EXCEPTION 'Impossibile eseguire l operazione: il massaggiatore deve avere almeno una specializzazione';
179 END IF;
180
181 ELSE
182 IF (SELECT count(*) FROM recapitoReceptionist R WHERE NEW.CodiceFiscale = R.Receptionist) = 0 THEN
183 RAISE EXCEPTION 'Impossibile eseguire l operazione: il receptionist deve avere almeno un recapito telefonico';
184 END IF;
185
186 END IF;
187 RETURN NEW;
188 END;
189$$ LANGUAGE plpgsql;
190
191CREATE TRIGGER inserisci_cliente
192BEFORE INSERT ON cliente
193FOR EACH ROW
194EXECUTE PROCEDURE inserimento_persona();
195
196CREATE TRIGGER inserisci_massaggiatore
197BEFORE INSERT ON massaggiatore
198FOR EACH ROW
199EXECUTE PROCEDURE inserimento_persona();
200
201CREATE TRIGGER inserisci_receptionist
202BEFORE INSERT ON receptionist
203FOR EACH ROW
204EXECUTE PROCEDURE inserimento_persona();
205
206
207/*
208 Alla modifica (o cancellazione) di un recapito telefonico, se l'oggetto della modifica è il proprietario del recapito,
209 verifica che tale proprietario possieda almeno un altro recapito telefonico, altrimenti impedisce l'operazione.
210*/
211CREATE OR REPLACE FUNCTION modifica_recapiti() RETURNS TRIGGER AS $$
212 BEGIN
213 IF TG_TABLE_NAME = 'recapitocliente' THEN
214 IF (SELECT count(*) FROM recapitoCliente R where OLD.Cliente = R.Cliente) = 0
215 AND OLD.cliente IN (SELECT codicefiscale FROM cliente) THEN
216 RAISE EXCEPTION 'Impossibile eseguire l operazione: il cliente deve avere almeno un recapito telefonico';
217 END IF;
218
219 ELSIF TG_TABLE_NAME = 'recapitomassaggiatore' THEN
220 IF (SELECT count(*) FROM recapitoMassaggiatore R where OLD.Massaggiatore = R.Massaggiatore) = 0
221 AND OLD.massaggiatore IN (SELECT codicefiscale FROM massaggiatore) THEN
222 RAISE EXCEPTION 'Impossibile eseguire l operazione: il massaggiatore deve avere almeno un recapito telefonico';
223 END IF;
224
225 ELSE
226 IF (SELECT count(*) FROM recapitoReceptionist R WHERE OLD.Receptionist = R.Receptionist) = 0
227 AND OLD.receptionist IN (SELECT codicefiscale FROM receptionist) THEN
228 RAISE EXCEPTION 'Impossibile eseguire l operazione: il receptionist deve avere almeno un recapito telefonico';
229 END IF;
230
231 END IF;
232 IF TG_OP = 'DELETE' THEN
233 RETURN OLD;
234 ELSE
235 RETURN NEW;
236 END IF;
237 END;
238$$ LANGUAGE plpgsql;
239
240CREATE TRIGGER modifica_recapito_cliente
241AFTER UPDATE OF cliente OR DELETE ON recapitocliente
242FOR EACH ROW
243EXECUTE PROCEDURE modifica_recapiti();
244
245CREATE TRIGGER modifica_recapito_massaggiatore
246AFTER UPDATE OF massaggiatore OR DELETE ON recapitomassaggiatore
247FOR EACH ROW
248EXECUTE PROCEDURE modifica_recapiti();
249
250CREATE TRIGGER modifica_recapito_receptionist
251AFTER UPDATE OF receptionist OR DELETE ON recapitoreceptionist
252FOR EACH ROW
253EXECUTE PROCEDURE modifica_recapiti();
254
255
256/*
257 Alla modifica della durata di un tipo massaggio verifica che la durata non venga aumentata.
258 Se la durata viene aumentata verifica che non siano stati prenotati, per date future, altri massaggi di quel tipo.
259 Se la verifica precedente non va a buon fine, per semplicità di implementazione, impedisce la modifica della durata.
260 Se invece la modifica della durata consiste in una riduzione, aggiorna l'ora di fine di tutti i massaggi
261 futuri con quel tipo di massaggio.
262*/
263CREATE OR REPLACE FUNCTION modifica_durata_tipomassaggio() RETURNS TRIGGER AS $$
264 BEGIN
265 IF NEW.durata > OLD.durata THEN
266 IF NEW.Tipo IN (
267 SELECT TipoMassaggio FROM Massaggio WHERE
268 DataMassaggio > current_date OR
269 DataMassaggio = current_date AND
270 OraInizio > current_time)
271 THEN
272 RAISE EXCEPTION 'Impossibile aumentare la durata del tipo di massaggio perchè
273sono stati prenotati altri massaggi per lo stesso tipo di massaggio';
274 END IF;
275 ELSE
276 UPDATE Massaggio SET OraFine = OraInizio + interval '1m' * NEW.durata
277 WHERE TipoMassaggio = NEW.Tipo AND (
278 DataMassaggio > current_date OR
279 DataMassaggio = current_date AND
280 OraInizio > current_time);
281 END IF;
282 RETURN NEW;
283 END;
284$$ LANGUAGE plpgsql;
285
286CREATE TRIGGER modifica_durata_massaggio
287BEFORE UPDATE OF durata ON tipomassaggio
288FOR EACH ROW
289EXECUTE PROCEDURE modifica_durata_tipomassaggio();
290
291/*
292 Alla modifica (o cancellazione) di una specializzazione,
293 siccome per ogni massaggiatore deve esistere almeno una specializzazione,
294 verifica che il vecchio massaggiatore abbia almeno un'altra specializzazione,
295 altrimenti impedisce l'operazione.
296 Se il massaggiatore in questione possiede almeno un'altra specializzazione si prova a sostituire
297 tutti i massaggi futuri che coinvolgevano quel massaggiatore e di quel tipo di massaggio con un altro
298 massaggiatore libero ed in grado di effettuare il tipo di massaggio necessario.
299 Per ogni massaggio per cui non risulta disponibile nessun massaggiatore adatto verrà
300 mostrata una nota ed il massaggio sarà cancellato.
301*/
302CREATE OR REPLACE FUNCTION modifica_specializzazione() RETURNS TRIGGER AS $$
303 DECLARE
304 r massaggio%rowtype;
305 MassaggiatoreLibero varchar;
306 BEGIN
307 IF (SELECT count(*) FROM specializzazione S where OLD.massaggiatore = S.massaggiatore) = 0
308 AND OLD.massaggiatore IN (SELECT codicefiscale FROM massaggiatore) THEN
309 RAISE EXCEPTION 'Impossibile eseguire l operazione, il massaggiatore deve avere almeno una specializzazione';
310 END IF;
311
312 FOR r IN
313 SELECT * FROM Massaggio
314 WHERE Massaggiatore = OLD.massaggiatore
315 AND TipoMassaggio = OLD.TipoMassaggio AND (
316 datamassaggio > current_date
317 OR datamassaggio = current_date
318 AND oraInizio > current_time)
319 LOOP
320 SELECT Massaggiatore INTO MassaggiatoreLibero FROM (
321 SELECT S.Massaggiatore FROM Specializzazione S
322 WHERE S.TipoMassaggio = r.TipoMassaggio
323 EXCEPT
324 SELECT M.Massaggiatore FROM Massaggio M
325 WHERE M.datamassaggio = r.datamassaggio
326 AND M.oraFine>r.oraInizio AND M.oraInizio<r.oraFine ) AS foo
327 ORDER BY random() LIMIT 1;
328
329 IF FOUND THEN
330 UPDATE Massaggio SET Massaggiatore = MassaggiatoreLibero
331 WHERE DataMassaggio = r.DataMassaggio AND
332 OraInizio = r.OraInizio AND
333 Cliente = r.Cliente;
334 ELSE
335 RAISE NOTICE 'Non è stato possibile sostituire il massaggiatore. La prenotazione del cliente % per il giorno % alle ore %
336sarà cancellata', r.cliente, r.datamassaggio, r.orainizio;
337 DELETE FROM Massaggio M
338 WHERE M.DataMassaggio = r.DataMassaggio
339 AND M.OraInizio = r.OraInizio
340 AND M.Cliente = r.Cliente;
341 END IF;
342 END LOOP;
343 IF TG_OP = 'DELETE' THEN
344 RETURN OLD;
345 ELSE
346 RETURN NEW;
347 END IF;
348 END;
349$$ LANGUAGE plpgsql;
350
351CREATE TRIGGER modifica_specializzazione
352AFTER UPDATE OR DELETE ON specializzazione
353FOR EACH ROW
354EXECUTE PROCEDURE modifica_specializzazione();
355
356
357/*
358 Alla cancellazione del massaggiatore prova a sostituire tutti i massaggi futuri che questo massaggiatore
359 avrebbe dovuto eseguire con un altro massaggiatore libero ed in grado di effettuare il tipo di massaggio necessario.
360 Per ogni massaggio per cui non risulta disponibile nessun massaggiatore adatto verrà
361 mostrata una nota ed il massaggio sarà cancellato.
362*/
363CREATE OR REPLACE FUNCTION cancellazione_massaggiatore() RETURNS TRIGGER AS $$
364 DECLARE
365 r massaggio%rowtype;
366 MassaggiatoreLibero varchar;
367 BEGIN
368
369 FOR r IN
370 SELECT * FROM Massaggio
371 WHERE Massaggiatore = OLD.codicefiscale
372 AND (DataMassaggio > current_date OR
373 DataMassaggio = current_date AND
374 OraInizio > current_time)
375 LOOP
376 SELECT Massaggiatore INTO MassaggiatoreLibero FROM (
377 SELECT S.Massaggiatore FROM Specializzazione S
378 WHERE S.Massaggiatore <> OLD.codicefiscale
379 AND S.TipoMassaggio = r.TipoMassaggio
380 EXCEPT
381 SELECT M.Massaggiatore FROM Massaggio M
382 WHERE M.datamassaggio = r.datamassaggio
383 AND M.oraFine>r.oraInizio AND M.oraInizio<r.oraFine ) AS foo
384 ORDER BY random() LIMIT 1;
385
386 IF FOUND THEN
387 UPDATE Massaggio SET Massaggiatore = MassaggiatoreLibero
388 WHERE DataMassaggio = r.DataMassaggio AND
389 OraInizio = r.OraInizio AND
390 Cliente = r.Cliente;
391 ELSE
392 RAISE NOTICE 'Non è stato possibile sostituire il massaggiatore. La prenotazione del cliente % per il giorno % alle ore %
393sarà cancellata', r.cliente, r.datamassaggio, r.orainizio;
394 END IF;
395 END LOOP;
396
397 RETURN OLD;
398 END;
399$$ LANGUAGE plpgsql;
400
401CREATE TRIGGER cancella_massaggiatore
402BEFORE DELETE ON massaggiatore
403FOR EACH ROW
404EXECUTE PROCEDURE cancellazione_massaggiatore();
405
406
407/*
408 Alla cancellazione di una sala prova a sostituire tutti i massaggi futuri previsti in quella sala
409 e prova a sostituirla con un'altra sala in cui ci sono lettini liberi.
410 Per ogni massaggio per cui non risulta disponibile nessun lettino (e quindi nessuna sala)
411 verrà mostrata una nota ed il massaggio sarà cancellato.
412*/
413CREATE OR REPLACE FUNCTION cancellazione_sala() RETURNS TRIGGER AS $$
414 DECLARE
415 r massaggio%rowtype;
416 SalaLibera varchar;
417 BEGIN
418
419 FOR r IN
420 SELECT * FROM Massaggio
421 WHERE Sala = OLD.numerosala
422 AND (DataMassaggio > current_date OR
423 DataMassaggio = current_date AND
424 OraInizio > current_time)
425 LOOP
426 SELECT S.NumeroSala INTO SalaLibera FROM Sala S
427 WHERE S.NumeroSala <> OLD.numeroSala
428 AND (S.NumeroLettini > 0 AND S.numeroSala NOT IN (SELECT DISTINCT sala FROM Massaggio)
429 OR S.NumeroLettini > (
430 SELECT count(*) FROM Massaggio M
431 WHERE M.datamassaggio = r.datamassaggio
432 AND M.orafine > r.orainizio AND M.orainizio < r.oraFine
433 AND M.Sala = S.numeroSala))
434 ORDER BY random() LIMIT 1;
435
436 IF FOUND THEN
437 UPDATE Massaggio SET Sala = SalaLibera
438 WHERE DataMassaggio = r.DataMassaggio AND
439 OraInizio = r.OraInizio AND
440 Cliente = r.Cliente;
441 ELSE
442 RAISE NOTICE 'Non è stato possibile sostituire la sala. La prenotazione del cliente % per il giorno % alle ore %
443sarà cancellata', r.cliente, r.datamassaggio, r.orainizio;
444 END IF;
445 END LOOP;
446
447 RETURN OLD;
448 END;
449$$ LANGUAGE plpgsql;
450
451CREATE TRIGGER cancella_sala
452BEFORE DELETE ON sala
453FOR EACH ROW
454EXECUTE PROCEDURE cancellazione_sala();
455
456
457/*
458 Alla modifica della quantità dei lettini di una sala verifica che la modifica non consista in una riduzione.
459 Se la modifica consiste in una riduzione del numero dei lettini di una sala cerca di riposizionare i massaggi
460 in altre sale libere.
461 Qualora non fosse possibile impedisce la modifica.
462*/
463CREATE OR REPLACE FUNCTION modifica_quantita_lettini() RETURNS TRIGGER AS $$
464 DECLARE
465 counter int;
466 r RECORD;
467 t RECORD;
468 SalaLibera varchar;
469 BEGIN
470 IF NEW.numerolettini < OLD.numerolettini THEN
471 EXECUTE format(
472 'CREATE TABLE IF NOT EXISTS MASSAGGI_CONTEMPORANEI (
473 cliente CHAR(16),
474 "data" DATE,
475 orainizio TIME,
476 orafine TIME,
477 PRIMARY KEY(cliente, data, orainizio, orafine)
478 );'
479 );
480
481 /*
482 DATAMASSAGGIO, ORAINIZIO, ORAFINE
483 DATA, ORA, TIPO
484 DATAMASSAGGIO, ORAINIZIO o ORAFINE, -1(ORAINIZIO) o +1(ORAFINE)
485 ORDINO PER DATA e se è uguale
486 ORDINO PER ORA
487 SE è un'ora di inizio sottraggo 1, altrimenti sommo 1
488 Se vado sotto 0 ritorno un'eccezione
489
490 In questo modo individuo per ogni istante il numero di macchinari/lettini/massaggiatori liberi
491 a seconda del filtro applicato ai massaggi selezionati.
492 */
493 counter := NEW.numerolettini;
494 FOR r IN
495 SELECT cliente, datamassaggio "data", OraInizio AS Ora, OI AS Orainizio, OraFine, tipo FROM (
496 SELECT Cliente, DataMassaggio, OraInizio, OraInizio AS OI, OraFine, -1 tipo FROM Massaggio
497 WHERE Sala = NEW.NumeroSala
498 AND (DataMassaggio > current_date OR
499 DataMassaggio = current_date AND
500 OraInizio > current_time)
501 UNION ALL
502 SELECT Cliente, DataMassaggio, OraFine, OraInizio, OraFine, 1 tipo FROM Massaggio
503 WHERE Sala = NEW.NumeroSala
504 AND (DataMassaggio > current_date OR
505 DataMassaggio = current_date AND
506 OraInizio > current_time)
507 ) AS O
508 ORDER BY "data", Ora
509 LOOP
510 counter := counter + r.Tipo;
511 IF r.Tipo = -1 THEN
512 INSERT INTO MASSAGGI_CONTEMPORANEI VALUES (r.cliente, r.data, r.orainizio, r.orafine);
513 ELSE
514 DELETE FROM MASSAGGI_CONTEMPORANEI MC WHERE MC.cliente = r.cliente
515 AND MC.data = r.data
516 AND MC.orainizio = r.oraInizio
517 AND MC.orafine = r.oraFine;
518 END IF;
519
520 IF counter < 0 AND r.Tipo = -1 THEN
521 FOR t IN SELECT * FROM MASSAGGI_CONTEMPORANEI
522 LOOP
523 SELECT S.NumeroSala INTO SalaLibera FROM Sala S
524 WHERE S.numeroSala <> NEW.numerosala AND
525 S.NumeroLettini > (
526 SELECT count(*) FROM Massaggio M
527 WHERE M.datamassaggio = t.data
528 AND M.orafine > t.orainizio AND M.orainizio < t.oraFine
529 AND M.Sala = S.numeroSala)
530 ORDER BY random() LIMIT 1;
531
532 IF FOUND THEN
533 UPDATE Massaggio SET Sala = SalaLibera
534 WHERE DataMassaggio = t.Data AND
535 OraInizio = t.OraInizio AND
536 Cliente = t.Cliente;
537
538 DELETE FROM MASSAGGI_CONTEMPORANEI MC WHERE MC.cliente = r.cliente
539 AND MC.data = r.data
540 AND MC.orainizio = r.oraInizio
541 AND MC.orafine = r.oraFine;
542 EXIT;
543 END IF;
544 END LOOP;
545 IF SalaLibera IS NULL THEN
546 EXECUTE format(
547 'DROP TABLE IF EXISTS MASSAGGI_CONTEMPORANEI;'
548 );
549
550 RAISE EXCEPTION 'Operazione non valida perché ci sono troppi massaggi prenotati collocati in questa sala.';
551 END IF;
552 END IF;
553 END LOOP;
554 END IF;
555 EXECUTE format(
556 'DROP TABLE IF EXISTS MASSAGGI_CONTEMPORANEI;'
557 );
558 RETURN NEW;
559 END;
560$$ LANGUAGE plpgsql;
561
562CREATE TRIGGER check_update_quantita_lettini
563BEFORE UPDATE OF numerolettini ON sala
564FOR EACH ROW
565EXECUTE PROCEDURE modifica_quantita_lettini();
566
567
568/*
569 Alla modifica della quantità di macchinari per un dato tipo di macchinario verifica che la modifica non consista in una riduzione.
570 Se la modifica consiste in una riduzione del numero dei macchinari verifica che questi siano ancora sufficienti
571 per effettuare tutti i massaggi.
572 In caso contrario impedisce la modifica.
573*/
574CREATE OR REPLACE FUNCTION modifica_quantita_macchinari() RETURNS TRIGGER AS $$
575 DECLARE
576 counter int;
577 r RECORD;
578 BEGIN
579 IF NEW.quantita < OLD.quantita THEN
580 counter := NEW.quantita;
581 FOR r IN
582 SELECT datamassaggio "data", orainizio ora, tipo FROM (
583 SELECT DataMassaggio, OraInizio, -1 tipo FROM Massaggio
584 WHERE (DataMassaggio > current_date OR
585 DataMassaggio = current_date AND
586 OraInizio > current_time) AND tipomassaggio IN (
587 SELECT tipo FROM tipomassaggio
588 WHERE tipomassaggio.macchinario = NEW.tipo)
589 UNION ALL
590 SELECT DataMassaggio, OraFine, 1 tipo FROM Massaggio
591 WHERE (DataMassaggio > current_date OR
592 DataMassaggio = current_date AND
593 OraInizio > current_time) AND tipomassaggio IN (
594 SELECT tipo FROM tipomassaggio
595 WHERE tipomassaggio.macchinario = NEW.tipo)
596 ) AS O
597 ORDER BY "data", ora
598 LOOP
599 counter := counter + r.Tipo;
600 IF counter < 0 THEN
601 RAISE EXCEPTION 'Operazione non valida perché ci sono troppi massaggi prenotati che utilizzano il macchinario.';
602 END IF;
603 END LOOP;
604 END IF;
605
606 RETURN NEW;
607 END;
608$$ LANGUAGE plpgsql;
609
610CREATE TRIGGER check_update_quantita_macchinario
611BEFORE UPDATE OF quantita ON macchinario
612FOR EACH ROW
613EXECUTE PROCEDURE modifica_quantita_macchinari();
614
615/*
616 All'inserimento (o alla modifica del codice fiscale) di un cliente, massaggiatore o receptionist verifica
617 che non esista un altro cliente, massaggiatore o receptionist con lo stesso codice fiscale.
618
619 Il vincolo tra massaggiatore e receptionist deriva dalla tecnica utilizzata per tradurre la gerarchia.
620 Il vincolo tra cliente e dipendente (quindi massaggiatore e recepitionist) deriva dalla combinazione di
621 due ulteriori vincoli aggiuntivi:
622 "Se un cliente è anche un dipendente allora non può prenotare un massaggio durante il suo orario di lavoro."
623 "Gli orari di lavoro di tutti i dipendenti coincidono con gli orari di apertura del centro massaggi."
624*/
625CREATE OR REPLACE FUNCTION check_codice_fiscale() RETURNS TRIGGER AS $$
626 DECLARE
627 temp1 int;
628 temp2 int;
629 temp3 int;
630 BEGIN
631 SELECT count(*) INTO temp1 FROM Cliente
632 WHERE codicefiscale = NEW.codicefiscale;
633 SELECT count(*) INTO temp2 FROM Massaggiatore
634 WHERE codicefiscale = new.codicefiscale;
635 SELECT count(*) INTO temp3 FROM Receptionist
636 WHERE codicefiscale = new.codicefiscale;
637 IF (temp1 + temp2 + temp3 > 0) THEN
638 RAISE EXCEPTION 'Codice Fiscale già presente';
639 END IF;
640 RETURN NEW;
641 END;
642$$ LANGUAGE plpgsql;
643
644CREATE TRIGGER check_insert_codice_fiscale_cliente
645BEFORE INSERT OR UPDATE OF codicefiscale ON cliente
646FOR EACH ROW
647EXECUTE PROCEDURE check_codice_fiscale();
648
649CREATE TRIGGER check_insert_codice_fiscale_massaggiatore
650BEFORE INSERT OR UPDATE OF codicefiscale ON massaggiatore
651FOR EACH ROW
652EXECUTE PROCEDURE check_codice_fiscale();
653
654CREATE TRIGGER check_insert_codice_fiscale_receptionist
655BEFORE INSERT OR UPDATE OF codicefiscale ON receptionist
656FOR EACH ROW
657EXECUTE PROCEDURE check_codice_fiscale();
658
659
660/*
661 All'inserimento di una prenotazione (cioè di soli cliente, dataMassaggio, oraInizio, tipoMassaggio),
662 dopo aver verificato la correttezza della richiesta calcola i campi mancanti per l'inserimento in massaggio.
663 (Si ricorda che prenotazione è una vista basata su massaggio)
664
665 oraFine = oraInizio + durata (ottenuta dalla tabella tipoMassaggio)
666 dataPrenotazione = data e ora correnti (timestamp)
667 Massaggiatore = un massaggiatore libero nella fascia oraria desiderata in grado di effettuare quel tipo di massaggio richiesto
668 Sala = una sala con sufficienti lettini liberi nella fascia oraria desiderata
669 Macchinario = un macchinario libero nella fascia oraria desiderata
670*/
671CREATE OR REPLACE FUNCTION prenota() RETURNS TRIGGER AS $$
672 DECLARE
673 -- NEW = cliente, dataMassaggio, oraInizio, tipoMassaggio
674 durata int;
675 NewOraFine time;
676 MassaggiatoreLibero varchar;
677 MacchinarioRichiesto varchar;
678 SalaLibera varchar;
679 BEGIN
680 -- Selezione durata del tipoMassaggio
681 SELECT tipomassaggio.durata INTO durata FROM tipomassaggio WHERE tipo = NEW.tipomassaggio;
682 IF NOT FOUND THEN
683 RAISE EXCEPTION 'Il tipo massaggio indicato non è disponibile';
684 END IF;
685
686 -- Calcola l'ora di fine
687 NewOraFine := NEW.orainizio + interval '1m' * durata;
688
689
690 -- Verifica massaggiatori disponibili per tutta la durata
691
692 -- Seleziona tutti i massaggiatori che sanno fare il tipo massaggio richiesto
693 SELECT foo.massaggiatore INTO massaggiatoreLibero from (
694 SELECT S.massaggiatore FROM Specializzazione S WHERE S.tipomassaggio = NEW.tipomassaggio
695 EXCEPT
696 -- Seleziona tutti i massaggiatori che sono impegnati in quel giorno in quell intervallo orario
697 SELECT M.massaggiatore FROM Massaggio M
698 WHERE M.dataMassaggio = NEW.dataMassaggio AND
699 M.orafine > NEW.orainizio AND M.orainizio < NewOraFine) AS foo
700 ORDER BY random() LIMIT 1;
701
702 IF NOT FOUND THEN
703 RAISE EXCEPTION 'Non c è nessun massaggiatore disponibile';
704 END IF;
705
706 -- Verifica se è richiesto il macchinario per quel tipo massaggio
707 SELECT T.macchinario INTO MacchinarioRichiesto FROM TipoMassaggio T
708 WHERE T.tipo = NEW.tipomassaggio;
709
710
711 IF MacchinarioRichiesto IS NOT NULL THEN
712 -- Verifica macchinari disponibili per tutta la durata
713 -- Prendi tutti i macchinari la cui quantità è maggiore delle volte che sono usati nell'intervallo orario desiderato
714 PERFORM macchinario.tipo FROM macchinario
715 WHERE macchinario.tipo = MacchinarioRichiesto
716 AND macchinario.quantita >
717 -- Le volte che lo stesso macchinario è usato
718 (select count(*) FROM massaggio M
719 -- Nel giorno scelto
720 WHERE M.datamassaggio = NEW.datamassaggio
721 -- È impegnato in quell'ora
722 AND M.orafine > NEW.orainizio AND M.orainizio < NewOraFine
723 -- Il macchinario utilizzato è quello richiesto
724 -- Il tipo massaggio prevede di utilizzare il macchinario richiesto
725 AND M.tipomassaggio IN (
726 SELECT tipo FROM tipomassaggio
727 WHERE tipomassaggio.macchinario = MacchinarioRichiesto)
728 );
729 IF NOT FOUND THEN
730 RAISE EXCEPTION 'Non c è nessun macchinario disponibile';
731 END IF;
732 END IF;
733
734 -- Verifica sale con lettini disponibili per tutta la durata
735 -- Prendi tutte le sale i cui lettini sono maggiori di quelli usati nell'intervallo orario desiderato
736 SELECT sala.numeroSala INTO SalaLibera FROM sala
737 WHERE sala.numeroLettini >
738 ( -- Sale usate nell'intervallo desiderato
739 SELECT count(*) FROM Massaggio M
740 WHERE M.datamassaggio = NEW.datamassaggio
741 AND M.orafine > NEW.orainizio AND M.orainizio < NewOraFine
742 AND M.Sala = sala.numeroSala
743 )
744 ORDER BY random() LIMIT 1;
745
746 IF NOT FOUND THEN
747 RAISE EXCEPTION 'Non c è nessuna sala disponibile';
748 END IF;
749
750 -- Verifica cliente non impegnato in quell'intervallo
751 PERFORM M.cliente FROM massaggio M
752 WHERE M.cliente = NEW.cliente
753 AND M.datamassaggio = NEW.datamassaggio
754 AND M.orafine > NEW.orainizio AND M.orainizio < NewOraFine;
755
756 IF FOUND THEN
757 RAISE EXCEPTION 'Il cliente è già impegnato';
758 END IF;
759
760 IF TG_OP = 'INSERT' THEN
761 INSERT INTO Massaggio
762 VALUES(NEW.Cliente, NEW.DataMassaggio, NEW.OraInizio, NewOraFine, now(), massaggiatoreLibero, Salalibera, NEW.TipoMassaggio);
763 ELSE
764 UPDATE Massaggio SET
765 Cliente = NEW.Cliente,
766 DataMassaggio = NEW.DataMassaggio,
767 OraInizio = NEW.OraInizio,
768 OraFine = NewOraFine,
769 dataPrenotazione = now(),
770 massaggiatore = massaggiatoreLibero,
771 Sala = Salalibera,
772 TipoMassaggio = NEW.TipoMassaggio
773 WHERE
774 Cliente = OLD.Cliente AND
775 DataMassaggio = OLD.DataMassaggio AND
776 OraInizio = OLD.OraInizio AND
777 OraFine = OLD.OraFine;
778 END IF;
779 RETURN NEW;
780 END;
781$$ LANGUAGE plpgsql;
782
783CREATE TRIGGER richiesta_massaggio
784INSTEAD OF INSERT OR UPDATE ON prenotazione
785FOR EACH ROW
786EXECUTE PROCEDURE prenota();
787
788CREATE USER tonino WITH PASSWORD 'Pippo';
789GRANT ALL PRIVILEGES ON Prenotazione TO tonino;
790GRANT ALL PRIVILEGES ON Massaggio TO tonino;
791GRANT ALL PRIVILEGES ON RecapitoMassaggiatore TO tonino;
792GRANT ALL PRIVILEGES ON RecapitoCliente TO tonino;
793GRANT ALL PRIVILEGES ON RecapitoReceptionist TO tonino;
794GRANT ALL PRIVILEGES ON Specializzazione TO tonino;
795GRANT ALL PRIVILEGES ON TipoMassaggio TO tonino;
796GRANT ALL PRIVILEGES ON Cliente TO tonino;
797GRANT ALL PRIVILEGES ON Sala TO tonino;
798GRANT ALL PRIVILEGES ON Macchinario TO tonino;
799GRANT ALL PRIVILEGES ON Receptionist TO tonino;
800GRANT ALL PRIVILEGES ON Massaggiatore TO tonino;