Considering a fork for implementing MySQL C library (multi-user) - thoughts?

[John Ralls]

> On Jan 7, 2015, at 10:50 AM, stuartb <stuart at buckhill.co.uk> wrote:
> 
> Hi GNU team,
> 
> We have been looking for an open source accounting system to connect to, to
> provide additional functionality we would rather not have to code ourselves
> (reconciliation, all the reporting, etc) as we have a system producing
> invoices, transactions and so on, using double-entry principles.   
> 
> We are considering using a small middleware component to map our tables and
> your SQL tables, and after investigating the DB structure this seems pretty
> straight forward to do.   This component would double up as suitable for
> mapping to any other accounting system in the future if our customer(s)
> require us to connect to anything else - but for small business situations,
> GNU seems perfectly suitable.   We would rather not re-invent the wheel.
> 
> We considered a workflow something along the lines of:-
> 
> Our system -> Create new invoice -> automatically generate commission ->
> create the various transactions required (automatic journal posting).
> 
> Our system would create a unique invoice ref and then create a sync job,
> ready to copy into the gnucash database - generating the GUID's and
> following the data structure to ensure data consistency.
> 
> After a quick review, these are the initial problems we would need to
> overcome:-
> 
> 1. Not loading entire dataset at load of application.  Our system is some
> instances generates a significant number of invoices, with millions of
> individual transaction postings - we can't load the entire dataset into
> memory every time someone opens the application.  It must read from the
> database in real time and "JIT" load data from the MySQL database.   I
> believe we may be able to achieve this with some sort of DAL which would sit
> in front of GNU or use the MySQL client library and change the code to query
> the data - without further investigating I am not sure of the potential work
> required here.  Please feel free to throw your thoughts and ideas across.
> 
> 2. Not saving entire dataset on-save, but only the changes made, in real
> time.
> 
> 3. Adding support for the MySQL client library for the above and generating
> the queries needed to read and write the necessary data?
> 
> 3. Adding foreign key constraints to the database to avoid data corruption
> (should be simple to do)
> 
> 4. Making use of MySQL triggers to create transaction safe auto-incremented
> numbering for invoices/credit notes, etc
> 
> 5. Support creation of transactions from gnu back to our system, not just
> from our system to gnu
> 
> Other notes:
> 
> We noticed the use of GUID frequently being used.  This should make our
> lives easier regarding syncing.  It should be possible to add a
> "last_updated" column in each database table and then use MySQL triggers to
> update this on change.  In our applications we always use a "date_created" +
> "date_modified" col for every table, with full datetime info stored to allow
> us to track changes.  In theory we could implement this without touching gnu
> code and benefit from this additional information in the case of multiple
> writes.
> 
> Why would we do this?:
> 
> Due to the nature of our application (typically tens of thousands of
> transactions a month per instance), we have created a bespoke highly
> automated approach to accounting which avoids any manual posting for day to
> day working.  We never set out to create an alternative to Quickbooks/Sage -
> our accounting module was simply created as a means to an end.  However our
> customers are so fed up with Quickbooks and Sage (and their own legacy
> in-house systems dating back 30+ years in some instances) they are piling on
> the pressure for us to further develop our accounting module so they can
> avoid using said packages.   When investigating their business requirement
> it turns out we would need to re-create a lot of the functionality found in
> gnucash/qb/sage (reconsolidates, reporting, expenses, etc) - which we would
> rather avoid.  Only accountants in our customer's organisations require this
> data and they will be happy to use gnucash to access this additional
> information.  General viewing of invoices and transaction data can continue
> to happen in our own system via a web interface for other users.
> 
> Catch 22;  do we invest the time branching gnucash and creating an
> abstraction layer to MySQL to allow us to have a multi-user concurrent
> active-active environment, or do we start developing our own platform to
> replicate all the years of hard work which have been invested in an existing
> solution?
> 
> I'm posting this topic hoping for gnu dev's to leave their thoughts on what
> might await us if we embark on the branch -> develop route!
> 

First of all, it GnuCash, not GNU. GNU is a large collection of software maintained under the auspices of the Free Software Foundation, with which we have no relationship other than using the license that they promote.

Speaking of the license, GnuCash is Free Software licensed under the GNU General Public License, version 2 or later. I bring this up because you seem to be a for-profit entity and there are provisions of that license that you or your counsel might find objectionable: Specifically that you must make any derivative work available as source code to anyone to whom you distribute and that you may place no restrictions on their right to copy it and distribute it as they like other than that they must also comply with the provisions of the GPL. Note that "derivative work" includes one that links any GnuCash library. (There's another GNU license, the "Lesser General Public License", which specifically makes an exception for linking libraries, but GnuCash was never intended for that kind of use and so isn't licensed that way. This applies regardless of whether you fork GnuCash or work with us.

This can probably be worked around by having GnuCash and your paid applications use a common database with independently developed libraries accessing it, but I encourage you to discuss it with your IP counsel before proceeding.

With that out of the way, on to the technical issues. GnuCash's current structure pretty much depends on loading the whole database into memory, which you say isn't feasible for you because your dataset is too big. We have just begun a project to move to a real database-based architecture because one of the most common requests from our users is multi-user access and that's not possible with the current design. That conversion addresses your items 1-3, which are all facets of database-based application design. 

Database triggers are a bit of a problem because of the requirement to support different databases and the fact that triggers tend to be non-portable. GnuCash currently supports 3 database backends (SQLite3, MySQL, and Postgresql) at present via an abstraction library, currently libdbi [1]. We intend to keep it that way; indeed, for most of our users SQLite3 is the preferred choice because it works without a server. However, GnuCash has a plugin facility that would allow you to write a module for your customers that would catch triggers from the db and emit GnuCash signals, and there's no reason you couldn't use triggers on your side when GnuCash makes changes to the common database.

GnuCash has a "Date Entered" field on transactions. It allows transactions to be modified but there's an optional setting that prevents modification after a user-configurable number of days. That facility can easily be made to prevent any modification once the transaction is committed to the database, which is usually required in a corporate environment. A "date modified" field would be redundant in that situation. The other requirement in a corporate environment is traceability: Each transaction entered must be tagged with who entered it. GnuCash doesn't have that and we have no plans to add it. You might be able to do so with a plugin, but that might not stand up to the scrutiny of your customer's auditors. 


Regards,
John Ralls

[1] http://sourceforge.net/projects/libdbi