L’architettura software dell’applicazione definita si posa su tre livelli logico-funzionali:
1. Livello Dati
2. Livello Applicazione 3. Livello Presentazione
Nel caso in esame viene sfruttata la potenza del DBMS Oracle 11g grazie al quale possiamo integrare una serie di procedure o funzioni PL/SQL grazie al quale possiamo rendere l’informazione quanto pi `u indipendente dai livelli superiori. Di seguito sono riportati alcuni fra Trigger&Stored Procedures utilizzati per rendere automatiche le regole di business applicate.
Livello Dati
-- Trigger che controlla la sovrapposizione di due spettacoli nella stessa sala di uno stesso cinema CREATE or replace TRIGGER sovrapposizione_spettacoli
BEFORE INSERT ON Spettacoli FOR EACH ROW
BEGIN DECLARE
CURSOR c1 IS SELECT data_e_ora, durata FROM mv_spettacoli_attivi s WHERE s.codice_sala=:NEW.sala;
inizio_spettacolo mv_spettacoli_attivi.data_e_ora%TYPE;
FETCH c1 INTO inizio_spettacolo, durata_spettacolo;
EXIT WHEN c1%NOTFOUND;
fine_spettacolo := inizio_spettacolo + (1 / 24 / (60 / durata_spettacolo));
-- DBMS_OUTPUT.put_line (’risultato:’);
-- DBMS_OUTPUT.put_line (’inizio spettacolo=’||inizio_spettacolo||’
-- durata=’||durata_spettacolo||’ fine=’||fine_spettacolo);
IF :NEW.data_e_ora < fine_spettacolo AND :NEW.data_e_ora > inizio_spettacolo THEN -- DBMS_OUTPUT.put_line(’errore’);
WHEN sovrapposizione THEN
-- DBMS_OUTPUT.put_line(’sovrapposizione spettacoli’);
RAISE_APPLICATION_ERROR( -20001,
’Sala occupata da un altro spettacolo’ );
Livello Dati : Triggers LATEX Tesina Basi Di Dati
-- Trigger che cancella il film da "prossimamente" se messo in programmazione per quel cinema CREATE OR REPLACE TRIGGER da_prossimamente_a_spettacolo
AFTER INSERT OR UPDATE ON Spettacoli FOR EACH ROW
BEGIN DECLARE
tmp_spettacolo Prossimamente.codice%TYPE;
tmp_cinema Cinema.codice%TYPE;
BEGIN
SELECT cinema INTO tmp_cinema
FROM Sale WHERE Sale.codice=:NEW.sala;
SELECT codice INTO tmp_spettacolo
FROM Prossimamente WHERE film=:NEW.film AND cinema=tmp_cinema;
DELETE FROM Prossimamente WHERE codice=tmp_spettacolo;
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
-- DBMS_OUTPUT.PUT_LINE(’NOT_FOUND’);
END;
END;
-- Trigger UNIQUE CONSTRAINT posti_riservati per prenotazioni attive CREATE OR REPLACE TRIGGER UQ_POST_RISE
BEFORE INSERT OR UPDATE ON Posti_riservati FOR EACH ROW
BEGIN DECLARE
n_posti NUMBER;
posto_occupato EXCEPTION;
BEGIN
SELECT count(*) INTO n_posti
FROM Posti_riservati PR JOIN Prenotazioni PRE ON PR.prenotazione=PRE.codice WHERE PR.posto=:NEW.posto AND PR.spettacolo=:NEW.spettacolo AND PRE.attiva=1;
IF n_posti>0 THEN RAISE POSTO_OCCUPATO;
END IF;
EXCEPTION
WHEN POSTO_OCCUPATO THEN
RAISE_APPLICATION_ERROR( -20002,
’Posto occupato da un altro spettatore’ );
END;
END;
Livello Dati : Triggers LATEX Tesina Basi Di Dati
-- Triger che calcola il prezzo totale della prenotazione in base al numero di posti prenotati CREATE OR REPLACE TRIGGER calcola_importo_prenotazione
AFTER INSERT ON posti_riservati FOR EACH ROW
BEGIN DECLARE
prezzo_posto cinema_dba.spettacoli.prezzo%TYPE;
BEGIN
SELECT prezzo INTO prezzo_posto FROM cinema_dba.spettacoli WHERE codice=:NEW.spettacolo;
UPDATE cinema_dba.prenotazioni SET importo=importo+prezzo_posto WHERE codice=:NEW.prenotazione;
END;
END;
Livello Dati : Stored Procedures LATEX Tesina Basi Di Dati
-- Procedura per l’archiviazione degli spettacoli con data antecedente a qu-ella odierna
-- La procedura sposta gli spettacoli nello storico, assieme alle prenotazioni ed ai posti riservati CREATE OR REPLACE PROCEDURE archivia_spettacoli
AS BEGIN DECLARE
CURSOR c_spettacoli IS SELECT codice FROM cinema_dba.spettacoli WHERE data_e_ora < SYSDATE;
codice_spettacolo cinema_dba.spettacoli.codice%TYPE;
BEGIN
OPEN c_spettacoli; -- apro il cursore degli spettacoli da spostare
-- inserisco tutti gli spettacoli non pi`u attivi (a partire da ieri) nella tabella storico INSERT INTO cinema_dba.storico_spettacoli
SELECT *
FROM cinema_dba.spettacoli WHERE data_e_ora < SYSDATE;
LOOP
FETCH c_spettacoli INTO codice_spettacolo; --leggo il codice_spettacolo i-esimo spettacolo EXIT WHEN c_spettacoli%NOTFOUND;
-- --- INSERT PHASE
-- inserisco nello storico le prenotazioni della i-esimo spettacolo INSERT INTO cinema_dba.storico_prenotazioni
SELECT *
FROM cinema_dba.prenotazioni WHERE spettacolo=codice_spettacolo;
---- inserisco nello storico i posti riservati dell’i-esimo spettacolo INSERT INTO cinema_dba.storico_posti_riservati
SELECT *
FROM cinema_dba.posti_riservati WHERE spettacolo=codice_spettacolo;
-- --- DELETE PHASE
DELETE FROM cinema_dba.prenotazioni WHERE spettacolo=codice_spettacolo;
DELETE FROM cinema_dba.posti_riservati WHERE spettacolo=codice_spettacolo;
END LOOP;
CLOSE c_spettacoli;
-- Cancello gli spettacoli non pi`u attivi
DELETE FROM cinema_dba.spettacoli WHERE data_e_ora < SYSDATE;
commit;
END;
END archivia_spettacoli;
Livello Dati : Stored Procedures LATEX Tesina Basi Di Dati
-- Procedura che controlla lo stato del pagamento
CREATE OR REPLACE PROCEDURE checkPagamento(idPrenotazione IN Prenotazioni.codice%TYPE) IS
BEGIN DECLARE
pagamento_success BOOLEAN;
BEGIN
pagamento_success := TRUE;
IF(pagamento_success=TRUE) THEN
UPDATE Prenotazioni SET pagato=1 WHERE codice=idPrenotazione;
END IF;
END;
END checkPagamento;
-- Procedura per l’inserimento di uno spettacolo con conseguente aggiornamento della vista materializzata CREATE PROCEDURE inserisci_spettacolo (codFilm IN spettacoli.film%TYPE,
codSala IN spettacoli.sala%TYPE, dataeora IN spettacoli.data_e_ora%TYPE, pr IN spettacoli.prezzo%TYPE, tipo IN
spettacoli.tipo_3d%TYPE) IS
BEGIN
INSERT INTO Spettacoli(codice, film, sala, data_e_ora, prezzo, tipo_3d) VALUES(spettacoli_seq.NEXTVAL, codFilm, codSala, dataeora, pr, tipo);
-- aggiorno la vista materializzata
DBMS_SNAPSHOT.REFRESH(’mv_spettacoli_attivi’);
END inserisci_spettacolo;
-- esempio di utilizzo:
-- ALTER SESSION SET nls_TIMESTAMP_format=’dd/mm/yyyy hh24:mi’;
-- begin
-- inserisci_spettacolo(550, 49, ’07/01/2017 12:25’, 5.00, 0);
-- end;
-- select * from mv_spettacoli_attivi;
-- select f.titolo from film f join spettacoli s on s.film=f.codice where f.codice=550;
Scheduler LATEX Tesina Basi Di Dati
-- SCHEDULE: Il 1o di ogni mese alle ore 06:00 BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
’inizio_mese’,
REPEAT_INTERVAL => ’FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=6;BYMINUTE=0;BYSECOND=0;’
);
END;
-- Il 1o di ogni mese alle 06:00 viene eseguita la stored procedure di archiviazione degli spettacoli BEGIN
DBMS_SCHEDULER.CREATE_JOB ( job_name => ’archivio_mensile’, schedule_name => ’inizio_mese’, job_type => ’STORED_PROCEDURE’, job_action => ’archivia_spettacoli’, enabled => TRUE
);
END;
ALTER SESSION SET nls_TIMESTAMP_format=’dd/mm/yyyy hh24:mi’;