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