<?php
/**
* phpPhrasebookSql: PHP Implementation of the Phrasebook pattern for
* SQL statements
*
* This class implements the phrasebook pattern for SQL statements, as
* documented by Yonat Sharon and Rani Pinchuk in their paper, The
* Phrasebook Pattern available at
* {@link http://jerry.cs.uiuc.edu/~plop/plop2k/proceedings/Pinchuk/Pinchuk.pdf}.
*
* @package phpPhrasebook
* @author Andrew Barilla <hide@address.com>
* @version 0.1
* @link http://phpphrasebook.sourceforge.net
* @copyright GNU-GPL
*
*/
/**
* Need to make sure we have the Phrasebook object
*/
require_once("Phrasebook.php");
/**
* Implements the Phrasebook pattern for SQL statements
*
* This class implements the phrasebook pattern, as documented by Yonat Sharon
* and Rani Pinchuk in their paper, The Phrasebook Pattern available at
* http://jerry.cs.uiuc.edu/~plop/plop2k/proceedings/Pinchuk/Pinchuk.pdf. It
* uses similar calls and XML document format to Rani Pinchuk's
* Class::Phrasebook module for Perl.
*
* @package phpPhrasebook
* @author Andrew Barilla <hide@address.com>
* @see phpPhrasebook
*
*/
class PhrasebookSql extends Phrasebook {
/**#@+
* @access private
*/
var $boUseIsNull;
var $boDoMysqlEscapeString;
var $stEscapedQuote;
var $astValues;
/**#@-*/
/**
* Constructor for PhrasebookSql class.
*
* @param string a_stFileName
* The filename of the xml phrasebook document to read.
*/
function PhrasebookSql ($a_stFileName) {
$this->Phrasebook($a_stFileName);
$this->boUseIsNull = false;
$this->boDoMysqlEscapeString = false;
$this->stEscapedQuote = "''";
}
/**
* Returns the phrase from the dictionary and will substitute
* variables when appropriate.
*
* One major difference between PhrasebookSql and the standard Phrasebook
* is that the PhrasebookSql class will remove lines from the SET part of
* the UPDATE method if those variables are not used. This results in the
* ability to define only one update statement but only updating some of
* the fields.
*
* For example if the following statement is defined in the dictionary:
*
* <pre>
* UPDATE my_table
* SET field1 = $field1,
* field2 = '$field2',
* field3 = $field3
* WHERE field4 = $field4
* </pre>
*
* And you only pass the array ("field1"=>"123", "field4"=>"456"), the
* following statement will be produced:
*
* <pre>
* UPDATE my_table
* SET field1 = 123
* WHERE field4 = 456
* </pre>
*
*
* @param string a_stName
* The key of the phrase to return
* @param array a_astValues
* Array of strings to replace variables in the phrase with
* @return string
* The phrase with variables replaced if appropriate
*
*/
function getPhrase ($a_stName, $a_astValues = array()) {
$this->astValues = $a_astValues;
if (!array_key_exists($a_stName, $this->astPhrases))
return "";
$stPhrase = $this->astPhrases[$a_stName];
if (preg_match('/^\s*update/i', $stPhrase)) {
$stBeforeSet = "";
$stPairs = "";
$stAfterWhere = "";
$astMatches = array();
$iWhereLoc = 0;
$iSetLoc = 0;
$astFoundLines = array();
if (preg_match('/(\sset\s)/i', $stPhrase, $astMatches)) {
$iSetLoc = strpos($stPhrase, $astMatches[1]) +
strlen($astMatches[1]);
$stBeforeSet = substr($stPhrase, 0, $iSetLoc);
if (preg_match('/(\swhere\s)/i', $stPhrase, $astMatches)) {
$iWhereLoc = strpos($stPhrase, $astMatches[1]);
$stAfterWhere = substr($stPhrase, $iWhereLoc);
}
if ($iWhereLoc != 0) {
$stPairs = substr($stPhrase, $iSetLoc,
$iWhereLoc - $iSetLoc);
}
else {
$stPairs = substr($stPhrase, $iSetLoc);
}
if ($stPairs != "") {
$astPairs = explode("\n", $stPairs);
foreach ($astPairs as $stValue) {
if ($this->_isVariableDefinedInThisLine($stValue,
$a_astValues))
$astFoundLines[] = $stValue;
}
$astPairs = trim(implode("", $astFoundLines));
if (substr($astPairs, strlen($astPairs) - 1) == ",")
$astPairs = substr($astPairs, 0, -1);
$stPhrase = $stBeforeSet . $astPairs . $stAfterWhere;
}
}
}
$stPhrase = preg_replace_callback('/(\')(\$)([a-zA-Z0-9_]+)(\')/',
array(&$this, '_escapeString'),
$stPhrase);
$stPhrase = preg_replace('/(\$)([a-zA-Z0-9_]+)/ie',
"\$a_astValues['$2']",
$stPhrase);
$stPhrase = preg_replace('/(\$\()([a-zA-Z0-9_]+)\)/ie',
"\$a_astValues['$2']",
$stPhrase);
if ($this->boRemoveNewLines)
$stPhrase = str_replace('\n', '', $stPhrase);
return $stPhrase;
}
/**
* Returns the current value of the UseIsNull setting
*
* @return boolean
* The value of the UseIsNull setting
*
*/
function getUseIsNull () {
return $this->boUseIsNull;
}
/**
* Registers the value of the UseIsNull setting. When true,
* getPhrase will change the text '= NULL' with 'is NULL'.
* When false, no changes to the phrase will occur.
*
* @param boolean a_boUseIsNull
* The value of the new UseIsNull setting
*
*/
function setUseIsNull ($a_boUseIsNull) {
$this->boUseIsNull = $a_boUseIsNull;
}
/**
* Returns the current value of the DoMysqlEscapeString setting
*
* @return boolean
* The value of the DoMysqlEscapeString setting
* @see setDoMysqlEscapeString
*
*/
function getDoMysqlEscapeString () {
return $this->boDoMysqlEscapeString;
}
/**
* Registers the value of the DoMysqlEscapeString. When true,
* getPhrase will replace variables in the phrase that are surrounded
* by quotes to be run through the mysql_escape_string function.
* When false, the value of EscapedQuote will be used instead.
*
* @param boolean a_boDoMysqlEscapeString
* The value of the new DoMysqlEscapeString setting
*
*/
function setDoMysqlEscapeString ($a_boDoMysqlEscapeString) {
$this->boDoMysqlEscapeString = $a_boDoMysqlEscapeString;
}
/**
* Returns the current value of the EscapedQuote setting
*
* @return string
* The value of the EscapedQuote setting
* @see setEscapedQuote
*
*/
function getEscapedQuote () {
return $this->stEscapedQuote;
}
/**
* Registers the value of the string to use to replace single quotes (')
* with when variables are surrounded by quotes in the phrase. This
* defaults to two single quotes ('')
*
* @param string a_stEscapedQuote
* The value of the quotes to use
*
*/
function setEscapedQuote ($a_stEscapedQuote) {
$this->stEscapedQuote = $a_stEscapedQuote;
}
/**#@+
* @access private
*/
function _escapeString ($a_astMatches) {
$stValue = $this->astValues[$a_astMatches[3]];
if (!$this->boDoMysqlEscapeString)
return $a_astMatches[1].
str_replace("'", $this->stEscapedQuote, $stValue).
$a_astMatches[4];
else
return $a_astMatches[1].
mysql_escape_string($stValue).
$a_astMatches[4];
}
function _escapeStrings ($a_stQuote1, $a_stValue, $a_stQuote2) {
if (!$this->boDoMysqlEscapeString)
return $a_stQuote1.
str_replace("'", $this->stEscapedQuote, $a_stValue).
$a_stQuote2;
else
return $a_stQuote1.mysql_escape_string($a_stValue).$a_stQuote2;
}
function _isVariableDefinedInThisLine ($a_stLine, $a_astValues) {
foreach ($a_astValues as $stKey => $stValue) {
if (strpos($a_stLine, "\$$stKey"))
return true;
if (strpos($a_stLine, "\$($stKey)"))
return true;
}
return false;
}
/**#@-*/
}
?>