Rows and Columns

At the end of my last post regarding blog creation, I had gotten to the point of creating the Wordpress blog based on SQL Anywhere. Unfortunately, the admin. console didn’t work and I couldn’t post or view my new blog.

The problem was the MySQL-specific syntax used in the Wordpress code. Fortunately, SQL Anywhere has equivalent functionality, but uses different syntax (we more closely match Microsoft SQL Server syntax than MySQL syntax).

I could have just gone through the Wordpress source and fixed all the queries to run against SQL Anywhere, but I wanted to be able to share my Wordpress/SQL Anywhere blog with people who were interested, and I did not want to package the entire Wordpress install (not to mention updating it everytime Wordpress is updated). I just wanted to supply a plugin type of module so a user could just drop a file (or set of files) into an existing Wordpress install, use the SQL Anywhere migration wizard to move their data/schema from MySQL to SQL Anywhere and continue blogging.

I needed to come up with a way to fix all of the queries in Wordpress so they would run against SQL Anywhere, but without modifying any Wordpress source. I noticed that all queries in Wordpress are funnelled through the query() function in my db.php file. This function has an ‘apply_filters’ hook that I could have used, but this would not work because (as stated in the code comments) there are queries executed before the apply_filters() function is called. This meant I would have to write my own function.

Enter search and replace and regular expressions. I was not looking forward to this since it always took me a long time to get regular expressions right when we covered them in university. However, it actually turned out to be straightforward (though time-consuming) to build a search and replace function to turn the MySQL syntax into SQL Anywhere syntax. I am the first to admit it isn’t perfect, but all of the features and functionality of the blog work, so it is good enough for now.

My approach was to look at each type of query, determine the SQL Anywhere equivalent, and then build a search and replace function to fix the query syntax. The first one I did was the LIMIT clause. This is used all over the place in Wordpress. SQL Anywhere has equivalent behaviour using the TOP N clause:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

//Find LIMIT clause integer arguments and replace with TOP N START AT m
//MySQL syntax for LIMIT is:
//  LIMIT {[offset,] row_count | row_count OFFSET offset}]
//Note that the LIMIT clause is at the end of the query
 
// pattern is LIMIT followed by an integer, possibly followed by a comma and another integer
$pattern = "/LIMIT\s*(\d+)((\s*,?\s*)(\d+)*)/i";  
$matched = preg_match( $pattern, $query, $limitmatches );
if( $matched == 1 ) {
    //Remove the LIMIT statement, replace offset 0 with 1
    $query = preg_replace( $pattern, '', $query);
    if( count( $limitmatches ) == 5 ) {
        if( $limitmatches[1] == '0' ) {
            $limitmatches[1] = '1';
        }
        $replacement = 'SELECT TOP '.$limitmatches[4].' START AT '.$limitmatches[1].' ';
    } else {
        $replacement = 'SELECT TOP '.$limitmatches[1].' ';
    }	
    $query = preg_replace( '/^\s*SELECT\s*/i', $replacement, $query );
}

Once I completed all of my search and replace code (this took a couple of days), I bundled them together into a function (which I placed in db.php) called rewrite_query (click through for the complete source).

I then added a call to the rewrite_query function at the beginning of the query() function in db.php.

At this point, the blog was fully functional. I was able to create a new blog, and run all of the management functions. Woo hoo!

I only had one final problem, which I didn’t notice until I had several articles. The query used to determine whether the blog homepage had next/previous links depended on the MySQL FOUND_ROWS function. SQL Anywhere does not have this function, but luckily Wordpress provides a filter hook called ‘found_posts_query’. I simply added a hook and
created a query to return the count of the number of posts:

function set_query_post_count() {
    global $wpdb;
 
    $query = "SELECT COUNT(*) FROM ".$wpdb->posts." WHERE post_status = 'publish'";
    return $query;
}

That was it… In total, except for the last fix (which I didn’t notice for a while because of its nature), I was able to get this work done in just a few days. I assume someone familiar with PHP and/or Wordpress and/or regular expressions could probably have done it faster, but I was quite happy with the way things went. And now here we are - hopefully everything on this blog will continue to work as I expect.