On Tue, Apr 22, 2014 at 6:48 PM, Richard Hipp <d...@sqlite.org> wrote: > On Tue, Apr 22, 2014 at 12:37 PM, Neville Dastur >> 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.
Exactly. >> Has the time not come to support a 128bit INT that can then be used for GUID? Yet I don't see the point of a BIGINT either. A blob can effectively act as a arbitrary sized integer already, albeit one stored in base 256 and on which you cannot do arithmetic, but that's OK and enough to use it as a PK / FK. > "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.) Many people, including me, do refer to that one standard Simon already linked to. And it is 128-bit. > 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. But again, random GUIDs and (secure) hashes like SHA1 are different beats. To compute your SHA1, you take an arbitrary large "content", and process it to generate a hopefully unique but definitely not random "number". The same content must generate the same "number, every time. And changing just one byte of the content must generate an entirely different hash. While a random generated GUID "only" needs to be unique, generated out of thin air, and strive to never generate the same "number" twice. > 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... It is enough for all practical purposes, with a good RNG. > 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. I'm with you there. > 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. Sure. As you say, different applications have different requirements. We load entities from different completely unrelated datastores, and by using GUID PKs, we never run in collisions, unlike locally derived local ids. We can even have cross-datastore FKs using GUIDs, albeit not enforced like intra-datastore FKs can be of course. The decentralized nature of GUIDs, and it's practical uniqueness, do make it a good choice for PK/FK IMHO, while remaining short enough a key. Sure, 2 bytes beat 16 bytes (although with 2 bytes you're likely on the low end; we can have in the millions of rows in our DBs). Again, my $0.02. I was more reacting to James' anti-GUID paragraph ;) --DD _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
