On 4/18/2014 12:29 AM, Max Vlasov wrote:
The problem was with my program that automatically converts xml data
into an sqilte table. It looks for an attribute and appends a column
if it does not exists, but stating no particular type. All values were
appended with sqlite_bind_text. Everything was fine, but an index
created after this on a ParentId field that was in real life integer
or null, actually appeared text-based. Sure, typeof all inserted
fields appeared to be text.
So it seems like if general queries allow affinity automatical
selection while bind api does not have the corresponent function. I
know that I can analize incoming data myself, but since general
queries use a similar function probably, making some kind of
sqlite_bind_auto should be no big deal. But probably this decision was
deliberate.
This was done for C compatibility. A C++ wrapper for SQLite can have an
sqlite_bind_auto() function which is overloaded for a variety of types,
but it would in reality be multiple functions that have the same name
but otherwise different type signatures. C doesn't mangle function
symbol names according to argument types, so each function must have a
distinct name.
You're free to write an sqlite_bind_auto() function (use whatever name
you choose) that takes a text argument but tries to coerce the data to
integer or real if it thinks it can do so reversibly. It's not enough
to call strtod() or whatever on the argument and check for success; you
have to make sure the input is that number expressed in canonical form.
There are many potential gotchas here. For instance, consider entering
telephone numbers. You may have a database which has entries like:
(800) 123-4567
But also has:
8001234567
These should both be represented as text even though the latter appears
to be an integer (which, by the way, is larger than the 32-bit signed
maximum integer, so don't go putting it in an int).
And why should they both be text? Because that's in accordance with the
database schema.
Now we're getting to the real reason SQLite doesn't have this auto
function you're asking about. That is: SQLite expects the programmer to
know the schema and to embed it in the program's structure. Providing
an automatic function means the programmer gives some of that control
and responsibility back to SQLite, and SQLite may well do it incorrectly
or suboptimally. Better not to have a feature that breeds bugs and
solves a non-problem.
What you're doing is atypical usage. You don't have a prearranged
schema, you're trying to detect it from your incoming data. If you're
going to take on that responsibility, you have to not only figure out
your tables and columns, but also their types. And if you don't want to
do that latter bit of work, you have to accept that SQLite will insert
everything as text.
And what's the problem with that, anyway? Sure, it may take a bit more
space on disk, but that's what XML was doing anyway. Sure, it may give
"incorrect" typeof(), but does your database application really need
that? At some point, something must know and require a particular
schema, otherwise it can't truly use the data, only pass it along, maybe
converting along the way. That end user will *expect* an integer here
and a datetime there, and SQLite will do the conversions on demand.
Read up on duck typing sometime.
--
Andy Goth | <andrew.m.goth/at/gmail/dot/com>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
