I coworker pointed me to this thread.
and why it isn't SQL.

>It isn't SQL simply because SQL won't let you insert text
>into a numeric field.
Yup, I have to agree that's pretty crappy. (Makes mental note to limit
use of SQLite).
Ever heard of check constraints? That's another feature of this
crappy, non-SQL database. They are one of at least three different
approaches you can take to implement strict typing using SQLite's SQL
and C library facilities.
mike@owens:/tmp# ./sqlite3
SQLite version 3.3.7
Enter ".help" for instructions
sqlitecreate table stupid(x INTEGER check(typeof(x)=='integer'));
sqlitecreate table little(x REAL check(typeof(x)=='real'));
sqlitecreate table database(x INTEGER check(typeof(x)!='text'));
sqlite-- insert text
sqliteinsert into stupid values('SQLite sucks!');
SQL error: constraint failed
sqliteinsert into stupid values("1");
sqlite
sqlite-- insert text
sqliteinsert into little values('This isn''t SQL!');
SQL error: constraint failed
sqliteinsert into little values(1.0);
sqliteabout that
sqlite-- insert text
sqliteinsert into database values('Dynamic typing just doesn''t work!');
SQL error: constraint failed
sqliteinsert into database values(x'FFFF');
sqlite-- something's going on here
sqlite-- so what do we have in the tables?
sqlite.h on
sqlite.m col
sqliteselect x, typeof(x) from stupid;
x typeof(x)
1 integer
sqlite-- integers only
sqliteselect x, typeof(x) from little;
x typeof(x)
1.0 real
sqlite-- reals only
sqliteselect x, typeof(x) from database;
x typeof(x)
blob
sqlite-- anything but text
That darned dynamic typing. It works like almost any other database if
you want it to. (Makes mental note to spend more time reading SQLite
documentation before bashing SQLite.)

9/11/06, Mike <mikeowens (AT) gmail (DOT) comwrote:
I coworker pointed me to this thread.

Joy for us.

< snipped good information >

In all seriousness, the information you present here is great, and
much appreciated. Your sarcastic, condescending tone kind of gets in
the way of the message, though.

And here is the crux of the issue. Sqlite doesn't follow the standard
for sql. The name certainly implies that it would. This doesn't make
it a crappy product, but it is certainly misleading. I must admit,
that after 10 years of oracle experience, I don't necessarily read all
of the documentation for a new dbms I'm trying out, particularly a
light weigth variety. I get in, and try things. Sometimes I get
bitten, but I learn better that way. I would expect, however, for each
product with 'sql' in the name, to, at least by default, adhere to the
standard. And expectations are what set this conversation up.

But, I don't expect that anything productive will come from the rest
of this thread. Your post had the stink of zealotry all over it, and
we all know what happens when a zealots favorite is questioned.

Again, thanks for the info. It'll serve me well when I'm playing with
sqlite later.

Mike wrote:
I coworker pointed me to this thread.

and why it isn't SQL.

It isn't SQL simply because SQL won't let you insert text
into a numeric field.


>>Yup, I have to agree that's pretty crappy. (Makes mental note to limit
>>use of SQLite).

Ever heard of check constraints? That's another feature of this
crappy, non-SQL database. They are one of at least three different
approaches you can take to implement strict typing using SQLite's SQL
and C library facilities.

, right. So you're trying to defend SQLite (which, by the way, doesn't
need it: it's a perfectly fine tool for limited purposes) by suggesting
that column typing's failure to work isn't a problem because you can
declare column types in check constraints? That seems like a hard
position to defend to me.

You might as well say it's K to sell blunt knives because they can
always be sharpened.

[examples snipped]

That darned dynamic typing. It works like almost any other database if
you want it to. (Makes mental note to spend more time reading SQLite
documentation before bashing SQLite.)

Sure. But if you go back to the start of the thread you'll remember the
P was originally complaining that SQLite was being promoted in the
Python docs as SQL compliant. It clearly isn't if its response to the
insertion of a data value that conflicts with the declared column type
is to store a value whose type is something else. You shouldn't need to
add check constraints to verify that the value stored in an integer
column is actually an integer.

I don't think anyone is trying to suggest that SQLite isn't a prefectly
good tool for many purposes: it's far more sophisticated than bsddb, for
example, and I've used both it and Gadfly (which has similar
deficiencies when compared to strict standards) with complete satisfaction.

So climb down off that high horse :-)

regards
Steve

9/11/06, Marty <stupidityreigns (AT) gmail (DOT) comwrote:

In all seriousness, the information you present here is great, and
much appreciated. Your sarcastic, condescending tone kind of gets in
the way of the message, though.

Sarcastic, perhaps. Condesceding, I think not. It is ridiculous that
people can simply say whatever they want to about software they've
taken little time to learn. I did not see one single post by
mensanator to the SQLite mailing list, or by anyone else on this
thread who criticized SQLite. Rather, it was SQLite is just crappy, or
not a database, or not an SQL database, or other expletives. And while
mensanator had other claims about Python's documentation, this general
frustration then carelessly took a turn to SQLite.

And as for the alleged problems in SQLite, there was (to my
estimation) much less effort expended in finding a solution than there
was in badmouthing an otherwise wonderful piece of software.

If you are too impatient to read the documentation, fine. If you don't
want to consult the experienced people on the SQLite mailing list (who
are glad to help), fine. But DN'T remain willfully ignorant AND blame
SQLite for not working the way your intuition demands. For years I've
watched people badmouth SQLite whose claims are uninformed, unfounded,
or downright unfair. Had a single accuser here posted this alleged
problem with SQLite to the SQLite mailing list, I probably would have
remained silent here (and answered it more politely there).

And here is the crux of the issue. Sqlite doesn't follow the standard
for sql. The name certainly implies that it would. This doesn't make
it a crappy product, but it is certainly misleading.

Newsflash: No database follows the complete SQL standard, not even
By the logic in this thread, there is no such thing as an SQL
database.

I must admit, that after 10 years of oracle experience, I don't necessarily read all
of the documentation for a new dbms I'm trying out, particularly a
light weigth variety. I get in, and try things. Sometimes I get
bitten, but I learn better that way.

I would hope then that when you don't read the documentation, and you
get bitten, you know better than to blame the software.

I would expect, however, for each
product with 'sql' in the name, to, at least by default, adhere to the
standard. And expectations are what set this conversation up.

These expectations are simply unrealistic. If someone is simply too
lazy to read the documentation or use the mailing list, then they can
only blame themselves. And if you say SQLite misrepresents itself,
then what do you say about MySQL, which until version 5 didn't have
views or triggers? In fact, it didn't even have subselects until
version 4. For a period of years, SQLite had more mainstream SQL
features than MySQL. Yet you don't see people going around claiming
that MySQL is not an SQL database -- that it's misrepresenting itself.

So no, SQLite most certainly does not misrepresent itself. It is an
open source, embedded, relational database that uses SQL as its query
language. Plain and simple. Just because it may not implement part of
the standard you or someone else likes does not strip it being an SQL
database.

But, I don't expect that anything productive will come from the rest
of this thread. Your post had the stink of zealotry all over it, and
we all know what happens when a zealots favorite is questioned.

Expecting people to get the facts before badmouthing something is
hardly zealotry. I am tired of seeing SQLite taking the blame when
certain people choose simply to assume rather than read (or consult
others). They see database-level locking, they ASSUME it's too slow
for any kind of write concurrency applications. They see type
affinity, they ASSUME it's just substandard or useless, and then by
further soritical leaps, discount it as even an SQL database? Rather
than reading, or testing, or asking people who know, they get
frustrated and go straight to blaming it. That is completely unfair.

The purpose of this post is to demonstrate that:

1. SQLite was not at fault here, nor insufficient for the purpose stated.

2. There are people who can easily provide the very help you need,
provided you ask them, and try to keep your derogatory comments to a
minimum.

3. There is no substitute for reading the documentation and learning
the product. This is doubly true of relational databases. I'd love to
see someone who uses MSSQL or try to install and use PostgreSQL
or Firebird with nothing but instinct. I currently use three of these
databases in production, and I couldn't survive without reading
documentation.

Again, thanks for the info. It'll serve me well when I'm playing with
sqlite later.

You are more than welcome. Glad to help.

9/11/06, Steve Holden <steve (AT) holdenweb (DOT) comwrote:

Sure. But if you go back to the start of the thread you'll remember the
P was originally complaining that SQLite was being promoted in the
Python docs as SQL compliant.

Define "SQL compliant." That's about as technically precise as saying
that something tastes like chicken.

Furthermore, I'm not responding to Python's representation of one
thing or another. I am responding to some of the ridiculous and unfair
criticisms directed at SQLite. Whatever Python did or didn't do, or
whatever PySQLite does or doesn't do, SQLite doesn't deserve to be
drug through the mud.

You shouldn't need to
add check constraints to verify that the value stored in an integer
column is actually an integer.

You should if your using SQLite, and this is clearly documented:

I don't think anyone is trying to suggest that SQLite isn't a prefectly
good tool for many purposes: it's far more sophisticated than bsddb, for
example, and I've used both it and Gadfly (which has similar
deficiencies when compared to strict standards) with complete satisfaction.

Then what does calling it crappy and goofy suggest?

Mike wrote:
9/11/06, Steve Holden <steve (AT) holdenweb (DOT) comwrote:


>>Sure. But if you go back to the start of the thread you'll remember the
>>P was originally complaining that SQLite was being promoted in the
>>Python docs as SQL compliant.

Define "SQL compliant." That's about as technically precise as saying
that something tastes like chicken.

Furthermore, I'm not responding to Python's representation of one
thing or another. I am responding to some of the ridiculous and unfair
criticisms directed at SQLite. Whatever Python did or didn't do, or
whatever PySQLite does or doesn't do, SQLite doesn't deserve to be
drug through the mud.

Which is precisely why I took pains to acknowledge that there were many
purposes for which SQLite is entirely suitable.

>>You shouldn't need to
>>add check constraints to verify that the value stored in an integer
>>column is actually an integer.

You should if your using SQLite, and this is clearly documented:

Right. In which case, why bother to define the types of the columns in
your table declarations?

>>I don't think anyone is trying to suggest that SQLite isn't a prefectly
>>good tool for many purposes: it's far more sophisticated than bsddb, for
>>example, and I've used both it and Gadfly (which has similar
>>deficiencies when compared to strict standards) with complete satisfaction.

Then what does calling it crappy and goofy suggest?

That colloquial English expression is acceptable on this list.

Strict affinity mode seems to represent a movement towards more rigorous
type checking. So the designers of SQLIte accept that it wasn't perfect.
So what? Please, don't take on so. It's only ones and zeroes.

regards
Steve

Mike wrote:
9/11/06, Steve Holden <steve (AT) holdenweb (DOT) comwrote:

Sure. But if you go back to the start of the thread you'll remember the
P was originally complaining that SQLite was being promoted in the
Python docs as SQL compliant.

Define "SQL compliant." That's about as technically precise as saying
that something tastes like chicken.

Can you run your car on diesel fuel?

Why not?

Because your car's specification says to use gasoline?

If your car has been designed to run on diesel, you shouldn't
be saying it has gasoline engine. Duh.

Furthermore, I'm not responding to Python's representation of one
thing or another. I am responding to some of the ridiculous and unfair
criticisms directed at SQLite. Whatever Python did or didn't do, or
whatever PySQLite does or doesn't do, SQLite doesn't deserve to be
drug through the mud.

Sure it does.

>From SQLite Homepage
documentation
Available Documentation
Distinctive Features

<quote *emphasis added*>
This page highlights some of the characteristics of SQLite
that are unusual and which make SQLite different from many
other SQL database engines.

Manifest typing

Most SQL database engines use static typing. A datatype is
associated with each column in a table and only values of
that particular datatype are allowed to be stored in that
column. SQLite relaxes this restriction by using manifest
typing.

In manifest typing, the datatype is a property of the
value itself, not of the column in which the value is
stored. SQLite thus allows the user to store any value
of any datatype into any column regardless of the declared
type of that column. (There are some exceptions to this
rule: An INTEGER PRIMARY KEY column may only store
integers. And SQLite attempts to coerce values into the
declared datatype of the column when it can.)

* The SQL language specification calls for static typing. *

So some people feel that the use of manifest typing is a
bug in SQLite. But the authors of SQLite feel very
strongly that this is a feature.

* The authors argue that static typing is a bug in the *
* SQL specification that SQLite has fixed in a backwards *
* compatible way. *

</quote>

A spcification cannot be a bug (unless it is inconsistent
with other specifications). An implementation can be,
but a specification cannot.

You shouldn't need to
add check constraints to verify that the value stored in an integer
column is actually an integer.

You should if your using SQLite, and this is clearly documented:

I don't think anyone is trying to suggest that SQLite isn't a prefectly
good tool for many purposes: it's far more sophisticated than bsddb, for
example, and I've used both it and Gadfly (which has similar
deficiencies when compared to strict standards) with complete satisfaction.

Then what does calling it crappy and goofy suggest?

It suggests that the SQLite author is a crackpot.

11 Sep 2006 18:23:50 -0700, mensanator (AT) aol (DOT) com <mensanator (AT) aol (DOT) comwrote:

Can you run your car on diesel fuel?

Why not?

Because your car's specification says to use gasoline?

If your car has been designed to run on diesel, you shouldn't
be saying it has gasoline engine. Duh.

No but you can still call it a car with an engine, just as SQLite is a
SQL database, with an SQL engine.

Furthermore, I'm not responding to Python's representation of one
thing or another. I am responding to some of the ridiculous and unfair
criticisms directed at SQLite. Whatever Python did or didn't do, or
whatever PySQLite does or doesn't do, SQLite doesn't deserve to be
drug through the mud.

Sure it does.

No it doesn't. If you don't like SQLite's design decisions, write your
own embedded relational database, and stop yapping about something you
didn't lift a finger to create, but are clearly trying to benefit
from.

>From SQLite Homepage
documentation
Available Documentation
Distinctive Features

In manifest typing, the datatype is a property of the
value itself, not of the column in which the value is
stored. SQLite thus allows the user to store any value
of any datatype into any column regardless of the declared
type of that column. (There are some exceptions to this
rule: An INTEGER PRIMARY KEY column may only store
integers. And SQLite attempts to coerce values into the
declared datatype of the column when it can.)

* The SQL language specification calls for static typing. *

It calls for other things that many databases don't implement or
altogether violate as well, so what? Show me how both MS SQL's T-SQL
and 's PL/SQL procedure languages are so standards compliant
that you can use the same procedure code in both databases. You can't
-- precisely because they ignore or outright violate parts of the
standard as well. What's your position on that? Do some Googling and
you can easily find 18 ways that 's PL/SQL deviates from the
standard. And T-SQL is plainly nowhere close.

A spcification cannot be a bug (unless it is inconsistent
with other specifications). An implementation can be,
but a specification cannot.

Then every database implementation, when held up to the SQL standard,
is equally guilty of being buggy and/or incomplete. Name one database
that fully conforms to SQL 2003. Can't name one? Then why single out
SQLite?

Then what does calling it crappy and goofy suggest?

It suggests that the SQLite author is a crackpot.

Crackpot? And now we get to why I took the flamebait -- wonderfully
constructive comments such as this.

I know SQLite's author. Besides being a nice and clearly very
intelligent person, he also holds a master's degree in electrical
engineering from Georgia Tech and a PhD in computer science from Duke
University. His "crackpot" software is used by Sun, Apple, Symbian,
Google, AL, Philips, DLink, and I don't know how many other
companies, not to mention countless open source projects such as
Mozilla, PHP, and now Python. But I guess they must all be crackpots
too.

It's clear. You're just way too smart for SQLite.

9/11/06, Steve Holden <steve (AT) holdenweb (DOT) comwrote:

Furthermore, I'm not responding to Python's representation of one
thing or another. I am responding to some of the ridiculous and unfair
criticisms directed at SQLite. Whatever Python did or didn't do, or
whatever PySQLite does or doesn't do, SQLite doesn't deserve to be
drug through the mud.

Which is precisely why I took pains to acknowledge that there were many
purposes for which SQLite is entirely suitable.

Which was objective and diplomatic. Thanks.

>>You shouldn't need to
>>add check constraints to verify that the value stored in an integer
>>column is actually an integer.
>
You should if your using SQLite, and this is clearly documented:

Right. In which case, why bother to define the types of the columns in
your table declarations?

Actually there is some use for this, so long as the declared types
correspond to one of SQLite's storage classes -- INTEGER, REAL, TEXT,
BLB, or NNE. When SQLite sees one of these as the declared type, it
will apply some "affinity" rules for updates/inserts which will
attempt to coerce the input value to that type if possible. So while
this does not give you true strict typing, it does provide you with
something helpful if you code your application with this in mind. As
already mentioned, you will need triggers or check constraints if you
need true strict typing.

Then what does calling it crappy and goofy suggest?

That colloquial English expression is acceptable on this list.

And what about some of the other colloquial expressions I saw but didn't repeat?

Strict affinity mode seems to represent a movement towards more rigorous
type checking. So the designers of SQLIte accept that it wasn't perfect.
So what? Please, don't take on so. It's only ones and zeroes.

This is an example of SQLite listening and responding to constructive
feedback. When I first started using SQLite, it didn't have an
autoincrement primary key. I made a case for it, asked nicely, and it
was done in three days. Most people will agree that this approach
tends to work better in most projects, as opposed to calling them
goofy and crappy, or its author a crackpot.

Mike

regards
Steve

Mike wrote:
9/11/06, Steve Holden <steve (AT) holdenweb (DOT) comwrote:

Furthermore, I'm not responding to Python's representation of one
thing or another. I am responding to some of the ridiculous and unfair
criticisms directed at SQLite. Whatever Python did or didn't do, or
whatever PySQLite does or doesn't do, SQLite doesn't deserve to be
drug through the mud.

>>
>>Which is precisely why I took pains to acknowledge that there were many
>>purposes for which SQLite is entirely suitable.

Which was objective and diplomatic. Thanks.

Well known for my even temper and cool objective thinking.
My modesty, too ;-)

You shouldn't need to
add check constraints to verify that the value stored in an integer
column is actually an integer.

You should if your using SQLite, and this is clearly documented:


>>
>>Right. In which case, why bother to define the types of the columns in
>>your table declarations?

Actually there is some use for this, so long as the declared types
correspond to one of SQLite's storage classes -- INTEGER, REAL, TEXT,
BLB, or NNE. When SQLite sees one of these as the declared type, it
will apply some "affinity" rules for updates/inserts which will
attempt to coerce the input value to that type if possible. So while
this does not give you true strict typing, it does provide you with
something helpful if you code your application with this in mind. As
already mentioned, you will need triggers or check constraints if you
need true strict typing.

Then what does calling it crappy and goofy suggest?
>>
>>That colloquial English expression is acceptable on this list.

And what about some of the other colloquial expressions I saw but didn't repeat?

Well, I'm not going to take responsibility for others' immoderate
comments, am I? But remember that this is usenet, where generally you
can count on somewhere between 20% and 80% of what you read being
somewhere between ill-informed and downright wrong. This group seems to
have a better record than most, but that just means less crap, not no
crap

>>Strict affinity mode seems to represent a movement towards more rigorous
>>type checking. So the designers of SQLIte accept that it wasn't perfect.
>>So what? Please, don't take on so. It's only ones and zeroes.

This is an example of SQLite listening and responding to constructive
feedback. When I first started using SQLite, it didn't have an
autoincrement primary key. I made a case for it, asked nicely, and it
was done in three days. Most people will agree that this approach
tends to work better in most projects, as opposed to calling them
goofy and crappy, or its author a crackpot.

Works better for Python too. Anyway, you seem to be feeling better now,
so I presume getting it off your chest helped :-)

SQLite will clearly fare well in the future with a responsive
development team like that.

regards
Steve

Mike wrote:
11 Sep 2006 18:23:50 -0700, mensanator (AT) aol (DOT) com <mensanator (AT) aol (DOT) comwrote:

Can you run your car on diesel fuel?

Why not?

Because your car's specification says to use gasoline?

If your car has been designed to run on diesel, you shouldn't
be saying it has gasoline engine. Duh.

No but you can still call it a car with an engine, just as SQLite is a
SQL database, with an SQL engine.

Seperate the data from the engine and what have you got?
Data with dynamic typing. Data that can't be migrated to
a "real" SQL database because you'll get type mismatches
when strings are inserted into numeric fields. The type affinity
kluge won't help there, will it?

Furthermore, I'm not responding to Python's representation of one
thing or another. I am responding to some of the ridiculous and unfair
criticisms directed at SQLite. Whatever Python did or didn't do, or
whatever PySQLite does or doesn't do, SQLite doesn't deserve to be
drug through the mud.

Sure it does.

No it doesn't. If you don't like SQLite's design decisions, write your
own embedded relational database, and stop yapping about something you
didn't lift a finger to create,

It's not the job of the System Test Engineer to design things.
It's his job to find fault with everything. I just happen to be very
good at finding faults with things.

But no one appreciates my finding those faults.

The Emperor is naked and I'm the only one who can see it.

but are clearly trying to benefit
from.
>
>From SQLite Homepage
documentation
Available Documentation
Distinctive Features

In manifest typing, the datatype is a property of the
value itself, not of the column in which the value is
stored. SQLite thus allows the user to store any value
of any datatype into any column regardless of the declared
type of that column. (There are some exceptions to this
rule: An INTEGER PRIMARY KEY column may only store
integers. And SQLite attempts to coerce values into the
declared datatype of the column when it can.)

* The SQL language specification calls for static typing. *

It calls for other things that many databases don't implement or
altogether violate as well, so what? Show me how both MS SQL's T-SQL
and 's PL/SQL procedure languages are so standards compliant
that you can use the same procedure code in both databases. You can't
-- precisely because they ignore or outright violate parts of the
standard as well. What's your position on that? Do some Googling and
you can easily find 18 ways that 's PL/SQL deviates from the
standard. And T-SQL is plainly nowhere close.

And how many of those systems use dynamic typing?

A spcification cannot be a bug (unless it is inconsistent
with other specifications). An implementation can be,
but a specification cannot.

Then every database implementation, when held up to the SQL standard,
is equally guilty of being buggy and/or incomplete. Name one database
that fully conforms to SQL 2003. Can't name one? Then why single out
SQLite?

Name one where the documentation claims the SQL Language
Specification is a bug.

Then what does calling it crappy and goofy suggest?

It suggests that the SQLite author is a crackpot.

Crackpot? And now we get to why I took the flamebait -- wonderfully
constructive comments such as this.

I know SQLite's author. Besides being a nice and clearly very
intelligent person, he also holds a master's degree in electrical
engineering from Georgia Tech and a PhD in computer science from Duke
University. His "crackpot" software is used by Sun, Apple, Symbian,
Google, AL, Philips, DLink, and I don't know how many other
companies, not to mention countless open source projects such as
Mozilla, PHP, and now Python. But I guess they must all be crackpots
too.

And a lot of people go to chiropractors. And chiropractors are
nice intelligent people with degrees. And the therapy provided
does good.

Nevertheless, the theory on which it's based is quackery.

It's clear. You're just way too smart for SQLite.

Did you see my solution to Rick Shepard's problem in the
thread "Parsing String, Dictionary Lookups, Writing to
Database Table"?

Mike wrote:
And if you say SQLite misrepresents itself,
then what do you say about MySQL, which until version 5 didn't have
views or triggers? In fact, it didn't even have subselects until
version 4. For a period of years, SQLite had more mainstream SQL
features than MySQL. Yet you don't see people going around claiming
that MySQL is not an SQL database -- that it's misrepresenting itself.

Just to be fair

You do hear many people claiming exactly that, and the primary
complaint is often exactly the same one that's being levelled against
sqlite here (it's incredibly lax with types and does sometimes
mystifying conversions rather than pointing out programmer errors
yes that's intentionally loaded language that I don't necessarily agree
with, it's a common argument though.). The lack of subselects was also
a major sticking point for a lot of people, as are other major missing
SQL features.

Not having used sqlite I can't comment on it in particular.

! Sorry for the top-post!

Marty wrote:
9/11/06, Mike <mikeowens (AT) gmail (DOT) comwrote:
>I coworker pointed me to this thread.

Joy for us.

>>
>< snipped good information >

In all seriousness, the information you present here is great, and
much appreciated. Your sarcastic, condescending tone kind of gets in
the way of the message, though.

What about jokes on "waterheadretard" then ?

"Mike " <mikeowens (AT) gmail (DOT) comwrites:
No it doesn't. If you don't like SQLite's design decisions, write your
own embedded relational database, and stop yapping about something you
didn't lift a finger to create, but are clearly trying to benefit
from.

That's silly. The sqlite developers are making all kinds of claims on
their web site, in order to attract users and build mindshare and gain
the benefits thereof. If the claims aren't valid, it's completely
appropriate for others to call attention to it, whether or not they
feel like doing anything to fix it.

It's just like anything else. If you think your Congressperson is
misleading the public about something or pursuing a bad policy, you
should speak out about it. That doesn't mean you need to run for
Congress yourself.

Mike wrote:

Crackpot? And now we get to why I took the flamebait -- wonderfully
constructive comments such as this.

I know SQLite's author. Besides being a nice and clearly very
intelligent person, he also holds a master's degree in electrical
engineering from Georgia Tech and a PhD in computer science from Duke
University. His "crackpot" software is used by Sun, Apple, Symbian,
Google, AL, Philips, DLink, and I don't know how many other
companies, not to mention countless open source projects such as
Mozilla, PHP, and now Python.

but is he a member of Mensa?

</F>

Steve Holden wrote:

Sure. But if you go back to the start of the thread you'll remember the
P was originally complaining that SQLite was being promoted in the
Python docs as SQL compliant. It clearly isn't if its response to the
insertion of a data value that conflicts with the declared column type
is to store a value whose type is something else.

the standard actually says "If the value of any input parameter provided
by the SQL-agent falls outside the set of allowed values of the data
type of the parameter // the effect is implementation-defined" so
that's perfectly SQL92 compliant.

in fact, the phrases "is implementation-defined" and "is implementation-
dependent" occurs hundreds of times in the SQL92 standard.

it's far from obvious to me that SQL92 would rule out storing everything
as strings ("The physical representation of a value is implementation-
dependent.") and leaving it to the language binding to map things back
to the host language types in whatever way it wants ("Each host language
has its own data types, which are separate and distinct from SQL data
types", the above quotation, and so on).

looks like the real problem here is that some people think that
"implementation-defined" means "works as it did in that other database
I'm using", and not "specified by the implementor for each particular
SQL-implementation". that's not how standards work; if something's
explicitly left undefined, it's not something you can rely on.

</F>

Paul Rubin wrote:
"Mike " <mikeowens (AT) gmail (DOT) comwrites:

>>No it doesn't. If you don't like SQLite's design decisions, write your
>>own embedded relational database, and stop yapping about something you
>>didn't lift a finger to create, but are clearly trying to benefit
>>from.

That's silly. The sqlite developers are making all kinds of claims on
their web site, in order to attract users and build mindshare and gain
the benefits thereof. If the claims aren't valid, it's completely
appropriate for others to call attention to it, whether or not they
feel like doing anything to fix it.

It's just like anything else. If you think your Congressperson is
misleading the public about something or pursuing a bad policy, you
should speak out about it. That doesn't mean you need to run for
Congress yourself.

Though it might improve the country's politics if wanting to be a
Congressperson was an absolute disqualification from the job.

regards
Steve

12 Sep 2006 00:15:41 -0700, sjdevnull (AT) yahoo (DOT) com <sjdevnull (AT) yahoo (DOT) comwrote:

Just to be fair

You do hear many people claiming exactly that, and the primary
complaint is often exactly the same one that's being levelled against
sqlite here (it's incredibly lax with types and does sometimes
mystifying conversions rather than pointing out programmer errors
yes that's intentionally loaded language that I don't necessarily agree
with, it's a common argument though.).

True enough.

Yet not a single conversion is undocumented in SQLite, nor is its type
affinity. It works exactly as advertised. And MySQL's chief type
conversion gotchas -- primarily dates/times I think -- were also
clearly addressed in the documentation, and where changed in later
versions of MySQL. Then there is, as I think another person pointed
out on this thread, 's silent conversion of empty strings to
NULLs in VARCHAR fields -- talk about mystifying. What about 's
native date format, which also has no relation to the standard? I have
little experience with , but from what I can tell, to get it to
display dates in standard (IS) format, you have to set the
NLS_DATE_FRMAT at the session or database level, which requires
either additional SQL commands, or administrative intervention. More
so-called non-SQL compliant behavior, although hardly a surprise (or
even a problem) to someone who is experienced with

The bottom line: to use *any* database effectively, big or small, one
has to read its documentation, not the SQL standard.

Mike wrote:

The bottom line: to use *any* database effectively, big or small, one
has to read its documentation, not the SQL standard.

note that the SQL standard tells you to read the documentation for the
database you're using, in at least 149 places (*).

</F>

*) See Annex B. I only have a draft edition; the number of items in the
final version may differ.

11 Sep 2006 21:35:28 -0700, mensanator (AT) aol (DOT) com <mensanator (AT) aol (DOT) comwrote:
Mike wrote:
11 Sep 2006 18:23:50 -0700, mensanator (AT) aol (DOT) com <mensanator (AT) aol (DOT) comwrote:

Can you run your car on diesel fuel?

Why not?

Because your car's specification says to use gasoline?

If your car has been designed to run on diesel, you shouldn't
be saying it has gasoline engine. Duh.

No but you can still call it a car with an engine, just as SQLite is a
SQL database, with an SQL engine.

Seperate the data from the engine and what have you got?
Data with dynamic typing. Data that can't be migrated to
a "real" SQL database because you'll get type mismatches
when strings are inserted into numeric fields. The type affinity
kluge won't help there, will it?

Did you even read my original post? did you just completely miss the point?

It's not the job of the System Test Engineer to design things.
It's his job to find fault with everything. I just happen to be very
good at finding faults with things.

And apparently not very good at providing any constructive solutions.

But no one appreciates my finding those faults.

No one appreciates the tone in which you report these alleged faults,
necessarily agrees with the faults that you find, nor elected you
system test engineer of the SQLite project.

It calls for other things that many databases don't implement or
altogether violate as well, so what? Show me how both MS SQL's T-SQL
and 's PL/SQL procedure languages are so standards compliant
that you can use the same procedure code in both databases. You can't
-- precisely because they ignore or outright violate parts of the
standard as well. What's your position on that? Do some Googling and
you can easily find 18 ways that 's PL/SQL deviates from the
standard. And T-SQL is plainly nowhere close.

And how many of those systems use dynamic typing?

And how many conform to the standard?

Name one where the documentation claims the SQL Language
Specification is a bug.

Name one that conforms to the standard.

And a lot of people go to chiropractors. And chiropractors are
nice intelligent people with degrees. And the therapy provided
does good.

Nevertheless, the theory on which it's based is quackery.

To use your specious analogy, it represents another way of doing
things, which you admit yourself works. That's your justification for
calling Richard Hipp a crackpot?

It's clear. You're just way too smart for SQLite.

Did you see my solution to Rick Shepard's problem in the
thread "Parsing String, Dictionary Lookups, Writing to
Database Table"?

The point being? -- you can write Python code and feel entitled to
condescending and rude?

"Mike " <mikeowens (AT) gmail (DOT) comwrites:
It's not the job of the System Test Engineer to design things.
It's his job to find fault with everything. I just happen to be very
good at finding faults with things.

And apparently not very good at providing any constructive solutions.

As he says, it's not his job.

But no one appreciates my finding those faults.

No one appreciates the tone in which you report these alleged faults,

Your tone is not so great either.

necessarily agrees with the faults that you find, nor elected you
system test engineer of the SQLite project.

It's an open source project, as you like to say. Everyone is a test
engineer.

standard as well. What's your position on that? Do some Googling and
you can easily find 18 ways that 's PL/SQL deviates from the
standard. And T-SQL is plainly nowhere close.

And how many of those systems use dynamic typing?

And how many conform to the standard?

How many of those deviations are justified in their documentation by
the responsible parties claiming, in effect, that they're smarter than
the standard's designers?

It seems obvious to me that there should, at minimum, be an option to
turn this particular nonstandard behavior on and off.

Fredrik Lundh wrote:
Steve Holden wrote:

Sure. But if you go back to the start of the thread you'll remember the
P was originally complaining that SQLite was being promoted in the
Python docs as SQL compliant. It clearly isn't if its response to the
insertion of a data value that conflicts with the declared column type
is to store a value whose type is something else.

the standard actually says "If the value of any input parameter provided
by the SQL-agent falls outside the set of allowed values of the data
type of the parameter // the effect is implementation-defined" so
that's perfectly SQL92 compliant.

To be fair, that text originates in section 12.3, referring to input
parameters to procedures. Meanwhile, the following text (subclause
13.8, "<insert statement>") appears to be more pertinent:

"If the data type of the target identified by the i-th <column nameis
an exact numeric type, then the data type of the i-th item of the
<insert statementshall be an exact numeric type."

I have used SQLite (releases 2 and 3) to my satisfaction, aware of the
"common knowledge" around the limitations (or features) of SQLite with
respect to data types. I'd agree with the complainant that the
behaviour of SQLite isn't what one would expect, although I started my
relational database experience using an database system and
admit that I may have been spoilt, thus saving Mr Holden - a
self-confessed Yorkshireman, I believe - the effort involved in
pointing out the relative luxury of my professional upbringing. ;-)

Paul

Mike wrote:
11 Sep 2006 21:35:28 -0700, mensanator (AT) aol (DOT) com <mensanator (AT) aol (DOT) comwrote:
Mike wrote:
11 Sep 2006 18:23:50 -0700, mensanator (AT) aol (DOT) com <mensanator (AT) aol (DOT) comwrote:

Can you run your car on diesel fuel?

Why not?

Because your car's specification says to use gasoline?

If your car has been designed to run on diesel, you shouldn't
be saying it has gasoline engine. Duh.

No but you can still call it a car with an engine, just as SQLite is a
SQL database, with an SQL engine.

Seperate the data from the engine and what have you got?
Data with dynamic typing. Data that can't be migrated to
a "real" SQL database because you'll get type mismatches
when strings are inserted into numeric fields. The type affinity
kluge won't help there, will it?

Did you even read my original post? did you just completely miss the point?

It's not the job of the System Test Engineer to design things.
It's his job to find fault with everything. I just happen to be very
good at finding faults with things.

And apparently not very good at providing any constructive solutions.

But no one appreciates my finding those faults.

No one appreciates the tone in which you report these alleged faults,
necessarily agrees with the faults that you find, nor elected you
system test engineer of the SQLite project.

It calls for other things that many databases don't implement or
altogether violate as well, so what? Show me how both MS SQL's T-SQL
and 's PL/SQL procedure languages are so standards compliant
that you can use the same procedure code in both databases. You can't
-- precisely because they ignore or outright violate parts of the
standard as well. What's your position on that? Do some Googling and
you can easily find 18 ways that 's PL/SQL deviates from the
standard. And T-SQL is plainly nowhere close.

And how many of those systems use dynamic typing?

And how many conform to the standard?

Name one where the documentation claims the SQL Language
Specification is a bug.

Name one that conforms to the standard.

And a lot of people go to chiropractors. And chiropractors are
nice intelligent people with degrees. And the therapy provided
does good.

Nevertheless, the theory on which it's based is quackery.

To use your specious analogy, it represents another way of doing
things, which you admit yourself works. That's your justification for
calling Richard Hipp a crackpot?

What was Richard Hipp's justification for slandering the
writers of the SQL Language Specification?

It's clear. You're just way too smart for SQLite.

Did you see my solution to Rick Shepard's problem in the
thread "Parsing String, Dictionary Lookups, Writing to
Database Table"?

The point being? -- you can write Python code and feel entitled to
condescending and rude?

Is there anything more rude than describling the SQL Language
Specification as a bug that needs to be fixed?

12 Sep 2006 08:29:34 -0700, Paul Rubin
<"http://phr.cx"@nospam.invalidwrote:

But no one appreciates my finding those faults.

No one appreciates the tone in which you report these alleged faults,

Your tone is not so great either.

And what would you expect after someone who has take little to no effort to

1. read the documentation

2. seek help in the appropriate places, or

3. raise concerns to the appropriate people

unjustly and publicly vilifies an otherwise wonderful volunteer effort
to provide a free, high-quality, open source software package?

necessarily agrees with the faults that you find, nor elected you
system test engineer of the SQLite project.

It's an open source project, as you like to say. Everyone is a test
engineer.

real test engineers provide constructive feedback to the
appropriate places. Posting ill-informed flames to the Python mailing
list is hardly constructive feedback, or worthy of being called test
engineering.

standard as well. What's your position on that? Do some Googling and
you can easily find 18 ways that 's PL/SQL deviates from the
standard. And T-SQL is plainly nowhere close.

And how many of those systems use dynamic typing?

And how many conform to the standard?

How many of those deviations are justified in their documentation by
the responsible parties claiming, in effect, that they're smarter than
the standard's designers?

No one said they are smarter than anyone else in effect or otherwise,
but rather the SQLite project stated that it disagreed with static
typing. When you write free software, you have the freedom to write it
however you want, and that is the justification. This is the approach
that SQLite took, they are free to do so, and in no way have they been
deceptive about it.

And because SQLite deviates in this way does not make it any more
guilty of deviating or not conforming to the SQL standard than any
other SQL database.

It seems obvious to me that there should, at minimum, be an option to
turn this particular nonstandard behavior on and off.

Then the obvious means by which to accomplish this are like any other
open source project: At a minimum, post this suggestion to the mailing
list, write an RFE, or best of all, write the missing code yourself
and supply a patch to the bug list.

Paul Boddie wrote:
Fredrik Lundh wrote:

>>Steve Holden wrote:
>>
>>
Sure. But if you go back to the start of the thread you'll remember the
P was originally complaining that SQLite was being promoted in the
Python docs as SQL compliant. It clearly isn't if its response to the
insertion of a data value that conflicts with the declared column type
is to store a value whose type is something else.
>>
>>the standard actually says "If the value of any input parameter provided
>>by the SQL-agent falls outside the set of allowed values of the data
>>type of the parameter // the effect is implementation-defined" so
>>that's perfectly SQL92 compliant.

To be fair, that text originates in section 12.3, referring to input
parameters to procedures. Meanwhile, the following text (subclause
13.8, "<insert statement>") appears to be more pertinent:

"If the data type of the target identified by the i-th <column nameis
an exact numeric type, then the data type of the i-th item of the
<insert statementshall be an exact numeric type."

I have used SQLite (releases 2 and 3) to my satisfaction, aware of the
"common knowledge" around the limitations (or features) of SQLite with
respect to data types. I'd agree with the complainant that the
behaviour of SQLite isn't what one would expect, although I started my
relational database experience using an database system and
admit that I may have been spoilt, thus saving Mr Holden - a
self-confessed Yorkshireman, I believe - the effort involved in
pointing out the relative luxury of my professional upbringing. ;-)

Spoilt? I should say so. When I were a lad we 'ad to scrape around for
half a bit here and half a bit there - you didn't find whole bits just
lying around in them days. As fer integers, you 'ad to save up for a
year just to get sixteen bits. We used ter dream abaht 'avin' 64 bits,
but there were no chance unless yer Dad were rich. Most of our strings
were put together from characters we'd dredged out of t' canal, you 'ad
to try and ignore the mud. We'd 'ave given our eye teeth for a bit of
UTF-8 on a Sunday.

These kids wi' their databases didn't know they were born. I can
remember 'avin' to optimise programs by making sure that the next
instruction were comin' under the heads of t' drum just as the last
instruction were finishing.

But yer tell these young folk nowadays and they just don't believe yer.

etc., etc.

pythonistical-ly y'rs - steve

11 Sep 2006 21:35:28 -0700, "mensanator (AT) aol (DOT) com" <mensanator (AT) aol (DOT) com>
declaimed the following in comp.lang.python:

Seperate the data from the engine and what have you got?
Data with dynamic typing. Data that can't be migrated to
a "real" SQL database because you'll get type mismatches
when strings are inserted into numeric fields. The type affinity
kluge won't help there, will it?

So don't insert strings into numeric fields Seems simple

The Emperor is naked and I'm the only one who can see it.

The rest of us have seen the signs that state that the Emperor
resides on a nude beach

Paul Boddie wrote:

To be fair, that text originates in section 12.3, referring to input
parameters to procedures.

which is the section that section 4.1 ("data types") refers to for more
details on mappings between host data and SQL data. guess it depends on
how you look at the different layers: if you're using SQLite via the DB-
API, are you using "SQL" or an "SQL Agent"?

</F>

12 Sep 2006 09:31:54 -0700, mensanator (AT) aol (DOT) com <mensanator (AT) aol (DOT) comwrote:

To use your specious analogy, it represents another way of doing
things, which you admit yourself works. That's your justification for
calling Richard Hipp a crackpot?

What was Richard Hipp's justification for slandering the
writers of the SQL Language Specification?

Slander? Richard wrote a free and open source relational database the
way he wanted to, in a way that seems useful to him, and apparently
thousands of other people. You are free to write your own open source
relational database as well which has its own deviations from the
standard. I say this because I'd love to see you write the worlds
first SQL compliant database, and do it in under 30,000 lines of C,
and make it portable across both operating systems and byte orders,
capable of being run on cell phones and low power environments.

The only slander I see are in your ill-informed posts on this mailing
lists which should really be directed to the SQLite list. Apparently,
you are too afraid to insult the SQLite community to its face, and
seek refuge on the Python list.

It's clear. You're just way too smart for SQLite.

Did you see my solution to Rick Shepard's problem in the
thread "Parsing String, Dictionary Lookups, Writing to
Database Table"?

The point being? -- you can write Python code and feel entitled to
condescending and rude?

Is there anything more rude than describling the SQL Language
Specification as a bug that needs to be fixed?

Yes, namely calling respectable people (who selflessly write free and
open source software in their spare time) crackpots. Also, not lifting
a finger to seek help or effect change, but taking every effort to
insult. And finally, there is entertaining the notion that people live
to serve your needs. Yes, there are things that are clearly more rude.

Mike wrote:
12 Sep 2006 09:31:54 -0700, mensanator (AT) aol (DOT) com <mensanator (AT) aol (DOT) comwrote:

To use your specious analogy, it represents another way of doing
things, which you admit yourself works. That's your justification for
calling Richard Hipp a crackpot?

What was Richard Hipp's justification for slandering the
writers of the SQL Language Specification?

Slander? Richard wrote a free and open source relational database the
way he wanted to, in a way that seems useful to him, and apparently
thousands of other people. You are free to write your own open source
relational database as well which has its own deviations from the
standard. I say this because I'd love to see you write the worlds
first SQL compliant database, and do it in under 30,000 lines of C,
and make it portable across both operating systems and byte orders,
capable of being run on cell phones and low power environments.

And when he was done, he made the ridiculous claim that
the SQL Language Specification of static typing was a bug.

The only slander I see are in your ill-informed posts on this mailing
lists which should really be directed to the SQLite list.

Why? I'm not requesting that dynamic typing be removed from
sqlite. I'm not even requesting that the slander in the sqlite docs
be removed. What I'm requesting is that these "features" of
sqlite be better presented in the Python docs, hence, no posting
to the sqlite list. Is that so hard to figure out?

Apparently,
you are too afraid to insult the SQLite community to its face, and
seek refuge on the Python list.

I'm sure the sqlite community could care less about what appears
in the Python documentation.

It's clear. You're just way too smart for SQLite.

Did you see my solution to Rick Shepard's problem in the
thread "Parsing String, Dictionary Lookups, Writing to
Database Table"?

The point being? -- you can write Python code and feel entitled to
condescending and rude?

Is there anything more rude than describling the SQL Language
Specification as a bug that needs to be fixed?

Yes, namely calling respectable people (who selflessly write free and
open source software in their spare time) crackpots. Also, not lifting
a finger to seek help or effect change, but taking every effort to
insult. And finally, there is entertaining the notion that people live
to serve your needs. Yes, there are things that are clearly more rude.

So you admit that Richard Hipp's characterization of SQL was
rude. And now that we've established what you are, we're just
haggling over price.