githubFork/roundcubemail.git

thomascube

2006-01-23 0677ca5a1e745643a9142081c4cbb7ea13e5a2e5

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>
	38	* @version 1.14
	39	* @link http://pear.php.net/package/DB
	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);
1676e1	307
1cded8	308	if (DB::isError($result))
T	309	{
	310	raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__,
	311	'message' => $this->db_link->getMessage()), TRUE, FALSE);
	312	return FALSE;
	313	}
1676e1	314
1cded8	315	return $result->fetchRow(DB_FETCHMODE_ASSOC);
1676e1	316	}
S	317
10a699	318
15a9d1	319	/**
T	320	* Formats input so it can be safely used in a query
	321	*
	322	* @param mixed Value to quote
	323	* @return string Quoted/converted string for use in query
	324	* @access public
	325	*/
	326	function quote($input)
10a699	327	{
15a9d1	328	// create DB handle if not available
1cded8	329	if (!$this->db_handle)
T	330	$this->db_connect('r');
15a9d1	331
T	332	// escape pear identifier chars
	333	$rep_chars = array('?' => '\?',
	334	'!' => '\!',
	335	'&' => '\&');
	336
	337	return $this->db_handle->quoteSmart(strtr($input, $rep_chars));
10a699	338	}
T	339
	340
15a9d1	341	/**
T	342	* Quotes a string so it can be safely used as a table or column name
	343	*
	344	* @param string Value to quote
	345	* @return string Quoted string for use in query
	346	* @deprecated Replaced by rcube_db::quote_identifier
	347	* @see rcube_db::quote_identifier
	348	* @access public
	349	*/
1cded8	350	function quoteIdentifier($str)
d7cb77	351	{
15a9d1	352	return $this->quote_identifier($str);
T	353	}
	354
	355
	356	/**
	357	* Quotes a string so it can be safely used as a table or column name
	358	*
	359	* @param string Value to quote
	360	* @return string Quoted string for use in query
	361	* @access public
	362	*/
	363	function quote_identifier($str)
	364	{
1cded8	365	if (!$this->db_handle)
T	366	$this->db_connect('r');
d7cb77	367
1cded8	368	return $this->db_handle->quoteIdentifier($str);
1676e1	369	}
S	370
	371
15a9d1	372	/**
T	373	* Return SQL statement to convert a field value into a unix timestamp
	374	*
	375	* @param string Field name
	376	* @return string SQL statement to use in query
	377	* @access public
	378	*/
1cded8	379	function unixtimestamp($field)
1676e1	380	{
1cded8	381	switch($this->db_provider)
T	382	{
	383	case 'pgsql':
	384	return "EXTRACT (EPOCH FROM $field)";
	385	break;
	386
	387	default:
	388	return "UNIX_TIMESTAMP($field)";
	389	}
	390	}
	391
	392
15a9d1	393	/**
T	394	* Return SQL statement to convert from a unix timestamp
	395	*
	396	* @param string Field name
	397	* @return string SQL statement to use in query
	398	* @access public
	399	*/
1cded8	400	function fromunixtime($timestamp)
T	401	{
	402	switch($this->db_provider)
	403	{
	404	case 'mysqli':
	405	case 'mysql':
	406	case 'sqlite':
	407	return "FROM_UNIXTIME($timestamp)";
	408
	409	default:
	410	return date("'Y-m-d H:i:s'", $timestamp);
	411	}
	412	}
	413
	414
15a9d1	415	/**
T	416	* Adds a query result and returns a handle ID
	417	*
	418	* @param object Query handle
	419	* @return mixed Handle ID or FALE on failure
	420	* @access private
	421	*/
1cded8	422	function _add_result($res)
T	423	{
	424	// sql error occured
	425	if (DB::isError($res))
	426	{
	427	raise_error(array('code' => 500, 'type' => 'db', 'line' => __LINE__, 'file' => __FILE__,
15a9d1	428	'message' => $res->getMessage() . " Query: " . substr(preg_replace('/[\r\n]+\s*/', ' ', $res->userinfo), 0, 512)), TRUE, FALSE);
1cded8	429	return FALSE;
T	430	}
	431	else
	432	{
	433	$res_id = sizeof($this->a_query_results);
	434	$this->a_query_results[$res_id] = $res;
	435	$this->last_res_id = $res_id;
	436	return $res_id;
	437	}
	438	}
	439
	440
15a9d1	441	/**
T	442	* Resolves a given handle ID and returns the according query handle
	443	* If no ID is specified, the last ressource handle will be returned
	444	*
	445	* @param number Handle ID
	446	* @return mixed Ressource handle or FALE on failure
	447	* @access private
	448	*/
	449	function _get_result($res_id=NULL)
1cded8	450	{
T	451	if ($res_id==NULL)
	452	$res_id = $this->last_res_id;
1676e1	453
1cded8	454	if ($res_id && isset($this->a_query_results[$res_id]))
T	455	return $this->a_query_results[$res_id];
	456	else
	457	return FALSE;
1676e1	458	}
S	459
597170	460
15a9d1	461	/**
T	462	* Create a sqlite database from a file
	463	*
	464	* @param object SQLite database handle
	465	* @param string File path to use for DB creation
	466	* @access private
	467	*/
	468	function _sqlite_create_database($dbh, $file_name)
597170	469	{
15a9d1	470	if (empty($file_name) \|\| !is_string($file_name))
T	471	return;
597170	472
1cded8	473	$data = '';
15a9d1	474	if ($fd = fopen($file_name, 'r'))
1cded8	475	{
15a9d1	476	$data = fread($fd, filesize($file_name));
1cded8	477	fclose($fd);
T	478	}
597170	479
1cded8	480	if (strlen($data))
T	481	sqlite_exec($dbh->connection, $data);
597170	482	}
T	483
15a9d1	484
T	485	/**
	486	* Add some proprietary database functions to the current SQLite handle
	487	* in order to make it MySQL compatible
	488	*
	489	* @access private
	490	*/
1cded8	491	function _sqlite_prepare()
597170	492	{
1cded8	493	include_once('include/rcube_sqlite.inc');
597170	494
1cded8	495	// we emulate via callback some missing MySQL function
T	496	sqlite_create_function($this->db_handle->connection, "from_unixtime", "rcube_sqlite_from_unixtime");
	497	sqlite_create_function($this->db_handle->connection, "unix_timestamp", "rcube_sqlite_unix_timestamp");
	498	sqlite_create_function($this->db_handle->connection, "now", "rcube_sqlite_now");
	499	sqlite_create_function($this->db_handle->connection, "md5", "rcube_sqlite_md5");
597170	500	}
1cded8	501
T	502
	503	} // end class rcube_db
1676e1	504
S	505	?>