Location: PHPKode > projects > crVCL PHP Framework > sqlite.lib.php
<?PHP


/*

The contents of this file are subject to the Mozilla Public License
Version 1.1 (the "License"); you may not use this file except in compliance
with the License. You may obtain a copy of the License at
http://www.mozilla.org/MPL/MPL-1.1.html or see MPL-1.1.txt in directory "license"

Software distributed under the License is distributed on an "AS IS" basis,
WITHOUT WARRANTY OF ANY KIND, either expressed or implied. See the License for
the specific language governing rights and limitations under the License.

The Initial Developers of the Original Code are: 
Copyright (c) 2003-2012, CR-Solutions (http://www.cr-solutions.net), Ricardo Cescon
All Rights Reserved.

Contributor(s): Ricardo Cescon

crVCL PHP Framework Version 2.4
*/




############################################################
if(!defined("SQLITE_LIB")){
   define ("SQLITE_LIB", 1);
############################################################

class SQLiteDB{
   private $m_db = array();
   private $m_sqlite_int_error = array();
   private $m_internal_errors = array();
   private $m_statistic = array();
   private $m_logger = null;
   private $m_transaction = array();
   private $m_use_sqlite3 = false;
   /**
	 * show warnings
	 *
	 * @var bool
	 */
	public $m_show_warning = true;
   /**
	 * open the connection persistent
	 *
	 * @var bool
	 */
	public $m_persistent_connection = false;
	/**
	 * return the last query execution time
	 *
	 * @var int
	 */
	public $m_last_query_ms = 0;
	/**
	 * id of last used db
	 *
	 * @var string
	 */
	public $m_last_id = "";
	/**
	 * path for trace file
	 *
	 * @var string
	 */
	public $m_trace_path = "";
	/**
	 * filename of tracefile
	 *
	 * @var string
	 */
	public $m_trace_file = "";
	/**
	 * size before trace file will set to zero
	 *
	 * @var mixed
	 */
	public $m_trace_delAfter = "1024K";
	/**
	 * size before log file(s) will set to zero
	 *
	 * @var mixed
	 */
	public $m_log_delAfter = "1024K";
	/**
	 * path for log files
	 *
	 * @var string
	 */
	public $m_log_path = "";
	/**
	 * enable critical section for write log files
	 *
	 * @var string
	 */
	public $m_log_critical_section_id = null;
	/**
	 * path to share the critical section for write log files
	 *
	 * @var string
	 */
	public $m_log_critical_section_path = "";
	/**
	 * max lock time for zombie processes
	 *
	 * @var int
	 */
	public $m_log_critical_section_maxlock_ms = 1000;
	/**
	 * sleep for retry enter critical section
	 *
	 * @var int
	 */
	public $m_log_critical_section_sleep_ms = 10;
	/**
	 * enable critical section for write trace files
	 *
	 * @var string
	 */
	public $m_trace_critical_section_id = null;
	/**
	 * path to share the critical section for write trace files
	 *
	 * @var string
	 */
	public $m_trace_critical_section_path = "./";
	/**
	 * max lock time for zombie processes
	 *
	 * @var int
	 */
	public $m_trace_critical_section_maxlock_ms = 1000;
	/**
	 * sleep for retry enter critical section
	 *
	 * @var int
	 */
	public $m_trace_critical_section_sleep_ms = 10;		
	
//-------------------------------------------------------------------------------------------------------------------------------------   
   /**    
    * @return Logger
    */
   private function getLogger(){
      return $this->m_logger;
   }
//-------------------------------------------------------------------------------------------------------------------------------------   
   private function setIntErr($id, $errno){
      $this->m_sqlite_int_error[$id] = $errno;
      return $errno;
   }	
//-------------------------------------------------------------------------------------------------------------------------------------   
   private function writeTrace($s){  
      $path = $this->m_trace_path;
      $file = $this->m_trace_file;
      $del = $this->m_trace_delAfter;
      $crit_id = $this->m_trace_critical_section_id;
      $crit_path = $this->m_trace_critical_section_path;
      $crit_maxlock = $this->m_trace_critical_section_maxlock_ms;
      $crit_sleep = $this->m_trace_critical_section_sleep_ms;
      
      if(!empty($path) || !empty($file)){                           
   		if(empty($file)){
   			$file = "sqlite.lib.trc";
   		}
         $logger = $this->getLogger();   
         $logger->setOutputFile($path, $file, $del);
         $logger->setOutputFile_CriticalSectionId($crit_id, $crit_path, 
                                                  $crit_maxlock, $crit_sleep);
         $logger->enableOutputFile(true);
         $logger->enableHeaderOutput(true);
         $logger->log(LOG_LEVEL_DEBUG, $s, $this->m_last_id." (".$this->m_last_query_ms." ms)");
      }       		
	}
//-------------------------------------------------------------------------------------------------------------------------------------   	
   private function writeLog($s, $file){
      $path = $this->m_log_path;
      $del = $this->m_log_delAfter;
      
      if(!empty($path)){
         $crit_id = $this->m_log_critical_section_id;
         $crit_path = $this->m_log_critical_section_path;
         $crit_maxlock = $this->m_log_critical_section_maxlock_ms;
         $crit_sleep = $this->m_log_critical_section_sleep_ms;
               
         $logger = $this->getLogger();   
         $logger->setOutputFile($path, $file, $del);
         $logger->setOutputFile_CriticalSectionId($crit_id, $crit_path, 
                                                  $crit_maxlock, $crit_sleep);
         $logger->enableOutputFile(true);
         $logger->enableHeaderOutput(true);
         $logger->log(LOG_LEVEL_DEBUG, $s, $this->m_last_id." (".$this->m_last_query_ms." ms)");           
      }   
	}
//-------------------------------------------------------------------------------------------------------------------------------------
   function __construct($useSQLite3=false){
      $this->m_logger = new Logger();
      
      $this->m_internal_errors = array(
                                    "0"=>"",
                                    "-97"=>"syntax error",
                                    "-98"=>"error open sqlite file",                                    
                                    "-99"=>"unknown database id",
                                    "-100"=>"sqlite database object is null",
                                    "-101"=>"secutity check failed, DELETE or UPDATE without WHERE",
                                    "-102"=>"parameter is no SQLiteBuilder Object"                                    
                                    );

      if(!is_callable("sqlite_open")){
      	showFrameworkError("SQLite module for PHP not installed or loaded, please see php.ini");
      	return;
      }
      
      $this->m_statistic["SELECTS"] = 0;
    	$this->m_statistic["SELECTS_TIME_TOTAL_MS"] = 0;
    	$this->m_statistic["UPDATES"] = 0;
    	$this->m_statistic["UPDATES_TIME_TOTAL_MS"] = 0;
    	$this->m_statistic["INSERTS"] = 0;
    	$this->m_statistic["INSERTS_TIME_TOTAL_MS"] = 0;
    	$this->m_statistic["DELETES"] = 0;
    	$this->m_statistic["DELETES_TIME_TOTAL_MS"] = 0;
    	
    	if($useSQLite3){
    	   $this->m_use_sqlite3 = true;
    	}
   }
//-------------------------------------------------------------------------------------------------------------------------------------   
   function __destruct(){
      $this->free();
   }
//-------------------------------------------------------------------------------------------------------------------------------------   
   function free(){
      free($this->m_db);
      free($this->m_sqlite_int_error);
   }
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * return a array with statistics about SELECTS, UPDATES, INSERTS ect. since the object was created
    *
    * @return array
    */
   function getStatistics(){
      return $this->m_statistic;
   }   
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * version of SQLite library (if you use SQLite3 you get an array instead of a string)
    *
    * @return mixed
    */
   function version(){
      if($this->m_use_sqlite3){
         $sqlite = &$GLOBALS["CRVCL"]["LAST_SQLITE3_OBJ"];
         return $sqlite->version();
      }
      return sqlite_libversion();
   }
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * get last error code
    *
    * @param string $id
    * @return int
    */
	function getError($id){	   
	   $this->setIntErr($id,0);
      
      if(!isset($this->m_db[$id])){
	      $this->setIntErr($id,-99);
         return false;
	   }
	   
	   if(!isset($this->m_db[$id]) || !$this->m_db[$id] || $this->m_sqlite_int_error[$id] != 0){
         return $this->m_sqlite_int_error[$id];
	   }

	   if($this->m_use_sqlite3){
	      $sqlite = &$this->m_db[$id];
	      return $sqlite->lastErrorCode();
	   }
		return sqlite_last_error($this->m_db[$id]);
	}
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * get last error message
    *
    * @param string $id
    * @return string
    */
	function getErrorMsg($id){
	   $this->setIntErr($id,0);
      
      if(!isset($this->m_db[$id])){
	      $this->setIntErr($id,-99);
         return false;
	   }
	   
	   if(!isset($this->m_db[$id]) || !$this->m_db[$id] || $this->m_sqlite_int_error[$id] != 0){
         return $this->m_internal_errors[$this->m_sqlite_int_error[$id]];
	   }
	   
	   if($this->m_use_sqlite3){
	      $sqlite = &$this->m_db[$id];
	      return $sqlite->lastErrorMsg();
	   }
	   
      $code = $this->getError($id); 
		return sqlite_error_string($code);
	}

//-------------------------------------------------------------------------------------------------------------------------------------   
   /**
    * open a SQLite database file
    *
    * @param string $id => id for the database name
    * @param string $filename
    */
   function connect($id, $filename=""){
      $this->open($id, $filename);
   }
//-------------------------------------------------------------------------------------------------------------------------------------   
   /**
    * open a SQLite database file
    *
    * @param string $id => id for the database name
    * @param string $filename
    */
   function open($id, $filename=""){
      $error_msg = "";
      $sqlite = false;
      $oSqlite = null;
      
      if(empty($filename)){
         $filename = $id . ".db";
      }
                 
      $this->close($id);
      
            
      if($this->m_use_sqlite3){
         $sqlite = true;
         try{
            $oSqlite = new SQLite3($filename, SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE);
         }catch(Exception $e){
            $sqlite = false;
            $error_msg = "open \r\nSQLite Error: could not open file ".$filename."\r\n".$e->getMessage();
         }
                  
      }else{
      
         if($this->m_persistent_connection){
            $sqlite = sqlite_popen($filename, 0666, $error_msg);
         }else{
            $sqlite = sqlite_open($filename, 0666, $error_msg);
         }
      }   
      
      if(!empty($error_msg)){
         if($this->m_show_warning){
            trigger_error($error_msg, E_USER_WARNING);
         }
         $this->setIntErr($id,-98);
		   $this->writeLog("open \r\nSQLite Error: ".$this->getErrorMsg($id)." (".$this->getError($id).")", $id.".querys.error.log");
		   return false;
      }   
            
      if($sqlite !== false){
         
         $GLOBALS["CRVCL"]["LAST_SQLITE3_OBJ"] = &$oSqlite; // required for mysqli::real_escape_string in the MySQL Builder
         if($oSqlite != null)$sqlite = &$oSqlite;
         
         $this->m_db += array($id=>$sqlite);
         $this->m_sqlite_int_error += array($id=>"0");
         return true;
      }
      return false;
   }
//-------------------------------------------------------------------------------------------------------------------------------------   
   function disconnect($id){
      $this->close();
   }
//-------------------------------------------------------------------------------------------------------------------------------------   
   function close($id){
      $this->setIntErr($id,0);
      $ret = false;

      if(!isset($this->m_db[$id])){
	      $this->setIntErr($id,-99);
         return $ret;
	   }
	   
      $sqlite = &$this->m_db[$id];
		if(!$sqlite){
		   $this->setIntErr($id,-100);
		   $this->writeLog("disconnect \r\nSQLite Error: ".$this->getErrorMsg($id)." (".$this->getError($id).")", $id.".querys.error.log");
         return $ret;
		}
   
      $ret = true;
   	if(!$this->m_persistent_connection){
   	   
   	   if($this->m_use_sqlite3){   	      
   	      $ret = $sqlite->close();
   	   }else{	   
   	      $ret = sqlite_close($sqlite);
   	   }   
   	}

   	if($ret){
   		unset($this->m_db[$id]);
   		unset($this->m_sqlite_int_error[$id]);
   	}
   	
      return $ret;
   }    
//-------------------------------------------------------------------------------------------------------------------------------------   
   /**
	 * return SQLiteDBResult or number of affected rows
	 *
	 * @param string $id => id for the database name
	 * @param mixed $sql_query or $sql_builder_object
	 * @param int $fetch_type
	 * @param bool $free_resource    if false the result resource will be free by garbage collector or should be free manually
	 * @return SQLiteDBResult
	 */
   function sql($id, $sql, $fetch_type=SQLITE_BOTH, $free_resource=true){
      $this->setIntErr($id,0);
      
      if(!isset($this->m_db[$id])){
	      $this->setIntErr($id,-99);
	      $this->writeLog($sql."\r\nSQLite Error: ".$this->getErrorMsg($id)." (".$this->getError($id).")", $id.".querys.error.log");
         return null;
	   }
		$sqlite = $this->m_db[$id];
		if(!$sqlite){
		   $this->setIntErr($id,-100);
		   $this->writeLog($sql."\r\nSQLite Error: ".$this->getErrorMsg($id)." (".$this->getError($id).")", $id.".querys.error.log");
         return null;
		}
		
		$this->m_last_id = $id;
		
		// SQLiteDBBuilder
	   if(is_object($sql)){
	      $ret = false;
	      $bsql = $sql;
	      $sql = "";
	      if($bsql->m_type == SQLITE_UPDINS){
	         $bsql->m_type = SQLITE_SELECT;
	         $bsql->addField("count(*) AS found_rows");
	         $res = $this->sql($id, $bsql, $fetch_type, $free_resource);
	         if($this->getError($id) != 0){
	            return $ret;
	         }
	         $rows = $res->getField("found_rows");
	         $res->free();
	         if($rows > 0){
   	         $bsql->m_type = SQLITE_UPDATE;
   	         $ret = $this->sql($id, $bsql, $fetch_type, $free_resource);
	         }else{
	            $bsql->m_type = SQLITE_INSERT;
	            $ret = $this->sql($id, $bsql, $fetch_type, $free_resource);
	         }
	         return $ret;
	      }else if($bsql->m_type == SQLITE_SELINS){
	         $bsql->m_type = SQLITE_SELECT;
	         $bsql->addField("count(*) AS found_rows");
	         $res = $this->sql($id, $bsql, $fetch_type, $free_resource);
	         if($this->getError($id) != 0){
	            return $ret;
	         }
	         $rows = $res->getField("found_rows");
	         $res->free();

	         if($rows == 0){
	            $bsql->m_type = SQLITE_INSERT;
               $ret = $this->sql($id, $bsql, $fetch_type, $free_resource);
	         }else{$ret = 0;}

	         return $ret;
	      }else{
	         $sql = $bsql->query();
	      }
	   }

	   ///////////////
	   
	   //for query analysing
	   $sql_words = strtoupper($sql);
	   $sql_words = str_replace("\r\n", " ", $sql_words);
	   $sql_words = str_replace("\r", " ", $sql_words);
	   $sql_words = str_replace("\n", " ", $sql_words);
	   $sql_words = str_replace("\t", " ", $sql_words);
	   $sql_words = str_replace("  ", " ", $sql_words);
	   $sql_words = explode(" ", trim($sql_words));
	   if(acount($sql_words) == 0){$sql_words = array("");}
	   //print_d($sql_words); 

	   // secutity check
	   if(   strtoupper($sql_words[0]) == "DELETE"
	      || strtoupper($sql_words[0]) == "UPDATE"
	   ){
	      if(array_search('WHERE', $sql_words) === false){
	         $this->setIntErr($id,-101);
	         $this->writeLog($sql."\r\nSQLite Error: ".$this->getErrorMsg($id)." (".$this->getError($id).")", $id.".querys.error.log");
	         return null;
	      }
	   }
	   
	   $err_msg = "";
	   $result = null;
	   $qstart = gettickcount();	   
	   if($this->m_use_sqlite3){   
	      $result = $sqlite->query($sql); 
	      if($sqlite->lastErrorCode() != 0){
	         $err_msg = $sqlite->lastErrorMsg();
	      }
	   }else{   
	      $result = @sqlite_query($sql, $sqlite, null, $err_msg); 
	   }   
	   $qend = gettickcount();
	   $this->m_last_query_ms = $qend - $qstart;

	   if(!empty($err_msg)){
	      $this->setIntErr($id,-97);
	      $this->writeLog($sql."\r\nSQLite Error: ".$this->getErrorMsg($id).", ".$err_msg." (".$this->getError($id).")", $id.".querys.error.log");
	      return null;
	   }
	   
	   $errno = 0;
	   if($this->m_use_sqlite3){   
	      $errno = $sqlite->lastErrorCode();
	   }else{   
	      $errno = sqlite_last_error($sqlite);
	   }   
	   /*
	   space for possible failover
	   */
	   
	   $this->writeTrace($sql);
	   
	   
	   if(   strtoupper($sql_words[0]) == "SELECT"
			|| strtoupper($sql_words[0]) == "EXPLAIN"
			|| strtoupper($sql_words[0]) == "SHOW"
			|| strtoupper($sql_words[0]) == "DESCRIBE"
		){
			if($errno != 0 || !$result){
				return null;
			}

			// modify also the constructor
			if(strtoupper($sql_words[0]) == "SELECT"){
   		   $this->m_statistic["SELECTS"] = $this->m_statistic["SELECTS"]+1;
   		   $this->m_statistic["SELECTS_TIME_TOTAL_MS"] = $this->m_statistic["SELECTS_TIME_TOTAL_MS"] + $this->m_last_query_ms;
   		}

			$mysqlres = new SQLiteDBResult($result, $fetch_type, $free_resource);

			return $mysqlres;
		}

      if($errno != 0){
			return null;
		}

		// modify also the constructor
		if(strtoupper($sql_words[0]) == "UPDATE"){
		   $this->m_statistic["UPDATES"] = $this->m_statistic["UPDATES"]+1;
		   $this->m_statistic["UPDATES_TIME_TOTAL_MS"] = $this->m_statistic["UPDATES_TIME_TOTAL_MS"] + $this->m_last_query_ms;
		}else if(strtoupper($sql_words[0]) == "INSERT"){
		   $this->m_statistic["INSERTS"] = $this->m_statistic["INSERTS"]+1;
		   $this->m_statistic["INSERTS_TIME_TOTAL_MS"] = $this->m_statistic["INSERTS_TIME_TOTAL_MS"] + $this->m_last_query_ms;
		}else if(strtoupper($sql_words[0]) == "DELETE"){
		   $this->m_statistic["DELETES"] = $this->m_statistic["DELETES"]+1;
		   $this->m_statistic["DELETES_TIME_TOTAL_MS"] = $this->m_statistic["DELETES_TIME_TOTAL_MS"] + $this->m_last_query_ms;
		}

		return ( $this->m_use_sqlite3 ? $sqlite->changes() : sqlite_changes($sqlite) );
   }
//-------------------------------------------------------------------------------------------------------------------------------------   
/**
    * start a transaction 
    *
    * @param string $id
    */
   function begin($id="master"){
      if(isset($this->m_transaction[$id]) && $this->m_transaction[$id][0] == true){
         $this->commit($id);
      }

      // Autocommit mode is on by default. Autocommit mode is disabled by a BEGIN statement. Autocommit mode is re-enabled by a COMMIT or ROLLBACK
      /*
      $lastautocommit = 1;
      $res = $this->sql($id, "SELECT @@autocommit AS val");
      if($this->getError($id) != 0){
         $lastautocommit = $res->getField("val");
      }

      $this->sql($id, "SET SESSION AUTOCOMMIT = 0");
      */
      $this->sql($id, "BEGIN");

      // $this->m_transaction[$id] = array(true, $lastautocommit);
      $this->m_transaction[$id] = array(true);

   }
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * rollback a transaction 
    *
    * @param string $id
    */
   function rollback($id="master"){
      if(!isset($this->m_transaction[$id]) || $this->m_transaction[$id][0] != true){
         return;
      }

      $this->sql($id, "ROLLBACK");

      //$this->sql($id, "SET SESSION AUTOCOMMIT = ".$this->m_transaction[$id][1]);

      $this->m_transaction[$id][0] = false;
   }
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * commit a transaction 
    *
    * @param string $id
    */
   function commit($id="master"){
      if(!isset($this->m_transaction[$id]) || $this->m_transaction[$id][0] != true){
         return;
      }

      $this->sql($id, "COMMIT");

      //$this->sql($id, "SET SESSION AUTOCOMMIT = ".$this->m_transaction[$id][1]);

      $this->m_transaction[$id][0] = false;
   }
//-------------------------------------------------------------------------------------------------------------------------------------   
}
//-------------------------------------------------------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------------------------------------------------------
class SQLiteDBResult{
	// private
	private $m_result = null;
	private $m_row_pointer = 0;
	private $m_field_names = array();
	private $m_field_info = array();
	private $m_free_result_resource = true;
	private $m_fetch_type = null;
	
	private $m_sqlite3_result = array();
//-------------------------------------------------------------------------------------------------------------------------------------
// constructor
   /**
    * sqlite result as object
    *
    * @param resource $result
    * @param int $fetch_type
    * @param bool $free_resource    if false the result resource will be free by garbage collector or should be free manually
    * @return SQLiteDBResult
    */
    function __construct($result, $fetch_type=SQLITE_BOTH, $free_resource=true){
      if(is_object($result) &&  $result instanceof SQLite3Result && $fetch_type == SQLITE_BOTH) $fetch_type = SQLITE3_BOTH; 
       
      $this->m_free_result_resource = $free_resource;
      $this->m_result = $result;
      $this->m_fetch_type = $fetch_type;

            
    	if($this->m_result && is_resource($this->m_result)){
			if($this->rows() > 0){			   
   			$fcount = sqlite_num_fields($this->m_result);
       	   for($f = 0; $f < $fcount; $f++){
       	       $fname = sqlite_field_name($this->m_result, $f);
       	       $this->m_field_names += array($fname=>$f);
       	       $info = array("name"=>$fname,
       	                     "type"=>"",
       	                     "len"=>-1,
       	                     "flags"=>""      	                     
       	                    );
       	       $this->m_field_info[] = $info;
       	   }
			}			
    	}
    	    	    	    	    	
    	
    	if(is_object($result) &&  $result instanceof SQLite3Result){
    	   while($row = $result->fetchArray($fetch_type)){
    	      $this->m_sqlite3_result[] = $row;  
    	   }
    	
    	   
    	   if($this->rows() > 0){
    	      $fcount = $result->numColumns();   			   			
    	      
    	      for($f = 0; $f < $fcount; $f++){
       	       $fname = $result->columnName($f);
       	              	       
       	       $this->m_field_names += array($fname=>$f);
       	       $info = array("name"=>$fname,
       	                     "type"=>$result->columnType($f),
       	                     "len"=>-1,
       	                     "flags"=>""      	                     
       	                    );
       	       $this->m_field_info[] = $info;
       	   }         	   
    	   }
    	}
    }
//-------------------------------------------------------------------------------------------------------------------------------------
   function __destruct(){
      $this->free();
   }
//-------------------------------------------------------------------------------------------------------------------------------------
// private
	function free(){
	   if(isset($this->m_result) && $this->m_result && is_resource($this->m_result)){
	      if($this->m_free_result_resource){
  			   unset($this->m_result);
	      }
  			$this->m_result = NULL;

			$this->m_field_info = NULL;
			$this->m_field_names = NULL;

			$this->m_row_pointer = 0;
			
			unset($this->m_result);		
			unset($this->m_field_info);
			unset($this->m_field_names);		
			unset($this->m_row_pointer);		
							
	      gc_collect_cycles_overX($GLOBALS['CRVCL']['GC_COLLECT_CYCLES_PERCENT']);
	   }
	   
	   if(isset($this->m_result) && $this->m_result && is_object($this->m_result)){
	      if($this->m_free_result_resource){
  			   $this->m_result->finalize();
	      }
  			$this->m_result = NULL;

			$this->m_field_info = NULL;
			$this->m_field_names = NULL;

			$this->m_row_pointer = 0;
			
			$this->m_sqlite3_result = NULL;
			
			unset($this->m_result);		
			unset($this->m_field_info);
			unset($this->m_field_names);		
			unset($this->m_row_pointer);		
			unset($this->m_sqlite3_result);
							
	      gc_collect_cycles_overX($GLOBALS['CRVCL']['GC_COLLECT_CYCLES_PERCENT']);
	   }
	}
//-------------------------------------------------------------------------------------------------------------------------------------
// public

   /**
    * return the resource of sqlite result
    *
    * @return resource
    */
   function getResource(){
   	if($this->rows() > 0){
   		$this->gotoRow(0);
   	}
      return $this->m_result;
   }
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * rows of result
    *
    * @return int
    */
	function rows(){
	   if($this->m_result && is_resource($this->m_result)){
		   return sqlite_num_rows($this->m_result);
	   }
	   if(is_object($this->m_result) && $this->m_result instanceof SQLite3Result){
	      return acount($this->m_sqlite3_result);
	   }
	   return 0;
	}
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * jump to the passed row
    *
    * @param int $rownum
    * @return bool
    */
   function gotoRow($rownum){
      if($this->m_result && is_resource($this->m_result)){
         $this->m_row_pointer = $rownum;
         if($this->bof() || $this->eof()){return false;}
		   return sqlite_seek($this->m_result, $rownum);
	   }
	   
	   if(is_object($this->m_result) &&  $this->m_result instanceof SQLite3Result){
         $this->m_row_pointer = $rownum;
         if($this->bof() || $this->eof()){return false;}
		   return isset($this->m_sqlite3_result[$rownum]);
	   }
	   
	   return false;
   }
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * current row
    *
    * @return int
    */
	function row(){
	   if($this->bof() || $this->eof()){return -1;}
		return $this->m_row_pointer;
	}
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * skip one row
    *
    * @return bool
    */
	function next(){
	   $this->m_row_pointer = $this->m_row_pointer+1;
	   if($this->eof()){return false;}
		return $this->gotoRow($this->m_row_pointer);
	}
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * skip back one row
    *
    * @return bool
    */
	function previous(){
	   $this->m_row_pointer = $this->m_row_pointer-1;
	   if($this->bof()){return false;}
		return $this->gotoRow($this->m_row_pointer);
	}
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * skip to first row
    *
    * @return bool
    */
	function first(){
		$this->m_row_pointer = 0;
		return $this->gotoRow($this->m_row_pointer);
	}
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * skip to last row
    *
    * @return bool
    */
	function last(){
		$this->m_row_pointer = $this->rows()-1;
		return $this->gotoRow($this->m_row_pointer);
	}
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * return true if EOF
    *
    * @return bool
    */
   function eof(){
      if($this->rows() == 0){return true;}
      if($this->m_row_pointer > $this->rows()-1){return true;}
      return false;
   }
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * return true if BOF
    *
    * @return bool
    */
   function bof(){
      if($this->rows() == 0){return true;}
      if($this->m_row_pointer < 0){return true;}
      return false;
   }
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * number of rows
    *
    * @return int
    */
	function fieldCount(){
		if(($this->m_result && is_resource($this->m_result)) || (is_object($this->m_result) &&  $this->m_result instanceof SQLite3Result)){
			return acount($this->m_field_names);
		}
		return 0;
	}
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * column of fieldname
    *
    * @param string $s
    * @return string
    */
	function fieldPos($s){
		if(($this->m_result && is_resource($this->m_result)) || (is_object($this->m_result) &&  $this->m_result instanceof SQLite3Result)){
		   if(array_key_exists ($s, $this->m_field_names)){
			   return $this->m_field_names[$s];
		   }
		   return -1;
		}
		return -1;
	}
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * fieldname of column
    *
    * @param int $i
    * @return string
    */
	function fieldName($i){
	   if(($this->m_result && is_resource($this->m_result)) || (is_object($this->m_result) &&  $this->m_result instanceof SQLite3Result)){
   		if(array_key_exists ($i, $this->m_field_info)){
   		   return $this->m_field_info[$i]["name"];
   	   }
   		return "";
	   }
	   return "";
	}
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * fieldtype of column
    *
    * @param mixed $field
    * @return string
    */
	function fieldType($field){
	   if(($this->m_result && is_resource($this->m_result)) || (is_object($this->m_result) &&  $this->m_result instanceof SQLite3Result)){
	      if(!is_numeric($field)){
	       $field = $this->fieldPos($field);
	      }
   		if(array_key_exists ($field, $this->m_field_info)){
   		   return $this->m_field_info[$field]["type"];
   	   }
   		return "null";
	   }
		return "null";
	}
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * fieldlen of column
    *
    * @param mixed $field
    * @return int
    */
	function fieldLen($field){
	   if(($this->m_result && is_resource($this->m_result)) || (is_object($this->m_result) &&  $this->m_result instanceof SQLite3Result)){
	      if(!is_numeric($field)){
	       $field = $this->fieldPos($field);
	      }
   		if(array_key_exists ($field, $this->m_field_info)){
   		   return $this->m_field_info[$field]["len"];
   	   }
   		return 0;
	   }
		return 0;
	}
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * fieldflags of column
    *
    * @param mixed $field
    * @return string
    */
	function fieldFlags($field){
	   if(($this->m_result && is_resource($this->m_result)) || (is_object($this->m_result) &&  $this->m_result instanceof SQLite3Result)){
	      if(!is_numeric($field)){
	       $field = $this->fieldPos($field);
	      }
   		if(array_key_exists ($field, $this->m_field_info)){
   		   return $this->m_field_info[$field]["flags"];
   	   }
   		return "";
	   }
		return "";
	}
//-------------------------------------------------------------------------------------------------------------------------------------
	/**
	 * to get the value of a field at current row, dependent from fetch_type
	 *
	 * @param mixed $fieldname_num
	 * @return string
	 */
	function getField($fieldname_num){
		if($this->rows() == 0 || $this->bof() || $this->eof()){return null;}
		
		
		$row = array();
		
		if(($this->m_result && is_resource($this->m_result)) || (is_object($this->m_result) &&  $this->m_result instanceof SQLite3Result)){
		   if(isset($this->m_sqlite3_result[$this->m_row_pointer]))
		      $row = $this->m_sqlite3_result[$this->m_row_pointer];
		}else{
		   $row = sqlite_fetch_array($this->m_result, $this->m_fetch_type);
		   sqlite_seek($this->m_result, $this->m_row_pointer); // because sqlite_fetch_array skip   
		}
		
		
		if(!isset($row[$fieldname_num])){return null;}
		return $row[$fieldname_num];
	}
//-------------------------------------------------------------------------------------------------------------------------------------
}
//-------------------------------------------------------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------------------------------------------------------
/**
 * create querys
 * to create a insert or update query use addValue()
 * to create a select query use addField() and addJoin()
 *
 */
class SQLiteDBBuilder{
	// private
	private $m_values = array();
	private $m_fields = array();
	private $m_joins = array();

	public $m_type = "";
	private $m_table = "";
	private $m_index = "";
	private $m_where = "";
	private $m_additional = "";
	private $m_options = "";	
	/**
	 * escape strings on method "addValue"
	 *
	 * @var bool
	 */
	public $m_escape_strings = true;
//-------------------------------------------------------------------------------------------------------------------------------------
// constructor
   /**
    * create a SQLiteDBBuilder object
    *
    * @param int $SQLITE_EXEC_TYPE SQLITE_INSERT or SQLITE_UPDATE or SQLITE_UPDINS or SQLITE_SELINS
    * @param mixed $table as string or array(<SQLiteDBBuilder object>, <string resultname>) sample: array($build, "s1") => used for a SELECT IN SELECT
    * @param string $where
    * @param string $additional => only for SELECT   Sample: GROUP BY f1
    * @param string $index  => only for SELECT, name of index or index expression like "IGNORE INDEX (key3)"
    * @return SQLiteDBBuilder
    */
    function __construct($SQLITE_EXEC_TYPE, $table, $where="", $additional="", $index=""){
    	$this->m_type = $SQLITE_EXEC_TYPE;
    	$this->m_table = $table;
    	$this->m_where = $where;
    	$this->m_index = $index;
    	$this->m_additional = $additional;
    }
//-------------------------------------------------------------------------------------------------------------------------------------
    function __destruct(){
       $this->free();
    }
//-------------------------------------------------------------------------------------------------------------------------------------
	function free(){
	   $this->m_values = NULL;
	   $this->m_fields = NULL;
	   $this->m_joins = NULL;
	}
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * return the sql query
    *
    * @return string
    */
   function query(){
      $sql = "";

      if($this->m_type == SQLITE_SELECT){
         $fcount = acount($this->m_fields);
         $jcount = acount($this->m_joins);

         $sql .= "SELECT ";

         if(!empty($this->m_options)){
            $sql .= $this->m_options;
         }

         if($fcount==0){
            $sql .= "* ";
         }else{
            for($f = 0; $f < $fcount; $f++){
               $table = $this->m_fields[$f]["table"];
               $field = $this->m_fields[$f]["field"];
               $quote = $this->m_fields[$f]["quote"];
//echo "t: ".$table . " f:" . $field.BR;
               if(empty($table)){
                  $table = $this->m_table;
               }
                                                                               // not longer needed since parameter $quote of method "addFiled", but leave it for compatibility
               if($quote && is_string($table) && strpos($field, ".") === false && strpos(strtoupper($field), "COUNT") === false){
                  if(strpos($field, "(") !== false){
                     $pre = strcut($field, "(", true);
                     $post = strrcut($field, ")");
                     $field = strcut(strrcut($field, "(", true), ")");
                     $sql .= $pre.$table.".\"".$field."\"".$post;
                  }else{
                       $post = "";
                       if(stripos($field, " AS ") !== false){
                          $post = strricut($field, " AS ");
                          $field = trim(stricut($field, " AS "));
                       }
                  	  $sql .= $table.".\"".$field."\"".$post;
                  }
               }else{
                  $sql .= $field;
               }
               if($f != $fcount-1){
                  $sql .= ", ";
               }
            }// for
         }

         $table = $this->m_table;
         if(is_array($table) && count($table) == 2){
            $buildobj = $table[0];
            $nameAS = $table[1];
            if(is_object($buildobj) && ($buildobj instanceof SQLiteDBBuilder) && $buildobj->m_type == SQLITE_SELECT){
               $table = '('.$buildobj->query().') AS '.$nameAS;
            }
         }

         $sql .= " FROM ".$table;

         if(!empty($this->m_index)){
           if(strpos($this->m_index, "(") !== false){
              $sql .= " ".$this->m_index;
           }else{
              $sql .= " FORCE INDEX(".$this->m_index.")";
           }
         }

         for($j = 0; $j < $jcount; $j++){
            $type = $this->m_joins[$j]["type"];
            $table = $this->m_joins[$j]["table"];
            $f1 = $this->m_joins[$j]["f1"];
            $f2 = $this->m_joins[$j]["f2"];
            $join = "LEFT JOIN";

            if($type == "R"){$join = "RIGHT JOIN";}
            if($type == "I"){$join = "INNER JOIN";}

            $sql .= " ".$join." ".$table." ON ";

            if(strpos($f1,".") === false && !empty($table)){
               $sql .= $table.".";
            }
            $sql .= $f1;

            $sql .= " = ";

            if(strpos($f2,".") === false){
               if($j == 0){
                  $table = $this->m_table;
               }else{
                  $table = $this->m_fields[$j-1]["table"];
               }
               if(!empty($table)){
                  $sql .= $table.".";
               }
            }
            $sql .= $f2;

            if($j != $jcount-1){
               $sql .= " ";
            }
         }

         if(!empty($this->m_where)){
           $sql .= " WHERE ".$this->m_where;
         }
         if(!empty($this->m_additional)){
           $sql .= " ".$this->m_additional;
         }
      ########################################
      }else if($this->m_type == SQLITE_DELETE){   
         $sql .= "DELETE ";   
         
         if(!empty($this->m_options)){
            $sql .= $this->m_options;
         }
         
         $sql .= "FROM ";
         
         $sql .= $this->m_table;
         
         if(!empty($this->m_where)){
           $sql .= " WHERE ".$this->m_where;
         }
         
         if(!empty($this->m_additional)){
           $sql .= " ".$this->m_additional;
         }        

                 
      ########################################
      }else{
         $fcount = acount($this->m_values);

         if($this->m_type == SQLITE_INSERT){
            $sql .= "INSERT ";
         }else if($this->m_type == SQLITE_UPDATE){
            $sql .= "UPDATE ";
         }

         if(!empty($this->m_options)){
            $sql .= $this->m_options;
         }
         
         
         if($this->m_type == SQLITE_INSERT){
            $sql .= "INTO ".$this->m_table;            
         }else if($this->m_type == SQLITE_UPDATE){         
            $sql .= $this->m_table . " SET ";
         }
         
         // used by insert
         $colnames = " (";
         $values = " VALUES(";
         /////////////////
         
         for($f = 0; $f < $fcount; $f++){                     
            $field = $this->m_values[$f]["field"];
            $val = $this->m_values[$f]["val"];
            $type = $this->m_values[$f]["type"];
            
            if(strpos("S;D;",$type) !== false){
               $val = "\"".$val."\"";
            }
            
            if($this->m_type == SQLITE_INSERT){
         
              if(strpos("F;",$type) !== false){
                 $colnames .= $field;
              }else{
                 $colnames .= "\"".$field."\"";
              }   
              if($f != $fcount-1){
                  $colnames .= ", ";
              }else{$colnames .= ")";} 
              
              $values .= $val;              
              if($f != $fcount-1){
                  $values .= ", ";
              }else{
                  $values .= ")";
                  
                  $sql .= $colnames . $values;
              } 
                  
            }else if($this->m_type == SQLITE_UPDATE){                        
               if(strpos("F;",$type) !== false){
                  $sql .= $field." = ".$val;
               }else{
                  $sql .= "\"".$field."\""." = ".$val;
               }
               if($f != $fcount-1){
                  $sql .= ", ";
               }            
            }   
         }            
         
         
         if($this->m_type == SQLITE_UPDATE && !empty($this->m_where)){
            $sql .= " WHERE ".$this->m_where;
         }
         if($this->m_type == SQLITE_UPDATE && !empty($this->m_additional)){
           $sql .= " ".$this->m_additional;
         }
      }
      return $sql;
   }
//-------------------------------------------------------------------------------------------------------------------------------------
// public

   /**
    * add a value for INSERT or UPDATE
    *
    * @param string $field
    * @param mixed $value
    * @param string $type as I for integer, N for numeric (float/double)  or  C and S for string  or  D for date  or  F and O for functions and operations
    */
   function addValue($field, $value, $type="U"){
      if($type != 'SET'){ // to support named types like 'int' or 'numeric'
         $type = strtoupper(left($type,1));
      }

      $type = str_replace("C","S",$type);
      $type = str_replace("O","F",$type);

      if(strpos("S;I;N;D;F;SET;E;",$type.';') === false){ // type auto detection
         if(is_string($value)){$type = "S";}
         if($type == 'S' && strpos($value, '(') !== false && strpos($value, ')') !== false){$type = "F";}
         if(is_numeric($value)){$type = "N";}
         if(is_bool($value)){$value = intval($value); $type = "N";}         
      }
      if($value === null){$value = 'NULL'; $type = "U";}

      if($type == "I"){
         $value = intval($value);
      }
      if($type == "N"){
         $value = doubleval($value);
      }
      if($type == "D"){
         $value = dateToMySQLDate($value);
      }
      if($type == "S" && $this->m_escape_strings){
         $value = sqlite_escape_string($value);                  
      }
      
      if($type == "SET" && is_array($value)){ 
         $tmp = '';         
         for($i = 0; $i < acount($value); $i++){
            if(!empty($value[$i])){
               $tmp .= $value[$i].',';
            }   
         } 
         $value = $tmp;         
         $tmp = null;
         if(right($value,1)==','){
            $value = substr($value, 0, -1);
         }
      }
      if("SET" == $type || "E" == $type){
         $value = str_replace(' ', '', $value);
      }      
      $this->m_values[] = array("field"=>$field,"val"=>$value,"type"=>$type);
   }
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * add a field for SELECT
    *
    * @param mixed $name => <fieldname> or <table.fieldname> or <db.table.fieldname> 
    * @param string $table as string (optional) or SQLiteDBBuilder object for a subselect  
    * @param bool $quote (optional) => set automatically the tablename to the field, if you use as sample a function like COUNT use $quote == false
    */
   function addField($name, $table="", $quote=true){
      if(is_object($table) && $table instanceof SQLiteDBBuilder){
         $quote = false;
         $name = ' ('.$table->query().') AS '.$name;
      }
      if($quote && strpos($name, ".") !== false){
         $tmp = explode(".", $name);
         $count = acount($tmp);
         if($count == 2){
            $name = $tmp[1];
            $table = $tmp[0];
         }else if($count == 3){
            $name = $tmp[2];
            $table = $tmp[0].".".$tmp[1];
         }
      }

      $this->m_fields[] = array("field"=>$name,"table"=>$table, "quote"=>$quote);
   }
//-------------------------------------------------------------------------------------------------------------------------------------
/**
 * add a JOIN
 *
 * @param string $table => table to join
 * @param string $field1 => field from join table
 * @param string $field2 => field from main table
 * @param string $type => "L" for LEFT JOIN, "R" for RIGHT JOIN, "I" for INNER JOIN
 */
   function addJoin($table, $field1, $field2, $type="L"){
      $type = strtoupper(left($type,1));
      $this->m_joins[] = array("type"=>$type,"table"=>$table,"f1"=>$field1,"f2"=>$field2);
   }
//-------------------------------------------------------------------------------------------------------------------------------------
   function setOptions($s){
      $this->m_options = trim($s)." ";
   }
}
//-------------------------------------------------------------------------------------------------------------------------------------
############################################################
}
############################################################
?>
Return current item: crVCL PHP Framework