se/SE/se-lib/SqlQueryWhereBuilder.php

<?php

class SqlQueryWhereBuilder {

	private $_log = array();
	private $_usedFields = array();
	private $_raw = "";

	public function __construct() {
	}

	public function openBlock($blockType) {
		if (!$this->isAllowedBlockType($blockType)) throw new Exception("unsupported block type");
		$this->_log[] = "open_block_{$blockType}";
	}

	public function closeBlock($blockType) {
		if (!$this->isAllowedBlockType($blockType)) throw new Exception("unsupported block type");
		$this->_log[] = "close_block_{$blockType}";
	}

	public function addComparisonFieldToValue($fieldName, $comparisonSign, $value) {
		$sqlCompSign = "";
		switch ($comparisonSign) {
			case '=': $sqlCompSign = '='; break;
			case 'like': $sqlCompSign = 'like'; break;
			case 'GreaterThen': $sqlCompSign = '>'; break;
			case 'LessThen': $sqlCompSign = '<'; break;
			case 'GreaterThenOrEqualTo': $sqlCompSign = '>='; break;
			case 'LessThenOrEqualTo': $sqlCompSign = '<='; break;
			default: throw new Exception("Unsupported comparison sign");
		}
		$this->_log[] = array('comparisonFieldToValue', $fieldName, $sqlCompSign, $value);
	}

	public function addComparisonFieldFunToValue($fieldFun, $fieldName, $comparisonSign, $value) {
		$sqlCompSign = "";
		switch ($comparisonSign) {
			case '=': $sqlCompSign = '='; break;
			case 'like': $sqlCompSign = 'like'; break;
			case 'GreaterThen': $sqlCompSign = '>'; break;
			case 'LessThen': $sqlCompSign = '<'; break;
			case 'GreaterThenOrEqualTo': $sqlCompSign = '>='; break;
			case 'LessThenOrEqualTo': $sqlCompSign = '<='; break;
			default: throw new Exception("Unsupported comparison sign");
		}
		$this->_log[] = array('comparisonFieldFunToValue', $fieldFun, $fieldName, $sqlCompSign, $value);
	}

	public function sql_filter_comparisonFieldIsNull($fieldName) {
		$this->_log[] = array('comparisonFieldIsNull', $fieldName);
	}

	public function getQueryWhere($tablePrefix = '') {
		$sqlWhereRaw = $this->parseQueryWhere();
		$sqlTablePrefix = ($tablePrefix)? "`{$tablePrefix}`." : '';
		return str_replace('{tablePrefix}', $sqlTablePrefix, $sqlWhereRaw);
	}

	public function filterRawArray($item) {
		$lowerItem = array(); foreach ((array)$item as $fieldName => $value) $lowerItem[strtolower($fieldName)] = $value;
		DBG::_('DBG_DS_OGC', '>2', "\$lowerItem", $lowerItem, __CLASS__, __FUNCTION__, __LINE__);

		$sqlBlocksStack = array();
		$sqlValuesStack = array();
		$arr = array(); array_push($sqlValuesStack, $arr);// empty array to start
		foreach ($this->_log as $idxLog => $log) {
			switch ($log) {
				case 'open_block_and':
				case 'open_block_or':
				case 'open_block_not': {
					$blockType = substr($log, 11);// 'and', 'or', 'not'
					array_push($sqlBlocksStack, $blockType);
					$arr = array(); array_push($sqlValuesStack, $arr);
					DBG::_('DBG_DS_OGC', '>2', "\nlog loop({$idxLog}) '{$log}' \$sqlBlocksStack", $sqlBlocksStack, __CLASS__, __FUNCTION__, __LINE__);
					DBG::_('DBG_DS_OGC', '>2', "\nlog loop({$idxLog}) '{$log}' \$sqlValuesStack", $sqlValuesStack, __CLASS__, __FUNCTION__, __LINE__);
				}
					break;
				case 'close_block_and': {
					$blockType = substr($log, 12);// 'and', 'or'
					if (empty($sqlBlocksStack)) throw new Exception("parse sql query failed - blocks stack is empty");
					$stackBlockType = array_pop($sqlBlocksStack);
					if ($blockType != $stackBlockType) throw new Exception("parse sql query failed - expected stop '{$blockType}', given '{$stackBlockType}'");

					// parse to string and add to parent value stack
					$stackValue = array_pop($sqlValuesStack);
					if (!is_array($stackValue)) throw new Exception("parse sql query failed - stack value is not array");
					if (empty($stackValue)) throw new Exception("parse sql query failed - stack value is empty");
					$sqlFromStack = true;
					foreach ($stackValue as $bool) {
						if (!is_scalar($bool) && ($bool == true or $bool == false)) throw new Exception("parse query failed - expected bool values in block or");
						if (!$bool) $sqlFromStack = false;
					}
					$parentStackValue = array_pop($sqlValuesStack);
					if (!is_array($parentStackValue)) throw new Exception("parse sql query failed - parent stack value is not array");
					array_push($parentStackValue, $sqlFromStack);
					array_push($sqlValuesStack, $parentStackValue);
					DBG::_('DBG_DS_OGC', '>2', "\nlog loop({$idxLog}) '{$log}' \$sqlBlocksStack", $sqlBlocksStack, __CLASS__, __FUNCTION__, __LINE__);
					DBG::_('DBG_DS_OGC', '>2', "\nlog loop({$idxLog}) '{$log}' \$sqlValuesStack", $sqlValuesStack, __CLASS__, __FUNCTION__, __LINE__);
				}
					break;
				case 'close_block_or': {
					$blockType = substr($log, 12);// 'and', 'or'
					if (empty($sqlBlocksStack)) throw new Exception("parse sql query failed - blocks stack is empty");
					$stackBlockType = array_pop($sqlBlocksStack);
					if ($blockType != $stackBlockType) throw new Exception("parse sql query failed - expected stop '{$blockType}', given '{$stackBlockType}'");

					// parse to string and add to parent value stack
					$stackValue = array_pop($sqlValuesStack);
					if (!is_array($stackValue)) throw new Exception("parse sql query failed - stack value is not array");
					if (empty($stackValue)) throw new Exception("parse sql query failed - stack value is empty");
					$sqlFromStack = false;
					foreach ($stackValue as $bool) {
						if (!is_scalar($bool) && ($bool == true or $bool == false)) throw new Exception("parse query failed - expected bool values in block or");
						if ($bool) $sqlFromStack = true;
					}
					$parentStackValue = array_pop($sqlValuesStack);
					if (!is_array($parentStackValue)) throw new Exception("parse sql query failed - parent stack value is not array");
					array_push($parentStackValue, $sqlFromStack);
					array_push($sqlValuesStack, $parentStackValue);
					DBG::_('DBG_DS_OGC', '>2', "\nlog loop({$idxLog}) '{$log}' \$sqlBlocksStack", $sqlBlocksStack, __CLASS__, __FUNCTION__, __LINE__);
					DBG::_('DBG_DS_OGC', '>2', "\nlog loop({$idxLog}) '{$log}' \$sqlValuesStack", $sqlValuesStack, __CLASS__, __FUNCTION__, __LINE__);
				}
					break;
				case 'close_block_not': {
					$blockType = substr($log, 12);// 'not'
					if (empty($sqlBlocksStack)) throw new Exception("parse sql query failed - blocks stack is empty");
					$stackBlockType = array_pop($sqlBlocksStack);
					if ($blockType != $stackBlockType) throw new Exception("parse sql query failed - expected stop '{$blockType}', given '{$stackBlockType}'");

					// parse to string and add to parent value stack
					$stackValue = array_pop($sqlValuesStack);
					if (!is_array($stackValue)) throw new Exception("parse sql query failed - stack value is not array");
					if (empty($stackValue)) throw new Exception("parse sql query failed - stack value is empty");
					if (1 != count($stackValue)) throw new Exception("parse sql query failed - stack value count is not equal to 1");
					$stackValue = reset($stackValue);
					$sqlFromStack = " ! ({$stackValue}) ";
					$parentStackValue = array_pop($sqlValuesStack);
					if (!is_array($parentStackValue)) throw new Exception("parse sql query failed - parent stack value is not array");
					array_push($parentStackValue, $sqlFromStack);
					array_push($sqlValuesStack, $parentStackValue);
					DBG::_('DBG_DS_OGC', '>2', "\nlog loop({$idxLog}) '{$log}' \$sqlBlocksStack", $sqlBlocksStack, __CLASS__, __FUNCTION__, __LINE__);
					DBG::_('DBG_DS_OGC', '>2', "\nlog loop({$idxLog}) '{$log}' \$sqlValuesStack", $sqlValuesStack, __CLASS__, __FUNCTION__, __LINE__);
				}
					break;
				default: {
					if (is_array($log) && 4 == count($log) && 'comparisonFieldToValue' == $log[0]) {
						list($logType, $fieldName, $compSign, $compValue) = $log;
						$fieldName = strtolower($fieldName);
						if (!array_key_exists($fieldName, $lowerItem)) throw new Exception("field '{$fieldName}' not defined");
						$fieldValue = $lowerItem[$fieldName];
						switch ($compSign) {
							case '=': {
								$sqlFromStack = ($fieldValue == $compValue);
								DBG::_('DBG_DS_OGC', '>2', "\nlog loop({$idxLog}) '{$logType}' comparison({$fieldValue} == {$compValue}) \$sqlFromStack(".($sqlFromStack ? 'true' : 'false').")", null, __CLASS__, __FUNCTION__, __LINE__);
								break;
							}
							case 'like': {
								$pattern = str_replace('%', '(.*)', $compValue);
								$sqlFromStack = (bool)preg_match("/^{$pattern}\$/i", $fieldValue);
								DBG::_('DBG_DS_OGC', '>2', "\nlog loop({$idxLog}) '{$logType}' comparison(preg_match('/^{$pattern}\$/i', '{$fieldValue}')) \$sqlFromStack(".($sqlFromStack ? 'true' : 'false').")", null, __CLASS__, __FUNCTION__, __LINE__);
								break;
							}
							case '>=': {
								$sqlFromStack = (bool)($fieldValue >= $compValue);
								DBG::_('DBG_DS_OGC', '>2', "\nlog loop({$idxLog}) '{$logType}' comparison({$compValue} {$compSign} '{$fieldValue}')) \$sqlFromStack(".($sqlFromStack ? 'true' : 'false').")", null, __CLASS__, __FUNCTION__, __LINE__);
								break;
							}
							case '<=': {
								$sqlFromStack = (bool)($fieldValue <= $compValue);
								DBG::_('DBG_DS_OGC', '>2', "\nlog loop({$idxLog}) '{$logType}' comparison({$compValue} {$compSign} '{$fieldValue}')) \$sqlFromStack(".($sqlFromStack ? 'true' : 'false').")", null, __CLASS__, __FUNCTION__, __LINE__);
								break;
							}
							case '>': {
								$sqlFromStack = (bool)($fieldValue > $compValue);
								DBG::_('DBG_DS_OGC', '>2', "\nlog loop({$idxLog}) '{$logType}' comparison({$compValue} {$compSign} '{$fieldValue}')) \$sqlFromStack(".($sqlFromStack ? 'true' : 'false').")", null, __CLASS__, __FUNCTION__, __LINE__);
								break;
							}
							case '<': {
								$sqlFromStack = (bool)($fieldValue < $compValue);
								DBG::_('DBG_DS_OGC', '>2', "\nlog loop({$idxLog}) '{$logType}' comparison({$compValue} {$compSign} '{$fieldValue}')) \$sqlFromStack(".($sqlFromStack ? 'true' : 'false').")", null, __CLASS__, __FUNCTION__, __LINE__);
								break;
							}
							default: throw new Exception("compaison sign '{$compSign}' not defined");
						}
						// $this->_usedFields[$sqlFieldName] = true;
						// $sqlFromStack = "{tablePrefix}`{$sqlFieldName}` {$compSign} '{$compValue}'";
						$stackValue = array_pop($sqlValuesStack);
						if (!is_array($stackValue)) throw new Exception("parse sql query failed - stack value is not array");
						array_push($stackValue, $sqlFromStack);
						array_push($sqlValuesStack, $stackValue);
					} else if (is_array($log) && 5 == count($log) && 'comparisonFieldFunToValue' == $log[0]) {
						$sqlFieldFunName = $log[1];
						if (strtolower($sqlFieldFunName) != 'geometrytype') throw new Exception("Unsupported db function {$sqlFieldName}");
						$sqlFieldName = $log[2];
						// $this->_usedFields[$sqlFieldName] = true;
						$sqlFromStack = "{$sqlFieldFunName}({tablePrefix}`{$sqlFieldName}`) {$log[3]} '{$log[4]}'";
						$stackValue = array_pop($sqlValuesStack);
						if (!is_array($stackValue)) throw new Exception("parse sql query failed - stack value is not array");
						array_push($stackValue, $sqlFromStack);
						array_push($sqlValuesStack, $stackValue);
					} else if (is_array($log) && 2 == count($log) && 'comparisonFieldIsNull' == $log[0]) {
						$sqlFieldName = $log[1];
						$sqlFromStack = "{tablePrefix}`{$sqlFieldName}` is null";
						$stackValue = array_pop($sqlValuesStack);
						if (!is_array($stackValue)) throw new Exception("parse sql query failed - stack value is not array");
						array_push($stackValue, $sqlFromStack);
						array_push($sqlValuesStack, $stackValue);
					} else {
						throw new Exception("parse sql query failed - unknown '" . json_encode($log) . "'");
					}
					DBG::_('DBG_DS_OGC', '>2', "\nlog loop({$idxLog}) '{$log}' \$sqlBlocksStack", $sqlBlocksStack, __CLASS__, __FUNCTION__, __LINE__);
					DBG::_('DBG_DS_OGC', '>2', "\nlog loop({$idxLog}) '{$log}' \$sqlValuesStack", $sqlValuesStack, __CLASS__, __FUNCTION__, __LINE__);
				}
			}
		}
		if (!empty($sqlBlocksStack)) throw new Exception("parse sql query failed - blocks stack is not empty");
		if (1 !== count($sqlValuesStack)) throw new Exception("parse sql query failed - values stack is empty");

		if (1 !== count($sqlValuesStack[0])) throw new Exception("parse sql query failed - values stack is empty");
		$bool = $sqlValuesStack[0][0];
		if (!is_scalar($bool) && ($bool == true or $bool == false)) throw new Exception("parse query failed - expected bool in stack value");

		DBG::_('DBG_DS_OGC', '>2', "\nOK return ".($bool ? 'true' : 'false')." for item:", $item, __CLASS__, __FUNCTION__, __LINE__);
		return $bool;
	}

	public function parseQueryWhere() {
		$this->_raw = "";
		$this->_usedFields = array();

		$sqlBlocksStack = array();
		$sqlValuesStack = array();
		$arr = array(); array_push($sqlValuesStack, $arr);// empty array to start
		foreach ($this->_log as $log) {
			switch ($log) {
				case 'open_block_and':
				case 'open_block_or':
				case 'open_block_not': {
					$blockType = substr($log, 11);
					array_push($sqlBlocksStack, $blockType);
					$arr = array(); array_push($sqlValuesStack, $arr);
//echo "L.".__LINE__.":sqlBlocksStack:" . json_encode($sqlBlocksStack) . "\n";
//echo "L.".__LINE__.":sqlValuesStack:" . json_encode($sqlValuesStack) . "\n\n";
				}
					break;
				case 'close_block_and':
				case 'close_block_or': {
					$blockType = substr($log, 12);
					if (empty($sqlBlocksStack)) throw new Exception("parse sql query failed - blocks stack is empty");
					$stackBlockType = array_pop($sqlBlocksStack);
					if ($blockType != $stackBlockType) throw new Exception("parse sql query failed - expected stop '{$blockType}', given '{$stackBlockType}'");

					// parse to string and add to parent value stack
					$stackValue = array_pop($sqlValuesStack);
					if (!is_array($stackValue)) throw new Exception("parse sql query failed - stack value is not array");
					if (empty($stackValue)) throw new Exception("parse sql query failed - stack value is empty");
					$sqlFromStack = " ( " . implode(" {$blockType} ", $stackValue) . " ) ";
					$parentStackValue = array_pop($sqlValuesStack);
					if (!is_array($parentStackValue)) throw new Exception("parse sql query failed - parent stack value is not array");
					array_push($parentStackValue, $sqlFromStack);
					array_push($sqlValuesStack, $parentStackValue);
				}
					break;
				case 'close_block_not': {
					$blockType = substr($log, 12);
					if (empty($sqlBlocksStack)) throw new Exception("parse sql query failed - blocks stack is empty");
					$stackBlockType = array_pop($sqlBlocksStack);
					if ($blockType != $stackBlockType) throw new Exception("parse sql query failed - expected stop '{$blockType}', given '{$stackBlockType}'");

					// parse to string and add to parent value stack
					$stackValue = array_pop($sqlValuesStack);
					if (!is_array($stackValue)) throw new Exception("parse sql query failed - stack value is not array");
					if (empty($stackValue)) throw new Exception("parse sql query failed - stack value is empty");
					if (1 != count($stackValue)) throw new Exception("parse sql query failed - stack value count is not equal to 1");
					$stackValue = reset($stackValue);
					$sqlFromStack = " ! ({$stackValue}) ";
					$parentStackValue = array_pop($sqlValuesStack);
					if (!is_array($parentStackValue)) throw new Exception("parse sql query failed - parent stack value is not array");
					array_push($parentStackValue, $sqlFromStack);
					array_push($sqlValuesStack, $parentStackValue);
				}
					break;
				default: {
					if (is_array($log) && 4 == count($log) && 'comparisonFieldToValue' == $log[0]) {
						$sqlFieldName = $log[1];
						$this->_usedFields[$sqlFieldName] = true;
						$sqlFromStack = "{tablePrefix}`{$sqlFieldName}` {$log[2]} '{$log[3]}'";
						$stackValue = array_pop($sqlValuesStack);
						if (!is_array($stackValue)) throw new Exception("parse sql query failed - stack value is not array");
						array_push($stackValue, $sqlFromStack);
						array_push($sqlValuesStack, $stackValue);
//echo "L.".__LINE__.":sqlBlocksStack:" . json_encode($sqlBlocksStack) . "\n";
//echo "L.".__LINE__.":sqlValuesStack:" . json_encode($sqlValuesStack) . "\n\n";
					} else if (is_array($log) && 5 == count($log) && 'comparisonFieldFunToValue' == $log[0]) {
						$sqlFieldFunName = $log[1];
						if (strtolower($sqlFieldFunName) != 'geometrytype') throw new Exception("Unsupported db function {$sqlFieldName}");
						$sqlFieldName = $log[2];
						$this->_usedFields[$sqlFieldName] = true;
						$sqlFromStack = "{$sqlFieldFunName}({tablePrefix}`{$sqlFieldName}`) {$log[3]} '{$log[4]}'";
						$stackValue = array_pop($sqlValuesStack);
						if (!is_array($stackValue)) throw new Exception("parse sql query failed - stack value is not array");
						array_push($stackValue, $sqlFromStack);
						array_push($sqlValuesStack, $stackValue);
					} else if (is_array($log) && 2 == count($log) && 'comparisonFieldIsNull' == $log[0]) {
						$sqlFieldName = $log[1];
						$sqlFromStack = "{tablePrefix}`{$sqlFieldName}` is null";
						$stackValue = array_pop($sqlValuesStack);
						if (!is_array($stackValue)) throw new Exception("parse sql query failed - stack value is not array");
						array_push($stackValue, $sqlFromStack);
						array_push($sqlValuesStack, $stackValue);
					} else {
						throw new Exception("parse sql query failed - unknown '" . json_encode($log) . "'");
					}
				}
			}
		}
		// TODO: parse log
//echo "L.".__LINE__.":sqlBlocksStack:" . json_encode($sqlBlocksStack) . "\n";
//echo "L.".__LINE__.":sqlValuesStack:" . json_encode($sqlValuesStack) . "\n--------\n";
		if (!empty($sqlBlocksStack)) throw new Exception("parse sql query failed - blocks stack is not empty");
		if (1 !== count($sqlValuesStack)) throw new Exception("parse sql query failed - values stack is empty");
		$this->_raw = implode("\n", $sqlValuesStack[0]);
		return $this->_raw;
	}

	public function getUsedFields() {
		return array_keys($this->_usedFields);
	}

	public function isAllowedBlockType($blockType) {
		if ('and' == $blockType || 'or' == $blockType) {
			return true;
		} else if ('not' == $blockType) {
			return true;
		}
		return false;
	}

	public function splitQueryToWords($q) {
		$searchWords = explode(' ', $q);
		$sqlSearchWords = array();
		if (!empty($searchWords)) {
			foreach ($searchWords as $word) {
				if (!empty($word) || '0' === $word) {
					$sqlSearchWords[] = $word;
				}
			}
		}
		return $sqlSearchWords;
	}

}