1<?php 2/** 3* LIBRERIA SQL per PostgreSQL con gestione errori ed altre utility 4* 5* @package VFront 6* @subpackage DB-Libraries 7* @author Mario Marcello Verona <marcelloverona@gmail.com> 8* @copyright 2007-2010 M.Marcello Verona 9* @version 0.96 $Id: vmsql.postgres.php 933 2011-03-26 15:48:54Z marciuz $ 10* @see vmsql.mysqli.php 11* @license http://www.gnu.org/licenses/gpl.html GNU Public License 12*/ 13 14 15class postgres_vmsql { 16 17 public $vmsqltype='postgres'; 18 19 public $link_db; 20 21 protected $transaction_is_open=false; 22 23 protected $connected=false; 24 25 protected $error_handler=null; 26 27 protected $last_error=null; 28 29 /** 30 * @desc DB Connection 31 * @param array $array_db 32 * @param string $charset 33 * @return resource 34 */ 35 public function connect($array_db,$charset=''){ 36 37 $this->link_db = @pg_connect("host={$array_db['host']} port={$array_db['port']} dbname={$array_db['postgres_dbname']} user={$array_db['user']} password={$array_db['passw']}"); 38 39 $charset = ($charset=='utf8') ? "UNICODE" : $charset; 40 41 if(!is_resource($this->link_db)){ 42 die("Connection error: is Postgres running? Otherwise please check your conf file"); 43 } 44 45 if(is_resource($this->link_db) && $charset!=''){ 46 47 $this->connected=true; 48 pg_set_client_encoding($this->link_db, $charset); 49 } 50 51 return $this->link_db; 52 53 } 54 55 56 57 58 /** 59 * @desc Esegue una query $sql 60 * @param string $sql 61 * @param bool $transazione 62 * @return object 63 */ 64 public function query($sql,$transazione=false){ 65 66 $getmicro=microtime(true); 67 68 $resource=null; 69 70 $err=null; 71 72 if(is_resource($this->link_db)){ 73 74 pg_send_query($this->link_db,$sql); 75 $resource = pg_get_result($this->link_db); 76 77 $err=pg_result_error_field($resource,PGSQL_DIAG_SQLSTATE); 78 79 if($err!==null){ 80 81 $this->error($sql,array('code'=>$err,'message'=>strtok(pg_result_error($resource),"\n"))); 82 } 83 84 if(isset($GLOBALS['DEBUG_SQL']) && $GLOBALS['DEBUG_SQL']){ 85 $GLOBALS['DEBUG_SQL_STRING'][] = round((microtime(true) - $getmicro),4) . " --- ". $sql; 86 } 87 88 } 89 else $this->error($sql,"Link al DB non disponibile"); 90 91 if(is_resource($resource) && $err===null) { 92 if($transazione) $this->commit(); 93 return $resource; 94 } 95 else{ 96 if($transazione) $this->rollback(); 97 return false; 98 } 99 100 101 } 102 103 104 /** 105 * Esegue una query $sql e restisce vero|falso a seconda dell'esito 106 * il secure_mode (di default) permette l'uso di sole query SELECT 107 * 108 * @param string $sql 109 * @param object $this->link_db 110 * @param bool $secure_mode 111 * @return bool 112 */ 113 public function query_try($sql,$secure_mode=true){ //,$prendi_errorn=false){ 114 115 $sql=trim(str_replace(array("\n","\r")," ",$sql)); 116 117 if($secure_mode){ 118 // piccolo accorgimento per la sicurezza... 119 if(!preg_match("'^SELECT 'i",$sql)) return 0; 120 $sql2=preg_replace("'([\W](UPDATE)|(DELETE)|(INSERT)|(DROP)|(ALTER)|(UNION)|(TRUNCATE)|(SHOW)|(CREATE)[\W])'ui","",$sql); 121 if($sql2!=$sql){ 122 return -1; 123 } 124 } 125 if(is_resource($this->link_db)){ 126 127 $getmicro=microtime(true); 128 129 pg_send_query($this->link_db,$sql); 130 $resource = pg_get_result($this->link_db); 131 132 $err=pg_result_error_field($resource,PGSQL_DIAG_SQLSTATE); 133 134 if(isset($GLOBALS['DEBUG_SQL']) && $GLOBALS['DEBUG_SQL']){ 135 $GLOBALS['DEBUG_SQL_STRING'][] = round((microtime(true) - $getmicro),4) . " --- ". $sql; 136 } 137 138 if($err!==null){ 139 140 $this->error($sql,array('code'=>$err,'message'=>strtok(pg_result_error($resource),"\n"))); 141 } 142 } 143 144 return (is_resource($resource) && $err===null) ? 1:0; 145 } 146 147 148 /** 149 * @return array 150 * @param resource $res 151 * @desc Funzione di fetch_row 152 */ 153 public function fetch_row(&$res){ 154 155 if(is_resource($res)){ 156 157 $RS= @pg_fetch_row($res); 158 if($RS) return $RS; 159 else return false; 160 161 } 162 163 164 } 165 166 /** 167 * @return array 168 * @param resource $res 169 * @desc Funzione di fetch_assoc 170 */ 171 public function fetch_assoc(&$res){ 172 173 if(is_resource($res)){ 174 175 $RS= @pg_fetch_assoc($res); 176 if($RS) return $RS; 177 else return false; 178 179 } 180 181 182 } 183 184 185 /** 186 * @return array 187 * @param resource $res 188 * @desc Funzione di fetch_array 189 */ 190 public function fetch_array(&$res){ 191 192 if(is_resource($res)){ 193 194 $RS= @pg_fetch_array($res); 195 if($RS) return $RS; 196 else return false; 197 198 } 199 } 200 201 202 /** 203 * @desc Funzione di fetch_object 204 * @return object 205 * @param resource $res 206 */ 207 public function fetch_object(&$res,$class_name=null){ 208 209 if(is_object($res)){ 210 211 $RS= ($class_name==null) ? @pg_fetch_object($res) : @pg_fetch_object($res, 0, $class_name); 212 213 if($RS) return $RS; 214 else return false; 215 } 216 } 217 218 219 /** 220 * @desc Funzione di num_rows 221 * @return array 222 * @param resource $res 223 */ 224 public function num_rows(&$res){ 225 226 if(is_resource($res)){ 227 return @pg_num_rows($res); 228 229 } 230 } 231 232 233 /** 234 * @return int 235 * @param resource $res 236 * @desc Funzione di insert ID che restituisce l'ultimo ID autoincrement inserito (Postgres) 237 */ 238 public function insert_id($tablename,$fieldname){ 239 240 241 $result=@pg_query($this->link_db, "SELECT last_value FROM {$tablename}_{$fieldname}_seq"); 242 if ($result) { 243 $arr = @pg_fetch_row($result,0); 244 pg_freeresult($result); 245 if (isset($arr[0])) return $arr[0]; 246 247 } 248 return false; 249 250 } 251 252 253 254 /** 255 * @return int 256 * @desc Funzione affected rows 257 */ 258 public function affected_rows($query){ 259 260 261 if(is_resource($query)){ 262 return @pg_affected_rows($query); 263 264 } 265 266 } 267 268 269 270 271 /** 272 * @desc Funzione di num_fields 273 * @return int 274 * @param string $dbname 275 */ 276 public function num_fields($dbname){ 277 278 return @pg_num_fields($dbname); 279 280 } 281 282 283 284 285 286 287 288 289 290 291 ######################################################################################### 292 # 293 # 294 # FUNZIONI DI ELABORAZIONE 295 # 296 297 298 299 300 301 302 /** 303 * @return array (matrice) 304 * @param resource $res 305 * @desc Funzione utility di fetch_assoc che restituisce tutta la matrice dei risultati 306 */ 307 public function fetch_assoc_all(&$res, $reverse=false){ 308 309 $matrice=array(); 310 311 if(is_resource($res)){ 312 313 while($RS= $this->fetch_assoc($res)) $matrice[]=$RS; 314 315 if($reverse) 316 return $this->reverse_matrix($matrice); 317 318 else 319 return $matrice; 320 321 } 322 } 323 324 325 326 327 /** 328 * @return matrix 329 * @param matrix $matrix 330 * @desc restituisce una traslata della matrice partendo da indici numerici 331 */ 332 public function reverse_matrix($matrix){ 333 334 if(!is_array($matrix) || count($matrix)==0) return false; 335 336 $keys = array_keys($matrix[0]); 337 338 for($i=0;$i<count($matrix);$i++){ 339 340 for($j=0;$j<count($keys);$j++) $rev[$keys[$j]][$i] = $matrix[$i][$keys[$j]]; 341 } 342 343 return $rev; 344 } 345 346 347 /** 348 * @return resource 349 * @param resource $res 350 * @desc Funzione utility di fetch_row che restituisce tutta la matrice dei risultati 351 */ 352 public function fetch_row_all(&$res,$reverse=false){ 353 354 $matrice=array(); 355 356 if(is_resource($res)){ 357 358 while($RS= $this->fetch_row($res)) $matrice[]=$RS; 359 360 if($reverse) 361 return $this->reverse_matrix($matrice); 362 363 else 364 return $matrice; 365 366 } 367 } 368 369 370 /** 371 * Funzione che recupera le informazioni sui campi di una tabella data 372 * 373 * @param string $tabella 374 * @return array 375 */ 376 public function fields($tabella){ 377 378 $res = $this->query("SELECT * FROM $tabella LIMIT 1"); 379 $i = @pg_num_fields($res); 380 for ($j = 0; $j < $i; $j++) { 381 $fieldname = @pg_field_name($res, $j); 382 $tab_fields[$fieldname]=@pg_field_type($res, $j); 383 } 384 385 return $tab_fields; 386 } 387 388 389 /** 390 * Recupera informazioni dal file e dalla query ed apre la funzione 391 * openError del file design/layouts.php dove cancella il buffer e manda a video l'errore codificato 392 * 393 * @return void 394 * @param string $sql 395 * @param string $message 396 * @desc Handler degli errori per le query. 397 */ 398 public function error($sql, $message){ 399 400 401 if(!is_object($this->error_handler)){ 402 403 $this->error_handler= new stdClass(); 404 405 $this->error_handler->dbtype=$this->vmsqltype; 406 $this->error_handler->errors=array(); 407 } 408 409 $trace=debug_backtrace(); 410 $last=count($trace)-1; 411 $file_line=str_replace(FRONT_ROOT, '', $trace[$last]['file']).":".$trace[$last]['line']; 412 413 $ee=array('date'=>date("c"), 414 'sql'=>$sql, 415 'code'=> $message['code'], 416 'msg'=>$message['message'], 417 'file'=>$file_line 418 ); 419 420 $this->error_handler->errors[]=$ee; 421 422 $this->last_error=$ee; 423 424 if(isset($GLOBALS['DEBUG_SQL']) && $GLOBALS['DEBUG_SQL']){ 425 426 $this->error_debug(); 427 } 428 else{ 429 430 if(!function_exists('openError')){ 431 432 include_once(FRONT_REALPATH."/inc/layouts.php"); 433 } 434 // richiamo la funzione openError 435 openError($this->last_error); 436 exit; 437 } 438 439 } 440 441 /** 442 * Questa funzione viene eseguita da {@link $this->query} qualora il debug sia attivato 443 * @desc Funzione che restituisce a video l'SQL che ha generato l'errore 444 * @param unknown_type $sql 445 * @param unknown_type $message 446 */ 447 public function error_debug($format='string'){ 448 449 if($format=='string'){ 450 451 var_dump($this->last_error); 452 } 453 } 454 455 456 457 458 459 460 461 // FUNZIONI DI TRANSAZIONE 462 463 /** 464 * @desc Funzione di transazione che corrisponde ad un BEGIN 465 */ 466 public function begin(){ 467 468 if(!$this->transaction_is_open){ 469 $q=$this->query("BEGIN"); 470 } 471 $this->transaction_is_open=true; 472 } 473 474 475 /** 476 * @desc Funzione di transazione di ROLLBACK 477 */ 478 public function rollback(){ 479 480 if($this->transaction_is_open){ 481 $q=$this->query("ROLLBACK"); 482 } 483 $this->transaction_is_open=false; 484 } 485 486 487 /** 488 * @desc Funzione di transazione di COMMIT 489 */ 490 public function commit(){ 491 492 if($this->transaction_is_open){ 493 $q=$this->query("COMMIT"); 494 } 495 $this->transaction_is_open=false; 496 } 497 498 499 500 501 502 /** 503 * Funzione di utilit� 504 * Testa l'esistenza di un $valore (di solito l'ID) nel $campo di una $tabella, 505 * con eventuali clausole $and 506 * 507 * @param string $campo 508 * @param mixed $valore_id 509 * @param string $tabella 510 * @param string $and 511 * @return bool 512 */ 513 public function test_id($campo,$valore_id,$tabella,$and="",$secure_test=false){ 514 515 $sql= "SELECT * FROM $tabella WHERE $campo=$valore_id $and"; 516 517 if($secure_test){ 518 if($this->query_try($sql)){ 519 $q=$this->query($sql); 520 521 return ($this->num_rows($q)>0) ? true:false; 522 } 523 else return null; 524 } 525 else{ 526 527 $q=$this->query($sql); 528 return ($this->num_rows($q)>0) ? true:false; 529 } 530 } 531 532 /** 533 * Escape function 534 * 535 * @param string $string 536 * @return string 537 */ 538 public function escape($string){ 539 540 return pg_escape_string($this->link_db,stripslashes($string)); 541 } 542 543 544 /** 545 * Unescape function 546 * 547 * @param string $string 548 * @return string 549 */ 550 public function unescape($string){ 551 552 return str_replace(pg_escape_string($this->link_db,"'"),"'",$string); 553 } 554 555 556 557 558 /** 559 * Recursive escape. Work on strings, numbers, array, objects 560 * 561 * @param mixed $mixed 562 * @return mixed 563 */ 564 public function recursive_escape($mixed){ 565 566 if(is_string($mixed)){ 567 568 $escaped= $this->escape($mixed); 569 } 570 else if(is_numeric($mixed)){ 571 572 $escaped= $mixed; 573 } 574 else if(is_array($mixed)){ 575 576 foreach ($mixed as $k=>$val) 577 $escaped[$k]=$this->recursive_escape($val); 578 } 579 else if(is_object ($mixed)){ 580 581 foreach ($mixed as $k=>$val) 582 $escaped->{$k}=$this->recursive_escape($val); 583 } 584 585 return $escaped; 586 } 587 588 589 590 /** 591 * Concat DB sintax 592 * 593 * @param string $args 594 * @param string $args 595 * @return string 596 */ 597 public function concat($args,$as=''){ 598 599 $str=str_replace(","," || ",$args); 600 601 if($as!='') $str.=" AS $as"; 602 603 return $str; 604 } 605 606 607 /** 608 * Set the LIMIT|OFFSET sintax 609 * 610 * @param int $limit 611 * @param int $offset 612 * @return string 613 */ 614 public function limit($limit,$offset=''){ 615 616 $str= "LIMIT $limit"; 617 618 if($offset!='') $str.=" OFFSET $offset"; 619 620 return $str; 621 } 622 623 624 625 public function db_version(){ 626 627 $q=$this->query("SELECT VERSION()"); 628 list($db_version)=$this->fetch_row($q); 629 630 return $db_version; 631 } 632 633 634 public function close(){ 635 636 if($this->error_handler!==null) db_error_log($this->error_handler); 637 638 if(is_resource($this->link_db)){ 639 pg_close($this->link_db); 640 } 641 } 642 643 /** 644 * For Oracle and MySQLi compatibility 645 * 646 * @param statement $stmt 647 * @return bool 648 */ 649 public function stmt_close($stmt){ 650 651 return true; 652 } 653 654 public function __destruct() { 655 656 $this->close(); 657 } 658 659} 660 661?>