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