Converting old LJ invite codes to DW invite codes

The format for DW invite codes is a bit different than the way LJ had invite codes (longer, harder to brute force, a better system IMO). Here is a simple way of converting your user's invite codes from the old to the new system without writing a single line of Perl, and using nothing but MySQL.

Warning: BACK UP YOUR ACCTCODE TABLE BEFORE YOU DO THIS!

DW invites are currently random character strings of length 13 made using a pool of 30 letters and digits (abcdefghjkmnpqrstvwxyz23456789).

Here is the MySQL query that convert , all on one line:

UPDATE acctcode SET
  auth=concat(substr("abcdefghjkmnpqrstvwxyz23456789",FLOOR(1+rand()*30),1),
  substr("abcdefghjkmnpqrstvwxyz23456789",FLOOR(1+rand()*30),1),
  substr("abcdefghjkmnpqrstvwxyz23456789",FLOOR(1+rand()*30),1),
  substr("abcdefghjkmnpqrstvwxyz23456789",FLOOR(1+rand()*30),1),
  substr("abcdefghjkmnpqrstvwxyz23456789",FLOOR(1+rand()*30),1),
  substr("abcdefghjkmnpqrstvwxyz23456789",FLOOR(1+rand()*30),1),
  substr("abcdefghjkmnpqrstvwxyz23456789",FLOOR(1+rand()*30),1),
  substr("abcdefghjkmnpqrstvwxyz23456789",FLOOR(1+rand()*30),1),
  substr("abcdefghjkmnpqrstvwxyz23456789",FLOOR(1+rand()*30),1),
  substr("abcdefghjkmnpqrstvwxyz23456789",FLOOR(1+rand()*30),1),
  substr("abcdefghjkmnpqrstvwxyz23456789",FLOOR(1+rand()*30),1),
  substr("abcdefghjkmnpqrstvwxyz23456789",FLOOR(1+rand()*30),1),
  substr("abcdefghjkmnpqrstvwxyz23456789",FLOOR(1+rand()*30),1)), 
  reason='Update from legacy invite system', 
  timegenerate=unix_timestamp() WHERE rcptid='0';

What we are doing is using substr (like LJ::make_auth_code) to nab a random letter between position 0-29 (or 1-30). Since there is no simple way to loop this substr and store it in an array (like Perl) in MySQL, we just use concat() and do the substr 13 times. We then update the reason stating that we are upgrading from the legacy code system, give a unix_time when we generated the codes (I just use realtime date), and only change the unused codes for nostalgia/accounting sake.

It looks like a horrible query, but you should only have to do this once (so there isn't any reason to write some horrible Perl script to calculate existing invite codes, wipe those, regenerate ones to replace them, etc).