1<?php
2 	/**
3	* Database class for MySQL
4	* @author NetUSE AG Boris Erdmann, Kristian Koehntopp
5    * @author Dan Kuykendall, Dave Hall and others
6	* @copyright Copyright (C) 1998-2000 NetUSE AG 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_mysql.inc.php 15462 2004-11-06 15:34:27Z powerstat $
13	*/
14
15	/**
16	* Database class for MySQL
17	*
18	* @package phpgwapi
19	* @subpackage database
20	*/
21	class db extends db_
22	{
23        /**
24         * @var string $type Connection type
25         */
26		var $type     = 'mysql';
27		/**
28		* API revision
29		*
30		* @internal This is an api revision, not a CVS revision
31		* @access public
32		*/
33		var $revision = '1.2';
34
35		/**
36		* Constructor
37		*
38		* @param $query SQL query
39		*/
40		function db($query = '')
41		{
42			$this->db_($query);
43		}
44
45		/**
46		 * Connect to database
47		 *
48		 * @param string $Database Database name
49		 * @param string $Host Database host
50		 * @param string $User Database user
51		 * @param string $Password Database users password
52		 * @return resource Database connection_id
53		 */
54		function connect($Database = '', $Host = '', $User = '', $Password = '')
55		{
56			/* Handle defaults */
57			if ($Database == '')
58			{
59				$Database = $this->Database;
60			}
61			if ($Host == '')
62			{
63				$Host     = $this->Host;
64			}
65			if ($User == '')
66			{
67				$User     = $this->User;
68			}
69			if ($Password == '')
70			{
71				$Password = $this->Password;
72			}
73			/* establish connection, select database */
74			if (! $this->Link_ID)
75			{
76				if ($GLOBALS['phpgw_info']['server']['db_persistent'])
77				{
78					$this->Link_ID=mysql_pconnect($Host, $User, $Password);
79				}
80				else
81				{
82					$this->Link_ID=mysql_connect($Host, $User, $Password);
83				}
84
85				if (!$this->Link_ID)
86				{
87					$this->halt(($GLOBALS['phpgw_info']['server']['db_persistent']?'p':'')."connect($Host, $User, \$Password) failed.");
88					return 0;
89				}
90
91				if (!@mysql_select_db($Database,$this->Link_ID))
92				{
93					$this->halt("cannot use database ".$this->Database);
94					$this->disconnect();
95					return 0;
96				}
97			}
98			return $this->Link_ID;
99		}
100
101		/**
102		* Disconnect from database
103		*
104		* @return integer 1: successful; 0: already disconnected
105		* @internal This only affects systems not using persistant connections
106		*/
107		function disconnect()
108		{
109			if($this->Link_ID <> 0)
110			{
111				@mysql_close($this->Link_ID);
112				$this->Link_ID = 0;
113				return 1;
114			}
115			else
116			{
117				return 0;
118			}
119		}
120
121        /**
122         * Convert a unix timestamp to a rdms specific timestamp
123         *
124         * @param int unix timestamp
125         * @return string rdms specific timestamp
126         */
127		function to_timestamp($epoch)
128		{
129			return date('Y-m-d H:i:s',$epoch);
130		}
131
132        /**
133         * Convert a rdms specific timestamp to a unix timestamp
134         *
135         * @param string rdms specific timestamp
136         * @return int unix timestamp
137         */
138		function from_timestamp($timestamp)
139		{
140			ereg('([0-9]{4})-([0-9]{2})-([0-9]{2}) ([0-9]{2}):([0-9]{2}):([0-9]{2})',$timestamp,$parts);
141
142			return mktime($parts[4],$parts[5],$parts[6],$parts[2],$parts[3],$parts[1]);
143		}
144
145        /**
146         * Discard the current query result
147         */
148		function free()
149		{
150			@mysql_free_result($this->Query_ID);
151			$this->Query_ID = 0;
152		}
153
154        /**
155         * Execute a query
156         *
157         * @param string $Query_String the query to be executed
158         * @param mixed $line the line method was called from - use __LINE__
159         * @param string $file the file method was called from - use __FILE__
160         * @return integer Current result if sucesful and null if failed
161         */
162		function query($Query_String, $line = '', $file = '')
163		{
164			/* No empty queries, please, since PHP4 chokes on them. */
165			/* The empty query string is passed on from the constructor,
166			* when calling the class without a query, e.g. in situations
167			* like these: '$db = new db_Subclass;'
168			*/
169			if ($Query_String == '')
170			{
171				return 0;
172			}
173			if (!$this->connect())
174			{
175				$this->Errno = @mysql_errno();
176				$this->Error = @mysql_error();
177				return 0; /* we already complained in connect() about that. */
178			};
179
180			# New query, discard previous result.
181			if ($this->Query_ID)
182			{
183				$this->free();
184			}
185
186			if ($this->Debug)
187			{
188				printf("Debug: query = %s<br />\n", $Query_String);
189			}
190
191			$this->Query_ID = @mysql_query($Query_String,$this->Link_ID);
192			$this->Row   = 0;
193			$this->Errno = mysql_errno();
194			$this->Error = mysql_error();
195			if (! $this->Query_ID)
196			{
197				$this->halt("Invalid SQL: ".$Query_String, $line, $file);
198			}
199
200			# Will return nada if it fails. That's fine.
201			return $this->Query_ID;
202		}
203
204        /**
205         * Execute a query with limited result set
206         *
207         * @param string $Query_String the query to be executed
208         * @param integer $offset row to start from
209         * @param mixed $line the line method was called from - use __LINE__
210         * @param string $file the file method was called from - use __FILE__
211         * @param int $num_rows number of rows to return (optional), if unset will use $GLOBALS['phpgw_info']['user']['preferences']['common']['maxmatchs']
212         * @return integer Current result if sucesful and null if failed
213         */
214		function limit_query($Query_String, $offset, $line = '', $file = '', $num_rows = 0)
215		{
216			$offset		= intval($offset);
217			$num_rows	= intval($num_rows);
218
219			if ($num_rows == 0)
220			{
221				$maxmatches = $GLOBALS['phpgw_info']['user']['preferences']['common']['maxmatchs'];
222				$num_rows = (isset($maxmatches)?intval($maxmatches):15);
223			}
224
225			if ($offset == 0)
226			{
227				$Query_String .= ' LIMIT ' . $num_rows;
228			}
229			else
230			{
231				$Query_String .= ' LIMIT ' . $offset . ',' . $num_rows;
232			}
233
234			if ($this->Debug)
235			{
236				printf("Debug: limit_query = %s<br />offset=%d, num_rows=%d<br />\n", $Query_String, $offset, $num_rows);
237			}
238
239			return $this->query($Query_String, $line, $file);
240		}
241
242        /**
243         * Move to the next row in the results set
244         *
245         * @return boolean was another row found?
246         */
247		function next_record()
248		{
249			if (!$this->Query_ID)
250			{
251				$this->halt('next_record called with no query pending.');
252				return 0;
253			}
254
255			$this->Record = @mysql_fetch_array($this->Query_ID);
256			$this->Row   += 1;
257			$this->Errno  = mysql_errno();
258			$this->Error  = mysql_error();
259
260			$stat = is_array($this->Record);
261			if (!$stat && $this->Auto_Free)
262			{
263				$this->free();
264			}
265			return $stat;
266		}
267
268        /**
269         * Move to position in result set
270         *
271         * @param integer $pos Required row (optional), default first row
272         * @return integer 1 if sucessful or 0 if not found
273         */
274		function seek($pos = 0)
275		{
276			$status = @mysql_data_seek($this->Query_ID, $pos);
277			if ($status)
278			{
279				$this->Row = $pos;
280			}
281			else
282			{
283				$this->halt("seek($pos) failed: result has ".$this->num_rows()." rows");
284				/* half assed attempt to save the day,
285				* but do not consider this documented or even
286				* desireable behaviour.
287				*/
288				@mysql_data_seek($this->Query_ID, $this->num_rows());
289				$this->Row = $this->num_rows;
290				return 0;
291			}
292			return 1;
293		}
294
295        /**
296         * Find the primary key of the last insertion on the current db connection
297         *
298         * @param string $table name of table the insert was performed on
299         * @param string $field the autoincrement primary key of the table
300         * @return integer The id, -1 if failed
301         */
302		function get_last_insert_id($table, $field)
303		{
304			/* This will get the last insert ID created on the current connection.  Should only be called
305			 * after an insert query is run on a table that has an auto incrementing field.  $table and
306			 * $field are required, but unused here since it's unnecessary for mysql.  For compatibility
307			 * with pgsql, the params must be supplied.
308			 */
309
310			if (!isset($table) || $table == '' || !isset($field) || $field == '')
311			{
312				return -1;
313			}
314
315			return @mysql_insert_id($this->Link_ID);
316		}
317
318        /**
319         * Lock a table
320         *
321         * @param string $table name of table to lock
322         * @param string $mode type of lock required (optional), default write
323         * @return boolean True if sucessful, False if failed
324         */
325		function lock($table, $mode='write')
326		{
327			$this->connect();
328
329			$query = "lock tables ";
330			if (is_array($table))
331			{
332				while (list($key,$value)=each($table))
333				{
334					if ($key == "read" && $key!=0)
335					{
336						$query .= "$value read, ";
337					}
338					else
339					{
340						$query .= "$value $mode, ";
341					}
342				}
343				$query = substr($query,0,-2);
344			}
345			else
346			{
347				$query .= "$table $mode";
348			}
349			$res = @mysql_query($query, $this->Link_ID);
350			if (!$res)
351			{
352				$this->halt("lock($table, $mode) failed.");
353				return 0;
354			}
355			return $res;
356		}
357
358        /**
359         * Unlock a table
360         *
361         * @return boolean True if sucessful, False if failed
362         */
363		function unlock()
364		{
365			$this->connect();
366
367			$res = @mysql_query("unlock tables");
368			if (!$res)
369			{
370				$this->halt("unlock() failed.");
371				return 0;
372			}
373			return $res;
374		}
375
376
377        /**
378         * Get the number of rows affected by last update
379         *
380         * @return integer number of affected rows
381         */
382		function affected_rows()
383		{
384			return @mysql_affected_rows($this->Link_ID);
385		}
386
387        /**
388         * Number of rows in current result set
389         *
390         * @return integer number of rows
391         */
392		function num_rows()
393		{
394			return @mysql_num_rows($this->Query_ID);
395		}
396
397        /**
398         * Number of fields in current row
399         *
400         * @return integer number of fields
401         */
402		function num_fields()
403		{
404			return @mysql_num_fields($this->Query_ID);
405		}
406
407        /**
408         * Get the id for the next sequence
409         *
410         * @param string $seq_name Name of the sequence
411         * @return integer sequence id
412         */
413		function nextid($seq_name)
414		{
415			$this->connect();
416
417			if ($this->lock($this->Seq_Table))
418			{
419				/* get sequence number (locked) and increment */
420				$q  = sprintf("select nextid from %s where seq_name = '%s'",
421					$this->Seq_Table,
422					$seq_name);
423				$id  = @mysql_query($q, $this->Link_ID);
424				$res = @mysql_fetch_array($id);
425
426				/* No current value, make one */
427				if (!is_array($res))
428				{
429					$currentid = 0;
430					$q = sprintf("insert into %s values('%s', %s)",
431						$this->Seq_Table,
432						$seq_name,
433						$currentid);
434					$id = @mysql_query($q, $this->Link_ID);
435				}
436				else
437				{
438					$currentid = $res["nextid"];
439				}
440				$nextid = $currentid + 1;
441				$q = sprintf("update %s set nextid = '%s' where seq_name = '%s'",
442					$this->Seq_Table,
443					$nextid,
444					$seq_name);
445				$id = @mysql_query($q, $this->Link_ID);
446				$this->unlock();
447			}
448			else
449			{
450				$this->halt("cannot lock ".$this->Seq_Table." - has it been created?");
451				return 0;
452			}
453			return $nextid;
454		}
455
456        /**
457         * Get description of a table
458         *
459         * @param string $table name of table to describe
460         * @param boolean $full optional, default False summary information, True full information
461         * @return array Table meta data
462         */
463		function metadata($table='',$full=false)
464		{
465			$count = 0;
466			$id    = 0;
467			$res   = array();
468
469			/* if no $table specified, assume that we are working with a query */
470			/* result */
471			if ($table)
472			{
473				$this->connect();
474				$id = @mysql_list_fields($this->Database, $table);
475				if (!$id)
476				{
477					$this->halt("Metadata query failed.");
478				}
479			}
480			else
481			{
482				$id = $this->Query_ID;
483				if (!$id)
484				{
485					$this->halt("No query specified.");
486				}
487			}
488
489			$count = @mysql_num_fields($id);
490
491			/* made this IF due to performance (one if is faster than $count if's) */
492			if (!$full)
493			{
494				for ($i=0; $i<$count; $i++)
495				{
496					$res[$i]['table'] = @mysql_field_table ($id, $i);
497					$res[$i]['name']  = @mysql_field_name  ($id, $i);
498					$res[$i]['type']  = @mysql_field_type  ($id, $i);
499					$res[$i]['len']   = @mysql_field_len   ($id, $i);
500					$res[$i]['flags'] = @mysql_field_flags ($id, $i);
501				}
502			}
503			else
504			{
505				/* full */
506				$res["num_fields"]= $count;
507
508				for ($i=0; $i<$count; $i++)
509				{
510					$res[$i]['table'] = @mysql_field_table ($id, $i);
511					$res[$i]['name']  = @mysql_field_name  ($id, $i);
512					$res[$i]['type']  = @mysql_field_type  ($id, $i);
513					$res[$i]['len']   = @mysql_field_len   ($id, $i);
514					$res[$i]['flags'] = @mysql_field_flags ($id, $i);
515					$res['meta'][$res[$i]['name']] = $i;
516				}
517			}
518
519			/* free the result only if we were called on a table */
520			if ($table)
521			{
522				@mysql_free_result($id);
523			}
524			return $res;
525		}
526
527        /**
528         * Error handler
529         *
530         * @param string $msg error message
531         * @param integer $line line of calling method/function (optional)
532         * @param string $file file of calling method/function (optional)
533         */
534		function halt($msg, $line = '', $file = '')
535		{
536			$this->Error = @mysql_error($this->Link_ID);	// need to be BEFORE unlock,
537			$this->Errno = @mysql_errno($this->Link_ID);	// else we get its error or none
538
539			if ($this->Link_ID)		// only if we have a link, else infinite loop
540			{
541				$this->unlock();	/* Just in case there is a table currently locked */
542			}
543			if ($this->Halt_On_Error == "no")
544			{
545				return;
546			}
547			$this->haltmsg($msg);
548
549			if ($file)
550			{
551				printf("<br /><b>File:</b> %s",$file);
552			}
553			if ($line)
554			{
555				printf("<br /><b>Line:</b> %s",$line);
556			}
557
558			if ($this->Halt_On_Error != "report")
559			{
560				echo "<p><b>Session halted.</b>";
561				$GLOBALS['phpgw']->common->phpgw_exit(True);
562			}
563		}
564
565        /**
566         * Display database error
567         *
568         * @param string $msg Error message
569         */
570		function haltmsg($msg)
571		{
572			printf("<b>Database error:</b> %s<br />\n", $msg);
573			if ($this->Errno != "0" && $this->Error != "()")
574			{
575				printf("<b>MySQL Error</b>: %s (%s)<br />\n",$this->Errno,$this->Error);
576			}
577		}
578
579        /**
580         * Get a list of table names in the current database
581         *
582         * @return array List of the tables
583         */
584		function table_names()
585		{
586			if (!$this->Link_ID)
587			{
588				$this->connect();
589			}
590			if (!$this->Link_ID)
591			{
592				return array();
593			}
594			$return = Array();
595			$this->query("SHOW TABLES");
596			$i=0;
597			while ($info=@mysql_fetch_row($this->Query_ID))
598			{
599				$return[$i]['table_name'] = $info[0];
600				$return[$i]['tablespace_name'] = $this->Database;
601				$return[$i]['database'] = $this->Database;
602				$i++;
603			}
604			return $return;
605		}
606
607        /**
608         * Create a new database
609         *
610         * @param string $adminname Name of database administrator user (optional)
611         * @param string $adminpasswd Password for the database administrator user (optional)
612         */
613		function create_database($adminname = '', $adminpasswd = '')
614		{
615			$currentUser = $this->User;
616			$currentPassword = $this->Password;
617			$currentDatabase = $this->Database;
618
619			if ($adminname != '')
620			{
621				$this->User = $adminname;
622				$this->Password = $adminpasswd;
623				$this->Database = "mysql";
624			}
625			$this->disconnect();
626			$this->query("CREATE DATABASE $currentDatabase");
627			$this->query("grant all on $currentDatabase.* to $currentUser@localhost identified by '$currentPassword'");
628			$this->disconnect();
629
630			$this->User = $currentUser;
631			$this->Password = $currentPassword;
632			$this->Database = $currentDatabase;
633			$this->connect();
634			/*return $return; */
635		}
636	}
637?>
638