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?>