Re: [sqlite] Why sqlite has no sqlite_bind_auto or similary named function

[Max Vlasov]

On Fri, Apr 18, 2014 at 8:08 PM, Andy Goth  wrote:
> On 4/18/2014 12:29 AM, Max Vlasov wrote:
>>
>> 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.

I meant something different. A function accepting a text value while
sqlite doing affinity conversion according to how the value "looks".
Actually it seems that all the problems I faced was related to the
type being typeless in declaration. For integer-declared fields sqlite
correctly converts them to the integer affinity even with
sqlite3_bind_text function. So it makes no sense to force other kind
of detection if the type is declared. But if I stay with typeless
fields, I will definitely will have problems with indexes on them,
because seems like the query planner relies on declared types and
doesn't care how many rows have particular affinity. So it looks like
I should prescan some rows for type detection if I want an automatic
conversion not knowing type info in advance

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why sqlite has no sqlite_bind_auto or similary named function

[Andy Goth]

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 | 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Why sqlite has no sqlite_bind_auto or similary named function

[Max Vlasov]

Hi,

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.

On the other side for a typeless table
  CREATE TABLE t (value)

Query
  insert into t (value) values (123)
  insert into t (value) values ('text')
  insert into t (value) values (34.45)

finally makes
  select typeof (value) from t

returns
  integer
  text
  real

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.

Max
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users