On August 8, 2009 04:26:48 am Marcus Wolschon wrote: > Colin Law schrieb: > > 2009/8/8 Marcus Wolschon <mar...@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 _______________________________________________ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
