<?php   
require_once 'db.php'; // contains the DB interface

class DBQuery
{
    /**
     * Holds a reference to an object which implements the DB interface.
     */
    protected $db;

    /**
     * Set to true if this is a stored procedure.
     */
    protected $stored_procedure = false;

    /**
     * Holds a query with all the strings removed.
     */
    protected $query;

    /**
     * Used to match quotes in SQL
     */ 
    private static $QUOTE_MATCH = "/(\".*(?<!\\\)\")|('.*(?<!\\\)')/Ue";

    /**
     * An array containing all the strings, if any, of the query.
     */
    protected $query_strings = array();

    protected $result;
    
    /**
     * Class Constructor
     * @param DB $db Reference to an object which implements the DB interface.
     */
    public function __construct(DB $db)
    {
        $this->db = $db;
    }

    /**
     * Prepares the query as a stored procedure.
     * @param string $query Prepared query text
     * @return void
     */
    public function prepare($query)
    {
        $this->stored_procedure = true;
        $this->quote_store = array(); // clear the quote store
        $this->query = preg_replace(self::$QUOTE_MATCH, '$this->sql_quote_replace("\1"?"\1":\'\2\')', $query);
    }

    private function sql_quote_replace($match)
    {   
        $number = count($this->query_strings);
        $this->query_strings[] = $match;        
        return "$||$$number";
    }

    /**
     * Returns the compiled query without executing it.
     * @param mixed $params,... Query Parameters
     * @return string Compiled Query
     */
    public function compile($params)
    {
        if (! $this->stored_procedure) {
            throw new Exception("Stored procedure has not been initialized.");
        }

        /* substitute parameters */
        $params = func_get_args(); // get function arguments
        $query = preg_replace("/(?<!\\\\)\:(\d+)([SIN])/e", '$this->compile_callback($params, \1, "\2")', $this->query);

        return $this->add_strings($query); // put the strigns back into the query
    }
    
    /**
     * Re-inserts strings removed by the prepare() function.
     */
    private function add_strings($string)
    {
        $numbers = array_keys($this->query_strings);
        $count = count($numbers);

        $searches = array();

        for($x = 0; $x < $count; $x++) {
            $searches[$x] = "$||\${$numbers[$x]}";
        }

        return str_replace($searches, $this->query_strings, $string);
    }

    /**
     * Executed each time a placeholder is substitued in the stored procedure.
     */
    protected function compile_callback($params, $index, $type)
    {        
        --$index;

        /* throw an exception */
        if (! isset($params[$index])) {
            throw new Exception("Required number of arguments not sent to stored procedure.");
        }
        
        /* you may want to add additional types such as dates and times here */
        switch ($type) {
            case 'S':
                return '"' . $this->db->escape_string($params[$index]) . '"';
                break;
            case 'I':
                return (int) $params[$index];
                break;
            case 'N':
                return (float) $params[$index];
            default:
                throw new Exception("Unrecognised data type '$type' specified in stored procedure.");
        }
    }

    /**
     * Executes the current Query
     * 
     * Executes the current query replacing any place holders with the supplied
     * parameters.
     *
     * @param mixed $queryParams,... Query parameter
     * @return resource A reference to the resource representing the executed query.
     */
    public function execute($queryParams = '')
    {
        //example: SELECT * FROM table WHERE name=:1S AND type=:2I AND level=:3N
        $args = func_get_args();

        if ($this->stored_procedure) {
            /* call the compile function to get the query */
            $query = call_user_func_array(array($this, 'compile'), $args);
        } else {
            /* a stored procedure was not initialised, so executre this as a standard query */
            $query = $queryParams;
        }
            
        $this->result = $this->db->query($query);

        return $this->result;
    }
}
?>