Capitolo 75.   PostgreSQL

$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

75.1   Struttura e preparazione

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.

75.1.1   Struttura dei dati nel file system

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).

75.1.2   Amministratore

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:

postgres:!:101:101:PostgreSQL Server:/var/lib/postgres:/bin/bash

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 pg_hba.conf, che viene descritto in seguito, all'interno di questo capitolo. Negli esempi che si mostrano qui, si presume proprio che l'utente postgres del sistema operativo, in quanto tale, sia riconosciuto così anche dal DBMS; se così non fosse, a causa della configurazione, è probabile vedere apparire la richiesta di introdurre una parola d'ordine, riferita però al DBMS.

75.1.3   Creazione del sistema di basi di dati

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.

Opzione Descrizione
--pgdata=directory_pgdata
-D directory_pgdata
Stabilisce la directory iniziale del sistema di basi di dati di PostgreSQL che si vuole creare. Di solito deve corrispondere a ~postgres/data/.
--locale=sigla_locale
Stabilisce la configurazione locale. Se non viene utilizzata questa opzione si usa il contenuto delle variabili di ambiente LANG ed eventualmente LC_*.
--encoding=codifica
-E codifica
Stabilisce la codifica della base di dati usata come modello (template1), diventando di conseguenza la codifica predefinita per le nuove basi di dati. Tra le varie sigle che si possono usare vale la pena di ricordare UNICODE, SQL_ASCII.

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.

75.1.4   Avvio del servizio

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.

Opzione Descrizione
-D directory_dei_dati
Permette di specificare la directory di inizio della struttura dei dati del DBMS.
-S
Specifica che il programma deve funzionare in modo «silenzioso», senza emettere alcuna segnalazione, diventando un processo discendente direttamente da quello iniziale (Init), disassociandosi dalla shell e quindi dal terminale da cui è stato avviato.
Questa opzione viene utilizzata particolarmente per avviare il programma all'interno della procedura di inizializzazione del sistema, quando non sono necessari dei controlli di funzionamento.
-b percorso_del_programma_terminale
Se il programma terminale, ovvero postgres, non si trova in uno dei percorsi contenuti nella variabile di ambiente PATH, è necessario specificare la sua collocazione (il percorso assoluto) attraverso questa opzione.
-d [livello_di_diagnosi]
Questa opzione permette di attivare la segnalazione di messaggi diagnostici (debug), da parte di postmaster e da parte dei programmi terminali, a più livelli di dettaglio:
1, segnala solo il traffico di connessione;
2, o superiore, attiva la segnalazione diagnostica anche nei programmi terminali, oltre ad aggiungere dettagli sul funzionamento di postmaster.
Di norma, i messaggi diagnostici vengono emessi attraverso lo standard output da parte di postmaster, anche quando si tratta di messaggi provenienti dai programmi terminali. Perché abbia significato l'uso di questa opzione, occorre avviare postmaster senza l'opzione -S.
-i
Abilita le connessioni TCP/IP. Senza l'indicazione di questa opzione, sono ammissibili solo le connessioni locali attraverso socket di dominio Unix (Unix domain socket).
-p porta
Se viene avviato in modo da accettare le connessioni attraverso la rete (l'opzione -i), specifica una porta di ascolto diversa da quella predefinita (5 432).

Segue la descrizione di alcuni esempi.

Riquadro 75.6. Controllo diagnostico.

Inizialmente, l'utilizzo di PostgreSQL si può dimostrare poco intuitivo, soprattutto per ciò che riguarda le segnalazioni di errore, spesso troppo poco esplicite. In caso di difficoltà, per permettere di avere una visione un po' più chiara di ciò che accade, sarebbe bene fare in modo che postmaster produca dei messaggi diagnostici, possibilmente diretti a un file o a una console virtuale inutilizzata.

Per avere una visione immediata di ciò che accade, l'esempio seguente avvia postmaster in modo manuale e, oltre a conservare le informazioni diagnostiche in un file, le visualizza continuamente attraverso una console virtuale inutilizzata, che in questo caso è l'ottava:

su postgres[Invio]

nohup postmaster -D/var/lib/postgres/data -d 1 \
  \> /var/log/pglog 2>&1 &
[Invio]

exit[Invio]

nohup tail -f /var/lib/postgres > /dev/tty8 &[Invio]

75.1.5   Configurazione del DBMS

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:

# PostgreSQL configuration file
...
#
# TCP/IP access is allowed by default, but the default
# access given in pg_hba.conf will permit it only from
# localhost, not other machines.
#
tcpip_socket = true
...
#
#       Message display
#
log_connections = true
log_pid = true
log_timestamp = true
...
#
#       Syslog
#
syslog = 2      # range 0-2
...
#
#       Misc
#
dynamic_library_path = \
  \'/usr/share/postgresql:\
  \/usr/lib/postgresql:/usr/lib/postgresql/lib' ... # # How (by default) to present dates to the frontend; the # user can override this setting for his own session. # The choices are: # Style Date Timestamptz # -------------------------------------------------------- # ISO 1999-07-17 1999-07-17 07:09:18+01 # SQL 17/07/1999 17/07/1999 07:09:19 BST # POSTGRES 17-07-1999 Sat 17 Jul 07:09:19 1999 BST # GERMAN 17.07.1999 17.07.1999 07:09:19 BST # # It is also possible to specify month-day or day-month # ordering in date input and output. Americans tend to use # month-day; Europeans use day-month. Specify European or # US. This is used for interpreting date input, even if the # output format is ISO. Separate the two parameters # by a comma with no spaces # datestyle = 'ISO,European' ... LC_MESSAGES = 'C' LC_MONETARY = 'C' LC_NUMERIC = 'C' LC_TIME = 'C'

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.

Stile Descrizione Esempio
ISO
ISO 8601 2012-12-31
SQL
Tipo tradizionale 12/31/2012
POSTGRESQL
Tipo specifico di PostgreSQL 12-31-2012
GERMAN
31.12.2012

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.

75.1.6   Accesso e autenticazione

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 \
  \    autenticazione_utente [mappa]

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).

Tabella 75.9. Parole chiave che possono essere usate nel campo autenticazione_utente.

Autenticazione Descrizione
trust
L'autenticazione non ha luogo e si accetta il nome fornito dall'utente senza alcuna verifica.
reject
La connessione viene rifiutata in ogni caso.
password
Viene richiesta una parola d'ordine riferita all'utente del DBMS.
md5
crypt
Viene richiesta una parola d'ordine riferita all'utente del DBMS, che però viene trasmessa in modo cifrato. Le due parole chiave si riferiscono a sistemi differenti; si osservi che, di solito, solo uno dei due sistemi può essere utilizzato, perché dipende dal modo in cui sono memorizzate le parole d'ordine. Pertanto, se uno dei due non funziona, si può tentare con l'altro (dopo aver verificato che comunque l'accesso con le parole d'ordine in chiaro funziona regolarmente).
ident sameuser
ident mappa
L'autenticazione avviene attraverso il sistema operativo locale, oppure con il protocollo IDENT per gli accessi remoti (sezione 43.3). Si usa questa modalità di riconoscimento, prevalentemente per gli accessi locali, ma in tal caso si mette quasi sicuramente anche l'opzione sameuser, per fare riferimento allo stesso utente del sistema operativo. Se non si utilizza la parola chiave sameuser, al suo posto va messo il nome di una «mappa», da definire in un altro file.
pam [servizio]
L'autenticazione avviene attraverso il sistema PAM (Pluggable authentication modules) del sistema operativo. Se non viene indicato il servizio PAM, si intende postgresql.

Segue la descrizione di alcuni esempi.

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:

#
# TYPE DATABASE USER  IP-ADDRESS  IP-MASK     METHOD
#
local  all      postgres                      ident sameuser
#
local  all      all                           password
host   all      all   127.0.0.1   255.0.0.0   password
host   all      all   192.168.0.0 255.255.0.0 password
host   all      all   172.16.0.0  255.240.0.0 password
host   all      all   10.0.0.0    255.0.0.0   password
#
host   all      all   0.0.0.0     0.0.0.0     reject 

75.2   Gestione del DBMS

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.

75.2.1   Accesso a una base di dati

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.

Opzione Descrizione
-c istruzione_sql
--command istruzione_sql
Permette di fornire un'istruzione SQL già nella riga di comando, ottenendone il risultato attraverso lo standard output e facendo terminare subito dopo l'esecuzione di psql. Questa opzione viene usata particolarmente in abbinamento a -q.
-d base_di_dati
--dbname base_di_dati
Permette di indicare il nome della base di dati da utilizzare. Può essere utile quando per qualche motivo potrebbe essere ambigua l'indicazione del suo nome come ultimo argomento.
-f file_di_istruzioni
--file file_di_istruzioni
Permette di fornire a psql un file da interpretare contenente le istruzioni SQL (oltre agli eventuali comandi specifici di psql), senza avviare così una sessione di lavoro interattiva.
-h nodo
--host nodo
Permette di specificare il nodo a cui connettersi per l'interrogazione del servente PostgreSQL.
-H
--html
Fa in modo che l'emissione in forma tabellare avvenga utilizzando il formato HTML. In pratica, ciò è utile per costruire un risultato da leggere attraverso un navigatore ipertestuale.
-o file_output
--output file_output
Fa in modo che tutto l'output venga inviato nel file specificato dall'argomento.
-p porta
--port porta
Nel caso in cui postmaster sia in ascolto su una porta TCP diversa dal numero 5 432 (corrispondente al valore predefinito), si può specificare con questa opzione il numero corretto da utilizzare.
-q
--quiet
Fa sì che psql funzioni in modo «silenzioso», limitandosi all'emissione pura e semplice di quanto generato dalle istruzioni impartite. Questa opzione è utile quando si utilizza psql all'interno di script che devono occuparsi di rielaborare il risultato ottenuto.
-t
--tuple-only
Disattiva l'emissione dei nomi degli attributi. Questa opzione viene utilizzata particolarmente in abbinamento con -c o -q.
-T opzioni_tabelle_html
--table-attr opzioni_tabelle_html
Questa opzione viene utilizzata in abbinamento con -H, per definire le opzioni HTML delle tabelle che si generano. In pratica, si tratta di ciò che può essere inserito all'interno del marcatore di apertura della tabella: <TABLE ...>.
-U utente
--username utente
Consente di specificare il nome dell'utente del DBMS.
-W
--password
Forza psql a richiedere una parola d'ordine, in ogni caso.

Tabella 75.20. Alcuni comandi che psql riconosce durante il funzionamento interattivo.

Comando Descrizione
\h [comando]
L'opzione \h usata da sola, elenca le istruzioni SQL che possono essere utilizzate. Se viene indicato il nome di una di queste, viene mostrata in breve la sintassi relativa.
\?
Elenca i comandi interni di psql, cioè quelli che iniziano con una barra obliqua inversa (\).
\l
Elenca tutte le basi di dati presenti nel servente. Ciò che si ottiene è una tabella contenente rispettivamente: i nomi delle basi di dati, i numeri di identificazione dei rispettivi amministratori (gli utenti che li hanno creati) e il nome della directory in cui sono collocati fisicamente.
\connect base_di_dati \
  \[nome_utente]
Chiude la connessione con la base di dati in uso precedentemente e tenta di accedere a quella indicata. Se il sistema di autenticazione lo consente, si può specificare anche il nome dell'utente con cui si intende operare sulla nuova base di dati. Generalmente, ciò dovrebbe essere impedito.
Se si utilizza un'autenticazione basata sul file pg_hba.conf, l'autenticazione di tipo trust consente questo cambiamento di identificazione, altrimenti, il tipo ident lo impedisce.
\d [relazione]
L'opzione \d usata da sola, elenca le relazioni contenute nella base di dati, altrimenti, se viene indicato il nome di una di queste relazioni, si ottiene l'elenco degli attributi. Se si utilizza il comando \d *, si ottiene l'elenco di tutte le relazioni con le informazioni su tutti gli attributi rispettivi.
\i file
Con questa opzione si fa in modo che psql esegua di seguito tutte le istruzioni contenute nel file indicato come argomento.
\q
Termina il funzionamento di psql.

Segue la descrizione di alcuni esempi.

75.2.1.1   Variabile di ambiente «PAGER»

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:

PAGER=less
export PAGER

Per eliminare l'impostazione di questa variabile, in modo da ritornare allo stato predefinito, basta annullare il contenuto della variabile nel modo seguente:

PAGER=
export PAGER

75.2.2   Organizzazione degli utenti

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]

75.2.3   Creazione ed eliminazione delle basi di dati

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]

75.2.4   La base di dati amministrativa

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.

75.2.5   Manutenzione delle basi di dati

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 \
  \      [nome_relazione [(attributo_1[,... attributo_n])]]

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:

#!/bin/sh
su postgres -c "psql $1 -c 'VACUUM'"

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.

#!/bin/sh
#
BASI_DATI=`psql template1 -t -c "SELECT datname from pg_database"`
#
echo "Procedimento di ripulitura e sistemazione delle basi di dati"
echo "di PostgreSQL."
echo "Se l'operazione dovesse essere interrotta accidentalmente,"
echo "potrebbe essere necessaria l'eliminazione del file pg_vlock"
echo "contenuto nella directory della base di dati relativa."
#
for BASE_DATI in $BASI_DATI
do
    printf "$BASE_DATI: "
    psql $BASE_DATI -c "VACUUM"
done

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.

75.2.6   Copie di sicurezza

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.

Autenticazione Descrizione
-d
--inserts
In condizioni normali, pg_dump salva i dati delle relazioni (le tabelle secondo l'SQL) in una forma compatibile con il comando COPY, che però non è compatibile con lo standard SQL. Con l'opzione -d, utilizza il comando INSERT tradizionale.
-D
--column-inserts
Come con l'opzione -d, con l'aggiunta dell'indicazione degli attributi (le colonne secondo l'SQL) in cui vanno inseriti i dati. In pratica, questa opzione permette di generare uno script più preciso e dettagliato.
-f file
--file=file
Permette di definire un file diverso dallo standard output, che si vuole generare con il risultato dell'elaborazione di pg_dump.
-h nodo
--host=nodo
Permette di specificare il nodo a cui connettersi per l'interrogazione del servente PostgreSQL. In pratica, se l'accesso è consentito, è possibile scaricare una base di dati gestita presso un nodo remoto.
-p porta
--port=porta
Nel caso in cui postmaster sia in ascolto su una porta TCP diversa dal numero 5 432 (corrispondente al valore predefinito), si può specificare con questa opzione il numero corretto da utilizzare.
-s
--schema-only
Scarica soltanto la struttura delle relazioni, senza occuparsi del loro contenuto. In pratica, serve per poter riprodurre le relazioni vuote.
-t nome_relazione
--table=nome_relazione
Utilizzando questa opzione, indicando il nome di una relazione, si ottiene lo scarico solo di quella.
-U nome
Specifica con quale nominativo utente identificarsi per eseguire l'operazione.
-W
Forza la richiesta di inserire una parola d'ordine, che comunque dovrebbe essere chiesta automaticamente se il DBMS la richiede.

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:

  1. 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;

  2. si aggiorna PostgreSQL;

  3. 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);

  4. si ricrea il sistema delle basi di dati, vuoto, attraverso initdb;

  5. 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.

75.2.7   Importazione ed esportazione dei dati

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.

Escape Descrizione
\\
Una barra obliqua inversa.
\.
Simbolo di conclusione del file.
\N
NULL.
\delimitatore
Protegge il simbolo che viene già utilizzato come delimitatore.
\<LF>
Tratta <LF> in modo letterale.
\b
<BS>.
\f
<FF>.
\n
<LF>.
\r
<CR>.
\t
<HT> (tabulazione orizzontale).
\v
<VT> (tabulazione verticale).
\ooo
Codice per un byte espresso in ottale.

È 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.

75.3   Il linguaggio

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:

classi istanze attributi tipi di dati contenibili negli attributi
relazioni tuple attributi domini
tabelle righe colonne tipi di dati contenibili nelle colonne

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.

75.3.1   Prima di iniziare

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).

75.3.2   Tipi di dati e rappresentazione

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.

Tipo Standard Descrizione
CHAR
CHARACTER
SQL92 Un carattere singolo.
CHAR(n)
CHARACTER(n)
SQL92 Una stringa di lunghezza fissa, di n caratteri, completata da spazi.
VARCHAR(n)
CHARACTER VARYING(n)
CHAR VARYING(n)
SQL92 Una stringa di lunghezza variabile con un massimo di n caratteri.
INTEGER
SQL92 Intero (al massimo nove cifre numeriche).
SMALLINT
SQL92 Intero più piccolo di INTEGER (al massimo quattro cifre numeriche).
FLOAT
SQL92 Numero a virgola mobile.
FLOAT(n)
SQL92 Numero a virgola mobile lungo n bit.
REAL
SQL92 Numero a virgola mobile (teoricamente più preciso di FLOAT).
DOUBLE PRECISION
SQL92 Numero a virgola mobile (più o meno equivalente a REAL).
NUMERIC
NUMERIC
  (precisione[,scala])
DECIMAL
DECIMAL
  (precisione[,scala])
DEC
DEC(precisione[,scala])
SQL92 Numero composto da un massimo di tante cifre numeriche quante indicate dalla precisione, cioè il primo argomento tra parentesi. Se viene specificata anche la scala, si intende riservare quella parte di cifre per quanto appare dopo la virgola.
DATE
SQL92 Data, di solito nella forma mm/gg/aaaa'.
TIME
SQL92 Orario, nella forma hh:mm:ss, oppure solo hh:mm.
TIMESTAMP
SQL92 Informazione completa data-orario.
INTERVAL
SQL92 Intervallo di tempo.
BIT(n)
SQL92 Stringa binaria di dimensione fissa.
BIT VARYING(n)
SQL92 Stringa binaria di dimensione variabile.
BOOLEAN
SQL99 Valore logico booleano.

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.

Tipo di valore in generale Esempi di rappresentazione in forma di costante letterale
CHAR
CHARACTER
CHAR(n)
CHARACTER(n)
VARCHAR(n)
CHARACTER VARYING(n)
CHAR VARYING(n)
'a'
'ciao'
'Ciao'
'123/der:876'
INTEGER
SMALLINT
1
123
-987
FLOAT
FLOAT(n)
REAL
DOUBLE PRECISION
NUMERIC
NUMERIC(precisione[,scala])
DECIMAL
DECIMAL(precisione[,scala])
DEC
DEC(precisione[,scala])
123.45
-45.3
123.45e+10
123.45e-10
DATE
DATE '31.12.2012'
DATE '12/31/2012'
DATE '2012-12-31'
TIME
TIME '15:55:27'
TIME '15:59'
TIMESTAMP
TIMESTAMP '2012-12-31 15:55:27'
TIMESTAMP '2012-12-31 15:55:27+1'
INTERVAL
INTERVAL '15:55:27'
INTERVAL '15 HOUR 59 MINUTE'
INTERVAL '- 15 HOUR'
BIT
BIT VARYING(n)
B'1'
B'101'
X'2F'
BOOLEAN
1
'y'
'yes'
't'
'true'
0
'n'
'no'
'f'
'false'

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.

Escape Significato
\n
<LF>
\r
<CR>
\b
<BS>
\'
'
''
'
\"
"
\\
\
\%
%
\_
_

75.3.3   Funzioni

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.

Funzione Descrizione
POSITION(stringa_1 IN stringa_2)
Posizione di stringa_1 in stringa_2.
SUBSTRING(stringa [FROM n] [FOR m])
Sottostringa da n per m caratteri.
TRIM([LEADING|TRAILING|BOTH] \
  \['x'] FROM [stringa])
Ripulisce all'inizio e alla fine del testo.

Tabella 75.46. Alcune funzioni riconosciute dal linguaggio di PostgreSQL.

Funzione Descrizione
UPPER(stringa)
Converte la stringa in caratteri maiuscoli.
LOWER(stringa)
Converte la stringa in caratteri minuscoli.
INITCAP(stringa)
Converte la stringa in modo che le parole inizino con la maiuscola.
SUBSTR(stringa,n,m)
Estrae la stringa che inizia dalla posizione n, lunga m caratteri.
LTRIM(stringa, 'x')
Ripulisce la stringa a sinistra (Left trim).
RTRIM(stringa, 'x')
Ripulisce la stringa a destra (Right trim).

Segue la descrizione di alcuni esempi.

75.3.4   Esempi comuni

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.

relazione

75.3.4.1   Creazione di una relazione

La relazione di esempio mostrata nella figura 75.57, potrebbe essere creata nel modo seguente:

CREATE TABLE Indirizzi (
        Codice          integer,
        Cognome         char(40),
        Nome            char(40),
        Indirizzo       varchar(60),
        Telefono        varchar(40)
    );

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.

75.3.4.2   Modifica della relazione

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:

ALTER TABLE Indirizzi ADD COLUMN Comune char(30);
ALTER TABLE Indirizzi DROP COLUMN Comune;

L'esempio seguente modifica il tipo di un attributo già esistente:

ALTER TABLE Indirizzi ALTER COLUMN Codice TYPE REAL;

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.

75.3.4.3   Inserimento dati in una relazione

L'esempio seguente mostra l'inserimento dell'indirizzo dell'impiegato «Pinco Pallino».

INSERT INTO Indirizzi
    VALUES (
        01,
        'Pallino',
        'Pinco',
        'Via Biglie 1',
        '0222,222222'
    );

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:

INSERT INTO Indirizzi (
        Codice,
        Cognome,
        Nome,
        Indirizzo,
        Telefono
    )
    VALUES (
        01,
        'Pallino',
        'Pinco',
        'Via Biglie 1',
        '0222,222222'
    );

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:

INSERT INTO Indirizzi (
        Codice,
        Cognome,
        Nome,
    )
    VALUES (
        01,
        'Pallino'
        'Pinco',
    );

75.3.4.4   Eliminazione di una relazione

Una relazione può essere eliminata completamente attraverso l'istruzione DROP. L'esempio seguente elimina la relazione degli indirizzi degli esempi già mostrati:

DROP TABLE Indirizzi;

75.3.4.5   Interrogazioni semplici

L'esempio seguente emette tutto il contenuto della relazione degli indirizzi già vista negli esempi precedenti:

SELECT * FROM Indirizzi;

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:

SELECT * FROM Indirizzi ORDER BY Cognome, Nome;
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:

SELECT Cognome as cognomi, Nome as nomi,
       Telefono as numeri_telefonici
    FROM Indirizzi;

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.

SELECT * FROM Indirizzi
         WHERE Cognome >= 'N' AND Cognome <= '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:

SELECT Indirizzi.Cognome, Indirizzi.Nome,
       Indirizzi.Telefono
    FROM Indirizzi;

Ecco il risultato:

cognome   nome       telefono

Pallino   Pinco      0222,222222
Tizi      Tizio      0555,555555
Cai       Caio       0888,888888
Semproni  Sempronio  0999,999999

75.3.4.6   Interrogazioni simultanee di più relazioni

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).

relazione

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.

SELECT
    Presenze.Giorno,
    Presenze.Ingresso,
    Presenze.Uscita,
    Indirizzi.Cognome,
    Indirizzi.Nome
    FROM Presenze, Indirizzi
    WHERE Presenze.Codice = Indirizzi.Codice;

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

75.3.4.7   Alias

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.

SELECT
    Pre.Giorno,
    Pre.Ingresso,
    Pre.Uscita,
    Nom.Cognome,
    Nom.Nome
    FROM Presenze AS Pre, Indirizzi AS Nom
    WHERE Pre.Codice = Nom.Codice;

75.3.4.8   Viste

Attraverso una vista, è possibile definire una relazione virtuale:

CREATE VIEW Presenze_dettagliate AS
SELECT
    Presenze.Giorno,
    Presenze.Ingresso,
    Presenze.Uscita,
    Indirizzi.Cognome,
    Indirizzi.Nome
    FROM Presenze, Indirizzi
    WHERE Presenze.Codice = Indirizzi.Codice;

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.

75.3.4.9   Aggiornamento delle tuple

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.

ALTER TABLE Indirizzi ADD COLUMN Comune char(30);
UPDATE Indirizzi
    SET Comune='Sferopoli'
    WHERE Telefono >= '022' AND Telefono < '023';

In pratica, viene aggiornata solo la tupla dell'impiegato Pinco Pallino.

75.3.4.10   Cancellazione delle tuple

L'esempio seguente elimina dalla relazione delle presenze le tuple riferite alle registrazioni del giorno 01/01/2012 e le eventuali antecedenti.

DELETE FROM Presenze WHERE Giorno <= '01/01/2012';

75.3.4.11   Creazione di una nuova relazione a partire da altre

L'esempio seguente crea la relazione mia_prova dalla fusione della relazioni degli indirizzi e delle presenze, come già mostrato in un esempio precedente:

SELECT
    Presenze.Giorno,
    Presenze.Ingresso,
    Presenze.Uscita,
    Indirizzi.Cognome,
    Indirizzi.Nome
    INTO TABLE mia_prova
    FROM Presenze, Indirizzi
    WHERE Presenze.Codice = Indirizzi.Codice;

75.3.4.12   Inserimento in una relazione esistente

L'esempio seguente aggiunge alla relazione dello storico delle presenze le registrazioni vecchie che poi vengono cancellate.

INSERT INTO PresenzeStorico (
        PresenzeStorico.Codice,
        PresenzeStorico.Giorno,
        PresenzeStorico.Ingresso,
        PresenzeStorico.Uscita
    )
    SELECT
        Presenze.Codice,
        Presenze.Giorno,
        Presenze.Ingresso,
        Presenze.Uscita
        FROM Presenze
        WHERE Presenze.Giorno <= '2012/01/01';

DELETE FROM Presenze WHERE Giorno <= '2012/01/01';

75.3.4.13   Controllare gli accessi a una relazione

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:

REVOKE ALL
    ON Presenze, Indirizzi
    FROM PUBLIC;

GRANT ALL
    ON Presenze, Indirizzi
    TO tizio;

75.3.5   Controllo delle transazioni

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:

START TRANSACTION;

INSERT INTO Indirizzi
    VALUES (
        05,
        'De Pippo',
        'Pippo',
        'Via Pappo, 5',
        '0333,3333333'
    );

COMMIT;

Nell'esempio seguente, si rinuncia all'inserimento della tupla con l'istruzione ROLLBACK finale:

START TRANSACTION;

INSERT INTO Indirizzi
    VALUES (
        05,
        'De Pippo',
        'Pippo',
        'Via Pappo, 5',
        '0333,3333333'
    );

ROLLBACK;

75.3.6   Cursori

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.

START TRANSACTION;

DECLARE Mio_cursore INSENSITIVE CURSOR FOR
    SELECT * FROM Indirizzi ORDER BY Cognome, Nome;

-- L'apertura del cursore non esiste in PostgreSQL
-- OPEN Mio_cursore;
...

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.

...
FETCH NEXT FROM Mio_cursore;
...
COMMIT;

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.

...
FETCH RELATIVE 1 FROM Mio_cursore;
...
COMMIT;

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.

START TRANSACTION;

DECLARE Mio_cursore INSENSITIVE CURSOR FOR
    SELECT * FROM Indirizzi ORDER BY Cognome, Nome;

-- L'apertura del cursore non esiste in PostgreSQL
-- OPEN Mio_cursore;

FETCH NEXT FROM Mio_cursore;

CLOSE Mio_cursore;

COMMIT;

75.3.7   Impostazione dell'ora locale

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:

SET TIME ZONE 'Europe/Rome';

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.

75.4   Accesso attraverso PgAccess

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.

pgaccess-inizio

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.

Purtroppo, l'uso di programmi come questo, che mediano la comunicazione con un DBMS attraverso delle finestre grafiche, può risultare più complicato della scrittura manuale del codice SQL necessario. In questo capitolo, le figure appartengono a versioni diverse del programma, perché alcune funzionalità essenziali della versione aggiornata, si sono rivelate inaffidabili.

75.4.1   Accesso alla base di dati

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.

pgaccess-connessione-base-dati

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.

pgaccess-connessione-avvenuta

È 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.

75.4.2   Gli «oggetti» secondo PgAccess

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 {Tables}.

pgaccess-tables

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.

75.4.3   Relazioni

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.

pgaccess-creazione-tabella

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.

pgaccess-scorrimento-tabella

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.

75.4.4   Interrogazioni e viste

È 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.

pgaccess-interrogazione-con-nome

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.

pgaccess-interrogazione-creazione-visuale

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 è:

select t0.nome, t0.cognome from "Indirizzi" t0

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.

pgaccess-scorrimento-vista

75.4.5   Stampe

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.

pgaccess-report-generator

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.

pgaccess-report-generator-anteprima

75.5   Accesso attraverso WWW-SQL

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.

75.5.1   Principio di funzionamento

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:

Action www-pgsql /cgi-bin/www-pgsql
AddHandler www-pgsql pgsql

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.

75.5.2   Preparazione delle basi di dati e accesso

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]

75.5.3   Linguaggio di WWW-SQL

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:

<HTML>
<HEAD>
    <TITLE>Esempio sul funzionamento delle
           variabili con WWW-SQL</TITLE>
</HEAD>
<BODY>
<H1>Esempio sul funzionamento delle variabili
    con WWW-SQL</H1>

<P><! SQL PRINT "var = $var" ></P>

<FORM ACTION="variabili.pgsql" METHOD="GET">
    <P><INPUT NAME="var">
    <INPUT TYPE="submit">
</FORM>

</BODY>

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 variabili.pgsql attraverso WWW-SQL.

www-sql-variabili

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.

75.5.3.1   Espressioni

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.

Operatore e
operandi
Descrizione
+op
Non ha alcun effetto.
-op
Inverte il segno dell'operando.
op1 + op2
Somma i due operandi.
op1 - op2
Sottrae dal primo il secondo operando.
op1 * op2
Moltiplica i due operandi.
op1 / op2
Divide il primo operando per il secondo.
op1 % op2
Modulo: il resto della divisione tra il primo e il secondo operando.
op1 ^ op2
Eleva il primo operando alla potenza del secondo.
op1 == op2
Vero se gli operandi sono uguali.
op1 = op2
Vero se gli operandi sono uguali (sinonimo di ==).
op1 != op2
Vero se gli operandi sono differenti.
op1 > op2
Vero se il primo operando è maggiore del secondo.
op1 < op2
Vero se il primo operando è minore del secondo.
op1 >= op2
Vero se il primo operando è maggiore o uguale al secondo.
op1 <= op2
Vero se il primo operando è minore o uguale al secondo.
! op
Negazione logica.
op1 && op2
AND logico.
op1 & op2
AND logico (sinonimo di &&).
op1 || op2
OR logico.
op1 | op2
OR logico (sinonimo di ||).

Tabella 75.110. Elenco degli operatori utilizzabili con operandi di tipo stringa.

Operatore e
operandi
Descrizione
op1 == op2
Vero se gli operandi sono uguali.
op1 != op2
Vero se gli operandi sono differenti.
op1 > op2
Vero se il primo operando è lessicograficamente successivo al secondo.
op1 < op2
Vero se il primo operando è lessicograficamente precedente al secondo.
op1 >= op2
Vero se il primo operando non è lessicograficamente precedente al secondo.
op1 <= op2
Vero se il primo operando non è lessicograficamente successivo al secondo.
str : regexp
Vero se l'espressione regolare corrisponde alla 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.

Escape Significato
\\
\
\"
"
\n
<LF>
\t
<HT> (tabulazione)
\$
$
\@
@
\#
#
\?
?
\~
~

Tabella 75.112. Variabili interne di WWW-SQL.

Variabile Descrizione
AFFECTED_ROWS
Numero di righe coinvolte dall'ultima interrogazione.
NUM_FIELDS
Numero di campi restituiti dall'ultima interrogazione.
NUM_ROWS
Numero di righe restituiti dall'ultima interrogazione.
WWW_SQL_VERSION
Versione di WWW-SQL.
GATEWAY_INTERFACE
Versione dell'interfaccia CGI.
HOSTTYPE
Tipo di macchina del servente HTTP.
HTTPHOST
Nome del nodo servente.
HTTP_REFERER
Pagina da cui proviene il cliente.
HTTP_USER_AGENT
Nome del programma di navigazione (cliente).
OSTYPE
Nome del sistema operativo del servente.
PATH_INFO
Percorso relativo dello script attuale.
PATH_TRANSLATED
Percorso assoluto del file corrispondente allo script attuale.
REMOTE_ADDR
Indirizzo del nodo remoto.
REMOTE_HOST
Nome del nodo remoto.
SERVER_ADMIN
Indirizzo di posta elettronica dell'amministratore.
SERVER_NAME
Nome del servente.
SERVER_PORT
Numero della porta utilizzata per la connessione con il servente.
SERVER_PROTOCOL
Nome e versione del protocollo (HTTP).
SERVER_SOFTWARE
Nome del software usato come servente HTTP.
SCRIPT_FILENAME
Percorso del programma CGI (l'eseguibile di WWW-SQL).
SCRIPT_NAME
Percorso relativo del programma CGI (l'eseguibile di WWW-SQL).
REQUEST_URI
Indirizzo richiesto.

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.

<HTML>
<HEAD>
    <title>Visualizzazione delle variabili interne</title>
</HEAD>
<BODY>
<H1>Visualizzazione delle variabili interne</H1>

<! SQL DUMPVARS >

</BODY>

Figura 75.114. Esempio del contenuto delle variabili interne attraverso l'istruzione <!SQL DUMPVARS>.

         Visualizzazione delle variabili interne

WWW_SQL_VERSION = 0.5.5
SERVER_SOFTWARE = Apache/1.3.3 (Unix) Debian/GNU
SERVER_PROTOCOL = HTTP/1.0
SERVER_PORT = 80
SERVER_NAME = dinkel.brot.dg
SERVER_ADMIN = webmaster@dinkel.brot.dg
SCRIPT_FILENAME = /usr/lib/cgi-bin/www-pgsql
SCRIPT_NAME = /cgi-bin/www-pgsql
REQUEST_URI = /cgi-bin/www-pgsql/interne.pgsql
REMOTE_ADDR = 127.0.0.1
QUERY_STRING =
PATH_TRANSLATED = /var/www/interne.pgsql
PATH_INFO = /interne.pgsql
HTTP_USER_AGENT = Lynx/2.8.1rel.2 libwww-FM/2.14
HTTP_HOST = localhost
GATEWAY_INTERFACE = CGI/1.1
DOCUMENT_ROOT = /var/www

75.5.3.2   Strutture di controllo

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:

<! SQL IF $NUM_ROWS == 10 >
    <P>Il numero delle righe è uguale a 10.</P>
<! SQL ELSE >
    <P>Il numero delle righe non corrisponde a
    quanto previsto.</P>
<! SQL ENDIF >

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).

<! SQL SET contatore 10 >
<! SQL WHILE $contatore > 0 >
    <P>Il contatore ha raggiunto il livello
       <! SQL PRINT "$contatore" >.</P>
    <! SQL SETEXPR contatore $contatore - 1 >
<! SQL DONE >

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>.

75.5.4   Istruzioni

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.

75.5.4.1   Apertura e chiusura di una connessione, e accesso a una base di dati

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.

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:

<HTML>
<HEAD>
    <TITLE>Esempio di interrogazione</TITLE>
</HEAD>
<BODY>
<H1>Esempio di interrogazione</H1>
<! SQL CONNECT localhost nobody >
<! SQL DATABASE anagrafe >
<! SQL QUERY "SELECT * FROM Indirizzi" RICHIESTA_1 >
<! SQL QTABLE RICHIESTA_1 >
<! SQL FREE RICHIESTA_1 >
<! SQL CLOSE >
</BODY>

75.5.4.2   Istruzioni di interrogazione normali

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 >

75.5.4.3   Istruzioni per la selezione dettagliata di tuple e attributi

È 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.

75.6   Riferimenti


1) PostgreSQL   software libero con licenza speciale

2) PgAccess   software libero con licenza speciale

3) WWW-SQL   GNU GPL

«a2» 2013.11.11 --- Copyright © Daniele Giacomini -- appunti2@gmail.com http://informaticalibera.net