Antonio Bernardi <mailto:brngb@tin.it>
La presente esercitazione è stata svolta presso l'Istituto Professionale di Stato per il Commercio Fabio Besta di Treviso, nelle classi quinte, corso A e B TGA, durante l'anno scolastico 2000/2001, e 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. Situazione da ritenersi abbastanza comune tra docenti e/o studenti che volessero iniziare a lavorare con questo prodotto.
Per prima cosa ci si deve collegare all'elaboratore GNU/Linux, come utente postgres, per creare il 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_ed char(5)
primary key,[Invio]
biblioteca-> rag_soc char(20),[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, (segue)[Invio]
citta,
telefono)
biblioteca-> values ('1', 'CEDAM SPA', 'VIA
JAPPELLI 5/6', (segue)[Invio]
'PADOVA',
'049-8239111');
biblioteca=> insert into editori (cod_ed,
rag_soc, indirizzo, (segue)[Invio]
citta,
telefono)
biblioteca-> values ('2', 'ELEMOND SPA',
'VIA ROMA 17', (segue)[Invio]
'MILANO',
'02-7820012');
biblioteca=> insert into utenti (cod_ut,
nome, cognome, (segue)[Invio]
telefono,
indirizzo, citta)
biblioteca-> values ('1',
'LUCA', 'BONALDO', '0422-401582', (segue)[Invio]
'VIA
CORNARE 14', 'TREVISO');
biblioteca=> insert into utenti
(cod_ut, nome, cognome, (segue)[Invio]
telefono,
indirizzo, citta)
biblioteca-> values ('2', 'LUIGI', 'GOBBO',
'049-458270', (segue)[Invio]
'VIA
MANIN 72', 'PADOVA');
biblioteca=> insert into utenti
(cod_ut, nome, cognome, (segue)[Invio]
telefono,
indirizzo, citta)
biblioteca-> values ('3', 'SIMONE',
'PRIAMO', '0422-478791', (segue)[Invio]
'VIALE
M.GRAPPA 1', 'TREVISO');
biblioteca=> insert into utenti
(cod_ut, nome, cognome, (segue)[Invio]
telefono,
indirizzo, citta)
biblioteca-> values ('4',
'MAURO', 'MENEGAZZI', '049-987756', (segue)[Invio]
'VIA
EVEREST 7', 'PADOVA');
biblioteca=> insert into libri
(n_inv, autore, titolo, (segue)[Invio]
collocazione,
soggetto, cod_ed, prezzo, anno_ed)
biblioteca-> values ('1',
'STELLIO MARTELLI', (segue)[Invio]
'RACCONTI
MITOLOGICI', 'X.1.1', 'STORICO', '1', 7000, '1992');
biblioteca=> insert into libri (n_inv,
autore, titolo, (segue)[Invio]
collocazione,
soggetto, cod_ed, prezzo, anno_ed)
biblioteca-> values ('2',
'HECTOR MALOT', 'SENZA FAMIGLIA', (segue)[Invio]
'X.1.2',
'DRAMMATICO', '2', 14000, '1990');
biblioteca=> insert into libri
(n_inv, autore, titolo, (segue)[Invio]
collocazione,
soggetto, cod_ed, prezzo, anno_ed)
biblioteca-> values ('3',
'LOUISE MAY ALCOTT', (segue)[Invio]
'PICCOLE
DONNE CRESCONO', 'X.1.3', 'ROMANTICO', '1', 10000, '1991');
biblioteca=> insert into libri (n_inv,
autore, titolo, (segue)[Invio]
collocazione,
soggetto, cod_ed, prezzo, anno_ed)
biblioteca-> values ('4', 'MARY E. MAPES
DODGE', (segue)[Invio]
'PATTINI D
ARGENTO', 'X.1.4', 'FANTASTICO', '2', 13000, '1987');
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. Situazione accaduta realmente nell'esercitazione in esame quando agli studenti è stato detto: «adesso, con i dati delle tabelle libri e utenti, cominciate ad inserire dei prestiti» (l'esercitazione si svolgeva in rete con un 286 per studente e ogni studente inseriva dati a piacere).
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 (segue)
--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', (segue)[Invio]
cast
'2001.1.1' as date);
A questo punto, se si tenta di inserire lo stesso libro a prestito,
biblioteca=> select inserisci_prestito
('2','1', (segue)[Invio]
cast
'2001.1.1' as date);
la funzione impedisce l'operazione e avvisa dell'errore.
L'utilizzo dell'istruzione select abbinata a una funzione può creare qualche confusione. Infatti alcuni studnti, per inserire i dati nella tabella prestiti, avevano difficoltà ad utilizzare l'istruzione
biblioteca=> select inserisci_prestito (...)
in quanto confondevano l'istruzione select con l'istruzione insert into precedentemente studiata e utilizzata per popolare una tabella.
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.(1)
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) (segue)[Invio]
values
('3','1','2001.1.1');
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) (segue)[Invio]
values
('3','2','2001.1.1');
ERROR: il libro è già a prestito
Adesso siamo più tranquilli perchè non succedderà più che vengano inseriti, nella tabella prestiti, dati contradditori.
Treviso 26 febbraio 2001
Bruce Momjian, PostgreSQL, Introduction and concepts, capitolo Functions and trigger
The PostgreSQL Global Development Group, PostgreSQL 7.1, Programmer's guide, capitolo Pl/pgSQL SQL procedural language
<http://www.postgresql.org/devel-corner/docs/postgres/programmer.html>
Daniele Giacomini, Informatica per il commercio elettronico, capp. 10-14
<http://didattica.swlibero.org/lavori/antologia-informatica-commercio-elettronico/>
1) Volendo rimanere nell'ambito di psql, si può usare il comando \! per avviare temporaneamente il programma di creazione e modifica dei file di testo.
Copyright ©
2000 Antonio Bernardi
Via Pietro di Dante, 7 - I-31100 Treviso -
brngb@tin.it
Questo documento, o parte di esso, può essere
riprodotto e distribuito con qualunque mezzo, fisico o elettronico,
purché sia accompagnato da questo copyright e da questa
licenza.