[SQLite] is an [embedded], serverless [SQL] [database] engine that has a long
and intimate relationship to [Tcl]. In fact, the author of SQLite says that
SQLite is really a TCL [extension] that broke loose and drifted out into the
larger world of open-source software.
Of particular interest in the Tcl to SQLite interface is the way that
TCL variables can be inserted into SQL statements using Tcl-like syntax.
Suppose you have a TCL variable named "$bigstring" that you want to
insert into an SQL table. You can say this:
======
db eval {INSERT INTO t1 VALUES($bigstring)}
======
SQLite sees the $bigstring in the middle of the INSERT statement (notice
that the entire INSERT statement is enclosed in {...} so that TCL does
no variable substitution itself) then asks TCL for the value of the
$bigstring variable and inserts that value directly. This is much easier
and faster than trying to insert the text of $bigstring directly into
in the INSERT statement:
======
db eval "INSERT INTO t1 VALUES([string map {' ''} $bigstring])"
======
Is there any doubt that the first method is easier to read and less
prone to programming errors? And because it does not make unnecessary
copies of the content of $bigstring, the first method is also much faster.
The problem is that in order to pull off this feat of magic, the TCL interface
to SQLite has to do something that is considered bad style in the TCL world:
SQLite checks to see the current representation of the value in $bigstring,
whether or not it is a string, integer, wide-int, floating point value,
or "bytearray", and it binds different datatypes to SQLite depending on what
it sees.
The problem boils down to this: TCL has only a single datatype which is "string".
The dual-representation mechanism in TCL is a behind-the-scenes cache that the
programmer is never suppose to know about and whose only purpose is to make
things run faster. But SQLite, on the other hand, does have a programmer-visible
concept of datatypes.
SQLite understands strings, BLOBs, integers, floating-point values, and NULL.
And so there is a bit of an "impedance mismatch" in the type systems of
SQLite and TCL. The interface mostly covers up this mismatch so that often
the programmer is never aware of it. But sometimes the impedance mismatch
doe surface and cause problems. And so a programmer that uses SQLite and
TCL frequently and for large projects should probably be aware of the issue.
When transfering values from TCL into SQLite using the $-substitution mechanism
shown in the first example above, SQLite looks at the alternative (non-string)
representation of the TCL variable (if it exists) and uses that alternative
representation to help decide what datatype to insert into SQLite.
1. If the alternative representation is "boolean" or "int" or "wideInt" then the value is inserted into SQLite as a 64-bit integer
2. If the alternative representation is "double" then the value is inserted into SQLite as a double.
3. If the alternative representation is "bytearray" and there is no string representation, or if an "@" character is used instead of the '$' to introduce the variable name, then the value inserted into SQLite as a BLOB.
4. If the variable does not exist, a NULL is inserted
5. Otherwise, the string representation is inserted as a string.
----
[NEM] ''25 Sept 07'': I wasn't aware of this behaviour, thanks for pointing it
out. Surely SQLite knows what types are ''expected'' for each attribute of a
relation? It could then use this information and attempt to coerce the Tcl value
to an appropriate type that way, rather than attempting to guess the type based
on what is passed. I.e., if a relation is declared as accepting an "id" attribute
of type "integer" then it could call Tcl_GetIntFromObj on the corresponding value
passed in.
<
>
[DRH] Replies: Your assumption that SQLite knows what types
to expect for bound parameters is incorrect. SQLite uses dynamic typing.
Just because a relation is declared "int" does not prevent it from storing a
string. SQLite will try to convert from a string to an int if it can, but will
store a string rather than lose information. This is a feature of SQLite, not
a bug.
<> Database