178 lines
5.7 KiB
Plaintext
178 lines
5.7 KiB
Plaintext
Jim Sqlite extension documentation.
|
|
Copyright 2005 Salvatore Sanfilippo <antirez@invece.org>
|
|
|
|
|
|
Overview
|
|
~~~~~~~~
|
|
|
|
The Sqlite extension makes possible to work with sqlite (http://www.sqlite.org)
|
|
databases from Jim. SQLite is a small C library that implements a
|
|
self-contained, embeddable, zero-configuration SQL database engine. This
|
|
means it is perfect for embedded systems, and for stand-alone applications
|
|
that need the power of SQL without to use an external server like Mysql.
|
|
|
|
Basic usage
|
|
~~~~~~~~~~~
|
|
|
|
The Sqlite extension exports an Object Based interface for databases. In order
|
|
to open a database use:
|
|
|
|
set f [sqlite3.open dbname]
|
|
|
|
The [sqlite3.open] command returns a db handle, that is a command name that
|
|
can be used to perform operations on the database. A real example:
|
|
|
|
. set db [sqlite3.open test.db]
|
|
sqlite.handle0
|
|
. $db query "SELECT * from tbl1"
|
|
{one hello! two 10} {one goodbye two 20}
|
|
|
|
In the second line the handle is used as a command name, followed
|
|
by the 'method' or 'subcommand' ("query" in the example), and the arguments.
|
|
|
|
The query method
|
|
~~~~~~~~~~~~~~~~
|
|
|
|
The query method has the following signature:
|
|
|
|
$db query SqlQuery ?args?
|
|
|
|
The sql query may contain occurrences of "%s" that are substituted
|
|
in the actual query with the following arguments, quoted in order
|
|
to make sure that the query is correct even if these arguments contain
|
|
"'" characters. So for example it is possible to write:
|
|
|
|
. $db query "SELECT * from tbl1 WHERE one='%s'" hello!
|
|
{one hello! two 10}
|
|
|
|
Instead of hello! it is possible to use a string with embedded "'":
|
|
|
|
. $db query "SELECT * from tbl1 WHERE one='%s'" a'b
|
|
(no matches - the empty list is returned)
|
|
|
|
This does not work instead using the Tcl variable expansion in the string:
|
|
|
|
. $db query "SELECT * from tbl1 WHERE one='$foo'"
|
|
Runtime error, file "?", line 1:
|
|
near "b": syntax error
|
|
|
|
In order to obtain an actual '%' character in the query, there is just
|
|
to use two, like in "foo %% bar". This is the same as the [format] argument.
|
|
|
|
Specification of query results
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
|
|
In one of the above examples, the following query was used:
|
|
|
|
. $db query "SELECT * from tbl1"
|
|
{one hello! two 10} {one goodbye two 20}
|
|
|
|
As you can see the result of a query is a list of lists. Every
|
|
element of the list represents a row, as a list of key/value pairs,
|
|
so actually every row is a Jim dictionary.
|
|
|
|
The following example and generated output show how to take advantage
|
|
of this representation:
|
|
|
|
. set res [$db query "SELECT * from tbl1"]
|
|
{one hello! two 10} {one goodbye two 20}
|
|
. foreach row $res {puts "One: $row(one), Two: $row(two)"}
|
|
One: hello!, Two: 10
|
|
One: goodbye, Two: 20
|
|
|
|
To access every row sequentially is very simple, and field of a row
|
|
can be accessed using the $row(field) syntax.
|
|
|
|
The close method
|
|
~~~~~~~~~~~~~~~~
|
|
|
|
In order to close the db, use the 'close' method that will have as side effect
|
|
to close the db and to remove the command associated with the db.
|
|
Just use:
|
|
|
|
$db close
|
|
|
|
Handling NULL values
|
|
~~~~~~~~~~~~~~~~~~~~
|
|
|
|
In the SQL language there is a special value NULL that is not the empty
|
|
string, so how to represent it in a typeless language like Tcl?
|
|
For default this extension will use the empty string, but it is possible
|
|
to specify a different string for the NULL value.
|
|
|
|
In the above example there were two rows in the 'tbl1' table. Now
|
|
we can add using the "sqlite" command line client another one with
|
|
a NULL value:
|
|
|
|
sqlite> INSERT INTO tbl1 VALUES(NULL,30);
|
|
sqlite> .exit
|
|
|
|
That's what the sqlite extension will return for default:
|
|
|
|
. $db query "SELECT * from tbl1"
|
|
{one hello! two 10} {one goodbye two 20} {one {} two 30}
|
|
|
|
As you can see in the last row, the NULL is represented as {}, that's
|
|
the empty string. Using the -null option of the 'query' command we
|
|
can change this default, and tell the sqlite extension to represent
|
|
the NULL value as a different string:
|
|
|
|
. $db query -null <<NULL>> "SELECT * from tbl1"
|
|
{one hello! two 10} {one goodbye two 20} {one <<NULL>> two 30}
|
|
|
|
This way if the empty string has some semantical value for your
|
|
dataset you can change it.
|
|
|
|
Finding the ID of the last inserted row
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
|
|
This is as simple as:
|
|
|
|
. $db lastid
|
|
10
|
|
|
|
Number of rows changed by the most recent query
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
|
|
This is also very simple, there is just to use the 'changes' method
|
|
without arguments.
|
|
|
|
. $db changes
|
|
5
|
|
|
|
Note that if you drop an entire table the number of changes will
|
|
be reported as zero, because of details of the sqlite implementation.
|
|
|
|
That's all,
|
|
Enjoy!
|
|
Salvatore Sanfilippo
|
|
|
|
p.s. this extension is just the work of some hour thanks to the cool
|
|
clean C API that sqlite exports. Thanks to the author of sqlite for this
|
|
great work.
|
|
|
|
In memory databases
|
|
~~~~~~~~~~~~~~~~~~~
|
|
|
|
SQLite is able to create in-memory databases instead to use files.
|
|
This is of course faster and does not need the ability to write
|
|
to the filesystem. Of course this databases are only useful for
|
|
temp data.
|
|
|
|
In-memory DBs are used just like regular databases, just the name used to
|
|
open the database is :memory:. That's an example that does not use the
|
|
filesystem at all to create and work with the db.
|
|
|
|
package require sqlite3
|
|
set db [sqlite3.open :memory:]
|
|
$db query {CREATE TABLE plays (id, author, title)}
|
|
$db query {INSERT INTO plays (id, author, title) VALUES (1, 'Goethe', 'Faust');}
|
|
$db query {INSERT INTO plays (id, author, title) VALUES (2, 'Shakespeare', 'Hamlet');}
|
|
$db query {INSERT INTO plays (id, author, title) VALUES (3, 'Sophocles', 'Oedipus Rex');}
|
|
set res [$db query "SELECT * FROM plays"]
|
|
$db close
|
|
foreach r $res {puts $r(author)}
|
|
|
|
Of course once the Jim process is destroyed the database will no longer
|
|
exists.
|