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;