Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?php
- /**
- * MJ_E_CONN
- * MJ_E_CONN_NO_DSN
- * MJ_E_CONN_CONFIG
- * MJ_E_CONN_NO_GROUP
- * MJ_E_CONN_UNDEF_GROUP
- * MJ_E_CONN_UNSUPPORTED
- */
- /**
- * An SQL builder class for PDO database connections.
- *
- * @package Majic
- * @author Michael Jackson
- * @version SVN: $id: $
- */
- abstract class majic_Connection extends PDO
- {
- private static $instances = array();
- private $cache = array(
- 'column_names' => array()
- );
- private $distinct = false;
- private $limit = null;
- private $offset = null;
- private $join = array();
- private $where = array();
- private $group_by = array();
- private $having = array();
- private $order_by = array();
- /**
- * Gets a database connection object with the specified parameters.
- *
- * @param string $dsn The PDO data source name to use
- * @param string $user The user name to use
- * @param string $pass The password to use
- * @param array $options Some connection-specific options to use
- * @return majic_Connection The database connection object
- * @throws majic_Exception
- * @access public
- * @static
- */
- public static function factory($dsn, $user = null, $pass = null,
- $options = array())
- {
- $driver = "majic_connection_" . preg_replace('/:.*$/', '', $dsn);
- if (!class_exists($driver, true)) {
- throw new majic_Exception(MJ_E_CONN_UNSUPPORTED, "driver=$driver");
- }
- try {
- $conn = new $driver($dsn, $user, $pass);
- $conn->setAttribute(PDO::ATTR_STATEMENT_CLASS, array(
- 'majic_Statement',
- array()
- ));
- $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
- } catch (PDOException $e) {
- $msg = $e->getMessage();
- $trc = $e->getTraceAsString();
- throw new majic_Exception(MJ_E_CONN, "message=$msg, trace=$trc");
- }
- self::$instances[$dsn] = array(
- 'conn' => $conn,
- 'user' => $user
- );
- return $conn;
- }
- /**
- * Gets a database connection object with the specified parameters. If an
- * object with the same connection parameters already exists, it will be
- * returned.
- *
- * @param string $group The database configuration options group
- * name to use
- * @return majic_Connection The database connection object
- * @access public
- * @static
- */
- public static function singleton($group = null)
- {
- extract(self::fetchOptions($group));
- if (array_key_exists($dsn, self::$instances)) {
- $conn = self::$instances[$dsn];
- if ($user == $conn['user']) {
- return $conn['conn'];
- }
- }
- $conn = self::factory($dsn, $user, $pass, $options);
- return $conn;
- }
- /**
- * Fetches an array of database configuration options from the specified
- * group in the database configuration file.
- *
- * @param string $group The configuration group options to get
- * @return array An array of connection configuration options
- * @throws majic_Exception
- * @access private
- * @static
- */
- private static function fetchOptions($group = null)
- {
- require APP_DIR . DS . 'config' . DS . 'conn.php';
- if (!isset($conn)) {
- throw new majic_Exception(MJ_E_CONN_CONFIG);
- }
- if (is_null($group)) {
- if (!isset($default_group)) {
- throw new majic_Exception(MJ_E_CONN_NO_GROUP);
- }
- $group = $default_group;
- unset($default_group);
- }
- if (!isset($conn[$group])) {
- throw new majic_Exception(MJ_E_CONN_UNDEF_GROUP);
- }
- if (isset($conn[$group]['dsn'])) {
- $dsn = $conn[$group]['dsn'];
- } else {
- throw new majic_Exception(MJ_E_CONN_NO_DSN);
- }
- $user = @$conn[$group]['user'];
- $pass = @$conn[$group]['pass'];
- $options = isset($conn[$group]['options'])
- ? $conn[$group]['options']
- : array();
- unset($conn);
- return array(
- 'dsn' => $dsn,
- 'user' => $user,
- 'pass' => $pass,
- 'options' => $options
- );
- }
- /**
- * Resets all class variables to their initial state. Automatically called
- * after every select, insert, update, and/or delete.
- *
- * @return void
- * @access public
- */
- public function reset()
- {
- $this->distinct = false;
- $this->limit = null;
- $this->offset = null;
- $this->join = array();
- $this->where = array();
- $this->having = array();
- $this->group_by = array();
- $this->order_by = array();
- }
- /**
- * Lists the columns in a database table.
- *
- * @param string $table The database table name
- * @return array The names of the table's columns
- * @access public
- */
- public function listColumns($table)
- {
- if (!isset($this->cache['column_names'][$table])) {
- if (!$this->moduleLoaded('manager')) {
- $this->loadModule('manager');
- }
- $this->cache['column_names'][$table] = $this->manager->listTableFields($table);
- }
- return $this->cache['column_names'][$table];
- }
- /**
- * Compiles a SELECT statement and queries the database.
- *
- * @param mixed $fields The fields to select
- * @param string $table The table to use
- * @param mixed $where The WHERE condition
- * @param string $limit The LIMIT to use
- * @param string $offset The OFFSET to use
- * @return MDB2_Result The result of the query
- * @throws majic_Exception, majic_PEARException
- * @access public
- */
- public function select($fields = null, $table = null, $where = null,
- $limit = null, $offset = null)
- {
- if (!is_null($where)) {
- $this->where($where);
- }
- if (!is_null($limit)) {
- $this->limit($limit, $offset);
- }
- $sql = $this->distinct == true ? 'SELECT DISTINCT ' : 'SELECT ';
- if (is_null($fields)) {
- $fields = array('*');
- }
- if (!is_array($fields)) {
- $fields = array($fields);
- }
- $sql .= implode(', ', $fields);
- if (is_null($table)) {
- throw new majic_Exception(MJ_E_SQL_NO_TABLE);
- } else {
- if (!is_array($table)) {
- $table = array($table);
- }
- $sql .= "\nFROM " . implode(', ', $table);
- }
- if (count($this->join) > 0) {
- $sql .= "\n";
- $sql .= implode("\n", $this->join);
- }
- if (count($this->where) > 0) {
- $sql .= "\nWHERE ";
- $sql .= implode("\n", $this->where);
- }
- if (count($this->group_by) > 0) {
- $sql .= "\nGROUP BY " . implode(', ', $this->group_by);
- }
- if (count($this->having) > 0) {
- $sql .= "\nHAVING " . implode("\n", $this->having);
- }
- if (count($this->order_by) > 0) {
- $sql .= "\nORDER BY " . implode(', ', $this->order_by);
- }
- if (is_numeric($this->limit)) {
- $limit = $this->db->setLimit($this->limit, $this->offset);
- /*
- if (MDB2::isError($limit)) {
- throw new majic_PEARException($limit);
- }
- */
- }
- $this->reset();
- $result = $this->query($sql);
- /*
- if (MDB2::isError($result)) {
- throw new majic_PEARException($result);
- }
- */
- return $result;
- }
- /**
- * Compiles an INSERT statement and queries the database.
- *
- * @param string $table The table name
- * @param mixed $data An array (or object) of data to insert
- * @return bool True upon success
- * @throws majic_Exception, majic_PEARException
- * @access public
- */
- public function insert($table = null, $data = null)
- {
- if (is_null($table)) {
- throw new majic_Exception(MJ_E_SQL_NO_TABLE);
- }
- if (is_null($data)) {
- throw new majic_Exception(MJ_E_SQL_NO_DATA);
- }
- if (is_object($data)) {
- $data = $this->objectToArray($data);
- }
- $values = array();
- foreach ($data as $datum) {
- $values[] = $this->db->quote($datum);
- }
- $keys = implode(', ', array_keys($data));
- $values = implode(', ', $values);
- $sql = "INSERT INTO $table ($keys) VALUES ($values)";
- $this->reset();
- $affected_rows = $this->db->exec($sql);
- if (MDB2::isError($affected_rows)) {
- throw new majic_PEARException($affected_rows);
- }
- return $affected_rows;
- }
- /**
- * Compiles an UPDATE statement and queries the database.
- *
- * @param mixed $table The table name
- * @param mixed $data An array (or object) of data to update
- * @param array $where The WHERE condition
- * @return int The number of affected rows
- * @throws majic_Exception, majic_PEARException
- * @access public
- */
- public function update($table = null, $data = null, $where = null)
- {
- if (is_null($table)) {
- throw new majic_Exception(MJ_E_SQL_NO_TABLE);
- }
- if (is_null($data)) {
- throw new majic_Exception(MJ_E_SQL_NO_DATA);
- }
- if (is_array($table)) {
- $table = implode(', ', $table);
- }
- if (is_object($data)) {
- $data = $this->objectToArray($data);
- }
- if (!is_null($where)) {
- $this->where($where);
- }
- $values = array();
- foreach ($data as $column => $datum) {
- $values[] = $column . ' = ' . $this->db->quote($datum);
- }
- $sql = "UPDATE $table SET " . implode(', ', $values);
- if (count($this->where) > 0) {
- $sql .= "\nWHERE " . implode("\n", $this->where);
- }
- $this->reset();
- $affected_rows = $this->db->exec($sql);
- if (MDB2::isError($affected_rows)) {
- throw new majic_PEARException($affected_rows);
- }
- return $affected_rows;
- }
- /**
- * Compiles a DELETE statement and queries the database.
- *
- * @param string $table The table to use
- * @param mixed $where The WHERE condition
- * @return int The number of affected rows
- * @throws majic_Exception, majic_PEARException
- * @access public
- */
- public function delete($table = null, $where = null)
- {
- if (is_null($table)) {
- throw new majic_Exception(MJ_E_SQL_NO_TABLE);
- }
- if (!is_null($where)) {
- $this->where($where);
- }
- $sql = "DELETE FROM $table";
- if (count($this->where) > 0) {
- $sql .= "\nWHERE " . implode("\n", $this->where);
- }
- $this->reset();
- $affected_rows = $this->db->exec($sql);
- if (MDB2::isError($affected_rows)) {
- throw new majic_PEARException($affected_rows);
- }
- return $affected_rows;
- }
- /**
- * Sets a flag which tells the query string compiler to add DISTINCT to
- * the query.
- *
- * @param bool $distinct True to add DISTINCT, false otherwise
- * @return object
- * @access public
- */
- public function distinct($distinct = true)
- {
- $this->distinct = is_bool($distinct) ? $distinct : true;
- return $this;
- }
- /**
- * Generates the JOIN portion of the query.
- *
- * @param string $table The table name
- * @param string $cond The join condition
- * @param string $type The type of join
- * @return object
- * @access public
- */
- public function join($table, $cond, $type = '')
- {
- // make sure the type of join is valid
- if ($type != '') {
- $join_types = array(
- 'LEFT',
- 'RIGHT',
- 'OUTER',
- 'INNER',
- 'LEFT OUTER',
- 'RIGHT OUTER'
- );
- $type = strtoupper(trim($type));
- if (!in_array($type, $join_types, true)) {
- $type = '';
- } else {
- $type .= ' ';
- }
- }
- $this->join[] = "{$type}JOIN $table ON $cond";
- return $this;
- }
- /**
- * Prepares the WHERE condition of the query with escaped values. May
- * be called in several different ways:
- *
- * 1) where('id', 1)
- *
- * Appends 'WHERE id = 1' to the query. Note: if this is called after the
- * first WHERE condition has already been set, WHERE will not be used and
- * the condition will be appended using AND.
- *
- * 2) where('id', 1, 'or')
- *
- * Appends 'OR id = 1' to the query. Note: if this is called after the
- * first WHERE condition has already been set, WHERE will not be used and
- * the condition will be appended using OR.
- *
- * 3) where('AND balance >', 1)
- *
- * Appends 'AND balance > 1' to the query. The SQL conjunction and/or
- * operator may be appended to the first value.
- *
- * 4) where(array('id' => 1, 'balance >' => 200))
- *
- * Appends 'AND id = 1 AND balance > 200' to the query. An array may be
- * used as the first parameter to specify multiple conditions.
- *
- * 5) where(array('id' => 1, 'balance >' => 200), 'or')
- *
- * Appends 'OR id = 1 OR balance > 200' to the query. When an array is
- * passed as the first parameter, the second parameter specifies the default
- * conjunction to use.
- *
- * @param mixed $key The key to use or an array of key => values
- * @param mixed $value The value to match
- * @param string $conj May be either 'and' or 'or'
- * @return object
- * @access public
- */
- public function where($key, $value = null, $conj = null)
- {
- if (is_array($key)) {
- $conj = $value; // second param is conj
- } else {
- $key = array($key => $value);
- }
- if (is_null($conj) || stripos($conj, 'and') !== false) {
- $conj = 'AND ';
- } else {
- $conj = 'OR ';
- }
- foreach ($key as $k => $v) {
- if (count($this->where) === 0) {
- $k = $this->stripConjunction($k);
- $pre = '';
- } else {
- $pre = $conj;
- }
- if (!empty($v)) {
- $v = $this->db->escape($v);
- if (!$op = $this->getOperator($k)) {
- $k .= ' =';
- } else if (strtoupper($op) == 'LIKE') {
- // does it have a wildcard already?
- if (!preg_match('/(^%|%$)/', $v)) {
- $v = "%$v%";
- }
- }
- $this->where[] = $pre . $k . ' ' . $this->db->quote($v);
- } else {
- $this->where[] = $pre . $k;
- }
- }
- return $this;
- }
- /**
- * Prepares the HAVING condition of the query with escaped values. May be
- * called in several different ways:
- *
- * 1) having('count(*) = 10')
- *
- * Appends 'HAVING count(*) = 10' to the query.
- *
- * 2) having('count(*)', 10)
- *
- * Appends 'HAVING count(*) = 10' to the query. Note that passing in the
- * second value separately will automatically escape it.
- *
- * 3) having('count(*) >', 10)
- *
- * Appends 'HAVING count(*) > 10' to the query. Note that SQL operators
- * may be appended to the first parameter.
- *
- * @param mixed $key The key to use or an array of key => values
- * @param mixed $value The value to match
- * @param string $conj May be either 'and' or 'or'
- * @return object
- * @access public
- */
- public function having($key, $value = null, $conj = null)
- {
- if (is_array($key)) {
- $conj = $value; // second param is conj
- } else {
- $key = array($key => $value);
- }
- if (is_null($conj) || stripos($conj, 'and') !== false) {
- $conj = 'AND ';
- } else {
- $conj = 'OR ';
- }
- foreach ($key as $k => $v) {
- if (count($this->having) === 0) {
- $k = $this->stripConjunction($k);
- $pre = '';
- } else {
- $pre = $conj;
- }
- if (!empty($v)) {
- if (!$op = $this->getOperator($k)) {
- $k .= ' =';
- }
- $v = ' ' . $this->db->escape($v);
- }
- $this->having[] = $pre . $k . $v;
- }
- return $this;
- }
- /**
- * Sets the ORDER BY portion of the query.
- *
- * @param string $order_by The key to order by
- * @param string $order The order to use
- * @return object
- * @access public
- */
- public function orderBy($order_by, $order = '')
- {
- $order = strtoupper(trim($order));
- if ($order != '') {
- $order_types = array(
- 'ASC',
- 'DESC',
- 'RAND()'
- );
- $order = in_array($order, $order_types, true)
- ? " $order"
- : ' ASC';
- }
- $this->order_by[] = $order_by . $order;
- return $this;
- }
- /**
- * Sets the GROUP BY portion of the query.
- *
- * @param string $by The value to group the results by
- * @return object
- * @access public
- */
- public function groupBy($by)
- {
- if (is_string($by)) {
- $by = explode(',', $by);
- }
- foreach ($by as $val) {
- $val = trim($val);
- if ($val != '') {
- $this->group_by[] = $val;
- }
- }
- return $this;
- }
- /**
- * Sets the LIMIT of the query.
- *
- * @param int $value The limit value
- * @param int $offset The offset value
- * @return object
- * @access public
- */
- public function limit($value, $offset = null)
- {
- $this->limit = $value;
- if (!is_null($offset)) {
- $this->offset = $offset;
- }
- return $this;
- }
- /**
- * Sets the OFFSET of the query.
- *
- * @param int $value The offset value
- * @return object
- * @access public
- */
- public function offset($value)
- {
- $this->offset = is_numeric($value) ? $value : 0;
- return $this;
- }
- /**
- * Tests a string for SQL operators.
- *
- * @param string $value The string to test
- * @return string The SQL operator in the string
- * @access private
- */
- private function getOperator($value)
- {
- preg_match('/(<|>|!|=|like|is null|is not null)$/i', trim($value), $match);
- return isset($match[1]) ? $match[1] : null;
- }
- /**
- * Strips the SQL conjunction from a string.
- *
- * @param string $value The string to strip
- * @return string The string with the SQL conjunction stripped
- * @access private
- */
- private function stripConjunction($value)
- {
- return preg_replace('/^\s*(and|or)\s*/i', '', $value);
- }
- /**
- * Converts an object to an array, ignoring nested objects and arrays.
- *
- * @param object $obj The object to convert
- * @return array An associative array containing the object's variables
- * @access private
- */
- private function objectToArray($obj)
- {
- $arr = array();
- $obj_vars = get_object_vars($obj);
- foreach ($obj_vars as $key => $value) {
- if (!is_object($value) && !is_array($value)) {
- $arr[$key] = $value;
- }
- }
- }
- }
- /**
- * A wrapper class for PDOStatement objects. Provides some convenient methods
- * for extracting data.
- *
- * @package Majic
- * @author Michael Jackson
- * @version SVN: $id: $
- */
- class majic_Statement extends PDOStatement
- {
- /**
- * Constructor.
- *
- * @access protected
- */
- protected function __construct()
- {
- }
- /**
- * Fetches the result of the query as an array of objects.
- *
- * @return array An array of objects resulting from the query
- * @throws majic_PEARException
- * @access public
- */
- public function result()
- {
- return $this->fetchAll(PDO::FETCH_OBJ);
- }
- /**
- * Fetches the result of the query as an array of arrays.
- *
- * @return array An array of arrays resulting from the query
- * @throws majic_PEARException
- * @access public
- */
- public function resultArray()
- {
- return $this->fetchAll(PDO::FETCH_ASSOC);
- return $this;
- }
- /**
- * Fetches the next row of the result as an object.
- *
- * @return object An object representing the next row of the query result
- * @throws majic_PEARException
- * @access public
- */
- public function row($num = null)
- {
- $row = $this->result->fetchRow(MDB2_FETCHMODE_OBJECT, $num);
- if (MDB2::isError($row)) {
- throw new majic_PEARException($row);
- }
- return $row;
- }
- /**
- * Fetches the next row of the result as an array.
- *
- * @return array An array representing the next row of the query result
- * @throws majic_PEARException
- * @access public
- */
- public function rowArray($num = null)
- {
- $row = $this->result->fetchRow(MDB2_FETCHMODE_ASSOC, $num);
- if (MDB2::isError($row)) {
- throw new majic_PEARException($row);
- }
- return $row;
- }
- }
- ?>
Add Comment
Please, Sign In to add comment