Adhoc Automating Using SQL For WordPress Migration

I have started to move the site over to WordPress, starting with the blog entries and during the conversion process I remembered to re-learn a little bit about SQL.

I studied relational databases at University but the days when I can knock up a left join insert with multiple renames, without thinking have passed me by.

I do find that in my old age I rely on query builders to help me generate some of the more complicated queries that I use during testing.

I hand crafted php files with automatically generated text data files to build my previous blog. With all of the blog data entry done in Compendium-TA using a VBA script to generate the files and the rss file, and generate an ftp script to upload it all to CompendiumDev.co.uk.

But I thought I’d get with the times and start to use a ‘proper’ blogging tool. So a quick hack to the VBA script and I generated an RSS file with all the posts in it. I imported the RSS file into WordPress. But because I have in the past written less than XHTML compliant posts, most of the posts generated had CDATA wrapped around them to generate a valid xml RSS file.

I started amending the posts in WordPress manually to remove the CDATA tags and after 5 edits thought “wait a minute why am I not automating this!”.

So a quick flick through the mysql manual and I identified the LEFT and SUBSTRING operators and voila…I could extract the contents of the CDATA information.

UPDATE 'posts'
SET post_content=left('post_content',length('post_content')-3) 
WHERE substring('post_content',1,9)='<![CDATA['; 

UPDATE 'posts' 
SET post_content=substring('post_content',10) 
WHERE substring('post_content',1,9)='<![CDATA[';

I could probably find nicer ways of doing it but I did enjoy remembering that SQL isn’t just for simple Selects and Inserts even though the bulk of my ’tester’ generated xml has required little more.

Of course I still have to manually go through all of the posts to check for rendering errors after uploading… but I’ll do that at my leisure over time - this seems ‘good enough’ to let me use the WordPress blog live.

I also had to hack the dates a bit - somewhere along the line I seem to have managed to update all the posts prior to April 2006 in April 2006 so It seems like I had a very busy writing month then.

UPDATE 'posts' 
SET post_date=ADDDATE('post_date',INTERVAL id SECOND) 
WHERE 'post_date'<'2006-04-26 22:00:02'; 

UPDATE 'posts'
SET post_date_gmt=ADDDATE('post_date_gmt',INTERVAL id SECOND) 
WHERE 'post_date_gmt'<'2006-04-26 22:00:02';

Hey ho - I fudged it so that the id gets added to the post date as a number of seconds otherwise WordPress stopped building ‘previous’ links on the blog posts since all the dates suggest that everything got posted at exactly the same time, which I guess WordPress by default doesn’t allow.

I have not amended the theme to match the main site properly yet, so as I move more of the site over to WordPress the look of the site will change anyway.

I did all the hacking in the live environment and tested by playing around - just because all good software developers and testers know, deep in their hearts of hearts, that I just described the one true way to do software development!