Sunday, October 19, 2014

Plugin to make Wordpress work with SQL server

If your website is using Windows server with IIS + SQL server and you want to have a blog using Wordpress, you may not want to install MySQL server on your existing server because it can make your server heavier and slower. Luckily there is a solution to make Wordpress work with your existing SQL server. Below are articles that can help you in details:

Installing WordPress on Windows using SQL Server 2008 R2 Part 1 - This section will cover installing and configuring PHP and IIS 7.5.
Installing WordPress on Windows using SQL Server 2008 R2 Part 2 - This section will cover configuring your SQL Server and installing and configuring WordPress.



The heart of this solution is wp-db-abstraction plugin, The following fixes can make it work more smoothly:
1. Open file wp-includes\wp-db.php, goto line 1294:
mysql_free_result( $this->result );
and change it to:
sqlsrv_free_stmt( $this->result );
This will help to flush out the resources after querying results. In this case we use sqlsrv function instead of mysql function

2. Open file wp-content\mu-plugins\wp-db-abstraction\translations\sqlsrv\translations.php, change:
$pattern = '/LIMIT\s*(\d+)((\s*,?\s*)(\d+)*)(;{0,1})$/is';
to
$pattern = '/LIMIT\s*(\d+)((\s*,?\s*)(\d+)*);{0,1}$/is';

In this file too, move to the function translate_specific($query), change it to the following code:

function translate_specific($query)
    {
        $tmp = strtoupper ("SELECT COUNT(NULLIF(`meta_value` LIKE '%\"administrator\"%', FALSE)), "
                        . "COUNT(NULLIF(`meta_value` LIKE '%\"editor\"%', FALSE)), "
                        . "COUNT(NULLIF(`meta_value` LIKE '%\"author\"%', FALSE)), "
                        . "COUNT(NULLIF(`meta_value` LIKE '%\"contributor\"%', FALSE)), "
                        . "COUNT(NULLIF(`meta_value` LIKE '%\"subscriber\"%', FALSE)), "
                        . "COUNT(*) FROM " . $this->prefix . "usermeta WHERE meta_key = '" . $this->prefix . "capabilities'");
        if (strtoupper($this->preg_original) == $tmp) { 
            $query = "SELECT 
    (SELECT COUNT(*) FROM " . $this->prefix . "usermeta WHERE meta_key = '" . $this->prefix . "capabilities' AND meta_value LIKE '%administrator%') as ca, 
    (SELECT COUNT(*) FROM " . $this->prefix . "usermeta WHERE meta_key = '" . $this->prefix . "capabilities' AND meta_value LIKE '%editor%') as cb, 
    (SELECT COUNT(*) FROM " . $this->prefix . "usermeta WHERE meta_key = '" . $this->prefix . "capabilities' AND meta_value LIKE '%author%') as cc, 
    (SELECT COUNT(*) FROM " . $this->prefix . "usermeta WHERE meta_key = '" . $this->prefix . "capabilities' AND meta_value LIKE '%contributor%') as cd, 
    (SELECT COUNT(*) FROM " . $this->prefix . "usermeta WHERE meta_key = '" . $this->prefix . "capabilities' AND meta_value LIKE '%subscriber%') as ce, 
    COUNT(*) as c FROM " . $this->prefix . "usermeta WHERE meta_key = '" . $this->prefix . "capabilities'";
            $this->preg_data = array();
        }

        if (stristr($query, "SELECT DISTINCT TOP 50 (" . $this->prefix . "users.ID) FROM " . $this->prefix . "users") !== FALSE) {
            $query = str_ireplace(
                "SELECT DISTINCT TOP 50 (" . $this->prefix . "users.ID) FROM", 
                "SELECT DISTINCT TOP 50 (" . $this->prefix . "users.ID), user_login FROM", $query);
        }
        
        if (stristr($query, 'INNER JOIN ' . $this->prefix . 'terms USING (term_id)') !== FALSE) {
            $query = str_ireplace(
                'USING (term_id)', 
                'ON ' . $this->prefix . 'terms.term_id = ' . $this->prefix . 'term_taxonomy.term_id', $query);
        }
        
        return $query;
    }

I believe that you can have a Wordpress blog work with your existing SQL server soon -:) Any comment is welcome.
Subscribe to RSS Feed Follow me on Twitter!