On Sat, Aug 8, 2009 at 6:07 PM, Phil Longstaff<[email protected]> wrote: > On August 8, 2009 04:26:48 am Marcus Wolschon wrote: >> Colin Law schrieb: >> > 2009/8/8 Marcus Wolschon <[email protected]>: >> > >> > 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 _______________________________________________ gnucash-devel mailing list [email protected] https://lists.gnucash.org/mailman/listinfo/gnucash-devel
