<?
/*
mysql_sp 1.1
Copyright: Left
---------------------------------------------------------------------------------
Version:        1.1
Last changed:   01 December 2004
---------------------------------------------------------------------------------
Authors:        Alexander Minkovsky (hide@address.com)
---------------------------------------------------------------------------------
License:        Choose the more appropriated for You - I don't care.
---------------------------------------------------------------------------------
Description:
Class emulates stored procedure execution.
SP is in fact an external file with placeholders for parameters.
Each SP statement is execited subsequently. The result of the execute method is an array
containing all statements results.
An array entry can be an associative array if the statement was SELECT type
or INT -> affected rows number. Also accessible through "results" property.
For example: mysql_sp->results[count(mysql_sp->results)-1] will return the result of the
last executed statement (usually the one which returns the results as in the example.)
---------------------------------------------------------------------------------
Placeholder Syntax: <param name="..."/>
Example:
    SET @DetailID = <param name="DetailID"/>;
    SET @myDate = '<param name="myDate"/>';
Default value: NULL
---------------------------------------------------------------------------------
Example usage: see example.php
---------------------------------------------------------------------------------
TODO:
    - Find out the regular expression which will split statements in a single step.
    - Fix bug only allowing a single parameter per line of SQL code
---------------------------------------------------------------------------------
Note:
    Rewrite or Override the onError method if needed. It's the error handler used from everywhere and takes 2 parameters:
    err_code and err_text. By default it just prints out a message about the error.
*/

define("ERR_OPEN_SP_FILE","ERR_OPEN_SP_FILE");
define("ERR_NO_MYSQL_SUPPORT","ERR_NO_MYSQL_SUPPORT");
define("ERR_MYSQL_CONNECT","ERR_MYSQL_CONNECT");
define("ERR_MYSQL_USE_DB","ERR_MYSQL_USE_DB");
define("ERR_MYSQL_QUERY","ERR_MYSQL_QUERY");
define("ERR_UNTERMINATED_STRING","ERR_UNTERMINATED_STRING");

Class mysql_sp{
  //Database connection
  var $db_host = "";
  var $db_port = "";
  var $db_name = "";
  var $db_user = "";
  var $db_pass = "";
  //Stored procedure details
  var $sp_file = "";
  var $sp_params = NULL;
  //Results
  var $results = NULL;
  //SQL [PRIVATE]
  var $sql = "";

  //Constructor
  function mysql_sp($db_host="localhost",$db_port="3306",$db_name="",$db_user="root",$db_pass="",$sp_file="",$sp_params=array()){
    $this->db_host = $db_host;
    $this->db_port = $db_port;
    $this->db_name = $db_name;
    $this->db_user = $db_user;
    $this->db_pass = $db_pass;
    $this->sp_file = $sp_file;
    $this->sp_params = $sp_params;
    $this->results = array();
  }

  //Error handler
  function onError($err_code,$err_text){
    print($err_code . " : " . $err_text . "<br>\n");
  }

  //Execute stored procedure
  function execute(){
    $this->results = array();
    $this->sql = @file_get_contents($this->sp_file)
        or exit($this->onError(ERR_OPEN_SP_FILE,"Error openning file: " . $this->sp_file));
    $this->_execSQL();
    return $this->results;
  }

  //Execute stored procedure from string instead of file
  function executeSQL($sql){
    $this->sql = $sql;
    $this->_execSQL();
    return $this->results;
  }

//Private methods

  //Execute SQL
  function _execSQL(){
    $this->_setParams();
    $statements = $this->_split_sql();
    $this->_exec($statements);
  }

  //Replace parameters in the SQL
  function _setParams(){
    $match = preg_match_all("/\<param.*(name=\"([^\"]*)\")[^\>]\>/i", $this->sql,$matches);
    if($match > 0){
      for($i=0;$i<count($matches[0]);$i++){
        $placeholder = $matches[0][$i];
        $name = $matches[2][$i];
        $this->sql = str_replace($placeholder, ((isset($this->sp_params[$name]))?$this->sp_params[$name]:"NULL"), $this->sql);
      }
    }
  }

  //Split SQL on separate statements
  function _split_sql(){
    //Strip comments
    $this->sql = preg_replace("/\#.*$/m","",$this->sql);
    $this->sql = preg_replace("/--\s.*$/m","",$this->sql);
    $this->sql = preg_replace("/\/\*[\d\D]*?\*\//","",$this->sql);

    $arr = explode(";",$this->sql);
    $idx = 0;
    while($idx < count($arr)){
      if($this->_isUnterminatedString($arr[$idx])){
        $arr[$idx] .= ";" . $arr[$idx+1];
        array_splice($arr,$idx+1,1);
      }
      else{
        $idx++;
      }
    }
    return $arr;
  }

  //Execute statements sequence
  function _exec($statements){
    $conn = $this->_mysql_conect();
    foreach($statements as $statement){
      if(trim($statement) != ""){
        $this->_exec_statement($conn,$statement);
      }
    }
    mysql_close($conn);
  }

  //Connect to mysql
  function _mysql_conect(){
    //Check if mysql is available
    function_exists('mysql_connect')
        or  exit($this->onError(ERR_NO_MYSQL_SUPPORT,"FATAL ERROR: MySQL support not avaiable.  Please check your configuration."));
    //Try to connect
    $conn = @mysql_connect( $this->db_host . ":" . $this->db_port, $this->db_user, $this->db_pass)
        or exit($this->onError(ERR_MYSQL_CONNECT,mysql_errno() . " : " . mysql_error()));
    //Set database name if supplied
    if($this->db_name != ""){
      @mysql_select_db($this->db_name, $conn)
        or exit ($this->onError(ERR_MYSQL_USE_DB, mysql_errno() . " : " . mysql_error()));
    }
    return $conn;
  }

  //Execute single statement
  function _exec_statement($conn,$statement){
    $res = mysql_query($statement,$conn)
        or exit($this->onError(ERR_MYSQL_QUERY, mysql_errno() . " : " . mysql_error()));
    if(!($res === TRUE)){
      $rows = array();
      while($row = mysql_fetch_assoc($res)){
        $rows[] = $row;
      }
      $this->results[] = $rows;
      mysql_free_result($res);
    }
    else{
      $this->results[] = mysql_affected_rows($conn);
    }
  }

  //Check for unterminated mySQL string constant
  function _isUnterminatedString($str){
    $str = str_replace("\\'","",$str);
    preg_match_all("/'/m",$str,$arr);
    return (count($arr[0]) % 2 == 1)?TRUE:FALSE;
  }

}
?>