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

[John Ralls]

> On Nov 6, 2018, at 4:47 PM, Craig Arno <craig at arno.com> wrote:
> 
> Phil,
> 
> I did more investigation of SQLite v3 today and found a few impressive
> things to summarize and pass along, which you may already know, but if not:
> 
> 1. SQLite is "/full featured/", providing almost all of the features of
>    a "Server" based database installation; *triggers* being the notable
>    one for this discussion
> 2. Stable, enduring file format
>    <https://www.sqlite.org/fileformat.html>.  Attention is paid to
>    backward compatibility.  If you pull a 30 year old SQLite database
>    file [GnuCash file] out of the archives, it should work just like
>    the day it was checked into the archives. Developer intent is to
>    support SQLite file backward compatibility through the year 2050. 
>    This is a feature I'd like in GnuCash
> 3. A SQLite database file is the recommended storage format
>    <https://www.sqlite.org/locrsf.html> by the US Library of Congress
>    for database files. The reasons in my mind place it up there with
>    "international standard ISO/IEC 26300 – Open Document Format for
>    Office Applications" used by LibreOffice/OpenOffice.  It still isn't
>    a real standard, but has some of the same desirable features, by design
> 4. ACID transactions, even after power loss
>    <https://www.sqlite.org/transactional.html>.  Atomic transactions
>    greatly reduce the possibility of database corruption or data loss
>    from power/system failure "fault tolerance".  Features desirable for
>    a financial application database, like I'd want for GnuCash.
> 5. Aviation-grade quality and testing
>    <https://www.sqlite.org/testing.html>.  I come from high reliability
>    "Aviation" and "Medical" development environments.  This is a
>    powerful statement about the software's ability to perform as intended
> 6. Zero-configuration <https://www.sqlite.org/zeroconf.html>. No
>    "login", "permissions", "processes", like there are in a server
>    application installation, yet #1 - it provides all the database API
>    features
> 7. SQLite can be 35% faster than direct filesystem I/O
>    <https://www.sqlite.org/fasterthanfs.html>
> 8. and of course, SQLite is cross platform
> 
> I found a non-commercial Windows ODBC driver
> <http://www.ch-werner.de/sqliteodbc/> with source for SQLite3 database
> files.  This let me open and work with a GnuCash SQLite3 database file
> like I currently can with MySQL and phpMyAdmin on my server.  I used
> LibreOffice-Base.  The peripheral tools are available today to support
> full development access to data contained in GnuCash SQLite3 files.
> 
> I'd like to see SQLite used as local database cache for GnuCash
> connection to a server based database, similar to how Git operates. 
> This will give a business user boarding an airplane the ability to enter
> a folder full of travel receipts into a local GnuCash database for
> upload/synchronization to a server based database when Internet access
> is restored.  This would also cover bad internet situations like Hotels,
> third world countries, and secure site (network blackout)
> installations.  And SQLite provides for full featured standalone GnuCash
> installations opening the possibility to connect to a GnuCash peer, for
> networked peer to peer database access to a single SQLite database
> residing on either machine using most of the same software as accessing
> a remote server installation.
> 
> I'm quite impressed with what I read about SQLite v3.

Yeah, SQLite3 is a pretty darn good job. It’s well written, well supported and consequently really widely used.

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.

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. IIRC SQLite3 locks tables not rows, which is a serious limitation for multi-user uses.

Regards,
John Ralls