[Nottingham] SQLite and 'locking'

[Martin Garton]

On Wed, 2007-02-28 at 12:47 +0000, Duncan John Fyfe wrote:

> And I have the scars from walking the pure-OO DB (called O2) road.  I
> don't want to go that way again (and no it wasn't by choice the first
> time round).

I know exactly what you mean.

> I see databases much in the same way I see security.  You can devise the
> perfect system but if it makes life difficult users will work around you
> and not with you.  Until somebody comes up with the perfect usable
> system (and isn't vapourware) then we are all just trying to get the job
> done the best way we can with what we have.  

At work, I use I almost exclusively use hibernate these days for
database access.  We have various apps installed that use it with
non-trivial sized databases (hundreds of tables with several millions of
records in quite a few of them) and it works very well for us.  In fact
its lazy loading and caching features fix extremely well with our
typical java use patterns and take a /lot/ of thinking away from the
developers whilst keeping things fast.  All we have to really do with
the database (once hibernate has created it for us) is analyse common
use patterns and tune things.  Typically this is a case of adding
indexes etc, as required and then handing it to a DBA for the RDBMS in
question to do the rest.

> > Doesn't your multiple table approach just trade a potential null field
> > error with a potential missing-record-on-one-of-the-tables error?
> 
> I'm not sure what you mean here.  Certainly SQLite doesn't enforce
> foreign key constraints which makes the kind of error I think you are
> alluding to possible while suitable constraints in for example
> postgresql would make it impossible.

It's quite likely I mis-understood your original point.  All I mean is
that by having a no-null-ever rule, you force what would otherwise be an
allowed null column onto a separate table.  And instead of using null on
the original table, you use the absence of a record on the separate
table to indicate no value for that field.  But that does not help
remove errors, because in the same way omitting "NOT NULL" on a column
in the first table might allow programmer errors, not having a record on
a separate table might allow programmer errors.  (if that makes sense?!)

In summary I think that allowing NULL as a method of indicating the
absence of a value does not encourage programmer mistakes any more than
other ways of doing it.

-- 
Martin.

-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 191 bytes
Desc: This is a digitally signed message part
Url : http://mailman.lug.org.uk/pipermail/nottingham/attachments/20070228/c1e2f985/attachment.bin