core/Segment/SegmentExpression.php

<?php

/**
 * Matomo - free/libre analytics platform
 *
 * @link    https://matomo.org
 * @license https://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
 */

namespace Piwik\Segment;

use Exception;

class SegmentExpression
{
    public const AND_DELIMITER = ';';
    public const OR_DELIMITER = ',';

    public const MATCH_EQUAL = '==';
    public const MATCH_NOT_EQUAL = '!=';
    public const MATCH_GREATER_OR_EQUAL = '>=';
    public const MATCH_LESS_OR_EQUAL = '<=';
    public const MATCH_GREATER = '>';
    public const MATCH_LESS = '<';
    public const MATCH_CONTAINS = '=@';
    public const MATCH_DOES_NOT_CONTAIN = '!@';
    public const MATCH_STARTS_WITH = '=^';
    public const MATCH_ENDS_WITH = '=$';

    public const BOOL_OPERATOR_OR = 'OR';
    public const BOOL_OPERATOR_AND = 'AND';
    public const BOOL_OPERATOR_END = '';

    // Note: you can't write this in the API, but access this feature
    // via field!=        <- IS NOT NULL
    // or via field==     <- IS NULL / empty
    public const MATCH_IS_NOT_NULL_NOR_EMPTY = '::NOT_NULL';
    public const MATCH_IS_NULL_OR_EMPTY = '::NULL';

    // Special case, since we look up Page URLs/Page titles in a sub SQL query
    public const MATCH_ACTIONS_CONTAINS = 'IN';
    public const MATCH_ACTIONS_NOT_CONTAINS = 'NOTIN';

    /**
     * A special match type for segments that require rejecting a visit if any action/conversion/etc. in the visit matches a condition.
     * These operands result in `idvisit NOT IN (...)` subqueries.
     */
    public const MATCH_IDVISIT_NOT_IN = 'IDVISIT_NOTIN';

    public const INDEX_OPERAND_NAME = 0;
    public const INDEX_OPERAND_OPERATOR = 1;
    public const INDEX_OPERAND_VALUE = 2;
    public const INDEX_OPERAND_JOIN_COLUMN = 3;
    public const INDEX_OPERAND_SEGMENT_INFO = 4;

    public const SQL_WHERE_DO_NOT_MATCH_ANY_ROW = "(1 = 0)";
    public const SQL_WHERE_MATCHES_ALL_ROWS = "(1 = 1)";

    protected $string;
    protected $valuesBind = [];
    protected $tree = [];
    protected $parsedSubExpressions = [];

    public function __construct($string)
    {
        $this->string = $string;
        $this->tree = $this->parseTree();
    }

    public function getSegmentDefinition()
    {
        return $this->string;
    }

    public function isEmpty()
    {
        return count($this->tree) == 0;
    }


    public function getSubExpressionCount()
    {
        $count = 0;
        foreach ($this->parsedSubExpressions as $orExpressions) {
            foreach ($orExpressions as $operand) {
                $isExpressionColumnPresent = !empty($operand[self::INDEX_OPERAND_NAME]);
                if ($isExpressionColumnPresent) {
                    ++$count;
                }
            }
        }
        return $count;
    }

    /**
     * Given the array of parsed filters containing, for each filter,
     * the boolean operator (AND/OR) and the operand,
     * Will return the array where the filters are in SQL representation
     *
     * @throws Exception
     * @return array
     */
    public function parseSubExpressions()
    {
        $parsedSubExpressions = array_map(function (array $orExpressions) {
            return array_map(function (string $operand) {
                return $this->parseOperand($operand);
            }, $orExpressions);
        }, $this->tree);
        $this->parsedSubExpressions = $parsedSubExpressions;

        return $parsedSubExpressions;
    }

    private function parseOperand(string $operand): array
    {
        $operand = urldecode($operand);

        $pattern = '/^(.+?)(' . self::MATCH_EQUAL . '|'
            . self::MATCH_NOT_EQUAL . '|'
            . self::MATCH_GREATER_OR_EQUAL . '|'
            . self::MATCH_GREATER . '|'
            . self::MATCH_LESS_OR_EQUAL . '|'
            . self::MATCH_LESS . '|'
            . self::MATCH_CONTAINS . '|'
            . self::MATCH_DOES_NOT_CONTAIN . '|'
            . preg_quote(self::MATCH_STARTS_WITH) . '|'
            . preg_quote(self::MATCH_ENDS_WITH)
            . '){1}(.*)/';
        $match = preg_match($pattern, $operand, $matches);
        if ($match == 0) {
            throw new Exception('The segment condition \'' . $operand . '\' is not valid.');
        }

        $leftMember = $matches[1];
        $operation = $matches[2];
        $valueRightMember = urldecode($matches[3]);

        // is null / is not null
        if ($valueRightMember === '') {
            if ($operation == self::MATCH_NOT_EQUAL) {
                $operation = self::MATCH_IS_NOT_NULL_NOR_EMPTY;
            } elseif ($operation == self::MATCH_EQUAL) {
                $operation = self::MATCH_IS_NULL_OR_EMPTY;
            } else {
                throw new Exception('The segment \'' . $operand . '\' has no value specified. You can leave this value empty ' .
                    'only when you use the operators: ' . self::MATCH_NOT_EQUAL . ' (is not) or ' . self::MATCH_EQUAL . ' (is)');
            }
        }

        return [
            self::INDEX_OPERAND_NAME => $leftMember,
            self::INDEX_OPERAND_OPERATOR => $operation,
            self::INDEX_OPERAND_VALUE => $valueRightMember,
        ];
    }

    /**
     * Set the given expression
     * @param $parsedSubExpressions
     */
    public function setSubExpressionsAfterCleanup($parsedSubExpressions)
    {
        $this->parsedSubExpressions = $parsedSubExpressions;
    }

    /**
     * @param array $availableTables
     */
    public function parseSubExpressionsIntoSqlExpressions(&$availableTables = array())
    {
        $this->valuesBind = [];

        $sqlSubExpressions = array_map(function (array $orExpressions) use (&$availableTables) {
            return array_map(function (array $operandDefinition) use (&$availableTables) {
                $operand = $this->getSqlMatchFromDefinition($operandDefinition, $availableTables);

                if ($operand[self::INDEX_OPERAND_OPERATOR] !== null) {
                    if (is_array($operand[self::INDEX_OPERAND_OPERATOR])) {
                        $this->valuesBind = array_merge($this->valuesBind, $operand[self::INDEX_OPERAND_OPERATOR]);
                    } else {
                        $this->valuesBind[] = $operand[self::INDEX_OPERAND_OPERATOR];
                    }
                }

                $operand = $operand[self::INDEX_OPERAND_NAME];
                return $operand;
            }, $orExpressions);
        }, $this->parsedSubExpressions);

        $this->tree = $sqlSubExpressions;
    }

    /**
     * Given an array representing one filter operand ( left member , operation , right member)
     * Will return an array containing
     * - the SQL substring,
     * - the values to bind to this substring
     *
     * @param array $def
     * @param array $availableTables
     * @throws Exception
     * @return array
     */
    protected function getSqlMatchFromDefinition($def, &$availableTables)
    {
        $field     = $def[self::INDEX_OPERAND_NAME];
        $matchType = $def[self::INDEX_OPERAND_OPERATOR];
        $value     = $def[self::INDEX_OPERAND_VALUE];
        $join      = $def[self::INDEX_OPERAND_JOIN_COLUMN] ?? null;
        $segment   = $def[self::INDEX_OPERAND_SEGMENT_INFO] ?? null;

        // Note: we save the SQL used in the subquery (if there is one), as we don't want to run
        // self::checkFieldIsAvailable() on it.
        $innerSql = null;

        // Segment::getCleanedExpression() may return array(null, $matchType, null)
        $operandWillNotMatchAnyRow = empty($field) && is_null($value);
        if ($operandWillNotMatchAnyRow) {
            if ($matchType == self::MATCH_EQUAL) {
                // eg. pageUrl==DoesNotExist
                // Equal to NULL means it will match none
                $sqlExpression = self::SQL_WHERE_DO_NOT_MATCH_ANY_ROW;
            } elseif ($matchType == self::MATCH_NOT_EQUAL) {
                // eg. pageUrl!=DoesNotExist
                // Not equal to NULL means it matches all rows
                $sqlExpression = self::SQL_WHERE_MATCHES_ALL_ROWS;
            } elseif (
                $matchType == self::MATCH_CONTAINS
                || $matchType == self::MATCH_DOES_NOT_CONTAIN
                || $matchType == self::MATCH_STARTS_WITH
                || $matchType == self::MATCH_ENDS_WITH
            ) {
                // no action was found for CONTAINS / DOES NOT CONTAIN
                // eg. pageUrl=@DoesNotExist -> matches no row
                // eg. pageUrl!@DoesNotExist -> matches no rows
                $sqlExpression = self::SQL_WHERE_DO_NOT_MATCH_ANY_ROW;
            } else {
                // it is not expected to reach this code path
                throw new Exception("Unexpected match type $matchType for your segment. " .
                    "Please report this issue to the Matomo team with the segment you are using.");
            }

            return array($sqlExpression, $value = null);
        }

        $alsoMatchNULLValues = false;
        switch ($matchType) {
            case self::MATCH_EQUAL:
                $sqlMatch = '%s =';
                break;
            case self::MATCH_NOT_EQUAL:
                $sqlMatch = '%s <>';
                $alsoMatchNULLValues = true;
                break;
            case self::MATCH_GREATER:
                $sqlMatch = '%s >';
                break;
            case self::MATCH_LESS:
                $sqlMatch = '%s <';
                break;
            case self::MATCH_GREATER_OR_EQUAL:
                $sqlMatch = '%s >=';
                break;
            case self::MATCH_LESS_OR_EQUAL:
                $sqlMatch = '%s <=';
                break;
            case self::MATCH_CONTAINS:
                $sqlMatch = '%s LIKE';
                $value = '%' . $this->escapeLikeString($value) . '%';
                break;
            case self::MATCH_DOES_NOT_CONTAIN:
                $sqlMatch = '%s NOT LIKE';
                $value = '%' . $this->escapeLikeString($value) . '%';
                $alsoMatchNULLValues = true;
                break;
            case self::MATCH_STARTS_WITH:
                $sqlMatch = '%s LIKE';
                $value = $this->escapeLikeString($value) . '%';
                break;
            case self::MATCH_ENDS_WITH:
                $sqlMatch = '%s LIKE';
                $value = '%' . $this->escapeLikeString($value);
                break;

            case self::MATCH_IS_NOT_NULL_NOR_EMPTY:
                $sqlMatch = '%s IS NOT NULL AND %s <> \'\' AND %s <> \'0\'';
                $value = null;
                break;

            case self::MATCH_IS_NULL_OR_EMPTY:
                $sqlMatch = '%s IS NULL OR %s = \'\' OR %s = \'0\'';
                $value = null;
                break;

            case self::MATCH_ACTIONS_CONTAINS:
                // this match type is not accessible from the outside
                // (it won't be matched in self::parseSubExpressions())
                // it can be used internally to inject sub-expressions into the query.
                // see Segment::getCleanedExpression()
                $innerSql = $value['SQL'];
                $sqlMatch = '%s IN (%innerSql%)';
                $value = $value['bind'];
                break;
            case self::MATCH_ACTIONS_NOT_CONTAINS:
                // this match type is not accessible from the outside
                // (it won't be matched in self::parseSubExpressions())
                // it can be used internally to inject sub-expressions into the query.
                // see Segment::getCleanedExpression()
                $innerSql = $value['sql'];
                $sqlMatch = '%s NOT IN (%innerSql%)';
                $value = $value['bind'];
                break;
            default:
                throw new Exception("Filter contains the match type '" . $matchType . "' which is not supported");
                break;
        }

        // We match NULL values when rows are excluded only when we are not doing a
        $alsoMatchNULLValues = $alsoMatchNULLValues && !empty($value);

        if (
            $matchType === self::MATCH_ACTIONS_CONTAINS || $matchType === self::MATCH_ACTIONS_NOT_CONTAINS
            || is_null($value)
        ) {
            $sqlExpression = "( $sqlMatch )";
        } else {
            if ($alsoMatchNULLValues) {
                $sqlExpression = "( $field IS NULL OR $sqlMatch ? )";
            } else {
                $sqlExpression = "$sqlMatch ?";
            }
        }

        if (!empty($join['field'])) {
            $sqlExpression = str_replace('%s', $join['field'], $sqlExpression);
        } else {
            $sqlExpression = str_replace('%s', $field, $sqlExpression);
        }

        if (!empty($join['discriminator'])) {
            $sqlExpression = '(' . $sqlExpression . ' AND ' . $join['discriminator'] . ')';
        }

        $columns = self::parseColumnsFromSqlExpr($sqlExpression);
        foreach ($columns as $column) {
            $this->checkFieldIsAvailable($column, $availableTables, $join);
        }

        if (!empty($join['field'])) {
            $this->checkFieldIsAvailable($join['field'], $availableTables, $join);
        }

        if (!empty($join['joinOn'])) {
            $joinOnColumns = self::parseColumnsFromSqlExpr($join['joinOn']);
            foreach ($joinOnColumns as $column) {
                $this->checkFieldIsAvailable($column, $availableTables, $join);
            }
        }

        if ($innerSql) {
            $sqlExpression = str_replace('%innerSql%', $innerSql, $sqlExpression);
        }

        return array($sqlExpression, $value);
    }

    /**
     * @param string $field
     * @return string[]
     */
    public static function parseColumnsFromSqlExpr($field)
    {
        preg_match_all('/[^@a-zA-Z0-9_]?`?([@a-zA-Z_][@a-zA-Z0-9_]*`?\.`?[a-zA-Z0-9_`]+)`?\b/', $field, $matches);
        $result = isset($matches[1]) ? $matches[1] : [];
        // remove uses of session vars
        $result = array_filter($result, function ($value) {
            return strpos($value, '@') === false;
        });
        $result = array_map(function ($item) {
            return str_replace('`', '', $item);
        }, $result);
        $result = array_unique($result);
        $result = array_values($result);
        return $result;
    }

    /**
     * Check whether the field is available
     * If not, add it to the available tables
     *
     * @param string $field
     * @param array $availableTables
     */
    private function checkFieldIsAvailable($field, &$availableTables, $join)
    {
        $fieldParts = explode('.', $field);

        $table = count($fieldParts) == 2 ? $fieldParts[0] : false;

        // remove sql functions from field name
        // example: `HOUR(log_visit.visit_last_action_time)` gets `HOUR(log_visit` => remove `HOUR(`
        $table = preg_replace('/^[A-Z_]+\(/', '', $table);

        $tableExists = !$table || in_array($table, $availableTables);

        if ($tableExists) {
            return;
        }

        if (is_array($availableTables)) {
            foreach ($availableTables as $availableTable) {
                if (is_array($availableTable)) {
                    if (!isset($availableTable['tableAlias']) && $availableTable['table'] === $table) {
                        return;
                    } elseif (isset($availableTable['tableAlias']) && $availableTable['tableAlias'] === $table) {
                        return;
                    }
                }
            }
        }

        if (
            $join
            && ((empty($join['tableAlias']) && $table == $join['table'])
                || $table == $join['tableAlias']
            )
        ) {
            $availableTables[] = $join;
        } else {
            $availableTables[] = $table;
        }
    }

    /**
     * Escape the characters % and _ in the given string
     * @param string $str
     * @return string
     */
    private function escapeLikeString($str)
    {
        if (false !== strpos($str, '%')) {
            $str = str_replace("%", "\%", $str);
        }

        if (false !== strpos($str, '_')) {
            $str = str_replace("_", "\_", $str);
        }

        return $str;
    }

    /**
     * Given a segment string, will parse it into a multi-level array with the first level representing AND groups
     * and the second level containing OR operands
     *
     * eg. the segment string 'A,B;C,D,E;F' will return:
     *
     * [
     * 0 => [0 => 'A', 1 => 'B'],            // First AND group containing A and B OR conditions
     * 1 => [0 => 'C', 1 => 'D', 2 => 'E'],  // Second AND group containing C, D & E
     * 2 => [0 => 'F']                       // Third AND group containing just F
     * ]
     *
     * @return array
     */
    protected function parseTree()
    {
        $segmentStr = trim($this->string);
        if (empty($segmentStr)) {
            return [];
        }

        // split on ; only when there's no end of string after and only when there's no backslash before it
        $ands = array_filter(preg_split('/' . self::AND_DELIMITER . '(?!$)(?<!\\\\' . self::AND_DELIMITER . ')/', $segmentStr));
        return array_map(function ($and) {
            // split on , only when there's no end of string after and only when there's no backslash before it
            $ors = preg_split('/' . self::OR_DELIMITER . '(?!$)(?<!\\\\' . self::OR_DELIMITER . ')/', $and);

            // remove backslash from in front of ; and ,
            return array_map(function ($or) {
                return str_replace(
                    ['\\' . self::AND_DELIMITER, '\\' . self::OR_DELIMITER],
                    [self::AND_DELIMITER, self::OR_DELIMITER],
                    $or
                );
            }, $ors);
        }, $ands);
    }

    /**
     * Given the array of parsed boolean logic, will return
     * an array containing the full SQL string representing the filter and the values to bind to the query
     *
     * @throws Exception
     * @return array SQL Query and Bind parameters
     */
    public function getSql()
    {
        if ($this->isEmpty()) {
            throw new Exception("Invalid segment, please specify a valid segment.");
        }

        $andExpressions = $this->tree;
        $andExpressions = array_map(function ($orExpressions) {
            if (count($orExpressions) == 1) {
                return $orExpressions[0];
            }

            return '( ' . implode(' OR ', $orExpressions) . ')';
        }, $andExpressions);

        $sql = implode(' AND ', $andExpressions);

        return array(
            'where' => $sql,
            'bind'  => $this->valuesBind,
        );
    }
}