Lexicographically Sortable Unique IDs and Cryptographic Random in Clarion

  • 28 Sep 2024
  • 5-minute read

Using randomly generated unique IDs has many advantages over table-based auto-numbered fields.

The most common option is to use UUIDs/GUIDs, which are 128 bits long but commonly stored as a 36-byte string.

Another option available for Clarion programmers is to use StringTheory guids, which are 16-byte strings containing uppercase letters and numbers.

I see two disadvantages with random IDs:

  • There is no way to know in what order the records were added, which may be useful in many situations
  • They cause index fragmentation in SQL databases

A solution exists for UUIDs/GUIDs with UUIDv7, and there’s a Clarion implementation available in ClarionHub. However, I prefer StringTheory GUIDs since they’re more compact and easier to use in manual SQL queries.

I came up with the idea of using Clarion’s standard date and time to create a large number that represents the current date and time in hundredths of seconds since December 28, 1800, that is, a Clarion standard date/time (similar to Unix time), encode it using base 36 to get the first 8 characters of the ID, then fill the remaining characters with random base 36 digits. I’ve uploaded an implementation and sample project to GitHub (tested with C11).

To integrate into your application:

  1. Copy MakeSGuid.inc and MakeSGuid.clw to accessory\libsrc\win or your app folder.
  2. Add INCLUDE('MakeSGuid.inc'),ONCE to your global map

Some comments about the code:

!!! <summary>
!!! Creates a lexicographically sortable unique id with uppercase letters and digits. Example: 90DKZP0B6WYLIFYZ
!!! </summary>
!!! <param name="pLength">The length of the id. Must be between 8 and 32, default is 16</param>
!!! <param name="pDate">Optional, used only when creating an id for old data</param>
!!! <param name="pTime">Optional, used only when creating an id for old data</param>
MakeSGuid           PROCEDURE(LONG pLength = 16,LONG pDate = 0,LONG pTime = 0)!,STRING
systemTime            LIKE(_SYSTEMTIME),AUTO           !System date/time
hundredthsPerDay      GROUP;LONG(8640000);LONG;END     !24*60*60*100 (one day in hundredths of a second)
dayInt64              LIKE(INT64),OVER(hundredthsPerDay) !As int64
base36Constant        GROUP;LONG(36);LONG;END          !Base 36
base36Int64           LIKE(INT64),OVER(base36Constant) !As int64
timestampInt64        LIKE(INT64),AUTO                 !Date/time in hundredths of a second since Dec 28, 1800
timeInt64             LIKE(INT64),AUTO                 !Time value
modResult             LIKE(INT64),AUTO                 !Modulo result
result                STRING(32),AUTO                  !Generated ID
position              LONG,AUTO                        !String position
base36Digits          STRING('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') !Base 36 encoding table
timestampDigits       EQUATE(8)                        !Base 36 digits for date/time (valid until 2694)
randomBytes           STRING(24),AUTO                  !Crypto random bytes
randomByteArray       BYTE,DIM(SIZE(randomBytes)),OVER(randomBytes) !As byte array
  CODE
  IF pLength < timestampDigits THEN pLength = timestampDigits. !Minimum length check
  IF pLength > SIZE(result) THEN pLength = SIZE(result).!Maximum length check
  IF NOT pDate
    GetLocalTime(systemTime)                            !Get system time
    pDate = DATE(systemTime.wMonth,systemTime.wDay,systemTime.wYear)  !Convert to Clarion date
    pTime = systemTime.wHour * 360000 + systemTime.wMinute * 6000 + systemTime.wSecond * 100 + systemTime.wMilliseconds * .10 + 1 !Convert to hundredths of a second
  END
  i64Assign(timestampInt64,pDate)                       !timestampInt64 = pDate
  i64Mult(timestampInt64,dayInt64,timestampInt64)       !timestampInt64 *= day
  i64Assign(timeInt64,pTime) ; i64Add(timestampInt64,timeInt64,timestampInt64) !timestampInt64 += pTime
  LOOP position = timestampDigits TO 1 BY -1            !Convert to base 36 (reverse order)
    i64Mod(timestampInt64,base36Int64,modResult)        !modResult = timestampInt64 % 36
    result[position] = base36Digits[ modResult.lo + 1 ] !Get encoded digit
    i64Div(timestampInt64,base36Int64,timestampInt64)   !timestampInt64 /= 36
  END
  IF pLength > timestampDigits
    BCryptGenRandom(0,randomBytes,pLength - timestampDigits,BCRYPT_USE_SYSTEM_PREFERRED_RNG) !Get random bytes
    LOOP position = timestampDigits + 1 TO pLength      !Add random digits
      result[position] = base36Digits[ ( randomByteArray[ position - timestampDigits ] % 36 ) + 1 ] !Convert to base 36 (slight bias acceptable)
    END
  END
  RETURN result[1 : pLength]                            !Return clipped ID

On my laptop (modern i9) it creates one million GUIDs in 1.1 seconds. The initial version used DECIMAL for large number operations. Switching to int64 increased speed by roughly 50x. Adding AUTO to the local variables reduced the time in half.

Clarion 11 i64.inc doesn’t include the prototype for i64Mod, but I found it exists in ClaRUN.dll. Copying the prototype from i64Div worked. svapifnc.inc doesn’t include GetLocalTime, but the prototype for GetSystemTime also worked. Both prototypes are in the module’s MAP.

Initially, I interrupted the first loop using IF i64Is0(dt64) and performed string operations to left-pad the ID with zeros. However, it turns out that not breaking the loop achieves the same result with less code and is likely faster.

For many applications, it may be enough to use a length of 12 (8 bytes for date/time and 4 bytes of random), but in my tests of creating one million queue records in a few seconds, there were some duplicates. Using the default length of 16 worked reliably.

Using MyId = MakeSGuid(8, MyDate, MyTime) is a way to encode date and time in a single sortable string field. It’s not very practical for databases since it’s not easily readable, but it could be useful for some in-memory, queue-based algorithms.

Update June 15, 2025

A user reported collisions when adding records to a database using multiple threads. After running a few tests, I discovered the problem was Clarion’s RANDOM() function, which apparently uses a simple call to rand that doesn’t work well with multithreaded code.

The fix was easy: use BCryptGenRandom. With this change I tested adding 1,000,000 records in 100 threads to a memory file in a few seconds without collisions.

Update June 16, 2025

Now that we have BCryptGenRandom working in Clarion, we can easily leverage the fact that a LONG is 4 bytes and use BCryptGenRandom to fill those bytes with cryptographic-quality random data to generate a random integer, then scale it to a specific range, just like Clarion’s built-in function. The code is simple:

!!! <summary>
!!! Returns a cryptographically secure random integer.
!!! </summary>
!!! <param name="low">A numeric constant, variable, or expression for the lower boundary of the range.</param>
!!! <param name="high">A numeric constant, variable, or expression for the upper boundary of the range.</param>
!!! <returns>A random LONG integer between the low and high values, inclusively.</returns>  
cRandom PROCEDURE(LONG low, LONG high)!,LONG
randomData STRING(4),AUTO
randomLong LONG,OVER(randomData)
range LONG,AUTO
  CODE
    BCryptGenRandom(0,randomData,4,BCRYPT_USE_SYSTEM_PREFERRED_RNG) !Get 4 cryptographic random bytes
    range = high - low + 1
    randomLong %= range
    randomLong += low
    RETURN randomLong

Performance testing shows cRandom is approximately 24 times slower than RANDOM, making it useful for scenarios where random distribution is more important than speed.

Code is available on GitHub.

To integrate into your application:

  1. Copy bcryptrandom.lib to accessory\lib or your app folder
  2. Copy cRandom.inc and cRandom.clw to accessory\libsrc\win or your app folder.
  3. Add INCLUDE('cRandom.inc'),ONCE to your global map

This content was originally published on Clarion Hub 1 and 2.

clarion