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<plongst...@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 <vladi...@bashkirtsev.com> > To: Derek Atkins <warl...@mit.edu> > Cc: gnucash-devel@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 <vladi...@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 <vladi...@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@gnucash.org >>>>> https://lists.gnucash.org/mailman/listinfo/gnucash-devel >>>>> >>>>> >>>>> >>>> >>> >>> >> >> > > _______________________________________________ > gnucash-devel mailing list > gnucash-devel@gnucash.org > https://lists.gnucash.org/mailman/listinfo/gnucash-devel > _______________________________________________ > gnucash-devel mailing list > gnucash-devel@gnucash.org > https://lists.gnucash.org/mailman/listinfo/gnucash-devel > _______________________________________________ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
