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

[John Ralls]

> On Nov 7, 2018, at 3:16 AM, Craig Arno <craig at arno.com> wrote:
> 
> 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.
> 

Read about SQLite3 locking: https://www.sqlite.org/lockingv3.html <https://www.sqlite.org/lockingv3.html>. They’re locking virtual memory pages, totally independent of table or record structure. In practice what that means is that at the application level only using the SQL Transaction API makes sense, the application doesn’t have enough visibility of the internals to be able to implement finer-grained controls.

Your use-case for a SQLite3 shim reminds me of IBM’s old Remote Job Entry. It won’t work because someone else might change the server database while you’re working offline and unlike git SQL has no conflict resolution facility. Individual database servers often have a replication facility (I’m pretty sure Postgres does, MySQLs depends on the database backend--ISAM no, Berkeley DB yes, InnoDB don’t know) that can do limited conflict resolution, but it’s completely internal to the database engine. It won’t work between engines. In fact the only way I know of to transfer between engines is to dump SQL statements out of one and play them back into the other.

I think you’re expecting too much by calling a shared SQLite3 file a “peer to peer scenario”. It’s shared-file IPC. As I said before, it will work in a low-latency situation but will fail in a high-latency one like DropBox where file changes can take several seconds to propagate.

Regards,
John Ralls