Gnucash and root-account in MySQL

[Phil Longstaff]

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

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.

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

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.

Phil