How To: Running WordPress with SQL Anywhere

By Jason Hinsperger Nov 25, 2008

3 comments

SQL Anywhere is a simple, powerful database with some interesting features. It has a small footprint, is self-managed and fully functional, and it provides many of the features of an enterprise database but can run on commodity hardware with a minimal amount of administration.

In addition, SQL Anywhere offers high performance, bi-directional synchronization that allows you to easily integrate your database application with your existing enterprise data center — and this last point could just be key for your corporate blogging infrastructure.

In this technical article we show you how to modify a WordPress install such that it runs against a SQL Anywhere database.

Getting Started

This write-up is focused on WordPress, but similar techniques can be used to migrate just about any PHP application. For our WP case, we will build a plugin for SQL Anywhere that can be dropped into a standard WordPress install and, with no other changes to the WordPress code, allow you to use SQL Anywhere.

This article assumes you already have a web server installed and running with PHP support and SQL Anywhere installed on the machine. In our setup, I used Apache 2, PHP 5, WordPress 2.3.3 and SQL Anywhere 10, and tested the solution on both Windows and Linux.

Starting with the standard WordPress install, copy the wp-db.php from the wp-admin into the file called wp-content\db.php. This file name and location is important, because the WordPress software looks for this file when the site is accessed and if it is present it will use it instead of the default wp-db.php file. This is what allows you to plugin your own database access layer for WordPress.

Setting Up the Database Connection

We will now need to modify this file in order to make it work with SQL Anywhere.
The SQL Anywhere PHP driver (see here) supports a similar API to MySQL, so a quick search and replace of mysql_ with sqlanywhere_ got much of the migration completed.

In other words, mysql_connect() becomes sqlanywhere_connect().

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);

In an ideal world, that would be all you would have to do. However, there are a couple more changes required before you can start blogging.

Migrating the Database Schema

The first is migrating the database schema to a SQL Anywhere format. This can be done trivially by using the SQL Anywhere migration wizard.

However, to build a more generic solution so that you can simply use SQL Anywhere in a standard WordPress install seamlessly, and so anyone can create a new WordPress blog within the WordPress application, there is more work to do to get the blog creation working.

The major change is replacing the WordPress wp_install() function. For SQL Anywhere, a clone of the existing wp_install() function is made, and 2 functions are added, and called from wp_install:

function wp_install($blog_title, $user_name, $user_email, $public, $meta='') {
global $wp_rewrite, $wpdb;
wp_cache_flush();
//Reset schema information
define_sa_schema();
make_db_current_silent();
create_sa_functions();
...
}