githubFork/roundcubemail.git

thomascube

2007-08-10 31d9efd97d9da09605d4329457ee218cba48f82f

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