Db PHP class

Db.php

<?php
/**
 * f framework, the lightest framework for PHP 5
 *
 * Copyright (c) 2007 Franck Marcia
 *
 * @package    f
 * @author     Franck Marcia
 * @copyright  (c) 2007 Franck Marcia
 * @license    http://www.opensource.org/licenses/mit-license.php MIT
 * @version    SVN: $Id: Db.php 60 2007-07-22 20:06:41Z Papa $
 */

/**
 * Class to manage databases using PDO.
 * This class is static.
 *
 * @package    f
 */

class Db {

	/**
	 * Database link identifiers.
	 *
	 * @var array              PDO ojbect after a successful connection to a
	 *                         server, false at start up or after a failure
	 */

	private static $obj = array();

	/**
	 * Data source names.
	 *
	 * @var array
	 */

	private static $dsn = array();

	/**
	 * User names to be connected to the database server.
	 *
	 * @var array
	 */

	private static $user = array();

	/**
	 * Passwords to be connected to the database.
	 *
	 * @var array
	 */

	private static $password = array();

	/**
	 * Optional names of the database.
	 *
	 * @var array
	 */

	private static $base = array();

	/**
	 * Fetch modes.
	 *
	 * @var array
	 */

	private static $fetchMode = array();

	/**
	 * Count of run queries.
	 *
	 * @var array
	 */

	private static $count = array();

	/**
	 * Driver options on connection.
	 *
	 * @var array
	 */

	private static $options = array();

	/**
	 * Attributes when connected.
	 *
	 * @var array
	 */

	private static $attributes = array();

	/**
	 * Constructor for static class pattern.
	 */

	private function __construct() {}

	/**
	 * Formats a error message.
	 * This function is used when throwing an exception.
	 *
	 * @param  string $name    Name of a database object
	 * @param  string $message Message to be formatted
	 * @return string          The formatted message
	 */

	private static function makeError($name, $message) {
		return 'ERROR: ' . $message . ($name === 0 ? '' : ' (' . $name . ')');
	}

	/**
	 * Stores parameters of a database.
	 *
	 * The connection will occur before the first query is run.
	 *
	 * @param  array  $params   Dsn, user, password and optionally base,
	 *                          fetchmode, options and attributes
	 * @param  string $name     Optional name of a database object
	 * @throws Exception
	 */

	public static function init($params, $name = 0) {

		if (!is_array($params) ||
			!isset($params['dsn']) ||
			!isset($params['user']) ||
			!isset($params['password'])
		) {
			throw new Exception(self::makeError($name, 'Wrong parameters in ' .
				'array on connection. The array must contain items "dsn", ' .
				'"user" and "password"'));
		}

		self::$obj[$name] = false;
		self::$dsn[$name] = $params['dsn'];
		self::$user[$name] = $params['user'];
		self::$password[$name] = $params['password'];
		self::$base[$name] =
			isset($params['base']) ? $params['base'] : null;
		self::$fetchMode[$name] =
			isset($params['fetchmode']) ? $params['fetchmode'] : 'assoc';
		self::$count[$name] = 0;
		self::$options[$name] =
			isset($params['options']) ? $params['options'] : null;
		self::$attributes[$name] =
			isset($params['attributes']) ? $params['attributes'] : array();
	}

	/**
	 * Opens a connection to a server.
	 *
	 * @param  string $name     Name of a database object
	 */

	private static function connect($name) {
		self::$obj[$name] = new PDO(
			self::$dsn[$name],
			self::$user[$name],
			self::$password[$name],
			self::$options[$name]
		);
		self::$obj[$name]->setAttribute(
			PDO::ATTR_ERRMODE,
			PDO::ERRMODE_EXCEPTION
		);
		foreach (self::$attributes as $key => $val) {
			self::$obj[$name]->setAttribute($key, $val);
		}
		if (isset(self::$base[$name])) {
			self::$obj[$name]->exec('USE ' . self::$base[$name]);
		}
	}

	/**
	 * Selects a database.
	 *
	 * @param  string $base     The new database name
	 * @param  string $name     Optional name of a database object
	 */

	public static function selectDb($base, $name = 0) {
		self::$base[$name] = $base;
	}

	/**
	 * Closes a connection to a database server.
	 *
	 * @param  string $name     Optional name of a database object
	 */

	public static function close($name = 0) {
		self::$obj[$name] = null;
	}

	/**
	 * Begins a transaction.
	 *
	 * @param  string $name     Optional name of a database object
	 */

	public static function begin($name = 0) {
		if (!self::$obj[$name]) {
			self::connect($name);
		}
		self::$obj[$name]->beginTransaction();
	}

	/**
	 * Commits a transaction.
	 *
	 * @param  string $name     Optional name of a database object
	 */

	public static function commit($name = 0) {
		if (!self::$obj[$name]) {
			self::connect($name);
		}
		self::$obj[$name]->commit();
	}

	/**
	 * Rollbacks a transaction.
	 *
	 * @param  string $name     Optional name of a database object
	 */

	public static function rollback($name = 0) {
		if (!self::$obj[$name]) {
			self::connect($name);
		}
		self::$obj[$name]->rollback();
	}

	/**
	 * Returns the ID generated from the last INSERT operation.
	 *
	 * @param  string $name     Optional name of a database object
	 * @return string           The ID
	 */

	public static function insertId($name = 0) {
		return self::$obj[$name]->lastInsertId();
	}

	/**
	 * Sends a query.
	 *
	 * Can be called with the form query($query, $name).
	 *
	 * @param  string $query    Query string
	 * @param  array  $params   Parameters of the query
	 * @param  string $name     Optional name of a database object
	 * @return mixed            true if the query is an action query or resource
	 *                          if the query is a select query
	 */

	public static function query($query, $params = null, $name = 0) {

		if ($name == 0 && !is_null($params) && !is_array($params)) {
			$name = $params;
			$params = array();
		}

		++self::$count[$name];

		if (!self::$obj[$name]) {
			self::connect($name);
		}
		$result = self::$obj[$name]->prepare($query);

		$n = count($params);

		for ($i = 0; $i < $n; $i += 2) {

			if (!isset($params[$i + 1])) {
				break;
			}

			switch ($params[$i]) {
				case 'bool': $type = PDO::PARAM_BOOL; break;
				case 'null': $type = PDO::PARAM_NULL; break;
				case 'int': $type = PDO::PARAM_INT; break;
				case 'txt': $type = PDO::PARAM_STR; break;
				default:
					throw new Exception(self::makeError($name, 'unknown type ' . $params[$i]));
			}
			$value = $params[$i + 1];
			$result->bindValue(($i / 2) + 1, $value, $type);
		}

		$result->execute();

		return $result;

	}

	/**
	 * Defines the fetch mode.
	 *
	 * @param  string $mode     Mode to fetch data
	 * @param  string $name     Optional name of a database object
	 * @throws Exception
	 */

	public static function setFetchMode($mode, $name = 0) {
		switch ($mode) {
	        case 'object':
	        case 'assoc':
	        case 'array':
				self::$fetchMode[$name] = $mode;
	        	break;
			default:
				throw new Exception(
					self::makeError($name, 'invalid fetch mode')
				);
		}
	}

	/**
	 * Fetches a result row as an associative array, an object or a numeric
	 * array depending on the parameter $mode or the general fetch mode
	 *
	 * @param  resource $result Resource which comes from a call to query
	 * @param  string   $mode   Fetching mode: 'assoc', 'object' or 'array'
	 * @param  string   $name   Optional name of a database object
	 * @return mixed            the result row as array if the mode is 'assoc'
	 *                          or 'array' and as object if the mode is 'object'
	 */

	public static function fetch($result, $mode = null, $name = 0) {
		if (is_null($mode)) {
			$mode = self::$fetchMode[$name];
		}
		switch ($mode) {
	        case 'object': $mode = PDO::FETCH_OBJ; break;
	        case 'assoc': $mode = PDO::FETCH_ASSOC; break;
	        case 'array': $mode = PDO::FETCH_NUM; break;
		}

		return $result->fetch($mode);
	}

	/**
	 * Returns the number of rows in result.
	 *
	 * @param  resource $result Resource which comes from a call to query
	 * @return integer          The number of rows
	 */

	public static function numRows($result) {
		return $result->rowCount();
	}

	/**
	 * Returns the number of run queries.
	 *
	 * @param  string $name     Optional name of a database object
	 * @return string           The number of run queries
	 */

	public static function count($name = 0) {
		return self::$count[$name];
	}

	/**
	 * Returns the value of the first field of the first row of a query.
	 *
	 * @param  string $query    Query string
	 * @param  array  $params   Parameters of the query
	 * @param  string $name     Optional name of a database object
	 * @return string           Value of the first field of the first row
	 */

	public static function queryVal($query, $params = null, $name = 0) {
		$result = self::query($query, $params, $name);
		$r = self::fetch($result, 'array');
		$result->closeCursor();
		return $r ? $r[0] : null;
	}

	/**
	 * Returns the first row of a query.
	 *
	 * @param  string $query    Query string
	 * @param  array  $params   Parameters of the query
	 * @param  string $name     Optional name of a database object
	 * @return array            First row of the query
	 */

	public static function queryOne($query, $params = null, $name = 0) {
		$result = self::query($query, $params, $name);
		$r = self::fetch($result);
		$result->closeCursor();
		return $r;
	}

	/**
	 * Returns the whole rows of a query.
	 *
	 * @param  string $query    Query string
	 * @param  array  $params   Parameters of the query
	 * @param  string $name     Optional name of a database object
	 * @return array            Results of the query
	 */

	public static function queryAll($query, $params = null, $name = 0) {
		if ($name == 0 && !is_null($params) && !is_array($params)) {
			$name = $params;
			$params = null;
		}
		$a = array();
		$result = self::query($query, $params, $name);
		while ($r = self::fetch($result)) {
			$a[] = $r;
		}
		$result->closeCursor();
		return $a;
	}

	/**
	 * Returns the whole rows of a query as a hash table indexed by a field.
	 *
	 * @param  string $query    Query string
	 * @param  string $key      Name of the field which is used to define the
	 *                          key of the row in the array
	 * @param  array  $params   Optional parameters of the query
	 * @param  string $name     Optional name of a database object
	 * @return array            Results of the query
	 */

	public static function queryHash($query, $key, $params = null, $name = 0) {
		if ($name == 0 && !is_null($params) && !is_array($params)) {
			$name = $params;
			$params = null;
		}
		$a = array();
		$result = self::query($query, $params, $name);
		while ($r = self::fetch($result)) {
			if (is_array($r)) {
				$a[$r[$key]] = $r;
			} else {
				$a[$r->$key] = $r;
			}
		}
		$result->closeCursor();
		return $a;
	}

	/**
	 * Returns values of the first column of a query.
	 *
	 * @param  string $query    Query string
	 * @param  array  $params   Parameters of the query
	 * @param  string $name     Optional name of a database object
	 * @return array            first column of the query
	 */

	public static function queryCol($query, $params = null, $name = 0) {
		if ($name == 0 && !is_null($params) && !is_array($params)) {
			$name = $params;
			$params = null;
		}
		$a = array();
		$result = self::query($query, $params, $name);
		while ($r = self::fetch($result, 'array')) {
			$a[] = $r[0];
		}
		$result->closeCursor();
		return $a;
	}
}

example.php

<?php

include_once "Db.php";
$database = "sqlite:/path/to/db"
Db::init($database);

$one = Db::queryOne(
		"SELECT userId, access, moderated " .
		"FROM users " .
		"WHERE path = ? AND enabled = 1 AND checked = 1",
		array('txt', $path)
);

$val = Db::queryVal(
		"SELECT role " .
		"FROM usersRoles " .
		"WHERE profileId = ? AND userId = ?",
		array('int', $profileId, 'int', $userId)
);

Db::begin();

if (!Db::query(
		"INSERT INTO `posts` " .
			"(`parentId`, `authorId`, `profileId`, `creation`, `checked`, " .
				"`type`, `vote`, `text`, `more`, `left`, `right`, `tags`, `links`) " .
		"VALUES (?, ?, ?, NOW(), ?, ?, ?, ?, ?, ?, ?, ?, ?)",
		array(
			'int', $parentId, 'int', $userId, 'int', $profileId, 'int', $checked, 'txt', $type,
			'txt', $vote, 'txt', $text, 'txt', $more, 'txt', $left, 'txt', $right, 'txt', $tags, 'txt', $links
		)
	)) {
	Db::rollback();
	return array('result' => 0, 'message' => 'error');
}
$postId = Db::insertId();
Db::commit();

define('DEBATES_SELECT',
	"SELECT m.type, m.vote, m.text, m.more, m.left, m.right, m.children, " .
	"m.leftChildren, m.rightChildren, m.locked, m.localPath, m.links, " .
	"m.tags, f.creation favorite, a.name, a.path authorPath, a.avatar, ru.role"
);
define('DEBATES_SELECT_LASTUPDATE', DEBATES_SELECT . ", m.lastUpdate usedDate ");
define('DEBATES_FROM',
	/* posts */
	"FROM posts m " .
	/* post author (LEFT for anonymous posts) */
	"LEFT JOIN users a ON m.authorId = a.userId " .
	/* post debate host */
	"INNER JOIN users p ON p.userId = m.profileId " .
	/* user favorites */
	"LEFT JOIN usersFavorites f ON m.postId = f.postId AND f.userId = ? " .
	/* every profile which can be seen */
	"LEFT JOIN usersRoles ru ON m.profileId = ru.profileId "
);
define('DEBATES_WHERE',
	"WHERE m.deleted = 0 " .
	"AND (m.checked = 1 OR ru.canModerate = 1) " .
	"AND (p.notPrivate = 1 OR ru.canView = 1) "
);
$arr = Db::queryHash(
	DEBATES_SELECT_LASTUPDATE .
	DEBATES_FROM .
	"INNER JOIN usersRoles rs ON rs.profileId = m.authorId AND rs.userId = ? AND rs.canView = 1 " .
	DEBATES_WHERE .
	"AND m.parentId = 0 " .
	"ORDER BY m.lastUpdate DESC",
	'localPath',
	array('int', $userId, 'int', $profileId)
);