MySQL Stored Procedure for generating Firebase Push IDs

GenerateFirebasePushID.SQL

/**
 * Fancy ID generator that creates 20-character string identifiers with the following properties:
 *
 * 1. They're based on timestamp so that they sort *after* any existing ids.
 * 2. They contain 72-bits of random data after the timestamp so that IDs won't collide with other clients' IDs.
 * 3. They sort *lexicographically* (so the timestamp is converted to characters that will sort properly).
 * 4. They're monotonically increasing.  Even if you generate more than one in the same timestamp, the
 *    latter ones will sort after the former ones.  We do this by using the previous random bits
 *    but "incrementing" them by 1 (only in the case of a timestamp collision).
 */
 
 /**
  * USAGE:
  * 
  *     SET @lastPushTime = 0;
  *     SET @lastRandChars = '';
  *     SET @pushId = '';
  *    
  *     CALL GenerateFirebasePushID(@lastPushTime, @lastRandChars, @pushId);
  *     
  *     SELECT @pushId;   
  * 
  * All three arguments are passed-by-reference, this allows the procedure to modify the user-defined variables passed
  * as arguments.
  */
  
 /** 
  * DEPENDENCIES:
  *     getUnixTimestampInMilliseconds() - https://gist.github.com/DimuDesigns/6a27fbe27b1b400b6de1f85d23e02548
  */
  
CREATE PROCEDURE `GenerateFirebasePushID`(
    INOUT lastPushTime BIGINT,          /* Timestamp of the last push, used to prevent local collisions if you push twice in one ms. */
  
    INOUT lastRandChars VARCHAR(255),   /* We generate 72-bits of randomness which get turned into 12 characters and appended to the
                                         * timestamp to prevent collisions with other clients. We store the last characters we
                                         * generated because in the event of a collision, we'll use those same characters except
                                         * "incremented" by one.
                                         */
  
    INOUT pushId VARCHAR(20)
)

BEGIN

  /* Modeled after base64 web-safe chars, but ordered by ASCII. */
  DECLARE PUSH_CHARS VARCHAR(64) DEFAULT '-0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_abcdefghijklmnopqrstuvwxyz';
  
  /* Get unix time stamp in milliseconds - https://gist.github.com/DimuDesigns/6a27fbe27b1b400b6de1f85d23e02548 */
  DECLARE now BIGINT DEFAULT getUnixTimestampInMilliseconds();
  DECLARE duplicateTime ENUM('true', 'false');
  DECLARE timeStampChars VARCHAR(20) DEFAULT '';
  DECLARE i INT;

  SET duplicateTime = IF(now = lastPushTime, 'true', 'false');
  SET lastPushTime = now;
  
  SET i = 8;
  WHILE i > 0 DO
      SET timeStampChars = CONCAT(timeStampChars, SUBSTRING(PUSH_CHARS, (now % 64) + 1, 1));
      SET now = FLOOR(now/64);
      SET i = i - 1;
  END WHILE;
  
  IF duplicateTime = 'false' THEN
      SET lastRandChars = CONCAT('', FLOOR(RAND() * 64) + 1);
      SET i = 0;

      WHILE i < 11 DO
          SET lastRandChars = CONCAT(lastRandChars, ',', FLOOR(RAND() * 64) + 1);
          SET i = i + 1;
      END WHILE;
  
  ELSE
      /* If the timestamp hasn't changed since last push, use the same random number, except incremented by 1. */
      SET @val = CAST(SUBSTRING_INDEX(lastRandChars,',', -12) AS UNSIGNED);
      SET @temp = CONCAT('', IF(@val = 64, 1, @val) + 1);

      SET i = 11;
      
      WHILE i > 0 DO
          SET @val = CAST(SUBSTRING_INDEX(lastRandChars,',', -i) AS UNSIGNED);
          SET @temp = CONCAT(@temp, ',', IF(@val = 64, 1, @val));
          SET i = i - 1;
      END WHILE;
      
      SET lastRandChars = @temp;
      
  END IF;
  
  SET i = 12;
  SET pushId = timeStampChars;

  WHILE i > 0 DO
      SET @val = CAST(SUBSTRING_INDEX(lastRandChars,',',-i) AS UNSIGNED);
      SET pushId = CONCAT(pushId, SUBSTRING(PUSH_CHARS, @val, 1));
      SET i = i - 1;
  END WHILE;

END;

Thanks for this gist and for sharing it.

I realize that it has been years, sorry about that. I am checking if I can use these pushid in a project, and in this Sql version I have noticed some discrepancies in the results compared to the Javascript and Php versions.

Seems to me that there is at least one problem with the order of the characters (and from what I understand the first characters should be those associated with the timestamp, which can be useful to sort chronologically)

I'm trying to figure out why but my sql is pretty basic.

The result of some tests (Click to expand)

js: new Date().getTime();
1626428055131

php: php -r 'echo (int) abs(round(microtime(true) * 1000));'
1626428086560

sql: SELECT getUnixTimestampInMilliseconds()
1626428284044

js: generatePushID()
"-Meifjgi5a9bXySR7Nun"
...
"-MeigT5QnuGEm0ARQ52W"
"-MeigTCn56haNrZ-5o9q"
"-MeigTIBWvY8tybOHjD7"

php: artisan pushid:gen
-Meifpa6gtkwl-x21LpB
...
-MeigcO-hXXI75LIpHJ7
-MeigcSYZ5n02oRQ8yE6
-MeigcWT1G3p_6Oq3oXY
-Meigc__W3WNJBDsJeAV
-MeigcqGhRygXqVR4ctC

sql: SET @lastPushTime = 0;
SET @lastRandChars = '';
SET @pushId = '';
CALL GenerateFirebasePushID(@lastPushTime, @lastRandChars, @pushId);
SELECT @pushId;

op7gieM-tdYpYFdWcbCB
j2KgieM-tUodlyYrgsK
ZjMgieM-2xdO4CnQjS42
9pPgieM-wIgZmFx2Nkcv

EDIT

Oh well, this was easy...

-      SET timeStampChars = CONCAT(timeStampChars, SUBSTRING(PUSH_CHARS, (now % 64) + 1, 1));
+      SET timeStampChars = CONCAT(SUBSTRING(PUSH_CHARS, (now % 64) + 1, 1), timeStampChars);

EDIT

I believe there is the same problem with the remaining characters, when there are two identical ids usually the last character should be the incremented one, while here it is the ninth

-Meixm2mb9vA768QgBtt
-Meixm2mc9vA768QgBtt

-Mej-e9V_sa1qkcnS9SM
-Mej-e9Vasa1qkcnS9SM

EDIT

+    SET @temp = '';
     WHILE i > 0 DO
         SET @val = CAST(SUBSTRING_INDEX(lastRandChars,',',-i) AS UNSIGNED);
-        SET pushId = CONCAT(pushId, SUBSTRING(PUSH_CHARS, @val, 1));
+        SET @temp = CONCAT(SUBSTRING(PUSH_CHARS, @val, 1), @temp);
         SET i = i - 1;
     END WHILE;
+    SET pushId = CONCAT(pushId, @temp);