<?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;
}
}
?>