SQL BackEnd Clarification

[David]

Ah. Derek's comment clears things up. I am accustomed (and I believe I am not alone in this) to thinking of databases as dynamically written and *read*, and it is this last part that tripped me up. 

David



_____________________________________________
From: Derek Atkins <warlord at MIT.EDU>
Sent: Mon Nov 25 07:17:15 PST 2013
To: John Ralls <jralls at ceridwen.us>
Cc: "David T." <sunfish62 at yahoo.com>, Users Gnucash <gnucash-user at gnucash.org>
Subject: Re: SQL BackEnd Clarification


John Ralls <jralls at ceridwen.us> writes:

> On Nov 22, 2013, at 12:48 PM, David T. <sunfish62 at yahoo.com> wrote:
>
>> I have yet to use the SQL back end with GnuCash, but in following
>> the many discussions on the lists (both users and devel), I have
>> read seemingly contradictory statements about the nature of data
>> storage with the SQL back end.
>> 
>> Specifically, at times we are told that GnuCash loads the entire SQL
>> database into memory, and that simultaneous access to the same data
>> file by multiple users will fail because GnuCash writes out the
>> entire database upon closing. However, at other times, we are told
>> that the Save button is never activated with the SQL back end
>> because the changes are saved to the SQL back end immediately.
>> 
>> Could someone explain this apparent paradox to me?
>
> You’re confusing the two backends. Both XML and SQL backends do load
> everything into memory at startup. The XML backend writes everything
> out when you run Save and periodically if you have auto-save turned
> on. The SQL backend writes at almost every time you do something, and
> that ‘almost’ is why we don’t recommend using it for production. We
> think that there are a few places that we haven’t found yet where a
> change doesn’t trigger a write or even mark the object as dirty. That
> works out OK in the XML backend because something else the user does
> or did will require a save and everything gets saved, but if the
> object isn’t marked dirty, the SQL backend won’t save it even if a
> write of some other object is triggered.
>
> The only locking in either backend is database-wide, and once the SQL
> backend has read the database it doesn’t do so again until you either
> switch databases or restart Gnucash. That’s why it’s single-user.

Just to add to this, the issue with sharing the SQL backend is due to
the fact that GnuCash does load everything at load time. If you had
multiple users making changes then the different users wouldn't see each
other's changes.

> Regards,
> John Ralls

> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.

-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