Add support for ULIDs - CreateULID()

language proposals
1

Universally Unique Lexicographically Sortable Identifier

Basically, a high performance alternative to UUIDs

Proposal, POC and more info on the ticket, plz comment here, not on the ticket

https://luceeserver.atlassian.net/browse/LDEV-4481

6 Likes
2

been doing some initial testing, not seeing any huge performance benefits

however, the test tables with a ULID are 33% smaller than the UUID tables!!

EDIT: OMG

image
image1061×182 8.14 KB

image
image957×815 92 KB

image
image975×866 104 KB

2 Likes
3

Sounds nice. The only problem that could exists under certain circumstances: You want anonymous data records (like a vote or survey) and with the timestamp you could match the logins from users.

Would you migrate the code von CreateUUID() or would you make a switch/checkbox in the Application-Scope how the ID would be created?

4

createUUID isn’t going away, for exactly the reasons you highlighted

this would be an elective choice within your application, should you decide to use it

3 Likes
5

MySQL have a specific datatype for ULID?

PostgreSQL have a specific datatype for UUID; it performs 50% better than a normal char datatype.

6

Check out an article by @bennadel about using cuid2:

And the cuid2 readme itself:

It is a fascinating read.

2 Likes
7

I have created a sample extension

here’s a sample extension

ulid-extension-0.0.0.1-SNAPSHOT.lex (24.2 KB)

3 Likes
8

This was added to core
CreateUUID() :: Lucee Documentation since 6.0.1

1 Like
9

I have a default i use that calls a function/view for sql server also. There are a few on github also like this:

rmalayter/ulid-mssql: Implementation of ULID generator For Microsoft SQL Server

CRYPT_GEN_RANDOM+SYSUTCDATETIME component GENERATION TEST: 
     ids/sec: 367631
ulid() as UNIQUEIDENTIFIER GENERATION TEST: 
     ids/sec: 78119.9
newid() as UNIQUEIDENTIFIER GENERATION TEST: 
     ids/sec: 594870
ulidStr() as NVARCHAR GENERATION TEST: 
     ids/sec: 7554.73
ulid_seeded() as UNIQUEIDENTIFIER GENERATION TEST: 
     ids/sec: 97270.2
ulid() as primary key INSERTION TEST: 
     rows/sec: 28999.7
     avg_fragmentation_in_percent: 32.5901
newid() as primary key INSERTION TEST: 
     rows/sec: 61424.5
     avg_fragmentation_in_percent: 99.5643
newsquentialid() as primary key INSERTION TEST: 
     rows/sec: 68488.5
     avg_fragmentation_in_percent: 0
ulidStr() as primary key INSERTION TEST: 
     rows/sec: 6055.82
     avg_fragmentation_in_percent: 44.2871

Then I just use dbo.ulidStr() in the default for the column. ( you need to add the function and view to the current db first (or model db for all new dbs)

(couldn’t get the lucee one to work, maybe needs more docco)

10

you can also create a custom data type in sql server ( varchar(26) and save as ulid data type ) then you can apply dbo.ulidStr() to the default of the column if you need.

11

Zac, I’m sensing you maybe meant to point to the new function instead, right?

(I missed this thread when you offered this comment last month, and indeed I only saw it on seeing dawesi’s comments today, which led me to read the thread. Just clarifying the link for others who may also only now see the thread.)

Interesting stuff on ulid’s and more, from everyone offering their perspectives so far.

1 Like