Просмотр файла includes/classes/DBM.php

Размер файла: 20.1Kb
<?php

class DBM {

	/**
	 * Automatically add/update created/updated fields
	 *
	 * @var boolean
	 */
	public static $timestamp_writes = false;

	/**
	 * Dynamic config creds
	 *
	 * @var Array - representing config details
	 */
	protected $config;

	/**
	 * The PDO objects for the connection
	 *
	 * @var PDO - the Pear Data Object
	 */
	protected $pdo;

	/**
	 * A reference to the singleton instance
	 *
	 * @var DBM
	 */
	protected static $instance = null;

	/**
	 * method instance.
	 * 	- static, for singleton, for creating a global instance of this object
	 *
	 * @return - DBM Object
	 */
	public static function run() {
		if (! isset(self::$instance)) {
			self::$instance = new DBM();
		}
		return self::$instance;
	}

	/**
	 * Constructor.
	 * 	- make protected so only subclasses and self can create this object (singleton)
	 */
	protected function __construct() {}

	/**
	 * method config
	 * 	- configure connection credentials to the db server
	 *
	 * @param host - the host name of the db to connect to
	 * @param name - the database name
	 * @param user - the user name
	 * @param password - the users password
	 * @param port (optional) - the port to connect using, default to 3306
	 * @param driver - the dsn prefix
	 */
	public function config($host, $name, $user, $password, $port=null, $driver='mysql') {
		if (!$this->validateDriver($driver)) {
			throw new Exception('DATABASE WRAPPER::error, the database you wish to connect to is not supported by your install of PHP.');
		}

		if (isset($this->pdo)) {
			error_log('DATABASE WRAPPER::warning, attempting to config after connection exists');
		}

		$this->config = array(
			'driver' => $driver,
			'host' => $host,
			'name' => $name,
			'user' => $user,
			'password' => $password,
			'port' => $port
		);
	}

	/**
	 * method createConnection.
	 * 	- create a PDO connection using the credentials provided
	 *
     * @param driver - the dsn prefix
	 * @param host - the host name of the db to connect to
	 * @param name - the database name
	 * @param user - the user name
	 * @param password - the users password
	 * @param port (optional) - the port to connect using, default to 3306
	 * @return PDO object with a connection to the database specified
	 */
	protected function createConnection($driver, $host, $name, $user, $password, $port=null) {
		if (!$this->validateDriver($driver)) {
			throw new Exception('DATABASE WRAPPER::error, the database you wish to connect to is not supported by your install of PHP.');
		}

		// attempt to create pdo object and connect to the database
		try {
			//@TODO the following drivers are NOT supported yet: odbc, ibm, informix, 4D
			// build the connection string from static constants based on the selected PDO Driver.
			if ($driver == "sqlite" || $driver == "sqlite2") {
				$connection_string = $driver.':'.$host;
			} elseif ($driver == "sqlsrv") {
				$connection_string = "sqlsrv:Server=".$host.";Database=".$name;
			} elseif ($driver == "firebird" || $driver == "oci") {
				$connection_string = $driver.":dbname=".$name;
			} else {
				$connection_string = $driver.':host='.$host.';dbname='.$name;
			}

			// add the port if one was specified
			if (!empty($port)) {
				$connection_string .= ";port=$port";
			}

			// initialize the PDO object
			$new_connection = new PDO($connection_string, $user, $password);

			// set the error mode
			$new_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
			$new_connection->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
			$new_connection->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);

			$new_connection->exec('SET CHARACTER SET utf8');
			$new_connection->exec('SET NAMES utf8');

			// return the new connection
			return $new_connection;
		}

		// handle any exceptions by catching them and returning false
		catch (PDOException $e) {
			throw $e;
		}
		catch(Exception $e) {
			throw $e;
		}
	}

	/**
	 * method get.
	 * 	- grab the PDO connection to the DB
	 */
	protected function get() {

		// if we have not created the db connection yet, create it now
		if (!isset($this->pdo)) {
			$this->pdo = $this->createConnection(
				$this->config['driver'],
				$this->config['host'],
				$this->config['name'],
				$this->config['user'],
				$this->config['password'],
				$this->config['port']
			);
		}

		return $this->pdo;
	}

	/**
	 * Получение количества элементов в таблице
	 * @param  string $table  имя таблицы
	 * @param  array  $params массив условий
	 * @return integer        количество записей
	 */
	public function count($table, $params = null) {
		$sql_str = "SELECT count(*) FROM $table";

		$sql_str .= ( count($params)>0 ? ' WHERE ' : '' );

		$add_and = false;
		// add each clause using parameter array
		if (empty($params)) {
			$params = array();
		}
		foreach ($params as $key=>$val) {
			// only add AND after the first clause item has been appended
			if ($add_and) {
				$sql_str .= ' AND ';
			} else {
				$add_and = true;
			}

			// append clause item
			if (is_array($val)) {
				$sql_str .= $key.current($val).":$key";
			} else {
				$sql_str .= "$key = :$key";
			}
		}

		// now we attempt to retrieve the row using the sql string
		try {

			$pstmt = $this->get()->prepare($sql_str);

			// bind each parameter in the array
			foreach ($params as $key=>$val) {
				if (is_array($val)) {
					$val = end($val);
				}
				$pstmt->bindValue(':'.$key, $val);
			}

			$pstmt->execute();

			return $pstmt->fetchColumn(0);
		}
		catch(PDOException $e) {
			throw $e;
		}
		catch(Exception $e) {
			throw $e;
		}
	}

	/**
	 * method select.
	 * 	- retrieve information from the database, as an array
	 *
	 * @param string $table - the name of the db table we are retreiving the rows from
	 * @param array $params - associative array representing the WHERE clause filters
	 * @param int $limit (optional) - the amount of rows to return
	 * @param int $start (optional) - the row to start on, indexed by zero
	 * @param array $order_by (optional) - an array with order by clause
	 * @return mixed - associate representing the fetched table row, false on failure
	 */
	public function select($table, $params = null, $limit = null, $start = null, $order_by = null) {
		// building query string
		$sql_str = "SELECT * FROM $table";
		// append WHERE if necessary
		$sql_str .= ( count($params)>0 ? ' WHERE ' : '' );

		$add_and = false;
		// add each clause using parameter array
		if (empty($params)) {
			$params = array();
		}
		foreach ($params as $key=>$val) {
			// only add AND after the first clause item has been appended
			if ($add_and) {
				$sql_str .= ' AND ';
			} else {
				$add_and = true;
			}

			// append clause item
			if (is_array($val)) {
				$sql_str .= $key.current($val).":$key";
			} else {
				$sql_str .= "$key=:$key";
			}
		}

		// add the order by clause if we have one
		if (!empty($order_by)) {
			$sql_str .= ' ORDER BY';
			$add_comma = false;
			foreach ($order_by as $column => $order) {
				if ($add_comma) {
					$sql_str .= ', ';
				}
				else {
					$add_comma = true;
				}
				$sql_str .= " $column $order";
			}
		}

		// now we attempt to retrieve the row using the sql string
		try {
			// decide which database we are selecting from
            $pdoDriver = $this->get()->getAttribute(PDO::ATTR_DRIVER_NAME);

			//@TODO MS SQL Server & Oracle handle LIMITs differently, for now its disabled but we should address it later.
			$disableLimit = array("sqlsrv", "mssql", "oci");

			// add the limit clause if we have one
			if (!is_null($limit) && !in_array($pdoDriver, $disableLimit)) {
				$sql_str .= ' LIMIT '.(!is_null($start) ? "$start, ": '')."$limit";
			}

			$pstmt = $this->get()->prepare($sql_str);

			// bind each parameter in the array
			foreach ($params as $key=>$val) {
				if (is_array($val)) {
					$val = end($val);
				}
				$pstmt->bindValue(':'.$key, $val);
			}

			$pstmt->execute();

			// now return the results, depending on if we want all or first row only
			if (! is_null($limit) && $limit == 1) {
				return $pstmt->fetch(PDO::FETCH_ASSOC);
			} else {
				return $pstmt->fetchAll(PDO::FETCH_ASSOC);
			}
		}
		catch(PDOException $e) {
			throw $e;
		}
		catch(Exception $e) {
			throw $e;
		}
	}

	/**
	 * method selectFirst.
	 * 	- retrieve the first row returned from a select statement
	 *
	 * @param table - the name of the db table we are retreiving the rows from
	 * @param params - associative array representing the WHERE clause filters
	 * @param array $order_by (optional) - an array with order by clause
	 * @return mixed - associate representing the fetched table row, false on failure
	 */
	public function selectFirst($table, $params = array(), $order_by = null) {
		return $this->select($table, $params, 1, null, $order_by);
	}

	/**
	 * method delete.
	 * 	- deletes rows from a table based on the parameters
	 *
	 * @param table - the name of the db table we are deleting the rows from
	 * @param params - associative array representing the WHERE clause filters
	 * @return bool - associate representing the fetched table row, false on failure
	 */
	public function delete($table, $params = array()) {
		// building query string
		$sql_str = "DELETE FROM $table";
		// append WHERE if necessary
		$sql_str .= count($params)>0 ? ' WHERE ' : '';

		$add_and = false;
		// add each clause using parameter array
		foreach ($params as $key=>$val) {
			// only add AND after the first clause item has been appended
			if ($add_and) {
				$sql_str .= ' AND ';
			} else {
				$add_and = true;
			}

			// append clause item
			if (is_array($val)) {
				$sql_str .= "$key".current($val).":$key";
			} else {
				$sql_str .= "$key=:$key";
			}
		}

		// now we attempt to retrieve the row using the sql string
		try {
			$pstmt = $this->get()->prepare($sql_str);

			// bind each parameter in the array
			foreach ($params as $key=>$val) {
				if (is_array($val)) {
					$val = end($val);
				}
				$pstmt->bindValue(':'.$key, $val);
			}

			// execute the delete query
			$successful_delete = $pstmt->execute();

			// if we were successful, return the amount of rows updated, otherwise return false
			return ($successful_delete == true) ? $pstmt->rowCount() : false;
		}
		catch(PDOException $e) {
			throw $e;
		}
		catch(Exception $e) {
			throw $e;
		}
	}

 	/**
	 * method update.
	 * 	- updates a row to the specified table
	 *
	 * @param string $table - the name of the db table we are adding row to
	 * @param array $params - associative array representing the columns and their respective values to update
	 * @param array $wheres (Optional) - the where clause of the query
	 * @param bool $timestamp_this (Optional) - if true we set created and modified values to now
	 * @return int|bool - the amount of rows updated, false on failure
	 */
	public function update($table, $params, $wheres = array(), $timestamp_this = false) {
		if (! $timestamp_this) {
			$timestamp_this = self::$timestamp_writes;
		}
		// build the set part of the update query by
		// adding each parameter into the set query string
		$add_comma = false;
		$set_string = '';
		foreach ($params as $key=>$val) {
			// only add comma after the first parameter has been appended
			if ($add_comma) {
				$set_string .= ', ';
			} else {
				$add_comma = true;
			}

			// now append the parameter
			if (is_array($val)) {
				$set_string .= "$key=$key".implode($val);
			} else {
				$set_string .= "$key=:param_$key";
			}
		}

		// add the timestamp columns if neccessary
		if ($timestamp_this) {
			$set_string .= ($add_comma ? ', ' : '') . 'modified='.time();
		}

		// lets add our where clause if we have one
		$where_string = '';
		if (!empty($wheres)) {
			// load each key value pair, and implode them with an AND
			$where_array = array();
			foreach($wheres as $key => $val) {

				// append clause item
				if (is_array($val)) {
					$where_array[] = $key.current($val).":where_$key";
				} else {
					$where_array[] = "$key=:where_$key";
				}
			}
			// build the final where string
			$where_string = 'WHERE '.implode(' AND ', $where_array);
		}

		// build final update string
		$sql_str = "UPDATE $table SET $set_string $where_string";

		// now we attempt to write this row into the database
		try {
			$pstmt = $this->get()->prepare($sql_str);

			// bind each parameter in the array
			foreach ($params as $key=>$val) {
				if (is_array($val)) continue;
				$pstmt->bindValue(':param_'.$key, $val);
			}

			// bind each where item in the array
			foreach ($wheres as $key=>$val) {
				if (is_array($val)) {
					$val = end($val);
				}
				$pstmt->bindValue(':where_'.$key, $val);
			}

			// execute the update query
			$successful_update = $pstmt->execute();

			// if we were successful, return the amount of rows updated, otherwise return false
			return ($successful_update == true) ? $pstmt->rowCount() : false;
		}
		catch(PDOException $e) {
			throw $e;
		}
		catch(Exception $e) {
			throw $e;
		}
	}

	/**
	 * method insert.
	 * 	- adds a row to the specified table
	 *
	 * @param string $table - the name of the db table we are adding row to
	 * @param array $params - associative array representing the columns and their respective values
	 * @param bool $timestamp_this (Optional), if true we set created and modified values to now
	 * @return mixed - new primary key of inserted table, false on failure
	 */
	public function insert($table, $params = array(), $timestamp_this = false) {
		if (! $timestamp_this) {
			$timestamp_this = self::$timestamp_writes;
		}

		// first we build the sql query string
		$columns_str = '(';
		$values_str = 'VALUES (';
		$add_comma = false;

		// add each parameter into the query string
		foreach ($params as $key=>$val) {
			// only add comma after the first parameter has been appended
			if ($add_comma) {
				$columns_str .= ', ';
				$values_str .= ', ';
			} else {
				$add_comma = true;
			}

			// now append the parameter
			$columns_str .= "$key";
			$values_str .= ":$key";
		}

		// add the timestamp columns if neccessary
		if ($timestamp_this) {
			$columns_str .= ($add_comma ? ', ' : '') . 'created, modified';
			$values_str .= ($add_comma ? ', ' : '') . time().', '.time();
		}

		// close the builder strings
		$columns_str .= ') ';
		$values_str .= ')';

		// build final insert string
		$sql_str = "INSERT INTO $table $columns_str $values_str";

		// now we attempt to write this row into the database
		try {
			$pstmt = $this->get()->prepare($sql_str);

			// bind each parameter in the array
			foreach ($params as $key=>$val) {
				$pstmt->bindValue(':'.$key, $val);
			}

			$pstmt->execute();
			$newID = $this->get()->lastInsertId();

			// return the new id
			return $newID;
		}
		catch(PDOException $e) {
			throw $e;
		}
		catch(Exception $e) {
			throw $e;
		}
	}

	/**
	 * method insertMultiple.
	 * 	- adds multiple rows to a table with a single query
	 *
	 * @param string $table - the name of the db table we are adding row to
	 * @param array $columns - contains the column names
	 * @param bool $timestamp_these (Optional), if true we set created and modified values to NOW() for each row
	 * @return mixed - new primary key of inserted table, false on failure
	 */
	public function insertMultiple($table, $columns = array(), $rows = array(), $timestamp_these = false) {
		if (! $timestamp_these) {
			$timestamp_these = self::$timestamp_writes;
		}

		// generate the columns portion of the insert statment
		// adding the timestamp fields if needs be
		if ($timestamp_these) {
			$columns[] = 'created';
			$columns[] = 'modified';
		}
		$columns_str = '(' . implode(',', $columns) . ') ';

		// generate the values portions of the string
		$values_str = 'VALUES ';
		$add_comma = false;

		foreach ($rows as $row_index => $row_values) {
			// only add comma after the first row has been added
			if ($add_comma) {
				$values_str .= ', ';
			} else {
				$add_comma = true;
			}

			// here we will create the values string for a single row
			$values_str .= '(';
			$add_comma_forvalue = false;
			foreach ($row_values as $value_index => $value) {
				if ($add_comma_forvalue) {
					$values_str .= ', ';
				} else {
					$add_comma_forvalue = true;
				}
				// generate the bind variable name based on the row and column index
				$values_str .= ':'.$row_index.'_'.$value_index;
			}
			// append timestamps if necessary
			if ($timestamp_these) {
				$values_str .= ($add_comma_forvalue ? ', ' : '') . time().', '.time();
			}
			$values_str .= ')';
		}

		// build final insert string
		$sql_str = "INSERT INTO $table $columns_str $values_str";

		// now we attempt to write this multi inster query to the database using a transaction
		try {
			$this->get()->beginTransaction();
			$pstmt = $this->get()->prepare($sql_str);

			// traverse the 2d array of rows and values to bind all parameters
			foreach ($rows as $row_index => $row_values) {
				foreach ($row_values as $value_index => $value) {
					$pstmt->bindValue(':'.$row_index.'_'.$value_index, $value);
				}
			}

			// now lets execute the statement, commit the transaction and return
			$pstmt->execute();
			$this->get()->commit();
			return true;
		}
		catch(PDOException $e) {
			$this->get()->rollback();
			throw $e;
		}
		catch(Exception $e) {
			$this->get()->rollback();
			throw $e;
		}
	}

	/**
	 * method execute.
	 * 	- executes a query that modifies the database
	 *
	 * @param string $query - the SQL query we are executing
	 * @return mixed - the affected rows, false on failure
	 */
	public function execute($query, $params = array()) {
		try {
			// prepare the statement
			$pstmt = $this->get()->prepare($query);

			// bind each parameter in the array
			foreach ((array)$params as $key=>$val) {
				$pstmt->bindValue(':'.$key, $val, is_int($val) ? PDO::PARAM_INT : PDO::PARAM_STR);
			}

			// execute the query
			$result = $pstmt->execute();

			// only if return value is false did this query fail
			return ($result == true) ? $pstmt->rowCount() : false;
		}
		catch(PDOException $e) {
			throw $e;
		}
		catch(Exception $e) {
			throw $e;
		}
	}

	/**
	 * method query.
	 * 	- returns data from a free form select query
	 *
	 * @param string $query - the SQL query we are executing
	 * @param array $params - a list of bind parameters
	 * @return mixed - the affected rows, false on failure
	 */
	public function query($query, $params = array()) {
		try {

			$pstmt = $this->get()->prepare($query);

			// bind each parameter in the array
			foreach ((array)$params as $key=>$val) {
				$pstmt->bindValue(':'.$key, $val, is_int($val) ? PDO::PARAM_INT : PDO::PARAM_STR);
			}

			// execute the query
			$pstmt->execute();

			// now return the results
			return $pstmt->fetchAll(PDO::FETCH_ASSOC);
		}
		catch(PDOException $e) {
			throw $e;
		}
		catch(Exception $e) {
			throw $e;
		}
	}

	/**
	 * method queryFirst.
	 * 	- returns the first record from a free form select query
	 *
	 * @param string $query - the SQL query we are executing
	 * @param array $params - a list of bind parameters
	 * @return mixed - the affected rows, false on failure
	 */
	public function queryFirst($query, $params = array()) {
		$result = $this->query($query, $params);
		if (empty($result)) {
			return false;
		}
		else {
			return $result[0];
		}
	}

	/**
	 * Validate the database in question is supported by your installation of PHP.
	 * @param string $driver The DSN prefix
	 * @return boolean true, the database is supported; false, the database is not supported.
	 */
	private function validateDriver($driver) {
		if (!in_array($driver, PDO::getAvailableDrivers())) {
			return false;
		} else {
			return true;
		}
	}

	/**
	 * Destructor.
	 * 	- release the PDO db connections
	 */
	function __destruct() {
		unset($this->pdo);
	}
}