Context Navigation

source: trunk/phpgwapi/inc/class.db.inc.php @ 2

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

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

Download in other formats: