Query.inc

<?php
require_once dirname(__FILE__).'/DAL.inc';

class Query
{


    public $_lobStack  = Array();


    private $_queryid = '';

    private $_totalBindings = 0;

    private $_arrayRepresentation = array();

    private $_sqlString = '';

    private $_bindings = array();

    private $_keywords = array();

    private $_execMethod = NULL;

    private $_bindMap = array();


    public function __construct($queryid='', array $arrayRepresentation=array(), $sqlString='')
    {
        $this->_queryid = $queryid;

        if (isset($arrayRepresentation['query']) === FALSE) {
            throw new DALException('Invalid array representation');
        }

        $this->_arrayRepresentation = $arrayRepresentation;
        $this->_sqlString           = $sqlString;

    }//end __construct()


    public function getQueryType()
    {
        return $this->_arrayRepresentation['query_type'];

    }//end getQueryType()


    public function getArrayRepresentation()
    {
        return $this->_arrayRepresentation;

    }//end getArrayRepresentation()


    public function merge($bindName, Query $query)
    {
        $subQuery      = clone $query;
        $subQueryArray = $subQuery->getQueryArray();

        // Merge the passed query into the appropriate part of ours.
        // We need to determine if the merging these two queries is simple or complex.
        if ($subQuery->getQueryType() === 'with') {
            $afterPart = $subQueryArray['WITH']['AFTER'];

            // Add this query to after WITH clause.
            $subQueryArray['WITH']['AFTER'] = $this->getQueryArray();


            $this->_arrayRepresentation['query'] = $subQueryArray;
            $this->_findAndReplacePlaceHolder($bindName, $afterPart);
            $this->_arrayRepresentation['query_type'] = 'with';
            $this->_addBindingsFromQuery($subQuery);
        } else {
            $this->_addBindingsFromQuery($subQuery);
            $this->_findAndReplacePlaceHolder($bindName, $subQueryArray);
        }

        return TRUE;

    }//end merge()


    private function _addBindingsFromQuery(Query $subQuery)
    {
        $bindings = $subQuery->getBindings();
        foreach ($bindings as $binding) {
            $newName = $this->_getNextValidBindName();
            $subQuery->_findAndReplacePlaceHolder($binding['name'], $newName);
            $this->_addBindVar($newName, $binding['value'], $binding['type'], $binding['name']);
        }

    }//end _addBindingsFromQuery()


    private function _getNextValidBindName()
    {
        $this->_totalBindings++;
        $bindName = ':bind_'.$this->_totalBindings;
        return $bindName;

    }//end _getNextValidBindName()


    private function _addBindVar($name, $value, $type, $realName)
    {
        $this->_bindings[] = array(
                              'name'  => $name,
                              'value' => $value,
                              'type'  => $type,
                             );

        $this->_bindMap[] = array(
                             $realName => $name,
                            );

    }//end _addBindVar()


    private function _removeLastBindVar($count=1)
    {
        for ($i = 0; $i < $count; $i++) {
            array_pop($this->_bindings);
            array_pop($this->_bindMap);
            $this->_totalBindings--;
        }

    }//end _removeLastBindVar()


    public function replaceBindVarName($oldName, $newName)
    {
         $this->_sqlString = str_replace($oldName, $newName, $this->_sqlString);

    }//end replaceBindVarName()


    public function getQueryid()
    {
        return $this->_queryid;

    }//end getQueryid()


    public function getBindings()
    {
        return $this->_bindings;

    }//end getBindings()


    public function getQueryArray()
    {
        return $this->_arrayRepresentation['query'];

    }//end getQueryArray()


    public function getSql($convert=FALSE)
    {
        if ($convert === TRUE || $this->_sqlString === '') {
             // TODO: Should not include DALBaker here.
             require_once SQ_LIB_PATH.'/DAL/DALBaker.inc';
             $converter        = DALBaker::getConverter();
             $this->_sqlString = $converter->convertToSql($this->_arrayRepresentation['query']);
        }

        return $this->_sqlString;

    }//end getSql()


    public function prepare()
    {
        $sql = $this->getSql(TRUE);

        // Replace keywords.
        foreach ($this->_keywords as $keyword => $value) {
            $sql = str_replace('['.$keyword.']', $value, $sql);
        }

        // Update sqlString..
        $this->_sqlString = $sql;

        // Prepare PDO statement.
        $dbh   = DAL::getDb();

        if (DAL::getDbType() === 'oci') {

            $query = oci_parse($dbh, $sql);
            // Add bindings
            foreach ($this->_bindings as $binding) {
                // TODO: Handle insert/update of CLOB fields - this needs to be determined at bake time
                if ($binding['type'] === PDO::PARAM_LOB) {
                    oci_bind_by_name($query, $binding['name'], $binding['value'], -1, SQLT_CLOB);

                    // Add to LOB stack so we can release it later
                    $this->_lobStack[] = $binding['value'];
                } else {
                    oci_bind_by_name($query, $binding['name'], $binding['value'], -1);
                }
            }

        } else {

            $query = $dbh->prepare($sql);
            // Add bindings to PDO.
            foreach ($this->_bindings as $binding) {
                // $query->bindValue($binding['name'], $binding['value'], $binding['type']);
                $query->bindValue($binding['name'], $binding['value']);
            }

        }

        return $query;

    }//end prepare()


    public function setExecMethod($method)
    {
        $this->_execMethod = $method;

    }//end setExecMethod()


    public function getExecMethod()
    {
        return $this->_execMethod;

    }//end getExecMethod()


    public function bind($name, $value, $dataType=NULL)
    {
        // Depending on the data type replace, merge, split etc.
        if (($value instanceof Query) === TRUE) {
            $this->merge($name, $value);
        } else if (is_array($value) === TRUE) {
            $bindNames = array();
            foreach ($value as $val) {
                $newBindName = $this->_getNextValidBindName();
                $this->_addBindVar($newBindName, $val, $dataType, $name);
                $bindNames[] = $newBindName;
            }

            if ($this->_findAndReplacePlaceHolder($name, implode(',', $bindNames)) === FALSE) {
                $this->_removeLastBindVar(count($bindNames));
            }
        } else {
            $newBindName = $this->_getNextValidBindName();
            $this->_addBindVar($newBindName, $value, $dataType, $name);

            // Find the bind place holder in the query array and replace it.
            if ($this->_findAndReplacePlaceHolder($name, $newBindName) === FALSE) {
                $this->_removeLastBindVar();
            }

        }//end if

    }//end bind()


    private function _findAndReplacePlaceHolder($placeHolder, $value)
    {
        $arrays = Array();
        $arrays[] =& $this->_arrayRepresentation['query'];
        $found = FALSE;

        for ($i = 0; $i < count($arrays); $i++) {
            $this_array =& $arrays[$i];

            foreach(array_keys($this_array) as $key) {
                if ($this_array[$key] === $placeHolder) {
                    $this_array[$key] = $value;

                    // Set found, then break out of both loops
                    $found = TRUE;
                    break 2;
                }

                if (is_array($this_array[$key])) {
                    $arrays[] =& $this_array[$key];
                }
            }
        }

        return $found;

    }//end _findAndReplacePlaceHolder()


    private function _findAndReplaceCallBack(&$item, $key, array $data)
    {
        if ($item === $data[0]) {
            $item = $data[1];
            // We are done exit array_walk_recursive.
            throw new Exception('done');
        }

    }//end _findAndReplaceCallBack()


    public function subQueries($subQueryid, array $queries)
    {
        foreach ($queries as $query) {
            if (($query instanceof Query) === TRUE) {
                $query->getSql();
                $this->merge('HOOKID:'.$subQueryid, $query);
            } else {
                $this->bind('HOOKID:'.$subQueryid, $query, PDO::PARAM_STR);
            }
        }

    }//end subQueries()


    public function keywords(array $keywords)
    {
        $this->_keywords = array_merge($this->_keywords, $keywords);

    }//end keywords()


    public function releaseLobs()
    {
        // OCI8:
        if (DAL::getDBType() === 'oci') {
            foreach($this->_lobStack as $key => $lob) {
                $lob->close();
                oci_free_descriptor($lob);
                unset($this->_lobStack[$key]);
            }
        }

    }//end releaseLobs()



}//end class


?>