Location: PHPKode > projects > Alloy PHP Framework > lib/Spot/Adapter/PDO/Abstract.php
<?php
namespace Spot\Adapter;

/**
 * Base PDO adapter
 *
 * @package Spot
 * @link http://spot.os.ly
 */
abstract class PDO_Abstract extends AdapterAbstract implements AdapterInterface
{
	protected $_database;
	
	
	/**
	 * Get database connection
	 * 
	 * @return object PDO
	 */
	public function connection()
	{
		if(!$this->_connection) {
			if($this->_dsn instanceof \PDO) {
				$this->_connection = $this->_dsn;
			} else {
				$dsnp = $this->parseDSN($this->_dsn);
				$this->_database = $dsnp['database'];
				
				// Establish connection
				try {
					$dsn = $dsnp['adapter'].':host='.$dsnp['hostspec'].';dbname='.$dsnp['database'];
					$this->_connection = new \PDO($dsn, $dsnp['username'], $dsnp['password'], $this->_options);
					// Throw exceptions by default
					$this->_connection->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
				} catch(Exception $e) {
					throw new \Spot\Exception($e->getMessage());
				}
			}
		}
		return $this->_connection;
	}
	
	
	/**
	 * Escape/quote direct user input
	 *
	 * @param string $string
	 */
	public function escape($string)
	{
		return $this->connection()->quote($string);
	}
	
	
	/**
	 * Migrate table structure changes to database
	 * @param String $table Table name
	 * @param Array $fields Fields and their attributes as defined in the mapper
	 */
	public function migrate($table, array $fields)
	{
		// Get current fields for table
		$tableExists = false;
		$tableColumns = $this->getColumnsForTable($table, $this->_database);
		
		if($tableColumns) {
			$tableExists = true;
		}
		if($tableExists) {
			// Update table
			$this->migrateTableUpdate($table, $fields);
		} else {
			// Create table
			$this->migrateTableCreate($table, $fields);
		}
	}
	
	
	/**
	 * Execute a CREATE TABLE command
	 */
	public function migrateTableCreate($table, array $formattedFields)
	{
		/*
			STEPS:
			* Use fields to get column syntax
			* Use column syntax array to get table syntax
			* Run SQL
		*/
		
		// Prepare fields and get syntax for each
		$columnsSyntax = array();
		foreach($formattedFields as $fieldName => $fieldInfo) {
			$columnsSyntax[$fieldName] = $this->migrateSyntaxFieldCreate($fieldName, $fieldInfo);
		}
		
		// Get syntax for table with fields/columns
		$sql = $this->migrateSyntaxTableCreate($table, $formattedFields, $columnsSyntax);
		
		// Add query to log
		\Spot\Log::addQuery($this, $sql);
		
		$this->connection()->exec($sql);
		return true;
	}
	
	
	/**
	 * Execute an ALTER/UPDATE TABLE command
	 */
	public function migrateTableUpdate($table, array $formattedFields)
	{
		/*
			STEPS:
			* Use fields to get column syntax
			* Use column syntax array to get table syntax
			* Run SQL
		*/
		
		// Prepare fields and get syntax for each
		$tableColumns = $this->getColumnsForTable($table, $this->_database);
		$updateFormattedFields = array();
		foreach($tableColumns as $fieldName => $columnInfo) {
			if(isset($formattedFields[$fieldName])) {
				// TODO: Need to do a more exact comparison and make this non-mysql specific
				if ( 
						$this->_fieldTypeMap[$formattedFields[$fieldName]['type']] != $columnInfo['DATA_TYPE'] ||
						$formattedFields[$fieldName]['default'] !== $columnInfo['COLUMN_DEFAULT']
					) {
					$updateFormattedFields[$fieldName] = $formattedFields[$fieldName];
				}
				
				unset($formattedFields[$fieldName]);
			}
		}
		
		$columnsSyntax = array();
		// Update fields whose options have changed
		foreach($updateFormattedFields as $fieldName => $fieldInfo) {
			$columnsSyntax[$fieldName] = $this->migrateSyntaxFieldUpdate($fieldName, $fieldInfo, false);
		}
		// Add fields that are missing from current ones
		foreach($formattedFields as $fieldName => $fieldInfo) {
			$columnsSyntax[$fieldName] = $this->migrateSyntaxFieldUpdate($fieldName, $fieldInfo, true);
		}
		
		// Get syntax for table with fields/columns
		if ( !empty($columnsSyntax) ) {
			$sql = $this->migrateSyntaxTableUpdate($table, $formattedFields, $columnsSyntax);
			
			// Add query to log
			\Spot\Log::addQuery($this, $sql);
			
			try {
				// Run SQL
				$this->connection()->exec($sql);
			} catch(\PDOException $e) {
				// Table does not exist
				if($e->getCode() == "42S02") {
					throw new \Spot\Exception_Datasource_Missing("Table '" . $table . "' does not exist");
				}
				return false;
			}
		}
		return true;
	}
	
	
	/**
	 * Prepare an SQL statement 
	 */
	public function prepare($sql)
	{
		return $this->connection()->prepare($sql);
	}
    
    
    /**
	 * Find records with custom SQL query
	 *
	 * @param string $sql SQL query to execute
	 * @param array $binds Array of bound parameters to use as values for query
	 * @throws \Spot\Exception
	 */
	public function query($sql, array $binds = array())
	{
		// Add query to log
		\Spot\Log::addQuery($this, $sql, $binds);
		
		// Prepare and execute query
		if($stmt = $this->connection()->prepare($sql)) {
			$results = $stmt->execute($binds);
			if($results) {
				$r = $stmt;
			} else {
				$r = false;
			}
			return $r;
		} else {
			throw new \Spot\Exception(__METHOD__ . " Error: Unable to execute SQL query - failed to create prepared statement from given SQL");
		}
	}
    
	
	/**
	 * Create new row object with set properties
	 */
	public function create($datasource, array $data, array $options = array())
	{
		$binds = $this->statementBinds($data);
		// build the statement
		$sql = "INSERT INTO " . $datasource .
			" (" . implode(', ', array_keys($data)) . ")" .
			" VALUES(:" . implode(', :', array_keys($binds)) . ")";
		
		// Add query to log
		\Spot\Log::addQuery($this, $sql, $binds);
		
		try {
			// Prepare update query
			$stmt = $this->connection()->prepare($sql);
			
			if($stmt) {
				// Execute
				if($stmt->execute($binds)) {
					$result = $this->connection()->lastInsertId();
				} else {
					$result = false;
				}
			} else {
				$result = false;
			}
		} catch(\PDOException $e) {
			// Table does not exist
			if($e->getCode() == "42S02") {
				throw new \Spot\Exception_Datasource_Missing("Table or datasource '" . $datasource . "' does not exist");
			}
			return false;
		}
		
		return $result;
	}
	
	
	/**
	 * Build a select statement in SQL
	 * Can be overridden by adapters for custom syntax
	 *
	 * @todo Add support for JOINs
	 */
	public function read(\Spot\Query $query, array $options = array())
	{
		$conditions = $this->statementConditions($query->conditions);
		$binds = $this->statementBinds($query->params());
		$order = array();
		if($query->order) {
			foreach($query->order as $oField => $oSort) {
				$order[] = $oField . " " . $oSort;
			}
		}
		
		$sql = "
			SELECT " . $this->statementFields($query->fields) . "
			FROM " . $query->datasource . "
			" . ($conditions ? 'WHERE ' . $conditions : '') . "
			" . ($query->group ? 'GROUP BY ' . implode(', ', $query->group) : '') . "
			" . ($order ? 'ORDER BY ' . implode(', ', $order) : '') . "
			" . ($query->limit ? 'LIMIT ' . $query->limit : '') . " " . ($query->limit && $query->offset ? 'OFFSET ' . $query->offset: '') . "
			";
		
		// Unset any NULL values in binds (compared as "IS NULL" and "IS NOT NULL" in SQL instead)
		if($binds && count($binds) > 0) {
			foreach($binds as $field => $value) {
				if(null === $value) {
					unset($binds[$field]);
				}
			}
		}
		
		// Add query to log
		\Spot\Log::addQuery($this, $sql, $binds);
		
		try {
			// Prepare update query
			$stmt = $this->connection()->prepare($sql);
			
			if($stmt) {
				// Execute
				if($stmt->execute($binds)) {
					$result = $this->toCollection($query, $stmt);
				} else {
					$result = false;
				}
			} else {
				$result = false;
			}
		} catch(PDOException $e) {
			// Table does not exist
			if($e->getCode() == "42S02") {
				throw new \Spot\Exception_Datasource_Missing("Table or datasource '" . $query->datasource . "' does not exist");
			}
			return false;
		}
		
		return $result;
	}
	
	/**
	 * Update entity
	 */
	public function update($datasource, array $data, array $where = array(), array $options = array())
	{
		$dataBinds = $this->statementBinds($data, 0);
        $whereBinds = $this->statementBinds($where, count($dataBinds));
        $binds = array_merge($dataBinds, $whereBinds);
        
        $placeholders = array();
        $dataFields = array_combine(array_keys($data), array_keys($dataBinds));
        // Placeholders and passed data
		foreach($dataFields as $field => $bindField) {
            $placeholders[] = $field . " = :" . $bindField . "";
		}
        
        $conditions = $this->statementConditions($where, count($dataBinds));
		
		// Ensure there are actually updated values on THIS table
		if(count($binds) > 0) {
			// Build the query
			$sql = "UPDATE " . $datasource .
				" SET " . implode(', ', $placeholders) .
				" WHERE " . $conditions;
			
			// Add query to log
			\Spot\Log::addQuery($this, $sql, $binds);
            
			try {
				// Prepare update query
				$stmt = $this->connection()->prepare($sql);
				
				if($stmt) {
					// Execute
					if($stmt->execute($binds)) {
						$result = true;
					} else {
						$result = false;
					}
				} else {
					$result = false;
				}
			} catch(\PDOException $e) {
				// Table does not exist
				if($e->getCode() == "42S02") {
					throw new \Spot\Exception_Datasource_Missing("Table or datasource '" . $datasource . "' does not exist");
				}
				return false;
			}
		} else {
			$result = false;
		}
		
		return $result;
	}
	
	
	/**
	 * Delete entities matching given conditions
	 *
	 * @param string $source Name of data source
	 * @param array $conditions Array of conditions in column => value pairs
	 */
	public function delete($datasource, array $data, array $options = array())
	{
		$binds = $this->statementBinds($data, 0);
		$conditions = $this->statementConditions($data);
		
		$sql = "DELETE FROM " . $datasource . "";
		$sql .= ($conditions ? ' WHERE ' . $conditions : '');
		
		// Add query to log
		\Spot\Log::addQuery($this, $sql, $binds);
		try {
			$stmt = $this->connection()->prepare($sql);
			if($stmt) {
				// Execute
				if($stmt->execute($binds)) {
					$result = true;
				} else {
					$result = false;
				}
			} else {
				$result = false;
			}
			return $result;
		} catch(\PDOException $e) {
			// Table does not exist
			if($e->getCode() == "42S02") {
				throw new \Spot\Exception_Datasource_Missing("Table or datasource '" . $datasource . "' does not exist");
			}
			return false;
		}
	}
	
	
	/**
	 * Truncate a database table
	 * Should delete all rows and reset serial/auto_increment keys to 0
	 */
	public function truncateDatasource($datasource) {
		$sql = "TRUNCATE TABLE " . $datasource;
		
		// Add query to log
		\Spot\Log::addQuery($this, $sql);
		
		try {
			return $this->connection()->exec($sql);
		} catch(\PDOException $e) {
			// Table does not exist
			if($e->getCode() == "42S02") {
				throw new \Spot\Exception_Datasource_Missing("Table or datasource '" . $datasource . "' does not exist");
			}
			return false;
		}
	}
	
	
	/**
	 * Drop a database table
	 * Destructive and dangerous - drops entire table and all data
	 */
	public function dropDatasource($datasource) {
		$sql = "DROP TABLE " . $datasource;
		
		// Add query to log
		\Spot\Log::addQuery($this, $sql);
		
		try {
			return $this->connection()->exec($sql);
		} catch(\PDOException $e) {
			// Table does not exist
			if($e->getCode() == "42S02") {
				throw new \Spot\Exception_Datasource_Missing("Table or datasource '" . $datasource . "' does not exist");
			}
			return false;
		}
	}
	
	
	/**
	 * Create a database
 	 * Will throw errors if user does not have proper permissions
	 */
	public function createDatabase($database) {
		$sql = "CREATE DATABASE " . $database;
		
		// Add query to log
		\Spot\Log::addQuery($this, $sql);
		
		return $this->connection()->exec($sql);
	}
	
	
	/**
	 * Drop a database table
	 * Destructive and dangerous - drops entire table and all data
	 * Will throw errors if user does not have proper permissions
	 */
	public function dropDatabase($database) {
		$sql = "DROP DATABASE " . $database;
		
		// Add query to log
		\Spot\Log::addQuery($this, $sql);
		
		return $this->connection()->exec($sql);
	}
	
	
	/**
	 * Return fields as a string for a query statement
	 */
	public function statementFields(array $fields = array())
	{
		return count($fields) > 0 ? implode(', ', $fields) : "*";
	}
	
	
	/**
	 * Builds an SQL string given conditions
	 */
	public function statementConditions(array $conditions = array(), $ci = 0)
	{
		if(count($conditions) == 0) { return; }
		
		$sqlStatement = "(";
		$defaultColOperators = array(0 => '', 1 => '=');
		$loopOnce = false;
		foreach($conditions as $condition) {
			if(is_array($condition) && isset($condition['conditions'])) {
				$subConditions = $condition['conditions'];
			} else {
				$subConditions = $conditions;
				$loopOnce = true;
			}
			$sqlWhere = array();
			foreach($subConditions as $column => $value) {
				$whereClause = '';
				
				// Column name with comparison operator
				$colData = explode(' ', $column);
				$operator = isset($colData[1]) ? $colData[1] : '=';
				if(count($colData) > 2) {
					$operator = array_pop($colData);
					$colData = array( implode(' ', $colData), $operator);
				}
				$col = $colData[0];
				
				// Determine which operator to use based on custom and standard syntax
				switch($operator) {
					case '<':
					case ':lt':
						$operator = '<';
					break;
					case '<=':
					case ':lte':
						$operator = '<=';
					break;
					case '>':
					case ':gt':
						$operator = '>';
					break;
					case '>=':
					case ':gte':
						$operator = '>=';
					break;
					// ALL - Find ALL values in a set - Kind of like IN(), but seeking *all* the values
					case ':all':
						throw new \Spot\Exception("SQL adapters do not currently support the ':all' operator");
					break;
					// Not equal
					case '<>':
					case '!=':
					case ':ne':
					case ':not':
						$operator = '!=';
						if(is_array($value)) {
							$operator = "NOT IN";
						} elseif(is_null($value)) {
							$operator = "IS NOT NULL";
						}
					break;
					// Equals
					case '=':
					case ':eq':
					default:
						$operator = '=';
						if(is_array($value)) {
							$operator = "IN";
						} elseif(is_null($value)) {
							$operator = "IS NULL";
						}
					break;
				}
				
				// If WHERE clause not already set by the code above...
				if(is_array($value)) {
					$valueIn = "";
					foreach($value as $val) {
						$valueIn .= $this->escape($val) . ",";
					}
					$value = "(" . trim($valueIn, ',') . ")";
					$whereClause = $col . " " . $operator . " " . $value;
				} elseif(is_null($value)) {
					$whereClause = $col . " " . $operator;
				}
				
				if(empty($whereClause)) {
					// Add to binds array and add to WHERE clause
					$colParam = preg_replace('/\W+/', '_', $col) . $ci;
					$sqlWhere[] = $col . " " . $operator . " :" . $colParam . "";
				} else {
					$sqlWhere[] = $whereClause;
				}
				
				// Increment ensures column name distinction
				$ci++;
			}
			if ( $sqlStatement != "(" ) {
				$sqlStatement .= " " . (isset($condition['setType']) ? $condition['setType'] : 'AND') . " (";
			}
			$sqlStatement .= implode(" " . (isset($condition['type']) ? $condition['type'] : 'AND') . " ", $sqlWhere );
			$sqlStatement .= ")";
			if($loopOnce) { break; }
		}
		
		// Ensure we actually had conditions
		if(0 == $ci) {
            $sqlStatement = '';
		}
		
		return $sqlStatement;
	}
	
	
	/**
	 * Returns array of binds to pass to query function
	 */
	public function statementBinds(array $conditions = array(), $ci = false)
	{
		if(count($conditions) == 0) { return; }
		
		$binds = array();
		$loopOnce = false;
		foreach($conditions as $condition) {
			if(is_array($condition) && isset($condition['conditions'])) {
				$subConditions = $condition['conditions'];
			} else {
				$subConditions = $conditions;
				$loopOnce = true;
			}
			foreach($subConditions as $column => $value) {
				
				$bindValue = false;
				
				// Handle binding depending on type
				if(is_object($value)) {
					if($value instanceof \DateTime) {
						// @todo Need to take into account column type for date formatting
						$bindValue = (string) $value->format($this->dateTimeFormat());
					} else {
						$bindValue = (string) $value; // Attempt cast of object to string (calls object's __toString method)
					}
				} elseif(!is_array($value)) {
					$bindValue = $value;
				}
				
				// Bind given value
				if($bindValue !== false) {
					// Column name with comparison operator
					$colData = explode(' ', $column);
					$operator = '=';
					if (count($colData) > 2) {
						$operator = array_pop($colData);
						$colData = array(implode(' ', $colData), $operator);
					}
					$col = $colData[0];
					
					// Increment ensures column name distinction
					if(false !== $ci) {
						$col = $col . $ci;
						$ci++;
					}
					
					$colParam = preg_replace('/\W+/', '_', $col);
					
					// Add to binds array and add to WHERE clause
					$binds[$colParam] = $bindValue;
				}
			}
			if($loopOnce) { break; }
		}
		return $binds;
	}
	
	
	/**
	 * Return result set for current query
	 */
	public function toCollection(\Spot\Query $query, $stmt)
	{
		$mapper = $query->mapper();
		$entityClass = $query->entityName();
		
		if($stmt instanceof \PDOStatement) {
			// Set PDO fetch mode
			$stmt->setFetchMode(\PDO::FETCH_ASSOC);
			
			$collection = $mapper->collection($entityClass, $stmt);
			
			// Ensure statement is closed
			$stmt->closeCursor();

			return $collection;
			
		} else {
			$mapper->addError(__METHOD__ . " - Unable to execute query " . implode(' | ', $this->connection()->errorInfo()));
			return array();
		}
	}
	
	
	/**
	 * Bind array of field/value data to given statement
	 *
	 * @param PDOStatement $stmt
	 * @param array $binds
	 */
	protected function bindValues($stmt, array $binds)
	{
		// Bind each value to the given prepared statement
		foreach($binds as $field => $value) {
			$stmt->bindValue($field, $value);
		}
		return true;
	}
}
Return current item: Alloy PHP Framework