Context Navigation

source: branches/2.3/phpgwapi/inc/class.db.inc.php @ 1738

Revision 1738, 35.7 KB checked in by rodsouza, 14 years ago (diff)
Ticket #491 - Mitigando situações que criam inundam o LOG.
Property svn:eol-style set to `native` Property svn:executable set to ``*

Line
1	<?php
2	/**************************************************************************\
3	* phpGroupWare API - database support via ADOdb *
4	* ------------------------------------------------------------------------ *
5	* This program is free software; you can redistribute it and/or modify it *
6	* under the terms of the GNU Lesser General Public License as published *
7	* by the Free Software Foundation; either version 2.1 of the License, or *
8	* any later version. *
9	\**************************************************************************/
10
11
12	/*
13	* Database abstraction library
14	*
15	* This allows eGroupWare to use multiple database backends via ADOdb 4.20
16	*
17	* @package phpgwapi
18	* @subpackage db
19	* @author RalfBecker@outdoor-training.de
20	* @license LGPL
21	*/
22
23	if(empty($GLOBALS['phpgw_info']['server']['db_type']))
24	{
25	$GLOBALS['phpgw_info']['server']['db_type'] = 'mysql';
26	}
27	include_once(PHPGW_API_INC.'/adodb/adodb.inc.php');
28
29	class db
30	{
31	/**
32	* @var string $type database type
33	*/
34	var $Type = '';
35
36	/**
37	* @var string $Host database host to connect to
38	*/
39	var $Host = '';
40
41	/**
42	* @var string $Port port number of database to connect to
43	*/
44	var $Port = '';
45
46	/**
47	* @var string $Database name of database to use
48	*/
49	var $Database = '';
50
51	/**
52	* @var string $User name of database user
53	*/
54	var $User = '';
55
56	/**
57	* @var string $Password password for database user
58	*/
59	var $Password = '';
60
61	/**
62	* @var bool $auto_stripslashes automatically remove slashes when returning field values - default False
63	*/
64	var $auto_stripslashes = False;
65
66	/**
67	* @var int $Auto_Free automatically free results - 0 no, 1 yes
68	*/
69	var $Auto_Free = 0;
70
71	/**
72	* @var int $Debug enable debuging - 0 no, 1 yes
73	*/
74	var $Debug = 0;
75
76	/**
77	* @var string $Halt_On_Error "yes" (halt with message), "no" (ignore errors quietly), "report" (ignore errror, but spit a warning)
78	*/
79	var $Halt_On_Error = 'no';//'yes';
80
81	/**
82	* @var string $Seq_Table table for storing sequences ????
83	*/
84	var $Seq_Table = 'db_sequence';
85
86	/**
87	* @var array $Record current record
88	*/
89	var $Record = array();
90
91	/**
92	* @var int row number for current record
93	*/
94	var $Row;
95
96	/**
97	* @var int $Errno internal rdms error number for last error
98	*/
99	var $Errno = 0;
100
101	/**
102	* @var string descriptive text from last error
103	*/
104	var $Error = '';
105
106	//i am not documenting private vars - skwashd :)
107	var $xmlrpc = False;
108	var $soap = False;
109	var $Link_ID = 0;
110	var $privat_Link_ID = False; // do we use a privat Link_ID or a reference to the global ADOdb object
111	var $Query_ID = 0;
112
113	/**
114	* @param string $query query to be executed (optional)
115	*/
116
117	function db($query = '')
118	{
119	$this->query($query);
120	}
121
122	function db_($query='') {} // only for NOT useing ADOdb
123
124	/**
125	* @return int current connection id
126	*/
127	function link_id()
128	{
129	return $this->Link_ID;
130	}
131
132	/**
133	* @return int id of current query
134	*/
135	function query_id()
136	{
137	return $this->Query_ID;
138	}
139
140	/**
141	* Open a connection to a database
142	*
143	* @param string $Database name of database to use (optional)
144	* @param string $Host database host to connect to (optional)
145	* @param string $Port database port to connect to (optional)
146	* @param string $User name of database user (optional)
147	* @param string $Password password for database user (optional)
148	*/
149	function connect($Database = NULL, $Host = NULL, $Port = NULL, $User = NULL, $Password = NULL,$Type = NULL)
150	{
151	/* Handle defaults */
152	if (!is_null($Database) && $Database)
153	{
154	$this->Database = $Database;
155	}
156	if (!is_null($Host) && $Host)
157	{
158	$this->Host = $Host;
159	}
160	if (!is_null($Port) && $Port)
161	{
162	$this->Port = $Port;
163	}
164	if (!is_null($User) && $User)
165	{
166	$this->User = $User;
167	}
168	if (!is_null($Password) && $Password)
169	{
170	$this->Password = $Password;
171	}
172	if (!is_null($Type) && $Type)
173	{
174	$this->Type = $Type;
175	}
176	elseif (!$this->Type)
177	{
178	$this->Type = $GLOBALS['phpgw_info']['server']['db_type'];
179	}
180
181	if (!$this->Link_ID)
182	{
183	foreach(array('Host','Database','User','Password') as $name)
184	{
185	$$name = $this->$name;
186	}
187	$type = $this->Type;
188
189	switch($this->Type) // convert to ADO db-type-names
190	{
191	case 'pgsql':
192	$type = 'postgres';
193	// create our own pgsql connection-string, to allow unix domain soccets if !$Host
194	$Host = "dbname=$this->Database".($this->Host ? " host=$this->Host".($this->Port ? " port=$this->Port" : '') : '').
195	" user=$this->User".($this->Password ? " password='".addslashes($this->Password)."'" : '');
196	$User = $Password = $Database = ''; // to indicate $Host is a connection-string
197	break;
198	case 'mssql':
199	if ($this->Port) $Host .= ','.$this->Port;
200	break;
201	default:
202	if ($this->Port) $Host .= ':'.$this->Port;
203	break;
204	}
205
206	if ( ! isset( $GLOBALS[ 'phpgw' ] ) )
207	{
208	$GLOBALS[ 'phpgw' ] = new stdClass;
209	$GLOBALS[ 'phpgw' ]->ADOdb = NULL;
210	}
211
212	if (!is_object($GLOBALS['phpgw']->ADOdb) \|\| // we have no connection so far
213	(is_object($GLOBALS['phpgw']->db) && // we connect to a different db, then the global one
214	($this->Type != $GLOBALS['phpgw']->db->Type \|\|
215	$this->Database != $GLOBALS['phpgw']->db->Database \|\|
216	$this->User != $GLOBALS['phpgw']->db->User \|\|
217	$this->Host != $GLOBALS['phpgw']->db->Host \|\|
218	$this->Port != $GLOBALS['phpgw']->db->Port)))
219	{
220	if (!is_object($GLOBALS['phpgw']->ADOdb)) // use the global object to store the connection
221	{
222	$this->Link_ID = &$GLOBALS['phpgw']->ADOdb;
223	}
224	else
225	{
226	$this->privat_Link_ID = True; // remember that we use a privat Link_ID for disconnect
227	}
228	$this->Link_ID = ADONewConnection($type);
229	if (!$this->Link_ID)
230	{
231	$this->halt("No ADOdb support for '$type' !!!");
232	return 0; // in case error-reporting = 'no'
233	}
234	$connect = ( isset( $GLOBALS['phpgw_info']['server']['db_persistent'] ) && $GLOBALS['phpgw_info']['server']['db_persistent'] ) ? 'PConnect' : 'Connect';
235	if (!$this->Link_ID->$connect($Host, $User, $Password, $Database))
236	{
237	$this->halt("ADOdb::$connect($Host, $User, \$Password, $Database) failed.");
238	return 0; // in case error-reporting = 'no'
239	}
240	//echo "new ADOdb connection<pre>".print_r($GLOBALS['phpgw']->ADOdb,True)."</pre>\n";
241
242	if ($this->Type == 'mssql')
243	{
244	// this is the format ADOdb expects
245	$this->Link_ID->Execute('SET DATEFORMAT ymd');
246	// sets the limit to the maximum
247	ini_set('mssql.textlimit',2147483647);
248	ini_set('mssql.sizelimit',2147483647);
249	}
250	}
251	else
252	{
253	$this->Link_ID = &$GLOBALS['phpgw']->ADOdb;
254	}
255	}
256	return $this->Link_ID;
257	}
258
259	/**
260	* Close a connection to a database
261	*/
262	function disconnect()
263	{
264	if (!$this->privat_Link_ID)
265	{
266	unset($GLOBALS['phpgw']->ADOdb);
267	}
268	unset($this->Link_ID);
269	$this->Link_ID = 0;
270	}
271
272	/**
273	* Escape strings before sending them to the database
274	*
275	* @param string $str the string to be escaped
276	* @return string escaped sting
277	*/
278	function db_addslashes($str)
279	{
280	if (!isset($str) \|\| $str == '')
281	{
282	return '';
283	}
284	if (!$this->Link_ID && !$this->connect())
285	{
286	return False;
287	}
288	return $this->Link_ID->addq($str);
289	}
290
291	/**
292	* Convert a unix timestamp to a rdms specific timestamp
293	*
294	* @param int unix timestamp
295	* @return string rdms specific timestamp
296	*/
297	function to_timestamp($epoch)
298	{
299	if (!$this->Link_ID && !$this->connect())
300	{
301	return False;
302	}
303	// the substring is needed as the string is already in quotes
304	return substr($this->Link_ID->DBTimeStamp($epoch),1,-1);
305	}
306
307	/**
308	* Convert a rdms specific timestamp to a unix timestamp
309	*
310	* @param string rdms specific timestamp
311	* @return int unix timestamp
312	*/
313	function from_timestamp($timestamp)
314	{
315	if (!$this->Link_ID && !$this->connect())
316	{
317	return False;
318	}
319	return $this->Link_ID->UnixTimeStamp($timestamp);
320	}
321
322	/**
323	* @deprecated
324	* @see limit_query()
325	*/
326	function limit($start)
327	{}
328
329	/**
330	* Discard the current query result
331	*/
332	function free()
333	{
334	unset($this->Query_ID); // else copying of the db-object does not work
335	$this->Query_ID = 0;
336	}
337
338	/**
339	* Execute a query
340	*
341	* @param string $Query_String the query to be executed
342	* @param mixed $line the line method was called from - use __LINE__
343	* @param string $file the file method was called from - use __FILE__
344	* @param int $offset row to start from
345	* @param int $num_rows number of rows to return (optional), if unset will use $GLOBALS['phpgw_info']['user']['preferences']['common']['maxmatchs']
346	* @return int current query id if sucesful and null if fails
347	*/
348	function query($Query_String, $line = '', $file = '', $offset=0, $num_rows=-1)
349	{
350	if ($Query_String == '')
351	{
352	return 0;
353	}
354	if (!$this->Link_ID && !$this->connect())
355	{
356	return False;
357	}
358
359	# New query, discard previous result.
360	if ($this->Query_ID)
361	{
362	$this->free();
363	}
364	if ($this->Link_ID->fetchMode != ADODB_FETCH_BOTH)
365	{
366	$this->Link_ID->SetFetchMode(ADODB_FETCH_BOTH);
367	}
368	if (! $num_rows)
369	{
370	$num_rows = $GLOBALS['phpgw_info']['user']['preferences']['common']['maxmatchs'];
371	}
372	if ($num_rows > 0)
373	{
374	$this->Query_ID = $this->Link_ID->SelectLimit($Query_String,$num_rows,(int)$offset);
375	}
376	else
377	{
378	$this->Query_ID = $this->Link_ID->Execute($Query_String);
379	}
380	$this->Row = 0;
381	$this->Errno = $this->Link_ID->ErrorNo();
382	$this->Error = $this->Link_ID->ErrorMsg();
383
384	if (! $this->Query_ID)
385	{
386	$this->halt("Invalid SQL: ".$Query_String, $line, $file);
387	}
388	return $this->Query_ID;
389	}
390
391	/**
392	* Execute a query with limited result set
393	*
394	* @param string $Query_String the query to be executed
395	* @param int $offset row to start from
396	* @param mixed $line the line method was called from - use __LINE__
397	* @param string $file the file method was called from - use __FILE__
398	* @param int $num_rows number of rows to return (optional), if unset will use $GLOBALS['phpgw_info']['user']['preferences']['common']['maxmatchs']
399	* @return int current query id if sucesful and null if fails
400	*/
401	function limit_query($Query_String, $offset, $line = '', $file = '', $num_rows = '')
402	{
403	return $this->query($Query_String,$line,$file,$offset,$num_rows);
404	}
405
406	/**
407	* Move to the next row in the results set
408	*
409	* @return bool was another row found?
410	*/
411	function next_record()
412	{
413	if (!$this->Query_ID)
414	{
415	$this->halt('next_record called with no query pending.');
416	return 0;
417	}
418	if ($this->Row) // first row is already fetched
419	{
420	$this->Query_ID->MoveNext();
421	}
422	++$this->Row;
423
424	$this->Record = $this->Query_ID->fields;
425
426	if ($this->Query_ID->EOF \|\| !$this->Query_ID->RecordCount() \|\| !is_array($this->Record))
427	{
428	return False;
429	}
430
431	return True;
432	}
433
434	/**
435	* Move to position in result set
436	*
437	* @param int $pos required row (optional), default first row
438	* @return int 1 if sucessful or 0 if not found
439	*/
440	function seek($pos = 0)
441	{
442	if (!$this->Query_ID \|\| !$this->Query_ID->Move($this->Row = $pos))
443	{
444	$this->halt("seek($pos) failed: resultset has " . $this->num_rows() . " rows");
445	$this->Query_ID->Move( $this->num_rows() );
446	$this->Row = $this->num_rows();
447	return False;
448	}
449	return True;
450	}
451
452	/**
453	* Begin Transaction
454	*
455	* @return int current transaction id
456	*/
457	function transaction_begin()
458	{
459	if (!$this->Link_ID && !$this->connect())
460	{
461	return False;
462	}
463	//return $this->Link_ID->BeginTrans();
464	return $this->Link_ID->StartTrans();
465	}
466
467	/**
468	* Complete the transaction
469	*
470	* @return bool True if sucessful, False if fails
471	*/
472	function transaction_commit()
473	{
474	if (!$this->Link_ID && !$this->connect())
475	{
476	return False;
477	}
478	//return $this->Link_ID->CommitTrans();
479	return $this->Link_ID->CompleteTrans();
480	}
481
482	/**
483	* Rollback the current transaction
484	*
485	* @return bool True if sucessful, False if fails
486	*/
487	function transaction_abort()
488	{
489	if (!$this->Link_ID && !$this->connect())
490	{
491	return False;
492	}
493	//return $this->Link_ID->RollbackTrans();
494	return $this->Link_ID->FailTrans();
495	}
496
497	/**
498	* Find the primary key of the last insertion on the current db connection
499	*
500	* @param string $table name of table the insert was performed on
501	* @param string $field the autoincrement primary key of the table
502	* @return int the id, -1 if fails
503	*/
504	function get_last_insert_id($table, $field)
505	{
506	if (!$this->Link_ID && !$this->connect())
507	{
508	return False;
509	}
510	$id = $this->Link_ID->Insert_ID();
511
512	if ($id === False) // function not supported
513	{
514	echo "<p>db::get_last_insert_id(table='$table',field='$field') not yet implemented for db-type '$this->Type'</p>\n";
515	return -1;
516	}
517	if ($this->Type != 'pgsql' \|\| $id == -1)
518	{
519	return $id;
520	}
521	// pgsql code to transform the OID into the real id
522	$id = $this->Link_ID->GetOne("SELECT $field FROM $table WHERE oid=$id");
523
524	return $id !== False ? $id : -1;
525	}
526
527	/**
528	* Lock a table
529	*
530	* @param string $table name of table to lock
531	* @param string $mode type of lock required (optional), default write
532	* @return bool True if sucessful, False if fails
533	*/
534	function lock($table, $mode='write')
535	{}
536
537	/**
538	* Unlock a table
539	*
540	* @return bool True if sucessful, False if fails
541	*/
542	function unlock()
543	{}
544
545	/**
546	* Get the number of rows affected by last update
547	*
548	* @return int number of rows
549	*/
550	function affected_rows()
551	{
552	if (!$this->Link_ID && !$this->connect())
553	{
554	return False;
555	}
556	return $this->Link_ID->Affected_Rows();
557	}
558
559	/**
560	* Number of rows in current result set
561	*
562	* @return int number of rows
563	*/
564	function num_rows()
565	{
566	return $this->Query_ID ? $this->Query_ID->RecordCount() : False;
567	}
568
569	/**
570	* Number of fields in current row
571	*
572	* @return int number of fields
573	*/
574	function num_fields()
575	{
576	return $this->Query_ID ? $this->Query_ID->FieldCount() : False;
577	}
578
579	/**
580	* short hand for @see num_rows()
581	*/
582	function nf()
583	{
584	return $this->num_rows();
585	}
586
587	/**
588	* short hand for print @see num_rows
589	*/
590	function np()
591	{
592	print $this->num_rows();
593	}
594
595	/**
596	* Return the value of a column
597	*
598	* @param string/integer $Name name of field or positional index starting from 0
599	* @param bool $strip_slashes string escape chars from field(optional), default false
600	* @return string the field value
601	*/
602	function f($Name, $strip_slashes = False)
603	{
604	if ($strip_slashes \|\| ($this->auto_stripslashes && ! $strip_slashes))
605	{
606	return stripslashes($this->Record[$Name]);
607	}
608	else
609	{
610	return $this->Record[$Name];
611	}
612	}
613
614	/**
615	* Print the value of a field
616	*
617	* @param string $Name name of field to print
618	* @param bool $strip_slashes string escape chars from field(optional), default false
619	*/
620	function p($Name, $strip_slashes = True)
621	{
622	print $this->f($Name, $strip_slashes);
623	}
624
625	/**
626	* Returns a query-result-row as an associative array (no numerical keys !!!)
627	*
628	* @param bool $do_next_record should next_record() be called or not (default not)
629	* @return array/bool the associative array or False if no (more) result-row is availible
630	*/
631	function row($do_next_record=False)
632	{
633	if ($do_next_record && !$this->next_record() \|\| !is_array($this->Record))
634	{
635	return False;
636	}
637	$result = array();
638	foreach($this->Record as $column => $value)
639	{
640	if (!is_numeric($column))
641	{
642	$result[$column] = $value;
643	}
644	}
645	return $result;
646	}
647
648	/**
649	* Get the id for the next sequence - not implemented!
650	*
651	* This seems not to be used anywhere in eGroupWhere !!!
652	*
653	* @param string $seq_name name of the sequence
654	* @return int sequence id
655	*/
656	function nextid($seq_name)
657	{
658	echo "<p>db::nextid(sequence='$seq_name') not yet implemented</p>\n";
659	}
660
661	/**
662	* Error handler
663	*
664	* @param string $msg error message
665	* @param int $line line of calling method/function (optional)
666	* @param string $file file of calling method/function (optional)
667	*/
668	function halt($msg, $line = '', $file = '')
669	{
670	if ($this->Link_ID) // only if we have a link, else infinite loop
671	{
672	$this->Error = $this->Link_ID->ErrorMsg(); // need to be BEFORE unlock,
673	$this->Errno = $this->Link_ID->ErrorNo(); // else we get its error or none
674
675	$this->unlock(); /* Just in case there is a table currently locked */
676	}
677	if ($this->Halt_On_Error == "no")
678	{
679	return;
680	}
681	$this->haltmsg($msg);
682
683	if ($file)
684	{
685	printf("<br><b>File:</b> %s",$file);
686	}
687	if ($line)
688	{
689	printf("<br><b>Line:</b> %s",$line);
690	}
691	printf("<br><b>Function:</b> %s\n",function_backtrace(2));
692
693	if ($this->Halt_On_Error != "report")
694	{
695	echo "<p><b>Session halted.</b>";
696	if (is_object($GLOBALS['phpgw']->common))
697	{
698	$GLOBALS['phpgw']->common->phpgw_exit(True);
699	}
700	else // happens eg. in setup
701	{
702	exit();
703	}
704	}
705	}
706
707	function haltmsg($msg)
708	{
709	printf("<p><b>Database error:</b> %s<br>\n", $msg);
710	if (($this->Errno \|\| $this->Error) && $this->Error != "()")
711	{
712	printf("<b>$this->Type Error</b>: %s (%s)<br>\n",$this->Errno,$this->Error);
713	}
714	}
715
716	/**
717	* Get description of a table
718	*
719	* Beside the column-name all other data depends on the db-type !!!
720	*
721	* @param string $table name of table to describe
722	* @param bool $full optional, default False summary information, True full information
723	* @return array table meta data
724	*/
725	function metadata($table='',$full=false)
726	{
727	if (!$this->Link_ID && !$this->connect())
728	{
729	return False;
730	}
731	$columns = $this->Link_ID->MetaColumns($table);
732	//$columns = $this->Link_ID->MetaColumnsSQL($table);
733	//echo "<b>metadata</b>('$table')=<pre>\n".print_r($columns,True)."</pre>\n";
734
735	$metadata = array();
736	$i = 0;
737	foreach($columns as $column)
738	{
739	// for backwards compatibilty (depreciated)
740	unset($flags);
741	if($column->auto_increment) $flags .= "auto_increment ";
742	if($column->primary_key) $flags .= "primary_key ";
743	if($column->binary) $flags .= "binary ";
744
745	// _debug_array($column);
746	$metadata[$i] = array(
747	'table' => $table,
748	'name' => $column->name,
749	'type' => $column->type,
750	'len' => $column->max_length,
751	'flags' => $flags, // for backwards compatibilty (depreciated) used by JiNN atm
752	'not_null' => $column->not_null,
753	'auto_increment' => $column->auto_increment,
754	'primary_key' => $column->primary_key,
755	'binary' => $column->binary,
756	'has_default' => $column->has_default,
757	'default' => $column->default_value,
758	);
759	$metadata[$i]['table'] = $table;
760	if ($full)
761	{
762	$metadata['meta'][$column->name] = $i;
763	}
764	++$i;
765	}
766	if ($full)
767	{
768	$metadata['num_fields'] = $i;
769	}
770	return $metadata;
771	}
772
773	/**
774	* Get a list of table names in the current database
775	*
776	* @return array list of the tables
777	*/
778	function table_names()
779	{
780	if (!$this->Link_ID && !$this->connect())
781	{
782	return False;
783	}
784	$result = array();
785	$tables = $this->Link_ID->MetaTables('TABLES');
786	if (is_array($tables))
787	{
788	foreach($tables as $table)
789	{
790	$result[] = array(
791	'table_name' => $table,
792	'tablespace_name' => $this->Database,
793	'database' => $this->Database
794	);
795	}
796	}
797	return $result;
798	}
799
800	/**
801	* Return a list of indexes in current database
802	*
803	* @return array list of indexes
804	*/
805	function index_names()
806	{
807	$indices = array();
808	if ($this->Type != 'pgsql')
809	{
810	echo "<p>db::index_names() not yet implemented for db-type '$this->Type'</p>\n";
811	return $indices;
812	}
813	$this->query("SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relkind ='i' ORDER BY relname");
814	while ($this->next_record())
815	{
816	$indices[] = array(
817	'index_name' => $this->f(0),
818	'tablespace_name' => $this->Database,
819	'database' => $this->Database,
820	);
821	}
822	return $indices;
823	}
824
825	/**
826	* Returns an array containing column names that are the primary keys of $tablename.
827	*
828	* @return array of columns
829	*/
830	function pkey_columns($tablename)
831	{
832	if (!$this->Link_ID && !$this->connect())
833	{
834	return False;
835	}
836	return $this->Link_ID->MetaPrimaryKeys($tablename);
837	}
838
839	/**
840	* Create a new database
841	*
842	* @param string $adminname name of database administrator user (optional)
843	* @param string $adminpasswd password for the database administrator user (optional)
844	*/
845	function create_database($adminname = '', $adminpasswd = '')
846	{
847	$currentUser = $this->User;
848	$currentPassword = $this->Password;
849	$currentDatabase = $this->Database;
850
851	$extra = array();
852	switch ($this->Type)
853	{
854	case 'pgsql':
855	$meta_db = 'template1';
856	break;
857	case 'mysql':
858	$meta_db = 'mysql';
859	$extra[] = "grant all on $currentDatabase.* to $currentUser@localhost identified by '$currentPassword'";
860	break;
861	default:
862	echo "<p>db::create_database(user='$adminname',\$pw) not yet implemented for DB-type '$this->Type'</p>\n";
863	break;
864	}
865	if ($adminname != '')
866	{
867	$this->User = $adminname;
868	$this->Password = $adminpasswd;
869	$this->Database = $meta_db;
870	}
871	$this->disconnect();
872	$this->query("CREATE DATABASE $currentDatabase");
873	foreach($extra as $sql)
874	{
875	$this->query($sql);
876	}
877	$this->disconnect();
878
879	$this->User = $currentUser;
880	$this->Password = $currentPassword;
881	$this->Database = $currentDatabase;
882	$this->connect();
883	}
884
885	/**
886	* concat a variable number of strings together, to be used in a query
887	*
888	* Example: $db->concat($db->quote('Hallo '),'username') would return
889	* for mysql "concat('Hallo ',username)" or "'Hallo ' \|\| username" for postgres
890	* @param $str1 string already quoted stringliteral or column-name, variable number of arguments
891	* @return string to be used in a query
892	*/
893	function concat($str1)
894	{
895	$args = func_get_args();
896
897	if (!$this->Link_ID && !$this->connect())
898	{
899	return False;
900	}
901	return call_user_func_array(array(&$this->Link_ID,'concat'),$args);
902	}
903
904	/**
905	* Correctly Quote Identifiers like table- or colmnnames for use in SQL-statements
906	*
907	* This is mostly copy & paste from adodb's datadict class
908	* @param $name string
909	* @return string quoted string
910	*/
911	function name_quote($name = NULL)
912	{
913	if (!is_string($name)) {
914	return FALSE;
915	}
916
917	$name = trim($name);
918
919	if (!$this->Link_ID && !$this->connect())
920	{
921	return False;
922	}
923
924	$quote = $this->Link_ID->nameQuote;
925
926	// if name is of the form `name`, quote it
927	if ( preg_match('/^`(.+)`$/', $name, $matches) ) {
928	return $quote . $matches[1] . $quote;
929	}
930
931	// if name contains special characters, quote it
932	if ( preg_match('/\W/', $name) ) {
933	return $quote . $name . $quote;
934	}
935
936	return $name;
937	}
938
939	/**
940	* Escape values before sending them to the database - prevents SQL injunction and SQL errors ;-)
941	*
942	* Please note that the quote function already returns necessary quotes: quote('Hello') === "'Hello'".
943	* Int and Auto types are casted to int: quote('1','int') === 1, quote('','int') === 0, quote('Hello','int') === 0
944	*
945	* @param $value mixed the value to be escaped
946	* @param $type string the type of the db-column, default False === varchar
947	* @return string escaped sting
948	*/
949	function quote($value,$type=False)
950	{
951	if ($this->Debug) echo "<p>db::quote('$value','$type')</p>\n";
952
953	switch($type)
954	{
955	case 'int':
956	case 'auto':
957	return (int) $value;
958	}
959	if (!$this->Link_ID && !$this->connect())
960	{
961	return False;
962	}
963	switch($type)
964	{
965	case 'blob':
966	switch ($this->Link_ID->blobEncodeType)
967	{
968	case 'C': // eg. postgres
969	return "'" . $this->Link_ID->BlobEncode($value) . "'";
970	case 'I':
971	return $this->Link_ID->BlobEncode($value);
972	}
973	break; // handled like strings
974	case 'date':
975	return $this->Link_ID->DBDate($value);
976	case 'timestamp':
977	return $this->Link_ID->DBTimeStamp($value);
978	}
979	return $this->Link_ID->qstr($value);
980	}
981
982	/**
983	* Implodes an array of column-value pairs for the use in sql-querys.
984	* All data is run through quote (does either addslashes() or (int)) - prevents SQL injunction and SQL errors ;-).
985	*
986	* @author RalfBecker<at>outdoor-training.de
987	*
988	* @param $glue string in most cases this will be either ',' or ' AND ', depending you your query
989	* @param $array array column-name / value pairs, if the value is an array all its array-values will be quoted
990	* according to the type of the column, and the whole array with be formatted like (val1,val2,...)
991	* If $use_key == True, an ' IN ' instead a '=' is used. Good for category- or user-lists.
992	* If the key is numerical (no key given in the array-definition) the value is used as is, eg.
993	* array('visits=visits+1') gives just "visits=visits+1" (no quoting at all !!!)
994	* @param $use_key boolean/string If $use_key===True a "$key=" prefix each value (default), typically set to False
995	* or 'VALUES' for insert querys, on 'VALUES' "(key1,key2,...) VALUES (val1,val2,...)" is returned
996	* @param $only array/boolean if set to an array only colums which are set (as data !!!) are written
997	* typicaly used to form a WHERE-clause from the primary keys.
998	* If set to True, only columns from the colum_definitons are written.
999	* @param $column_definitions array/boolean this can be set to the column-definitions-array
1000	* of your table ($tables_baseline[$table]['fd'] of the setup/tables_current.inc.php file).
1001	* If its set, the column-type-data determinates if (int) or addslashes is used.
1002	*/
1003	function column_data_implode($glue,$array,$use_key=True,$only=False,$column_definitions=False)
1004	{
1005	if (!is_array($array)) // this allows to give an SQL-string for delete or update
1006	{
1007	return $array;
1008	}
1009	if (!$column_definitions)
1010	{
1011	$column_definitions = $this->column_definitions;
1012	}
1013	if ($this->Debug) echo "<p>db::column_data_implode('$glue',".print_r($array,True).",'$use_key',".print_r($only,True).",<pre>".print_r($column_definitions,True)."</pre>\n";
1014
1015	$keys = $values = array();
1016	foreach($array as $key => $data)
1017	{
1018	if (!$only \|\| $only === True && isset($column_definitions[$key]) \|\| is_array($only) && in_array($key,$only))
1019	{
1020	$keys[] = $this->name_quote($key);
1021
1022	$column_type = is_array($column_definitions) ? @$column_definitions[$key]['type'] : False;
1023
1024	if (is_array($data))
1025	{
1026	foreach($data as $k => $v)
1027	{
1028	$data[$k] = $this->quote($v,$column_type);
1029	}
1030	$values[] = ($use_key===True ? $key.' IN ' : '') . '('.implode(',',$data).')';
1031	}
1032	elseif (is_int($key) && $use_key===True)
1033	{
1034	$values[] = $data;
1035	}
1036	else
1037	{
1038	$values[] = ($use_key===True ? $this->name_quote($key) . '=' : '') . $this->quote($data,$column_type);
1039	}
1040	}
1041	}
1042	return ($use_key==='VALUES' ? '('.implode(',',$keys).') VALUES (' : '').
1043	implode($glue,$values) . ($use_key==='VALUES' ? ')' : '');
1044	}
1045
1046	/**
1047	* Sets the default column-definitions for use with column_data_implode()
1048	*
1049	* @author RalfBecker<at>outdoor-training.de
1050	*
1051	* @param $column_definitions array/boolean this can be set to the column-definitions-array
1052	* of your table ($tables_baseline[$table]['fd'] of the setup/tables_current.inc.php file).
1053	* If its set, the column-type-data determinates if (int) or addslashes is used.
1054	*/
1055	function set_column_definitions($column_definitions=False)
1056	{
1057	$this->column_definitions=$column_definitions;
1058	}
1059
1060	function set_app($app)
1061	{
1062	$this->app = $app;
1063	}
1064
1065	/**
1066	* reads the table-definitions from the app's setup/tables_current.inc.php file
1067	*
1068	* The already read table-definitions are shared between all db-instances via $GLOBALS['phpgw_info']['apps'][$app]['table_defs']
1069	*
1070	* @author RalfBecker<at>outdoor-training.de
1071	*
1072	* @param $app bool/string name of the app or default False to use the app set by db::set_app or the current app
1073	* @param $table bool/string if set return only defintions of that table, else return all defintions
1074	* @return mixed array with table-defintions or False if file not found
1075	*/
1076	function get_table_definitions($app=False,$table=False)
1077	{
1078	if (!$app)
1079	{
1080	$app = $this->app ? $this->app : $GLOBALS['phpgw_info']['flags']['currentapp'];
1081	}
1082	if (isset($GLOBALS['phpgw_info']['apps'])) // dont set it, if it does not exist!!!
1083	{
1084	$this->app_data = &$GLOBALS['phpgw_info']['apps'][$app];
1085	}
1086	// this happens during the eGW startup or in setup
1087	else
1088	{
1089	$this->app_data =& $this->all_app_data[$app];
1090	}
1091	if (!isset($this->app_data['table_defs']))
1092	{
1093	$tables_current = PHPGW_INCLUDE_ROOT . "/$app/setup/tables_current.inc.php";
1094	if (!@file_exists($tables_current))
1095	{
1096	return $this->app_data['table_defs'] = False;
1097	}
1098	include($tables_current);
1099	$this->app_data['table_defs'] = $phpgw_baseline;
1100	}
1101	if ($table && (!$this->app_data['table_defs'] \|\| !isset($this->app_data['table_defs'][$table])))
1102	{
1103	return False;
1104	}
1105	return $table ? $this->app_data['table_defs'][$table] : $this->app_data['table_defs'];
1106	}
1107
1108	/**
1109	* Insert a row of data into a table, all data is quoted according to it's type
1110	*
1111	* @author RalfBecker<at>outdoor-training.de
1112	*
1113	* @param $table string name of the table
1114	* @param $data array with column-name / value pairs
1115	* @param $where mixed array with column-name / values pairs to check if a row with that keys already exists,
1116	* if the row exists db::update is called else a new row with $date merged with $where gets inserted (data has precedence)
1117	* @param $line int line-number to pass to query
1118	* @param $file string file-name to pass to query
1119	* @param $app mixed string with name of app or False to use the current-app
1120	* @return object/boolean Query_ID of the call to db::query, or True if we had to do an update
1121	*/
1122	function insert($table,$data,$where,$line,$file,$app=False)
1123	{
1124	if ($this->Debug) echo "<p>db::insert('$table',".print_r($data,True).",".print_r($where,True).",$line,$file,'$app')</p>\n";
1125
1126	$table_def = $this->get_table_definitions($app,$table);
1127
1128	if (is_array($where) && count($where))
1129	{
1130	$this->select($table,'count(*)',$where,$line,$file);
1131	if ($this->next_record() && $this->f(0))
1132	{
1133	return !!$this->update($table,$data,$where,$line,$file,$app);
1134	}
1135	$data = array_merge($where,$data); // the checked values need to be inserted too, value in data has precedence
1136	}
1137	$sql = "INSERT INTO $table ".$this->column_data_implode(',',$data,'VALUES',False,$table_def['fd']);
1138
1139	return $this->query($sql,$line,$file);
1140	}
1141
1142	function persist_shared_groups($location,$account_id)
1143	{
1144
1145	$sql = "delete from phpgw_cc_contact_grps where oid in (
1146	select A.oid from phpgw_cc_contact_grps A inner join phpgw_cc_groups B on A.id_group=B.id_group
1147	inner join phpgw_cc_contact_conns C on A.id_connection=C.id_connection
1148	inner join phpgw_cc_contact D on C.id_contact=D.id_contact
1149	where B.owner=$location and D.id_owner=$account_id );";
1150
1151	return $this->query($sql);
1152	}
1153
1154	/**
1155	* Updates the data of one or more rows in a table, all data is quoted according to it's type
1156	*
1157	* @author RalfBecker<at>outdoor-training.de
1158	*
1159	* @param $table string name of the table
1160	* @param $data array with column-name / value pairs
1161	* @param $where array column-name / values pairs and'ed together for the where clause
1162	* @param $line int line-number to pass to query
1163	* @param $file string file-name to pass to query
1164	* @param $app mixed string with name of app or False to use the current-app
1165	* @return the return-value of the call to db::query
1166	*/
1167	function update($table,$data,$where,$line,$file,$app=False)
1168	{
1169	$table_def = $this->get_table_definitions($app,$table);
1170	$sql = "UPDATE $table SET ".
1171	$this->column_data_implode(',',$data,True,False,$table_def['fd']).' WHERE '.
1172	$this->column_data_implode(' AND ',$where,True,False,$table_def['fd']);
1173
1174	return $this->query($sql,$line,$file);
1175	}
1176
1177	/**
1178	* Deletes one or more rows in table, all data is quoted according to it's type
1179	*
1180	* @author RalfBecker<at>outdoor-training.de
1181	*
1182	* @param $table string name of the table
1183	* @param $where array column-name / values pairs and'ed together for the where clause
1184	* @param $line int line-number to pass to query
1185	* @param $file string file-name to pass to query
1186	* @param $app mixed string with name of app or False to use the current-app
1187	* @return the return-value of the call to db::query
1188	*/
1189	function delete($table,$where,$line,$file,$app=False)
1190	{
1191	$table_def = $this->get_table_definitions($app,$table);
1192	$sql = "DELETE FROM $table WHERE ".
1193	$this->column_data_implode(' AND ',$where,True,False,$table_def['fd']);
1194
1195	return $this->query($sql,$line,$file);
1196	}
1197
1198	/**
1199	* Formats and quotes a sql expression to be used eg. as where-clause
1200	*
1201	* The function has a variable number of arguments, from which the expession gets constructed
1202	* eg. db::expression('my_table','(',array('name'=>"test'ed",'lang'=>'en'),') OR ',array('owner'=>array('',4,10)))
1203	* gives "(name='test\'ed' AND lang='en') OR 'owner' IN (0,4,5,6,10)" if name,lang are strings and owner is an integer
1204	* @param $table string name of the table
1205	* @param $args mixed variable number of arguments of the following types:
1206	* string: get's as is into the result
1207	* array: column-name / value pairs: the value gets quoted according to the type of the column and prefixed
1208	* with column-name=, multiple pairs are AND'ed together, see db::column_data_implode
1209	* bool: If False or is_null($arg): the next 2 (!) arguments gets ignored
1210	* @return string the expression generated from the arguments
1211	*/
1212	function expression($table,$args)
1213	{
1214	$table_def = $this->get_table_definitions($app,$table);
1215	$sql = '';
1216	$ignore_next = 0;
1217	foreach(func_get_args() as $n => $arg)
1218	{
1219	if ($n < 1) continue; // table-name
1220
1221	if ($ignore_next)
1222	{
1223	--$ignore_next;
1224	continue;
1225	}
1226	if (is_null($arg)) $arg = False;
1227
1228	switch(gettype($arg))
1229	{
1230	case 'string':
1231	$sql .= $arg;
1232	break;
1233	case 'boolean':
1234	$ignore_next += !$arg ? 2 : 0;
1235	break;
1236	case 'array':
1237	$sql .= $this->column_data_implode(' AND ',$arg,True,False,$table_def['fd']);
1238	break;
1239	}
1240	}
1241	if ($this->Debug) echo "<p>db::expression($table,<pre>".print_r(func_get_args(),True)."</pre>) ='$sql'</p>\n";
1242	return $sql;
1243	}
1244
1245	/**
1246	* Selects one or more rows in table depending on where, all data is quoted according to it's type
1247	*
1248	* @author RalfBecker<at>outdoor-training.de
1249	*
1250	* @param $table string name of the table
1251	* @param $cols mixed string or array of column-names / select-expressions
1252	* @param $where array/string string or array with column-name / values pairs AND'ed together for the where clause
1253	* @param $line int line-number to pass to query
1254	* @param $file string file-name to pass to query
1255	* @param $offset int/bool offset for a limited query or False (default)
1256	* @param string $append string to append to the end of the query, eg. ORDER BY ...
1257	* @param $app mixed string with name of app or False to use the current-app
1258	* @return the return-value of the call to db::query
1259	*/
1260	function select($table,$cols,$where,$line,$file,$offset=False,$append='',$app=False)
1261	{
1262	if ($this->Debug) echo "<p>db::select('$table',".print_r($cols,True).",".print_r($where,True).",$line,$file,$offset,'$app')</p>\n";
1263
1264	$table_def = $this->get_table_definitions($app,$table);
1265	if (is_array($cols))
1266	{
1267	$cols = implode(',',$cols);
1268	}
1269	if (is_array($where))
1270	{
1271	$where = $this->column_data_implode(' AND ',$where,True,False,$table_def['fd']);
1272	}
1273	$sql = "SELECT $cols FROM $table WHERE ".($where ? $where : '1=1').
1274	($append ? ' '.$append : '');
1275
1276	if ($this->Debug) echo "<p>sql='$sql'</p>";
1277
1278	return $this->query($sql,$line,$file,$offset,$offset===False ? -1 : 0);
1279	}
1280	}

Note: See TracBrowser for help on using the repository browser.

Download in other formats: