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!
You will need a Github account to comment. Or you can contact me with your comment.
I reserve the right to delete spam comments e.g. if your comment adds no value and its purpose is simply to create a backlink to another site offering training, or courses, etc.