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. -j -- 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
