githubFork/roundcubemail.git

thomascube

2006-02-22 745b1466fc76d5ded589e2469328086002430c1c

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