QOF-to-SQL Proof of Concept

[Derek Atkins]

I'm sorry, but all I have to say is:

DUH!

-derek

"John Arrowwood" <jarrowwx at hotmail.com> writes:

> 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
>
>
>
>

-- 
       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