QOF-to-SQL Proof of Concept
jarrowwx at hotmail.com
Sun May 30 02:26:09 EDT 2004
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
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:
> check status
> if (status changed)
> update record
>} while (0);
>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 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
More information about the gnucash-devel