<?php
// MySQLStatementBuilder.php

/**
 * Builds series of MySQL Insert,Select,Update,and Delete statements.
 *
 * @author Matthew Lindberg <webmaster@thelindbergs.org>
 * @version 0.2
 *
 * @todo Re-evaluate entire class and refactor to include more MySQL functionality and an easier interface.
 */
class MySQLStatementBuilder
{
	var $inserts;     	// inserts[<tablename>][<column>] 		= columnvalue 
	
	var $updates;		// updates[<tablename>][<column>] 		= columnvalue
	
	var $selects;		// selects[<tablename>]					= array( <column1>, <column2>, ... )
	
	var $deletes;		// deletes[]							= tablename
	
	var $constraints;	// constraints[<tablename>][<column>] 	= array( literal, operator )
	
	var $joins;			// joins[<tablename1>][<column1>][<tablename2>][<column2>] = operator
	
	var $valid_ops = array("=",">",">=","<","<=");
	
	
	
	/**
	 * Constructor.
	 *
	 * @access public
	 */
	function MySQLStatementBuilder()
	{
		global $__CS; 
		$__CS->EnterMethod("MySQLStatementBuilder");
		
		$this->clear();
		
		$__CS->ExitMethod("MySQLStatementBuilder");
	}
	
	
	
	/**
	 * Clears all set statement parameters.  Call this if you are
	 * building a fresh query.
	 *
	 * @access public
	 */
	function clear()
	{
		global $__CS; 
		$__CS->EnterMethod("clear");
		
		$this->inserts 			= array();
		$this->updates 			= array();
		$this->selects			= array();
		$this->deletes			= array();
		$this->constraints 		= array();
		$this->joins 			= array();
		
		$__CS->ExitMethod("clear");
	}
	
	
	
	/**
	 * Adds a set of table column selections.
	 *
	 * @param $table string
	 * @param $columns array
	 * @access public
	 */
	function addSelect($table, $columns)
	{
		global $__CS; 
		$__CS->EnterMethod("addSelect");
		$__CS->SendVar($table,"table");
		$__CS->SendVar($column,"column");
		
		$this->selects[$table] = $columns;
		
		$__CS->ExitMethod("addSelect");
	}
	
	
	
	/**
	 * Adds an inner join constraint.
	 * joins[<tablename1>][<column1>][<tablename2>][<column2>] = operator
	 *
	 * @param $table1 string
	 * @param $column1 string
	 * @param $operator string
	 * @param $table2 string
	 * @param $column2 string
	 * @access public
	 */
	function addInnerJoin($table1,$column1,$operator,$table2,$column2)
	{
		global $__CS; 
		$__CS->EnterMethod("addInnerJoin");
		$__CS->SendVar($table1,"table1");
		$__CS->SendVar($column1,"column1");
		$__CS->SendVar($operator,"operator");
		$__CS->SendVar($table2,"table2");
		$__CS->SendVar($column2,"column2");
		
		$this->joins[$table1][$column1][$table2][$column2] = $operator;
		
		$__CS->ExitMethod("addInnerJoin");
	}
	
	
	
	/**
	 * Adds a new column/value pair to insert into a table.
	 *
	 * @param $table string
	 * @param $column string
	 * @param $value
	 * @access public
	 */
	function addInsert($table, $column, $value)
	{
		global $__CS; 
		$__CS->EnterMethod("addInsert");
		$__CS->SendVar($table,"table");
		$__CS->SendVar($column,"column");
		$__CS->SendVar($value,"value");
		
		$this->inserts[$table][$column] = $value;
		
		$__CS->ExitMethod("addInsert");
	}
	
	
	
	/**
	 * Adds a new column/value pair to update a table.
	 *
	 * @param $table string
	 * @param $column string
	 * @param $value 
	 * @access public
	 */
	function addUpdate($table, $column, $value)
	{
		global $__CS; 
		$__CS->EnterMethod("addUpdate");
		$__CS->SendVar($table,"table");
		$__CS->SendVar($column,"column");
		$__CS->SendVar($value,"value");
		
		$this->updates[$table][$column] = $value;
		
		$__CS->ExitMethod("addUpdate");
	}
	
	
	
	/**
	 * Adds a new table to delete from.
	 *
	 * @param $table string
	 * @access public
	 */
	function addDelete($table)
	{
		global $__CS; 
		$__CS->EnterMethod("addDelete");
		$__CS->SendVar($table,"table");
		
		$this->deletes[] = $table;
		
		$__CS->ExitMethod("addDelete");
	}
	
	
	
	/**
	 * Adds an SQL statement constraint on a column value.
	 *
	 * @param $table string
	 * @param $column string
	 * @param $literal
	 * @param $operator string [ = | > | >= | < | <= ]
	 */
	function addConstraint($table,$column,$operator,$literal)
	{
		global $__CS; 
		$__CS->EnterMethod("addConstraint");
		$__CS->SendVar($table,"table");
		$__CS->SendVar($column,"column");
		$__CS->SendVar($literal,"literal");
		$__CS->SendVar($constraint,"operator");
		
		if( !in_array($operator,$this->valid_ops) ) {
			trigger_error(__ERROR.": ".$operator." is not a valid MySQL operator.");
		}
		$this->constraints[$table][$column] = array($literal,$operator);
		
		$__CS->ExitMethod("addConstraint");
	}
	
	
	
	/**
	 * Generates a WHERE clause for a single table using the 'constraints' array.
	 * 
	 * @param $tablename string
	 * @param $die_on_empty boolean
	 * @access private
	 */
	function _generateSingleWhereClause($tablename,$die_on_empty=false,$add_on_to_existing=false)
	{
		global $__CS; 
		$__CS->EnterMethod("_generateSingeWhereClause");
		$__CS->SendVar($tablename,"tablename");
		$__CS->SendVar($die_on_empty,"die_on_empty");
		
		$sql = "";
		
		if( !array_key_exists($tablename, $this->constraints) ) {
			if( $die_on_empty ) {
				trigger_error(__ERROR.": You must specify constraints for a DELETE statement or you risk deleting all table records!");
			}
			else {
				return $sql;
			}
		}
		
		if($add_on_to_existing) {
			$sql .= " AND ";
		}
		else {
			$sql .= " WHERE ";
		}
		$i = 0;
		while( list($column,$constraint) = each($this->constraints[$tablename]) ) {
			list( $literal, $op ) = $constraint;
			if( $i > 0 ) {
				$sql .= " AND ";
			}
			$sql .= $tablename.".".$column." ".$op." ";
					
			if( is_string($literal) ) {
				$sql .= '"'.$literal.'"';
			}
			else if( is_null($literal) ) {
				$sql .= "NULL";
			}
			else {
				$sql .= $literal;
			}
			$i++;
		}
		reset($this->constraints);
		
		$__CS->SendSQL($sql);
		$__CS->ExitMethod("_generateSingleWhereClause");
		return $sql;
	}
	
	
	
	/**
	 * Generates a WHERE clause for multiple tables using the 'joins' array.
	 * joins[<tablename1>][<column1>][<tablename2>] = column2
	 *
	 * @access private
	 */
	function _generateJoinWhereClause()
	{
		global $__CS; 
		$__CS->EnterMethod("_generateJoinWhereClause");
		
		$sql = "";
		if( count($this->joins) == 0 ) {
			return $sql;
		}
		$sql .= " WHERE ";
		$i = 0;
		while( list($table1,$table1columnjoins) = each($this->joins) ){
			while( list($column1, $table2joins ) = each($table1columnjoins) ) {
				while( list($table2, $table2columnjoins) = each($table2joins) ) {
					while( list($column2, $operator) = each($table2columnjoins) ) {
						
						if( $i > 0 ) {
							$sql .= " AND ";
						}
						$sql .= $table1.".".$column1.$operator.$table2.".".$column2;
						$i++;
					}
					
				}
			
			}
		}
		reset($this->joins);
		$__CS->SendSQL($sql);
		$__CS->ExitMethod("_generateJoinWhereClause");
		return $sql;
	}
	
	
	
	/**
	 * Generates a series of MySQL Insert statements using the 'inserts' array of 
	 * table/column/values.
	 *
	 * @access public
	 * @return array
	 */
	function getInsertSQL()
	{
		global $__CS; 
		$__CS->EnterMethod("getInsertSQL");
		
		$statements = array();
		$sql = "";
		// Generate a MySQL Insert statement for each table
		while( list($table,$allcolumns) = each($this->inserts) ) {
			$__CS->SendNote("Start generating INSERT statement for table ".$table);
			
			$sql .= "INSERT INTO ".$table." (";
			$values = "";  
			// Write all column names and values to insert
			$i = 0;
			while( list($column,$value) = each($allcolumns)) {
				$__CS->SendNote("Adding column name ".$column." and value ".$value);
				
				// Add comma delimeters after the first column and value pair
				if( $i != 0 ) {
					$sql 	.= ",";
					$values .= ",";
				}
				$sql 	.= $column;
				
				if( is_string($value) ) {
					$values .= '"'.$value.'"';
				}
				else if( is_null($value) ) {
					$values .= "NULL";
				}
				else {
					$values .= $value;
				}
				$i++;
			}
			$sql .= ") VALUES (".$values.")";
			$statements[] = $sql;
			$sql = "";
			reset($allcolumns);
		}
		reset($this->inserts);
		
		for($i=0; $i<count($statements); $i++) {
			$__CS->SendSQL($statements[$i]);
		}
		
		$__CS->ExitMethod("getInsertSQL");
		return $statements;
	}
		
		
	
	/**
	 * Generates a series of MySQL UPDATE statements.
	 * Uses the 'updates' and 'constraints' arrays.
	 *
	 * @access public 
	 */
	function getUpdateSQL()
	{
		global $__CS; 
		$__CS->EnterMethod("getUpdateSQL");
		
		$statements = array();
		// Generate a MySQL Update statement for each table
		while( list($table,$allcolumns) = each($this->updates) ) {
			$__CS->SendNote("Start generating UPDATE statement for table ".$table);
			
			$sql = "UPDATE ".$table." SET ";
			// Write all column names and values to insert
			$i = 0;
			while( list($column,$value) = each($allcolumns)) {
				$__CS->SendNote("Adding column name ".$column." and value ".$value);
				
				// Add comma delimeters after the first column and value pair
				if( $i != 0 ) {
					$sql 	.= ",";
				}
				$sql 	.= $column;
				
				if( is_string($value) ) {
					$sql .= '="'.$value.'"';
				}
				else if( is_null($value) ) {
					$sql .= "=NULL";
				}
				else {
					$sql .= "=".$value;
				}
				$i++;
			}
			$sql .= $this->_generateSingleWhereClause($table);
			$statements[] = $sql;
			reset($allcolumns);
		}
		reset($this->updates);
		
		for($i=0; $i<count($statements); $i++) {
			$__CS->SendSQL($statements[$i]);
		}
		
		$__CS->ExitMethod("getUpdateSQL");
		return $statements;
	}
	
	
	
	/**
	 * Generates a single MySQL SELECT statement with single table constraints.
	 * Uses the 'selects' and 'constraints' arrays.
	 *
	 * @param $count_only boolean
	 * @param $limit array( <start_from_index>, <# items> )
	 * @access public
	 */
	function getSingleSelectSQL($count_only=false, $limit=NULL)
	{
		global $__CS;
		$__CS->EnterMethod("getSingleSelectSQL"); 
		
		
		$sql = "";
		// Generate a MySQL Select statement for single table
		list($table,$columns) = each($this->selects);
		$__CS->SendNote("Start generating SELECT statement for table ".$table);
		
		
		if($count_only) {
			$sql .= " SELECT Count(*) As rowcount ";
		}
		else {
			$sql .= " SELECT ";
			$i = 0;
			foreach( $columns as $column ) {
				if( $i > 0 ) {
					$sql .= ",".$column;
				}
				else {
					$sql .= $column;
				}
				$i++;
			}
		}
		
		$sql .= " FROM ".$table;
		$sql .= $this->_generateSingleWhereClause($table,false);
		if( $limit ) {
			list($start_from, $total) = $limit;
			$sql .= " LIMIT ".$start_from.",".$total;
		}

		reset($this->selects);
		
		$__CS->SendSQL($sql);
		$__CS->ExitMethod("getSingleSelectSQL");
		return $sql;
	}
	
	
	
	/**
	 * Generates a single MySQL SELECT statement with multiple table join constraints.
	 * Uses the 'joins' and 'selects' arrays. 
	 * joins[<tablename1>][<column1>]		= array[] = array( <tablename2>, <column2> )
	 *
	 * @param $count_only boolean
	 * @param $limit array( <start_from_index>, <# items> )
	 * @access public
	 * @
	 */
	function getJoinSelectSQL($count_only=false, $limit=NULL)
	{
		global $__CS; 
		$__CS->EnterMethod("getJoinSelectSQL");
		
		$sql = "SELECT ";
		$from = " FROM ";
		$i = 0;
		$j = 0;
		while( list($table,$columns) = each($this->selects) ) {
			if( $i > 0 ) {
				$from .= ",";
			}
			$from .= $table;
			if($count_only) {
				if( $i == 0 ) {
					$sql .= "Count(*) As rowcount ";
				}
				
			}
			else {
				foreach( $columns as $col ) {
					if( $j > 0 ) {
						$sql .= ",";
					}
					$sql .= $table.".".$col;
					$j++;
				}
			}
			$i++;
		}
		reset($this->selects);
		$sql .= $from;
		
		$sql .= $this->_generateJoinWhereClause();
		
		$constraint_tables = array_keys($this->constraints);
		for($i=0; $i<count($constraint_tables); $i++) {
			$sql .= $this->_generateSingleWhereClause($constraint_tables[$i],false,true);
		}
		
		if( $limit ) {
			list($start_from, $total) = $limit;
			$sql .= " LIMIT ".$start_from.",".$total;
		}
		
		$__CS->SendSQL($sql);
		$__CS->ExitMethod("getJoinSelectSQL");
		return $sql;
	}
	
	
	
	/**
	 * Generates a series of MySQL deletes statements.
	 * Uses the 'deletes' and 'constraints' arrays.
	 *
	 * @access public
	 */
	function getDeleteSQL()
	{
		global $__CS; 
		$__CS->EnterMethod("getDeleteSQL");
		
		$sql = "";
		// Generate a MySQL Delete statement for each table
		while( list($index,$table) = each($this->deletes) ) {
			$__CS->SendNote("Start generating DELETE statement for table ".$table);
			
			$sql .= "DELETE FROM ".$table;
			$sql .= $this->_generateSingleWhereClause($table,true);
		}
		reset($this->deletes);
		
		$__CS->SendSQL($sql);
		$__CS->ExitMethod("getDeleteSQL");
		return $sql;
	}

}
?>