QOF-to-SQL Proof of Concept

[Derek Atkins]

Benjamin Carlyle <benjamincarlyle at optusnet.com.au> writes:

>> I'm not sure a BEGIN TRANSACTION / COMMIT TRANSACTION is sufficient to
>> do this, unless we can actually perform a SELECT and get back real
>> data in the middle of a transaction?
>
> # Calculate update ahead of time, allowing user to
> # enter new values.
> BEGIN TRANSACTION;
> SELECT version FROM mytable WHERE record = "record1";
> # Programmatically compare result to the version we expected
> # If different, cancel transaction and deal with error
> # If same, proceed
> UPDATE mytable SET foo = "bar", version = version+1 WHERE record =
> "record1";
> COMMIT;

Ok, so this construct DOES work..  The "BEGIN TRANSACTION" is
effectively the "LOCK".  :)

> I suspect other databases will require some kind of "lock whole
> database" semantic. Sqlite won't, as the BEGIN TRANSACTION is sufficient
> to exclusively lock the whole database. You're right, though. If you're
> going to target more than just sqlite you will have to setup some larger
> locking semantic. I believe most client-server databases will allow some
> kind of "LOCK" command to be issued on specific tables, rows, or the
> whole database.

Yea, we're targetting at least Sqlite and PG.  Maybe Mysql, too..
We're trying to be general.  Right now we've got PG, but I _really_
want to get sqlite (and use that in lieu of XML data files).

So basically, to sum it up, my understanding of sqlite semantics is
still exactly correct.. :)

Thank you for confirming this.

> Benjamin.

-derek

-- 
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       warlord at MIT.EDU                        PGP key available