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