Difference between revisions of "Converting old LJ invite codes to DW invite codes"

From Dreamwidth Notes
Jump to: navigation, search
m
(a bit of clean up and categorization!)
 
Line 1: Line 1:
−
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.
+
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.
  
−
'''BACK UP YOUR ACCTCODE TABLE BEFORE YOU DO THIS!'''
+
{{Warn|text=BACK UP YOUR ACCTCODE 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).
+
DW invites are currently random character strings of length 13 made using a pool of 30 letters and digits (abcdefghjkmnpqrstvwxyz23456789).
  
−
Here is the totally ghetto (but works!) mysql query that will do this.  Remember that this goes on one line.
+
Here is the MySQL query that convert , all on one line:
  
−
update acctcode set
+
<source lang="sql">update acctcode set
−
auth=concat(substr("abcdefghjkmnpqrstvwxyz23456789",floor(1+rand()*30),1),
+
  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),
−
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',  
+
  reason='Update from legacy invite system',  
−
timegenerate=unix_timestamp() where rcptid='0';
+
  timegenerate=unix_timestamp() where rcptid='0';</source>
  
 +
What we are doing is using <tt>substr</tt> (like <tt>LJ::make_auth_code</tt>) to nab a random letter between position 0-29 (or 1-30).  Since there is no simple way to loop this <tt>substr</tt> and store it in an array (like Perl) in MySQL, we just use concat() and do the <tt>substr</tt> 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.
  
−
Yeah, thats ghetto.
+
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).
  
−
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.
+
[[Category: LiveJournal Conversion]]
−
 
+
−
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).
+

Latest revision as of 16:35, 9 September 2009

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).

Retrieved from "http://wiki.dreamwidth.net/wiki/index.php?title=Converting_old_LJ_invite_codes_to_DW_invite_codes&oldid=5812"