<?php
/**
 * this class offers the following public interface methods
 * in each case the $params argument should be null or an associative array of fieldname=>value pairs.
 * 
 * 
 * getAll($sql, $params) -	returns all the rows returned by the sql as a numeric array of objects
 * getOne($sql, $params) -	returns the first row of the recordset (as filtered by the params) as an object
 * getVar($sql, $params) - 	returns the first column of the first row of the recordset as a variable
 * for the above methods:
 * 	used with a sql string as the first argument the $params should be a numeric array of arguments in the same order as the sql placeholders in the query
 * 	used with a table name as the first argument, the $params should be an associative array of field_names to values to be used in a where clause
 *  
 * save ($table, $params) - will save the data in the specified table.  if a primary key is present in the $params, the record will be updated. otherwise a new record will be added.
 * 							NOTE see update for behaviour with $params
 * 							returns false on failure and either the last insert id (if an insert) or true (if an update)
 * replace($table, $params) - as for update but it issues a replace command rather than update in mysql. in sqlite it issues a delete then a replace.
 * insert($table, $params) - ignore the fact that there is a $fields argument.  it is not used for static calls. 
 * 							this method inserts a record into the database.  it returns false on failure or the last insert id on success
 * delete($table, $params) - deletes records from database table where the $params are met
 * 
 * setlimit($limit, $offset) - $limit and $offset are integers.  $offset may be omitted.
 * getFoundRows() - used to obtain the unfiltered row count in a limit/offset query.  returns null if not a limit query.
 * 
 * doQuery($sql, $params) - use with caution.  this is a direct pipe to the $pdo->prepare function.  it returns false on failure or an executed PDO_STATEMENT object if not.  you will then need to iterate over the object with a PDO_STATEMENT::fetch_* method
 * 
 * //for limit/offset queries
 * $sql = "Select * from tablename";
 * $where = array('mymonth'=>5, 'myYear'='2009');
 * $db->setLimit(20, 40);
 * $rows = $db->getAll($sql, $where);
 * $unfilteredResults =  $db->getFoundRows();
 * 
 * 
 * //error handling
 * if false is returned by a function there has been an error;
 * so you somecode like this
 * $results = $db->getAll('mytableName');
 * if (!$results && $db->isError){
 * 	echo nl2br($db->getErrors());
 * }
 * 
 * if you want code execution to stop after an error then
 * pass an argument in getErrors: echo nl2br($db->getErrors(true));
 * 
 */
class myPDO extends PDO{
 
	public $isError = false;
	public $killSwitch = false;
 
	public function __construct($dsn, $user=null, $pass=null){
		parent::__construct($dsn, $user, $pass);
		$this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
		$this->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, true);
	}
 
	public function setKillSwitch($val){
		$this->$killSwitch = (bool) $val;
	}
 
 
	public function doQuery($sql, $params=array(), $override = false){
		$params = array_values($params);
		$this->flush();
		if (!empty($this->limit) && ! $override){
			$rawSql = $sql;
			$sql .= "LIMIT $this->limit OFFSET $this->offset";
			$sql = preg_replace('/select (distinct)?\s+/im', 'select \1 SQL_CALC_FOUND_ROWS ', $sql, 1);
		}
		$s = $this->prepare($sql);
		if (DBTYPE == 'sqlite') {
			$pattern = '/select\s+(distinct)?\s+(.*?)from(.*?)$/im';
			$countQuery = preg_replace($pattern, 'select \1 count(*) from \3', $rawSql);
		}
		$y = $this->prepare($rawSql);
		if (!$s){
			$i = $this->errorInfo();
			$this->setError ($sql, $params, $i[2]);
			return false;
		} else {
			$result = $s->execute($params);
			if (!$result){
				$i = $s->errorInfo();
				$this->setError ($sql, $params, $i[2]);
				return false;
			} else {
				if (!empty($this->limit)){
					$this->limit=$this->offset = null;
					switch (DBTYPE){
						case 'sqlite':
							$s = $this->query('select found_rows()');
							$this->numRows = $s->fetchColum();	
							break;
						case 'mysql':
							$this->numRows = $this->getVar($countQuery);
							break;		
					}
				}
				return $s;
			}
		}
	}
 
	public function setLimit($limit, $offset = 0){
		$this->limit = $limit;
		$this->offset = $offset;
	}
 
	public function getFoundRows(){
		return empty( $this->numRows) ? NULL : $this->numRows;
	}
 
	private function getAllFromTable($table, $params=array()){
		$where = array();
		$fields = $this->getStruct($table);
		foreach ($params as $key=>$value){
			$where[] = "$key=?";
			$values[] = $value;
		}
		$whereClause = empty($where) ? '' : "where " . implode(',' , $where);
		$sql = "Select * from $table $whereClause";
		return $this->_getAll ($sql, $values);
	}
 
	private function _getAll($sql, $params){
		return $this->doQuery($sql, $params);
	}
 
	public function getAll($sql, $params=array(), $raw=false){
		$sql = trim($sql);
		$words = explode(' '. $sql);
		if (count($words) < 2){
			$r = $this->getAllFromTable($sql, $params);
		} else {
			$r = $this>_getAll($sql,$params);
		}
		if (!$r){
			return false;
		} else {
			if($raw) return $r;
			return $r->fetchAll(PDO::FETCH_OBJ);
		}
	}
 
	private function flush (){
		$this->isError = false;
		$this->errors = array();
	}
 
	private function setError($sql, $params, $message){
		$this->errors[] = array('query'=>$sql, 'parameters'=>print_r($params, true), 'message'=>$message, 'time'=>date('r'));
		$this->isError = true;
	}
 
	public function getErrors($kill = false){
		if (count($this->errors) < 1) return;
		foreach($this->errors as $e){
			$message[] = <<<HH
 
Error with a query.
Time: {$e['time']}
Query was: {$e['query']}
Supplied params were: {$e['parameters']}
Error message was: {$e['message']}
HH;
		}
 
		return (implode("\n", $message));
	}
 
	public function __destruct(){
		if (DEBUG && $this->isError) {
			$message = $this->getErrors();
			file_put_contents(BASE . DIRECTORY_SEPARATOR . 'debugLog.txt', implode("\n", $message), FILE_APPEND);
		}
	}
	private function getStruct($table){
		if (DBTYPE == 'sqlite'){
			$fields =  $this->getAll('pragma table_info(?)', array($table));
			foreach ($fields as $f){
				$_fields[$f->name] = $f;
				if ($f['pk'] == 1){
					$_fields['pk'] = $f->name;
				}
			}
		} else {
			$sql = 'Show Columns from ' . $table;
			$fields = $this->getAll($sql);
			foreach ($fields as $f){
				$f->name = $f->Field;
				$_fields[$f->Field] = $f;
				if ($f->Key == 'PRI'){
					$_fields['pk'] = $f->Field;
				}
			}
		}
		return $_fields;
	}
 
	public function update($table, $params = array(), $fields=array()){
		if (empty($fields)) $fields = $this->getStruct($table);
		$fields = $this->getStruct($table);
		$where = "where $primary = ?";
		$sql = "Update $table set ";
		foreach ($params as $param=>$value){
			if (isset($fields[$param]) && $param != $fields['pk']){
				$q[] = "$param = ?";
				$p[] = $value;
			}
		}
		$sql .= implode(',', $q) . $where;
		$p[] = $params[$fields['pk']];
		$result = $this->doQuery($sql, $p);
		return ($result === false) ? false : true;
	}
 
	public function insert($table, $params=array(), $fields=array()){
		if (empty($fields)) {
			$fields = $this->getStruct($table);
		}
		$_f = array();
		foreach ($fields as $type => $f){
			if ($type == 'pk') continue;
			$_f[] = $f->name;
			$placeHolder[] = '?';
			if (empty($params[$f->name])){
				$_p[] = null;
			} else {
				$_p[] = trim($params[$f->name]);
			}
		}
		$sql = "Insert into $table (".implode(',',$_f) .") values (".implode(',', $placeHolder) . ")";
		$r = $this->doQuery($sql, $_p);
		return ($r === false) ? false : $this->lastinsertid();
	}
 
	public function save($table, $params = array()){
		$struct = $this->getStruct($table);
		$primary = $struct['pk'];
		if (empty($params[$primary])){
			return $this->insert($table, $params, $struct);
		} else {
			return $this->update($table, $params, $struct);
		}
	}
 
	public function delete($table, $params=array()){
		$sql = "delete from $table where ";
		$sql .= implode ('=? AND ', array_keys($field));
		$r = $this->doQuery($sql, array_values($params));
		return ($r===false) ? false : true;
	}
 
	public function getOne($sql, $params = array()){
		$this->setLimit(1);
		$results = $this->getAll($sql, $params, true);
		if ($results !== false){
			return $results->fetchObject();			
		} else {
			return false;
		}
	}
 
	public function getVar($sql, $params=array()){
		$results = $this->getAll($sql, $params, true);
		if ($result === false) return false;
		return $result->fetchColumn();
	}
 
	public function replace($table, $params=array()){
		switch (DBTYPE){
			case 'mysql':
				$sql = "Replace into $table set ";
				$sql .= implode ("=?,", array_keys($params));
				return $this->doQuery($sql, array_values($params));
				break;
			case 'sqlite':
				$sql = "Update or replace $table set ";
				$sql .= implode ("=?,", array_keys($params));
				return $this->doQuery($sql, array_values($params));
				break;
		}
	}
}
define ('DBTYPE', 'mysql');
define('DEBUG', true);
$host = '';
$database = '';
$user = '';
$pass = '';
$db = new myPDO("mysql:host=$host;dbname=$database",$user, $pass);
?>