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