Db.php

Go to the documentation of this file.

<?php

namespace Rsi\Fred\Controller\Provider;

use \Rsi\Fred\Controller;

class Db extends \Rsi\Fred\Controller\Provider{

  const COLUMN_TYPE_GLUE = '__';

  public $select = '*'; //!< Columns to select.
  public $table = null; //!< Table(s) to select from (including joins and unions).
  public $search = []; //!< Custom search (key = column, value = SQL - :ref and :count can be used as placeholders for the
    // searched value(s) and the number of values).
  public $where = null; //!< Basic where clause (without the 'where' keyword; empty = all rows).
  public $args = []; //!< Fixed parameters (in contrast to those provided during actions).

  protected $_db = null;

  /**
   * Return a single row from the dataset.
   * @param string $select  Column(s) to select.
   * @param string $key  Column to use to find the record.
   * @param mixed $value  Value to use to find the record.
   * @param array $params  Extra parameters.
   * @return mixed  Row (multi column) or value (single column).
   */
  protected function single($select,$key,$value,$params = null){
    return \Rsi::nothing($value) ? null : $this->db->single($this->db->limit("
      select $select from {$this->table}
      where `$key` = :this" . ($this->where ? '
        and (' . $this->where . ')' : ''),1),
      array_merge($params ?: [],$this->args,['this' => $value])
    );
  }

  public function trans($column,$key,$value,$params = null){
    return $column == $key ? $value : $this->single("`$column`",$key,$value,$params);
  }

  public function exists($key,$value,$params = null){
    return (bool)$this->single(1,$key,$value,$params);
  }

  protected function implodeMulti($where,$enclose = false){
    if(count($where) <= 1) return array_pop($where);
    if($enclose) foreach($where as &$arg) $arg = "($arg)";
    unset($arg);
    return '(' . implode(' or ',$where) . ')';
  }
  /**
   * Build a where statement for use in an SQL query.
   * @param array $search  Search criteria.
   * @param array $args  Bind variables for the query.
   * @param string $strict  Where statement (including basic where clause, including 'where' keyword - empty if no criteria).
   * @param string $loose  Where statement with loose evaluation of a 'like' criterion (including basic where clause, including
   *   'where' keyword - empty if no criteria or not different to basic where clause).
   * @param string $all  Where statement that will find all records (both strict and loose).
   */
  protected function where($search,&$args,&$strict,&$loose = null,&$all = null){
    $strict = $loose = [];

    foreach($search as $column => $values){
      $where = [];
      foreach($values as $type => $ref){
        $operator = null;
        if(array_key_exists($column,$this->search)){
          $args[$key = 's' . count($args)] = $ref;
          $args[$key_count = 'c' . count($args)] = count($ref);
          $where[] = strtr($this->search[$column],[':ref' => ':' . $key,':count' => ':' . $key_count]);
        }
        else switch($type){
          case self::SEARCH_FROM:
            $operator = '>=';
          case self::SEARCH_TO:
            if(!$operator) $operator = '<=';
          case self::SEARCH_EXACT:
            if($ref === null) $where[] = "`$column` is null";
            else{
              if(!$operator) $operator = '=';
              $args[$key = 'v' . count($args)] = $ref;
              $where[] = "`$column` " . (is_array($ref) ? "in (:$key)" : "$operator :$key");
            }
            break;
          case self::SEARCH_DATE:
            $dates = [];
            foreach((array)$ref as $date) if($date = \Rsi\Fred\DateTime\Date::create($date)){
              $args[$key_from = 'd' . count($args)] = (string)$date;
              $args[$key_to = 'd' . count($args)] = (string)$date->add();
              $dates[] = "`$column` >= :$key_from and `$column` < :$key_to";
            }
            $where[] = $this->implodeMulti($dates,true);
            break;
          default:
            $likes = $loose_likes = [];
            foreach((array)$ref as $like) if($like){
              if(preg_match('/^\\/.+\\/$/',$like)) try{
                preg_match($like,''); //test it
                $args[$key = 'r' . count($args)] = substr($like,1,-1);
                $likes[] = "`$column` regexp :$key";
              }
              catch(\Exception $e){
                $this->_controller->component('log')->debug("Invalid reg-ex '$like': " . str_replace('preg_match(): Compilation failed: ','',$e->getMessage()),__FILE__,__LINE__);
                $likes[] = '1 = 0';
              }
              elseif(substr($like = strtr($like,['?' => '_','*' => '%']),0,1) == '-'){
                $args[$key = 'x' . count($args)] = '%' . substr($like,1) . '%';
                $likes[] = "(`$column` is null or `$column` not like :$key)";
              }
              else{
                $args[$key = 'l' . count($args)] = $like . '%';
                $likes[] = "`$column` like :$key";
                if(substr($like,0,1) != '%'){
                  $args[$key = 'l' . count($args)] = '%' . $like . '%';
                  $loose_likes[] = "`$column` like :$key";
                }
              }
            }
            if($likes){
              $where[] = $this->implodeMulti($likes);
              if($loose_likes) $loose[$column] = $this->implodeMulti($loose_likes);
            }
        }
      }
      if($where) $strict[$column] = implode(' and ',$where);
    }

    $where = 'where ' . ($this->where ? '(' . $this->where . ')' . ($strict ? "\nand " : '') : '');
    $all = $this->where || $strict ? $where . implode(' and ',$loose + $strict) : null;
    $loose = $loose ? $where . 'not (' . implode(' and ',$strict) . ")\nand " . implode(' and ',array_merge($strict,$loose)) : null;
    $strict = $this->where || $strict ? $where . implode(' and ',$strict) : null;
  }
  /**
   * Build an order statement for an SQL query.
   * @param array $order  Sort order for the result.
   * @return string  Order statement (including 'order by' keyword - empty if no criteria).
   */
  protected function order($order){
    $columns = [];
    foreach($order + $this->order as $column => $type){
      $column = "`$column`";
      if($type & SORT_DESC) $column .= ' desc';
      $columns[] = $column;
    }
    return $columns ? 'order by ' . implode(',',$columns) : null;
  }

  protected function _search($search,$order,$params,$offset,$limit){
    $args = array_merge($params ?: [],$this->args);
    $this->where($search,$args,$strict,$loose);
    $order = $this->order($order ?: []);
    session_write_close();
    $select = "select {$this->select}\nfrom {$this->table}";
    $sql = "$select\n$strict\n$order";
    if($limit) $sql = $this->db->limit($sql,$limit,$offset);
    $records = $this->db->all($sql,$args);
    if($loose && (!$limit || (($count = count($records)) < $limit))){
      $sql = "$select\n$loose\n$order";
      if($limit){
        if($count){
          $offset = 0;
          $limit -= $count;
        }
        elseif($offset) $offset -= $this->db->single("select count(*) from {$this->table}\n$strict",$args);
        $sql = $this->db->limit($sql,$limit,$offset);
      }
      $records = array_merge($records,$this->db->all($sql,$args));
    }
    session_start();
    return $records;
  }

  protected function _totals($search,$totals,$params){
    if(!$totals) return [];
    $args = array_merge($params ?: [],$this->args);
    $this->where($search,$args,$strict,$loose,$all);
    session_write_close();
    $columns = ['count(*) as ' . ($count_alias = self::COLUMN_TYPE_GLUE . Controller::TOTAL_COUNT)];
    foreach($totals as $column => &$types) foreach(($types = (array)$types) as $type){
      $alias = $column . self::COLUMN_TYPE_GLUE . $type;
      switch($type){
        case Controller::TOTAL_COUNT: break;
        case Controller::TOTAL_UNIQUE: $columns[] = "count(distinct `$column`) as `$alias`"; break;
        case Controller::TOTAL_NULL: $columns[] = "sum(if(`$column` is null,1,0)) as `$alias`"; break;
        case Controller::TOTAL_NOT_NULL: $columns[] = "sum(if(`$column` is null,0,1)) as `$alias`"; break;
        default: $columns[] = "$type(`$column`) as `$alias`";
      }
    }
    unset($types);
    $select = 'select ' . implode(',',$columns) . "\nfrom {$this->table}\n";
    $alias_totals = $this->db->single($select . $all,$args,false);
    session_start();
    $result = [];
    foreach($totals as $column => $types){
      $result[$column] = [];
      foreach($types as $type) $result[$column][$type] =
        $alias_totals[$type == Controller::TOTAL_COUNT ? $count_alias : $column . self::COLUMN_TYPE_GLUE . $type];
    }
    return $result;
  }

  protected function _group($search,$group,$column,$total,$limit,$params){
    $args = array_merge($params ?: [],$this->args);
    $this->where($search,$args,$strict,$loose,$all);
    switch($total){
      case Controller::TOTAL_UNIQUE: $total = "count(distinct `$column`)"; break;
      default: $total = "$total(`$column`)";
    }
    session_write_close();
    $sql = "select `$group`,$total from {$this->table}\n$all\ngroup by `$group`\norder by ";
    $result = $this->db->record($limit ? $this->db->limit($sql . $total . ($limit > 0 ? '' : ' desc'),abs($limit)) : $sql . "`$group`",$args);
    session_start();
    return $result;
  }

  protected function setDb($value){
    $this->_db = $value;
  }

  protected function getDb(){
    if($this->_db === null) $this->_db = $this->_controller->component('db');
    return $this->_db;
  }

  protected function getId(){
    if(!$this->_id) $this->_id = md5($this->select . $this->table . $this->where);
    return $this->_id;
  }

}