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