SQLite has many users employing dozens of programming language. But
what most of these users fail to realize is that SQLite is inspired by,
originally designed for use with, tightly bound to, and dependent upon
Tcl and Tcl/Tk. As a result, SQLite is a particularly handy addition
to every Tcl and Tcl/Tk programmers toolbox.
SQLite can, of course, be used in place of a traditional client/server
database engine. But because SQLite is serverless and stores an entire
database in a single ordinary disk file (or at the programmer's option,
entirely in memory) and because it is so well integrated with Tcl,
SQLite
finds uses that go well beyond the traditional client/server database
engine. Some of these non-traditional uses for SQLite include:
* A local cache for a remote enterprise database
* Internal storage for complicated data structures in Tcl
applications
* Object persistence
* Persistent storage for configuration options and user preferences
* As a chalkboard for interprocess communication
* Last, but not least, as a application file format for advanced
Tcl and Tcl/Tk programs
If you would like to learn more about SQLite and how it relates to
Tcl/Tk, visit the website at http://www.sqlite.org/. Or consider
attending one of the tutorials on SQLite at the upcoming Tcl/Tk
Conference (http://www.tcl.tk/community/tcl2005/) in Portland, OR
on October 25.
I can only emphasise this. I fisrt used metakit as a storage and
finally switched to sqlite with the new version 3 because it then
became equally fast and the database files equally small compared to
metakit. And I wanted better query possibilities, which sqlite offers.
The syntax is so easy and yet so powerful and with SQL at my
fingertips, this is great really!!
There are, however, some things to be aware of. Some tips for
optimizing the performance were very helpful:
http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html
Although sqlite is able to substitute variable names in queries like
db eval {select * from table where col=$myVar}
so you don't have to quote them, this mechanism fails in a single
situations. I had a hard time figuring this out. While this
db eval {select * from table where col=$myVar(item)}
works, this here
db eval {select * from table where col=$myVar($item)}
does not work. It seems, as if the substitution stops when array
variables have key that are variables themselves. I have not checked,
whether this has been resolved in the most recent version of the
tclsqlite binding, but I feel these should work.
Torsten
Are there built-in functions in SQLite and how are they handled? These
would be string functions (length, substring, etc.), date (month, day,
difference between two dates, etc.) and so on. Are these available
natively? If not, how would one go about writing these functions at
SQL level? (I know Tcl itself provides these, I am wondering if SQLite
has them and how they are used within queries.)
You can create SQL functions using Tcl.
proc db_now {} {
return [clock format [clock seconds] -format "%Y-%m-%d %H:%M:%S"]
}
db function NOW ::db_now
--
WK
sqlite provides the following functions: abs, coalesce, glob, ifnull,
last_insert_rowid, length, like, lower, max, min, nullif, quote,
random, round, soundex, sqlite_version, substr, typeoff, upper.
Aggregate functions are avg, sum, min, max, count. See also
http://www.sqlite.org/lang_expr.html.
All other functions can be written in C using the
sqlite3_create_function() API or in Tcl using the method described
already by Wojciech Kocjan.
Torsten
> All other functions can be written in C using the
> sqlite3_create_function() API or in Tcl using the method described
> already by Wojciech Kocjan.
>
> Torsten
Thanks to you and to Wojciech. SQLite looks more attractive now.
Yes, even Google and O'Reilly seemed to have missed this point:
"One person was able to get Python, Perl & PHP people to all agree on
something."
http://osdir.com/Article6677.phtml
As normal, Tcl does not get mentioned. Anyways, congrats on the award,
well deserved.
And they forgot C and C++, which also use sqlite. And they forgot to
mention the spotlight technology from MacOS X which uses sqlite for
storage of metadata. Would be cool to have a Tcl app replacement for
spotlight on the Mac for doing sophisticated searches without the need
to use the Mac's command line tool ...
Regards,
Arjen
I have actually written a SQLite3 based VFS for personal use for some
time and it seems stable.
Used it to build a tclkit based app without problems.
Here is a link to the script file:
http://mega.ist.utl.pt/~tngd/tcl/sqlite3vfs.tcl