class.dbconnection.php

<?php

/**
 *
 *	By Jelle Ursem
 *	
 *	Ultra-simple database abstraction class.
 *	Reads settings from a properties file and takes care of executing queries.
 *	You can easily extend this class to add new database types.	
 *	see http://code.google.com/p/pork-dbobject/ for more info
 *
 *	@package Pork
 */


/**
 * dbConnection class
 * Handles database connections and querying/inserting/removal of rows.
 * 
 * @package Pork
 * @author Jelle Ursem
 * @copyright Jelle Ursem 2009
 * @version 2.0
 * @access public
 */
class dbConnection
{	
	 public  $adapter, $insertID, $log, $debug;
	/**
	 * dbConnection::__construct()
	 * Reads settings from the default settings file and creates the connection.
	 * @param String $useAlternative Alternative settings file
	 */
	
	function __construct($instanceName='Database')
	{
		$this->adapter = $this->getAdapter(Settings::Load()->Get($instanceName));
		$this->debug = Settings::Load()->Get($instanceName, 'debug') === '1';
	}

	/**
	 * dbConnection::getInstance()	 
	 * Singleton functionality.
	 * Creates a static instance.
	 * Usage: DbConnection::getInstance($name)->fetchAll("show tables");
	 * @return DbConnection instance
	 */
	public static function getInstance($instanceName='Database')
    {
		static $instances = array();
		if (!array_key_exists($instanceName, $instances)) 
		{
		   $instances[$instanceName] = new dbConnection($instanceName);
	   }
	  return $instances[$instanceName];
    }


	/**
	 * dbConnection::getAdapter()
	 *
	 * Returns the correct adapter class for the current database type.
	 *
	 */
	private function getAdapter($connectInfo)
	{

		if(class_exists($connectInfo['dbtype']."Adapter"))
		{
			$adapterclass = "{$connectInfo['dbtype']}Adapter";
			$adapter = new $adapterclass($connectInfo);
		}
		else
		{
			die("Adapter not found for database connection {$connectInfo->dbtype}");
		}
		return($adapter);
	}


	/**
	 * dbConnection::connect()
	 * Creates the actual connection
	 * @return bool did the connection succeed
	 */
	function connect()
	{
		$this->connection = $this->adapter->connect();

		if ($this->connection)	
		{
			return true;
		}
		else
		{
			logger::getInstance()->LogEmail(get_class($this->adapter).' : could not connect');
		}
		return false;
	}


	function escapeValue($value)
	{
		return($this->adapter->escapeValue($value));
	}

	/**
	 * dbConnection::numrows()
	 * Find out the number of rows returned
	 * @return int Number of rows
	 */
	function numrows()
	{
		return($this->adapter->numRows($this->result));
	}

		
	/**
	 * dbConnection::query()
	 * Execute the passed query on the database and determine if insert_id or affected_rows or numrows has to be called.
	 * @param String $query Query to be executed.
	 * @returns mixed ID if inserted row, false on error
	 */
	function query($query)
	{
		if($this->adapter->connection == false) {
			logger::getInstance()->LogEmail('Not connected');
			return false;
		}
		if($this->debug) Logger::getInstance()->log[]= "executing: {$query}";
				
		$result = $this->adapter->query($query);
		
		if(!$result)
		{
			$error = $this->adapter->getError();
			if($error != '' && $this->adapter->connection != false) Logger::Warn("Error '{$error}' during execution of query {$query}");
			return false;
		}
		
		$query = trim(strtolower($query));

		$firstpart = substr($query, 0, 6);
		$this->insertID = false;
		$this->affected = false;
		$this->numrows = false;
		switch($firstpart)
		{
				case 'insert':
					$this->insertID = $this->adapter->getInsertID();
				break;
				case 'delete':
				case 'replac':
				case 'update':
					$this->affected = $this->adapter->numAffected();
				break;
				case 'select':
					$this->numrows = $this->adapter->numRows();
				break;
		}
		if ($this->insertID != false) { return ($this->insertID); }

		return true;
		
	}

	/**
	 * dbConnection::fetchOne()
	 * Execute the query and return result # 0.
	 * If no query is passed it will use the previous result.
	 * @param $query optional query to execute. 
	 * @returns String $output
	 */
	function fetchOne($query)
	{
		return ($this->adapter->fetchOne($query));
	}

	/**
	 * dbConnection::fetchAll()
	 * Execute the passed query and fetch a multi-dimensional array of results using $func
	 * If no query is passed it will use the previous result.
	 * @param $query optional query to execute. 
	 * @param $func function to use. Can use mysql_fetch_array or mysql_fetch_object or mysql_fetch_assoc at will.
	 * @returns Array|Object $output multi dimensional array of output.
	 */
	function fetchAll($query=false, $type='assoc')
	{
		return($this->adapter->fetchAll($query, $type));
	}

	/** 
	 * dbConnection::fetchRow()
	 * Execute the passed query and fetch only one row of results using $func
	 * If no query is passed it will use the previous result.
	 * @param $query optional query to execute. 
	 * @param $func function to use. Can use array or object or assoc at will.
	 * @returns Array|Object $output multi dimensional array of output.
	 */
	function fetchRow($query=false, $type='assoc')
	{
		return ($this->adapter->fetchRow($query, $type));
	}

	/**
	 * dbConnection::setDatabase()
	 * 
	 * @param mixed $val
	 * @return
	 */
	function setDatabase($val)
	{
		$this->adapter->setDatabase($val);
	}

	/**
	 * dbConnection::getError()
	 * Returns the last error from the database connection. 
	 * @return string error
	 */
	function getError()
	{
		return($this->adapter->getError());
	}


	/**
	 * dbConnection::getQueries()
	 * Returns an array with executed SQL queries.
	 * @return array $queries
	 */
	function getQueries()
	{
		return($this->adapter->queries);
	}


	/**
	 * dbConnection::tableExists()
	 * @param string $table talble to check if exists
	 * @return boolean exists
	 */	
	function tableExists($table)
	{
		return($this->adapter->tableExists($table));
	}


}

/**
 *  dbConnectionAdapter interface.
 *
 *  Defines all the functions a database adapter should have to be working with Pork.dbObject
 * 
 * @package Pork
 * @author Jelle Ursem 
 * @copyright Jelle Ursem 2009
 * @version 1.0
 * @access public
 */
interface dbConnectionAdapter 
{  
	public function __construct($info);
	public function connect($host, $username,$password);
	public function escapeValue($value);
	public function fetchOne($query=false);
	public function fetchRow($query=false, $type='assoc');
	public function fetchAll($query=false, $type='assoc');	
	public function getError();    
	public function getInsertID();
	public function numRows();
	public function numAffected();
	public function query($query);
	public function selectDatabase($db);	
	public function tableExists($table);
}

/**
 * MySQLAdapter
 * 
 * @package Pork
 * @author Jelle Ursem 
 * @copyright Jelle Ursem 2009
 * @version 1.0
 * @access public
 */
class MySQLAdapter implements dbConnectionAdapter
{
	public $connection;
	public $result;
	public $database;
	public $queries;

	/**
	 * MySQLAdapter::__construct()
	 * 
	 * @param mixed $info
	 * @return void
	 */
	public function __construct($info)
	{
		$this->database = $info['database'];
		$this->connection = $this->connect($info['host'], $info['username'], $info['password']);
		$this->queries = array();
	}

	/**
	 * MySQLAdapter::escapeValue()
	 * 
	 * @param mixed $value
	 * @return
	 */
	public function escapeValue($value)
	{
		return mysql_real_escape_string($value);
	}

	/**
	 * MySQLAdapter::connect()
	 * 
	 * @param mixed $host
	 * @param mixed $username
	 * @param mixed $password
	 * @return
	 */
	public function connect($host, $username, $password)
	{	
		return mysql_connect($host, $username, $password);
	}

	/**
	 * MySQLAdapter::query()
	 * 
	 * @param mixed $query
	 * @return
	 */
	public function query($query)
	{
		$this->queries[] = $query;
		if(!$this->selectDatabase($this->database)) return false;
		$this->result = mysql_query($query, $this->connection);
		return($this->result);
	}

	/**
	 * MySQLAdapter::fetchOne()
	 * 
	 * @param bool $query
	 * @return
	 */
	public function fetchOne($query=false)
	{	
		if($query != false) $this->query($query);
		if($this->result != false && mysql_num_rows($this->result) > 0 && mysql_num_fields($this->result) > 0)
		{
			return( mysql_result($this->result,0) );
		}
		return false;
	}

	/**
	 * MySQLAdapter::fetchRow()
	 * 
	 * @param bool $query
	 * @param string $type
	 * @return
	 */
	/**
	 * MySQLAdapter::fetchRow()
	 * 
	 * @param bool $query
	 * @param string $type
	 * @return
	 */
	public function fetchRow($query=false, $type='assoc')
	{
		if($query != false) $this->query($query);
		if($this->result != false)
		{
			$func = "mysql_fetch_{$type}";
			return($func($this->result));
		}
		return false;
	}

	/**
	 * MySQLAdapter::fetchAll()
	 * 
	 * @param bool $query
	 * @param string $type
	 * @return
	 */
	public function fetchAll($query=false, $type='assoc')
	{
		if($query != false) $this->query($query);
		if($this->result !== false)
		{
			$func = "mysql_fetch_{$type}";
			$output = array();	
			while ($row = $func($this->result))
			{
				$output[] = $row;
			}
			return $output;
		}
		return false;
	}
	
	/**
	 * MySQLAdapter::getInsertID()
	 * 
	 * @return
	 */
	public function getInsertID()
	{
		return mysql_insert_id($this->connection);
	}

	/**
	 * MySQLAdapter::numRows()
	 * 
	 * @return
	 */
	public function numRows()
	{
		return ($this->result) ? mysql_num_rows($this->result) : 0;
	}

	/**
	 * MySQLAdapter::numAffected()
	 * 
	 * @return
	 */
	public function numAffected()
	{
		return ($this->result) ? @mysql_affected_rows($this->result) : 0;
	}

	/**
	 * MySQLAdapter::selectDatabase()
	 * 
	 * @param mixed $db
	 * @return
	 */
	public function selectDatabase($db)
	{
		return mysql_select_db($db, $this->connection);
	}

	/**
	 * MySQLAdapter::tableExists()
	 * 
	 * @param mixed $table
	 * @return
	 */
	public function tableExists($table)
	{
		$input = $this->fetchOne("SHOW TABLES FROM {$this->database} LIKE '{$table}'");
		return($input != false);
	}

	/**
	 * MySQLAdapter::getError()
	 * 
	 * @return
	 */
	public function getError()
	{
		return mysql_error($this->connection);
	}
}


/**
 * SQLiteAdapter
 *
 * @package Pork
 * @author Jelle Ursem 
 * @copyright Jelle Ursem 2009
 * @version 1.0
 * @access public
 */
class SQLiteAdapter implements dbConnectionAdapter
{
	public $connection, $result,$database,$queries;

	/**
	 * SQLiteAdapter::__construct()
	 * 
	 * @param mixed $info
	 * @return void
	 */
	public function __construct($info)
	{
		$error = false;
		$this->database = $info['database'];
		$this->connection = $this->connect($info['database'], $info['mode'],$error);
		if($error != false)
		{
			echo print_array($error, 'SQLITE connection error');
		}
	}

	/**
	 * SQLiteAdapter::escapeValue()
	 * 
	 * @param mixed $value
	 * @return
	 */
	public function escapeValue($value)
	{
		return sqlite_escape_string($value);
	}


	/**
	 * SQLiteAdapter::connect()
	 * 
	 * @param mixed $host
	 * @param mixed $username
	 * @param mixed $password
	 * @return
	 */
	public function connect($host, $username, $password)
	{	
		return sqlite_open($host, $username, $password);
	}

	/**
	 * SQLiteAdapter::query()
	 * 
	 * @param mixed $query
	 * @return
	 */
	public function query($query)
	{
		$this->result = sqlite_query($query, $this->connection);
		return($this->result);
	}

	/**
	 * SQLiteAdapter::fetchOne()
	 * 
	 * @param bool $query
	 * @return
	 */
	public function fetchOne($query=false)
	{	
		if ($query != false) $this->query($query);
		if($this->result !== false)
		{
			return( sqlite_fetch_single($this->result) );
		}
		return false;
	}

	/**
	 * SQLiteAdapter::fetchRow()
	 * 
	 * @param bool $query
	 * @param string $type
	 * @return
	 */
	public function fetchRow($query=false, $type='assoc')
	{
		if ($query != false) $this->query($query);
		if($this->result !== false)
		{
			$func = "sqlite_fetch_{$type}";
			return( $func($this->result, 0));
		}
		return false;
	}

	/**
	 * SQLiteAdapter::fetchAll()
	 * 
	 * @param bool $query
	 * @param string $type
	 * @return
	 */
	public function fetchAll($query=false, $type='assoc')
	{
		if ($query != false) $this->query($query);
		if($this->result !== false)
		{
			if($type == 'assoc') return(sqlite_fetch_all($this->result, SQLITE_ASSOC));
			if($type == 'object') 
			{
				$output = array();	
				while ($row = sqlite_fetch_object($this->result))
				{
					$output[] = $row;
				}
			}
			return $output;
		}
		return false;
	}

	/**
	 * SQLiteAdapter::getInsertID()
	 * 
	 * @return
	 */
	public function getInsertID()
	{
		return sqlite_last_insert_rowid($this->connection);
	}
	
	/**
	 * SQLiteAdapter::numRows()
	 * 
	 * @return
	 */
	public function numRows()
	{
		return ($this->result) ? mysql_num_rows($this->result) : 0;
	}

	/**
	 * SQLiteAdapter::numAffected()
	 * 
	 * @return
	 */
	public function numAffected()
	{
		return ($this->result) ? @mysql_affected_rows($this->result) : 0;
	}

	/**
	 * SQLiteAdapter::selectDatabase()
	 * 
	 * @param mixed $db
	 * @return
	 */
	public function selectDatabase($db)
	{
		return $this->connect($db);
	}

	/**
	 * SQLiteAdapter::getError()
	 * 
	 * @return
	 */
	public function getError()
	{
		return sqlite_error_string(sqlite_last_error($this->connection));

	}

	/**
	 * SQLiteAdapter::tableExists()
	 * 
	 * @param mixed $table
	 * @return
	 */
	public function tableExists($table)
	{
		$input = $this->fetchOne("SELECT count(name) FROM sqlite_master WHERE type='table' and name='{$table}'");
		return($input == 1);
	}
}


