SQL backend for GnuCash 2

[Phil Longstaff]

On Wed, 2006-25-10 at 11:29 -0400, Derek Atkins wrote:
> 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.

Well, the ID serves the same purpose as the GUID - to uniquely identify
the object.  I didn't see any purpose in duplicating the current GUIDs
inside the database.  I figured if we ever needed an interchange format,
the GUIDs could just be regenerated.  If we do need to keep the current
GUIDs, I can add guid_1 through guid_4 as ints.

> 
> 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;

I generated the initial DDL by looking at the XML tags in
src/backend/file/*-v2.c.  There is an act:lots, so I added it.  I think
I need to set up a test file with a number of different features (e.g.
business) so I can see how the XML is used.


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

I'm aware of the problem of plugability.  I need to look more at how the
business file code ties into the core file code.  It's a little
different with SQL than with XML.  With XML, if a user suddenly starts
using the business code (or some new plugin), that plugin just writes
its contents to the XML file.  With SQL, that plugin will need to check
if its tables exist, and if they don't, will have to create them.

Phil