SQL backend for GnuCash 2

[Derek Atkins]

Quoting Phil Longstaff <plongstaff at rogers.com>:

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

Sorry, you can't.  The GUIDs MUST remain with the object.  They
uniquely define the object in the universe.  You cannot regenerate
them, because that object needs to maintain the same GUID forever.

You really should just use GUIDs and not integers.  Sorry.

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

That's fine.  The SQL doesn't have to map 1:1 with the XML..

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

This is true..  But we already need that kind of "version" code in order
to do table updates over time..  So we already need that kind of
"test the database" machinery.

> Phil

-derek

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