Joan: /* Instal.lant SQLite */

2012-10-26T17:37:15Z

Instal.lant SQLite

Pàgina nova

=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)
<pre>
$ sudo apt-get install sqlite3 libsqlite3-dev
</pre>
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
<pre>
...
Configurando sqlite3 (3.5.9-3) ...
joan@joan-laptop:~$ sqlite
SQLite version 2.8.17
Enter ".help" for instructions
sqlite>
</pre>

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

<pre>
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
</pre>

=Utilitzant SQLite=
http://www.sqlite.org/sqlite.html

Des de la línia de comandes de la shell:
<pre>
$ 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);"
</pre>

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.

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

Des de la línia de comandes del SQLite:
<pre>
$ 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
</pre>

===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:
<pre>
$ 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>
</pre>

De les comandes que podem consultar amb .help, m'interessa especialment
===mode: canviar el format de sortida===
Podem fer moltes coses. Per exemple,
<pre>
sqlite> .separator ", "
sqlite> select * from tbl1;
hello, 10
goodbye, 20
sqlite>
</pre>

===enviar resultat a fitxer i carregar fitxer===
Enviem el resultat d'una select al fitxer:
<pre>
sqlite> .mode list
sqlite> .separator |
sqlite> .output fitxer.txt
sqlite> select * from tbl1;
sqlite> .exit
$ cat test_file_1.txt
hello|10
goodbye|20
</pre>

I ara importem el fitxer:
<pre>
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
</pre>

=Programant SQLite=

Amb un script shell podem fer coses més o menys complicades:
<pre>
$ 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>
</pre>

man awk -> pattern scanning and text processing language

{{Autor}}, febrer 2009
[[Categoria: SQLite]]
[[Categoria: Bases de dades]]

SQLite - Historial de revisió

Joan: /* Instal.lant SQLite */