Revisiting the database

[Robert Graham Merkel]

Gary Bickford writes:
 > Folks,
 > I have a fair amount of experience working with databases of all kinds
 > now.  My latest installation of SuSE 6.4 auto-installed MySQL and Webmin
 > to administer it and a bunch of other stuff.
 > 
 > The following may seem cranky, but it isn't. :O)  I just have a strong
 > feeling that the database idea should not be dismissed so easily.  To my
 > knowledge,  _all_ enterprise level accounting systems built on database
 > management systems.  So please take this as an attempt to be
 > constructive :O)
 > 
I think the essential point here is "enterprise level".  We are not
*yet* aiming at an "enterprise level" system.  We will, but we aren't.

 > First, using a database will not increase the maintenance and
 > administration headaches.  Perhaps if looked at from the position of the
 > present state of GNUcash, appending a database would do that.  But that
 > is because GNUCash is presently designed to work without an external
 > database.  I note that at present administration of GNUCash data is
 > essentially impossible because the resources haven't been available to
 > develop that component.  If it were built based on a simple database,
 > construction of the entire accounting system would be greatly
 > simplified.  Such a database, whether embedded or not, would be easier
 > to maintain than a one-off system that has no maintenance tools.
 > 
For a business, perhaps.  For a single user with limited computer
skills, a single file is a very reassuring thing.

Additionally, when we start depending on a database, the hassles we
currently get with different versions of guile across systems would
likely be multiplied twenty-fold.

 > Nearly all computers now essentially come with one or more databases
 > already running, from Access (fie!!) on MS (fie!! fie!!) and Filemaker
 > (Mac, PC) to MySQL and PostgreSQL on all platforms.   There are many
 > others.  If not already installed, their installation is generally
 > easier than the GNUCash installation.  In my experience, administration
 > of databases is as easy as pie for simple uses.
 
Think of Grandma.

 > Instead of programming a lot of complex stuff in C and Scheme, and
 > constructing a single-purpose database (that's what it really is) from
 > scratch, a database version of GNUCash would be simplified to three
 > pieces:
 > 
 >   1. The database engine (which automatically comes with access control,
 >      transaction support, rollback, etc., etc. depending on the
 >      database.
 >   2. The schema and query system
 >   3. The graphical front end
 > 
 > The second item, schema and query system, constitutes the business logic
 > of GNUCash.  All GNUCash operations would be reduced to a set of
 > queries.  New features could be added merely by adding new queries to
 > the query table, which can itself be retained in the database.  There is
 > a certain complexity in the metadata necessary to make queries
 > adaptable, but I've done equivalent systems and they're not very hard.
 > 
 > Reports as well as registers would be constructed easily from queries
 > into the financial data and queries into the report style tables.  The
 > process to add functionality to the system would be:
 > 
 >   1. Construct an accounting query, and test from the command line
 >   2. (perhaps) Evaluate the query with respect to valid business logic
 >   3. Construct a look-and-feel query, append to the accounting query and
 >      test
 >   4. Add to the query list and the menu list in the database.
 > 
 > This would result in a new report or capability in a few minutes (in
 > most cases) which would work seamlessly with any windowing system that
 > was supported by GNUCash.
 >
I think you considerably underestimate the complexity of some of the
reports one has to do.  Yes, a database takes away *some* of the work.
It doesn't take away all of it.
 
 > Such query-report development would not depend on GNUCash developers,
 > but could be done by fellow users and contributed to the product.  This
 > would greatly accelerate the development of the product, as the core
 > developers could concentrate on the basic tool set and the users could
 > contribute to building business logic based on their experience and
 > knowledge.
 > 
People can write 
 > Since the system is cleanly separated into three levels or modules,
 > integration with any GUI or windowing system would be simplified to a
 > set of basic operations that could be implemented for each windowing
 > system.  The primary complication would be restricting queries to valid
 > business logic.
 > 
We do that already anyway.

 > Because the user interface is now abstracted, it would be relatively
 > simple to build user interfaces on any windowing platform - even curses
 > or web browsers.  Using Jabber's XML protocol, queries could be
 > performed via XML messages from a cell phone.  All that would be
 > required would be to add a set of mappings from the query outputs to the
 > GUI messages for each system.  And _all_ outputs are in the form of
 > menus, dialogs or tables.
 > 
The user interface is *not* simple.  It is in fact very complex in parts.

 > As for performance, nowadays a majority of users are running 400 MHz or
 > better machines.  A fast database could perform almost any query
 > imaginable for this application in one or two seconds, even for
 > accounting systems with thousands of records.  Using MySQL I have run a
 > variety of queries including summing data from 100,000 rows of data that
 > still run fast enough to be almost invisible to the user.  Some
 > databases slow down on complex joins, but that would be necessary only
 > on annual reports and similar problems.
 > 

Agreed.  For the large-scale, a database is definitely the way to go.

 > Doing things this way would also eliminate the need to maintain the
 > bottom end of the package, which would be handled by the database and
 > enforce a clean modular programming model.  I believe that such as
 > system could be built in less time and with less effort than the present
 > one.
 > 
Some of the "bottom end".  There is a substantial amount of stuff that
I consider "bottom end" that isn't provided by a database.

 > Continuing with a one-off specialized database is a dead end and a waste
 > of time IMHO.  It also prevents integration of GNUCash with other
 > systems such as ERP without specialized plug-ins that again have to be
 > built from scratch.  An ERP system could interface with the database
 > directly using EJB or other standard SQL query tools, without requiring
 > any effort on the part of GNUCash.
 > 

Even if we end up using a database front end, the file format we have
will be extremely useful as an exchange, backup, and transport medium.

 > Another win with a commonly-used database is that it would come with its
 > own maintenance tools so users can make backups and do other work
 > without code written by GNUCash, 
They can do that now.  We didn't write cp and tar.

 > and its own team of folks assuring
 > reliability and robustness.  This effectively frees the GNUCash team to
 > work on the business logic and GUI.  It also enforces code separation.
 > 
We try very hard to maintain code separation right now.

 > I hope these thoughts are useful and provocative.  I am confident that
 > in the long run GNUCash will have to be built on a robust database
 > engine in order to provide the functionality required to be a 'real'
 > accounting package.
 > 
In the long run, yes.  Not yet.

 > Well, that's my opinion :O)
 > 

I appreciate your thoughts, and you're absolutely correct that
long-term, we've really got to go with some kind of database
approach.  However, for the here and now, the text file format is
considerably better than what we have right now, it's almost ready
to integrate with the main tree, and we'll continue to maintain a
separation between GUI and engine so that when the time comes, the
introduction of a database-driven back end won't be too painful.

------------------------------------------------------------
Robert Merkel	                           rgmerk@mira.net

------------------------------------------------------------