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 users invite codes from the old -> new system without writing a single line of perl, and using nothing but mysql.

• • BACK UP YOUR TABLE BEFORE YOU DO THIS!**

The current DW invite does is a random character string of length 13 using a pool of 30 letters, digits (abcdefghjkmnpqrstvwxyz23456789).

Here is the totally ghetto (but works!) mysql query that will do this. Remember that this goes 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';

Yeah, thats ghetto.

What we are doing is we are 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 in mysql (and store it in an array like perl) 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).