PHP Decode

<?php
// Copyright 2011 Toby Zerner, Simon Zerner
// This file is part of esoTalk. Please see the included license file for usage information.

if (!defined("IN_ESOTALK")) exit;

/**
 * Enables dynamic construction of SQL queries.
 *
 * The purpose of this class is not so much to be a database abstraction layer and prevent
 * the need to write straight SQL. It is more to allow query components be added/changed dynamically
 * (by plugins, for example), and to aid in writing safe queries.
 *
 * This implementation tries to be as SQL-neutral as possible, but is ultimately written to work
 * with MySQL. It can be extended to provide a query constructor for a different database engine.
 *
 * @package esoTalk
 */
class ETSQLQuery {


/**
 * The type of query that is being constructed (select, update, insert, replace, delete, or union).
 * @var string
 */
protected $mode = "select";


/**
 * An array of expressions to SELECT.
 * @var array
 */
public $select = array();


/**
 * An array of tables to select FROM, including JOIN clauses, or to INSERT into, UPDATE, or DELETE from.
 * @var array
 */
public $tables = array();


/**
 * An array of WHERE conditions.
 * @var array
 */
public $where = array();


/**
 * An array of GROUP BY expressions.
 * @var array
 */
public $groupBy = array();


/**
 * The number of results to limit the query to.
 * @var int
 */
public $limit = null;


/**
 * The result number to start from.
 * @var int
 */
public $offset = null;


/**
 * An array of ORDER BY expressions.
 * @var array
 */
public $orderBy = array();


/**
 * An array of HAVING expressions.
 * @var array
 */
public $having = array();


/**
 * The name of an index to force use of.
 * @var string
 */
public $index = null;


/**
 * An array of fields to set in an INSERT query.
 * @var array
 */
public $insertFields = array();


/**
 * An array of fields => values to set for an UPDATE query, or an array of arrays of values to INSERT.
 * @var array
 */
public $set = array();


/**
 * An array of fields => values to set ON DUPLICATE KEY.
 * @var array
 */
public $setDuplicateKey = array();


/**
 * An array of SQL queries to UNION.
 * @var array
 */
public $union = array();


/**
 * An array of bound parameters to replace when the query is constructed.
 * @var array
 */
public $parameters = array();


/**
 * Bind a value to a parameter that will be substituted safely when the query is constructed.
 *
 * @param string $parameter The name of the parameter. This must begin with a colon (:).
 * @param mixed $value The value to substitute.
 * @param int $dataType Explicit data type for the parameter using PDO::PARAM_* constants. If null,
 * 		the type of $value will be used.
 * @return ETSQLQuery
 */
public function bind($parameter, $value, $dataType = null)
{
	$this->parameters[$parameter] = array($value, $dataType);
	return $this;
}


/**
 * Add an expression to the SELECT clause.
 *
 * @param string|array $expression The expression to select. If an array is passed, all values will be added.
 * @param string $as An optional identifier to select the expression AS.
 * @return ETSQLQuery
 */
public function select($expression, $as = false)
{
	$this->mode = "select";

	// If an AS name was specified, set a keyed value in the array.
	if ($as !== false) $this->select[$as] = $expression;

	// Otherwise, cast the expression to an array and add all its values to the SELECTs array.
	else {
		$expressions = (array)$expression;
		foreach ($expressions as $expression) {
			if (!empty($expression)) $this->select[] = $expression;
		}
	}

	return $this;
}


/**
 * Add a table to the FROM clause, optionally as a JOIN.
 *
 * @param string $table The name of the table. This can include an alias at the end. The table prefix will
 * 		automatically be added.
 * @param string $on An optional condition to JOIN the table ON.
 * @param string $type The type of JOIN (eg. left, inner, etc.)
 * @return ETSQLQuery
 */
public function from($table, $on = false, $type = false)
{
	// If the first character is an opening bracket, then assume the table is a SELECT query. Otherwise,
	// add the table prefix.
	if ($table[0] != "(") {
		$parts = explode(" ", ET::$database->tablePrefix.$table);
		$parts[0] = "`".$parts[0]."`";
		$table = implode(" ", $parts);
	}

	// If a JOIN type or condition was specified, add the table with JOIN syntax.
	if (!empty($type) or !empty($on))
		$this->tables[] = strtoupper($type ? $type : "inner")." JOIN $table".(!empty($on) ? " ON ($on)" : "");

	// Otherwise, just add the table name normally.
	else array_unshift($this->tables, $table);

	return $this;
}


/**
 * Add a WHERE predicate to the query.
 *
 * @param string $predicate The predicate to add. This can be either:
 * 		1. A string and the only argument, and it is added as is.
 * 		2. A string with the $value argument specified, and it is used as the field name to be tested for equality.
 * 		3. An array of predicates. Non-numeric keys will be used as field names to be tested for equality with
 * 		   their values, while numeric keys will be added as is.
 * @param mixed $value The value to test for equality with in case 2 above.
 * @return ETSQLQuery
 */
public function where($predicate, $value = false)
{
	if (empty($predicate)) return $this;

	// If a value was specified, use the predicate as the field name.
	if ($value !== false) $predicate = array($predicate => $value);

	// Go through the predicates and add them to the query one by one.
	$predicates = (array)$predicate;
	foreach ($predicates as $field => $predicate) {

		// If the key is non-numeric, use it as the field name add an equality predicate.
		// Bind the value with a parameter called :where#.
		if (!is_numeric($field)) {
			$i = count($this->where);
			$this->where[] = "$field=:where$i";
			$this->bind(":where$i", $predicate);
		}

		// If the key is numeric, add the predicate as is.
		else $this->where[] = $predicate;
	}

	return $this;
}


/**
 * Add an expression to the GROUP BY clause.
 *
 * @param string|array $expression The expression, or an array of expressions, to add.
 * @return ETSQLQuery
 */
public function groupBy($expression)
{
	$expressions = (array)$expression;
	foreach ($expressions as $expression) {
		if (!empty($expression)) $this->groupBy[] = $expression;
	}
	return $this;
}


/**
 * Add an expression to the ORDER BY clause.
 *
 * @param string|array $expression The expression, or an array of expressions, to add.
 * @return ETSQLQuery
 */
public function orderBy($expression)
{
	$expressions = (array)$expression;
	foreach ($expressions as $expression) {
		if (!empty($expression)) $this->orderBy[] = $expression;
	}
	return $this;
}


/**
 * Add an expression to the HAVING clause.
 *
 * @param string|array $expression The expression, or an array of expressions, to add.
 * @return ETSQLQuery
 */
public function having($expression)
{
	$expressions = (array)$expression;
	foreach ($expressions as $expression) {
		if (!empty($expression)) $this->having[] = $expression;
	}
	return $this;
}


/**
 * Force the use of an index in the query.
 *
 * @param string $index The name of the index to force use of.
 * @return ETSQLQuery
 */
public function useIndex($index)
{
	$this->index = $index;
	return $this;
}


/**
 * Set the maximum number of results for the query to return.
 *
 * @param string $limit The maximum number of results.
 * @return ETSQLQuery
 */
public function limit($limit)
{
	$this->limit = $limit;
	return $this;
}


/**
 * Set the row number to start getting results from.
 *
 * @param string $offset The row number to start from.
 * @return ETSQLQuery
 */
public function offset($offset)
{
	$this->offset = $offset;
	return $this;
}


/**
 * Begin an UPDATE query and add a table to update.
 *
 * @param string $table The name of the table to update.
 * @return ETSQLQuery
 */
public function update($table)
{
	$this->mode = "update";
	$this->tables[] = ET::$database->tablePrefix.$table;
	return $this;
}


/**
 * Set a field to a value in an UPDATE or INSERT query.
 *
 * @param string|array $field The name of the field to set, or an array of fields => values to set.
 * @param mixed $value The value to set the field to.
 * @param bool $sanitize Whether or not to escape and quote the value.
 * @return ETSQLQuery
 */
public function set($field, $value = false, $sanitize = true)
{
	if (!is_array($field)) $field = array($field => $value);
	foreach ($field as $field => $value) {

		$value = $sanitize ? ET::$database->escapeValue($value) : $value;

		// For an UPDATE query, simply add the field and value to the SET array.
		if ($this->mode == "update")
			$this->set[$field] = $value;

		// But for an INSERT query, we need to add the field to $this->insertFields and the value to the
		// first row in the SET array.
		else {
			$this->insertFields[] = $field;
			$this->set[0][] = $value;
		}

	}
	return $this;
}


/**
 * Begin an INSERT query and add a table to insert into.
 *
 * @param string $table The name of the table to insert into.
 * @return ETSQLQuery
 */
public function insert($table)
{
	$this->mode = "insert";
	$this->tables[] = ET::$database->tablePrefix.$table;
	return $this;
}


/**
 * Set multiple rows of data in an INSERT query.
 *
 * @param array $fields An array of field names to set.
 * @param array $valueSets An array of arrays of values to insert.
 * @return ETSQLQuery
 */
public function setMultiple($fields, $valueSets)
{
	$this->insertFields = $fields;
	foreach ($valueSets as &$row) {
		foreach ($row as &$value) {
			$value = ET::$database->escapeValue($value);
		}
	}
	$this->set = $valueSets;
	return $this;
}


/**
 * Set a field to a value when there is a duplicate key in an INSERT query.
 *
 * @param string|array $field The name of the field to set, or an array of fields => values to set.
 * @param mixed $value The value to set the field to.
 * @param bool $sanitize Whether or not to escape and quote the value.
 * @return ETSQLQuery
 */
public function setOnDuplicateKey($field, $value = false, $sanitize = true)
{
	if (!is_array($field)) $field = array($field => $value);
	foreach ($field as $field => $value) {
		$this->setDuplicateKey[$field] = $sanitize ? ET::$database->escapeValue($value) : $value;
	}
	return $this;
}


/**
 * Begin a REPLACE query and add a table to replace into.
 *
 * @param string $table The name of the table to replace into.
 * @return ETSQLQuery
 */
public function replace($table)
{
	$this->mode = "replace";
	$this->tables[] = ET::$database->tablePrefix.$table;
	return $this;
}


/**
 * Begin a DELETE query and add a table to delete.
 *
 * @param string $table The name of the table to delete.
 * @return ETSQLQuery
 */
public function delete($table = null)
{
	$this->mode = "delete";
	if ($table) $this->select[] = $table;
	return $this;
}


/**
 * Add a SELECT query to be UNIONed.
 *
 * @param ETSQLQuery $query The ETSQLQuery object to UNION.
 * @return ETSQLQuery
 */
public function union($query)
{
	$this->mode = "union";
	$this->union[] = $query;
	return $this;
}


/**
 * Indent each line in each value of an array. This is used on each item in the SELECT, WHERE, and FROM clause
 * so that sub-SELECTs appear indented.
 *
 * @param mixed $value The value to apply indentation to.
 * @return mixed The value with indentation applied.
 */
protected function indent($value)
{
	if (is_array($value)) return array_map(array($this, "indent"), $value);
	else return str_replace("\n", "\n\t\t", $value);
}


/**
 * Construct a WHERE clause from the query structure information gathered in this class.
 *
 * @return string
 */
protected function getWhere()
{
	return count($this->where) ? "\nWHERE (".implode(")\n\tAND (", $this->indent($this->where)).")" : "";
}


/**
 * Construct a FROM clause from the query structure information gathered in this class.
 *
 * @return string
 */
protected function getOrderBy()
{
	return count($this->orderBy) ? "\nORDER BY ".implode(", ", $this->orderBy) : "";
}


/**
 * Construct a SELECT SQL query from the query structure information gathered in this class.
 *
 * @return string
 */
protected function getSelect()
{
	// Construct the SELECT clause.
	$select = array();
	foreach ($this->select as $k => $v) {
		if (!is_numeric($k)) $select[] = "$v AS $k";
		else $select[] = $v;
	}
	$select = "SELECT ".implode(", \n\t", $this->indent($select));

	// Construct some other clauses.
	$from = count($this->tables) ? "\nFROM ".implode("\n\t", $this->indent($this->tables)) : "";
	$index = $this->index ? "\nUSE INDEX ($this->index)" : "";
	$having = count($this->having) ? "\nHAVING (".implode(") AND (", $this->indent($this->having)).")" : "";
	$groupBy = count($this->groupBy) ? "\nGROUP BY ".implode(", ", $this->groupBy) : "";
	$limit = $this->limit ? "\nLIMIT $this->limit" : "";
	$offset = $this->offset ? "\nOFFSET $this->offset" : "";

	// Put the whole query together and return it.
	return $select.$from.$index.$this->getWhere().$groupBy.$this->getOrderBy().$limit.$offset;
}


/**
 * Construct an UPDATE SQL query from the query structure information gathered in this class.
 *
 * @return string
 */
protected function getUpdate()
{
	// Put together the tables to update.
	$tables = implode(", ", $this->tables);

	// Construct the SET clause.
	$set = array();
	foreach ($this->set as $k => $v) $set[] = "$k=$v";
	$set = implode(", ", $set);

	return "UPDATE $tables SET $set ".$this->getWhere();
}


/**
 * Construct an INSERT SQL query from the query structure information gathered in this class.
 *
 * @return string
 */
protected function getInsert()
{
	// Put together the tables to insert into.
	$tables = implode(", ", $this->tables);

	// Make a list of fields to insert data into.
	$fields = implode(", ", $this->insertFields);

	// Make a list of rows and their values to insert.
	$rows = array();
	foreach ($this->set as $row) $rows[] = "(".implode(", ", $row).")";
	$values = implode(", ", $rows);

	// Construct the ON DUPLICATE KEY UPDATE clause.
	$onDuplicateKey = array();
	foreach ($this->setDuplicateKey as $k => $v) $onDuplicateKey[] = "$k=$v";
	$onDuplicateKey = implode(", ", $onDuplicateKey);

	return "INSERT INTO $tables ($fields) VALUES $values".($onDuplicateKey ? " ON DUPLICATE KEY UPDATE $onDuplicateKey" : "");
}


/**
 * Construct a REPLACE SQL query.
 *
 * @return string
 */
protected function getReplace()
{
	// Simply construct an INSERT query, replacing the word INSERT with REPLACE.
	$query = $this->getInsert();
	$query = "REPLACE".substr($query, 6);
	return $query;
}


/**
 * Construct a DELETE SQL query from the query structure information gathered in this class.
 *
 * @return string
 */
protected function getDelete()
{
	$tables = implode(", ", $this->select);
	$from = implode("\n\t", $this->indent($this->tables));

	return "DELETE $tables FROM $from ".$this->getWhere();
}


/**
 * Construct a UNION SQL query from the query structure information gathered in this class.
 *
 * @return string
 */
protected function getUnion()
{
	// Convert the queries that we want to UNION to strings.
	$selects = $this->union;
	foreach ($selects as &$sql) $sql = "\t(".$sql->get().")";

	// Implode them with the UNION keyword.
	$selects = implode("\nUNION\n", $this->indent($selects));

	// Add order by, limit, and offset clauses.
	$limit = $this->limit ? "\nLIMIT $this->limit" : "";
	$offset = $this->offset ? "\nOFFSET $this->offset" : "";

	// Put the query together.
	return $selects.$this->getOrderBy().$limit.$offset;
}


/**
 * Construct the SQL from the query structure information we've gathered in this class, substitute in parameter
 * values, and return the final product it as a string.
 *
 * @return string
 */
public function get()
{
	// Run the appropriate get function depending on this query's mode.
	switch ($this->mode) {

		case "select":
			$query = $this->getSelect();
			break;

		case "update":
			$query = $this->getUpdate();
			break;

		case "insert":
			$query = $this->getInsert();
			break;

		case "replace":
			$query = $this->getReplace();
			break;

		case "delete":
			$query = $this->getDelete();
			break;

		case "union":
			$query = $this->getUnion();
			break;

		default:
			$query = "";
	}

	// Substitute in bound parameter values.
	$self = $this;
	$query = preg_replace_callback('/(:[A-Za-z0-9_]+)/', function ($matches) use ($self) {
		return array_key_exists($matches[1], $self->parameters)
			? ET::$database->escapeValue($self->parameters[$matches[1]][0], $self->parameters[$matches[1]][1])
			: $matches[1];
	}, $query);

	return $query;
}


/**
 * Construct the SQL query and execute it, returning the result.
 *
 * @return ETSQLResult
 */
public function exec()
{
	$query = $this->get();
	return ET::$database->query($query);
}

public function __toString()
{
	return $this->get();
}

}