SQL backend for GnuCash 2

[Mark Johnson]

Derek Atkins wrote:

>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.
>  
>
>  
>
I may be getting a bit ahead of the play here, since you are talking 
about design still, and I am thinking about implementation of it in a 
MySql backend.

I was recently doing some research on storing GUIDs in MySQL.  There is 
no guid type in MySql.  There seems to be two different ways people 
store them - as a string (varchar) and as a varbinary.

Currently, (MySql 5.0.x) only the InnoDb storage engine enforces foreign 
keys.  This enforcement is especially important during development, when 
one expects to find and fix referential integrity bugs.  However, using 
a GUID as a primary key in InnoDB has poor performance - the indices get 
very large.  The recommendation for working around this is to use an 
auto-increment integer as the primary key and a guid as a secondary key.

Also, MySql has a bug which recently cost me some time.  Foreign keys 
expressed as column constraints rather than table constraints are 
silently ignored!  It has been reported multiple times (MySql bugs 
11049, 7427, 4919, 13301, 18917 and probably more).

Hope this is helpful,
Mark