Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
MySQL Stored Procedure for generating Firebase Push IDs
/**
* 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.