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

/*
 * A class for generic MySQL database access.
 * 
 * SVN INFORMATION:::
 * SVN Signature:::::::: $Id: cs_phpDB__mysql.class.php 272 2008-04-29 16:46:43Z crazedsanity $
 * Last Committted Date: $Date: 2008-04-29 11:46:43 -0500 (Tue, 29 Apr 2008) $
 * Last Committed Path:: $HeadURL: https://cs-content.svn.sourceforge.net/svnroot/cs-content/releases/0.10/db_types/cs_phpDB__mysql.class.php $
 * 
 */



class cs_phpDB__mysql {

	/** 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;
	
	/** Hostname or IP to connect to */
	protected $host;
	
	/** Port to connect to (default for Postgres is 5432) */
	protected $port;
	
	/** Name of the database */
	protected $dbname;
	
	/** Username to connect to the database */
	protected $user;
	
	/** password to connect to the database */
	protected $password;
	
	/** Row counter for looping through records */
	protected $row = -1;
	
	/** 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('host', 'dbname', 'user', 'password');
		
		$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) {
			$retval = mysqlclose($this->connectionID);
		}
		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;
		if(is_array($dbParams)) {
			$this->set_db_info($dbParams);
		}
		
		if($this->paramsAreSet === TRUE && $this->isConnected === FALSE) {
			
			//start output buffer for displaying error.
			ob_start();
			$connID = mysql_connect($this->host, $this->user, $this->pass, $forceNewConnection);
			mysql_select_db($this->dbname);
			$connectError = ob_get_contents();
			ob_end_clean();
			
			if(is_resource($connID)) {
				$this->errorCode=0;
				$this->connectionID = $connID;
				$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()
	//=========================================================================
	
	
	
	//=========================================================================
	function get_hostname() {
		$this->sanity_check();
		return($this->host);
	}//end get_hostname()
	//=========================================================================
	
	
	
	//=========================================================================
	/** 
	 * 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;
		
		$this->result = mysql_query($query, $this->connectionID);
		
		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();
				if($numRows > 0) {
					$this->move_first();
				}
				$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) {
			//TODO: implement MySQL version (error codes may vary)...
			switch ($this->errorCode) {
				//###############################################
				case -1:
				$retVal = "FATAL ERROR - CONNECTION ERROR: RESOURCE NOT FOUND";
				break;
				//###############################################
	
				//###############################################
				case -2:
				$retVal = "FATAL ERROR - CLASS ERROR: FUNCTION CALLED WITHOUT PARAMETERS";
				break;
				//###############################################
				
				//###############################################
				case -3:
				$retVal = "Query exceeded maximum timeout (". $this->timeoutSeconds .")";
				break;
				//###############################################
	
				//###############################################
				default:
				$retVal = null;
				//###############################################
			}
		} else {
			//TODO: implement MySQL version..
			$retVal = mysql_error($this->connectionID);
		}

		return($retVal);
	}//end errorMsg()
	//=========================================================================
	
	
	
	
	////////////////////
	// Cursor movement
	////////////////////
	
	
	
	
	//=========================================================================
	/**
	 * move pointer to first row of result set
	 */
	function move_first() {
		$this->sanity_check();
		if($this->result == NULL) {
			$retval = FALSE;
		}
		else {
			$this->set_row(0);
			$retval = TRUE;
		}
		
		return($retval);
	}//end move_first()
	//=========================================================================
	
	
	
	//=========================================================================
	/** 
	 * move pointer to last row of result set
	 */
	function move_last() {
		$this->sanity_check();
		if($this->result == NULL) {
			$retval = FALSE;
		}
		else {
			$this->set_row($this->numRows()-1);
			$retval = TRUE;
		}
		
		return($retval);
	}//end move_list()
	//=========================================================================
	
	
	
	//=========================================================================
	/** 
	 * point to the next row, return false if no next row
	 */
	function move_next() {
		$this->sanity_check();
		// If more rows, then advance row pointer
		if($this->row < $this->numRows()-1) {
			$this->set_row($this->row +1);
			$retval = TRUE;
		}
		else {
			$retval = FALSE;
		}
		
		return($retval);
	}//end move_next()
	//=========================================================================
	
	
	
	//=========================================================================
	/** 
	 * point to the previous row, return false if no previous row
	 */
	function move_previous() {
		// If not first row, then advance row pointer
		if ($this->row > 0) {
			$this->set_row($this->row -1);
			return true;
		}
		else return false;
	}//end move_previous()
	//=========================================================================
	
	
	
	//=========================================================================
	// point to the next row, return false if no next row
	function next_row() {
		// If more rows, then advance row pointer
		if ($this->row < $this->numRows()-1) {
				$this->set_row($this->row +1);
				return true;
		}
		else return false;
	}//end next_row()
	//=========================================================================
	
	
	
	//=========================================================================
	// can be used to set a pointer to a perticular row
	function set_row($row){
		if(is_numeric($row)) {
			$this->row = $row;
		}
		else {
			throw new exception(__METHOD__ .": invalid data for row (". $row .")");
		}
		return($this->row);
	}//end set_row();
	//=========================================================================
	
	
	
	
	///////////////////////
	// 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 {
			//TODO: implement MySQL version..
			$retval = mysql_fetch_object($this->result, $this->row);
		}
		
		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 {
			//TODO: implement MySQL version..
			$retval = mysql_fetch_array($this->result,$this->row);
		}
		
		return($retval);
	}//end farray()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Another way to retrieve a single row (useful for loops).
	 */
	function frow(){
		$this->sanity_check();
		if($this->numRows() <= 0) {
			$retval = NULL;
		}
		else {
			if($this->result == null || $this->row == -1) {
				$retval = NULL;
			}
			else {
			//TODO: implement MySQL version..
				$retval = mysql_fetch_row($this->result, $this->row);
			}
		}
		
		return($retval);
	}//end frow()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * 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=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 {		
			//keep any errors/warnings from printing to the screen by using OUTPUT BUFFERS.
			ob_start();
			
			$x = 0;
			do {
				$temp = $this->farray();
				foreach($temp as $key=>$value) {
					//remove the numbered indexes.
					if(is_string($key)) {
						$tArr[$key] = $value;
					}
				}
				$newArr[$x] = $tArr;
				$x++;
			}
			while($this->next_row());
			
			if($index) {
				foreach($newArr as $row=>$contents) { //For each of the returned sets of information
					foreach($contents as $fieldname=>$value) { //And now for each of the items in that set
						if($fieldname == $index) {
							//The index for the new array will be this fieldname's value
							$arrayKey = $value;
						}
						
						$tempContent[$fieldname] = $value;
						//don't include the "index" field in the subarray; that always seems to end badly.
						if ($unsetIndex) {
							unset($tempContent[$index]);
						}
					}
					
					if (!isset($tempArr[$arrayKey])) {
						//Make sure we didn't already set this in the array. If so, then we don't have a unique variable to use for the array index. 
						$tempArr[$arrayKey] = $tempContent;
					}
					else {
						//TODO: bigtime cleaning... should only return at the bottom of the method.
						$retval = 0;
						break;
					}
					$arrayKey = NULL; //Blank this out after using it, just in case we don't find one in the next iteration
				}
	
				if (count($tempArr) != count($newArr)) {
					$details = "farray_fieldnames(): Array counts don't match.<BR>\n"
						."FUNCTION ARGUMENTS: index=[$index], numbered=[$numbered], unsetIndex=[$unsetIndex]<BR>\n"
						."LAST QUERY: ". $this->lastQuery;
					throw new exception(__METHOD__ .": $details");
				}
				$newArr = $tempArr;
			}
			//this is where, if there's only one row (and the planets align just the way 
			//	I like them to), there's no row w/ a sub-array...  This is only done 
			//	if $index is NOT set...
			if(($this->numRows() == 1) AND (!$index) AND (!$numbered)) {
				$newArr = $newArr[0];
			}
			$retval = $newArr;
			ob_end_clean();
		}
		return($retval);
	}//end farray_fieldnames()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Uses farray_fieldnames() to retrieve the entire result set, but the final 
	 * array is contains name=>value pairs.
	 */
	function farray_nvp($name, $value) {
		if((!$name) OR (!$value)) {
			$retval = 0;
		}
		else {	
			$tArr = $this->farray_fieldnames(NULL,1);
			if(!is_array($tArr)) {
				$retval = 0;
			}
			else {
				//loop through it & grab the proper info.
				$retval = array();
				foreach($tArr as $row=>$array) {
					$tKey = $array[$name];
					$tVal = $array[$value];
					$retval[$tKey] = $tVal;
				}
			}
		}

		//return the new array.
		return($retval);
	}//end farray_nvp()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Similar to farray_fieldnames(), but only returns the NUMERIC indexes
	 */
	function farray_numbered() {
		do {
			$temp = $this->frow();
			$retArr[] = $temp[0];
		}
		while($this->next_row());
		
		return($retArr);
	}//end farray_numbered()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * 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 {
			//TODO: implement MySQL version..
			$this->affectedRows = mysql_affected_rows($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 {
			//TODO: implement MySQL version..
			$this->numrows = mysql_num_rows($this->result);
			$retval = $this->numrows;
		}
		
		return($retval);
	}//end numRows()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * wrapper for numAffected()
	 */
	function affectedRows(){
		return($this->numAffected());
	}//end affectedRows()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Returns the current row number.
	 */
	function currRow(){
		return($this->row);
	}//end currRow()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * 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 {
			//TODO: implement MySQL version..
			$retval = mysql_num_fields($this->result);
		}
		return($retval);	
	}//end num_fields()
	//=========================================================================
	
	
	
	//=========================================================================
	function column_count() {
		return($this->numFields());
	}//end column_count()
	//=========================================================================
	
	
	
	//=========================================================================
	/** 
	 * get last ID of last INSERT statement
	 */
	function lastID() {
		$retval = mysql_insert_id();
		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 {
			//TODO: implement MySQL version..
			$retval = mysql_field_name($this->result, $fieldnum);
		}
		
		return($retval);
	}//end fieldname()
	//=========================================================================
	
	
	////////////////////////
	// 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()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Gives textual explanation of the current status of our database 
	 * connection.
	 * 
	 * @param $goodOrBad		(bool,optional) return good/bad status.
	 * 
	 * @return (-1)				(FAIL) connection is broken
	 * @return (0)				(FAIL) error was encountered (transient error)
	 * @return (1)				(PASS) useable
	 * @return (2)				(PASS) useable, but not just yet (working 
	 * 								on something)
	 */
	function get_transaction_status($goodOrBad=TRUE) {
		//TODO: implement MySQL version..
		$myStatus = pg_transaction_status($this->connectionID);
		$text = 'unknown';
		switch($myStatus) {
			case PGSQL_TRANSACTION_IDLE: {
				//No query in progress: it's idle.
				$goodOrBadValue = 1;
				$text = 'idle';
				$this->inTrans = FALSE;
			}
			break;
			
			
			case PGSQL_TRANSACTION_ACTIVE: {
				//there's a command in progress.
				$goodOrBadValue = 2;
				$text = 'processing';
			}
			break;
			
			
			case PGSQL_TRANSACTION_INTRANS: {
				//connection idle within a valid transaction block.
				$goodOrBadValue = 1;
				$text = 'valid transaction';
				$this->inTrans = TRUE;
			}
			break;
			
			
			case PGSQL_TRANSACTION_INERROR: {
				//connection idle within a broken transaction.
				$goodOrBadValue = 0;
				$text = 'failed transaction';
				$this->inTrans = TRUE;
			}
			break;
			
			
			case PGSQL_TRANSACTION_UNKNOWN:
			default: {
				//the connection is bad.
				$goodOrBadValue = -1;
				$text = 'bad connection';
			}
			break;
		}
		
		//do they want text or the good/bad number?
		$retval = $text;
		$this->transactionStatus = $goodOrBadValue;
		if($goodOrBad) {
			//they want the number.
			$retval = $goodOrBadValue;
		}
		
		return($retval);
	}//end get_transaction_status()
	//=========================================================================
	
	
	
	//=========================================================================
	public function is_connected() {
		$retval = FALSE;
		if(is_resource($this->connectionID) && $this->isConnected === TRUE) {
			$retval = TRUE;
		}
		
		return($retval);
	}//end is_connected()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Starts a copy command.
	 * 
	 * TODO: implement safeguards so they can only put a line until the copy is ended.
	 */
	public function start_copy($tableName, array $fields) {
		//TODO: implement MySQL version..
		$retval = FALSE;
		$copyStmt = "COPY ". $tableName ." (". $this->gfObj->string_from_array($fields, NULL, ", ") . ") FROM stdin;";
		$this->exec($copyStmt);
		if(!strlen($this->errorMsg())) {
			//TODO: set something here so that NOTHING ELSE can be done except put_line() and end_copy().
			$this->copyInProgress = TRUE;
			$retval = TRUE;
		}
		else {
			$this->end_copy();
			$retval = FALSE;
		}
		
		return($retval);
	}//end start_copy()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Used to send a line to the COPY in progress (only if it was initiated by 
	 * the internal start_copy() method).
	 * 
	 * NOTE: the "end-of-copy" line, '\.', should NEVER be sent here.
	 */
	public function put_line($line) {
		//TODO: implement MySQL version..
		$retval = FALSE;
		if($this->copyInProgress === TRUE) {
			$myLine = trim($line);
			$myLine .= "\n";
			
			$retval = pg_put_line($this->connectionID, $myLine);
		}
		else {
			throw new exception(__METHOD__ .": cannot send line if no copy is in progress");
		}
		
		return($retval);
	}//end put_line()
	//=========================================================================
	
	
	
	//=========================================================================
	public function end_copy() {
		if($this->copyInProgress === TRUE) {
			//send the end-of-copy line...
			$this->put_line("\\.\n");
		}
		
		//TODO: implement MySQL version..
		$retval = pg_end_copy($this->connectionID);
		
		return($retval);
	}//end end_copy()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Determines how many times a transaction has been started.  Starting 
	 * multiple transactions does NOT protect the outer transaction from 
	 * problems that occur in the inner transaction.  In fact, it does the 
	 * opposite: it protects the code from committing too early (which might 
	 * destroy something that depending on the transaction).
	 */
	public function get_transaction_level() {
		if(is_array($this->transactionTree)) {
			$retval = count($this->transactionTree);
		}
		else {
			$retval = 0;
		}
		
		return($retval);
	}//end get_transaction_level()
	//=========================================================================
	
	
	
	//=========================================================================
	/**
	 * Simple way to determine if the current connection is inside a 
	 * transaction or not.
	 */
	public function is_in_transaction() {
		$retval = 0;
		if($this->inTrans || $this->get_transaction_level() != 0) {
			$retval = TRUE;
		}
		return($retval);
	}//end is_in_transaction()
	//=========================================================================
	
	
	
} // end class phpDB

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