MySQL vrs SQLite

[Dave Benjamin]

In article <mailman.304.1083838565.25742.python-list at python.org>, Richie Hindle wrote:
> 
> [Dave, quoting the SQLite docs]
>> """SQLite is "typeless". This means that you can store any kind of data you
>> want in any column of any table, regardless of the declared datatype of that
>> column. (See the one exception to this rule in section 2.0 below.) This
>> behavior is a feature, not a bug. A database is suppose to store and
>> retrieve data and it should not matter to the database what format that data
>> is in. The strong typing system found in most other SQL engines and codified
>> in the SQL language spec is a misfeature - it is an example of the
>> implementation showing through into the interface. SQLite seeks to overcome
>> this misfeature by allowing you to store any kind of data into any kind of
>> column and by allowing flexibility in the specification of datatypes."""
> 
> How about this?
> 
> """Python containers (eg. lists, sets, tuples) are "typeless". This means
> that you can store any kind of data you want in any container, without a
> declared datatype for that container. This behavior is a feature, not a bug.
> A container is suppose to store and retrieve data and it should not matter
> to the container what format that data is in. The static typing systems
> found in many other programming languages and codified in many language
> specs is a misfeature - it is an example of the implementation showing
> through into the interface. Python seeks to overcome this misfeature by
> allowing you to store any kind of data into any container and by allowing
> flexibility in the specification of datatypes."""

First of all, a minor nit: the word you want here is "supposed", not
"suppose". =)

Database relations are not generic containers. For a tuple to exist in a
relation, it must satisfy a set of predicates that have been defined for
that relation. These predicates include primary key, foreign key, and unique
constraints. They also include (IMHO) the domains of the values for any
particular column. A "typeless" database would limit the kinds of assertions
I could make about a relation, and to me this is a misfeature.

> See every third posting to comp.lang.python over the past ten years for why
> this kind of thing may or may not be a good idea.  Most of the arguments
> that apply to dynamic typing in Python also apply to typelessness in
> databases (the main one being: it's up to your unit tests, not anything
> else, to make sure your program works).  If Python's dynamic typing fits
> your brain, it seems likely that a typeless database should do so too.

I reject the claim that typing issues in programming languages are the same
for databases.

There are definite advantages to programming in dynamically typed languages
like Python; most importantly, they are lean on syntax and don't get in your
way like the popular statically typed languages. The main thing that makes
programming in statically typed languages a pain is the amount of explicit
type annotations and casts typically required to pull it off. This is more
of a flaw in the respective type systems of these languages than with static
typing in general. Languages in the ML family have shown that a type system
can be out of your way (via type inference) yet still allow for compile-time
checks. The "duck typing" concept that Python and Ruby programmers get so
excited about can be accomplished using OCaml's objects because OCaml's type
inference system is powerful enough to support this.

Don't get me wrong; I love Python, and I love dynamically typed languages.
There are still lots of things you can do in a dynamically typed language
that would be difficult or impossible to accomplish in a statically typed
language (without heavy use of reflection and dynamic casts, anyway).

However, you simply cannot extend this issue to cover databases as well. For
instance, SQL does type inference also. The only time you need to specify
the types of data is when you declare your schema. The types of columns
returned by queries are inferred by the database automatically. No explicit
type annotations are necessary here. So, SQL does not suffer from my main
pet peeve with statically typed languages, even inside of a "typeful"
database.

If the database has the expressiveness to make assertions about the data it
contains, these assertions become assumptions to the programs that use the
database. If you like simple, terse Python programs, you'll enjoy using a
database that guarantees that your assertions were met, because these
assertions do not need to be checked by your program.

Databases may be shared by multiple programs written in multiple programming
languages. How are you going to write a unit test to ensure that someone
doesn't write a program in the future that sticks "hello" in a customer's
age field? Put simply, if the database does not ensure that this will not
happen, you'd better guard against this by using an int() cast and catching
the ValueError exception, in which case you're basically back to type
annotation.

Your claims about types in database being a feature remind me of MySQL's
earlier claims about why they didn't support foreign key constraints (if you
write your code properly, you don't need them; they just get in the way).
Now, you'll notice that they have thoroughly removed such claims from their
documentation.

I think it's fine if you want to have a typeless database. I was just
advising the OP to take a look at his requirements and make sure that this
is acceptable. Surely you don't expect him to assume that since he already
uses Python, a dynamically typed (read: not *typeless*) language, he does
not need to consider the repercussions that switching from MySQL to SQLite
will have on his program, because of a few weak analogies and claims of
brain-fitting?

-- 
.:[ dave benjamin: ramen/[sp00] -:- spoomusic.com -:- ramenfest.com ]:.
:  please talk to your son or daughter about parametric polymorphism. :