Location: PHPKode > projects > CS-Content [Dynamic Content System] > cs-content_0.10.12/db_types/cs_phpDB__sqlite.class.php
<?php

/*
 * A class for generic SQLite database access.
 * 
 * SVN INFORMATION:::
 * SVN Signature:::::::: $Id: cs_phpDB__sqlite.class.php 298 2008-07-15 20:49:56Z crazedsanity $
 * Last Committted Date: $Date: 2008-07-15 15:49:56 -0500 (Tue, 15 Jul 2008) $
 * Last Committed Path:: $HeadURL: https://cs-content.svn.sourceforge.net/svnroot/cs-content/releases/0.10/db_types/cs_phpDB__sqlite.class.php $
 * 
 */


class cs_phpDB__sqlite {

	/** Internal result set pointer. */
	protected $result = NULL;
	
	/** Internal error code. */
	protected $errorCode = 0;
	
	/** Status of the current transaction. */
	protected $transStatus = NULL;
	
	/** Whether there is a transaction in progress or not. */
	protected $inTrans = FALSE;
	
	/** Holds the last query performed. */
	protected $lastQuery = NULL;
	
	/** List of queries that have been run */
	protected $queryList=array();
	
	/** How many seconds to wait for a query before cancelling it. */
	protected $timeOutSeconds = NULL;
	
	/** Internal check to determine if a connection has been established. */
	protected $isConnected=FALSE;
	
	/** Internal check to determine if the parameters have been set. */
	protected $paramsAreSet=FALSE;
	
	/** Resource handle. */
	protected $connectionID = -1;
	
	/** Name of the database */
	protected $dbname;
	
	/** Directory that is readable + writable, which contains the SQLite db file. */
	protected $rwDir;
	
	/** cs_globalFunctions object, for string stuff. */
	protected $gfObj;
	
	/** Internal check to ensure the object has been properly created. */
	protected $isInitialized=FALSE;
	
	/** List of prepared statements, indexed off the name, with the sub-array being fieldname=>dataType. */
	protected $preparedStatements = array();
	
	/** Set to TRUE to save all queries into an array. */
	protected $useQueryList=FALSE;
	
	/** array that essentially remembers how many times beginTrans() was called. */
	protected $transactionTree = NULL;
	
	////////////////////////////////////////////
	// Core primary connection/database function
	////////////////////////////////////////////
	
	
	//=========================================================================
	public function __construct() {
		$this->gfObj = new cs_globalFunctions;
		
		if(defined('DEBUGPRINTOPT')) {
			$this->gfObj->debugPrintOpt = DEBUGPRINTOPT;
		}
		
		$this->isInitialized = TRUE;
	}//end __construct()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Make sure the object is sane.
	 */
	final protected function sanity_check() {
		if($this->isInitialized !== TRUE) {
			throw new exception(__METHOD__ .": not properly initialized");
		}
	}//end sanity_check()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Set appropriate parameters for database connection
	 */
	public function set_db_info(array $params){
		$this->sanity_check();
		$required = array('rwDir', 'dbname');
		
		$requiredCount = 0;
		foreach($params as $index=>$value) {
			if(property_exists($this, $index) && in_array($index, $required)) {
				$this->$index = $value;
				$requiredCount++;
			}
			else {
				throw new exception(__METHOD__. ": property (". $index .") does " .
					"not exist or isn't allowed");
			}
		}
		
		if($requiredCount == count($required)) {
			$this->paramsAreSet = TRUE;
		}
		else {
			throw new exception(__METHOD__ .": required count (". $requiredCount 
				.") does not match required number of fields (". count($required) .")");
		}
	}//end set_db_info()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Wrapper for close()
	 */
	function disconnect() {
		//Disconnect from $database
		return($this->close());
	}//end disconnect()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Standard method to close connection.
	 */
	function close() {
		$this->isConnected = FALSE;
		$retval = null;
		if($this->connectionID != -1) {
			sqlite_close($this->dbConnObj);
			$retval = TRUE;
		}
		else {
			throw new exception(__METHOD__ .": Failed to close connection: connection is invalid");
		}
		
		return($retval);
	}//end close()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Connect to the database
	 */
	function connect(array $dbParams=NULL, $forceNewConnection=FALSE){
		$this->sanity_check();
		$retval = NULL;
		$connectError = NULL;
		
		$this->set_db_info($dbParams);
		
		if($this->paramsAreSet === TRUE && $this->isConnected === FALSE) {
			
			$dbFile = $this->rwDir .'/'. $this->dbname;
			$this->connectionID = sqlite_open($dbFile, $connectError);
			
			if(is_resource($this->connectionID)) {
				$this->errorCode=0;
				$this->isConnected = TRUE;
				$retval = $this->connectionID;
			}
			else {
				throw new exception(__METHOD__ .": FATAL ERROR: ". $connectError);
			}
		}
		else {
			throw new exception(__METHOD__ .": paramsAreSet=(". $this->paramsAreSet ."), isConnected=(". $this->isConnected .")");
		}
		
		return($retval);
	}//end connect()
	//=========================================================================
	
	
	
	//=========================================================================
	/** 
	 * Run sql queries
	 * 
	 * TODO: re-implement query logging (setting debug, logfilename, etc).
	 */
	function exec($query) {
		$this->lastQuery = $query;
		if($this->useQueryList) {
			$this->queryList[] = $query;
		}
		$returnVal = false;
		
		if($this->connectionID != -1) {
			$this->result = @sqlite_query($this->connectionID, $query);

			if($this->result !== false) {
				if (eregi("^[[:space:]]*select", $query)) {
					//If we didn't have an error and we are a select statement, move the pointer to first result
					$numRows = $this->numRows();
					$returnVal = $numRows;
				}
				else {
					//We got something other than an update. Use numAffected
					$returnVal = $this->numAffected();
				}
			}
 		}
		return($returnVal);
	}//end exec()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Returns any error caused by the last executed query.
	 * 
	 * @return NULL			OK: no error
	 * @return (string)		FAIL: contains error returned from the query.
	 */
	function errorMsg($setMessage=NULL,$logError=NULL) {
		$this->sanity_check();
		if ($this->connectionID < 0 || !is_resource($this->connectionID)) {
			$retVal = "Failed to open connection to database (". $this->dbname .")";
		} else {
			$errorCode = sqlite_last_error($this->connectionID);
			if($errorCode === 0) {
				$retVal = NULL;
			}
			else {
				$retVal = sqlite_error_string($errorCode);
			}
		}

		return($retVal);
	}//end errorMsg()
	//=========================================================================
	
	
	
	
	///////////////////////
	// Result set related
	///////////////////////
	
	
	
	//=========================================================================
	/**
	 * Return the current row as an object.
	 */
	function fobject() {
		$this->sanity_check();
		if($this->result == NULL || $this->row == -1) {
			$retval = NULL;
		}
		else {
			//NOTE::: this function isn't documented (as of 2008-06-04)... maybe broken.
			$retval = sqlite_fetch_object($this->result);
		}
		
		return($retval);
	}
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Fetch the current row as an array containing fieldnames AND numeric indexes.
	 */
	function farray(){
		if($this->result == NULL || $this->row == -1) {
			$retval = NULL;
		}
		else {
			$retval = sqlite_fetch_array($this->result);
		}
		
		return($retval);
	}//end farray()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Similar to farray(), except all indexes are non-numeric, and the entire 
	 * result set is retrieved: if only one row is available, no numeric index 
	 * is set, unless $numbered is TRUE.
	 * 
	 * TODO: clean this up!
	 */
	function farray_fieldnames($index=NULL, $numbered=NULL, $unsetIndex=NULL) {
		$this->sanity_check();
		$retval = NULL;
		
		//before we get too far, let's make sure there's something there.
		if($this->numRows() <= 0) {
			$retval = 0;
		}
		else {
			$loopThis = $this->fetch_all();
			$retval = array();
			foreach($loopThis as $num=>$record) {
				if(!is_null($index)) {
					if(!isset($record[$index])) {
						throw new exception(__METHOD__ .": index (". $index .") doesn't exist in array::: ". $this->gfObj->debug_print($record,0));
					}
				}
				else {
					$index = $num;
				}
				
				$subRecordArr = array();
				foreach($record as $subIndex=>$subValue) {
					if(!is_numeric($subIndex)) {
						$subRecordArr[$subIndex] = $subValue;
					}
				}
				
				if($unsetIndex) {
					unset($subRecordArr[$index]);
				}
				$retval[$record[$index]] = $subRecordArr;
			}
			
			if(!$numbered && count($retval) == 1) {
				//just give back the single record...
				$keys = array_keys($retval);
				$retval = $retval[$keys[0]];
			}
		}
		return($retval);
	}//end farray_fieldnames()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Retrieve the entire result set, with the final array containing 
	 * name=>value pairs.
	 */
	function farray_nvp($name, $value) {
		if((!$name) OR (!$value)) {
			$retval = 0;
		}
		else {
			$data = $this->fetch_all();
			$retval = array();
			foreach($data as $key=>$data) {
				$retval[$data[$name]] = $data[$value];
			}
		}

		return($retval);
	}//end farray_nvp()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Similar to farray_fieldnames(), but only returns the NUMERIC indexes
	 */
	function farray_numbered($index, $unsetIndex=NULL) {
		$this->sanity_check();
		$retval = NULL;
		
		//before we get too far, let's make sure there's something there.
		if($this->numRows() <= 0) {
			$retval = 0;
		}
		else {
			$loopThis = $this->fetch_all();
			$retval = array();
			foreach($loopThis as $num=>$record) {
				if(!is_null($index)) {
					if(!isset($record[$index])) {
						throw new exception(__METHOD__ .": index (". $index .") doesn't exist in array::: ". $this->gfObj->debug_print($record,0));
					}
					if($unsetIndex) {
						unset($record[$index]);
					}
				}
				else {
					$index = $num;
				}
				
				$subRecordArr = array();
				foreach($record as $subIndex=>$subValue) {
					if(is_numeric($subIndex)) {
						$subRecordArr[$subIndex] = $subValue;
					}
				}
				$retval[$record[$index]] = $subRecordArr;
			}
		}
		
		return($retval);
	}//end farray_numbered()
	//=========================================================================
	
	
	
	//=========================================================================
	function fetch_all($index=NULL, $numbered=NULL,$unsetIndex=1) {
		$this->sanity_check();
		$retval = NULL;
		
		//before we get too far, let's make sure there's something there.
		if($this->numRows() <= 0) {
			$retval = 0;
		}
		else {
			$retval = sqlite_fetch_all($this->result);
		}
		return($retval);
	}//end farray_fieldnames()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Returns the number of tuples affected by an insert/delete/update query.
	 * NOTE: select queries must use numRows()
	 */
	function numAffected() {
		if($this->result == null) {
			$retval = 0;
		} else {
			$this->affectedRows = sqlite_changes($this->connectionID);
			$retval = $this->affectedRows;
		}
		
		return($retval);
	}//end numAffected()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Returns the number of rows in a result (from a SELECT query).
	 */
	function numRows() {
		if ($this->result == null) {
			$retval = 0;
		}
		else {
			$this->numrows = sqlite_num_rows($this->result);
			$retval = $this->numrows;
		}
		
		return($retval);
	}//end numRows()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * wrapper for numAffected()
	 */
	function affectedRows(){
		return($this->numAffected());
	}//end affectedRows()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Get the number of fields in a result.
	 */
	// get the number of fields in a result
	function num_fields() {
		if($this->result == null) {
			$retval = 0;
		}
		else {
			$retval = sqlite_num_fields($this->result);
		}
		return($retval);	
	}//end num_fields()
	//=========================================================================
	
	
	
	//=========================================================================
	function column_count() {
		return($this->numFields());
	}//end column_count()
	//=========================================================================
	
	
	
	//=========================================================================
	/** 
	 * get last OID (object identifier) of last INSERT statement
	 */
	function lastOID($doItForMe=0, $field=NULL) {
		if($this->connectionID == NULL) {
			throw new exception(__METHOD__ .": no connectionID to use (". $this->connectionID .")");
		}
		else {
			$retval = sqlite_last_insert_rowid($this->connectionID);
		}
		return($retval);
	}//end lastOID()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * get result field name of the given field number.
	 */
	// get result field name
	function fieldname($fieldnum) {
		if($this->result == NULL) {
			$retval =NULL;
		}
		else {
			$retval = sqlite_field_name($this->result, $fieldnum);
		}
		
		return($retval);
	}//end fieldname()
	//=========================================================================
	
	
	
	
	////////////////////////
	// Transaction related
	////////////////////////
	
	
	
	
	//=========================================================================
	/**
	 * Start a transaction.
	 */
	function beginTrans() {
		return($this->exec("BEGIN TRANSACTION"));
	}//end beginTrans()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Commit a transaction.
	 */
	function commitTrans() {
		return($this->exec("COMMIT TRANSACTION"));
	}//end commitTrans()
	//=========================================================================
	
	
	
	//=========================================================================
	// returns true/false
	function rollbackTrans() {
		$retval = $this->exec("ROLLBACK TRANSACTION");
		return($retval);
	}//end rollbackTrans()
	//=========================================================================
	
	
	
	////////////////////////
	// SQL String Related
	////////////////////////
	
	
	
	//=========================================================================
	/**
	 * Gets rid of evil characters that might lead ot SQL injection attacks.
	 */
	function querySafe($string) {
		return($this->gfObj->cleanString($string,"query"));
	}//end querySafe()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Make it SQL safe.
	 */
	function sqlSafe($string) {
		return($this->gfObj->cleanString($string,"sql"));
	}//end sqlSafe()
	//=========================================================================
	
	
	
	//=========================================================================
	public function is_connected() {
		$retval = FALSE;
		if(is_resource($this->connectionID) && $this->isConnected === TRUE) {
			$retval = TRUE;
		}
		
		return($retval);
	}//end is_connected()
	//=========================================================================
	
	
	
} // end class phpDB

?>
Return current item: CS-Content [Dynamic Content System]