LDB3.php

<?php

/**
 * Diesel Framework
 * Copyright © LQDI Technologies - 2011
 * http://www.lqdi.net
 *
 * Serviço de abstração do banco de dados
 * Light Database Abstraction Layer
 *
 * @author Aryel 'DfKimera' Tupinambá
 * @author Noam 'DfReisan' Gendlerman
 * 
 */

define('LDB_VERSION', 3);

define('LDB_ERROR_EXIT', 1);
define('LDB_ERROR_LOG', 2);
define('LDB_ERROR_EXCEPTION', 3);

class LDB {

	/**
	 * Array of available connections
	 * @var array
	 */
	public static $connections = array();
	
	/**
	 * Should we log all queries in the system log?
	 * @var boolean
	 */
	public static $logQueries = false;
	
	/**
	 * ID of the current connection
	 * @var int
	 */
	public static $currentConnection = 0;
	
	private static $connectionIndex = 0;
	private static $errorMode = LDB_ERROR_EXIT;

	/**
	 * Opens a new connection to the database
	 * 
	 * @param string $hostname The database's hostname to use
	 * @param string $username The database's username
	 * @param string $password The database's password
	 * @param string $database The database's name
	 * @param integer $port The database's port
	 * @param string $table_prefix Prefix to use when accessing tables
	 * @param boolean $auto_switch Should we switch to this connection automatically?
	 * @param boolean $persistent Should this be a persistent connection?
	 * @param string $driver The driver type to use (options: sqlite, mysql, pgsql, odbc)
	 * 
	 * @return mixed Either the connection's ID or false if something happend
	 */
	public static function Connect($hostname, $username, $password, $database, $port = 3306, $table_prefix = "", $auto_switch = true, $persistent = true, $driver = "mysql") {
		$conn = array();

		$conn['hostname'] = $hostname;
		$conn['username'] = $username;
		$conn['password'] = $password;
		$conn['database'] = $database;
		$conn['port'] = $port;
		$conn['persistent'] = (bool) $persistent;
		$conn['table_prefix'] = $table_prefix or "";

		$conn['last_action'] = "Connect to {$hostname} as {$username} in database {$database} (Persistent mode: {$persistent})";
		$conn['last_query'] = NULL;

		try {
			if ($conn['persistent']) {
				$conn['connection'] = new PDO("$driver:host=$hostname;port=$port;dbname=$database", $username, $password, array(PDO::ATTR_PERSISTENT => true));
			} else {
				$conn['connection'] = new PDO("$driver:host=$hostname;port=$port;dbname=$database", $username, $password);
			}
		} catch (PDOException $e) {
			LDB::HandleException($e);
			return false;
		}


		$conn['connection']->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
		$conn['connection']->exec("SET CHARACTER SET utf8");

		$error = self::CheckErrors($connID);
		if ($error) {
			LDB::HandleError($error);
			return false;
		}

		$connID = self::$connectionIndex++;
		self::$connections[$connID] = $conn;
		self::$connections[$connID]['id'] = $connID;

		if ($auto_switch) {
			self::SwitchTo($connID);
		}

		return $connID;
	}

	/**
	 * Shortcut command to Connect
	 * 
	 * @param array $server Array with the proper details (hostname, username, password, database, port)
	 * @param string $table_prefix Prefix to use when accessing tables
	 * @param boolean $auto_switch Should we switch to this connection automatically?
	 * @param boolean $persistent Should this be a persistent connection?
	 * @param string $driver The driver type to use (options: sqlite, mysql, pgsql, odbc)
	 * 
	 * @return mixed Either the connection's ID or false if something happend
	 */
	public static function QuickConnect($server, $table_prefix = "", $auto_switch = true, $persistent = true, $driver = "mysql") {
		return LDB::Connect($server['hostname'], $server['username'], $server['password'], $server['database'], $server['port'], $table_prefix, $auto_switch, $persistent, $driver);
	}
	
	/**
	 * Sets the way LDB should handle errors
	 * @param int $errorMode The error mode (LDB_ERROR_*
	 */
	public static function SetErrorMode($errorMode) {
		self::$errorMode = $errorMode;
	}

	/**
	 * Checks for errors on a connection
	 * 
	 * @param integer $connID The connection's ID
	 * @return mixed False if there's no error or an error string if there is
	 */
	private static function CheckErrors($connID) {
		$conn = self::$connections[$connID];

		if (is_array($conn)) {
			$errInfo = $conn['connection']->errorInfo();
			$errMsg = $errInfo[2];
			if (strlen($errMsg) > 0) {
				$errCode = $conn['connection']->errorCode();
				return "MySQL Error (ConnID: {$connID}) #{$errCode} ('{$errMsg}') while performing the following action: {$conn['last_action']}";
			} else {
				return false;
			}
		}
	}

	/**
	 * Checks for errors on a query
	 * 
	 * @param integer $connID The connection's ID
	 * @param PDOStatement $query Query object
	 * @return mixed False if there's no error or an error string if there is
	 */
	private static function CheckQueryErrors($connID, $query) {
		$conn = self::$connections[$connID];

		$errInfo = $query->errorInfo();
		$errMsg = $errInfo[2];
		if (strlen($errMsg) > 0) {
			$errCode = $query->errorCode();
			return "MySQL Error (ConnID: {$connID}) #{$errCode} ('{$errMsg}')";
		} else {
			return false;
		}
	}
	
	private static function HandleError($error) {
		
		switch(self::$errorMode) {
			
			case LDB_ERROR_EXIT:
				Log::Write("[LDB:ERROR] {$error}");
				error("LDB: {$error}");
				break;
			case LDB_ERROR_LOG:
				Log::Write("[LDB:ERROR] {$error}");
				break;
			case LDB_ERROR_EXCEPTION:
				throw new Exception($error);
				break;
			
		}
		
	}
	
	private static function HandleException(Exception $e) {
		switch(self::$errorMode) {
			
			case LDB_ERROR_EXIT:
				Log::Write("[LDB:EXCEPTION] {$e->getMessage()}");
				Log::Object($e);
				
				error("LDB: {$e->getMessage()}");
				
				break;
			case LDB_ERROR_LOG:
				Log::Write("[LDB:EXCEPTION] {$e->getMessage()}");
				Log::Object($e);
				
				break;
			case LDB_ERROR_EXCEPTION:
				throw $e;
				break;
			
		}
	}

	/**
	 * Switches to a different connection
	 * 
	 * @param integer $connID The new connection's ID
	 */
	public static function SwitchTo($connID) {
		self::$currentConnection = $connID;
	}

	/**
	 * Gets the current connection
	 * 
	 * @return object The current connection
	 */
	private static function GetCurrent() {
		return self::$connections[self::$currentConnection];
	}

	/**
	 * Gets the last inserted row's ID
	 * 
	 * @return integer The last inserted row's ID
	 */
	public static function LastInsertID() {
		return self::$connections[self::$currentConnection]['connection']->lastInsertId();
	}

	/**
	 * Executes a query
	 *
	 * @param string $sqlStatement The SQL string to execute
	 * @return PDOStatement The query object or false on failure
	 *
	 */
	public static function RunQuery($sqlStatement, $data=NULL) {
		$conn = self::GetCurrent();

		if(self::$logQueries) {
			Log::Write("[LDB] Running query: [{$sqlStatement}]");
		}

		$query = $conn['connection']->prepare($sqlStatement);

		if ($data != NULL) {
			foreach ($data as $column => $value) {
				$query->bindParam($column, $value);
			}
		}

		$queryReturn = $query->execute($data);
		$conn['last_action'] = "Query: {$sqlStatement}";
		$conn['last_query'] = $query;

		$error = self::CheckErrors($conn['id']);
		$error = (!$error) ? self::CheckQueryErrors($conn['id'], $query) : false;

		if (!$error && $queryReturn) {
			return $query;
		} else {
			LDB::HandleError($error);
			return false;
		}
	}

	/**
	 * Inserts a row into the database.
	 * Utilizes the current connection;
	 *
	 * @param string $table The table to utilize
	 * @param array $data An array of data
	 *
	 *  Example: array('name' => 'John Doe', 'title' => 'Test');
	 *
	 * @return int The ID of the new row
	 */
	public static function Insert($table, $data) {
		$conn = self::GetCurrent();

		$table = $conn['table_prefix'] . $table;

		ob_start();
		var_dump($data);
		$dump = ob_get_clean();
		Log::Write($dump);

		$columnList = array();
		$valueList = array();
		$newData = array();

		foreach ($data as $column => $value) {
			$columnList[] = $column;
			$valueList[] = ":$column";
			$newData[":$column"] = $value;
		}

		$columns = join(" , ", $columnList);
		$values = join(" , ", $valueList);

		$sqlStatement = "INSERT INTO {$table} ( {$columns} ) VALUES ( {$values} )";
		$query = self::RunQuery($sqlStatement, $newData);

		if ($query) {
			return self::LastInsertID();
		} else {
			return false;
		}
	}

	/**
	 * Updates a single row in the database.
	 * Utilizes the current connection;
	 *
	 * @param string $table The table to utilize
	 * @param array $data An array of data
	 *
	 *  Example: array('name' => 'John Doe', 'title' => 'Test');
	 *
	 * @param int $id The row's ID in the database (column 'id')
	 *
	 * @return boolean True if the operation was completed, false if an error has occurred.
	 */
	public static function Update($table, $data, $id) {
		$conn = self::GetCurrent();

		$table = $conn['table_prefix'] . $table;
		$id = intval($id);

		$pairs = array();
		$newData = array();

		foreach ($data as $column => $value) {
			array_push($pairs, "{$column} = :{$column}");
			$newData[":$column"] = $value;
		}

		$pairs = join(", ", $pairs);

		$sqlStatement = "UPDATE {$table} SET {$pairs} WHERE `id` = {$id}";
		$query = self::RunQuery($sqlStatement, $newData);

		if ($query) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 * Updates multiple rows in the database, using the conditions specified.
	 * Utilizes the current connection.
	 *
	 * @param string $table The table to utilize
	 * @param array $data An array of data
	 *
	 * Example: array('name' => 'John Doe', 'title' => 'Test');
	 *
	 * @param array $conditions An array of conditions in SQL format
	 *
	 * Example: array("`contractID` = 15", "`value` > 5000");
	 *
	 * @param string $operator The junction of the conditional operator (AND, OR, etc.)
	 *
	 * @return boolean True if the operation was completed, false if an error has occurred.
	 */
	public static function ConditionalUpdate($table, $data, $conditions, $operator = "AND") {
		$conn = self::GetCurrent();

		$table = $conn['table_prefix'] . $table;

		$pairs = array();

		$newData = array();

		foreach ($data as $column => $value) {
			array_push($pairs, "{$column} = :{$column}");
			$newData[":$column"] = $value;
		}

		$pairs = join(", ", $pairs);
		$conditions = join(" {$operator} ", $conditions);

		$sqlStatement = "UPDATE {$table} SET {$pairs} WHERE {$conditions}";
		$query = self::RunQuery($sqlStatement, $newData);

		if ($query) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 * Increases the value of a specific field.
	 * Utilizes the current connection.
	 * 
	 * @param string $table The table to utilize
	 * @param string $field The field to increase the value of 
	 * @param integer $value The amount to increase it by
	 * @param integer $id The row's ID (field `id`)
	 * 
	 * @return boolean True on success, false on error.
	 */
	public static function IncreaseValue($table, $field, $value, $id) {
		$conn = self::GetCurrent();

		$table = $conn['table_prefix'] . $table;
		$id = intval($id);
		$value = intval($value);

		$sqlStatement = "UPDATE {$table} SET {$field}={$field}+:value WHERE `id` = {$id}";
		$query = self::RunQuery($sqlStatement, array('value' => $value));

		if ($query) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 * Conditionally increases the value of a specific field.
	 * Utilizes the current connection.
	 * 
	 * @param string $table The table to utilize
	 * @param string $field The field to increase the value of 
	 * @param integer $value The amount to increase it by
	 * @param array $conditions An array of conditions in SQL format
	 * Example: array("`contractID` = 15", "`value` > 5000");
	 * 
	 * @return boolean True on success, false on error.
	 */
	public static function ConditionalIncreaseValue($table, $field, $value, $conditions, $operator="AND") {
		$conn = self::GetCurrent();

		$table = $conn['table_prefix'] . $table;

		$conditions = join(" {$operator} ", $conditions);

		$sqlStatement = "UPDATE {$table} SET {$field}={$field}+:value WHERE {$conditions}";
		$query = self::RunQuery($sqlStatement, array('value' => $value));

		if ($query) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 * Removes a row from the database.
	 * Uses the current connection.
	 *
	 * @param string $table The table to utilize
	 * @param int $id The ID of the row to remove
	 *
	 * @return boolean True if the operation was completed, false if an error has occurred.
	 */
	public static function Remove($table, $id) {
		$conn = self::GetCurrent();

		$table = $conn['table_prefix'] . $table;
		$id = intval($id);

		$sqlStatement = "DELETE FROM {$table} WHERE `id` = {$id}";
		$query = self::RunQuery($sqlStatement);

		if ($query) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 * Removes multiple rows from the database.
	 * Uses the current connection.
	 *
	 * @param string $table The table to utilize
	 * @param array $conditions An array of conditions in SQL format
	 *
	 * Example: array("`contractID` = 15", "`value` > 5000");
	 *
	 * @param string $operator The junction of the conditional operator (AND, OR, etc.)
	 *
	 * @return boolean True if the operation was completed, false if an error has occurred.
	 */
	public static function ConditionalRemove($table, $conditions, $operator="AND") {
		$conn = self::GetCurrent();

		$table = $conn['table_prefix'] . $table;
		$conditions = join(" {$operator} ", $conditions);

		$sqlStatement = "DELETE FROM {$table} WHERE {$conditions}";
		$query = self::RunQuery($sqlStatement);

		if ($query) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 * Gets the data from a single row in the database.
	 * Utilizes the current connection.
	 *
	 * @param string $table The table to utilize
	 * @param int $id The ID of the row to get the data of
	 * @param array $fields The fields to use
	 *
	 * @return array An array of columns and values​​, or false if an error occurs.
	 */
	public static function GetSingle($table, $id, $fields=NULL) {
		$conn = self::GetCurrent();

		$table = $conn['table_prefix'] . $table;
		$id = intval($id);

		$pairs = "*";
		if ($fields != NULL) {
			$pairs = array();
			foreach ($fields as $field) {
				$field = "`" . $field . "`";
				$pairs[] = $field;
			}
			$pairs = join(" , ", $pairs);
		}

		$sqlStatement = "SELECT {$pairs} FROM {$table} WHERE `id` = {$id} LIMIT 1";
		$query = self::RunQuery($sqlStatement);

		if ($query) {
			$data = $query->fetch(PDO::FETCH_ASSOC);

			return $data;
		} else {
			return false;
		}
	}

	/**
	 * Gets the data in a single row in the database, from the properties.
	 * Uses the current connection.
	 *
	 * @param string $table The table to utilize
	 * @param array $properties An array of properties to go by
	 *
	 * Example: array('name' => 'John Doe', 'title' => 'Test');
	 *
	 * @return array An array of columns and values​​, or false if an error occurs.
	 */
	public static function GetSingleByProperty($table, $properties) {
		$conn = self::GetCurrent();

		$table = $conn['table_prefix'] . $table;

		$pairs = array();
		$newData = array();

		foreach ($properties as $column => $value) {
			array_push($pairs, "{$column} = :{$column}");
			$newData[":$column"] = $value;
		}

		$pairs = join(" AND ", $pairs);

		$sqlStatement = "SELECT * FROM {$table}  WHERE {$pairs} LIMIT 1";
		$query = self::RunQuery($sqlStatement, $newData);
		
		if ($query) {
			$data = $query->fetch(PDO::FETCH_ASSOC);
			return $data;
		} else {
			return false;
		}
	}

	/**
	 * Gets a list of multiple records in the database, according to the conditions and parameters
	 * Uses the current connection.
	 *
	 * @param string $table The table to utilize
	 * @param array $conditions An array of conditions in SQL format
	 *
	 * Example: array("`contractID` = 15", "`value` > 5000");
	 *
	 * @param string $operator The junction of the conditional operator (AND, OR, etc.)
	 * @param string $order The order of the records.
	 *
	 * Example: `id` DESC
	 *
	 * @param int $maxrecords Maximum records to obtain
	 * @param int $offset Where should we start counting records from?
	 *
	 * @return object Returns the query to iterate using while, or false if an error occurs.
	 *
	 * @see LDB::Next();
	 *
	 */
	public static function GetMultiple($table, $conditionals = NULL, $operator = "AND", $order = NULL, $maxrecords = 0, $offset = NULL, $fields=NULL) {
		$conn = self::GetCurrent();

		$table = $conn['table_prefix'] . $table;

		if ($conditionals != NULL) {
			$conditionals = "WHERE " . join(" {$operator} ", $conditionals);
		} else {
			$conditionals = "";
		}


		if ($order != NULL) {
			$order = "ORDER BY {$order}";
		} else {
			$order = "";
		}

		if ($maxrecords > 0) {
			if ($offset != NULL) {
				$limit = "LIMIT {$offset}, {$maxrecords}";
			} else {
				$limit = "LIMIT {$maxrecords}";
			}
		} else {
			$limit = "";
		}

		$pairs = "*";
		if ($fields != NULL) {
			$pairs = array();
			foreach ($fields as $field) {
				$field = "`" . $field . "`";
				$pairs[] = $field;
			}
			$pairs = join(" , ", $pairs);
		}

		$sqlStatement = "SELECT {$pairs} FROM {$table} {$conditionals} {$order} {$limit}";
		$query = self::RunQuery($sqlStatement);

		if ($query) {
			return $query->fetchAll(PDO::FETCH_ASSOC);
		} else {
			return false;
		}
	}

	/**
	 * Gets the next record from a list.
	 *
	 * @return array The record data or false if the list is invalid
	 */
	public static function Next($query) {
		if ($query) {
			return $query->fetch(PDO::FETCH_ASSOC);
		} else {
			return false;
		}
	}

	/**
	 * Oops, PDO has no mysql_num_rows equivalent. Guess we can't do this the right way. Sucks.
	 * 
	 * @return integer Always returns zero
	 */
	public static function NumRows() {

		return 0;
	}
	
	public static function CountRecords($table, $conditionals = NULL, $operator = "AND") {
		$conn = self::GetCurrent();

		$table = $conn['table_prefix'] . $table;

		if ($conditionals != NULL) {
			$conditionals = "WHERE " . join(" {$operator} ", $conditionals);
		} else {
			$conditionals = "";
		}


		if ($order != NULL) {
			$order = "ORDER BY {$order}";
		} else {
			$order = "";
		}


		$sqlStatement = "SELECT COUNT(*) as count FROM {$table} {$conditionals}";
		$query = self::RunQuery($sqlStatement);
		
		if ($query) {
			return intval($query->fetchColumn());
		} else {
			return false;
		}
	}

	/**
	 * Returns The number of affected rows in the last query in the current connection
	 * 
	 * @return integer The number of affected rows in the last query in the current connection
	 */
	public static function AffectedRows() {
		$conn = self::GetCurrent();
		return $conn['last_query']->rowCount();
	}

	/**
	 * Cleans the memory from data of the last query.
	 *
	 * @param string $connID The connection ID or NULL to use the current connection
	 * @return boolean True if the operation was completed, false if an error occurs
	 */
	public static function Clear($connID = NULL) {
		if ($connID != NULL) {
			$conn = self::GetCurrent();
		} else {
			$conn = self::$connections[$connID];
		}

		$conn['last_action'] = "Cleared the last called query";
		$conn['last_query'] = NULL;

		//return $success;
		return true;
	}

	/**
	 * Disconnects a connection.
	 *
	 * @param string $connID The ID of the connection to disconnect
	 * @return boolean true
	 */
	public static function Disconnect($connID = NULL) {
		if ($connID != NULL) {
			$conn = self::GetCurrent();
		} else {
			$conn = self::$connections[$connID];
		}

		self::Clear($connID);
		$conn['connection'] = NULL;
		$conn['last_action'] = "Disconnected";

		return true;
	}

}

?>