Location: PHPKode > projects > Escher CMS > eschercms-0.9.2/escher/sparkplug/plugs/sparkdb/sparkdb.php
<?php

/*
Copyright 2009-2011 Sam Weiss
All Rights Reserved.

This file is part of Spark/Plug.

Spark/Plug 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/>.
*/

if (!defined('spark/plug'))
{
	header('HTTP/1.1 403 Forbidden');
	exit('<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN"><html><head><title>403 Forbidden</title></head><body><h1>Forbidden</h1><p>You don\'t have permission to access the requested resource on this server.</p></body></html>');
}

// -----------------------------------------------------------------------------

class SparkDBModel extends SparkModel
{
	private static $_cache = array();

	// --------------------------------------------------------------------------

	public function __construct()
	{
		parent::__construct();
	}

	// --------------------------------------------------------------------------

	final public function loadDB($params = NULL)
	{
		// $params may be one of:
		//		1) empty (use default db config)
		//		2) string (label: use named db config)
		//		3) array (explicit connection settings)
		
		// first, determine the label for the requested connection
		
		if (empty($params))
		{
			$label = $this->config->get('database_default', 'default');
		}
		elseif (is_string($params))
		{
			$label = $params;
		}
		elseif (!empty($params['label']))
		{
			$label = $params['label'];
		}
		else
		{
			$label = @$params['hostname'] . '_' . @$params['database'] . '_' . @$params['username'];
		}

		// next, create a key for caching this connection
		
		$key =  'db_' . $label;
		
		// check the cache for the requested connection

		if (!$db = @self::$_cache[$key])
		{
			// if necesary, load the requested configuration
			
			if (!is_array($params))
			{
				if ((!$params = $this->config->get('db')) || (!$params = @$params[$label]))
				{
					throw new SparkException('No database configuration provided.');
				}
			}

 			$db = _SparkDatabase::connect($params);

			// cache the connection for subsequent access
			
			self::$_cache[$key] = $db;
		}

		return $db;
	}
}

// -----------------------------------------------------------------------------

abstract class SparkDBQueryFunction
{
	// --------------------------------------------------------------------------

	// Abstract Public Methods: To be implemented by implementation class

	// --------------------------------------------------------------------------

	abstract public function compile();
	abstract public function clear();
}

// -----------------------------------------------------------------------------

interface iSparkDBQueryFunctionLiteral
{
	// --------------------------------------------------------------------------

	// Abstract Public Methods: To be implemented by implementation class

	// --------------------------------------------------------------------------

	public function literal($literal);
}

// -----------------------------------------------------------------------------

interface iSparkDBQueryFunctionMetadata
{
	// --------------------------------------------------------------------------

	// Abstract Public Methods: To be implemented by implementation class

	// --------------------------------------------------------------------------

	public function columns($table);
}

// -----------------------------------------------------------------------------

interface iSparkDBQueryFunctionCreateTable
{
	const kFieldTypeInteger = 1;
	const kFieldTypeFloat = 2;
	const kFieldTypeString = 3;
	const kFieldTypeText = 4;
	const kFieldTypeDate = 5;
	const kFieldTypeBoolean = 6;
	const kFieldTypeBinary = 7;

	const kFieldTypeByte = 8;
	const kFieldTypeShort = 9;
	const kFieldTypeLong = 10;

	const kFlagPrimaryKey = 1;
	const kFlagAutoincrement = 2;

	const kForeignKeyTriggerDelete = 1;
	const kForeignKeyTriggerUpdate = 2;

	const kForeignKeyActionNone = 1;
	const kForeignKeyActionCascade = 2;
	const kForeignKeyActionRestrict = 3;
	const kForeignKeyActionSetNULL = 4;
	const kForeignKeyActionSetDefault = 5;
	
	// --------------------------------------------------------------------------

	// Abstract Public Methods: To be implemented by implementation class

	// --------------------------------------------------------------------------

	public function table($name, $options = NULL);
	public function field($name, $type, $length = NULL, $default = NULL, $canBeNULL = false, $flags = NULL);
	public function primaryKey($columns);
	public function foreignKey($columns, $foreignTable, $foreignColumns, $actions = NULL);
}

// -----------------------------------------------------------------------------

interface iSparkDBQueryFunctionAlterTable
{
	// --------------------------------------------------------------------------

	// Abstract Public Methods: To be implemented by implementation class

	// --------------------------------------------------------------------------

	public function table($name, $options = NULL);
	public function field($name, $type, $length = NULL, $default = NULL, $canBeNULL = false, $flags = NULL);
	public function primaryKey($columns);
	public function foreignKey($columns, $foreignTable, $foreignColumns, $actions = NULL);
}

// -----------------------------------------------------------------------------

interface iSparkDBQueryFunctionCreateIndex
{
	const kIndexTypeNormal = 1;
	const kIndexTypeUnique = 2;

	// --------------------------------------------------------------------------

	// Abstract Public Methods: To be implemented by implementation class

	// --------------------------------------------------------------------------

	public function table($name);
	public function index($type, $fields, $name = NULL);
}

// -----------------------------------------------------------------------------

interface iSparkDBQueryFunctionDropIndex
{
	// --------------------------------------------------------------------------

	// Abstract Public Methods: To be implemented by implementation class

	// --------------------------------------------------------------------------

	public function table($name);
	public function drop($name);
}

// -----------------------------------------------------------------------------

interface iSparkDBQueryFunctionResetAutoIncrement
{
	// --------------------------------------------------------------------------

	// Abstract Public Methods: To be implemented by implementation class

	// --------------------------------------------------------------------------

	public function table($name);
	public function reset($value = 1);
}

// -----------------------------------------------------------------------------

interface iSparkDBQueryFunctionDate
{
	const kSeconds = 1;
	const kMinutes = 2;
	const kHours = 3;
	const kDays = 4;

	// --------------------------------------------------------------------------

	// Abstract Public Methods: To be implemented by implementation class

	// --------------------------------------------------------------------------

	public function date($dateStr);
	public function now();
	public function add();
	public function sub();
	public function interval($num, $type);
}

// -----------------------------------------------------------------------------

interface iSparkDBQueryFunctionReplace
{
	// --------------------------------------------------------------------------

	// Abstract Public Methods: To be implemented by implementation class

	// --------------------------------------------------------------------------

	public function replace($search, $replace, $column);
}

// -----------------------------------------------------------------------------

interface iSparkDBQueryFunctionCondition
{
	// --------------------------------------------------------------------------

	// Abstract Public Methods: To be implemented by implementation class

	// --------------------------------------------------------------------------

	public function condition($condition, $if, $else);
}

// -----------------------------------------------------------------------------

final class SparkDBException extends SparkException
{
	const kUnknown = 0;
	const kCannotConnect = 1;
	const kDuplicateRecord = 2;
	const kFailedTransaction = 3;
	const kFunctionNotImplemented = 4;
	const kUnknownColumn = 5;
	const kDatabaseExists = 6;
	
	private $_dbErrorCode;

	// --------------------------------------------------------------------------

	public function __construct($message, $code = 0)
	{
		parent::__construct($message);
		$this->_dbErrorCode = $code;
	}

	// --------------------------------------------------------------------------

	public function dbErrorCode()
	{
		return $this->_dbErrorCode;
	}
}

// -----------------------------------------------------------------------------

final class SparkDBResult
{
	private $_numCols;			// number of columns in result
	private $_numRows;			// number of rows in result
	private $_rows;				// resultant rows

	// --------------------------------------------------------------------------

	public function __construct($pdoResult)
	{
		if ($this->_numCols = $pdoResult->columnCount())
		{
			$pdoResult->setFetchMode(PDO::FETCH_ASSOC);
			$this->_rows = $pdoResult->fetchAll();
			$this->_numRows = count($this->_rows);
		}
		else
		{
			$this->_numRows = 0;
			$this->_rows = NULL;
		}
		$pdoResult->closeCursor();
	}

	// --------------------------------------------------------------------------

	// Public Methods

	// --------------------------------------------------------------------------

	public function numRows()
	{
		return $this->_numRows;
	}
	
	// --------------------------------------------------------------------------

	public function numColumns()
	{
		return $this->_numCols;
	}

	// --------------------------------------------------------------------------

	public function rows($index = NULL)
	{
		if ($index === NULL)
		{
			return $this->_rows;
		}

		$rows = array();
		foreach ($this->_rows as $row)
		{
			$rows[$row[$index]] = $row;
		}
		return $rows;
	}

	// --------------------------------------------------------------------------

	public function row()
	{
		if ($this->numRows() > 0)
		{
			return $this->_rows[0];
		}
		else
		{
			return false;
		}
	}
}

// -----------------------------------------------------------------------------

class SparkDBFunction_literal extends SparkDBQueryFunction implements iSparkDBQueryFunctionLiteral
{
	private $_db;
	private $_literal;
	
	public function __construct($db)
	{
		return $this->_db = $db;
	}

	public function compile()
	{
		return $this->_literal;
	}
	
	public function clear()
	{
		$this->_literal = '';
		return $this;
	}

	public function literal($literal)
	{
		$this->_literal = preg_replace_callback('/"([^"]*)"/', array($this, 'quote'), $literal);
		return $this;
	}

	private function quote($matches)
	{
		return $this->_db->quoteIdentifier($matches[1]);
	}
}

// -----------------------------------------------------------------------------

abstract class SparkDBFunction_metadata extends SparkDBQueryFunction implements iSparkDBQueryFunctionMetadata
{
	protected $_db;

	public function __construct($db)
	{
		$this->_db = $db;
	}

	public function compile() {}
	public function clear() {}

	public function tables($filterByPrefix = true, $stripPrefix = true)
	{
		$prefix = $this->_db->tablePrefix();
		$tables = array();

		if ($checkPrefix = (($prefix !== '') && ($filterByPrefix || $stripPrefix)))
		{
			$pattern = '/^' . preg_quote($prefix) . '/';
		}
		
		foreach ($this->_db->query($this->tables_sql())->rows() as $row)
		{
			$name = current($row);
			if ($checkPrefix)
			{
				if ($filterByPrefix)
				{
					if (!preg_match($pattern, $name))
					{
						continue;
					}
				}
				if ($stripPrefix)
				{
					$name = preg_replace($pattern, '',  $name);
				}
			}
			$tables[] = $name;
		}

		return $tables;
	}

	public function columns($table)
	{
		$columns = array();
		foreach ($this->_db->query($this->columns_sql($table))->rows() as $row)
		{
			$column = $row['column_name'];
			$columns[$column] = $column;
		}
		return $columns;
	}
	
	abstract protected function tables_sql();
	abstract protected function columns_sql($table);
}

// -----------------------------------------------------------------------------

abstract class _SparkDatabase extends SparkPlug
{
	private $_dbName;
	private $_charset;
	private $_tablePrefix;
	private $_connection;
	private $_transactionError;
	private $_transactionDepth;
	private $_affectedRows;
	private $_queryCount;
	private $_queryTime;
	private $_debugMode;
	
	// --------------------------------------------------------------------------

	final public static function connect($params)		// connection factory method
	{
		$spark = SparkApplication::instance();

		$spark->factory()->loadClass('SparkDatabase', true);
		
		if (!class_exists($adapterClass = 'SparkDB_' . $params['adapter'], false))
		{
			if ($adapter = $params['adapter'])
			{
				$myInfo = $spark->factory->getPlug('SparkDBModel');
				require(dirname($myInfo['file']) . '/adapters/' . SparkInflector::decamelize($adapter) . '.php');
			}
		}

		return $spark->factory()->manufacture($adapterClass, $params);
	}

	//---------------------------------------------------------------------------
	
	final public static function trimIfNotNull($str) { return ($str === NULL) ? $str : trim($str); }
	final public static function makeList(&$options)
	{
		return $options = array_map(array('_SparkDatabase', 'trimIfNotNull'), is_array($options) ? $options : explode(',', strval($options)));
	}

	// --------------------------------------------------------------------------

	public function __construct($params)
	{
		parent::__construct($params);

		$this->_dbName = @$params['database'];
		$this->_charset = isset($params['charset']) ? $params['charset'] : 'utf8';
		$this->_tablePrefix = isset($params['table_prefix']) ? $params['table_prefix'] : '';
		$this->_transactionError = false;
		$this->_transactionDepth = 0;
		$this->_affectedRows = 0;
		$this->_queryCount = 0;
		$this->_queryTime = 0;
		$this->_debugMode = !empty($params['debug']);

		if (empty($params['options']))
		{
			$params['options'] = array();
		}

		if (!empty($params['persistent']))
		{
			$params['options'][PDO::ATTR_PERSISTENT] = $params['persistent'];
		}

		try
		{
			$this->_connection = new PDO($params['dsn'], @$params['username'], @$params['password'], @$params['options']);
		}
		catch (Exception $e)
		{
			$this->_connection = NULL;
		}
		if (!$this->_connection)
		{
			throw new SparkDBException('Cannot connect to database.', SparkDBException::kCannotConnect);
		}
		$this->_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
	}
	
	// --------------------------------------------------------------------------

	final private function _raiseEx($statement = NULL)
	{
		$errorInfo = $statement ? $statement->errorInfo() : $this->_connection->errorInfo();
		throw new SparkDBException(!empty($errorInfo[2]) ? $errorInfo[2] : 'Database error', $this->translateErrorCode($errorInfo[1]));
	}

	// --------------------------------------------------------------------------

	final private function _preBind(&$sql, &$bind)
	{
		if ($bind !== NULL)
		{
			$bind = is_array($bind) ? (empty($bind) ? NULL : $bind) : array($bind);
		}
		
		if (!empty($bind))
		{
			$args = $bind;
			
			$segments = explode('?', $sql);
			$numSegments = count($segments);
			
			if ($numSegments <= count($args))
			{
				$args = array_slice($args, 0, $numSegments-1, true);
			}
			
			$sql = $segments[$i = 0];
			$seen_where = false;
			$seen_set = false;
			
			foreach ($args as $key => $arg)
			{
				if (!($seen_where || ($seen_where = (preg_match('/\s+WHERE\s+/i',  $segments[$i]) === 1))))
				{
					if (!$seen_set)
					{
						$seen_set = (preg_match('/\s+SET\s+/i',  $segments[$i]) === 1);
					}
				}
				if ($this->preBindArg($arg))
				{
					$sql .= $arg;
					unset($bind[$key]);
				}
				elseif ($arg === NULL)
				{
					if ($seen_where || !$seen_set)
					{
						$sql = preg_replace(array('/\s*!=\s*$/', '/\s*=\s*$/'), array(' IS NOT ', ' IS '), $sql, 1);
					}
					$sql .= 'NULL';
					unset($bind[$key]);
				}
				elseif (is_object($arg) && ($arg instanceof SparkDBQueryFunction))
				{
					$sql .= $arg->compile();
					unset($bind[$key]);
				}
				else
				{
					$sql .= '?';
				}
				$sql .= $segments[++$i];
			}
			
			$bind = array_values($bind);	// recompute the indexes for PDO
		}
		
		// prepend table prefix if necessary
		
		if (strpos($sql, '{') !== false)
		{
			$sql = preg_replace('/{(.+?)}/', $this->_tablePrefix.'$1', $sql);
		}
	}

	// --------------------------------------------------------------------------

	final private function _query($query, $bind = NULL)
	{
		return $this->execute(is_string($query) ? $this->prepare($query) : $query, $bind);
	}

	// --------------------------------------------------------------------------

	final public function name()
	{
		return $this->_dbName;
	}

	// --------------------------------------------------------------------------

	final public function charset()
	{
		return $this->_charset;
	}

	// --------------------------------------------------------------------------

	final public function tablePrefix()
	{
		return $this->_tablePrefix;
	}

	// --------------------------------------------------------------------------

	final public function begin()
	{
		if (++$this->_transactionCount == 1)
		{
			$this->_transactionError = false;
			try
			{
				if (!$this->_connection->beginTransaction())
				{
					$this->_raiseEx();
				}
			}
			catch (Exception $e)
			{
				--$this->_transactionCount;
				throw $e;
			}
		}
	}
	
	// --------------------------------------------------------------------------

	final public function commit()
	{
		if (--$this->_transactionCount == 0)
		{
			if ($this->_transactionError)
			{
				$this->rollback();
				throw new SparkDBException('Attempt to commit a failed transaction', SparkDBException::kFailedTransaction);
			}
			else
			{
				if (!$this->_connection->commit())
				{
					$this->_raiseEx();
				}
			}
		}
	}
	
	// --------------------------------------------------------------------------

	final public function rollback()
	{
		$this->_transactionError = true;
		
		if (--$this->_transactionCount == 0)
		{
			if (!$this->_connection->rollBack())
			{
				$this->_raiseEx();
			}
		}
	}
	
	// --------------------------------------------------------------------------

	final public function quote($str, $ptype = PDO::PARAM_STR)
	{
		return $this->_connection->quote($str, $ptype);
	}

	// --------------------------------------------------------------------------

	public function quoteIdentifier($ident)
	{
		return '"' . $ident . '"';
	}

	// --------------------------------------------------------------------------

	final public function prepare($sql)
	{
		$statement = $this->_connection->prepare($sql);
		if ($statement === false)
		{
			$this->_raiseEx($statement);
		}
		
		return $statement;
	}

	// --------------------------------------------------------------------------

	final public function execute($statement, $bind = NULL)
	{
		++$this->_queryCount;

		if ($this->_debugMode)
		{
			$start = microtime();
		}
		
		// Bind parameters - we use bindParam() rather than the simpler method of
		// passing the $bind array directly to execute(), because in the latter case
		// all parameters are treated as strings, which breaks the storage of BLOBs
		// in sqlite.
		//
		// To use blobs, they should be added to the $bind array as a single element
		// array, with the binary data in element[0].
		
		if (!empty($bind))
		{
			if (!is_array($bind))
			{
				$bind = array($bind);
			}
			$index = 1;
			foreach(array_keys($bind) as $key)
			{
				switch (gettype($bind[$key]))
				{
					case 'boolean':
						$result = $statement->bindParam($index, $bind[$key], PDO::PARAM_BOOL);
						break;
					case 'integer':
						$result = $statement->bindParam($index, $bind[$key], PDO::PARAM_INT);
						break;
					case 'array':
						$result = $statement->bindParam($index, $bind[$key][0], PDO::PARAM_LOB);
						break;
					case 'object':
						$result = $statement->bindValue($index, strval($bind[$key]), PDO::PARAM_LOB);
						break;
					case 'NULL':
						$result = $statement->bindParam($index, $bind[$key], PDO::PARAM_NULL);
						break;
					default:
						$result = $statement->bindParam($index, $bind[$key], PDO::PARAM_STR);
				}
				if ($result === false)
				{
					$this->_raiseEx($statement);
				}
				++$index;
			}
		}
		
		$result = $statement->execute();

		if ($this->_debugMode)
		{
			$stop = microtime();
			list($sm, $ss) = explode(' ', $start);
			list($em, $es) = explode(' ', $stop);
			$elapsed = number_format(($em + $es) - ($sm + $ss), 4);
			$this->_queryTime += $elapsed;
		}
		
		if ($result === false)
		{
			$this->_raiseEx($statement);
		}
		$this->_affectedRows = $statement->rowCount();

		return new SparkDBResult($statement);
	}

	// --------------------------------------------------------------------------

	final public function query($query, $bind = NULL)
	{
		if (is_string($query))
		{
			$this->_preBind($query, $bind);
		}
		return $this->_query($query, $bind);
	}

	// --------------------------------------------------------------------------

	final public function affectedRows()
	{
		return $this->_affectedRows;
	}

	// --------------------------------------------------------------------------

	final public function queryCount()
	{
		return $this->_queryCount;
	}

	// --------------------------------------------------------------------------

	final public function queryTime()
	{
		return $this->_queryTime;
	}

	// --------------------------------------------------------------------------

	final public function lastInsertID()
	{
		return $this->_connection->lastInsertId();
	}

	// --------------------------------------------------------------------------

	final public function exists($table, $where = NULL, $bind = NULL)
	{
		return $this->countRows($table, $where, $bind) > 0;
	}
	
	// --------------------------------------------------------------------------

	final public function countRows($table, $where = NULL, $bind = NULL)
	{
		$row = $this->selectRow($table, 'COUNT(*) as count', $where, $bind);
		return intval($row['count']);
	}
	
	// --------------------------------------------------------------------------
		
	final public function selectRow($table, $select = '*', $where = NULL, $bind = NULL)
	{
		return $this->query($this->buildSelect($table, $select, NULL, $where, NULL, 1), $bind)->row();
	}

	// --------------------------------------------------------------------------
		
	final public function selectRows($table, $select = '*', $where = NULL, $bind = NULL, $distinct = false)
	{
		return $this->query($this->buildSelect($table, $select, NULL, $where, NULL, NULL, NULL, $distinct), $bind)->rows();
	}

	// --------------------------------------------------------------------------
		
	final public function selectJoinRow($table, $select = '*', $joins = NULL, $where = NULL, $bind = NULL)
	{
		return $this->query($this->buildSelect($table, $select, $joins, $where, NULL, 1), $bind)->row();
	}

	// --------------------------------------------------------------------------
		
	final public function selectJoinRows($table, $select = '*', $joins = NULL, $where = NULL, $bind = NULL, $distinct = false)
	{
		return $this->query($this->buildSelect($table, $select, $joins, $where, NULL, NULL, NULL, $distinct), $bind)->rows();
	}

	// --------------------------------------------------------------------------
		
	final public function insertRow($table, $row)
	{
		$columns = array_keys($row);
		$bind = array_values($row);
		$this->query($this->buildInsert($table, array_combine($columns, array_fill(0, count($columns), '?'))), $bind);
	}

	// --------------------------------------------------------------------------
		
	final public function updateRows($table, $row, $where = NULL, $bind = NULL)
	{
		$columns = array_keys($row);
		$bind = empty($bind) ? array_values($row) : array_merge(array_values($row), (array)$bind);
		$this->query($this->buildUpdate($table, array_combine($columns, array_fill(0, count($columns), '?')), $where), $bind);
	}

	// --------------------------------------------------------------------------
		
	final public function deleteRows($table, $where = NULL, $bind = NULL)
	{
		$this->query($this->buildDelete($table, NULL, $where), $bind);
	}

	// --------------------------------------------------------------------------
		
	final public function deleteJoinRows($table, $joins = NULL, $where = NULL, $bind = NULL)
	{
		$this->query($this->buildDelete($table, $joins, $where), $bind);
	}

	// --------------------------------------------------------------------------
		
	final public function upsertRow($table, $row, $primaryKey)
	{
		if ($sql = $this->buildUpsert($table, $row, $primaryKey, $bind))
		{
			$this->query($sql, $bind);
			return;
		}

		foreach ((array)$primaryKey as $key)
		{
			$where[] = "{$key}=?";
			$bind[] = $row[$key];
		}
		$where = implode(' AND ', $where);

		$this->begin();

		try
		{
			$this->updateRows($table, $row, $where, $bind);
			if (!$this->affectedRows() && !$this->countRows($table, $where, $bind))
			{
				$this->insertRow($table, $row);
			}
		}
		catch (Exception $e)
		{
			$this->rollback();
			throw $e;
		}
		
		$this->commit();
	}

	// --------------------------------------------------------------------------
		
	final public function insertRows($table, $rows)
	{
		foreach ($rows as $row)
		{
			$this->insertRow($table, $row);
		}
	}

	// --------------------------------------------------------------------------
		
	final public function upsertRows($table, $rows, $primaryKey)
	{
		foreach ($rows as $row)
		{
			$this->upsertRow($table, $row, $primaryKey);
		}
	}

	// --------------------------------------------------------------------------

	// The following functions may be overridden by adapter classes for database-specific SQL.

	// --------------------------------------------------------------------------

	public function buildSelect($table, $select = '*', $joins = NULL, $where = NULL, $orderBy = NULL, $limit = NULL, $offset = NULL, $distinct = false)
	{
		$joinTable = $table;
		
		if (is_array($table))
		{
			$alias = $table[1];
			$table = $table[0];
		}
		else
		{
			$alias = NULL;
		}
		
		$sql = 'SELECT ' . ($distinct ? 'DISTINCT ' : '') . "{$select} FROM {$this->_tablePrefix}{$table}";
		if ($alias)
		{
			$sql .= " AS {$alias}";
		}

		if ($joins)
		{
			$sql .= $this->buildTableJoins($joinTable, $joins);
		}
		if ($where)
		{
			$sql .= " WHERE {$where}";
		}
		if ($orderBy)
		{
			$sql .= ' ' . $this->buildSelectOrderBy($orderBy);
		}
		if ($limit || $offset)
		{
			$sql .= ' ' . $this->buildSelectLimit($limit, $offset);
		}

		return $sql;
	}
	
	// --------------------------------------------------------------------------

	public function buildSelectOrderBy($orderBy = NULL)
	{
		if ($orderBy)
		{
			if (is_array($orderBy))
			{
				$orderBy = implode(', ', $this->_orderBy);
			}
			return " ORDER BY {$orderBy}";
		}
		return '';
	}

	// --------------------------------------------------------------------------

	public function buildSelectLimit($limit = NULL, $offset = NULL)
	{
		if (!$limit)
		{
			$limit = 1000000;
		}
		$sql = "LIMIT {$limit}";
		if ($offset)
		{
			$sql .= " OFFSET {$offset}";
		}
		return $sql;
	}

	// --------------------------------------------------------------------------
		
	public function buildInsert($table, $row)
	{
		$sql = "INSERT INTO {$this->_tablePrefix}{$table}"
			. ' (' . implode(', ', array_map(array($this, 'quoteIdentifier'), array_keys($row))) . ') VALUES (' . implode(', ', $row) .')';
		return $sql;
	}

	// --------------------------------------------------------------------------
		
	public function buildReplace($table, $row)
	{
		// WARNING! This method utilizes a non-standard SQL extension and may not be implemented in all database adapters.
		// Should be avoided if possible. May be removed in future release.
		
		$sql = "REPLACE INTO {$this->_tablePrefix}{$table}"
			. ' (' . implode(', ', array_map(array($this, 'quoteIdentifier'), array_keys($row))) . ') VALUES (' . implode(', ', $row) .')';
		return $sql;
	}

	// --------------------------------------------------------------------------
		
	public function buildUpdate($table, $row, $where = NULL)
	{
		$set = array();
		$sql = "UPDATE {$this->_tablePrefix}{$table}";
		foreach($row as $col => $val)
		{
			$set[] = $this->quoteIdentifier($col) . '=' . $val;
		}
		$sql .= " SET " . implode(', ', $set);
		if ($where)
		{
			$sql .= " WHERE {$where}";
		}
		return $sql;
	}

	// --------------------------------------------------------------------------
	
	public function buildDelete($table, $joins = NULL, $where = NULL)
	{
		$sql = "DELETE FROM {$this->_tablePrefix}{$table}";
		if ($joins)
		{
			$sql .= $this->buildTableJoins($table, $joins);
		}
		if ($where)
		{
			$sql .= " WHERE {$where}";
		}
		return $sql;
	}

	// --------------------------------------------------------------------------
	
	public function buildUpsert($table, $row, $primaryKey, &$bind)
	{
		return false;	// no default, since non-standard SQL
	}

	// --------------------------------------------------------------------------

	public function buildTableJoins($table, $joins = NULL)
	{
		$sql = '';
		
		if (is_array($table))
		{
			$alias = $table[1];
			$table = $table[0];
		}
		else
		{
			$alias = NULL;
		}
		
		if ($joins)
		{
			$rightTableOrAlias = $alias ? $alias : $this->_tablePrefix.$table;
			foreach ((array)$joins as $join)
			{
				$joinType = !empty($join['type']) ? strtoupper(trim($join['type'])).' ' : '';
				$leftTableOrAlias = ($leftTable = @$join['leftTable']) ? (is_array($leftTable) ? $leftTable[1] : $this->_tablePrefix.$leftTable) : $rightTableOrAlias;
				$rightTable = $join['table'];
				if (is_array($rightTable))
				{
					$alias = $rightTable[1];
					$rightTable = $rightTable[0];
					$rightTableOrAlias = $alias;
				}
				else
				{
					$alias = NULL;
					$rightTableOrAlias = $this->_tablePrefix.$rightTable;
				}
				$conditions = array();
				foreach ($join['conditions'] as $condition)
				{
					if (is_string($condition))
					{
						$conditions[] = $condition;
					}
					else
					{
						$joinOp = isset($condition['joinOp']) ? trim($condition['joinOp']) : '=';
	
						if (isset($condition['leftField']) && isset($condition['rightField']))
						{
							$conditions[] = "{$leftTableOrAlias}.{$condition['leftField']} {$joinOp} {$rightTableOrAlias}.{$condition['rightField']}";
						}
						elseif (isset($condition['leftField']))
						{
							$conditions[] = "{$leftTableOrAlias}.{$condition['leftField']} {$joinOp} {$condition['value']}";
						}
						elseif (isset($condition['rightField']))
						{
							$conditions[] = "{$rightTableOrAlias}.{$condition['rightField']} {$joinOp} {$condition['value']}";
						}
					}
				}
				
				$bool = isset($join['boolean']) ? $join['boolean'] : 'AND';
				$sql .= " {$joinType}JOIN {$this->_tablePrefix}{$rightTable}" . ($alias ? " AS {$alias}" : '') . ' ON ' . implode(" {$bool} ", $conditions);
			}
		}

		return $sql;
	}
	
	//---------------------------------------------------------------------------
	
	public function buildWhere($table, $conditions, &$where, &$bind)
	{
		if (!empty($conditions))
		{
			foreach ($conditions as $field => $val)
			{
				$column = "{$this->_tablePrefix}{$table}.{$field}";
				if (is_array($val))
				{
					$op = $val[0];
					$val = $val[1];
				}
				else
				{
					$op = '=';
				}
				if ($val === NULL)
				{
					$op = (($op === '=') || (trim($op) === 'IS')) ? 'IS' : 'IS NOT';
				}
				$where[] = "{$column} {$op} ?";
				$bind[] = $val;
			}
		}
	}

	//---------------------------------------------------------------------------
	
	public function buildConjunction($table, $conditions, &$bind)
	{
		$this->buildWhere($table, $conditions, $where, $bind);
		return empty($where) ? '' : implode(' AND ', $where);
	}

	//---------------------------------------------------------------------------
	
	public function buildDisjunction($table, $conditions, &$bind)
	{
		$this->buildWhere($table, $conditions, $where, $bind);
		return empty($where) ? '' : implode(' OR ', $where);
	}

	//---------------------------------------------------------------------------
	
	public function buildMarkers($count)
	{
		return rtrim(str_repeat('?,', $count), ',');
	}
	
	//---------------------------------------------------------------------------
	
	public function buildFieldIn($table, $field, &$values, $negate = false)
	{
		$table = is_array($table) ? $table[1] : $this->_tablePrefix . $table;
	
		self::makeList($values);

		$checkNULL = false;
		foreach ($values as $key=>$val)
		{
			if ($val === NULL)
			{
				unset($values[$key]);
				$checkNULL = true;
			}
		}
		
		if (!empty($values))
		{
			$markers = $this->buildMarkers(count($values));
		}
		$column = "{$table}.{$field}";
		$sql = '';
		
		if ($negate)
		{
			if (!empty($markers))
			{
				$sql = "{$column} NOT IN ({$markers})";
			}
			if ($checkNULL)
			{
				if (!empty($markers))
				{
					$sql .= ' AND ';
				}
				$sql .= "({$column} IS NOT NULL)";
			}
		}
		else
		{
			if (!empty($markers))
			{
				$sql = "{$column} IN ({$markers})";
			}
			if ($checkNULL)
			{
				if (!empty($markers))
				{
					$sql .= ' OR ';
				}
				$sql .= "({$column} IS NULL)";
			}
		}

		return $sql;
	}
	
	//---------------------------------------------------------------------------
	
	public function buildFieldNotIn($table, $field, &$values)
	{
		return $this->buildFieldIn($table, $field, $values, true);
	}

	// --------------------------------------------------------------------------

	// Hooks for driver plugins.

	// --------------------------------------------------------------------------
	
	protected function preBindArg(&$arg)
	{
		return false;
	}

	// --------------------------------------------------------------------------

	// The following functions **must** be overridden by adapter classes for database-specific functions.

	// --------------------------------------------------------------------------
	
	abstract public function translateErrorCode($errorCode);

	// --------------------------------------------------------------------------
	
	public function getFunction($name)
	{
		switch ($name)
		{
			case 'literal':
				return new SparkDBFunction_literal($this);
			default:
				throw new SparkDBException('Database adapter does not implement function "' . $name . '"', SparkDBException::kFunctionNotImplemented);
		}
	}

	// --------------------------------------------------------------------------
}
Return current item: Escher CMS