githubFork/roundcubemail.git

alecpl

2010-09-25 e019f2d0f2dc2fbfa345ab5d7ae85e67bfdd76b8

commit \| author \| age
f45ec7	1	<?php
S	2
	3	/*
	4	+-----------------------------------------------------------------------+
47124c	5	\| program/include/rcube_mdb2.php \|
f45ec7	6	\| \|
e019f2	7	\| This file is part of the Roundcube Webmail client \|
A	8	\| Copyright (C) 2005-2009, Roundcube Dev. - Switzerland \|
f45ec7	9	\| Licensed under the GNU GPL \|
S	10	\| \|
	11	\| PURPOSE: \|
d2a9db	12	\| PEAR:DB wrapper class that implements PEAR MDB2 functions \|
T	13	\| See http://pear.php.net/package/MDB2 \|
f45ec7	14	\| \|
S	15	+-----------------------------------------------------------------------+
d2a9db	16	\| Author: Lukas Kahwe Smith <smith@pooteeweet.org> \|
f45ec7	17	+-----------------------------------------------------------------------+
S	18
	19	$Id$
	20
	21	*/
	22
d2a9db	23
T	24	/**
	25	* Database independent query interface
	26	*
	27	* This is a wrapper for the PEAR::MDB2 class
	28	*
6d969b	29	* @package Database
d2a9db	30	* @author David Saez Padros <david@ols.es>
T	31	* @author Thomas Bruederli <roundcube@gmail.com>
	32	* @author Lukas Kahwe Smith <smith@pooteeweet.org>
a3de4f	33	* @version 1.17
d2a9db	34	* @link http://pear.php.net/package/MDB2
T	35	*/
6d969b	36	class rcube_mdb2
a004bb	37	{
A	38	var $db_dsnw; // DSN for write operations
	39	var $db_dsnr; // DSN for read operations
	40	var $db_connected = false; // Already connected ?
	41	var $db_mode = ''; // Connection mode
	42	var $db_handle = 0; // Connection handle
	43	var $db_error = false;
	44	var $db_error_msg = '';
f45ec7	45
a004bb	46	private $debug_mode = false;
A	47	private $a_query_results = array('dummy');
	48	private $last_res_id = 0;
	49	private $tables;
f45ec7	50
d2a9db	51
a004bb	52	/**
A	53	* Object constructor
	54	*
	55	* @param string DSN for read/write operations
	56	* @param string Optional DSN for read only operations
	57	*/
	58	function __construct($db_dsnw, $db_dsnr='', $pconn=false)
f45ec7	59	{
a004bb	60	if ($db_dsnr == '')
A	61	$db_dsnr = $db_dsnw;
d2a9db	62
a004bb	63	$this->db_dsnw = $db_dsnw;
A	64	$this->db_dsnr = $db_dsnr;
	65	$this->db_pconn = $pconn;
05a7e3	66
a004bb	67	$dsn_array = MDB2::parseDSN($db_dsnw);
A	68	$this->db_provider = $dsn_array['phptype'];
f45ec7	69	}
S	70
d2a9db	71
a004bb	72	/**
A	73	* Connect to specific database
	74	*
	75	* @param string DSN for DB connections
	76	* @return object PEAR database handle
	77	* @access private
	78	*/
	79	private function dsn_connect($dsn)
f45ec7	80	{
a004bb	81	// Use persistent connections if available
A	82	$db_options = array(
	83	'persistent' => $this->db_pconn,
	84	'emulate_prepared' => $this->debug_mode,
	85	'debug' => $this->debug_mode,
	86	'debug_handler' => 'mdb2_debug_handler',
05a7e3	87	'portability' => MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_EMPTY_TO_NULL);
9f23f0	88
a004bb	89	if ($this->db_provider == 'pgsql') {
A	90	$db_options['disable_smart_seqname'] = true;
	91	$db_options['seqname_format'] = '%s';
	92	}
9f23f0	93
a004bb	94	$dbh = MDB2::connect($dsn, $db_options);
d2a9db	95
a004bb	96	if (MDB2::isError($dbh)) {
A	97	$this->db_error = true;
	98	$this->db_error_msg = $dbh->getMessage();
05a7e3	99
a004bb	100	raise_error(array('code' => 500, 'type' => 'db',
A	101	'line' => __LINE__, 'file' => __FILE__,
	102	'message' => $dbh->getUserInfo()), true, false);
f45ec7	103	}
a004bb	104	else if ($this->db_provider == 'sqlite') {
A	105	$dsn_array = MDB2::parseDSN($dsn);
	106	if (!filesize($dsn_array['database']) && !empty($this->sqlite_initials))
	107	$this->_sqlite_create_database($dbh, $this->sqlite_initials);
f45ec7	108	}
a004bb	109	else if ($this->db_provider!='mssql' && $this->db_provider!='sqlsrv')
A	110	$dbh->setCharset('utf8');
d2a9db	111
a004bb	112	return $dbh;
f45ec7	113	}
S	114
ccfda8	115
a004bb	116	/**
A	117	* Connect to appropiate database depending on the operation
	118	*
	119	* @param string Connection mode (r\|w)
	120	* @access public
	121	*/
	122	function db_connect($mode)
10a699	123	{
a004bb	124	// Already connected
A	125	if ($this->db_connected) {
cb2bc8	126	// no replication, current connection is ok for read and write
T	127	if (empty($this->db_dsnr) \|\| $this->db_dsnw == $this->db_dsnr) {
	128	$this->db_mode = 'w';
a004bb	129	return;
cb2bc8	130	}
a004bb	131
a3de4f	132	// connected to read-write db, current connection is ok
a004bb	133	if ($this->db_mode == 'w')
A	134	return;
	135
	136	// Same mode, current connection is ok
	137	if ($this->db_mode == $mode)
	138	return;
	139	}
	140
	141	$dsn = ($mode == 'r') ? $this->db_dsnr : $this->db_dsnw;
	142
	143	$this->db_handle = $this->dsn_connect($dsn);
a3de4f	144	$this->db_connected = !PEAR::isError($this->db_handle);
T	145	$this->db_mode = $mode;
10a699	146	}
T	147
	148
a004bb	149	/**
A	150	* Activate/deactivate debug mode
	151	*
	152	* @param boolean True if SQL queries should be logged
	153	* @access public
	154	*/
	155	function set_debug($dbg = true)
d2a9db	156	{
a004bb	157	$this->debug_mode = $dbg;
A	158	if ($this->db_connected) {
	159	$this->db_handle->setOption('debug', $dbg);
	160	$this->db_handle->setOption('emulate_prepared', $dbg);
	161	}
d2a9db	162	}
ccfda8	163
d2a9db	164
a004bb	165	/**
A	166	* Getter for error state
	167	*
	168	* @param boolean True on error
	169	* @access public
	170	*/
	171	function is_error()
d2a9db	172	{
a004bb	173	return $this->db_error ? $this->db_error_msg : false;
e1ac21	174	}
a004bb	175
A	176
	177	/**
	178	* Connection state checker
	179	*
	180	* @param boolean True if in connected state
	181	* @access public
	182	*/
	183	function is_connected()
	184	{
	185	return PEAR::isError($this->db_handle) ? false : $this->db_connected;
	186	}
	187
	188
	189	/**
	190	* Execute a SQL query
	191	*
	192	* @param string SQL query to execute
	193	* @param mixed Values to be inserted in query
	194	* @return number Query handle identifier
	195	* @access public
	196	*/
	197	function query()
	198	{
	199	$params = func_get_args();
	200	$query = array_shift($params);
	201
	202	return $this->_query($query, 0, 0, $params);
	203	}
	204
	205
	206	/**
	207	* Execute a SQL query with limits
	208	*
	209	* @param string SQL query to execute
	210	* @param number Offset for LIMIT statement
	211	* @param number Number of rows for LIMIT statement
	212	* @param mixed Values to be inserted in query
	213	* @return number Query handle identifier
	214	* @access public
	215	*/
	216	function limitquery()
	217	{
	218	$params = func_get_args();
	219	$query = array_shift($params);
	220	$offset = array_shift($params);
	221	$numrows = array_shift($params);
	222
	223	return $this->_query($query, $offset, $numrows, $params);
	224	}
	225
	226
	227	/**
	228	* Execute a SQL query with limits
	229	*
	230	* @param string SQL query to execute
	231	* @param number Offset for LIMIT statement
	232	* @param number Number of rows for LIMIT statement
	233	* @param array Values to be inserted in query
	234	* @return number Query handle identifier
	235	* @access private
	236	*/
	237	private function _query($query, $offset, $numrows, $params)
	238	{
	239	// Read or write ?
	240	$mode = (strtolower(substr(trim($query),0,6)) == 'select') ? 'r' : 'w';
	241
	242	$this->db_connect($mode);
	243
a3de4f	244	// check connection before proceeding
T	245	if (!$this->is_connected())
	246	return null;
	247
a004bb	248	if ($this->db_provider == 'sqlite')
A	249	$this->_sqlite_prepare();
	250
	251	if ($numrows \|\| $offset)
	252	$result = $this->db_handle->setLimit($numrows,$offset);
	253
	254	if (empty($params))
	255	$result = $mode == 'r' ? $this->db_handle->query($query) : $this->db_handle->exec($query);
	256	else {
	257	$params = (array)$params;
	258	$q = $this->db_handle->prepare($query, null, $mode=='w' ? MDB2_PREPARE_MANIP : null);
	259	if ($this->db_handle->isError($q)) {
	260	$this->db_error = true;
	261	$this->db_error_msg = $q->userinfo;
	262
	263	raise_error(array('code' => 500, 'type' => 'db',
	264	'line' => __LINE__, 'file' => __FILE__,
	265	'message' => $this->db_error_msg), true, true);
	266	}
	267	else {
	268	$result = $q->execute($params);
	269	$q->free();
	270	}
	271	}
	272
	273	// add result, even if it's an error
	274	return $this->_add_result($result);
	275	}
	276
	277
	278	/**
	279	* Get number of rows for a SQL query
	280	* If no query handle is specified, the last query will be taken as reference
	281	*
	282	* @param number Optional query handle identifier
	283	* @return mixed Number of rows or false on failure
	284	* @access public
	285	*/
	286	function num_rows($res_id=null)
	287	{
	288	if (!$this->db_handle)
	289	return false;
	290
	291	if ($result = $this->_get_result($res_id))
	292	return $result->numRows();
	293	else
	294	return false;
	295	}
	296
	297
	298	/**
	299	* Get number of affected rows for the last query
	300	*
	301	* @param number Optional query handle identifier
	302	* @return mixed Number of rows or false on failure
	303	* @access public
	304	*/
	305	function affected_rows($res_id = null)
	306	{
	307	if (!$this->db_handle)
	308	return false;
	309
	310	return (int) $this->_get_result($res_id);
	311	}
	312
	313
	314	/**
	315	* Get last inserted record ID
	316	* For Postgres databases, a sequence name is required
	317	*
	318	* @param string Table name (to find the incremented sequence)
	319	* @return mixed ID or false on failure
	320	* @access public
	321	*/
	322	function insert_id($table = '')
	323	{
	324	if (!$this->db_handle \|\| $this->db_mode == 'r')
	325	return false;
	326
	327	if ($table) {
	328	if ($this->db_provider == 'pgsql')
	329	// find sequence name
	330	$table = get_sequence_name($table);
	331	else
	332	// resolve table name
	333	$table = get_table_name($table);
	334	}
	335
	336	$id = $this->db_handle->lastInsertID($table);
05a7e3	337
a004bb	338	return $this->db_handle->isError($id) ? null : $id;
d2a9db	339	}
T	340
	341
a004bb	342	/**
A	343	* Get an associative array for one row
	344	* If no query handle is specified, the last query will be taken as reference
	345	*
	346	* @param number Optional query handle identifier
	347	* @return mixed Array with col values or false on failure
	348	* @access public
	349	*/
	350	function fetch_assoc($res_id=null)
d2a9db	351	{
a004bb	352	$result = $this->_get_result($res_id);
A	353	return $this->_fetch_row($result, MDB2_FETCHMODE_ASSOC);
d2a9db	354	}
T	355
	356
a004bb	357	/**
A	358	* Get an index array for one row
	359	* If no query handle is specified, the last query will be taken as reference
	360	*
	361	* @param number Optional query handle identifier
	362	* @return mixed Array with col values or false on failure
	363	* @access public
	364	*/
	365	function fetch_array($res_id=null)
d2a9db	366	{
a004bb	367	$result = $this->_get_result($res_id);
A	368	return $this->_fetch_row($result, MDB2_FETCHMODE_ORDERED);
d2a9db	369	}
T	370
	371
a004bb	372	/**
A	373	* Get col values for a result row
	374	*
	375	* @param object Query result handle
	376	* @param number Fetch mode identifier
	377	* @return mixed Array with col values or false on failure
	378	* @access private
	379	*/
	380	private function _fetch_row($result, $mode)
d2a9db	381	{
a004bb	382	if ($result === false \|\| PEAR::isError($result) \|\| !$this->is_connected())
A	383	return false;
d2a9db	384
a004bb	385	return $result->fetchRow($mode);
d2a9db	386	}
T	387
	388
a004bb	389	/**
A	390	* Wrapper for the SHOW TABLES command
	391	*
	392	* @return array List of all tables of the current database
	393	* @access public
	394	* @since 0.4-beta
	395	*/
	396	function list_tables()
d2a9db	397	{
a004bb	398	// get tables if not cached
A	399	if (!$this->tables) {
	400	$this->db_handle->loadModule('Manager');
	401	if (!PEAR::isError($result = $this->db_handle->listTables()))
	402	$this->tables = $result;
	403	else
	404	$this->tables = array();
	405	}
d2a9db	406
a004bb	407	return $this->tables;
d2a9db	408	}
T	409
	410
a004bb	411	/**
A	412	* Formats input so it can be safely used in a query
	413	*
	414	* @param mixed Value to quote
	415	* @param string Type of data
	416	* @return string Quoted/converted string for use in query
	417	* @access public
	418	*/
	419	function quote($input, $type = null)
9c5bee	420	{
a004bb	421	// handle int directly for better performance
A	422	if ($type == 'integer')
	423	return intval($input);
	424
	425	// create DB handle if not available
	426	if (!$this->db_handle)
	427	$this->db_connect('r');
	428
	429	return $this->db_handle->quote($input, $type);
9c5bee	430	}
ccfda8	431
10a699	432
a004bb	433	/**
A	434	* Quotes a string so it can be safely used as a table or column name
	435	*
	436	* @param string Value to quote
	437	* @return string Quoted string for use in query
	438	* @deprecated Replaced by rcube_MDB2::quote_identifier
	439	* @see rcube_mdb2::quote_identifier
	440	* @access public
	441	*/
	442	function quoteIdentifier($str)
f45ec7	443	{
a004bb	444	return $this->quote_identifier($str);
f45ec7	445	}
S	446
a004bb	447
A	448	/**
	449	* Quotes a string so it can be safely used as a table or column name
	450	*
	451	* @param string Value to quote
	452	* @return string Quoted string for use in query
	453	* @access public
	454	*/
	455	function quote_identifier($str)
8f4dcb	456	{
a004bb	457	if (!$this->db_handle)
A	458	$this->db_connect('r');
	459
	460	return $this->db_handle->quoteIdentifier($str);
	461	}
	462
	463
	464	/**
	465	* Escapes a string
	466	*
	467	* @param string The string to be escaped
	468	* @return string The escaped string
	469	* @access public
	470	* @since 0.1.1
	471	*/
	472	function escapeSimple($str)
	473	{
	474	if (!$this->db_handle)
	475	$this->db_connect('r');
05a7e3	476
a004bb	477	return $this->db_handle->escape($str);
8f4dcb	478	}
T	479
f45ec7	480
a004bb	481	/**
A	482	* Return SQL function for current time and date
	483	*
	484	* @return string SQL function to use in query
	485	* @access public
	486	*/
	487	function now()
7139e3	488	{
a004bb	489	switch($this->db_provider) {
A	490	case 'mssql':
	491	case 'sqlsrv':
	492	return "getdate()";
7139e3	493
a004bb	494	default:
A	495	return "now()";
	496	}
7139e3	497	}
T	498
	499
a004bb	500	/**
A	501	* Return list of elements for use with SQL's IN clause
	502	*
	503	* @param array Input array
	504	* @param string Type of data
	505	* @return string Comma-separated list of quoted values for use in query
	506	* @access public
	507	*/
	508	function array2list($arr, $type = null)
ad84f9	509	{
a004bb	510	if (!is_array($arr))
A	511	return $this->quote($arr, $type);
05a7e3	512
a004bb	513	foreach ($arr as $idx => $item)
A	514	$arr[$idx] = $this->quote($item, $type);
ad84f9	515
a004bb	516	return implode(',', $arr);
ad84f9	517	}
A	518
	519
a004bb	520	/**
A	521	* Return SQL statement to convert a field value into a unix timestamp
	522	*
	523	* @param string Field name
	524	* @return string SQL statement to use in query
	525	* @access public
	526	*/
	527	function unixtimestamp($field)
f45ec7	528	{
a004bb	529	switch($this->db_provider) {
A	530	case 'pgsql':
	531	return "EXTRACT (EPOCH FROM $field)";
d2a9db	532
a004bb	533	case 'mssql':
A	534	case 'sqlsrv':
05a7e3	535	return "DATEDIFF(second, '19700101', $field) + DATEDIFF(second, GETDATE(), GETUTCDATE())";
7139e3	536
a004bb	537	default:
A	538	return "UNIX_TIMESTAMP($field)";
	539	}
f45ec7	540	}
S	541
	542
a004bb	543	/**
A	544	* Return SQL statement to convert from a unix timestamp
	545	*
	546	* @param string Field name
	547	* @return string SQL statement to use in query
	548	* @access public
	549	*/
	550	function fromunixtime($timestamp)
f45ec7	551	{
a004bb	552	switch($this->db_provider) {
A	553	case 'mysqli':
	554	case 'mysql':
	555	case 'sqlite':
	556	return sprintf("FROM_UNIXTIME(%d)", $timestamp);
f45ec7	557
a004bb	558	default:
A	559	return date("'Y-m-d H:i:s'", $timestamp);
	560	}
f45ec7	561	}
S	562
d2a9db	563
a004bb	564	/**
A	565	* Return SQL statement for case insensitive LIKE
	566	*
	567	* @param string Field name
	568	* @param string Search value
	569	* @return string SQL statement to use in query
	570	* @access public
	571	*/
	572	function ilike($column, $value)
d8d416	573	{
a004bb	574	// TODO: use MDB2's matchPattern() function
A	575	switch($this->db_provider) {
	576	case 'pgsql':
	577	return $this->quote_identifier($column).' ILIKE '.$this->quote($value);
	578	default:
	579	return $this->quote_identifier($column).' LIKE '.$this->quote($value);
	580	}
d8d416	581	}
A	582
	583
a004bb	584	/**
A	585	* Encodes non-UTF-8 characters in string/array/object (recursive)
	586	*
	587	* @param mixed Data to fix
	588	* @return mixed Properly UTF-8 encoded data
	589	* @access public
	590	*/
	591	function encode($input)
ac6229	592	{
a004bb	593	if (is_object($input)) {
A	594	foreach (get_object_vars($input) as $idx => $value)
	595	$input->$idx = $this->encode($value);
	596	return $input;
	597	}
	598	else if (is_array($input)) {
	599	foreach ($input as $idx => $value)
	600	$input[$idx] = $this->encode($value);
	601	return $input;
	602	}
ac6229	603
a004bb	604	return utf8_encode($input);
ac6229	605	}
A	606
	607
a004bb	608	/**
A	609	* Decodes encoded UTF-8 string/object/array (recursive)
	610	*
	611	* @param mixed Input data
	612	* @return mixed Decoded data
	613	* @access public
	614	*/
	615	function decode($input)
ac6229	616	{
a004bb	617	if (is_object($input)) {
A	618	foreach (get_object_vars($input) as $idx => $value)
	619	$input->$idx = $this->decode($value);
	620	return $input;
	621	}
	622	else if (is_array($input)) {
	623	foreach ($input as $idx => $value)
	624	$input[$idx] = $this->decode($value);
	625	return $input;
	626	}
ac6229	627
a004bb	628	return utf8_decode($input);
ac6229	629	}
A	630
	631
a004bb	632	/**
A	633	* Adds a query result and returns a handle ID
	634	*
	635	* @param object Query handle
	636	* @return mixed Handle ID
	637	* @access private
	638	*/
	639	private function _add_result($res)
f45ec7	640	{
a004bb	641	// sql error occured
A	642	if (PEAR::isError($res)) {
	643	$this->db_error = true;
	644	$this->db_error_msg = $res->getMessage();
	645	raise_error(array('code' => 500, 'type' => 'db',
05a7e3	646	'line' => __LINE__, 'file' => __FILE__,
A	647	'message' => $res->getMessage() . " Query: "
	648	. substr(preg_replace('/[\r\n]+\s*/', ' ', $res->userinfo), 0, 512)),
	649	true, false);
a004bb	650	}
05a7e3	651
a004bb	652	$res_id = sizeof($this->a_query_results);
A	653	$this->last_res_id = $res_id;
	654	$this->a_query_results[$res_id] = $res;
	655	return $res_id;
f45ec7	656	}
d2a9db	657
T	658
a004bb	659	/**
A	660	* Resolves a given handle ID and returns the according query handle
	661	* If no ID is specified, the last resource handle will be returned
	662	*
	663	* @param number Handle ID
	664	* @return mixed Resource handle or false on failure
	665	* @access private
	666	*/
	667	private function _get_result($res_id = null)
d2a9db	668	{
a004bb	669	if ($res_id == null)
A	670	$res_id = $this->last_res_id;
d2a9db	671
a004bb	672	if (isset($this->a_query_results[$res_id]))
A	673	if (!PEAR::isError($this->a_query_results[$res_id]))
	674	return $this->a_query_results[$res_id];
05a7e3	675
a004bb	676	return false;
d2a9db	677	}
T	678
	679
a004bb	680	/**
A	681	* Create a sqlite database from a file
	682	*
	683	* @param object SQLite database handle
	684	* @param string File path to use for DB creation
	685	* @access private
	686	*/
	687	private function _sqlite_create_database($dbh, $file_name)
d2a9db	688	{
a004bb	689	if (empty($file_name) \|\| !is_string($file_name))
A	690	return;
d2a9db	691
a004bb	692	$data = file_get_contents($file_name);
d2a9db	693
a004bb	694	if (strlen($data))
A	695	if (!sqlite_exec($dbh->connection, $data, $error) \|\| MDB2::isError($dbh))
	696	raise_error(array('code' => 500, 'type' => 'db',
05a7e3	697	'line' => __LINE__, 'file' => __FILE__,
a004bb	698	'message' => $error), true, false);
d2a9db	699	}
T	700
	701
a004bb	702	/**
A	703	* Add some proprietary database functions to the current SQLite handle
	704	* in order to make it MySQL compatible
	705	*
	706	* @access private
	707	*/
	708	private function _sqlite_prepare()
d2a9db	709	{
a004bb	710	include_once('include/rcube_sqlite.inc');
d2a9db	711
a004bb	712	// we emulate via callback some missing MySQL function
A	713	sqlite_create_function($this->db_handle->connection,
	714	'from_unixtime', 'rcube_sqlite_from_unixtime');
	715	sqlite_create_function($this->db_handle->connection,
	716	'unix_timestamp', 'rcube_sqlite_unix_timestamp');
	717	sqlite_create_function($this->db_handle->connection,
	718	'now', 'rcube_sqlite_now');
	719	sqlite_create_function($this->db_handle->connection,
	720	'md5', 'rcube_sqlite_md5');
d2a9db	721	}
T	722
a004bb	723	} // end class rcube_db
f45ec7	724
981472	725
T	726	/* this is our own debug handler for the MDB2 connection */
	727	function mdb2_debug_handler(&$db, $scope, $message, $context = array())
	728	{
a004bb	729	if ($scope != 'prepare') {
860678	730	$debug_output = sprintf('%s(%d): %s;',
A	731	$scope, $db->db_index, rtrim($message, ';'));
a004bb	732	write_log('sql', $debug_output);
A	733	}
981472	734	}
05a7e3	735