1<?php
2 	/**
3	* Database class for PostgreSQL
4	* @author SH Online Dienst GmbH Boris Erdmann, Kristian Koehntopp
5    * @author Dan Kuykendall, Michael Dean, Dave Hall and others
6	* @copyright Copyright (C) 1998-2000 SH Online Dienst GmbH Boris Erdmann, Kristian Koehntopp
7	* @copyright Portions Copyright (C) 2001-2004 Free Software Foundation, Inc. http://www.fsf.org/
8	* @license http://www.fsf.org/licenses/lgpl.html GNU Lesser General Public License
9	* @link http://www.sanisoft.com/phplib/manual/DB_sql.php
10	* @package phpgwapi
11	* @subpackage database
12	* @version $Id: class.db_pgsql.inc.php 21209 2010-05-02 20:16:48Z Caeies $
13	*/
14
15	/**
16	* Database class for PostgreSQL
17	*
18	* @package phpgwapi
19	* @subpackage database
20	* @ignore
21	*/
22	class db extends db_
23	{
24		var $Seq_Table = 'db_sequence';
25
26		// PostgreSQL changed somethings from 6.x -> 7.x
27		var $db_version;
28
29		function ifadd($add, $me)
30		{
31			if($add != '')
32			{
33				return ' ' . $me . $add;
34			}
35		}
36
37		/**
38		* Constructor
39		*
40		* @param $query SQL query
41		*/
42		function db($query = '')
43		{
44			$this->db_($query);
45		}
46
47		/**
48		 * Connect to database
49		 *
50		 * @param string $Database Database name
51		 * @param string $Host Database host
52		 * @param string $User Database user
53		 * @param string $Password Database users password
54		 * @return resource Database connection_id
55		 */
56		function connect($Database = '', $Host = '', $User = '', $Password = '')
57		{
58			/* Handle defaults */
59			if ($Database == '')
60			{
61				$Database = $this->Database;
62			}
63			if ($Host == '')
64			{
65				$Host     = $this->Host;
66			}
67			if ($User == '')
68			{
69				$User     = $this->User;
70			}
71			if ($Password == '')
72			{
73				$Password = $this->Password;
74			}
75			if(($pos = strpos($Host, ':')) !== False)
76			{
77				$Port = substr($Host, $pos + 1);
78				$Host = substr($Host, 0, $pos);
79			}
80
81			if (! $this->Link_ID)
82			{
83				$cstr = 'dbname=' . $Database
84					. $this->ifadd($Host, 'host=')
85					. $this->ifadd($Port, 'port=')
86					. $this->ifadd($User, 'user=')
87					. $this->ifadd("'".$Password."'", 'password=');
88				if ($GLOBALS['phpgw_info']['server']['db_persistent'])
89				{
90					$this->Link_ID=@pg_pconnect($cstr);
91				}
92				else
93				{
94					$this->Link_ID=@pg_connect($cstr);
95				}
96
97				if (! $this->Link_ID)
98				{
99					$this->halt('Link-ID == false, '.($GLOBALS['phpgw_info']['server']['db_persistent']?'p':'').'connect failed');
100					return 0;
101				}
102				else
103				{
104					$this->query("select version()",__LINE__,__FILE__);
105					$this->next_record();
106
107					$version          = $this->f('version');
108					$parts            = explode(' ',$version);
109					$this->db_version = $parts[1];
110				}
111			}
112			return $this->Link_ID;
113		}
114
115        /**
116         * Convert a unix timestamp to a rdms specific timestamp
117         *
118         * @param int unix timestamp
119         * @return string rdms specific timestamp
120         */
121		function to_timestamp($epoch)
122		{
123			$db_version = $this->db_version;
124			if (floor($db_version) == 6)
125			{
126				return $this->to_timestamp_6($epoch);
127			}
128			else
129			{
130				return $this->to_timestamp_7($epoch);
131			}
132		}
133
134        /**
135         * Convert a rdms specific timestamp to a unix timestamp
136         *
137         * @param string rdms specific timestamp
138         * @return int unix timestamp
139         */
140		function from_timestamp($timestamp)
141		{
142			if (floor($this->db_version) == 6)
143			{
144				return $this->from_timestamp_6($timestamp);
145			}
146			else
147			{
148				return $this->from_timestamp_7($timestamp);
149			}
150		}
151
152        /**
153         * Convert a unix timestamp to a rdms specific timestamp in PostgreSQL 6
154         *
155         * @param int unix timestamp
156         * @return string rdms specific timestamp
157		 * @access private
158         */
159		function to_timestamp_6($epoch)
160		{
161
162		}
163
164        /**
165         * Convert a rdms specific timestamp to a unix timestamp for PostgreSQL 6
166         *
167         * @param string rdms specific timestamp
168         * @return int unix timestamp
169		 * @access private
170         */
171		function from_timestamp_6($timestamp)
172		{
173
174		}
175
176        /**
177         * Convert a unix timestamp to a rdms specific timestamp in PostgreSQL 7
178         *
179         * @param int unix timestamp
180         * @return string rdms specific timestamp
181		 * @access private
182         */
183		function to_timestamp_7($epoch)
184		{
185			// This needs the GMT offset!
186			return date('Y-m-d H:i:s-00',$epoch);
187		}
188
189        /**
190         * Convert a rdms specific timestamp to a unix timestamp for PostgreSQL 6
191         *
192         * @param string rdms specific timestamp
193         * @return int unix timestamp
194		 * @access private
195         */
196		function from_timestamp_7($timestamp)
197		{
198			ereg('([0-9]{4})-([0-9]{2})-([0-9]{2}) ([0-9]{2}):([0-9]{2}):([0-9]{2})',$timestamp,$parts);
199
200			return mktime($parts[4],$parts[5],$parts[6],$parts[2],$parts[3],$parts[1]);
201		}
202
203		/**
204		 * Disconnect database connection
205		 *
206		 * This only affects systems not using persistant connections
207		 * @return integer 1: ok; 0: not possible/already closed
208		 */
209		function disconnect()
210		{
211			return @pg_close($this->Link_ID);
212		}
213
214        /**
215         * Execute a query
216         *
217         * @param string $Query_String the query to be executed
218         * @param mixed $line the line method was called from - use __LINE__
219         * @param string $file the file method was called from - use __FILE__
220         * @return integer Current result if sucesful and null if failed
221         */
222		function query($Query_String, $line = '', $file = '')
223		{
224			if ($Query_String == '')
225			{
226				return 0;
227			}
228			if (!$this->connect())
229			{
230				return 0; /* we already complained in connect() about that. */
231			};
232			# New query, discard previous result.
233			if ($this->Query_ID)
234			{
235				$this->free();
236			}
237
238			if ($this->Debug)
239			{
240				printf("Debug: query = %s<br />\n", $Query_String);
241			}
242
243			$this->Query_ID = @pg_Exec($this->Link_ID, $Query_String);
244			$this->Row   = 0;
245
246			$this->Error = @pg_ErrorMessage($this->Link_ID);
247			$this->Errno = ($this->Error == '') ? 0 : 1;
248			if (! $this->Query_ID)
249			{
250				$this->halt('Invalid SQL: ' . $Query_String, $line, $file);
251				return 0;
252			}
253			# Will return nada if it fails. That's fine.
254			return $this->Query_ID;
255		}
256
257        /**
258         * Execute a query with limited result set
259         *
260         * @param string $Query_String the query to be executed
261         * @param integer $offset row to start from
262         * @param mixed $line the line method was called from - use __LINE__
263         * @param string $file the file method was called from - use __FILE__
264         * @param int $num_rows number of rows to return (optional), if unset will use $GLOBALS['phpgw_info']['user']['preferences']['common']['maxmatchs']
265         * @return integer Current result if sucesful and null if failed
266         */
267		function limit_query($Query_String, $offset, $line = '', $file = '', $num_rows = 0)
268		{
269			$offset		= intval($offset);
270			$num_rows	= intval($num_rows);
271
272			if ($num_rows == 0)
273			{
274				$maxmatches = $GLOBALS['phpgw_info']['user']['preferences']['common']['maxmatchs'];
275				$num_rows = (isset($maxmatches)?intval($maxmatches):15);
276			}
277
278			if ($offset == 0)
279			{
280				$Query_String .= ' LIMIT ' . $num_rows;
281			}
282			else
283			{
284				$Query_String .= ' LIMIT ' . $num_rows . ' OFFSET ' . $offset;
285			}
286
287			if ($this->Debug)
288			{
289				printf("Debug: limit_query = %s<br />offset=%d, num_rows=%d<br />\n", $Query_String, $offset, $num_rows);
290			}
291
292			return $this->query($Query_String, $line, $file);
293		}
294
295		function free()
296		{
297			@pg_freeresult($this->Query_ID);
298			$this->Query_ID = 0;
299		}
300
301        /**
302         * Move to the next row in the results set
303         *
304         * @return boolean was another row found?
305         */
306		function next_record()
307		{
308			$this->Record = @pg_fetch_array($this->Query_ID, $this->Row++);
309
310			if ($this->Link_ID)
311			{
312				$this->Error = pg_ErrorMessage($this->Link_ID);
313				$this->Errno = ($this->Error == '') ? 0 : 1;
314			}
315
316			$stat = is_array($this->Record);
317			if (!$stat && $this->Auto_Free)
318			{
319				pg_freeresult($this->Query_ID);
320				$this->Query_ID = 0;
321			}
322			return $stat;
323		}
324
325        /**
326         * Move to position in result set
327         *
328         * @param integer $pos Required row (optional), default first row
329         * @return integer 1 if sucessful or 0 if not found
330         */
331		function seek($pos)
332		{
333			$this->Row = $pos;
334		}
335
336		function transaction_begin()
337		{
338			return $this->query('begin');
339		}
340
341        /**
342         * Complete the transaction
343         *
344         * @return boolean True if sucessful, False if failed
345         */
346		function transaction_commit()
347		{
348			if (! $this->Errno)
349			{
350				return pg_Exec($this->Link_ID,'commit');
351			}
352			else
353			{
354				return False;
355			}
356		}
357
358        /**
359         * Rollback the current transaction
360         *
361         * @return boolean True if sucessful, False if failed
362         */
363		function transaction_abort()
364		{
365			return @pg_Exec($this->Link_ID,'rollback');
366		}
367
368        /**
369         * Find the primary key of the last insertion on the current db connection
370         *
371         * @param string $table name of table the insert was performed on
372         * @param string $field the autoincrement primary key of the table
373         * @return integer The id, -1 if failed
374         */
375		function get_last_insert_id($table, $field)
376		{
377			if (!isset($table) || $table == '' || !isset($field) || $field == '')
378			{
379				return -1;
380			}
381
382			$params = explode('.',$this->db_version);
383
384			if ($params[0] < 8 || ($params[0] == 8 && $params[1] ==0))
385			{
386				$oid = pg_getlastoid($this->Query_ID);
387				if ($oid == -1)
388				{
389					return -1;
390				}
391
392				$result = @pg_Exec($this->Link_ID, "select $field from $table where oid=$oid");
393			}
394			else
395			{
396				$result = @pg_Exec($this->Link_ID, "select lastval()");
397			}
398
399			if (!$result)
400			{
401				return -1;
402			}
403
404			$Record = @pg_fetch_array($result, 0);
405			@pg_freeresult($result);
406			if (!is_array($Record)) /* OID not found? */
407			{
408				return -1;
409			}
410
411			return $Record[0];
412		}
413
414        /**
415         * Lock a table
416         *
417         * @param string $table name of table to lock
418         * @param string $mode type of lock required (optional), default write
419         * @return boolean True if sucessful, False if failed
420         */
421		function lock($table, $mode = 'write')
422		{
423			$result = $this->transaction_begin();
424
425			if ($mode == 'write')
426			{
427				if (is_array($table))
428				{
429					while ($t = each($table))
430					{
431						$result = pg_Exec($this->Link_ID,'lock table ' . $t[1] . ' in share mode');
432					}
433				}
434				else
435				{
436					$result = pg_Exec($this->Link_ID, 'lock table ' . $table . ' in share mode');
437				}
438			}
439			else
440			{
441				$result = 1;
442			}
443
444			return $result;
445		}
446
447        /**
448         * Unlock a table
449         *
450         * @return boolean True if sucessful, False if failed
451         */
452		function unlock()
453		{
454			return $this->transaction_commit();
455		}
456
457
458        /**
459         * Get the id for the next sequence
460         *
461         * @param string $seq_name Name of the sequence
462         * @return integer sequence id
463         */
464		function nextid($seq_name)
465		{
466			$this->connect();
467
468			if ($this->lock($this->Seq_Table))
469			{
470				/* get sequence number (locked) and increment */
471				$q  = sprintf("select nextid from %s where seq_name = '%s'",
472					$this->Seq_Table,
473					$seq_name);
474				$id  = @pg_Exec($this->Link_ID, $q);
475				$res = @pg_Fetch_Array($id, 0);
476
477				/* No current value, make one */
478				if (!is_array($res))
479				{
480					$currentid = 0;
481					$q = sprintf("insert into %s values('%s', %s)",
482						$this->Seq_Table,
483						$seq_name,
484						$currentid);
485					$id = @pg_Exec($this->Link_ID, $q);
486				}
487				else
488				{
489					$currentid = $res['nextid'];
490				}
491				$nextid = $currentid + 1;
492				$q = sprintf("update %s set nextid = '%s' where seq_name = '%s'",
493					$this->Seq_Table,
494					$nextid,
495					$seq_name);
496				$id = @pg_Exec($this->Link_ID, $q);
497				$this->unlock();
498			}
499			else
500			{
501				$this->halt('cannot lock ' . $this->Seq_Table . ' - has it been created?');
502				return 0;
503			}
504			return $nextid;
505		}
506
507        /**
508         * Get description of a table
509         *
510         * @param string $table name of table to describe
511         * @param boolean $full optional, default False summary information, True full information
512         * @return array Table meta data
513         */
514		function metadata($table)
515		{
516			$count = 0;
517			$id    = 0;
518			$res   = array();
519
520			$this->connect();
521			$id = pg_exec($this->Link_ID, "select * from $table");
522			if ($id < 0)
523			{
524				$this->Error = pg_ErrorMessage($id);
525				$this->Errno = 1;
526				$this->halt('Metadata query failed.');
527			}
528			$count = pg_NumFields($id);
529
530			for ($i=0; $i<$count; $i++)
531			{
532				$res[$i]['table'] = $table;
533				$res[$i]['name']  = pg_FieldName  ($id, $i);
534				$res[$i]['type']  = pg_FieldType  ($id, $i);
535				$res[$i]['len']   = pg_FieldSize  ($id, $i);
536				$res[$i]['flags'] = '';
537			}
538
539			pg_FreeResult($id);
540			return $res;
541		}
542
543        /**
544         * Get the number of rows affected by last update
545         *
546         * @return integer number of affected rows
547         */
548		function affected_rows()
549		{
550			return pg_cmdtuples($this->Query_ID);
551		}
552
553        /**
554         * Number of rows in current result set
555         *
556         * @return integer number of rows
557         */
558		function num_rows()
559		{
560			return pg_numrows($this->Query_ID);
561		}
562
563        /**
564         * Number of fields in current row
565         *
566         * @return integer number of fields
567         */
568		function num_fields()
569		{
570			return pg_numfields($this->Query_ID);
571		}
572
573        /**
574        * Error handler
575        *
576        * @param string $msg error message
577        * @param integer $line line of calling method/function (optional)
578        * @param string $file file of calling method/function (optional)
579		* @access private
580        */
581		function halt($msg, $line = '', $file = '')
582		{
583			/* private: error handling */
584			if ($this->Halt_On_Error == 'no')
585			{
586				$this->Error = @pg_ErrorMessage($this->Link_ID);
587				$this->Errno = 1;
588				return;
589			}
590
591			/* Just in case there is a table currently locked */
592			$this->transaction_abort();
593
594
595			if ($this->xmlrpc || $this->soap)
596			{
597				$s = sprintf("Database error: %s\n", $msg);
598				$s .= sprintf("PostgreSQL Error: %s\n\n (%s)\n\n",$this->Errno,$this->Error);
599			}
600			else
601			{
602				$s = sprintf("<b>Database error:</b> %s<br />\n", $msg);
603				$s .= sprintf("<b>PostgreSQL Error</b>: %s (%s)<br />\n",$this->Errno,$this->Error);
604			}
605
606			if ($file)
607			{
608				if ($this->xmlrpc || $this->soap)
609				{
610					$s .=	sprintf("File: %s\n",$file);
611				}
612				else
613				{
614					$s .=	sprintf("<br /><b>File:</b> %s",$file);
615				}
616			}
617
618			if ($line)
619			{
620				if ($this->xmlrpc || $this->soap)
621				{
622					$s .=	sprintf("Line: %s\n",$line);
623				}
624				else
625				{
626					$s .=	sprintf("<br /><b>Line:</b> %s",$line);
627				}
628			}
629
630			if ($this->Halt_On_Error == 'yes')
631			{
632				if (! $this->xmlrpc && ! $this->soap)
633				{
634					$s .= '<p><b>Session halted.</b>';
635				}
636			}
637
638			if ($this->xmlrpc)
639			{
640				xmlrpcfault($s);
641			}
642			elseif ($this->soap)
643			{
644
645			}
646			else
647			{
648				echo $s;
649				$GLOBALS['phpgw']->common->phpgw_exit(True);
650			}
651		}
652
653        /**
654         * Get a list of table names in the current database
655         *
656         * @return array List of the tables
657         */
658		function table_names()
659		{
660			$return = array();
661			$this->query("select relname from pg_class where relkind = 'r' and not relname like 'pg_%'");
662			$i=0;
663			while ($this->next_record())
664			{
665				$return[$i]['table_name']= $this->f(0);
666				$return[$i]['tablespace_name']=$this->Database;
667				$return[$i]['database']=$this->Database;
668				$i++;
669			}
670			return $return;
671		}
672
673		function index_names()
674		{
675			$this->query("SELECT relname FROM pg_class WHERE NOT relname ~ 'pg_.*' AND relkind ='i' ORDER BY relname");
676			$i=0;
677			while ($this->next_record())
678			{
679				$return[$i]['index_name']= $this->f(0);
680				$return[$i]['tablespace_name']=$this->Database;
681				$return[$i]['database']=$this->Database;
682				$i++;
683			}
684			return $return;
685		}
686
687        /**
688         * Create a new database
689         *
690         * @param string $adminname Name of database administrator user (optional)
691         * @param string $adminpasswd Password for the database administrator user (optional)
692         */
693		function create_database($adminname = '', $adminpasswd = '')
694		{
695			$currentUser = $this->User;
696			$currentPassword = $this->Password;
697			$currentDatabase = $this->Database;
698
699			if ($adminname != "")
700			{
701				$this->User = $adminname;
702				$this->Password = $adminpasswd;
703				$this->Database = 'template1';
704			}
705
706			/*
707			This doesn't work if the httpd server user doesn't have execute permissions on the createdb program
708			*/
709			/*
710			if (! $this->Host)
711			{
712				system('createdb ' . $currentDatabase, $outval);
713			}
714			else
715			{
716				system('createdb -h ' . $this->Host . ' ' . $currentDatabase, $outval);
717			}
718
719			if($outval != 0)
720			{
721				// either the rights are not available or the postmaster is not running ....
722				echo 'database creation failure <br />';
723				echo 'please setup the postreSQL database manually<br />';
724			}*/
725
726			$this->query("CREATE DATABASE $currentDatabase");
727			$this->query("grant all on $currentDatabase.* to $currentUser@localhost identified by '$currentPassword'");
728
729			$this->User = $currentUser;
730			$this->Password = $currentPassword;
731			$this->Database = $currentDatabase;
732			$this->connect();
733			//return $return;
734		}
735	}
736
737