Gnucash and root-account in MySQL

Marcus Wolschon Marcus at
Sun Aug 9 02:11:46 EDT 2009

On Sat, Aug 8, 2009 at 6:07 PM, Phil Longstaff<plongstaff at> wrote:
> On August 8, 2009 04:26:48 am Marcus Wolschon wrote:
>> Colin Law schrieb:
>> > 2009/8/8 Marcus Wolschon <Marcus at>:
>> >
>> > The root account guid is in the books table (column
>> > root_account_guid). This is causing me some pain as the type of the
>> > object referenced by parent_guid is not consistent.
>> Thanks, I found it.
>> I also have that data-type -issue and am working around
>> it with lots of special cases :( as I cannot save that account
>> and a special query to get the root-account with lots
>> of "select \"ROOT\" as account_type,...." .
>> Having a database but not even the possibility of referential integrity
>> causes me quite some headache. I'm even thinking about not supporting
>> mysql at all as I can't validate it anymore.
>> It also looses data in the xml<->mysql -conversion.
>> e.g. As the XML-model allows the root-account to have a commodity
>> but the db-model does not. This may sound like an extreme case
>> but that`s not how I`m comfortable writing software that deals with
>> money.
>> I dare not think what else I may find. As I`m dealing with the
>> one core software for my business I do extensive tests and validations
>> in every step (strong type safety with generics, parameters are checked,
>> null-checks everywhere, extensive validation uppon loading, ...
>> In the XML-model I`m even taking care that whitespace and the order
>> of elements is preserved exactly).
>> Thus I`m extremely uncomfortable with this design.
>> (Any idea what the template_guid is?)
> My memory is that the root account is a pseudo-account. I have no problem
> adding it to the accounts in the table. I may also have omitted it because
> it would require parent-guid = NULL, and I wanted to keep that check.
> So, proposal:
> 1) accounts table remove restriction that parent-guid != NULL
> 2) add ROOT account to the table with parent-guid = NULL

You do not have to remove the restriction "parent_guid != NULL"
if you define that an account that has guid = parent_guid is a root-
account. (If multiple of these exist, then there are multiple books.
They cannot lead to circles in the account-tree as they have no
parent and are thus never children of anything.)

Just a thought.

> Any other issues with the ROOT account?
> BTW, the template_guid is the guid for the template root account. This
> pseudo-account is similar to the root account, but it forms the root for the
> template accounts used for scheduled transactions. I can also add it to the
> accounts table.

Ah. thanks. My test-database did not yet contain any scheduled transactions
so I could not find that guid anywhere. If it is referenced from other tables
we should keep that record around. Again to be able to add foreign key-
constraints in the future.

I'll write my own implemenation with such restrictions in place on my
That way we can find out early if there are other details that would
cause problems
when they get added some time in the future.
In fact, I am thinking of adding the constraints and
enabling/disabling foreign-key
-checks for tha database uppon connect/disconnect in my code. However I`m
not yet sure how I may ensure that the disabling can be ensured even in case
of exceptions.

I can not read the tree of accounts using Spring-JDBC. Reading transactions
and splits will be an easy task now. Then I need to add calculation of balances
(in multi-currency environments) and that will complete a very simple viewer
to start with.

> Gnucash is not yet a real database program. It is changing from a program
> which keeps everything in memory but stores in XML into a program which
> keeps everything in memory but stores in an SQL database.

I am aware of that. My concern is that the option of having foreign keys should
be kept open for the future as a companys/natural persons financial data is too
important to not have an enforced integrity (be it a checked XML-schema or

> There are a number
> of NOT NULL restrictions which I have had to remove, or will remove, because
> of this. One known restriction is in the 'lots' table, where I removed the
> NOT NULL restriction on account_guid. This was because there are certain
> operations in gnucash which can result, temporarily, in a NULL account for a
> lot. In the future, I hope the restriction can be added again and the code
> changed. There is a problem creating a new Job which has a similar problem.
> Basically, the dialog code creates the object (and stores it) before all of
> the info is available. If I can modify it to create the object when the user
> clicks OK, it may solve that problem. However, my objective has been to get
> gnucash to use SQL for its data storage, and not necessarily to ensure that
> the database has perfect referential integrity (though I have kept that goal
> where possible).

Understandably. We should just keep the option of having that later.

> I'm happy to work with you if you have suggestions on how the db design can
> be improved. That is one of the reasons I pushed and created the 2.3.X
> series of releases.


As for the indexes, In noticed that there is a bug in the
database-client I used on
friday, forget what I said about it. :)

How would I find out what the default-currency is?


More information about the gnucash-devel mailing list