Login   Register  
PHP Classes
elePHPant
Icontem

File: Sql_Compiler.class.php

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  Classes of Tom Schaefer  >  SQL Parse and Compile  >  Sql_Compiler.class.php  >  Download  
File: Sql_Compiler.class.php
Role: Class source
Content type: text/plain
Description: compiler class
Class: SQL Parse and Compile
Parse and compose SQL queries programatically
Author: By
Last change: update
Date: 5 years ago
Size: 10,961 bytes
 

Contents

Class file image Download
<?php

/**
 *
 * Sql_Compiler
 * @package Sql
 * @author Thomas Sch�fer
 * @since 02.12.2008
 * @desc compiles sql statements into string
 */
/**
 *
 * Sql_Compiler
 * @package Sql
 * @author Thomas Sch�fer
 * @since 02.12.2008 07:49:30
 * @desc compiles sql statements into string
 */
class Sql_Compiler {

	const LINEBREAK = "\n";
	const OPENBRACE = "(";
	const CLOSEBRACE = ")";
	const COMMA = ",";
	const SEMICOLON = ";";
	const COLON = ".";
	const ESCAPE = "'";
	const SPACE = " ";
	const TICK = "`";
	const ALIAS = " AS ";
	const ON = " ON ";
	const ASTERISK = "*";
	const DBLQUOTE = '"';
	const QUOTE =  "'";

	const SQL_NOT = "NOT";
	const SQL_NULL = "NULL";
	const SQL_IS = "IS";
	const SQL_WHERE = "WHERE";
	const SQL_AND = "AND";
	const SQL_OR = "OR";
	const SQL_GROUPBY = "GROUP BY";
	const SQL_HAVING = "HAVING";
	const SQL_SELECT = "SELECT";
	const SQL_FROM = "FROM";
	const SQL_DISTINCT = "DISTINCT";
	const SQL_ORDERBY = "ORDER BY";
	const SQL_LIMIT = "LIMIT";
	const SQL_INSERT = "INSERT INTO";
	const SQL_REPLACE = "REPLACE INTO";
	const SQL_DELETE = "DELETE";
	const SQL_UPDATE = "UPDATE";
	const SQL_SET = "SET";
	const SQL_INTO = "INTO";
	const SQL_VALUES = "VALUES";
	const SQL_UNION = "UNION";
	const SQL_ADD = "ADD";
	const SQL_ALTER = "ALTER";
	const SQL_CREATE = "CREATE";
	const SQL_VIEW = "VIEW";
	const SQL_INDEX = "INDEX";
	const SQL_TABLE = "TABLE";
	const SQL_SHOW = "SHOW";
	const SQL_GRANT = "GRANT";
	const SQL_COLUMNS = "COLUMNS";
	const SQL_EXPLAIN = "EXPLAIN";
	const SQL_DESCRIBE = "DESCRIBE";
	const SQL_ANALYSE = "ANALYSE";
	const SQL_OPTIMIZE = "OPTIMIZE";
	const SQL_DROP = "DROP";
	const SQL_PRIMARYKEY = "PRIMARY KEY";
	const SQL_DEFAULT = "DEFAULT";
	const SQL_KEY = "KEY";
	const SQL_USING = "USING";
	const SQL_ON = "ON";


	/**
	 * linebreak char property
	 * @var string
	 */
	public static $breakline = self::LINEBREAK;

	/**
	 * indentation level property
	 * @var integer
	 */
	protected static $indent = 0;

	/**
	 * sql object tree
	 * @var array
	 */
	protected $tree;

	/**
	 * holder of copy
	 * @var array
	 */
	protected $backupTree;


	/**
	 * notBreakLine
	 *
	 * @desc unset line break
	 * @access public
	 * return void
	 */
	public static function notBreakline() {
		self::$breakLine = '';
	}

	/**
	 * setIndent
	 * @desc set indentation string
	 * @desc public
	 * @param string $indent
	 */
	public static function setIndent($indent) {
		self::$indent = $indent;
	}

	/**
	 * indent
	 *
	 * @access private
	 * @desc indentation string by level
	 * @return string
	 */
	public static function indent() {
		return str_pad(self::SPACE, 2 * (self::$indent+1),self::SPACE);
	}

	/**
	 * isError
	 *
	 * @desc check if is error
	 * @param Exception $data
	 * @param string $code
	 * @return mixed
	 */
	public static function isError($data, $code = null)
	{
		if (is_a($data, 'Exception')) {
			if (is_null($code)) {
				return true;
			} elseif (is_string($code)) {
				return $data->getMessage() == $code;
			} else {
				return $data->getCode() == $code;
			}
		}
		return false;
	}

	/**
	 * raiseError
	 *
	 * @desc raise an exception
	 * @access private
	 * @param string $message
	 * @return Exception
	 */
	public static function raiseError($message) {
		return new Exception($message);
	}

	public static function escape($value) {
		return self::DBLQUOTE . $value . self::DBLQUOTE;
	}

	public static function compileColumns($sql) {

		for ($i = 0; $i < count(Sql_Object :: get('tree.ColumnNames')); $i++) {
            // added 2008-01-16
            if(Sql_Object :: has('tree.ColumnTableAliases') and Sql_Object :: count('tree.ColumnTableAliases')) {
                $column = '';
                if(Sql_Object :: length('tree.ColumnTableAliases.' . $i)>0)
                    $column = Sql_Object :: get('tree.ColumnTableAliases.' . $i) . ".";
                $column .= Sql_Object :: get('tree.ColumnNames.' . $i);
            } elseif(Sql_Object :: has('tree.ColumnTables') and Sql_Object :: count('tree.ColumnTables')) {
                $column = '';
                if(Sql_Object :: length('tree.ColumnTables.' . $i)>0)
                $column = Sql_Object :: get('tree.ColumnTables.' . $i) . ".";
                $column .= Sql_Object :: get('tree.ColumnNames.' . $i);
            } else {
			    $column = Sql_Object :: get('tree.ColumnNames.' . $i);
            }
			if (Sql_Object :: get('tree.ColumnAliases.' . $i) != '') {
				$column .= Sql_Compiler :: ALIAS . Sql_Object :: get('tree.ColumnAliases.' . $i);
			}
			// add only if there is a column
			if(strlen($column)) {
				$column_names[] = $column;
			}
		}

		// loop on functions
		for ($i = 0; $i < count(Sql_Object :: get('tree.Function')); $i++) {
			if (Sql_Object :: has('tree.Function.' . $i . '.Arg')) {
				$funcName = Sql_Object :: get('tree.Function.' . $i . '.Name');
				$column = Sql_Object :: get('tree.Function.' . $i . '.Name') . Sql_Compiler :: OPENBRACE;
				if (Sql_Object :: has('tree.Function.' . $i . '.Distinct')) {
					$column .= Sql_Compiler :: SQL_DISTINCT . Sql_Compiler :: SPACE;
				}
				switch (strtolower($funcName)) {
					case 'case' :
					case 'if' :
						$column_names[] = Sql_CompilerFlow::compile($funcName, Sql_Object :: get('tree.Function.' . $i));
						break;
					case Sql_Object::has("functions.".strtolower($funcName)):
						$column_names[] = Sql_CompilerFunction::compile($funcName, Sql_Object :: get('tree.Function.' . $i));
						break;
					default :
						if (is_array(Sql_Object :: get('tree.Function.' . $i . '.Arg'))) {
							$column .= implode(Sql_Compiler :: COMMA, Sql_Object :: get('tree.Function.' . $i . '.Arg'));
						} else {
							$column .= Sql_Object :: get('tree.Function.' . $i . '.Arg');
						}
						$column .= Sql_Compiler :: CLOSEBRACE;
						if (Sql_Object :: get('tree.Function.' . $i . '.Alias') != '') {
							$column .= Sql_Compiler :: ALIAS . Sql_Object :: get('tree.Function.' . $i . '.Alias');
						}
						$column_names[] = $column;
						break;
				}
			}
		}

		if (isset ($column_names)) {
			$sql .= implode(", ", $column_names);
		}

		return $sql;
	}

	/**
	 * values of where
	 *
	 * @desc set condition items by type
	 * @access private
	 * @param array $arg
	 * @return string
	 */
	public static function getWhereValue ($arg)
	{
		switch ($arg['Type'])
		{
			case 'null':
			case 'ident':
			case 'real_val':
			case 'int_val':
				$value = $arg['Value'];
				break;
			case 'text_val':
				$value = self::ESCAPE .$arg['Value']. self::ESCAPE;
				break;
			case 'subselect':
				$value = self::OPENBRACE;
				$value .= self::doCompile($arg['Value']);
				$value .= self::CLOSEBRACE;
				break;
			case 'subclause':
				$value = self::OPENBRACE . self::compileSearchClause($arg['Value']). self::CLOSEBRACE;
				break;
			default:
				return self::raiseError('Unknown type: '.$arg['type']);
		}
		return $value;
	}

	/**
	 * get params
	 *
	 * @desc identify parameters within compilation
	 * @access private
	 * @param array $arg
	 * @return string
	 */
	public static function getParams($arg)
	{
		for ($i = 0; $i < count ($arg['Type']); $i++) {
			switch ($arg['Type'][$i]) {
				case 'ident':
				case 'real_val':
				case 'int_val':
					$value[] = $arg['Value'][$i];
					break;
				case 'text_val':
					$value[] = self::ESCAPE .$arg['Value'][$i]. self::ESCAPE;
					break;
				default:
					return self::raiseError('Unknown type: '.$arg['Type']);
			}
		}
		$value = self::OPENBRACE . implode(self::COMMA . self::SPACE, $value) . self::CLOSEBRACE;
		return $value;
	}

	/**
	 * search clause
	 *
	 * @desc build sql where statement part
	 * @accress private
	 * @param array $where_clause
	 * @return string
	 */
	public static function compileSearchClause($where_clause)
	{
		$value = '';
		if (isset ($where_clause['Left']['Value'])) {
			$value = self::getWhereValue ($where_clause['Left']);
			if (self::isError($value)) {
				return $value;
			}
			$sql = $value;
		} else {
			$value = self::compileSearchClause($where_clause['Left']);
			if (self::isError($value)) {
				return $value;
			}
			$sql = $value;
		}
		if (isset ($where_clause['Op'])) {
			if (strtolower($where_clause['Op']) == 'in') {
				if($where_clause["Right"]["Type"]=="command") {
					// new instance enabling sub-selects
					$value 	= self::OPENBRACE
							. Sql_Compiler::compile($where_clause["Right"]["Value"])
							. self::CLOSEBRACE;

				} else {
					$value = self::getParams($where_clause['Right']);
				}
				if (self::isError($value)) {
					return $value;
				}

				$sql 	.= self::SPACE
						. $where_clause['Op']
						. self::SPACE
						. $value;

			} elseif (strtolower($where_clause['Op']) == 'is') {
				if (isset ($where_clause['Neg'])) {
					$value 	= self::SQL_NOT
							. self::SPACE
							. self::SQL_NULL;
				} else {
					$value = self::SQL_NULL;
				}
				$sql 	.= self::SPACE
						. self::SQL_IS
						. self::SPACE
						. $value;
			} elseif (strtolower($where_clause['Op']) == 'between') {
				// added 2008-12-19
				$sql .= self::SPACE;
				$sql .= $where_clause['Op'];
				$sql .= self::SPACE;

				if(isset($where_clause['Right'])){
					$sql .= self::SPACE;
					$sql .= $where_clause['Right']['Value']['Left']["Value"];
					$sql .= self::SPACE;
					$sql .= $where_clause['Right']['Value']["Op"];
					$sql .= self::SPACE;
					$sql .= $where_clause['Right']['Value']['Right']["Value"];
				}
			} else {
				$sql .= self::SPACE.$where_clause['Op'].self::SPACE;
				if (isset ($where_clause['Right']['Value'])) {
					$value = self::getWhereValue ($where_clause['Right']);
					if (self::isError($value)) {
						return $value;
					}
					$sql .= $value;
				} else {
					$value = self::compileSearchClause($where_clause['Right']);
					if (self::isError($value)) {
						return $value;
					}
					$sql .= $value;
				}
			}
		}
		return $sql;
	}

	private function reCompile($array){
		$object = new Sql_Compiler();
		return $object->compile($array);
	}

	private static function doCompile($array){
		$object = new Sql_Compiler();
		return $object->compile($array);
	}

	public function compile($array = null)
	{

		$this->tree = $array;
		pdbg($this->tree, "orange", __LINE__,__FILE__);
		switch ($this->tree['Command']) {
			case 'union':
			case 'select':
			case 'update':
			case 'delete':
			case 'insert':
			case 'replace':
				$className = __CLASS__.ucfirst($this->tree['Command']);
				return call_user_func(array($className,"compile"), $array);
			default:
				return self::raiseError('Unknown action: '.$this->tree['Command']);
		}

	}

}