[Nottingham] SQLite and 'locking'

[Ovid]

--- Martin <martin at ml1.co.uk> wrote:

> Is not the question then:
> 
> Has the seat really not been booked?
> 
> Or was there an error in the booking where a booking has been made
> but
> the booker's name errored out to NULL?
> 
> 
> Would it not be better to have the special person's name "NoOne" and
> then ensure that there is noone in the world whom out of
> perniciousness
> against IT have called themselves "NoOne"?

No!  This now introduces a special case which introduces even more
potential bugs.

  my ($not_booked) = $dbh->selectrow_array(<<'END_SQL', undef,
'Noone');
    SELECT count(*)
    FROM   seats
    WHERE  booked_by = ?
  END_SQL

Yay!  All seats are booked :)

Um, no they're not :)

Don't deliberately introduce special cases in code or databases unless
you have no other choice.  Just go with two tables and the problem goes
away.

  SELECT COUNT(*)
  FROM   seats 
  WHERE  id NOT IN (SELECT seat_id FROM seats_booked)

No more ambiguity and no special case.

Cheers,
Ovid

--

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