Location: PHPKode > projects > My Scrapbook > myscrapbook_3_1/txt-db-api/sql.php
<?php
/**********************************************************************
						 Php Textfile DB Access API
						Copyright 2002 by c-worker.ch
						  http://www.c-worker.ch
***********************************************************************/
/**********************************************************************
This program 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 2 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, write to the Free Software
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
***********************************************************************/

include_once(API_HOME_DIR . "util.php");
include_once(API_HOME_DIR . "const.php");


/**********************************************************************
							Global Vars 
***********************************************************************/

// Special Strings in SQL Queries
// Insert Strings before Single Chars !! (e.g. >= before >)
$g_sqlComparisonOperators=array("<>","!=",">=","<=","=","<",">");
$g_sqlQuerySpecialStrings = array_merge($g_sqlComparisonOperators, array("(",")",";",",","."));
$g_sqlQuerySpecialStringsMaxLen = 2;


/**********************************************************************
							SqlParser
***********************************************************************/
// Used to parse an SQL-Query (as String) into an SqlObject
class SqlParser {
	
	var $query_str="";
	
	/***********************************
		 		Constructor
	************************************/
	function SqlParser($sql_query_str) {
		debug_print ("New SqlParser instance: $sql_query_str<br>");
		$this->query_str=$sql_query_str;
	}
	
	/***********************************
		 	Parse Dispatcher
	************************************/
	// Returns a SqlQuery Object or null if an error accoured
	function parseSqlQuery() {
		$type="";
		if(!$type=$this->parseNextElement()) 
			return null;
		$type=strtoupper($type);
		switch($type) {
			case "SELECT":
				return $this->parseSelectQuery();
				break;
			case "INSERT":
				return $this->parseInsertQuery();
				break;
			case "DELETE":
				return $this->parseDeleteQuery();
				break;
			case "UPDATE":
				return $this->parseUpdateQuery();
				break;
			case "CREATE":
				if(strtoupper($this->peekNextElement())=="TABLE") {
					$this->skipNextElement();
					return $this->parseCreateTableQuery();
				}	
				break;
			default:
				print_error("SQL Type $type not supported");
				return null;
		}
	}
	
	
	/***********************************
		 Select Query Parse Function
	************************************/
	
	// SELECT must be removed here (do not call this Function directly !!!)
	// returns a SqlQuery Object
	function parseSelectQuery() {
		$fieldNames=array();
		$fieldTables=array();
		$fieldAliases=array();
		$fieldValues=array();
		$tables=array();
		$orderFields=array();
		$orderTypes=array();
		$where_expr="";
		
		// parse Fields
		$fieldIndex=0;
		while(!is_empty_str($elem=$this->parseNextElement())) {
			if(strtoupper($elem)=="FROM")
				break;
			if($this->peekNextElement()==".") {
				$fieldTables[$fieldIndex]=$elem;
				$this->skipNextElement();
				$fieldNames[$fieldIndex]=$this->parseNextElement();
			} else {
				$fieldTables[$fieldIndex]="";
				$fieldNames[$fieldIndex]=$elem;
			}
			if(strtoupper($this->peekNextElement())=="AS") {
				$this->skipNextElement();
				$fieldAliases[$fieldIndex]=$this->parseNextElement();
			} else {
				$fieldAliases[$fieldIndex]="";
			}
			if($this->peekNextElement()==",") {
				$this->skipNextElement();
			}
			$fieldIndex++;
		}
		
		// parse Tables
		$tableIndex=0;
		while(!is_empty_str($elem=$this->peekNextElement())) {
			if(strtoupper($elem)=="WHERE" || strtoupper($elem)=="ORDER" || $elem==";")
				break;
			$this->skipNextElement();
			if($elem==",")
				$tableIndex++;
			else
				$tables[$tableIndex]=$elem;
		}
		
		// parse Where statement (Raw, because the escape-chars are needend in the ExpressionParser)
		if(strtoupper($this->peekNextElement()) == "WHERE") {
			$this->skipNextElement();
			while(!is_empty_str($elem=$this->peekNextElementRaw())) {
				if(strtoupper($elem)=="ORDER" || $elem==";")
					break;
				$this->skipNextElement();
				
				// no " " on points
				if($elem==".") {
					remove_last_char($where_expr);
					$where_expr .= $elem;
				} else {
					$where_expr .= $elem . " ";
				}
			}
		}
		debug_print( "WHERE EXPR: $where_expr<br>");
		
		// parse ORDER BY
		$orderFieldIndex=0;
		if(strtoupper($this->peekNextElement()) == "ORDER") {
			$this->skipNextElement();
			if(strtoupper($this->parseNextElement())!="BY") {
				print_error("BY expected");
				return null;
			}
			
			while(!is_empty_str($elem=$this->peekNextElement())) {
				if($elem==";")
					break;
				$this->skipNextElement();
				if($elem==",") {
					$orderFieldIndex++;
				}
				else if(strtoupper($elem)=="ASC") 
					$orderTypes[$orderFieldIndex]=ORDER_ASC;
				else if(strtoupper($elem)=="DESC")
					$orderTypes[$orderFieldIndex]=ORDER_DESC;
				else {
					if(!isset($orderFields[$orderFieldIndex])) 
						$orderFields[$orderFieldIndex]=$elem;
					else
						$orderFields[$orderFieldIndex].=$elem;
					$orderTypes[$orderFieldIndex]=ORDER_ASC;
				}	
			}
		}
		
		return new SqlQuery("SELECT", $fieldNames, $tables, $fieldAliases, $fieldTables, $where_expr, $orderFields, $orderTypes);;
	}


	/***********************************
		 Insert Query Parse Function
	************************************/
	
	// INSERT must be removed here (do not call this Function directly !!!)
	// returns a SqlQuery Object
	function parseInsertQuery() {
		
		$fieldNames=array();
		$fieldTables=array();
		$fieldAliases=array();
		$fieldValues=array();
		$tables=array();
		$insertType="";
		
		
		// remove INTO
		if(strtoupper($this->peekNextElement())=="INTO") 
			$this->skipNextElement();
				
		// Read Table				
		$tables[0]=$this->parseNextElement();
		
		// Read Field Names between ()'s
		$fieldNamesIndex=0;
		if($this->peekNextElement()=="(") {
			while(($elem=$this->parseNextElement())!=")") {
				if($elem==",")
					$fieldNamesIndex++;
				else 
					$fieldNames[$fieldNamesIndex]=$elem;
			}
		}

		// read Insert Type
		$insertType=$this->parseNextElement();
		
		switch($insertType) {
			case "SELECT":
				break;
			case "SET":
				$commaCheck=5;  // table.field=xy 
				// Read Fields and Values
				$fieldNamesIndex=0;
				while( !is_empty_str(($elem=$this->parseNextElement())) && ($elem != ";")) {
					if($elem==",") {
						$commaCheck=5;
						$fieldNamesIndex++;
					} else if($elem=="=") {
						$commaCheck=2;
						$fieldValues[$fieldNamesIndex]=$this->parseNextElement();
					} else {
						$fieldNames[$fieldNamesIndex]=$elem;
					}
					$commaCheck--;
					if($commaCheck<=0) {
						print_error(", Expected");
						return null;
					}
				}
				break;
				
			case "VALUES":
				if($this->parseNextElement()!="(") {
					print_error("VALUES in the INSERT Statement must be in Braces \"(,)\"");
					return null;
				}
				$fieldValuesIndex=0;
				while(($elem=$this->parseNextElement())!=")") {
					if(is_empty_str($elem)) {
						print_error(") Expected");
						return null;
					}
					
					if($elem==",")
						$fieldValuesIndex++;
					else 
						$fieldValues[$fieldValuesIndex]=$elem;
				}
				break;
			default:
				print_error("Insert Type " . $insertType . " not supported");
				return null;
		}
		$sqlObj = new SqlQuery();
		$sqlObj->type = "INSERT";
		$sqlObj->fields=$fieldNames;
		$sqlObj->fieldAlias=$fieldAliases;
		$sqlObj->fieldTables=$fieldTables;
		$sqlObj->fieldValues=$fieldValues;
		$sqlObj->insertType=$insertType;
		$sqlObj->tables=$tables;
		
		return $sqlObj;
	}
	
	
	/***********************************
		 Delete Query Parse Function
	************************************/
	
	// DELETE must be removed here (do not call this Function directly !!!)
	// returns a SqlQuery Object
	function parseDeleteQuery() {
		
		$tables=array();
		$where_expr="";
		
		if(strtoupper($this->parseNextElement())!="FROM") {
			print_error("FROM expected");
			return null;
		}
		$tables[0]=$this->parseNextElement();
		
		// Because the Where Statement is not parsed with 
		// the parseXX Functions, this equals a Raw-Parse,
		// as needed for the ExpressionParser
		if(strtoupper($this->parseNextElement())=="WHERE") {
			$where_expr=$this->query_str;
			if(last_char($where_expr)==";")
				remove_last_char($where_expr);
		} else if ($elem=$this->parseNextElement()) {
			print_error("Nothing more expected: $elem");
			return null;
		}
		
		$sqlObj = new SqlQuery();
		$sqlObj->type = "DELETE";
		$sqlObj->tables=$tables;
		$sqlObj->where_expr=$where_expr;
		
		return $sqlObj;
	}
	
	
	/***********************************
		 Update Query Parse Function
	************************************/
	
	// UPDATE must be removed here (do not call this Function directly !!!)
	// returns a SqlQuery Object
	function parseUpdateQuery() {
		
		$fieldNames=array();
		$fieldValues=array();
		$tables=array();
		$where_expr="";
		
		// Read Table				
		$tables[0]=$this->parseNextElement();
		
		// Remove SET
		if(strtoupper($this->parseNextElement())!="SET") {
			print_error("SET expected");
			return null;
		}
		
		// Read Fields and Values
		$elem="";
		$fieldNamesIndex=0;
		while( !is_empty_str(($elem=$this->parseNextElement())) && ($elem != ";") && (strtoupper($elem) != "WHERE")) {
			if($elem==",")
				$fieldNamesIndex++;
			else if($elem=="=") {
				$fieldValues[$fieldNamesIndex]=$this->parseNextElement();
			} else {
				$fieldNames[$fieldNamesIndex]=$elem;
			}
		}
		
		// Raw-Parse Where Statement
		if(strtoupper($elem)=="WHERE") {
			$where_expr=$this->query_str;
			if(last_char($where_expr)==";")
				remove_last_char($where_expr);
		}


		$sqlObj = new SqlQuery();
		$sqlObj->type = "UPDATE";
		$sqlObj->fields=$fieldNames;
		$sqlObj->fieldValues=$fieldValues;
		$sqlObj->tables=$tables;
		$sqlObj->where_expr=$where_expr;
		
		return $sqlObj;
	}
	
	
	/***********************************
	  Create Table Query Parse Function
	************************************/
	
	// CREATE TABLE must be removed here (do not call this Function directly !!!)
	// returns a SqlQuery Object
	function parseCreateTableQuery() {
		$fieldNames=array();
		$fieldTypes=array();
		$tables=array();
	
		$tables[0]=$this->parseNextElement();	
		
		if($this->parseNextElement()!="(") {
			print_error("( expected");
			return null;
		}

		$name=""; $type="";
		$index=0;
		while( !is_empty_str(($name=$this->parseNextElement())) &&  ($type=$this->parseNextElement()) && $name!=";" && $type !=";") { 
			$fieldNames[$index]=$name;
			$fieldTypes[$index]=$type;
			if($this->parseNextElement()==",")
				$index++;
			else
				break;
		}
		
		$sqlObj = new SqlQuery();
		$sqlObj->type = "CREATE TABLE";
		$sqlObj->fields=$fieldNames;
		$sqlObj->fieldTypes=$fieldTypes;
		$sqlObj->tables=$tables;
	
		return $sqlObj;		
	}

	
	/***********************************
		 	Parse Helper Functions
	************************************/
	// All these Functions remove the parsed part out of
	// the source String ($str)
	
	function skipNextElement() {
		$this->parseNextElement(true);
	}
	
	function peekNextElement() {
		return $this->parseNextElement(false);
	}
	
	// does not remove Escape Chars
	function parseNextElementRaw() {
		return $this->parseNextElement(true, false);
	}
	
	// does not remove Escape Chars
	function peekNextElementRaw() {
		return $this->parseNextElement(false, false);
	}
	
	// Returns the next element of the SQL Query or "" at the End (use is_empty_str() to check)
	// if $remove is true the element will be remove from $this->query_str
	// if $removeEscapeChar is true, the the Escape Chars will be correctly removed,
	// e.g. \" => ", \\ => \
	function parseNextElement($remove=true, $removeEscapeChar=true) {
		global $g_sqlQuerySpecialStrings;
		global $g_sqlQuerySpecialStringsMaxLen;
		$inQuotes=false;
		$element="";
		
		// handles \ escape Chars
		$lastWasEscapeChar=false;
		
		for($i=0;$i<strlen($this->query_str);$i++) {
			$c=$this->query_str{$i};
			switch($c) {
				case "\\":
					if($lastWasEscapeChar) {
						$element.= $c;
						$lastWasEscapeChar=false;
					} else {
						$lastWasEscapeChar=true;
						if(!$removeEscapeChar)
							$element.= $c;
					}
					break;
				case "'":
				case "\"":
					if(!$lastWasEscapeChar)
						$inQuotes=(!$inQuotes);
					$element.= $c;
					break;
				case " ":
					if(!$inQuotes && strlen($element)>0) 
						break 2;
					else if($inQuotes)
						$element.= $c;
					break;
			}
			if($c!="\\")
				$lastWasEscapeChar=false;
			if($c=="\\" || $c=="\"" || $c=="'" || $c==" ")  // ugly..
				continue;                         
			
			$testSrt=substr($this->query_str,$i,$g_sqlQuerySpecialStringsMaxLen);
			if(
			(!$inQuotes) && 
			($specialChar=array_search_str_start($testSrt,$g_sqlQuerySpecialStrings))) {
				if(strlen($element)>0) {
					break;
				} else {
					$element=$specialChar;
					$i+=strlen($specialChar);
					break;
				}
			} else {
				$element.= $c;	
			}
		}
		if($remove) {
			$this->query_str=substr($this->query_str,$i);
			debug_print( "Element:" . $element . "<br>");
		}
		return $element;
	}
}

/**********************************************************************
								SqlQuery
***********************************************************************/
// Represents an SQL Query 
// Fields should be accessed directly here -> faster 

class SqlQuery {
	
	
	/***********************************
			Member Variables
	************************************/

	var $type;
	
	var $fields=array();
	var $fieldAlias=array();
	var $fieldTables=array();
	
	var $fieldTypes=array();	// At the Moment only used in CREATE TABLE (int, string OR inc)
								// may also used in other Queries 
	var $fieldValues=array(); 	// Used in: INSERT, UPDATE
	
	var $insertType=""; 		// Used in: INSERT ("VALUES", "SET" or "SELECT")
	
	var $orderFields=array(); 	// Used by: ORDER BY
	var $orderTypes=array();	// Used by: ORDER BY
	
	var $tables=array();
	var $where_expr;
	
	
	
	/***********************************
				Constructor
	************************************/
	
	function SqlQuery($type="SELECT", $fields=array(), $tables=array(), $fieldAlias=array(), $fieldTables=array(), $where_expr="", $orderFields=array(),$orderTypes=array()) {
		$this->type=$type;
		$this->fields=$fields;
		$this->tables=$tables;
		$this->where_expr=$where_expr;
		$this->fieldAlias=$fieldAlias;
		$this->fieldTables=$fieldTables;
		$this->orderFields=$orderFields;
		$this->orderTypes=$orderTypes; // ORDER_ASC or ORDER_DESC
	}
	
	function getSize() {
		return count($this->fields);
	}
	
	
	/***********************************
				Test
	************************************/
	// NOT Up to Date
	// Test's if the SqlQuery is valid
	// TRUE if ok, FALSE if not ok
	function test() {
		reset($this->fields);
		for($i=0;$i<count($this->fields);$i++) {
			if($this->fields[$i]=="*")
			{
				if($this->fieldAlias[$i]) {
					print_error("Cannot define Alias by a *");
					return FALSE;
				}
				continue;
			}
			if($key=array_search  ($this->fields[$i], $this->fields)) {
				if($i==$key)
					continue;
				if($this->fieldAlias[$i] == $this->fieldAlias[$key]) {
					print_error("Two Fields with the same name use no or the same alias (" . $this->fields[$i] . ", " . $this->fields[$key] . ")");
					return FALSE;
				}
				if(!$this->fieldTables[$i]) {
					print_error("Field " . $this->fields[$i] . " could belong to multiple Tables");
					return FALSE;
				}
			}
		}
		reset($this->fieldAlias);
		for($i=0;$i<count($this->fieldAlias);$i++) {
			if($key=array_search  ($this->fieldAlias[$i], $this->fieldAlias)) {
				if($i==$key || $this->fieldAlias[$i]=="")
					continue;
				print_error("Two Fields (" . $this->fields[$i] . ", " . $this->fields[$key] . ") use the same alias");
				return FALSE;
			}
		}
		
		reset($this->fields);
		// TODO: error ..?!?  SELECT nr, tabelle1.nr As nr FROM ....
		// produces no Error !
		for($i=0;$i<count($this->fieldAlias);$i++) {
			if(($key=array_search($this->fieldAlias[$i], $this->fields))) {
				if($i==$key) {
					continue;
				}
				print_error("Alias is the name from another field (" . $this->fieldAlias[$i] . ")");
				return FALSE;
			}
		}
		return TRUE;
		
	}

}

?>
Return current item: My Scrapbook