QOF-to-SQL Proof of Concept

[John Arrowwood]

You've got to be careful here.  Some things to ponder:

In a multi-user environment, you don't want user A to be blocked because 
user B is trying to do something.  If they are blocked, it should be for no 
longer than a second.  That means, for example, you can't have things locked 
for the entire time that a dialog is open.

Another thing to keep in mind is the availability of features.  MySQL at one 
time didn't have transaction support.  Now it does.  Other database systems 
you might be thinking of supporting may or may not have this or that 
feature.

I suggest a database abstraction.  Have the ability to write 
database-specific code that is optimized for the way things are done in a 
particular database.  That'll give you the speed that is both desired and 
deserved.  But if you have the ability to drop a different piece of code in 
for a different database for a particular action, then you can design it in 
such a way that you don't have to do 'least-common-denomenator' and all the 
performance penalties that doing so will impose on you.  Have an 
architecture that doesn't care what the data-store device is, and doesn't 
have any knowledge of that store, it just says "this is what I need done."  
Figure out what kinds of things you will need done, and then find an OPTIMAL 
way of doing that in each database.

That MAY mean that setting up the tables will be different for each 
back-end.  That's not as bad as you might think.  Design it such that doing 
so won't fundamentally break the system.  Then, you could have an XML 
back-end.  You could have a CSV back-end.  You could have a dbase back-end.  
Or Oracle.  Or MySQL.  Or whatever you want.  And the limitations of the 
back-end could be completely transparent to the front-end.  The front-end 
doesn't suffer from the limitations of the back-end.  For example, some 
systems may require a file-based lock.  Others may require a row-based lock. 
  Others, a table-lock.  Design it in the abstract, and then take advantage 
of the available features of each back-end in order to make it work.

Or, pick one database and say "this is it, folks!  it works here, no where 
else."  But we both know that there is a reason you don't want to do that...

Now, you may have already planned on doing all of that.  Don't know.  But on 
the off chance that you hadn't, I figured I'd toss it out there! :)


>From: Derek Atkins <warlord at MIT.EDU>
>To: Benjamin Carlyle <benjamincarlyle at optusnet.com.au>
>CC: gnucash-devel at gnucash.org
>Subject: Re: QOF-to-SQL Proof of Concept
>Date: Sat, 29 May 2004 22:59:53 -0400
>
>Benjamin Carlyle <benjamincarlyle at optusnet.com.au> writes:
>
> > The database handles it for you.
> >
> > Whenever you say "BEGIN TRANSACTION;" a exclusive lock will be placed on
> > the whole database. When you commit or rollback your transaction the
> > lock is removed. Any insert, update, or other operation that modifies
> > the database will implicitly begin and commit a transaction during its
> > execution. Every select statement locks the whole database with a shared
> > lock and unlocks when the query is done.
>
>Good.  This is exactly what I expected.  So long as this works across
>multiple applications connecting to the same database, then it's just
>fine.  I think we'll still need to do some sort of internal locking
>to stop the race condition of having two apps trying to change the
>same piece of data..
>
>I guess we could have the internal "begin edit" actually perform a
>database lock, but that's not really a viable option because the app
>could hold that lock for a long time waiting on user input.
>So we just need to be careful to:
>
>lock db
>do {
>    check status
>    if (status changed)
>       break;
>    update record
>} while (0);
>unlock db
>
>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?
>
> > 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
>_______________________________________________
>gnucash-devel mailing list
>gnucash-devel at gnucash.org
>https://lists.gnucash.org/mailman/listinfo/gnucash-devel