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