WordPress on Ms-Sql

WordPress Database Abstraction Plug-in

As more web hosts migrate to Server 2008 R2, more users are putting WordPress on existing Windows Server hosting accounts. While the web hosts could easily install a MySql server to fully support the many excellent PHP applications that use it, many if not most of them are just leaving their MS-SQL server infrastructure as-is and installing PHP to use it instead. This presents users and developers with a major problem, because MySql and MS-SQL query dialects are very different. The solution, for now, seems to be to write a rather complex database abstraction layer that allows native PHP/MySql code to connect and query MS-SQL. One such example (well, the only one) is the WordPress Database Abstraction plug-in. While it works quite well overall, it is frustrating users (like me) with several bugs, and the author has not updated the plug-in over 7 months (I know, people do get busy and have a life beyond making free software!).  Meanwhile, a few bug reports have piled up, as well as several fixes, some good and some that gin-up new bugs.

Bugs and Fixes

I’ll relate what I have found by much research and testing. This applies to installs using the sqlsrv driver. I don’t know anything about installs using mssql or azure drivers. I can’t take any credit for these fixes, just for researching the issues and testing them, on this very blog.

1. Posts don’t show, for any query that should return a range of posts

  • posts don’t show in admin posts table
  • posts don’t show in blog, where posts n to n selected: home, category, archive, etc.
  • Recent Posts widget doesn’t show because it retrieves no posts
  • but they do show if you use the permalink to each one.

The reason for this is a bug in wp-db-abstraction\translations\sqlsrv\translations.php, line 728 (in v.1.1.4), where the regular expression matches 6 items, but tests for 5. Several people offered a fix by either changing ==5 to >=5 or ==6, but this causes pagination (and other things) to break. The proper fix is to simply remove the last set of parens from the regexp: At or about line 728,

// $pattern = '/LIMIT\s*(\d+)((\s*,?\s*)(\d+)*)(;{0,1})$/is';
$pattern = '/LIMIT\s*(\d+)((\s*,?\s*)(\d+)*);{0,1}$/is';

That way it finds 5 items, not 6, and the rest of the function works as-written. Ref:  http://sourceforge.net/tracker/?func=detail&aid=3485384&group_id=315685&atid=1328061

2. Putting backslash \ in a post breaks query translation

At line 246,

// Remove backslash in order to avoid problems with rewriting
$query = str_replace('\\', '\', $query);

And at line 327,

// Restore backslash earlier removed
$query = str_replace('\', '\\', $query);
// debug code

As you can see from this page, the backslash bug-fix works! Ref: http://wordpress.org/support/topic/plugin-wp-db-abstraction-fix-problems-with-backslash-usage

3. Back-ticks ` leaking into queries

Note that the tick remover at line 513 works fine unless something else (like the previous backslash problem) causes the query translation to break before it’s fully translated, so this mod might not be necessary (but it can’t hurt). At line 283,

// $query = vsprintf($query, $this->preg_data);
$query = vsprintf($query, str_replace('`', '', $this->preg_data));

Ref: http://wordpress.org/support/topic/plugin-wp-db-abstraction-changes-to-114-to-run-with-332-on-mssql

4. Doubled single-quote marks in queries

There’s a post (see Ref below) that "fixes" doubled single-quotes in query translation (see line 391):

if($param[0] !== "'") {
// $param = "'$param'";
$param = "$param";
}

However, this fix doesn’t make sense, because the IF checks to see if the first character of the parameter is NOT a single-quote, and if it isn’t, it adds them to the param. Besides, making $param = "$param" does nothing. I don’t know much about PHP, but shouldn’t it be $param = "'" . $param . "'" ? Ref: http://sourceforge.net/projects/wp-sqlsrv/forums/forum/1125734/topic/5008814 Anyway, I did a full text search on all WordPress 3.4.1 files, and all 6 of them that contain SHOW TABLES LIKE have their parameters properly enclosed in single-quotes, so the whole block below // quoted with double quotes instead of single? is unnecessary, no?

5. Several posts relate to errors on upgrades and imports

I have not done either, so don’t know about that.  Maybe no one does, because the posts were not answered.

Related Sites

The plug-in download: http://wordpress.org/extend/plugins/wordpress-database-abstraction/ -- be sure to read everything in the several tabs. The author’s WordPress on Microsoft site: http://wordpress.visitmix.com/ The Sourceforge wp-sqlsrv page: http://sourceforge.net/projects/wp-sqlsrv/ Lots of background info: http://blogs.msdn.com/b/brian_swan/archive/2010/05/12/running-wordpress-on-sql-server.aspx -- use this article if you are setting up mssql server and php on iis from scratch. —kv5r PS: If you know more about this than I do, please comment! If you don’t, don’t ask me because all that I know about it is on this page.

Leave a Reply

Your email address will not be published. Required fields are marked *