se/SE/se-lib/AclQueryBuilder.php

<?php

Lib::loadClass('P5');
Lib::loadClass('Core_AclBase');
Lib::loadClass('AntAclBase');
Lib::loadClass('ACL');

class AclQueryBuilder {

  public $select;
  public $from;
  public $where;
  /* where: array of [ $fieldName, $comparisonSign, $value ]
   *   $fieldName - field name, TODO: xpath, null for groups (or, and)
   *   $comparisonSign - @see where() to check allowed (implemented) signs or function names
   *   $value - string or another $where if group (or, and)
   * where examples: [ $fieldName, $comparisonSign, $value ]
     [ 'ID', '=', '1' ] // where ID = '1'
     [ 'LABEL', 'like', '%abc%' ] // where LABEL like '%abc%'
     [ null, 'or',  [] $values ] // where ( $values[0] or  $values[1] or  ... ) // or where $values[0] when 1 === count($values)
     [ null, 'and', [] $values ] // where ( $values[0] and $values[1] and ... ) // or where $values[0] when 1 === count($values)
  */
  public $orderBy;
  public $groupBy;
  public $limit;
  public $offset;
  public $_fromPrefix;
  public $_joinPrefix;
  public $_joinParams;
  public $isInstances;
  public $isNotInstances;
  public $_hasSelectRemoteFields;
  public $_hasQueryRemoteFields;

  public function __construct() {
    $this->select = [];
    $this->from = null; // (ACL | tableName)
    $this->where = [];
    $this->orderBy = null;
    $this->groupBy = null;
    $this->limit = null;
    $this->offset = null;
    $this->_fromPrefix = 't'; // prefix for this->from - default 't'
    $this->_joinPrefix = []; // prefix => from (Acl | tableName)
    $this->_joinParams = []; // prefx => params
    $this->isInstances = [];
    $this->isNotInstances = [];

    $this->_hasSelectRemoteFields = false;
    $this->_hasQueryRemoteFields = false;
  }

  public function from($from, $prefix = 't') {
    DBG::log([
      'from instanceof Core_AclBase' => ($from instanceof Core_AclBase),
      'from instanceof AntAclBase' => ($from instanceof AntAclBase),
      'from instanceof TableAcl' => ($from instanceof TableAcl),
    ], 'array', "\$from class(".get_class($from).")");
    if ($this->from) throw new Exception("Duplicate FROM");
    $this->from = $from;
    $this->_fromPrefix = $prefix;
    return $this;
  }

  public function select($propertyName) { // TODO: ogc:propertyName, *, xpath, @instances, etc...
    if (empty($propertyName)) return $this; // SKIP empty values, null's, etc.
    DBG::log($propertyName, 'array', "AclQueryBuilder->select");
    if (is_array($propertyName)) {
      $hasWildcard = in_array('*', $propertyName);
      foreach ($propertyName as $k => $v) {
        if ('rawSelect' === $k) $this->select['__rawSelect__'] = $v;
        else if ('*' === $v) continue;
        else $this->select[] = $v;
      }
      if ($hasWildcard) array_unshift($this->select, '*');
      return $this;
    }
    if (!in_array($propertyName, $this->select)) $this->select[] = $propertyName; // TODO: split by property source
    return $this;
  }

  public function where($where) {
    if (null === $where) return $this;
    if (is_string($where)) return $this->whereRaw($where);
    list($fieldName, $comparisonSign, $value) = $where;
    if (!in_array($comparisonSign, [ // validation
      '=', '<', '>', '<=', '>=', '<>', '!='
      , 'like', 'not like'
      , 'is not null', 'is null'
      , 'Intersects', 'GeometryType'
      , 'or' // $value = [ comparisons for $fieldName ... or null ]
      , 'and' // $value = [ comparisons for $fieldName ... or null ]
    ])) {
      throw new Exception("Not implemented comparisonSign '{$comparisonSign}'");
    }
    $this->where[] = [$fieldName, $comparisonSign, $value];
    return $this;
  }
  public function _generateWhereMain($where) { // @returns string
    if (is_string($where)) return $where; // whereRaw
    list($fieldName, $comparisonSign, $value) = $where;
    $sqlFieldName = $fieldName; // TODO: getSqlFieldName // TODO: get sql field name with table prefix from join list to replace "{$this->_fromPrefix}.{$sqlFieldName}" below
    switch ($comparisonSign) {
      case 'is not null': return "{$this->_fromPrefix}.{$sqlFieldName} is not null";
      case 'is null': return "{$this->_fromPrefix}.{$sqlFieldName} is null";
      case 'Intersects': return "Intersects(GeomFromText('{$value}'), {$this->_fromPrefix}.`{$sqlFieldName}`)=1";
      case 'GeometryType': return "GeometryType({$this->_fromPrefix}.`{$sqlFieldName}`)='{$value}'";
      case 'or': return $this->_generateWhereBlock($where);
      case 'and': return $this->_generateWhereBlock($where);
      case 'UNIX_TIMESTAMP_LESS_THAN_NOW': return "
        COALESCE(UNIX_TIMESTAMP({$this->_fromPrefix}.`{$sqlFieldName}`), 0) < UNIX_TIMESTAMP()
        and {$this->_fromPrefix}.`{$sqlFieldName}` != ''
        and {$this->_fromPrefix}.`{$sqlFieldName}` != '0000-00-00 00:00:00'
      ";
      case 'UNIX_TIMESTAMP_NOW_3600': return "
        COALESCE(UNIX_TIMESTAMP({$this->_fromPrefix}.`{$sqlFieldName}`), 0) < UNIX_TIMESTAMP()+3600
        and COALESCE(UNIX_TIMESTAMP({$this->_fromPrefix}.`{$sqlFieldName}`), 0) > UNIX_TIMESTAMP()-3600
      ";
      case 'UNIX_TIMESTAMP_GREATER_THAN': return " COALESCE(UNIX_TIMESTAMP({$this->_fromPrefix}.`{$sqlFieldName}`), 0) > '{$value}' ";
      case 'UNIX_TIMESTAMP_LESS_THAN':    return " COALESCE(UNIX_TIMESTAMP({$this->_fromPrefix}.`{$sqlFieldName}`), 0) < '{$value}' ";
      default: return "{$this->_fromPrefix}.{$sqlFieldName} {$comparisonSign} " . DB::getPDO()->quote($value);
    }
    return null;
  }
  public function _generateWhereBlock($where) { // @returns string
    list($fieldName, $sqlGlue, $list) = $where;
    $list = array_filter($list, function ($value) { return null !== $value; });
    $sqlList = array_filter(
      array_map([$this, '_generateWhereMain'], $list),
      'is_string'
    );
    if (1 === count($sqlList)) return $sqlList[0];
    else return "( " . implode(" {$sqlGlue} ", $sqlList) . " )";
  }
  public function whereIsNotNull($fieldName) {
    $this->where[] = "{$this->_fromPrefix}.{$fieldName} is not null";
    return $this;
  }
  public function whereRaw($rawWhere) { // add where without validation
    if (!$rawWhere) return $this;
    $this->where[] = $rawWhere;
    return $this;
  }
  // public function whereFunction($column, $operator = null, $value = null) {
  public function whereFunction($fieldName, $comparisonSign = null, $value = null) {
    throw new Exception("whereFunction not supported");
    $this->where[] = '';
    return $this;
  }
  public function generateWhereSql() { // @return string - sql without where keyword
    $sqlWhere = array_filter(
      array_map([$this, '_generateWhereMain'], $this->where),
      'is_string'
    );
    return (!empty($sqlWhere)) ? implode("\n\t and ", $sqlWhere) : '';
  }

  public function _generateSelectMain($select, $key) {
    if ('__rawSelect__' === $key) return $select;
    $sqlPk = 'ID';
    if ($this->from instanceof Core_AclBase) $sqlPk = $this->from->getSqlPrimaryKeyField();
    if ('*' === $select) return "{$this->_fromPrefix}.*";
    if ('@instances' === $select) {
      if (!($this->from instanceof Core_AclBase)) throw new Exception("select @instances allowed only for Acl object");
      $instanceTable = ACL::getInstanceTable($this->from->getNamespace());
      return "
        (
          select GROUP_CONCAT(inst_conf.namespace)
          from `{$instanceTable}` inst_tbl
          join `CRM_INSTANCE_CONFIG` inst_conf on (inst_conf.id = inst_tbl.idInstance)
          where inst_tbl.pk = {$this->_fromPrefix}.{$sqlPk}
        ) as `@instances`
      ";
    }
    if (is_array($select)) {
      // TODO: [ '__backRef' => [ ... ] ]
      DBG::log($select, 'array', "TODO: select array");
      return null;
    }
    if (is_string($select)) {
      // TODO: only real table field
      // TODO: if geometry type then `AsWKT(t.`{$fieldName}`) as {$fieldName}`
      try {
        return $this->parseSelectFieldValueToSql($select, $this->_fromPrefix);
      } catch (Exception $e) {
        DBG::log($e);
      }
    }
    return null;
  }

  public function join($join, $prefix, $params) {
    if (array_key_exists($prefix, $this->_joinPrefix)) throw new Exception("Prefix already used!");
    $this->_joinPrefix[$prefix] = $join;
    $this->_joinParams[$prefix] = $params;
    return $this;
  }

  public function orderBy($orderBy) { // TODO: ogc: order by ...
    if (null !== $this->orderBy) throw new Exception("OrderBy already set!");
    $this->orderBy = [];
    if (!$orderBy) return $this;
    // ID A,COL_X D,COL_Y A,...
    $sortByEx = array_map('trim', explode(',', $orderBy));
    $sortByEx = array_filter($sortByEx, ['V', 'filterNotEmpty']);
    foreach ($sortByEx as $sortPart) {
      $sortPartEx = explode(' ', $sortPart);
      if (count($sortPartEx) > 2) throw new Exception("SortBy parse error #" . __LINE__);
      $fieldName = trim($sortPartEx[0]);
      if (!$this->isFieldAllowedToOrderBy($fieldName)) throw new Exception("SortBy parse error for field '{$fieldName}' #" . __LINE__);
      $colSortDir = 'ASC';
      if (count($sortPartEx) == 2) {
        if ('A' == strtoupper($sortPartEx[1]) || 'ASC' == strtoupper($sortPartEx[1])) {
        } else if ('D' == strtoupper($sortPartEx[1]) || 'DESC' == strtoupper($sortPartEx[1])) {
          $colSortDir = 'DESC';
        } else throw new Exception("SortBy parse error - unknown sort order '{$sortPartEx[1]}' #" . __LINE__);
      }
      $this->orderBy[] = [$fieldName, $colSortDir];
      DBG::log($this->orderBy, 'array', "sortBy");
    }
    return $this;
  }
  public function isFieldAllowedToOrderBy($fieldName) {
    return true;
  }
  public function generateOrderBySql() {
    if (empty($this->orderBy)) return '';
    $sortByList = [];
    foreach ($this->orderBy as $orderBy) {
      $sortByList[] = "t.`{$orderBy[0]}` {$orderBy[1]}";
    }
    return (!empty($sortByList))
      ? "order by " . implode(", ", $sortByList)
      : '';
  }

  public function limit($limit) {
    $this->limit = (int)$limit;
    return $this;
  }

  public function offset($offset) {
    $this->offset = (int)$offset;
    return $this;
  }

  public function isInstance($instances) {
    $this->isInstances = (is_array($instances)) ? $instances : [ $instances ];
    return $this;
  }

  public function isNotInstance($instances) {
    $this->isNotInstances = (is_array($instances)) ? $instances : [ $instances ];
    return $this;
  }

  public function _parseJoinParams($params) {
    if (array_key_exists('rawJoin', $params)) return $params['rawJoin'];
    throw new Exception("Not implemented JOIN params '".json_encode($params)."'");
  }

  public function _getTableName($source) {
    if (is_scalar($source)) return $source;
    if ($source instanceof Core_AclBase) return $source->getRootTableName();
    throw new Exception("Not implemented FROM type '".get_class($source)."'");
  }

  public function execute() {
    return $this->fetchAll();
  }

  public function fetchAll() {
    $sql = $this->generateSql();
    DBG::log((array)$this, 'array', "AclQueryBuilder::fetchAll");
    return DB::getPDO()->fetchAll($sql);
  }

  public function fetchTotal() {
    $sql = $this->generateSql("count(*) as cnt");
    return DB::getPDO()->fetchValue($sql);
  }

  public function fetchValue() {
    $sql = $this->generateSql();
    DBG::log(['sql'=>$sql,'this'=>(array)$this], 'array', "AclQueryBuilder::fetchValue");
    return DB::getPDO()->fetchValue($sql);
  }

  public function fetchFirst() {
    $sql = $this->generateSql();
    DBG::log(['sql'=>$sql,'this'=>(array)$this], 'array', "AclQueryBuilder::fetchFirst");
    return DB::getPDO()->fetchFirst($sql);
  }

  public function generateSql($select = null) {
    if (!$this->from) throw new Exception("Missing FROM");
    $tableName = $this->_getTableName($this->from);
    if (!$tableName) throw new Exception("Missing FROM table name");

    $sqlPk = ($this->from instanceof Core_AclBase)
      ? $this->from->getSqlPrimaryKeyField()
      : 'ID';

    $sqlJoin = [];
    DBG::log($this, 'array', '$this');
    foreach ($this->isInstances as $k => $ns) {
      $idInstance = ACL::getInstanceId($ns);
      $instanceTable = ACL::getInstanceTable($ns);
      // ->join($instanceTable, 'i', [ 'rawJoin' => "i.pk = t.{$sqlPk} and i.idInstance = {$idInstance}" ])
      $prefix = "is_inst_{$k}";
      // $sqlJoin[] = " inner join `{$joinName}` {$prefix} on (
      //   {$prefix}.pk = {$this->_fromPrefix}.{$sqlPk}
      //   and {$prefix}.idInstance = {$idInstance}
      // )";
      $this->where[] = "{$this->_fromPrefix}.{$sqlPk} in (
        select {$prefix}.pk
        from `{$instanceTable}` {$prefix}
        where {$prefix}.idInstance = {$idInstance}
      )";
      DBG::log("{$this->_fromPrefix}.{$sqlPk} in (
        select {$prefix}.pk
        from `{$instanceTable}` {$prefix}
        where {$prefix}.idInstance = {$idInstance}
      )", 'string', "\$this->where[] =");
    }
    foreach ($this->isNotInstances as $k => $ns) {
      $idInstance = ACL::getInstanceId($ns);
      $instanceTable = ACL::getInstanceTable($ns);
      // ->join($instanceTable, 'i', [ 'rawJoin' => "i.pk = t.{$sqlPk} and i.idInstance = {$idInstance}" ])
      $prefix = "is_inst_{$k}";
      // $sqlJoin[] = " inner join `{$joinName}` {$prefix} on (
      //   {$prefix}.pk = {$this->_fromPrefix}.{$sqlPk}
      //   and {$prefix}.idInstance = {$idInstance}
      // )";
      $this->where[] = "{$this->_fromPrefix}.{$sqlPk} not in (
        select {$prefix}.pk
        from `{$instanceTable}` {$prefix}
        where {$prefix}.idInstance = {$idInstance}
      )";
    }
    // join `{$instanceTable}` i on(i.pk = t.{$sqlPk} and i.idInstance = {$idInstance})
    foreach ($this->_joinPrefix as $prefix => $join) {
      $joinName = $this->_getTableName($join);
      $sqlJoin[] = " join `{$joinName}` {$prefix} on(" . $this->_parseJoinParams($this->_joinParams[$prefix]) . ")";
    }

    $sqlJoin = (!empty($sqlJoin)) ? implode("\n\t", $sqlJoin) : "";

    DBG::log($this->where, 'array', "generateSql \$this->where");
    $sqlWhere = array_filter(
      array_map([$this, '_generateWhereMain'], $this->where),
      'is_string'
    );
    $sqlWhere = (!empty($sqlWhere)) ? "where " . implode("\n\t and ", $sqlWhere) : '';

    $limit = ($this->limit < 0) ? 0 : $this->limit;
    $offset = ($this->offset < 0) ? 0 : $this->offset;
    $sqlLimit = ($limit > 0) ? "limit {$limit} offset {$offset}" : '';

    // TODO: split select to local and remote (use field isLocal)
    // TODO: $this->_hasSelectRemoteFields = false;
    // TODO: $this->_hasQueryRemoteFields = false;

    if ($select) {
      $sqlSelect = $select; // used for example fetchTotal -> generateSql("count(*) as cnt")
    } else {
      $sqlSelect = array_filter(
        array_map([$this, '_generateSelectMain'], $this->select, array_keys($this->select)),
        'is_string'
      );
      $sqlSelect = (!empty($sqlSelect))
        ? implode(",\n\t", $sqlSelect)
        : "{$this->_fromPrefix}.*"
      ;
    }

    $sqlOrderBy = $this->generateOrderBySql();
    return "
      select {$sqlSelect}
      from `{$tableName}` {$this->_fromPrefix}
        {$sqlJoin}
      {$sqlWhere}
      {$sqlOrderBy}
      {$sqlLimit}
    ";
  }

  public function parseSelectFieldValueToSql($fieldName, $prefix = 't') {
    if (false !== strpos($fieldName, '/')) {
      DBG::log($fieldName, 'string', "TODO: select by xpath");
      throw new Exception("Not implemented select by xpath ('{$fieldName}')");
    }

    $fieldType = $this->from->getXsdFieldType($fieldName);
    @list($typePrefix, $typeName, $retTypeName) = explode(':', $fieldType);
    switch ($typePrefix) {
      case 'xsd': return "{$prefix}.`{$fieldName}`";
      // 'gml:PolygonPropertyType':
      // 'gml:PointPropertyType':
      // 'gml:LineStringPropertyType':
      // 'gml:GeometryPropertyType':
      case 'gml': return "AsWKT({$prefix}.`{$fieldName}`) as `{$fieldName}`";
      case 'p5': {
        switch ($typeName) {
          case 'price': return "{$prefix}.`{$fieldName}`";
          case 'enum': return "{$prefix}.`{$fieldName}`"; // TODO: check if local or remote
          case 'www_link': return "{$prefix}.`{$fieldName}`"; // TODO: check if local or remote?
          default: throw new Exception("Not implemented field type in select '{$fieldType}' (field: '{$fieldName}')");
        }
      }
      default: throw new Exception("Not implemented field type in select '{$fieldType}' (field: '{$fieldName}')");
    }
    return null;
  }

}