L'introduzione all'utilizzo di SQLite ci ha fornito una breve panoramica sulle sue potenzialità e sugli utilizzi più idonei nel settore IT.
In particolare, ci siamo posti il problema di comprendere in cosa esso sia differente rispetto ad un RDBMS (Relational Database Management System) come MySQL o PostgreSQL. A questo punto è giunto il momento di esaminare un po' più dettagliatamente le funzionalità e particolarità di questo database, con particolare riferimento a PHP.
PHP ci fornice una serie di funzioni che non risulteranno del tutto nuove a chi ha un po' di dimestichezza con le API per MySQL, pertanto nei paragrafi successivi prenderemo in considerazione soltanto quelle che potrebbero indurre qualche perplessità.
Il database
SQLite non è un server e che un database SQLite è costituito da un file binario accessibile direttamente, quindi risulta influenzato dalle restrizioni imposte dai Chmod e da configurazioni legate a Php come il Safe Mode e configurazioni sicure.
L'accesso diretto ad un database SQLite non richiede username e password, mentre l'accesso remoto (http:// o ftp://), a differenza di quanto avviene nel caso dei file DBM, non è supportato dall'API Php: in entrambi i casi non si tratta di vere e proprie limitazioni, infatti stiamo parlando di un database engine pensato per essere utilizzato sulla stessa macchina in cui opera Php.
sqlite_open()
sqlite_open() consente di creare un database o di aprirlo se già esiste,
resource sqlite_open ( string filename [, int mode [, string &error_message]])Il primo argomento, il file, è l'unico obbligatorio: per evitare effetti imprevedibili è preferibile utilizzare un percorso fisico assoluto (facilmente ricostruibile con funzioni come getcwd() e realpath()).
Al posto di un file presente sull'hard disk possiamo anche utilizzare ":memory:" per creare un database temporaneo (con vita pari alla durata dello script) residente soltanto memoria, una caratteristica che può tornare utile per caricare/scaricare provvisoriamente dati durante alcune operazioni di manipolazione.
"mode" per ora non ha nessun ruolo se non quello di segnaposto, ma in futuro avrà il compito di definire la modalità di accesso al file.
Il terzo argomento rappresenta un contenitore per gli errori in caso di fallimento al momento dell'apertura, tuttavia non sembra funzionare come dovrebbe (specialmente in Windows), quindi al momento è consigliabile utilizzare sqlite_open() come segue.
<?php
/* [LISTATO 1]
Attiviamo il tracking degli errori:
In caso di errore soppresso con "@" troveremo il messaggio
nella variabile PHP predefinita "$php_errormsg".
Si tratta di un modo per sopperire al non funzionamento del terzo argomento di sqlite_open
*/
ini_set('track_errors', '1') ;
/* Percorso assoluto del database */
$dbFile = realpath('./').'/testDB1' ;
$dbLink=@sqlite_open($dbFile) ;
/* Se qualcosa è andato storto gestiamo l'errore e stampiamo un avviso */
if(!is_resource($dbLink)) {
$sqliteError= "Si è verificato un errore al momento dell'apertura/creazione del database <br>n";
$sqliteError.= '<strong>'.$php_errormsg.'</strong>' ;
$php_errormsg='' ;
die($sqliteError) ;
}
//altre operazioni
/* Chiude il database */
sqlite_close($dbLink) ;
?>Tabelle, campi e assenza di tipi
Un peculiarità di SQLite è la quasi totale assenza di tipi di dato, infatti nel nome della comodità e della facilità d'uso non siamo obbligati a specificare il tipo di dato per i campi di una tabella.
Indicare dei "data type" rimane una pratica consigliabile, allo scopo di mantenere la compatibilità con altri database, ma si tratta di un'operazione quasi del tutto ininfluente: SQLite consente di immagazzinare ogni tipo di dato in qualsiasi tipo di campo.
I tipi invece sono rilevanti ai fini della comparazione e l'ordinamento dei valori.
Creare una tabella
Ecco come creare una tabella:
<?php
/* [LISTATO 2] */
// ...
/* Qui va inserito il codice del listato precedente,
* [LISTATO 1], per l'apertura del database
*/
// Query per creare una tabella (notare i commenti)
$query = 'CREATE TABLE clienti(
-- id_cliente è un campo autoincrement
id_cliente INTEGER PRIMARY KEY,
-- il nome del cliente
nome_cliente VARCHAR(20),
/*
Commento multi-line per il
cognome del cliente
*/
cognome_cliente VARCHAR(35),
-- la data di nascita
d_nascita DATE
);' ;
/* Se qualcosa è andato storto gestiamo l'errore
* come già visto e stampiamo un avviso
*/
if(!@sqlite_query ($dbLink, $query)){
$sqliteError= "<strong>Impossibile eseguire la query</strong><br>n";
$sqliteError.= "{$query} <br>n";
$sqliteError.= '<strong>'.$php_errormsg.'</strong>' ;
$php_errormsg='' ;
die($sqliteError) ;
}
sqlite_close($dbLink) ;
?>Dichiarando il primo campo INTEGER PRIMARY KEY abbiamo definito un campo autoincrement, tuttavia esistono alcune piccole differenze rispetto al tipo equivalente in MySQL, come viene evidenziato nelle FAQ presenti sul sito ufficiale.
Escape dei dati "pericolosi" con sqlite_escape_string()
SQLite non è "binary safe", inserire in un campo il contenuto di un file binario che contenga caratteri NUL all'interno delle stringhe comporta la corruzione dei dati e ne compromette il recupero.
Ci viene in aiuto la funzione sqlite_escape_string() che, a differenza dell'equivalente per MySQL, non si limita a fare soltanto l'escape degli apici ma codifica anche i dati binari (in modo più rapido rispetto a base64_encode()).
È quindi raccomandabile applicare questa funzione ogni volta che effettuiamo una INSERT di dati che potrebbero creare problemi.
La decodifica è automatica al momento dell'estrazione delle informazioni, se tuttavia desideriamo disabilitare questo comportamento di default (proprio soltanto dell'API PHP) possiamo farlo impostando a FALSE il terzo parametro delle varie funzioni sqlite_fetch_....
Avvertenza:
- La documentazione ufficiale raccomanda di non utilizzare addslashes() per l'escape delle stringhe
- Non è consigliabile inserire grandi quantità di dati in SQLite, pena un decadimento delle prestazioni del database: è meglio quindi mantenere tali dati (testi, immagini e file in genere) nel filesystem, inserendo soltanto un riferimento (un percorso) nei campi del database. SQLite raggiunge le migliori performance quando la quantità di dati salvati viene distribuita rispettando un limite di 230 byte per singola riga.
UDF, user defined functions
SQLite supporta soltanto un limitato numero di funzioni rispetto a MySQL e altri RDBMS, tuttavia presenta un grande vantaggio dal punto di vista della flessibilità: è possibile integrare i comandi sql con le funzioni Php, sia standard che definite dall'utente.
sqlite_create_function() e sqlite_create_aggregate() consentono di registrare delle funzioni che diverranno direttamente applicabili nell'sql.
La documentazione ufficiale porta l'esempio di md5() ma noi proveremo a farne uno più interessante: poichè manca un equivalente del DATE_FORMAT, utilizzato in MySQL per estrarre date formattate, aggiungeremo questa funzionalità attraverso PHP.
Per prima cosa definiamo la funzione Php che useremo per la formattazione
<?php
/* Il solito tracking degli errori */
ini_set('track_errors', '1') ;
function dFormat($format, $date) {
list($anno, $mese, $giorno) = explode('-', $date) ;
return( strftime($format, mktime(0,0,0, $mese, $giorno, $anno)) ) ;
}Apriamo il database e registriamo la funzione per integrarla nel sql di SQLite,
dFormat è la funzione di callback, DATE_FORMAT è il nome che prenderà nell'SQL.
$dbFile = realpath('./').'/testDB1' ;
$dbLink = @sqlite_open($dbFile) ;
sqlite_create_function($dbLink, 'DATE_FORMAT', 'dFormat') ;Inseriamo un record di prova: la data è in formato anglosassone (anno-mese-giorno) per renderla compatibile con altri database.
$queryInsert = "INSERT INTO clienti (id_cliente, nome_cliente, cognome_cliente, d_nascita)";
$queryInsert .= "VALUES (NULL, 'Mario', 'Rossi', '1973-01-13')";
if(!@sqlite_query ($dbLink, $queryInsert)) {
$sqliteError= "<strong>Impossibile eseguire la query</strong><br>n";
$sqliteError.= "$queryInsert <br>n";
$sqliteError.= '<strong>'.$php_errormsg.'</strong>' ;
$php_errormsg='' ;
die($sqliteError) ;
}Query di selezione con estrazione della data formattata (13/01/1973), notare il ruolo di DATE_FORMAT
$querySelect = "SELECT DATE_FORMAT('%d/%m/%Y', d_nascita) as data, nome_cliente, cognome_cliente FROM clienti" ;
if(!$result=@sqlite_array_query ($dbLink, $querySelect, SQLITE_ASSOC)) {
$sqliteError = "Impossibile eseguire la query
n";
$sqliteError.= "$querySelect
n";
$sqliteError.= ''.$php_errormsg.'' ;
$php_errormsg='' ;
die($sqliteError) ;
}
/* Estraiamo un dump dimostrativo dei dati estratti */
var_export($result) ;
sqlite_close($dbLink) ;
?>Anziche registrare la funzione dFormat() con sqlite_create_function() avremmo anche potuto impostare la query nel modo descritto più sotto, ottenendo lo stesso risultato.
$querySelect = "SELECT php('dFormat','%d/%m/%Y', d_nascita) as data, nome_cliente, cognome_cliente FROM clienti" ;UDF e dati binari
Nel caso in cui i dati sui cui agiscono le UDF, in quanto binari, fossero stati sottoposti ad escape prima di essere inseriti nel database, al momento dell'estrazione la decodifica non averrà automaticamente (per ragioni legate alle performance).
Sarà quindi necessario applicare sqlite_udf_decode_binary() all'interno della funzione creata dall'utente, prima che questa restituisca i dati.
Conclusioni su SQLite
L'argomento è lungi dall'essere esaurito ma, descrivendo gli aspetti più singolari di SQLite, speriamo di aver spianato la strada a chi deciderà di servirsi per la prima volta di questo piccolo ma utile database engine.
SQLite è un progetto nato dalla mente di D. Richard Hipp, con uno scopo
ben preciso: rendere facile ed immediato il processo di deployment di
un database SQL. Le scelte progettuali che caratterizzano SQLite lo
hanno reso praticamente compatibile con qualunque dispositivo di
calcolo e ciò ha certamente contribuito a fare di esso il database
SQL più utilizzato al mondo.
Scopo di tale lezione sarà quello di evidenziare sia le peculiarità
progettuali che le funzionalità offerte da SQLite. Inoltre, benchè SQLite
possa essere utilizzato praticamente in qualunque tipo di applicazione
reale, sia essa sviluppata su un sistema desktop, mobile o embedded, per
comodità la lezione riporterà una breve serie di casi
pratici dove l’utilizzo di SQLite NON è consigliato, e dove
concorrenti come MySQL o PostgreSQL potrebbero rivelarsi una scelta più
azzeccata.
Nelle successive pagine di questo articolo, vedremo più da vicino
come utilizzare praticamente le API di SQLite nel web, ed utilizzarlo
congiuntamente a PHP.
Storia di SQLite
SQLite è stato rilasciato nel lontano anno 2000, come appartenente al
“pubblico dominio” e con licenza “open-source”. Questo significa che
chiunque può utilizzare il codice sorgente e/o i file binari distribuiti
dal produttore, crearne tante copie e senza alcuna restrizione di
utilizzo. Tuttavia, come specificato da Richard Hipp, il progetto non è
“open contribution”. Questa volontà nasce dall’esigenza di impedire a
sviluppatori sconosciuti di contaminare il progetto con codice
proprietario e/o licenze d’utilizzo.
La documentazione ufficiale di SQLite viene costantemente aggiornata ed è
disponibile sul sito
web ufficiale. Va anche notato che SQLite è un progetto a lungo
termine, e secondo quanto indicato il supporto non
terminerà prima del 2050.
SQLite è una libreria C che implementa (quasi) tutte le funzionalità di
altri DBMS relazionali blasonati come MySQL,
PosgreSQL,
ecc. Una differenza sostanziale con gli altri DBMS è che SQLite è
“embedded”. Questo significa che non viene istanziato sotto un processo
separato, ma gestito all’interno della stessa applicazione che lo
utilizza. Infatti, la libreria di SQLite legge e scrive direttamente
all’interno di un unico file su disco rappresentativo del database stesso.
Questo è il principale elemento distintivo di SQLite: effettuare il deploy
di un database consiste semplicemente nello specificare un path sul disco
che punti al file contenente il dabase. Conseguentemente, trasportare un
database tra sistemi diversi è un’operazione semplice che consiste nella
semplice copia di un file.
Le principali caratteristiche di SQLite possono essere riassunte come
segue:
- Nessuna configurazione: la creazione di un database non
comporta assolutamente nulla. Non bisogna creare un utente
amministrativo, né istanziare un processo separato che stia in ascolto
di comandi SQL, né assegnare permessi agli utenti. Ancora più
importante, nessuna azione di restore è richiesta in seguito ad un crash
di sistema; - Unico file per il database: l’applicazione che utilizza SQLite
accede direttamente ad un unico file contenente il database. Grazie
all’utilizzo di tecniche di caching (completamente trasparenti
all’applicazione), l’accesso al file risulta circa il 20%-30% più veloce
rispetto all’accesso diretto al file system; - Formato cross-platform: SQLite è assolutamente compatibile con
qualunque file-system, con qualunque macchina a 32/64 bit
indipendentemente dall’architettura (big-endian o little-endian).
Compatibilità
Il sorgente di SQLite è scritto in C, e disponibile a questa
pagina web. Praticamente qualunque macchina equipaggiata di GCC
può compilare il codice ed avere a disposizione le API
per l’accesso e la creazione del database. All’interno della stessa pagina
web è possibile trovare i binari per Android, Linux, Mac
OS X, Windows, Windows Phone 8, Windows Runtime
e .NET.
Oltre a ciò, va notato che esistono i binding di SQLite per la maggior
parte dei linguaggi di programmazione quali:
- C, C++
- C#
- Java
- Javascript
- Matlab
- Perl
- PHP
- Python
- Ruby
Quando NON utilizzare SQLite
Finora SQLite ha dimostrato di risolvere un gran numero di problemi
riguardanti lo sviluppo ed il deployment di applicazioni richiedenti un
database di tipo SQL. La
lista degli utenti famosi di SQLite
è impressionante, e tra essi si annoverano: Adobe, Bentley, Dropbox, Facebook, Google, McAfee,
Microsoft, Firefox, PhP, Python, RPM, Skype, Tcl/Tk.
Non possiamo comunque considerarlo come una panacea per risolvere
tutti i problemi e vi sono alcuni casi specifici in cui NON si dovrebbe
ricorrere all’utilizzo di SQLite, riportati nella seguente figura
riassuntiva:
Le motivazioni sono semplici e spiegate qui di seguito:
- Remotizzazione: utilizzare SQLite come database remoto è sempre
possibile qualora sia disponibile un filesystem di rete. Tuttavia, la
latenza associata all’accesso a file condivisi in rete lo renderà lento
e di scarsa utilità pratica. In questi casi bisogna necessariamente
ricorrere a un DBMS client/server; - Database di grandi dimensioni: SQLite può gestire
database della dimensione massima di 140 Terabytes. Benchè tale
dimensione sia ragguardevole, questo può essere fonte di problemi in
molti filesystem non in grado di gestire file di dimensioni così grandi; - Concorrenza: andiamo alla vera nota dolente. Il processo di
scrittura di SQLite è mono-thread, sebbene esso sia in grado di gestire
diversi processi in lettura contemporaneamente. Gli accessi concorrenti
vengono gestiti tramite una coda che ha ovviamente una dimensione finita
e diventa sorgente di una certa latenza in scrittura. Nel caso di
applicazioni/siti web che richiedono un elevato numero di scritture allo
stesso tempo NON utilizzate SQLite; - Caratteristiche (minori) mancanti: come accennato prima,
SQLite implementa quasi tutte le caratteristiche di SQL standard. Ciò
che manca è elencato di seguito:- Right e full outer join;
- Alter table: implementato solo da rename table e add
column; - Scrittura sulle viste;
- Grant e Revoke: questo è dovuto alla scelta di progetto di
rendere SQLite disponibile su unico file. La gestione dei permessi va
dunque demandata al file system.
