· 5 years ago · May 17, 2020, 05:10 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
240--DROP TRIGGER modifica_recapito_receptionist on recapitoreceptionist
241CREATE TRIGGER modifica_recapito_cliente
242AFTER UPDATE OF cliente OR DELETE ON recapitocliente
243FOR EACH ROW
244EXECUTE PROCEDURE modifica_recapiti();
245
246CREATE TRIGGER modifica_recapito_massaggiatore
247AFTER UPDATE OF massaggiatore OR DELETE ON recapitomassaggiatore
248FOR EACH ROW
249EXECUTE PROCEDURE modifica_recapiti();
250
251CREATE TRIGGER modifica_recapito_receptionist
252AFTER UPDATE OF receptionist OR DELETE ON recapitoreceptionist
253FOR EACH ROW
254EXECUTE PROCEDURE modifica_recapiti();
255
256
257/*
258 Alla modifica della durata di un tipo massaggio verifica che la durata non venga aumentata.
259 Se la durata viene aumentata verifica che non siano stati prenotati, per date future, altri massaggi di quel tipo.
260 Se la verifica precedente non va a buon fine, per semplicità di implementazione, impedisce la modifica della durata.
261 Se invece la modifica della durata consiste in una riduzione, aggiorna l'ora di fine di tutti i massaggi
262 futuri con quel tipo di massaggio.
263*/
264CREATE OR REPLACE FUNCTION modifica_durata_tipomassaggio() RETURNS TRIGGER AS $$
265 BEGIN
266 IF NEW.durata > OLD.durata THEN
267 IF NEW.Tipo IN (
268 SELECT TipoMassaggio FROM Massaggio WHERE
269 DataMassaggio > current_date OR
270 DataMassaggio = current_date AND
271 OraInizio > current_time)
272 THEN
273 RAISE EXCEPTION 'Impossibile aumentare la durata del tipo di massaggio perchè
274sono stati prenotati altri massaggi per lo stesso tipo di massaggio';
275 END IF;
276 ELSE
277 UPDATE Massaggio SET OraFine = OraInizio + interval '1m' * NEW.durata
278 WHERE TipoMassaggio = NEW.Tipo AND (
279 DataMassaggio > current_date OR
280 DataMassaggio = current_date AND
281 OraInizio > current_time);
282 END IF;
283 RETURN NEW;
284 END;
285$$ LANGUAGE plpgsql;
286
287CREATE TRIGGER modifica_durata_massaggio
288BEFORE UPDATE OF durata ON tipomassaggio
289FOR EACH ROW
290EXECUTE PROCEDURE modifica_durata_tipomassaggio();
291
292/*
293 Alla modifica (o cancellazione) di una specializzazione,
294 siccome per ogni massaggiatore deve esistere almeno una specializzazione,
295 verifica che il vecchio massaggiatore abbia almeno un'altra specializzazione,
296 altrimenti impedisce l'operazione.
297 Se il massaggiatore in questione possiede almeno un'altra specializzazione si prova a sostituire
298 tutti i massaggi futuri che coinvolgevano quel massaggiatore e di quel tipo di massaggio con un altro
299 massaggiatore libero ed in grado di effettuare il tipo di massaggio necessario.
300 Per ogni massaggio per cui non risulta disponibile nessun massaggiatore adatto verrà
301 mostrata una nota ed il massaggio sarà cancellato.
302*/
303CREATE OR REPLACE FUNCTION modifica_specializzazione() RETURNS TRIGGER AS $$
304 DECLARE
305 r massaggio%rowtype;
306 MassaggiatoreLibero varchar;
307 BEGIN
308 IF (SELECT count(*) FROM specializzazione S where OLD.massaggiatore = S.massaggiatore) = 0
309 AND OLD.massaggiatore IN (SELECT codicefiscale FROM massaggiatore) THEN
310 RAISE EXCEPTION 'Impossibile eseguire l operazione, il massaggiatore deve avere almeno una specializzazione';
311 END IF;
312
313 FOR r IN
314 SELECT * FROM Massaggio
315 WHERE Massaggiatore = OLD.massaggiatore
316 AND TipoMassaggio = OLD.TipoMassaggio AND (
317 datamassaggio > current_date
318 OR datamassaggio = current_date
319 AND oraInizio > current_time)
320 LOOP
321 SELECT Massaggiatore INTO MassaggiatoreLibero FROM (
322 SELECT S.Massaggiatore FROM Specializzazione S
323 WHERE 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 DELETE FROM Massaggio M
339 WHERE M.DataMassaggio = r.DataMassaggio
340 AND M.OraInizio = r.OraInizio
341 AND M.Cliente = r.Cliente;
342 END IF;
343 END LOOP;
344 IF TG_OP = 'DELETE' THEN
345 RETURN OLD;
346 ELSE
347 RETURN NEW;
348 END IF;
349 END;
350$$ LANGUAGE plpgsql;
351
352CREATE TRIGGER modifica_specializzazione
353AFTER UPDATE OR DELETE ON specializzazione
354FOR EACH ROW
355EXECUTE PROCEDURE modifica_specializzazione();
356
357
358/*
359 Alla cancellazione del massaggiatore prova a sostituire tutti i massaggi futuri che questo massaggiatore
360 avrebbe dovuto eseguire con un altro massaggiatore libero ed in grado di effettuare il tipo di massaggio necessario.
361 Per ogni massaggio per cui non risulta disponibile nessun massaggiatore adatto verrà
362 mostrata una nota ed il massaggio sarà cancellato.
363*/
364CREATE OR REPLACE FUNCTION cancellazione_massaggiatore() RETURNS TRIGGER AS $$
365 DECLARE
366 r massaggio%rowtype;
367 MassaggiatoreLibero varchar;
368 BEGIN
369
370 FOR r IN
371 SELECT * FROM Massaggio
372 WHERE Massaggiatore = OLD.codicefiscale
373 AND (DataMassaggio > current_date OR
374 DataMassaggio = current_date AND
375 OraInizio > current_time)
376 LOOP
377 SELECT Massaggiatore INTO MassaggiatoreLibero FROM (
378 SELECT S.Massaggiatore FROM Specializzazione S
379 WHERE 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
402AFTER DELETE ON massaggiatore
403FOR EACH ROW
404EXECUTE PROCEDURE cancellazione_massaggiatore();
405
406
407/*
408 Alla cancellazione di una sala prova a sostituire tutte 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.NumeroLettini > 0 AND S.numeroSala NOT IN (SELECT DISTINCT sala FROM Massaggio)
428 OR S.NumeroLettini > (
429 SELECT count(*) FROM Massaggio M
430 WHERE M.datamassaggio = r.datamassaggio
431 AND M.orafine > r.orainizio AND M.orainizio < r.oraFine
432 AND M.Sala = S.numeroSala)
433 ORDER BY random() LIMIT 1;
434
435 IF FOUND THEN
436 UPDATE Massaggio SET Sala = SalaLibera
437 WHERE DataMassaggio = r.DataMassaggio AND
438 OraInizio = r.OraInizio AND
439 Cliente = r.Cliente;
440 ELSE
441 RAISE NOTICE 'Non è stato possibile sostituire la sala. La prenotazione del cliente % per il giorno % alle ore %
442sarà cancellata', r.cliente, r.datamassaggio, r.orainizio;
443 END IF;
444 END LOOP;
445
446 RETURN OLD;
447 END;
448$$ LANGUAGE plpgsql;
449
450CREATE TRIGGER cancella_sala
451AFTER DELETE ON sala
452FOR EACH ROW
453EXECUTE PROCEDURE cancellazione_sala();
454
455
456/*
457 Alla modifica della quantità dei lettini di una sala verifica che la modifica non consista in una riduzione.
458 Se la modifica consiste in una riduzione del numero dei lettini di una sala cerca di riposizionare i massaggi
459 in altre sale libere.
460 Qualora non fosse possibile impedisce la modifica.
461*/
462CREATE OR REPLACE FUNCTION modifica_quantita_lettini() RETURNS TRIGGER AS $$
463 DECLARE
464 counter int;
465 r RECORD;
466 t RECORD;
467 SalaLibera varchar;
468 BEGIN
469 IF NEW.numerolettini < OLD.numerolettini THEN
470 EXECUTE format(
471 'CREATE TABLE IF NOT EXISTS MASSAGGI_CONTEMPORANEI (
472 cliente CHAR(16),
473 "data" DATE,
474 orainizio TIME,
475 orafine TIME,
476 PRIMARY KEY(cliente, data, orainizio, orafine)
477 );'
478 );
479
480 /*
481 DATAMASSAGGIO, ORAINIZIO, ORAFINE
482 DATA, ORA, TIPO
483 DATAMASSAGGIO, ORAINIZIO o ORAFINE, -1(ORAINIZIO) o +1(ORAFINE)
484 ORDINO PER DATA e se è uguale
485 ORDINO PER ORA
486 SE è un'ora di inizio sottraggo 1, altrimenti sommo 1
487 Se vado sotto 0 ritorno un'eccezione
488
489 In questo modo individuo per ogni istante il numero di macchinari/lettini/massaggiatori liberi
490 a seconda del filtro applicato ai massaggi selezionati.
491 */
492 counter := NEW.numerolettini;
493 FOR r IN
494 SELECT cliente, datamassaggio "data", OraInizio AS Ora, OI AS Orainizio, OraFine, tipo FROM (
495 SELECT Cliente, DataMassaggio, OraInizio, OraInizio AS OI, OraFine, -1 tipo FROM Massaggio
496 WHERE Sala = NEW.NumeroSala
497 AND (DataMassaggio > current_date OR
498 DataMassaggio = current_date AND
499 OraInizio > current_time)
500 UNION ALL
501 SELECT Cliente, DataMassaggio, OraFine, OraInizio, OraFine, 1 tipo FROM Massaggio
502 WHERE Sala = NEW.NumeroSala
503 AND (DataMassaggio > current_date OR
504 DataMassaggio = current_date AND
505 OraInizio > current_time)
506 ) AS O
507 ORDER BY "data", Ora
508 LOOP
509 counter := counter + r.Tipo;
510 IF r.Tipo = -1 THEN
511 INSERT INTO MASSAGGI_CONTEMPORANEI VALUES (r.cliente, r.data, r.orainizio, r.orafine);
512 ELSE
513 DELETE FROM MASSAGGI_CONTEMPORANEI MC WHERE MC.cliente = r.cliente
514 AND MC.data = r.data
515 AND MC.orainizio = r.oraInizio
516 AND MC.orafine = r.oraFine;
517 END IF;
518
519 IF counter < 0 AND r.Tipo = -1 THEN
520 FOR t IN SELECT * FROM MASSAGGI_CONTEMPORANEI
521 LOOP
522 SELECT S.NumeroSala INTO SalaLibera FROM Sala S
523 WHERE S.numeroSala <> NEW.numerosala AND
524 S.NumeroLettini > (
525 SELECT count(*) FROM Massaggio M
526 WHERE M.datamassaggio = t.data
527 AND M.orafine > t.orainizio AND M.orainizio < t.oraFine
528 AND M.Sala = S.numeroSala)
529 ORDER BY random() LIMIT 1;
530
531 IF FOUND THEN
532 UPDATE Massaggio SET Sala = SalaLibera
533 WHERE DataMassaggio = t.Data AND
534 OraInizio = t.OraInizio AND
535 Cliente = t.Cliente;
536
537 DELETE FROM MASSAGGI_CONTEMPORANEI MC WHERE MC.cliente = r.cliente
538 AND MC.data = r.data
539 AND MC.orainizio = r.oraInizio
540 AND MC.orafine = r.oraFine;
541 EXIT;
542 END IF;
543 END LOOP;
544 IF SalaLibera IS NULL THEN
545 EXECUTE format(
546 'DROP TABLE IF EXISTS MASSAGGI_CONTEMPORANEI;'
547 );
548
549 RAISE EXCEPTION 'Operazione non valida perché ci sono troppi massaggi prenotati collocati in questa sala.';
550 END IF;
551 END IF;
552 END LOOP;
553 END IF;
554 EXECUTE format(
555 'DROP TABLE IF EXISTS NUOVA_TABELLA;'
556 );
557 RETURN NEW;
558 END;
559$$ LANGUAGE plpgsql;
560
561CREATE TRIGGER check_update_quantita_lettini
562BEFORE UPDATE OF numerolettini ON sala
563FOR EACH ROW
564EXECUTE PROCEDURE modifica_quantita_lettini();
565
566
567/*
568 Alla modifica della quantità di macchinari per un dato tipo di macchinario verifica che la modifica non consista in una riduzione.
569 Se la modifica consiste in una riduzione del numero dei macchinari verifica che questi siano ancora sufficienti
570 per effettuare tutti i massaggi.
571 In caso contrario impedisce la modifica.
572*/
573CREATE OR REPLACE FUNCTION modifica_quantita_macchinari() RETURNS TRIGGER AS $$
574 DECLARE
575 counter int;
576 r RECORD;
577 BEGIN
578 IF NEW.quantita < OLD.quantita THEN
579 counter := NEW.quantita;
580 FOR r IN
581 SELECT datamassaggio "data", orainizio ora, tipo FROM (
582 SELECT DataMassaggio, OraInizio, -1 tipo FROM Massaggio
583 WHERE (DataMassaggio > current_date OR
584 DataMassaggio = current_date AND
585 OraInizio > current_time) AND tipomassaggio IN (
586 SELECT tipo FROM tipomassaggio
587 WHERE tipomassaggio.macchinario = NEW.tipo)
588 UNION ALL
589 SELECT DataMassaggio, OraFine, 1 tipo FROM Massaggio
590 WHERE (DataMassaggio > current_date OR
591 DataMassaggio = current_date AND
592 OraInizio > current_time) AND tipomassaggio IN (
593 SELECT tipo FROM tipomassaggio
594 WHERE tipomassaggio.macchinario = NEW.tipo)
595 ) AS O
596 ORDER BY "data", ora
597 LOOP
598 counter := counter + r.Tipo;
599 IF counter < 0 THEN
600 RAISE EXCEPTION 'Operazione non valida perché ci sono troppi massaggi prenotati che utilizzano il macchinario.';
601 END IF;
602 END LOOP;
603 END IF;
604
605 RETURN NEW;
606 END;
607$$ LANGUAGE plpgsql;
608
609CREATE TRIGGER check_update_quantita_macchinario
610BEFORE UPDATE OF quantita ON macchinario
611FOR EACH ROW
612EXECUTE PROCEDURE modifica_quantita_macchinari();
613
614/*
615 All'inserimento (o alla modifica del codice fiscale) di un cliente, massaggiatore o receptionist verifica
616 che non esista un altro cliente, massaggiatore o receptionist con lo stesso codice fiscale.
617
618 Il vincolo tra massaggiatore e receptionist deriva dalla tecnica utilizzata per tradurre la gerarchia.
619 Il vincolo tra cliente e dipendente (quindi massaggiatore e recepitionist) deriva dalla combinazione di
620 due ulteriori vincoli aggiuntivi:
621 "Se un cliente è anche un dipendente allora non può prenotare un massaggio durante il suo orario di lavoro."
622 "Gli orari di lavoro di tutti i dipendenti coincidono con gli orari di apertura del centro massaggi."
623*/
624CREATE OR REPLACE FUNCTION check_codice_fiscale() RETURNS TRIGGER AS $$
625 DECLARE
626 temp1 int;
627 temp2 int;
628 temp3 int;
629 BEGIN
630 SELECT count(*) INTO temp1 FROM Cliente
631 WHERE codicefiscale = NEW.codicefiscale;
632 SELECT count(*) INTO temp2 FROM Massaggiatore
633 WHERE codicefiscale = new.codicefiscale;
634 SELECT count(*) INTO temp3 FROM Receptionist
635 WHERE codicefiscale = new.codicefiscale;
636 IF (temp1 + temp2 + temp3 > 0) THEN
637 RAISE EXCEPTION 'Codice Fiscale già presente';
638 END IF;
639 RETURN NEW;
640 END;
641$$ LANGUAGE plpgsql;
642
643CREATE TRIGGER check_insert_codice_fiscale_cliente
644BEFORE INSERT OR UPDATE OF codicefiscale ON cliente
645FOR EACH ROW
646EXECUTE PROCEDURE check_codice_fiscale();
647
648CREATE TRIGGER check_insert_codice_fiscale_massaggiatore
649BEFORE INSERT OR UPDATE OF codicefiscale ON massaggiatore
650FOR EACH ROW
651EXECUTE PROCEDURE check_codice_fiscale();
652
653CREATE TRIGGER check_insert_codice_fiscale_receptionist
654BEFORE INSERT OR UPDATE OF codicefiscale ON receptionist
655FOR EACH ROW
656EXECUTE PROCEDURE check_codice_fiscale();
657
658
659/*
660 All'inserimento di una prenotazione (cioè di soli cliente, dataMassaggio, oraInizio, tipoMassaggio),
661 dopo aver verificato la correttezza della richiesta calcola i campi mancanti per l'inserimento in massaggio.
662 (Si ricorda che prenotazione è una vista basata su massaggio)
663
664 oraFine = oraInizio + durata (ottenuta dalla tabella tipoMassaggio)
665 dataPrenotazione = data e ora correnti (timestamp)
666 Massaggiatore = un massaggiatore libero nella fascia oraria desiderata in grado di effettuare quel tipo di massaggio richiesto
667 Sala = una sala con sufficienti lettini liberi nella fascia oraria desiderata
668 Macchinario = un macchinario libero nella fascia oraria desiderata
669*/
670CREATE OR REPLACE FUNCTION prenota() RETURNS TRIGGER AS $$
671 DECLARE
672 -- NEW = cliente, dataMassaggio, oraInizio, tipoMassaggio
673 durata int;
674 NewOraFine time;
675 MassaggiatoreLibero varchar;
676 MacchinarioRichiesto varchar;
677 SalaLibera varchar;
678 BEGIN
679 -- Selezione durata del tipoMassaggio
680 SELECT tipomassaggio.durata INTO durata FROM tipomassaggio WHERE tipo = NEW.tipomassaggio;
681 IF NOT FOUND THEN
682 RAISE EXCEPTION 'Il tipo massaggio indicato non è disponibile';
683 END IF;
684
685 -- Calcola l'ora di fine
686 NewOraFine := NEW.orainizio + interval '1m' * durata;
687
688
689 -- Verifica massaggiatori disponibili per tutta la durata
690
691 -- Seleziona tutti i massaggiatori che sanno fare il tipo massaggio richiesto
692 SELECT foo.massaggiatore INTO massaggiatoreLibero from (
693 SELECT S.massaggiatore FROM Specializzazione S WHERE S.tipomassaggio = NEW.tipomassaggio
694 EXCEPT
695 -- Seleziona tutti i massaggiatori che sono impegnati in quel giorno in quell intervallo orario
696 SELECT M.massaggiatore FROM Massaggio M
697 WHERE M.dataMassaggio = NEW.dataMassaggio AND
698 M.orafine > NEW.orainizio AND M.orainizio < NewOraFine) AS foo
699 ORDER BY random() LIMIT 1;
700
701 IF NOT FOUND THEN
702 RAISE EXCEPTION 'Non c è nessun massaggiatore disponibile';
703 END IF;
704
705 -- Verifica se è richiesto il macchinario per quel tipo massaggio
706 SELECT T.macchinario INTO MacchinarioRichiesto FROM TipoMassaggio T
707 WHERE T.tipo = NEW.tipomassaggio;
708
709
710 IF MacchinarioRichiesto IS NOT NULL THEN
711 -- Verifica macchinari disponibili per tutta la durata
712 -- Prendi tutti i macchinari la cui quantità è maggiore delle volte che sono usati nell'intervallo orario desiderato
713 PERFORM macchinario.tipo FROM macchinario
714 WHERE macchinario.tipo = MacchinarioRichiesto
715 AND macchinario.quantita >
716 -- Le volte che lo stesso macchinario è usato
717 (select count(*) FROM massaggio M
718 -- Nel giorno scelto
719 WHERE M.datamassaggio = NEW.datamassaggio
720 -- È impegnato in quell'ora
721 AND M.orafine > NEW.orainizio AND M.orainizio < NewOraFine
722 -- Il macchinario utilizzato è quello richiesto
723 -- Il tipo massaggio prevede di utilizzare il macchinario richiesto
724 AND M.tipomassaggio IN (
725 SELECT tipo FROM tipomassaggio
726 WHERE tipomassaggio.macchinario = MacchinarioRichiesto)
727 );
728 IF NOT FOUND THEN
729 RAISE EXCEPTION 'Non c è nessun macchinario disponibile';
730 END IF;
731 END IF;
732
733 -- Verifica sale con lettini disponibili per tutta la durata
734 -- Prendi tutte le sale i cui lettini sono maggiori di quelli usati nell'intervallo orario desiderato
735 SELECT sala.numeroSala INTO SalaLibera FROM sala
736 WHERE sala.numeroLettini >
737 ( -- Sale usate nell'intervallo desiderato
738 SELECT count(*) FROM Massaggio M
739 WHERE M.datamassaggio = NEW.datamassaggio
740 AND M.orafine > NEW.orainizio AND M.orainizio < NewOraFine
741 AND M.Sala = sala.numeroSala
742 )
743 ORDER BY random() LIMIT 1;
744
745 IF NOT FOUND THEN
746 RAISE EXCEPTION 'Non c è nessuna sala disponibile';
747 END IF;
748
749 -- Verifica cliente non impegnato in quell'intervallo
750 PERFORM M.cliente FROM massaggio M
751 WHERE M.cliente = NEW.cliente
752 AND M.datamassaggio = NEW.datamassaggio
753 AND M.orafine > NEW.orainizio AND M.orainizio < NewOraFine;
754
755 IF FOUND THEN
756 RAISE EXCEPTION 'Il cliente è già impegnato';
757 END IF;
758
759 IF TG_OP = 'INSERT' THEN
760 INSERT INTO Massaggio
761 VALUES(NEW.Cliente, NEW.DataMassaggio, NEW.OraInizio, NewOraFine, now(), massaggiatoreLibero, Salalibera, NEW.TipoMassaggio);
762 ELSE
763 UPDATE Massaggio SET
764 Cliente = NEW.Cliente,
765 DataMassaggio = NEW.DataMassaggio,
766 OraInizio = NEW.OraInizio,
767 OraFine = NewOraFine,
768 dataPrenotazione = now(),
769 massaggiatore = massaggiatoreLibero,
770 Sala = Salalibera,
771 TipoMassaggio = NEW.TipoMassaggio
772 WHERE
773 Cliente = OLD.Cliente AND
774 DataMassaggio = OLD.DataMassaggio AND
775 OraInizio = OLD.OraInizio AND
776 OraFine = OLD.OraFine;
777 END IF;
778 RETURN NEW;
779 END;
780$$ LANGUAGE plpgsql;
781
782CREATE TRIGGER richiesta_massaggio
783INSTEAD OF INSERT OR UPDATE ON prenotazione
784FOR EACH ROW
785EXECUTE PROCEDURE prenota();
786
787CREATE USER tonino WITH PASSWORD 'Pippo';
788GRANT ALL PRIVILEGES ON Prenotazione TO tonino;
789GRANT ALL PRIVILEGES ON Massaggio TO tonino;
790GRANT ALL PRIVILEGES ON RecapitoMassaggiatore TO tonino;
791GRANT ALL PRIVILEGES ON RecapitoCliente TO tonino;
792GRANT ALL PRIVILEGES ON RecapitoReceptionist TO tonino;
793GRANT ALL PRIVILEGES ON Specializzazione TO tonino;
794GRANT ALL PRIVILEGES ON TipoMassaggio TO tonino;
795GRANT ALL PRIVILEGES ON Cliente TO tonino;
796GRANT ALL PRIVILEGES ON Sala TO tonino;
797GRANT ALL PRIVILEGES ON Macchinario TO tonino;
798GRANT ALL PRIVILEGES ON Receptionist TO tonino;
799GRANT ALL PRIVILEGES ON Massaggiatore TO tonino;