/lib/database/cfDatabaseWorker.class.php

<?php
/**
 -----------------------------------------------------------------------------
* DATA OBJECT WORKER CLASS
*
*
 -----------------------------------------------------------------------------
 -----------------------------------------------------------------------------
* @copyright (C) 2011 Cyberfox Software Solutions e.U.
* @license http://www.gnu.org/licenses/lgpl.html GNU Lesser General Public License version 3 (LGPLv3)
* @author Christian Graf <christian.graf@cyberfox.at>
 -----------------------------------------------------------------------------
 -----------------------------------------------------------------------------
* @package redfox
* @subpackage dataset
* @category data
 -----------------------------------------------------------------------------
 -----------------------------------------------------------------------------
* @version	$Id: cfDatabaseWorker.class.php 126 2012-10-15 13:58:44Z cgraf $
* @date	$Date: 2012-10-15 15:58:44 +0200 (Mo, 15 Okt 2012) $
* @svnauthor	$Author: cgraf $
 -----------------------------------------------------------------------------
*/

class cfDatabaseWorker implements cfIDataWorker
{
	const RETURN_ARRAY = 1;
	const RETURN_OBJECTLIST = 2;
	const RETURN_COLLECTION = 3;

	/**
	* put your comment there...
	*
	* @param cfIDataObject $DataObj
	* @param mixed $Filter
	* @param mixed $Options
	*/
	public static function Load(cfIDataObject &$DataObj, $Filter = null, array $Options = array())
	{
		if($DataObj instanceof cfIDataObject)
		{
			$mapping = cfObjectMapper::GetDatabaseMapping($DataObj);
			if($mapping !== false && !$mapping->IsEmpty())
			{
				$tablename = $mapping->GetTablename();
				$fields = $mapping->GetFields();
				$stmt = self::GetSelectStatement($tablename, $fields, $Filter, true, $Options);
				if($stmt->execute() === true)
				{
					if($stmt->rowCount() == 1)
					{
						$ret = $stmt->fetch(PDO::FETCH_ASSOC);
						if(empty($ret)) //No data found
							return false;
						else
							return self::FillObject($DataObj, $ret);
					}
					else
						return false; //Should never happenz: To many reults, only one row should be returned!
				}
				else
				{
					$err = $stmt->errorInfo();
					throw new cfDatabaseException('An error occured while executing a database statement => SQLSTATE: ' .$err[0] .', CODE: ' .$err[1] .', MESSAGE: ' .$err[2] ."\n" .print_r($stmt, true));
				}
			}
			else
				return false;
		}
		else
		{
			throw new cfArgumentException('Wrong object type for loading.');
		}

		return false;
	}

	public static function LoadByPrimaryKey(cfIDataObject &$DataObj, $PrimaryKeyValue, array $Options = array())
	{
		if($DataObj instanceof cfIDataObject)
		{
			$mapping = cfObjectMapper::GetDatabaseMapping($DataObj);
			if($mapping !== false && !$mapping->IsEmpty())
			{
				return self::Load($DataObj, array($mapping->GetPrimaryKeyName() => $PrimaryKeyValue), $Options);
			}
		}
		else
		{
			throw new cfArgumentException('Wrong object type for loading.');
		}

		return false;
	}

	public static function LoadBySQL(cfIDataObject &$DataObj, $SQL, array $Options = array())
	{
		if($DataObj instanceof cfIDataObject)
		{
			$mapping = cfObjectMapper::GetDatabaseMapping($DataObj);
			if($mapping !== false && !$mapping->IsEmpty())
			{
				$stmt = cfDatabase::Prepare($SQL);
				if($stmt->execute() === true)
				{
					if($stmt->rowCount() == 1)
					{
						$ret = $stmt->fetch(PDO::FETCH_ASSOC);
						if(empty($ret)) //No data found
							return false;
						else
							return self::FillObject($DataObj, $ret);
					}
					else
						return false; //Should never happenz: To many reults, only one row should be returned!
				}
				else
				{
					$err = $stmt->errorInfo();
					throw new cfDatabaseException('An error occured while executing a database statement => SQLSTATE: ' .$err[0] .', CODE: ' .$err[1] .', MESSAGE: ' .$err[2] ."\n" .print_r($stmt, true));
				}
			}
			else
				return false;
		}
		else
		{
			throw new cfArgumentException('Wrong object type for loading.');
		}

		return false;
	}

	public static function GetTablename(cfIDataObject &$DataObj)
	{
		if($DataObj instanceof cfIDataObject)
		{
			$mapping = cfObjectMapper::GetDatabaseMapping($DataObj);
			if($mapping !== false && !$mapping->IsEmpty())
			{
				return $mapping->GetTablename();
			}
		}
		else
		{
			throw new cfArgumentException('Wrong object type for loading.');
		}

		return false;
	}

	public static function GetPrimaryKeyName(cfIDataObject &$DataObj)
	{
		if($DataObj instanceof cfIDataObject)
		{
			$mapping = cfObjectMapper::GetDatabaseMapping($DataObj);
			if($mapping !== false && !$mapping->IsEmpty())
			{
				return $mapping->GetPrimaryKeyName();
			}
		}
		else
		{
			throw new cfArgumentException('Wrong object type for loading.');
		}

		return false;
	}

	/**
	* put your comment there...
	*
	* @param cfIDataObject $DataObj
	* @param mixed $Data
	*/
	private static function FillObject(cfIDataObject &$DataObj, &$Data)
	{
		if($DataObj instanceof cfIDataObject)
		{
			$mapping = cfObjectMapper::GetDatabaseMapping($DataObj);
			if($mapping !== false && !$mapping->IsEmpty())
			{
				$properties = $mapping->GetProperties();
				if($mapping->IsNamebased()) //no mapping, property names and db column names are the same
				{
					foreach($Data as $fieldname => $value)
					{
						$call = '$DataObj->';
						$set = $mapping->GetSetMethodForProperty($fieldname);
						if(!empty($set))
							$call .= $set .'(' .($value !== null ? cfFilter::QuoteStr($value, true) : 'null') .')';
						else
							$call .= $fieldname .' = ' .($value !== null ? cfFilter::QuoteStr($value, true) : 'null');

						if(eval($call .';') === false)
						{
							throw new cfApplicationException('One or more values of the given object "' .get_class($DataObj) .'" could not be updated.');
						}
					}
					return true;
				}
				else
				{
					throw new cfNotImplementedException('Sorry, in this version an data object can only be filled with the option "namebased"!');
				}
			}
			return false;
		}
	}

	/**
	* put your comment there...
	*
	* @param mixed $Tablename
	* @param mixed $Fields
	* @param mixed $Filter
	* @param mixed $Limit
	* @param mixed $Options
	*/
	private static function &GetSelectStatement($Tablename, $Fields, $Filter = null, $Limit = false, $Options = array())
	{
		$select = $where = $params = array();
		$whereSeperator = isset($Options['where_seperator']) && !empty($Options['where_seperator']) ? trim($Options['where_seperator']) : 'AND';
		$useLike = $useWildcard_right = $useWildcard_left = false;

		if(isset($Options['use_like_wildcards']) && $Options['use_like_wildcards'] === true)
		{
			$useLike = true;
			$useWildcard_right = true;
			$useWildcard_left = true;
		}
		else if(isset($Options['use_like_wildcard_right']) && $Options['use_like_wildcard_right'] === true)
		{
			$useLike = true;
			$useWildcard_right = true;
		}
		else if(isset($Options['use_like_wildcard_left']) && $Options['use_like_wildcard_left'] === true)
		{
			$useLike = true;
			$useWildcard_left = true;
		}
		else if(isset($Options['use_like']) && $Options['use_like'] === true)
		{
			$useLike = true;
		}

		//WHERE cases
		if(!empty($Filter) && is_array($Filter))
		{
			foreach($Filter as $searchField => $searchValue)
			{
				if(isset($Fields[$searchField]))
				{
					$nullable = isset($Fields[$searchField]['nullable']) && $Fields[$searchField]['nullable'] === true ? true : false;
					$type = isset($Fields[$searchField]['type']) && !empty($Fields[$searchField]['type']) ? $Fields[$searchField]['type'] : PDO::PARAM_STR;
					$length = isset($Fields[$searchField]['length']) && !empty($Fields[$searchField]['length']) ? $Fields[$searchField]['length'] : null;

					if(is_null($searchValue) && $nullable === true)
					{
						$where[] = cfDatabase::QuoteIdentifier($searchField) .' IS NULL';
					}
					else if(is_array($searchValue))
					{
						$tmp = array();
						foreach($searchValue as $i => $value)
						{
							$tmp[] = ':' .$searchField .$i;
							$params[$searchField .$i] = array('value' => $value, 'type' => $type, 'length' => $length);
						}
						$where[] = cfDatabase::QuoteIdentifier($searchField) .' IN (' .implode(',', $tmp) .')';
					}
					else
					{
						if($useLike)
						{
							$tmp = $useWildcard_left ? '%' : '';
							$tmp .= $searchValue;
							$tmp = $useWildcard_right ? '%' : '';
							$where[] = cfDatabase::QuoteIdentifier($searchField) .' LIKE :' .$searchField;
							$params[$searchField] = array('value' => $searchValue, 'type' => PDO::PARAM_STR, 'length' => $length);
						}
						else
						{
							$where[] = cfDatabase::QuoteIdentifier($searchField) .' = :' .$searchField;
							$params[$searchField] = array('value' => $searchValue, 'type' => $type, 'length' => $length);
						}
					}
				}
			}
		}
		else if(!empty($Filter) && !is_object($Filter))
		{
			$where[] = trim($Filter);
		}

		$query = new cfSimpleSqlQuery(cfSimpleSqlQuery::SELECT);
		$query->SetTable(cfDatabase::QuoteIdentifier($Tablename));
		//COLUMNS
		foreach($Fields as $fieldname => $settings)
		{
			$query->AddColumn(cfDatabase::QuoteIdentifier($fieldname));
		}

		//WHERE
		if(!empty($where))
			$query->SetWhere(implode(" $whereSeperator ", $where));

		//ORDER BY
		if(isset($Options['orderby']) && is_array($Options['orderby']) && !empty($Options['orderby']))
			$query->SetOrderBy(implode(', ', $Options['orderby']));

		//LIMIT
		if($Limit === true)
			$query->SetLimit('1');
		else
		{
			if(cfDatabase::GetDBH()->getAttribute(PDO::ATTR_DRIVER_NAME)  == 'mysql')
			{
				if(isset($Options['limit']) && is_numeric($Options['limit']) && $Options['limit'] > 0)
					$query->SetLimit('0, ' .intval($Options['limit']));
			}
		}

		$stmt = null;
		$stmt = cfDatabase::Prepare($query->Build());

		if(!empty($params))
		{
			$referenceValues = array();
			foreach($params as $paramName => $param) //Needed for reference to bind parameter
				$referenceValues[$paramName] = isset($param['value']) ? $param['value'] : null;
			foreach($referenceValues as $paramName => &$v)
			{
				$t = isset($params[$paramName]['type']) ? $params[$paramName]['type'] : PDO::PARAM_STR;
				$l = isset($params[$paramName]['length']) ? $params[$paramName]['length'] : null;
				$stmt->bindParam($paramName, $v, $t);
			}
		}

		return $stmt;
	}

	public static function Save(cfIDataObject &$DataObj, array $Options = array())
	{
		if($DataObj instanceof cfIDataObject)
		{
			$mapping = cfObjectMapper::GetDatabaseMapping($DataObj);
			if($mapping !== false && !$mapping->IsEmpty())
			{
				$tablename = $mapping->GetTablename();
				$fields = $mapping->GetFields();
				$properties = $mapping->GetProperties();
				$namebased = $mapping->IsNamebased();

				$primaryKey = $mapping->GetPrimaryKeyName();
				if($primaryKey === false)
					throw new cfDatabaseException('Could not save the data object, no primary key is defined.');

				$values = self::GetValues($DataObj);
				$qt = !isset($values[$primaryKey]['value']) || empty($values[$primaryKey]['value']) ? cfSimpleSqlQuery::INSERT : cfSimpleSqlQuery::UPDATE;
				$query = new cfSimpleSqlQuery($qt);
				if($qt == cfSimpleSqlQuery::INSERT && isset($Options['delayed']) && $Options['delayed'] === true)
					$query->DelayedInsert(true);

				$query->SetTable(cfDatabase::QuoteIdentifier($tablename));

				//COLUMNS
				foreach($fields as $fieldname => $settings)
				{
					if($fieldname != $primaryKey)
					{
						$query->AddColumn(cfDatabase::QuoteIdentifier($fieldname));
						$query->AddValue(':' .$fieldname);
					}
				}
				if($qt == cfSimpleSqlQuery::UPDATE)
					$query->SetWhere(cfDatabase::QuoteIdentifier($primaryKey) .' = :' .$primaryKey);

				$stmt = null;
				$stmt = cfDatabase::Prepare($query->Build());

				//VALUES
				$referenceValues = array();
				foreach($values as $fieldname => $value) //Needed for reference to bind parameter
					$referenceValues[$fieldname] = isset($value['value']) ? $value['value'] : null;
				foreach($referenceValues as $fieldname => &$v)
				{
					if($fieldname != $primaryKey || $qt == cfSimpleSqlQuery::UPDATE)
					{
						$t = isset($values[$fieldname]['type']) ? $values[$fieldname]['type'] : PDO::PARAM_STR;
						$l = isset($values[$fieldname]['length']) ? $values[$fieldname]['length'] : null;
						if(!$stmt->bindParam($fieldname, $v, $t))
							cfLogger::Log()->Error('Value for "' .$fieldname .'" could not be bind to param. (Type: ' .$t .', Value: ' .print_r($v, true) .')');
					}
				}

				try
				{
					if($stmt->execute() === false)
					{
						$err = $stmt->errorInfo();
						throw new cfDatabaseException('An error occured while executing a database statement => SQLSTATE: '
							.$err[0] .', CODE: ' .$err[1] .', MESSAGE: ' .$err[2] ."\n" .print_r($stmt, true)
							."\n VALUES: " .print_r($referenceValues, true)
						);
					}
					if($qt == cfSimpleSqlQuery::INSERT)
					{
						$newID = cfDatabase::LastInsertId();
						$call = '$DataObj->';
						$set = $mapping->GetSetMethodForProperty($primaryKey);
						if(!empty($set))
							$call .= $set .'(' .cfDatabase::Quote($newID) .')';
						else
							$call .= $primaryKey .' = ' .cfDatabase::Quote($newID);

						if(eval($call .';') === false)
							throw new cfApplicationException('One or more values of the given object "' .get_class($DataObj) .'" could not be updated.');
					}
				}
				catch(Exception $ups)
				{
					throw new cfDatabaseException('An error occured while saving the object => ' .$ups->getMessage() ."\n" .print_r($stmt, true)
						//."\n" .print_r($stmt->debugDumpParams(), true) //@Todo: Catch direct output form this method and put it into a variable
						."\n VALUES: " .print_r($referenceValues, true)
					);
				}

				return true;
			}
			else
				return false;
		}
		else
		{
			throw new cfArgumentException('Wrong object type for saving.');
		}

		return false;
	}

	/**
	* put your comment there...
	*
	* @param mixed $Properties
	*/
	private static function GetValues(cfIDataObject &$DataObj)
	{
		$mapping = cfObjectMapper::GetDatabaseMapping($DataObj);
		if($mapping !== false && !$mapping->IsEmpty())
		{
			$fields = $mapping->GetFields();
			$properties = $mapping->GetProperties();

			$values = array();
			if($mapping->IsNamebased())
			{
				foreach($fields as $fieldname => $settings)
				{
					$v = null;
					$call = '$v = $DataObj->';
					$get = $mapping->GetGetMethodForProperty($fieldname);
					if(!empty($get))
						$call .= $get .'()';
					else
						$call .= $fieldname;

					eval($call .';');
					//echo $fieldname .' = ' .(is_null($v) ? ' NULL ' : ($v === 0 || $v === '0' ? ' 0 ' : (empty($v) ? ' EMPTY ' : ' OK '))) .' => ' .$v .'<br>';
					$t = isset($settings['type']) ? $settings['type'] : PDO::PARAM_STR;
					$l = isset($settings['length']) ? $settings['length'] : null;
					$n = isset($settings['nullable']) && $settings['nullable'] == true ? true : false;

					if(empty($v) && $v !== 0 && $v !== '0' && $n)
					{
						$v = null;
						$t = PDO::PARAM_NULL;
					}
					else if(is_null($v))
					{
						$v = null;
						$t = PDO::PARAM_NULL;
					}

					$values[$fieldname] = array('value' => $v, 'type' => $t, 'length' => $l, 'nullable' => $n);
				}
				return $values;
			}
			else
				return false; //NOT IMPLEMENTED YET
		}
		else
			return false;
	}

	/**
	* put your comment there...
	*
	* @param cfIDataObject $DataObj
	* @param mixed $Options
	*/
	public static function Delete(cfIDataObject &$DataObj, array $Options = array())
	{
		if($DataObj instanceof cfIDataObject)
		{
			$mapping = cfObjectMapper::GetDatabaseMapping($DataObj);
			if($mapping !== false && !$mapping->IsEmpty())
			{
				$tablename = $mapping->GetTablename();
				$fields = $mapping->GetFields();
				$properties = $mapping->GetProperties();

				$primaryKey = $mapping->GetPrimaryKeyName();
				if($primaryKey === false)
					throw new cfDatabaseException('Could not delete the data object, no primary key is defined.');

				$values = self::GetValues($DataObj);
				if(empty($values[$primaryKey]))
					throw new cfDatabaseException('Could not delete the data object, no primary key is defined.');

				$query = new cfSimpleSqlQuery(cfSimpleSqlQuery::DELETE);
				$query->SetTable(cfDatabase::QuoteIdentifier($tablename));
				$query->SetWhere(cfDatabase::QuoteIdentifier($primaryKey) .' = :' .$primaryKey);

				$stmt = null;
				$stmt = cfDatabase::Prepare($query->Build());

				//VALUES
				$stmt->bindParam($primaryKey, $values[$primaryKey]['value'], $values[$primaryKey]['type']);

				try
				{
					//print_r($stmt);
					if($stmt->execute() === false)
					{
						$err = $stmt->errorInfo();
						throw new cfDatabaseException('An error occured while executing a database statement => SQLSTATE: ' .$err[0] .', CODE: ' .$err[1] .', MESSAGE: ' .$err[2]."\n".print_r($stmt,true));
					}

					/*
					foreach($fields as $fieldname => $settings)
					{
						$call = '$DataObj->';
						$set = $mapping->GetSetMethodForProperty($fieldname);
						if(!empty($set))
							$call .= $set .'(null)';
						else
							$call .= $fieldname .' = null';
						if(eval($call .';') === false)
						{
							throw new cfApplicationException('One or more values of the given object "' .get_class($DataObj) .'" could not be updated.');
						}
					}
					*/
				}
				catch(Exception $ups)
				{
					throw new cfDatabaseException('An error occured while deleting the object => ' .$ups->getMessage()."\n".print_r($stmt,true));
				}

				return true;
			}
			else
				return false;
		}
		else
		{
			throw new cfArgumentException('Wrong object type for saving.');
		}

		return false;
	}

	/**
	* put your comment there...
	*
	* @param cfIDataObject $DataObj
	* @param mixed $Filter
	* @param mixed $Options
	*/
	public static function LoadAndGetAll($DataObjOrString, $Filter = null, $ReturnType = self::RETURN_OBJECTLIST,  array $Options = array())
	{
		if(empty($DataObjOrString))
			return false;

		$obj = null;
		$classname = '';
		if(is_object($DataObjOrString))
		{
			$obj = $DataObjOrString;
			$classname =  get_class($obj);
		}
		else
		{
			$obj = cfHelper::GetObjectFromString($DataObjOrString);
			$classname = $DataObjOrString;
		}

		if($obj instanceof cfIDataObject)
		{
			$mapping = cfObjectMapper::GetDatabaseMapping($obj);
			if($mapping !== false && !$mapping->IsEmpty())
			{
				$tablename = $mapping->GetTablename();
				$fields = $mapping->GetFields();
				$stmt = self::GetSelectStatement($tablename, $fields, $Filter, false, $Options);
				if($stmt->execute() === true)
				{
					$primaryKeyName = $mapping->GetPrimaryKeyName();
					$retData = array();
					for($i = 0; $ret = $stmt->fetch(PDO::FETCH_ASSOC); $i++)
					{
						switch($ReturnType)
						{
							case self::RETURN_ARRAY:
								{
									//$retData[$ret[$primaryKeyName]] = $ret;
									$retData[] = $ret;
								}
								break;
							case self::RETURN_OBJECTLIST:
							default:
								{
									$tmpObj = cfHelper::GetObjectFromString($classname);
									if(self::FillObject($tmpObj, $ret))
									{
										if(isset($ret[$primaryKeyName]))
											$rowIdx = $ret[$primaryKeyName];
										else
											$rowIdx = '_' .$i;
										$retData[$rowIdx] = $tmpObj;
									}
								}
								break;
						}
					}

					return $retData;
				}
				else
				{
					$err = $stmt->errorInfo();
					throw new cfDatabaseException('An error occured while executing a database statement => SQLSTATE: ' .$err[0] .', CODE: ' .$err[1] .', MESSAGE: ' .$err[2] ."\n" .print_r($stmt, true));
				}
			}
			else
				return false;
		}
		else
		{
			throw new cfArgumentException('Wrong object type for loading.');
		}

		return false;
	}

}

?>