SQLite: il più usato motore SQL al mondo | NEXT chapters | ||||
SQLite: perchè è necessario conoscerlo I browser Google Chrome, Opera, Safari, Android, Firefox e il client di posta elettronica Thunderbird, utilizzano SQLite per conservare i dati di navigazione dell'utente, quali: la cronologia di navigazione, i Cookie ed i Bookmark. I dati vengono conservati anche quando l'utente invia il comando SQL DELETE, oppure utilizza il menu del browser per cancellarli. Non a caso, l'analisi forense ( l'analisi al servizio delle professioni forensi ) si affida a SQLite per conoscere le abitudini di navigazione di un utente, i siti visitati, i dati inseriti nei vari moduli internet ( form ), al fine di tracciarne un profilo da esibire in tribunale, oppure alla ricerca di prove a sostegno della sua innocenza o della sua colpevolezza. SQLite è una libreria che implementa un motore SQL, con le seguenti proprietà:
Con SQLite, un intero database SQL, completo di tabelle, indici, trigger, viste viene memorizzato in un unico file. SQLite supporta gran parte dello standard SQL92 ( SQL2 ). Quindi, con SQLite è possibile inviare query SQL ( SELECT, INSERT, UPDATE, DELETE ), come faremmo con un qualsiasi altro database SQL o MySQL, ad un database SQLite. Esiste uno statement SQL specifico per SQLite:
che permette di interrogare il database per ottenere dati estranei alle tabelle, oppure per modificare le operazioni di SQLite. Lo statement PRAGMA può essere usato, ma senza dimenticare alcune importanti avvertenze:
Ci sono pragma che permettono di ottenere informazioni sui database ai quali si è connessi. Per esempio, il comando:
restituisce un record per ciascuna colonna presente nella tabella table-name ( deve essere la tabella principale ), con le seguenti informazioni ( colonne o record ):
dove:
Per ottenere la lista degli indici associati ad una tabella, utilizzare il comando:
che restituisce tre soli campi:
Per ciascuno degli indici, il campo UNIQUE sarà impostato a 1 se l'indice è UNIQUE. Per avere informazioni su uno specifico indice, eseguire:
che restituisce tre soli campi:
dove:
Altri pragma permettono, invece, di estrarre e modificare alcuni parametri SQLite. Per esempio:
Le modifiche apportate con il comando PRAGMA varranno per la sola sessione corrente, a meno che non si utilizzi il prefisso default per renderle permanenti:
Esistono, infine, alcuni PRAGMA, utili alla manutenzione del database. Il comando:
verifica l'integrità dell'intero database, alla ricerca di record incoerenti, pagine mancanti, indici incompleti, errori su vincoli UNIQUE e NOT NULL. Se, nel corso dell'
Il valore di default di N è 100. Se, nel corso dell'
è molto simile al PRAGMA precedente, ma, in fase di verifica, non cerca gli errori sui vincoli UNIQUE e NOT NULL e non controlla che il contenuto degli indici rifletta il reale contenuto delle tabelle corrispondenti. Quindi, questo PRAGMA è più veloce del precedente. Un altro statement SQL specifico per SQLite è il comando SQL ATTACH o ATTACH DATABASE. Durante una sessione di lavoro, sarà sempre possibile invocare il comando SQL ATTACH, per selezionare un database specifico da aggiungere alla connessione corrente:
oppure:
Il nome Alias che appare dopo la keyword AS è il nome che SQLite userà internamente. I nomi 'main' e 'temp' vengono assegnati, da SQLite, rispettivamente, al database principale, il database al quale si è effettuata la connessione, e al database utilizzato per le tabelle temporanee e non possono essere usati all'interno del comando ATTACH. Le tabelle di un database aggiunto alla connessione corrente con il comando ATTACH devono essere indicate con il loro percorso completo:
a meno che il nome della tabella sia unico, rispetto a tutte le tabelle dei database aggiunti alla connessione corrente con il comando ATTACH ed alle tabelle dei database 'main' e temp'. Il comando ATTACH può anche essere usato per aprire un database virtuale, non memorizzato in un file, ma gestito completamente nella memoria del sistema:
Quando viene usato lo speciale nome di file:
SQLite non apre alcun file dal disco fisso, ma crea un nuovo database interamente memorizzato nella memoria volatile del sistema. Il database cesserà di esistere non appena la connessione al database verrà chiusa. Le transazioni eseguite su più database, aggiunti con il comando ATTACH, sono atomiche: i dati generati da una transazione vengono salvati solo quando tutte le istruzioni inviate sono state eseguite correttamente: se anche solo una delle istruzioni inviate non fosse andata a buon fine, SQLite si libererà dei nuovi dati, ripristinando, in tutti i database coinvolti, i dati originali ( rollback ). Questo a condizione che il database principale non sia del tipo:
oppure non sia in esecuzione in modalità WAL ( Write-Ahead Log ). In uno di questi ultimi due casi, le transazioni saranno atomiche solo per ciascuno dei database. Nel caso dovesse intervenire un crash del sistema durante il salvataggio dei dati ( COMMIT ), potrebbe accadere che un database risulti aggiornato, un altro no. Per quanto riguarda i tipi di dato supportati da SQLite, occorre ricordare che SQLite è un database "typeless" ( senza tipi di dato ): pur permettendo di specificare il tipo di dato da associare a ciascuna colonna, all'interno del comando CREATE TABLE, SQLite ignorerà sempre queste indicazioni, permettendo a chiunque di inserire, per esempio, una stringa di testo in una colonna che è stata creata per contenere un dato di tipo INTEGER. Con una sola eccezione: il tipo di dato INTEGER PRIMARY KEY, che pretende, sempre, un tipo di dato signed integer a 32-bit. Tentando di inserire un tipo di dato diverso, si riceverà un messaggio di errore. Attenzione: il tipo di dato INTEGER deve essere scritto per esteso e deve essere specificato come PRIMARY KEY:
Se, in fase di inserimento, si assegnerà il valore NULL al campo ID, SQLite si comporterà come se il campo contenesse l'attributo AUTO_INCREMENT, incrementando di uno il valore più alto già assegnato alla colonna:
Il formato del file di database principale Un database SQLite è interamente contenuto in un singolo file, normalmente, chiamato "main database file" ( file principale del database ). Nel corso di una transazione, SQLite memorizza informazioni aggiuntive in un secondo file, chiamato "rollback journal", oppure, se eseguito in modalità WAL ( Write-Ahead Log ), in un file di LOG. Se il programma o il computer si bloccano, nel corso di una transazione, allora il "rollback journal", oppure il file di "Write-Ahead Log", contiene le informazioni critiche necessarie al ripristino del file principale del database. Il file principale del database è costituito da una o più pagine. Ciascuna pagina è composta da un numero di byte, pari ad una potenza di due, compresa tra 512 e 65.536 ( incluse ). Tutte le pagine di un database hanno la stessa dimensione. La dimensione della pagina di un database è espressa dal numero intero, a due byte, scritto a partire dal 16esimo byte del file di database:
In questo esempio, il sedicesimo e diciassettesimo byte riportano:
che è da leggere:
a causa dell'ordine "little-endian" utilizzato dal comando
Quindi, la dimensione minima per un database SQLite è una sola pagina di 512 byte. La dimensione massima per un database SQLite, invece, è di 2.147.483.646 pagine, ciascuna delle quali composta da 65536 byte: 140.737.488.224.256 byte ( 140 terabyte ) totali. Normalmente, SQLite, prima di raggiungere la dimensione massima di file prevista, raggiungerà la dimensione massima di file, permessa dal sistema operativo sottostante oppure dal disco fisso. Le operazioni di lettura e di scrittura partono sempre dall'inizio di una pagina. La dimensione di un'operazione di scrittura o di lettura viene espressa in numero di pagine, ad eccezione della prima apertura di un database, quando i primi 100 byte del file del database ( header ) vengono letti come un'unità a se stante ( sottopagina ). Prima che una pagina del database, contenente informazioni, possa essere modificata, il contenuto originale viene salvato nel " rollback journal ", nel caso in cui una transazione venisse interrotta, per un qualsiasi motivo, e si rendesse necessario il suo ripristino. I primi 100 byte di un file di database SQLite contengono l'header del file. L'header del file di database è suddiviso in campi. I campi multibyte son memorizzati con il byte più significativo prima ( big-endian ). Ogni file di database SQLite inizia con la seguente sequenza di 16 byte ( in hex ):
che corrisponde alla stringa testuale UTF-8 "SQLite format 3", incluso il carattere finale NUL. Per leggere questi primi 16 byte, usare l'opzione
I successivi byte di un header di un file di database SQLite sono:
I file temporanei usati da SQLite Attenzione: il modo in cui SQLite utilizza i file temporanei non è considerato parte del contratto che SQLite stipula con i programmatori delle applicazioni. Le informazioni qui contenute, quindi, rappresentano una corretta descrizione del modo di operare di SQLite al momento della stesura del documento, senza alcuna garanzia che esse saranno valide per le versioni future di SQLite. Attualmente, SQLite utilizza nove distinti file temporanei, durante la sessione di lavoro:
SQLite mette a disposizione un programma a riga di comando,
Abbiamo appena visto che SQLite contiene un intero database in un unico file. Per accedere ad un database, è sufficiente invocare
Se il file (
Come primo esempio, proviamo ad accedere, direttamente, al database dei cookie di Firefox:
Questo comando apre una shell interattiva, dalla quale è possibile eseguire statement SQL, oppure inviare comandi a SQLite. I comandi SQLite sono preceduti da un punto:
mentre i comandi SQL possono essere inseriti direttamente. Per creare un nuovo database SQLite, chiamato "
Uno statement SQL deve sempre essere chiuso con un punto e virgola. Se omesso,
Per conoscere i comandi di
Questi cosiddetti " dot command " vengono usati, normalmente, per modificare il formato di output delle query, oppure per eseguire statement precompilati. I " dot command ", inoltre, non possono essere eseguiti sui database caricati con il comando ATTACH. Questo significa che, per interagire con un database caricato con il comando ATTACH, sarà necessario conoscere i nomi delle sue tabelle. Vediamo, ora, alcuni di questo " dot command":
stampa a video il valore di alcuni parametri. Il comando:
elenca i nomi dei database presenti e dei relativi file. Il comando:
elenca le tabelle presenti nel database. È anche possibile specificare un pattern di ricerca:
La stessa interrogazione può essere fatta per gli indici:
Il comando:
stampa a video gli statement CREATE TABLE e CREATE INDEX usati per la creazione delle tabelle del database. I comandi che manipolano il formato dell'output di
Il MODE di defaul è "LIST":
Per scrivere l'output in un file, eseguire:
Per tornare allo STDOUT:
Un accenno al comando:
che può essere utilizzato per impostare il formato di output su "column", con il valore di larghezza di colonna ( width ) appropriato. Il comando EXPLAIN è un comando specifico di SQLite, utile per il debug: quando una qualsiasi query SQL è preceduta dall'espressione EXPLAIN, il comando SQL viene simulato, ma non eseguito:
È possibile modificare le dimensioni delle colonne di output:
In questo esempio, stiamo impostando la prima colonna a 12 caratteri di larghezza e la seconda colonna a 6. La larghezza di tutte le altre colonne resterà immutata. È possibile dare all'attributo "
In questo esempio, stiamo impostando la quinta colonna a 22 caratteri di larghezza, lasciando inalterate tutte le altre. Specificando un valore di larghezza negativo, il testo della colonna verrà allineato a destra. SQLite supporta le chiavi esterne? Il supporto per le chiavi esterne ( SQL foreign key ) è stato introdotto in SQLite, a partire dalla versione 3.6.19. Le chiavi esterne sono disabilitate, di default, per un problema di retrocompatibilità e devono essere abilitate esplicitamente, per ciascuna connessione. Da notare, tuttavia, che le future release di SQLite potrebbero abilitare di default le chiavi esterne. Lo sviluppatore, quindi, non dovrebbe mai dare per scontato alcunché, rispetto alle chiavi esterne, ma dovrebbe abilitarle o disabilitarle, a seconda delle sue specifiche esigenze del momento. Per sapere se le chavi esterne sono supportate dal proprio sistema SQLite, eseguire:
Se questo comando non restituisce alcun dato, significa che la versione SQLite che stiamo utilizzando non supporta le chiavi esterne. Se il comando restituisce zero, significa che la versione SQLite che stiamo utilizzando supporta le chiavi esterne, ma il supporto alle chiavi esterne non è stato attivato. Se il comando restituisce uno, significa che la versione SQLite che stiamo utilizzando supporta le chiavi esterne e che il supporto alle chiavi esterne è stato attivato. Lo stesso comando può essere utilizzato per abilitare o disabilitare le chiavi esterne:
Altri PRAGMA controllano il comportamento delle chiavi esterne:
restituisce la lista delle violazioni delle clausole Foreign Key.
restituisce la lista delle dei vincoli Foreign Key per la tabella indicata. SQLite supporta l'operatore CONCAT? No. Per unire più stringhe in un'unica stringa, SQLite supporta i doppi pipe:
In questo esempio, la stringa finale risulterà composta dal valore contenuto nel campo "Comune" e la stringa
In SQL, questa query potrebbe utilizzare l'operatore CONCAT:
|
|||||
SQLite: il più usato motore SQL al mondo | The .bit guides: original contents |