Re: Web Storage & SQL

Giovanni Campagna wrote:
> Yes. With extensions I mean writing "start transaction" instead of
> "begin", using the backquotes instead of double quotes for
> identifires, using INET and spatial data types, using table options...

OK.

>>  This is
>> not the case.  A query written based on that ISO standard has different
>> behavior in different databases, in many cases.
> 
> This is obviously an implementation bug, and should be corrected in
> the DBMS, not in the browser or in the web page.

Note that this is certainly the behavior of SQLite.  In particular, it 
does not enforce strong typing on columns.  This is not considered a bug 
by the database author, nor are there plans to change this behavior.[1]

Whether this is a bug per the ISO standard I can't say; I'm not quite 
willing to pay several hundred dollars for an answer to this question.

Note that it's not just a matter of enforcing types.  Comparing the web 
page I cite above to the page that describes comparison operators for 
MySQL, say, [2] we see that for SQLite:

   When two TEXT values are compared, the C library function memcmp() is
   usually used to determine the result. However this can be overridden,
   as described under 'User-defined collation Sequences' below.

whereas for MySQL:

    By default, string comparisons are not case sensitive and use the
    current character set. The default is latin1 (cp1252 West European),
    which also works well for English.

So comparing the string "a" to the string "A" using the obvious "a" == 
"A" syntax will give different results in the two databases.  Which one 
(if either) is correct or incorrect per the ISO standard in question?

> We have tutorials and guides for that

Writing a tutorial that describes "what queries are safe to use" would 
basically be equivalent to defining a subset of SQL.  Either this 
tutorial describes a smaller subset than what's actually safe (in which 
case, why not just restrict to this subset?) or it defines the full set 
of things in the ISO spec (and then is pretty clearly a copyright 
violation if published).

>> You could also require UAs to implement queries exactly per this standard.
>>  Vladimir mentioned as a possible option in his original post.  This is
>> definitely the way forward (with either this SQL definition or some subset
>> thereof)
> 
> But some extensions are useful (like spatial data types or
> non-transactional database engines): I don't see why we should not
> allow them, in a vendor (browser or dbms) specific form.

Because that leads to non-interoperable behavior and pages being tested 
in one browser breaking in another one.  Unless you propose that the 
extensions be very clearly vendor-specific (e.g. the way CSS works). 
I'm not sure how one would make the == operator vendor-specific, which 
is clearly needed per the above.

>> Use of the existing standard by authors doesn't give interoperability.
> 
> Again, this is an implementation bug, not something that should affect authors

Can you please cite the relevant part of the ISO specification that 
forbids the SQLite typing behavior?

> Unfortunately, these are the problems of ISO standards. We cannot change that.

While true, we could refuse to use such a standard as the basis for an 
open platform.  I'm not saying we _should_ refuse to do so, mind you, 
just that this is an important factor to keep in mind when deciding 
whether to use it.

-Boris

[1] http://www.sqlite.org/datatype3.html
[2] http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html

Received on Thursday, 9 April 2009 17:55:39 UTC