rmalayter / ulid-mssql Goto Github PK

Right now the dbo.ulid() function returns a UNIQUEIDENTIFIER type, while in ULID implementations for most other languages ulid() returns the string version of a ULID. Refactor API of this implementation to be more consistent with others. It might be best to not have a "bare" dbo.ulid() function at all, and instead always include a "type" as part of the function name for clarity, e.g. dbo.ulidMSSQL() would return the MSSQL-sort-friendly specific format UNIQUEIDENTIFIER, and dbo.ulidChar() would return a CHAR(26) BASE32-encoded version. Add conversion functions as well.

Summary: [dbo].[ulidStr]() is generating incorrect ULIDs which seems to imply that there is a problem in your Crockford encoding. Additionally, I also found some issues in casting uniqueidentifier to binary(16).

Calling the [dbo].[ulidStr]() function just recently returned 05WHN5W2WHFXQM5W1PY3EN9XNR, which according to the NUlid library in C# Interactive is giving me a timestamp for [4/19/2175 5:47:10 PM +00:00], which is "weirdly close" but also more than a hundred years wrong to my system time (I'm using a local DB for testing). The time component from casting the uniqueidentifier to binary substring seems to be the correct portion of the uniqueidentifier which leads me to believe it is an error in Crockford encoding.

I discovered this in testing if I can round-trip the uniqueidentifier ULIDs through a version of [dbo].[ulidStr](@guid) that takes the uniqueidentifier instead of calling [dbo].[ulid]() to generate a new one. The other thing I discovered was the random bytes were even more scrambled than the timestamp. That lead me to discovering that CAST(uniqueidentifier as binary(16)) appears to include a "little endian" issue with the first three groups of the uniqueidentifier bytes getting swapped in binary order versus GUID string order. The substring for the random section of the uniqueidentifier needs to be more complex to take that into account to round-trip them properly.