On Tue, Apr 22, 2014 at 12:37 PM, Neville Dastur <[email protected]>wrote:
> > On 22 Apr 2014, at 17:33, Richard Hipp <[email protected]> wrote: > > > 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. > > Sorry, but does this not just over complicate the problem. > I guess it depends on the problem. The same idea is used in the schema for Fossil (http://www.fossil-scm.org/) and it works quite well there. But every problem is different. It's an engineering judgement. > I would hazard a guess that most mobile apps that use an internal DB, use > sqlite. With inconsistent mobile network coverage, having pure client side > PK generation is a must and GUIDs solve that problem well. Has the time not > come to support a 128bit INT that can then be used for GUID? > "GUID" means different things to different people. There are some "GUID" standards out there that people use. But I take a more flexible approach and say that a "GUID" is any "Globally Unique IDentifier". This generalized definition of "GUID" is not necessarily 128 bits (though I would argue 128 bits should be the bare minimum.) Fossil generates some of its "GUID"s using the SHA1 hash algorithm. Other GUIDs (for example for ticket IDs) are generated using: SELECT lower(hex(randomblob(20))); You can increase the 20 to make the GUIDs as "globally unique" as you want. The GUIDs discussed previously in this thread seem use 16 instead of 20 and thus are less unique. So a 128bit int really isn't going to help here because as soon as you have one, you'll need a 160bit int. And so forth... Better to simply use a BLOB which can have arbitrary length. You aren't going to be adding and subtracting the GUIDs, so no need to call them integers. The average length of the derived, locally-unique identifiers in Fossil is about two bytes. That is more compact than 16 or 20 bytes, regardless of whether you call it a BLOB or an INT. So having a BIGINT capability doesn't really help you there either. -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
