· 5 years ago · May 16, 2020, 04:32 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, cancellandoli qualora non fosse possibile.
455*/
456CREATE OR REPLACE FUNCTION modifica_quantita_lettini() RETURNS TRIGGER AS $$
457 DECLARE
458 counter int;
459 r RECORD;
460 t RECORD;
461 SalaLibera varchar;
462 BEGIN
463 IF NEW.numerolettini < OLD.numerolettini THEN
464 EXECUTE format(
465 'CREATE TABLE IF NOT EXISTS MASSAGGI_CONTEMPORANEI (
466 cliente CHAR(16),
467 "data" DATE,
468 orainizio TIME,
469 orafine TIME,
470 PRIMARY KEY(cliente, data, orainizio, orafine)
471 );'
472 );
473
474 /*
475 DATAMASSAGGIO, ORAINIZIO, ORAFINE
476 DATA, ORA, TIPO
477 DATAMASSAGGIO, ORAINIZIO o ORAFINE, -1(ORAINIZIO) o +1(ORAFINE)
478 ORDINO PER DATA e se è uguale
479 ORDINO PER ORA
480 SE è un'ora di inizio sottraggo 1, altrimenti sommo 1
481 Se vado sotto 0 ritorno un'eccezione
482
483 In questo modo individuo per ogni istante il numero di macchinari/lettini/massaggiatori liberi
484 a seconda del filtro applicato ai massaggi selezionati.
485 */
486 counter := NEW.numerolettini;
487 FOR r IN
488 SELECT cliente, datamassaggio "data", OraInizio AS Ora, OI AS Orainizio, OraFine, tipo FROM (
489 SELECT Cliente, DataMassaggio, OraInizio, OraInizio AS OI, OraFine, -1 tipo FROM Massaggio
490 WHERE Sala = NEW.NumeroSala
491 AND (DataMassaggio > current_date OR
492 DataMassaggio = current_date AND
493 OraInizio > current_time)
494 UNION ALL
495 SELECT Cliente, DataMassaggio, OraFine, OraInizio, 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 ) AS O
501 ORDER BY "data", Ora
502 LOOP
503 counter := counter + r.Tipo;
504 IF r.Tipo = -1 THEN
505 INSERT INTO MASSAGGI_CONTEMPORANEI VALUES (r.cliente, r.data, r.orainizio, r.orafine);
506 ELSE
507 DELETE FROM MASSAGGI_CONTEMPORANEI MC WHERE MC.cliente = r.cliente
508 AND MC.data = r.data
509 AND MC.orainizio = r.oraInizio
510 AND MC.orafine = r.oraFine;
511 END IF;
512
513 IF counter < 0 AND r.Tipo = -1 THEN
514 FOR t IN SELECT * FROM MASSAGGI_CONTEMPORANEI
515 LOOP
516 SELECT S.NumeroSala INTO SalaLibera FROM Sala S
517 WHERE S.numeroSala <> NEW.numerosala AND
518 S.NumeroLettini > (
519 SELECT count(*) FROM Massaggio M
520 WHERE M.datamassaggio = t.data
521 AND M.orafine > t.orainizio AND M.orainizio < t.oraFine
522 AND M.Sala = S.numeroSala)
523 ORDER BY random() LIMIT 1;
524
525 IF FOUND THEN
526 UPDATE Massaggio SET Sala = SalaLibera
527 WHERE DataMassaggio = t.Data AND
528 OraInizio = t.OraInizio AND
529 Cliente = t.Cliente;
530
531 DELETE FROM MASSAGGI_CONTEMPORANEI MC WHERE MC.cliente = r.cliente
532 AND MC.data = r.data
533 AND MC.orainizio = r.oraInizio
534 AND MC.orafine = r.oraFine;
535 EXIT;
536 END IF;
537 END LOOP;
538 IF SalaLibera IS NULL THEN
539 EXECUTE format(
540 'DROP TABLE IF EXISTS MASSAGGI_CONTEMPORANEI;'
541 );
542
543 RAISE EXCEPTION 'Operazione non valida perché ci sono troppi massaggi prenotati collocati in questa sala.';
544 END IF;
545 END IF;
546 END LOOP;
547 END IF;
548 EXECUTE format(
549 'DROP TABLE IF EXISTS NUOVA_TABELLA;'
550 );
551 RETURN NEW;
552 END;
553$$ LANGUAGE plpgsql;
554
555CREATE TRIGGER check_update_quantita_lettini
556BEFORE UPDATE OF numerolettini ON sala
557FOR EACH ROW
558EXECUTE PROCEDURE modifica_quantita_lettini();
559
560
561/*
562 Alla modifica della quantità di macchinari per un dato tipo di macchinario verifica che la modifica non consista in una riduzione.
563 Se la modifica consiste in una riduzione del numero dei macchinari verifica che questi siano ancora sufficienti
564 per effettuare tutti i massaggi.
565 In caso contrario impedisce la modifica.
566*/
567CREATE OR REPLACE FUNCTION modifica_quantita_macchinari() RETURNS TRIGGER AS $$
568 DECLARE
569 counter int;
570 r RECORD;
571 BEGIN
572 IF NEW.quantita < OLD.quantita THEN
573 counter := NEW.quantita;
574 FOR r IN
575 SELECT datamassaggio "data", orainizio ora, tipo FROM (
576 SELECT DataMassaggio, OraInizio, -1 tipo FROM Massaggio
577 WHERE (DataMassaggio > current_date OR
578 DataMassaggio = current_date AND
579 OraInizio > current_time) AND tipomassaggio IN (
580 SELECT tipo FROM tipomassaggio
581 WHERE tipomassaggio.macchinario = NEW.tipo)
582 UNION ALL
583 SELECT DataMassaggio, OraFine, 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 ) AS O
590 ORDER BY "data", ora
591 LOOP
592 counter := counter + r.Tipo;
593 IF counter < 0 THEN
594 RAISE EXCEPTION 'Operazione non valida perché ci sono troppi massaggi prenotati che utilizzano il macchinario.';
595 END IF;
596 END LOOP;
597 END IF;
598
599 RETURN NEW;
600 END;
601$$ LANGUAGE plpgsql;
602
603CREATE TRIGGER check_update_quantita_macchinario
604BEFORE UPDATE OF quantita ON macchinario
605FOR EACH ROW
606EXECUTE PROCEDURE modifica_quantita_macchinari();
607
608/*
609 All'inserimento (o alla modifica del codice fiscale) di un cliente, massaggiatore o receptionist verifica
610 che non esista un altro cliente, massaggiatore o receptionist con lo stesso codice fiscale.
611
612 Il vincolo tra massaggiatore e receptionist deriva dalla tecnica utilizzata per tradurre la gerarchia.
613 Il vincolo tra cliente e dipendente (quindi massaggiatore e recepitionist) deriva dalla combinazione di
614 due ulteriori vincoli aggiuntivi:
615 "Se un cliente è anche un dipendente allora non può prenotare un massaggio durante il suo orario di lavoro."
616 "Gli orari di lavoro di tutti i dipendenti coincidono con gli orari di apertura del centro massaggi."
617*/
618CREATE OR REPLACE FUNCTION check_codice_fiscale() RETURNS TRIGGER AS $$
619 DECLARE
620 temp1 int;
621 temp2 int;
622 temp3 int;
623 BEGIN
624 SELECT count(*) INTO temp1 FROM Cliente
625 WHERE codicefiscale = NEW.codicefiscale;
626 SELECT count(*) INTO temp2 FROM Massaggiatore
627 WHERE codicefiscale = new.codicefiscale;
628 SELECT count(*) INTO temp3 FROM Receptionist
629 WHERE codicefiscale = new.codicefiscale;
630 IF (temp1 + temp2 + temp3 > 0) THEN
631 RAISE EXCEPTION 'Codice Fiscale già presente';
632 END IF;
633 RETURN NEW;
634 END;
635$$ LANGUAGE plpgsql;
636
637CREATE TRIGGER check_insert_codice_fiscale_cliente
638BEFORE INSERT OR UPDATE OF codicefiscale ON cliente
639FOR EACH ROW
640EXECUTE PROCEDURE check_codice_fiscale();
641
642CREATE TRIGGER check_insert_codice_fiscale_massaggiatore
643BEFORE INSERT OR UPDATE OF codicefiscale ON massaggiatore
644FOR EACH ROW
645EXECUTE PROCEDURE check_codice_fiscale();
646
647CREATE TRIGGER check_insert_codice_fiscale_receptionist
648BEFORE INSERT OR UPDATE OF codicefiscale ON receptionist
649FOR EACH ROW
650EXECUTE PROCEDURE check_codice_fiscale();
651
652
653/*
654 All'inserimento di una prenotazione (cioè di soli cliente, dataMassaggio, oraInizio, tipoMassaggio),
655 dopo aver verificato la correttezza della richiesta calcola i campi mancanti per l'inserimento in massaggio.
656 (Si ricorda che prenotazione è una vista basata su massaggio)
657
658 oraFine = oraInizio + durata (ottenuta dalla tabella tipoMassaggio)
659 dataPrenotazione = data e ora correnti (timestamp)
660 Massaggiatore = un massaggiatore libero nella fascia oraria desiderata in grado di effettuare quel tipo di massaggio richiesto
661 Sala = una sala con sufficienti lettini liberi nella fascia oraria desiderata
662 Macchinario = un macchinario libero nella fascia oraria desiderata
663*/
664CREATE OR REPLACE FUNCTION prenota() RETURNS TRIGGER AS $$
665 DECLARE
666 -- NEW = cliente, dataMassaggio, oraInizio, tipoMassaggio
667 durata int;
668 NewOraFine time;
669 MassaggiatoreLibero varchar;
670 MacchinarioRichiesto varchar;
671 SalaLibera varchar;
672 BEGIN
673 -- Selezione durata del tipoMassaggio
674 SELECT tipomassaggio.durata INTO durata FROM tipomassaggio WHERE tipo = NEW.tipomassaggio;
675 IF NOT FOUND THEN
676 RAISE EXCEPTION 'Il tipo massaggio indicato non è disponibile';
677 END IF;
678
679 -- Calcola l'ora di fine
680 NewOraFine := NEW.orainizio + interval '1m' * durata;
681
682
683 -- Verifica massaggiatori disponibili per tutta la durata
684
685 -- Seleziona tutti i massaggiatori che sanno fare il tipo massaggio richiesto
686 SELECT Risultato.massaggiatore INTO massaggiatoreLibero from (
687 SELECT S.massaggiatore FROM Specializzazione S WHERE S.tipomassaggio = NEW.tipomassaggio
688 EXCEPT
689 -- Seleziona tutti i massaggiatori che sono impegnati in quel giorno in quell intervallo orario
690 SELECT M.massaggiatore FROM Massaggio M
691 WHERE M.dataMassaggio = NEW.dataMassaggio AND
692 M.orafine > NEW.orainizio AND M.orainizio < NewOraFine) AS foo
693 ORDER BY random() LIMIT 1;
694
695 IF NOT FOUND THEN
696 RAISE EXCEPTION 'Non c è nessun massaggiatore disponibile';
697 END IF;
698
699 -- Verifica se è richiesto il macchinario per quel tipo massaggio
700 SELECT T.macchinario INTO MacchinarioRichiesto FROM TipoMassaggio T
701 WHERE T.tipo = NEW.tipomassaggio;
702
703
704 IF MacchinarioRichiesto IS NOT NULL THEN
705 -- Verifica macchinari disponibili per tutta la durata
706 -- Prendi tutti i macchinari la cui quantità è maggiore delle volte che sono usati nell'intervallo orario desiderato
707 PERFORM macchinario.tipo FROM macchinario
708 WHERE macchinario.tipo = MacchinarioRichiesto
709 AND macchinario.quantita >
710 -- Le volte che lo stesso macchinario è usato
711 (select count(*) FROM massaggio M
712 -- Nel giorno scelto
713 WHERE M.datamassaggio = NEW.datamassaggio
714 -- È impegnato in quell'ora
715 AND M.orafine > NEW.orainizio AND M.orainizio < NewOraFine
716 -- Il macchinario utilizzato è quello richiesto
717 -- Il tipo massaggio prevede di utilizzare il macchinario richiesto
718 AND M.tipomassaggio IN (
719 SELECT tipo FROM tipomassaggio
720 WHERE tipomassaggio.macchinario = MacchinarioRichiesto)
721 );
722 IF NOT FOUND THEN
723 RAISE EXCEPTION 'Non c è nessun macchinario disponibile';
724 END IF;
725 END IF;
726
727 -- Verifica sale con lettini disponibili per tutta la durata
728 -- Prendi tutte le sale i cui lettini sono maggiori di quelli usati nell'intervallo orario desiderato
729 SELECT sala.numeroSala INTO SalaLibera FROM sala
730 WHERE sala.numeroLettini >
731 ( -- Sale usate nell'intervallo desiderato
732 SELECT count(*) FROM Massaggio M
733 WHERE M.datamassaggio = NEW.datamassaggio
734 AND M.orafine > NEW.orainizio AND M.orainizio < NewOraFine
735 AND M.Sala = sala.numeroSala
736 )
737 ORDER BY random() LIMIT 1;
738
739 IF NOT FOUND THEN
740 RAISE EXCEPTION 'Non c è nessuna sala disponibile';
741 END IF;
742
743 -- Verifica cliente non impegnato in quell'intervallo
744 PERFORM M.cliente FROM massaggio M
745 WHERE M.cliente = NEW.cliente
746 AND M.datamassaggio = NEW.datamassaggio
747 AND M.orafine > NEW.orainizio AND M.orainizio < NewOraFine;
748
749 IF FOUND THEN
750 RAISE EXCEPTION 'Il cliente è già impegnato';
751 END IF;
752
753 IF TG_OP = 'INSERT' THEN
754 INSERT INTO Massaggio
755 VALUES(NEW.Cliente, NEW.DataMassaggio, NEW.OraInizio, NewOraFine, now(), massaggiatoreLibero, Salalibera, NEW.TipoMassaggio);
756 ELSE
757 UPDATE Massaggio SET
758 Cliente = NEW.Cliente,
759 DataMassaggio = NEW.DataMassaggio,
760 OraInizio = NEW.OraInizio,
761 OraFine = NewOraFine,
762 dataPrenotazione = now(),
763 massaggiatore = massaggiatoreLibero,
764 Sala = Salalibera,
765 TipoMassaggio = NEW.TipoMassaggio
766 WHERE
767 Cliente = OLD.Cliente AND
768 DataMassaggio = OLD.DataMassaggio AND
769 OraInizio = OLD.OraInizio AND
770 OraFine = OLD.OraFine;
771 END IF;
772 RETURN NEW;
773 END;
774$$ LANGUAGE plpgsql;
775
776CREATE TRIGGER richiesta_massaggio
777INSTEAD OF INSERT OR UPDATE ON prenotazione
778FOR EACH ROW
779EXECUTE PROCEDURE prenota();
780
781CREATE USER tonino WITH PASSWORD 'Pippo';
782GRANT ALL PRIVILEGES ON Prenotazione TO tonino;
783GRANT ALL PRIVILEGES ON Massaggio TO tonino;
784GRANT ALL PRIVILEGES ON RecapitoMassaggiatore TO tonino;
785GRANT ALL PRIVILEGES ON RecapitoCliente TO tonino;
786GRANT ALL PRIVILEGES ON RecapitoReceptionist TO tonino;
787GRANT ALL PRIVILEGES ON Specializzazione TO tonino;
788GRANT ALL PRIVILEGES ON TipoMassaggio TO tonino;
789GRANT ALL PRIVILEGES ON Cliente TO tonino;
790GRANT ALL PRIVILEGES ON Sala TO tonino;
791GRANT ALL PRIVILEGES ON Macchinario TO tonino;
792GRANT ALL PRIVILEGES ON Receptionist TO tonino;
793GRANT ALL PRIVILEGES ON Massaggiatore TO tonino;