[Nottingham] SQLite and 'locking'

[Ovid]

--- Martin Garton <martin at stupids.org> wrote:

> A meaning being ascribed to null may be wrong in relational
> theory[1],

NULLs don't work in the relational model because an attribute (what
most would call a column or field) have a name *and* a type.  NULLs
cannot have a type.  As a side effect, queries like the following would
properly be compile-time errors rather than simply returning garbage as
they do today:

  SELECT last_name, first_name, age
  FROM   customers
  WHERE  age > id

A logical comparison of a person's age to the customer.id attribute is
non-sensical.  If for some reason you did need that, you'd have to
explicitly tell the database how such comparisons are to be made as the
database can't guess what's really intended there.  (i.e., you have to
manually extend the type system).

That seems like a stupid example, but consider this:

  SELECT last_name, first_name, age
  FROM   customers
  WHERE  age > weight

For all I know, that might be a valid comparison for medical reasons,
but you're comparing a temporal number to a mass number.  Whether or
not that makes sense depends up on the context of the application and
the developer *should* be required to extend the type system to resolve
the ambiguity.

> and may be unpleasant to some, but certainly works perfectly well in
> many real situations.  In fact solutions like mine often work better
> in practise. 

Except when they return bad data.  Consider the following very
reasonable looking query to determine how many seats are not booked:

  SELECT COUNT(booked_by)
  FROM   seats
  WHERE  booked_by IS NULL

In most databases, that will always return 0 regardless of how many
seats are or are not booked but it's easy for a developer to make that
mistake.  However, the problem is even worse than that.  By overloading
the meaning of 'booked_by' to mean both "this seat is booked" and "this
person booked the seat", the poor developer reading the database code
has no way of knowing what NULL means.  Was the seat not booked?  Was
it booked by we do not know by whom?  Is there a bug in the code?  I am
constantly trying to stamp out bugs in a horrid database caused by
allowing NULL values in it.

> One reason is that because by always avoiding nulls at all
> costs can actually make things harder for developers to understand
> and so bugs spring up for different reasons.

To a large extent that's because most programmers treat databases as a
dumb data store and don't realize that proper design of a database is
as important as proper design of the code.  By ignoring database
design, they introduce many long-term bugs that keep me employed (and
I'm a Perl programmer, not a DBA!)

Heck, many developers who would get annoyed with someone doing a sloppy
read of some code don't mind someone doing a sloppy read of a database.
 :(

> Also, taking things to the extreme (6th normal form for example)
> tends to destroy performance in many use cases.

We are in complete agreement here.  I would argue against "premature
optimization" and say not to worry about it until a performance problem
can actually be demonstrated, but it's true that since database
manufacturers have never really paid much attention to the relational
model, following that model for more correct code often comes with a
performance penalty.

Cheers,
Ovid

--

Buy the book -- http://www.oreilly.com/catalog/perlhks/
Perl and CGI -- http://users.easystreet.com/ovid/cgi_course/