The usual solution here is to have a table that maps GUIDs into small
locally-unique integers:
CREATE TABLE guid_id(id INTEGER PRIMARY KEY, guid TEXT UNIQUE);
Use the small integer "id" value for internal foreign keys and whatnot.
And use the guid_id table to map GUIDs to id when moving data in from and
out to the rest of the world.
On Tue, Apr 22, 2014 at 11:55 AM, Dominique Devienne <ddevie...@gmail.com>wrote:
> On Tue, Apr 22, 2014 at 5:35 PM, Drago, William @ MWG - NARDAEAST
> <william.dr...@l-3com.com> wrote:
> >>I myself prefer create table foo (guid blob primary key [NOT NULL], ...).
> >
> > If a genuine GUID looks like this: 37af1247-2e77-4880-8f46-48803ae2cd0a,
> then why blob and not text?
>
> Simply because of the extra space needed to store it. 36 bytes vs 16
> bytes. That's 20 wasted bytes for the PK, and everytime that PK is
> references in other tables' FKs too. Times millions of rows, it adds
> up, for nothing. The GUID is no less "genuine" as you put it, just
> because it's stored as a 16-bytes blob rather than the canonical
> 36-char text preferred by humans. The native code guids, e.g.
> boost::uuid, also use 16 bytes, so conversions to/from the DB
> (binding/defining) would required bytes-to-text, and text-to-byte
> conversions, again for no gain. If we ever show a GUID to the user,
> which is rather rare (and often a bug), sure, we pretty-print it as
> dash-separated hex, but otherwise the most compact "native"
> representation is used everywhere else. For ad-hoc queries using the
> shell-tool, SQLite's x'ff' blob-literal comes in handy too, and
> quote() [1] is used for blob-to-hex conversions in selects (nothing
> needed in blob-to-blob joins). --DD
>
> [1]
> http://stackoverflow.com/questions/1039461/how-to-display-blob-value-using-xabc-binary-string-literal-syntax
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
