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

[Craig Arno]

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.

Craig


On 11/5/2018 1:06 PM, Phil Longstaff wrote:
> I would assume postgresql and mysql would be more likely to provide
> this kind of notification because they have a central server. sqlite
> does not. I'm not sure how it could have 2 instances notify each other
> if they are just accessing the same sqlite file.