SQLite

Introduction edit

An SQL database (written in C but with Tcl extension/bindings as part of the source tree and with Tcl used for most testing). Author is D. Richard Hipp. Features include:

Atomic, Consistent, Isolated, and Durable ACID transactions
Zero-configuration - there is no setup or administration needed
Simple usage from Tcl
A complete database is stored as a single cross-platform disk file
Supports huge databases (tebibytes) and huge strings and blobs (gibibytes)
Small footprint ("lean", not "crippled")

SQLite is not intended to be a replacement for client/server enterprise database engines like Oracle, MySQL, or PostgreSQL. SQLite is intended to replace the use of open to write and create flat files in proprietary formats used to store miscellaneous information.

SQLite is especially useful in Tcl/Tk programs as an application file format. When the user selects the File->Open menu option, the application can connect to an SQLite database instead of reading and parsing a file. This makes the file format cross-platform, gives the application ACID I/O semantics and rollback capabilities, and provides a powerful query mechanism for accessing and updating application data.

The C-code portion of SQLite is used extensively in commercial products and is likely the single most widely deployed SQL database engine. Despite the fact that most people use only the C-code portions of SQLite, the author has stated that he considers SQLite to be a Tcl extension, not a C library that happens to have Tcl bindings. The author points out the well over half of SQLite (specifically all of the test scripts) are written in Tcl and that SQLite would never have been possible without Tcl.

Version 3.0 provides a lot of important new and improved functionality, including user-defined collating, limited table-level locking, 64-bit rowids, "manifest typing", proper UTF-{8,16} support, new concurrency possibilities, and sqlite-side dereferencing of Tcl variables (see below) [1].

List of associated software edit

 What: SQLite
 Where: http://www.sqlite.org/
 Description:  SQL database implementation in C.  Supports a large subset
        of SQL92 .  Comes with bindings for Tcl/Tk.
        The Tcl binding is stubs-enabled so it can be wrapped in a starkit
        if need be.
        Currently at version 3.6.16 .
 Updated: 3/2008
 Contact: mailto:drh@hwaci.com  (D. Richard Hipp)

 What: sqliteodbc
 Where: http://www.ch-werner.de/sqliteodbc/
 Description: ODBC driver for SQLite.
        Currently at version 0.66 .
 Updated: 05/2006
 Contact: See web site

 What: nsdbilite
 Description: Native SQLite driver for the nsdbi database interface.
 Updated: 06/2008

 What: javasqlite
 Where: http://www.ch-werner.de/javasqlite/
 Description: Java JDBC wrapper for sqlite.
        Currently at version 20050608 .
 Updated: 05/2006
 Contact: See web site

 What: wisql
 Where: http://www.ucolick.org/%7Ede/
        http://www.ucolick.org/cgi-bin/Tcl/wisql.cgi
        ftp://ftp.ucolick.org/pub/UCODB/wisql5.1.tar.gz
 Description: UCO wisql is an "upscale" version of Tom Poindexter's wisqlite
        according to the WWW page above.  Requires Tcl/Tk/tclX/sybtcl and
        tkbind.  Many other Sybase related tools are found at the above site
        include a forms GUI interface to Sybase called fosql.
 Updated: 02/2000
 Contact: mailto:de@ucolick.org  (De Clarke)

 What: tksqlite 0.5.6
 Where: http://reddog.s35.xrea.com/wiki/TkSQLite.html
 Updated: 07/2008
 Contact: See web site

The [Tcl/Tk Aqua] BI distribution at [3] has SQLite included (the current BI version 8.4.10 as of November 2005 has tclsqlite versions 2.8.16 and 3.2.1).

Acacio Cruz Compiled 3.3.13 Tcl-bindings for Mac OS X 10.4 PowerPC. Available here: http://www.acacio.com/tcl/libsqlite3.3.13-macosx10.4-PPC.tar.gz Latest build: macosx 10.5, Intel: http://www.acacio.com/tcl/tclsqlite3.6.7-macosx10.5-tcl8.6-x86.tgz

tclsqlite 3.0.8 extension library for Linux on an iPaq or Zaurus: http://www.sr-tech.com/testing/libtclsqlite3.so

There is a complete sqlite extension for Jim with a nice command based interface. See Jim Extensions for more information.

Tclkit Kitgen Build System contain sqlite 3.5.9 and tksqlite 0.5.6 as library and starpack.

Books edit

The Definitive Guide to SQLite. By Mike Owens. Apress, 2006. ISBN 1-59059-673-0, 464 pages [4]

SQLite (Developer's Library). By Chris Newman [5]

SQLite??. By Naoki Nishizawa [6]

Differences to other databases edit

SQLite has omitted some features, that a typical database implements [7].

By default, SQLite doesn't constrain referential integrity. It parses foreign keys, it builds an internal data structure to describe them--- but normally stops there. An equivalent constraint enforcement can be achieved using triggers. CHECK constraints are supported as of version 3.3.0 (the pragma 'PRAGMA foreign_key_list(table-name)' provides introspection on the foreign keys). With SQLite 3.6.19 and up, FOREIGN KEY constraint enforcement can be enabled via a PRAGMA [8].

SQLite uses manifest typing. This means, the datatype is a property of the value, not of the column. SQLite allows to store any value of any datatype into any column regardless of the declared type of that column. SQLite attempts to coerce values into the declared datatype of the column when it can. (Exception: INTEGER PRIMARY KEY column may only store integers).

Configuration under Windows and MacOS X edit

Configuration under Windows and MacOS is only a bit cumbersome, if you compile yourself. The FAQ advises for the former: "Create a subdirectory named tclsqlite into the lib directory of your tcl installation (usually C:\tcl\lib) and put there the tclsqlite.dll library. Then, put the line:

   load tclsqlite

at the beginning of your code, and you can then use the sqlite command in your tcl script."

Another way: Get a copy of tclsqlite3.dll, save it to e.g. \winnt\system32\ and then, in Tcl, do:

    load tclsqlite3.dll Sqlite3

Anther variation is to to put tclsqlite.dll in c:\tcl\lib\sqlite and create a pkgIndex.tcl file that says something like:

    package ifneeded sqlite 2.0 [list load [file join $dir \
        tclsqlite[info sharedlibextension]]]

Then, [package require sqlite] in the script.

Or, for SQLite 3:

    package ifneeded sqlite3 3.0 [list load [file join $dir \
        tclsqlite3[info sharedlibextension]] tclsqlite3]

Then, [package require sqlite3] in the script.

There is also a simpler way. The tclsqlite.dll file can be in a directory totally unrelated to Tcl. The directory only needs to be in the $PATH. Then just load tclsqlite.dll and it works (LES).

Usage examples edit

The best place to start is here: http://www.sqlite.org/tclsqlite.html

SQLite partakes of the Tcl spirit, in that it's easy to learn by starting it up and asking it about itself. If someone presents you with a database image unknown.db, for example, you can begin to introspect:

    # sqlite unknown.db
    ...
    sqlite> .help
    ...
    sqlite> .tables
    ...
    sqlite> .schema sqlite_master
    ...
    sqlite> select * from sqlite_master;
    ...

and so on. Notice that sqlite_master here is a keyword that names the table that holds the schema for unknown.db's instance.

kostix SQLite creates its command for the opened database in the global namespace, i.e.:

 % namespace eval ::foo {
   sqlite3 db mydbfile
 }
 % info comm ::foo::*
 %
 % info comm ::db*
 % ::db
So you must fully qualify the first argument to '''sqlite''' command if you want the database handling command to be created in a different namespace, like this:
 % namespace eval ::foo {
   sqlite3 [namespace current]::db mydbfile
 }
 % info comm ::foo::*
 % ::foo::db

( Side note: the hexadecimal number that returns from the sqlite3 command is the value of the sqlite3 pointer. It is used for testing and will likely be replaced by something less kludgy in the future. So don't use it.)

Tcl variable handling edit

Dereferencing

SQLite 3.0 introduced the capability of sqlite-side dereferencing of Tcl variables. This means that

    db eval {INSERT INTO table1 VALUES($name,$vx)}

will be interpreted sensibly, and, moreover, is both faster and more correct than

    db eval "INSERT INTO table1 VALUES($name,$vx)"

or its variants in the cases where the latter embeds SQL-privileged characters such as '.

Note, that dereferencing of array variables does only work partly:

  set item "one"
  set data(one) "a number"
  db eval {insert into table1 values ($data($item))}

will not do what you expect, but

  db eval {insert into table1 values ($data(one))}

will do the right thing. So, you should never use variables as keys in arrays, when SQLite is supposed to dereference the variable.

The dereferencing only works at specific places. The following is wrong:

 set table project
 set field projectName
 db eval {SELECT * FROM $table WHERE $field LIKE $searchterm)

A variable is not allowed as the table name. Here's one way to accomplish varying the table name:

 db eval [format {SELECT * FROM %s WHERE %s LIKE $searchterm} $table $field]

Which would expand to:

 db eval {SELECT * FROM project WHERE projectName LIKE 'some search term'}

Of course, you can also just use double quotes and backslashes where appropriate:

  db eval "SELECT * FROM $table WHERE $field LIKE \$searchterm"

Note, that if you create SQL queries like this, you better be very sure that $searchterm is under your control, because otherwise you are asking for SQL injection attacks (MJ).

DKF: It's actually easier to use the other variable dereference format (with a colon, which is ?Standard SQL? AIUI) where you can in situations like this. For example:

 db eval "INSERT INTO $table (a,b,c) VALUES (:tclVarA, :tclVarB, :tclVarC)"

If you're doing this, take care to put a space after the table name if you want to use a parenthesis straight afterwards (or use ${...}. Otherwise Tcl will assume you're doing an array dereference; probably not what you want.

Local variables

Sqlite creates local variables when a statement is issued in the form:

 db eval {select id from table1} {puts $id}

Here, the variable $id is automatically created by sqlite. This is always done, when you specify a script argument. This is very handy! But you must be carefull when doing queries like:

 db eval {select * from table1} {}

because you get a bunch of predefined variables matching the resulting columns without ever really specifying them. If the table has the columns a,b,c you get variables with the same names and these may interfere with your own variables ...

If you want to retain control over what variables exist in the calling context, then supply the name of an array variable instead:

 array unset name_of_array ;#Make sure array doesn't have extra column names from a previous resultset!
 db eval {select * from table1} name_of_array {parray name_of_array}

So, instead of a variable per column, you get an additional array element for each column (plus the extra element '*' which contains the column names). Try this particular example only on a small table! parray is called for each row.

A statement like the following will not work, however:

   db eval {select s.name, f.parent, f.name
            from files f, shares s
            where s.name = f.share
            ...
   } { puts ${s.name}}

The problem here is that both "shares" and "files" tables contain column named "name" so the disambiguation table prefixes were necessary. But Tcl variable binding mechanism of SQLite doesn't take those prefixes into account, so in the case above you'll end up with two variables "name" and one variable "parent". This behaviour is consistent with "big" RDBMSs like PostgreSQL in that column names of the result set produced by any SQL statement are arbitrary. The way to resolve this problem is to use as clauses, like this:

 db eval {
   select s.name as sname, f.parent as fparent, f.name as fname
   ...
 }

Thus, you will get variable names corresponding to the aliases: sname, fparent, and fname.

SQLite functions edit

TR MJ Apart from the built-in functions in SQLite, you can make your own. SQLite functions can give you capabillities similar to for example Oracle's PL/SQL. So how difficult is it to add regexp support? Easy. Just do:

  $dbhandle function regexp regexp

and then you have it! Usage like this:

  $dbhandle eval {select column1,column2 from table where column2 regexp '^something\s.*'}

See [9] for reference.

Here is another simple example, that adds a function to double a number:

 package require sqlite3
 sqlite3 db {}

 # create a Tcl procedure, that will get called when the db function is invoked
 proc double {num} {
    return [expr {$num*2}]
 }

 # register this procedure to the SQL engine
 db function double double

 # create a test table and fill it with some data
 db eval {create table test(i integer, i2 integer)}
 for {set i 0} {$i < 100} {incr i} {
    db eval {insert into test values($i,NULL)}
 }

 # show the records
 db eval {SELECT * from test} {
    puts "$i: $i2"
 }

 # update the values using the registered procedure
 # this is a contrived example, normally you would just use i2=2*i
 db eval {UPDATE test SET i2=double(i) WHERE i2 isnull}

 db eval {SELECT * from test} {
    puts "$i: $i2"
 }

Useful code snippets edit

US This recipe is for a hot backup of a sqlite database file (confirmed by drh on the sqlite mailing list) and translated to a short Tcl code snippet:

 # Connect to the database
 sqlite3 db $dbfile
 # Lock the database, copy and commit or rollback
 if {[catch {db transaction immediate {file copy $dbfile ${dbfile}.bak}} res]} {
   puts "Backup failed: $res"
 } else {
   puts "Backup succeeded"
 }

AMG: How does this compare to using the [database backup] command [10]?

Googie - 25/10/2011 - I believe both ways realize pretty much the same thing. They both keep write-lock during operation, but other connections can read the database. The difference is when you do a backup of in-memory database (a dump of "temp" database to file), or backup of ATTACHed database - these are supported by [database backup], but not by simple file copying.

sisusimple - 26/10/2011: The above backup code snippet was developed before the sqlite backup interface/api was developed. Both can still be used. The advantage of the backup interface is that the source db needs not to be locked for the complete time of the backup. See also [11] .

Here is a work-around to abort a sqlite-operation:

 sqlite3 db $dbFileName
 # process events during query
 db progress 100 {update}

 # this forces a "callback requested query abort"-error to be generated if button is pressed while query runs
 button .cancelBtn -text "cancel query" -command {db progress 100 {set notExistingVar 1}}
 pack .cancelBtn

 # start the query
 db eval $veryLongRunningQuery

If you catch the "callback ..."-error you can return an empty string or handle it in another way. Don't forget to call

 db progress 100 {update}

once more, after aborting, otherwise all later queries will abort as well.

The above is even easier using the new "interrupt" method to the SQLite database object. See http://www.sqlite.org/cvstrac/tktview?tn=1889 for additional information.

JOB Days between dates code snipped. How many days have been passed by, since ... ? Type the following:

SELECT julianday ( current_date ) - julianday( DATE('2011-09-29'));

Note: In sqlite is not necessary to specify "select ... from dual".

Discussions edit

Tcl variables as value-lists

AMG: Feature request! I'd like to use lists stored in Tcl variables as SQL value-lists. Of course there needs to be a way to differentiate between the Tcl variable expanding to a single value versus a list of values, so I will borrow {*}, but by no means am I married to this particular notation! Here are some examples of what I'd like to do:

 # Define data.
 db eval {create table rolodex (name, number)}
 set insert {{"Andy Goth"  222-333-5555}
             {"Chris Goth" 444-777-5555}
             {"Dick Goth"  999-888-5555}}
 set delete  {"Andy Goth" "Chris Goth"}

 # Insert some rows.
 foreach entry $insert {
    db eval {insert into rolodex values({*}$entry)}
 }

 # Delete some rows.
 db eval {delete from rolodex where name in ({*}$delete)}

Using current SQLite, the above is coded:

 # Insert some rows.
 foreach entry $insert {
    lassign $entry name number
    db eval {insert into rolodex values($name, $number)}
 }

 # Delete some rows.
 foreach name $delete {
    db eval {delete from rolodex where name = $name}
 }

which requires more local variables, more effort on Tcl's part, and more SQLite invocations.

I'm not sure if it's necessary to support combinations like the following:

 # More data.
 set delete2 "Keith Vetter"
 set delete3 {"Larry Virden" "Richard Suchenwirth"}

 # Delete lots of rows.  (Pretend I inserted them earlier.)
 db eval {delete from rolodex where name in ({*}$delete, $delete2, {*}$delete3, "Jean-Claude Wippler")}

AMG, update:

After thinking about it some more, I have decided that it's best not to support these arbitrary expansion combinations. This would needlessly bloat SQLite, as it is reasonable to use Tcl scripting to assemble the list.
If it's not legal to mix expansion-to-multiple-values with anything else, then it's needlessly* verbose to say ({*}$var). Instead just say $var, without parentheses.

* Today's Wiki edit is brought to you by the word "needlessly" and the number pi!

Here's an example showing (1) and (2) above:

 set victims [list {*}$delete $delete2 {*}$delete3 "Jean-Claude Wippler"]
 db eval {delete from rolodex where name in $victims}

See, no parentheses. If the query had instead been written {delete from rolodex where name in ($victims)}, it likely would have had no effect, as it would be equivalent to {delete from rolodex where name = $victims}.

I can only find two places where SQLite supports value-lists:

The "IN" and "NOT IN" expressions.
The "VALUES" clause of "INSERT" and its alias "REPLACE".

And both places I think would greatly benefit from the ability to expand a Tcl variable to an entire value-list rather than a single value.

ZB 30.09.2008. "After thinking about it some more" I'm pretty sure, that ability to use "value list" during INSERT would be very helpful. Currently one has to "manually" change appropriate place in script, when the amount of values has been changed (how can you dynamically resize "values($name, $number)" in your "Insert some rows" example?).

So, it's not necessary to describe every new - really useful - feature as "bloat".

AMG: It is bloat because it duplicates existing functionality. Tcl excels at list processing, so why not let Tcl do its job? I'm not saying that I have second thoughts about the whole feature request, only about the support for {*}. Instead, all I ask is for the ability to supply an entire value list from a single Tcl object, rather than being required to supply each element of the list from a separate Tcl object. The syntax I propose is to drop the parentheses when the entire value list is to be taken from a single Tcl object.

AMG: I noticed that Dossy requested a feature very similar (possibly identical) to my value-list proposal. Search for dossy@panoptic.com on the SQLite UnsupportedSQL page: [12].

Performance benefit of explicit transactions

AMG: Today I spent some time learning about how SQLite guarantees atomicity in the face of software, filesystem, and power failures [13]. It occurred to me that it takes an awful lot of work to do it right. Next I realized that all this setup and tear-down work has to be done for each transaction, not for each statement.

This explains why a simple database generation utility I wrote is so slow, even though it does nothing more than build a database from scratch to store precomputed data. (It's automatically generated C code, by the way.) It doesn't explicitly use transactions, so each statement is implicitly a separate transaction. After learning about the overhead involved in each transaction, I decided to add BEGIN and COMMIT to my utility. Here's the result:

Database generation time without BEGIN/COMMIT	7.347 seconds
Database generation time with BEGIN/COMMIT	0.070 seconds

Yup, it's now 100 times faster!

The moral of the story: Always use explicit transactions when executing more than one statement at a time, even if your specific application would not benefit from atomicity.

Scott Beasley Transactions are a good rule for all multi-sql runs for most any dB, not just Sqlite. I bulk load 2-6million blobs a night and Transactions are the only way to get it done in a reasonable amount of time. Another performance helper is to make the journal file persistent, for long running insert/update jobs.

Performance differences on various filesystems

2009-June-29: According to some tests (http://www.phoronix.com/scan.php?page=article&item=ext4_btrfs_nilfs2&num=2) there are shocking differences on various filesystems, which should be taken into consideration:

Precompiled tcl-sqlite

AMG: Where are the precompiled binaries for tcl-sqlite? They used to be available on the download page [14], but they're not there now. Anyway, I just compiled an x86 Windows binary of tcl-sqlite 3.7.4; it can be found here: [15]. Here's how you load it:

load sqlite374.dll Sqlite3

DDG: A starkit for version 3.7.4 containing builds for Linux (x86 and x86_64), Windows (x86) and OS-X Darwin (ppc and x86). Also additional math, string and aggregate functions are available: Math: acos, asin, atan, atn2, atan2, acosh, asinh, atanh, difference, degrees, radians, cos, sin, tan, cot, cosh, sinh, tanh, coth, exp, log, log10, power, sign, sqrt, square, ceil, floor, pi. String: replicate, charindex, leftstr, rightstr, ltrim, rtrim, trim, replace, reverse, proper, padl, padr, padc, strfilter. Aggregate: stdev, variance, mode, median, lower_quartile, upper_quartile. [16] I updated the starkit to version 3.7.10 and added as well a few more sqlite functions md5, md5_crypt, format (like tcl's format command), cflormat (clock format), uuid (from the tcllib package uuid). The download page is: https://bitbucket.org/mittelmark/tcl-code/downloads/ the starkit should run on all flavours of OSX- Linux and Win32 from tclkit 8.4, 8.5, 8.6. , A sample session on Windows:

 $ /c/ActiveTcl8.4.13/bin/tclkitsh856.exe
 % source sqlite-3.7.4.kit
 % package require sqlite3
 3.7.4
 % sqlite3 sql sample.db
 {}
 % sql eval "create table test (col1,col2)"
 % sql eval "insert into test (col1,col2) values (2,3)"
 % sql eval "insert into test (col1,col2) values (3,4)"
 % sql eval "insert into test (col1,col2) values (10,1)"
 % sql eval "select median(col1),stdev(col1),sin(col2),upper_quartile(col2) from test"
 3 4.358898943540674 0.8414709848078965 4
 % exit

Column names for an empty table

AMG: I can get the names of the columns using the "*" element of the array produced by [$db eval], but what do I do when the table is empty?

Googie: You can use following before evaluating actual select from table:

 db eval {PRAGMA table_info('table_name');} row {
   # Here use $row(name) as column name
 }

AMG: Thanks, that works great! I was sure that if anyone knew the answer, it would be you. ;^) Now, it would be Really Cool to have an constraint_info() that can also tell us all the constraints on a table, although this could get really fancy and tricky to design properly. But if done right, maybe you can avoid having to parse SQL in SQLiteStudio.

Googie: It's not that simple subject, believe me. But this is not the place to discuss it.

AMG: table_info doesn't work all that well when the table's in an attached database. In 3.7.7.1, it's a syntax error to prefix the table name with the database name in the argument to table_info. If the table name is unique across all attached databases and the main database, just leave the database part out and you're fine. But if there's a conflict, I see no alternative to creating a separate SQLite database connection object with the database containing the table as its main (and only) database.

Googie 2011-08-08: You still can use table_info() pragma, but you need to do it (the pragma, not its argument) on specific database:

 PRAGMA attached_db_name.table_info('table_name');

Pragma always works on its local database, including table name that you pass in argument, so you have to execute whole pragma on desired database. Note, that following diagram [17] of PRAGMA syntax represents exactly what I just described.

Troubleshooting edit

Unable to open database error

Sqlite3 data folder and database must be rw accessible to the web server user. Sqlite3 may also need to create temporary files in the data folder during transactions.

The following works on a Slackware 10.2 install.

Look in your web server's config files (/etc/apache/httpd.conf) for the default user/group.
Make both the Sqlite3 folder and database file rw for this user.
Better yet, move the Sqlite3 database file to its own folder in the htdocs subtree before you adjust the ownership.

Note that the sqlite data file must also be on a real file system. Thus, if your starkit/starpack application needs to use sqlite, the database is going to have to be copied out to a real filesystem before use. If the database also needs to be written, then it is going to be tricky, because I seem to recall that on some (most? all?) systems, the starpack that is running can't be modified...

Bus Error (coredump)

I have this script on a SPARC Solaris 9 system.

package require sqlite3

sqlite3 db1 /tmp/testdb
db1 eval {CREATE TABLE geom1(rect_id int, x0 real, y0 real, x1 real, y1 real)}

db1 eval {INSERT INTO geom1 VALUES(1, 0.0, 0.0, 20.1, 3.1415)}
db1 eval {INSERT INTO geom1 VALUES(2, -10.0, -20.0, 200.123, -0.1234)}
db1 eval {INSERT INTO geom1 VALUES(3, -100.0, -200.0, 300.0, 400.0)}

set x [db1 eval {SELECT * from geom1 ORDER BY rect_id}]

puts $x

db1 close

When I attempt to run it with ActiveTcl 8.5.4, I get a Bus Error(coredump) when attempting to perform the CREATE TABLE.

Does anyone know what might cause this sort of problem?

LV While I don't know what causes this problem, if I download the sqlite3 source code, build and install it on my machine, then use the version I build and install, the script in question runs to completion. When I try either ActiveTcl 8.4.19 or 8.5.4 I get the bus error. After exchanging emails with ActiveState support staff, they provided a fresh build for activetcl 8.4.19 and sqlite3. With that, the core dump did not occur. If you encounter a similar issue using ActiveTcl, submit a bug report providing details about your platform and the version of ActiveTcl you are using with a small coherent example that demonstrates the problem.

DKF: Also note that this is the wrong place to report bugs in sqlite or ActiveState's build of it since there's no guarantee that anyone who can fix things will watch this page.

LV Certainly I agree. I was asking a question here first because I thought the problem might have been my usage of sqlite3. After I found more information that confirmed that my usage wasn't wrong, I submitted the bug to ActiveState and worked out a resolution with them.

[$db incrblob] and asynchronous [chan copy]

AMG: I can't seem to get asynchronous [chan copy] to work with [$db incrblob]. Synchronous works just fine, but when I do asynchronous, the copy never starts. I'm copying from the incrblob to a network socket. (Someday I might also copy from a network socket to the incrblob, but this is doubtful.) I tried an asynchronous copy from the incrblob to a local file, and that hung too. No, I'm not forgetting to enter the event loop. ;^) The copy code I've written works fine when the source channel is an on-disk file, just not when it's an incrblob. Is this an SQLite bug, a design limitation, or a problem in my code?

AMG: At the Eighteenth Annual Tcl/Tk Conference (2011), AK, DRH, and I had a nice discussion concerning this issue, and we identified a potential fix. AK's memchan provides an example of how to support asynchronous [chan copy].

SQLite, Web SQL, and IndexedDB

LV A news article from CNET today (13 Jan 2011) [18] has a brief mention of sqlite as an underlying technology related to Web SQL, and which has now been rejected in favor of IndexedDB.

Minor edit above, and the following, from EMJ.

Someone from Microsoft describes IndexedDB (at [19]):

For database people, this is basically an ISAM API with Javascript objects as the record format. You can create indexes to speed up lookups or scans in particular orders. Other than that there is no schema (any clonable Javascript object will do) and no query language.

So not a database at all then, more a way for people to write a lot of rubbish software. Another case of people with limited experience enthusiastically solving the wrong problem. (Apologies for rant.)

LV I was a bit puzzled by the whole thing - unless IndexedDB has multiple implementations, then it is no better than Web SQL. It looks, to me, like a case of NIH in action.

CliC Neither Microsoft nor Mozilla "invented" it, according to the CNET article, but rather someone from Oracle (maybe formerly from Sun?). They talk about using it to store gobs of data for working offline, a la Google Gears, so I'm guessing they didn't feel need the data guarantees, and did not want the structure, either, of a real SQL database. (They also mention that Google will replace Gears with IndexedDB in some future Chrome release.)

EMJ Glad you put "invented" in quotes. It doesn't matter who they are, they haven't invented anything. They've just designed an API to tie Javascript to a fairly low-level storage mechanism that is not a new concept at all (reminds me of Btrieve (1987)). And they do have transactions, but without any structure at all it is not a database (BTW, SQL has nothing to do with whether or not something is a database).

Prepared statement caching

AMG: I had always assumed SQLite cached prepared statements inside a custom Tcl_Obj type. However, is actually not the case, at least not in 3.7.7.1. tclsqlite.c instead maintains its own prepared statement cache mapping from the text of the SQL query to the prepared statement object. This design approach has some interesting properties:

+: Executing an SQL query won't change the internal representation of a Tcl_Obj, so no other special intrep will be lost. (However, I can't imagine an SQL query being anything other than plain text.)
+: Prepared statement cache lookups aren't hindered by shimmering. As long as the string representation remains consistent, the prepared statement will be found.
+: The cache will still work even if the statement is dynamically regenerated, if it winds up having the same string representation each time. I guess you could call this immunity to meta-shimmering.
-: The size of the prepared statement cache is independent of the number of extant Tcl_Objs containing (textual) SQL statements. If the program cycles through executing (at least) n+1 queries when the size of the cache is only n, the queries will get recompiled every time. See [20] for more information on the cache size, which defaults to 10.
-: Prepared statements are not automatically flushed from the cache when the Tcl_Objs they were created from are deleted. This could be a plus, if the program depends on dynamic regeneration of statements.

Transactions trying to nest themself

Googie - 30 Oct 2011 - I have very weird problem in my application. SQLite reports error on executing "BEGIN" statement, because it says I'm trying to nest transaction (execute "BEGIN" inside of previous "BEGIN"). The exact SQLite error message is: "cannot start a transaction within a transaction".

Well, I added debug info to application and here's a trace of BEGIN/COMMIT/ROLLBACK statements executed:

 BEGIN [14:20:45.834 26.10.2011]
 BEGIN succeed [14:20:45.834 26.10.2011]
 COMMIT [14:20:45.834 26.10.2011]
 COMMIT succeed [14:20:46.832 26.10.2011]
 BEGIN [14:21:33.414 26.10.2011]
 BEGIN failed [14:21:33.414 26.10.2011]

This trace is done with my wrapper around [db eval], and here's a trace made with [db trace], so - as I believe - there's no way to skip anything, it logs everything that is executed on database connection, so here is is:

SQLite trace:

 BEGIN TRANSACTION
 COMMIT TRANSACTION
 BEGIN TRANSACTION

The [db errorcode] returns 1, so this is "SQL error or missing database".

It cannot be missing database, because this error happens just after other SQL statement is executed on that database - 2 simple code lines earlier. Also the error is always raised by exactly the same "BEGIN" execution, but there are plenty other places with "BEGIN" and only this one causes problem.

SQL error doesn't seem to be an answer as well. I receive bug report like these twice a week. This must be something obvious :(

I run out of ideas. How can it happen?

Note, that I cannot reproduce it. These all are reports from end-users.