SqlBuilder.class.php

<?php
Library::import('recess.database.sql.ISqlConditions');
Library::import('recess.database.sql.ISqlSelectOptions');

/**
 * SqlBuilder is used to incrementally compose named-parameter PDO Sql strings 
 * using a simple, chainable method call API. This is a naive wrapper that does
 * not gaurantee valid SQL output (i.e. column names using reserved SQL words).
 * 
 * 4 classes of SQL strings can be built: INSERT, UPDATE, DELETE, SELECT.
 * This class is intentionally arranged from the low complexity requirements
 * of INSERT to the more complex SELECT.
 * 
 * INSERT:        table, column/value assignments
 * UPDATE/DELETE: where conditions
 * SELECT:        order, joins, offset, limit, distinct
 * 
 * Example usage: 
 * 
 * $sqlBuilder->into('table_name')->assign('column', 'value')->insert() .. 
 * 		returns "INSERT INTO table_name (column) VALUES (:column)"
 * $sqlBuilder->getPdoArguments() returns array( ':column' => 'value' )
 * 
 * @author Kris Jordan <krisjordan@gmail.com>
 * @contributor Luiz Alberto Zaiats 
 * 
 * @copyright 2008, 2009 Kris Jordan
 * @package Recess PHP Framework
 * @license MIT
 * @link http://www.recessframework.org/
 */
class SqlBuilder implements ISqlConditions, ISqlSelectOptions {
		
	/* INSERT */
	protected $table;
	protected $assignments = array();
	
	/**
	 * Build an INSERT SQL string from SqlBuilder's state.
	 * 
	 * @return string INSERT string.
	 */
	public function insert() {
		$this->insertSanityCheck();

		$sql = 'INSERT INTO ' . self::escapeWithTicks($this->table);
		
		$columns = '';
		$values = '';
		$first = true;
		$table_prefix = $this->tableAsPrefix() . '.';
		foreach($this->assignments as $assignment) {
			if($first) { $first = false; }
			else { $columns .= ', '; $values .= ', '; }
			$columns .= self::escapeWithTicks(str_replace($table_prefix, '', $assignment->column));
			$values .= $assignment->getQueryParameter();
		}
		$columns = ' (' . $columns . ')';
		$values = '(' . $values . ')';
		
		$sql .= $columns . ' VALUES ' . $values;
		
		return $sql;
	}
	
	/**
	 * Safety check used with insert to ensure only a table and assignments were applied.
	 */
	protected function insertSanityCheck() {
		if(	!empty($this->conditions) )
			throw new RecessException('Insert does not use conditionals.', get_defined_vars());
		if(	!empty($this->joins) )
			throw new RecessException('Insert does not use joins.', get_defined_vars());
		if(	!empty($this->orderBy) ) 
			throw new RecessException('Insert does not use order by.', get_defined_vars());
		if(	isset($this->limit) )
			throw new RecessException('Insert does not use limit.', get_defined_vars());
		if(	isset($this->offset) )
			throw new RecessException('Insert does not use offset.', get_defined_vars());
		if(	isset($this->distinct) )
			throw new RecessException('Insert does not use distinct.', get_defined_vars());
	}
	
	/**
	 * Set the table of focus on a sql statement.
	 *
	 * @param string $table
	 * @return SqlBuilder 
	 */
	public function table($table) { $this->table = $table; return $this; }
	
	/**
	 * Alias for table (insert into)
	 *
	 * @param string $table
	 * @return SqlBuilder
	 */
	public function into($table) { return $this->table($table); }

	/**
	 * Assign a value to a column. Used with inserts and updates.
	 *
	 * @param string $column
	 * @param mixed $value
	 * @return SqlBuilder
	 */
	public function assign($column, $value) { 
		if(strpos($column, '.') === false) {
			if(isset($this->table)) {
				$this->assignments[] = new Criterion($this->tableAsPrefix() . '.' . $column, $value, Criterion::ASSIGNMENT); 
			} else {
				throw new RecessException('Cannot assign without specifying table.', get_defined_vars());
			}
		} else {
			$this->assignments[] = new Criterion($column, $value, Criterion::ASSIGNMENT); 
		}
		return $this;
	}
	
	/* UPDATE & DELETE */
	protected $conditions = array();
	protected $conditionsUsed = array();
	protected $useAssignmentsAsConditions = false;
	
	/**
	 * Build a DELETE SQL string from SqlBuilder's state.
	 *
	 * @return string DELETE string
	 */
	public function delete() {
		$this->deleteSanityCheck();
		return 'DELETE FROM ' . self::escapeWithTicks($this->table) . $this->whereHelper();
	}
	
	/**
	 * Safety check used with delete.
	 */
	protected function deleteSanityCheck() {
		if(	!empty($this->joins) )
			throw new RecessException('Delete does not use joins.', get_defined_vars());
		if(	!empty($this->orderBy) ) 
			throw new RecessException('Delete does not use order by.', get_defined_vars());
		if(	isset($this->limit) )
			throw new RecessException('Delete does not use limit.', get_defined_vars());
		if(	isset($this->offset) )
			throw new RecessException('Delete does not use offset.', get_defined_vars());
		if(	isset($this->distinct) )
			throw new RecessException('Delete does not use distinct.', get_defined_vars());
		if( !empty($this->assignments) && !$this->useAssignmentsAsConditions)
			throw new RecessException('Delete does not use assignments. To use assignments as conditions add ->useAssignmentsAsConditions() to your method call chain.', get_defined_vars());
	}
	
	/**
	 * Build an UPDATE SQL string from SqlBuilder's state.
	 *
	 * @return string
	 */
	public function update() {
		$this->updateSanityCheck();
		$sql = 'UPDATE ' . self::escapeWithTicks($this->table) . ' SET ';
		
		$first = true;
		$table_prefix = $this->tableAsPrefix() . '.';
		foreach($this->assignments as $assignment) {
			if($first) { $first = false; }
			else { $sql .= ', '; }
			$sql .= self::escapeWithTicks(str_replace($table_prefix, '', $assignment->column)) . ' = ' . $assignment->getQueryParameter();;
		}
		
		$sql .= $this->whereHelper();
		
		return $sql;
	}
	
	/**
	 * Safety check used with update.
	 */
	protected function updateSanityCheck() {
		if(	!empty($this->joins) )
			throw new RecessException('Update does not use joins.', get_defined_vars());
		if(	!empty($this->orderBy) ) 
			throw new RecessException('Update (in Recess) does not use order by.', get_defined_vars());
		if(	isset($this->limit) )
			throw new RecessException('Update (in Recess) does not use limit.', get_defined_vars());
		if(	isset($this->offset) )
			throw new RecessException('Update (in Recess) does not use offset.', get_defined_vars());
		if(	isset($this->distinct) )
			throw new RecessException('Update does not use distinct.', get_defined_vars());
	}
	
	/**
	 * Return the collection of PDO named parameters and values to be
	 * applied to a parameterized PDO statement.
	 *
	 * @return array
	 */
	public function getPdoArguments() {
		if($this->useAssignmentsAsConditions)
			return array_merge($this->conditions, $this->cleansedAssignmentsAsConditions());
		else
			return array_merge($this->conditions, $this->assignments);
	}
	
	/**
	 * Method for when using assignments as conditions. This purges
	 * assignments which have null values.
	 *  
	 * @return array
	 */
	protected function cleansedAssignmentsAsConditions() {
		$assignments = array();
		
		$count = count($this->assignments);
		for($i = 0; $i < $count; $i++) {
			if(isset($this->assignments[$i]->value))
				$assignments[] = $this->assignments[$i];
		}
		
		return $assignments;
	}
	
	/**
	 * Alias to specify which table is being used.
	 *
	 * @param string $table
	 * @return SqlBuilder
	 */
	public function from($table) { return $this->table($table); }
	
	/**
	 * Handy shortcut which allows assignments to be used as conditions
	 * in a select statement.
	 *
	 * @param boolean $bool
	 * @return SqlBuilder
	 */
	public function useAssignmentsAsConditions($bool) { $this->useAssignmentsAsConditions = $bool; return $this; }
	
	/* ISqlConditions */
	
	/**
	 * Equality expression for WHERE clause of update, delete, or select statements.
	 *
	 * @param string $column
	 * @param mixed $value
	 * @return SqlBuilder
	 */
	public function equal($column, $value)       { return $this->addCondition($column, $value, Criterion::EQUAL_TO); }
	
	/**
	 * Inequality than expression for WHERE clause of update, delete, or select statements.
	 *
	 * @param string $column
	 * @param mixed $value
	 * @return SqlBuilder
	 */
	public function notEqual($column, $value)    { return $this->addCondition($column, $value, Criterion::NOT_EQUAL_TO); }
	
	/**
	 * Shortcut alias for SqlBuilder->lessThan($column,$big)->greaterThan($column,$small) 
	 *
	 * @param string $column
	 * @param numeric $small Greater than this number. 
	 * @param numeric $big Less than this number.
	 * @return SqlBuilder
	 */
	public function between ($column, $small, $big) { $this->greaterThan($column, $small); return $this->lessThan($column, $big); }
	
	/**
	 * Greater than expression for WHERE clause of update, delete, or select statements.
	 *
	 * @param string $column
	 * @param numeric $value
	 * @return SqlBuilder
	 */
	public function greaterThan($column, $value)          { return $this->addCondition($column, $value, Criterion::GREATER_THAN); }
	
	/**
	 * Greater than or equal to expression for WHERE clause of update, delete, or select statements.
	 *
	 * @param string $column
	 * @param numeric $value
	 * @return SqlBuilder
	 */
	public function greaterThanOrEqualTo($column, $value)         { return $this->addCondition($column, $value, Criterion::GREATER_THAN_EQUAL_TO); }
	
	/**
	 * Less than expression for WHERE clause of update, delete, or select statements.
	 *
	 * @param string $column
	 * @param numeric $value
	 * @return SqlBuilder
	 */
	public function lessThan($column, $value)          { return $this->addCondition($column, $value, Criterion::LESS_THAN); }

	/**
	 * Less than or equal to expression for WHERE clause of update, delete, or select statements.
	 *
	 * @param string $column
	 * @param numeric $value
	 * @return SqlBuilder
	 */
	public function lessThanOrEqualTo($column, $value)         { return $this->addCondition($column, $value, Criterion::LESS_THAN_EQUAL_TO); }

	/**
	 * LIKE expression for WHERE clause of update, delete, or select statements, does not include wildcards.
	 *
	 * @param string $column
	 * @param string $value
	 * @return SqlBuilder
	 */
	public function like($column, $value)        { return $this->addCondition($column, $value, Criterion::LIKE); }
	
	/**
	 * NOT LIKE expression for WHERE clause of update, delete, or select statements, does not include wildcards.
	 *
	 * @param string $column
	 * @param string $value
	 * @return SqlBuilder
	 */
	public function notLike($column, $value)        { return $this->addCondition($column, $value, Criterion::NOT_LIKE); }

	/**
	 * IS NULL expression for WHERE clause of update, delete, or select statements
	 *
	 * @param string $column
	 * @param string $value
	 * @return SqlBuilder
	 */
	public function isNull($column)        { return $this->addCondition($column, null, Criterion::IS_NULL); }
	
	/**
	 * IS NOT NULL expression for WHERE clause of update, delete, or select statements
	 *
	 * @param string $column
	 * @param string $value
	 * @return SqlBuilder
	 */
	public function isNotNull($column)        { return $this->addCondition($column, null, Criterion::IS_NOT_NULL); }
	
	/**
	 * Add a condition to the SqlBuilder statement. Additional logic here to prepend
	 * a table name and also keep track of which columns have already been assigned conditions
	 * to ensure we do not use two identical named parameters in PDO.
	 *
	 * @param string $column
	 * @param mixed $value
	 * @param string $operator
	 * @return SqlBuilder
	 */
	protected function addCondition($column, $value, $operator) {
		if(strpos($column, '.') === false && strpos($column, '(') === false && !in_array($column, array_keys($this->selectAs))) {
			if(isset($this->table)) {
				$column = $this->tableAsPrefix() . '.' . $column;
			} else {
				throw new RecessException('Cannot use "' . $operator . '" operator without specifying table for column "' . $column . '".', get_defined_vars());
			}
		}
				
		if(isset($this->conditionsUsed[$column])) {
			$this->conditionsUsed[$column]++;
			$pdoLabel = $column . '_' . $this->conditionsUsed[$column];
		} else {
			$this->conditionsUsed[$column] = 1;
			$pdoLabel = null;
		}
		
		$this->conditions[] = new Criterion($column, $value, $operator, $pdoLabel);
		
		return $this;
	}
	
	/* SELECT */
	protected $select = '*';
	protected $selectAs = array();
	protected $joins = array();
	protected $limit;
	protected $offset;
	protected $distinct;
	protected $orderBy = array();
	protected $usingAliases = false;
	
	/**
	 * Build a SELECT SQL string from SqlBuilder's state.
	 *
	 * @return string
	 */
	public function select() {
		$this->selectSanityCheck();

		$sql = 'SELECT ' . $this->distinct . self::escapeWithTicks($this->select);

		foreach($this->selectAs as $selectAs) {
			$sql .= ', ' . $selectAs;
		}
		
		$sql .= ' FROM ' . self::escapeWithTicks($this->table);
		
		$sql .= $this->joinHelper();
		
		$sql .= $this->whereHelper();
		
		$sql .= $this->orderByHelper();
		
		$sql .= $this->rangeHelper();
		
		return $sql;
	}
	
	/**
	 * Safety check used when creating a SELECT statement.
	 */
	protected function selectSanityCheck() {
		if( (!empty($this->where) || !empty($this->orderBy)) && !isset($this->table))
			throw new RecessException('Must have from if using where.', get_defined_vars());
		
		if( isset($this->offset) && !isset($this->limit))
			throw new RecessException('Must define limit if using offset.', get_defined_vars());
		
		if($this->select == '*' && !isset($this->table))
			throw new RecessException('No table has been selected.', get_defined_vars());
	}
	
	/* ISqlSelectOptions */
	
	/**
	 * LIMIT results to some number of records.
	 *
	 * @param integer $size
	 * @return SqlBuilder
	 */
	public function limit($size)           { $this->limit = $size; return $this; }
	
	/**
	 * When used in conjunction with limit($size), offset specifies which row the results will begin at.
	 *
	 * @param integer $offset
	 * @return SqlBuilder
	 */
	public function offset($offset)        { $this->offset = $offset; return $this; }
	
	/**
	 * Shortcut alias to ->limit($finish - $start)->offset($start);
	 *
	 * @param integer $start
	 * @param integer $finish
	 * @return SqlBuilder
	 */
	public function range($start, $finish) { $this->offset = $start; $this->limit = $finish - $start; return $this; }
	
	/**
	 * Add an ORDER BY expression to sql string. Example: ->orderBy('name ASC')
	 *
	 * @param string $clause
	 * @return SqlBuilder
	 */
	public function orderBy($clause) {
		if(($spacePos = strpos($clause,' ')) !== false) {
			$name = substr($clause,0,$spacePos);
		} else {
			$name = $clause;
		}
		
		if(isset($this->table) && strpos($clause,'.') === false && strpos($name,'(') === false && !array_key_exists($name, $this->selectAs)) {
			$this->orderBy[] = $this->tableAsPrefix() . '.' . $clause; 
		} else {
			$this->orderBy[] = $clause;
		}
		return $this; 
	}
	
	/**
	 * Helper method which returns the current table even when it 
	 * is aliased due to joins between the same table.
	 *
	 * @return string The current table which can be used as a prefix.
	 */
	protected function tableAsPrefix() {
		if($this->usingAliases) {
			$spacePos = strrpos($this->table, ' ');
			if($spacePos !== false) {
				return substr($this->table, $spacePos + 1);
			}
		}
		return $this->table;
	}
	
	/**
	 * Left outer join expression for SELECT SQL statement.
	 *
	 * @param string $table
	 * @param string $tablePrimaryKey
	 * @param string $fromTableForeignKey
	 * @return SqlBuilder
	 */
	public function leftOuterJoin($table, $tablePrimaryKey, $fromTableForeignKey) {
		return $this->join(Join::LEFT, Join::OUTER, $table, $tablePrimaryKey, $fromTableForeignKey);
	}
	
	/**
	 * Inner join expression for SELECT SQL statement.
	 *
	 * @param string $table
	 * @param string $tablePrimaryKey
	 * @param string $fromTableForeignKey
	 * @return SqlBuilder
	 */
	public function innerJoin($table, $tablePrimaryKey, $fromTableForeignKey) {
		return $this->join('', Join::INNER, $table, $tablePrimaryKey, $fromTableForeignKey);
	}
	
	/**
	 * Generic join expression to be added to a SELECT SQL statement.
	 *
	 * @param string $leftOrRight
	 * @param string $innerOrOuter
	 * @param string $table
	 * @param string $tablePrimaryKey
	 * @param string $fromTableForeignKey
	 * @return SqlBuilder
	 */
	protected function join($leftOrRight, $innerOrOuter, $table, $tablePrimaryKey, $fromTableForeignKey) {
		if($this->table == $table) {
			$oldTable = $this->table;
			$parts = explode('__', $this->table);
			$partsCount = count($parts);
			if($partsCount > 0 && is_int($parts[$partsCount-1])) {
				$number = $parts[$partsCount - 1] + 1;
			} else {
				$number = 2;
			}
			$tableAlias = $this->table . '__' . $number;
			$this->table = self::escapeWithTicks($this->table) . ' AS ' . self::escapeWithTicks($tableAlias);
			$this->usingAliases = true;
			
			$tablePrimaryKey = str_replace($oldTable,$tableAlias,$tablePrimaryKey);			
		}
		
		$this->select = $this->tableAsPrefix() . '.*';
		$this->joins[] = new Join($leftOrRight, $innerOrOuter, $table, $tablePrimaryKey, $fromTableForeignKey);	
		return $this;
	}
	
	/**
	 * Add additional field to select statement which is aliased using the AS parameter.
	 * ->selectAs("ABS(location - 5)", 'distance') translates to => SELECT ABS(location-5) AS distance
	 *
	 * @param string $select
	 * @param string $as
	 * @return SqlBuilder
	 */
	public function selectAs($select, $as) {
		$this->selectAs[$as] = $select . ' as ' . $as;
		return $this;
	}
	
	/**
	 * Add a DISTINCT clause to SELECT SQL.
	 *
	 * @return SqlBuilder
	 */
	public function distinct() { $this->distinct = ' DISTINCT '; return $this; }

	/* HELPER METHODS */
	protected function whereHelper() {
		$sql = '';
		
		$first = true;
		if(!empty($this->conditions)) {
			foreach($this->conditions as $clause) {
				if(!$first) { $sql .= ' AND '; } else { $first = false; } // TODO: Figure out how we'll do ORing
				$sql .= self::escapeWithTicks($clause->column) . $clause->operator . $clause->getQueryParameter();
			}
		}
		
		if($this->useAssignmentsAsConditions && !empty($this->assignments)) {
			$assignments = $this->cleansedAssignmentsAsConditions();
			foreach($assignments as $clause) {
				if(!$first) { $sql .= ' AND '; } else { $first = false; } // TODO: Figure out how we'll do ORing
				$sql .= self::escapeWithTicks($clause->column) . ' = ' . $clause->getQueryParameter();
			}
		}
		
		if($sql != '') {
			$sql = ' WHERE ' . $sql;
		}
		
		return $sql;
	}
	
	protected function joinHelper() {
		$sql = '';
		if(!empty($this->joins)) {
			$joins = array_reverse($this->joins, true);
			foreach($joins as $join) {
				$joinStatement = ' ';
				
				if(isset($join->natural) && $join->natural != '') {
					$joinStatement .= $join->natural . ' ';
				}
				if(isset($join->leftRightOrFull) && $join->leftRightOrFull != '') {
					$joinStatement .= $join->leftRightOrFull . ' ';
				}
				if(isset($join->innerOuterOrCross) && $join->innerOuterOrCross != '') {
					$joinStatement .= $join->innerOuterOrCross . ' ';
				}
				
				$onStatement = ' ON ' . self::escapeWithTicks($join->tablePrimaryKey) . ' = ' . self::escapeWithTicks($join->fromTableForeignKey);
				$joinStatement .= 'JOIN ' . self::escapeWithTicks($join->table) . $onStatement;
				
				$sql .= $joinStatement;
			}
		}
		return $sql;
	}
	
	protected static function escapeWithTicks($string) {
		if($string == '*' || strpos($string, '`') !== false) {
			return $string;
		}
		if(strpos($string,Library::dotSeparator) !== false) { // Todo: Replace with Regexp
			$parts = explode(Library::dotSeparator, $string);
			if(isset($parts[1]) && $parts[1] == '*') {
				return '`' . $parts[0] . '`.*';
			} else {
				return '`' . implode('`.`', $parts) . '`';
			}
		} else {
			return '`' . $string . '`';
		}
	}
	
	protected function orderByHelper() {
		$sql = '';
		if(!empty($this->orderBy)){
			$sql = ' ORDER BY ';
			$first = true;
			foreach($this->orderBy as $order){
				if(!$first) { $sql .= ', '; } else { $first = false; }
				$sql .= $order;
			}
		}
		return $sql;
	}
	
	protected function rangeHelper() {
		$sql = '';
		if(isset($this->limit)){ $sql .= ' LIMIT ' . $this->limit; }
		if(isset($this->offset)){ $sql .= ' OFFSET ' . $this->offset; }
		return $sql;
	}
	

	public function getCriteria() {
		return array_merge($this->conditions, $this->assignments);
	}
	public function getTable() {
		return $this->table;
	}
}

class Criterion {
	public $column;
	public $pdoLabel;
	public $value;
	public $operator;
	
	const GREATER_THAN = ' > ';
	const GREATER_THAN_EQUAL_TO = ' >= ';
	
	const LESS_THAN = ' < ';
	const LESS_THAN_EQUAL_TO = ' <= ';
	
	const EQUAL_TO = ' = ';
	const NOT_EQUAL_TO = ' != ';
	
	const LIKE = ' LIKE ';
	const NOT_LIKE = ' NOT LIKE ';
	
	const IS_NULL = ' IS NULL';
	const IS_NOT_NULL = ' IS NOT NULL';
	
	const COLON = ':';
	
	const ASSIGNMENT = '=';
	const ASSIGNMENT_PREFIX = 'assgn_';
	
	const UNDERSCORE = '_';
	
	public function __construct($column, $value, $operator, $pdoLabel = null){
		$this->column = $column;
		$this->value = $value;
		$this->operator = $operator;
		if(!isset($pdoLabel)) {
			$this->pdoLabel = preg_replace('/[ \-.,\(\)`]/', '_', $column);
		} else {
			$this->pdoLabel = preg_replace('/[ \-.,\(\)`]/', '_', $pdoLabel);
		}
	}
	
	public function getQueryParameter() {
		// Begin workaround for PDO's poor numeric binding
		if(is_numeric($this->value)) {
			return $this->value;
		}
		// End workaround
		
		if($this->operator == self::ASSIGNMENT) { 
			return self::COLON . self::ASSIGNMENT_PREFIX . $this->pdoLabel;
		} elseif($this->operator == self::IS_NULL || $this->operator == self::IS_NOT_NULL) {
			return '';
		} else {
			return self::COLON . $this->pdoLabel;
		}
	}
}

class Join {
	const NATURAL = 'NATURAL';
	
	const LEFT = 'LEFT';
	const RIGHT = 'RIGHT';
	const FULL = 'FULL';
	
	const INNER = 'INNER';
	const OUTER = 'OUTER';
	const CROSS = 'CROSS';
	
	public $natural;
	public $leftRightOrFull;
	public $innerOuterOrCross = 'OUTER';
	
	public $table;
	public $tablePrimaryKey;
	public $fromTableForeignKey;
	
	public function __construct($leftRightOrFull, $innerOuterOrCross, $table, $tablePrimaryKey, $fromTableForeignKey, $natural = ''){
		$this->natural = $natural;
		$this->leftRightOrFull = $leftRightOrFull;
		$this->innerOuterOrCross = $innerOuterOrCross;
		$this->table = $table;
		$this->tablePrimaryKey = $tablePrimaryKey;
		$this->fromTableForeignKey = $fromTableForeignKey;
	}
}

?>