From d2a9dbb0562dd35df998f417c8cd7d34c60a16e6 Mon Sep 17 00:00:00 2001 From: thomascube Date: Thu, 18 May 2006 21:25:11 +0000 Subject: Updated MDB2 wrapper class contributed by Lukas Kahwe Smith --- program/include/rcube_mdb2.inc | 675 +++++++++++++++++++++++++++-------------- 1 file changed, 451 insertions(+), 224 deletions(-) (limited to 'program/include/rcube_mdb2.inc') diff --git a/program/include/rcube_mdb2.inc b/program/include/rcube_mdb2.inc index 54a40e796..eec975c69 100755 --- a/program/include/rcube_mdb2.inc +++ b/program/include/rcube_mdb2.inc @@ -2,318 +2,545 @@ /* +-----------------------------------------------------------------------+ - | program/include/rcube_db.inc | + | program/include/rcube_mdb2.inc | | | | This file is part of the RoundCube Webmail client | | Copyright (C) 2005, RoundCube Dev. - Switzerland | | Licensed under the GNU GPL | | | | PURPOSE: | - | PEAR:DB wrapper class that implements PEAR DB functions | - | See http://pear.php.net/package/DB | + | PEAR:DB wrapper class that implements PEAR MDB2 functions | + | See http://pear.php.net/package/MDB2 | | | +-----------------------------------------------------------------------+ - | Author: David Saez Padros | + | Author: Lukas Kahwe Smith | +-----------------------------------------------------------------------+ $Id$ */ + +/** + * Obtain the PEAR::DB class that is used for abstraction + */ require_once('MDB2.php'); + +/** + * Database independent query interface + * + * This is a wrapper for the PEAR::MDB2 class + * + * @package RoundCube Webmail + * @author David Saez Padros + * @author Thomas Bruederli + * @author Lukas Kahwe Smith + * @version 1.16 + * @link http://pear.php.net/package/MDB2 + */ class rcube_db -{ - var $db_dsnw; // DSN for write operations - var $db_dsnr; // DSN for read operations - var $db_connected=false; // Already connected ? - var $db_mode=''; // Connection mode - var $db_handle=0; // Connection handle - - var $a_query_results = array('dummy'); - var $last_res_id = 0; - - // PHP 5 constructor - function __construct($db_dsnw,$db_dsnr='') + { + var $db_dsnw; // DSN for write operations + var $db_dsnr; // DSN for read operations + var $db_connected = false; // Already connected ? + var $db_mode = ''; // Connection mode + var $db_handle = 0; // Connection handle + var $db_error = false; + var $db_error_msg = ''; + + var $a_query_results = array('dummy'); + var $last_res_id = 0; + + + /** + * Object constructor + * + * @param string DSN for read/write operations + * @param string Optional DSN for read only operations + */ + function __construct($db_dsnw, $db_dsnr='') { - if ($db_dsnr=='') $db_dsnr=$db_dsnw; - - $this->db_dsnw = $db_dsnw; - $this->db_dsnr = $db_dsnr; - - $dsn_array = MDB2::parseDSN($db_dsnw); - $this->db_provider = $dsn_array['phptype']; + if ($db_dsnr=='') + $db_dsnr=$db_dsnw; + + $this->db_dsnw = $db_dsnw; + $this->db_dsnr = $db_dsnr; + + $dsn_array = MDB2::parseDSN($db_dsnw); + $this->db_provider = $dsn_array['phptype']; } - // PHP 4 compatibility - function rcube_db($db_dsnw,$db_dsnr='') + + /** + * PHP 4 object constructor + * + * @see rcube_MDB2::__construct + */ + function rcube_db($db_dsnw,$db_dsnr='') { - $this->__construct($db_dsnw,$db_dsnr); + $this->__construct($db_dsnw,$db_dsnr); } - // Connect to specific database - function dsn_connect($dsn) + + /** + * Connect to specific database + * + * @param string DSN for DB connections + * @return object PEAR database handle + * @access private + */ + function dsn_connect($dsn) { - // Use persistent connections if available - $dbh = MDB2::factory($dsn, array('persistent' => $true)); - - if (PEAR::isError($dbh)) - raise_error(array('code' => 500, - 'type' => 'db', - 'line' => __LINE__, - 'file' => __FILE__, + // Use persistent connections if available + $dbh = MDB2::connect($dsn, array('persistent' => TRUE, 'portability' => MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_EMPTY_TO_NULL)); + + if (PEAR::isError($dbh)) + { + $this->db_error = TRUE; + $this->db_error_msg = $dbh->getMessage(); + + raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__, 'message' => $dbh->getMessage()), TRUE, FALSE); + } - else if ($this->db_provider=='sqlite') - { - $dsn_array = MDB2::parseDSN($dsn); - if (!filesize($dsn_array['database']) && !empty($this->sqlite_initials)) - $this->_sqlite_create_database($dbh, $this->sqlite_initials); - } - - return $dbh; + else if ($this->db_provider=='sqlite') + { + $dsn_array = MDB2::parseDSN($dsn); + if (!filesize($dsn_array['database']) && !empty($this->sqlite_initials)) + $this->_sqlite_create_database($dbh, $this->sqlite_initials); + } + + return $dbh; + } + + + /** + * Connect to appropiate databse + * depending on the operation + * + * @param string Connection mode (r|w) + * @access public + */ + function db_connect($mode) + { + $this->db_mode = $mode; + + // Already connected + if ($this->db_connected) + { + // no replication, current connection is ok + if ($this->db_dsnw==$this->db_dsnr) + return; + + // connected to master, current connection is ok + if ($this->db_mode=='w') + return; + + // Same mode, current connection is ok + if ($this->db_mode==$mode) + return; + } + + if ($mode=='r') + $dsn = $this->db_dsnr; + else + $dsn = $this->db_dsnw; + + $this->db_handle = $this->dsn_connect($dsn); + $this->db_connected = true; } - // Connect to appropiate databse - function db_connect ($mode) + + + /** + * Getter for error state + * + * @param boolean True on error + */ + function is_error() { - $this->db_mode = $mode; - - // Already connected - if ($this->db_connected) - { - // no replication, current connection is ok - if ($this->db_dsnw==$this->db_dsnr) return; - - // connected to master, current connection is ok - if ($this->db_mode=='w') return; - - // Same mode, current connection is ok - if ($this->db_mode==$mode) return; - } - - if ($mode=='r') - $dsn=$this->db_dsnr; - else - $dsn=$this->db_dsnw; - - $this->db_handle = $this->dsn_connect($dsn); - $this->db_connected = true; + return $this->db_error ? $this->db_error_msg : FALSE; } + - // Query database - function query() + /** + * Execute a SQL query + * + * @param string SQL query to execute + * @param mixed Values to be inserted in query + * @return number Query handle identifier + * @access public + */ + function query() { - $params = func_get_args(); - $query = array_shift($params); + $params = func_get_args(); + $query = array_shift($params); - return $this->_query($query, 0, 0, $params); + return $this->_query($query, 0, 0, $params); } - function limitquery() + /** + * Execute a SQL query with limits + * + * @param string SQL query to execute + * @param number Offset for LIMIT statement + * @param number Number of rows for LIMIT statement + * @param mixed Values to be inserted in query + * @return number Query handle identifier + * @access public + */ + function limitquery() { + $params = func_get_args(); + $query = array_shift($params); + $offset = array_shift($params); + $numrows = array_shift($params); - $params = func_get_args(); - $query = array_shift($params); - $offset = array_shift($params); - $numrows = array_shift($params); - - return $this->_query($query, $offset, $numrows, $params); + return $this->_query($query, $offset, $numrows, $params); } - - function _query($query, $offset, $numrows, $params) + + + /** + * Execute a SQL query with limits + * + * @param string SQL query to execute + * @param number Offset for LIMIT statement + * @param number Number of rows for LIMIT statement + * @param array Values to be inserted in query + * @return number Query handle identifier + * @access private + */ + function _query($query, $offset, $numrows, $params) { - // Read or write ? - if (strtolower(trim(substr($query,0,6)))=='select') - $mode='r'; - else - $mode='w'; - - $this->db_connect($mode); - - if ($this->db_provider == 'sqlite') - $query = $this->_sqlite_prepare_query($query); - - $this->db_handle->row_offset = $offset; - $this->db_handle->row_limit = $numrows; - - //$result = $this->db_handle->query($query,$params); - $q = $this->db_handle->prepare($query); - $q->bindParamArray($params); - $result = $q->execute(); - - if (PEAR::isError($result)) - raise_error(array('code' => 500, - 'type' => 'db', - 'line' => __LINE__, - 'file' => __FILE__, - 'message' => $result->getMessage()), TRUE, FALSE); - - return $this->_add_result($result, $query); + // Read or write ? + if (strtolower(trim(substr($query,0,6)))=='select') + $mode='r'; + else + $mode='w'; + + $this->db_connect($mode); + + if ($this->db_provider == 'sqlite') + $this->_sqlite_prepare(); + + if ($numrows || $offset) + $result = $this->db_handle->setLimit($numrows,$offset); + + if (empty($params)) + $result = $this->db_handle->query($query); + else + { + $params = (array)$params; + $q = $this->db_handle->prepare($query); + if ($this->db_handle->isError($q)) + { + $this->db_error = TRUE; + $this->db_error_msg = $q->userinfo; + + raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__, + 'message' => $this->db_error_msg), TRUE, TRUE); + } + else + { + $result = $q->execute($params); + $q->free(); + } + } + + // add result, even if it's an error + return $this->_add_result($result); } - function num_rows($res_id=NULL) + /** + * Get number of rows for a SQL query + * If no query handle is specified, the last query will be taken as reference + * + * @param number Optional query handle identifier + * @return mixed Number of rows or FALSE on failure + * @access public + */ + function num_rows($res_id=NULL) { - if (!$this->db_handle) - return FALSE; + if (!$this->db_handle) + return FALSE; - $result = $this->_get_result($res_id); - - if ($result) - return $result->numRows(); - else - return FALSE; + if ($result = $this->_get_result($res_id)) + return $result->numRows(); + else + return FALSE; } - function affected_rows($res_id=NULL) + /** + * Get number of affected rows fort he last query + * + * @return mixed Number of rows or FALSE on failure + * @access public + */ + function affected_rows($result = null) { - if (!$this->db_handle) - return FALSE; - - return $this->db_handle->affectedRows(); + if (!$this->db_handle) + return FALSE; + + return $result; } - function insert_id($sequence = '') + /** + * Get last inserted record ID + * For Postgres databases, a sequence name is required + * + * @param string Sequence name for increment + * @return mixed ID or FALSE on failure + * @access public + */ + function insert_id($sequence = '') { - if (!$this->db_handle || $this->db_mode=='r') - return FALSE; + if (!$this->db_handle || $this->db_mode=='r') + return FALSE; - return $this->db_handle->lastInsertID(); + return $this->db_handle->lastInsertID($sequence); } - function fetch_assoc($res_id=NULL) + /** + * Get an associative array for one row + * If no query handle is specified, the last query will be taken as reference + * + * @param number Optional query handle identifier + * @return mixed Array with col values or FALSE on failure + * @access public + */ + function fetch_assoc($res_id=NULL) { - $result = $this->_get_result($res_id); - - if (PEAR::isError($result)) - { - raise_error( array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__, - 'message' => $this->db_link->getMessage()), TRUE, FALSE); - return FALSE; - } - - return $result->fetchRow(MDB2_FETCHMODE_ASSOC); + $result = $this->_get_result($res_id); + return $this->_fetch_row($result, MDB2_FETCHMODE_ASSOC); } - function quote($input, $type=null) + /** + * Get an index array for one row + * If no query handle is specified, the last query will be taken as reference + * + * @param number Optional query handle identifier + * @return mixed Array with col values or FALSE on failure + * @access public + */ + function fetch_array($res_id=NULL) { - if (!$this->db_handle) - $this->db_connect('r'); + $result = $this->_get_result($res_id); + return $this->_fetch_row($result, MDB2_FETCHMODE_ORDERED); + } + - return $this->db_handle->quote($input, $type); + /** + * Get co values for a result row + * + * @param object Query result handle + * @param number Fetch mode identifier + * @return mixed Array with col values or FALSE on failure + * @access private + */ + function _fetch_row($result, $mode) + { + if (PEAR::isError($result)) + { + raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__, + 'message' => $this->db_link->getMessage()), TRUE, FALSE); + return FALSE; + } + + return $result->fetchRow($mode); } - function quoteIdentifier($str) - { - if (!$this->db_handle) - $this->db_connect('r'); + /** + * Formats input so it can be safely used in a query + * + * @param mixed Value to quote + * @return string Quoted/converted string for use in query + * @access public + */ + function quote($input, $type = null) + { + // create DB handle if not available + if (!$this->db_handle) + $this->db_connect('r'); - return $this->db_handle->quoteIdentifier($str); - } + // escape pear identifier chars + $rep_chars = array('?' => '\?', + '!' => '\!', + '&' => '\&'); - function quote_identifier($str) - { - return $this->quoteIdentifier($str); - } + return $this->db_handle->quote($input, $type); + } - function unixtimestamp($field) + /** + * Quotes a string so it can be safely used as a table or column name + * + * @param string Value to quote + * @return string Quoted string for use in query + * @deprecated Replaced by rcube_MDB2::quote_identifier + * @see rcube_MDB2::quote_identifier + * @access public + */ + function quoteIdentifier($str) { - switch($this->db_provider) - { - case 'pgsql': - return "EXTRACT (EPOCH FROM $field)"; - break; - - default: - return "UNIX_TIMESTAMP($field)"; - } + return $this->quote_identifier($str); } - function format_date($timestamp) - { - switch($this->db_provider) - { - case 'mysqli': - case 'mysql': - return "FROM_UNIXTIME($timestamp)"; - break; - case 'sqlite': - return "datetime('$timestamp')"; - break; - default: - return date("Y-m-d H:i:s", $timestamp); - } - } - - function _add_result($res, $query) + /** + * Quotes a string so it can be safely used as a table or column name + * + * @param string Value to quote + * @return string Quoted string for use in query + * @access public + */ + function quote_identifier($str) { - // sql error occured - if (PEAR::isError($res)) - { - raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__, 'message' => $res->getMessage() . " Query: " . substr(preg_replace('/[\r\n]+\s*/', ' ', $query), 0, 1024)), TRUE, FALSE); - return FALSE; - } - else - { - $res_id = sizeof($this->a_query_results); - $this->a_query_results[$res_id] = $res; - $this->last_res_id = $res_id; - return $res_id; - } + if (!$this->db_handle) + $this->db_connect('r'); + + return $this->db_handle->quoteIdentifier($str); } - function _get_result($res_id) + /** + * Return SQL statement to convert a field value into a unix timestamp + * + * @param string Field name + * @return string SQL statement to use in query + * @access public + */ + function unixtimestamp($field) { - if ($res_id==NULL) - $res_id = $this->last_res_id; - - if ($res_id && isset($this->a_query_results[$res_id])) - return $this->a_query_results[$res_id]; - else - return FALSE; + switch($this->db_provider) + { + case 'pgsql': + return "EXTRACT (EPOCH FROM $field)"; + break; + + default: + return "UNIX_TIMESTAMP($field)"; + } } - // create a sqlite database from a file - function _sqlite_create_database($dbh, $fileName) + /** + * Return SQL statement to convert from a unix timestamp + * + * @param string Field name + * @return string SQL statement to use in query + * @access public + */ + function fromunixtime($timestamp) { - if (empty($fileName) || !is_string($fileName)) - return ; - - $data = ''; - if ($fd = fopen($fileName, 'r')) - { - $data = fread($fd, filesize($fileName)); - fclose($fd); - } - - if (strlen($data)) - sqlite_exec($dbh->connection, $data); + switch($this->db_provider) + { + case 'mysqli': + case 'mysql': + case 'sqlite': + return "FROM_UNIXTIME($timestamp)"; + + default: + return date("'Y-m-d H:i:s'", $timestamp); + } } - // transform a query so that it is sqlite2 compliant - function _sqlite_prepare_query($query) + + /** + * Adds a query result and returns a handle ID + * + * @param object Query handle + * @return mixed Handle ID or FALE on failure + * @access private + */ + function _add_result($res) { - if (!is_string($query)) - return ($query); + // sql error occured + if (PEAR::isError($res)) + { + raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__, + 'message' => $res->getMessage() . " Query: " . substr(preg_replace('/[\r\n]+\s*/', ' ', $res->userinfo), 0, 512)), TRUE, FALSE); + return FALSE; + } + else + { + $res_id = sizeof($this->a_query_results); + $this->a_query_results[$res_id] = $res; + $this->last_res_id = $res_id; + return $res_id; + } + } + - $search = array('/NOW\(\)/i', '/`/'); - $replace = array("datetime('now')", '"'); - $query = preg_replace($search, $replace, $query); + /** + * Resolves a given handle ID and returns the according query handle + * If no ID is specified, the last ressource handle will be returned + * + * @param number Handle ID + * @return mixed Ressource handle or FALE on failure + * @access private + */ + function _get_result($res_id=NULL) + { + if ($res_id==NULL) + $res_id = $this->last_res_id; - return ($query); + if ($res_id && isset($this->a_query_results[$res_id])) + return $this->a_query_results[$res_id]; + else + return FALSE; } - -} + + + /** + * Create a sqlite database from a file + * + * @param object SQLite database handle + * @param string File path to use for DB creation + * @access private + */ + function _sqlite_create_database($dbh, $file_name) + { + if (empty($file_name) || !is_string($file_name)) + return; + + $data = ''; + if ($fd = fopen($file_name, 'r')) + { + $data = fread($fd, filesize($file_name)); + fclose($fd); + } + + if (strlen($data)) + sqlite_exec($dbh->connection, $data); + } + + + /** + * Add some proprietary database functions to the current SQLite handle + * in order to make it MySQL compatible + * + * @access private + */ + function _sqlite_prepare() + { + include_once('include/rcube_sqlite.inc'); + + // we emulate via callback some missing MySQL function + sqlite_create_function($this->db_handle->connection, "from_unixtime", "rcube_sqlite_from_unixtime"); + sqlite_create_function($this->db_handle->connection, "unix_timestamp", "rcube_sqlite_unix_timestamp"); + sqlite_create_function($this->db_handle->connection, "now", "rcube_sqlite_now"); + sqlite_create_function($this->db_handle->connection, "md5", "rcube_sqlite_md5"); + } + + + } // end class rcube_db ?> \ No newline at end of file -- cgit v1.2.3