SQLite and Tcl/Tk

[d...@hwaci.com]

SQLite is a small (230KiB) but fully-functional, embedded SQL database
engine. It is widely used. SQLite is now integrated into Mac OSX and
Solaris as well as countless open-source and proprietary software
projects
and also in many hardware gadgets. SQLite is a winner of the 2005
Google/O'Reilly Open-Source Award.

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.

[Torsten Reincke]

> 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.

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

[tun...@yahoo.com]

d...@hwaci.com wrote:
> SQLite is a small (230KiB) but fully-functional, embedded SQL database
> engine. It is widely used. SQLite is now integrated into Mac OSX and
> Solaris as well as countless open-source and proprietary software
> projects
> and also in many hardware gadgets.

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.)

[Wojciech Kocjan]

tun...@yahoo.com napisał(a):

> 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

[Torsten Reincke]

> 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.

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

[tun...@yahoo.com]

Torsten Reincke wrote:

> 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.

[bs]

"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. "

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.

[Torsten Reincke]

> 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 ...

[Arjen Markus]

Maybe it _is_ more difficult to get these people to agree on something
than it is with Tclers ... Anyway Tcl was no part of the happening as
such, it would seem.

Regards,

Arjen

[lister]

Torsten Reincke wrote:
>>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.
>

Is there an SQLite based VFS?

[Robert]

This is a good place to start for VFS: http://wiki.tcl.tk/2466

I did not see one for SQLite...

Robert

[Tiago Dionizio]

lister wrote:
> Torsten Reincke wrote:
> >>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.
> >
> Is there an SQLite based VFS?

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