Well, in my opinion the guy is an idiot.  The way to avoid the issues he is 
describing as the problems with serial IDs (or using the RowID) are simple to 
avoid:  DO NOT DO THOSE THINGS!  If you know that disclosing the RowID in a URL 
is ungood, then DO NOT DO THAT.  Sheesh.  As my good buddy Buggs Bunny would 
say "What a maroon!".

Nonetheless, I have created an SQLite extension for Windows (2000 or later) 
that will generate UUIDs using the builtin Windows RPC interface.  Apparently 
similar facilities are available on other OSes though all in different manners 
(different functions in different libraries).  Note that the silly proxies for 
the RPC functions are so that the compiler can maintain correct linkage to the 
RPC libraries when using function pointers -- the linkage through function 
pointers cast to (void*) works on 64-bit Windows but not on 32-bit Windows.  On 
32-bit windows not using a proxy function to maintain the correct linkage 
results in the stack frame corruption.

Also, uuid generation function for V1/3/4/5 are available in the Python 
standard uuid library, not mentioned in the article.

File is sqlfwin.c located in 
http://www.dessus.com/files/SQLiteExtensions.zip for the 32-bit Windows 
versions; or
http://www.dessus.com/files/SQLiteExtensions64.zip for the 64-bit Windows 
Source is the same in both, .dll compiled with MinGW (dependencies only 
to standard windows DLLs and to the subsystem runtime library).

SQLite3 UDF functions returning blobs (16-byte UUID) are:

And returning textual renderings are:

The create functions are volatile (like the randomblob function), and the 
To/From string functions are deterministic.

sqlfwin.c also contains some other Windows API functions for working with the 
builtin windows security such as looking up names and sids, checking whether 
the current process access token contains a given sid/name, getting the current 
process access token username, computername, FQDN, and a few others.

The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Simon Slavin
>Sent: Thursday, 30 November, 2017 07:16
>To: SQLite mailing list
>Subject: [sqlite] Article on AUTOINC vs. UUIDs
>Thought some of you might enjoy seeing this article.  I make no
>comment on what I think of the reasoning therein.  It’s set in the
>PostgreSQL world, but you could make an external function for SQLite
>which generates UUIDs.
>"Today, I'll talk about why we stopped using serial integers for our
>primary keys, and why we're now extensively using Universally Unique
>IDs (or UUIDs) almost everywhere."
>sqlite-users mailing list

sqlite-users mailing list

Reply via email to