[successivo] [precedente] [inizio] [fine] [indice generale] [violazione GPL] [translators] [docinfo] [indice analitico] [volume] [parte]
Questo capitolo deriva da un lavoro originale di Antonio Bernardi <mailto:brngb@tin.it> che ne ha concesso espressamente l'utilizzo libero all'interno di questa opera. Il testo originale di questo lavoro si trova presso <http://linuxdidattica.org/docs/besta/>.
L'esercitazione che viene proposta, riguarda la gestione dei prestiti di una biblioteca. Le tabelle coinvolte sono:
libri(n_inv,autore,titolo,collocazione,soggetto,cod_ed,prezzo,anno_ed)
utenti(cod_ut,nome,cognome,telefono,indirizzo,citta)
localita(citta,cap,prov,naz)
editori(cod_ed,rag_soc,indirizzo,citta,telefono)
prestiti(np,n_inv,cod_ut,data_p,data_r)
Le tabelle sono ovviamente ridotte all'osso e sono autoesplicative. In questo esercizio la tabella fondamentale è la tabella prestiti, nella quale troviamo il codice dell'utente (cod_ut) e il numero di inventario del libro (n_inv). Quando viene fatto un prestito l'impiegato dovrà inserire:
il numero di inventario del libro, n_inv;
il codice dell'utente, cod_ut;
la data del prestito, data_p.
A questo punto, per evitare errori e non immettere un libro che è già a prestito, si dovrà fare un controllo che impedisca tale inserimento. Nella tabella prestiti vi è il campo data_r che riporta la data di rientro di un libro. Quando un libro viene dato a prestito questa data verrà inserita in modo predefinito usando un valore assurdo (2050.01.01) che serve a indicare che il libro è a prestito. Conseguentemente, quando si inserisce un prestito nuovo, se nella tabella prestiti esiste una riga con un attributo n_inv uguale a quello che si vuole inserire e data_r equivalente al valore convenzionale indicante che il libro è a prestito, il DBMS deve avvisare dell'errore.
Nell'esercitazione seguente questo controllo viene fatto in due modi differenti: prima con l'utilizzo di una funzione, poi con l'utilizzo di un trigger.
Si immagina di avere un'unico elaboratore, nel quale sia già installato PostgreSQL.
Per prima cosa ci si deve collegare all'elaboratore GNU/Linux, come utente postgres, per creare la base di dati:
postgres$ createdb biblioteca
Successivamente ci si connette alla base di dati biblioteca con il programma cliente psql per creare le tabelle:
postgres$ psql -h localhost -d biblioteca -U postgres[Invio]
biblioteca=> create table localita ([Invio]
biblioteca-> citta char(20) primary key,[Invio]
biblioteca-> cap char(5),[Invio]
biblioteca-> prov char(2),[Invio]
biblioteca-> naz char(3)[Invio]
biblioteca-> );[Invio]
biblioteca=> create table editori ([Invio]
biblioteca-> cod_ut char(3) primary key,[Invio]
biblioteca-> nome char(10),[Invio]
biblioteca-> cognome char(10),[Invio]
biblioteca-> telefono char(11),[Invio]
biblioteca-> indirizzo char(20),[Invio]
biblioteca-> citta char(20) references localita[Invio]
biblioteca-> );[Invio]
biblioteca=> create table libri ([Invio]
biblioteca-> n_inv char(5) primary key,[Invio]
biblioteca-> autore char(25),[Invio]
biblioteca-> titolo char(35) not null,[Invio]
biblioteca-> collocazione char(15),[Invio]
biblioteca-> soggetto char(11),[Invio]
biblioteca-> cod_ed char(5) references editori,[Invio]
biblioteca-> prezzo integer,[Invio]
biblioteca-> anno_ed char(4)[Invio]
biblioteca-> );[Invio]
biblioteca=> create table utenti ([Invio]
biblioteca-> cod_ut char(5) primary key,[Invio]
biblioteca-> nome char(10),[Invio]
biblioteca-> cognome char(10),[Invio]
biblioteca-> telefono char(11),[Invio]
biblioteca-> indirizzo char(20),[Invio]
biblioteca-> citta char(20) references localita[Invio]
biblioteca-> );[Invio]
biblioteca=> create table prestiti ([Invio]
biblioteca-> np serial,[Invio]
biblioteca-> n_inv char(5) references libri,[Invio]
biblioteca-> cod_ut char(5) references utenti,[Invio]
biblioteca-> data_p date check (data_p <= data_r),[Invio]
biblioteca-> data_r date default '2050.1.1'[Invio]
biblioteca-> );[Invio]
A questo punto si vanno a popolare le tabelle (esclusa la tabella prestiti): qui bisogna fare attenzione, inserendo prima i dati delle tabelle che non hanno chiavi esterne; successivamente inserendo quelle tabelle che fanno riferimento alle prime tramite chiavi esterne. Per esempio, è necessario popolare la tabella localita prima della tabella editori.
biblioteca=> insert into localita (citta, cap, prov, naz)[Invio]
biblioteca-> values ('TREVISO', '31100', 'TV', 'I');[Invio]
biblioteca=> insert into localita (citta, cap, prov, naz)[Invio]
biblioteca-> values ('PADOVA', '35100', 'PD', 'I');[Invio]
biblioteca=> insert into localita (citta, cap, prov, naz)[Invio]
biblioteca-> values ('MILANO', '20100', 'MI', 'I');[Invio]
biblioteca=> insert into editori (cod_ed, rag_soc, indirizzo, <-'
`->citta, telefono)[Invio]
biblioteca-> values ('1', 'CEDAM SPA', 'VIA JAPPELLI 5/6', <-'
`->'PADOVA', '049-8239111');[Invio]
biblioteca=> insert into editori (cod_ed, rag_soc, indirizzo, <-'
`->citta, telefono)[Invio]
biblioteca-> values ('2', 'ELEMOND SPA', 'VIA ROMA 17', <-'
`->'MILANO', '02-7820012');[Invio]
biblioteca=> insert into utenti (cod_ut, nome, cognome, <-'
`->telefono, indirizzo, citta)[Invio]
biblioteca-> values ('1', 'LUCA', 'BONALDO', '0422-401582', <-'
`->'VIA CORNARE 14', 'TREVISO');[Invio]
biblioteca=> insert into utenti (cod_ut, nome, cognome, <-'
`->telefono, indirizzo, citta)[Invio]
biblioteca-> values ('2', 'LUIGI', 'GOBBO', '049-458270', <-'
`->'VIA MANIN 72', 'PADOVA');[Invio]
biblioteca=> insert into utenti (cod_ut, nome, cognome, <-'
`->telefono, indirizzo, citta)[Invio]
biblioteca-> values ('3', 'SIMONE', 'PRIAMO', '0422-478791', <-'
`->'VIALE M.GRAPPA 1', 'TREVISO');[Invio]
biblioteca=> insert into utenti (cod_ut, nome, cognome, <-'
`->telefono, indirizzo, citta)[Invio]
biblioteca-> values ('4', 'MAURO', 'MENEGAZZI', '049-987756', <-'
`->'VIA EVEREST 7', 'PADOVA');[Invio]
biblioteca=> insert into libri (n_inv, autore, titolo, <-'
`->collocazione, soggetto, cod_ed, prezzo, anno_ed)[Invio]
biblioteca-> values ('1', 'STELLIO MARTELLI', <-'
`->'RACCONTI MITOLOGICI', 'X.1.1', 'STORICO', '1', 7000, '1992');[Invio]
biblioteca=> insert into libri (n_inv, autore, titolo, <-'
`->collocazione, soggetto, cod_ed, prezzo, anno_ed)[Invio]
biblioteca-> values ('2', 'HECTOR MALOT', 'SENZA FAMIGLIA', <-'
`->'X.1.2', 'DRAMMATICO', '2', 14000, '1990');[Invio]
biblioteca=> insert into libri (n_inv, autore, titolo, <-'
`->collocazione, soggetto, cod_ed, prezzo, anno_ed)[Invio]
biblioteca-> values ('3', 'LOUISE MAY ALCOTT', <-'
`->'PICCOLE DONNE CRESCONO', 'X.1.3', 'ROMANTICO', '1', 10000, '1991');[Invio]
biblioteca=> insert into libri (n_inv, autore, titolo, <-'
`->collocazione, soggetto, cod_ed, prezzo, anno_ed)[Invio]
biblioteca-> values ('4', 'MARY E. MAPES DODGE', <-'
`->'PATTINI D ARGENTO', 'X.1.4', 'FANTASTICO', '2', 13000, '1987');[Invio]
A questo punto se si inseriscono i dati nella tabella prestiti ci si può trovare nella situazione di avere a prestito lo stesso libro più volte contemporaneamente. Naturalmente si riesce a sperimentare facilmente tale situazione con qualche prova.
Per esempio, se viene digitato
biblioteca=> insert into prestiti (n_inv, cod_ut, data_p)[Invio]
biblioteca-> values ('2','3', '2001.1.1');[Invio]
si inserisce una riga sulla tabella prestiti. Se si digita ancora
biblioteca=> insert into prestiti (n_inv, cod_ut, data_p)[Invio]
biblioteca-> values ('2','1', '2001.1.1');[Invio]
ci si trova con un libro che è dato a prestito all'utente di codice 3 e contemporaneamente all'utente di codice 1, il che è assurdo, assumendo il fatto che un libro non possa essere preso a prestito da più utenti, contemporaneamente.
La soluzione proposta utilizza le funzioni e i trigger di PostgreSQL. Per la realizzazione delle funzioni si mostra qui l'uso del linguaggio Plpgsql, che prima di poter essere utilizzato deve essere associato esplicitamente. Questa operazione richiede l'uso del comando createlang, come si vede nell'esempio seguente:
postgres$ createlang plpgsql -h localhost -d biblioteca <-'
`->--pglib=/usr/lib/pgsql
In questo caso, si intende che il file plpgsql.so sia contenuto nella directory /usr/lib/pgsql/.
Successivamente si passa alla scrittura della funzione che viene mostrata sotto, con l'aiuto di un programma per la creazione e la modifica di file di testo (come VI per esempio), generando il file funzione_controlla.plpgsql.(1)
create function inserisci_prestito(char(5), char(5), date)
returns boolean
as 'declare
numero_inventario alias for $1;
codice_utente alias for $2;
data_prestito alias for $3;
data_restituzione date;
prestito record;
begin
data_restituzione:=''2050.1.1'';
select into prestito *
from prestiti
where n_inv=numero_inventario and
data_r=data_restituzione;
if found
then
raise exception \'il libro è già a prestito\';
return ''f'';
else
insert into prestiti (n_inv, cod_ut, data_p)
values (numero_inventario, codice_utente, data_prestito);
return ''t'';
end if;
end;'
language 'plpgsql';
Inizialmente si assegnano alle variabili numero_inventario, codice_utente e data_prestito i valori corrispondenti n_inv, cod_ut e data_p. Successivamente viene definita la variabile data_restituzione, di tipo date, alla quale viene assegnato il valore sentinella 2050.1.1; quindi la variabile prestito, di tipo record, che dovrà contenere la riga letta dalla tabella prestiti, nel caso la lettura vada a buon fine con l'istruzione:
select into prestito * from prestiti where...
Sostanzialmente si legge la tabella prestiti e se si trova una riga che soddisfa la condizione di uguaglianza tra n_inv e il numero di inventario del libro che si vuole dare a prestito e tra la data_r e la data fittizia del 2050.01.01, significa che il libro è già a prestito.
Se questa riga viene trovata (con la condizione if found), la funzione deve uscire dal blocco begin-end ed emettere un avviso che il libro è già a prestito, altrimenti deve inserire la riga in oggetto nella tabella prestiti con l'istruzione
insert into prestiti ...
con i dati passati dalla funzione.
Una volta scritto il file della funzione, si deve acquisirne il codice con il comando seguente, nell'ambito di psql:
biblioteca=> \i funzione_controlla.plpgsql[Invio]
Se nel frattempo la tabella prestiti contiene righe senza senso, conviene azzerarla completamente, prima di mettere in pratica l'uso della nuova funzione di controllo:
biblioteca=> \delete from prestiti;[Invio]
Per l'uso vero e proprio della funzione, si interviene come nell'esempio seguente:
biblioteca=> select inserisci_prestito ('2','3', <-'
`->cast '2001.1.1' as date);[Invio]
A questo punto, se si tenta di inserire per due volte lo stesso prestito, la funzione impedisce l'operazione e avvisa dell'errore.
L'utilizzo dell'istruzione select abbinata a una funzione può creare qualche confusione. Si può superare questo problema utilizzando un trigger che richiami automaticamente una funzione di controllo. Quello che segue è l'esempio di tale funzione corrispondente al file funzione_trigger.plpgsql.
create function inserisci_prestito_trigger()
returns opaque
as 'declare
numero_inventario char(5);
data_restituzione date;
prestito record;
begin
numero_inventario:=new.n_inv;
data_restituzione:=''2050.1.1'';
select into prestito *
from prestiti
where n_inv=numero_inventario and
data_r=data_restituzione;
if found
then
raise exception \'il libro è già a prestito\';
else
return new;
end if;
end;'
language 'plpgsql';
create trigger controlla_libro_uscito
before insert
on prestiti
for each row
execute procedure inserisci_prestito_trigger();
Nel file in questione, si vede la dichiarazione di una funzione analoga a quanto già mostrato in precedenza, seguita dalla dichiarazione del trigger relativo.
La variabile new corrisponde alla nuova riga che si vuole inserire con l'istruzione insert into, ed è di tipo record.
Dopo averle dichiarate, si assegna alla variabile numero_inventario il valore new.n_inv e alla variabile data_restituzione il valore sentinella 2050.1.1. Successivamente con l'istruzione
select into prestito * ...
si va a vedere se nella tabella prestiti esiste una riga che soddisfa la condizione di esistenza del libro a prestito. Se si trova questa riga viene mostrato un messaggio di errore, altrimenti la funzione deve restituire il valore contenuto nella variabile new, ovvero la riga che verrà inserita nella tabella.
La funzione inserisci_prestito_trigger() viene messa in azione, ogni volta che si vuole inserire una riga nel file prestiti, attraverso il controllo del trigger controlla_libro_uscito.
Si acquisisce la funzione e il trigger con il comando seguente, nell'ambito di psql:
biblioteca=> \i funzione_trigger.plpgsql[Invio]
A questo punto per inserire un libro a prestito si userà l'istruzione standard:
biblioteca=> insert into prestiti (n_inv, cod_ut, data_p) <-'
`->values ('3','2','2001.10.10');[Invio]
Se il libro non è già a prestito, si ottiene la segnalazione standard del fatto che il libro è stato inserito. Se si ritenta l'inserimento di un prestito con lo stesso numero di inventario, si ottiene solo la segnalazione di errore prevista.
biblioteca=> insert into prestiti (n_inv, cod_ut, data_p) <-'
`->values ('3','3','2001.10.10');[Invio]
ERROR: il libro è già a prestito
Bruce Momjian, PostgreSQL, Introduction and concepts, capitolo Functions and trigger
The PostgreSQL Global Development Group, PostgreSQL, Programmer's guide, capitolo Pl/pgSQL SQL procedural language
daniele @ swlibero.org1) Volendo rimanere nell'ambito di psql, si può usare il comando \! per avviare temporaneamente il programma di creazione e modifica dei file di testo.
Dovrebbe essere possibile fare riferimento a questa pagina anche con il nome le_funzioni_e_i_trigger_in_postgresql_un_esercitazione_didat.html
[successivo] [precedente] [inizio] [fine] [indice generale] [violazione GPL] [translators] [docinfo] [indice analitico]