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. MigrateDb.jpg

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(); ... } The define_sa_schema() procedure basically redefines the global variable $wp_queries to use SQL Anywhere syntax to create the WordPress schema tables, rather than the default MySQL syntax (for the complete listing of the function see http://iablog.sybase.com/hinsperg/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 them as UDFs with the same name and mapped them to the SQL Anywhere functionality. For example: function create_sa_functions() { global $wpdb; $wpdb->query( " IF NOT EXISTS( select * from sysprocedure where proc_name = 'MD5' ) THEN CREATE FUNCTION MD5( str varchar(255) ) RETURNS binary(32) BEGIN return HASH( str, 'MD5' ); END; END IF;"); ... } You can get the complete listing for this function here. At this point, you can point a browser at your WordPress homepage and successfully create a new, empty blog.

Modifying WordPress to use SQL Anywhere

Now that we have a new blog, in order to actually create/view the blog entries and use the other WordPress blog administration tools, we have to modify the MySQL specific syntax to be accepted by SQL Anywhere. Again, the easiest thing to do is to go through the WordPress install files and alter all of the occurrences of incorrect syntax to fix them. However, for a more generic solution, rewriting the queries inside of our db.php file allows for easier installation of SQL Anywhere as the DBMS in new WordPress installations and makes maintenance and upgrades of the WordPress software much easier. Fortunately, the problem is a straightforward one to solve, since SQL Anywhere supports all of the MySQL functionality used in WordPress. However, SQL Anywhere uses different syntax (SQL Anywhere adheres to ANSI in most cases and where there is no ANSI specification for a SQL construct, it more closely matches Microsoft SQL Server syntax than MySQL syntax). So we need to find a way to map the syntax within our db.php file.

Creating a Rewrite Function

Enter search and replace and regular expressions. The easiest way to map the syntax is to build a 'rewrite' function that was called before any query was executed. Fortunately, in WordPress, all queries go through a "query" function which is implemented in our db.php file, so all we have to do is add the new rewrite function and call it from the query() function. The rewrite_query() function, basically searched for specific query constructs and rewrites them into the desired format. For example, the LIMIT clause is used extensively in WordPress. SQL Anywhere has equivalent behavior using the TOP N clause. To rewrite these queries, I used the following code in my rewrite function: //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 //The 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 ); I had to do this with several other constructs, but once completed, I bundled them together into the rewrite_query() function. For the complete listing of the rewrite function, see here.

A Fully Functional Blog Using SQL Anywhere

At this point, the blog is fully functional. You can create new blogs, and run all of the management functions. The nicest part is that you have a single file (db.php) that you can now drop into any WordPress installation to move it from MySQL to SQL Anywhere. Using the SQL Anywhere migration wizard, you can also migrate existing blogs, complete with blog entries over to a SQL Anywhere database. You can also use this same technique to migrate any PHP based database application to SQL Anywhere. To check out some examples of blogs using this modified version of WordPress, see here.

About the Author

Jason Hinsperger has been working with SQL Anywhere for over 15 years. In his current role as a Senior Product Manager with Sybase iAnywhere, Jason regularly shares his in-depth knowledge about SQL Anywhere through articles and talks. When he's not working with SQL Anywhere or learning new technologies, Jason spends his time outdoors running a cattle farm in southern Ontario, Canada.