/library/database/class.sqldriver.php

Large files files are truncated, but you can click here to view the full file

<?php if (!defined('APPLICATION')) exit();
/*
Copyright 2008, 2009 Vanilla Forums Inc.
This file is part of Garden.
Garden 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.
Garden 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 Garden.  If not, see <http://www.gnu.org/licenses/>.
Contact Vanilla Forums Inc. at support [at] vanillaforums [dot] com
*/

/**
 * The Gdn_DatabaseDriver class (equivalent to SqlBuilder from Vanilla 1.x) is used
 * by any given database driver to build and execute database queries.
 *
 * This class is HEAVILY inspired by and, in places, flat out copied from
 * CodeIgniter (http://www.codeigniter.com). My hat is off to them.
 *
 * @author Mark O'Sullivan
 * @copyright 2003 Mark O'Sullivan
 * @license http://www.opensource.org/licenses/gpl-2.0.php GPL
 * @package Garden
 * @version @@GARDEN-VERSION@@
 * @namespace Garden.Database
 */
require_once(dirname(__FILE__).DS.'class.database.php');

abstract class Gdn_SQLDriver {
   
   /// CONSTRUCTOR ///
   public function __construct() {
      $this->ClassName = get_class($this);
      $this->Reset();
   }
   
   
   /// PROPERTIES ///
   
   

   /**
    * An associative array of table alias => table name pairs.
    *
    * @var array
    */
   protected $_AliasMap;
   
   /**
    * The name of the class that has been instantiated.
    *
    * @var string
    */
   public $ClassName;
   
   /**
    * The database connection.
    *
    * @var Gdn_Database The connection and engine information for the database.
    */
   public $Database;
   
   /**
    * An associative array of information about the database to which the
    * application is connected. Values include: Engine, Version, DatabaseName.
    *
    * @var string
    */
   protected $_DatabaseInfo = array();

   /**
    * A boolean value indicating if this is a distinct query.
    *
    * @var boolean
    */
   protected $_Distinct;

   /**
    * A collection of tables from which data is being selected.
    *
    * @var array
    */
   protected $_Froms;

   /**
    * A collection of group by clauses.
    *
    * @var array
    */
   protected $_GroupBys;

   /**
    * A collection of having clauses.
    *
    * @var array
    */
   protected $_Havings;

   /**
    * A collection of tables which have been joined to.
    *
    * @var array
    */
   protected $_Joins;

   /**
    * The number of records to limit the query to. FALSE by default.
    *
    * @var int
    */
   protected $_Limit;

   /**
    * An associative array of parameter_name => parameter_value pairs to be
    * inserted into the prepared $this->_PDOStatement.
    *
    * @var array
    */
   protected $_NamedParameters = array();
   
   /**
    * Whether or not to reset the properties when a query is executed.
    *
    * @var int
    *   0 = The object will reset after query execution.
    *   1 = The object will not reset after the <b>NEXT</b> query execution.
    *   2 = The object will not reset after <b>ALL</b> query executions.
    */
   protected $_NoReset = FALSE;

   /**
    * The offset from which data should be returned. FALSE by default.
    *
    * @var int
    */
   protected $_Offset;
   
   /**
    * The number of where groups currently open.
    *
    * @var int
    */
   protected $_OpenWhereGroupCount;

   /**
    * A collection of order by statements.
    *
    * @var array
    */
   protected $_OrderBys;
   
   /**
    * A collection of fields that are being selected.
    *
    * @var array
    */
   protected $_Selects;

   /**
    * An associative array of Field Name => Value pairs to be saved
    * to the database.
    *
    * @var array
    */
   protected $_Sets;
   
   /**
    * The logical operator used to concatenate where clauses.
    * 
    * @var string
    */
   protected $_WhereConcat;
   
   /**
    * The default $_WhereConcat that will be reverted back to after every where clause is appended.
    *
    * @var string
    */
   protected $_WhereConcatDefault;

   /**
    * The number of where groups to open.
    *
    * @var int
    */
   protected $_WhereGroupCount;

   /**
    * A collection of where clauses.
    *
    * @var array
    */
   protected $_Wheres;

 
   /// METHODS ///
   
   /**
    * Removes table aliases from an array of JOIN ($this->_Joins) and GROUP BY
    * ($this->_GroupBys) strings. Returns the $Statements array with prefixes
    * removed.
    *
    * @param array $Statements The string specification of the table. ie.
    * "tbl_User as u" or "user u".
    * @return array the array of filtered statements.
    */
   //protected function _FilterTableAliases($Statements) {
   //   foreach ($Statements as $k => $v) {
   //      foreach ($this->_AliasMap as $Alias => $Table) {
   //         $Statement = preg_replace('/(\w+\.\w+)/', $this->EscapeIdentifier('$0'), $v); // Makes `table.field`
   //         $Statement = str_replace(array($this->Database->DatabasePrefix.$Table, '.'), array($Table, $this->EscapeSql('.')), $Statement);
   //      }
   //      $Statements[$k] = $Statement;
   //   }
   //   return $Statements;
   //}
   
   /**
    * Concat the next where expression with an 'and' operator.
    * <b>Note</b>: Since 'and' is the default operator to begin with this method doesn't usually have to be called,
    * unless Gdn_DatabaseDriver::Or(FALSE) has previously been called.
    *
    * @param boolean $SetDefault Whether or not the 'and' is one time or sets the default operator.
    * @return Gdn_DatabaseDriver $this
    * @see Gdn_DatabaseDriver::OrOp()
    */
   public function AndOp($SetDefault = FALSE) {
      $this->_WhereConcat = 'and';
      if($SetDefault) {
         $this->_WhereConcatDefault = 'and';
      }
      
      return $this;
   }
   
   /**
    * Begin bracketed group in the where clause to group logical expressions together.
    *
    * @return Gdn_DatabaseDriver $this
    */
   public function BeginWhereGroup() {
      $this->_WhereGroupCount++;
      $this->_OpenWhereGroupCount++;
      return $this;
   }
   
   /**
    * Returns a single Condition Expression for use in a 'where' or an 'on' clause.
    *
    * @param string $Field The name of the field on the left hand side of the expression.
    *   If $Field ends with an operator, then it used for the comparison. Otherwise '=' will be used.
    * @param mixed $Value The value on the right side of the expression. This has different behaviour depending on the type.
    *   <b>string</b>: The value will be used. If $EscapeValueSql is true then it will end up in a parameter.
    *   <b>array</b>: DatabaseFunction => Value will be used. if DatabaseFunction contains a "%s" then sprintf will be used.
    *     In this case Value will be assumed to be a string.
    *
    * <b>New Syntax</b>
    * The $Field and Value expressions can begin with special characters to do certain things.
    * <ul>
    * <li><b>=</b>: This means that the argument is a function call.
    *   If you want to pass field reference arguments into the function then enclose them in square brackets.
    *   ex. <code>'=LEFT([u.Name], 4)'</code> will call the LEFT database function on the u.Name column.</li>
    * <li><b>@</b>: This means that the argument is a literal.
    *   This is useful for passing in literal numbers.</li>
    * <li><b>no prefix></b>: This will treat the argument differently depending on the argument.
    *   - <b>$Field</b> - The argument is a column reference.
    *   - <b>$Value</b> - The argument will become a named parameter.
    * </li></ul>
    * @return string The single expression.
    */
   public function ConditionExpr($Field, $Value, $EscapeFieldSql = TRUE, $EscapeValueSql = TRUE) {
      // Change some variables from the old parameter style to the new one.
      // THIS PART OF THE FUNCTION SHOULD EVENTUALLY BE REMOVED.
      if($EscapeFieldSql === FALSE) {
         $Field = '@' . $Field;
      }
      if(is_array($Value)) {
         $FunctionCall = array_keys($Value);
         $FunctionCall = $FunctionCall[0];
         $FunctionArg = $Value[$FunctionCall];
         if($EscapeValueSql)
            $FunctionArg = '[' . $FunctionArg . ']';
         
         if(stripos($FunctionCall, '%s') === FALSE) 
            $Value = '=' . $FunctionCall . '(' . $FunctionArg . ')';
         else
            $Value = '=' . sprintf($FunctionCall, $FunctionArg);
         $EscapeValueSql = FALSE;
      } else if(!$EscapeValueSql && !is_null($Value)) {
         $Value = '@' . $Value;
      }
      
      // Check for a straight literal field expression.
      if(!$EscapeFieldSql && !$EscapeValueSql && is_null($Value))
         return substr($Field, 1); // warning: might not be portable across different drivers
      
      $Expr = ''; // final expression which is built up
      $Op = ''; // logical operator
      
      // Try and split an operator out of $Field.
      $FieldOpRegex = "/(?:\s*(=|<>|>|<|>=|<=)\s*$)|\s+(like|not\s+like)\s*$|\s+(?:(is)\s+(null)|(is\s+not)\s+(null))\s*$/i";
      $Split = preg_split($FieldOpRegex, $Field, -1, PREG_SPLIT_NO_EMPTY | PREG_SPLIT_DELIM_CAPTURE);
      if(count($Split) > 1) {
         $Field = $Split[0];
         $Op = $Split[1];
         if (count($Split) > 2) {
            $Value = null;
         }
      } else {
         $Op = '=';
      }
      
      if($Op == '=' && is_null($Value)) {
         // This is a special case where the value SQL is checking for an is null operation.
         $Op = 'is';
         $Value = '@null';
         $EscapeValueSql = FALSE;
      }
      
      // Add the left hand side of the expression.
      $Expr .= $this->_ParseExpr($Field, NULL, $EscapeFieldSql);
      
      // Add the expression operator.
      $Expr .= ' '.$Op.' ';
      
      if ($Op == 'is' || $Op == 'is not' && is_null($Value)) {
         $Expr .= 'null';
      } else {
         // Add the right side of the expression.
         $Expr .= $this->_ParseExpr($Value, $Field, $EscapeValueSql);
      }
      
      return $Expr;
   }

   /**
    * Returns the name of the database currently connected to.
    */
   public function DatabaseName() {
      return $this->Information('DatabaseName');
   }

   /**
    * Builds and executes a delete from query.
    *
    * @param mixed $Table The table (or array of table names) to delete from.
    * @param mixed $Where The string on the left side of the where comparison, or an associative
    * array of Field => Value items to compare.
    * @param int $Limit The number of records to limit the query to.
    */
   public function Delete($Table = '', $Where = '', $Limit = FALSE) {
      if ($Table == '') {
         if (!isset($this->_Froms[0]))
            return FALSE;

         $Table = $this->_Froms[0];
      } elseif (is_array($Table)) {
         foreach ($Table as $t) {
            $this->Delete($t, $Where, $Limit, FALSE);
         }

         return;
      } else {
         $Table = $this->EscapeIdentifier($this->Database->DatabasePrefix.$Table);
      }

      if ($Where != '')
         $this->Where($Where);

      if ($Limit !== FALSE)
         $this->Limit($Limit);

      if (count($this->_Wheres) == 0)
         return FALSE;

      $Sql = $this->GetDelete($Table, $this->_Wheres, $this->_Limit);

      return $this->Query($Sql);
   }
   
   /**
    * Specifies that the query should be run as a distinct so that duplicate
    * columns are grouped together. Returns this object for chaining purposes.
    *
    * @param boolean $Bool A boolean value indicating if the query should be distinct or not.
    */
   public function Distinct($Bool = TRUE) {
      $this->_Distinct = (is_bool($Bool)) ? $Bool : TRUE;
      return $this;
   }

   /**
    * Removes all data from a table.
    *
    * @param string $Table The table to empty.
    */
   public function EmptyTable($Table = '') {
      if ($Table == '') {
         if (!isset($this->_Froms[0]))
            return FALSE;

         $Table = $this->_Froms[0];
      } else {
         $Table = $this->EscapeIdentifier($this->Database->DatabasePrefix.$Table);
      }


      $Sql = $this->GetDelete($Table);
      
      return $this->Query($Sql);
   }
   
   /**
    * Closes off any open elements in the query before execution.
    * Ideally, the programmer should have everything closed off so this method will do nothing.
    */
   protected function _EndQuery() {
      // Close the where groups.
      while($this->_WhereGroupCount > 0) {
         $this->EndWhereGroup();
      }
   }
   
   /**
    * End a bracketed group in the where clause.
    * <b>Note</b>: If no items where added to the group then no barackets will appear in the final statement.
    *
    * @return Gdn_DatabaseDriver $this.
    */
   public function EndWhereGroup() {
      if($this->_WhereGroupCount > 0) {
         $WhereCount = count($this->_Wheres);
         
         if($this->_OpenWhereGroupCount >= $this->_WhereGroupCount)
            $this->_OpenWhereGroupCount--;
         else if($WhereCount > 0)
            $this->_Wheres[$WhereCount-1] .= ')';
            
         $this->_WhereGroupCount--;
      }  
      
      return $this;
   }
   
   /**
    * Takes a string formatted as an SQL field reference and escapes it for the defined database engine.
    *
    * @param string $RefExpr The reference expression to be escaped.
    *   The reference should be in the form of alias.column.
    */
   protected function EscapeIdentifier($RefExpr) {
      trigger_error(ErrorMessage('The selected database engine does not perform the requested task.', $this->ClassName, 'EscapeSql'), E_USER_ERROR);
   }
   
   /**
    * Takes a string of SQL and escapes it for the defined database engine.
    * ie. adds backticks or any other database-specific formatting.
    *
    * @param mixed $String The string (or array of strings) of SQL to be escaped.
    * @param boolean $FirstWordOnly A boolean value indicating if the first word should be escaped only.
    */
   protected function EscapeSql($String, $FirstWordOnly = FALSE) {
      trigger_error(ErrorMessage('The selected database engine does not perform the requested task.', $this->ClassName, 'EscapeSql'), E_USER_ERROR);
   }

   /**
    * Returns a platform-specific query to fetch column data from $Table.
    *
    * @param string $Table The name of the table to fetch column data from.
    */
   public function FetchColumnSql($Table) {
      trigger_error(ErrorMessage('The selected database engine does not perform the requested task.', $this->ClassName, 'FetchColumnSql'), E_USER_ERROR);
   }

   /**
    * Returns a platform-specific query to fetch table names.
    *
    * @param boolean $LimitToPrefix Should the query be limited to tables that have $this->Database->DatabasePrefix ?
    */
   public function FetchTableSql($LimitToPrefix = FALSE) {
      trigger_error(ErrorMessage('The selected database engine does not perform the requested task.', $this->ClassName, 'FetchTableSql'), E_USER_ERROR);
   }

   /**
    * Returns an array containing table names in the database.
    *
    * @param boolean $LimitToPrefix Should the query be limited to tables that have $this->DatabasePrefix ?
    */
   public function FetchTables($LimitToPrefix = FALSE) {
      $Sql = $this->FetchTableSql($LimitToPrefix);
      $Data = $this->Query($Sql);
      $Return = array();
      foreach($Data->ResultArray() as $Row) {
         if (isset($Row['TABLE_NAME']))
            $Return[] = $Row['TABLE_NAME'];
         else
            $Return[] = array_shift($Row);
      }

      return $Return;
   }

   /**
    * Returns an array of schema data objects for each field in the specified
    * table. The returned array of objects contains the following properties:
    * Name, PrimaryKey, Type, AllowNull, Default, Length, Enum.
    *
    * @param string $Table The name of the table to get schema data for.
    */
   public function FetchTableSchema($Table) {
      trigger_error(ErrorMessage('The selected database engine does not perform the requested task.', $this->ClassName, 'FetchTableSchema'), E_USER_ERROR);
   }

   /**
    * Returns a string of SQL that retrieves the database engine version in the
    * fieldname "version".
    */
   public function FetchVersionSql() {
      trigger_error(ErrorMessage('The selected database engine does not perform the requested task.', $this->ClassName, 'FetchVersionSql'), E_USER_ERROR);
   }

   /**
    * Returns an array containing column names from $Table.
    *
    * @param string $Table The name of the table to fetch column data from.
    */
   public function FetchColumns($Table) {
      $Sql = $this->FetchColumnSql($Table);
      $Data = $this->Query($Sql);
      $Return = array();
      foreach($Data->ResultArray() as $Row) {
         if (isset($Row['COLUMN_NAME']))
            $Return[] = $Row['COLUMN_NAME'];
         else
            $Return[] = current($Row);
      }

      return $Return;
   }

   /**
    * Takes a table name and makes sure it is formatted for this database
    * engine.
    *
    * @param string $Table The name of the table name to format.
    */
   public function FormatTableName($Table) {
      trigger_error(ErrorMessage('The selected database engine does not perform the requested task.', $this->ClassName, 'FormatTableName'), E_USER_ERROR);
   }
   
   /**
    * The table(s) from which to select values. Returns this object for
    * chaining purposes.
    *
    * @param mixed $From A string or array of table names/aliases from which to select data.
    * Accepted formats include:
    *    user
    *    user, user u2, role
    *    array("user u", "user u2", "role")
    **/
   public function From($From) {
      if (!is_array($From))
         $From = array($From);

      $Count = count($From);
      $i = 0;
      for ($i = 0; $i < $Count; ++$i) {
         $this->_Froms[] = $this->EscapeIdentifier($this->MapAliases($From[$i]));
      }

      return $this;
   }

   /**
    * Returns a string of comma delimited table names to select from.
    *
    * @param mixed $Tables The name of a table (or an array of table names) to be added in the from
    * clause of a query.
    */
   protected function _FromTables($Tables) {
      return is_array($Tables) ? implode(', ', $Tables) : $Tables;
   }

   /**
    * Builds the select statement and runs the query, returning a result object.
    *
    * @param string $Table          The table from which to select data. Adds to the $this->_Froms collection.
    * @param string $OrderFields    A string of fields to be ordered.
    * @param string $OrderDirection The direction of the sort.
    * @param int    $Limit          Adds a limit to the query.
    * @param int    $PageNumber     The page of data to retrieve.
    * @return DataSet
    */
   public function Get($Table = '', $OrderFields = '', $OrderDirection = 'asc', $Limit = FALSE, $PageNumber = FALSE) {
      if ($Table != '') {
         //$this->MapAliases($Table);
         $this->From($Table);
      }

      if ($OrderFields != '')
         $this->OrderBy($OrderFields, $OrderDirection);

      if ($Limit !== FALSE) {
         if ($PageNumber == FALSE || $PageNumber < 1)
            $PageNumber = 1;

         $Offset = ($PageNumber - 1) * $Limit;
         $this->Limit($Limit, $Offset);
      }

      $Result = $this->Query($this->GetSelect());
      return $Result;
   }
   
   /**
    * A helper function for escaping sql identifiers.
    * @param string The sql containing identifiers to escape in a different language.
    *   All identifiers requiring escaping should be enclosed in back ticks (`).
    * @return array All of the tokens in the sql. The tokens that require escaping will still have back ticks.
    */
   protected function _GetIdentifierTokens($Sql) {
      $Tokens = preg_split('/`/', $Sql, -1, PREG_SPLIT_DELIM_CAPTURE);
      $Result = array();
      
      $InIdent = FALSE;
      $CurrentToken = '';
      for($i = 0; $i < count($Tokens); $i++) {
         $Token = $Tokens[i];
         $Result .= $Token;
         if($Token == '`') {
            if($InIdent && $i < count($Tokens) - 1 && $Tokens[$i + 1] == '`') {
               // This is an escaped back tick.
               $i++; // skip next token
            } else if($InIdent) {
               $Result[] = $CurrentToken;
               $CurrentToken = $CurrentToken;
               $InIdent = false;
            } else {
               $InIdent = true;
            }
         } else if(!$InIdent) {
            $Result[] = $CurrentToken;
            $CurrentToken = '';
         }
      }
      
      return $Result;
   }

   /**
    * Returns the total number of records in the specified table.
    *
    * @param string $Table The table from which to count rows of data.
    * @param mixed  $Where Adds to the $this->_Wheres collection using $this->Where();
    */
   public function GetCount($Table = '', $Where = FALSE) {
      if($Table != '') {
         //$this->MapAliases($Table);
         $this->From($Table);
      }

      if ($Where !== FALSE)
         $this->Where($Where);

      $this->Select('*', 'count', 'RowCount');
      $Sql = $this->GetSelect();
      $Result = $this->Query($Sql);

      $CountData = $Result->FirstRow();
      return $CountData->RowCount;
   }

   /**
    * Returns the total number of records in the specified table.
    *
    * @param string $Table The table from which to count rows of data.
    * @param mixed  $Like  Adds to the $this->_Wheres collection using $this->Like();
    */
   public function GetCountLike($Table = '', $Like = FALSE) {
      if ($Table != '') {
         $this->MapAliases($Table);
         $this->From($Table);
      }

      if ($Like !== FALSE)
         $this->Like($Like);

      $this->Select('*', 'count', 'RowCount');
      $Result = $this->Query($this->GetSelect());

      $CountData = $Result->FirstRow();
      return $CountData->RowCount;
   }

   /**
    * Returns a delete statement for the specified table and the supplied
    * conditions.
    *
    * @param string $TableName The name of the table to delete from.
    * @param array $Wheres An array of where conditions.
    */
   public function GetDelete($TableName, $Wheres = array()) {
      trigger_error(ErrorMessage('The selected database engine does not perform the requested task.', $this->ClassName, 'GetDelete'), E_USER_ERROR);
   }

   /**
    * Returns an insert statement for the specified $Table with the provided $Data.
    *
    * @param string $Table The name of the table to insert data into.
    * @param string $Data An associative array of FieldName => Value pairs that should be inserted
    * $Table.
    */
   public function GetInsert($Table, $Data) {
      trigger_error(ErrorMessage('The selected database engine does not perform the requested task.', $this->ClassName, 'GetInsert'), E_USER_ERROR);
   }
   
   /**
    * Adds a limit clause to the provided query for this database engine.
    *
    * @param string $Query The SQL string to which the limit statement should be appended.
    * @param int $Limit The number of records to limit the query to.
    * @param int $Offset The number of records to offset the query from.
    */
   public function GetLimit($Query, $Limit, $Offset) {
      trigger_error(ErrorMessage('The selected database engine does not perform the requested task.', $this->ClassName, 'GetLimit'), E_USER_ERROR);
   }

   /**
    * Builds the select statement based on the various collections in this
    * object. This method should not be called directly; it is called by
    * $this->Get() and $this->GetWhere().
    */
   public function GetSelect() {
      // Close off any open query elements.
      $this->_EndQuery();
      
      $Sql = (!$this->_Distinct) ? 'select ' : 'select distinct ';

      // Don't escape the field if it is numeric or an asterisk (all columns)
      $Selects = array();
      foreach($this->_Selects as $Key => $Expr) {
         $Field = $Expr['Field'];
         $Function = $Expr['Function'];
         $Alias = $Expr['Alias'];
         $CaseOptions = ArrayValue('CaseOptions', $Expr);
         if ($Field != '*' && !is_numeric($Field))
            $Field = $this->EscapeIdentifier($Field);

         if ($Alias == '' && $Function != '')
            $Alias = $Field;

         // if (in_array(strtolower($Function), array('max', 'min', 'avg', 'sum', 'count')))
         if ($Function != '') {
				if(strpos($Function, '%s') !== FALSE)
					$Field = sprintf($Function, $Field);
				else
					$Field = $Function.'('.$Field.')';
			}

         if ($CaseOptions !== FALSE)
            $Field = 'case ' . $Field . $CaseOptions . ' end';

         if ($Alias != '')
            $Field .= ' as '.$this->QuoteIdentifier($Alias);

         if ($Field != '')
            $Selects[] = $Field;
      }
      $Sql .= (count($Selects) == 0) ? '*' : implode(', ', $Selects);

      if (count($this->_Froms) > 0)
         $Sql .= "\nfrom ".$this->_FromTables($this->_Froms);

      if (count($this->_Joins) > 0) {
         $Sql .= "\n";
         $Sql .= implode("\n", $this->_Joins);
      }

      if (count($this->_Wheres) > 0)
         $Sql .= "\nwhere ";

      $Sql .= implode("\n", $this->_Wheres);

      // Close any where groups that were left open.
      for ($i = 0; $i < $this->_OpenWhereGroupCount; ++$i) {
         $Sql .= ')';
      }
      $this->_OpenWhereGroupCount = 0;

      if (count($this->_GroupBys) > 0) {
         $Sql .= "\ngroup by ";

         // special consideration for table aliases
         if (count($this->_AliasMap) > 0 && $this->Database->DatabasePrefix)
            $Sql .= implode(', ', $this->_FilterTableAliases($this->_GroupBys));
         else
            $Sql .= implode(', ', $this->_GroupBys);
      }

      if (count($this->_Havings) > 0)
         $Sql .= "\nhaving ".implode("\n", $this->_Havings);

      if (count($this->_OrderBys) > 0)
         $Sql .= "\norder by ".implode(', ', $this->_OrderBys);

      if (is_numeric($this->_Limit)) {
         $Sql .= "\n";
         $Sql = $this->GetLimit($Sql, $this->_Limit, $this->_Offset);
      }

      return $Sql;
   }

   /**
    * Returns a truncate statement for this database engine.
    *
    * @param string $Table The name of the table to updated data in.
    */
   public function GetTruncate($Table) {
      trigger_error(ErrorMessage('The selected database engine does not perform the requested task.', $this->ClassName, 'GetTruncate'), E_USER_ERROR);
   }

   /**
    * Returns an update statement for the specified table with the provided
    * $Data.
    *
    * @param array $Tables The names of the tables to updated data in.
    * @param array $Data An associative array of FieldName => Value pairs that should be inserted
    * $Table.
    * @param mixed $Where A where clause (or array containing multiple where clauses) to be applied
    * to the where portion of the update statement.
    */
   public function GetUpdate($Tables, $Data, $Where) {
      trigger_error(ErrorMessage('The selected database engine does not perform the requested task.', $this->ClassName, 'GetUpdate'), E_USER_ERROR);
   }

   /**
    * Builds the select statement and runs the query, returning a result
    * object. Allows a where clause, limit, and offset to be added directly.
    *
    * @param string $Table The table from which to select data. Adds to the $this->_Froms collection.
    * @param mixed  $Where Adds to the $this->_Wheres collection using $this->Where();
    * @param string $OrderFields A string of fields to be ordered.
    * @param string $OrderDirection The direction of the sort.
    * @param int    $Limit The number of records to limit the query to.
    * @param int    $PageNumber The offset where the query results should begin.
    */
   public function GetWhere($Table = '', $Where = FALSE, $OrderFields = '', $OrderDirection = 'asc', $Limit = FALSE, $PageNumber = FALSE) {
      if ($Table != '') {
         //$this->MapAliases($Table);
         $this->From($Table);
      }

      if ($Where !== FALSE)
         $this->Where($Where);

      if ($OrderFields != '')
         $this->OrderBy($OrderFields, $OrderDirection);

      if ($Limit !== FALSE) {
         if ($PageNumber == FALSE || $PageNumber < 1)
            $PageNumber = 1;

         $Offset = ($PageNumber - 1) * $Limit;
         $this->Limit($Limit, $Offset);
      }

      $Result = $this->Query($this->GetSelect());
      
      return $Result;
   }

   /**
    * Builds the select statement and runs the query, returning a result
    * object. Allows a like clause, limit, and offset to be added directly.
    *
    * @param string $Table The table from which to select data. Adds to the $this->_Froms collection.
    * @param mixed  $Like Adds to the $this->_Wheres collection using $this->Like();
    * @param string $OrderFields A string of fields to be ordered.
    * @param string $OrderDirection The direction of the sort.
    * @param int    $Limit The number of records to limit the query to.
    * @param int    $PageNumber The offset where the query results should begin.
    */
   public function GetWhereLike($Table = '', $Like = FALSE, $OrderFields = '', $OrderDirection = 'asc', $Limit = FALSE, $PageNumber = FALSE) {
      if ($Table != '') {
         $this->MapAliases($Table);
         $this->From($Table);
      }

      if ($Like !== FALSE)
         $this->Like($Like);

      if ($OrderFields != '')
         $this->OrderBy($OrderFields, $OrderDirection);

      if ($Limit !== FALSE) {
         if ($PageNumber == FALSE || $PageNumber < 1)
            $PageNumber = 1;

         $Offset = ($PageNumber - 1) * $Limit;
         $this->Limit($Limit, $Offset);
      }

      $Result = $this->Query($this->GetSelect());
      
      return $Result;
   }

   /**
    * Adds to the $this->_GroupBys collection.
    *
    * @param mixed $Fields An array of field names (or a comma-delimited list of field names) to be
    * grouped by.
    */
   public function GroupBy($Fields = NULL) {
      if(is_null($Fields)) {
         // Group by every item in the select that isn't a function.
         foreach($this->_Selects as $Alias => $Select) {
            if(ArrayValue('Function', $Select) == '') {
               $this->_GroupBys[] = $Select['Field'];
            }
         }
         return $this;
      }
      
      if (is_string($Fields))
         $Fields = explode(',', $Fields);

      foreach ($Fields as $Field) {
         $Field = trim($Field);

         if ($Field != '')
            $this->_GroupBys[] = $this->EscapeIdentifier($Field);
      }
      return $this;
   }
   
   /**
    * Adds to the $this->_Havings collection.
    *
    * This is the most basic having that adds a freeform string of text.
    * It should be used only in conjunction with methods that properly escape the sql.
    *
    * @param string $Sql The condition to add.
    * @return Gdn_DatabaseDriver $this
    */
   protected function _Having($Sql) {
      // Figure out the concatenation operator.
      $Concat = '';
      
      if(count($this->_Havings) > 0) {
         $Concat = ' ' . $this->_WhereConcat . ' ';
      }
      
      // Revert the concat back to 'and'.
      $this->_WhereConcat = $this->_WhereConcatDefault;
      
      $this->_Havings[] = $Concat . $Sql;

      return $this;
   }
   
   /**
    * Adds to the $this->_Havings collection. Called by $this->Having() and
    * $this->OrHaving().
    *
    * @param mixed $Field The name of the field (or array of field names) in the having clause.
    * @param string $Value The string on the right side of the having comparison.
    * @param boolean $EscapeSql A boolean value indicating if $this->EscapeSql method should be called
    * on $Field.
    * @param boolean $EscapeString A boolean value indicating if $this->EscapeString method should be called
    * on $Value.
    */
   function Having($Field, $Value = '', $EscapeField = TRUE, $EscapeValue = TRUE) {
      if (!is_array($Field))
         $Field = array($Field => $Value);

      foreach ($Field as $ChildField => $ChildValue) {
         $Expr = $this->ConditionExpr($ChildField, $ChildValue, $EscapeField, $EscapeValue);
         $this->_Having($Expr);
      }

      return $this;
   }
   
   public function History($UpdateFields = TRUE, $InsertFields = FALSE) {
      $UserID = Gdn::Session()->UserID;
      if($InsertFields)
         $this->Set('DateInserted', Gdn_Format::ToDateTime())->Set('InsertUserID', $UserID);
      if($UpdateFields)
         $this->Set('DateUpdated', Gdn_Format::ToDateTime())->Set('UpdateUserID', $UserID);
      return $this;
   }

   /**
    * Returns the last identity to be inserted into the database at
    * $this->_Connection.
    */
   public function Identity() {
      return $this->Connection()->lastInsertId();
   }

   /**
    * Returns information about the database. Values include: Engine, Version,
    * DatabaseName.
    *
    * @param string $Request The piece of information being requested. Accepted values are: Engine,
    * Version, and DatabaseName.
    */
   public function Information($Request) {
      if (array_key_exists($Request, $this->_DatabaseInfo) === FALSE) {
         if ($Request == 'Version') {
            $this->_DatabaseInfo['Version'] = $this->Version();
         } else {
            $this->_DatabaseInfo['HostName'] = Gdn::Config('Database.Host', '');
            $this->_DatabaseInfo['DatabaseName'] = Gdn::Config('Database.Name', '');
         }
      }
      if (array_key_exists($Request, $this->_DatabaseInfo) === TRUE) {
         return $this->_DatabaseInfo[$Request];
      } else {
         return '';
      }
   }
   
   /**
    * Builds the insert statement and runs the query, returning a result
    * object.
    *
    * @param string $Table The table to which data should be inserted.
    * @param mixed  $Set An associative array (or object) of FieldName => Value pairs that should
    * be inserted, or an array of FieldName values that should have values
    * inserted from $Select.
    * @param string $Select A select query that will fill the FieldNames specified in $Set.
    */
   public function Insert($Table = '', $Set = NULL, $Select = '') {
      if (count($Set) == 0 && count($this->_Sets) == 0) {
         return FALSE;
      }
      
      if (!is_null($Set) && $Select == '' && !array_key_exists(0, $Set)) {
         $this->Set($Set);
         $Set = $this->_Sets;
      }

      if ($Table == '') {
         if (!isset($this->_Froms[0]))
            return FALSE;

         $Table = $this->_Froms[0];
      }

      $Sql = $this->GetInsert($this->EscapeIdentifier($this->Database->DatabasePrefix.$Table), $Set, $Select);
      $Result = $this->Query($Sql);
      
      return $Result;
   }
   
   /**
    * Inserts or updates values in the table depending on whether they are already there.
    *
    * @param string $Table The name of the table to insert/update.
    * @param array $Set The columns to update.
    * @param array $Where The columns to find the row to update.
    * If a row is not found then one is inserted and the items in this array are merged with $Set.
    */
   public function Replace($Table = '', $Set = NULL, $Where) {
      if(count($this->_Sets) > 0) {
         foreach($this->_Sets as $Key => $Value) {
            if(array_key_exists($Value, $this->_NamedParameters)) {
               $Set[$Key] = $this->_NamedParameters[$Value];
               unset($this->_NamedParameters[$Value]);
            } else {
               $Set[$Key] = $Value;
            }
         }
         $this->_Sets = array();
      }
      
      // Check to see if there is a row in the table like this.
      $Count = $this->GetCount($Table, $Where);
      if($Count > 0) {
         // Update the table.
         $this->Put($Table, $Set, $Where);
      } else {
         // Insert the table.
         $Set = array_merge($Set, $Where);
         $this->Insert($Table, $Set);
      }
   }
   
   /**
    * The table(s) to which this query should join. Returns this object for
    * chaining purposes.
    *
    * @param string $TableName The name of a single table to join to.
    * @param string $On The conditions on which the join should occur.
    * ie. "user.role_id = role.id"
    * @param string $Join The type of join to be made. Accepted values are:
    * 'inner', 'outer', 'left', 'right', 'left outer', and 'right outer'.
    * @return Gdn_SQLDriver
    */
   public function Join($TableName, $On, $Join = '') {
      $Join = strtolower(trim($Join));
      if ($Join != '' && !in_array($Join, array('inner', 'outer', 'left', 'right', 'left outer', 'right outer'), TRUE))
         $Join = '';
         
      // Add the table prefix to any table specifications in the clause
      // echo '<div>'.$TableName.' ---> '.$this->EscapeSql($this->Database->DatabasePrefix.$TableName, TRUE).'</div>';
      if($this->Database->DatabasePrefix) {
         $TableName = $this->MapAliases($TableName);

         //$Aliases = array_keys($this->_AliasMap);
         //$Regex = '';
         //foreach ($Aliases as $Alias) {
         //   $Regex .= '(?<! '.$Alias.')';
         //}
         //$Regex = '/(\w+'.$Regex.'\.)/';
         //$On = preg_replace($Regex, $this->Database->DatabasePrefix.'$1', ' '.$On);
      }
      $JoinClause = ltrim($Join . ' join ') . $this->EscapeIdentifier($TableName, TRUE) . ' on ' . $On;
      $this->_Joins[]  = $JoinClause;

      return $this;
   }
   
   /**
    * A convenience method for Gdn_DatabaseDriver::Join that makes the join type 'left.'
    * @see Gdn_DatabaseDriver::Join()
    */
   public function LeftJoin($TableName, $On) {
      return $this->Join($TableName, $On, 'left');
   }
   
   /**
    * Adds to the $this->_Wheres collection. Used to generate the LIKE portion
    * of a query. Called by $this->Like(), $this->NotLike()
    *
    * @param mixed $Field The field name (or array of field name => match values) to search in for
    * a like $Match.
    * @param string $Match The value to try to match using a like statement in $Field.
    * @param string $Concat The concatenation operator for the items being added to the like in
    * clause.
    * @param string $Side A string indicating which side of the match to place asterisk operators.
    * Accepted values are left, right, both, none. Default is both.
    * @param string $Op Either 'like' or 'not like' clause.
    * @return Gdn_SQLDriver
    */
   public function Like($Field, $Match = '', $Side = 'both', $Op = 'like') {
      if (!is_array($Field))
         $Field = array($Field => $Match);

      foreach ($Field as $SubField => $SubValue) {
         $SubField .= ' ' . $Op . ' ';
         switch($Side) {
            case 'left':
               $SubValue = '%' . $SubValue;
               break;
            case 'right':
               $SubValue .= '%';
               break;
            case 'both':
               if(strlen($Match) == 0)
                  $SubValue = '%';
               else
                  $SubValue = '%' . $SubValue . '%';
               break;
         }
         $Expr = $this->ConditionExpr($SubField, $SubValue);
         $this->_Where($Expr);
      }
      return $this;
   }

   /**
    * Sets the limit (and offset optionally) for the query.
    *
    * @param int $Limit  The number of records to limit the query to.
    * @param int $Offset The offset where the query results should begin.
    */
   public function Limit($Limit, $Offset = FALSE) {
      $this->_Limit = $Limit;

      if ($Offset !== FALSE)
         $this->_Offset = $Offset;

      return $this;
   }
   
   /**
    * Takes a provided table specification and parses out any table aliases
    * provided, placing them in an alias mapping array. Returns the table
    * specification with any table prefix prepended.
    *
    * @param string $TableString The string specification of the table. ie.
    * "tbl_User as u" or "user u".
    * @return string
    */
   public function MapAliases($TableString) {
      // Make sure all tables have an alias.
      if(strpos($TableString, ' ') === FALSE) {
         $TableString .= ' ' . $TableString;
      }
      
      // Map the alias to the alias mapping array
      $TableString = trim(preg_replace('/\w+as\w+/i', ' ', $TableString));
      $Alias = strrchr($TableString, " ");
      $TableName = substr($TableString, 0, strlen($TableString) - strlen($Alias));
   
      // If no alias was specified then it will be set to the tablename.
      $Alias = trim($Alias);
      if(strlen($Alias) == 0) {
         $Alias = $TableName;
         $TableString .= ' ' . $Alias;
      }
      
      //$this->_AliasMap[$Alias] = $TableName;

      // Return the string with the database table prefix prepended
      return $this->Database->DatabasePrefix . $TableString;
   }
   
   /**
    * A convenience method for Gdn_DatabaseDriver::Like that changes the operator to 'not like.'
    * @see Gdn_DatabaseDriver::Like()
    */
   public function NotLike($Field, $Match = '', $Side = 'both') {
      return $this->Like($Field, $Match, $Side, 'not like');
   }

   /**
    * Takes a parameter name and makes sure it is cleaned up to be used as a
    * named parameter in a pdo prepared statement.
    * @param string $Name The name of the parameter to cleanup
    * @param boolean $CreateNew Wether or not this is a new or existing parameter.
    * @return string The cleaned up named parameter name.
    */
   public function NamedParameter($Name, $CreateNew = FALSE, $Value = NULL) {
      // Format the parameter name so it is safe for sql
      $NiceName = ':'.preg_replace('/([^\w\d_-])/', '', $Name); // Removes everything from the string except letters, numbers, dashes, and underscores

      if($CreateNew) {
         // Make sure that the new name doesn't already exist.
         $NumberedName = $NiceName;
         $i = 0;
         while (array_key_exists($NumberedName, $this->_NamedParameters)) {
            $NumberedName = $NiceName.$i;
            ++$i;
         }
         $NiceName = $NumberedName;
      }
      
      if(!is_null($Value)) {
         $this->_NamedParameters[$NiceName] = $Value;
      }
         
      return $NiceName;
   }
   
   /**
    * Allows a query to be called without resetting the object.
    * @param boolean $Reset Whether or not to reset this object when the next query executes.
    * @param boolean $OneTime Whether or not this will apply for only the next query or for all subsequent queries.
    */
   public function NoReset($NoReset = TRUE, $OneTime = TRUE) {
      $_NoReset = $NoReset ? ($OneTime ? 1 : 2) : 0;
      return $this;
   }

   /**
    * Sets the offset for the query.
    *
    * @param int $Offset The offset where the query results should begin.
    */
   public function Offset($Offset) {
      $this->_Offset = $Offset;
      return $this;
   }

   /**
    * Adds to the $this->_OrderBys collection.
    *
    * @param string $Fields A string of fields to be ordered.
    * @param string $Direction The direction of the sort.
    */
   public function OrderBy($Fields, $Direction = 'asc') {
      if ($Direction != 'asc')
         $Direction = 'desc';

      $this->_OrderBys[] = $this->EscapeIdentifier($Fields, TRUE).' '.$Direction;
      return $this;
   }
   
   /**
    * Adds to the $this->_Havings collection. Concatenates multiple calls with OR.
    *
    * @param mixed $Field The name of the field (or array of field names) in the having clause.
    * @param string $Value The string on the right side of the having comparison.
    * @param boolean $EscapeField A boolean value indicating if $this->EscapeSql method should be called
    * on $Field.
    * @param boolean $EscapeValue A boolean value indicating if $this->EscapeString method should be called
    * on $Value.
    * @return Gdn_DatabaseDriver $this.
    * @see Gdn_DatabaseDriver::Having()
    */
   function OrHaving($Field, $Value = '', $EscapeField = TRUE, $EscapeValue = TRUE) {
      return $this->OrOp()->Having($Field, $Value, $EscapeField, $EscapeValue);
   }
   
   /**
    * A convenience method that calls Gdn_DatabaseDriver::Like with concatenated with an 'or.'
    * @See Gdn_DatabaseDriver::Like()
    */
   public function OrLike($Field, $Match = '', $Side = 'both', $Op = 'like') {
      if (!is_array($Field))
         $Field = array($Field => $Match);
      
      foreach ($Field as $f => $v) {
         $this->OrOp()->Like($f, $v, $Side, $Op);
      }
      return $this;

//       return $this->OrOp()->Like($Field, $Match, $Side, $Op);
   }
   
   /** A convenience method for Gdn_DatabaseDriver::Like that changes the operator to 'not like,'
    *    and is concatenated with an 'or.'
    * @see Gdn_DatabaseDriver::NotLike()
    * @see GenricDriver::Like()
    */
   public function OrNotLike($Field, $Match = '', $Side = 'both') {
      return $this->OrLike($Field, $Match, $Side, 'not like');
   }
   
   /**
    * Concat the next where expression with an 'or' operator.
    *
    * @param boolean $SetDefault Whether or not the 'or' is one time, or will revert.
    * @return Gdn_DatabaseDriver $this
    * @see Gdn_DatabaseDriver::AndOp()
    */
   public function OrOp($SetDefault = FALSE) {
      $this->_WhereConcat = 'or';
      if($SetDefault) {
         $this->_WhereConcatDefault = 'or';
      }
      
      return $this;
   }
      
   /**
    * @link Gdn_DatabaseDriver::Where()
    */
   public function OrWhere($Field, $Value = NULL, $EscapeFieldSql = TRUE, $EscapeValueSql = TRUE) {
      return $this->OrOp()->Where($Field, $Value, $EscapeFieldSql, $EscapeValueSql);
   }
   
   /**
    * A convienience method for Gdn_DatabaseDriver::WhereExists() concatenates with an 'or.'
    * @see Gdn_DatabaseDriver::WhereExists()
    */
   public function OrWhereExists($SqlDriver, $Op = 'exists') {
      return $this->OrOp()->WhereExists($SqlDriver, $Op);
   }
   
   /**
    * @ling Gdn_DatabaseDriver::WhereIn()
    */
   public function OrWhereIn($Field, $Values) {
      return $this->OrOp()->WhereIn($Field, $Values);
   }
    
   /**
    * A convienience method for Gdn_DatabaseDriver::WhereExists() that changes the operator to 'not exists,'
    *   and concatenates with an 'or.'
    * @see Gdn_DatabaseDriver::WhereExists()
    * @see Gdn_DatabaseDriver::WhereNotExists()
    */
   public function OrWhereNotExists($SqlDriver) {
      return $this->OrWhereExists($SqlDriver, 'not exists');
   }
     
   /**
    * A convenience method for Gdn_DatabaseDriver::WhereIn() that changes the operator to 'not in,'
    *   and concatenates with an 'or.'
    * @see Gdn_DatabaseDriver::WhereIn()
    * @see Gdn_DatabaseDriver::WhereNotIn()
    */
   public function OrWhereNotIn($Field, $Values) {
      return $this->OrOp()->WhereNotIn($Field, $Values);
   }
   
   /**
    * Parses an expression for use in where clauses.
    *
    * @param string $Expr The expression to parse.
    * @param string $Name A name to give the parameter if $Expr becomes a named parameter.
    * @return string The parsed expression.
    */
   protected function _ParseExpr($Expr, $Name = NULL, $EscapeExpr = FALSE) {
      $Result = '';
      
      $C = substr($Expr, 0, 1);
      
      if($C === '=' && $EscapeExpr === FALSE) {
         // This is a function call. Each parameter has to be parsed.
         $FunctionArray = preg_split('/(\[[^\]]+\])', substr($Expr, 1), -1, PREG_SPLIT_DELIM_CAPTURE);
         for($i = 0; $i < count($FunctionArray); $i++) {
            $Part = $FunctionArray[$i];
            if(substr($Part, 1) == '[') {
               // Translate the part of the function call.
               $Part = $this->_FieldExpr(substr($Part, 1, strlen($Part) - 2), $Name);
               $FunctionArray[$i] = $Part;
            }
         }
         // Combine the array back to the original function call.
         $Result = join($FunctionArray);
      } elseif($C === '@' && $EscapeExpr === FALSE) {
         // This is a literal. Don't do anything.
         $Result = substr($Expr, 1);
      } else {
         // This is a column reference.
         if(is_null($Name)) {
            $Result = $this->EscapeIdentifier($Expr);
         } else {
            // This is a named parameter.
            
            // Check to see if the named parameter is valid.
            if(in_array(substr($Expr, 0, 1), array('=', '@'))) {
               // The parameter has to be a default name.
               $Result = $this->NamedParameter('Param', TRUE);
            } else {
               $Result = $this->NamedParameter($Name, TRUE);
            }
            $this->_NamedParameters[$Result] = $Expr;
         }
      }
   
      return $Result;
   }
   
   /**
    * Joins the query to a permission junction table and limits the results
    * accordingly.
    *
    * @param string $JunctionTable The table to join to (ie. Category)
    * @param string $JunctionColumn The primary key column name of $JunctionTable (ie. CategoryID).
    * @param mixed $Permissions The permission name (or array of names) to use when limiting the query.
    */
   public function Permission($JunctionTableAlias, $JunctionColumn, $Permissions) {
      $PermissionModel = Gdn::PermissionModel();
      $PermissionModel->SQLPermission($this, $JunctionTableAlias, $JunctionColumn, $Permissions);
  
      return $this;
   }

   /**
    * Prefixes a table with the database prefix if it is not already there.
    *
    * @param string $Table The table name to prefix.
    */
   public function PrefixTable($Table) {
      $Prefix = $this->Database->DatabasePrefix;
      
      if ($Prefix != '' && substr($Table, 0, strlen($Prefix)) != $Prefix)
         $Table = $Pref…

Large files files are truncated, but you can click here to view the full file