· 5 years ago · May 12, 2020, 07:42 PM
1--create user tonino with password 'Pippo';
2
3DROP TABLE IF EXISTS Massaggio CASCADE;
4DROP TABLE IF EXISTS RecapitoMassaggiatore CASCADE;
5DROP TABLE IF EXISTS RecapitoCliente CASCADE;
6DROP TABLE IF EXISTS RecapitoReceptionist CASCADE;
7DROP TABLE IF EXISTS Specializzazione CASCADE;
8DROP TABLE IF EXISTS TipoMassaggio CASCADE;
9DROP TABLE IF EXISTS Cliente CASCADE;
10DROP TABLE IF EXISTS Sala CASCADE;
11DROP TABLE IF EXISTS Macchinario CASCADE;
12DROP TABLE IF EXISTS Receptionist CASCADE;
13DROP TABLE IF EXISTS Massaggiatore CASCADE;
14
15
16CREATE TABLE Cliente (
17 CodiceFiscale CHAR(16) PRIMARY KEY CHECK( CHAR_LENGTH(CodiceFiscale) = 16 ),
18 Cognome VARCHAR(20) NOT NULL,
19 Nome VARCHAR(20) NOT NULL
20);
21
22
23CREATE TABLE Sala(
24 NumeroSala VARCHAR(20) PRIMARY KEY,
25 NumeroLettini SMALLINT NOT NULL CHECK( NumeroLettini >= 0 )
26);
27
28
29CREATE TABLE Macchinario(
30 Tipo VARCHAR(20) PRIMARY KEY,
31 Quantita SMALLINT NOT NULL CHECK( Quantita >= 0 )
32);
33
34
35CREATE TABLE Receptionist(
36 CodiceFiscale CHAR(16) PRIMARY KEY CHECK( CHAR_LENGTH(CodiceFiscale) = 16 ),
37 Cognome VARCHAR(20) NOT NULL,
38 Nome VARCHAR(20) NOT NULL,
39 via VARCHAR(20) NOT NULL,
40 CAP CHAR(5) NOT NULL CHECK( CHAR_LENGTH(CAP) = 5 ),
41 Citta VARCHAR(20) NOT NULL,
42 Stipendio DECIMAL(8,2) NOT NULL
43);
44
45
46CREATE TABLE Massaggiatore(
47 CodiceFiscale CHAR(16) PRIMARY KEY CHECK( CHAR_LENGTH(CodiceFiscale) = 16 ),
48 Cognome VARCHAR(20) NOT NULL,
49 Nome VARCHAR(20) NOT NULL,
50 Via VARCHAR(20) NOT NULL,
51 CAP CHAR(5) NOT NULL CHECK( CHAR_LENGTH(CAP) = 5 ),
52 Citta VARCHAR(20) NOT NULL,
53 StipendioBase DECIMAL(8,2) NOT NULL
54);
55
56
57CREATE TABLE TipoMassaggio(
58 Tipo VARCHAR(20) PRIMARY KEY,
59 Prezzo DECIMAL(8,2) NOT NULL,
60 Durata SMALLINT NOT NULL,
61 Macchinario VARCHAR(20) NULL,
62
63 FOREIGN KEY (Macchinario) REFERENCES Macchinario(Tipo)
64 ON DELETE CASCADE
65 ON UPDATE CASCADE
66);
67
68
69CREATE TABLE Specializzazione(
70 Massaggiatore CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Massaggiatore) = 16 ),
71 TipoMassaggio VARCHAR(20) NOT NULL,
72
73 PRIMARY KEY (Massaggiatore, TipoMassaggio),
74 FOREIGN KEY (Massaggiatore) REFERENCES Massaggiatore(CodiceFiscale)
75 ON DELETE CASCADE
76 ON UPDATE CASCADE
77 DEFERRABLE INITIALLY DEFERRED,
78 FOREIGN KEY (TipoMassaggio) REFERENCES TipoMassaggio(Tipo)
79 ON DELETE CASCADE
80 ON UPDATE CASCADE
81);
82
83
84CREATE TABLE RecapitoCliente(
85 Cliente CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Cliente) = 16 ),
86 Telefono VARCHAR(13) NOT NULL,
87
88 PRIMARY KEY (Cliente, Telefono),
89 FOREIGN KEY (Cliente) REFERENCES Cliente(CodiceFiscale)
90 ON DELETE CASCADE
91 ON UPDATE CASCADE
92 DEFERRABLE INITIALLY DEFERRED
93);
94
95
96CREATE TABLE RecapitoMassaggiatore(
97 Massaggiatore CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Massaggiatore) = 16 ),
98 Telefono VARCHAR(13) NOT NULL,
99
100 PRIMARY KEY (Massaggiatore, Telefono),
101 FOREIGN KEY (Massaggiatore) REFERENCES Massaggiatore(CodiceFiscale)
102 ON DELETE CASCADE
103 ON UPDATE CASCADE
104 DEFERRABLE INITIALLY DEFERRED
105);
106
107
108CREATE TABLE RecapitoReceptionist(
109 Receptionist CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Receptionist) = 16 ),
110 Telefono VARCHAR(13) NOT NULL,
111
112 PRIMARY KEY (Receptionist, Telefono),
113 FOREIGN KEY (Receptionist) REFERENCES Receptionist(CodiceFiscale)
114 ON DELETE CASCADE
115 ON UPDATE CASCADE
116 DEFERRABLE INITIALLY DEFERRED
117);
118
119
120CREATE TABLE Massaggio(
121 Cliente CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Cliente) = 16 ),
122 DataMassaggio DATE NOT NULL CHECK( DataMassaggio > CURRENT_DATE OR DataMassaggio = CURRENT_DATE AND OraInizio > CURRENT_TIME ),
123 OraInizio TIME NOT NULL CHECK( OraInizio >= TIME '09:00:00' ),
124 OraFine TIME NOT NULL CHECK( OraFine <= TIME '21:00:00' ), -- Trigger per altro costraint
125 DataPrenotazione TIMESTAMP NOT NULL,
126 Massaggiatore CHAR(16) NOT NULL CHECK( CHAR_LENGTH(Massaggiatore) = 16 ),
127 Sala VARCHAR(20) NOT NULL,
128 TipoMassaggio VARCHAR(20) NOT NULL,
129
130 PRIMARY KEY (Cliente, DataMassaggio, OraInizio),
131 FOREIGN KEY (Massaggiatore) REFERENCES Massaggiatore(CodiceFiscale)
132 ON DELETE CASCADE
133 ON UPDATE CASCADE,
134 FOREIGN KEY (Sala) REFERENCES Sala(NumeroSala)
135 ON DELETE CASCADE
136 ON UPDATE CASCADE,
137 FOREIGN KEY (TipoMassaggio) REFERENCES TipoMassaggio(Tipo)
138 ON DELETE CASCADE
139 ON UPDATE CASCADE
140);
141
142CREATE OR REPLACE FUNCTION cancellazione_massaggiatore() RETURNS TRIGGER AS $$
143 DECLARE
144 r massaggio%rowtype;
145 MassaggiatoreLibero varchar;
146 BEGIN
147
148 FOR r IN
149 SELECT * FROM Massaggio
150 WHERE Massaggiatore = OLD.codicefiscale
151 LOOP
152 SELECT Massaggiatore INTO MassaggiatoreLibero FROM (
153 SELECT S.Massaggiatore FROM Specializzazione S
154 WHERE S.Massaggiatore <> OLD.codicefiscale AND S.TipoMassaggio = r.TipoMassaggio
155 EXCEPT
156 SELECT M.Massaggiatore FROM Massaggio M
157 WHERE M.datamassaggio = r.datamassaggio
158 AND M.oraFine>r.oraInizio AND M.oraInizio<r.oraFine ) AS foo
159 ORDER BY random() LIMIT 1;
160
161 IF FOUND THEN
162 UPDATE Massaggio SET Massaggiatore = MassaggiatoreLibero
163 WHERE DataMassaggio = r.DataMassaggio AND
164 OraInizio = r.OraInizio AND
165 Cliente = r.Cliente;
166 ELSE
167 RAISE NOTICE 'Non è stato possibile sostituire il massaggiatore. La prenotazione del cliente % per il giorno % alle ore %
168sarà cancellata', r.cliente, r.datamassaggio, r.orainizio;
169 END IF;
170 END LOOP;
171
172 RETURN OLD;
173 END;
174$$ LANGUAGE plpgsql;
175
176CREATE TRIGGER cancella_massaggiatore BEFORE DELETE
177ON massaggiatore
178FOR EACH ROW
179EXECUTE PROCEDURE cancellazione_massaggiatore();
180
181CREATE OR REPLACE FUNCTION cancellazione_sala() RETURNS TRIGGER AS $$
182 DECLARE
183 r massaggio%rowtype;
184 SalaLibera varchar;
185 BEGIN
186
187 FOR r IN
188 SELECT * FROM Massaggio
189 WHERE Sala = OLD.numerosala
190 LOOP
191 SELECT S.NumeroSala INTO SalaLibera FROM Sala S
192 WHERE S.NumeroSala <> OLD.numerosala
193 AND (S.numeroSala NOT IN (SELECT DISTINCT sala FROM Massaggio)
194 OR S.NumeroLettini > (
195 SELECT count(*) FROM Massaggio M
196 WHERE M.datamassaggio = r.datamassaggio
197 AND M.orafine > r.orainizio AND M.orainizio < r.oraFine
198 AND M.Sala = S.numeroSala))
199 ORDER BY random() LIMIT 1;
200
201 IF FOUND THEN
202 UPDATE Massaggio SET Sala = SalaLibera
203 WHERE DataMassaggio = r.DataMassaggio AND
204 OraInizio = r.OraInizio AND
205 Cliente = r.Cliente;
206 ELSE
207 RAISE NOTICE 'Non è stato possibile sostituire la sala. La prenotazione del cliente % per il giorno % alle ore %
208sarà cancellata', r.cliente, r.datamassaggio, r.orainizio;
209 END IF;
210 END LOOP;
211
212 RETURN OLD;
213 END;
214$$ LANGUAGE plpgsql;
215
216CREATE TRIGGER cancella_sala BEFORE DELETE
217ON sala
218FOR EACH ROW
219EXECUTE PROCEDURE cancellazione_sala();
220
221CREATE OR REPLACE FUNCTION modifica_quantita_lettini() RETURNS TRIGGER AS $$
222 DECLARE
223 counter int;
224 r RECORD;
225 t RECORD;
226 SalaLibera varchar;
227 BEGIN
228 IF NEW.numerolettini < OLD.numerolettini THEN
229
230
231 EXECUTE format(
232 'CREATE TABLE IF NOT EXISTS NUOVA_TABELLA (
233 cliente CHAR(16),
234 "data" DATE,
235 ora TIME,
236 orainizio TIME,
237 orafine TIME,
238 PRIMARY KEY(cliente, data, ora)
239 );'
240 );
241 counter := NEW.numerolettini;
242 FOR r IN
243 SELECT cliente, datamassaggio "data", Ora, Orainizio, OraFine, tipo FROM (
244 SELECT Cliente, DataMassaggio, OraInizio, OraInizio, OraFine, -1 tipo FROM Massaggio
245 WHERE Sala = NEW.NumeroSala
246 UNION
247 SELECT Cliente, DataMassaggio, OraFine, OraInizio, OraFine, 1 tipo FROM Massaggio
248 WHERE Sala = NEW.NumeroSala
249 ) AS O
250 ORDER BY "data", Ora
251 LOOP
252
253 IF r.Tipo = -1 THEN
254 INSERT INTO NUOVA_TABELLA VALUES (r.cliente, r.data, r.orainizio, r.orafine);
255 ELSE
256 DELETE FROM NUOVA_TABELLA WHERE cliente = r.cliente
257 AND "data" = r.data
258 AND orainizio = r.oraInizio
259 AND orafine = r.oraFine;
260 END IF;
261
262 counter := counter + r.Tipo;
263 IF counter < 0 THEN
264 FOR t IN SELECT * FROM NUOVA_TABELLA
265 LOOP
266 SELECT S.NumeroSala INTO SalaLibera FROM Sala S
267 WHERE S.NumeroLettini > (
268 SELECT count(*) FROM Massaggio M
269 WHERE M.datamassaggio = t.datamassaggio
270 AND M.orafine > t.orainizio AND M.orainizio < t.oraFine
271 AND M.Sala = S.numeroSala)
272 ORDER BY random() LIMIT 1;
273
274 IF FOUND THEN
275 UPDATE Massaggio SET Sala = SalaLibera
276 WHERE DataMassaggio = t.Data AND
277 OraInizio = t.OraInizio AND
278 Cliente = t.Cliente;
279 EXIT;
280 END IF;
281 END LOOP;
282
283 IF NOT FOUND THEN
284 EXECUTE format(
285 'DROP TABLE IF EXISTS NUOVA_TABELLA;'
286 );
287
288 RAISE EXCEPTION 'Operazione non valida perché ci sono troppi massaggi prenotati collocati in questa sala.';
289 END IF;
290 END IF;
291 END LOOP;
292 END IF;
293 EXECUTE format(
294 'DROP TABLE IF EXISTS NUOVA_TABELLA;'
295 );
296 RETURN NEW;
297 END;
298$$ LANGUAGE plpgsql;
299
300CREATE TRIGGER checkUpdateQuantitaLettini
301BEFORE UPDATE OF numerolettini ON sala
302FOR EACH ROW
303EXECUTE PROCEDURE modifica_quantita_lettini();
304
305CREATE OR REPLACE FUNCTION modifica_quantita_macchinari() RETURNS TRIGGER AS $$
306 DECLARE
307 counter int;
308 r RECORD;
309 BEGIN
310 /*
311 DATAMASSAGGIO, ORAINIZIO, ORAFINE
312 DATA, ORA, TIPO
313 DATAMASSAGGIO, ORAINIZIO o ORAFINE, -1(ORAINIZIO) o +1(ORAFINE)
314 ORDINO PER DATA e se è uguale
315 ORDINO PER ORA
316 SE è un'ora di inizio sottraggo 1, altrimenti sommo 1
317 Se vado sotto 0 ritorno un'eccezione
318 */
319 IF NEW.quantita < OLD.quantita THEN
320 counter := NEW.quantita;
321 FOR r IN
322 SELECT datamassaggio "data", orainizio ora, tipo FROM (
323 SELECT DataMassaggio, OraInizio, -1 tipo FROM Massaggio
324 WHERE tipomassaggio IN (
325 SELECT tipo FROM tipomassaggio
326 WHERE tipomassaggio.macchinario = 'clgvqgssoql')
327 UNION
328 SELECT DataMassaggio, OraFine, 1 tipo FROM Massaggio
329 WHERE tipomassaggio IN (
330 SELECT tipo FROM tipomassaggio
331 WHERE tipomassaggio.macchinario = 'clgvqgssoql')
332 ) AS O
333 ORDER BY "data", ora
334 LOOP
335 counter := counter + r.Tipo;
336 IF counter < 0 THEN
337 RAISE EXCEPTION 'Operazione non valida perché ci sono troppi massaggi prenotati che utilizzano il macchinario.';
338 END IF;
339 END LOOP;
340 END IF;
341
342 RETURN NEW;
343 END;
344$$ LANGUAGE plpgsql;
345
346CREATE TRIGGER checkUpdateQuantitaMacchinario
347BEFORE UPDATE OF quantita ON macchinario
348FOR EACH ROW
349EXECUTE PROCEDURE modifica_quantita_macchinari();
350
351CREATE OR REPLACE FUNCTION checkcodicefiscale() RETURNS TRIGGER AS $$
352 DECLARE
353 temp1 int;
354 temp2 int;
355 temp3 int;
356 temp4 int;
357 BEGIN
358 SELECT count(*) INTO temp1 FROM Cliente
359 WHERE codicefiscale = NEW.codicefiscale;
360 SELECT count(*) INTO temp2 FROM Massaggiatore
361 WHERE codicefiscale = new.codicefiscale;
362 SELECT count(*) INTO temp3 FROM Receptionist
363 WHERE codicefiscale = new.codicefiscale;
364 temp4 = temp1 + temp2 + temp3;
365 IF (temp4 > 0) THEN
366 RAISE EXCEPTION 'Codice Fiscale già presente';
367 END IF;
368 RETURN NEW;
369 END;
370$$ LANGUAGE plpgsql;
371
372CREATE TRIGGER checkInsertCodiceFiscaleCliente
373BEFORE INSERT OR UPDATE OF codicefiscale ON cliente
374FOR EACH ROW
375EXECUTE PROCEDURE checkcodicefiscale();
376
377CREATE TRIGGER checkInsertCodiceFiscaleMassaggiatore
378BEFORE INSERT OR UPDATE OF codicefiscale ON massaggiatore
379FOR EACH ROW
380EXECUTE PROCEDURE checkcodicefiscale();
381
382CREATE TRIGGER checkInsertCodiceFiscaleReceptionist
383BEFORE INSERT OR UPDATE OF codicefiscale ON receptionist
384FOR EACH ROW
385EXECUTE PROCEDURE checkcodicefiscale();
386
387-- Vista massaggi per tonino
388-- cliente, dataMassaggio, oraInizio, oraFine, tipoMassaggio
389CREATE OR REPLACE VIEW Prenotazione AS
390SELECT Cliente, DataMassaggio, OraInizio, TipoMassaggio
391FROM Massaggio;
392
393-- NEW = cliente, dataMassaggio, oraInizio, tipoMassaggio
394CREATE OR REPLACE FUNCTION prenota() RETURNS TRIGGER AS $$
395 DECLARE
396 durata int;
397 NewOraFine time;
398 MassaggiatoreLibero varchar;
399 MacchinarioRichiesto varchar;
400 SalaLibera varchar;
401 BEGIN
402 -- Selezione durata del tipoMassaggio
403 IF EXISTS(SELECT tipomassaggio.durata FROM tipomassaggio WHERE tipo = NEW.tipomassaggio) THEN
404 SELECT tipomassaggio.durata INTO durata FROM tipomassaggio WHERE tipo = NEW.tipomassaggio;
405 ELSE
406 RAISE EXCEPTION 'Il tipo massaggio indicato non è disponibile';
407 END IF;
408
409 -- Calcola l'ora di fine
410 NewOraFine := NEW.orainizio + interval '1m' * durata;
411
412
413 -- Verifica massaggiatori disponibili per tutta la durata
414
415 -- Seleziona tutti i massaggiatori che sanno fare il tipo massaggio richiesto
416 SELECT Risultato.massaggiatore INTO massaggiatoreLibero from (
417 SELECT S.massaggiatore FROM Specializzazione S WHERE S.tipomassaggio = NEW.tipomassaggio
418 EXCEPT
419 -- Seleziona tutti i massaggiatori che sono impegnati in quel giorno in quell intervallo orario
420 SELECT M.massaggiatore FROM Massaggio M
421 WHERE M.dataMassaggio = NEW.dataMassaggio AND
422 M.orafine > NEW.orainizio AND M.orainizio < NewOraFine) AS Risultato
423 ORDER BY random()
424 LIMIT 1;
425
426 IF NOT FOUND THEN
427 RAISE EXCEPTION 'Non c è nessun massaggiatore disponibile';
428 END IF;
429
430 -- Verifica se è richiesto il macchinario per quel tipo massaggio
431 SELECT T.macchinario INTO MacchinarioRichiesto FROM TipoMassaggio T
432 WHERE T.tipo = NEW.tipomassaggio;
433
434
435 IF MacchinarioRichiesto IS NOT NULL THEN
436 -- Verifica macchinari disponibili per tutta la durata
437 -- Prendi tutti i macchinari la cui quantità è maggiore delle volte che sono usati nell'intervallo orario desiderato
438 PERFORM macchinario.tipo FROM macchinario
439 WHERE macchinario.tipo = MacchinarioRichiesto
440 AND macchinario.quantita >
441 -- Le volte che lo stesso macchinario è usato
442 (select count(*) FROM massaggio M
443 -- Nel giorno scelto
444 WHERE M.datamassaggio = NEW.datamassaggio
445 -- È impegnato in quell'ora
446 AND M.orafine > NEW.orainizio AND M.orainizio < NewOraFine
447 -- Il macchinario utilizzato è quello richiesto
448 -- Il tipo massaggio prevede di utilizzare il macchinario richiesto
449 AND M.tipomassaggio IN (
450 SELECT tipo FROM tipomassaggio
451 WHERE tipomassaggio.macchinario = MacchinarioRichiesto)
452 );
453 IF NOT FOUND THEN
454 RAISE EXCEPTION 'Non c è nessun macchinario disponibile';
455 END IF;
456 END IF;
457
458 -- Verifica sale con lettini disponibili per tutta la durata
459 -- Prendi tutte le sale i cui lettini sono maggiori di quelli usati nell''intervallo orario desiderato
460 SELECT sala.numeroSala INTO SalaLibera FROM sala
461 WHERE sala.numeroLettini >
462 ( -- Sale usate nell'intervallo desiderato
463 SELECT count(*) FROM Massaggio M
464 WHERE M.datamassaggio = NEW.datamassaggio
465 AND M.orafine > NEW.orainizio AND M.orainizio < NewOraFine
466 AND M.Sala = sala.numeroSala
467 )
468 ORDER BY random()
469 LIMIT 1;
470
471 IF NOT FOUND THEN
472 RAISE EXCEPTION 'Non c è nessuna sala disponibile';
473 END IF;
474
475 -- Verifica cliente con unico massaggio in quell'intervallo
476 PERFORM M.cliente FROM massaggio M
477 WHERE M.cliente = NEW.cliente
478 AND M.datamassaggio = NEW.datamassaggio
479 AND M.orafine > NEW.orainizio AND M.orainizio < NewOraFine;
480
481 IF FOUND THEN
482 RAISE EXCEPTION 'Il cliente è già impegnato';
483 END IF;
484
485 IF TG_OP = 'INSERT' THEN
486 INSERT INTO Massaggio
487 VALUES(NEW.Cliente, NEW.DataMassaggio, NEW.OraInizio, NewOraFine, now(), massaggiatoreLibero, Salalibera, NEW.TipoMassaggio);
488 ELSE
489 UPDATE Massaggio SET
490 Cliente = NEW.Cliente,
491 DataMassaggio = NEW.DataMassaggio,
492 OraInizio = NEW.OraInizio,
493 OraFine = NewOraFine,
494 dataPrenotazione = now(),
495 massaggiatore = massaggiatoreLibero,
496 Sala = Salalibera,
497 TipoMassaggio = NEW.TipoMassaggio
498 WHERE
499 Cliente = OLD.Cliente AND
500 DataMassaggio = OLD.DataMassaggio AND
501 OraInizio = OLD.OraInizio AND
502 OraFine = OLD.OraFine;
503 END IF;
504 RETURN NEW;
505 END;
506$$ LANGUAGE plpgsql;
507
508CREATE TRIGGER richiesta_massaggio INSTEAD OF INSERT OR UPDATE
509ON prenotazione
510FOR EACH ROW
511EXECUTE PROCEDURE prenota();
512
513grant all privileges on Prenotazione to tonino;
514grant all privileges on Massaggio to tonino;
515grant all privileges on RecapitoMassaggiatore to tonino;
516grant all privileges on RecapitoCliente to tonino;
517grant all privileges on RecapitoReceptionist to tonino;
518grant all privileges on Specializzazione to tonino;
519grant all privileges on TipoMassaggio to tonino;
520grant all privileges on Cliente to tonino;
521grant all privileges on Sala to tonino;
522grant all privileges on Macchinario to tonino;
523grant all privileges on Receptionist to tonino;
524grant all privileges on Massaggiatore to tonino;