MySQL sync

[Marcus Wolschon]

There must be a way without violating 1st normal form and storing
everything twice.
Maybe an "updated"-timstamp or something may help.

(Also I`m concerned that still loading all transactions from the db
into ram will work
 out after a few years of bookkeeping. You cannot split a database by
year as you can
 an xml-file.
 I'm thinking about maybe 100 splits and 40 transactions as well as 10
currency-quotes
 every day for 10 years as a sensible testcase for scalability.)

Marcus


On Fri, Aug 14, 2009 at 9:11 PM, Phil Longstaff<plongstaff at rogers.com> wrote:
> I've thought a bit about an audit log for the sql backend.  If all you need is a parallel set of tables (e.g. for accounts, have accounts_audit_log which contains the same fields, as well as an autoinc index and date), then you might be able to implement it using triggers - any change to the accounts table would have a trigger which would copy the changes to the audit log.
>
> Phil
>
>

>
>
> ________________________________
> From: Vladimir Bashkirtsev <vladimir at bashkirtsev.com>
> To: Derek Atkins <warlord at MIT.EDU>
> Cc: gnucash-devel at gnucash.org
> Sent: Friday, August 14, 2009 2:43:24 PM
> Subject: Re: MySQL sync
>
> Derek Atkins wrote:
>> Hi,
>>
>> Please remember to CC gnucash-devel on all replies using your mailer's
>> Reply-To-List or Reply-All functionality.
>>
> Missed that. Now I have pressed correct button. :)
>> Vladimir Bashkirtsev <vladimir at bashkirtsev.com> writes:
>>
>>
>>> Derek,
>>>
>>> Perhaps I need to check ascertain amount of work involved. There's two
>>> approaches we can take: first one is using timestamps and re-reading
>>> data if change happened. Second one is to get data straight out of DB
>>> each time it is needed and use DB transactions (if available). First
>>> approach most likely will be prone to data corruption and re-reading
>>> whole lot will slow things right down. Second approach looks like more
>>> appropriate but most likely it will require good amount of work as it
>>> is quite different to current architecture. Can you give your opinion
>>> about how hard it would be to implement second approach? How hard it
>>> is to get GnuCash not to use data from memory but use it directly from
>>> DB?
>>>
>>
>> The hardest part is figuring out "what changed".  Ideally we'd have an
>> audit log in the database, which would effectively mimic the .log files
>> for XML.  Every operation would get logged in the DB both for potential
>> undo and also for later auditing.
>>
>> It would be nice if there were some way to "notify" clients that there's
>> a change to the DB so it knows it should reload.  However most DBs dont
>> have that, so we'd need to do it manually by, say, an epoch number for
>> the database.  Everytime the db is updated you increase the epoch.
>> Another way you could do this would be an autoincrement in the 'audit
>> log' and thereby use your audit log id number as your epoch.
>>
>> Really, tho, this is just a cache coherency problem, which has been
>> solved many times in many ways.
>>
>> However, changing the architecture of GnuCash to be a pure DB app would
>> entail rewriting MOST of the engine.  I wouldn't recommend going that
>> route in the short term.
>>
> Well... Rewriting most of engine is definitely not something I plan. :)
>
> So I should take on board your idea to go with audit log. It should not be too hard to implement. Then use autoincrement in DB and have GnuCash to check it at regular intervals and before any operation which requires access to the data. If there's new entries in the log then just replay them to get updated. Something tells me that ability to store log records and ability to replay them back already is part of the engine.
>
> Have I missed anything? If not then I am quite excited and... (read below)
>>
>>> Of course I can just read the source but it would take sometime and
>>> your opinion can get me to make a decision to do it or not a bit
>>> quicker.
>>>
>>
>> You should still read the source. :)
>>
> I will read the source! :)
>>
>>> Vladimir
>>>
>>
>> -derek
>>
>>
>>> Derek Atkins wrote:
>>>
>>>> Hi,
>>>>
>>>> Vladimir Bashkirtsev <vladimir at bashkirtsev.com> writes:
>>>>
>>>>
>>>>> Hello,
>>>>>
>>>>> I was testing latest development version with MySQL backend and in
>>>>> general I was satisfied with the results. Great piece of software!
>>>>>
>>>>> Now here the scenario I want to happen: I have PHP scripts which deal
>>>>> with GnuCash data in MySQL DB. It is not a problem to read and show
>>>>> account records from MySQL. But PHP scripts should be able to post
>>>>> invoices back to GnuCash DB. This side of business is working well too
>>>>> but if I have GnuCash open and connected to the same MySQL DB no
>>>>> update in GnuCash happens. I've being looking for something what may
>>>>> get GnuCash to re-read records but I cannot find anything like
>>>>> it. Does MySQL backend has something like this? Perhaps having "last
>>>>> modified" in DB record would be good and GnuCash should check it
>>>>> periodically. Or the best way is to not to cache anything in memory
>>>>> and use MySQL for each single operation. Or I just dreaming? :)
>>>>>
>>>>> Generally it comes to concurrent use of the same DB by two separate
>>>>> GnuCash processes and how they get in sync. Without syncing use of DB
>>>>> does not gain much in relation to XML file.
>>>>>
>>>> This would be "multi-user support" which is not supported at this time.
>>>> As Phil says, it's also not on the current path so unless some developer
>>>> comes in and offers to implement multi-user I don't see this happening
>>>> any time soon.
>>>>
>>>>
>>>>> Regards,
>>>>> Vladimir
>>>>>
>>>>> PS: I have read warnings about modification of the data by something
>>>>> but GnuCash. Still it appears to be the best way to resolve my
>>>>> problem.
>>>>>
>>>> -derek
>>>>
>>>>
>>>>> gnucash-devel mailing list
>>>>> gnucash-devel at gnucash.org
>>>>> https://lists.gnucash.org/mailman/listinfo/gnucash-devel
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>>
>
> _______________________________________________
> gnucash-devel mailing list
> gnucash-devel at gnucash.org
> https://lists.gnucash.org/mailman/listinfo/gnucash-devel
> _______________________________________________
> gnucash-devel mailing list
> gnucash-devel at gnucash.org
> https://lists.gnucash.org/mailman/listinfo/gnucash-devel
>