UserSpice patch - improvements on DB.php

*** ../UserSpice41/users/classes/DB.php 2016-08-15 15:51:50.857641600 +0200
--- ./users/classes/DB.php  2016-08-28 17:38:12.822601400 +0200
***************
*** 19,34 ****
  */
  class DB {
    private static $_instance = null;
!   private $_pdo, $_query, $_error = false, $_results, $_resultsArray, $_count = 0, $_lastId, $_queryCount=0;

    private function __construct(){
        try{
            $this->_pdo = new PDO('mysql:host=' .
                Config::get('mysql/host') .';dbname='.
                Config::get('mysql/db'),
                Config::get('mysql/username'),
                Config::get('mysql/password'),
!               array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET SESSION sql_mode = ''"));
        } catch(PDOException $e){
            die($e->getMessage());
        }
--- 19,36 ----
  */
  class DB {
    private static $_instance = null;
!   private $_pdo, $_query, $_error = false, $_errorInfo, $_results, $_resultsArray, $_count = 0, $_lastId, $_queryCount=0;

    private function __construct(){
+       if (!$opts = Config::get('mysql/options'))
+           $opts = array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET SESSION sql_mode = ''");
        try{
            $this->_pdo = new PDO('mysql:host=' .
                Config::get('mysql/host') .';dbname='.
                Config::get('mysql/db'),
                Config::get('mysql/username'),
                Config::get('mysql/password'),
!               $opts);
        } catch(PDOException $e){
            die($e->getMessage());
        }
***************
*** 42,49 ****
--- 44,53 ----
    }

    public function query($sql, $params = array()){
+       #echo "DEBUG: query(sql=$sql, params=".print_r($params,true).")<br />\n";
        $this->_queryCount++;
        $this->_error = false;
+       $this->_errorInfo = array(0, null, null);
        if ($this->_query = $this->_pdo->prepare($sql)) {
            $x = 1;
            if (count($params)) {
***************
*** 54,65 ****
--- 58,72 ----
            }

            if ($this->_query->execute()) {
+               if ($this->_query->columnCount() > 0) {
                    $this->_results = $this->_query->fetchALL(PDO::FETCH_OBJ);
                    $this->_resultsArray = json_decode(json_encode($this->_results),true);
+               }
                $this->_count = $this->_query->rowCount();
                $this->_lastId = $this->_pdo->lastInsertId();
            } else{
                $this->_error = true;
+               $this->_errorInfo = $this->_query->errorInfo();
            }
        }
        return $this;
***************
*** 75,96 ****

    public function action($action, $table, $where = array()){
        $sql = "{$action} FROM {$table}";
!       $value = '';
!       if (count($where) === 3) {
!           $operators = array('=', '>', '<', '>=', '<=');
!
!           $field = $where[0];
!           $operator = $where[1];
!           $value = $where[2];

!           if(in_array($operator, $operators)){
!               $sql .= " WHERE {$field} {$operator} ?";
            }
        }
!       if (!$this->query($sql, array($value))->error()) {
!           return $this;
        }
!       return false;
    }

    public function get($table, $where){
--- 82,151 ----

    public function action($action, $table, $where = array()){
        $sql = "{$action} FROM {$table}";
!       $values = array();
!     $where_text = $this->_calcWhere($where, $values);
!     if ($where_text) $sql .= " WHERE $where_text";
!       if (!$this->query($sql, $values)->error()) {
!           return $this;
!       }
!       return false;
!   }

!   private function _calcWhere($w, &$vals, $comboparg='and') {
!     #echo "DEBUG: Entering _calcwhere(w=".print_r($w,true).",...)<br />\n";
!     if (is_array($w)) {
!               #echo "DEBUG: is_array - check<br />\n";
!         $comb_ops = ['and', 'or'];
!         $valid_ops = ['=', '<', '>', '<=', '>=', '<>', '!='];
!         # believe it or not, this appears to be the fastest way to check
!         # sequential vs associative. Particularly with our expected short
!         # arrays it shouldn't impact memory usage
!         # https://gist.github.com/Thinkscape/1965669
!         if (array_values($w) === $w) { // sequential array
!                       #echo "DEBUG: Sequential array - check!<br />\n";
!             if (in_array(strtolower($w[0]), $comb_ops)) {
!                               #echo "DEBUG: w=".print_r($w,true)."<br />\n";
!                 $sql = '';
!                 $combop = '';
!                 for ($i = 1; $i < count($w); $i++) {
!                     $sql .= ' '. $combop . ' ' . $this->_calcWhere($w[$i], $vals);
!                     $combop = $w[0];
!                 }
!                 return '('.$sql.')';
!             } elseif (count($w) == 3 && in_array($w[1], $valid_ops)) {
!                 #echo "DEBUG: normal condition w=".print_r($w,true)."<br />\n";
!                 $vals[] = $w[2];
!                 return $w[0] . ' ' . $w[1] . ' ?';
!             } else {
!                 echo "ERROR: w=".print_r($w,true)."<br />\n";
!             }
!         } else { // associative array ['field' => 'value']
!             #echo "DEBUG: Associative<br />\n";
!             $sql = '';
!             $combop = '';
!             foreach ($w as $k=>$v) {
!                 if (in_array(strtolower($k), $comb_ops)) {
!                     #echo "DEBUG: A<br />\n";
!                     #echo "A: k=$k, v=".print_r($v,true)."<br />\n";
!                     $sql .= $combop . ' (' . $this->calcWhere($v, $vals, $k) . ') ';
!                     $combop = $comboparg;
!                 } else {
!                     #echo "DEBUG: B<br />\n";
!                     #echo "B: k=$k, v=".print_r($v,true)."<br />\n";
!                     $vals[] = $v;
!                     if (in_array(substr($k,-1,1), array('=', '<', '>'))) // 'field !='=>'value'
!                         $sql .= $combop . ' ' . $k . ' ? ';
!                     else // 'field'=>'value'
!                         $sql .= $combop . ' ' . $k . ' = ? ';
!                     $combop = $comboparg;
                  }
              }
!             return ' ('.$sql.') ';
          }
!     } else {
!         echo "ERROR: No array in $w<br />\n";
!     }
!
    }

    public function get($table, $where){
***************
*** 163,168 ****
--- 218,231 ----
        return $this->_error;
    }

+   public function errorInfo() {
+       return $this->_errorInfo;
+   }
+
+   public function errorString() {
+       return 'ERROR #'.$this->_errorInfo[0].': '.$this->_errorInfo[2];
+   }
+
    public function lastId(){
        return $this->_lastId;
    }