SQLite

Què és SQLite

http://www.sqlite.org/

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain.

Documentació: http://www.sqlite.org/docs.html

Instal.lant SQLite

descàrrega: http://www.sqlite.org/download.html (van per la versió 3.6)

$ sudo apt-get install sqlite3 libsqlite3-dev

sqlite3 ja està instal.lat, s'instal.la el dev. Resulta que està instal.lada la versió 2.8.17

Desinstal.lo (sudo apt-get install sqlite3) i torno a instal.lar

...
Configurando sqlite3 (3.5.9-3) ...
joan@joan-laptop:~$ sqlite
SQLite version 2.8.17
Enter ".help" for instructions
sqlite>

Veig que no era veritat que estigués instal.lada la versió 2.8.17, això deu ser la versió de la consola.

sqlite> .help
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in a text format
.echo ON|OFF           Turn command echo on or off
.exit                  Exit this program
.explain ON|OFF        Turn output mode suitable for EXPLAIN on or off.
.header(s) ON|OFF      Turn display of headers on or off
.help                  Show this message
.indices TABLE         Show names of all indices on TABLE
.mode MODE             Set mode to one of "line(s)", "column(s)", 
                       "insert", "list", or "html"
.mode insert TABLE     Generate SQL insert statements for TABLE
.nullvalue STRING      Print STRING instead of nothing for NULL data
.output FILENAME       Send output to FILENAME
.output stdout         Send output to the screen
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.schema ?TABLE?        Show the CREATE statements
.separator STRING      Change separator string for "list" mode
.show                  Show the current values for various settings
.tables ?PATTERN?      List names of tables matching a pattern
.timeout MS            Try opening locked tables for MS milliseconds
.width NUM NUM ...     Set column widths for "column" mode

Utilitzant SQLite

http://www.sqlite.org/sqlite.html

Des de la línia de comandes de la shell:

$ sqlite3 test.db
$ sqlite3 test.db "create table t1 (t1key INTEGER PRIMARY KEY,data TEXT,num double,timeEnter DATE);"
$ sqlite3 test.db "insert into t1 (data,num) values ('This is sample data',3);"
$ sqlite3 test.db "insert into t1 (data,num) values ('More sample data',6);"
$ sqlite3 test.db "insert into t1 (data,num) values ('And a little more',9);"

As expected, doing a select returns the data in the table. Note that the primary key "t1key" auto increments; however, there are no default values for timeEnter. To populate the timeEnter field with the time, an update trigger is needed. Note that you should not use the abbreviation "INT" when working with the PRIMARY KEY. You must use "INTEGER" for the primary key to update.

$ sqlite3 test.db "select * from t1 limit 2";
    1|This is sample data|3|
    2|More sample data|6|

Des de la línia de comandes del SQLite:

$ sqlite3 ex1
SQLite version 3.3.10
Enter ".help" for instructions
sqlite> create table tbl1(one varchar(10), two smallint);
sqlite> insert into tbl1 values('hello!',10);
sqlite> insert into tbl1 values('goodbye', 20);
sqlite> select * from tbl1;
hello!|10
goodbye|20

La taula SQLITE_MASTER:

The database schema in an SQLite database is stored in a special table named "sqlite_master". You can execute "SELECT" statements against the special sqlite_master table just like any other table in an SQLite database. For example:

    $ sqlite3 ex1
    SQlite vresion 3.3.10
    Enter ".help" for instructions
    sqlite> select * from sqlite_master;
        type = table
        name = tbl1
    tbl_name = tbl1
    rootpage = 3
         sql = create table tbl1(one varchar(10), two smallint)
    sqlite>

De les comandes que podem consultar amb .help, m'interessa especialment

mode: canviar el format de sortida

Podem fer moltes coses. Per exemple,

sqlite> .separator ", "
sqlite> select * from tbl1;
hello, 10
goodbye, 20
sqlite>

enviar resultat a fitxer i carregar fitxer

Enviem el resultat d'una select al fitxer:

sqlite> .mode list
sqlite> .separator |
sqlite> .output fitxer.txt
sqlite> select * from tbl1;
sqlite> .exit
$ cat test_file_1.txt
hello|10
goodbye|20

I ara importem el fitxer:

joan@joan-laptop:~$ sqlite3 ex1
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> delete from tbl1;
sqlite> select * from tbl1;
sqlite> .import fitxer.txt tbl1
sqlite> select * from tbl1;
hello!|10
goodbye|20

Programant SQLite

Amb un script shell podem fer coses més o menys complicades:

$ sqlite3 ex1 'select * from tbl1' | awk '{printf "<tr><td>%s<td>%s\n",$1,$2 }'
<tr><td>hello!|10<td>
<tr><td>goodbye|20<td>

man awk -> pattern scanning and text processing language

---

creat per Joan Quintana Compte, febrer 2009