Friday, 28 December 2007

Moved to WordPress

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!

No comments:

Post a Comment