[GNC-dev] [GNC] mysql backend, second user (lock, for example)

[Craig Arno]

On 11/6/2018 4:19 AM, John Ralls wrote:
> We intend to convert the XML backend to loading a SQLite3 in-memory
> database at session startup so that we can query against it instead of
> the current QOFQuery. I’m still working out how to handle the
> transition and how to prioritize it relative to GObject->C++ in the
> core engine objects. 
Glad to hear this.  I didn't want to propose this fundamental change
being the "new kid" with outrageous ideas.
I hope this change will make the rest of the GnuCash code consistent. 
Being able to use a text editor on "optional" XML is a nice alternative
to SQLite for users who aren't comfortable with databases and trying to
correct "deleted date-posted element" internal type problems.

> I hadn’t considered doing that as a shim for a server-based DB. I’m not sure that it would be a real benefit and it could get pretty ugly to implement. I think the shared SQLite3 file will work OK with a low-latency LAN file share (e.g. NFS or SMB), but I’m also pretty sure that it won’t for a high-latency share like Dropbox or Google Drive.
I'm mostly interested in the Usecase where a user is configured to use a
Server based database as their backend, then is disconnected from the
network and wants to keep working against the same database.  For me
this is usually travel and while Internet may be unavailable (might be
quite expensive, unreliable, and insecure).  Usually this means I have
dead time which can be used to catch up on paperwork as long as I'm not
the one piloting or driving our conveyance, or just wanting to look out
the window while I collect my thoughts.  This feature is what makes me
prefer GIT over SVN even though in reality I currently have to use
both.  What I'm trying to get away from is the SVN model where if there
is no server, no work can be performed.  GIT gets around this with a
local DB for work and push/pull for server updates.  I'm hoping for
something a little more automated to hide GnuCash internals from the
user.  GnuCash I suspect isn't operated by a highly technical audience,
as GIT is.  Details of working with local and remote databases could get
quite confusing for most users who's experience goes as far as
"thumbdrive" and "WiFi".

>  IIRC SQLite3 locks tables not rows, which is a serious limitation for multi-user uses
Good to know, thanks, but you also have all the SQLite code running on
the local machine for "peer" access which means you have the potential
to do pretty much anything you need.  Does ODB provide compensation for
individual record locking locally for SQLite?

Otherwise individual record locking could be provided in a local API
expander "peer helper" class which communicates with SQLite for local
record updates between ODB and SQLite.  Then if SQLite should later
implement record locking as part of their API this "helper" class could
be depreciated/removed.  This will help the ODB interface remain
consistent to GnuCash architecture, unless ODB already has a way to work
around the lack of individual record locking in SQLite v3.

It might be worth asking the SQLite team for a record locking API.  They
may tell you it's already on their roadmap, or ask "why" it should be? 
If asked "why", then you can share the peer-to-peer Usecase scenario.