Is it just me, or is Sqlite3 goofy?

[Fredrik Lundh]

No, it isn't the same old crap. When I define an Access field as
Double, I cannot insert a value such as ">200" or "ND" or "Yes".
I'm not saying static typing is better, just that migrating a dynamic
types to static types may cause difficulties that wouldn't be present
if it was static to static.

dynamic typing != random typing. if your program is using the DB-API to
add data to an SQLite database, who, exactly, is inserting the values?
who's producing the data? under what circumstances would that code
produce or insert arbitrarily typed data?

</F>

 

[mensanator]

As the original author of that sentence, I don't think it's either
misleading or untruthful; 'relatively easy' gives me wiggle room.

Ok, I appologize for saying that. Got a little carried away
by the flames.

However, to fix your complaint, I've changed the paragraph to read:

SQLite is a C library that provides a lightweight disk-based
database that doesn't require a separate server process and allows
accessing the database using a nonstandard variant of the SQL query
language. Some applications can use SQLite for internal data
storage. It's also possible to prototype an application using SQLite
and then port the code to a larger database such as PostgreSQL or
Oracle.

Of course, if you accept Fredrik's reading of the SQL standard, the
word 'nonstandard' in the revised text is incorrect; SQLite is
compliant with the standard but in an unusual way. (But most readers
will interpret "nonstandard" as meaning "not like most other SQL
databases", so I'll let it stand.)

And that was basically what I was originally asking for.

That and fixing the broken examples.

And maybe add a few more to illustrate SQLite manifest
typing which is alien to a long time Access user.

Should I start a new thread (sans polemics) to discuss the
examples?

 

[Tim Chase]

dynamic typing != random typing. if your program is using the

DB-API to add data to an SQLite database, who, exactly, is
inserting the values? who's producing the data? under what
circumstances would that code produce or insert arbitrarily
typed data?

Must be the code written by a Dr. Jekyll/Mr. Hyde personality...

"But honestly, boss, I didn't write this code! It was my evil
alter-ego that puts VARCHAR values containing Gilbert & Sullivan
lyrics into the Amount_Due CURRENCY fields!"



-tkc

 

[Steve Holden]

Must be the code written by a Dr. Jekyll/Mr. Hyde personality...

"But honestly, boss, I didn't write this code! It was my evil
alter-ego that puts VARCHAR values containing Gilbert & Sullivan
lyrics into the Amount_Due CURRENCY fields!"

Hence the phrase "Going for a song"?

groan-along-with-me-ly y'rs - steve

 

[Steve Holden]

Ok, I appologize for saying that. Got a little carried away
by the flames.




And that was basically what I was originally asking for.

That and fixing the broken examples.

And maybe add a few more to illustrate SQLite manifest
typing which is alien to a long time Access user.

Should I start a new thread (sans polemics) to discuss the
examples?

Why don't you? That would seem like a productive forward direction.

regards
Steve

 

[A.M. Kuchling]

Ok, I appologize for saying that. Got a little carried away
by the flames.

Apology accepted; no problem.

That and fixing the broken examples.

That's also done. I fixed the executescript.py example, and tried
running all the other examples as a check; that didn't turn up any
more crashers.

The development version of the docs, built nightly from the SVN trunk,
are at <http://docs.python.org/dev/>. Note that the version number is
now 2.6a0 on the trunk, but I can backport fixes to 2.5-maint as
they're made. (Any new changes won't get in 2.5c2, which should be
released tomorrow, but will get into 2.5final if the fixes are made by
about the 17th.)

Should I start a new thread (sans polemics) to discuss the
examples?

Certainly!

--amk

 

[A.M. Kuchling]

(Any new changes won't get in 2.5c2, which should be
released tomorrow, but will get into 2.5final if the fixes are made by
about the 17th.)

And in fact the formatted development version no longer reflects
what's in the trunk: I've just checked in a bunch of minor typo fixes
and other edits to the trunk. So you may want to wait until tomorrow
when these fixes show up... or you can look at the original LaTeX in
the SVN browser at <http://svn.python.org/view/python/trunk/Doc/lib/>.

--amk

 

[Tim Chase]

"But honestly, boss, I didn't write this code! It was my

Hence the phrase "Going for a song"?

I am the very model of a modern major database,
For gigabytes of information gathered out in userspace.
For banking applications to a website crackers will deface,
You access me from console or spiffy user interface.

My multi-threaded architecture offers you concurrency,
And loads of RAM for caching things reduces query latency.
The data is correctly typed, a fact that I will guarantee,
Each datum has a data type, it's specified explicitly.

is-it-friday-yet'ly yers,

-tkc

 

[mensanator]

I am the very model of a modern major database,
For gigabytes of information gathered out in userspace.
For banking applications to a website crackers will deface,
You access me from console or spiffy user interface.

My multi-threaded architecture offers you concurrency,
And loads of RAM for caching things reduces query latency.
The data is correctly typed, a fact that I will guarantee,
Each datum has a data type, it's specified explicitly.

Mensantor:
When I was a lad I had to load
Data to Access via import mode.
Data came in as DBase III
And had to be converted to csv.
I so carefully converted the csv's
That now I am the admin of big db's.

Chorus:
He so carefully converted the csv's
That now he is the admin of big db's.

 

[Dennis Lee Bieber]

Because I can extrapolate. I *know* before even trying it that
if I export all my data from a sqlite db to a csv file and then try
to import it into Access that there will be problems if the fields
aren't static typed.

GIGO... Don't rely on the DBMS to catch your bad data for you -- fix
it before you put it /into/ SQLite and you won't have any problems
taking it out of SQLite to some other system.

I've not installed/started other DBMS systems with which to test, so
much of this is based upon various documents found online or in my
library:

SQLite: Attempts to convert data into the type class of the
destination field; if the conversion fails it stored the data "as-is"...
No Data Loss

MySQL: Attempts to convert data into the type class of the destination
field; if the conversion fails, numeric fields seem to receive whatever
portion converted before the non-numeric character(s) were reached.
INPUT: ['132', 'A piece of PI', ' "Pythagoras"', ' -245', ' 3.I4']
OUTPUT: (8L, 132L, 'A piece of PI', ' "Pythagoras"', -245L,
Decimal("3.00"))
{That's three point EYE four on input, the 3. converted and was stored.}
Loss of Data

PostgreSQL (per PostgreSQL Essential Reference, 2002 New Riders):
"If an attempt is made to insert the wrong data type into a column,
PostgreSQL will automatically try to convert the data to the correct
data type."
The 2003 book does state that CAST() returns an error if a
conversion fails -- but the example isn't clear enough to indicate if
there might be "partial success" (a non-numeric in the middle of a
sequence of digits). This error may result in an exception in Python,
depending on how the DB-API adapter behaves.

Documentation for Firebird/Interbase and MaxDB is just as vague -- a
complete failure to convert sets an error code; but do the Python
adapters test and raise an exception, or does the application need to
query for the code?

Any takers on modifying my (non-validating) MySQLdb test program for
Firebird/MaxDB/PostgreSQL and seeing what they produce on the sample CSV
data?

That's one of the reasons why I was such a good test engineer.
I could anticipate problems the design engineers didn't think of
and I would deliberately provoke those problems during testing
and crash their hardware/software.

I wasn't very popular.

Even when it is a documented design behavior and for which
operational procedures are available to minimize future impacts?

You have not "provoked" a problem with SQLite. You /have/ brought up
that the USER (application programmer) of SQLite is responsible for
validating that the data they insert IS what they expect to use in the
future.
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[Hendrik van Rooyen]

I am the very model of a modern major database,
For gigabytes of information gathered out in userspace.
For banking applications to a website crackers will deface,
You access me from console or spiffy user interface.

My multi-threaded architecture offers you concurrency,
And loads of RAM for caching things reduces query latency.
The data is correctly typed, a fact that I will guarantee,
Each datum has a data type, it's specified explicitly.

is-it-friday-yet'ly yers,

-tkc

I love this group - thanks for that - it made my day ...

- Hendrik

 

[Magnus Lycka]

First of all, anyone with extensive experience in database systems
understand that validating and cleaning input is an unavoidable task.

Static typing can help identify some of the problems, but far from
all, and there is often data processing done before the data enters
the database, so it's often too late to do the cleaning at that time
anyway.

Once you are dealing with data within your system, it's a bug in the
software if data doesn't fulfill the intended constraints. Such
problems should be identified by unit tests.

Next, as far as transferring you data, you most likely have to resort
to some delimited format, or INSERT statements, which is no different
than any other database.

You can always write a Python script which reads from one
database, cleans up the data and inserts into another, one
row at a time. This isn't just a porting activity. I've written
a Python app that moved data from a DB2 mainframe production
database to DB2 testing database on Windows. It moved data
from a hierarchy of tables, starting in a given table, and
reading foreign keys from system tables (very slow in mainframe
DB2!) to figure out what data to bring from which tables.

Since the system used abstract keys and moved data to a populated
database, it had to generate new keys and adjust the foreign keys
in the dependent tables. It also had to work around bugs and
quirks in Windows NT, DB2 and ADODBAPI etc. These things are
relatively easy, but it's never trivial. There are always a lot
of details to deal with.

For big volumes, you typically need to use some kind of bulk
loading facility. Inserts are generally much too slow. (I'm
talking about general data porting woes here--in the case of
SQLite it's not likely that you have many million rows of data.)

Efficient bulk loading means that you have different data format
for different database systems, and also that you need to validate
your data before insertion, so the problems directly related to
SQLite doesn't seem very odd.

 

[Ben Sizer]

The mathematical definition of the relational model includes
that data values are drawn from specific sets.

Well, I did say relational algebra, which from what I understand
predates the official 'relational model'.

Implementing variable width columns has nothing to do with it.

On a practical level, it has lots to do with it!

Here's
the reference:

1.3. A Relational View of Data

The term relation is used here in its accepted mathematical
sense. Given sets S1, S2, ···, Sn, (not necessarily
distinct), R is a relation on these n sets if it is a set
of n-tuples each of which has its first element from S1,
its second element from S2, and so on [1]. We shall refer to
Sj as the jth domain of R.

Does it specify anywhere that sets S1...Sn cannot each be the universal
set? To put it another way - although the spec implies the existence of
limited set domains, and data types enforce limited domains, I don't
think a requirement to allow limited domains is a requirement for
static data types.

 

[Bryan Olson]

Well, I did say relational algebra, which from what I understand
predates the official 'relational model'.

Relational algebra got into it when you said "SQL is based on
relational algebra". SQL is based on the relation model.
Incidentally SQL's expressions are closer to relation calculus
than to relational algebra.

Furthermore, relation algebra does deal with types. One can,
for example, infer the type of a result from the type of the
operands.

On a practical level, it has lots to do with it!

There are subtler points on that, but it's not the issue here.

Here's
the reference:

1.3. A Relational View of Data

The term relation is used here in its accepted mathematical
sense. Given sets S1, S2, ···, Sn, (not necessarily
distinct), R is a relation on these n sets if it is a set
of n-tuples each of which has its first element from S1,
its second element from S2, and so on [1]. We shall refer to
Sj as the jth domain of R.

Does it specify anywhere that sets S1...Sn cannot each be the universal
set?

No; it indicates that they can be different, and when they are,
value not of the given set are not legal.

To put it another way - although the spec implies the existence of
limited set domains, and data types enforce limited domains, I don't
think a requirement to allow limited domains is a requirement for
static data types.

One might argue that a one-atom-type-only DBMS can still be
relational, but that's not what SQLite offers. As we've seen,
one does declare the type of a column, and the DBMS seems to
prefer the stated type in that it will sometimes convert
values of other types.

 

[Scott David Daniels]

... dynamic typing != random typing.

So true. To get a really good random typing going, you need a
cryptographically strong random number generator to feed the
application of type constructors to values during the execution
of a program. Perhaps the best way to do this is to modify the
the interpreter. We could call the new language "Python?!", or
actually use an interobang if Unicode has such a character.

--Scott David Daniels
(e-mail address removed)