Revisiting the database

[Gary Bickford]

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)

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.

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.

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.

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.

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.

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.

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.

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.

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.

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, 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.

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.

Well, that's my opinion :O)

--
"We feel that this change will be sufficient to discourage "hackers",
although it is obviously insufficient to protect a node against a
determined and malicious attack."
- RFC521 (ftp://ftp.isi.edu/in-notes/rfc521.txt), 1973

Gary E Bickford, mailto:garyb-at-fxt.com.
Web and content/asset management systems, PHP, XML, Apache, SQL
FXT Corp, http://www.fxt.com, tel. 541-383-2749