Pagine

giovedì 14 luglio 2011

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.

12 commenti:

  1. GRAZIE!
    Molto utile

    Solo una integrazione relativa all'export (sperando che sia utile spiegare in maniera piu' semplice quello che hai spiegato molto bene):
    per fare l'export di una query, ad esempio, si puo' fare cosi'
    .mode csv # imposta come formato il csv
    .separator ";" # usa come separatore il carattere ";"
    [.headers on] # se si vuole esportare anche i campi
    .output nome_file_da_esportare.csv # imposta dove indirizzare l'output, cioe' in un file
    .SELECT * FROM Prodotti; # esporta una query

    ciao a tutti

    snow

    RispondiElimina
  2. Ciao stò utilizzando SQLite in una applicazione java android...
    ora dovrei estrarre l'ultimo dato inserito in un campo della tabella, ho utilizzato questo codice: SELECT LAST(campo5) FROM Tabella ORDER BY id ASC ma non funziona. Qual'è la sintassi giusta??
    Grazie

    RispondiElimina
  3. Io userei il LIMIT 1 e quindi:
    SELECT id,campo5 FROM Tabella ORDER BY id ASC LIMIT 1

    Al posto di ASC puoi usare DESC a seconda che tu voglia il primo o l'ultimo. Ti riesce?

    RispondiElimina
  4. Grazie tutto ok!!

    RispondiElimina
  5. Conosci anche il Java??

    RispondiElimina
  6. Ciao e scusa ancora il disturbo, ma non riesco a risolvere un problema nella mia app Java Android che utilizza SQLite....
    ho fatto questa query e tramite una funzione delete permetto all'utente di eliminare singolarmente i record
    inseriti...
    SELECT Data,Riposo FROM Giornate WHERE riposo>=1 AND data LIKE '"+anno+"-"+mese+"%'

    Ora quando viene inserito il giorno di riposo, nel db viene memorizzata la data scelta nel campo data e nel campo riposo il valore 1. Fin qui tutto ok, ma il problema stà nel fatto che quando lancio la mia funzione delete(che funziona bene perchè la utilizzo per altri campi che hanno valori sempre diversi)invece di eliminare solamente il record scelto vengono eliminati tutti i record riposo...mi sono informato e a questo punto credo che sia sbagliata la query...ma dove e perchè???Grazie in anticipo!!

    RispondiElimina
  7. @Anonimo, no, non conosco Java..

    Christian mi sfugge una cosa... Come fai a capire quale è il record "selezionato"? La query tira fuori tutti i record "riposo" ma quando l'utente seleziona quello di interesse come fa il programma a capire che si tratta di quello da eliminare?

    RispondiElimina
  8. Infatti il campo riposo (è una stringa che vale sempre 1)quindi vengono eliminati tutti. non riesco ad uscirne.

    RispondiElimina
    Risposte
    1. Christian è un problema di programmazione. Dovresti avere un campo in più da chiamare "selezione" che sia sempre a zero o NULL; quando l'utente fa una query gli appare, per ogni record un box da flaggare e l'aventuale flag valorizza il campo citato.
      A quel punto cancelli quelli che hanno il campo valorizzato e non quelli che hanno il campo riposo pari ad 1

      Elimina
  9. ho dimenticato di chiederti che l'ORDER BY DESC di quella query non ordina..lo scrivo così:
    "SELECT Data,Riposo FROM Giornate WHERE riposo>=1 AND data LIKE '"+anno+"-"+mese+"%' ORDER BY Data DESC"

    RispondiElimina
  10. La notazione corretta in un file .bat è la seguente
    sqlite3 -header -csv -separator ";" prova.db "SELECT * FROM nometabella;" > nomefile.csv

    per più tabelle ripetere la riga sempre dichiarando all'inizio sqlite3

    RispondiElimina