DBWhere: An old attempt at a PHP5 generic DB helper

DBWhere.class.php

<?php
/**
 * DBWhere: a where clause constructor for use in platform-independant database libraries
 *
 *  This program is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program.  If not, see <http://www.gnu.org/licenses/>.
 *
 * @author Justin Fargione <justin@lexicalbits.com>
 * @copyright Copyright (c) 2008, Justin Fargione
 */
 
/**
 * A class used for building complex, platform-independant SQL where clauses
 * @package DBTools
 */
class DBWhere {
    /**
     * A clause's default setting for values: the value is static and should be escaped (i.e. 1, Steve, or array(5, 60, bob))
     * @access public
     * @var integer
     */
    const VALUE_IS_VALUE = 0;
    /**
     * An alternate setting for values: the value is actually another column and should be escaped appropriatley
     * @access public
     * @var integer
     */
    const VALUE_IS_COLUMN = 1;
    /**
     * An alternate setting for values: the value is something that shouldn't be escaped normally (like a function call)
     * @access public
     * @var integer
     */
    const VALUE_IS_SPECIAL = 2;
    /**
     * The string we're building as we go along the Also/Either chain
     * @access protected
     * @var string
     */
    protected $_String = '';
    
    /**
     * Starts up our where clause with a single clause
     * @param string|DBWhere $columnOrWhere Name of the column to compare on, or another DBWhere whose logic should be nested
     * @param mixed|null $value The 'value', or right-hand part of the clause.  Should be null if $columnOrWhere is a DBWhere object, or should be defined by the $valueType parameter.  Defaults to null.
     * @param string $logic The type of logic to be used in this clause, defaults to '='.
     * @param int $valueType What type of value we've passed.  Can be either DBWhere::VALUE_IS_VALUE (the default), DBWhere::VALUE_IS_COLUMN, or DBWhere::VALUE_IS_SPECIAL
     * @return DBWhere This DBWhere obejct
     */
    public function __construct($columnOrWhere, $value = null, $logic = '=', $valueType = DBWhere::VALUE_IS_VALUE) {
        $this->_String = $this->_GetClause($columnOrWhere, $value, $logic, $valueType);
        return $this;
    }
    /**
     * Builds a clause from the given arguments
     * @param string|DBWhere $columnOrWhere Name of the column to compare on, or another DBWhere whose logic should be nested
     * @param mixed|null $value The 'value', or right-hand part of the clause.  Should be null if $columnOrWhere is a DBWhere object, or should be defined by the $valueType parameter.
     * @param string $logic The type of logic to be used in this clause.
     * @param int $valueType What type of value we've passed.  Can be either DBWhere::VALUE_IS_VALUE, DBWhere::VALUE_IS_COLUMN, or DBWhere::VALUE_IS_SPECIAL
     * @return string The constructed clause
     */
    protected function _GetClause($columnOrWhere, $value, $logic, $valueType) {
        if($columnOrWhere instanceof DBWhere) {
            return '('.$columnOrWhere->End().')';
        } else {
            $logic = $this->_GetLogic($logic, is_array($value));
            switch($valueType) {
                case DBWhere::VALUE_IS_VALUE:
                    $value = is_array($value)?$this->_WrapDBVals($value):$this->EscapeDBVal($value);
                    break;
                case DBWhere::VALUE_IS_COLUMN:
                    $value = $this->EscapeDBName($value);
                    break;
            }
            return $this->EscapeDBName($columnOrWhere).' '.$logic.' '.$value;
        }
    }
    protected function _GetLogic($logic, $valuesAreArrays) {
        switch(trim($logic)) {
            case '<>':
            case '!=':
            case '=!':
            case '!':
                return ($valuesAreArrays)?'NOT IN':trim($logic);
                break;
            default:
                return ($valuesAreArrays)?'IN':trim($logic);
                break;
        }
    }
    public function EscapeDBName($name) {
        return $name;
    }
    public function EscapeDBVal($val) {
        return $val;
    }
    protected function _WrapDBVals($vals) {
        foreach($vals as &$val) $val = $this->EscapeDBVal($val);
        return '('.implode(', ', $vals).')';
    }
    /**
     * Adds an 'And' clause (but can't be called And, since that's a protected word in PHP)
     * @param string|DBWhere $columnOrWhere Name of the column to compare on, or another DBWhere whose logic should be nested
     * @param mixed|null $value The 'value', or right-hand part of the clause.  Should be null if $columnOrWhere is a DBWhere object, or should be defined by the $valueType parameter.  Defaults to null.
     * @param string $logic The type of logic to be used in this clause, defaults to '='.
     * @param int $valueType What type of value we've passed.  Can be either DBWhere::VALUE_IS_VALUE (the default), DBWhere::VALUE_IS_COLUMN, or DBWhere::VALUE_IS_SPECIAL
     * @return DBWhere This DBWhere obejct
     */
    public function Also($columnOrWhere, $value = null, $logic = '=', $valueType = DBWhere::VALUE_IS_VALUE) {
        $this->_String .= ' AND '.$this->_GetClause($columnOrWhere, $value, $logic, $valueType);
        return $this;
    }
    /**
     * Adds an 'Or' clause (but can't be called Or, since that's a protected word in PHP)
     * @param string|DBWhere $columnOrWhere Name of the column to compare on, or another DBWhere whose logic should be nested
     * @param mixed|null $value The 'value', or right-hand part of the clause.  Should be null if $columnOrWhere is a DBWhere object, or should be defined by the $valueType parameter.  Defaults to null.
     * @param string $logic The type of logic to be used in this clause, defaults to '='.
     * @param int $valueType What type of value we've passed.  Can be either DBWhere::VALUE_IS_VALUE (the default), DBWhere::VALUE_IS_COLUMN, or DBWhere::VALUE_IS_SPECIAL
     * @return DBWhere This DBWhere obejct
     */
    public function Either($columnOrWhere, $value = null, $logic = '=', $valueType = DBWhere::VALUE_IS_VALUE) {
        $this->_String .= ' OR '.$this->_GetClause($columnOrWhere, $value, $logic, $valueType);
        return $this;
    }
    /**
     * Gets the final where clause.  Should only be called on the outer DBWhere clause in nested clauses.
     * @return string The final where clause
     */
    public function End() {
        return 'WHERE '.$this->_String;
    }
}
/**
 * A DBWhere clause builder tuned for MySQL string escaping
 * @author Justin Fargione <justin@lexicalbits.com>
 * @package DBTools
 */
class MySqlWhere extends DBWhere {
    public function EscapeDBName($name) {
        return "`".preg_replace("/`/", "\`", $name)."`";
    }
    public function EscapeDBVal($val) {
        return "'".preg_replace("/'/", "\'", $val)."'";
    }
}
/**
 * A DBWhere clause builder tuned for TSQL (MSSQL) string escaping
 * @author Justin Fargione <justin@lexicalbits.com>
 * @package DBTools
 */
class TSqlWhere extends DBWhere {
    public function EscapeDBName($name) {
        return "[".preg_replace("/([\[\]])/", "\$1", $name)."]";
    }
    public function EscapeDBVal($val) {
        return "'".preg_replace("/'/", "''", $val)."'";
    }
}
?>

DBWhere.test.php

<?php
/**
 * DBWhere Test: a quick example of how to use the DBWhere library
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *     * Redistributions of source code must retain the above copyright
 *       notice, this list of conditions and the following disclaimer.
 *     * Redistributions in binary form must reproduce the above copyright
 *       notice, this list of conditions and the following disclaimer in the
 *       documentation and/or other materials provided with the distribution.
 *     * Neither the name of Second Mind Software nor the
 *       names of its contributors may be used to endorse or promote products
 *       derived from this software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY Justin Fargione ''AS IS'' AND ANY
 * EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 * DISCLAIMED. IN NO EVENT SHALL Justin Fargione BE LIABLE FOR ANY
 * DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * @author Justin Fargione <justin@lexicalbits.com>
 * @copyright Copyright (c) 2008, Justin Fargione
 */

//Make sure we have the library (there are no other prerequisites)
require_once('DBWhere.class.php');

//Set up a sample class to test different DBWhere classes (DBWhere, MySqlWhere, TSqlWhere).
//In a real-world scenario, you'd probably have a global variable defining what kind of
// database you're using, and would return the proper child class accordingly
class MyClass {
    public $WhereToUse = '';
    public function Where($keyOrWhere, $value = null, $logic = '=', $escapeValue = DBWhere::VALUE_IS_VALUE) {
        $w = $this->WhereToUse;
        return new $w($keyOrWhere, $value, $logic, $escapeValue);
    }
    
    public function Test() {
        print '<b>'.$this->WhereToUse.'</b><br>';
        //The simplest version of the where clause would look like:
        $w = $this->Where('id', 5)->End();
        $this->Show(' Small', $w);
        
        //Slightly more complicated would be:
        $w = $this->Where('status', array(1,5,2))->Also('timestamp', '1/1/2000', '<')->End();
        $this->Show('Medium', $w);
        //Note the use of 'Also' in place of 'And'...the joy of PHP's reserved words...
        //Otherwise, it reads mostly like a regular query would.
        
        //This is a nasty where that tests most of the DBWhere object's features:
        $w = $this->Where('modified', 'added', '<', DBWhere::VALUE_IS_COLUMN)
            ->Also('added', 'dateAdd(d, -10, getDate())', '>', DBWhere::VALUE_IS_SPECIAL)
            ->Also('category_id', 2)
            ->Also(
                $this->Where('status', array(1,2))
                ->Either('is_admin', true)
                ->Either('name', "O'Rielly")
            )->End();
        $this->Show(' Large', $w);
            
        print('<br><br>');
    }
    public function Show($name, $text) {
        print('<code><b>'.preg_replace('/ /', '&nbsp;', $name).':</b> '.$text.'</code><br>');
    }
}
?>
<p>Here's the commands we'll be using to create the output below:</p>
<dl>
    <dt>Small</dt>
    <dd><pre>$this->Where('id', 5)->End();</pre></dd>
    <dt>Medium</dt>
    <dd><pre>$this->Where('status', array(1,5,2))->Also('timestamp', '1/1/2000', '<')->End();</pre></dd>
    <dt>Large</dt>
    <dd><pre>
$this->Where('modified', 'added', '<', DBWhere::VALUE_IS_COLUMN)
    ->Also('added', 'dateAdd(d, -10, getDate())', '>', DBWhere::VALUE_IS_SPECIAL)
    ->Also('category_id', 2)
    ->Also(
        $this->Where('status', array(1,2))
        ->Either('is_admin', true)
        ->Either('name', "O'Rielly")
    )->End();</pre></dd>
</dl>
<?php

$c = new MyClass();
//We'll show the output from a raw DBWhere clause first - never use this, as no escaping occurs
$c->WhereToUse = 'DBWhere';
$c->Test();

//Next is our MySQL implementation...
$c->WhereToUse = 'MySqlWhere';
$c->Test();

//...followed by an MSSQL/TSQL implementation.
$c->WhereToUse = 'TSqlWhere';
$c->Test();

?>