SQL backend for GnuCash 2

[Derek Atkins]

Phil,

Nice work so far.  I've got a few comments.

IDs:

The ID should be the GUID.  I don't think you can make that
an int per-se; although it IS a 128-bit number.  I don't think the
databases have 128-bit integer data types.

LOTS:

You probably want a Lots table.  The table would have the definition
of the Lot (LotID, Title, AccountID, and other information that's
stored in the Lot definition in the Account XML.  Then in the Split
you should just have a lot_id which is the reference to the Lot (if
any).

Account objects don't need any direct reference into lots.  You can
always do something like this to find the account from the lot:

  select * from Account where account_id=(select account_id from Lots where lot_id=xxx);

or this to find the list of lots for an account:

  select * from Lots where account_id=xxx;

SLOTS:

The PG Backend stores KVP Frames (slots) in their own table.  They
work well for XML because you can add arbitrary storage to any data
object without invalidating the XML Schema.  In other words, slots are
a completely forwards- (and backwards-) compatible data storage
method.  It also means that an object doesn't need to maintain it's
full data schema.  For example, some "plugin" can decide that it wants
to store additional information for an object; it can just store it in
the KVP slots.

So, you might want to do something similar to the PG Backend.  You
just have a general "slots" table for kvps and link it back to the
object by a GUID.  You never need to find an object by a KVP entry;
you're always looking INTO the kvp table FROM the object, so you don't
need the object type with the GUID.

BUSINESS:

The business XML "schema" should translate almost directly into SQL.

I realize you don't use the business code for your personal use, but
you might want to just create one or two of each type of object for
testing purposes.

I hope this helps.  I'm sure I'll have more to say.  :)

Oh, one other thing to keep in mind: We need a plugable architecture.
For example, we should have a means to plug-in the business code.  The
SQL backend should stand without the business plugin (and contain none
of the business tables)..  But with the business plugin that tables
should "appear" and work.  This means we might want to have a tighter
binding of SQL table name to QOF object name. 

-derek

Phil Longstaff <plongstaff at rogers.com> writes:

> On Mon, 2006-23-10 at 22:38 -0400, Josh Sled wrote:
>> It'll probably be the case that the SQL DDL statements will be easier to
>> work with than these pictures.  Just plain text like...
>> 
>>     CREATE TABLE accounts
>>     (
>>       id int NOT NULL,
>>       name String,
>>       parent int NOT NULL, -- fk(Account.id)
>>       [...]
>>     )
>> 
>> ...is better.
>
> I've attached a first cut at DDL for the core stuff.  It doesn't include
> slots, lots, some sched txn stuff.  I based it on the code which writes
> out XML.  I don't understand some of the interactions with pieces of the
> sched txn stuff or lots.  The contents of slots seems to me to be object
> dependent i.e budget slots will be different than account slots.  I also
> haven't looked too much at the business stuff yet.  I imagine I will
> tackle it in a similar manner to what I do now i.e. convert XML
> definitions to table definitions.  However, I don't use the business
> features, so won't really be able to test it.
>
> Phil
>
> _______________________________________________
> gnucash-devel mailing list
> gnucash-devel at gnucash.org
> https://lists.gnucash.org/mailman/listinfo/gnucash-devel

-- 
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       warlord at MIT.EDU                        PGP key available