Is it just me, or is Sqlite3 goofy?

[Mike Owens]

On 11 Sep 2006 23:29:28 -0700, mensanator at aol.com <mensanator at aol.com> wrote:

> But it was stated in the sqlite docs that ALL SQL databases
> use static types implying that sqlite will be incompatible
> with any "heavy" database should the need arise to migrate
> upwards. The issue is not that there will be compatibilty
> problems with any data migration but that the truth is exactly
> opposite of what's claimed in Section 13.13.

Implying? There's a solid word. Migrating data from SQLite to other
databases is no more difficult or easy than migrating data to any
other database. Do you think this is ever trivial? It's as hard or as
easy as you make it. No database can just take any schema and the data
you put in it and just magically convert that schema/data to
flawlessly work in any arbitrary database of your choosing. Some
databases have tools to help with this, but they still are not
perfect.

> I'm not saying sqlite can't be used, what I'm asking for
> is that the documentation lay the facts out and I'll decide
> whether I can make this work in my application. Lying about
> it makes you sound like Microsoft.

Lying? Whose lying? Where on the website is there a lie about
anything? From what I can tell, you've not taken the time to read the
documentation or post anything to the mailing list. You've just posted
jeremiads on the Python list.

Don't like the documentation? Ever volunteered to help out? Ever
posted any suggestions on the list or report a bug?  Do you really
think that open source projects exists to serve you and meet your
standards? Do you think that free code and documentation just falls
like manna from heaven? Do you honestly think the two core developers
of SQLite have some secret agenda to deceive you or the world into
using SQLite?

> > Of course you could restrict yourself
> > to, by example, SQL92 entry level and be a lot more compatible.
> > But if I'm using a nice OO language like Python which lets me bind
> > *any* object to *any* name, why should be wrong to bind *any* object
> > to *any* database column?
>
> But SQL isn't OO, it's relational. That means JOINing tables
> together on a common field. In theory, due to the comparison
> hierarchy, it is impossible to do JOINs with dynamic typing
> since different types can never be equal. In practice, the type
> affinity kluge trys to work around this but can't do anything
> if the string doesn't look like an integer when a text field
> attempts to JOIN to an interger field.

Unless you ensure that the correct types are put int the column to
begin with, which is entirely possible with SQLite, as I've already
demonstrated. And if that's just too much to bear, you can still do an
inner join by explicitly casting the two columns in the join
constraint to a common desired type. Want to know how? Read the
documentation.