SQL backend for GnuCash 2

[Derek Atkins]

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.

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

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

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