Location: PHPKode > projects > crVCL PHP Framework > mysql.lib.php
<?PHP

/*

  The contents of this file are subject to the Mozilla Public License
  Version 1.1 (the "License"); you may not use this file except in compliance
  with the License. You may obtain a copy of the License at
  http://www.mozilla.org/MPL/MPL-1.1.html or see MPL-1.1.txt in directory "license"

  Software distributed under the License is distributed on an "AS IS" basis,
  WITHOUT WARRANTY OF ANY KIND, either expressed or implied. See the License for
  the specific language governing rights and limitations under the License.

  The Initial Developers of the Original Code are:
  Copyright (c) 2003-2012, CR-Solutions (http://www.cr-solutions.net), Ricardo Cescon
  All Rights Reserved.

  Contributor(s): PanterMedia GmbH (http://wwwpanthermedianet), Peter Ammel, Ricardo Cescon

  crVCL PHP Framework Version 2.4
 */


############################################################
if (!defined("MYSQL_LIB")) {
   define("MYSQL_LIB", 1);
############################################################
//require("tools.lib.php");

   abstract class MySQL_{
      
      protected function cast($value, &$type, $escape_strings=true){


         if(is_string($value) && left(strtolower($value),6) == "<cast:"){
            $value = substr($value, 6);
            $end = strpos($value, ">");
            if($end !== false){
               $type = trim(substr($value, 0, $end));
               $value = substr($value, $end+1);
            }
         }

         if ($type != 'SET') { // to support named types like 'int' or 'numeric'
            $type = strtoupper(left($type, 1));
         }

         $type = str_replace("C", "S", $type);
         $type = str_replace("O", "F", $type);

         if (strpos("S;I;N;D;F;SET;E;", $type . ';') === false) { // type auto detection
            if (is_string($value)) {
               $type = "S";
            }
            if ($type == 'S' && strpos($value, '(') !== false && strpos($value, ')') !== false) {
               $type = "F";
            }
            if (is_numeric($value)) {
               $type = "N";
            }
            if (is_bool($value)) {
               $value = intval($value);
               $type = "N";
            }
         }
         if ($value === null) {
            $value = 'NULL';
            $type = "U";
         }

         if ($type == "I") {
            $value = intval($value);
         }
         if ($type == "N") {
            $value = doubleval($value);
         }
         if ($type == "D") {
            $value = dateToMySQLDate($value);
         }
         if ($type == "S" && $escape_strings) {
            if (isset($GLOBALS["CRVCL"]["LAST_MYSQL_OBJ"])) {
               if($GLOBALS["CRVCL"]["LAST_MYSQL_OBJ"] instanceof mysqli){
                  $value = $GLOBALS["CRVCL"]["LAST_MYSQL_OBJ"]->real_escape_string($value);
               }else if($GLOBALS["CRVCL"]["LAST_MYSQL_OBJ"] instanceof PDO){
                  $value = $GLOBALS["CRVCL"]["LAST_MYSQL_OBJ"]->quote($value);
               }
            } else {
               $value = mysql_real_escape_string($value);
            }
         }

         if ($type == "SET" && is_array($value)) {
            $tmp = '';
            for ($i = 0; $i < acount($value); $i++) {
               if (!empty($value[$i])) {
                  $tmp .= $value[$i] . ',';
               }
            }
            $value = $tmp;
            $tmp = null;
            if (right($value, 1) == ',') {
               $value = substr($value, 0, -1);
            }
         }
         if ("SET" == $type || "E" == $type) {
            $value = str_replace(' ', '', $value);
         }
         return $value;
      }
//-------------------------------------------------------------------------------------------------------------------------------------
      protected function buildField(&$sql, $field, $val, $type){
         if ($type != 'SET') { // to support named types like 'int' or 'numeric'
            $type = strtoupper(left($type, 1));
         }

         if (right($type, 1) != ';') {
            $type .= ';';
         }

         if (strpos("S;D;SET;E;", $type) !== false) {
            $val = "\"" . $val . "\"";
         }
         if (strpos("F;", $type) !== false) {
            $sql .= $field . " = " . $val;
         } else {
            $sql .= "`" . $field . "`" . " = " . $val;
         }
      }
//-------------------------------------------------------------------------------------------------------------------------------------
   }
//-------------------------------------------------------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * Sample how to use
    *
    *
    * $mysql = new MySQL();
    * $mysql->m_trace_path = "/var/www/test";
    * $mysql->connect("server1", "localhost","root","123");
    * $mysql->connect("server2", "192.168.2.15","root","123");
    *
    * $res = $mysql->sql("server1","SELECT * FROM test.t1 LIMIT 10");
    * $res2 = $mysql->sql("server2","SELECT * FROM test.t4 LIMIT 20");
    *
    *
    * if($mysql->getError("server1") == 0){
    *    print $res->rows();
    *    $res->free();
    * }else{
    *    $mysql->getErrorMsg("server1");
    * }
    *
    * print "<br>";
    *
    * if($mysql->getError("server2") == 0){
    *    print $res2->rows();
    *    $res2->free();
    * }else{
    *    $mysql->getErrorMsg("server2");
    * }
    *
    * $build = new MySQLBuilder(MYSQL_UPDINS, "test.t1", "id = 99");
    * $build->addValue("num",10);
    * $build->addValue("text","Hello World");
    * $build->addValue("date",date("d.m.Y"), "D");
    *
    * $mysql->sql("server1", $build);
    * print $mysql->getErrorMsg("server1");
    *
    * print "<br>";
    *
    * $build = new MySQLBuilder(MYSQL_SELECT, "test.t1", "id > 3 AND id < 50");
    * $build->addField("num");
    * $build->addField("text");
    * $build->addField("date");
    *
    * $build->addJoin("test.t1", "classid", "id");
    * $build->addField("classid", "t1");
    *
    * $res = $mysql->sql("server1", $build);
    * print $mysql->getErrorMsg("server1");
    *
    * print $res->rows();
    *
    */
   class MySQL extends MySQL_{

      const ConnType_MySQL = 0;
      const ConnType_MySQLi = 1;
      const ConnType_PDO = 2;

      private $m_logger = null;
      private $m_mysql = array();
      private $m_mysql_connect_info = array();
      private $m_mysql_type = array();
      private $m_mysql_lb_proxy = array();
      private $m_mysql_int_error = array();
      private $m_internal_errors = array();
      private $m_internal_errors_additional_msg = array();
      private $m_last_exception = array();
      private $m_notrace_next_query = false;
      private $m_lb_last_best_server = "";
      private $m_lb_last_rescan = 0;
      private $m_object_id = 0;      
      private $m_statistic = array();
      private $m_transaction = array();
      private $m_charset = "utf8";
      private $m_apc_cache = null;
      private $m_apc_cache_ttl = -1;
      private $m_default_schema = '';
      private $m_disable_safe_mode_next_query = false;
      private $m_retry_count = 0;
      private $m_connection_type = null;
      private $m_primary_id = 0;

      /**
       * show warnings
       *
       * @var bool
       */
      public $m_show_warning = true;

      /**
       * path for trace file
       *
       * @var string
       */
      public $m_trace_path = "";

      /**
       * filename of tracefile
       *
       * @var string
       */
      public $m_trace_file = "";

      /**
       * size before trace file will set to zero
       *
       * @var mixed
       */
      public $m_trace_delAfter = "1024K";

      /**
       * return the last query execution time
       *
       * @var int
       */
      public $m_last_query_ms = 0;

      /**
       * id of last used server
       *
       * @var string
       */
      public $m_last_serverid = "";

      /**
       * time for retry a lost connection (dead lock), -1 for no retry
       *
       * @var int
       */
      public $m_retry_query_on_connection_lost_ms = 1000;

      /**
       * only recommended to increase for CLI
       * 
       * @var int
       */
      public $m_retry_on_connection_lost = 1;

      /**
       * time for rescan status of servers (used by load balancing)
       *
       * @var int
       */
      public $m_lb_rescan_servers_after_ms = 5000;

      /**
       * max sec. behind master that are O.K. (used by load balancing)
       *
       * @var int
       */
      public $m_lb_max_allowed_sec_behind_master = 0;
      /**
       * prefer secondary master in round robin mode for SELECT's
       *
       * @var bool
       */
      public $m_prefer_secondary = false;
      /**
       * path for log files
       *
       * @var string
       */
      public $m_log_path = "";

      /**
       * write a error log
       *
       * @return bool
       */
      public $m_create_slave_error_log = false;

      /**
       * write a slow query log
       *
       * @var string
       */
      public $m_create_slow_query_log = false;

      /**
       * time before a query will declare as slow query
       *
       * @var int
       */
      public $m_slow_query_time_ms = 5000;

      /**
       * size before log file(s) will set to zero
       *
       * @var mixed
       */
      public $m_log_delAfter = "1024K";

      /**
       * write a query error log
       *
       * @var bool
       */
      public $m_create_query_error_log = false;

      /**
       * turn load balancing on / off (require php session and MySQL SUPER or REPLICATION CLIENT privilege)
       *
       * @var bool
       */
      public $m_lb_active = true;

      /**
       * load balancing mode, default => MYSQL_LB_LOAD_SHARING
       *
       * @var int
       */
      public $m_lb_mode = 0;

      /**
       * timeout for connection
       *
       * @var int
       */
      public $m_timeout = 10;
      /**
       * open the connection persistent (don't forgett to adjust max_connections, max_user_connections and wait_timeout for MySQL Server)
       *
       * @var bool
       */
      public $m_persistent_connection = false;

      /**
       * enable critical section for write trace files
       *
       * @var string
       */
      public $m_trace_critical_section_id = null;

      /**
       * path to share the critical section for write trace files
       *
       * @var string
       */
      public $m_trace_critical_section_path = "./";

      /**
       * max lock time for zombie processes
       *
       * @var int
       */
      public $m_trace_critical_section_maxlock_ms = 1000;

      /**
       * sleep for retry enter critical section
       *
       * @var int
       */
      public $m_trace_critical_section_sleep_ms = 10;

      /**
       * enable critical section for write log files
       *
       * @var string
       */
      public $m_log_critical_section_id = null;

      /**
       * path to share the critical section for write log files
       *
       * @var string
       */
      public $m_log_critical_section_path = "";

      /**
       * max lock time for zombie processes
       *
       * @var int
       */
      public $m_log_critical_section_maxlock_ms = 1000;

      /**
       * sleep for retry enter critical section
       *
       * @var int
       */
      public $m_log_critical_section_sleep_ms = 10;

      /**
       * activate explain debug log
       *
       * @var bool
       */
      public $m_create_explain_log = false;

      /**
       * calculate the explain max query time in milliseconds for bad queries as samlpe for queries without used index (used by explain log)<br>calculation: m_slow_query_time_ms / m_explain_slow_query_ratio
       *
       * @var int
       */
      public $m_explain_slow_query_ratio = 100;

      /**
       * num. of similar querys will be cached per php session to increase the performance of the explain log
       *
       * @var int
       */
      public $m_explain_log_cache = 50;

      /**
       * write the trace to a SQLite file instead of a text file
       *
       * @var bool
       */
      public $m_trace_sqlite = false;

      /**
       * write the log to a SQLite file instead of a text file
       *
       * @var bool
       */
      public $m_log_sqlite = false;

      /**
       * the application (client) SQL mode<br>(since Version 2.2 beta Rev. 140 it is default "TRADITIONAL" by the config.inc.php)
       *
       * @var string
       */
      public $m_sql_mode = null;

      /**
       * return the last result source, see MYSQL_RESULT_SOURCE_<X>
       *
       * @var int
       */
      public $m_last_result_source = 0;

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       *
       *
       * @param mysqli $obj
       * @return mysqli
       */
      private function MySQLi(&$obj) {
         return $obj;
      }
//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       *
       *
       * @param PDO $obj
       * @return PDO
       */
      private function MySQL_PDO(&$obj) {
         return $obj;
      }
//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * get the connection type of MySQL object
       *
       * @return MySQL::ConnType
       */
      function getConnectionType(){return $this->m_connection_type;}
//-------------------------------------------------------------------------------------------------------------------------------------
// constructor
      /**
       *
       * @param int $object_id as unique object identifier
       * @param MySQL::ConnType_MySQL $connection_type
       * @return void
       */
      function __construct($object_id = 0, $connection_type = 0) {
         $this->m_connection_type = intval($connection_type);


         if (isset($GLOBALS["CRVCL"]["CHARSET"])) {
            if (strtoupper($GLOBALS["CRVCL"]["CHARSET"]) == "UTF-8") {
               $this->m_charset = 'utf8';
            } else {
               $this->m_charset = 'latin1';
            }
         }

         $this->m_logger = new Logger();
         $this->m_internal_errors = array(
             "0" => "",
             "-99" => "unknown server id  - please check the php session is started",
             "-100" => "mysql connection object is null",
             "-101" => "You are using safe update mode and you tried to update or delete a table without a WHERE",
             "-102" => "parameter is no MySQLBuilder Object",
             "-103" => "no server set as MASTER",
             "-104" => "autoInc. no rows found for update",
             "-105" => "autoInc. updated less rows than possible rows",
             "-106" => "error get status for queries per sec. calculation",
             "-107" => "server is not SLAVE",
             "-108" => "parse error",
             "-109" => "You are using safe update mode and you tried to truncate a table",
             "-999" => "MySQL internal exception",
         );

         if ($this->m_connection_type == MySQL::ConnType_MySQL && !is_callable("mysql_connect")) {
            showFrameworkError("MySQL module for PHP not installed or loaded, please see php.ini");
            return;
         }
         if ($this->m_connection_type == MySQL::ConnType_MySQLi && !is_callable("mysqli_init")) {
            showFrameworkError("MySQLi module for PHP not installed or loaded, please see php.ini");
            return;
         }
         if ($this->m_connection_type == MySQL::ConnType_PDO && !class_exists("PDO")) {
            showFrameworkError("PDO module for PHP not installed or loaded, please see php.ini");
            return;
         }

         $this->m_object_id = $object_id;

         $this->m_statistic["SELECTS"] = 0;
         $this->m_statistic["SELECTS_TIME_TOTAL_MS"] = 0;
         $this->m_statistic["UPDATES"] = 0;
         $this->m_statistic["UPDATES_TIME_TOTAL_MS"] = 0;
         $this->m_statistic["INSERTS"] = 0;
         $this->m_statistic["INSERTS_TIME_TOTAL_MS"] = 0;
         $this->m_statistic["DELETES"] = 0;
         $this->m_statistic["DELETES_TIME_TOTAL_MS"] = 0;
         $this->m_statistic["EXPLAIN_WARNINGS"] = 0;
         $this->m_statistic["EXPLAIN_WARNINGS_TIME_TOTAL_MS"] = 0;
         $this->m_statistic["SLOW_QUERIES"] = 0;
         $this->m_statistic["SLOW_QUERIES_TIME_TOTAL_MS"] = 0;
         $this->m_statistic["TRANSACTIONS"] = 0;
         $this->m_statistic["TRANSACTIONS_TIME_TOTAL_MS"] = 0;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      function __destruct() {
         $this->free();
      }

//-------------------------------------------------------------------------------------------------------------------------------------
// private
      /**
       * @return Logger
       */
      private function &getLogger() {
         return $this->m_logger;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * return the APC OpcodeCache as reference
       *
       * @return OpcodeCache
       */
      private function &getOpcodeCache() {
         return $this->m_apc_cache;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * used for query analysing, return uppercase array with all words
       *
       * @param string $sql
       * @return array
       */
      private function parseSqlWords(&$sql) {
         $sql_words = strtoupper($sql);
         $sql_words = str_replace("\r\n", " ", $sql_words);
         $sql_words = str_replace("\r", " ", $sql_words);
         $sql_words = str_replace("\n", " ", $sql_words);
         $sql_words = str_replace("\t", " ", $sql_words);
         $sql_words = str_replace("  ", " ", $sql_words);
         $sql_words = explode(" ", trim($sql_words));
         if (acount($sql_words) == 0) {
            $sql_words = array("");
         }
         return $sql_words;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * return the MySQL Object Id
       *
       * @return int
       */
      function getObjectId() {
         return $this->m_object_id;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * return information about the loadbalancing
       *
       * @return array
       */
      function getLBStatus() {
         if (isset($_SESSION["CRVCL"]["MYSQL"]["OBJECTS"][$this->m_object_id])) {
            return $_SESSION["CRVCL"]["MYSQL"]["OBJECTS"][$this->m_object_id];
         }
         return false;
      }

//-------------------------------------------------------------------------------------------------------------------------------------


      function writeTrace($s, $add_header_msg = '') {
         $path = $this->m_trace_path;
         $file = $this->m_trace_file;
         $del = $this->m_trace_delAfter;
         $crit_id = $this->m_trace_critical_section_id;
         $crit_path = $this->m_trace_critical_section_path;
         $crit_maxlock = $this->m_trace_critical_section_maxlock_ms;
         $crit_sleep = $this->m_trace_critical_section_sleep_ms;

         if ($add_header_msg != '')
            $add_header_msg = ' - ' . $add_header_msg;

         if (!empty($path) || !empty($file)) {
            if (empty($file)) {
               $file = "mysql.lib.trc";
            }
            $logger = $this->getLogger();
            $logger->setOutputFile($path, $file, $del);
            $logger->setOutputFile_CriticalSectionId($crit_id, $crit_path, $crit_maxlock, $crit_sleep);
            $logger->enableOutputFile(true, $this->m_trace_sqlite);
            $logger->enableHeaderOutput(true);
            $logger->log(LOG_LEVEL_DEBUG, $s, $this->m_last_serverid . " (" . $this->m_last_query_ms . " ms)" . $add_header_msg);
         }
      }

//-------------------------------------------------------------------------------------------------------------------------------------   	
      function writeLog($s, $file) {
         $path = $this->m_log_path;
         $del = $this->m_log_delAfter;

         if (!empty($path)) {
            $crit_id = $this->m_log_critical_section_id;
            $crit_path = $this->m_log_critical_section_path;
            $crit_maxlock = $this->m_log_critical_section_maxlock_ms;
            $crit_sleep = $this->m_log_critical_section_sleep_ms;

            $logger = $this->getLogger();
            $logger->setOutputFile($path, $file, $del);
            $logger->setOutputFile_CriticalSectionId($crit_id, $crit_path, $crit_maxlock, $crit_sleep);
            $logger->enableOutputFile(true, $this->m_log_sqlite);
            $logger->enableHeaderOutput(true);
            $logger->log(LOG_LEVEL_DEBUG, $s, $this->m_last_serverid . " (" . $this->m_last_query_ms . " ms)");
         }
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      function free() {
         if ($this->m_mysql != NULL) {
            reset($this->m_mysql);
            for ($c = 0; $c < acount($this->m_mysql); $c++) {
               $key = key($this->m_mysql);
               $this->disconnect($key);
               next($this->m_mysql);
            }
         }

         free($this->m_mysql);
         free($this->m_mysql_type);
         free($this->m_mysql_int_error);
         free($this->m_internal_errors_additional_msg);
         free($this->m_last_exception);
         free($this->m_mysql_connect_info);
         gc_collect_cycles_overX($GLOBALS['CRVCL']['GC_COLLECT_CYCLES_PERCENT']);
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      private function setIntErr($id, $errno, $additional_msg='') {
         if (empty($id)) {
            $id = "master";
         }
         
         if(!isset($this->m_mysql_int_error[$id])){
            $id = 'unknown';            
         }
         
         $this->m_internal_errors_additional_msg[$id] = $additional_msg;
         
         $this->m_mysql_int_error[$id] = $errno;
         return $errno;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * disable the safe update mode for the next query, to use UPDATE/DELETE without WHERE or TRUNCATE
       *
       * @param bool $b
       */
      function disableSafeUpdateModeNextQuery($b) {
         $this->m_disable_safe_mode_next_query = $b;
      }

//-------------------------------------------------------------------------------------------------------------------------------------

      /**
       * open a connection to a mysql server
       *
       * @param string $id internal / id for server identification
       * @param string $server
       * @param string $user
       * @param string $pw
       * @param mixed $client_flags as sample MYSQL_CLIENT_COMPRESS for MySQL and MySQLi or array(PDO::MYSQL_ATTR_COMPRESS=>true) for PDO
       * @return bool
       */
      function connect($id = "", $server = "localhost:3306", $user = "root", $pw = "", $client_flags = null) {
         $mysql = false;

         if (empty($id)) {
            $id = "master";
         }

         $this->disconnect($id);
                  

         $tmp = explode(':', $server);
         $server = $tmp[0];
         $port = 3306;
         if(isset($tmp[1])){
            $port = 3306;
         }

         if ($this->m_show_warning) {

            try {
               if ($this->m_connection_type == MySQL::ConnType_PDO) {
                  $options = array(PDO::ATTR_TIMEOUT=>$this->m_timeout);
                  if($this->m_persistent_connection){
                     $options[PDO::ATTR_PERSISTENT] = true;
                  }
                  if($client_flags !== null && is_array($client_flags)){
                     $options = array_merge($options, $client_flags);
                  }
                  $mysql = new PDO('mysql:host='.$server.';port='.$port.';', $user, $pw, $options);
                  $mysql = $this->MySQL_PDO($mysql);
                  $mysql->setAttribute( PDO::ATTR_ERRMODE , PDO::ERRMODE_WARNING);
                  
                  $GLOBALS["CRVCL"]["LAST_MYSQL_OBJ"] = &$mysql; // required for PDO::quote in the MySQL Builder

               }else if ($this->m_connection_type == MySQL::ConnType_MySQLi) {
                  $mysql = mysqli_init();
                  if (!$mysql)
                     return false;
                  $mysql = $this->MySQLi($mysql);
                  $mysql->options(MYSQLI_OPT_CONNECT_TIMEOUT, $this->m_timeout);
                  if (!$mysql->real_connect(($this->m_persistent_connection ? 'p:' : '') . $server, $user, $pw, null, $port, null, $client_flags)) {
                     return false;
                  }

                  $GLOBALS["CRVCL"]["LAST_MYSQL_OBJ"] = &$mysql; // required for mysqli::real_escape_string in the MySQL Builder
               } else if ($this->m_persistent_connection) {
                  @ini_set('mysql.connect_timeout', $this->m_timeout);
                  $mysql = mysql_pconnect($server.':'.$port, $user, $pw, $client_flags);
                  if($mysql === false)
                     throw new Exception(mysql_error(), mysql_errno());
               } else {
                  @ini_set('mysql.connect_timeout', $this->m_timeout);
                  $mysql = mysql_connect($server.':'.$port, $user, $pw, true, $client_flags);
                  if($mysql === false)
                     throw new Exception(mysql_error(), mysql_errno());
               }
            } catch (Exception $e) {
               trigger_error($e->getMessage().' ('.$e->getCode().') ' . BR . $e->getTraceAsString(), E_USER_NOTICE);
               return false;
            }
         } else {
            try{
               if ($this->m_connection_type == MySQL::ConnType_PDO) {
                     $options = array(PDO::ATTR_TIMEOUT=>$this->m_timeout);
                     if($this->m_persistent_connection){
                        $options[PDO::ATTR_PERSISTENT] = true;
                     }
                     if($client_flags !== null && is_array($client_flags)){
                        $options = array_merge($options, $client_flags);
                     }
                     $mysql = new PDO('mysql:host='.$server.';port='.$port.';', $user, $pw, $options);
                     $mysql = $this->MySQL_PDO($mysql);

                     $GLOBALS["CRVCL"]["LAST_MYSQL_OBJ"] = &$mysql; // required for PDO::quote in the MySQL Builder

               }else if ($this->m_connection_type == MySQL::ConnType_MySQLi) {
                  $mysql = mysqli_init();
                  if (!$mysql)
                     return false;
                  $mysql = $this->MySQLi($mysql);
                  $mysql->options(MYSQLI_OPT_CONNECT_TIMEOUT, $this->m_timeout);

                  if (!$mysql->real_connect(($this->m_persistent_connection ? 'p:' : '') . $server, $user, $pw, null, $port, null, $client_flags)) {
                     return false;
                  }

                  $GLOBALS["CRVCL"]["LAST_MYSQL_OBJ"] = &$mysql; // required for mysqli::real_escape_string in the MySQL Builder
               } else if ($this->m_persistent_connection) {
                  @ini_set('mysql.connect_timeout', $this->m_timeout);
                  $mysql = @mysql_pconnect($server.':'.$port, $user, $pw, $client_flags);
                  if($mysql === false)
                     throw new Exception(mysql_error(), mysql_errno());
               } else {
                  @ini_set('mysql.connect_timeout', $this->m_timeout);
                  $mysql = @mysql_connect($server.':'.$port, $user, $pw, true, $client_flags);
                  if($mysql === false)
                     throw new Exception(mysql_error(), mysql_errno());
               }

            }catch(Exception $e){
               $this->setIntErr($id, -999);
               $this->m_last_exception[$id] = $e->getMessage().' ('.$e->getCode().') ' . BR . $e->getTraceAsString();
               return false;
            }
         }
         if ($mysql !== false) {
            /*
            $this->m_mysql += array($id => $mysql);
            $this->m_mysql_type += array($id => MYSQL_NULL);
            $this->m_mysql_int_error += array($id => "0");
            $this->m_internal_errors_additional_msg += array($id => "");
            $this->m_last_exception += array($id => "");
            $this->m_mysql_connect_info += array($id => array("host" => $server, "port"=>$port, "user" => $user, "password" => base64_encode($pw), "cflags" => $client_flags));
             */
            $this->m_mysql[$id] = $mysql;
            $this->m_mysql_type[$id] = MYSQL_NULL;
            $this->m_mysql_int_error[$id] = "0";
            $this->m_internal_errors_additional_msg[$id] = "";
            $this->m_last_exception[$id] = "";

            $version = $this->sqlGetField($id, 'SELECT VERSION() as version');
            $version = explode('.', $version);
            if(isset($version[0]) && isset($version[1]) && isset($version[2])){
               $version[2] = strcut($version[2], '-');
               $version[2] = strcut($version[2], '_');
               $version = $version[0].'.'.$version[1].'.'.$version[2];
            }else{
               $version = '0.0.0';
            }

            $this->m_mysql_connect_info[$id] = array("host" => $server, "port"=>$port, "user" => $user, "password" => base64_encode($pw), "cflags" => $client_flags, "version"=>$version);

            if (!empty($this->m_charset)) {
               if ($this->m_connection_type == MySQL::ConnType_PDO) {
                  $mysql->exec('SET NAMES '.$this->m_charset);
               }else if ($this->m_connection_type == MySQL::ConnType_MySQLi) {
                  $mysql->set_charset($this->m_charset);
               } else {
                  mysql_set_charset($this->m_charset, $mysql);
               }
            }

            if ($this->m_sql_mode === null && isset($GLOBALS["CRVCL"]["SQL_MODE"])) {
               $this->m_sql_mode = $GLOBALS["CRVCL"]["SQL_MODE"];
            }

            if ($this->m_sql_mode !== null) {
               $this->sql($id, "SET SQL_MODE='" . $this->m_sql_mode . "'");
            }

            return true;
         }
         return false;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * try to reconnect to the MySQL Server
       *
       * @param string $id
       * @return bool
       */
      function reconnect($id = "master") {
         if (empty($id)) {
            $id = "master";
         }
         $this->setIntErr($id, 0);

         if (!isset($this->m_mysql[$id])) {
            $this->setIntErr($id, -99, var_dumpEx($id,true));
            $this->writeLog("reconnect \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }
         $mysql = &$this->m_mysql[$id];
         if (!$mysql) {
            $this->setIntErr($id, -100);
            $this->writeLog("reconnect \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }

         $ret = false;
         $connect_info = $this->m_mysql_connect_info;
         if($connect_info[$id]['version'] > "0.0.0" && $connect_info[$id]['version'] < '5.0.3'){ // see http://dev.mysql.com/doc/refman/5.0/en/auto-reconnect.html
                 
            if ($this->m_connection_type == MySQL::ConnType_PDO) {
               $mysql = $this->MySQL_PDO($mysql);
               $ret = true;
               try {
                  $mysql->query('SELECT 1');
               }catch(Exception $e){
                  $e = $ret = false;
               }
            }else if ($this->m_connection_type == MySQL::ConnType_MySQLi) {
               $mysql = $this->MySQLi($mysql);
               $ret = $mysql->ping();
            } else {
               $ret = mysql_ping($mysql);
            }
         }
         
         if ($ret == false) {            
            $this->disconnect($id);
            gc_collect_cycles_overX($GLOBALS['CRVCL']['GC_COLLECT_CYCLES_PERCENT']);
            mssleep(100);            
            $ret = $this->connect($id, $connect_info[$id]['host'].':'.$connect_info[$id]['port'], $connect_info[$id]['user'], base64_decode($connect_info[$id]['password']), $connect_info[$id]['cflags']);
         }

         return $ret;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * set the connection charset => "null" for use server default charset (must be called before method "connect")
       *
       * @param string $charset
       */
      function setCharset($charset) {
         $this->m_charset = $charset;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * disconnect a mysql server connection
       *
       * @param string $id
       * @return bool
       */
      function disconnect($id = "master") {
         if (empty($id)) {
            $id = "master";
         }
         $this->setIntErr($id, 0);
         $ret = false;

         if (!isset($this->m_mysql[$id])) {
            $this->setIntErr($id, -99, var_dumpEx($id,true));
            return $ret;
         }
         $mysql = $this->m_mysql[$id];
         if (!$mysql) {
            $this->setIntErr($id, -100);
            $this->writeLog("disconnect \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return $ret;
         }

         if (acount($this->m_mysql) > 0 && array_key_exists($id, $this->m_mysql)) {
            $this->rollback($id);

            $mysql = $this->m_mysql[$id];

            $ret = true;
            if ($this->m_connection_type == MySQL::ConnType_PDO) {
               $mysql = $this->MySQL_PDO($mysql);
               free($mysql);
               $ret = true;
            }else if ($this->m_connection_type == MySQL::ConnType_MySQLi) {
               $mysql = $this->MySQLi($mysql);
               $ret = $mysql->close();
            } else {
               if (!$this->m_persistent_connection) {
                  $ret = mysql_close($mysql);
               }
            }


            if ($ret) {
               free($this->m_mysql[$id]);
               free($this->m_mysql_type[$id]);
               free($this->m_mysql_int_error[$id]);
               free($this->m_internal_errors_additional_msg);
               free($this->m_last_exception[$id]);
               free($this->m_mysql_connect_info[$id]);
            }
         }
         return $ret;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * version info of mysql server
       *
       * @param string $id
       * @return string
       */
      function version($id = "master") {
         if (empty($id)) {
            $id = "master";
         }
         $this->setIntErr($id, 0);

         if (!isset($this->m_mysql[$id])) {
            $this->setIntErr($id, -99, var_dumpEx($id,true));
            $this->writeLog("get version \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return "";
         }
         $mysql = $this->m_mysql[$id];
         if (!$mysql) {
            $this->setIntErr($id, -100);
            $this->writeLog("get version \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return "";
         }

         if ($this->m_connection_type == MySQL::ConnType_PDO) {
            $mysql = $this->MySQL_PDO($mysql);
            return $mysql->getAttribute(PDO::ATTR_SERVER_VERSION);
         }else if ($this->m_connection_type == MySQL::ConnType_MySQLi) {
            $mysql = $this->MySQLi($mysql);
            return $mysql->server_info;
         }
         return mysql_get_server_info($mysql);
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * info of mysql server
       *
       * @param string $id
       * @return string
       */
      function host_info($id = "master") {
         if (empty($id)) {
            $id = "master";
         }
         $this->setIntErr($id, 0);

         if (!isset($this->m_mysql[$id])) {
            $this->setIntErr($id, -99, var_dumpEx($id,true));
            $this->writeLog("get version \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return "";
         }
         $mysql = $this->m_mysql[$id];
         if (!$mysql) {
            $this->setIntErr($id, -100);
            $this->writeLog("get version \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return "";
         }

         if ($this->m_connection_type == MySQL::ConnType_PDO) {
            $mysql = $this->MySQL_PDO($mysql);
            return $mysql->getAttribute(PDO::ATTR_SERVER_INFO);
         }else if ($this->m_connection_type == MySQL::ConnType_MySQLi) {
            $mysql = $this->MySQLi($mysql);
            return $mysql->host_info;
         }
         return mysql_get_host_info($mysql);
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * get last error code
       *
       * @param string $id
       * @return int
       */
      function getError($id = "master") {
         if (empty($id)) {
            $id = "master";
         }

         if (!isset($this->m_mysql[$id]) || !$this->m_mysql[$id] || $this->m_mysql_int_error[$id] != 0) {
            return $this->m_mysql_int_error[$id];
         }

         if ($this->m_connection_type == MySQL::ConnType_PDO) {
            $mysql = &$this->m_mysql[$id];
            $mysql = $this->MySQL_PDO($mysql);
            return $mysql->errorCode();
         }else if ($this->m_connection_type == MySQL::ConnType_MySQLi) {
            $mysql = &$this->m_mysql[$id];
            $mysql = $this->MySQLi($mysql);
            return $mysql->errno;
         }
         return mysql_errno($this->m_mysql[$id]);
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * get last error message
       *
       * @param string $id
       * @return string
       */
      function getErrorMsg($id = "master") {
         if (empty($id)) {
            $id = "master";
         }
         
         if (!isset($this->m_mysql[$id]) || !$this->m_mysql[$id] || $this->m_mysql_int_error[$id] != 0) {
            if($this->m_mysql_int_error[$id] == -999){
               return $this->m_internal_errors[$this->m_mysql_int_error[$id]].' - '.$this->m_last_exception[$id];
            }
            
            return (isset($this->m_internal_errors[$this->m_mysql_int_error[$id]]) 
                    ? $this->m_internal_errors[$this->m_mysql_int_error[$id]] (isset($this->m_internal_errors_additional_msg[$id])?' - '.$this->m_internal_errors_additional_msg[$id]:'')
                    : 'unknown internal error');
         }

         if ($this->m_connection_type == MySQL::ConnType_PDO) {
            $mysql = &$this->m_mysql[$id];
            $mysql = $this->MySQL_PDO($mysql);
            return $mysql->errorInfo();
         }else if ($this->m_connection_type == MySQL::ConnType_MySQLi) {
            $mysql = &$this->m_mysql[$id];
            $mysql = $this->MySQLi($mysql);
            return $mysql->error;
         }
         return mysql_error($this->m_mysql[$id]);
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * return a array with statistics about SELECTS, UPDATES, INSERTS ect. since the object was created
       *
       * @return array
       */
      function getStatistics() {
         return $this->m_statistic;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * return MySQLResult or number of affected rows
       *
       * @param string $id internal / id for server identification
       * @param mixed $sql_query or $sql_builder_object
       * @param int $fetch_type
       * @param bool $free_result    if false the result resource will be freed by garbage collector or should be freed by mysql_free_result manually
       * @return MySQLResult
       */
      function sql($id = "master", $sql, $fetch_type = MYSQL_BOTH, $free_result = true) {
         if ($this->m_connection_type == MySQL::ConnType_MySQLi && $fetch_type == MYSQL_BOTH)
            $fetch_type = MYSQLI_BOTH;
         else if ($this->m_connection_type == MySQL::ConnType_PDO && $fetch_type == MYSQL_BOTH)
            $fetch_type = PDO::FETCH_BOTH;
         
         $this->setIntErr($id, 0);

         if (empty($id)) {
            $id = "master";
         }

         //echo "id: ".$id.BR;
         //echo "sql: ".$sql.BR;
         //echo "free: ".$free_result.BR;
         // MySQLBuilder
         if (is_object($sql)) {
            $ret = false;
            $bsql = $sql;
            $sql = "";
            if ($bsql->m_type == MYSQL_UPDINS) {
               $bsql->m_type = MYSQL_SELECT;
               $bsql->addField("count(*) AS found_rows");
               $res = $this->sql($id, $bsql, $fetch_type, $free_result);
               if ($this->getError($id) != 0) {
                  return $ret;
               }
               $rows = $res->getField("found_rows");
               $res->free();
               if ($rows > 0) {
                  $bsql->m_type = MYSQL_UPDATE;
                  $ret = $this->sql($id, $bsql, $fetch_type, $free_result);
               } else {
                  $bsql->m_type = MYSQL_INSERT;
                  $ret = $this->sql($id, $bsql, $fetch_type, $free_result);
               }
               return $ret;
            } else if ($bsql->m_type == MYSQL_SELINS) {
               $bsql->m_type = MYSQL_SELECT;
               $bsql->addField("count(*) AS found_rows");
               $res = $this->sql($id, $bsql, $fetch_type, $free_result);
               if ($this->getError($id) != 0) {
                  return $ret;
               }
               $rows = $res->getField("found_rows");
               $res->free();

               if ($rows == 0) {
                  $bsql->m_type = MYSQL_INSERT;
                  $ret = $this->sql($id, $bsql, $fetch_type, $free_result);
               } else {
                  $ret = 0;
               }

               return $ret;
            } else {
               $sql = $bsql->query();
            }
         }

         ///////////////

         $this->m_last_serverid = $id;

         if (!isset($this->m_mysql[$id])) { 
            $this->setIntErr($id, -99, var_dumpEx($id,true));
            $this->writeLog($sql . "\r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return null;
         }
         $mysql = $this->m_mysql[$id];
         if (!$mysql) {
            $this->setIntErr($id, -100);
            $this->writeLog($sql . "\r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return null;
         }

         ///////////////

         if (strtolower($this->m_charset) == "utf8") {
            if (!is_utf8($sql)) {
               $sql = utf8_encodeEx($sql);
            }
         } else {
            if (is_utf8($sql)) {
               $sql = utf8_decode($sql);
            }
         }



         $sql_words = $this->parseSqlWords($sql);

         if (!$this->m_disable_safe_mode_next_query) {
            // secutity check (safe update mode)
            if (strtoupper($sql_words[0]) == "DELETE"
                    || strtoupper($sql_words[0]) == "UPDATE"
                    || strtoupper($sql_words[0]) == "TRUNCATE"
            ) {
               if (strtoupper($sql_words[0]) == "TRUNCATE") {
                  $ret = $this->setIntErr($id, -109);
                  $this->writeLog($sql . "\r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
                  return null;
               } else if (array_search('WHERE', $sql_words) === false) {
                  $ret = $this->setIntErr($id, -101);
                  $this->writeLog($sql . "\r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
                  return null;
               }
            }
         }
         $this->m_disable_safe_mode_next_query = false;


         $apc_key = '';
         $oc = null;
         $this->m_last_result_source = MYSQL_RESULT_SOURCE_SERVER;
         if ($this->m_apc_cache_ttl > -1 && strtoupper($sql_words[0]) == "SELECT") {
            $oc = $this->getOpcodeCache();
            if ($oc === null) {
               $oc = new OpcodeCache();
               $this->m_apc_cache = &$oc;
            }

            $apc_key = 'MySQL_Cache:'.md5($id) . ':' . hash('sha256', $sql);

            $qstart = gettickcount();

            $success = false;
            $ret = $oc->fetch($apc_key, $success);

            if ($success && is_object($ret) && $ret instanceof MySQLResult) {
               $oc->add($apc_key, $ret, $this->m_apc_cache_ttl); // restore with new ttl
               $qend = gettickcount();
               $this->m_last_result_source = MYSQL_RESULT_SOURCE_CACHE;
               $this->m_last_query_ms = $qend - $qstart;
               $this->writeTrace($sql, 'internal query cache'); // trace the query
               return $ret;
            }
         }

         // save default schema
         if (strtoupper($sql_words[0]) == "USE" && isset($sql_words[1])) {
            $this->m_default_schema = strtolower($sql_words[1]);
         }

         if (isset($this->m_transaction[$id][0]) && $this->m_transaction[$id][0] === true) {
            $this->m_transaction[$id][3] = $this->m_transaction[$id][3] + 1;
         }

         $result = false;
         $qstart = gettickcount();
         try {
            if ($this->m_connection_type == MySQL::ConnType_PDO) {
               $mysql = $this->MySQL_PDO($mysql);


               # scrollable cursor not supported this time by MySQL, see also in the constructor of the result
               #$result = $mysql->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
               $result = $mysql->prepare($sql);
               
               
               $result->execute();

            }else if ($this->m_connection_type == MySQL::ConnType_MySQLi) {
               $mysql = $this->MySQLi($mysql);
               $result = $mysql->query($sql);
               
            } else {
               $result = mysql_query($sql, $mysql);
            }
         } catch (Exception $e) {
            $mysql_errorcode = -1;
            try {
               if ($this->m_connection_type == MySQL::ConnType_PDO) {
                  $mysql = $this->MySQL_PDO($mysql);
                  $mysql_errorcode = $mysql->errorCode();
               }else if ($this->m_connection_type == MySQL::ConnType_MySQLi) {
                  $mysql = $this->MySQLi($mysql);
                  $mysql_errorcode = $mysql->errno;
               } else {
                  $mysql_errorcode = mysql_errno($mysql);
               }
            } catch (Exception $e) {

            }

            if ($mysql_errorcode != -1) {
               return null;
            }
            throw $e;
         }
         $qend = gettickcount();
         $this->m_last_query_ms = $qend - $qstart;

         $errno = -1;
         if ($this->m_connection_type == MySQL::ConnType_PDO) {
            $mysql = $this->MySQL_PDO($mysql);
            $errno = $mysql->errorCode();
         }else if ($this->m_connection_type == MySQL::ConnType_MySQLi) {
            $mysql = $this->MySQLi($mysql);
            $errno = $mysql->errno;
         } else {
            $errno = mysql_errno($mysql);
         }


         # !!! see also lbSQL !!!
         // 2006 => MySQL server has gone away
         // 2011 => %s via TCP/IP
         // 2013 => Lost connection to MySQL server during query
         // 1040 => Too many connections
         // 1053 => Server shutdown in progress
         // 1152 => Aborted connection %ld to db: '%s' user: '%s' (%s)
         // 1184 => Aborted connection %ld to db: '%s' user: '%s' host: '%s' (%s)
         // 1203 => User %s already has more than 'max_user_connections' active connections
         // 1205 => Lock wait timeout expired.
         // 1213 => Deadlock found when trying to get lock; try restarting transaction
         // 2048 => Invalid connection handle
         // 2055 => Lost connection to MySQL server at '%s', system error: %d
         // retry only allowed for no or single transaction and in case of 1205/1213
         if ((!isset($this->m_transaction[$id]) || $this->m_transaction[$id][0] === false
                 || $this->m_transaction[$id][3] <= 1
                 || $errno == 1205 || $errno == 1213) // only for not lb mode
         ) {

            if ($errno == 2013 || $errno == 2011 || $errno == 2006 ||
                    $errno == 1040 || $errno == 1053 ||
                    $errno == 1152 || $errno == 1184 || $errno == 1203 || $errno == 1205 || $errno == 1213 ||
                    $errno == 2048 || $errno == 2055) {

               // retry query
               if ($this->m_retry_query_on_connection_lost_ms > -1) {
                  $this->writeLog($sql . "\r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
                  mssleep($this->m_retry_query_on_connection_lost_ms);

                  $connected = true;
                  if ($errno != 1205 && $errno != 1213) {
                     $connected = $this->reconnect($id);
                  }
                  if ($connected) {
                     $retry_query_on_connection_lost_ms = $this->m_retry_query_on_connection_lost_ms;
                     $this->m_retry_count = $this->m_retry_count + 1;
                     if ($this->m_retry_count >= $this->m_retry_on_connection_lost) {
                        $this->m_retry_query_on_connection_lost_ms = -1; // disable retry for next query call if it fail again
                     }
                     $res = $this->sql($id, $sql, $fetch_type, $free_result);
                     $this->m_retry_query_on_connection_lost_ms = $retry_query_on_connection_lost_ms;
                     return $res;
                  }
               }
            }
         }
         $this->m_retry_count = 0;


         if ($this->m_notrace_next_query == false) {
            $this->writeTrace($sql); // trace the query

            if ($this->m_create_slow_query_log && $this->m_last_query_ms > $this->m_slow_query_time_ms) {
               $this->m_statistic["SLOW_QUERIES"] = $this->m_statistic["SLOW_QUERIES"] + 1;
               $this->m_statistic["SLOW_QUERIES_TIME_TOTAL_MS"] = $this->m_statistic["SLOW_QUERIES_TIME_TOTAL_MS"] + $this->m_last_query_ms;

               $this->writeLog($sql, $id . ".slowquerys.log");
            }

            if ($this->m_create_query_error_log && (
                    ( $this->m_connection_type == MySQL::ConnType_PDO && $mysql->errorCode() != 0)
                    || ( $this->m_connection_type == MySQL::ConnType_MySQLi && $mysql->errno != 0)
                    || !$result)   ) {

               if ($this->m_connection_type == MySQL::ConnType_PDO) {
                  $this->writeLog($sql . "\r\nMySQL Error: " . $mysql->errorInfo() . " (" . $mysql->errorCode() . ")", $id . ".querys.error.log");
               }else if ($this->m_connection_type == MySQL::ConnType_MySQLi) {
                  $this->writeLog($sql . "\r\nMySQL Error: " . $mysql->error . " (" . $mysql->errno . ")", $id . ".querys.error.log");
               } else {
                  $this->writeLog($sql . "\r\nMySQL Error: " . mysql_error($mysql) . " (" . mysql_errno($mysql) . ")", $id . ".querys.error.log");
               }
            }
         } else {
            $this->m_notrace_next_query = false;
         }



         $explain_warning_max_query_time_ms = intval($this->m_slow_query_time_ms / $this->m_explain_slow_query_ratio);
         if ($explain_warning_max_query_time_ms < 1) {
            $explain_warning_max_query_time_ms = 1;
         }
         $explain_warning = '';
         if ($this->m_create_explain_log && (strtoupper($sql_words[0]) == "SELECT") && $this->m_last_query_ms > $explain_warning_max_query_time_ms) {

            $esql = 'EXPLAIN ' . $sql;

            $similar_query = false;
            if (isset($_SESSION["CRVCL"]["MYSQL"]["EXPLAIN_CACHE"])) {
               $cq = acount($_SESSION["CRVCL"]["MYSQL"]["EXPLAIN_CACHE"]);
               for ($q = 0; $q < $cq; $q++) {
                  $percent = 0;
                  similar_text($_SESSION["CRVCL"]["MYSQL"]["EXPLAIN_CACHE"][$q], $esql, $percent);
                  if ($percent > 60) {
                     $similar_query = true;
                     break;
                  }
               }
               if ($cq > $this->m_explain_log_cache) { // trim cache
                  unset($_SESSION["CRVCL"]["MYSQL"]["EXPLAIN_CACHE"][0]);
                  sort($_SESSION["CRVCL"]["MYSQL"]["EXPLAIN_CACHE"]);
               }
            } else {
               $_SESSION["CRVCL"]["MYSQL"]["EXPLAIN_CACHE"] = array();
            }

            if (!$similar_query) {
               $_SESSION["CRVCL"]["MYSQL"]["EXPLAIN_CACHE"][] = $esql;

               $this->m_notrace_next_query = true;

               $res = $this->sql($id, $esql);
               if ($this->getError($id) != 0) {
                  $this->writeLog("Error EXPLAIN query: " . CRLF . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", 'explain.log');
               } else {
                  for ($i = 0; $i < $res->rows(); $i++) {
                     $key = $res->getField('key');
                     $extra = $res->getField('Extra');
                     $type = $res->getField('type');


                     if ($i == 0 && $type == "ALL") {
                        $explain_warning = "Query with full table scan at the first table: " . CRLF . $esql;
                        break;
                     }
                     /* check not relevant
                       if($i == 0 && $type == "index"){
                       $explain_warning = "Query with full index scan at the first table: ".CRLF.$esql;
                       break;
                       } */

                     if (strpos($extra, 'No tables used') === false // functions like: EXPLAIN SELECT f1(5);
                             && strpos($extra, 'Select tables optimized away') === false // http://dev.mysql.com/doc/refman/5.0/en/using-explain.html
                             && strpos($extra, 'Impossible WHERE noticed after reading const tables') === false // transformed UPDATE
                     ) {
                        if (empty($key)) {
                           $explain_warning = "Query without used index: " . CRLF . $esql;
                           break;
                        } else if (stripos($extra, 'filesort') !== false) {
                           $explain_warning = "Query useing filesort: " . CRLF . $esql;
                           break;
                        } else if (stripos($extra, 'temporary') !== false) {
                           $explain_warning = "Query useing temporary: " . CRLF . $esql;
                           break;
                        }
                     }

                     $res->next();
                  }
               }
            }

            if ($explain_warning != '') {
               $this->m_statistic["EXPLAIN_WARNINGS"] = $this->m_statistic["EXPLAIN_WARNINGS"] + 1;
               $this->m_statistic["EXPLAIN_WARNINGS_TIME_TOTAL_MS"] = $this->m_statistic["EXPLAIN_WARNINGS_TIME_TOTAL_MS"] + $this->m_last_query_ms;
               $this->writeLog($explain_warning, 'explain.log');
            }
         }



         if (strtoupper($sql_words[0]) == "SELECT"
                 || strtoupper($sql_words[0]) == "EXPLAIN"
                 || strtoupper($sql_words[0]) == "SHOW"
                 || strtoupper($sql_words[0]) == "DESCRIBE"
         ) {
            if ($errno != 0 || !$result) {
               return null;
            }

            // modify also the constructor
            if (strtoupper($sql_words[0]) == "SELECT") {
               $this->m_statistic["SELECTS"] = $this->m_statistic["SELECTS"] + 1;
               $this->m_statistic["SELECTS_TIME_TOTAL_MS"] = $this->m_statistic["SELECTS_TIME_TOTAL_MS"] + $this->m_last_query_ms;
            }

            $mysqlres = new MySQLResult($result, $fetch_type, $free_result);

            if ($apc_key != '') {
               $mysqlres->toHeap();

               $oc = $this->getOpcodeCache();
               $oc->add($apc_key, $mysqlres, $this->m_apc_cache_ttl);
            }

            return $mysqlres;
         }



         if ($errno != 0) {
            return null;
         }

         // modify also the constructor
         if (strtoupper($sql_words[0]) == "UPDATE") {
            $this->m_statistic["UPDATES"] = $this->m_statistic["UPDATES"] + 1;
            $this->m_statistic["UPDATES_TIME_TOTAL_MS"] = $this->m_statistic["UPDATES_TIME_TOTAL_MS"] + $this->m_last_query_ms;
         } else if (strtoupper($sql_words[0]) == "INSERT") {
            $this->m_statistic["INSERTS"] = $this->m_statistic["INSERTS"] + 1;
            $this->m_statistic["INSERTS_TIME_TOTAL_MS"] = $this->m_statistic["INSERTS_TIME_TOTAL_MS"] + $this->m_last_query_ms;
         } else if (strtoupper($sql_words[0]) == "DELETE") {
            $this->m_statistic["DELETES"] = $this->m_statistic["DELETES"] + 1;
            $this->m_statistic["DELETES_TIME_TOTAL_MS"] = $this->m_statistic["DELETES_TIME_TOTAL_MS"] + $this->m_last_query_ms;
         } else if (strtoupper($sql_words[0]) == "BEGIN") {
            $this->m_statistic["TRANSACTIONS"] = $this->m_statistic["TRANSACTIONS"] + 1;
            if (!isset($this->m_transaction[$id]) || $this->m_transaction[$id][0] === false) { // if not class own transaction start via method "begin"
               $this->m_transaction[$id] = array(true, null, gettickcount(), 0);
            }
         } else if (strtoupper($sql_words[0]) == "COMMIT" || strtoupper($sql_words[0]) == "ROLLBACK") {
            $this->m_statistic["TRANSACTIONS_TIME_TOTAL_MS"] = $this->m_statistic["TRANSACTIONS_TIME_TOTAL_MS"] + (gettickcount() - $this->m_transaction[$id][2]);
         }

         if($this->m_connection_type == MySQL::ConnType_PDO){
            return $result->rowCount();
         }else if($this->m_connection_type == MySQL::ConnType_MySQLi){
            return $mysql->affected_rows;
         }
         return mysql_affected_rows($mysql);
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * INSERT or UPDATE a row and return the id or passed field,<br>this method is a little bit more flexible than the functions LAST_INSERT_ID() and mysql_insert_id(),<br>but require a UUID field in the table where the row should be inserted
       *
       * @param string $id
       * @param MySQLBuilder $sql_builder_object
       * @param int $fetch_type
       * @param string $uuid_field should be default CHAR(38)
       * @param mixed $uuid is default 38 char length
       * @param string $ret_field
       * @return mixed
       */
      function sqlUUID($id = "master", $sql_builder_object, $fetch_type = MYSQL_BOTH, $uuid_field = "uuid", $uuid = null, $ret_field = "id") {
         if ($this->m_connection_type == MySQL::ConnType_MySQLi && $fetch_type == MYSQL_BOTH)
            $fetch_type = MYSQLI_BOTH;
         else if ($this->m_connection_type == MySQL::ConnType_PDO && $fetch_type == MYSQL_BOTH)
            $fetch_type = PDO::FETCH_BOTH;
         
         $this->setIntErr($id, 0);

         if (empty($id)) {
            $id = "master";
         }

         $this->m_last_serverid = $id;

         if (!isset($this->m_mysql[$id])) {
            $this->setIntErr($id, -99, var_dumpEx($id,true));
            $this->writeLog("sqlUUID \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return null;
         }
         $mysql = $this->m_mysql[$id];
         if (!$mysql) {
            $this->setIntErr($id, -100);
            $this->writeLog("sqlUUID \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return null;
         }
         if (!is_object($sql_builder_object)) {
            $this->setIntErr($id, -102);
            $this->writeLog("sqlUUID \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return null;
         }
         if ($uuid === null) {
            $uuid = UUID();
         }

         $table = $sql_builder_object->m_table;

         $sql_builder_object->addValue($uuid_field, $uuid, "S");

         $ret = $this->sql($id, $sql_builder_object, $fetch_type);
         if ($this->getError($id) != 0) {
            return null;
         }

         if (!is_numeric($uuid)) {
            $uuid = "'" . $uuid . "'";
         }

         $ret = $this->sqlGetField($id, "SELECT " . $ret_field . " FROM " . $table . " WHERE " . $uuid_field . " = " . $uuid);

         if ($this->getError($id) != 0) {
            return null;
         }

         return $ret;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * update a TEXT field<br>Alias for "sqlBLOB" with default string escaping<br><br>ATTENTION: Since MySQL 5.1.31 the parameter max_allowed_packet_size will be change the globally server setting and not per session (need super privileges), see also http://bugs.mysql.com/bug.php?id=22891
       *
       * @param string $id
       * @param string $table
       * @param string $fieldname
       * @param string $string
       * @param mixed $where_id
       * @param string $where_fieldname
       * @param int $max_allowed_packet_size
       * @param int $truncate_packet_size
       * @param bool $escape_string
       * @return bool
       */
      function sqlTEXT($id = "master", $table, $fieldname, $string, $where_id, $where_fieldname = "id", $max_allowed_packet_size = '1M', $truncate_packet_size = '512K', $escape_string = true) {
            return $this->sqlBLOB($id, $table, $fieldname, $string, $where_id, $where_fieldname, $max_allowed_packet_size, $truncate_packet_size, $escape_string);
      }
//------------------------------------
      /**
       * update a BLOB or a TEXT field <br><br>ATTENTION: Since MySQL 5.1.31 the parameter max_allowed_packet_size will be change the globally server setting and not per session (need super privileges), see also http://bugs.mysql.com/bug.php?id=22891
       *
       * @param string $id
       * @param string $table
       * @param string $fieldname
       * @param string $string
       * @param mixed $where_id
       * @param string $where_fieldname
       * @param int $max_allowed_packet_size
       * @param int $truncate_packet_size
       * @param bool $escape_string
       * @return bool
       */
      function sqlBLOB($id = "master", $table, $fieldname, $string, $where_id, $where_fieldname = "id", $max_allowed_packet_size = '1M', $truncate_packet_size = '512K', $escape_string = false) {
         $this->setIntErr($id, 0);

         if (empty($id)) {
            $id = "master";
         }

         $this->m_last_serverid = $id;

         if (!isset($this->m_mysql[$id])) {
            $this->setIntErr($id, -99, var_dumpEx($id,true));
            $this->writeLog("sqlBLOB \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }
         $mysql = $this->m_mysql[$id];
         if (!$mysql) {
            $this->setIntErr($id, -100);
            $this->writeLog("sqlBLOB \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }


         if (strtolower($this->m_charset) == "utf8") {
            if (!is_utf8($string)) {
               $string = utf8_encodeEx($string);
            }
         } else {
            if (is_utf8($string)) {
               $string = utf8_decode($string);
            }
         }


         $max_allowed_packet_size = str2byteInt($max_allowed_packet_size);
         $truncate_packet_size = str2byteInt($truncate_packet_size);



         if (!is_numeric($where_id)) {
            $where_id = "'" . $where_id . "'";
         }


         $old_max_allowed_packet_size = $this->sqlGetField($id, "SELECT @@max_allowed_packet AS psize");
         if ($this->getError($id) != 0) {
            return false;
         }

         // try to set new max_allowed_packet_size
         $bug = false;
         if ($old_max_allowed_packet_size < $max_allowed_packet_size) {
            $this->sql($id, "SET SESSION max_allowed_packet = " . $max_allowed_packet_size);
            if ($this->getError($id) != 0) { // fix the bug #22891 since MySQL 5.1.31
               $this->sql($id, "SET GLOBAL max_allowed_packet = " . $max_allowed_packet_size);
               $bug = true;
            }
            if ($this->getError($id) != 0 && $this->getError($id) != 1227) { // catch the mysql error "you need the SUPER privilege for this operation"
               return false;
            }
         }

         while (strlen($string) > 0) {
            $packet = substr($string, 0, $truncate_packet_size);
            $string = substr($string, $truncate_packet_size);
            if ($escape_string) {
               if ($this->m_connection_type == MySQL::ConnType_PDO) {
                  $packet = $mysql->quote($packet);
               }else if ($this->m_connection_type == MySQL::ConnType_MySQLi) {
                  $packet = $mysql->real_escape_string($packet);
               } else {
                  $packet = mysql_real_escape_string($packet, $mysql);
               }
            }

            if (strpos($fieldname, "`") === false) {
               $fieldname = "`" . $fieldname . "`";
            }
            $ret = $this->sql($id, "UPDATE " . $table . " SET " . $fieldname . " = CONCAT(" . $fieldname . ", '" . $packet . "') WHERE " . $where_fieldname . " = " . $where_id);
            if ($this->getError($id) != 0) {
               return false;
            }
         }

         // reset old max_allowed_packet_size
         if (!$bug && $old_max_allowed_packet_size < $max_allowed_packet_size) {
            $this->sql($id, "SET SESSION max_allowed_packet = " . $old_max_allowed_packet_size);
            if ($this->getError($id) != 0) {
               return false;
            }
         }

         return true;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * return a single field value
       *
       * @param string $sql
       * @param int $row
       * @return mixed
       */
      function sqlGetField($id = "master", $sql, $row = 0) {
         $this->setIntErr($id, 0);

         if (empty($id)) {
            $id = "master";
         }

         $this->m_last_serverid = $id;

         if (!isset($this->m_mysql[$id])) {
            $this->setIntErr($id, -99, var_dumpEx($id,true));
            $this->writeLog($sql . "\r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }
         $mysql = $this->m_mysql[$id];
         if (!$mysql) {
            $this->setIntErr($id, -100);
            $this->writeLog($sql . "\r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }

         if (strtolower($this->m_charset) == "utf8") {
            if (!is_utf8($sql)) {
               $sql = utf8_encodeEx($sql);
            }
         } else {
            if (is_utf8($sql)) {
               $sql = utf8_decode($sql);
            }
         }

         $qstart = gettickcount();
         if ($this->m_connection_type == MySQL::ConnType_PDO) {
            $result = $mysql->query($sql);
         }else if ($this->m_connection_type == MySQL::ConnType_MySQLi) {
            $result = $mysql->query($sql);
         } else {
            $result = mysql_query($sql, $mysql);
         }
         $qend = gettickcount();
         $this->m_last_query_ms = $qend - $qstart;

         $this->writeTrace($sql);

         if ($this->m_create_slow_query_log && $this->m_last_query_ms > $this->m_slow_query_time_ms) {
            $this->writeLog($sql, $id . ".slowquerys.log");
         }

         if ($this->m_create_query_error_log && (
                    ( $this->m_connection_type == MySQL::ConnType_PDO && $mysql->errorCode() != 0)
                    || ( $this->m_connection_type == MySQL::ConnType_MySQLi && $mysql->errno != 0)
                    || !$result)   ) {

               if ($this->m_connection_type == MySQL::ConnType_PDO) {
                  $this->writeLog($sql . "\r\nMySQL Error: " . $mysql->errorInfo() . " (" . $mysql->errorCode() . ")", $id . ".querys.error.log");
               }else if ($this->m_connection_type == MySQL::ConnType_MySQLi) {
                  $this->writeLog($sql . "\r\nMySQL Error: " . $mysql->error . " (" . $mysql->errno . ")", $id . ".querys.error.log");
               } else {
                  $this->writeLog($sql . "\r\nMySQL Error: " . mysql_error($mysql) . " (" . mysql_errno($mysql) . ")", $id . ".querys.error.log");
               }
            }

         if ($this->m_connection_type == MySQL::ConnType_PDO) {
            if ($mysql->errorCode() == 0) {
               if (is_object($result) && $result instanceof PDOStatement) {
                  $ret = $result->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_REL, $row);
                  $ret = $ret[0];
                  return $ret;
               }
            }
         }else if ($this->m_connection_type == MySQL::ConnType_MySQLi) {
            if ($mysql->error == 0) {
               if (is_object($result) && $result instanceof mysqli_result) {
                  $result->data_seek($row);
                  $ret = $result->fetch_array(MYSQLI_NUM);
                  $ret = $ret[0];
                  return $ret;
               }
            }
         } else {
            if (mysql_errno($mysql) == 0) {
               $ret = @mysql_result($result, $row);
               return $ret;
            }
         }

         return null;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * get double entrys of a field
       *
       * @param string $id
       * @param string $table
       * @param string $field
       * @param string $resfields
       * @param int    $fetch_type
       * @return MySQLResult
       */
      function doubleEnrty($id = "master", $table, $field, $resfields = "*", $fetch_type = MYSQL_BOTH) {
         if ($this->m_connection_type == MySQL::ConnType_MySQLi && $fetch_type == MYSQL_BOTH)
            $fetch_type = MYSQLI_BOTH;
         else if ($this->m_connection_type == MySQL::ConnType_PDO && $fetch_type == MYSQL_BOTH)
            $fetch_type = PDO::FETCH_BOTH;


         $this->setIntErr($id, 0);

         if (empty($id)) {
            $id = "master";
         }

         $this->m_last_serverid = $id;

         if (!isset($this->m_mysql[$id])) {
            $this->setIntErr($id, -99, var_dumpEx($id,true));
            $this->writeLog("doubleEnrty \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }
         $mysql = $this->m_mysql[$id];
         if (!$mysql) {
            $this->setIntErr($id, -100);
            $this->writeLog("doubleEnrty \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }

         $sql = 'SELECT ' . $resfields . ', COUNT(' . $field . ') AS double_entrys
              FROM ' . $table . ' GROUP BY ' . $field . ' HAVING count(' . $field . ') > 1';

         return $this->sql($id, $sql, $fetch_type);
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * auto increment a field for existing rows with secure readlock and anti deadlock, return all autoinc. values
       *
       * @param stirng $id
       * @param string $table
       * @param string $field
       * @param string $where
       * @param int $fetch_type
       * @param bool $decrement
       * @return mixed
       */
      function autoInc($id = "master", $table, $field, $where, $decrement = false) {
         $this->setIntErr($id, 0);

         if (empty($id)) {
            $id = "master";
         }

         $this->m_last_serverid = $id;

         if (!isset($this->m_mysql[$id])) {
            $this->setIntErr($id, -99, var_dumpEx($id,true));
            $this->writeLog("autoInc \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }
         $mysql = $this->m_mysql[$id];
         if (!$mysql) {
            $this->setIntErr($id, -100);
            $this->writeLog("autoInc \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }

         $operator = '+';
         if ($decrement) {
            $operator = '-';
         }

         $sql = 'SELECT `' . $field . '` ' . $operator . ' 1 AS inc FROM ' . $table . ' WHERE ' . $where . ' FOR UPDATE; ';
         $res = $this->sql($id, $sql);
         if ($this->getError($id) != 0) {
            return false;
         }
         if ($res->rows() == 0) {
            $this->setIntErr($id, -104);
            $this->writeLog("autoInc \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }

         $sql = 'UPDATE ' . $table . ' SET `' . $field . '` = `' . $field . '` ' . $operator . ' 1 WHERE ' . $where . ';';
         $ret = $this->sql($id, $sql);
         if ($this->getError($id) != 0) {
            return false;
         }

         if ($ret < $res->rows()) {
            $this->setIntErr($id, -105);
            $this->writeLog("autoInc \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }

         $inc = 0;
         if ($res->rows() > 1) {
            $inc = array();
            for ($r = 0; $r < $res->rows(); $r++) {
               $inc[] = $res->getField("inc");
            }
         } else {
            $inc = $res->getField("inc");
         }

         return $inc;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * auto increment a field for existing row or insert it with secure readlock and anti deadlock (needs a unique index), return the autoinc. value<br>you can cast a values using as sample:<br>MYSQL_CAST_INT.$val
       *
       * @param string $id
       * @param string $table
       * @param array $unique_field_val  => array("date"=>"2008-04-16", "id_user"=>"12345")
       * @param string $autoinc_field
       * @param array $additional_field_val  => array("text"=>"xxxxxxxx", "flag"=>"1") or array("text"=>MYSQL_CAST_CHAR."xxxxxxxx", "flag"=>MYSQL_CAST_INT."1")
       * @param bool $decrement
       * @param bool $escape_strings
       * @return int
       */
      function autoIncUnique($id = "master", $table, $unique_field_val, $autoinc_field, $additional_field_val = null, $decrement = false, $escape_strings = true) {
         $this->setIntErr($id, 0);

         if (empty($id)) {
            $id = "master";
         }

         $this->m_last_serverid = $id;

         if (!isset($this->m_mysql[$id])) {
            $this->setIntErr($id, -99, var_dumpEx($id,true));
            $this->writeLog("autoIncUnique \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }
         $mysql = $this->m_mysql[$id];
         if (!$mysql) {
            $this->setIntErr($id, -100);
            $this->writeLog("autoIncUnique \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }

         $where = "";
         reset($unique_field_val);
         while (list($field, $val) = each($unique_field_val)) {

            $type = 'U';
            $val = $this->cast($val, $type, $escape_strings);

            $this->buildField($where, $field, $val, $type);

            $where .= " AND ";
         }
         $where = substr($where, 0, strlen($where) - 5);

         $set = "";
         reset($unique_field_val);
         while (list($field, $val) = each($unique_field_val)) {
            $type = 'U';
            $val = $this->cast($val, $type, $escape_strings);

            $this->buildField($set, $field, $val, $type);
            $set .= ", ";
         }
         if (is_array($additional_field_val)) {
            reset($additional_field_val);
            while (list($field, $val) = each($additional_field_val)) {
               $type = 'U';
               $val = $this->cast($val, $type, $escape_strings);

               $this->buildField($set, $field, $val, $type);
               $set .= ", ";
            }
         }

         $operator = '+';
         if ($decrement) {
            $operator = '-';
         }

         $sql = 'SELECT `' . $autoinc_field . '` ' . $operator . ' 1 AS inc FROM ' . $table . ' WHERE ' . $where . ' FOR UPDATE; ';         
         $res = $this->sql($id, $sql);
         if ($this->getError($id) != 0) {
            return false;
         }

         $sql = 'INSERT ' . $table . ' SET ' . $set . '`' . $autoinc_field . '` = `' . $autoinc_field . '` ' . $operator . ' 1';
         $sql .= ' ON DUPLICATE KEY UPDATE';
         $sql .= ' ' . $set . '`' . $autoinc_field . '` = `' . $autoinc_field . '` ' . $operator . ' 1;';

         $ret = $this->sql($id, $sql);
         if ($this->getError($id) != 0) {
            return false;
         }

         if ($ret < $res->rows()) {
            $this->setIntErr($id, -105);
            $this->writeLog("autoInc \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }

         return $res->getField("inc");
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * Insert a row for a unique Index, if the unique value?s exsists it will be updated with secure readlock and anti deadlock (needs a unique index)<br>you can cast a values using as sample:<br>MYSQL_CAST_INT.$val
       *
       * @param string $id
       * @param string $table
       * @param array $unique_field_val  => array("date"=>"2008-04-16", "id_user"=>"12345") or array("date"=>MYSQL_CAST_DATETIME."2008-04-16", "id_user"=>MYSQL_CAST_INT."12345")
       * @param array $field_val  => array("text"=>"xxxxxxxx", "flag"=>"1") or array("text"=>MYSQL_CAST_CHAR."xxxxxxxx", "flag"=>MYSQL_CAST_INT."1")
       * @param bool $escape_strings
       * @return bool
       */
      function InsUpdUnique($id = "master", $table, $unique_field_val, $field_val, $escape_strings = true) {
         $this->setIntErr($id, 0);

         if (empty($id)) {
            $id = "master";
         }

         $this->m_last_serverid = $id;

         if (!isset($this->m_mysql[$id])) {
            $this->setIntErr($id, -99, var_dumpEx($id,true));
            $this->writeLog("InsUpdUnique \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }
         $mysql = $this->m_mysql[$id];
         if (!$mysql) {
            $this->setIntErr($id, -100);
            $this->writeLog("InsUpdUnique \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }


         $set1 = "";
         $set2 = "";

         reset($unique_field_val);
         while (list($field, $val) = each($unique_field_val)) {

            $type = 'U';
            $val = $this->cast($val, $type, $escape_strings);

            $this->buildField($set1, $field, $val, $type);

            $set1 .= ", ";
         }

         if (is_array($field_val)) {
            reset($field_val);
            while (list($field, $val) = each($field_val)) {
               $type = 'U';
               $val = $this->cast($val, $type, $escape_strings);

               $this->buildField($set2, $field, $val, $type);

               $set2 .= ", ";
            }
         }

         $sql = 'INSERT ' . $table . ' SET ' . substr($set1 . $set2, 0, strlen($set1 . $set2) - 2);
         $sql .= ' ON DUPLICATE KEY UPDATE ' . substr($set2, 0, strlen($set2) - 2);
         
         $this->sql($id, $sql);
         if ($this->getError($id) != 0) {
            return false;
         }

         return true;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * copy a whole table very high speed to a other (need high privileges)
       *
       * @param string $id
       * @param string $source
       * @param string $target
       * @param string $tmpfile
       * @param bool $tmptable create the target table temporary
       * @param bool $replace if true, input rows replace existing rows. In other words, rows that have the same value for a primary key or unique index as an existing row.
       * @return bool
       */
      function copyTable($id = "master", $source, $target, $tmpfile = '/tmp/copyTable.txt.foo', $tmptable = false, $replace = false) {
         $this->setIntErr($id, 0);

         if (empty($id)) {
            $id = "master";
         }

         $this->m_last_serverid = $id;

         if (!isset($this->m_mysql[$id])) {
            $this->setIntErr($id, -99, var_dumpEx($id,true));
            $this->writeLog("copyTable \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }
         $mysql = $this->m_mysql[$id];
         if (!$mysql) {
            $this->setIntErr($id, -100);
            $this->writeLog("copyTable \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }


         $res = $this->sql($id, "SHOW CREATE TABLE " . $source);
         if ($this->getError($id) != 0) {
            return false;
         }
         $create_stm = $res->getField('Create Table');

         $pos = strpos($create_stm, '(');
         if ($pos === false) {
            $this->setIntErr($id, -108);
            $this->writeLog("copyTable \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }
         $create_stm = substr($create_stm, $pos);
         $create_stm = "CREATE TABLE " . $target . " " . $create_stm;
         if ($replace) {
            $create_stm = str_replace('CREATE TABLE ', 'CREATE TABLE IF NOT EXISTS ', $create_stm);
         }
         if ($tmptable) {
            $create_stm = str_replace('CREATE TABLE ', 'CREATE TEMPORARY TABLE ', $create_stm);
         }

         $this->sql($id, "USE " . strcut($target, '.'));
         if ($this->getError($id) != 0) {
            return false;
         }

         $this->sql($id, $create_stm);
         if ($this->getError($id) != 0) {
            return false;
         }

         $this->sql($id, "SELECT * FROM " . $source . " INTO OUTFILE '" . $tmpfile . "'");
         if ($this->getError($id) != 0) {
            return false;
         }

         if ($replace) {
            $this->sql($id, "LOAD DATA INFILE '" . $tmpfile . "' REPLACE INTO TABLE " . $target);
         } else {
            $this->sql($id, "LOAD DATA INFILE '" . $tmpfile . "' INTO TABLE " . $target);
         }
         if ($this->getError($id) != 0) {
            return false;
         }

         return true;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * get the last X rows from a group by a refernce field
       *
       * @param string $id
       * @param string $table
       * @param string $groupfield
       * @param array $group_values
       * @param string $reffield
       * @param array $resfields
       * @param int $limit
       * @param int $fetch_type
       * @return MySQLResult
       */
      function getLastXFromGroup($id = "master", $table, $groupfield, $group_values, $reffield = "id", $resfields = "*", $limit = 1, $additional_where = "", $fetch_type = MYSQL_BOTH) {
         if ($this->m_connection_type == MySQL::ConnType_MySQLi && $fetch_type == MYSQL_BOTH)
            $fetch_type = MYSQLI_BOTH;
         else if ($this->m_connection_type == MySQL::ConnType_PDO && $fetch_type == MYSQL_BOTH)
            $fetch_type = PDO::FETCH_BOTH;


         $sql = "SELECT ";

         if (is_array($resfields)) {
            for ($f = 0; $f < count($resfields); $f++) {
               $sql .= $resfields[$f] . ", ";
            }
            $sql = substr($sql, 0, strlen($sql) - 2);
         } else {
            $sql .= "*";
         }

         if (!empty($additional_where)) {
            $additional_where = " AND " . $additional_where;
         }

         $sql .= " FROM " . $table . " AS t1";
         $sql .= " WHERE " . $groupfield;
         $sql .= " IN (" . implode(",", $group_values) . ")" . $additional_where;
         $sql .= " AND (";
         $sql .= "    SELECT COUNT(*) FROM " . $table . " AS t2";
         $sql .= "    WHERE t2." . $groupfield . " = t1." . $groupfield . " AND t2." . $reffield . " >= t1." . $reffield;
         $sql .= " ) <= " . $limit;

         return $this->sql($id, $sql);
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * copy a row, for copy also TEXT/BLOB fields a UUID field is required
       *
       * @param string $id
       * @param string $table
       * @param string $where
       * @param array $field_val overwite existing values in the copied row => array("text"=>"xxxxxxxx", "flag"=>"1")
       * @param $uuid_filed
       * @return bool
       */
      function copyRow($id = "master", $table, $where, $field_val = null, $uuid_filed = "") {
         $build = new MySQLBuilder(MYSQL_SELECT, $table, $where, "LIMIT 1");
         $res = $this->sql($id, $build);
         if ($this->getError($id) != 0) {
            return false;
         }

         if (!is_array($field_val)) {
            $field_val = array();
         }

         $build = new MySQLBuilder(MYSQL_INSERT, $table);

         $cfields = $res->fieldCount();
         for ($f = 0; $f < $cfields; $f++) {
            $name = $res->fieldName($f);
            $type = strtoupper($res->fieldType($f));
            $flags = $res->fieldFlags($f);
            $val = null;

            if ($type == "blob" || $type == "text") {
               $val = "";
            } else {
               if (isset($field_val[$name])) {
                  $val = $field_val[$name];
               } else {
                  if (strpos($flags, "auto_increment") !== false || $name == $uuid_filed) {
                     continue;
                  } else {
                     $val = $res->getField($name);
                  }
               }
            }
            $build->addValue($name, $val);
         }

         $uuid = null;
         if (!empty($uuid_filed)) {
            $uuid = $this->sqlUUID($id, $build, MYSQL_BOTH, $uuid_filed, null, $uuid_filed);
         } else {
            $this->sql($id, $build);
         }

         if ($this->getError($id) != 0) {
            return false;
         }

         if ($uuid !== null) {
            for ($f = 0; $f < $cfields; $f++) {
               $name = $res->fieldName($f);
               $type = strtoupper($res->fieldType($f));
               $val = null;

               if ($type == "blob" || $type == "text") {
                  if (isset($field_val[$name])) {
                     $val = $field_val[$name];
                  } else {
                     $val = $res->getField($name);
                  }
                  $this->sqlBLOB($id, $table, $name, $val, $uuid, $uuid_filed);
                  if ($this->getError($id) != 0) {
                     return false;
                  }
               }
            }
         }

         return true;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * Returns all values of a ENUM field
       *
       * @param string $id
       * @param string $table
       * @param string $field
       * @return array
       */
      function getEnumValues($id = "master", $table, $field) {
         return $this->getSetValues($id, $table, $field);
      }

//------------------------------------
      /**
       * Returns all values of a SET field
       *
       * @param string $id
       * @param string $table
       * @param string $field
       * @return array
       */
      function getSetValues($id = "master", $table, $field) {

         $sql = 'SHOW COLUMNS FROM ' . $table . ' LIKE "' . $field . '";';
         $res = $this->sql($id, $sql);
         if ($this->getError($id) != 0) {
            return false;
         }

         $fieldInfos = $res->getField('Type');

         $fieldInfos = strrcut($fieldInfos, '(', true);
         $fieldInfos = strcut($fieldInfos, ')', false);
         $fieldInfos = str_replace("'", '', $fieldInfos);

         return explode(',', $fieldInfos);
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * set server type for loadbalancing querys
       *
       * @param string $id
       * @param int $MYSQL_SERVER_TYPE
       * @return bool
       */
      function setServerType($id = "master", $MYSQL_SERVER_TYPE) {
         $this->setIntErr($id, 0);

         if (empty($id)) {
            $id = "master";
         }

         $this->m_last_serverid = $id;

         if (!isset($this->m_mysql[$id])) {
            $this->setIntErr($id, -99, var_dumpEx($id,true));
            $this->writeLog("setServerType \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }
         $mysql = $this->m_mysql[$id];
         if (!$mysql) {
            $this->setIntErr($id, -100);
            $this->writeLog("setServerType \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }


         if ($MYSQL_SERVER_TYPE == MYSQL_MASTER) {
            reset($this->m_mysql_type);
            for ($s = 0; $s < acount($this->m_mysql_type); $s++) {
               $key = key($this->m_mysql_type);
               $val = $this->m_mysql_type[$key];

               if ($val == MYSQL_MASTER) {
                  $this->m_mysql_type[$key] = MYSQL_SLAVE;
                  break;
               }

               next($this->m_mysql_type);
            }
            $this->m_mysql_type[$id] = MYSQL_MASTER;
            return true;
         } else if ($MYSQL_SERVER_TYPE == MYSQL_SLAVE) {
            $this->m_mysql_type[$id] = MYSQL_SLAVE;
            return true;
         }

         return false;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * set a list of tables or databases to a SALVE server to use this server for each SELECT statement called via "lbSQL" and "lbQSql"<br><br>Sample:<br>$mysql->setLbProxy("slave3", array('mydb1.table5','mydb3.*'));
       *
       * @param string $id
       * @param array $db_table_list
       * @return bool
       */
      function setLbProxy($id = 'slave1', $db_table_list) {
         $this->setIntErr($id, 0);

         if (empty($id)) {
            $id = "unknown";
         }

         if (!isset($this->m_mysql[$id])) {
            $this->setIntErr($id, -99, var_dumpEx($id,true));
            $this->writeLog("setServerType \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }
         $mysql = $this->m_mysql[$id];
         if (!$mysql) {
            $this->setIntErr($id, -100);
            $this->writeLog("setServerType \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }

         if (!isset($this->m_mysql_type[$id]) || $this->m_mysql_type[$id] != MYSQL_SLAVE) {
            $this->setIntErr($id, -107);
            $this->writeLog("setServerType \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }

         $this->m_mysql_lb_proxy[$id] = $db_table_list;

         return true;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * return MySQLResult or number of affected rows ("lbSql" need a started php session and privileges for PROCESSLIST and SLAVE STATUS)
       *
       * @param string $id_ret by reference
       * @param mixed $sql_query or $sql_builder_object
       * @param int $fetch_type
       * @param bool $forceMaster
       * @param bool $free_result    if false the result resource will be freed by garbage collector or should be freed by mysql_free_result manually
       * @return MySQLResult
       */
      function lbSql(&$id_ret, $sql, $fetch_type = MYSQL_BOTH, $forceMaster = false, $free_result = true) {
         if ($this->m_connection_type == MySQL::ConnType_MySQLi && $fetch_type == MYSQL_BOTH)
            $fetch_type = MYSQLI_BOTH;
         else if ($this->m_connection_type == MySQL::ConnType_PDO && $fetch_type == MYSQL_BOTH)
            $fetch_type = PDO::FETCH_BOTH;



         $id_ret = "unknown";
         $useMaster = true;

         if ((is_object($sql) && $sql->m_type == MYSQL_SELECT)
                 ||
                 (!is_object($sql) && strpos(strtoupper($sql), "SELECT") !== false  // found
                 && strpos(strtoupper($sql), "INSERT") === false // !found
                 && strpos(strtoupper($sql), "UPDATE") === false // !found
                 && strpos(strtoupper($sql), "DELETE") === false // !found
                 )
         ) {
            $useMaster = false;
         }

         if (!$this->m_lb_active) {
            $useMaster = true;
            free($_SESSION["CRVCL"]["MYSQL"]["OBJECTS"][$this->m_object_id]);
         }

         if ($forceMaster == true || $useMaster == true) {
            //echo "master query (lb:".  boolval($this->m_lb_active).")";
            $tcount = acount($this->m_mysql_type);
            reset($this->m_mysql_type);
            for ($s = 0; $s < $tcount; $s++) {
               $key = key($this->m_mysql_type);
               $val = $this->m_mysql_type[$key];
               //echo $key."/".$val.BR;
               if ($val == MYSQL_MASTER) {
                  $id_ret = $key;
                  return $this->sql($key, $sql, $fetch_type, $free_result);
               }
               next($this->m_mysql_type);
            }

            $this->setIntErr("unknown", "-103");
            $this->writeLog($sql . "\r\nMySQL Error: " . $this->getErrorMsg("unknown") . " (" . $this->getError("unknown") . ")", "unknown.querys.error.log");

            if ($this->m_show_warning) {
               trigger_error("mysql.lib.php warning, can't execute query (no server type set)", E_USER_WARNING);
            }

            return null;
         } else {
            //echo "slave query";
            $best_id = "";
            $round_robin_id = -1;
            $rescan = true;
            if (isset($_SESSION["CRVCL"]["MYSQL"]["OBJECTS"][$this->m_object_id])) {
               $this->m_lb_last_rescan = $_SESSION["CRVCL"]["MYSQL"]["OBJECTS"][$this->m_object_id]["LB_LAST_RESCAN"];
               $this->m_lb_last_best_server = $_SESSION["CRVCL"]["MYSQL"]["OBJECTS"][$this->m_object_id]["LAST_BEST_SERVER"];
               $round_robin_id = $_SESSION["CRVCL"]["MYSQL"]["OBJECTS"][$this->m_object_id]["ROUND_ROBIN_ID"];
            }

            $elapsed = doubleval(gettickcount()) - doubleval($this->m_lb_last_rescan);
            //echo "elapsed:".$elapsed.BR."rescan after:".$this->m_lb_rescan_servers_after_ms;
            if (!empty($this->m_lb_last_best_server) && $elapsed < $this->m_lb_rescan_servers_after_ms) {
               $rescan = false;
               $best_id = $this->m_lb_last_best_server;
            }

            if (!isset($this->m_mysql[$best_id])) { // session maybe currupt or overwritten from a other application with the same MySQL objeckt id, do rescan
               $rescan = true;
            }

            //echo "rescan:".boolToSetting($rescan).BR;
            if ($rescan) {
               $best_proc = 999999999;
               $best_qsec = 999999999;
               $round_robin_first_slave = "";
               $tcount = acount($this->m_mysql_type);

               
               if($this->m_lb_mode == MYSQL_LB_ROUND_ROBIN && $this->m_prefer_secondary){
                  reset($this->m_mysql_type);
                  for ($s = 0; $s < $tcount; $s++) {
                     $key = key($this->m_mysql_type);
                     $val = $this->m_mysql_type[$key];
                     
                     if($val == MYSQL_MASTER){
                        $res = $this->sql($key, "SELECT @@global.server_id AS sid");
                        if($this->getError($key) != 0) {
                           $this->m_primary_id = 0;
                           trigger_error("mysql.lib.php warning, can't check internal server id for " . $key . " (" . $this->getErrorMsg($key) . ")", E_USER_WARNING);
                           break;
                        }
                        $this->m_primary_id = $res->getField('sid');                        
                        free($res);
                        break;
                     }
                  }
               }

               
               if ($this->m_lb_mode == MYSQL_LB_ROUND_ROBIN && $round_robin_id == $tcount - 1) {
                  $round_robin_id = -1;
               }

               reset($this->m_mysql_type);
               for ($s = 0; $s < $tcount; $s++) {
                  $key = key($this->m_mysql_type);
                  $val = $this->m_mysql_type[$key];

                  if ($val == MYSQL_SLAVE) {
                     if ($this->m_lb_mode == MYSQL_LB_ROUND_ROBIN && !isset($_SESSION)) { // use load sharing on session error
                        $this->m_lb_mode == MYSQL_LB_LOAD_SHARING;
                     }

                     if ($this->m_lb_mode == MYSQL_LB_ROUND_ROBIN) {
                        if (empty($round_robin_first_slave)) {
                           $round_robin_first_slave = $key;
                        }

                        // if current server object not bigger then last id skip (not check, to increase performance)
                        if ($s <= $round_robin_id && $round_robin_id != $tcount - 1) {
                           next($this->m_mysql_type);
                           continue;
                        }
                     }

                     $this->m_notrace_next_query = true;
                     $res = $this->sql($key, "SHOW SLAVE STATUS");
                     if ($this->getError($key) == 0) {
                        $io_running = strtoupper($res->getField("Slave_IO_Running"));
                        $sql_running = strtoupper($res->getField("Slave_SQL_Running"));
                        $sec_behind = $res->getField("Seconds_Behind_Master");
                        //echo $key." ".$io_running." ".$sql_running." ".$sec_behind.BR;

                        if ($io_running == "YES" && $sql_running == "YES" && $sec_behind <= $this->m_lb_max_allowed_sec_behind_master) {
                           $mysql = &$this->m_mysql[$key];
                           if ($mysql) {

                              ###################################
                              # for LB Proxy
                              $proxy_found = false;
                              if (!empty($this->m_mysql_lb_proxy)) {
                                 $tmp_sql = $sql;
                                 if (is_object($sql)) {
                                    $tmp_sql = $sql->query();
                                 }
                                 $sql_words = $this->parseSqlWords($tmp_sql);

                                 $p = array_search("FROM", $sql_words);
                                 if ($p !== false) {
                                    $table_name = '';
                                    $db_name = $this->m_default_schema;
                                    if (isset($sql_words[$p + 1])) {
                                       $table_name = $sql_words[$p + 1];
                                    }
                                    if (strpos($table_name, '.') !== false) {
                                       $table_name = explode('.', $table_name);
                                       $db_name = $table_name[0];
                                       $table_name = $table_name[1];
                                    }

                                    reset($this->m_mysql_lb_proxy);
                                    while (list($key, $list) = each($this->m_mysql_lb_proxy)) {
                                       while (true) {
                                          $found = false;
                                          if (!empty($db_name)) {
                                             $found = array_isearch($db_name . '.*', $list);
                                             if ($found !== false) {
                                                $best_id = $key;
                                                $proxy_found = true;
                                                break 2;
                                             }

                                             $found = array_isearch($db_name . '.' . $table_name, $list);
                                             if ($found !== false) {
                                                $best_id = $key;
                                                $proxy_found = true;
                                                break 2;
                                             }
                                          }

                                          $found = array_isearch($table_name, $list);
                                          if ($found !== false) {
                                             $best_id = $key;
                                             $proxy_found = true;
                                             break 2;
                                          }
                                          // no proxy
                                          break;
                                       }
                                    }
                                 }
                              }
                              ###################################

                              if (!$proxy_found) {
                                 if ($this->m_lb_mode == MYSQL_LB_LOAD_SHARING) {
                                    $res = $this->sql($key, "SHOW PROCESSLIST");
                                    if ($this->getError($key) == 0) {
                                       $processes = 0;
                                       for ($r = 0; $r < $res->rows(); $r++) {
                                          if (trim($res->getField("Command")) != "Sleep" && trim($res->getField("State")) != "end") {
                                             $processes++;
                                          }
                                          $res->next();
                                       }
                                       $res->free();
                                       if ($processes < $best_proc) {
                                          $best_proc = $processes;
                                          $best_id = $key;
                                       }
                                    } else {
                                       
                                       $serverstat = null;
                                       if ($this->m_connection_type == MySQL::ConnType_MySQLi){
                                          $serverstat = explode('  ', $mysql->stat());
                                       }else if ($this->m_connection_type == MySQL::ConnType_PDO){
                                          $serverstat = explode('  ', $mysql->getAttribute(PDO::ATTR_SERVER_INFO));
                                       }else{
                                          $serverstat = explode('  ', mysql_stat($mysql));
                                       }

                                       $queriespersec = explode(":", $serverstat[7]);
                                       $queriespersec = trim($queriespersec[1]);
                                       //print $key." ".$queriespersec.BR.BR;
                                       if ($queriespersec < $best_qsec) {
                                          $best_qsec = $queriespersec;
                                          $best_id = $key;
                                       }
                                    }
                                 }// end load sharing     ----------------------------------------------------
                                 else if ($this->m_lb_mode == MYSQL_LB_ROUND_ROBIN) {
                                    if ($s > $round_robin_id) {

                                       if($this->m_prefer_secondary){                                          
                                          $res = $this->sql($key, "SELECT @@global.server_id AS sid");
                                          if($this->getError($key) != 0) {
                                             trigger_error("mysql.lib.php warning, can't check internal server id for " . $key . " (" . $this->getErrorMsg($key) . ")", E_USER_WARNING);
                                             break;
                                          }
                                          $sid = $res->getField('sid');
                                          

                                          if($sid != $this->m_primary_id){
                                             $best_id = $key;
                                             $round_robin_id = $s;
                                             break;
                                          }
                                       }else{
                                          $best_id = $key;
                                          $round_robin_id = $s;
                                          break;
                                       }
                                    }

                                    if ($round_robin_first_slave != $key && $s == $tcount - 1) { // restart on first server object
                                       reset($this->m_mysql_type);
                                       $s = 0;
                                       $round_robin_first_slave = "";
                                       $round_robin_id = -1;
                                       continue;
                                    }
                                 }// end round robin
                                 else if ($this->m_create_slave_error_log == true) {
                                    $error = "unknown lb mode";
                                    $this->writeLog($error, $key . ".error.log");
                                    if ($best_id == $key) {
                                       $best_id = "";
                                    }
                                 }
                              } // proxy found
                           } // if mysql object
                        } else if ($this->m_create_slave_error_log == true) {
                           $error = "MySQL Slave Error:\r\nSlave_IO_Running - "
                                   . $io_running . ", Slave_SQL_Running - "
                                   . $sql_running . ", Seconds_Behind_Master - " . $sec_behind;
                           $this->writeLog($error, $key . ".error.log");
                           if ($best_id == $key) {
                              $best_id = "";
                           }
                        } else {
                           if ($best_id == $key) {
                              $best_id = "";
                           }
                        }
                     } else if ($this->m_show_warning) {
                        trigger_error("mysql.lib.php warning, can't check slave status for " . $key . " (" . $this->getErrorMsg($key) . ")", E_USER_WARNING);
                     }
                  } // if SLAVE
                  next($this->m_mysql_type);
               }
               $this->m_lb_last_rescan = doubleval(gettickcount());
               $this->m_lb_last_best_server = $best_id;
               //echo "best_id: ".$best_id.BR;
               //echo "object id: ".$this->m_object_id.BR;
               //print_d($_SESSION["CRVCL"]["MYSQL"]["OBJECTS"]);
               if (isset($_SESSION)) {
                  if (!isset($_SESSION["CRVCL"]["MYSQL"]["OBJECTS"][$this->m_object_id])) {
                     $_SESSION["CRVCL"]["MYSQL"]["OBJECTS"][$this->m_object_id] = array();
                  }

                  $connect_info = &$this->m_mysql_connect_info;

                  // debug only
                  /*if($_SESSION["user"]["id"]==16923){
                     var_dump($connect_info);
                     var_dump($best_id);
                     var_dump($connect_info[$best_id]['host']);
                  }*/
                  /*if(defined('debugtest')){
                     echo BR.$this->m_object_id.BR;
                     print_r($_SESSION);
                     print_r($connect_info);
                     echo "b: ".$best_id.BR;
                  }*/

                  if($best_id != ""){
                     $_SESSION["CRVCL"]["MYSQL"]["OBJECTS"][$this->m_object_id]["LB_LAST_RESCAN"] = $this->m_lb_last_rescan;
                     $_SESSION["CRVCL"]["MYSQL"]["OBJECTS"][$this->m_object_id]["LAST_BEST_SERVER"] = $this->m_lb_last_best_server;
                     $_SESSION["CRVCL"]["MYSQL"]["OBJECTS"][$this->m_object_id]["ROUND_ROBIN_ID"] = $round_robin_id;
                     $_SESSION["CRVCL"]["MYSQL"]["OBJECTS"][$this->m_object_id]["LAST_BEST_SERVER_IP"] = $connect_info[$best_id]['host'];
                     $_SESSION["CRVCL"]["MYSQL"]["OBJECTS"][$this->m_object_id]["LAST_BEST_SERVER_PORT"] = $connect_info[$best_id]['port'];
                  }else{ // reset if lb fails
                     $_SESSION["CRVCL"]["MYSQL"]["OBJECTS"][$this->m_object_id] = array();
                  }
                 

               } else if ($this->m_show_warning) {
                  trigger_error("mysql.lib.php warning, can't enable loadbalancing - SESSION required", E_USER_WARNING);
               }
            }// if rescan
            //echo "best id:".$best_id.BR;
            if ($best_id != "") {
               $id_ret = $best_id;

               $ret = $this->sql($best_id, $sql, $fetch_type, $free_result);

               $errno = $this->getError($best_id);

               # !!! see also sql !!!
               // 2006 => MySQL server has gone away
               // 2011 => %s via TCP/IP
               // 2013 => Lost connection to MySQL server during query
               // 1040 => Too many connections
               // 1053 => Server shutdown in progress
               // 1152 => Aborted connection %ld to db: '%s' user: '%s' (%s)
               // 1184 => Aborted connection %ld to db: '%s' user: '%s' host: '%s' (%s)
               // 1203 => User %s already has more than 'max_user_connections' active connections
               // 1205 => Lock wait timeout expired.
               // 1213 => Deadlock found when trying to get lock; try restarting transaction
               // 2048 => Invalid connection handle
               // 2055 => Lost connection to MySQL server at '%s', system error: %d
               // retry only allowed for no or single transaction in lb mode
               if ((!isset($this->m_transaction[$best_id]) || $this->m_transaction[$best_id][0] === false
                       || $this->m_transaction[$best_id][3] <= 1)
               ) {
                  if ($errno == 2013 || $errno == 2011 || $errno == 2006 ||
                          $errno == 1040 || $errno == 1053 ||
                          $errno == 1152 || $errno == 1184 || $errno == 1203 || $errno == 1205 || $errno == 1213 ||
                          $errno == 2048 || $errno == 2055) {

                     if (isset($_SESSION["CRVCL"]["MYSQL"]["OBJECTS"][$this->m_object_id])) {
                        $_SESSION["CRVCL"]["MYSQL"]["OBJECTS"][$this->m_object_id]["LB_LAST_RESCAN"] = 0;
                        $_SESSION["CRVCL"]["MYSQL"]["OBJECTS"][$this->m_object_id]["LAST_BEST_SERVER"] = "";
                        $_SESSION["CRVCL"]["MYSQL"]["OBJECTS"][$this->m_object_id]["ROUND_ROBIN_ID"] = -1;
                     }

                     return $this->lbSql($id_ret, $sql, $fetch_type, true, $free_result); // use master
                  }
               }
               return $ret;
            } else {// use master, if no slaves available
               if ($this->m_show_warning) {
                  trigger_error("mysql.lib.php warning, no slave for loadbalancing available (no server set as slave or slave status error)", E_USER_WARNING);
               }
               return $this->lbSql($id_ret, $sql, $fetch_type, true, $free_result);
            }
            // no return needed
         }
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * return MySQLResult or number of affected rows (use OpcodeCache for SELECT statements, require APC)
       *
       * @param string $id internal / id for server identification
       * @param mixed $sql_query or $sql_builder_object
       * @param int ttl for the OpcodeCache
       * @param int $fetch_type
       * @param bool $free_result    if false the result resource will be freed by garbage collector or should be freed by mysql_free_result manually
       * @return MySQLResult
       */
      function qSql($id = "master", $sql, $ttl = 1, $fetch_type = MYSQL_BOTH, $free_result = true) {
         if ($this->m_connection_type == MySQL::ConnType_MySQLi && $fetch_type == MYSQL_BOTH)
            $fetch_type = MYSQLI_BOTH;
         else if ($this->m_connection_type == MySQL::ConnType_PDO && $fetch_type == MYSQL_BOTH)
            $fetch_type = PDO::FETCH_BOTH;


         $this->m_apc_cache_ttl = $ttl;
         $ret = $this->sql($id, $sql, $fetch_type, $free_result);
         $this->m_apc_cache_ttl = -1;
         return $ret;
      }

//-------------------------------------------------------------------------------------------------------------------------------------	
      /**
       * return MySQLResult (use OpcodeCache for SELECT statements, require APC) or number of affected rows<br><br>("lbSql" need a started php session and privileges for PROCESSLIST and SLAVE STATUS)
       *
       * @param string $id_ret by reference
       * @param mixed $sql_query or $sql_builder_object
       * @param int ttl for the OpcodeCache
       * @param int $fetch_type
       * @param bool $forceMaster
       * @param bool $free_result    if false the result resource will be freed by garbage collector or should be freed by mysql_free_result manually
       * @return MySQLResult
       */
      function lbQSql(&$id_ret, $sql, $ttl = 1, $fetch_type = MYSQL_BOTH, $forceMaster = false, $free_result = true) {
         if ($this->m_connection_type == MySQL::ConnType_MySQLi && $fetch_type == MYSQL_BOTH)
            $fetch_type = MYSQLI_BOTH;
         else if ($this->m_connection_type == MySQL::ConnType_PDO && $fetch_type == MYSQL_BOTH)
            $fetch_type = PDO::FETCH_BOTH;


         $this->m_apc_cache_ttl = $ttl;

         #################################
         // not required, but faster than check the APC in the method "sql", because loadbalancing is skipped
         $apc_key = '';
         $oc = null;

         if ($this->m_apc_cache_ttl > -1 && isset($_SESSION["CRVCL"]["MYSQL"]["OBJECTS"][$this->m_object_id]["LAST_BEST_SERVER"]) &&
                 (
                 (is_object($sql) && $sql->m_type == MYSQL_SELECT)
                 ||
                 (!is_object($sql) && strpos(strtoupper($sql), "SELECT") !== false  // found
                 && strpos(strtoupper($sql), "INSERT") === false // !found
                 && strpos(strtoupper($sql), "UPDATE") === false // !found
                 && strpos(strtoupper($sql), "DELETE") === false // !found
                 )
                 )
         ) {
            $oc = $this->getOpcodeCache();
            if ($oc != null) {
               $id = $_SESSION["CRVCL"]["MYSQL"]["OBJECTS"][$this->m_object_id]["LAST_BEST_SERVER"];
               $query_string = &$sql;
               if (is_object($query_string)) {
                  $query_string = $sql->query();
               }
               $apc_key = 'MySQL_Cache:'.md5($id) . ':' . hash('sha256', $query_string);

               $qstart = gettickcount();
               $ret = $oc->fetch($apc_key, $success);
               if ($success && is_object($ret) && $ret instanceof MySQLResult) {
                  $oc->add($apc_key, $ret, $this->m_apc_cache_ttl); // restore with new ttl
                  $qend = gettickcount();
                  $this->m_last_result_source = MYSQL_RESULT_SOURCE_CACHE;
                  $this->m_last_query_ms = $qend - $qstart;
                  $this->writeTrace($sql, 'internal query cache'); // trace the query
                  return $ret;
               }
            }
         }
         #################################

         $ret = $this->lbSql($id_ret, $sql, $fetch_type, $forceMaster, $free_result);
         $this->m_apc_cache_ttl = -1;
         return $ret;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * return a array with keys IO_RUNNING, SQL_RUNNING, SEC_BEHIND, LAST_ERROR
       *
       * @param string $id
       * @return array
       */
      function checkSlaveStatus($id = "master") {
         $this->setIntErr($id, 0);

         if (empty($id)) {
            $id = "master";
         }

         $this->m_last_serverid = $id;

         if (!isset($this->m_mysql[$id])) {
            $this->setIntErr($id, -99, var_dumpEx($id,true));
            $this->writeLog("checkSlaveStatus \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return array();
         }
         $mysql = $this->m_mysql[$id];
         if (!$mysql) {
            $this->setIntErr($id, -100);
            $this->writeLog("checkSlaveStatus \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return array();
         }

         $res = $this->sql($id, "SHOW SLAVE STATUS");
         if ($this->getError($id) != 0) {
            return array();
         }

         $io_running = strtoupper($res->getField("Slave_IO_Running"));
         $sql_running = strtoupper($res->getField("Slave_SQL_Running"));
         $sec_behind = $res->getField("Seconds_Behind_Master");
         $last_error = $res->getField("Last_Error");

         return array("IO_RUNNING" => $io_running,
             "SQL_RUNNING" => $sql_running,
             "SEC_BEHIND" => $sec_behind,
             "LAST_ERROR" => $last_error);
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * return a array with importent information of the server
       *
       * @param string $id
       * @return array
       */
      function checkStatus($id = "master") {
         $this->setIntErr($id, 0);

         if (empty($id)) {
            $id = "master";
         }

         $this->m_last_serverid = $id;

         if (!isset($this->m_mysql[$id])) {
            $this->setIntErr($id, -99, var_dumpEx($id,true));
            $this->writeLog("checkStatus \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return array();
         }
         $mysql = $this->m_mysql[$id];
         if (!$mysql) {
            $this->setIntErr($id, -100);
            $this->writeLog("checkStatus \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return array();
         }

         $res = $this->sql($id, "SHOW FULL PROCESSLIST");
         if ($this->getError($id) != 0) {
            return array();
         }

         $serverstate = "MASTER";

         $processes = 0;
         $hasMasterThreads = false;
         $hasSlaveThreads = false;
         // source:  http://dev.mysql.com/doc/refman/5.1/de/master-thread-states.html
         for ($r = 0; $r < $res->rows(); $r++) {
            if (trim($res->getField("State")) == "Waiting for master to send event"
                    || trim($res->getField("State")) == "Reconnecting after a failed master event read"
                    || trim($res->getField("State")) == "Has read all relay log; waiting for the slave I/O thread to update it") {
               $hasSlaveThreads = true;
            }
            if (trim($res->getField("State")) == "Has sent all binlog to slave; waiting for binlog to be updated"  // before MySQL 5.5
               || trim($res->getField("State")) == "Master has sent all binlog to slave; waiting for binlog to be updated"   ){ // since MySQL 5.5
               $hasMasterThreads = true;
            }

         if(trim($res->getField("Command")) != "Sleep" && trim($res->getField("State")) != "end" && trim($res->getField("Command")) != "Killed"){
            $processes++;
         }
         $res->next();
      }
      $res->free();

         if ($hasSlaveThreads) {
            $serverstate = "SLAVE";
            if ($hasMasterThreads) {
               $serverstate = "MULTIMASTER";
            }
         }

         ///////

         $res = $this->sql($id, "SHOW STATUS");
         if ($this->getError($id) != 0) {
            return array();
         }

         $threads = -1;
         $connections = -1;
         $opentabels = -1;
         $locks_immediate = -1;
         $locks_waited = -1;

         for ($r = 0; $r < $res->rows(); $r++) {
            $varname = strtolower(trim($res->getField("Variable_name")));
            $val = strtolower(trim($res->getField("Value")));

            if ($varname == "threads_running") {
               $threads = $val;
            }

            if ($varname == "threads_connected") {
               $connections = $val;
            }

            if ($varname == "open_tables") {
               $opentabels = $val;
            }

            if ($varname == "table_locks_immediate") {
               $locks_immediate = $val;
            }

            if ($varname == "table_locks_waited") {
               $locks_waited = $val;
            }


            $res->next();
         }

         $res->free();

         $lockstate = -1;
         if ($locks_waited > -1 && $locks_immediate > -1) {
            $lockstate = doubleval($locks_waited) / (doubleval($locks_immediate) / 100);
            $lockstate = round($lockstate, 2);
         }

         ///////

         $res = $this->sql($id, "SELECT @@global.server_id AS sid");
         if ($this->getError($id) != 0) {
            return array();
         }
         $server_id = $res->getField("sid");

         ///////

         $serverstat = null;
         if ($this->m_connection_type == MySQL::ConnType_MySQLi){
            $serverstat = explode('  ', $mysql->stat());
         }else if ($this->m_connection_type == MySQL::ConnType_PDO){
            $serverstat = explode('  ', $mysql->getAttribute(PDO::ATTR_SERVER_INFO));
         }else{
            $serverstat = explode('  ', mysql_stat($mysql));
         }
         
         
         $uptime = explode(":", $serverstat[0]);
         $uptime = trim($uptime[1]);

         $queriespersec = explode(":", $serverstat[7]); // queries / uptime = queries per sec. avg.
         $queriespersec = trim($queriespersec[1]);

         $slowquerys = explode(":", $serverstat[3]);
         $slowquerys = trim($slowquerys[1]);

         return array("PROCESSES" => $processes,
             "UPTIME" => $uptime,
             "THREADS" => $threads,
             "OPENTABELS" => $opentabels,
             "QUERYSPERSEC" => $queriespersec,
             "QUERIESSPERSEC" => $queriespersec,
             "SLOWQUERYS" => $slowquerys,
             "CONNECTIONS" => $connections,
             "LOCKSTATE" => $lockstate,
             "SERVERSTATE" => $serverstate,
             "SERVERID" => $server_id);
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * get the load of queries per seconds on the server<br><br><b>ATTENTION, if you set the parameter average to false,<br>it calculates the current load of queries per seconds,<br>but this take minimum 1 second of calculation time</b>
       *
       * @param string $id
       * @param bool $average
       * @return numeric
       */
      function queriesPerSec($id = "master", $average = true) {
         $this->setIntErr($id, 0);

         if (empty($id)) {
            $id = "master";
         }

         $this->m_last_serverid = $id;

         if (!isset($this->m_mysql[$id])) {
            $this->setIntErr($id, -99, var_dumpEx($id,true));
            $this->writeLog("queriesPerSec \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }
         $mysql = $this->m_mysql[$id];
         if (!$mysql) {
            $this->setIntErr($id, -100);
            $this->writeLog("queriesPerSec \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }

         $queriespersec = 0;
         if ($average) {

            $serverstat = null;
            if ($this->m_connection_type == MySQL::ConnType_MySQLi){
               $serverstat = explode('  ', $mysql->stat());
            }else if ($this->m_connection_type == MySQL::ConnType_PDO){
               $serverstat = explode('  ', $mysql->getAttribute(PDO::ATTR_SERVER_INFO));
            }else{
               $serverstat = explode('  ', mysql_stat($mysql));
            }

            $queriespersec = explode(":", $serverstat[7]); // queries / uptime = queries per sec. avg.
            $queriespersec = trim($queriespersec[1]);
         } else {


            $res = mysql_query("SHOW STATUS WHERE VARIABLE_NAME LIKE 'QUERIES';", $mysql);
            if (mysql_errno() != 0) {
               $this->setIntErr($id, -106);
               $this->writeLog("queriesPerSec \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
               return false;
            }

            $ret = mysql_fetch_row($res);

            $q1 = $ret[1];

            mssleep(990);

            $res = mysql_query("SHOW STATUS WHERE VARIABLE_NAME LIKE 'QUERIES';", $mysql);
            if (mysql_errno() != 0) {
               $this->setIntErr($id, -106);
               $this->writeLog("queriesPerSec \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
               return false;
            }

            $ret = mysql_fetch_row($res);

            $q2 = $ret[1];

            $queriespersec = ($q2 - $q1);
         }
         return $queriespersec;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * set the transaction isolation level for the next transaction,<br>for more see http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html<br>!!! InnoDB only !!! For READ COMMITTED/READ UNCOMMITED maybe also required set binlog-format=ROW/MIXED (this is possible since MySQL 5.1.8 on runtime, you must have the SUPER privilege. This is also true for the session value as of MySQL 5.1.29) !!!
       *
       * @param string $id
       * @param string $MYSQL_TRANSACTION_ISOLATION_LEVEL => MYSQL_TRANS_ISO_LEV_READ_UNCOMMITTED, MYSQL_TRANS_ISO_LEV_READ_COMMITTED, MYSQL_TRANS_ISO_LEV_REPEATABLE_READ, MYSQL_TRANS_ISO_LEV_SERIALIZABLE
       * @param string $subsequent => empty string for next transaction, SESSION, GLOBAL
       * @return bool
       */
      function setTransactionIsolationLevel($id = "master", $MYSQL_TRANSACTION_ISOLATION_LEVEL, $subsequent = "") {
         $this->setIntErr($id, 0);

         if (empty($id)) {
            $id = "master";
         }

         $this->m_last_serverid = $id;

         if (!isset($this->m_mysql[$id])) {
            $this->setIntErr($id, -99, var_dumpEx($id,true));
            $this->writeLog("setTransactionIsolationLevel \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }
         $mysql = $this->m_mysql[$id];
         if (!$mysql) {
            $this->setIntErr($id, -100);
            $this->writeLog("setTransactionIsolationLevel \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }

         $ret = $this->sqlGetField($id, "SELECT @@session.binlog_format AS format");
         if (strtoupper($ret) == 'STATEMENT'
                 && ($MYSQL_TRANSACTION_ISOLATION_LEVEL == MYSQL_TRANS_ISO_LEV_READ_COMMITTED || $MYSQL_TRANSACTION_ISOLATION_LEVEL == MYSQL_TRANS_ISO_LEV_READ_UNCOMMITTED)) {
            return false;
         }

         if ($subsequent != "") {
            $subsequent .= " ";
         }

         $this->sql($id, "SET " . $subsequent . "TRANSACTION ISOLATION LEVEL " . $MYSQL_TRANSACTION_ISOLATION_LEVEL);
         if ($this->getError($id) != 0) {
            return false;
         }

         $ret = '';
         if (strtoupper($subsequent) == 'GLOBAL') {
            $ret = $this->sqlGetField($id, "SELECT @@GLOBAL.tx_isolation AS TXI");
         } else {
            $ret = $this->sqlGetField($id, "SELECT @@tx_isolation AS TXI");
         }
         $ret = strtoupper(trim_specialchars($ret, ' '));

         if ($ret == $MYSQL_TRANSACTION_ISOLATION_LEVEL)
            return true;

         return false;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * start a transaction and disable the auto commit (only for storage engines support transaction)
       *
       * @param string $id
       * @return bool
       */
      function begin($id = "master") {
         $this->setIntErr($id, 0);

         if (empty($id)) {
            $id = "master";
         }

         $this->m_last_serverid = $id;

         if (!isset($this->m_mysql[$id])) {
            $this->setIntErr($id, -99, var_dumpEx($id,true));
            $this->writeLog("begin \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }
         $mysql = $this->m_mysql[$id];
         if (!$mysql) {
            $this->setIntErr($id, -100);
            $this->writeLog("begin \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }

         if (isset($this->m_transaction[$id]) && $this->m_transaction[$id][0] == true) {
            $this->commit($id);
         }

         $lastautocommit = 1;
         $res = $this->sql($id, "SELECT @@autocommit AS val");
         if ($this->getError($id) != 0) {
            $lastautocommit = $res->getField("val");
         }

         $this->sql($id, "SET SESSION AUTOCOMMIT = 0");

         $this->sql($id, "BEGIN");

         $this->m_transaction[$id] = array(true, $lastautocommit, gettickcount(), 0);

         return true;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * rollback a transaction and set the last auto commit state (only for storage engines support transaction)
       *
       * @param string $id
       * @return bool
       */
      function rollback($id = "master") {
         $this->setIntErr($id, 0);

         if (empty($id)) {
            $id = "master";
         }

         $this->m_last_serverid = $id;

         if (!isset($this->m_mysql[$id])) {
            $this->setIntErr($id, -99, var_dumpEx($id,true));
            $this->writeLog("rollback \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }
         $mysql = $this->m_mysql[$id];
         if (!$mysql) {
            $this->setIntErr($id, -100);
            $this->writeLog("rollback \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }

         if (!isset($this->m_transaction[$id]) || $this->m_transaction[$id][0] != true) {
            return false;
         }

         $this->sql($id, "ROLLBACK");

         if (isset($this->m_transaction[$id][1]) && $this->m_transaction[$id][1] !== null) {
            $this->sql($id, "SET SESSION AUTOCOMMIT = " . $this->m_transaction[$id][1]);
         }

         $this->m_transaction[$id][0] = false;

         return true;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * commit a transaction and set the last auto commit state (only for storage engines support transaction)
       *
       * @param string $id
       * @return bool
       */
      function commit($id = "master") {
         $this->setIntErr($id, 0);

         if (empty($id)) {
            $id = "master";
         }

         $this->m_last_serverid = $id;

         if (!isset($this->m_mysql[$id])) {
            $this->setIntErr($id, -99, var_dumpEx($id,true));
            $this->writeLog("commit \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }
         $mysql = $this->m_mysql[$id];
         if (!$mysql) {
            $this->setIntErr($id, -100);
            $this->writeLog("commit \r\nMySQL Error: " . $this->getErrorMsg($id) . " (" . $this->getError($id) . ")", $id . ".querys.error.log");
            return false;
         }

         if (!isset($this->m_transaction[$id]) || $this->m_transaction[$id][0] != true) {
            return false;
         }

         $this->sql($id, "COMMIT");

         if (isset($this->m_transaction[$id][1]) && $this->m_transaction[$id][1] !== null) {
            $this->sql($id, "SET SESSION AUTOCOMMIT = " . $this->m_transaction[$id][1]);
         }

         $this->m_transaction[$id][0] = false;

         return true;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * lock a table in a own transaction
       *
       * @param string $id
       * @param string $table
       * @param string $MYSQL_LOCK
       * @return bool
       */
      function lock($id = "master", $table, $MYSQL_LOCK) {
         if (!$this->begin($id))
            return false;

         $this->sql($id, "LOCK TABLES " . $table . " " . $MYSQL_LOCK);
         if ($this->getError($id) != 0)
            return false;

         return true;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * unlock the last locked table and commit (rollback) the transaction
       *
       * @param string $id
       * @return bool
       */
      function unlock($id = "master", $rollback = false) {

         $this->sql($id, "UNLOCK TABLES");
         if ($this->getError($id) != 0) {
            $this->rollback($id);
            return false;
         }

         if ($rollback) {
            if (!$this->rollback($id))
               return false;
         }else {
            if (!$this->commit($id))
               return false;
         }

         return true;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * create like a critical section for the given mysql host, useful for application locks or to simulate record locks
       *
       * @param string $id
       * @param string $lockid
       * @param int $max_lock_s
       * @return bool
       */
      function lockX($id = "master", $lockid, $max_lock_s = null) {
         if($max_lock_s === null){
            $max_lock_s = intval(ini_get('max_execution_time'));
            if($max_lock_s < 1){
               $max_lock_s = 1;
            }
         }
         $res = $this->sql($id, "SELECT GET_LOCK('" . $lockid . "', " . $max_lock_s . ") AS locked");
         if ($this->getError($id) != 0 || $res->getField('locked') != 1) {
            return false;
         }
         return true;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * release the critical section on the given mysql host
       *
       * @param string $id
       * @param string $lockid
       * @return bool
       */
      function unlockX($id = "master", $lockid) {
         $res = $this->sql($id, "SELECT RELEASE_LOCK('" . $lockid . "') AS unlocked");
         if ($this->getError($id) != 0 || $res->getField('unlocked') != 1) {
            return false;
         }
         return true;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * check a critical section is set for the given mysql host
       *
       * @param string $id
       * @param string $lockid
       * @return bool
       */
      function is_lockedX($id = "master", $lockid) {
         $res = $this->sql($id, "SELECT IS_USED_LOCK('" . $lockid . "') AS ClientId");
         if ($this->getError($id) != 0 && $res->rows() > 0) {
            return true;
         }
         return false;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
   }

//-------------------------------------------------------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------------------------------------------------------
   class MySQLResult {

      private $m_result = null;
      private $m_row_pointer = 0;
      private $m_field_names = array();
      private $m_field_info = array();
      private $m_free_result_resource = true;
      private $m_mysql_resource = null;
      private $m_fetch_type = null;
      private $m_data = null;
      private $m_rows = null;

//-------------------------------------------------------------------------------------------------------------------------------------
// constructor
      /**
       * mysql result as object
       *
       * @param resource $result
       * @param int $fetch_type
       * @param bool $free_result    if false the result resource will be freed by garbage collector or should be freed by mysql_free_result manually
       * @return MySQLResult
       */
      function __construct($result = null, $fetch_type = MYSQL_BOTH, $free_result = true) {
         if (is_object($result) && $result instanceof PDOStatement && $fetch_type == MYSQL_BOTH){
            $fetch_type = PDO::FETCH_BOTH;            
         }else if (is_object($result) && $result instanceof mysqli_result && $fetch_type == MYSQL_BOTH){
            $fetch_type = MYSQLI_BOTH;          
         }


         $this->m_free_result_resource = $free_result;
         $this->m_result = $result;
         $this->m_fetch_type = $fetch_type;

         if ($this->m_result && is_resource($this->m_result)) {
            if ($this->rows() > 0) {
               $fcount = mysql_num_fields($this->m_result);

               for ($f = 0; $f < $fcount; $f++) {
                  $fname = mysql_field_name($this->m_result, $f);
                  $this->m_field_names += array($fname => $f);
                  $info = array("name" => $fname,
                      "type" => mysql_field_type($this->m_result, $f),
                      "len" => mysql_field_len($this->m_result, $f),
                      "flags" => mysql_field_flags($this->m_result, $f)
                  );
                  $this->m_field_info[] = $info;
               }
            }

            //echo "fnames:".BR; print_d($this->m_field_names); echo BR;
            //echo "finfo:".BR; print_d($this->m_field_info); echo BR;
         }

         if (is_object($result) && $result instanceof PDOStatement) {            
            if ($result->rowCount() > 0) {
               $fcount = $result->columnCount();
               
               for ($f = 0; $f < $fcount; $f++) {
                   $meta = $result->getColumnMeta($f);
                           
                   $fname = $meta['name'];
                   $this->m_field_names += array($fname => $f);
                   $info = array("name" => $fname,
                      "type" => $meta['pdo_type'],
                      "len" => $meta['len'],
                      "flags" => $meta['flags']
                   );
                   $this->m_field_info[] = $info;        
               }        
            }

            #######################
            # scrollable cursor not supported this time by MySQL, result will be fetched direct to the heap mem of the object
            $this->m_data = $this->m_result->fetchAll($this->m_fetch_type);
            # free immediately the object of PDOStatement, because of serialize/unserialize problems and to save memory
            free($this->m_result);
            #######################
         }

         if (is_object($result) && $result instanceof mysqli_result) {
            if ($result->num_rows > 0) {
               $fcount = $result->field_count;


               $finfo = $result->fetch_fields();

               $f = 0;
               foreach ($finfo as $val) {
                  $fname = $val->name;
                  $this->m_field_names += array($fname => $f);

                  $info = array("name" => $fname,
                      "type" => $val->type,
                      "len" => $val->max_length,
                      "flags" => $val->flags
                  );
                  $this->m_field_info[] = $info;

                  $f++;
               }
            }
         }
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      function __destruct() {
         $this->free();
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * put the complete result to the internal object memory (take more mem, but make the object cacheable)
       *
       */
      function toHeap() {
         $connType = MySQL::ConnType_MySQL;
         if (is_object($this->m_result) && $this->m_result instanceof PDOStatement)
            $connType = MySQL::ConnType_PDO;
         else if (is_object($this->m_result) && $this->m_result instanceof mysqli_result)
            $connType = MySQL::ConnType_MySQLi;

         if ($this->m_data != null)
            return;

         $fcount = $this->fieldCount();

         $data = array();
         for ($r = 0; $r < $this->rows(); $r++) {
            $fieldvals = array();
            for ($f = 0; $f < $fcount; $f++) {
               $fname = $this->fieldName($f);
               
               $fval = $this->getField($fname);
               $len_fval = strlen($fval);

               if ($len_fval > str2byteInt("4K")) {
                  $compress = 4;
                  if ($len_fval > str2byteInt("18k")) {
                     $compress = 5;
                  } else if ($len_fval > str2byteInt("25k")) {
                     $compress = 6;
                  }
                  $fval = "gz:" . gzcompress($fval, $compress);
               }

               $fieldvals[$fname] = $fval;
            }

            $data[] = $fieldvals;
            $this->next();
         }
         $this->m_data = &$data;
         $this->first();

         if ($this->m_free_result_resource) {
            if ($connType == MySQL::ConnType_PDO) {
               $this->m_result->closeCursor();
            }else if ($connType == MySQL::ConnType_MySQLi) {
               $this->m_result->free();
            } else {
               mysql_free_result($this->m_result);
            }
            $this->m_result = null;
         }
      }

//-------------------------------------------------------------------------------------------------------------------------------------
// private
      function free() {
         if (isset($this->m_result) && $this->m_result && is_resource($this->m_result)) {
            if ($this->m_free_result_resource) {
               mysql_free_result($this->m_result);
            }
         }
         if (isset($this->m_result) && is_object($this->m_result) && $this->m_result instanceof mysqli_result) {
            if ($this->m_free_result_resource) {
               $this->m_result->free();
            }
         }
         if (isset($this->m_result) && is_object($this->m_result) && $this->m_result instanceof PDOStatement) {
            if ($this->m_free_result_resource) {
               $this->m_result->closeCursor();
            }
         }

         free($this->m_result);

         free($this->m_field_info);
         free($this->m_field_names);

         free($this->m_row_pointer);

         free($this->m_data);


         gc_collect_cycles_overX($GLOBALS['CRVCL']['GC_COLLECT_CYCLES_PERCENT']);
      }

//-------------------------------------------------------------------------------------------------------------------------------------
// public

      /**
       * return the resource of mysql result
       *
       * @return resource
       */
      function getResource() {
         if ($this->rows() > 0) {
            $this->gotoRow(0);
         }
         return $this->m_result;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * rows of result
       *
       * @return int
       */
      function rows() {
         if($this->m_rows !== null)return $this->m_rows;

         if ($this->m_data != null) {
            return $this->m_rows = acount($this->m_data);
         }

         if ($this->m_result && is_resource($this->m_result)) {
            return $this->m_rows = mysql_num_rows($this->m_result);
         }
         if (is_object($this->m_result) && $this->m_result instanceof mysqli_result) {
            return $this->m_rows = $this->m_result->num_rows;
         }
         if (is_object($this->m_result) && $this->m_result instanceof PDOStatement) {
            return $this->m_rows = $this->m_result->rowCount();
         }
         return 0;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * jump to the passed row
       *
       * @param int $rownum
       * @return bool
       */
      function gotoRow($rownum) {
         if ($this->m_data != null) {
            $this->m_row_pointer = $rownum;
            if ($this->bof() || $this->eof()) {
               return false;
            }
            return true;
         }

         if ($this->m_result && is_resource($this->m_result)) {
            $this->m_row_pointer = $rownum;
            if ($this->bof() || $this->eof()) {
               return false;
            }
            return mysql_data_seek($this->m_result, $rownum);
         }
         if (is_object($this->m_result) && $this->m_result instanceof mysqli_result) {
            $this->m_row_pointer = $rownum;
            if ($this->bof() || $this->eof()) {
               return false;
            }
            return $this->m_result->data_seek($rownum);
         }
         if (is_object($this->m_result) && $this->m_result instanceof PDOStatement) {
            $this->m_row_pointer = $rownum;
            if ($this->bof() || $this->eof()) {
               return false;
            }
            return true;
         }
         return false;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * current row
       *
       * @return int
       */
      function row() {
         if ($this->bof() || $this->eof()) {
            return -1;
         }
         return $this->m_row_pointer;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * skip one row
       *
       * @return bool
       */
      function next() {
         $this->m_row_pointer = $this->m_row_pointer + 1;
         if ($this->eof()) {
            return false;
         }
         return $this->gotoRow($this->m_row_pointer);
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * skip back one row
       *
       * @return bool
       */
      function previous() {
         $this->m_row_pointer = $this->m_row_pointer - 1;
         if ($this->bof()) {
            return false;
         }
         return $this->gotoRow($this->m_row_pointer);
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * skip to first row
       *
       * @return bool
       */
      function first() {
         $this->m_row_pointer = 0;
         return $this->gotoRow($this->m_row_pointer);
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * skip to last row
       *
       * @return bool
       */
      function last() {
         $this->m_row_pointer = $this->rows() - 1;
         return $this->gotoRow($this->m_row_pointer);
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * return true if EOF
       *
       * @return bool
       */
      function eof() {
         if ($this->rows() == 0) {
            return true;
         }
         if ($this->m_row_pointer > $this->rows() - 1) {
            return true;
         }
         return false;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * return true if BOF
       *
       * @return bool
       */
      function bof() {
         if ($this->rows() == 0) {
            return true;
         }
         if ($this->m_row_pointer < 0) {
            return true;
         }
         return false;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * number of fields
       *
       * @return int
       */
      function fieldCount() {
         if ($this->m_data != null
             || ($this->m_result && is_resource($this->m_result))
             || (is_object($this->m_result) && ($this->m_result instanceof mysqli_result || $this->m_result instanceof PDOStatement))) {
            return acount($this->m_field_names);
         }
         return 0;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * column of fieldname
       *
       * @param string $s
       * @return int
       */
      function fieldPos($s) {
         if ($this->m_data != null
             || ($this->m_result && is_resource($this->m_result))
             || (is_object($this->m_result) && ($this->m_result instanceof mysqli_result || $this->m_result instanceof PDOStatement))) {
            
            if (array_key_exists($s, $this->m_field_names)) {
               return $this->m_field_names[$s];
            }
            return -1;
         }
         return -1;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * fieldname of column
       *
       * @param int $i
       * @return string
       */
      function fieldName($i) {
         if ($this->m_data != null
             || ($this->m_result && is_resource($this->m_result))
             || (is_object($this->m_result) && ($this->m_result instanceof mysqli_result || $this->m_result instanceof PDOStatement))) {

            if (array_key_exists($i, $this->m_field_info)) {
               return $this->m_field_info[$i]["name"];
            }
            return "";
         }
         return "";
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * fieldtype of column
       *
       * @param mixed $field
       * @return string
       */
      function fieldType($field) {
         if ($this->m_data != null
             || ($this->m_result && is_resource($this->m_result))
             || (is_object($this->m_result) && ($this->m_result instanceof mysqli_result || $this->m_result instanceof PDOStatement))) {

            if (!is_numeric($field)) {
               $field = $this->fieldPos($field);
            }
            if (array_key_exists($field, $this->m_field_info)) {
               return $this->m_field_info[$field]["type"];
            }
            return "null";
         }
         return "null";
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * fieldlen of column
       *
       * @param mixed $field
       * @return int
       */
      function fieldLen($field) {
         if ($this->m_data != null
             || ($this->m_result && is_resource($this->m_result))
             || (is_object($this->m_result) && ($this->m_result instanceof mysqli_result || $this->m_result instanceof PDOStatement))) {

            if (!is_numeric($field)) {
               $field = $this->fieldPos($field);
            }
            if (array_key_exists($field, $this->m_field_info)) {
               return $this->m_field_info[$field]["len"];
            }
            return 0;
         }
         return 0;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * fieldflags of column
       *
       * @param mixed $field
       * @return string
       */
      function fieldFlags($field) {
         if ($this->m_data != null
             || ($this->m_result && is_resource($this->m_result))
             || (is_object($this->m_result) && ($this->m_result instanceof mysqli_result || $this->m_result instanceof PDOStatement))) {

            if (!is_numeric($field)) {
               $field = $this->fieldPos($field);
            }
            if (array_key_exists($field, $this->m_field_info)) {
               return $this->m_field_info[$field]["flags"];
            }
            return "";
         }
         return "";
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * to get the value of a field at current row, dependent from fetch_type
       *
       * @param mixed $fieldname_num
       * @return string
       */
      function getField($fieldname_num) {
         if ($this->rows() == 0 || $this->bof() || $this->eof()) {
            return null;
         }


         if ($this->m_data != null) {
            if (is_numeric($fieldname_num)) {
               $fieldname_num = $this->fieldName($fieldname_num);
            }

            if (isset($this->m_data[$this->m_row_pointer][$fieldname_num])) {
               if (left($this->m_data[$this->m_row_pointer][$fieldname_num], 3) == "gz:") {
                  return gzuncompress(substr($this->m_data[$this->m_row_pointer][$fieldname_num], 3));
               }
               return $this->m_data[$this->m_row_pointer][$fieldname_num];
            }
            return null;
         }

         $row = array();

         if (is_object($this->m_result) && $this->m_result instanceof PDOStatement) {
            # scrollable cursor not supported this time by MySQL, result will be set from constructor direct to the heap mem of the object
            #$row = $this->m_result->fetch($this->m_fetch_type, PDO::FETCH_ORI_REL, $this->m_row_pointer);

            
         }else if (is_object($this->m_result) && $this->m_result instanceof mysqli_result) {
            $row = $this->m_result->fetch_array($this->m_fetch_type);
            $this->m_result->data_seek($this->m_row_pointer); // because fetch_array skip
         } else {
            $row = mysql_fetch_array($this->m_result, $this->m_fetch_type);
            mysql_data_seek($this->m_result, $this->m_row_pointer); // because mysql_fetch_array skip
         }
        
         if (!isset($row[$fieldname_num])) {
            return null;
         }
         return $row[$fieldname_num];
      }

//-------------------------------------------------------------------------------------------------------------------------------------
   }

//-------------------------------------------------------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------------------------------------------------------
   /**
    * create querys
    * to create a insert or update query use addValue()
    * to create a select query use addField() and addJoin()
    *
    */
   class MySQLBuilder extends MySQL_{

      // private
      private $m_values = array();
      private $m_fields = array();
      private $m_joins = array();

      /**
       *
       * @var string
       */
      public $m_table = "";

      /**
       * MYSQL_EXEC_TYPE like MYSQL_SELECT
       * 
       * @var int
       */
      public $m_type = "";

      /**
       * index of a SELECT Statement
       * @var string
       */
      public $m_index = "";

      /**
       * WHERE condition of a Statement
       * @var string
       */
      public $m_where = "";

      /**
       * additional settings, like GROUP BY
       * @var string
       */
      public $m_additional = "";

      /**
       * options, like NO_CACHE
       * @var string
       */
      public $m_options = "";

      /**
       * escape strings on method "addValue"
       *
       * @var bool
       */
      public $m_escape_strings = true;

//-------------------------------------------------------------------------------------------------------------------------------------
// constructor
      /**
       * create a MySQLBuilder object
       *
       * @param int $MYSQL_EXEC_TYPE MYSQL_INSERT or MYSQL_UPDATE or MYSQL_UPDINS or MYSQL_SELINS
       * @param mixed $table as string or array(<MySQLBuilder object>, <string resultname>) sample: array($build, "s1") => used for a SELECT IN SELECT
       * @param string $where
       * @param string $additional => only for SELECT   Sample: GROUP BY f1
       * @param string $index  => only for SELECT, name of index or index expression like "IGNORE INDEX (key3)"
       * @return MySQLBuilder
       */
      function __construct($MYSQL_EXEC_TYPE, $table, $where = "", $additional = "", $index = "") {
         $this->m_type = $MYSQL_EXEC_TYPE;
         $this->m_table = $table;
         $this->m_where = $where;
         $this->m_index = $index;
         $this->m_additional = $additional;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      function __destruct() {
         $this->free();
      }

//-------------------------------------------------------------------------------------------------------------------------------------
// private
      function free() {
         $this->m_values = NULL;
         $this->m_fields = NULL;
         $this->m_joins = NULL;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * return the sql query
       *
       * @return string
       */
      function query() {
         $sql = "";

         ########################################
         if ($this->m_type == MYSQL_SELECT) {
            $fcount = acount($this->m_fields);
            $jcount = acount($this->m_joins);

            $sql .= "SELECT ";

            if (!empty($this->m_options)) {
               $sql .= $this->m_options;
            }

            if ($fcount == 0) {
               $sql .= "* ";
            } else {
               for ($f = 0; $f < $fcount; $f++) {
                  $table = $this->m_fields[$f]["table"];
                  $field = $this->m_fields[$f]["field"];
                  $quote = $this->m_fields[$f]["quote"];
//echo "t: ".$table . " f:" . $field.BR;
                  if (empty($table)) {
                     $table = $this->m_table;
                  }
                  // not longer needed since parameter $quote of method "addFiled", but leave it for compatibility
                  if ($quote && is_string($table) && strpos($field, ".") === false && strpos(strtoupper($field), "COUNT") === false) {
                     if (strpos($field, "(") !== false) {
                        $pre = strcut($field, "(", true);
                        $post = strrcut($field, ")");
                        $field = strcut(strrcut($field, "(", true), ")");
                        $sql .= $pre . $table . ".`" . $field . "`" . $post;
                     } else {
                        $post = "";
                        if (stripos($field, " AS ") !== false) {
                           $post = strricut($field, " AS ");
                           $field = trim(stricut($field, " AS "));
                        }
                        $sql .= $table . ".`" . $field . "`" . $post;
                     }
                  } else {
                     $sql .= $field;
                  }
                  if ($f != $fcount - 1) {
                     $sql .= ", ";
                  }
               }// for
            }

            $table = $this->m_table;
            if (is_array($table) && count($table) == 2) {
               $buildobj = $table[0];
               $nameAS = $table[1];
               if (is_object($buildobj) && ($buildobj instanceof MySQLBuilder) && $buildobj->m_type == MYSQL_SELECT) {
                  $table = '(' . $buildobj->query() . ') AS ' . $nameAS;
               }
            }

            $sql .= " FROM " . $table;

            if (!empty($this->m_index)) {
               if (strpos($this->m_index, "(") !== false) {
                  $sql .= " " . $this->m_index;
               } else {
                  $sql .= " FORCE INDEX(" . $this->m_index . ")";
               }
            }

            for ($j = 0; $j < $jcount; $j++) {
               $type = $this->m_joins[$j]["type"];
               $jtable = $this->m_joins[$j]["table"];
               $fj = $this->m_joins[$j]["fj"];
               $fm = $this->m_joins[$j]["fm"];
               $add = $this->m_joins[$j]["add"];
               $join = "LEFT JOIN";

               if ($type == "R") {
                  $join = "RIGHT JOIN";
               }
               if ($type == "I") {
                  $join = "INNER JOIN";
               }

               $sql .= " " . $join . " " . $jtable . " ON ";

               $a_fj = $fj;
               if(!is_array($fj)){
                  $a_fj = array($fj);
               }
               $a_fm = $fm;
               if(!is_array($fm)){
                  $a_fm = array($fm);
               }

               $c_fj = acount($a_fj);
               for($f = 0; $f < $c_fj; $f++){
                  $fj = $a_fj[$f];
                  $fm = $a_fm[$f];

                  if (strpos($fj, ".") === false && !empty($jtable)) {
                     $sql .= $jtable . ".";
                  }
                  $sql .= $fj;

                  $sql .= " = ";

                  if (strpos($fm, ".") === false) {
                     $mtable = '';
                     if ($j == 0) {
                        $mtable = $this->m_table;
                     } else {
                        $mtable = $this->m_fields[$j - 1]["table"];
                     }
                     
                     if (!empty($mtable)) {
                        $sql .= $mtable . ".";
                     }
                  }
                  $sql .= $fm;

                  if($c_fj > 1 && $f < $c_fj-1){
                     $sql .= ' AND ';
                  }
               } // for fields of join

               $sql .= $add;

               if ($j != $jcount - 1) {
                  $sql .= " ";
               }
            } // for joins

            if (!empty($this->m_where)) {
               $sql .= " WHERE " . $this->m_where;
            }
            if (!empty($this->m_additional)) {
               $sql .= " " . $this->m_additional;
            }


            ########################################
         } else if ($this->m_type == MYSQL_DELETE) {
            $sql .= "DELETE ";

            if (!empty($this->m_options)) {
               $sql .= $this->m_options;
            }

            $sql .= "FROM ";

            $sql .= $this->m_table;

            if (!empty($this->m_where)) {
               $sql .= " WHERE " . $this->m_where;
            }

            if (!empty($this->m_additional)) {
               $sql .= " " . $this->m_additional;
            }


            ########################################
         } else {
            $fcount = acount($this->m_values);

            if ($this->m_type == MYSQL_INSERT) {
               $sql .= "INSERT ";
            } else if ($this->m_type == MYSQL_UPDATE) {
               $sql .= "UPDATE ";
            }

            if (!empty($this->m_options)) {
               $sql .= $this->m_options;
            }

            $sql .= $this->m_table . " SET ";
            for ($f = 0; $f < $fcount; $f++) {
               $field = $this->m_values[$f]["field"];
               $val = $this->m_values[$f]["val"];
               $type = $this->m_values[$f]["type"];

               $this->buildField($sql, $field, $val, $type);
               
               if ($f != $fcount - 1) {
                  $sql .= ", ";
               }
            }
            if ($this->m_type == MYSQL_UPDATE && !empty($this->m_where)) {
               $sql .= " WHERE " . $this->m_where;
            }
            if ($this->m_type == MYSQL_UPDATE && !empty($this->m_additional)) {
               $sql .= " " . $this->m_additional;
            }
         }
         ########################################
         return $sql;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
// public

      /**
       * add a value for INSERT or UPDATE
       *
       * @param string $field
       * @param mixed $value you can cast a value using as sample:<br>MYSQL_CAST_INT.$val<br>or use the third parameter
       * @param string $type as I for integer, N for numeric (float/double)  or  C and S for string  or  D for date  or  F and O for functions and operations  or  SET for set type (support array as value)  or  E for enum<br>or use MYSQL_FIELD_TYPE's like MYSQL_INT or MYSQL_CHAR etc.
       */
      function addValue($field, $value, $type = "U") {

         $value = $this->cast($value, $type, $this->m_escape_strings);
                  
         $this->m_values[] = array("field" => $field, "val" => $value, "type" => $type);
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * add a field for SELECT
       *
       * @param string $name => <fieldname> or <table.fieldname> or <db.table.fieldname>
       * @param mixed $table as string (optional) or MySQLBuilder object for a subselect
       * @param bool $quote (optional) => set automatically the tablename to the field, if you use as sample a function like COUNT use $quote == false
       */
      function addField($name, $table = "", $quote = true) {
         if (is_object($table) && $table instanceof MySQLBuilder) {
            $quote = false;
            $name = ' (' . $table->query() . ') AS ' . $name;
         }
         if ($quote && strpos($name, ".") !== false) {
            $tmp = explode(".", $name);
            $count = acount($tmp);
            if ($count == 2) {
               $name = $tmp[1];
               $table = $tmp[0];
            } else if ($count == 3) {
               $name = $tmp[2];
               $table = $tmp[0] . "." . $tmp[1];
            }
         }

         $this->m_fields[] = array("field" => $name, "table" => $table, "quote" => $quote);
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * add a JOIN
       *
       * @param string $table => table to join
       * @param mixed $field_in_join_table => field from join table (use strings as array for join more than one field)
       * @param mixed $field_used_to_join => field from table used to join (use strings as array for join more than one field)
       * @param string $type => "L" for LEFT JOIN, "R" for RIGHT JOIN, "I" for INNER JOIN
       * @param string $additional => for additional conditions like as sample: lang='en'
       */
      function addJoin($table, $field_in_join_table, $field_used_to_join, $type = "L", $additional='') {
         if(is_array($field_in_join_table) && acount($field_in_join_table) != acount($field_used_to_join)){
            trigger_error("MySQLBuilder::addJoin - parameter 2 and parameter 3 must have the same length", E_USER_NOTICE);
         }

         if($additional!=''){
            $additional = ' '.trim($additional);
         }

         $type = strtoupper(left($type, 1));
         $this->m_joins[] = array("type" => $type, "table" => $table, "fj" => $field_in_join_table, "fm" => $field_used_to_join, "add"=>$additional);
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * to add a option like SQL_NO_CACHE or LOW_PRIORITY
       *
       * @param string $s
       */
      function setOptions($s) {
         $this->m_options = trim($s) . " ";
      }

   }

//-------------------------------------------------------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------------------------------------------------------

   /**
    * handle a language table or database
    *
    * need a table with fields id, lang, pid, wid, text
    * index must be on lang + pid + wid
    * table and field name(s) does not matter, you can use any allowed.
    * if you not use the default names, please change the class settings
    *
    * Sample for standard language table:
    *
    * DROP TABLE IF EXISTS `mydb`.`lang`;
    * CREATE TABLE  `mydb`.`lang` (
    * `id` int(10) unsigned NOT NULL auto_increment,
    * `lid` char(2) NOT NULL,
    * `pid` varchar(45) NOT NULL,
    * `wid` varchar(45) NOT NULL,
    * `text` text NOT NULL,
    * PRIMARY KEY  (`id`),
    * KEY `INX_LANG_PID_WID` USING BTREE (`lid`,`pid`,`wid`)
    * ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
    *
    *
    * Sample for standard language database:
    *
    * DROP TABLE IF EXISTS `mylngdb`.`en`;
    * CREATE TABLE  `mylngdb`.`en` (
    * `id` int(10) unsigned NOT NULL auto_increment,
    * `lid` char(2) NOT NULL,
    * `pid` varchar(45) NOT NULL,
    * `wid` varchar(45) NOT NULL,
    * `text` text NOT NULL,
    * PRIMARY KEY  (`id`),
    * KEY `INX_LANG_PID_WID` USING BTREE (`lid`,`pid`,`wid`)
    * ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
    *
    * DROP TABLE IF EXISTS `mylngdb`.`de`;
    * CREATE TABLE  `mylngdb`.`de` (
    * `id` int(10) unsigned NOT NULL auto_increment,
    * `lid` char(2) NOT NULL,
    * `pid` varchar(45) NOT NULL,
    * `wid` varchar(45) NOT NULL,
    * `text` text NOT NULL,
    * PRIMARY KEY  (`id`),
    * KEY `INX_LANG_PID_WID` USING BTREE (`lid`,`pid`,`wid`)
    * ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci;
    */
   class MySQLLang {

      // private
      private $m_mysql = null;
      private $m_serverid = "";
      // public
      public $m_database = "";
      public $m_tablename = "lang";
      public $m_lid = "lid";
      public $m_pid = "pid";
      public $m_wid = "wid";
      public $m_text = "text";
      public $m_default_lang = "";
      public $m_autosense_lang = true;
      public $m_db_instead_of_table = false;
      public $m_errormsg;

//-------------------------------------------------------------------------------------------------------------------------------------
// constructor
      /**
       *
       * @param MySQL $mysql
       * @param string $serverid
       * @return MySQLLang
       */
      function __construct($mysql, $serverid = "master") {
         $this->m_mysql = $mysql;
         $this->m_serverid = $serverid;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
// private
      function getDBTab($lid) {
         $db = $this->m_database;
         $table = $this->m_tablename;
         if (strpos($db, '`') === false) {
            $db = "`" . $db . "`";
         }
         if (strpos($table, '`') === false) {
            $table = "`" . $table . "`";
         }

         if ($this->m_db_instead_of_table) {
            if (empty($db)) {
               throw new Exception('language database must be specified');
            }
            return $db . "." . $lid;
         }
         if (!empty($db)) {
            return $db . "." . $table;
         }
         return $table;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
// public
      /**
       * return a text
       *
       * @param string $pid
       * @param string $wid
       * @param string $lang
       * @return string
       */
      function getText($pid, $wid, $lid = "") {
         $this->m_errormsg = "";

         $wid = str_replace("#", "", $wid);

         if ($this->m_mysql === null) {
            $this->m_errormsg = "mysql object initialized";
            return "";
         }

         if (empty($lid) && $this->m_autosense_lang) {
            $lid = getClientLanguage(true);
         }

         $table = $this->getDBTab($lid);

         $res = $this->m_mysql->sqlGetField($this->m_serverid, 'SELECT ' . $this->m_text . ' FROM ' . $table . ' WHERE ' . $this->m_lid . ' = "' . $lid . '" AND ' . $this->m_pid . ' = "' . $pid . '" AND ' . $this->m_wid . ' = "' . $wid . '"');
         if ($this->m_mysql->getError($this->m_serverid) != 0) {
            $this->m_errormsg = $this->m_mysql->getErrorMsg($this->m_serverid);
            $res = "#" . $wid . "#";
         }

         if (empty($res) && !empty($this->m_default_lang)) {
            $res = $this->m_mysql->sqlGetField($this->m_serverid, 'SELECT ' . $this->m_text . ' FROM ' . $table . ' WHERE ' . $this->m_lid . ' = "' . $this->m_default_lang . '" AND ' . $this->m_pid . ' = "' . $pid . '" AND ' . $this->m_wid . ' = "' . $wid . '"');
         }

         if (empty($res)) {
            $res = "#" . $wid . "#";
         }

         return $res;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * return parsed stream
       *
       * @param string $s
       * @param mixed $pid   as string or array
       * @param string $lid
       * @param bool $return_pid   if true, return template var with pid
       * @param int $ttl enable the Opcache support, require APC
       * @param bool $useLB enable/disable loadbalancing
       * @return string
       */
      function parseStream($s, $pid, $lid = "", $return_pid = false, $ttl = -1, $useLB = false) {
         $this->m_errormsg = "";

         if (is_object($s) && ($s instanceof multiAjaxHtmlResponse) == true) {
            $s = $s->partsToHtml();
         }

         if ($this->m_mysql === null) {
            $this->m_errormsg = "mysql object initialized";
            return "";
         }

         if (empty($lid) && $this->m_autosense_lang) {
            $lid = getClientLanguage(true);
         }

         $table = $this->getDBTab($lid);

         $where = "";
         if (is_array($pid)) {
            $where .= ' AND(';
            for ($i = 0; $i < count($pid); $i++) {
               if ($i > 0) {
                  $where .= ' OR ';
               }
               $where .= $this->m_pid . ' = "' . $pid[$i] . '" ';
            }
            $where .= ')';
         } else {
            $where = ' AND ' . $this->m_pid . ' = "' . $pid . '"';
         }

         ######################################################################
         # speed up by - PA 2012-06-26 (Peter Ammel)
         $matches = null;
         $c_matches = preg_match_all('/#[^#<>\"\';\t\r\n]{3,100}#/', $s, $matches);

         $word_in = '';
         if($c_matches !== null && $c_matches > 0){
            
            for($i = 0; $i < $c_matches; $i++)
            {
               $word_in .= '"'.$matches[0][$i].'",';
            }

            $word_in = str_replace('#', '', $word_in);

            $where .= ' AND wid IN ('.trim($word_in, ',').')';
         }
         ######################################################################

         $res = null;
         $host_id = $this->m_serverid;
         if ($ttl > 0) {
            if ($useLB) {
               $res = $this->m_mysql->lbQSql($host_id,'SELECT ' . $this->m_pid . ',' . $this->m_wid . ',' . $this->m_text . ' FROM ' . $table . ' WHERE ' . $this->m_lid . ' = "' . $lid . '"' . $where, $ttl);
            } else {
               $res = $this->m_mysql->qSql($host_id, 'SELECT ' . $this->m_pid . ',' . $this->m_wid . ',' . $this->m_text . ' FROM ' . $table . ' WHERE ' . $this->m_lid . ' = "' . $lid . '"' . $where, $ttl);
            }
         } else {
            if ($useLB) {
               $res = $this->m_mysql->lbSql($host_id, 'SELECT ' . $this->m_pid . ',' . $this->m_wid . ',' . $this->m_text . ' FROM ' . $table . ' WHERE ' . $this->m_lid . ' = "' . $lid . '"' . $where);
            } else {
               $res = $this->m_mysql->sql($host_id, 'SELECT ' . $this->m_pid . ',' . $this->m_wid . ',' . $this->m_text . ' FROM ' . $table . ' WHERE ' . $this->m_lid . ' = "' . $lid . '"' . $where);
            }
         }

         if ($this->m_mysql->getError($host_id) != 0) {
            $this->m_errormsg = $this->m_mysql->getErrorMsg($host_id);
            return null;
         }

         $vars = array();
         for ($r = 0; $r < $res->rows(); $r++) {
            $key = $res->getField($this->m_wid);
            if ($return_pid) {
               $key = $res->getField($this->m_pid) . "_" . $key;
            }
            $val = $res->getField($this->m_text);
            $vars += array($key => $val);
            $res->next();
         }
         free($res);
         gc_collect_cycles_overX($GLOBALS['CRVCL']['GC_COLLECT_CYCLES_PERCENT']);


         $t = new template();
         $s = $t->parseStream($s, $vars);

         free($vars);
         gc_collect_cycles_overX($GLOBALS['CRVCL']['GC_COLLECT_CYCLES_PERCENT']);

         if (empty($s)) {
            $this->m_errormsg = $t->m_errormsg;
         }

         return $s;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
      /**
       * import a text
       *
       * @param string $pid
       * @param string $wid
       * @param string $lid
       * @param bool $overwrite
       * @return bool
       */
      function import($pid, $wid, $lid, $text, $overwrite = false) {
         $this->m_errormsg = "";

         if ($this->m_mysql === null) {
            $this->m_errormsg = "mysql object initialized";
            return "";
         }

         $type = MYSQL_INSERT;
         if ($overwrite) {
            $type = MYSQL_UPDINS;
         }

         $table = $this->getDBTab($lid);

         $res = $this->m_mysql->sqlGetField($this->m_serverid, 'SELECT ' . $this->m_text . ' FROM ' . $table . ' WHERE ' . $this->m_lid . ' = "' . $lid . '" AND ' . $this->m_pid . ' = "' . $pid . '" AND ' . $this->m_wid . ' = "' . $wid . '"');
         if ($this->m_mysql->getError($this->m_serverid) != 0) {
            $this->m_errormsg = $this->m_mysql->getErrorMsg($this->m_serverid);
            return false;
         }

         $build = null;
         if ($res !== null && $overwrite) {
            $build = new MySQLBuilder(MYSQL_UPDATE, $table, $this->m_lid . ' = "' . $lid . '" AND ' . $this->m_pid . ' = "' . $pid . '" AND ' . $this->m_wid . ' = "' . $wid . '"');
            $build->addValue($this->m_lid, $lid);
            $build->addValue($this->m_pid, $pid);
            $build->addValue($this->m_wid, $wid);
            $build->addValue($this->m_text, $text, "S");
         } else if ($res === null) {
            $build = new MySQLBuilder(MYSQL_INSERT, $table);
            $build->addValue($this->m_lid, $lid);
            $build->addValue($this->m_pid, $pid);
            $build->addValue($this->m_wid, $wid);
            $build->addValue($this->m_text, $text, "S");
         }

         if ($build !== null) {
            $res = $this->m_mysql->sql($this->m_serverid, $build);
            if ($res == 0) {
               $this->m_errormsg = $this->m_mysql->getErrorMsg($this->m_serverid);
               return false;
            }
         }

         return true;
      }

//-------------------------------------------------------------------------------------------------------------------------------------
   }

//-------------------------------------------------------------------------------------------------------------------------------------
############################################################
}
############################################################
?>
Return current item: crVCL PHP Framework