SegmentExpression.php
424 lines
| 1 | <?php |
| 2 | |
| 3 | /** |
| 4 | * Matomo - free/libre analytics platform |
| 5 | * |
| 6 | * @link https://matomo.org |
| 7 | * @license https://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later |
| 8 | */ |
| 9 | namespace Piwik\Segment; |
| 10 | |
| 11 | use Exception; |
| 12 | /** |
| 13 | * |
| 14 | */ |
| 15 | class SegmentExpression |
| 16 | { |
| 17 | public const AND_DELIMITER = ';'; |
| 18 | public const OR_DELIMITER = ','; |
| 19 | public const MATCH_EQUAL = '=='; |
| 20 | public const MATCH_NOT_EQUAL = '!='; |
| 21 | public const MATCH_GREATER_OR_EQUAL = '>='; |
| 22 | public const MATCH_LESS_OR_EQUAL = '<='; |
| 23 | public const MATCH_GREATER = '>'; |
| 24 | public const MATCH_LESS = '<'; |
| 25 | public const MATCH_CONTAINS = '=@'; |
| 26 | public const MATCH_DOES_NOT_CONTAIN = '!@'; |
| 27 | public const MATCH_STARTS_WITH = '=^'; |
| 28 | public const MATCH_ENDS_WITH = '=$'; |
| 29 | public const BOOL_OPERATOR_OR = 'OR'; |
| 30 | public const BOOL_OPERATOR_AND = 'AND'; |
| 31 | public const BOOL_OPERATOR_END = ''; |
| 32 | // Note: you can't write this in the API, but access this feature |
| 33 | // via field!= <- IS NOT NULL |
| 34 | // or via field== <- IS NULL / empty |
| 35 | public const MATCH_IS_NOT_NULL_NOR_EMPTY = '::NOT_NULL'; |
| 36 | public const MATCH_IS_NULL_OR_EMPTY = '::NULL'; |
| 37 | // Special case, since we look up Page URLs/Page titles in a sub SQL query |
| 38 | public const MATCH_ACTIONS_CONTAINS = 'IN'; |
| 39 | public const MATCH_ACTIONS_NOT_CONTAINS = 'NOTIN'; |
| 40 | /** |
| 41 | * A special match type for segments that require rejecting a visit if any action/conversion/etc. in the visit matches a condition. |
| 42 | * These operands result in `idvisit NOT IN (...)` subqueries. |
| 43 | */ |
| 44 | public const MATCH_IDVISIT_NOT_IN = 'IDVISIT_NOTIN'; |
| 45 | public const INDEX_OPERAND_NAME = 0; |
| 46 | public const INDEX_OPERAND_OPERATOR = 1; |
| 47 | public const INDEX_OPERAND_VALUE = 2; |
| 48 | public const INDEX_OPERAND_JOIN_COLUMN = 3; |
| 49 | public const INDEX_OPERAND_SEGMENT_INFO = 4; |
| 50 | public const SQL_WHERE_DO_NOT_MATCH_ANY_ROW = "(1 = 0)"; |
| 51 | public const SQL_WHERE_MATCHES_ALL_ROWS = "(1 = 1)"; |
| 52 | protected $string; |
| 53 | protected $valuesBind = []; |
| 54 | protected $tree = []; |
| 55 | protected $parsedSubExpressions = []; |
| 56 | public function __construct($string) |
| 57 | { |
| 58 | $this->string = $string; |
| 59 | $this->tree = $this->parseTree(); |
| 60 | } |
| 61 | public function getSegmentDefinition() |
| 62 | { |
| 63 | return $this->string; |
| 64 | } |
| 65 | public function isEmpty() |
| 66 | { |
| 67 | return count($this->tree) == 0; |
| 68 | } |
| 69 | public function getSubExpressionCount() |
| 70 | { |
| 71 | $count = 0; |
| 72 | foreach ($this->parsedSubExpressions as $orExpressions) { |
| 73 | foreach ($orExpressions as $operand) { |
| 74 | $isExpressionColumnPresent = !empty($operand[self::INDEX_OPERAND_NAME]); |
| 75 | if ($isExpressionColumnPresent) { |
| 76 | ++$count; |
| 77 | } |
| 78 | } |
| 79 | } |
| 80 | return $count; |
| 81 | } |
| 82 | /** |
| 83 | * Given the array of parsed filters containing, for each filter, |
| 84 | * the boolean operator (AND/OR) and the operand, |
| 85 | * Will return the array where the filters are in SQL representation |
| 86 | * |
| 87 | * @throws Exception |
| 88 | * @return array |
| 89 | */ |
| 90 | public function parseSubExpressions() |
| 91 | { |
| 92 | $parsedSubExpressions = array_map(function (array $orExpressions) { |
| 93 | return array_map(function (string $operand) { |
| 94 | return $this->parseOperand($operand); |
| 95 | }, $orExpressions); |
| 96 | }, $this->tree); |
| 97 | $this->parsedSubExpressions = $parsedSubExpressions; |
| 98 | return $parsedSubExpressions; |
| 99 | } |
| 100 | private function parseOperand(string $operand) : array |
| 101 | { |
| 102 | $operand = urldecode($operand); |
| 103 | $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}(.*)/'; |
| 104 | $match = preg_match($pattern, $operand, $matches); |
| 105 | if ($match == 0) { |
| 106 | throw new Exception('The segment condition \'' . $operand . '\' is not valid.'); |
| 107 | } |
| 108 | $leftMember = $matches[1]; |
| 109 | $operation = $matches[2]; |
| 110 | $valueRightMember = urldecode($matches[3]); |
| 111 | // is null / is not null |
| 112 | if ($valueRightMember === '') { |
| 113 | if ($operation == self::MATCH_NOT_EQUAL) { |
| 114 | $operation = self::MATCH_IS_NOT_NULL_NOR_EMPTY; |
| 115 | } elseif ($operation == self::MATCH_EQUAL) { |
| 116 | $operation = self::MATCH_IS_NULL_OR_EMPTY; |
| 117 | } else { |
| 118 | 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)'); |
| 119 | } |
| 120 | } |
| 121 | return [self::INDEX_OPERAND_NAME => $leftMember, self::INDEX_OPERAND_OPERATOR => $operation, self::INDEX_OPERAND_VALUE => $valueRightMember]; |
| 122 | } |
| 123 | /** |
| 124 | * Set the given expression |
| 125 | * @param $parsedSubExpressions |
| 126 | */ |
| 127 | public function setSubExpressionsAfterCleanup($parsedSubExpressions) |
| 128 | { |
| 129 | $this->parsedSubExpressions = $parsedSubExpressions; |
| 130 | } |
| 131 | /** |
| 132 | * @param array $availableTables |
| 133 | */ |
| 134 | public function parseSubExpressionsIntoSqlExpressions(&$availableTables = array()) |
| 135 | { |
| 136 | $this->valuesBind = []; |
| 137 | $sqlSubExpressions = array_map(function (array $orExpressions) use(&$availableTables) { |
| 138 | return array_map(function (array $operandDefinition) use(&$availableTables) { |
| 139 | $operand = $this->getSqlMatchFromDefinition($operandDefinition, $availableTables); |
| 140 | if ($operand[self::INDEX_OPERAND_OPERATOR] !== null) { |
| 141 | if (is_array($operand[self::INDEX_OPERAND_OPERATOR])) { |
| 142 | $this->valuesBind = array_merge($this->valuesBind, $operand[self::INDEX_OPERAND_OPERATOR]); |
| 143 | } else { |
| 144 | $this->valuesBind[] = $operand[self::INDEX_OPERAND_OPERATOR]; |
| 145 | } |
| 146 | } |
| 147 | $operand = $operand[self::INDEX_OPERAND_NAME]; |
| 148 | return $operand; |
| 149 | }, $orExpressions); |
| 150 | }, $this->parsedSubExpressions); |
| 151 | $this->tree = $sqlSubExpressions; |
| 152 | } |
| 153 | /** |
| 154 | * Given an array representing one filter operand ( left member , operation , right member) |
| 155 | * Will return an array containing |
| 156 | * - the SQL substring, |
| 157 | * - the values to bind to this substring |
| 158 | * |
| 159 | * @param array $def |
| 160 | * @param array $availableTables |
| 161 | * @throws Exception |
| 162 | * @return array |
| 163 | */ |
| 164 | protected function getSqlMatchFromDefinition($def, &$availableTables) |
| 165 | { |
| 166 | $field = $def[self::INDEX_OPERAND_NAME]; |
| 167 | $matchType = $def[self::INDEX_OPERAND_OPERATOR]; |
| 168 | $value = $def[self::INDEX_OPERAND_VALUE]; |
| 169 | $join = $def[self::INDEX_OPERAND_JOIN_COLUMN] ?? null; |
| 170 | $segment = $def[self::INDEX_OPERAND_SEGMENT_INFO] ?? null; |
| 171 | // Note: we save the SQL used in the subquery (if there is one), as we don't want to run |
| 172 | // self::checkFieldIsAvailable() on it. |
| 173 | $innerSql = null; |
| 174 | // Segment::getCleanedExpression() may return array(null, $matchType, null) |
| 175 | $operandWillNotMatchAnyRow = empty($field) && is_null($value); |
| 176 | if ($operandWillNotMatchAnyRow) { |
| 177 | if ($matchType == self::MATCH_EQUAL) { |
| 178 | // eg. pageUrl==DoesNotExist |
| 179 | // Equal to NULL means it will match none |
| 180 | $sqlExpression = self::SQL_WHERE_DO_NOT_MATCH_ANY_ROW; |
| 181 | } elseif ($matchType == self::MATCH_NOT_EQUAL) { |
| 182 | // eg. pageUrl!=DoesNotExist |
| 183 | // Not equal to NULL means it matches all rows |
| 184 | $sqlExpression = self::SQL_WHERE_MATCHES_ALL_ROWS; |
| 185 | } elseif ($matchType == self::MATCH_CONTAINS || $matchType == self::MATCH_DOES_NOT_CONTAIN || $matchType == self::MATCH_STARTS_WITH || $matchType == self::MATCH_ENDS_WITH) { |
| 186 | // no action was found for CONTAINS / DOES NOT CONTAIN |
| 187 | // eg. pageUrl=@DoesNotExist -> matches no row |
| 188 | // eg. pageUrl!@DoesNotExist -> matches no rows |
| 189 | $sqlExpression = self::SQL_WHERE_DO_NOT_MATCH_ANY_ROW; |
| 190 | } else { |
| 191 | // it is not expected to reach this code path |
| 192 | throw new Exception("Unexpected match type {$matchType} for your segment. " . "Please report this issue to the Matomo team with the segment you are using."); |
| 193 | } |
| 194 | return array($sqlExpression, $value = null); |
| 195 | } |
| 196 | $alsoMatchNULLValues = \false; |
| 197 | switch ($matchType) { |
| 198 | case self::MATCH_EQUAL: |
| 199 | $sqlMatch = '%s ='; |
| 200 | break; |
| 201 | case self::MATCH_NOT_EQUAL: |
| 202 | $sqlMatch = '%s <>'; |
| 203 | $alsoMatchNULLValues = \true; |
| 204 | break; |
| 205 | case self::MATCH_GREATER: |
| 206 | $sqlMatch = '%s >'; |
| 207 | break; |
| 208 | case self::MATCH_LESS: |
| 209 | $sqlMatch = '%s <'; |
| 210 | break; |
| 211 | case self::MATCH_GREATER_OR_EQUAL: |
| 212 | $sqlMatch = '%s >='; |
| 213 | break; |
| 214 | case self::MATCH_LESS_OR_EQUAL: |
| 215 | $sqlMatch = '%s <='; |
| 216 | break; |
| 217 | case self::MATCH_CONTAINS: |
| 218 | $sqlMatch = '%s LIKE'; |
| 219 | $value = '%' . $this->escapeLikeString($value) . '%'; |
| 220 | break; |
| 221 | case self::MATCH_DOES_NOT_CONTAIN: |
| 222 | $sqlMatch = '%s NOT LIKE'; |
| 223 | $value = '%' . $this->escapeLikeString($value) . '%'; |
| 224 | $alsoMatchNULLValues = \true; |
| 225 | break; |
| 226 | case self::MATCH_STARTS_WITH: |
| 227 | $sqlMatch = '%s LIKE'; |
| 228 | $value = $this->escapeLikeString($value) . '%'; |
| 229 | break; |
| 230 | case self::MATCH_ENDS_WITH: |
| 231 | $sqlMatch = '%s LIKE'; |
| 232 | $value = '%' . $this->escapeLikeString($value); |
| 233 | break; |
| 234 | case self::MATCH_IS_NOT_NULL_NOR_EMPTY: |
| 235 | $sqlMatch = '%s IS NOT NULL AND %s <> \'\' AND %s <> \'0\''; |
| 236 | $value = null; |
| 237 | break; |
| 238 | case self::MATCH_IS_NULL_OR_EMPTY: |
| 239 | $sqlMatch = '%s IS NULL OR %s = \'\' OR %s = \'0\''; |
| 240 | $value = null; |
| 241 | break; |
| 242 | case self::MATCH_ACTIONS_CONTAINS: |
| 243 | // this match type is not accessible from the outside |
| 244 | // (it won't be matched in self::parseSubExpressions()) |
| 245 | // it can be used internally to inject sub-expressions into the query. |
| 246 | // see Segment::getCleanedExpression() |
| 247 | $innerSql = $value['SQL']; |
| 248 | $sqlMatch = '%s IN (%innerSql%)'; |
| 249 | $value = $value['bind']; |
| 250 | break; |
| 251 | case self::MATCH_ACTIONS_NOT_CONTAINS: |
| 252 | // this match type is not accessible from the outside |
| 253 | // (it won't be matched in self::parseSubExpressions()) |
| 254 | // it can be used internally to inject sub-expressions into the query. |
| 255 | // see Segment::getCleanedExpression() |
| 256 | $innerSql = $value['sql']; |
| 257 | $sqlMatch = '%s NOT IN (%innerSql%)'; |
| 258 | $value = $value['bind']; |
| 259 | break; |
| 260 | default: |
| 261 | throw new Exception("Filter contains the match type '" . $matchType . "' which is not supported"); |
| 262 | break; |
| 263 | } |
| 264 | // We match NULL values when rows are excluded only when we are not doing a |
| 265 | $alsoMatchNULLValues = $alsoMatchNULLValues && !empty($value); |
| 266 | if ($matchType === self::MATCH_ACTIONS_CONTAINS || $matchType === self::MATCH_ACTIONS_NOT_CONTAINS || is_null($value)) { |
| 267 | $sqlExpression = "( {$sqlMatch} )"; |
| 268 | } else { |
| 269 | if ($alsoMatchNULLValues) { |
| 270 | $sqlExpression = "( {$field} IS NULL OR {$sqlMatch} ? )"; |
| 271 | } else { |
| 272 | $sqlExpression = "{$sqlMatch} ?"; |
| 273 | } |
| 274 | } |
| 275 | if (!empty($join['field'])) { |
| 276 | $sqlExpression = str_replace('%s', $join['field'], $sqlExpression); |
| 277 | } else { |
| 278 | $sqlExpression = str_replace('%s', $field, $sqlExpression); |
| 279 | } |
| 280 | if (!empty($join['discriminator'])) { |
| 281 | $sqlExpression = '(' . $sqlExpression . ' AND ' . $join['discriminator'] . ')'; |
| 282 | } |
| 283 | $columns = self::parseColumnsFromSqlExpr($sqlExpression); |
| 284 | foreach ($columns as $column) { |
| 285 | $this->checkFieldIsAvailable($column, $availableTables, $join); |
| 286 | } |
| 287 | if (!empty($join['field'])) { |
| 288 | $this->checkFieldIsAvailable($join['field'], $availableTables, $join); |
| 289 | } |
| 290 | if (!empty($join['joinOn'])) { |
| 291 | $joinOnColumns = self::parseColumnsFromSqlExpr($join['joinOn']); |
| 292 | foreach ($joinOnColumns as $column) { |
| 293 | $this->checkFieldIsAvailable($column, $availableTables, $join); |
| 294 | } |
| 295 | } |
| 296 | if ($innerSql) { |
| 297 | $sqlExpression = str_replace('%innerSql%', $innerSql, $sqlExpression); |
| 298 | } |
| 299 | return array($sqlExpression, $value); |
| 300 | } |
| 301 | /** |
| 302 | * @param string $field |
| 303 | * @return string[] |
| 304 | */ |
| 305 | public static function parseColumnsFromSqlExpr($field) |
| 306 | { |
| 307 | preg_match_all('/[^@a-zA-Z0-9_]?`?([@a-zA-Z_][@a-zA-Z0-9_]*`?\\.`?[a-zA-Z0-9_`]+)`?\\b/', $field, $matches); |
| 308 | $result = isset($matches[1]) ? $matches[1] : []; |
| 309 | // remove uses of session vars |
| 310 | $result = array_filter($result, function ($value) { |
| 311 | return strpos($value, '@') === \false; |
| 312 | }); |
| 313 | $result = array_map(function ($item) { |
| 314 | return str_replace('`', '', $item); |
| 315 | }, $result); |
| 316 | $result = array_unique($result); |
| 317 | $result = array_values($result); |
| 318 | return $result; |
| 319 | } |
| 320 | /** |
| 321 | * Check whether the field is available |
| 322 | * If not, add it to the available tables |
| 323 | * |
| 324 | * @param string $field |
| 325 | * @param array $availableTables |
| 326 | */ |
| 327 | private function checkFieldIsAvailable($field, &$availableTables, $join) |
| 328 | { |
| 329 | $fieldParts = explode('.', $field); |
| 330 | $table = count($fieldParts) == 2 ? $fieldParts[0] : \false; |
| 331 | // remove sql functions from field name |
| 332 | // example: `HOUR(log_visit.visit_last_action_time)` gets `HOUR(log_visit` => remove `HOUR(` |
| 333 | $table = preg_replace('/^[A-Z_]+\\(/', '', $table); |
| 334 | $tableExists = !$table || in_array($table, $availableTables); |
| 335 | if ($tableExists) { |
| 336 | return; |
| 337 | } |
| 338 | if (is_array($availableTables)) { |
| 339 | foreach ($availableTables as $availableTable) { |
| 340 | if (is_array($availableTable)) { |
| 341 | if (!isset($availableTable['tableAlias']) && $availableTable['table'] === $table) { |
| 342 | return; |
| 343 | } elseif (isset($availableTable['tableAlias']) && $availableTable['tableAlias'] === $table) { |
| 344 | return; |
| 345 | } |
| 346 | } |
| 347 | } |
| 348 | } |
| 349 | if ($join && (empty($join['tableAlias']) && $table == $join['table'] || $table == $join['tableAlias'])) { |
| 350 | $availableTables[] = $join; |
| 351 | } else { |
| 352 | $availableTables[] = $table; |
| 353 | } |
| 354 | } |
| 355 | /** |
| 356 | * Escape the characters % and _ in the given string |
| 357 | * @param string $str |
| 358 | * @return string |
| 359 | */ |
| 360 | private function escapeLikeString($str) |
| 361 | { |
| 362 | if (\false !== strpos($str, '%')) { |
| 363 | $str = str_replace("%", "\\%", $str); |
| 364 | } |
| 365 | if (\false !== strpos($str, '_')) { |
| 366 | $str = str_replace("_", "\\_", $str); |
| 367 | } |
| 368 | return $str; |
| 369 | } |
| 370 | /** |
| 371 | * Given a segment string, will parse it into a multi-level array with the first level representing AND groups |
| 372 | * and the second level containing OR operands |
| 373 | * |
| 374 | * eg. the segment string 'A,B;C,D,E;F' will return: |
| 375 | * |
| 376 | * [ |
| 377 | * 0 => [0 => 'A', 1 => 'B'], // First AND group containing A and B OR conditions |
| 378 | * 1 => [0 => 'C', 1 => 'D', 2 => 'E', // Second AND group containing C, D & E |
| 379 | * 2 => [0 => 'F'] // Third AND group containing just F |
| 380 | * ] |
| 381 | * |
| 382 | * @return array |
| 383 | */ |
| 384 | protected function parseTree() |
| 385 | { |
| 386 | $segmentStr = trim($this->string); |
| 387 | if (empty($segmentStr)) { |
| 388 | return []; |
| 389 | } |
| 390 | // split on ; only when there's no end of string after and only when there's no backslash before it |
| 391 | $ands = array_filter(preg_split('/' . self::AND_DELIMITER . '(?!$)(?<!\\\\' . self::AND_DELIMITER . ')/', $segmentStr)); |
| 392 | return array_map(function ($and) { |
| 393 | // split on , only when there's no end of string after and only when there's no backslash before it |
| 394 | $ors = preg_split('/' . self::OR_DELIMITER . '(?!$)(?<!\\\\' . self::OR_DELIMITER . ')/', $and); |
| 395 | // remove backslash from in front of ; and , |
| 396 | return array_map(function ($or) { |
| 397 | return str_replace(['\\' . self::AND_DELIMITER, '\\' . self::OR_DELIMITER], [self::AND_DELIMITER, self::OR_DELIMITER], $or); |
| 398 | }, $ors); |
| 399 | }, $ands); |
| 400 | } |
| 401 | /** |
| 402 | * Given the array of parsed boolean logic, will return |
| 403 | * an array containing the full SQL string representing the filter and the values to bind to the query |
| 404 | * |
| 405 | * @throws Exception |
| 406 | * @return array SQL Query and Bind parameters |
| 407 | */ |
| 408 | public function getSql() |
| 409 | { |
| 410 | if ($this->isEmpty()) { |
| 411 | throw new Exception("Invalid segment, please specify a valid segment."); |
| 412 | } |
| 413 | $andExpressions = $this->tree; |
| 414 | $andExpressions = array_map(function ($orExpressions) { |
| 415 | if (count($orExpressions) == 1) { |
| 416 | return $orExpressions[0]; |
| 417 | } |
| 418 | return '( ' . implode(' OR ', $orExpressions) . ')'; |
| 419 | }, $andExpressions); |
| 420 | $sql = implode(' AND ', $andExpressions); |
| 421 | return array('where' => $sql, 'bind' => $this->valuesBind); |
| 422 | } |
| 423 | } |
| 424 |