Gnucash and root-account in MySQL

[Marcus Wolschon]

On Sat, Aug 8, 2009 at 6:07 PM, Phil Longstaff<plongstaff at rogers.com> wrote:
> On August 8, 2009 04:26:48 am Marcus Wolschon wrote:
>> Colin Law schrieb:
>> > 2009/8/8 Marcus Wolschon <Marcus at wolschon.biz>:
>> >
>> > 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
test-database.
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.

Status:
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
database-constraints).

> 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. :)


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

Marcus