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

[stuartb]

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!

Thanks for listening!



--
View this message in context: http://gnucash.1415818.n4.nabble.com/Considering-a-fork-for-implementing-MySQL-C-library-multi-user-thoughts-tp4674947.html
Sent from the GnuCash - Dev mailing list archive at Nabble.com.