Figura 75.57. La relazione Indirizzi (Codice, Cognome, Nome, Indirizzo, Telefono) usata in molti esempi del capitolo.
$PGHOST 75.2.1
$PGPORT 75.2.1
pg_database 75.2.4
pg_shadow 75.2.4
pg_user 75.2.4
psql 75.2.1
PostgreSQL(1) è un DBMS (Data base management system) relazionale, esteso agli oggetti. In questo capitolo si vuole introdurre al suo utilizzo e accennare alla sua struttura, senza affrontare le particolarità del linguaggio di interrogazione. Il nome lascia intendere che si tratti di un DBMS in grado di comprendere le istruzioni SQL.
PostgreSQL, a parte i programmi binari, gli script e la documentazione, colloca i file di gestione delle basi di dati a partire da una certa directory, che nella documentazione originale viene definita PGDATA. Questo è il nome di una variabile di ambiente che può essere utilizzata per informare i vari programmi di PostgreSQL della sua collocazione; tuttavia, di solito questo meccanismo della variabile di ambiente non viene utilizzato, specificando tale directory in fase di compilazione dei sorgenti oppure avviando i programmi con opzioni appropriate.
|
Tutti i programmi che compongono il sistema di PostgreSQL, che hanno la necessità di sapere dove si trovano i dati, oltre al meccanismo della variabile di ambiente PGDATA permettono di indicare tale directory attraverso un'opzione della riga di comando. I programmi più importanti riconoscono l'opzione -D. Come si può intuire, l'utilizzo di questa opzione, o di un'altra equivalente per gli altri programmi, fa in modo che l'indicazione della variabile PGDATA non abbia effetto. |
Questa directory è contenuta solitamente nella directory iniziale dell'utente di sistema per l'amministrazione di PostgreSQL, che dovrebbe essere postgres. La directory iniziale dell'utente postgres (ovvero ~postgres/) è normalmente /var/lib/postgres/; la directory usata normalmente per collocarvi le basi di dati è normalmente ~postgres/data/, ovvero /var/lib/postgres/data/. Di norma, tutto ciò che si trova a partire da ~postgres/ appartiene all'utente postgres, anche se i permessi per il gruppo e gli altri utenti variano a seconda della circostanza.
Inizialmente, la directory che costituisce l'inizio delle basi di dati (~postgres/data/) dovrebbe contenere dei file di configurazione, una basi di dati amministrativa (trasparente) e una base di dati da usare come modello per la produzione di altre (template1) o semplicemente per accedere al DBMS quando non se ne può indicare un'altra. Naturalmente, se per qualche ragione si utilizza l'utenza postgres in modo normale, nella sua directory personale (~postgres/) potrebbero apparire dei file che riguardano la personalizzazione di questo utente (.profile, .bash_history, o altre cose simili, in funzione dei programmi che si utilizzano).
L'amministratore dei servizi offerti dal DBMS PostgreSQL potrebbe essere una persona diversa dall'amministratore del sistema operativo (l'utente root) e corrisponde di solito all'utente postgres. In condizioni normali, tale utente del DBMS viene riconosciuto implicitamente da PostgreSQL, purché acceda localmente utilizzando un'utenza del sistema operativo con lo stesso nome.
Quando la propria distribuzione GNU è già predisposta per PostgreSQL, l'utente postgres dovrebbe essere stato previsto (non importa il numero UID che gli sia stato abbinato), ma quasi sicuramente la parola d'ordine per l'accesso al sistema operativo dovrebbe essere «impossibile», come nell'esempio seguente:
|
Come si vede, in questo esempio il campo della parola d'ordine è occupato da un punto esclamativo che di fatto impedisce l'accesso all'utente postgres.
A questo punto si pongono due alternative, a seconda che si voglia affidare la gestione del DBMS allo stesso utente root oppure che si voglia incaricare per questo un altro utente. Nel primo caso non occorrono cambiamenti: l'utente root può diventare postgres quando vuole con il comando su.
# su postgres[Invio]
Nel secondo caso, l'attribuzione di una parola d'ordine all'utente postgres permetterebbe a una persona diversa di amministrare il DBMS.
# passwd postgres[Invio]
|
Di solito, nella sua configurazione iniziale, l'utente postgres ha la facoltà di accedere localmente al DBMS, senza bisogno di altre forme di autenticazione, a parte il fatto di essere riconosciuto dal sistema operativo proprio con quello stesso nome. Ciò dipende principalmente dalla configurazione contenuta nel file |
La prima volta che si installa PostgreSQL, è molto probabile che venga predisposta automaticamente la directory ~postgres/. Se così non fosse, o se per qualche motivo si dovesse intervenire manualmente, si può utilizzare initdb, che però potrebbe risiedere al di fuori dei percorsi normali contenuti nella variabile $PATH; precisamente potrebbe trattarsi della directory /usr/lib/postgresql/bin/.
[percorso]initdb [opzioni] [--pgdata=directory |-D directory] |
Lo schema sintattico mostra in modo molto semplice l'uso di initdb. Se si definisce correttamente la variabile di ambiente PGDATA, si può fare anche a meno delle opzioni, diversamente diventa necessario dare questa informazione attraverso l'opzione -D.
Volendo fare tutto da zero, occorre predisporre la directory iniziale in modo che appartenga dell'utente fittizio postgres:
# mkdir ~postgres[Invio]
# chown postgres: ~postgres[Invio]
Prima di avviare initdb, è bene utilizzare l'identità dell'utente amministratore di PostgreSQL:
# su postgres[Invio]
Successivamente, si deve avviare initdb specificando la directory a partire dalla quale si devono articolare i file che costituiscono le basi di dati. Come già descritto, la directory in questione è normalmente ~postgres/data/:
postgres$ /usr/lib/postgresql/bin/initdb \
\ --locale=it_IT.UTF-8 \
\ --encoding=UNICODE \
\ --pgdata=/var/lib/postgres/data[Invio]
The files belonging to this database system will be owned
by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale
it_IT.UTF-8.
creating directory /var/lib/postgres/data... ok
creating directory /var/lib/postgres/data/base... ok
creating directory /var/lib/postgres/data/global... ok
creating directory /var/lib/postgres/data/pg_xlog... ok
creating directory /var/lib/postgres/data/pg_clog... ok
selecting default max_connections... 100
selecting default shared_buffers... 1000
creating configuration files... ok
creating template1 database in
/var/lib/postgres/data/base/1... ok
initializing pg_shadow... ok
enabling unlimited row size for system tables... ok
initializing pg_depend... ok
creating system views... ok
loading pg_description... ok
creating conversions... ok
setting privileges on built-in objects... ok
creating information schema... ok
vacuuming database template1... ok
copying template1 to template0... ok
Success. The database server should be started
automatically.
If not, you can start the database server using:
/etc/init.d/postgresql start
|
Nell'esempio sono state usate anche due opzioni il cui significato dovrebbe risultare intuitivo.
|
Tabella 75.3. Alcune opzioni per l'uso di initdb.
|
Teoricamente, initdb fa tutto quello che è necessario fare; in pratica potrebbe non essere così. La prima cosa da considerare sono i file di configurazione, che, seguendo l'esempio mostrato, vengono collocati nella directory ~postgres/data/. Molto probabilmente la propria distribuzione GNU è organizzata per avere i file di configurazione in una directory /etc/postgresql/, o simile. Se le cose stanno così, bisogna provvedere a sostituire i file di configurazione nella directory ~postgres/data/ con dei collegamenti simbolici appropriati.
Le distribuzioni GNU possono avere la necessità di passare alcune informazioni, tramite variabili di ambiente, all'utente fittizio postgres, cosa che si ottiene con un file ~postgres/.profile appropriato. Se si vuole ricreare la directory ~postgres/ da zero, ma si nota la presenza di file di configurazione della shell, è necessario accertarsi del loro contenuto e provvedere di conseguenza nella ricostruzione della directory.
Un'ultima questione importante da sistemare è la directory ~postgres/dumpall/, che serve a contenere versioni vecchie degli eseguibili di PostgreSQL, con lo scopo di recuperare i dati dalle versioni vecchie delle basi di dati. Normalmente è sufficiente recuperare la directory già usata in precedenza.
Il DBMS di PostgreSQL si basa su un sistema cliente-servente, in cui, il programma che vuole interagire con una base di dati determinata deve farlo attraverso delle richieste inviate a un servente. In questo modo, il servizio può essere esteso anche attraverso la rete.
L'organizzazione di PostgreSQL prevede la presenza di un demone sempre in ascolto (può trattarsi di un socket di dominio Unix o anche di una porta TCP, che di solito corrisponde al numero 5 432). Quando questo riceve una richiesta valida per iniziare una connessione, attiva una copia del servente vero e proprio (back-end), a cui affida la connessione con il cliente. Il demone in ascolto per le richieste di nuove connessioni è postmaster, mentre il servente è postgres.
|
Purtroppo, la scelta del nome «postmaster» è un po' infelice, dal momento che potrebbe far pensare all'amministratore del servizio di posta elettronica. Come al solito occorre un po' di attenzione al contesto in cui ci si trova. |
Generalmente, il demone postmaster viene avviato attraverso la procedura di inizializzazione del sistema, in modo indipendente dal supervisore dei servizi di rete. In pratica, di solito si utilizza uno script collocato all'interno di /etc/init.d/, o in un'altra collocazione simile, per l'avvio e l'interruzione del servizio.
Durante il funzionamento del sistema, quando alcuni clienti sono connessi, si può osservare una dipendenza del tipo rappresentato dallo schema seguente:
--postmaster-+-postgres
|-postgres
`-postgres
|
Il demone postmaster si occupa di restare in ascolto in attesa di una richiesta di connessione con un servente postgres (il programma terminale, o back-end in questo contesto). Quando riceve questo tipo di richiesta mette in connessione il cliente (programma frontale, o front-end) con una nuova copia del servente postgres.
postmaster [opzioni] |
Per poter compiere il suo lavoro, il demone deve essere a conoscenza di alcune notizie essenziali, tra cui in particolare: la collocazione del programma postgres (se questo non è in uno dei percorsi della variabile PATH) e la directory da cui si dirama il sistema di file che costituisce l'insieme delle varie basi di dati. Queste notizie possono essere predefinite, nella configurazione usata al momento della compilazione dei sorgenti, oppure possono essere indicate attraverso la riga di comando.
|
Il demone postmaster e i processi terminali da lui controllati, gestiscono dei file che compongono le varie basi di dati del sistema. Trattandosi di un sistema di gestione dei dati molto complesso, è bene evitare di inviare il segnale SIGKILL (9), perché con questo si provoca la conclusione immediata del processo destinatario e di tutti i suoi discendenti, senza permettere una conclusione corretta. Al contrario, gli altri segnali sono accettabili, come per esempio un SIGTERM che viene dato in modo predefinito quando si utilizza il comando kill. |
|
Tabella 75.5. Alcune opzioni per l'avvio di postmaster.
|
Segue la descrizione di alcuni esempi.
# su postgres -c 'postmaster -S \
\ -D/var/lib/postgres/data'[Invio]
L'utente root, avvia postmaster dopo essersi trasformato temporaneamente nell'utente postgres (attraverso su), facendo in modo che il programma si disassoci dalla shell e dal terminale, diventando un discendente da Init. Attraverso l'opzione -D si specifica la directory di inizio dei file della base di dati.
# su postgres -c 'postmaster -i -S \
\ -D/var/lib/postgres/data'[Invio]
Come nell'esempio precedente, specificando che si vuole consentire, in modo preliminare, l'accesso attraverso la rete.
|
Per consentire in pratica l'accesso attraverso la rete, occorre anche intervenire all'interno del file di configurazione |
# su postgres -c 'nohup postmaster \
\ -D/var/lib/postgres/data \
\ > /var/log/pglog 2>&1 &'[Invio]
L'utente root, avvia postmaster in modo simile al precedente, dove in particolare viene diretto lo standard output all'interno di un file, per motivi diagnostici. Si osservi l'utilizzo di nohup per evitare l'interruzione del funzionamento di postmaster all'uscita del programma su.
# su postgres -c 'nohup postmaster \
\ -D/var/lib/postgres -d 1 \
\ > /var/log/pglog 2>&1 &'[Invio]
Come nell'esempio precedente, con l'attivazione del primo livello diagnostico nei messaggi emessi.
|
Riquadro 75.6. Controllo diagnostico.
|
Come già accennato, è possibile influenzare il comportamento del servente PostgreSQL attraverso opzioni della riga di comando e variabili di ambiente. Oltre a questi metodi, è possibile intervenire nel file ~postgres/data/postgresql.conf, attraverso direttive che assomigliano all'assegnamento di variabili. Il loro significato dovrebbe risultare intuitivo. Viene mostrato un estratto di esempio di questo file:
|
Si può osservare la direttiva tcpip_socket = true, che abilita l'accesso al servente attraverso la rete, ma che richiede di specificare meglio le possibilità di accesso attraverso il file ~postgres/data/pg_hba.conf.
|
Tabella 75.8. Elenco dei formati di data gestibili con PostgreSQL.
|
Nel caso particolare della distribuzione GNU/Linux Debian, può essere controllato tutto a partire dai file che si trovano nella directory /etc/postgresql/. In particolare, si trova in questa directory il file pg_hba.conf e il file postgresql.conf, già descritti in altre sezioni; inoltre, si trova un file aggiuntivo che viene interpretato dallo script della procedura di inizializzazione del sistema che si occupa di avviare e di arrestare il servizio. Si tratta dei file /etc/postgresql/postmaster.conf, attraverso il quale si possono controllare delle piccole cose a cui non si può accedere con il file postgresql.conf, che altrimenti richiederebbero di intervenire attraverso le opzioni della riga di comando del demone relativo.
L'accesso alle basi di dati viene permesso attraverso un sistema di autenticazione. I sistemi di autenticazione consentiti possono essere diversi e dipendono dalla configurazione di PostgreSQL fatta all'atto della compilazione dei sorgenti.
Il file di configurazione pg_hba.conf (Host-based authentication), che si trova nella directory ~postgres/data/, serve per controllare il sistema di autenticazione una volta installato PostgreSQL.
L'autenticazione degli utenti può avvenire in modo incondizionato (trust), dove ci si fida del nome fornito come utente del DBMS, senza richiedere altro.
L'autenticazione può essere semplicemente disabilitata, nel senso di impedire qualunque accesso incondizionatamente. Questo può servire per impedire l'accesso da parte di un certo gruppo di nodi.
L'accesso può essere controllato attraverso l'abbinamento di una parola d'ordine agli utenti di PostgreSQL.
Inoltre, l'autenticazione può avvenire attraverso un sistema Kerberos, oppure attraverso il protocollo IDENT (sezione 43.3). In questo ultimo caso, ci si fida di quanto riportato dal sistema remoto il quale conferma o meno che la connessione appartenga a quell'utente che si sta connettendo.
Il file ~postgres/data/pg_hba.conf (ma spesso questo è un collegamento simbolico che punta a /etc/postgresql/pg_hba.conf o a un'altra posizione simile) permette di definire quali nodi possono accedere al servizio DBMS di PostgreSQL, eventualmente stabilendo anche un abbinamento specifico tra basi di dati, utenti e nodi di rete.
Le righe vuote e il testo preceduto dal simbolo # vengono ignorati. I record (cioè le righe contenenti le direttive del file in questione) sono suddivisi in campi separati da spazi o caratteri di tabulazione. Il formato può essere semplificato nei due modelli sintattici seguenti, tenendo conto che esistono comunque altri casi:
local base_di_dati utente_dbms autenticazione_utente [mappa] |
host base_di_dati utente_dbms indirizzo_ip maschera_degli_indirizzi \ |
Nel primo caso si intendono controllare gli accessi provenienti da programmi clienti avviati nello stesso sistema locale, utilizzando un socket di dominio Unix per il collegamento; nel secondo si fa riferimento ad accessi attraverso la rete (connessioni TCP).
Il secondo campo del record serve a indicare il nome di una base di dati per la quale autorizzare l'accesso; in alternativa si può usare la parola chiave all, in modo da specificare tutte le basi di dati in una sola volta.
Il terzo campo del record serve a indicare il nome dell'utente del DBMS da autorizzare; in alternativa si può usare la parola chiave all, in modo da rendere indifferente chi sia l'utente.
I campi indirizzo_ip e maschera_degli_indirizzi rappresentano un gruppo di indirizzi di nodi che hanno diritto di accedere a quella base di dati determinata.
Il campo autenticazione_utente rappresenta il tipo di autenticazione attraverso una parola chiave. Le più comuni sono elencate nella tabella 75.9.
L'ultimo campo dipende dal penultimo. Nel caso di autenticazione ident, si utilizza quasi sempre la parola chiave sameuser per indicare a PostgreSQL che i nomi usati dagli utenti nei sistemi remoti da cui possono accedere, coincidono con quelli predisposti per la gestione del DBMS. Nel caso di autenticazione password, l'ultimo campo potrebbe rappresentare il nome del file di testo contenente le parole d'ordine.
|
Tabella 75.9. Parole chiave che possono essere usate nel campo autenticazione_utente.
|
Segue la descrizione di alcuni esempi.
|
Concede a tutti gli utenti di accedere localmente (tramite un socket di dominio Unix), a qualunque base di dati, senza bisogno di alcun riconoscimento (si accetta il nome e basta).
|
Concede a tutti gli utenti di accedere localmente, ma tramite un socket di dominio Internet (l'indirizzo 127.0.0.1 e normalmente quello di ogni nodo, dal punto di vista locale), senza bisogno di alcun riconoscimento.
|
Concede a tutti gli utenti di accedere localmente (tramite un socket di dominio Unix), a qualunque base di dati, sulla base del riconoscimento fatto dal sistema operativo (si intende che ci si affida ai privilegi che ha ottenuto il programma usato per accedere).
|
Concede a tutti gli utenti di accedere localmente, ma tramite un socket di dominio Internet, sulla base del riconoscimento ottenuto tramite l'uso del protocollo di rete IDENT. Questo metodo può essere usato in alternativa a quello dell'esempio precedente, se per qualche ragione il riconoscimento locale (senza rete), non dovesse funzionare.
|
Concede all'utente pippo di accedere alla base di dati gazie, da un nodo qualunque tra quelli che hanno indirizzi del tipo 192.168.*.*, attraverso l'indicazione di una parola d'ordine, che viene trasmessa in chiaro.
L'esempio seguente rappresenta una configurazione che potrebbe essere considerata «ragionevole», per poter utilizzare l'utente postgres, localmente, senza bisogno di fornire una parola d'ordine (come richiesto dagli esempi mostrati in questo capitolo), consentendo agli altri utenti di accedere da una rete locale qualunque (lo si determina in base al fatto che si fa riferimento a indirizzi IPv4 privati), ma in tal caso si richiede un riconoscimento basato su una parola d'ordine:
|
La gestione di un DBMS richiede di occuparsi di utenze e di basi di dati. PostgreSQL mette a disposizione degli script per facilitare la loro creazione ed eliminazione, ma in generale è meglio avvalersi di istruzioni SQL, anche se non sono standard.
Per poter impartire comandi in forma di istruzioni SQL, occorre collegarsi al DBMS attraverso un programma appropriato (di solito psql); per poter maneggiare gli utenti e le basi di dati è necessario disporre dei privilegi necessari. Generalmente, le prime volte si compiono queste operazioni in qualità di amministratore, pertanto con l'utenza postgres.
Per accedere al DBMS, occorre indicare una basi di dati, anche se le funzioni in questione non interagiscono direttamente con questa. Di solito, dato che inizialmente non è disponibile altro, ci si collega alla basi di dati template1.
|
Teoricamente, PostgreSQL non distingue tra lettere maiuscole e minuscole quando si tratta di nominare le basi di dati, le relazioni (le tabelle o gli oggetti a seconda della definizione che si preferisce utilizzare) e gli elementi che compongono delle relazioni. Tuttavia, in certi casi si verificano degli errori inspiegabili dovuti alla scelta dei nomi che in generale conviene indicare sempre solo con lettere minuscole. |
L'accesso a una base di dati avviene attraverso un cliente, ovvero un programma frontale, o front-end, secondo la documentazione di PostgreSQL. Questo programma si avvale generalmente della libreria LibPQ. PostgreSQL fornisce un programma cliente standard, psql, che si comporta come una sorta di shell tra l'utente e la base di dati stessa.
|
Il programma cliente tipico, dovrebbe riconoscere le variabili di ambiente PGHOST e PGPORT. La prima serve a stabilire l'indirizzo o il nome a dominio del servente, indicando implicitamente che la connessione avviene attraverso una connessione TCP e non con un socket di dominio Unix; la seconda specifica il numero della porta, ammesso che si voglia utilizzare un numero diverso da 5 432. L'uso di queste variabili non è indispensabile, ma serve solo per non dover specificare queste informazioni attraverso opzioni della riga di comando. |
Il programma psql permette un utilizzo interattivo attraverso una serie di comandi impartiti dall'utente su una riga di comando; oppure può essere avviato in modo da eseguire il contenuto di un file o di un solo comando fornito tra gli argomenti. Per quanto riguarda l'utilizzo interattivo, il modo più semplice per avviarlo è quello che si vede nell'esempio seguente, dove si indica semplicemente il nome della base di dati sulla quale intervenire.
$ psql mio_db[Invio]
Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute query You are currently connected to the database: mio_db mio_db=>_ |
Da questo momento si possono inserire le istruzioni SQL per la base di dati selezionata, in questo caso mio_db, oppure si possono inserire dei comandi specifici di psql. Questi ultimi si notano perché sono composti da una barra obliqua inversa (\), seguita da un carattere.
Il comando interno di psql più importante è \h che permette di visualizzare una guida rapida alle istruzioni SQL che possono essere utilizzate.
=> \h[Invio]
type \h <cmd> where <cmd> is one of the following:
abort abort transaction alter table
begin begin transaction begin work
cluster close commit
...
type \h * for a complete description of all commands
|
Nello stesso modo, il comando \? fornisce un riepilogo dei comandi interni di psql.
=> \?[Invio]
\? -- help \a -- toggle field-alignment (currenty on) \C [<captn>] -- set html3 caption (currently '') ... |
Tutto ciò che psql non riesce a interpretare come un suo comando interno viene trattato come un'istruzione SQL. Dal momento che queste istruzioni possono richiedere più righe, è necessario informare psql della conclusione di queste, per permettergli di analizzarle e inviarle al servente. Queste istruzioni possono essere terminate con un punto e virgola (;), oppure con il comando \g.
Si può osservare, utilizzando psql, che l'invito mostrato cambia leggermente a seconda del contesto: inizialmente appare nella forma =>, mentre quando è in corso l'inserimento di un'istruzione SQL non ancora terminata si trasforma in ->. Il comando \g viene usato prevalentemente in questa situazione.
-> \g[Invio]
Le istruzioni SQL possono anche essere raccolte in un file di testo normale. In tal caso si può utilizzare il comando \i per fare in modo che psql interpreti il suo contenuto, come nell'esempio seguente, dove il file in questione è mio_file.sql.
=> \i mio_file.sql[Invio]
Nel momento in cui si utilizza questa possibilità (quella di scrivere le istruzioni SQL in un file facendo in modo che poi questo venga letto e interpretato), diventa utile il poter annotare dei commenti. Questi sono iniziati da una sequenza di due trattini (--), come prescrive lo standard, e tutto quello che vi appare dopo viene ignorato.
La conclusione del funzionamento di psql si ottiene con il comando \q.
=> \q[Invio]
Per l'avvio di psql si può usare la sintassi seguente. L'opzione -f consente di indicare un file contenente istruzioni SQL da eseguire subito; in alternativa, un file di questo tipo può essere fornito attraverso lo standard input.
psql [opzioni] [base_di_dati] |
psql -f file_di_istruzioni [altre_opzioni] [base_di_dati] |
cat file_di_istruzioni | psql [opzioni] [base_di_dati] |
Il programma psql può funzionare solo in abbinamento a una base di dati determinata. In questo senso, se non viene indicato il nome di una base di dati nella riga di comando, psql tenta di utilizzarne una con lo stesso nome dell'utente. Per la precisione, si fa riferimento alla variabile di ambiente USER.
|
Questo dettaglio dovrebbe permettere di comprendere il significato della segnalazione di errore che si ottiene se si tenta di avviare psql senza indicare una base di dati, quando non ne esiste una con lo stesso nome dell'utente. |
|
Tabella 75.19. Alcune opzioni per l'avvio di psql.
|
|
Tabella 75.20. Alcuni comandi che psql riconosce durante il funzionamento interattivo.
|
Segue la descrizione di alcuni esempi.
$ psql mio_db[Invio]
Cerca di connettersi con la base di dati mio_db nel nodo locale, riferendosi alla stessa utenza riconosciuta dal sistema operativo, utilizzando il meccanismo del socket di dominio Unix.
$ psql -d mio_db[Invio]
Esattamente come nell'esempio precedente, con l'uso dell'opzione -d che serve a evitare ambiguità sul fatto che mio_db sia il nome della base di dati.
$ psql -U tizio -d mio_db[Invio]
Come nell'esempio precedente, ma specificando che si intende accedere in qualità di utente tizio.
$ psql -U tizio -W -d mio_db[Invio]
Come nell'esempio precedente, ma forzando in ogni caso la richiesta di inserimento di una parola d'ordine.
$ psql -U tizio -W -h dinkel.brot.dg -d mio_db[Invio]
Come nell'esempio precedente, ma questa volta l'accesso viene fatto a una base di dati con lo stesso nome presso il nodo dinkel.brot.dg.
$ psql -U tizio -W -f istruzioni.sql -d mio_db[Invio]
Cerca di connettersi con la base di dati mio_db nel nodo locale, utilizzando il meccanismo del socket di dominio Unix, quindi esegue le istruzioni contenute nel file istruzioni.sql.
$ psql -U tizio -W -d mio_db < istruzioni.sql[Invio]
Come nell'esempio precedente, ricevendo il contenuto del file istruzioni.sql dallo standard input.
Il programma psql è sensibile alla presenza o meno della variabile di ambiente PAGER. Se questa esiste e non è vuota, psql utilizza il programma indicato al suo interno per controllare l'emissione dell'output generato. Per esempio, se contiene less, come si vede nell'esempio seguente che fa riferimento a una shell POSIX o compatibile con quella di Bourne, si fa in modo che l'output troppo lungo venga controllato da Less:
|
Per eliminare l'impostazione di questa variabile, in modo da ritornare allo stato predefinito, basta annullare il contenuto della variabile nel modo seguente:
|
La creazione di un utente per il DBMS si ottiene con l'istruzione CREATE USER, che nel modello seguente appare in modo semplificato:
CREATE USER nome_utente
[WITH [PASSWORD 'parola_d'ordine']
[CREATEDB|NOCREATEDB|CREATEUSER|NOCREATEUSER]]
|
Si comprende intuitivamente il significato delle parole chiave delle opzioni finali, con le quali è possibile concedere o negare i privilegi di creare o eliminare delle basi di dati e di creare o eliminare degli utenti. Si osservi che la parola d'ordine va indicata esattamente tra apici singoli. Se si omettono le opzioni finali, i privilegi relativi vengono negati, come se fossero state specificate implicitamente le parole chiave NOCREATEDB e NOCREATEUSER.
|
L'uso della parola chiave CREATEUSER nella creazione o nella modifica di un'utenza, concede a questa la facoltà di creare o eliminare delle utenze, senza limitazioni, oltre che di creare ed eliminare delle basi di dati. In altri termini, dà all'utente il ruolo di amministrazione del DBMS, con tutti i poteri necessari. |
L'esempio seguente mostra i passaggi per la creazione, presso il DBMS locale, dell'utente tizio (con una parola d'ordine di esempio) a cui viene concesso di creare delle basi di dati, ma non di gestire delle utenze:
# su postgres[Invio]
postgres$ psql template1[Invio]
template1=# CREATE USER tizio WITH PASSWORD 'segreta' \
\ CREATEDB NOCREATEUSER;[Invio]
CREATE USER |
template1=# \q[Invio]
postgres$ exit[Invio]
Così come è stato creata, le caratteristiche di un'utenza possono essere modificate con l'istruzione ALTER USER, per esempio per modificare la parola d'ordine:
ALTER USER nome_utente
[WITH [PASSWORD 'parola_d'ordine']
[CREATEDB|NOCREATEDB]
[CREATEUSER|NOCREATEUSER]]
|
Logicamente, se si tratta di modificare la parola d'ordine, può essere lo stesso utente che esegue questa istruzione; altrimenti, per cambiare i privilegi, è necessario che intervenga un utente che ha maggiori facoltà. Nell'esempio seguente, l'utente tizio creato in quello precedente, modifica la sua parola d'ordine; si osservi che la scelta della base di dati template1 è puramente casuale:
$ psql --username tizio template1[Invio]
Password: digitazione_all'oscuro[Invio]
template1=> ALTER USER tizio \
\ WITH PASSWORD 'segretissima';[Invio]
ALTER USER |
template1=> \q[Invio]
L'eliminazione di un'utenza avviene con un'istruzione molto semplice, senza opzioni particolari:
DROP USER nome_utente |
L'esempio seguente elimina l'utenza tizio e l'operazione viene svolta dall'utente postgres:
# su postgres[Invio]
postgres$ psql template1[Invio]
template1=# DROP USER tizio;[Invio]
DROP USER |
template1=# \q[Invio]
postgres$ exit[Invio]
La creazione di una base di dati è consentita agli amministratori e agli utenti che hanno ottenuto questo privilegio. La base di dati può essere creata per sé, oppure per farla gestire da un altro utente.
CREATE DATABASE nome_base_di_dati
[[WITH] [OWNER [=] utente_proprietario]
[ENCODING [=] 'codifica']]
|
Il modello sintattico mostrato omette alcune opzioni, di utilizzo meno frequente. In particolare, sarebbe possibile specificare il modello di riferimento per la creazione della base di dati, ma in modo predefinito viene utilizzata la base di dati template1 per crearne una di nuova.
Nell'esempio seguente, l'utente tizio crea la base di dati mia_db, specificando espressamente la codifica; inizialmente accede facendo riferimento alla base di dati template1:
$ psql --username tizio template1[Invio]
Password: digitazione_all'oscuro[Invio]
template1=> CREATE DATABASE mia_db ENCODING 'UNICODE';[Invio]
CREATE DATABASE |
template1=> \q[Invio]
L'eliminazione di una base di dati si ottiene con l'uso di un'istruzione molto semplice:
DROP DATABASE nome_base_di_dati |
Questa istruzione può essere usata da un amministratore, oppure dall'utente che ne è proprietario. Nell'esempio seguente, l'utente tizio elimina la sua base di dati mia_db; per farlo, accede facendo riferimento a un'altra (la solita template1):
$ psql --username tizio template1[Invio]
Password: digitazione_all'oscuro[Invio]
template1=> DROP DATABASE mia_db;[Invio]
DROP DATABASE |
template1=> \q[Invio]
PostgreSQL memorizza le informazioni sugli utenti e sulle basi di dati all'interno di una sorta di base di dati amministrativa, senza nome. Alle relazioni di questa base di dati trasparente, si accede da qualunque posizione; in pratica, le relazioni sono accessibili quando si apre la base di dati template1, o qualunque altra, ma ovviamente, solo l'amministratore del DBMS ha la facoltà di modificarle direttamente.
Come conseguenza del fatto che le relazioni della base di dati amministrativa sono accessibili da qualunque posizione, si comprende che i nomi di queste relazioni non si possono utilizzare per la costruzione di nuove.
La documentazione originale di PostgreSQL individua queste relazioni, definendole «cataloghi». In questo documento, si preferisce indicarle come relazioni o tabelle della base di dati amministrativa.
Le relazioni più importanti della base di dati amministrativa sono pg_user, pg_shadow e pg_database. Vale la pena di osservare il loro contenuto.
postgres:~$ psql -d template1[Invio]
La relazione pg_user è in realtà una vista del catalogo pg_shadow, che contiene le informazioni sugli utenti di PostgreSQL. La figura 75.28 mostra un esempio di come potrebbe essere composta. La consultazione della relazione si ottiene con l'istruzione SQL seguente:
template1=> SELECT * FROM pg_user;[Invio]
|
Figura 75.28. Esempio del contenuto di pg_user. usename |usesysid|usecreatedb|usesuper|usecatupd| passwd |valuntil|useconfig ----------+--------+-----------+--------+---------+--------+--------+--------- postgres | 1|t |t |t |********| | pgnanouser| 100|t |f |f |********| | tizio | 1001|t |t |t |********| | |
Si può osservare che l'utente postgres ha tutti gli attributi booleani attivi (usecreatedb, usesuper, usecatupd) e questo per permettergli di compiere tutte le operazioni all'interno delle basi di dati. In particolare, l'attributo usecreatedb permette all'utente di creare una base di dati e usesuper permette di aggiungere utenti. In effetti, osservando l'esempio della figura, l'utente tizio ha praticamente gli stessi privilegi dell'amministratore postgres.
La relazione pg_shadow è il contenitore delle informazioni sugli utenti, a cui si accede normalmente tramite la vista pg_user. Il suo scopo è quello di conservare in un file più sicuro, in quanto non accessibile agli utenti comuni, i dati delle parole d'ordine degli utenti che intendono usare le forme di autenticazione basate su queste. L'esempio della figura 75.29 mostra gli stessi utenti a cui non viene abbinata alcuna parola d'ordine (probabilmente perché accedono localmente e vengono identificati dal sistema operativo). La consultazione della relazione si ottiene con l'istruzione SQL seguente:
template1=> SELECT * FROM pg_shadow;[Invio]
|
Figura 75.29. Esempio del contenuto di pg_shadow. usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig ------------+----------+-------------+----------+-----------+--------+----------+----------- postgres | 1 | t | t | t | | | pgnanouser | 100 | t | f | f | | | tizio | 1001 | t | t | t | | | |
La relazione pg_database contiene le informazioni sulle basi di dati esistenti. La figura 75.30 mostra un esempio di come potrebbe essere composta. La consultazione della relazione si ottiene con l'istruzione SQL:
template1=> SELECT * FROM pg_database;[Invio]
|
Figura 75.30. Esempio del contenuto di pg_database, diviso in due parti per motivi tipografici. datname | datdba | encoding | datistemplate | datallowconn | datlastsysoid |
-----------+--------+----------+---------------+--------------+---------------+--
nanodb | 100 | 6 | f | t | 17140 |
template1 | 1 | 6 | t | t | 17140 |
template0 | 1 | 6 | t | f | 17140 |
| datvacuumxid | datfrozenxid | datpath | datconfig | datacl
-+--------------+--------------+---------+-----------+--------------------------
| 8159 | 3221233632 | | |
| 8271 | 3221233744 | | | {postgres=C*T*/postgres}
| 464 | 464 | | | {postgres=C*T*/postgres}
|
Il primo attributo rappresenta il nome della base di dati, il secondo riporta il numero di identificazione dell'utente che rappresenta il suo DBA, cioè colui che l'ha creata o che comunque deve amministrarla. Per esempio, si può osservare che la base di dati nanodb è stata creata dall'utente identificato dal numero 100, che da quanto riportato in pg_user è pgnanouser.
Un problema comune dei DBMS è quello della riorganizzazione periodica dei dati, in modo da semplificare e accelerare le elaborazioni successive. Nei sistemi più semplici si parla a volte di «ricostruzione indici», o di qualcosa del genere. Nel caso di PostgreSQL, si utilizza un comando specifico che è estraneo all'SQL standard: VACUUM.
VACUUM [altre_opzioni] [VERBOSE] [nome_relazione] |
VACUUM [altre_opzioni] [VERBOSE] ANALYZE \ |
L'operazione di pulizia si riferisce alla base di dati aperta in quel momento. L'opzione VERBOSE permette di ottenere i dettagli sull'esecuzione dell'operazione; ANALYZE serve invece per indicare specificatamente una relazione, o addirittura solo alcuni attributi (le colonne delle tabelle) una relazione e avere informazioni su questi. Eventualmente, sono disponibili altre opzioni per ottenere una riorganizzazione dei dati più importante.
Anche se non si tratta di un comando SQL standard, per PostgreSQL è importante che venga eseguita periodicamente una ripulitura con il comando VACUUM, eventualmente attraverso uno script simile a quello seguente, da avviare per mezzo del sistema Cron:
|
In pratica, richiamando questo script con i privilegi dell'utente root, indicando come argomento il nome della base di dati (viene inserito al posto di $1 dalla shell), si ottiene di avviare il comando VACUUM attraverso psql.
Per riuscire a fare il lavoro in serie per tutte le basi di dati, si potrebbe scrivere uno script più complesso, come quello seguente. In questo caso, lo script deve essere avviato con i privilegi dell'utente postgres.
|
In breve, si utilizza la prima volta psql in modo da aprire la base di dati template1 (quella usata come modello, che si ha la certezza di trovare sempre), accedendo alla relazione pg_database, che fa parte della base di dati amministrativa, per leggere l'attributo contenente i nomi delle basi di dati. In particolare, l'opzione -t serve a evitare di inserire il nome dell'attributo stesso. L'elenco che si ottiene viene inserito nella variabile di ambiente BASI_DATI, che in seguito viene scandita da un ciclo for, all'interno del quale si utilizza psql per ripulire ogni singola base di dati.
Prima di poter pensare a copiare o a spostare una base di dati occorre avere chiaro in mente che si tratta di file «binari» (nel senso che non si tratta di file di testo), contenenti informazioni collegate l'una all'altra in qualche modo più o meno oscuro. Queste informazioni possono a volte essere espresse anche in forma numerica; in tal caso dipende dall'architettura in cui sono state create. Ciò implica due cose fondamentali: la copia deve essere fatta in modo che non si perdano dei pezzi per la strada; lo spostamento dei dati in forma binaria, in un'altra architettura, non è ammissibile.
La copia di sicurezza binaria, di tutto ciò che serve a PostgreSQL per la gestione delle sue basi di dati, si ottiene semplicemente archiviando quanto contenuto a partire da ~postgres/, così come si può comprendere intuitivamente. Ciò che conta è che il ripristino dei dati avvenga nello stesso contesto (architettura, sistema operativo, librerie, versione di PostgreSQL e configurazione).
Per una copia di sicurezza più «sicura», è necessario archiviare i dati in modo indipendente da tutto. Si ottiene questo generando un file di testo, contenente istruzioni SQL con le quali ricostruire poi una sola base di dati o anche tutte assieme. Per questo vengono in aiuto due programmi di PostgreSQL: pg_dump e pg_dumpall.
|
Non sempre il procedimento di trasferimento dei dati in forma di comandi SQL può essere portato a termine con successo. Può succedere che delle relazioni troppo complesse o con dati troppo grandi, non siano tradotte correttamente nella fase di archiviazione. Questo problema deve essere preso in considerazione già nel momento della progettazione di una base di dati, avendo cura di verificare, sperimentandolo, che il procedimento di scarico e recupero dei dati possa funzionare. |
Lo scarico di una sola base di dati si ottiene attraverso il programma pg_dump, che, eventualmente, potrebbe risiedere al di fuori dei percorsi normali contenuti nella variabile $PATH e potrebbe trovarsi nella directory /usr/lib/postgresql/bin/:
pg_dump [opzioni] base_di_dati |
Se non si indicano delle opzioni e ci si limita a specificare la base di dati su cui intervenire, si ottiene il risultato attraverso lo standard output, composto in pratica dai comandi necessari a psql per ricostruire le relazioni che compongono la base di dati (la base di dati stessa deve essere ricreata manualmente). Tanto per chiarire subito il senso della cosa, se si utilizza pg_dump nel modo seguente, si ottiene il file di testo mio_db.dump:
$ pg_dump mio_db > mio_db.dump[Invio]
Questo file va verificato, ricercando la presenza eventuale di segnalazioni di errore che vengono generate in presenza di dati che non possono essere riprodotti fedelmente; eventualmente, il file può anche essere modificato se si conosce la sintassi dei comandi che vengono inseriti in questo script. Per fare in modo che le relazioni della base di dati vengano ricreate e caricate, si può utilizzare psql nel modo seguente:
$ psql -e mio_db < mio_db.dump[Invio]
|
Tabella 75.33. Alcune opzioni che possono essere usate con pg_dump.
|
Per copiare o trasferire tutte le basi di dati del sistema di PostgreSQL, si può utilizzare pg_dumpall, che, eventualmente, potrebbe risiedere al di fuori dei percorsi normali contenuti nella variabile $PATH e potrebbe trovarsi nella directory /usr/lib/postgresql/bin/:
[percorso]pg_dumpall [opzioni] |
Il programma pg_dumpall provvede a scaricare tutte le basi di dati, assieme alle informazioni necessarie per ricreare il catalogo pg_shadow (la vista pg_user si ottiene di conseguenza). Come si può intuire, si deve utilizzare pg_dumpall con i privilegi dell'amministratore del DBMS (di solito l'utente postgres).
postgres$ pg_dumpall > basi_dati.dump[Invio]
L'esempio mostra il modo più semplice di utilizzare pg_dumpall per scaricare tutte le basi di dati in un file unico. In questo caso, si ottiene il file di testo basi_dati.dump. Questo file va verificato alla ricerca di segnalazioni di errore che potrebbero essere generate in presenza di dati che non possono essere riprodotti fedelmente; eventualmente, può essere modificato se si conosce la sintassi dei comandi che vengono inseriti in questo script.
Il recupero dell'insieme completo delle basi di dati avviene normalmente in un ambiente PostgreSQL, in cui il sistema delle basi di dati sia stato predisposto, ma non sia stata creata alcuna base di dati (a parte quelle standard, come template1). Come si può intuire, il comando necessario per ricaricare le basi di dati, assieme alle informazioni sugli utenti (la relazione pg_shadow), è quello seguente:
postgres$ psql -e template1 < basi_dati.dump[Invio]
La situazione tipica in cui è necessario utilizzare pg_dumpall per scaricare tutto il sistema delle basi di dati, è quella del momento in cui ci si accinge ad aggiornare la versione di PostgreSQL. In breve, in quella occasione, si devono eseguire i passaggi seguenti:
con la versione vecchia di PostgreSQL, si deve utilizzare pg_dumpall in modo da scaricare tutto il sistema delle basi di dati in un solo file di testo;
si aggiorna PostgreSQL;
si elimina il contenuto della directory ~postgres/data/, ovvero quella che altrimenti viene definita PGDATA (prima conviene forse fare una copia di sicurezza del suo contenuto, tale e quale, in forma binaria);
si ricrea il sistema delle basi di dati, vuoto, attraverso initdb;
si ricaricano le basi di dati precedenti, assieme alle informazioni sugli utenti, attraverso psql, utilizzando il file generato in precedenza attraverso pg_dumpall.
Quello che manca, eventualmente, è la configurazione di PostgreSQL, in particolare per ciò che riguarda i sistemi di accesso e autenticazione (il file ~postgres/data/pg_hda.conf), che deve essere ripristinata manualmente.
Al posto di utilizzare gli script già pronti per la copia e il recupero dei dati, è possibile avvalersi di comandi SQL. PostgreSQL fornisce un'istruzione speciale per permettere l'importazione e l'esportazione dei dati da e verso un file indipendente dalla piattaforma. Si tratta dell'istruzione COPY, la cui sintassi semplificata è quella seguente:
COPY relazione TO { 'file' | STDIN }
[ [WITH]
[BINARY]
[DELIMITER [AS] 'delimitatore']]
|
COPY relazione FROM { 'file' | STDIN }
[ [WITH]
[BINARY]
[DELIMITER [AS] 'delimitatore']]
|
Nella prima delle due forme, si esportano i dati verso un file o verso lo standard input; nella seconda si importano da un file o dallo standard output.
Se si usa l'opzione BINARY si ottiene un file «binario» indipendente dalla piattaforma; diversamente si ottiene un file di testo tradizionale. Nel caso del file di testo, ogni riga corrisponde a una tupla della relazione; gli attributi sono separati da un carattere di delimitazione, che in mancanza della definizione tramite l'opzione DELIMITER AS è un carattere di tabulazione. In ogni caso, anche se si specifica tale opzione, può trattarsi solo di un carattere. In pratica, sempre nell'ipotesi di creazione di un file di testo, ogni riga è organizzata secondo lo schema seguente:
attributo_1xattributo_2x...xattributo_n |
Nello schema, x rappresenta il carattere di delimitazione, che, come si può vedere, non viene inserito all'inizio e alla fine.
Quando l'istruzione COPY viene usata per importare dati dallo standard input, in formato testo, è necessario che dopo l'ultima riga che contiene attributi da inserire nella relazione, sia presente una sequenza di escape speciale: una barra obliqua inversa seguita da un punto (\.). Il file ottenuto quando si esporta verso lo standard output contiene questo simbolo di conclusione.
Il file di testo in questione può contenere anche altre sequenze di escape, che si trovano descritte nella tabella 75.34.
|
Tabella 75.34. Sequenze di escape nei file di testo generati e utilizzati da COPY.
|
|
È importante fare mente locale al fatto che l'istruzione viene eseguita dal servente. Ciò significa che i file, quando non si tratta di standard input o di standard output, sono creati o cercati secondo il file system che questo servente si trova ad avere sotto di sé. |
Segue la descrizione di alcuni esempi.
|
L'esempio mostra l'istruzione necessaria a emettere attraverso lo standard output del programma cliente (psql) la trasformazione in testo del contenuto della relazione Indirizzi.
|
Come nell'esempio precedente, generando però un formato binario, indipendente dalla piattaforma.
|
In questo caso, si genera il file di testo /tmp/prova nel file system dell'elaboratore servente, inoltre gli attributi sono separati attraverso una barra verticale (|).
|
In questo caso, si aggiungono tuple alla relazione Indirizzi, utilizzando quanto proviene dallo standard input, che si attende essere un file di testo (alla fine deve apparire la sequenza di escape \.).
|
Come nell'esempio precedente, attendendo i dati in formato binario.
|
Si aggiungono tuple alla relazione Indirizzi, utilizzando quanto proviene dal file /tmp/prova, che si trova nel file system dell'elaboratore servente. Il file deve essere in formato testo e gli attributi si intendono separati da una barra verticale (|).
PostgreSQL è un ORDBMS, ovvero un Object-relational DBMS, cioè un DBMS relazionale a oggetti. La sua documentazione utilizza terminologie differenti, a seconda delle preferenze dei rispettivi autori. In generale si possono distinguere tre modalità, riferite a tre punti di vista: la programmazione a oggetti, la teoria generale sui DBMS e il linguaggio SQL. Le equivalenze dei termini sono riassunte dallo schema seguente:
|
In questo capitolo si intende usare la terminologia tradizionale dei DBMS, dove i dati sono organizzati in relazioni, tuple e attributi, affiancando eventualmente i termini del linguaggio SQL tradizionale (tabelle, righe e colonne). Inoltre, la sintassi delle istruzioni (interrogazioni) SQL che vengono mostrate è limitata alle funzionalità più semplici, sempre compatibilmente con le possibilità di PostgreSQL. Per una visione più estesa delle funzionalità SQL di PostgreSQL conviene consultare la sua documentazione.
Per fare pratica con il linguaggio SQL, il modo migliore è quello di utilizzare il programma psql con il quale si possono eseguire interrogazioni interattive con il servente. Quello che conta è tenere a mente che per poterlo utilizzare occorre avere già creato una base di dati (vuota), in cui vanno poi inserite delle nuove relazioni, con le quali si possono eseguire altre operazioni.
Attraverso le istruzioni SQL si fa riferimento sempre a un'unica base di dati: quella a cui ci si collega quando si avvia psql.
|
Utilizzando psql, le istruzioni devono essere terminate con il punto e virgola (;), oppure dal comando interno \g (go). |
I tipi di dati gestibili sono un punto delicato della compatibilità tra un DBMS e lo standard SQL. Vale la pena di riepilogare i tipi più comuni, compatibili con lo standard SQL, che possono essere trovati nella tabella 75.42.
|
Tabella 75.42. Elenco dei tipi di dati standard utilizzabili con PostgreSQL.
|
Oltre ai tipi di dati gestibili, è necessario conoscere il modo di rappresentarli in forma costante. In particolare, è bene osservare che PostgreSQL ammette solo l'uso degli apici singoli come delimitatori; pertanto, per rappresentare un apice in una stringa delimitata in questo modo, lo si può raddoppiare, oppure si può usare la sequenza di escape \'. La tabella 75.43 mostra alcuni esempi.
|
Tabella 75.43. Esempi di rappresentazione dei valori costanti. Si osservi che in alcuni casi, conviene dichiarare il tipo di valore, seguito da una costante stringa che lo rappresenta, come in questi esempi a proposito di valori data-orario.
|
In particolare, le costanti stringa possono contenere delle sequenze di escape, rappresentate da una barra obliqua inversa seguita da un simbolo. La tabella 75.44 mostra le sequenze di escape tipiche e inserisce anche il caso del raddoppio degli apici singoli.
|
Tabella 75.44. Sequenze di escape utilizzabili all'interno delle stringhe di caratteri costanti.
|
PostgreSQL, come altri DBMS SQL, offre una serie di funzioni che fanno parte dello standard SQL, assieme ad altre non standard che però sono ampiamente diffuse e di grande utilità. Le tabelle 75.45 e 75.46 ne riportano alcune.
|
Tabella 75.45. Funzioni SQL riconosciute da PostgreSQL.
|
|
Tabella 75.46. Alcune funzioni riconosciute dal linguaggio di PostgreSQL.
|
Segue la descrizione di alcuni esempi.
|
Restituisce il valore due.
|
Restituisce un elenco delle posizioni in cui si trova la stringa ino all'interno dell'attributo Cognome, per tutte le tuple della relazione Indirizzi.
|
Restituisce la stringa ni.
|
Restituisce la stringa Ciao****.
|
Restituisce la stringa *****Ciao.
|
Restituisce la stringa Ciao.
|
Restituisce la stringa Ciao.
|
Esattamente come nell'esempio precedente, dal momento che lo spazio normale è il carattere predefinito e che la parola chiave BOTH è anche predefinita.
|
Restituisce la stringa Ciao****.
|
Restituisce la stringa *****Ciao.
Nelle sezioni seguenti vengono mostrati alcuni esempi comuni di utilizzo del linguaggio SQL, limitato alle possibilità di PostgreSQL. La sintassi non viene descritta, salvo quando la differenza tra quella standard e quella di PostgreSQL è importante.
Negli esempi si fa riferimento frequentemente a una relazione di indirizzi, il cui contenuto è visibile nella figura 75.57.
|
Figura 75.57. La relazione Indirizzi (Codice, Cognome, Nome, Indirizzo, Telefono) usata in molti esempi del capitolo. |
La relazione di esempio mostrata nella figura 75.57, potrebbe essere creata nel modo seguente:
|
Quando si inseriscono i valori per una tupla, può capitare che venga omesso l'inserimento di alcuni attributi. In questi casi, il campo corrispondente riceve il valore NULL, cioè un valore indefinito, oppure il valore predefinito attraverso quanto specificato con l'espressione che segue la parola chiave DEFAULT.
In alcuni casi non è possibile definire un valore predefinito e nemmeno è accettabile che un dato resti indefinito. In tali situazioni si può aggiungere l'opzione NOT NULL, dopo la definizione del tipo.
La modifica di una relazione implica l'intervento sulle caratteristiche degli attributi, oppure la loro aggiunta ed eliminazione. Seguono due esempi, con cui si aggiunge un attributo e poi lo si elimina:
|
|
L'esempio seguente modifica il tipo di un attributo già esistente:
|
Naturalmente, la conversione del tipo di un attributo può avere significato solo se i valori contenuti nelle tuple esistenti, in corrispondenza di quell'attributo, sono convertibili.
L'esempio seguente mostra l'inserimento dell'indirizzo dell'impiegato «Pinco Pallino».
|
In questo caso, si presuppone che i valori inseriti seguano la sequenza degli attributi, così come è stata creata la relazione in origine. Se si vuole indicare un comando più leggibile, occorre aggiungere l'indicazione della sequenza degli attributi da compilare, come nell'esempio seguente:
|
In questo stesso modo, si può evitare di compilare il contenuto di un attributo particolare, indicando espressamente solo gli attributi che si vogliono fornire; in tal caso gli altri attributi ricevono il valore predefinito o NULL in mancanza d'altro. Nell'esempio seguente viene indicato solo il codice e il nominativo:
|
Una relazione può essere eliminata completamente attraverso l'istruzione DROP. L'esempio seguente elimina la relazione degli indirizzi degli esempi già mostrati:
|
L'esempio seguente emette tutto il contenuto della relazione degli indirizzi già vista negli esempi precedenti:
|
Seguendo l'esempio fatto in precedenza si dovrebbe ottenere l'elenco riportato sotto, equivalente a tutto il contenuto della relazione.
codice cognome nome indirizzo telefono
1 Pallino Pinco Via Biglie 1 0222,222222
2 Tizi Tizio Via Tazi 5 0555,555555
3 Cai Caio Via Caini 1 0888,888888
4 Semproni Sempronio Via Sempi 7 0999,999999
|
Per ottenere un elenco ordinato in base al cognome e al nome (in caso di ambiguità), lo stesso comando si completa nel modo seguente:
|
codice cognome nome indirizzo telefono
3 Cai Caio Via Caini 1 0888,888888
1 Pallino Pinco Via Biglie 1 0222,222222
4 Semproni Sempronio Via Sempi 7 0999,999999
2 Tizi Tizio Via Tazi 5 0555,555555
|
La selezione degli attributi permette di ottenere un risultato che contenga solo quelli desiderati, permettendo anche di cambiarne l'intestazione. L'esempio seguente permette di mostrare solo i nominativi e il telefono, cambiando un po' le intestazioni:
|
Quello che si ottiene è simile all'elenco seguente:
cognomi nomi numeri_telefonici Pallino Pinco 0222,222222 Tizi Tizio 0555,555555 Cai Caio 0888,888888 Semproni Sempronio 0999,999999 |
La selezione delle tuple può essere fatta attraverso la condizione che segue la parola chiave WHERE. Nell'esempio seguente vengono selezionate le tuple in cui l'iniziale dei cognomi è compresa tra N e T.
|
Dall'elenco che si ottiene, si osserva che Caio è stato escluso:
codice cognome nome indirizzo telefono
1 Pallino Pinco Via Biglie 1 0222,222222
2 Tizi Tizio Via Tazi 5 0555,555555
4 Semproni Sempronio Via Sempi 7 0999,999999
|
Per evitare ambiguità possono essere indicati i nomi degli attributi prefissati dal nome della relazione a cui appartengono, separando le due parti con l'operatore punto (.). Nell'esempio seguente si selezionano solo il cognome, il nome e il numero telefonico, specificando il nome della relazione a cui appartengono gli attributi:
|
Ecco il risultato:
cognome nome telefono Pallino Pinco 0222,222222 Tizi Tizio 0555,555555 Cai Caio 0888,888888 Semproni Sempronio 0999,999999 |
Se dopo la parola chiave FROM si indicano più relazioni (ciò vale anche se si indica più volte la stessa relazione), si intende fare riferimento a una relazione generata dal «prodotto» di queste. Si immagini di abbinare alla relazione Indirizzi la relazione Presenze contenente i dati visibili nella figura 75.76.
|
Figura 75.76. La relazione Presenze (Codice, Giorno, Ingresso, Uscita). |
Come si può intendere, il primo attributo, Codice, serve a identificare la persona per la quale è stata fatta l'annotazione dell'ingresso e dell'uscita. Tale codice viene interpretato in base al contenuto della relazione Indirizzi. Si immagini di volere ottenere un elenco contenente tutti gli ingressi e le uscite, indicando chiaramente il cognome e il nome della persona a cui si riferiscono.
|
Ecco quello che si dovrebbe ottenere:
giorno ingresso uscita cognome nome 01-01-2012 07:30:00 13:30:00 Pallino Pinco 01-01-2012 07:35:00 13:37:00 Tizi Tizio 01-01-2012 07:45:00 14:00:00 Cai Caio 01-01-2012 08:30:00 16:30:00 Semproni Sempronio 01-02-2012 07:35:00 13:38:00 Pallino Pinco 01-02-2012 08:35:00 14:37:00 Tizio Tizi 01-02-2012 07:40:00 13:30:00 Semproni Sempronio |
Una stessa relazione può essere presa in considerazione come se si trattasse di due o più relazioni differenti. Per distinguere tra questi punti di vista diversi, si devono usare degli alias, che sono in pratica dei nomi alternativi. Gli alias si possono usare anche solo per questioni di leggibilità. L'esempio seguente è la semplice ripetizione di quello mostrato nella sezione precedente, con l'aggiunta però della definizione degli alias Pre e Nom.
|
Attraverso una vista, è possibile definire una relazione virtuale:
|
L'esempio mostra la creazione della vista Presenze_dettagliate, ottenuta dalle relazioni Presenze e Indirizzi. In pratica, questa vista permette di interrogare direttamente la relazione virtuale Presenze_dettagliate, invece di utilizzare ogni volta un comando SELECT molto complesso, per ottenere lo stesso risultato.
La modifica di tuple già esistenti avviene attraverso l'istruzione UPDATE, la cui efficacia viene controllata dalla condizione posta dopo la parola chiave WHERE. Se tale condizione manca, l'effetto delle modifiche si riflette su tutte le tuple della relazione.
L'esempio seguente, aggiunge un attributo alla relazione degli indirizzi, per contenere il nome del comune di residenza degli impiegati; successivamente viene inserito il nome del comune Sferopoli in base al prefisso telefonico.
|
|
In pratica, viene aggiornata solo la tupla dell'impiegato Pinco Pallino.
L'esempio seguente elimina dalla relazione delle presenze le tuple riferite alle registrazioni del giorno 01/01/2012 e le eventuali antecedenti.
|
L'esempio seguente crea la relazione mia_prova dalla fusione della relazioni degli indirizzi e delle presenze, come già mostrato in un esempio precedente:
|
L'esempio seguente aggiunge alla relazione dello storico delle presenze le registrazioni vecchie che poi vengono cancellate.
|
Quando si creano delle relazioni in una base di dati, tutti gli altri utenti che sono stati registrati nel sistema del DBMS, potrebbero accedervi e fare le modifiche che vogliono. Per controllare questi accessi, l'utente proprietario delle relazioni (di solito è colui che le ha create), può usare le istruzioni GRANT e REVOKE. La prima permette a un gruppo di utenti di eseguire operazioni determinate, la seconda toglie dei privilegi.
GRANT {ALL | SELECT | INSERT | UPDATE | DELETE
| RULE}[,...]
ON relazione[,...]
TO {PUBLIC | GROUP gruppo | utente}
|
REVOKE {ALL | SELECT | INSERT | UPDATE | DELETE
| RULE}[,...]
ON relazione[,...]
FROM {PUBLIC | GROUP gruppo | utente}
|
La sintassi delle due istruzioni è simile, basta fare attenzione a cambiare la parola chiave TO con FROM. I gruppi e gli utenti sono nomi che fanno riferimento a quanto registrato all'interno del DBMS.
L'esempio seguente toglie a tutti gli utenti (PUBLIC) tutti i privilegi sulle relazioni delle presenze e degli indirizzi; successivamente vengono ripristinati tutti i privilegi solo per l'utente tizio:
|
La gestione delle transazioni richiede che queste siano introdotte dall'istruzione START TRANSACTION:
START TRANSACTION |
L'esempio seguente mostra il caso in cui si voglia isolare l'inserimento di una tupla nella relazione Indirizzi all'interno di una transazione, che alla fine viene confermata regolarmente con l'istruzione COMMIT:
|
Nell'esempio seguente, si rinuncia all'inserimento della tupla con l'istruzione ROLLBACK finale:
|
La gestione dei cursori da parte di PostgreSQL è abbastanza compatibile con lo standard, a parte il fatto che avviene fuori dal contesto previsto, che viene consentito un accesso in sola lettura e che non è possibile assegnare i dati a delle variabili.
|
La gestione dei cursori riguarda generalmente gli accessi a un DBMS tramite codice SQL incorporato in un programma (che usa un altro linguaggio), mentre PostgreSQL estende il loro utilizzo anche se il «programma» in questione è costituito esclusivamente da codice SQL. |
La dichiarazione di un cursore si ottiene nel modo solito, con la differenza che questa deve avvenire esplicitamente in una transazione. In particolare, con PostgreSQL, il cursore viene aperto automaticamente nel momento della dichiarazione, per cui l'istruzione OPEN non è disponibile.
|
L'esempio mostra la dichiarazione dell'inizio di una transazione, assieme alla dichiarazione del cursore Mio_cursore, per selezionare tutta la relazione Indirizzi in modo ordinato per Cognome. Si osservi che per PostgreSQL la selezione che si ingloba nella gestione di un cursore non può aggiornarsi automaticamente se i dati originali cambiano, per cui è come se fosse sempre definita la parola chiave INSENSITIVE.
|
L'esempio mostra l'uso tipico dell'istruzione FETCH, in cui si preleva la tupla successiva rispetto alla posizione corrente del cursore e più avanti si conclude la transazione con un COMMIT. L'esempio seguente è identico, con la differenza che si indica espressamente il passo.
|
Un cursore dovrebbe essere chiuso attraverso una richiesta esplicita, con l'istruzione CLOSE, ma la chiusura della transazione chiude implicitamente il cursore, se questo dovesse essere rimasto aperto. L'esempio seguente riepiloga quanto visto sopra, completato dell'istruzione CLOSE.
|
Il linguaggio SQL dispone dell'istruzione SET TIME ZONE per definire l'ora locale e di conseguenza lo scostamento dal tempo universale. PostgreSQL dispone della stessa istruzione che funziona in modo molto simile allo standard; per la precisione, la definizione dell'ora locale avviene attraverso le definizioni riconosciute dal sistema operativo (nel caso di GNU/Linux si tratta delle definizioni che si articolano a partire dalla directory /usr/share/zoneinfo/).
SET TIME ZONE { 'definizione_ora_locale' | LOCAL }
|
Per esempio, per definire che si vuole fare riferimento all'ora locale italiana, si potrebbe usare il comando seguente:
|
Questa impostazione riguarda la visione del programma cliente, mentre il programma servente può essere stato preconfigurato attraverso le variabili di ambiente LC_* oppure la variabile LANG, che in questo caso hanno effetto sullo stile di rappresentazione delle informazioni data-orario. Anche il programma cliente può essere preconfigurato attraverso la variabile di ambiente PGTZ, assegnandole gli stessi valori che si possono utilizzare per l'istruzione SET TIME ZONE.
PgAccess (2) (ovvero PostgreSQL Access) è un componente di una libreria Tcl/Tk: LibPgTcl. A volte viene distribuito come un pacchetto autonomo, che comunque dipende dalla libreria indicata, oppure viene incluso nello stesso pacchetto della libreria. PgAccess è un programma frontale (che utilizza l'interfaccia grafica) per accedere alle funzionalità di PostgreSQL.
|
Prima di poter utilizzare qualunque programma frontale per PostgreSQL, occorre ricordare di configurare correttamente PostgreSQL stesso, in modo che questo consenta gli accessi previsti. |
PgAccess è costituito in pratica dall'eseguibile pgaccess, che si utilizza senza argomenti e si presenta inizialmente come si vede nella figura 75.94.
|
Figura 75.94. Finestra iniziale di PgAccess, quando viene avviato per la prima volta dall'utente. |
Mentre lo si usa, PgAccess memorizza alcune informazioni nella directory ~/.pgaccess/ e questo fatto facilita successivamente le operazioni di accesso alla base di dati da parte dell'utente.
|
PostgreSQL è un DBMS in grado di gestire diverse basi di dati simultaneamente; pertanto, con PgAccess è necessario stabilire per prima cosa quale sia la base di dati. Dal menù {Database}, si seleziona la funzione {Open}, ottenendo la mascherina che si vede nella figura 75.95. Da lì si possono indicare tutte le informazioni necessarie alla connessione con la base di dati desiderata; in particolare, per quanto riguarda le informazioni sull'autenticazione, queste sono richieste solo in base al modo in cui sono stati regolati i permessi di accesso da parte di PostgreSQL.
|
Figura 75.95. Connessione alla base di dati nanodb, presso il nodo 172.17.1.254, come utente pgnanouser. |
|
Attraverso PgAccess non è possibile creare una base di dati. Per questo occorre usare le funzioni di PostgreSQL, descritto nella sezione 75.2. |
La base di dati aperta, assieme all'indicazione del nodo presso il quale si trova il DBMS con cui si interagisce, appare in basso, nella finestra principale di PgAccess.
|
Figura 75.96. Quando è attiva una connessione con una base di dati, lo si vede dalle informazioni che appaiono in basso nella finestra principale di PgAccess. |
È importante ricordare che PgAccess tiene nota dell'ultima base di dati aperta attraverso i file di configurazione contenuti in ~/.pgaccess/; in questo modo la connessione viene ritentata automaticamente all'avvio del programma la volta successiva che lo si utilizza.
Dal punto di vista di PgAccess, una base di dati contiene degli «oggetti» (secondo la stessa filosofia di PostgreSQL). Questi possono essere delle relazioni, il risultato di interrogazioni SQL, delle viste, delle stampe o altro ancora.
Per intervenire su ognuno di questi oggetti basta selezionare la voce relativa che si trova sulla parte sinistra (nella figura 75.97 si vede selezionata la gestione delle «tabelle», ovvero delle relazioni).
|
Figura 75.97. L'aspetto di PgAccess quando viene evidenziata a sinistra la voce { |
Premendo il tasto destro del mouse quando il puntatore si trova nel riquadro centrale, si ottiene un menù a scomparsa, con il quale è possibile modificare gli oggetti a cui fa riferimento la voce selezionata a sinistra; in alternativa, le stesse voci sono disponibili dal menù {Object}. In particolare, la voce {New} serve a creare un oggetto nuovo, {Open} serve ad accedervi e {Design} serve a modificarne la struttura (ammesso che ciò sia consentito in base al tipo di oggetto). Eventualmente è possibile anche modificare il nome dell'oggetto e visualizzarne la struttura.
|
PgAccess gestisce una serie aggiuntiva di oggetti rispetto a quanto fa PostgreSQL. Per realizzarli, PgAccess gestisce delle relazioni proprie, che non vengono mostrate all'utente, distinguibili per il fatto di avere un nome che inizia per pga_. In generale, queste relazioni hanno tutti i permessi di accesso per tutti gli utenti di PostgreSQL. |
La figura 75.98 mostra l'esempio della creazione di una relazione molto semplice, per contenere una serie di indirizzi. Alla creazione della relazione, dopo avere selezionato la voce relativa a questo tipo di oggetto, si accede selezionando la voce {New} del menù {Object}.
|
Figura 75.98. Finestra per la creazione di una relazione. |
Una volta creata la relazione (si ottiene questo confermando con il pulsante grafico <Create table>), il suo nome appare nella parte centrale della finestra principale del programma; per accedere al suo contenuto basta selezionare la voce {Open} dal menù Object, ottenendo così una tabella di scorrimento con la quale si possono aggiungere e modificare righe preesistenti. La figura 75.99 mostra l'inserimento di alcuni nomi. Si osservi in particolare il fatto che, eventualmente, si può richiedere espressamente l'aggiunta di una riga nuova premendo il terzo tasto del mouse.
|
Figura 75.99. Finestra per lo scorrimento del contenuto di una relazione. |
Vale la pena di osservare che la maschera di scorrimento e inserimento dati nella relazione, permette di leggere le tuple in ordine, in base a un certo attributo, filtrando eventualmente le tuple in base a una condizione. Si stabilisce questo mettendo il nome di un attributo nella casella Sort field e mettendo l'espressione della condizione di filtro nella casella Filter conditions: se poi si seleziona il pulsante grafico <Reload> (che comunque appare come un'icona), si riottiene il contenuto ordinato e filtrato in base alle preferenze indicate.
È possibile realizzare facilmente dei modelli di interrogazione e delle viste, attraverso la selezione delle voci {Queries} e {Views} (nella parte sinistra della finestra, sotto alla voce {Tables}). Nel primo caso si tratta di interrogazioni SQL che vengono memorizzate da PgAccess e richiamate a piacere, mentre nel secondo si tratta di viste vere e proprie. A livello operativo, con PgAccess le due cose sono praticamente identiche, per cui si passa generalmente per la creazione di un'interrogazione SQL che poi, eventualmente, si salva come vista. La figura 75.100 mostra la definizione dell'interrogazione Nominativi, abbinata al comando SELECT Cognome, Nome FROM "Indirizzi", scritto manualmente dall'utilizzatore.
|
Figura 75.100. Finestra per la creazione di un'interrogazione. |
Nella figura si può osservare che è disponibile una casella di selezione attraverso la quale si può richiedere di salvare come vista. In particolare, con il pulsante grafico <Save query definition> si salva il modello dell'interrogazione, con il nome fissato in alto; ma volendo, con il pulsante grafico <Visual designer>, si accede a una maschera per la definizione grafica dell'interrogazione, come si vede nella figura 75.101.
|
Figura 75.101. Finestra per la creazione visuale di un'interrogazione. |
In alto appare una casella in cui si deve indicare il nome di una relazione da cui si vogliono prelevare i campi; una volta fatto, appare un riepilogo di questi campi, in un riquadro. Questi nomi possono essere trascinati con il puntatore del mouse, in basso, dove vengono elencati i campi da includere nell'interrogazione; se si sbaglia, gli elementi che si vogliono togliere possono essere cancellati premendo il tasto [Canc] ([Del] nelle tastiere inglesi). Nella figura mostrata, sono già stati trascinati e depositati i campi del nome e del cognome.
Al termine, se si è soddisfatti del risultato, si può confermare con il pulsante grafico <Save to query builder>, ritrovando poi nella finestra precedente l'interrogazione corrispondente alle scelte fatte, che può essere ritoccata a mano se lo si desidera. Nel caso dell'esempio mostrato, l'interrogazione SQL che si ottiene è:
|
L'apertura di un'interrogazione o di una vista, genera lo scorrimento del risultato dell'interrogazione, oppure della vista, come si vede nella figura 75.103 che fa sempre riferimento agli esempi precedenti.
|
Figura 75.103. Scorrimento di una vista. |
Con PgAccess è possibile definire anche delle stampe, nel senso di rapporti stampati contenenti il risultato di un'interrogazione SQL. La figura 75.104 mostra la finestra che si utilizza per questo scopo, dove è già iniziata la compilazione dello schema di stampa.
|
Figura 75.104. Creazione di un tabulato. |
Una volta selezionata la relazione da cui prelevare i campi, dopo aver indicato il nome del tabulato che si vuole generare, basta fare un clic con il tasto sinistro del mouse mentre si punta sul nome del campo che si vuole inserire sullo schema di destra (che rappresenta il modello della stampa). Una volta che sono apparsi i nomi nello spazio a destra, questi possono essere trascinati dove si vuole, eventualmente possono anche essere cancellati usando il tasto [Canc]. Nell'esempio della figura, si vede anche che è stato inserito un titolo.
Spostando il puntatore del mouse sullo spazio che rappresenta lo schema di stampa, si vede cambiare la sua descrizione in alto. Nella figura mostrata viene indicato Page footer, perché in quel momento il puntatore del mouse era nella penultima riga di quello schema.
Per verificare il risultato, è disponibile anche un'anteprima, che si ottiene selezionando il pulsante grafico <Preview>. Seguendo gli esempi precedenti, la figura 75.105 mostra questa anteprima. Da lì si può passare alla stampa, che però potrebbe limitarsi a generare un file PostScript.
|
Figura 75.105. Anteprima di stampa. |
WWW-SQL (3) è un programma CGI in grado di creare pagine HTML a partire dalle informazioni ottenute da una base di dati PostgreSQL o MySQL. In questo capitolo si vuole vedere in particolare l'interazione rispetto alle basi di dati di PostgreSQL. In ogni caso, per poter leggere questo capitolo, occorre sapere cosa sia un programma CGI e come interagisce con un servente HTTP, come spiegato nel capitolo 40.
È molto probabile che la propria distribuzione GNU abbia organizzato due pacchetti distinti, in base all'uso che se ne intende fare, per l'abbinamento con PostgreSQL, oppure con MySQL. In questo modo, il nome del programma CGI a cui si deve fare riferimento può cambiare leggermente, anche da una distribuzione all'altra. Qui si fa riferimento al nome www-pgsql per quello che riguarda l'uso con PostgreSQL.
Ammesso che il pacchetto organizzato dalla propria distribuzione sia stato realizzato nel modo corretto, l'eseguibile www-pgsql dovrebbe trovarsi nella directory più adatta per i programmi CGI, ovvero quella a cui si accede normalmente con l'URI http://localhost/cgi-bin/. In tal caso, per accedere a questo programma, basta avviare il proprio navigatore preferito e puntare sull'indirizzo http://localhost/cgi-bin/www-pgsql. Ma non basta, dal momento che il programma in questione ha bisogno di interpretare un file HTML speciale dal quale restituisce poi un risultato. Per capire come funziona la cosa, prima ancora di avere affrontato lo studio del linguaggio specifico di WWW-SQL, si può provare con un file HTML normale: si supponga di avere a disposizione il file http://localhost/index.html; per fare in modo che WWW-SQL lo analizzi, basta indicare l'URI http://localhost/cgi-bin/www-pgsql/index.html. Il risultato è identico all'originale, ma per arrivare a questo si passa attraverso l'elaborazione del programma CGI, dimostrando così il suo funzionamento.
Volendo, se il proprio programma servente HTTP è Apache, è possibile rendere la cosa più elegante attraverso una configurazione opportuna del file srm.conf. Per esempio si potrebbe fare in modo che i file che terminano con l'estensione .pgsql vengano elaborati automaticamente attraverso il programma CGI in questione:
|
Tuttavia, occorre considerare che alcune installazioni di Apache sono state predisposte in modo da impedire l'utilizzazione dell'istruzione Action. Se dopo le modifiche di questo file, il servizio di Apache non si riavvia, ciò potrebbe essere un sintomo di questo problema.
Perché il programma CGI possa accedere alle basi di dati di PostgreSQL, occorre ricordare di predisporre gli utenti e i permessi necessari all'interno della gestione delle basi di dati stesse. Potrebbe essere conveniente prevedere la possibilità di accesso per l'utente di sistema usato dal processo elaborativo del servente HTTP, quando esegue i programmi CGI, in modo da semplificare l'istruzione necessaria alla connessione. Supponendo che si tratti dell'utente www-cgi, volendo procedere in questo modo, occorre aggiungere tale utente, con lo stesso nome, nel sistema di PostgreSQL:
postgres$ createuser www-cgi[Invio]
Quindi occorre intervenire nelle basi di dati regolando i permessi attraverso i comandi GRANT e REVOKE, tenendo conto che a questo proposito si può consultare quanto già spiegato nella sezione 75.1. Per fare un esempio, volendo concedere l'accesso in lettura alla relazione Indirizzi, della base di dati anagrafe, all'utente www-cgi, si potrebbe agire come si vede di seguito:
postgres$ psql anagrafe[Invio]
anagrafe=> GRANT SELECT ON Indirizzi TO www-cgi;[Invio]
WWW-SQL interpreta un file HTML alla ricerca di istruzioni secondo il formato schematizzato di seguito:
<! SQL comando [argomento...] > |
Come si vede, queste istruzioni assomigliano a dei commenti per l'HTML, ma anche se non lo sono realmente, di solito i navigatori ignorano dei marcatori di questo tipo. Tuttavia, questa si può considerare solo come una misura di sicurezza, dal momento che questi file non dovrebbero essere raggiunti direttamente, ma solo attraverso l'intermediazione di WWW-SQL.
Le istruzioni di WWW-SQL rappresentano un linguaggio di programmazione, semplice, ma efficace per lo scopo che ci si prefigge. Si osservi che il «comando» è una parola chiave che rappresenta il tipo di azione che si intende svolgere; inoltre, gli argomenti possono essere presenti o meno, in funzione del comando. Gli argomenti di un comando possono essere racchiusi tra apici doppi ("..."): all'interno di queste stringhe si possono indicare delle variabili da espandere e si possono usare anche delle sequenze di escape per rappresentare simboli speciali che altrimenti avrebbero un altro significato.
|
Le parole chiave che costituiscono le istruzioni di WWW-SQL possono essere scritte indipendentemente utilizzando lettere maiuscole o minuscole. Inoltre, lo spazio dopo il delimitatore iniziale <! e lo spazio prima del delimitatore finale > sono facoltativi. |
Per iniziare subito con un esempio che faccia capire la logica di funzionamento di WWW-SQL, si osservi il «programma» seguente, rappresentato dal file variabili.pgsql:
|
L'unica istruzione per WWW-SQL è <!SQL PRINT...>, con la quale si vuole ottenere la visualizzazione di una stringa tra apici doppi. Si osservi che $var è il riferimento alla variabile var, che viene espanso, come parte della valutazione della stringa.
Come si può intuire leggendo l'esempio, i campi definiti attraverso i modelli (gli elementi FORM), si traducono in variabili per WWW-SQL.
Per verificare il funzionamento di questo programma, supponendo di avere collocato il file variabili.pgsql nella directory iniziale dei documenti HTML offerti dal servente HTTP, basta puntare il navigatore sull'indirizzo http://localhost/cgi-bin/www-pgsql/variabili.pgsql (sempre ammettendo che l'indirizzo http://localhost/cgi-bin/www-pgsql corrisponda all'avvio del programma CGI che costituisce in pratica WWW-SQL).
Quello che si ottiene dovrebbe essere un modulo HTML molto semplice, dove si può inserire un testo. Inviando il modulo compilato, dovrebbe essere restituito lo stesso modulo, con la stringa iniziale aggiornata, dove viene mostrato che è stato recepito il dato inserito (nella figura 75.108 si vede che è stata inviata la stringa «Saluti».
|
Figura 75.108. Risultato dell'interpretazione del file |
|
I sorgenti di WWW-SQL possono essere compilati in modo differente. In particolare, si può distinguere tra due tipi di scansione: il tipo vecchio non permette l'uso di istruzioni che prevedono un'iterazione. In pratica, in quel caso, non funzionano i cicli iterativi e gli altri comandi correlati. |
Si distinguono due tipi di espressioni che si possono valutare all'interno delle istruzioni di WWW-SQL: quelle che si applicano ai valori numerici e quelle che si applicano alle stringhe. Le tabelle 75.109 e 75.110 elencano gli operatori che possono essere utilizzati a questo proposito. Si osservi in particolare l'operatore :, che permette di fare un confronto tra una stringa e un'espressione regolare.
|
Tabella 75.109. Elenco degli operatori utilizzabili con operandi numerici.
|
|
Tabella 75.110. Elenco degli operatori utilizzabili con operandi di tipo stringa.
|
All'interno delle stringhe è prevista l'espansione di variabili e sono anche riconosciute alcune sequenze di escape (tabella 75.111). Le variabili in questione vanno intese come parte del linguaggio di WWW-SQL; alcune di queste sono la ripetizione di variabili di ambiente corrispondenti, altre sono variabili interne del programma (come elencato nella tabella 75.112), altre ancora possono essere definite all'interno del «programma» stesso, o meglio ancora, attraverso dei moduli, come è stato mostrato nell'esempio iniziale. Le variabili vengono riconosciute in quanto scritte secondo lo schema seguente:
prefissonome_della_variabile |
Il prefisso è un simbolo a scelta tra: $, @, ?, #. In pratica, $var, @var, ?var, e #var, sono riferimenti identici alla stessa variabile var. Per questo motivo, se si vogliono usare i simboli corrispondenti a questi prefissi in modo letterale, occorre usare una sequenza di escape.
|
Tabella 75.111. Sequenze di escape utilizzabili all'interno delle stringhe.
|
|
Tabella 75.112. Variabili interne di WWW-SQL.
|
Per prendere confidenza con le variabili interne di WWW-SQL, si può realizzare lo script seguente (interne.pgsql), che con l'istruzione <!SQL DUMPVARS> le elenca tutte. La figura 75.114 mostra il risultato che si potrebbe ottenere.
|
|
Figura 75.114. Esempio del contenuto delle variabili interne attraverso l'istruzione <!SQL DUMPVARS>.
|
Attraverso le istruzioni di WWW-SQL, si possono realizzare le strutture di controllo che sono comuni nei linguaggi di programmazione. È prevista la struttura condizionale e il ciclo iterativo.
<! SQL IF espressione >
...
[<! SQL ELSIF espressione >]
...
[<! SQL ELSE >]
...
<! SQL ENDIF >
|
La struttura condizionale che si vede nello schema, permette di delimitare uno spazio da filtrare in base all'esito delle espressioni condizionali coinvolte. Si osservi l'esempio seguente:
|
In questo modo si condiziona la visualizzazione di una frase in base al fatto che la variabile NUM_ROWS contenga o meno il valore 10.
|
È importante osservare che l'espressione usata come condizione di controllo potrebbe restituire un risultato numerico e non logico. In tal caso, lo zero corrisponde a Falso, mentre qualunque altro valore corrisponde a Vero. |
<! SQL WHILE espressione >
...
<! SQL DONE >
|
La struttura iterativa che si vede nello schema, permette di delimitare uno spazio da interpretare ripetitivamente, finché l'espressione condizionale introduttiva continua a restituire il valore Vero (o un valore numerico diverso da zero).
|
L'esempio mostra l'inizializzazione di una variabile, denominata contatore, al valore iniziale 10; quindi inizia un ciclo iterativo che si arresta quando tale variabile raggiunge lo zero. A ogni ciclo, viene visualizzato il contenuto della variabile, che subito dopo viene ridotto di un'unità.
|
Se l'istruzione <!SQL WHILE...> non viene riconosciuta, significa che non è disponibile la scansione iterativa. |
Nell'ambito di un'iterazione, possono essere usate delle istruzioni per interrompere il ciclo in corso o per interrompere tutta l'iterazione:
<! SQL CONTINUE > |
<! SQL BREAK > |
La prima della due istruzioni interrompe il ciclo attuale, facendo riprendere immediatamente l'iterazione, mentre il secondo interrompe l'iterazione del tutto.
Esiste anche un altro tipo di iterazione, il cui scopo è la scansione delle righe ottenute dall'interrogazione di una base di dati:
<! SQL PRINT_LOOP riferimento_all'interrogazione >
...
<! SQL DONE >
|
Anche all'interno di questa struttura si possono usare le istruzioni <!SQL CONTINUE> e <!SQL BREAK>.
Le istruzioni «normali» di WWW-SQL, ovvero quelle che non servono a descrivere delle strutture di controllo, sono descritte in questa sezione e in quelle seguenti. In particolare si può notare che WWW-SQL offre delle istruzioni per la lettura semplificata dell'esito di un'interrogazione SQL e altre per la lettura dettagliata, fino ad arrivare a distinguere tupla per tupla e attributo per attributo.
|
È importante chiarire che, anche se un'«interrogazione» serve principalmente per leggere dati da una relazione di una base di dati, nello stesso modo, attraverso WWW-SQL si potrebbero fare delle modifiche ai dati. |
Segue un elenco di istruzioni di tipo vario, mentre nelle sezioni seguenti vengono raccolte altre istruzioni più specifiche.
Emissione di una stringa con espansione di variabili:
<! SQL PRINT stringa > |
L'istruzione <!SQL PRINT ...> permette di emettere una stringa. Dal momento che un file HTML non ha bisogno di accorgimenti particolari per mostrare una stringa costante, è evidente che il senso di questa istruzione sta nella possibilità di indicare delle variabili da espandere, come nell'esempio seguente:
|
Risultato di un'espressione:
<! SQL EVAL espressione > |
L'istruzione <!SQL EVAL ...> è simile a <!SQL PRINT ...>, con la differenza che l'argomento non è più una stringa, ma un'espressione differente, il cui risultato viene emesso alla fine.
Impostazione di una variabile:
<! SQL SET nome_variabile valore_da_assegnare > |
L'istruzione <!SQL SET ...> permette di definire e inizializzare una variabile. L'esempio seguente definisce la variabile contatore, inizializzandola a zero:
|
Impostazione di una variabile attraverso un'espressione:
<! SQL SETEXPR nome_variabile espressione > |
L'istruzione <!SQL SETEXPR ...> permette di definire e inizializzare una variabile; in particolare, il valore che si assegna può essere il risultato della valutazione di un'espressione. L'esempio seguente definisce la variabile contatore, inizializzandola con il risultato dell'espressione $contatore - 1. In pratica viene decrementato il contenuto della variabile contatore:
|
Definizione di un valore predefinito per il contenuto di una variabile:
<! SQL SETDEFAULT nome_variabile valore_da_assegnare > |
L'istruzione <!SQL SETDEFAULT ...> permette di stabilire un valore predefinito per una variabile; a differenza di <!SQL SET ...> la variabile non viene modificata se esiste già e ha un valore. L'esempio seguente definisce la variabile contatore, solo se necessario, inizializzandola con il valore 10:
|
Elenco variabili:
<! SQL DUMPVARS > |
L'istruzione <!SQL DUMPVARS> emette l'elenco delle variabili esistenti, assieme al valore che contengono. Può essere usato per scopo diagnostico, quando si cerca di capire cosa succede realmente.
L'interrogazione di una base di dati deve essere preceduta dalla connessione a un servente DBMS e dalla selezione di una base di dati; inoltre, al termine delle interrogazioni, si passa normalmente alla chiusura di una connessione, in pratica secondo lo schema seguente:
<! SQL CONNECT ... > <! SQL DATABASE nome_della_base_di_dati > ... ... <! SQL CLOSE > |
In breve: <!SQL CONNECT ...> serve a iniziare una connessione con un servente per l'accesso a una base di dati; <!SQL DATABASE ...> serve a indicare la base di dati specifica presso il servente; <!SQL CLOSE> chiude la connessione.
Accesso a un servente DBMS:
<! SQL CONNECT [nodo [utente [parola_d'ordine]]] > |
L'istruzione <!SQL CONNECT ...> permette di iniziare una connessione con un DBMS. Dipende dal DBMS stesso se è possibile accedere senza alcun sistema di autenticazione. In generale, se non si indica il nodo a cui accedere, si intende localhost; inoltre, se non si indica l'utente, si fa riferimento al numero UID con il quale funziona il programma servente del servizio HTTP (che a sua volta avvia il programma CGI). L'esempio che segue richiede di connettersi al servente DBMS PostgreSQL che opera nello stesso elaboratore locale, utilizzando l'identità dell'utente pgnanouser e senza specificare alcuna parola d'ordine:
|
Selezione di una base di dati specifica:
<! SQL DATABASE nome_base_di_dati > |
L'istruzione <!SQL DATABASE ...> permette di aprire una base di dati specifica; per la precisione, utilizzando PostgreSQL, l'accesso al servente avviene solo dopo che è stata specificata la base di dati.
Chiusura di una connessione:
<! SQL CLOSE > |
La chiusura di una connessione (e quindi anche di una base di dati aperta), si ottiene con l'istruzione <!SQL CLOSE>.
Prima di passare alla descrizione delle istruzioni che permettono l'interrogazione del contenuto di una base di dati, viene mostrato un esempio che si limita a elencare la relazione Indirizzi della base di dati anagrafe:
|
L'interrogazione di una base di dati avviene attraverso la definizione di un riferimento, che si apre e si chiude come se fosse un flusso di file nei linguaggi di programmazione comuni. Per aprire questo riferimento si inizia con l'invio di un'interrogazione SQL; successivamente è possibile leggere l'esito dell'interrogazione attraverso il riferimento che è stato aperto; infine si passa alla chiusura del riferimento:
<! SQL QUERY stringa_di_interrogazione_sql riferimento > ... ... <! SQL FREE riferimento > |
Apertura di un'interrogazione:
<! SQL QUERY stringa_di_interrogazione_sql riferimento > |
L'istruzione <!SQL QUERY ...> definisce una stringa di interrogazione da inviare al servente DBMS. A questa interrogazione viene abbinato un riferimento costituito da un nome, che in seguito deve essere usato per leggere l'esito dell'interrogazione. Nell'esempio che appare nella sezione precedente, si vedeva l'istruzione seguente con la quale si selezionano tutte le tuple della relazione Indirizzi, abbinando questo risultato al nome RICHIESTA_1:
|
Tabella rapida:
<! SQL QTABLE riferimento [borders] > |
L'istruzione <!SQL QTABLE ...> consente di rappresentare rapidamente il risultato di un'interrogazione attraverso una tabella HTML. In particolare, utilizzando la parola chiave borders, la tabella che si genera ha i bordi delle caselle visibili. L'esempio seguente mostra in che modo visualizzare rapidamente il risultato dell'interrogazione abbinata al nome RICHIESTA_1:
|
Elenco rapido:
<! SQL QLONGFORM riferimento > |
L'istruzione <!SQL QLONGFORM ...> si utilizza in modo simile a <!SQL QTABLE ...>, per rappresentare il risultato di un'interrogazione attraverso un elenco dettagliato, senza una tabella HTML.
Chiusura del riferimento all'interrogazione:
<! SQL FREE riferimento > |
Come è stato mostrato all'inizio, l'istruzione <!SQL FREE ...> serve a chiudere il riferimento a un'interrogazione.
Realizzazione di un elenco di voci da selezionare:
<! SQL QSELECT riferimento variabile_modulo_html > |
Con l'istruzione <!SQL QSELECT ...> si ottiene un elenco di voci di un modulo di selezione. In generale, la cosa corrisponde a:
<SELECT NAME="variabile_modulo_html"> <! SQL PRINT_ROWS riferimento \ |
L'istruzione <!SQL PRINT_ROWS ...> è descritta nella prossima sezione.
È possibile selezionare in maniera più precisa le tuple e gli attributi da ciò che si ottiene da un'interrogazione SQL. Attraverso l'istruzione <!SQL FETCH riferimento> si preleva la tupla attuale dall'interrogazione a cui si fa riferimento. Questo prelievo permette di fare riferimento agli attributi della tupla attraverso una notazione particolare:
@riferimento.n |
In pratica, è come se fosse l'espansione di una variabile, con la differenza che si indica il nome di un riferimento a un'interrogazione aperta, aggiungendo un'estensione numerica, separata da un punto, dove lo zero corrisponde al primo attributo e n-1 corrisponde all'attributo n-esimo.
Spostamento della tupla attuale all'interno del risultato di un'interrogazione:
<! SQL SEEK riferimento n_riga > |
L'istruzione <!SQL SEEK ...> permette di spostare la tupla attuale all'interno di un'interrogazione. Per indicare il numero della tupla da raggiungere, occorre tenere presente che lo zero corrisponde alla prima. L'esempio seguente fa in modo che la tupla attuale diventi la seconda del riferimento RICHIESTA_1:
|
Prelievo della tupla attuale di un certo riferimento:
<! SQL FETCH riferimento > |
L'istruzione <!SQL FETCH ...> permette di rendere disponibile il contenuto della tupla attuale di un certo riferimento. L'esempio seguente preleva il contenuto della tupla attuale del riferimento RICHIESTA_1; quindi mostra il primo e il secondo attributo di questa tupla, che si presume corrispondano al cognome e al nome di una persona:
|
Emissione di una stringa per ogni tupla:
<! SQL PRINT_ROWS riferimento stringa > |
L'istruzione <!SQL PRINT_ROWS ...> è una sorta di istruzione <!SQL PRINT ...> ripetuta per tutte le tuple di un'interrogazione, a partire da quella corrente. L'esempio seguente mostra la visualizzazione dei primi due attributi di tutte le tuple di un'interrogazione, a cui si fa riferimento con il nome Q:
|
L'esempio seguente mostra la realizzazione di un modulo per la selezione di un articolo, attraverso l'invio del codice corrispondente. A questo proposito, si suppone che il primo attributo del risultato dell'interrogazione a cui si fa riferimento con il nome ELENCO, corrisponda al codice dell'articolo, mentre il secondo corrisponda a una sua descrizione:
|
Dal momento che si fa riferimento alle prime due colonne, la stessa cosa avrebbe potuto essere realizzata con l'istruzione <!SQL QSELECT ...>, nel modo seguente:
|
PostgreSQL, http://www.postgresql.org/
The PostgreSQL Global Development Group, PostgreSQL Documentation, http://www.postgresql.org/docs/manuals/
James Henstridge, WWW-SQL, http://www.jamesh.id.au/software/www-sql/www-sql.html
1) PostgreSQL software libero con licenza speciale
2) PgAccess software libero con licenza speciale
«a2» 2013.11.11 --- Copyright © Daniele Giacomini -- appunti2@gmail.com http://informaticalibera.net
