SQLite
Contingut
Què és SQLite
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