GUIDs (was Re: SQL backend for GnuCash 2)

[Phil Longstaff]

On Fri, 2006-27-10 at 12:01 -0400, Derek Atkins wrote:
> Phil Longstaff <plongstaff at rogers.com> writes:
> 
> >> 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.
> >
> > I am looking at having an int as the primary key for references to a
> > table.  The GUID would be stored with the row as 4 ints (guid_1, guid_2,
> > guid_3 and guid_4).  If we need to search a lot by GUID, we could have
> > an index which spans those 4 columns.  I don't see there is a lot of
> > need, though.
> 
> I still don't understand why you want to do this.  What does it buy
> us?  It seems to add a LOT of complexity on the GnuCash side when
> building up SQL queries.  Instead of just being able to print out
> "$table.${object}_id='$guid'" we'd need a much more complicated SQL
> generator routine.

I'm changing this to char(16).   I'm still concerned about 0x00 in a
GUID.  The SQLite documentation says that memcmp is used to compare char
strings, so that is OK, but I don't know about other db's.  MySQL
supports binary(16), but other db's may not.

Since the connection string will be db-specific, we may want a db core
built around libgda (see libgda vs libdbi e-mail) with a small add-on to
handle the connection string formatting and how guid's will be handled
(as well as adding db-specific indexes, stored procedures, ...).

Phil