Location: PHPKode > projects > OpenNitro > trunk/Nitro/Transaction.inc.php
<?php
//
// +---------------------------------------------------------------------------+
// | Nitro :: Transaction Class                                                |
// +---------------------------------------------------------------------------+
// | Copyright (c) 2006 June Systems BV                                        |
// +---------------------------------------------------------------------------+
// | This library is free software; you can redistribute it and/or modify it   |
// | under the terms of the GNU Lesser General Public License as published by  |
// | the Free Software Foundation; either version 2.1 of the License, or (at   |
// | your option) any later version.                                           |
// |                                                                           |
// | This library is distributed in the hope that it will be useful, but       |
// | WITHOUT ANY WARRANTY; without even the implied warranty of                |
// | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Lesser   |
// | General Public License for more details.                                  |
// |                                                                           |
// | You should have received a copy of the GNU Lesser General Public License  |
// | along with this library; if not, write to the Free Software Foundation,   |
// | Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA            |
// +---------------------------------------------------------------------------+
// | Authors: Siggi Oskarsson <hide@address.com>                          |
// | Authors: Jesper Avôt <hide@address.com>                             |
// +---------------------------------------------------------------------------+
//
// $Id: Transaction.inc.php 229 2008-04-17 09:20:31Z oli $
//

/**
 * This file contains the Transaction class of Nitro
 *
 * @package			Nitro
 * @subpackage	DB
 * @author 			Siggi Oskarsson
 * @author			Jesper Avôt
 * @version 		$Revision:$
 * @copyright		2006 June Systems BV
 */

/**
 * Transaction class
 *
 * This class will automate some of the tasks of inserting and updating data into a 
 * database used by the Nitro DB class. The transaction class will verify the data
 * being added and automatically, by checking the table definition (primary keys etc.),
 * judge whether the data is to be inserted or updated. If the fields CreatedByUserID, CreatedOn,
 * EditedByUserID and EditedOn exist in the table, they will be automatically update accordingly.
 * If the Log flag is set, the transaction class will log all query's done by the transaction
 * to the Transaction table in the Nitro database.
 * 
 * The addData function returns if succeed a random code which enables you to retrieve the options 
 * after the Commit, if the functions fails, it returns false.
 *
 * Tables with no primary keys should not be handled by the transaction class. At this moment
 * that is not supported.
 *
 * <CODE>
 * $Log = TRUE;
 * $Transaction = new Transaction($DB, $UserID, $Log);
 * if ($Transaction->Error) {
 *   echo "ERROR: Failed creating a new transaction";
 * } else {
 *   $ID = $Transaction->addData($Table1, $Fields1);
 *   $ID2 = $Transaction->addData($Table2, $Fields2);
 *   $ID3 = $Transaction->addData($Table3, $Fields3);
 *   $Transaction->Commit();
 *
 *   echo $Transaction->Transactions[$ID]->AffectedRows;
 * }
 * </CODE>
 */
class Transaction {
	/**
	 * @var		boolean		$Log	Use Logging? TRUE or FALSE.
	 */
	var $Log = FALSE;
	
	/**
	 * @var		object	$DB		Nitro database connection object
	 */
	var $DB;
	
	/**
	 * @var		int		$UserID		User ID if user doing transaction. This is used for logging and special fields (CreatedByUserID, CreatedOn, EditedByUserID and EditedOn)
	 */
	var $UserID;
	
	/**
	 * @var		string	$Error	String with Error message.
	 */
	var $Error;

	/**
	 * @var		array		$Transactions		Array of Transaction objects to execute in the transaction.
	 */
	var $Transactions;
	
	var $TransactionID;
	
	/**
	 * Transaction constructor function
	 *
	 * @param	string	$DSN	DSN to use for connection (database_type://username:[password]@host/database_name)
	 * @param	boolean	$NewLink	Force new link or allow to reuse link if host and login the same
	 * @param	boolean	$AutoConnect	Automatically connect to the database
	 * @access public
	 */
	function Transaction($DB, $UserID, $Log = FALSE)
	{
		DebugGroup(__CLASS__, __FUNCTION__, "Initializing Transaction", __FILE__, __LINE__, DEBUG_SQL_OK);
		
		if (DB::isDBCon($DB)) {
			$this->DB 		= $DB;
			$this->UserID = $UserID;
			
			if ($Log !== FALSE) {
				$this->Log = TRUE;
				$Result = $this->DB->query("
					INSERT INTO `Transaction`
					(Date, UserID, Module, Action, Description)
					VALUES
					(CURRENT_TIMESTAMP, " . (int)$this->UserID . ", '', '', '')
				");
				$this->TransactionID = $Result->InsertID();
			}
		} else {
			$this->Error = Language("DB connection is not a valid Nitro database connection");
		}
		
		DebugCloseGroup(DEBUG_SQL_OK);
	}
	
	/**
	 * Add data to transaction
	 *
	 * This function is used to add data (row inserts, updates etc.) to the transaction.
	 * The Mode parameter can be used to force the transaction to do an INSERT OR UPDATE
	 * when the transaction would have done differently. The DELETE mode will delete all rows
	 * that correspond to the data in the fields given.
	 *
	 * @param	string	$Table	Table name of table to insert/update data
	 * @param	array	$Fields	Associative array of fieldname => value to insert/update data
	 * @param	string	$Mode	Forced mode of transaction (UPDATE, INSERT OR DELETE)
	 * @access	public
	 * @return mixed code or FALSE on error
	 */
	function addData($Table, $Fields, $Where = FALSE, $Mode = FALSE)
	{
		DebugGroup(__CLASS__, __FUNCTION__, "Adding transaction for table $Table", __FILE__, __LINE__, DEBUG_SQL_OK);

		$RV = FALSE;
		$transCom = new TransactionCommand($this->DB, $this->UserID, $Table, $Fields, $Where, $this->Log, $this->TransactionID);

		if ($transCom->Error) {
			$this->Error = Language('Failed creating transaction command: {%Error%}', Array(
				'Error' => $transCom->Error
			));
			
			Debug(__CLASS__, __FUNCTION__, $this->Error, __FILE__, __LINE__, DEBUG_SQL_ERR|DEBUG_APPL_ERR);

			$RV = FALSE;
		} else {
			if ($Mode !== FALSE) $transCom->ForceMode($Mode);
			
			$this->Transactions[] = $transCom;
			$RV = count($this->Transactions) - 1;
		}

		DebugCloseGroup(DEBUG_SQL_OK);
		
		return $RV;
	}
	
	/**
	 * getInsertID function
	 *
	 * @param	boolean	$ID The ID of the transaction.
	 * @return integer The InsertID of the requested transaction ID.
	 * @access	public
	 */
	function getInsertID($ID)
	{
		return $this->Transactions[$ID]->InsertID;
	}
	
	/**
	 * getAffectedRows function
	 *
	 * @param	boolean	$ID The ID of the transaction.
	 * @return integer The number of AffectedRows of the requested transaction ID.
	 * @access	public
	 */
	function getAffectedRows($ID)
	{
		return $this->Transactions[$ID]->AffectedRows;
	}
	
	/**
	 * Class commit function
	 *
	 * Run this function and all the added (see addData function) query's will be executed.
	 *
	 * @return	boolean true|false
	 * @access	public
	 */
	function commit()
	{
		DebugGroup(__CLASS__, __FUNCTION__, "Running all transactions", __FILE__, __LINE__, DEBUG_SQL_OK);
		
		$RV = TRUE;
		
		$this->DB->TransactionStart(); //Not used yet!
		
		if (is_array($this->Transactions)) {
			foreach($this->Transactions AS $key => $transaction) {
				if (!($RV = $this->Transactions[$key]->commit())) {
					$this->Error = Language('Failed committing transaction').': '.$this->Transactions[$key]->Error;
				
					Debug(__CLASS__, __FUNCTION__, $this->Error, __FILE__, __LINE__, DEBUG_SQL_ERR|DEBUG_APPL_ERR);
					
					$this->DB->TransactionRollback(); //Not used yet!
					
					break; //Stop further transactions
				}
			}
		}
		
		$this->DB->TransactionCommit(); //Not used yet!
		
		DebugCloseGroup(DEBUG_SQL_OK);
		
		return $RV;
	}
}

class TransactionCommand {
	/**
	 * @var	object	Nitro database connection object
	 */
	var $DB;
	
	/**
	 * @var	int	User ID if user doing transaction. This is used for logging and special fields (CreatedByUserID, CreatedOn, EditedByUserID and EditedOn)
	 */
	var $UserID;
	
	/**
	 * @var	string	Error string
	 */
	var $Error;

	/**
	 * @var	string	Table name of transaction command
	 */
	var $Table;
	
	/**
	 * @var	array	Associative array of field names and values to be added/changed
	 */
	var $Fields = Array();
	
	/**
	 * @var	array	Associative array of field names and values to be used in the where clause (of an update)
	 */
	var $Where = Array();
	
	/**
	 * @var	int	Affected rows of query
	 */
	var $AffectedRows;
	
	/**
	 * @var	int	Inserted Id of new row or original id of updated row
	 */
	var $InsertID;
	
	var $QueryMode;
	var $ForceMode;
	var $QueryWhere = FALSE;
	
	var $TransactionID;
	var $TransactionCommandID;
	var $Log = FALSE;
	var $PrimaryKeys;
	
	/**
	 * TransactionCommand constructor
	 */
	function TransactionCommand($DB, $UserID, $Table, $Fields, $Where, $Log, $TransactionID)
	{
		DebugGroup(__CLASS__, __FUNCTION__, "Initializing Transaction", __FILE__, __LINE__, DEBUG_SQL_OK);
		
		if ($Log !== FALSE) {
			$this->Log = TRUE;
			$this->TransactionID = $TransactionID;
			$Result = $DB->query("
						INSERT INTO `TransactionCommand` 
						(TransactionID, Table, QueryMode, Query)
						VALUES
						(" . (int)$this->TransactionID . ", '" . $DB->escapeString($Table) . "', '', '')
						");
			$this->TransactionCommandID = $Result->InsertID();
		}
		
		if (DB::isDBCon($DB)) {
			$this->DB = $DB;
			$this->UserID = $UserID;
			$tables = $this->DB->listTables();
			
			if (in_array($Table, $tables)) {
				$this->Table = $Table;
				$tableFields = $this->DB->listFields($Table);
				$this->PrimaryKeys = $this->getPrimaryKey($tableFields);
				
				foreach($Fields AS $field => $value) {
					// check if all fields set exist in table and if value set corresponds to data type of field
					if (!array_key_exists($field, $tableFields)) {
						$this->Error = "Field ($field) does not exist in table (".$Table.", ".$this->DB->Database[0].")";
						unset($this->Fields);
						break;
					} else {
						$type = $tableFields[$field]['Type'];
						// TODO add value check
						$this->Fields[$field] = $value;
					}
				}

				if (is_array($Where)) {
					foreach($Where AS $field => $value) {
						// check if all fields set exist in table and if value set corresponds to data type of field
						if (!array_key_exists($field, $tableFields)) {
							$this->Error = "Field does not exist in table (".$Table.", ".$this->DB->Database.")";
							unset($this->Where);
							unset($this->Fields);
							break;
						} else {
							$type = $tableFields[$field]['Type'];
							// TODO add value check
							$this->Where[$field] = $value;
						}
					}
				}
			} else {
				$this->Error = "Table does not exist in database (".$this->DB->Database.")";
			}
		} else {
			$this->Error = "DB connection is not a valid Nitro database connection";
		}
		DebugCloseGroup(DEBUG_SQL_OK);
	}
	
	/**
	 * Commit the data to database
	 *
	 * This function will create a query to send to the database and send it to the database.
	 */
	function commit()
	{
		DebugGroup(__CLASS__, __FUNCTION__, "Committing data to database", __FILE__, __LINE__, DEBUG_SQL_OK);

		$Query = $this->createQuery();
		if ($this->Log !== FALSE) {
			$this->logTransaction($Query);
		}
		if ($Query !== FALSE) {
			$Result = $this->DB->query($Query);
			
			if (DB::isError($Result)) {
				$RV = FALSE;
				$this->Error = "Failed running query: ".$Result->Error;
			} else {				
				if (is_object($Result)) {
					$this->AffectedRows = $Result->affectedRows();
					$this->InsertID = $Result->InsertID();
				}
				
				$RV = TRUE;
			}
		} else {
			// Error message given elsewhere in function
			$RV = FALSE;
		}

		DebugCloseGroup(DEBUG_SQL_OK);
		return $RV;
	}
	
	/**
	 * Prepare Log Transaction
	 *
	 * When an UPDATE or DELETE Transaction is comitted, then we log the current query data first, before we UPDATE or DELETE 
	 * the query data.
	 */
	function logTransaction($Query = FALSE)
	{
		DebugGroup(__CLASS__, __FUNCTION__, "prepareLogTransaction", __FILE__, __LINE__, DEBUG_SQL_OK);
		
		$theMode = (strlen($this->ForceMode) ? $this->ForceMode : $this->QueryMode);
		$Result = $this->DB->query("
			UPDATE `TransactionCommand` 
			SET `QueryMode` = '" . $this->DB->escapeString($theMode) . "', 
					`Query` = '" . $this->DB->escapeString($Query) . "' 
			WHERE `TransactionCommandID` = " . (int)$this->TransactionCommandID);
		
		switch ($theMode) {
			case "UPDATE":
			case "DELETE":
				$theQuery = 'SELECT * FROM `' . $this->Table . '` ' . ($this->QueryWhere !== FALSE && strlen($this->QueryWhere) ? $this->QueryWhere : '');
				$Result = $this->DB->query($theQuery);
				
				if ($Result->numRows()) {
					if ($Result->numRows() > 1) {
						while ($Data = $Result->fetchArray()) {
							$PK = Array();
							$TableFields = $this->DB->listFields($this->Table);
							
							foreach ($this->getPrimaryKey($TableFields) AS $Key) {
								if ($Data[$Key]) {
									$PK[] = $Data[$Key];
								}
							}
							
							$this->DB->query("
								INSERT INTO `TransactionCommandData` 
								(TransactionCommandID, OldData, PrimaryKey)
								VALUES
								(" . (int)$this->TransactionCommandID . ", '" . $this->DB->escapeString(serialize($Data)) . "', '" . $this->DB->escapeString(implode(',', $PK)) . "')
							");
						}
					} else {
						$PK = Array();
						$TableFields = $this->DB->listFields($this->Table);
						$Data = $Result->fetchArray();
						
						foreach ($this->getPrimaryKey($TableFields) AS $Key) {
							if ($Data[$Key]) {
								$PK[] = $Data[$Key];
							}
						}
						
						$this->DB->query("
							INSERT INTO `TransactionCommandData` 
							(TransactionCommandID, OldData, PrimaryKey)
							VALUES
							(" . (int)$this->TransactionCommandID . ", '" . $this->DB->escapeString(serialize($Data)) . "', '" . $this->DB->escapeString(implode(',', $PK)) . "')
						");
					}
				}
				break;
		}
		
		DebugCloseGroup(DEBUG_SQL_OK);
	}

	/**
	 * Create query for commit
	 *
	 * This function will create the query used in the commit
	 */
	function createQuery()
	{
		// check whether primary key was set, if yes query DB for record
		$TableFields = $this->DB->listFields($this->Table);
		$Fields = $this->Fields;
		
		if ($this->ForceMode == 'DELETE') {
			// delete data, this is done differently than insert or update, now we check that
			// all the fields given really exist. Otherwise we do not delete any data. This is
			// done to prevent accidental deletion of large amount of data with a spelling mistake
			// in the fieldnames (more data will be deleted than expected).
			$deleteFields = Array();
			foreach($Fields AS $field => $value) {
				if (array_key_exists($field, $TableFields)) {
					$deleteFields[$field] = $value;
				} else {
					$unknownField = $field;
					$deleteFields = Array();
					break; // break from foreach
				}
			}
			
			if (count($deleteFields)) {
				$Query = "
					DELETE FROM $this->Table
					WHERE 1
					";
				$this->QueryWhere = "WHERE 1";
				foreach($deleteFields AS $field => $value) {
					$Query.= " AND `" . $field . "` = '".$this->DB->escapeString($value)."'";
					$this->QueryWhere.= " AND `" . $field . "` = '".$this->DB->escapeString($value)."'";
				}
			} else {
				$this->Error = "Unknown field ($unknownField) for where clause of delete";
				$Query = FALSE;
			}
		} else {
			$PrimaryKeys = $this->PrimaryKeys;
			$WhereClause = Array();
			if (count($this->Where)) {
				// where clause overrides usage of primary keys
				// Query mode with where statement is always an update
				$WhereClause = $this->Where;
				$this->QueryMode = "UPDATE";
			} elseif (count($PrimaryKeys) === 1) {
				// single primary key exists
				if ($Fields[$PrimaryKeys[0]]) {
					// Field set, check whether is already exists in database
					$SubQuery = "
						SELECT ".$PrimaryKeys[0]."
						FROM $this->Table
						WHERE `".$PrimaryKeys[0]."` = '".$this->DB->escapeString($Fields[$PrimaryKeys[0]])."'
						";
					$exists = ($this->DB->getOne($SubQuery) !== FALSE ? TRUE : FALSE);
				} else {
					$exists = FALSE;
				}
				
				if ($exists) {
					$WhereClause[$PrimaryKeys[0]] = $Fields[$PrimaryKeys[0]];
					$this->QueryMode = "UPDATE";
				} else {
					$WhereClause = Array();
					$this->QueryMode = "INSERT INTO";
				}
			} elseif (count($PrimaryKeys)) {
				// multiple primary key exists
				// In a multiple primary key all fields are set to the default value if not set
				$oneSet = FALSE;
				foreach($PrimaryKeys AS $field) {
					if (isset($Fields[$field])) {
						$oneSet = TRUE;
					} else {
						// field was not set, but is a member of a multiple primary keu
						// we therefore need to set the default value in the field
						$Fields[$field] = $TableFields[$field]['Default'];
					}
				}
				
				if ($oneSet) {
					// one primary key field has a value set, we must now create a where clause and
					// look up the value to see if a row already exists
					foreach($PrimaryKeys AS $field) {
						$WhereClause[$field] = $Fields[$field];
					}

					$SubQuery = "
						SELECT ".$PrimaryKeys[0]."
						FROM $this->Table
						WHERE 1
					";
					foreach($WhereClause AS $field => $value) {
						$SubQuery.= " AND `".$field."` = '".$this->DB->escapeString($value)."'";
					}
					$exists = ($this->DB->getOne($SubQuery) !== FALSE ? TRUE : FALSE);
				} else {
					$exists = FALSE;
				}

				if ($exists) {
					// WhereClause stays the same
					$this->QueryMode = "UPDATE";
				} else {
					$WhereClause = Array();
					$this->QueryMode = "INSERT INTO";
				}
			} else {
				// No primary key, just insert the data
				// Tables with no primary keys should not be handled in the Transaction class
				$this->QueryMode = "INSERT INTO";
			}
	
			// TODO: FIX $Query not to use "INSERT INTO $table SET ..."
			$queryLines = Array();
			foreach($TableFields AS $field => $settings) {
				$line = $this->_constructFieldQueryLine($field, $settings, $Fields, $this->QueryMode);
				if ($line !== FALSE) $queryLines[] = $line;
			}
			if (count($queryLines)) {
				$Query = $this->QueryMode." ".$this->Table." SET \n";
				$Query.= implode(",\n", $queryLines)."\n";
	
				if ($this->QueryMode === "UPDATE" && count($WhereClause)) {
					// Update query, set WHERE clause
					$n = 0;
					$this->QueryWhere = "";
					foreach($WhereClause AS $key => $value) {
						$Query.= ($n > 0 ? " AND" : "WHERE")." `".$key."` = '".$this->DB->escapeString($value)."'\n";
						$this->QueryWhere = ($n > 0 ? " AND" : "WHERE")." `".$key."` = '".$this->DB->escapeString($value)."'\n";
						$n++;
					}
				}
			} else {
				$Query = FALSE;
			}
		}
		//echo $Query; exit;
		return $Query;
	}

	/**
	 * Force the transaction command to update or insert
	 *
	 * This function sets the ForceMode flag of the transaction command. If set
	 * the query that will be send to the database server will be an explicit update
	 * or insert without properly checking for existing rows.
	 * This function should only be used in very exceptional cases. If the table being
	 * updated / inserted does not have a primary key and the expected format, the transaction
	 * class should not be used.
	 */	
	function ForceMode($Mode)
	{
		if (eregi('^U', $Mode)) {
			$this->ForceMode = 'UPDATE';
		} elseif (eregi('^I', $Mode)) {
			$this->ForceMode = 'INSERT INTO';
		} elseif (eregi('^D', $Mode)) {
			$this->ForceMode = 'DELETE';
		}
	}
	
	/**
	 * Get primary key from list of fields
	 *
	 * Primary key can be made up of multiple fields.
	 *
	 * @param	array	$Fields	All fields to look for primary key
	 * @return	array	$Primary	Array of all fields in the primary key
	 */
	function getPrimaryKey($Fields)
	{
		$primaryKey = Array();
		foreach($Fields AS $field => $setting) {
			if ($setting['Key'] === 'PRI') {
				// construct primary key
				$primaryKey[] = $field;
			}
		}
		return $primaryKey;
	}

	function _constructFieldQueryLine($field, $settings, $Fields, $QueryMode)
	{
		// check special fields
		switch($field) {
			case "CreatedBy":
			case "CreatedByUserID":
				if ($QueryMode === "INSERT INTO" && !array_key_exists($field, $Fields)) {
					// only set on update and if not already set
					$Fields[$field] = (int)$this->UserID;
				}
				break;
			case "CreateDate":
			case "CreatedOn":
				if ($QueryMode === "INSERT INTO" && !array_key_exists($field, $Fields)) {
					// only set on update and if not already set
					$Fields[$field] = $this->DB->getDateTimeFunction();
				} else {
					$Fields[$field] = "'".$this->DB->escapeString($Fields[$field])."'";
				}
				break;
			case "EditedBy":
			case "EditedByUserID":
				if ($QueryMode === "UPDATE" && !array_key_exists($field, $Fields)) {
					// only set on update and if not already set
					$Fields[$field] = (int)$this->UserID;
				}
				break;
			case "EditDate":
			case "EditedOn":
				if ($QueryMode === "UPDATE" && !array_key_exists($field, $Fields)) {
					// only set on update and if not already set
					$Fields[$field] = $this->DB->getDateTimeFunction();
				} else {
					$Fields[$field] = "'".$this->DB->escapeString($Fields[$field])."'";
				}
				break;
			default:
				// all fields are set on INSERT, only given fields on an UPDATE
				if ($QueryMode === 'INSERT INTO' OR array_key_exists($field, $Fields)) {
					if (isset($Fields[$field])) {
						if (ereg('int\(', $settings['Type'])) {
							$Fields[$field] = (int)$Fields[$field];
						} else if (is_array($Fields[$field])) {
							$Fields[$field] = $this->DB->getFunction(key($Fields[$field]), $Fields[$field][key($Fields[$field])]);
						} else if ($Fields[$field] == "EMPTY:()") {
							unset($Fields[$field]);
						} else if ($Fields[$field] == "NULL:()") {
							$Fields[$field] = "NULL";
						} else {
							$Fields[$field] = "'".$this->DB->escapeString($Fields[$field])."'";
						}
					} else {
						$Fields[$field] = ($settings['Null'] == 'YES' ? "NULL" : "'".$settings['Default']."'");
					}
				}
				break;
		}

		if (array_key_exists($field, $Fields)) {
			$RV = " `".$field."` = ".$Fields[$field];
		} else {
			$RV = FALSE;
		}

		return $RV;
	}
}
?>
Return current item: OpenNitro