SQLite - Roberto Mana

Tecnologie - Classe Quarta
robertomana.it
SQLite
SQLite
Rev Digitale 1.0 del 01/09/2016
Molto leggero. Salva i dati all’interno di un normalissimo file sequenziale che poi mostra come tabella.
SQLite è una libreria scritta in linguaggio C che implementa un DBMS SQL incorporabile all'interno di
altre applicazioni. Il suo creatore, D. Richard Hipp, lo ha rilasciato nel pubblico dominio, rendendolo
utilizzabile quindi senza alcuna restrizione. Permette di creare una base di dati (comprese tabelle, query,
form, report) incorporata in un unico file, come nel caso dei moduli Access di Microsoft Office
SQLite non è un processo standalone utilizzabile di per sé, ma deve essere incorporato all'interno delle
librerie di livello più alto di un altro linguaggio, che funge da wrapper incorporando le chiamate alle API del
dbms e mettendo a disposizione dell’utente dei metodi di livello più alto, relativi all’ambiente di utilizzo
SQLite è scaricabile dal sito sqlite.org sotto forma di DLL scritta in C/C++ che è grande meno di 500KB e
che dovrà poi in qualche essere collegata al progetto. Conviene però scaricare direttamente le librerie di
livello più alto relative all’ambiente di sviluppo. Sono state scritte librerie per moltissimi linguaggi di
programmazione, fra cui C/C++, Java, PHP, NodeJs, etc
SQLite viene utilizzato in:
 Mozilla Firefox per memorizzare i bookmark, la cronologia di navigazione ed altre informazioni.
 Nel sistema operativo dell'iPhone Apple per la gestione degli SMS e MMS, per il calendario, la
cronologia delle chiamate e la memorizzazione dei contatti.
 In Symbian OS ed Android,
 sul sistema operativo del BlackBerry
Per la sintassi completa di SQLite si rimanda a http://www.sqlite.org/lang.html
Per la sintassi dei metodi del wrapper NodeJs https://github.com/mapbox/node-sqlite3/wiki/API
Pregi e Caratteristiche






è molto compatto (meno di 500KB per l'intera libreria alla versione 3.6.14);
Non richiede configurazioni nè amministrazione
Non ha dipendenze esterne;
è molto veloce; in molti casi più d MySQL e PostgreSQL;
Supporta buona parte dello standard SQL92;
supporta database che possono essere anche molto grandi; attualmente il limite è 2TB

ha transazioni atomiche, consistenti, isolate e durabili (ACID), anche in caso di crash di sistema o
blackout;
include un programma di utilità a riga di comando per accedere al database anche manualmente (come
su MySQL, Postgresql e tanti altri DB) o tramite scripting;
l'API è molto semplice da utilizzare;
il codice sorgente è liberamente disponibile, chiaro e ben commentato;



pag 1
Tecnologie - Classe Quarta
robertomana.it
SQLite
Limiti di carattere generale
legati in parte alla finalità di semplice database da incorporare in altre applicazioni:

non offre le stored procedure.

non prevede la gestione dei permessi d'accesso, demandata al software con cui si interagisce con il
database e/o al meccanismo dei permessi del file system;

non ha una vera gestione della concorrenza le applicazioni che lo utilizzano, se necessario, devono
implementarla;

non offre alcuna cache per le query (e non ne ha la possibilità, non esistendo un processo server
centrale);

non ha protocolli di rete, non essendo utilizzabile come programma a sé; è possibile utilizzare un
database remoto, ma solo tramite file system di rete del sistema operativo, con prestazioni
difficilmente accettabili;

per garantire la coerenza del file del database sono usati i lock del file system, e quindi vi possono
essere problemi qualora quest'ultimo non li implementi correttamente, ad esempio con file system di
rete (come NFS);
Limiti legati all’SQL





non supporta alcuni importanti costrutti SQL quali gli OUTER JOIN.
non supporta le sottoquery variabili.
non supporta la scrittura diretta nelle viste (occorre usare trigger "INSTEAD OF");
non consente di modificare, cancellare o rinominare le colonne di una tabella: il comando ALTER
TABLE è infatti limitato alla modifica del nome della tabella e all'aggiunta di colonne in coda alla
stessa. Molti frontend di terze parti aggirano comunque il limite rigenerando in modo trasparente la
tabella (perdendo però trigger e indici ad essa correlati);
non supporta trigger di tipo "FOR EACH STATEMENT" (solo "FOR EACH ROW", eventualmente
combinato con "INSTEAD OF");
Interfacce grafiche per SQLite
SQLitebrowser è una comodissima applicazione che consente di ispezionare e gestire i database SQLite.
Scaricabile dal sito http://sourceforge.net/projects/sqlitebrowser/
Viene installato nella cartella Database browser for SQLite.
Attenzione che SQLitebrowser non salva direttamente su file le variazioni apportate ai dati, ma occorre
salvare ogni volta mediante il terzo pulsantino Write Changes
Note relative alle installazioni
 Per quanto riguarda sqLite, prima di lanciare l’installazione occorre creare la cartella
c:\utenti\myUser\AppData\roaming\npm.
Dopo di che si può procedere all’installazione anche senza essere amministratori.
 Per quanto riguarda invece SQLitebrowser, è consigliato installarlo come amministratori.
E’ anche possibile cambiare il percorso di installazione ed installarlo all’interno del profilo utente
(c:/utenti/myUser/SqliteBrowser3) ma non sempre funziona.
pag 2
Tecnologie - Classe Quarta
robertomana.it
SQLite
Creazione / Accesso al Database
var sqlite = require("sqlite3").verbose();
var db = new sqlite.Database("test.db");
Metodi del’oggetto sqlite
Il metodo .verbose() fa si che, in caso di errori legati ai comandi sql, node restituisca uno stack di
informazioni relative all’errore. Questo stack è molto comodo ma può avere peso sulle prestazioni.
Il metodo .Database(filename, callback(err)) provvede ad aprire il file di database indicato.
Se il file non esiste viene automaticamente creato, per cui restituisce sempre un oggetto di tipo
database. L’eventuale funzione di callback viene richiamata al termine della creazione del DB. In
caso di successo err == null.
Metodi del’oggetto database
Il metodo .serialize() fa sì che il dbms esegua tutti i comandi interni in modo sincrono,
snaturando un po’ la filosofia ad eventi di Node.js. Ogni riga di accesso al database diventa bloccante
ed il programma non avanza fino a quando il dbms non ha terminato l’operazione richiesta.
Senza .serialize() la chiamata al dbms non è più bloccante ed i comandi vengono inviati
sostanzialmente in parallelo. Va bene per comandi che agiscono su dati indipendenti, ma è molto più
complicato da gestire in quanto occorre gestire i vari step tramite funzioni di callback annidate.
Notare però che serialize non è bloccante rispetto alle istruzioni che vengono dopo, per cui
un eventuale invio dei dati al client deve essere eseguito all’interno di serialize.
Il metodo .each(“sql”, callback(err, row), completionCallBack(err, nRecord))
consente di eseguire comandi SQL eseguendo subito dopo una scansione del recordset risultante.
 Dopo ogni iterazione viene richiamata la funzione di callback associata a cui viene passato un
oggetto row contenente tutti i campi restituiti dalla query.
In caso di recordset vuoto la funzione di callback non viene richiamata.
 Al termine dell’elaborazione viene richiamata la completionCallBack facoltativa,
ma molto utile nel caso in cui, al termine dell’elaborazione, sia necessario apportare
modifiche alla pagina html e/o inviare i dati al client.
Il metodo .all(“sql”, callback(err, rows) è simile a .each() però la funzione di callback
viene eseguita solo al termine dell’intera query. All’interno della funzione di callback è comunque
possibile eseguire un ciclo rows.forEach(function (row) per scandire il recordset dei dati.
Preferibile per query che restituiscono pochi dati.
Il metodo .close() chiude la connessione al database.
Il metodo .run(“sql”, callabck(err)) consente di eseguire comandi DDL / DML. L’eventuale
funzione di callback viene richiamata al termine dell’esecuzione,
In caso di successo err==null e this contiene due proprietà: lastID che contiene l’ID dell’ultimo
record aggiunto (in caso di insert) e changes che, in caso di Update e Delete, indica il numero di
record interessati dall’operazione.
Il metodo .prepare(“sql”) consente di preparare un comando DDL / DML che dovrà essere
eseguito più volte, eventualmente anche con dati differenti. Il metodo .prepare restituisce uno
statement cioè un “oggetto” sql privo dotato di appositi segnaposti che potrà essere eseguito più
volte. Il metodo statement.run(values) consente di eseguire lo statement
Il metodo statement.finalize() consente di chiudere la transazione iniziata mediante .prepare
pag 3
Tecnologie - Classe Quarta
robertomana.it
SQLite
Nota sulla gestione della Primary Key
Quando si crea una nuova tabella, Il tipo INTEGER PRIMARY KEY indica una primary key numerica
non autoincrement. L’utente, in fase di Insert Into, gli può assegnare un valore esplicito (positivo),
oppure può lasciare che sia il dbms ad assegnare automaticamente un valore di una unità più grande
rispetto al massimo id presente all’interno del DB.
La clausola INTEGER PRIMARY KEY ASC vincola l’utente all’inserimento di un valore maggiore
rispetto al massimo attualmente presente all’interno del database.
La clausola INTEGER PRIMARY KEY AUTOINCREMENT definisce una chiave di tipo contatore.
Se invece in fase di Create Table non viene definita nessuna primary key, sqLite provvede ad
creare automaticamente un campo rowid di tipo autoincrement.
 Questo campo in realtà viene aggiunto in tutti i casi. Se però viene definita una chiave utente,
rowid diventa praticamente inaccessibile
 In caso di assenza della chiave utente, rowid diventa visibile ma non viene comunque restituito
da SELECT * che restituisce soltanto i campi utente.
Per vedere il rowid occorre richiederlo esplicitamente:
var sql = "select *, rowid from studenti";
Una tabella in cui si desidera NON avere la chiave rowid, deve essere esplicitamente dichiarata come
WITHOUT ROWID (possibile solo dalla vers 3.8.2 in avanti).
CREATE TABLE IF NOT EXISTS miaTabella(
word TEXT PRIMARY KEY,
cnt INTEGER
) WITHOUT ROWID;
Nota sull’utilizzo dei placeholder
I palceholder possono essere utilizzati in tre modi diversi, come indicato nei seguenti esempi:
// Directly in the function arguments.
db.run("UPDATE tbl SET name = ? WHERE id = ?",
"mario", 2);
// As an array.
db.run("UPDATE tbl SET name = ? WHERE id = ?",
[ "mario", 2 ]);
// As an object with named parameters.
db.run("UPDATE tbl SET name = $name WHERE id = $id", {
$id: 2,
$name: "mario"
});
Riferimenti per SQLite
https://github.com/mapbox/node-sqlite3/wiki/API
http://stackoverflow.com/questions/21069059/node-js-and-sqlite3-npm-moduleobtain-all-rows-in-databaseeach
pag 4
Tecnologie - Classe Quarta
robertomana.it
SQLite
Interfacciamento fra Java e SQLite
JDBC è il wrapper java di ODBC, cioè un driver ODBC ‘specializzato’ per Java.
Sul sito https://bitbucket.org/xerial/sqlite-jdbc/downloads scaricare sqlite-jdbc-3.7.15-M1.jar
I file.jar sono genericamente file compatti contenenti delle librerie java.
L’istallazione del driver JDBC (il cui scopo è quello di consentire l’accesso da Java), provvede automaticamente anche ad
installare le librerie base necessarie al funzionamento di SQLite.
Tutorial Completo: http://www.tutorialspoint.com/sqlite/sqlite_java.htm
Connessione
Dal menù File / Project Structure
scegliere la scheda Libraries,
utilizzare il pulsante + per aggiungere una nuova libreria
scegliere JAVA
scegliere tramite PATH la libreria .jar da includere al progetto
import java.sql.*;
public static void main(String[] args) {
Connection c = null;
try{
Class.forName("org.sqlite.JDBC");
c=DriverManager.getConnection("jdbc:sqlite:test.db");
}
catch(Exception ex) {
System.err.println(ex.getClass().getName()+ " : " + ex.getMessage());
System.exit(0);
}
System.out.println("OK");
}
Se il database test.db non esiste viene automaticamente creato.
La classe org.sqlite.JDBC è una delle classi contenute all’interno del file .jar scaricato.
pag 5
Tecnologie - Classe Quarta
robertomana.it
SQLite
Interfacciamento fra Nodejs e SQLite
Occorre innanzitutto installare SQL Lite con le librerie necessarie per l’utilizzo con Node.js
npm install sqlite3
var sqlite = require("./node_modules/sqlite3"); // oppure
= require('C:/Users/admin/AppData/Roaming/npm/node_modules/sqlite3');
sqlite.verbose();
var db = new sqlite.Database(“studenti.db”);
db.serialize(function(){
var sql = "select * from studenti";
db.each(sql,
function(err, row){
console.log(row.id + " - " + row.nome);
},
function(err, nRecord){
console.log("--------------------");
}
);
db.all(sql, function(err, rows) {
if (err) throw err;
if (rows.length == 0)
console.log("Hey, no records found ...");
else
rows.forEach(function (row) {
console.log(row.id + " - " + row.nome);
});
console.log("--------------------");
});
db.get(sql, function(err, row) {
if(row != undefined){
………………
}
});
// restituisce un solo record
db.run("CREATE TABLE IF NOT EXISTS foo (id INTEGER NOT NULL
PRIMARY KEY, desc TEXT)");
var stmt = db.prepare("INSERT INTO foo VALUES (?, ?)");
for (var i = 1; i <= 10; i++)
stmt.run(i, "Element " + i);
stmt.finalize();
db.close();

Ogni campo non inizializzato da INSERT INTO assume il valore NULL.

Non sembra consentito fare dei console.log fra un comando db e l’altro
pag 6