Location: PHPKode > scripts > phpPhrasebook > phpphrasebook/PhrasebookSql.php
<?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;



    }

    /**#@-*/



}



?>
Return current item: phpPhrasebook