/core/inc/class/class.sqlite3db.php

<?php


/**


* @author Jonathan Gotti <jgotti at jgotti dot org>


* @copyleft (l) 2008  Jonathan Gotti


* @package class-db


* @file


* @since 2008-04


* @license http://opensource.org/licenses/lgpl-license.php GNU Lesser General Public License


* @svnInfos:


*            - $LastChangedDate: 2009-04-30 01:00:12 +0200 (jeu. 30 avril 2009) $


*            - $LastChangedRevision: 127 $


*            - $LastChangedBy: malko $


*            - $HeadURL: http://trac.jgotti.net/svn/class-db/trunk/adapters/class-sqlite3db.php $


* @changelog


*            - 2008-07-29 - suppress a bug to avoid some error while trying to destroy twice the same last_qres.


* @todo add transactions support


*/





/**


* exented db class to use with sqlite3 databases.


* require php sqlite3 extension to work


* @class sqlite3db


*/


class sqlite3db extends db{


	public $autocreate= TRUE;





	public $db_file = '';


	public $_protect_fldname = "'";


	/**


	* create a sqlitedb object for managing locale data


	* if DATA_PATH is define will force access in this directory


	* @param string $Db_file


	* @return sqlitedb object


	*/


	function __construct($db_file){


		$this->host = 'localhost';


		$this->db_file = $db_file;


		$this->conn = &$this->db; # only for better compatibility with other db implementation


		if(db::$autoconnect)


			$this->open();


	}





	###*** REQUIRED METHODS FOR EXTENDED CLASS ***###


	/** open connection to database */


	function open(){


		//prevent multiple db open


		if($this->db)


			return $this->db;


		if(! $this->db_file )


			return FALSE;


		if(! (is_file($this->db_file) || $this->autocreate) )


			return FALSE;


		if( $this->db = sqlite3_open($this->db_file)){


			return $this->db;


		}else{


			$this->set_error(__FUNCTION__);


			return FALSE;


		}


	}


	/** close connection to previously opened database */


	function close(){


		if( !is_null($this->db) ){


			if($this->last_qres){


				sqlite3_query_close($this->last_qres);


				$this->last_qres = null;


			}


			sqlite3_close($this->db);


		}


		$this->db = null;


	}





	/**


	* check and activate db connection


	* @param string $action (active, kill, check) active by default


	* @return bool


	*/


	function check_conn($action = ''){


		if(! $this->db){


			if($action !== 'active')


				return $action==='kill'?true:false;


			return $this->open()===false?false:true;


		}else{


			if($action==='kill'){


				$this->close();


				$this->db = null;


			}


			return true;


		}


	}





	/**


	* take a resource result set and return an array of type 'ASSOC','NUM','BOTH'


	* @param resource $result_set


	* @param string $result_type in 'ASSOC','NUM','BOTH'


	*/


	function fetch_res($result_set,$result_type='ASSOC'){


		$result_type = strtoupper($result_type);


		if(! in_array($result_type,array('NUM','ASSOC','BOTH')) )


			$result_type = 'ASSOC';


		if($result_type==='ASSOC'){


			while($res[]=sqlite3_fetch_array($result_set));


			unset($res[count($res)-1]);//unset last empty row


		}elseif($result_type==='NUM'){


			while($res[]=sqlite3_fetch($result_set));


			unset($res[count($res)-1]);//unset last empty row


		}else{


			while($row=sqlite3_fetch_array($result_set)){


				$res[] = array_merge($row,array_values($row));


			};


		}


		if( empty($res) )


			return $this->last_q2a_res = false;


		$this->num_rows = count($res);


		return $this->last_q2a_res = $res;


	}





	function last_insert_id(){


		return $this->db?sqlite3_last_insert_rowid($this->db):FALSE;


	}





	/**


	* perform a query on the database


	* @param string $Q_str


	* @return result id or bool depend on the query type| FALSE


	*/


	function query($Q_str){


		if(! $this->db ){


			if(! (db::$autoconnect && $this->open()) )


				return FALSE;


		}


		$this->verbose($Q_str,__FUNCTION__,2);


		if($this->last_qres){#- close unclosed previous qres


			sqlite3_query_close($this->last_qres);


			$this->last_qres = null;


		}





		if( preg_match('!^\s*select!i',$Q_str) ){


			$this->last_qres = sqlite3_query($this->db,$Q_str);


			$res = $this->last_qres;


		}else{


			$res = sqlite3_exec($this->db,$Q_str);


		}


		if(! $res)


			$this->set_error(__FUNCTION__);





		return $res;


	}





	/**


	* perform a query on the database like query but return the affected_rows instead of result


	* give a most suitable answer on query such as INSERT OR DELETE


	* Be aware that delete without where clause can return 0 even if several rows were deleted that's a sqlite bug!


	*    i will add a workaround when i'll get some time! (use get_count before and after such query)


	* @param string $Q_str


	* @return int affected_rows


	*/


	function query_affected_rows($Q_str){


		if(! $this->query($Q_str) )


			return FALSE;


		return sqlite3_changes($this->db);


	}





	/**


	* return the list of field in $table


	* @param string $table name of the sql table to work on


	* @param bool $extended_info if true will return the result of a show field query in a query_to_array fashion


	*                           (indexed by fieldname instead of int if false)


	* @return array


	*/


	function list_table_fields($table,$extended_info=FALSE){


		# Try the simple method


		if( (! $extended_info) && $res = $this->query_to_array("SELECT * FROM $table LIMIT 0,1")){


			return array_keys($res[0]);


		}else{ # There 's no row in this table so we try an alternate method or we want extended infos


			if(! $fields = $this->query_to_array("SELECT sql FROM sqlite_master WHERE type='table' AND name ='$table'") )


				return FALSE;


			# get fields from the create query


			$flds_str = $fields[0]['sql'];


			$flds_str = substr($flds_str,strpos($flds_str,'('));


			$type = "((?:[a-z]+)\s*(?:\(\s*\d+\s*(?:,\s*\d+\s*)?\))?)?\s*";


			$default = '(?:DEFAULT\s+((["\']).*?(?<!\\\\)\\4|[^\s,]+))?\s*';


			if( preg_match_all('/(\w+)\s+'.$type.$default.'[^,]*(,|\))/i',$flds_str,$m,PREG_SET_ORDER) ){


				$key  = "PRIMARY|UNIQUE|CHECK";


				$Extra = 'AUTOINCREMENT';


				$default = 'DEFAULT\s+((["\'])(.*?)(?<!\\\\)\\2|\S+)';


				foreach($m as $v){


					list($field,$name,$type,$default) = $v;


					# print_r($field);


					if(!$extended_info){


						$res[] = $name;


						continue;


					}


					$res[$name] = array('Field'=>$name,'Type'=>$type,'Null'=>'YES','Key'=>'','Default'=>$default,'Extra'=>'');


					if( preg_match("!($key)!i",$field,$n))


						$res[$name]['Key'] = $n[1];


					if( preg_match("!($Extra)!i",$field,$n))


						$res[$name]['Extra'] = $n[1];


					if( preg_match('!(NO)T\s+NULL!i',$field,$n))


						$res[$name]['Null'] = $n[1];


				}


				return $res;


			}


			return FALSE;


		}


	}


	/**


	* get the table list


	* @return array


	*/


	function list_tables(){


		if(! $tables = $this->query_to_array('SELECT name FROM sqlite_master WHERE type=\'table\'') )


			return FALSE;


		foreach($tables as $v){


			$ret[] = $v['name'];


		}


		return $ret;


	}





	/** Verifier si cette methode peut s'appliquer a SQLite * /


	function show_table_keys($table){}





	/**


	* optimize table statement query


	* @param string $table name of the table to optimize


	* @return bool


	*/


	function optimize($table){


		return $this->vacuum($table);


	}


	/**


	* sqlitedb specific method to use the vacuum statement (used as replacement for mysql optimize statements)


	* you should use db::optimize() method instead for better portability


	* @param string $table_or_index name of table or index to vacuum


	* @return bool


	*/


	function vacuum($table_or_index){


		return $this->query("VACUUM $table_or_index;");


	}





	/**


	* base method you should replace this one in the extended class, to use the appropriate escape func regarding the database implementation


	* @param string $quotestyle (both/single/double) which type of quote to escape


	* @return str


	*/


	function escape_string($string,$quotestyle='both'){





		if( function_exists('sqlite_escape_string') ){


			$string = sqlite_escape_string($string);


			$string = str_replace("''","'",$string); #- no quote escaped so will work like with no sqlite_escape_string available


		}else{


			$escapes = array("\x00", "\x0a", "\x0d", "\x1a", "\x09","\\");


			$replace = array('\0',   '\n',    '\r',   '\Z' , '\t',  "\\\\");


		}


		switch(strtolower($quotestyle)){


			case 'double':


			case 'd':


			case '"':


				$escapes[] = '"';


				$replace[] = '\"';


				break;


			case 'single':


			case 's':


			case "'":


				$escapes[] = "'";


				$replace[] = "''";


				break;


			case 'both':


			case 'b':


			case '"\'':


			case '\'"':


				$escapes[] = '"';


				$replace[] = '\"';


				$escapes[] = "'";


				$replace[] = "''";


				break;


		}


		return str_replace($escapes,$replace,$string);


	}





	function error_no(){


		$this->verbose('sqlite3 driver doesn\'t support this method',__function__,1);


	}





	function error_str($errno=null){


		return sqlite3_error($this->db);


	}





	protected function set_error($callingfunc=null){


		static $i=0;


		if(! $this->db ){


			$this->error[$i] = '[ERROR] No Db Handler';


		}else{


			$this->error[$i] =  $this->error_str();


		}


		$this->last_error = $this->error[$i];


		$this->verbose($this->error[$i],$callingfunc,1);


		$i++;


	}


}