Vedremo in questa lezione come utilizzare il database che abbiamo
impostato nella lezione precedente.
Innanzitutto decidiamo di creare una stored function per
il calcolo del prezzo di una camera, dato il periodo di interesse e il
tipo di camera:
DELIMITER //
CREATE FUNCTION prezzo(arrivo date, partenza date,
tipo enum('singola','doppia','matrimoniale','tripla'))
RETURNS DECIMAL(7,2)
READS SQL DATA
BEGIN
DECLARE varData DATE;
DECLARE varTotale DECIMAL(7,2) default 0;
DECLARE varPrezzo DECIMAL(7,2);
SET varData = arrivo;
WHILE varData < partenza DO
SELECT prezzo INTO varPrezzo FROM prezzi
WHERE varData BETWEEN periodoDal AND periodoAl
AND tipoCamera = tipo;
SET varTotale = varTotale + varPrezzo;
SET varData = DATE_ADD(varData,INTERVAL 1 day);
END WHILE;
return varTotale;
END; //
DELIMITER ;
Come vedete la funzione, che si chiama 'prezzo', accetta come parametri
in input le date di arrivo e partenza e il tipo di camera, che ricalca lo
stesso tipo di campo presente nella tabella relativa.
Passiamo ora alla fase delle prenotazioni: il problema più importante da
risolvere è quello di cercare sul database le disponibilità in base alle
prenotazioni già presenti e alle richieste che riceviamo. Prima di tutto
però dobbiamo accertarci che, nel momento in cui andiamo ad effettuare
una prenotazione, nessun altro possa intervenire sulle tabelle, per cui
dovremo effettuare un LOCK. Quindi effettueremo la query di ricerca.
Immaginiamo che ci sia stata richiesta la disponibilità di una camera
matrimoniale per la settimana dal 3 al 10 giugno:
LOCK TABLES camere c READ, prenotazioni p READ,
supplementi READ, prezzi READ, clienti WRITE,
prenotazioni WRITE, supplementi_prenotati WRITE;
SET @inizioPeriodo = '2006-06-03';
SET @finePeriodo = '2006-06-10';
SELECT c.* FROM camere c
WHERE tipo = 'matrimoniale'
AND NOT EXISTS
(SELECT * FROM prenotazioni p WHERE p.camera = c.numero
AND (p.periodoDal < @finePeriodo and @inizioPeriodo < p.periodoAl)
);
La ricerca di disponibilità viene effettuata attraverso una
subquery correlata che, per ogni camera del tipo che ci
interessa, va a verificare che non esistano prenotazioni che si
intersecano con il periodo richiesto (per verificare le intersezioni
bisogna confrontare le date di inizio con quelle di fine periodo). Questa
query ci restituirà l'elenco delle camere che hanno disponibilità nel
periodo richiesto, dandoci la possibilità di scegliere quale assegnare al
cliente che ha effettuato la richiesta.
Ipotizziamo ora di scegliere la camera 201, e che il cliente abbia
richiesto il trattamento di pensione completa, nonchè la presenza della
culla in camera. Dovremo quindi inserire i dati del cliente in tabella,
poi registrare la prenotazione calcolandone il prezzo totale:
INSERT INTO clienti SET
nominativo = 'Rossi Mario',
indirizzo = 'via Manzi, 2 - 00153 Roma',
telefono = '06 86123920';
SELECT LAST_INSERT_ID() INTO @codCliente;
Inseriti i dati del cliente in tabella, utilizziamo la funzione
LAST_INSERT_ID() per recuperare l'identificativo e assegnarlo ad una
variabile, quindi procediamo a calcolare il prezzo totale:
SELECT prezzo(@inizioPeriodo,@finePeriodo,'matrimoniale') + (SELECT SUM(prezzo * DATEDIFF(@finePeriodo,@inizioPeriodo)) FROM supplementi WHERE codice IN (1,5)) INTO @prezzoTotale;
Questa query effettua la somma di due valori: il primo è il risultato
della chiamata alla stored function prezzo che abbiamo definito
in precedenza, che definisce il prezzo base della camera, mentre il
secondo calcola il prezzo dei supplementi leggendo dalla tabella omonima
i valori relativi ai codici 1 e 5 (culla e pensione completa) e
moltiplicandoli per i giorni di permanenza, dati dalla differenza fra le
due date (in questa seconda query la keyword 'prezzo' si riferisce alla
colonna della tabella supplementi). Il risultato complessivo va nella
variabile @prezzoTotale, che conterrà quindi il valore 756, dato dal
prezzo della matrimoniale (95 * 7) più i supplementi pari a (8+5)*7.
Possiamo quindi ora utilizzare tale valore per inserire la prenotazione:
INSERT INTO prenotazioni VALUES (null, @inizioPeriodo,
@finePeriodo, 202, @codCliente, @prezzoTotale);
SELECT LAST_INSERT_ID() INTO @idPrenotazione;
INSERT INTO supplementi_prenotati VALUES
(@idPrenotazione,1),
(@idPrenotazione,5);
UNLOCK TABLES;
Abbiamo così terminato il ciclo della prenotazione con lo sblocco delle
tabelle che avevamo bloccato in precedenza. Come vedete nell'inserimento
sulla tabella prenotazioni abbiamo usato null al posto della
colonna id che riceve il valore auto_increment.
Avrete forse notato che nella istruzione iniziale di blocco delle tabelle
abbiamo citato due volte la tabella prenotazioni. Ciò accade perchè nella
query di ricerca delle disponibilità la tabella viene citata con l'alias
p, mentre in fase di inserimento ciò non avviene (e nemmeno sarebbe
possibile): di conseguenza siamo costretti ad acquisire il lock sia per
l'alias, sia per il nome completo. Come possibilità alternativa avremmo
potuto rinunciare all'uso dell'alias nella prima query; in questo caso
sarebbe stato sufficiente il blocco in scrittura.
Naturalmente questa era una simulazione studiata per essere eseguita con
il client mysql; in una realtà applicativa le query verranno eseguite
attraverso le interfacce proprie del linguaggio utilizzato, e i dati che
qui abbiamo salvato nelle variabili SQL verranno più probabilmente
esportati nello script che gestisce la prenotazione.
Inoltre sarebbe stato possibile, utilizzando ad esempio lo storage engine
InnoDB invece di MyISAM, effettuare il tutto all'interno di una
transazione invece di usare i lock sulle tabelle. A questo proposito però
è bene notare che, per garantirsi dal rischio che elaborazioni
concorrenti potessero inserire prenotazioni nell'arco di tempo necessario
alla nostra elaborazione, un sistema transazionale avrebbe dovuto
lavorare con il massimo livello di isolamento, cioè SERIALIZABLE.
Concludiamo facendo alcune considerazioni ulteriori. La query di ricerca
delle disponibilità può naturalmente essere arricchita con l'indicazione
delle eventuali caratteristiche richieste per la stanza. Immaginiamo ad
esempio di voler cercare la disponibilità di una camera doppia, per il
periodo dall'8 al 22 luglio, con aria condizionata e vista mare, e la
possibilità di un terzo letto aggiunto. La query sarebbe diventata così:
SELECT c.* FROM camere c
WHERE tipo = 'doppia'
AND FIND_IN_SET('ariaCondizionata',optionals) > 0
AND FIND_IN_SET('vistaMare',optionals) > 0
AND FIND_IN_SET('terzo',lettiAggiunti) > 0
AND NOT EXISTS
(SELECT * FROM prenotazioni p WHERE p.camera = c.numero
AND (p.periodoDal < '2006-07-22' and '2006-07-08' < p.periodoAl)
);
Vediamo ora quale sarebbe la query da effettuare per avere la lista delle
camere disponibili un determinato giorno:
SELECT * FROM camere c WHERE NOT EXISTS
(SELECT * FROM prenotazioni p WHERE p.camera = c.numero
AND (p.periodoDal <= '2006-07-15'
AND p.periodoAl > '2006-07-15' ));
Abbiamo usato l'operatore 'minore o uguale' per la data iniziale e solo
'maggiore' per la data finale in quanto consideriamo la stanza libera
nella giornata in cui un soggiorno termina. Ovviamente basterebbe
invertire la clausola NOT EXISTS con EXISTS per avere l'elenco delle
stanze occupate.
Concludiamo con un'ultima query che ci restituisce l'elenco delle stanze
occupate un determinato giorno con il nome dell'ospite di ciascuna ed il
giorno in cui la stanza sarà liberata:
SELECT c.numero, c.piano, cl.nominativo, p.periodoAl AS partenza FROM camere c JOIN prenotazioni p ON p.camera = c.numero LEFT JOIN clienti cl ON p.idCliente = cl.id WHERE p.periodoDal <= '2006-07-15' AND p.periodoAl > '2006-07-15'
Come vedete abbiamo effettuato una join fra tre tabelle (camere,
prenotazioni e clienti), usando il numero di camera per relazionare le
camere con le prenotazioni ed il codice cliente per legare la
prenotazione all'ospite; la LEFT JOIN usata in quest'ultimo caso ci
garantisce che una stanza che risulta occupata venga inclusa nel
risultato anche se, per qualche motivo, il codice cliente memorizzato
sulla tabella prenotazioni dovesse essere inesistente sulla tabella
clienti. Ovviamente però questo sarebbe il segnale che qualcosa non ha
funzionato nella nostra applicazione.
