<?php
/*
	GDC = generic database controls
	
	Depends on:
	sqlLogger.php	(if logging is turned on)
	errorHandler.php
	
	Provides two classes, a genericTableReader and a genericTableWriter.
	Each provides generic but limited functionality for making simple
	SQL queries (SELECT, INSERT, and UPDATE).
	
	Provides a genericQueryControl object used by the reader/writer
	to execute the sql statement. Also triggers logging if turned on.
*/

// these two only affect code that uses genericTableReader or the Writer
// it will not affect code that uses the genericQueryControl directly
// those will have to control logging individually (use doLogging)
define('GDC_Log_Writes', true);
define('GDC_Log_Reads', false);

define('Default_SQLLog_Table', 'sqlLog');

class genericQueryControl{
	public $lastResult;		// mysql result handler object
	private $doLogging;		// bool
	private $logTable;		// string
	
	public function __construct($options = array()){
		$this->setDefaults();
		$this->setProperties($options);
	}
	public function queryDatabase($queryStatement, $customLogFields = array()){
		$sqlResult = mysql_query($queryStatement);
		if(!$sqlResult){
			// query failed!
			$errorMsg = 'MySQL query failed with error: "'.mysql_error().'"'."\n".'QUERY="'.$queryStatement.'"';
			trigger_error($errorMsg, E_USER_WARNING);
		}
		if ($this->doLogging && isset($this->logTable)){
			sql_log($queryStatement, $customLogFields, $this->logTable);
		}
		$this->lastResult = $sqlResult;
		return $sqlResult;
	}
	public function setProperties($options = array()){
		// a single setter function for the class
		foreach ($options as $property => $value){
			if (property_exists($this, $property)){
				$this->$property = $value;
			}
		}
	}
	public function getProperty($property = null){
		if (property_exists($this, $property)){
			$value = $this->$property;
		} else{
			$value = null;
		}
		return $value;
	}
	public function setDefaults(){
		$this->doLogging = false;
		$this->logTable = Default_SQLLog_Table;
	}
	
	public function loggingOff(){
		$this->doLogging = false;
	}
	public function loggingOn(){
		$this->doLogging = true;
	}
}

class genericTableReader{
	public $tableName;		// string, FROM database.table
	public $fieldsToRead; 	// assoc. array, [field,alias], SELECT field1,field2
	public $selectors;		// assoc. array, [field,value], WHERE field=value AND ...
	public $sortField;		// string, ORDER BY field, dir
	public $sortDirection;	// string, ASC or DSC, ORDER BY field, dir
	public $startIndex;		// integer, LIMIT start,length
	public $rowsToReturn;	// integer, LIMIT start,length
	public $customLogFields;	// assoc. array, [field,value], atypical fields for log table
	public $lastResult;		// handle to mysql result
	private $doLogging;		// bool, should reads be logged?
	private $logTable;		// string, if not specified, use default table name for logging
	private $queryCtrl;		// a genericQueryControl object for executing query
	
	public function __construct($options = array()){
		$this->setDefaults();
		$this->setProperties($options);
		$queryOptions = array('doLogging' => $this->doLogging, 'logTable' => $this->logTable);
		$this->queryCtrl = new genericQueryControl($queryOptions);
	}
	public function setDefaults(){
		$this->tableName = '';
		$this->fieldsToRead = array();
		$this->selectors = array();
		$this->sortField = '';
		$this->sortDirection = '';
		$this->startIndex = 0;
		$this->rowsToReturn = 100;	// stupid mysql
		$this->customLogFields = array();
		$this->doLogging = GDC_Log_Reads;
		$this->logTable = Default_SQLLog_Table;
	}
	public function setProperties($options = array()){
		// a single setter function for the class
		foreach ($options as $property => $value){
			if (property_exists($this, $property)){
				$this->$property = $value;
			}
		}
	}
	public function getProperty($property = null){
		if (property_exists($this, $property)){
			$value = $this->$property;
		} else{
			$value = null;
		}
		return $value;
	}
	public function getRows(){
		$rows = array();
		$this->lastResult = null;
		if ($this->isDataSane()){
			$sqlStatement = $this->getSQL();
			$customLogFields = $this->customLogFields;
			$sqlResult = $this->queryCtrl->queryDatabase($sqlStatement, $customLogFields);	// query database
			if (mysql_num_rows($sqlResult) > 0){
				$totalRows = mysql_num_rows($sqlResult);
				for($i=0; $i < $totalRows; $i++){
					$rows[$i] = mysql_fetch_object($sqlResult);
				}
			}
			$this->lastResult = $sqlResult;
		}
		return $rows;
	}
	public function loggingOff(){
		$this->doLogging = false;
		$this->queryCtrl->loggingOff();
	}
	public function loggingOn(){
		$this->doLogging = true;
		$this->queryCtrl->loggingOn();
	}
	
	private function getSQL(){
		$sql = '';
		$sql .= $this->getSELECTtext();
		$sql .= $this->getFROMtext();
		$sql .= $this->getWHEREtext();
		$sql .= $this->getHAVINGtext();		
		$sql .= $this->getORDERBYtext();
		$sql .= $this->getLIMITtext();
		return $sql;
	}
	private function getSELECTtext(){
		// "SELECT `field` AS "alias",`field` " or "SELECT * "
		$text = 'SELECT ';
		
		if (count($this->fieldsToRead) > 0) {
			foreach ($this->fieldsToRead as $fieldName => $alias){
				$text .= '`'.mysql_real_escape_string($fieldName).'`';
				if ($alias != ''){
					$text .= ' AS "'.mysql_real_escape_string($alias).'"';
				}
				$text .= ',';
			}
		} else{
			$text .= '*';
		}
		
		// remove final extra comma!
		$patternToFindComma = '/[,]$/';
		$replaceWith = '';
		$text = preg_replace($patternToFindComma, $replaceWith, $text);
		
		$text .= ' ';
		return $text;
	}
	private function getFROMtext(){
		// "FROM `tablename` "
		$text = 'FROM ';
		$text .= '`' . mysql_real_escape_string($this->tableName) . '`';
		$text .= ' ';
		return $text;
	}
	private function getWHEREtext(){
		// "WHERE `field`="value" AND `field`="value" "
		$text = 'WHERE 1=1';
		foreach ($this->selectors as $fieldName => $value){
			$text .= ' AND ';
			$text .= '`'.mysql_real_escape_string($fieldName).'`';
			if ($value !== null){
				$text .= '="'.mysql_real_escape_string($value).'"';
			} else{
				$text .= ' IS NULL';
			}
		}
		$text .= ' ';
		return $text;
	}
	private function getHAVINGtext(){
		// not implemented yet
		return '';
	}
	private function getORDERBYtext(){
		// "ORDER BY `field` ASC "
		$text = '';
		if (isset($this->sortField) && $this->sortField != ''){
			$text .= 'ORDER BY `'.mysql_real_escape_string($this->sortField).'`';
			if (isset($this->sortDirection) && ($this-sortDirection == 'ASC' || $this->sortDirection == 'DESC')){
				$text .= ' '.$this->sortDirection;
			}
		}
		$text .= ' ';
		return $text;
	}
	private function getLIMITtext(){
		// "LIMIT #,# "
		if (isset($this->startIndex)){
			$start = $this->startIndex;
		}
		if (isset($this->rowsToReturn)){
			$total = $this->rowsToReturn;
		}
		// convert $start and $total to strings, controlling format to prevent commas and scientific notation (stupid php)
		$start = number_format($start, 0, '', '');
		$total = number_format($total, 0, '', '');
		
		$text = 'LIMIT '.mysql_real_escape_string($start).','.mysql_real_escape_string($total).' ';
		return $text;
	}
	private function isDataSane(){
		$sanity = true;
		return $sanity;
	}
}
class genericTableWriter{
	// this class generates only E_USER_WARNINGS and E_USER_NOTICES it will not stop execution
	// but it will return an
	
	public $tableName;			// string, FROM database.table
	public $action;				// string, "update" or "insert"
	public $isSuccess;			// whether the action was successful
	public $errorText;			// string
	public $updateIfExists;		// bool, when inserting, update if row matching selectors is found
	public $insertIfNotFound; 	// bool, when updating, insert new row if none matching selectors is found
	public $updateLimit;		// int, usually 1, how many rows to update (max)
	public $fieldsToWrite; 		// assoc. array, [field,alias], SELECT field1,field2
	// selectors serve as row identifier for update actions
	// and serve as a unique key(s) for insert actions
	// insert will check if a row matching the selectors is found
	// if so, no insert is done (though an update may occur if flag above is set)
	public $selectors;			// assoc. array, [field,value], WHERE field=value AND ...
	public $customLogFields;	// assoc. array, [field,value], atypical fields for log table
	public $lastResult;		// handle to last return from mysql query
	public $lastInsertID;	// if table has an autoincrement column, this will be the value generated for the last inserted row
	private $doLogging;		// bool, should reads be logged?
	private $logTable;		// string, if not specified, use default table name for logging
	private $queryCtrl;		// a genericQueryControl object for executing query
	
	public function __construct($options = array()){
		$this->setDefaults();
		$this->setProperties($options);
		$queryOptions = array('doLogging' => $this->doLogging, 'logTable' => $this->logTable);
		$this->queryCtrl = new genericQueryControl($queryOptions);
	}
	public function writeRows(){
		$errorText = '';
		$this->errorText = '';
		$rowExists = $this->isRowExists();
		// if -1, an error occurred
		if ($rowExists != -1){		
			if ($this->action == 'insert'){
				if (!$rowExists){
					$errorText = $this->insert();
				}
				elseif ($this->updateIfExists){
					$errorText = $this->update();
				}
				else{
					$errorText = 'Insert failed. This row already exists.';
					$errorText .= ' Table = ' . $this->tableName . '.';
					$errorText .= ' Selectors = ' . print_r($this->selectors,true);
					trigger_error($errorText, E_USER_WARNING);
				}
			}
			if ($this->action == 'update'){
				if ($rowExists){
					$errorText = $this->update();
				}
				elseif ($this->insertIfNotFound){
					$mergeSelectorsAndFields = true;
					$errorText = $this->insert($mergeSelectorsAndFields);
				}
				else{
					$errorText = 'Update failed. No existing row found.';
					$errorText .= ' Table = ' . $this->tableName . '.';
					$errorText .= ' Selectors = ' . print_r($this->selectors,true);
					$errorText .= ' SQL = ' . $this->getRowSearchSQL();
					trigger_error($errorText, E_USER_WARNING);
				}
			}
		} else{
			// $rowExists is -1, an error occurred, we can get it from mysql_error()
			$errorText = 'Couldn\'t determine if row exists. '.mysql_error();
			$errorText .= ' Table = ' . $this->tableName . '.';
			$errorText .= ' Selectors = ' . print_r($this->selectors,true);
		}
		
		$this->errorText = $errorText;
	}
	public function loggingOff(){
		$this->doLogging = false;
		$this->queryCtrl->loggingOff();
	}
	public function loggingOn(){
		$this->doLogging = true;
		$this->queryCtrl->loggingOn();
	}
	public function setProperties($options = array()){
		// a single setter function for the class
		foreach ($options as $property => $value){
			if (property_exists($this, $property)){
				$this->$property = $value;
			}
		}
	}
	public function getProperty($property = null){
		if (property_exists($this, $property)){
			$value = $this->$property;
		} else{
			$value = null;
		}
		return $value;
	}
	public function setDefaults(){
		$this->tableName = '';
		$this->action = '';
		$this->updateIfExists = false;
		$this->insertIfNotFound = false;
		$this->updateLimit = 1;
		$this->fieldsToWrite = array();
		$this->selectors = array();
		$this->customLogFields = array();
		$this->doLogging = GDC_Log_Writes;
		$this->logTable = Default_SQLLog_Table;
		$this->lastInsertID = null;
	}

	private function isRowExists(){
		// look for row in database that matches selectors
		// returns 0, 1 (false, true), or -1 on error
		$rowExists = -1;
		// only bother if selectors have been specified
		if (count($this->selectors) > 0) {
			$sql = $this->getRowSearchSQL();
			// this call will never be logged
			$loggingState = $this->doLogging;
			$this->loggingOff();
			$sqlResult = $this->queryCtrl->queryDatabase($sql);	// query database
			// restore logging state
			if ($loggingState){ 
				$this->loggingOn();
			}
			
			if ($sqlResult){
				// query succeeded
				if (mysql_num_rows($sqlResult) > 0){
					$rowExists = 1;
				} else{
					$rowExists = 0;
				}
			} else{
				// query failed
				trigger_error("Couldn't determine if row exists.",E_USER_WARNING);
			}
		} else{
			// no selectors specified
			$rowExists = 0;
		}
		return $rowExists;
	}
	private function getRowSearchSQL(){
		$sql = 'SELECT * FROM `'.mysql_real_escape_string($this->tableName).'` WHERE 1=1';
		foreach ($this->selectors as $fieldName => $value){
			$sql .= ' AND ';
			$sql .= '`'.mysql_real_escape_string($fieldName).'`';
			if ($value !== null){
				$sql .= '="'.mysql_real_escape_string($value).'"';
			} else{
				$sql .= ' IS NULL';
			}
		}
		$sql .= ' LIMIT 1';
		return $sql;
	}
	private function insert($doMergeSelectorsAndFields = false){
		$errorText = '';
		$sqlStatement = $this->getInsertSQL($doMergeSelectorsAndFields);
		$customLogFields = $this->customLogFields;
		$sqlResult = $this->queryCtrl->queryDatabase($sqlStatement, $customLogFields);	// query database
		if (!$sqlResult){
			// error occurred
			$errorText = 'Failed to insert row. '.mysql_error();
			trigger_error('Failed to insert row.',E_USER_WARNING);
		}
		$this->lastResult = $sqlResult;
		if ($this->lastResult) { $this->lastInsertID = mysql_insert_id();}
		return $errorText;
	}
	private function update(){
		$errorText = '';
		$sqlStatement = $this->getUpdateSQL();
		$customLogFields = $this->customLogFields;
		$sqlResult = $this->queryCtrl->queryDatabase($sqlStatement, $customLogFields);	// query database
		if (!$sqlResult){
			// error occurred
			$errorText = 'Failed to update row(s). '.mysql_error();
			trigger_error('Failed to update row.',E_USER_WARNING);
		}
		$this->lastResult = $sqlResult;
		return $errorText;
	}
	
	private function getUpdateSQL(){
		// 'UPDATE `table` SET `field`="value",`field`="value" WHERE `field`="value" AND `field`="value" LIMIT 1'
		$sql = 'UPDATE `'.mysql_real_escape_string($this->tableName).'` ';
		$sql .= $this->getUpdateSETtext();
		$sql .= $this->getUpdateWHEREtext();
		$sql .= $this->getUpdateLIMITtext();
		return $sql;
	}
	private function getInsertSQL($doMergeSelectorsAndFields = false){
		// 'INSERT INTO `table` (`field`,`field`) VALUES ("value","value")'
		$sql = 'INSERT INTO `'.mysql_real_escape_string($this->tableName).'` ';
		$sql .= $this->getInsertFieldsText($doMergeSelectorsAndFields);
		$sql .= $this->getInsertValuesText($doMergeSelectorsAndFields);
		return $sql;
	}
	private function getUpdateSETtext(){
		// 'SET `field`="value",`field`="value" '
		$text = 'SET ';
		foreach ($this->fieldsToWrite as $fieldName => $value){
			$text .= '`'.mysql_real_escape_string($fieldName).'`=';
			if ($value !== null){
				$text .= '"'.mysql_real_escape_string($value).'",';
			} else{
				// if value is null, we don't want to enclose in ""
				$text .= 'NULL,';
			}
		}
		$text = $this->stripFinalCommaFromString($text);
		$text .= ' ';
		return $text;
	}
	private function getUpdateWHEREtext(){
		// "WHERE `field`="value" AND `field`="value" "
		$text = 'WHERE 1=1';
		foreach ($this->selectors as $fieldName => $value){
			$text .= ' AND ';
			$text .= '`'.mysql_real_escape_string($fieldName).'`';
			if ($value !== null){
				$text .= '="'.mysql_real_escape_string($value).'"';
			} else{
				// if null, have to test with IS NULL
				$text .= ' IS NULL';
			}
		}
		$text .= ' ';
		return $text;
	}
	private function getUpdateLIMITtext(){
		// 'LIMIT 1 ' or ''
		$text = '';
		$limit = 1;
		if (isset($this->updateLimit)){ $limit = $this->updateLimit;}
		if ($limit > 0){
			$text = 'LIMIT '.mysql_real_escape_string(strval($limit)).' ';
		}
		return $text;
	}
	private function getInsertFieldsText($doMergeSelectorsAndFields = false){
		if ($doMergeSelectorsAndFields){
			$fields = array_merge($this->fieldsToWrite, $this->selectors);
		} else{
			$fields = $this->fieldsToWrite;
		}
		// '(`field`,`field`) '
		$text = '(';
		foreach ($fields as $fieldName => $value){
			$text .= '`'.mysql_real_escape_string($fieldName).'`,';
		}
		$text = $this->stripFinalCommaFromString($text);
		$text .= ') ';
		return $text;
	}
	private function getInsertValuesText($doMergeSelectorsAndFields = false){
		if ($doMergeSelectorsAndFields){
			$fields = array_merge($this->fieldsToWrite, $this->selectors);
		} else{
			$fields = $this->fieldsToWrite;
		}
		// 'VALUES ("value","value") '
		$text = 'VALUES (';
		foreach ($fields as $fieldName => $value){
			if ($value !== null){
				$text .= '"'.mysql_real_escape_string($value).'",';
			} else{
				$text .= 'NULL,';
			}
		}
		$text = $this->stripFinalCommaFromString($text);
		$text .= ') ';
		return $text;
	}
	private function stripFinalCommaFromString($string){
		// remove final extra comma!
		$patternToFindComma = '/[,]$/';
		$replaceWith = '';
		$string = preg_replace($patternToFindComma, $replaceWith, $string);
		return $string;
	}
}
class genericTableEraser{
	public $errorText;			// string
	public $tableName;			// string, FROM database.table
	public $selectors;			// assoc. array, [field,value], WHERE field=value AND ...
	public $sortField;		// string, ORDER BY field, dir
	public $sortDirection;	// string, ASC or DSC, ORDER BY field, dir
	public $startIndex;		// integer, LIMIT start,length
	public $numberToDelete;	// integer, LIMIT start,length (use -1 for all rows)
	public $customLogFields;	// assoc. array, [field,value], atypical fields for log table
	public $lastResult;		// handle to last return from mysql query (true if success, false otherwise)
	public $numberOfDeletedRows;	// after query, stores mysql_affected_rows()
	private $doLogging;		// bool, should reads be logged?
	private $logTable;		// string, if not specified, use default table name for logging
	private $queryCtrl;		// a genericQueryControl object for executing query

	public function __construct($options = array()){
		$this->setDefaults();
		$this->setProperties($options);
		$queryOptions = array('doLogging' => $this->doLogging, 'logTable' => $this->logTable);
		$this->queryCtrl = new genericQueryControl($queryOptions);
	}
	public function deleteRows(){
		$this->lastResult = null;
		if ($this->isDataSane()){
			$sqlStatement = $this->getSQL();
			$customLogFields = $this->customLogFields;
			$sqlResult = $this->queryCtrl->queryDatabase($sqlStatement, $customLogFields);	// query database
			$this->lastResult = $sqlResult;
			$this->numberOfDeletedRows = null;
			if ($this->lastResult){
				$this->numberOfDeletedRows = mysql_affected_rows();
			}
		}
	}
	public function loggingOff(){
		$this->doLogging = false;
		$this->queryCtrl->loggingOff();
	}
	public function loggingOn(){
		$this->doLogging = true;
		$this->queryCtrl->loggingOn();
	}
	public function setProperties($options = array()){
		// a single setter function for the class
		foreach ($options as $property => $value){
			if (property_exists($this, $property)){
				$this->$property = $value;
			}
		}
	}
	public function getProperty($property = null){
		if (property_exists($this, $property)){
			$value = $this->$property;
		} else{
			$value = null;
		}
		return $value;
	}
	public function setDefaults(){
		$this->tableName = '';
		$this->selectors = array();
		$this->sortField = '';
		$this->sortDirection = '';
		$this->startIndex = 0;
		$this->rowsToDELETE = 1;
		$this->customLogFields = array();
		$this->doLogging = GDC_Log_Writes;
		$this->logTable = Default_SQLLog_Table;
	}
	private function getSQL(){
		$sql = '';
		$sql .= $this->getDELETEtext();
		$sql .= $this->getFROMtext();
		$sql .= $this->getWHEREtext();
		$sql .= $this->getHAVINGtext();		
		$sql .= $this->getORDERBYtext();
		$sql .= $this->getLIMITtext();
		return $sql;
	}
	private function getDELETEtext(){
		return 'DELETE ';
	}
	private function getFROMtext(){
		// "FROM `tablename` "
		$text = 'FROM ';
		$text .= '`' . mysql_real_escape_string($this->tableName) . '`';
		$text .= ' ';
		return $text;
	}
	private function getWHEREtext(){
		// "WHERE `field`="value" AND `field`="value" "
		$text = 'WHERE 1=1';
		foreach ($this->selectors as $fieldName => $value){
			$text .= ' AND ';
			$text .= '`'.mysql_real_escape_string($fieldName).'`';
			if ($value !== null){
				$text .= '="'.mysql_real_escape_string($value).'"';
			} else{
				$text .= ' IS NULL';
			}
		}
		$text .= ' ';
		return $text;
	}
	private function getHAVINGtext(){
		// not implemented yet
		return '';
	}
	private function getORDERBYtext(){
		// "ORDER BY `field` ASC "
		$text = '';
		if (isset($this->sortField) && $this->sortField != ''){
			$text .= 'ORDER BY `'.mysql_real_escape_string($this->sortField).'`';
			if (isset($this->sortDirection) && ($this-sortDirection == 'ASC' || $this->sortDirection == 'DESC')){
				$text .= ' '.$this->sortDirection;
			}
		}
		$text .= ' ';
		return $text;
	}
	private function getLIMITtext(){
		// "LIMIT #,# "
		// first # is index to start at, set of matched rows is zero-indexed, first row at 0
		// second # is total # or rows to affect (NOT final index)
		if (isset($this->startIndex)){
			$start = $this->startIndex;
		}
		if (isset($this->numberToDelete)){
			$total = $this->numberToDelete;
			if($total < 0){
				$total = 100; 	// mysql's dumb way of getting all rows
			}
		}
		// convert $start and $total to strings, controlling format to prevent commas and scientific notation (stupid php)
		$start = number_format($start, 0, '', '');
		$total = number_format($total, 0, '', '');
		
		$text = 'LIMIT '.mysql_real_escape_string($start).','.mysql_real_escape_string($total).' ';
		return $text;
	}
	private function isDataSane(){
		$sanity = true;
		return $sanity;
	}
}
?>