Here’s a quick script I was playing around with this morning. I want to do some extensive string handling on all of the posts in my WordPress database. It made the most sense to me to pull each post’s content from the db, do the string mangling with a shell script and then replace the post_content field as I go. This script is destructive and any mistakes you introduce could kill your entire blog. Should you have a need for this script, back up first!
The script itself is really simple: it just pulls all of the post content and ids from the blog database, runs each one through your processing script and then sticks the result back in the post. The handler is empty here, it’s assumed that you’ll edit the function to call your own script to suit your needs.
#!/usr/bin/env ruby# Requires 'sequel' and 'mysql' rubygemsrequire'rubygems'require'sequel'require'cgi'defe_sql(str)str.to_s.gsub(/(?=[\\])/,"\\")enddefprocess_post(input)# do your thinginputenddefprocess_db(db_name='',user='',pass='',host='localhost',db_prefix='')db=Sequel.mysql(db_name,:user=>user,:password=>pass,:host=>host)db["select post_content, ID from wp_#{db_prefix}posts where post_status = 'publish' and post_type = 'post'"].eachdo|post|content=post[:post_content]content=e_sql(process_post(content))# run process_post on post_content, escape resultupdate_ds=db["UPDATE wp_#{db_prefix}posts SET post_content = ? WHERE ID = ?",content,post[:ID]]update_ds.update# replace the existing content with the new contentendend# process_db (dbname, username, password, host, dbprefix)process_db('database','user','pass','localhost','tableprefix_')
You’ll need to edit the process_db call in the last line with your mySQL database credentials and host info.
As an example, here’s a function you can put into the above script that will find all your inline links and turn them into reference links at the bottom of the post. Just for fun.
The script requires the ‘sequel’ and ‘mysql’ Ruby gems to run. I was able to install these on a Dreamhost server with no problem, but I’d recommend just dumping your database to a local mysql server, running the script and testing the result, then uploading it. Assuming you have a local testing environment with a MySQL server…
Anyway, doubt it’s of much use to most of you, but figured I’d put it out there.