githubFork/roundcubemail.git

- Case insensitive contacts searching using PostgreSQL (#1485259)

alecpl

2008-08-09 d8d41658d3fb87c71c3e30825003f827f4ffbdae

commit \| author \| age
f45ec7	1	<?php
S	2
	3	/*
	4	+-----------------------------------------------------------------------+
47124c	5	\| program/include/rcube_mdb2.php \|
f45ec7	6	\| \|
S	7	\| This file is part of the RoundCube Webmail client \|
47124c	8	\| Copyright (C) 2005-2008, 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>
	33	* @version 1.16
	34	* @link http://pear.php.net/package/MDB2
	35	*/
6d969b	36	class rcube_mdb2
d2a9db	37	{
T	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 = '';
981472	45	var $debug_mode = false;
f45ec7	46
d2a9db	47	var $a_query_results = array('dummy');
T	48	var $last_res_id = 0;
f45ec7	49
d2a9db	50
T	51	/**
	52	* Object constructor
	53	*
	54	* @param string DSN for read/write operations
	55	* @param string Optional DSN for read only operations
	56	*/
bad419	57	function __construct($db_dsnw, $db_dsnr='', $pconn=false)
f45ec7	58	{
d2a9db	59	if ($db_dsnr=='')
T	60	$db_dsnr=$db_dsnw;
	61
	62	$this->db_dsnw = $db_dsnw;
	63	$this->db_dsnr = $db_dsnr;
bad419	64	$this->db_pconn = $pconn;
T	65
d2a9db	66	$dsn_array = MDB2::parseDSN($db_dsnw);
T	67	$this->db_provider = $dsn_array['phptype'];
f45ec7	68	}
S	69
d2a9db	70
T	71	/**
	72	* PHP 4 object constructor
	73	*
6d969b	74	* @see rcube_mdb2::__construct
d2a9db	75	*/
T	76	function rcube_db($db_dsnw,$db_dsnr='')
f45ec7	77	{
d2a9db	78	$this->__construct($db_dsnw,$db_dsnr);
f45ec7	79	}
S	80
d2a9db	81
T	82	/**
	83	* Connect to specific database
	84	*
	85	* @param string DSN for DB connections
	86	* @return object PEAR database handle
	87	* @access private
	88	*/
	89	function dsn_connect($dsn)
f45ec7	90	{
d2a9db	91	// Use persistent connections if available
981472	92	$dbh = MDB2::connect($dsn, array(
T	93	'persistent' => $this->db_pconn,
0ce1a6	94	'emulate_prepared' => $this->debug_mode,
981472	95	'debug' => $this->debug_mode,
T	96	'debug_handler' => 'mdb2_debug_handler',
7eaf7a	97	'portability' => MDB2_PORTABILITY_ALL ^ MDB2_PORTABILITY_EMPTY_TO_NULL));
d2a9db	98
7eaf7a	99	if (MDB2::isError($dbh))
d2a9db	100	{
T	101	$this->db_error = TRUE;
	102	$this->db_error_msg = $dbh->getMessage();
	103
7eaf7a	104	raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__,
T	105	'file' => __FILE__, 'message' => $dbh->getUserInfo()), TRUE, FALSE);
d2a9db	106	}
T	107	else if ($this->db_provider=='sqlite')
	108	{
	109	$dsn_array = MDB2::parseDSN($dsn);
	110	if (!filesize($dsn_array['database']) && !empty($this->sqlite_initials))
	111	$this->_sqlite_create_database($dbh, $this->sqlite_initials);
	112	}
b66d40	113	else
T	114	$dbh->setCharset('utf8');
d2a9db	115
T	116	return $dbh;
	117	}
	118
	119
	120	/**
	121	* Connect to appropiate databse
	122	* depending on the operation
	123	*
	124	* @param string Connection mode (r\|w)
	125	* @access public
	126	*/
	127	function db_connect($mode)
	128	{
	129	$this->db_mode = $mode;
	130
	131	// Already connected
	132	if ($this->db_connected)
	133	{
	134	// no replication, current connection is ok
	135	if ($this->db_dsnw==$this->db_dsnr)
	136	return;
	137
	138	// connected to master, current connection is ok
	139	if ($this->db_mode=='w')
	140	return;
	141
	142	// Same mode, current connection is ok
	143	if ($this->db_mode==$mode)
	144	return;
	145	}
	146
	147	if ($mode=='r')
	148	$dsn = $this->db_dsnr;
	149	else
	150	$dsn = $this->db_dsnw;
	151
	152	$this->db_handle = $this->dsn_connect($dsn);
	153	$this->db_connected = true;
	154	}
	155
981472	156
T	157	/**
	158	* Activate/deactivate debug mode
	159	*
	160	* @param boolean True if SQL queries should be logged
	161	*/
	162	function set_debug($dbg = true)
	163	{
	164	$this->debug_mode = $dbg;
	165	if ($this->db_connected)
0ce1a6	166	{
981472	167	$this->db_handle->setOption('debug', $dbg);
0ce1a6	168	$this->db_handle->setOption('emulate_prepared', $dbg);
T	169	}
981472	170	}
d2a9db	171
T	172
	173	/**
	174	* Getter for error state
	175	*
	176	* @param boolean True on error
	177	*/
	178	function is_error()
	179	{
	180	return $this->db_error ? $this->db_error_msg : FALSE;
	181	}
	182
	183
	184	/**
	185	* Execute a SQL query
	186	*
	187	* @param string SQL query to execute
	188	* @param mixed Values to be inserted in query
	189	* @return number Query handle identifier
	190	* @access public
	191	*/
	192	function query()
	193	{
	194	$params = func_get_args();
	195	$query = array_shift($params);
	196
	197	return $this->_query($query, 0, 0, $params);
	198	}
	199
	200
	201	/**
	202	* Execute a SQL query with limits
	203	*
	204	* @param string SQL query to execute
	205	* @param number Offset for LIMIT statement
	206	* @param number Number of rows for LIMIT statement
	207	* @param mixed Values to be inserted in query
	208	* @return number Query handle identifier
	209	* @access public
	210	*/
	211	function limitquery()
	212	{
	213	$params = func_get_args();
	214	$query = array_shift($params);
	215	$offset = array_shift($params);
	216	$numrows = array_shift($params);
	217
	218	return $this->_query($query, $offset, $numrows, $params);
	219	}
	220
	221
	222	/**
	223	* Execute a SQL query with limits
	224	*
	225	* @param string SQL query to execute
	226	* @param number Offset for LIMIT statement
	227	* @param number Number of rows for LIMIT statement
	228	* @param array Values to be inserted in query
	229	* @return number Query handle identifier
	230	* @access private
	231	*/
	232	function _query($query, $offset, $numrows, $params)
	233	{
	234	// Read or write ?
	235	if (strtolower(trim(substr($query,0,6)))=='select')
	236	$mode='r';
	237	else
	238	$mode='w';
	239
	240	$this->db_connect($mode);
	241
	242	if ($this->db_provider == 'sqlite')
	243	$this->_sqlite_prepare();
	244
	245	if ($numrows \|\| $offset)
	246	$result = $this->db_handle->setLimit($numrows,$offset);
	247
	248	if (empty($params))
	249	$result = $this->db_handle->query($query);
	250	else
	251	{
	252	$params = (array)$params;
	253	$q = $this->db_handle->prepare($query);
	254	if ($this->db_handle->isError($q))
f45ec7	255	{
d2a9db	256	$this->db_error = TRUE;
T	257	$this->db_error_msg = $q->userinfo;
	258
	259	raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__,
	260	'message' => $this->db_error_msg), TRUE, TRUE);
f45ec7	261	}
d2a9db	262	else
f45ec7	263	{
d2a9db	264	$result = $q->execute($params);
T	265	$q->free();
f45ec7	266	}
d2a9db	267	}
T	268
	269	// add result, even if it's an error
	270	return $this->_add_result($result);
f45ec7	271	}
S	272
ccfda8	273
d2a9db	274	/**
T	275	* Get number of rows for a SQL query
	276	* If no query handle is specified, the last query will be taken as reference
	277	*
	278	* @param number Optional query handle identifier
	279	* @return mixed Number of rows or FALSE on failure
	280	* @access public
	281	*/
	282	function num_rows($res_id=NULL)
10a699	283	{
d2a9db	284	if (!$this->db_handle)
T	285	return FALSE;
ccfda8	286
d2a9db	287	if ($result = $this->_get_result($res_id))
T	288	return $result->numRows();
	289	else
	290	return FALSE;
10a699	291	}
T	292
	293
d2a9db	294	/**
T	295	* Get number of affected rows fort he last query
	296	*
	297	* @return mixed Number of rows or FALSE on failure
	298	* @access public
	299	*/
	300	function affected_rows($result = null)
	301	{
	302	if (!$this->db_handle)
	303	return FALSE;
ccfda8	304
bad419	305	return $this->_get_result($result);
d2a9db	306	}
ccfda8	307
d2a9db	308
T	309	/**
	310	* Get last inserted record ID
	311	* For Postgres databases, a sequence name is required
	312	*
	313	* @param string Sequence name for increment
	314	* @return mixed ID or FALSE on failure
	315	* @access public
	316	*/
	317	function insert_id($sequence = '')
	318	{
	319	if (!$this->db_handle \|\| $this->db_mode=='r')
	320	return FALSE;
	321
	322	return $this->db_handle->lastInsertID($sequence);
	323	}
	324
	325
	326	/**
	327	* Get an associative array for one row
	328	* If no query handle is specified, the last query will be taken as reference
	329	*
	330	* @param number Optional query handle identifier
	331	* @return mixed Array with col values or FALSE on failure
	332	* @access public
	333	*/
	334	function fetch_assoc($res_id=NULL)
	335	{
	336	$result = $this->_get_result($res_id);
	337	return $this->_fetch_row($result, MDB2_FETCHMODE_ASSOC);
	338	}
	339
	340
	341	/**
	342	* Get an index array for one row
	343	* If no query handle is specified, the last query will be taken as reference
	344	*
	345	* @param number Optional query handle identifier
	346	* @return mixed Array with col values or FALSE on failure
	347	* @access public
	348	*/
	349	function fetch_array($res_id=NULL)
	350	{
	351	$result = $this->_get_result($res_id);
	352	return $this->_fetch_row($result, MDB2_FETCHMODE_ORDERED);
	353	}
	354
	355
	356	/**
	357	* Get co values for a result row
	358	*
	359	* @param object Query result handle
	360	* @param number Fetch mode identifier
	361	* @return mixed Array with col values or FALSE on failure
	362	* @access private
	363	*/
	364	function _fetch_row($result, $mode)
	365	{
	366	if (PEAR::isError($result))
	367	{
	368	raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__,
	369	'message' => $this->db_link->getMessage()), TRUE, FALSE);
	370	return FALSE;
	371	}
	372
	373	return $result->fetchRow($mode);
	374	}
	375
	376
	377	/**
	378	* Formats input so it can be safely used in a query
	379	*
	380	* @param mixed Value to quote
	381	* @return string Quoted/converted string for use in query
	382	* @access public
	383	*/
	384	function quote($input, $type = null)
	385	{
	386	// create DB handle if not available
	387	if (!$this->db_handle)
	388	$this->db_connect('r');
	389
	390	// escape pear identifier chars
	391	$rep_chars = array('?' => '\?',
	392	'!' => '\!',
	393	'&' => '\&');
	394
	395	return $this->db_handle->quote($input, $type);
	396	}
	397
	398
	399	/**
	400	* Quotes a string so it can be safely used as a table or column name
	401	*
	402	* @param string Value to quote
	403	* @return string Quoted string for use in query
	404	* @deprecated Replaced by rcube_MDB2::quote_identifier
	405	* @see rcube_MDB2::quote_identifier
	406	* @access public
	407	*/
	408	function quoteIdentifier($str)
d7cb77	409	{
d2a9db	410	return $this->quote_identifier($str);
10a699	411	}
ccfda8	412
10a699	413
d2a9db	414	/**
T	415	* Quotes a string so it can be safely used as a table or column name
	416	*
	417	* @param string Value to quote
	418	* @return string Quoted string for use in query
	419	* @access public
	420	*/
	421	function quote_identifier($str)
f45ec7	422	{
d2a9db	423	if (!$this->db_handle)
T	424	$this->db_connect('r');
	425
	426	return $this->db_handle->quoteIdentifier($str);
f45ec7	427	}
S	428
8f4dcb	429	/**
T	430	* Escapes a string
	431	*
	432	* @param string The string to be escaped
	433	* @return string The escaped string
	434	* @access public
	435	* @since 0.1.1
	436	*/
	437	function escapeSimple($str)
	438	{
	439	if (!$this->db_handle)
	440	$this->db_connect('r');
	441
	442	return $this->db_handle->escape($str);
	443	}
	444
f45ec7	445
d2a9db	446	/**
7139e3	447	* Return SQL function for current time and date
T	448	*
	449	* @return string SQL function to use in query
	450	* @access public
	451	*/
	452	function now()
	453	{
	454	switch($this->db_provider)
	455	{
	456	case 'mssql':
	457	return "getdate()";
	458
	459	default:
	460	return "now()";
	461	}
	462	}
	463
	464
	465	/**
d2a9db	466	* Return SQL statement to convert a field value into a unix timestamp
T	467	*
	468	* @param string Field name
	469	* @return string SQL statement to use in query
	470	* @access public
	471	*/
	472	function unixtimestamp($field)
f45ec7	473	{
d2a9db	474	switch($this->db_provider)
T	475	{
	476	case 'pgsql':
	477	return "EXTRACT (EPOCH FROM $field)";
	478	break;
	479
7139e3	480	case 'mssql':
T	481	return "datediff(s, '1970-01-01 00:00:00', $field)";
	482
d2a9db	483	default:
T	484	return "UNIX_TIMESTAMP($field)";
	485	}
f45ec7	486	}
S	487
	488
d2a9db	489	/**
T	490	* Return SQL statement to convert from a unix timestamp
	491	*
	492	* @param string Field name
	493	* @return string SQL statement to use in query
	494	* @access public
	495	*/
	496	function fromunixtime($timestamp)
f45ec7	497	{
d2a9db	498	switch($this->db_provider)
T	499	{
	500	case 'mysqli':
	501	case 'mysql':
	502	case 'sqlite':
3978ab	503	return sprintf("FROM_UNIXTIME(%d)", $timestamp);
f45ec7	504
d2a9db	505	default:
T	506	return date("'Y-m-d H:i:s'", $timestamp);
	507	}
f45ec7	508	}
S	509
d2a9db	510
T	511	/**
d8d416	512	* Return SQL statement for case insensitive LIKE
A	513	*
	514	* @param string Field name
	515	* @param string Search value
	516	* @return string SQL statement to use in query
	517	* @access public
	518	*/
	519	function ilike($column, $value)
	520	{
	521	// TODO: use MDB2's matchPattern() function
	522	switch($this->db_provider)
	523	{
	524	case 'pgsql':
	525	return $this->quote_identifier($column).' ILIKE '.$this->quote($value);
	526	default:
	527	return $this->quote_identifier($column).' LIKE '.$this->quote($value);
	528	}
	529	}
	530
	531
	532	/**
d2a9db	533	* Adds a query result and returns a handle ID
T	534	*
	535	* @param object Query handle
	536	* @return mixed Handle ID or FALE on failure
	537	* @access private
	538	*/
	539	function _add_result($res)
f45ec7	540	{
d2a9db	541	// sql error occured
T	542	if (PEAR::isError($res))
	543	{
	544	raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__,
	545	'message' => $res->getMessage() . " Query: " . substr(preg_replace('/[\r\n]+\s*/', ' ', $res->userinfo), 0, 512)), TRUE, FALSE);
	546	return FALSE;
	547	}
	548	else
	549	{
	550	$res_id = sizeof($this->a_query_results);
	551	$this->a_query_results[$res_id] = $res;
	552	$this->last_res_id = $res_id;
	553	return $res_id;
	554	}
f45ec7	555	}
d2a9db	556
T	557
	558	/**
	559	* Resolves a given handle ID and returns the according query handle
	560	* If no ID is specified, the last ressource handle will be returned
	561	*
	562	* @param number Handle ID
	563	* @return mixed Ressource handle or FALE on failure
	564	* @access private
	565	*/
	566	function _get_result($res_id=NULL)
	567	{
	568	if ($res_id==NULL)
	569	$res_id = $this->last_res_id;
	570
	571	if ($res_id && isset($this->a_query_results[$res_id]))
	572	return $this->a_query_results[$res_id];
	573	else
	574	return FALSE;
	575	}
	576
	577
	578	/**
	579	* Create a sqlite database from a file
	580	*
	581	* @param object SQLite database handle
	582	* @param string File path to use for DB creation
	583	* @access private
	584	*/
	585	function _sqlite_create_database($dbh, $file_name)
	586	{
	587	if (empty($file_name) \|\| !is_string($file_name))
	588	return;
	589
ff73e0	590	$data = file_get_contents($file_name);
d2a9db	591
T	592	if (strlen($data))
	593	sqlite_exec($dbh->connection, $data);
	594	}
	595
	596
	597	/**
	598	* Add some proprietary database functions to the current SQLite handle
	599	* in order to make it MySQL compatible
	600	*
	601	* @access private
	602	*/
	603	function _sqlite_prepare()
	604	{
	605	include_once('include/rcube_sqlite.inc');
	606
	607	// we emulate via callback some missing MySQL function
	608	sqlite_create_function($this->db_handle->connection, "from_unixtime", "rcube_sqlite_from_unixtime");
	609	sqlite_create_function($this->db_handle->connection, "unix_timestamp", "rcube_sqlite_unix_timestamp");
	610	sqlite_create_function($this->db_handle->connection, "now", "rcube_sqlite_now");
	611	sqlite_create_function($this->db_handle->connection, "md5", "rcube_sqlite_md5");
	612	}
	613
	614
	615	} // end class rcube_db
f45ec7	616
981472	617
T	618	/* this is our own debug handler for the MDB2 connection */
	619	function mdb2_debug_handler(&$db, $scope, $message, $context = array())
	620	{
	621	if ($scope != 'prepare')
	622	{
	623	$debug_output = $scope . '('.$db->db_index.'): ';
	624	$debug_output .= $message . $db->getOption('log_line_break');
	625	write_log('sqllog', $debug_output);
	626	}
	627	}
	628
	629