SQLite da riga di comando (2)

Premessa

Dopo una veloce carrellata dei comandi di SQLite visti nell'articolo precedente denominato SQLite da riga di comando (1), in questo articolo andremo un pochino più nei dettagli sebbene non è scopo di questo articolo, essere una guida al linguaggio SQL..

Creiamo/modifichiamo un database

Dopo aver scaricato e scompattato SQLite, la prima cosa da fare è modificare un database o, se questo non esiste, dobbiamo crearlo:

sqlite3 prova.db

A questo punto ci verrà presentata la shell già vista nel precedente articolo. Per essere sicuri di lavorare sul database sopra citato possiamo digitare il comando .database con relativo output in rosso.

sqlite- .database

seq name file
--- ----- -------------------
0 main j:\sqlite\prova.db

Creiamo una tabella

Come ho già detto, dato che si parla di comandi SQL, rimando ad una guida più esaustiva. In questa sede si vuol solo dare una idea di massima. La prima cosa da fare, però, è proprio la costruzione di una tabella dove immagazzinare i nostri dati. A titolo di esempio, pensiamo ad un elenco di prodotti.

sqlite- CREATE TABLE Prodotti (IDProdotto INTEGER PRIMARY KEY, Colore varchar(25), Prezzo double);

Il programma non ci restituirà alcun output che significa che tutto è andato a buon fine.
Quando un numero intero (integer) è dichiarato chiave primaria come nell'esempio, sqlite lo identifica come autoincrementale.

Viste ed indici

Le viste sono come tabelle virtuali. Permettono di vedere le singole tabelle o più tabelle in join, con criteri differenti tipo "due campi di A, 1 campo di B e 3 di C come se fossero un'unica tabella".
Gli indici, al contrario, sono dei campi privilegiati. Essi, se interrogati, hanno dei tempi di risposta decisamente minori.
In altre parole se chiedete tutti gli abitanti di una città e il campo città è indicizzato, la ricerca su vasta scala sarà decisamente più veloce.
La differenza non si nota con qualche centinaio di record ma con migliaia o milioni di record...
Vediamo un esempio di vista e di indice:

sqlite- CREATE VIEW vistaRosso AS select Colore, Prezzo from Prodotti where Colore="Rosso";

sqlite- CREATE INDEX indiceProva on Prodotti (IDProdotto);

Elenco tabelle, viste, indici, schema

Può essere necessario "ricordarsi" la lista degli oggetti elencati nel sottotitolo. Di seguito i comandi con i relativi output in rosso. Una nota di riguardo allo schema: è il termine con il quale SQLite indica gli schemi di creazione degli oggetti come tabelle, viste.

sqlite- .tables
Prodotti

sqlite- .tables P%
Prodotti

sqlite- .indices
indiceProva

sqlite- .indices Prodotti
indiceProva

sqlite- .schema
CREATE TABLE Prodotti (IDProdotto INTEGER PRIMARY KEY, Colore varchar(25), Prezzo double);
CREATE VIEW vistaRosso AS select Colore, Prezzo from Prodotti where Colore="Rosso";
CREATE INDEX indiceProva on Prodotti (IDProdotto);

Inserimento record

A questo punto è arrivato il momento di inserire un po' di record.
Nel precedente articolo ho già fatto vedere come si fa ed ho anche evidenziato l'opportunità di importare dei files di testo (CSV) creati in Excel o OpenOffice Calc. Rimando quindi all'articolo.

Visualizzare i dati

La parte più affascinante è senza dubbio l'interrogazione del database. Anche qui rimando ad un manuale più esaustivo per la sintassi del comando SELECT nel linguaggio SQL.

sqlite- SELECT * FROM Prodotti;
1|Rosso|44.55
2|Rosso|33.33
3|Giallo|34.33

Eventualmente si può rendere più gradevole l'ouput si possono utilizzare delle opzioni come mode e/o headers. Personlamente, prima del select, consiglio le seguenti opzioni:

sqlite- .headers on
sqlite- .mode column
sqlite- SELECT * FROM Prodotti;

IDProdotto Colore Prezzo
----------- ------ -------
1 Rosso 44.55
2 Rosso 33.33
3 Giallo 34.33

Esportare/Importare i dati

Una possibilità molto interessante è la possibilità di esportare il database o anche solo una tabella in formati differenti.

sqlite- .output fileoutput.sql
sqlite- .dump
sqlite- .output stdout

In questo modo verrà creato un file con le istruzioni SQL per la creazione delle tabelle, inserimento dati.
Tale file può quindi essere un backup a tutti gli effetti, importabile in un secondo momento come segue:

sqlite- .read fileoutput.sql

SQLite da shell di sistema

La feature più interessante di tutte, a mio avviso, è la possibilità di dare i medesimi comandi non da shell interattiva ma da shell di sistema.
Questo ci da modo di creare degli script di sistema (file.bat in windows, script in linux e MacOS) in maniera estremamente agevole e veloce.
Non mi dilungherò troppo ma mi limiterò a fare tre esempi:

1) Selezione: sqlite3 -header -column prova.db 'select * from Prodotti;'
2) Visionare lo schema: sqlite3 prova.db '.schema'
3) Inserire un record: sqlite3 prova.db 'insert into Prodotti values (NULL, "Giallo", 33.55)
4) Fare il backup: sqlite3 prova.db '.dump' > fileoupt.sql

Conclusioni

SQLite è, senza dubbio, un sistema di database molto interessante; per degli approfondimenti rimando ad un mio precedente articolo: sqlite, libreria sql in 200kb.
Personalmente lo reputo un alleato estremamente potente ed utile nella vita "informatica" di tutti i giorni come memorizzatore di log ma anche come aiuto nella operatività giornaliera.
Ovviamente non va bene se si intende utilizzarlo come sistema multiutente ma saprà donare moltissime soddisfazioni in tutti quei contesti monoutente dove è richiesta rapidità, semplicità d'uso e poco ingombro.