githubFork/roundcubemail.git

svncommit

2005-10-26 43fa235da354c8b53aa69ba745c1d398a758fcaf

commit \| author \| age
d13c36	1	<?php
S	2	// +----------------------------------------------------------------------+
	3	// \| PHP versions 4 and 5 \|
	4	// +----------------------------------------------------------------------+
	5	// \| Copyright (c) 1998-2004 Manuel Lemos, Tomas V.V.Cox, \|
	6	// \| Stig. S. Bakken, Lukas Smith \|
	7	// \| All rights reserved. \|
	8	// +----------------------------------------------------------------------+
	9	// \| MDB2 is a merge of PEAR DB and Metabases that provides a unified DB \|
	10	// \| API as well as database abstraction for PHP applications. \|
	11	// \| This LICENSE is in the BSD license style. \|
	12	// \| \|
	13	// \| Redistribution and use in source and binary forms, with or without \|
	14	// \| modification, are permitted provided that the following conditions \|
	15	// \| are met: \|
	16	// \| \|
	17	// \| Redistributions of source code must retain the above copyright \|
	18	// \| notice, this list of conditions and the following disclaimer. \|
	19	// \| \|
	20	// \| Redistributions in binary form must reproduce the above copyright \|
	21	// \| notice, this list of conditions and the following disclaimer in the \|
	22	// \| documentation and/or other materials provided with the distribution. \|
	23	// \| \|
	24	// \| Neither the name of Manuel Lemos, Tomas V.V.Cox, Stig. S. Bakken, \|
	25	// \| Lukas Smith nor the names of his contributors may be used to endorse \|
	26	// \| or promote products derived from this software without specific prior\|
	27	// \| written permission. \|
	28	// \| \|
	29	// \| THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS \|
	30	// \| "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT \|
	31	// \| LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS \|
	32	// \| FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE \|
	33	// \| REGENTS OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, \|
	34	// \| INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, \|
	35	// \| BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS\|
	36	// \| OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED \|
	37	// \| AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT \|
	38	// \| LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY\|
	39	// \| WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE \|
	40	// \| POSSIBILITY OF SUCH DAMAGE. \|
	41	// +----------------------------------------------------------------------+
	42	// \| Author: Lukas Smith <smith@pooteeweet.org> \|
	43	// +----------------------------------------------------------------------+
	44	//
	45	// $Id$
	46
	47	/**
	48	* @package MDB2
	49	* @category Database
	50	* @author Lukas Smith <smith@pooteeweet.org>
	51	*/
	52
	53	/**
	54	* Used by autoPrepare()
	55	*/
	56	define('MDB2_AUTOQUERY_INSERT', 1);
	57	define('MDB2_AUTOQUERY_UPDATE', 2);
	58
	59	/**
	60	* MDB2_Extended: class which adds several high level methods to MDB2
	61	*
	62	* @package MDB2
	63	* @category Database
	64	* @author Lukas Smith <smith@pooteeweet.org>
	65	*/
	66	class MDB2_Extended extends MDB2_Module_Common
	67	{
	68	// }}}
	69	// {{{ autoPrepare()
	70
	71	/**
	72	* Make automaticaly an insert or update query and call prepare() with it
	73	*
	74	* @param string $table name of the table
	75	* @param array $table_fields ordered array containing the fields names
	76	* @param int $mode type of query to make (MDB2_AUTOQUERY_INSERT or MDB2_AUTOQUERY_UPDATE)
	77	* @param string $where in case of update queries, this string will be put after the sql WHERE statement
	78	* @return resource handle for the query
	79	* @param mixed $types array that contains the types of the placeholders
	80	* @param mixed $result_types array that contains the types of the columns in
	81	* the result set
	82	* @see buildManipSQL
	83	* @access public
	84	*/
	85	function autoPrepare($table, $table_fields, $mode = MDB2_AUTOQUERY_INSERT,
	86	$where = false, $types = null, $result_types = null)
	87	{
	88	$query = $this->buildManipSQL($table, $table_fields, $mode, $where);
	89	if (PEAR::isError($query)) {
	90	return $query;
	91	}
	92	$db =& $this->getDBInstance();
	93	if (PEAR::isError($db)) {
	94	return $db;
	95	}
	96
	97	return $db->prepare($query, $types, $result_types);
	98	}
	99
	100	// {{{
	101	// }}} autoExecute()
	102
	103	/**
	104	* Make automaticaly an insert or update query and call prepare() and execute() with it
	105	*
	106	* @param string $table name of the table
	107	* @param array $fields_values assoc ($key=>$value) where $key is a field name and $value its value
	108	* @param int $mode type of query to make (MDB2_AUTOQUERY_INSERT or MDB2_AUTOQUERY_UPDATE)
	109	* @param string $where in case of update queries, this string will be put after the sql WHERE statement
	110	* @param mixed $types array that contains the types of the placeholders
	111	* @param mixed $result_types array that contains the types of the columns in
	112	* the result set
	113	* @param mixed $result_class string which specifies which result class to use
	114	* @return mixed a new MDB2_Result or a MDB2 Error Object when fail
	115	* @see buildManipSQL
	116	* @see autoPrepare
	117	* @access public
	118	*/
	119	function &autoExecute($table, $fields_values, $mode = MDB2_AUTOQUERY_INSERT,
	120	$where = false, $types = null, $result_types = null, $result_class = true)
	121	{
	122	$stmt = $this->autoPrepare($table, array_keys($fields_values), $mode, $where, $types, $result_types);
	123	if (PEAR::isError($stmt)) {
	124	return $stmt;
	125	}
	126	$params = array_values($fields_values);
	127	$stmt->bindParamArray($params);
	128	$result =& $stmt->execute($result_class);
	129	$stmt->free();
	130	return $result;
	131	}
	132
	133	// {{{
	134	// }}} buildManipSQL()
	135
	136	/**
	137	* Make automaticaly an sql query for prepare()
	138	*
	139	* Example : buildManipSQL('table_sql', array('field1', 'field2', 'field3'), MDB2_AUTOQUERY_INSERT)
	140	* will return the string : INSERT INTO table_sql (field1,field2,field3) VALUES (?,?,?)
	141	* NB : - This belongs more to a SQL Builder class, but this is a simple facility
	142	* - Be carefull ! If you don't give a $where param with an UPDATE query, all
	143	* the records of the table will be updated !
	144	*
	145	* @param string $table name of the table
	146	* @param array $table_fields ordered array containing the fields names
	147	* @param int $mode type of query to make (MDB2_AUTOQUERY_INSERT or MDB2_AUTOQUERY_UPDATE)
	148	* @param string $where in case of update queries, this string will be put after the sql WHERE statement
	149	* @return string sql query for prepare()
	150	* @access public
	151	*/
	152	function buildManipSQL($table, $table_fields, $mode, $where = false)
	153	{
	154	$db =& $this->getDBInstance();
	155	if (PEAR::isError($db)) {
	156	return $db;
	157	}
	158
	159	if (count($table_fields) == 0) {
	160	return $db->raiseError(MDB2_ERROR_NEED_MORE_DATA);
	161	}
	162	switch ($mode) {
	163	case MDB2_AUTOQUERY_INSERT:
	164	$cols = implode(', ', $table_fields);
	165	$values = '?'.str_repeat(', ?', count($table_fields)-1);
	166	return 'INSERT INTO '.$table.' ('.$cols.') VALUES ('.$values.')';
	167	break;
	168	case MDB2_AUTOQUERY_UPDATE:
	169	$set = implode(' = ?, ', $table_fields).' = ?';
	170	$sql = 'UPDATE '.$table.' SET '.$set;
	171	if ($where !== false) {
	172	$sql.= ' WHERE '.$where;
	173	}
	174	return $sql;
	175	break;
	176	}
	177	return $db->raiseError(MDB2_ERROR_SYNTAX);
	178	}
	179
	180	// {{{
	181	// }}} limitQuery()
	182
	183	/**
	184	* Generates a limited query
	185	*
	186	* @param string $query query
	187	* @param mixed $types array that contains the types of the columns in
	188	* the result set
	189	* @param integer $from the row to start to fetching
	190	* @param integer $count the numbers of rows to fetch
	191	* @param mixed $result_class string which specifies which result class to use
	192	* @return mixed a valid ressource pointer or a MDB2 Error Object
	193	* @access public
	194	*/
	195	function &limitQuery($query, $types, $count, $from = 0, $result_class = true)
	196	{
	197	$db =& $this->getDBInstance();
	198	if (PEAR::isError($db)) {
	199	return $db;
	200	}
	201
	202	$result = $db->setLimit($count, $from);
	203	if (PEAR::isError($result)) {
	204	return $result;
	205	}
	206	$result =& $db->query($query, $types, $result_class);
	207	return $result;
	208	}
	209
	210	// {{{
	211	// }}} getOne()
	212
	213	/**
	214	* Fetch the first column of the first row of data returned from
	215	* a query. Takes care of doing the query and freeing the results
	216	* when finished.
	217	*
	218	* @param string $query the SQL query
	219	* @param string $type string that contains the type of the column in the
	220	* result set
	221	* @param array $params if supplied, prepare/execute will be used
	222	* with this array as execute parameters
	223	* @param array $param_types array that contains the types of the values
	224	* defined in $params
	225	* @param mixed $colnum which column to return
	226	* @return mixed MDB2_OK or data on success, a MDB2 error on failure
	227	* @access public
	228	*/
	229	function getOne($query, $type = null, $params = array(),
	230	$param_types = null, $colnum = 0)
	231	{
	232	$db =& $this->getDBInstance();
	233	if (PEAR::isError($db)) {
	234	return $db;
	235	}
	236
	237	settype($params, 'array');
	238	settype($type, 'array');
	239	if (count($params) == 0) {
	240	return $db->queryOne($query, $type, $colnum);
	241	}
	242
	243	$stmt = $db->prepare($query, $param_types, $type);
	244	if (PEAR::isError($stmt)) {
	245	return $stmt;
	246	}
	247
	248	$stmt->bindParamArray($params);
	249	$result = $stmt->execute();
	250	if (!MDB2::isResultCommon($result)) {
	251	return $result;
	252	}
	253
	254	$one = $result->fetchOne($colnum);
	255	$stmt->free();
	256	$result->free();
	257	return $one;
	258	}
	259
	260	// }}}
	261	// {{{ getRow()
	262
	263	/**
	264	* Fetch the first row of data returned from a query. Takes care
	265	* of doing the query and freeing the results when finished.
	266	*
	267	* @param string $query the SQL query
	268	* @param array $types array that contains the types of the columns in
	269	* the result set
	270	* @param array $params array if supplied, prepare/execute will be used
	271	* with this array as execute parameters
	272	* @param array $param_types array that contains the types of the values
	273	* defined in $params
	274	* @param integer $fetchmode the fetch mode to use
	275	* @return mixed MDB2_OK or data array on success, a MDB2 error on failure
	276	* @access public
	277	*/
	278	function getRow($query, $types = null, $params = array(),
	279	$param_types = null, $fetchmode = MDB2_FETCHMODE_DEFAULT)
	280	{
	281	$db =& $this->getDBInstance();
	282	if (PEAR::isError($db)) {
	283	return $db;
	284	}
	285
	286	settype($params, 'array');
	287	if (count($params) == 0) {
	288	return $db->queryRow($query, $types, $fetchmode);
	289	}
	290
	291	$stmt = $db->prepare($query, $param_types, $types);
	292	if (PEAR::isError($stmt)) {
	293	return $stmt;
	294	}
	295
	296	$stmt->bindParamArray($params);
	297	$result = $stmt->execute();
	298	if (!MDB2::isResultCommon($result)) {
	299	return $result;
	300	}
	301
	302	$row = $result->fetchRow($fetchmode);
	303	$stmt->free();
	304	$result->free();
	305	return $row;
	306	}
	307
	308	// }}}
	309	// {{{ getCol()
	310
	311	/**
	312	* Fetch a single column from a result set and return it as an
	313	* indexed array.
	314	*
	315	* @param string $query the SQL query
	316	* @param string $type string that contains the type of the column in the
	317	* result set
	318	* @param array $params array if supplied, prepare/execute will be used
	319	* with this array as execute parameters
	320	* @param array $param_types array that contains the types of the values
	321	* defined in $params
	322	* @param mixed $colnum which column to return
	323	* @return mixed MDB2_OK or data array on success, a MDB2 error on failure
	324	* @access public
	325	*/
	326	function getCol($query, $type = null, $params = array(),
	327	$param_types = null, $colnum = 0)
	328	{
	329	$db =& $this->getDBInstance();
	330	if (PEAR::isError($db)) {
	331	return $db;
	332	}
	333
	334	settype($params, 'array');
	335	settype($type, 'array');
	336	if (count($params) == 0) {
	337	return $db->queryCol($query, $type, $colnum);
	338	}
	339
	340	$stmt = $db->prepare($query, $param_types, $type);
	341	if (PEAR::isError($stmt)) {
	342	return $stmt;
	343	}
	344
	345	$stmt->bindParamArray($params);
	346	$result = $stmt->execute();
	347	if (!MDB2::isResultCommon($result)) {
	348	return $result;
	349	}
	350
	351	$col = $result->fetchCol($colnum);
	352	$stmt->free();
	353	$result->free();
	354	return $col;
	355	}
	356
	357	// }}}
	358	// {{{ getAll()
	359
	360	/**
	361	* Fetch all the rows returned from a query.
	362	*
	363	* @param string $query the SQL query
	364	* @param array $types array that contains the types of the columns in
	365	* the result set
	366	* @param array $params array if supplied, prepare/execute will be used
	367	* with this array as execute parameters
	368	* @param array $param_types array that contains the types of the values
	369	* defined in $params
	370	* @param integer $fetchmode the fetch mode to use
	371	* @param boolean $rekey if set to true, the $all will have the first
	372	* column as its first dimension
	373	* @param boolean $force_array used only when the query returns exactly
	374	* two columns. If true, the values of the returned array will be
	375	* one-element arrays instead of scalars.
	376	* @param boolean $group if true, the values of the returned array is
	377	* wrapped in another array. If the same key value (in the first
	378	* column) repeats itself, the values will be appended to this array
	379	* instead of overwriting the existing values.
	380	* @return mixed MDB2_OK or data array on success, a MDB2 error on failure
	381	* @access public
	382	*/
	383	function getAll($query, $types = null, $params = array(),
	384	$param_types = null, $fetchmode = MDB2_FETCHMODE_DEFAULT,
	385	$rekey = false, $force_array = false, $group = false)
	386	{
	387	$db =& $this->getDBInstance();
	388	if (PEAR::isError($db)) {
	389	return $db;
	390	}
	391
	392	settype($params, 'array');
	393	if (count($params) == 0) {
	394	return $db->queryAll($query, $types, $fetchmode, $rekey, $force_array, $group);
	395	}
	396
	397	$stmt = $db->prepare($query, $param_types, $types);
	398	if (PEAR::isError($stmt)) {
	399	return $stmt;
	400	}
	401
	402	$stmt->bindParamArray($params);
	403	$result = $stmt->execute();
	404	if (!MDB2::isResultCommon($result)) {
	405	return $result;
	406	}
	407
	408	$all = $result->fetchAll($fetchmode, $rekey, $force_array, $group);
	409	$stmt->free();
	410	$result->free();
	411	return $all;
	412	}
	413
	414	// }}}
	415	// {{{ getAssoc()
	416
	417	/**
	418	* Fetch the entire result set of a query and return it as an
	419	* associative array using the first column as the key.
	420	*
	421	* If the result set contains more than two columns, the value
	422	* will be an array of the values from column 2-n. If the result
	423	* set contains only two columns, the returned value will be a
	424	* scalar with the value of the second column (unless forced to an
	425	* array with the $force_array parameter). A MDB error code is
	426	* returned on errors. If the result set contains fewer than two
	427	* columns, a MDB2_ERROR_TRUNCATED error is returned.
	428	*
	429	* For example, if the table 'mytable' contains:
	430	*
	431	* ID TEXT DATE
	432	* --------------------------------
	433	* 1 'one' 944679408
	434	* 2 'two' 944679408
	435	* 3 'three' 944679408
	436	*
	437	* Then the call getAssoc('SELECT id,text FROM mytable') returns:
	438	* array(
	439	* '1' => 'one',
	440	* '2' => 'two',
	441	* '3' => 'three',
	442	* )
	443	*
	444	* ...while the call getAssoc('SELECT id,text,date FROM mytable') returns:
	445	* array(
	446	* '1' => array('one', '944679408'),
	447	* '2' => array('two', '944679408'),
	448	* '3' => array('three', '944679408')
	449	* )
	450	*
	451	* If the more than one row occurs with the same value in the
	452	* first column, the last row overwrites all previous ones by
	453	* default. Use the $group parameter if you don't want to
	454	* overwrite like this. Example:
	455	*
	456	* getAssoc('SELECT category,id,name FROM mytable', null, null
	457	* MDB2_FETCHMODE_ASSOC, false, true) returns:
	458	* array(
	459	* '1' => array(array('id' => '4', 'name' => 'number four'),
	460	* array('id' => '6', 'name' => 'number six')
	461	* ),
	462	* '9' => array(array('id' => '4', 'name' => 'number four'),
	463	* array('id' => '6', 'name' => 'number six')
	464	* )
	465	* )
	466	*
	467	* Keep in mind that database functions in PHP usually return string
	468	* values for results regardless of the database's internal type.
	469	*
	470	* @param string $query the SQL query
	471	* @param array $types array that contains the types of the columns in
	472	* the result set
	473	* @param array $params array if supplied, prepare/execute will be used
	474	* with this array as execute parameters
	475	* @param array $param_types array that contains the types of the values
	476	* defined in $params
	477	* @param boolean $force_array used only when the query returns
	478	* exactly two columns. If TRUE, the values of the returned array
	479	* will be one-element arrays instead of scalars.
	480	* @param boolean $group if TRUE, the values of the returned array
	481	* is wrapped in another array. If the same key value (in the first
	482	* column) repeats itself, the values will be appended to this array
	483	* instead of overwriting the existing values.
	484	* @return array associative array with results from the query.
	485	* @access public
	486	*/
	487	function getAssoc($query, $types = null, $params = array(), $param_types = null,
	488	$fetchmode = MDB2_FETCHMODE_DEFAULT, $force_array = false, $group = false)
	489	{
	490	$db =& $this->getDBInstance();
	491	if (PEAR::isError($db)) {
	492	return $db;
	493	}
	494
	495	settype($params, 'array');
	496	if (count($params) == 0) {
	497	return $db->queryAll($query, $types, $fetchmode, true, $force_array, $group);
	498	}
	499
	500	$stmt = $db->prepare($query, $param_types, $types);
	501	if (PEAR::isError($stmt)) {
	502	return $stmt;
	503	}
	504
	505	$stmt->bindParamArray($params);
	506	$result = $stmt->execute();
	507	if (!MDB2::isResultCommon($result)) {
	508	return $result;
	509	}
	510
	511	$all = $result->fetchAll($fetchmode, true, $force_array, $group);
	512	$stmt->free();
	513	$result->free();
	514	return $all;
	515	}
	516
	517	// }}}
	518	// {{{ executeMultiple()
	519
	520	/**
	521	* This function does several execute() calls on the same statement handle.
	522	* $params must be an array indexed numerically from 0, one execute call is
	523	* done for every 'row' in the array.
	524	*
	525	* If an error occurs during execute(), executeMultiple() does not execute
	526	* the unfinished rows, but rather returns that error.
	527	*
	528	* @param resource $stmt query handle from prepare()
	529	* @param array $params numeric array containing the
	530	* data to insert into the query
	531	* @return mixed a result handle or MDB2_OK on success, a MDB2 error on failure
	532	* @access public
	533	* @see prepare(), execute()
	534	*/
	535	function executeMultiple(&$stmt, $params = null)
	536	{
	537	for ($i = 0, $j = count($params); $i < $j; $i++) {
	538	$stmt->bindParamArray($params[$i]);
	539	$result = $stmt->execute();
	540	if (PEAR::isError($result)) {
	541	return $result;
	542	}
	543	}
	544	return MDB2_OK;
	545	}
	546
	547	// }}}
	548	// {{{ getBeforeID()
	549
	550	/**
	551	* returns the next free id of a sequence if the RDBMS
	552	* does not support auto increment
	553	*
	554	* @param string $table name of the table into which a new row was inserted
	555	* @param boolean $ondemand when true the seqence is
	556	* automatic created, if it
	557	* not exists
	558	*
	559	* @return mixed MDB2 Error Object or id
	560	* @access public
	561	*/
	562	function getBeforeID($table, $field, $ondemand = true)
	563	{
	564	$db =& $this->getDBInstance();
	565	if (PEAR::isError($db)) {
	566	return $db;
	567	}
	568
	569	if ($db->supports('auto_increment') !== true) {
	570	$seq = $table.(empty($field) ? '' : '_'.$field);
	571	$id = $db->nextID($seq, $ondemand);
	572	if (PEAR::isError($id)) {
	573	return $id;
	574	}
	575	return $db->quote($id, 'integer');
	576	}
	577	return 'NULL';
	578	}
	579
	580	// }}}
	581	// {{{ getAfterID()
	582
	583	/**
	584	* returns the autoincrement ID if supported or $id
	585	*
	586	* @param mixed $id value as returned by getBeforeId()
	587	* @param string $table name of the table into which a new row was inserted
	588	* @return mixed MDB2 Error Object or id
	589	* @access public
	590	*/
	591	function getAfterID($id, $table, $field)
	592	{
	593	$db =& $this->getDBInstance();
	594	if (PEAR::isError($db)) {
	595	return $db;
	596	}
	597
	598	if ($db->supports('auto_increment') !== true) {
	599	return $id;
	600	}
	601	return $db->lastInsertID($table, $field);
	602	}
	603
	604	}
	605	?>