Budgets ... again

[John Ralls]

On Sep 25, 2011, at 8:57 PM, Wm Tarr wrote:

> On 2011-09-18 15:17, Frank H. Ellenberger wrote:
>> Hi,
>> 
>> Am Montag, 12. September 2011 um 03:24:30 schrieb Wm Tarr:
>> :
>>> Extracting data from an SQL backend is much cleaner and just involves
>>> one sql script.  Essentially what it does is "normalise" GnuCash's
>>> sometimes obscure tables into ones that humans can easily understand,
>>> i.e. accounts become viewable as accounts, transactions become viewable
>>> as transactions, etc.
>> Just for couriosity: where is the normalization broken - with exception of
>> the "For backward compatibility use slots for new features" rule?
> Using the slots suggests bad design higher up the thinking process of the db design.  I am not here just to be a critic, Frank.  I *like* GnuCash.
> 
>> 
>>> I'm new to this list so I'm not sure if it is good manners to post the
>>> script here or provide a link.  Hopefully someone will let me know.
>> 
>> If you would link or add it to the wiki - you could create a page explaining
>> the issues you see and/or fix - it would not get lost.
>> 
> Has anyone put
> http://wiki.gnucash.org/wiki/SQL
> and
> http://cloud.github.com/downloads/jralls/gnucash/gnucash_erd.png
> together yet?
> 
> I don't see the point in repeating work someone else has done.
> 
> I am aiming at a dia [1] representation and an SQL script *including* relationships.  If someone else has already done this I need not waste my time repeating their process.  Thing is I don't think anyone has done it yet or if they have tried it didn't work because the underlying design may be broken <-- I don't know if that is true yet as I have yet to try.


The wiki SQL page is a dump of the table layouts, made by copying and reformatting the output of SQLite3's "table" command. I made the ERD by importing the same database, then working through the C code to discover how KVP slots were used and creating new tables to represent each use. Finally, I worked through the code again, looking for connections and documented them as foreign keys, and in a few cases "relationship" tables (I've forgotten the correct name for those. I mean tables consisting only of foreign keys, used to connect actual data tables.) to give some idea how the data objects interact. 

At this point the SQL database is just a store. Each real table corresponds to a data class (Account, Transaction, Split, etc.) and it's all loaded into memory when you open a file. Internally everything is done procedurally, with links between objects represented as pointers (which were created when the data was loaded from the backend). Edited objects are written back out to the SQL database when edits are committed, but there are still a few holes because of poor object discipline (meaning that there are cases where classes have KVP data that's attached to them by other objects, so they don't know to write it back out.

If you try to approach this as a SQL problem you'll just make yourself crazy. I have a plan to rework the engine (the module which does all of the accounting and business calculations) in stages. Stage one is to write comprehensive tests for each class. Stage two (which will interleave with stage one) is to overhaul each class to be a well-behaved GObject class with all access to data (including KVP) via function calls instead of passing out pointers for other objects to modify at will. Stage three will rationalize the interface. At that point it will be feasible to start discussing object responsibilities and separating what should be done inside the engine from what should be done in the database backends. 

Regards,
John Ralls