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