SQL backend for GnuCash 2

[Mark Johnson]

Derek Atkins wrote:

>Mark Johnson <mrj001 at shaw.ca> writes:
>
>  
>
>>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.
>>    
>>
>
>I suppose we could use varchar; the guid has a fixed size so we know
>exactly how much space it requires.  It's just an MD5 hash, so it's
>128 bits, which is 16 bytes of binary or 32 bytes of Hexstring.
>  
>
Varchar has the advantage of being human-readable.  This may be very 
helpful during development.  One may have to issue ad-hoc queries to 
check whether something worked or to help find out what went wrong.

>  
>
>>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).
>>    
>>
>
>Honestly, for our usage I don't care so much about the database doing
>enforcement for us.  All (write) access to the database should be
>through the GnuCash API; the database is just a "data store".  There's
>lots of referential logic that CANT be implement in the database, such
>as enforcing balanced transactions.
>  
>
I would suggest the use of foreign keys during development at least.  I 
find it helpful in finding bugs in my code.  Once something is 
"debugged" it should never violate referential integrity, and one could 
drop the foreign keys for production code.  (For example, by using a 
different MySql storage engine with better performance on indexing GUIDs.)

>Besides, I don't care about performance of MySQL.  I DO care about
>performance in SQLite.  The GnuCash engine has no space to use a
>db-specific reference to an object; the engine always uses the GUID
>for that.  So all the queries are GOING to be based on GUID.
>
>You're welcome to add an auto-increment integer primary key, but the
>queries necessarily cannot use it because that information just isn't
>available in the engine.  C.f.: Database is just a data store.
>
>  
>
>>Hope this is helpful,
>>Mark
>>    
>>
>
>-derek
>
>  
>
Mark