Alexis Turner

Comments, at last

(blog, mysql, wordpress — )

17 Feb. 2008

Okay, I finally bit the bullet and added comments to this thing. I noticed that my blog entry screen wasn't working any more. Did my host update PHP? I have no idea, but I decided the homebrew blog had served its purpose (making me practice my programming) and it was time to move to a "real" system. So, it might look the same, but you're actually staring at a shiny Wordpress blog right now.

Lest you think I wimped out by giving up the roll-yer-own blog system, I would like to say that I had a ton of fun playing with MySQL to transfer the old blog entries into the new database schema. Here's the outline, for anyone else finding the need to do this (I was annoyed to discover there is no info on doing this on the Wordpress site, so I'll stick it here for some desperate soul to stumble across):

1. I used mysqldump to dump the old blog posts and the wp_0d5m70_posts table from the new.
   Deciding to do this was a real stroke of inspiration, because I had no idea how I was going to keep the post IDs the same from the old to the new - which would have broken any outside links to specific entries (and, yes, there are at least a couple out there). But performing the mysqldump showed me a couple of cool little tricks I didn't know, including "ALTER TABLE `table-name` DISABLE KEYS." This command turns off auto incrementing while you add your old post IDs to the new table. At the end of the process, mysqldump enables the keys again, and voila! All new posts will increment correctly.
2. I cut the INSERT statement from the homebrew dump file into the wordpress dump file
   This was a huge line, so I used a little Linux trick that kept me from having to cut and paste. Basically, I found the line number of the statement in the homebrew file and ran the shell command 'head -line# homedump.txt | tail -1 >> wp-dump.txt'. This took out the single line I wanted and appended it to the end of the wp-dump file. Then I could just go in to the wp file and move it to the right place.
3. I edited the INSERT statement to only insert the columns that my table shared
   This was a simple matter of defining the column names being inserted. 'INSERT INTO `wp_0d5m70_posts` (ID, post_title, post_date, post_content) VALUES (blahblahblah). I didn't touch the values section of this statement any more than necessary because it was shit crazy long. Instead, I just told the insert statement what it was looking at that corresponded to the wordpress column names. Now, there was a single column I had that did not translate to the wordpress table. That could have been really ugly, but I got lucky because it happened to be formatted pretty uniquely. So I exited the text editor and used the command 'perl -p -i -e "s/find-this//g' to delete all occurrences of that field. If you have never used Perl pie before, you should seriously learn how, because it saves my ass all the time, and I barely even know how to use it. I can only imagine how rad it would be if I got good at it.
4. I added in a couple of new UPDATE statements to fill in the fields that my table never had.
   Listing every one is a pain in the ass, but basically I looked at the wp-dump file to identify all the columns being used and used the generic values like so "UPDATE `wp_0d5m70_posts` SET post_author=1, post_category=0, post_excerpt='', post_status='publish'....etc". The second update statement I used was pretty cool and I had never done this one before, but I used it for the guid column, which is the one that defines the permalink to the post. Since my permalinks involve the post ID, I used the CONCAT function like this: "UPDATE `wp_0d5m70_posts` SET guid=CONCAT('http://redheadedstepchild.org/lists/scratchpad/entry', ID);" That tacks the ID onto the end of the URL in the guid column. Pretty sweet.
5. Ran the commands in the edited dump file to change the table with my updates.
   This can be done by running mysql from the command line and redirecting the output of the file into it as commands. 'mysql tablename < wp-dumpfile.txt' If there are any errors, mysql tells you right away where the error existed and you can edit your dumpfile to fix it and try again.

This is definitely involved and roundabout, and it wasn't perfect (I have yet to figure out how to nicely transfer my post tags more quickly than it would take me to enter them by hand), but it was 8 trillion times faster than manually entering the posts via the Wordpress interface. I basically did it in something like an hour after I finished breakfast. All in all, I'm pretty pleased with how painless it was, but if you notice anything buggy about the transfer, let me know. In my new comments section.