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 $_joinType;
	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->_joinType = []; // prefix => ( 'join' | 'left join' )
		$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),
		// 	'$from' => $from
		// ], '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($prefixedFieldName, $comparisonSign, $value) = $where;
		// $sqlFieldName = $fieldName; // TODO: getSqlFieldName // TODO: get sql field name with table prefix from join list to replace "{$this->_fromPrefix}.{$sqlFieldName}" below
		$prefixedFieldName = (false === strpos($prefixedFieldName, '.')) ? "{$this->_fromPrefix}.{$prefixedFieldName}" : $prefixedFieldName;
		list($prefix, $fieldName) = explode('.', $prefixedFieldName);
		$sqlPrefix = $this->getPDO()->identifierQuote($prefix);
		$sqlFieldName = $this->getPDO()->identifierQuote($fieldName);
		switch ($comparisonSign) {
			case 'is not null': return "{$sqlPrefix}.{$sqlFieldName} is not null";
			case 'is null': return "{$sqlPrefix}.{$sqlFieldName} is null";
			case 'Intersects': return "Intersects(GeomFromText('{$value}'), {$sqlPrefix}.{$sqlFieldName})=1";
			case 'GeometryType': return "GeometryType({$sqlPrefix}.{$sqlFieldName})='{$value}'";
			case 'or': return $this->_generateWhereBlock($where);
			case 'and': return $this->_generateWhereBlock($where);
			case 'UNIX_TIMESTAMP_LESS_THAN_NOW': return "
				COALESCE(UNIX_TIMESTAMP({$sqlPrefix}.{$sqlFieldName}), 0) < UNIX_TIMESTAMP()
				and {$sqlPrefix}.{$sqlFieldName} != ''
				and {$sqlPrefix}.{$sqlFieldName} != '0000-00-00 00:00:00'
			";
			case 'UNIX_TIMESTAMP_NOW_3600': return "
				COALESCE(UNIX_TIMESTAMP({$sqlPrefix}.{$sqlFieldName}), 0) < UNIX_TIMESTAMP()+3600
				and COALESCE(UNIX_TIMESTAMP({$sqlPrefix}.{$sqlFieldName}), 0) > UNIX_TIMESTAMP()-3600
			";
			case 'UNIX_TIMESTAMP_GREATER_THAN': return " COALESCE(UNIX_TIMESTAMP({$sqlPrefix}.{$sqlFieldName}), 0) > '{$value}' ";
			case 'UNIX_TIMESTAMP_LESS_THAN':		return " COALESCE(UNIX_TIMESTAMP({$sqlPrefix}.{$sqlFieldName}), 0) < '{$value}' ";
			default: return "{$sqlPrefix}.{$sqlFieldName} {$comparisonSign} " . $this->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 ('@primaryKey' === $select) {
			return "{$this->_fromPrefix}.{$sqlPk} as " . $this->getPDO()->identifierQuote("@primaryKey");
		}
		if ('@instances' === $select) {
			if (!($this->from instanceof Core_AclBase)) throw new Exception("select @instances allowed only for Acl object");
			// TODO: fetch list possible instances, get all instance table name
			// group_concat(
			// > foreach ($listInstanceConfig as $instanceConfig) {
			//  IF( ( select 1 from `{$instanceTable}` where ID = {$this->_fromPrefix}.{$sqlPk} ) > 0, '{$instanceName},', ''),
			// )
			return " '' as " . $this->getPDO()->identifierQuote("@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.{$sqlFieldName}) as {$fieldName}`
			try {
				return $this->parseSelectFieldValueToSql($select, $this->_fromPrefix);
			} catch (Exception $e) {
				DBG::log($e);
			}
		}
		return null;
	}

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

	public function leftJoin($remoteTableName, $prefix, $params) {
		if (array_key_exists($prefix, $this->_joinPrefix)) throw new Exception("Prefix already used!");
		$this->_joinPrefix[$prefix] = $remoteTableName;
		$this->_joinParams[$prefix] = $params;
		$this->_joinType[$prefix] = 'left join';
		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,...
		if (false !== strpos($orderBy, '+')) $orderBy = str_replace('+', ' ', $orderBy);
		$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; // TODO:? only local fields in $this->from
	}
	public function generateOrderBySql() {
		if (empty($this->orderBy)) return '';
		$sortByList = [];
		foreach ($this->orderBy as $orderBy) {
			list($fieldName, $orderDir) = $orderBy;
			if (false !== strpos($fieldName, '.')) {
				list($joinPrefix, $joinFieldName) = explode('.', $fieldName, 2);
				$sqlOrderByField = $this->getPDO()->identifierQuote($joinFieldName);
				if ('t' !== $joinPrefix && !array_key_exists($joinPrefix, $this->_joinParams)) throw new Exception("Missing table prefix '{$joinPrefix}' in orderBy");
				$sortByList[] = "{$joinPrefix}.{$sqlOrderByField} {$orderDir}";
			} else {
				$sqlOrderByField = $this->getPDO()->identifierQuote($fieldName);
				$sortByList[] = "t.{$sqlOrderByField} {$orderDir}";
			}
		}
		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 getPDO() {
		$idDatabase = $this->from->getDB();
		return DB::getPDO($idDatabase);
	}

	public function fetchAll() {
		DBG::log("AclQueryBuilder::fetchAll - generateSql... ns(".$this->from->getNamespace().")");
		$sql = $this->generateSql();
		DBG::log("AclQueryBuilder::fetchAll - generateSql end ns(".$this->from->getNamespace().")");
		return $this->getPDO()->fetchAll($sql);
	}

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

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

	public function fetchFirst() {
		$sql = $this->generateSql();
		DBG::log(['sql'=>$sql,'this'=>(array)$this], 'array', "AclQueryBuilder::fetchFirst");
		return $this->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) {
			$instanceConf = ACL::getInstanceConfig($ns);
			$sqlInstPrefix = "inst_{$instanceConf->id}_is";
			DBG::log($instanceConf, 'array', "DBG: \$instanceConf");
			$sqlJoin[] = " join `{$instanceConf->tableName}` `{$sqlInstPrefix}` on(`{$sqlInstPrefix}`.ID = {$this->_fromPrefix}.{$sqlPk}) ";
		}
		foreach ($this->isNotInstances as $k => $ns) {
			$instanceConf = ACL::getInstanceConfig($ns);
			$sqlInstPrefix = "inst_{$instanceConf->id}_is_not";
			$sqlJoin[] = " left join `{$instanceConf->tableName}` `{$sqlInstPrefix}` on(`{$sqlInstPrefix}`.ID = {$this->_fromPrefix}.{$sqlPk}) ";
			$this->where[] = " `{$sqlInstPrefix}`.ID IS NULL "; // TODO: use view
		}

		foreach ($this->_joinPrefix as $prefix => $join) {
			$joinName = $this->_getTableName($join);
			$sqlJoin[] = " {$this->_joinType[$prefix]} `{$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) : '';
		DBG::log($sqlWhere, 'array', "generateSql \$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();
		$sqlTableName = (is_object($this->from) && method_exists($this->from, 'getSqlTableFrom'))
		? $this->from->getSqlTableFrom()
		: $this->getPDO()->tableNameQuote( $tableName );
		return "
			select {$sqlSelect}
			from {$sqlTableName} {$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);
		$sqlFieldName = $this->getPDO()->identifierQuote($fieldName);
		switch ($typePrefix) {
			case 'xsd': {
				switch ($typeName) {
					case 'base64Binary': return "IF({$prefix}.{$sqlFieldName} is not null, 1, 0) as {$sqlFieldName}";
					default: return "{$prefix}.{$sqlFieldName}";
				}
			}
			// 'gml:PolygonPropertyType':
			// 'gml:PointPropertyType':
			// 'gml:LineStringPropertyType':
			// 'gml:GeometryPropertyType':
			case 'gml': return "AsWKT({$prefix}.{$sqlFieldName}) as {$sqlFieldName}";
			case 'p5': {
				switch ($typeName) {
					case 'text': return "{$prefix}.{$sqlFieldName}";
					case 'price': return "{$prefix}.{$sqlFieldName}";
					case 'enum': return "{$prefix}.{$sqlFieldName}"; // TODO: check if local or remote
					case 'www_link': return "{$prefix}.{$sqlFieldName}"; // TODO: check if local or remote?
					case 'string': return "{$prefix}.{$sqlFieldName}"; // TODO: check if local or remote?
					default: throw new Exception("Not implemented field type in select '{$fieldType}' (field: '{$fieldName}')");
				}
			}
			case 'p5Type': {
				// g 'p5Type:' SE/schema/ant-object/|awk -F'p5Type:' '{print $2}'|awk -F'"' '{print $1}'|sort -u
				// result at @2018-04-17:
				// date
				// dateTime
				// decimal
				// integer
				// lineString
				// point
				// polygon
				// string
				// text
				switch ($typeName) {
					case 'text': return "{$prefix}.{$sqlFieldName}";
					case 'price': return "{$prefix}.{$sqlFieldName}";
					case 'enum': return "{$prefix}.{$sqlFieldName}"; // TODO: check if local or remote
					case 'www_link': return "{$prefix}.{$sqlFieldName}"; // TODO: check if local or remote?
					case 'string': return "{$prefix}.{$sqlFieldName}"; // TODO: check if local or remote?
					case 'date':
					case 'dateTime':
					case 'decimal':
					case 'double':
					case 'float':
					case 'hexBinary':
					case 'text':
					case 'time':
					case 'year':
					case 'integer': return "{$prefix}.{$sqlFieldName}"; // TODO: check if local or remote?
					case 'polygon':
					case 'lineString':
					case 'point': return "AsWKT({$prefix}.{$sqlFieldName}) as {$sqlFieldName}";
					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;
	}

}