SQL BackEnd Clarification

[Egbert van der Wal]

Ah, another thing I've been wondering. Very useful, this mailing list!

I have two more questions.

If I understand it correctly, there seems to be a bug somewhere 
resulting in some things not being saved in SQL, but it is unknown where 
this bug is? From a backup point of view it's more convenient for me to 
use a database over XML file, so if I can be of any help in bug testing 
/ looking for ways to reproduce I'm willing to do so. It's good to have 
a starting point, then.

And so, multi-user is unsupported in the current version, because when 
writing to the database, changes made by others are overwritten / undone 
and may result in inconsistency in the data. Is support for multi-user 
on the wishlist, and if so, is it in current progress or what is its 
priority?

Just out of interest, there's no specific need in my use case for 
multi-user support currently, but it's always good to know what to 
expect (or not to expect).

Thanks for the great software!

Best regards,

Egbert van der Wal


On 26/11/13 05:46, David wrote:
> 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
>