Rows and Columns

In the previous article, I talked about the software packages I chose for building the blog. In this article, I am going to detail the setup and development of the SQL Anywhere backed Wordpress 2.3.3 blog that you are reading.

Once I had the basic software components installed (Windows Server 2003, Apache 2, PhP 5, Wordpress 2.3.3 and SQL Anywhere 10), my next task was to get Wordpress and SQL Anywhere talking. This turned out to be pretty easy. In reading the Wordpress docs and surfing the wordpress code, I found that if I placed a db.php file in the wp-content directory of my Wordpress install, I could implement my own database interface, without having to edit any other files in the Wordpress installation (more on this later).

I started by making a copy of wp-db.php from the wordpress install, which contained all of the MySQL supporting database calls. The SQL Anywhere PhP driver supports a similar api to MySQL, so a quick search and replace of mysql_ with sqlanywhere_ and I had a good start on things.
The connect strings for MySQL and SQL Anywhere are formatted differently, but both use the same basic data (user, password, server location, database). It was straightforward to convert the connect string:

//MySQL connection
$this->dbh = @mysql_connect($dbhost, $dbuser, $dbpassword);
 
//SQL Anywhere connection
$connstr= "eng=" . $dbhost . ";uid=" . $dbuser . ";pwd=" . $dbpassword . ";dbn=" . $dbname . ";links=shmem,tcpip";
$this->dbh = @sqlanywhere_connect($connstr);

Two other things that had me stumped for a while. The first (and longest to find, but easy to fix) was the fact that sqlanywhere_query() function and the mysql_query() function take the same arguments, but in reverse order. The second was that I had to re-arrange the error handling code slightly in the query() function to avoid a ‘cursor not open’ problem.

In an ideal world, that would be all I would have to do… However, there was a little more required before I could start blogging. The first was migrating the database schema to a SQL Anywhere format. This would have been trivial if I wanted to run a ‘one-off’ type of process and get my blog running, by using the SQL Anywhere migration wizard.
However, I wanted my end result to be usable by anyone to create a new wp blog, so I had to do a little more work.

To get the Wordpress install to work, I had to replace the wp_install() function. I basically cloned the existing wp_install() function, then added 2 functions, and called them from wp_install:

function wp_install($blog_title, $user_name, $user_email, $public, $meta='') {
     global $wp_rewrite, $wpdb;

     //Reset schema information
     define_sa_schema();
     make_db_current_silent();
     create_sa_functions();
…


The define_sa_schema() procedure basically redefines the global variable $wp_queries to use the appropriate SQL Anywhere syntax to create the Wordpress schema tables. Here is the actual function:
define_sa_schema

The create_sa_functions() procedure creates some user defined functions that do not exist as built-ins in SQL Anywhere. SQL Anywhere supports the functionality of these functions and so I simply added the functions as UDFs with the same name and mapped them to the SQL Anywhere functionality. Here is the source:
create_sa_functions

I had to do one last thing to enable the Wordpress install. Unfortunately, the Wordpress creator(s) did not completely encapsulate the mysql function calls into the wp-db.php file. There was a call to the function mysql_get_server_info() in the main wordpress source. Of course, I was not running MySQL so this function was failing. To get around this problem, I simply added the function to my db.php file.

//replace the mysql_get_server_info function to avoid failing to create the blog
function mysql_get_server_info() {
	//Add a filter for the FOUND_ROWS query to select the total number of posts
	add_filter( 'found_posts_query', 'set_query_post_count' );
	return '10.0.0';
}

At this point, I could point my browser at the blog homepage and Wordpress would successfully create the blog. Yay!

Now to tackle the administrative console and all that MySQL-specific query syntax… Stay tuned!