On Tue, Jun 15, 2010 at 04:36:15PM +0100, Simon Slavin scratched on the wall:
> On 15 Jun 2010, at 4:23pm, Robert Latest wrote:
> > Instinctively I'd rather first SELECT, store the
> > results, finalize the SELECT statement and then get to work on its
> > result using the stored data. It's just that without intermediate
> > storage it's a bit easier (no need to do any ressource management),
> > and I've found nothing in the docs that says I shouldn't do that.
> On a standard desktop system where RAM is cheap, your principle
> of storing all the SELECT data before beginning your changes is sound. 

  Depends on the database, but in general I suppose that's true.

> Technically there's no reason not to interleave the _step() with
> changing the data, but it requires a detailed understanding of how
> SQLite works,

  Pre-selecting the data doesn't really simplify the situation.  You
  still need to deal with transactions and locking issues.  In
  specific, you need to wrap the whole process-- the read, store, and
  modify steps-- into a single transaction or you risk having the
  database change between the read and modify steps, possibly
  invalidating the modifications you're trying to make.

  Once you go that far, interleaving the select and modify commands is
  more or less the same thing.

> especially if the changes you make would have changed the result of
> the SELECT command. [1]

  OK, yeah, that's an issue.  There isn't any way to do this in a 
  deterministic way when using interleaved select/modify statements.
  Most people don't do that, however... If you need to modify the
  table you're scanning, you can usually roll the whole thing up
  into one very large and complex update command.  

> Storing all the SELECT data before making your changes means you
> don't have to understand these technicalities,

  I disagree with this.  Doing the pre-select doesn't get rid of any of
  the transaction issues.  In fact, it makes it worse.  If you
  interleave the commands, the select's autocommit transaction protects
  the whole process.  If the developer never issues or thinks about a
  transaction, it all works correctly and safely.
  If you pre-select and then modify, you have to be aware enough to
  realize you MUST wrap the whole process in a manual transaction,
  and you still need to know how to deal with all the locking and
  busy issues that come with that.


Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
sqlite-users mailing list

Reply via email to