Add a site id beside every serial

Hi, this is a good solution, but just a thought...

a 32b signed int is not a great amount of randomness. There are enough drupal sites out there that some will get the same siteid.

If the goal is to save database space by using an int(4) then my suggestion below will have little value, but I don't think that's the goal. A 32b number can be expressed in hex as 16 chars or much smaller in base36.

So a way to greatly decrease the chance that two sites would have the same siteid is to use the current (as of the time the siteid is generated) unix timestamp bit shifted left 16 bits then or'd with a 16b random number. Here's code to illustrate it:

function unique_siteid($print = FALSE) {
  $ts = time();
  $ran = rand(0, 0xFFFF);
  $siteid = ($ts << 16) | $ran;
  $short = base_convert($siteid, 10, 36);
  if($print)
    printf("ts: %s, ran: %s, siteid(long): %s, siteid(short): %s\n", $ts, $ran, $siteid, $short);
  return $short;
}

// just to show that it's unlikely to get the same result twice run it 25 times
for ($i = 0; $i < 25; $i++) unique_siteid(TRUE);

This gives the odds that two sites generated at the same computer time have a 1 in 65,535 chance of having the same site id. Site ids generated at different times have a 0 chance of having the same id.

In the example above my site id looks like this: ib8hhc

Short and sweet.

The point wasn't the size of the number but the chance that two sites will clash. If 1 in 2M (or even 4M) were astronomical odds then no one would buy lottery tickets.

Setting aside the issue of what exactly the site id is and how it is generated, I like this proposal a lot. It has a much easier upgrade path than replacing serials with GUIDs or mapping UUIDs, it is more performant than GUIDs would be, it has all the benefits that come with unique identifiers. So +1 for the idea.

I don't have a strong opinion on the what/how of the Site ID at the moment.

I actually came across this whole thread by chance at the right time. I wanted to work on cross-site synchronization and possibly off-line access a few weeks ago and the node ids were going to be a difficult challenge. Doing something like this makes life a lot easier. I think that having a high-certainty of uniqueness would only make life easier. Plus, displaying the 48b integer as base36 gives a very compact representation which is also handy (imho).

"a variant exists..." the nice thing about base36 is that it's built into PHP so no special code is needed. There is a wikipedia page on base36, but basically it's all the numbers and all the lower case letters of the ascii alphabet, which makes it case insensitive with no puncutation and is very URL friendly. The largest unsigned 60b number produces a 12 char 36b string, and obviously smaller numbers will be shorter.

print base_convert('0xFFFFFFFFFFFFFFF', 16, 36);

// 8rc4kbdvss1s

My interest is only in the uniqueness of the ID so I don't care if it's a 48 bit or 60 bit number, I think when combined with a timestamp as the most significant part of the number and the remaining part randomness (or psuedo randomness) then it will provide a high enough probability to give a unique number.

I tested my code above adjusted for 60b and it didn't work on my 32b machine. I'm sure something can be done to work around it though.

Regarding random.org, my hosts are firewalled and can't see the internet at large so be sure there's a fallback.

@chx - we have a D7 function - let's just use it since it's more than sufficient for this: http://api.drupal.org/api/function/drupal_random_bytes/7

Two remarks:

(1) we don't need the object identifier to be a number (so we don't really need to deal with > 32bit numbers in PHP, only their string representation),
(2) UUID can be made more efficient, for example by storing the spatial part first, and PostgreSQL has a nice UUID data type

If we want to follow this route, I would rather prefer that Drupal be the first common CMS to use UUID rather then building our own custom solution.

Discussed this a bit in IRC with Damien (DamZ), chx, and Greg Dunlap (heyrocker).

A relevant point to come out of this includes that object paths would almost certainly get a second part - like 'node/422174-1431840' or maybe 'node/422174-15d9z' depending on the column formats (see below). The upgrade path can still be pretty seamless, since existing links (e.g. form a 3rd party site or manually entered within site content) to http://example.com/node/422174 would be handled internally by just appending the current site ID for the missing part.

I also think the idea of assigning 0 as a default site ID is problematic - it should really be randomized out of the box, I think.

Damien suggests that using a string column might not cost too much in terms of performance. Needs real benchmarks, obviously. Also, we'd probably still have to retain a serial column on each primary table for generating that half of each object's ID. Making columns like nid strings does have one big advantage, in that relatively few of the queries or existing PHP logic would need to change (thanks to PHP loose typing).

While having two int columns might be faster for joins we'd have to rewite a ton of queries and logic to be of the form

select * FROM {node} n JOIN {node_revision} nr ON n.vid=nr.vid AND n.site=nr.site


I have yet to hear a persuasive argument for trying to generate real UUIDs - I'd rather work on a approach generally like this where the IDs are usefully unique. The MySQL article above points out that even in that simple test, longer string keys makes for worse performance, so if we were to use string keys, they should be as short as feasible to satisfy the goals.

Moving to 8.x-dev