In this article, we are going to cover the basics of how you could migrate any PHP database application to SQL Anywhere. SQL Anywhere is a small-footprint, self-managing, fully functional relational database that provides all of the features of an enterprise database, but can run on commodity hardware with a minimal amount of administration. In addition, SQL Anywhere offers robust, high performance, bi-directional synchronization that allows you to easily integrate your SQL Anywhere database application with your existing enterprise data center.
The application we are using to demonstrate this is the popular PHP blog application Wordpress. We will build a plug-in 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 that 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 file from the wp-admin folder into the file called wp-content\db.php. This file name and location are 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 plug in your own database access layer for Wordpress. We will now need to modify this file in order to make it work with SQL Anywhere.
The SQL Anywhere PHP driver supports a similar API to MySQL, so a quick search and replace of mysql_ with sqlanywhere_ got much of the migration completed.
e.g. 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. The first is migrating the database schema to 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();
...
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 here.
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.
e.g.
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.
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 also 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. 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 that 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 searches 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 behaviour 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
// 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 this.
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 see some examples of blogs using this modified version of Wordpress, visit here.
Contact the author to get the latest revision of the complete db.php file.
