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.

I was surfing the internet and found that you can download a dump of the wikipedia article data from Wikimedia

I was learning about the XML capabilities of SQL Anywhere, so I decided to download the XML dump of the article data and load it into a database. From there I could use the database to test the SQL Anywhere v11 (currently in beta) full text search functionality.

The XML file was about 12G of data. The format was pretty straight forward, so I decided I would use the OpenXML(…) function to simply read the data into a table in the database.

My table looked like this:

CREATE TABLE "DBA"."wikipedia" (
    "article_id"                     bigint NULL
   ,"title"                          varchar(1000) NULL INLINE 256 PREFIX 8
   ,"contributor"                    varchar(1000) NULL INLINE 256 PREFIX 8
   ,"comments"                       long varchar NULL
   ,"details"                        long varchar NULL
)

and my load script looked like this:

CREATE VARIABLE content long varchar;
SELECT xp_read_file( '\wikipediadata\data\file1.xml' ) INTO content;
INSERT INTO wikipedia( article_id, title, contributor, "comments", details )
    SELECT * FROM OPENXML( content, '/mediawiki/page', 1, 
                          '<root xmlns="http://www.mediawiki.org/xml/export-0.3/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance/"/>' )
	WITH( article_id bigint 'id', 
             title varchar(1000) 'title', 
             contributor varchar(1000) './revision/contributor/username', 
             comments long varchar './revision/comment', 
             details long varchar './revision/text' );

The first problem I encountered was a limitation of SQL Anywhere. In SQL Anywhere, the maximum size of a string variable (including function/producedure arguments and character columns) is 2G. This meant before I could load the data I would have to break the 12G XML file into 2G pieces. I used a file split utility to break create a bunch of smaller XML files.
I then had to edit the beginning and end of each file to ensure each individual file was well formatted XML.

After that, I changed my load script above into several read/load statements and ran them. a couple of hours later I had all of the data loaded into the database. If the SQL Anywhere LOAD TABLE statement supported a clause that allowed me to specify the same information as
OpenXML, I could have used it instead, avoiding the requirement to split the large file into smaller components and presumably providing a much faster load than individual insert statements. I have added this to the list of future enhancements for SQL Anywhere.

In any case, now that I had the data loaded, I could view and query the data. However, the real goal is to play with the full text searching functionality in SQL Anywhere 11. I’ll talk about that in a future article.

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!

As mentioned in an earlier post I am planning a series of posts on how I built the blog server.  In my first post, I gave an overview of the process. In this post I will provide further detail on the criteria I used and the components of software that I actually chose to set up the blog site.
After I decided to build a blog to increase our interaction with the SQL Anywhere developer community, the first decision I had to make was what software I wanted to use to manage my blogs.  Given that we are a database company, it was clear that data management for the blog would be done by SQL Anywhere.  Since support for a variety of platforms is one of the cornerstones of our success, I chose Apache as my web server because it also supports a variety of platforms and environments. Since I am more familiar with Windows than Linux, I set everything up on a Windows machine, but because the SQL Anywhere database file format is binary compatible across the platforms we support, the entire blog could be moved to Linux in the future by simply copying the database files and wordpress install to a Linux machine running Apache, PHP and SQL Anywhere.

Once I decided on the OS and web server, I moved on to looking at the blog itself.  Being new to website design, management and implementation, I decided the best thing for me to do was to look at what the blogs I read used, rather than try to do everything myself (why re-invent the wheel?).  I looked at a couple of different pieces of blog software before I settled on Wordpress.  It was relatively small, devoted to doing one thing (blogs) well and did not require any other external software support besides PHP and MySQL. Also, since it is open source, I could do the migration to replace MySQL with SQL Anywhere without requiring any 3rd party assistance.

In my next article, I will go into detail on the actual implementation of the blog site.

This will be the first of a few blog entries discussing how I set up the iaBlog server using SQL Anywhere and Wordpress running on Apache.

When we (the SQL Anywhere team) first decided we would like to blog, I set up an internal server from scratch with a test blog for our team so we could become familiar with the process.  In installed the OS, Apache, PHP Wordpress and SQL Anywhere.  Since we have our own database technology, I took the opportunity to port Wordpress to use SQL Anywhere as the database for the blog(s). 

Setting the server up wasn’t too difficult.  The installs ran smoothly.  The Wordpress port took more time, but was not a difficult process either.  It gave me the opportunity to learn some php and brush up on my regular expressions.  Wordpress provides a hook where you can drop a db.php file into your Wordpress install, and Wordpress will use it instead of the default wp-db.php.  I copied wp-db.php to db.php, renamed all the MySQL functions to refer to the SQL Anywhere php driver instead, and I was well on my way. From there, I simply had to deal with all of the MySQL queries that used syntax that was not supported by SQL Anywhere. 

There are some syntax differences between our products. For example, in MySQL there is a LIMIT clause to limit the number of rows returned by a query.  In SQL Anywhere, the same feature is available, but we use the TOP N clause instead (this syntax was supported by more vendors when we first added the feature).  Another example is the MySQL ‘SHOW TABLES’ statement.  I used “SELECT table_name from SYSTABLE” to achieve the same result.  There were a number of others, but all were along a similar vein of the above.

To deal with these differences in syntax, I wrote a ‘rewrite_query’ function.  This function searches for specific pieces of MySQL queries and replaces them with the SQL Anywhere equivalent.

That was enough to get everything going, and formed the basis for the blog site you are visiting right now.  At some point I will polish the db.php file and post it here in case anyone is interested in seeing it.

Welcome to my blog.  My plan is to use this blog to provide practical solutions to technical problems I have encountered with software I use regularly, provide and solicit commentary on news stories which interest me and are relevant to the SQL Anywhere developer community, and even throw in some of my own opinion pieces based on the work I do as a SQL Anywhere product manager at Sybase iAnywhere.