Transaction logging and the dbi backend

[Geert Janssens]

Executive summary:
* The engine supports transaction logging
* For which backends should be enable this ?
* If also enabled for mysql/postgres: where to store the logs ?

Longer story with motivations:
My hacking attention is currently focussed on the gnucash transaction log 
feature. I don't have the time to fix all of its missing features [1] and 
considering we're working to finish the 2.4 stable release, I don't think 
that's where I should spend my time now.

However, there's one small aspect of it that caught my attention and I'd like 
to solve still: transaction logging is implemented in the engine, so it comes 
"for free" for all backends. Generally each time you open a data file, a 
timestamped log file is created next to the file.

The first issue pops up here. The way transaction logging is implemented 
assumes the user's data store has got a valid file name, which will be 
extended to create a log file. This obviously won't work for mysql or 
postgres. For that reason I chose to enable transaction logging only for file 
based backends (xml, sqlite) when I did my uri normalization work.

But I'm unhappy with that choice, because it's inconsistent. I see two 
potential uses for the transaction log:
* Data recovery in case of a crash
* Audit trail

For the first use, logging only makes sense for the xml backend. The dbi 
backend commits each change to the backend immediately and atomically (in 
theory at least), so the transaction log won't add benifits here. By the way 
both mysql and postgres have their own logging mechanisms that are more robust 
than the current transaction log we provide.

If the transaction logs serve to generate an audit trail, all backends would 
benefit from the transaction logging.

So there's question number one: should transaction logging be enabled for all 
backends or only for the xml backend ?

Suppose it is for all backends, this is easily implemented for xml (already 
there) and sqlite, because they match the 'data is in a file' assumption of 
the transaction log. But how to solve this for mysql and postgres ? There is 
no file associated with these backends, so it's not possible to use the 
filename as a basis for the log files.

Any comments ?

Geert

[1] See bugs https://bugzilla.gnome.org/show_bug.cgi?id=621075 and 
https://bugzilla.gnome.org/show_bug.cgi?id=621079 together with the bugs they 
are depending on.