LogQueryBuilder
3 weeks ago
Actions.php
8 months ago
ArchiveSelector.php
3 weeks ago
ArchiveTableCreator.php
3 weeks ago
ArchiveTableDao.php
3 weeks ago
ArchiveWriter.php
3 weeks ago
ArchivingDbAdapter.php
3 weeks ago
LogAggregator.php
3 weeks ago
LogQueryBuilder.php
3 weeks ago
LogTableTemporary.php
2 years ago
Model.php
3 weeks ago
RawLogDao.php
3 weeks ago
TableMetadata.php
1 year ago
LogQueryBuilder.php
262 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\DataAccess; |
| 10 | |
| 11 | use Exception; |
| 12 | use Piwik\DataAccess\LogQueryBuilder\JoinGenerator; |
| 13 | use Piwik\DataAccess\LogQueryBuilder\JoinTables; |
| 14 | use Piwik\Plugin\LogTablesProvider; |
| 15 | use Piwik\Segment\SegmentExpression; |
| 16 | class LogQueryBuilder |
| 17 | { |
| 18 | public const FORCE_INNER_GROUP_BY_NO_SUBSELECT = '__##nosubquery##__'; |
| 19 | /** |
| 20 | * @var LogTablesProvider |
| 21 | */ |
| 22 | private $logTableProvider; |
| 23 | /** |
| 24 | * Forces to use a subselect when generating the query. Set value to the FORCE_INNER_GROUP_BY_NO_SUBSELECT constant to force not using a subselect. |
| 25 | * @var string |
| 26 | */ |
| 27 | private $forcedInnerGroupBy = ''; |
| 28 | public function __construct(LogTablesProvider $logTablesProvider) |
| 29 | { |
| 30 | $this->logTableProvider = $logTablesProvider; |
| 31 | } |
| 32 | /** |
| 33 | * Forces to use a subselect when generating the query. |
| 34 | * |
| 35 | * @param string $innerGroupBy |
| 36 | */ |
| 37 | public function forceInnerGroupBySubselect($innerGroupBy) |
| 38 | { |
| 39 | $this->forcedInnerGroupBy = $innerGroupBy; |
| 40 | } |
| 41 | public function getForcedInnerGroupBySubselect() |
| 42 | { |
| 43 | return $this->forcedInnerGroupBy; |
| 44 | } |
| 45 | public function getSelectQueryString(SegmentExpression $segmentExpression, $select, $from, $where, $bind, $groupBy, $orderBy, $limitAndOffset, bool $withRollup = \false) |
| 46 | { |
| 47 | if (!is_array($from)) { |
| 48 | $from = array($from); |
| 49 | } |
| 50 | $fromInitially = $from; |
| 51 | if (!$segmentExpression->isEmpty()) { |
| 52 | $segmentExpression->parseSubExpressionsIntoSqlExpressions($from); |
| 53 | $segmentSql = $segmentExpression->getSql(); |
| 54 | $where = $this->getWhereMatchBoth($where, $segmentSql['where']); |
| 55 | $bind = array_merge($bind, $segmentSql['bind']); |
| 56 | } |
| 57 | // hack to allow db planner and db query optimiser to use an anti-join which results in a lower cost query |
| 58 | // and filtering on the log_visit table first when it doesn't need to consider null-extended rows |
| 59 | if ($from === ['log_link_visit_action', 'log_visit']) { |
| 60 | $from[1] = ['table' => 'log_visit', 'join' => 'INNER JOIN']; |
| 61 | } |
| 62 | $tables = new JoinTables($this->logTableProvider, $from); |
| 63 | $join = new JoinGenerator($tables); |
| 64 | $join->generate(); |
| 65 | $from = $join->getJoinString(); |
| 66 | $joinWithSubSelect = $join->shouldJoinWithSelect(); |
| 67 | // hack for https://github.com/piwik/piwik/issues/9194#issuecomment-164321612 |
| 68 | $useSpecialConversionGroupBy = !empty($segmentSql) && strpos($groupBy, 'log_conversion.idgoal') !== \false && $fromInitially == array('log_conversion') && strpos($from, 'log_link_visit_action') !== \false; |
| 69 | if (!empty($this->forcedInnerGroupBy)) { |
| 70 | if ($this->forcedInnerGroupBy === self::FORCE_INNER_GROUP_BY_NO_SUBSELECT) { |
| 71 | $sql = $this->buildSelectQuery($select, $from, $where, $groupBy, $orderBy, $limitAndOffset); |
| 72 | } else { |
| 73 | $sql = $this->buildWrappedSelectQuery($select, $from, $where, $groupBy, $orderBy, $limitAndOffset, $tables, $this->forcedInnerGroupBy); |
| 74 | } |
| 75 | } elseif ($useSpecialConversionGroupBy) { |
| 76 | $innerGroupBy = "CONCAT(log_conversion.idvisit, '_' , log_conversion.idgoal, '_', log_conversion.buster)"; |
| 77 | $sql = $this->buildWrappedSelectQuery($select, $from, $where, $groupBy, $orderBy, $limitAndOffset, $tables, $innerGroupBy); |
| 78 | } elseif ($joinWithSubSelect) { |
| 79 | $sql = $this->buildWrappedSelectQuery($select, $from, $where, $groupBy, $orderBy, $limitAndOffset, $tables); |
| 80 | } else { |
| 81 | $sql = $this->buildSelectQuery($select, $from, $where, $groupBy, $orderBy, $limitAndOffset, $withRollup); |
| 82 | } |
| 83 | return array('sql' => $sql, 'bind' => $bind); |
| 84 | } |
| 85 | private function getKnownTables() |
| 86 | { |
| 87 | $names = array(); |
| 88 | foreach ($this->logTableProvider->getAllLogTablesWithTemporary() as $logTable) { |
| 89 | $names[] = $logTable->getName(); |
| 90 | } |
| 91 | return $names; |
| 92 | } |
| 93 | /** |
| 94 | * Build a select query where actions have to be joined on visits (or conversions) |
| 95 | * In this case, the query gets wrapped in another query so that grouping by visit is possible |
| 96 | * @param string $select |
| 97 | * @param string $from |
| 98 | * @param string $where |
| 99 | * @param string $groupBy |
| 100 | * @param string $orderBy |
| 101 | * @param string $limitAndOffset |
| 102 | * @param null|string $innerGroupBy If given, this inner group by will be used. If not, we try to detect one |
| 103 | * @throws Exception |
| 104 | * @return string |
| 105 | */ |
| 106 | private function buildWrappedSelectQuery($select, $from, $where, $groupBy, $orderBy, $limitAndOffset, JoinTables $tables, $innerGroupBy = null) |
| 107 | { |
| 108 | $matchTables = $this->getKnownTables(); |
| 109 | foreach ($tables as $table) { |
| 110 | if (is_array($table) && isset($table['tableAlias']) && !in_array($table['tableAlias'], $matchTables, $strict = \true)) { |
| 111 | $matchTables[] = $table['tableAlias']; |
| 112 | } elseif (is_array($table) && isset($table['table']) && !in_array($table['table'], $matchTables, $strict = \true)) { |
| 113 | $matchTables[] = $table['table']; |
| 114 | } elseif (is_string($table) && !in_array($table, $matchTables, $strict = \true)) { |
| 115 | $matchTables[] = $table; |
| 116 | } |
| 117 | } |
| 118 | $matchTables = '(' . implode('|', $matchTables) . ')'; |
| 119 | preg_match_all("/" . $matchTables . "\\.[a-z0-9_\\*]+/", $select, $matches); |
| 120 | $neededFields = array_unique($matches[0]); |
| 121 | if (count($neededFields) == 0) { |
| 122 | throw new Exception("No needed fields found in select expression. " . "Please use a table prefix."); |
| 123 | } |
| 124 | $fieldNames = array(); |
| 125 | $toBeReplaced = array(); |
| 126 | $epregReplace = array(); |
| 127 | foreach ($neededFields as &$neededField) { |
| 128 | $parts = explode('.', $neededField); |
| 129 | if (count($parts) === 2 && !empty($parts[1])) { |
| 130 | if (in_array($parts[1], $fieldNames, $strict = \true)) { |
| 131 | // eg when selecting 2 dimensions log_action_X.name |
| 132 | $columnAs = $parts[1] . md5($neededField); |
| 133 | $fieldNames[] = $columnAs; |
| 134 | // we make sure to not replace a idvisitor column when duplicate column is idvisit |
| 135 | $toBeReplaced[$neededField . ' '] = $parts[0] . '.' . $columnAs . ' '; |
| 136 | $toBeReplaced[$neededField . ')'] = $parts[0] . '.' . $columnAs . ')'; |
| 137 | $toBeReplaced[$neededField . '`'] = $parts[0] . '.' . $columnAs . '`'; |
| 138 | $toBeReplaced[$neededField . ','] = $parts[0] . '.' . $columnAs . ','; |
| 139 | // replace when string ends this, we need to use regex to check for this |
| 140 | $epregReplace["/(" . $neededField . ")\$/"] = $parts[0] . '.' . $columnAs; |
| 141 | $neededField .= ' as ' . $columnAs; |
| 142 | } else { |
| 143 | $fieldNames[] = $parts[1]; |
| 144 | } |
| 145 | } |
| 146 | } |
| 147 | preg_match_all("/" . $matchTables . "/", $from, $matchesFrom); |
| 148 | $innerSelect = implode(", \n", $neededFields); |
| 149 | $innerFrom = $from; |
| 150 | $innerWhere = $where; |
| 151 | $innerLimitAndOffset = $limitAndOffset; |
| 152 | $innerOrderBy = "NULL"; |
| 153 | if ($innerLimitAndOffset && $orderBy) { |
| 154 | // only When LIMITing we can apply to the inner query the same ORDER BY as the parent query |
| 155 | $innerOrderBy = $orderBy; |
| 156 | } |
| 157 | if ($innerLimitAndOffset) { |
| 158 | // When LIMITing, no need to GROUP BY (GROUPing by is done before the LIMIT which is super slow when large amount of rows is matched) |
| 159 | $innerGroupBy = \false; |
| 160 | } |
| 161 | if (!isset($innerGroupBy) && in_array('log_visit', $matchesFrom[1])) { |
| 162 | $innerGroupBy = "log_visit.idvisit"; |
| 163 | } elseif (!isset($innerGroupBy)) { |
| 164 | throw new Exception('Cannot use subselect for join as no group by rule is specified'); |
| 165 | } |
| 166 | if (!empty($toBeReplaced)) { |
| 167 | $select = preg_replace(array_keys($epregReplace), array_values($epregReplace), $select); |
| 168 | $select = str_replace(array_keys($toBeReplaced), array_values($toBeReplaced), $select); |
| 169 | if (!empty($groupBy)) { |
| 170 | $groupBy = preg_replace(array_keys($epregReplace), array_values($epregReplace), $groupBy); |
| 171 | $groupBy = str_replace(array_keys($toBeReplaced), array_values($toBeReplaced), $groupBy); |
| 172 | } |
| 173 | if (!empty($orderBy)) { |
| 174 | $orderBy = preg_replace(array_keys($epregReplace), array_values($epregReplace), $orderBy); |
| 175 | $orderBy = str_replace(array_keys($toBeReplaced), array_values($toBeReplaced), $orderBy); |
| 176 | } |
| 177 | } |
| 178 | $innerQuery = $this->buildSelectQuery($innerSelect, $innerFrom, $innerWhere, $innerGroupBy, $innerOrderBy, $innerLimitAndOffset); |
| 179 | $select = preg_replace('/' . $matchTables . '\\./', 'log_inner.', $select); |
| 180 | $from = "\n (\n {$innerQuery}\n ) AS log_inner"; |
| 181 | $where = \false; |
| 182 | $orderBy = preg_replace('/' . $matchTables . '\\./', 'log_inner.', $orderBy); |
| 183 | $groupBy = preg_replace('/' . $matchTables . '\\./', 'log_inner.', $groupBy); |
| 184 | $outerLimitAndOffset = null; |
| 185 | $query = $this->buildSelectQuery($select, $from, $where, $groupBy, $orderBy, $outerLimitAndOffset); |
| 186 | return $query; |
| 187 | } |
| 188 | /** |
| 189 | * Build select query the normal way |
| 190 | * |
| 191 | * @param string $select fieldlist to be selected |
| 192 | * @param string $from tablelist to select from |
| 193 | * @param string $where where clause |
| 194 | * @param string $groupBy group by clause |
| 195 | * @param string $orderBy order by clause |
| 196 | * @param string|int $limitAndOffset limit by clause eg '5' for Limit 5 Offset 0 or '10, 5' for Limit 5 Offset 10 |
| 197 | * @return string |
| 198 | */ |
| 199 | private function buildSelectQuery($select, $from, $where, $groupBy, $orderBy, $limitAndOffset, bool $withRollup = \false) |
| 200 | { |
| 201 | $sql = "\n\t\t\tSELECT\n\t\t\t\t{$select}\n\t\t\tFROM\n\t\t\t\t{$from}"; |
| 202 | if ($where) { |
| 203 | $sql .= "\n\t\t\tWHERE\n\t\t\t\t{$where}"; |
| 204 | } |
| 205 | if ($groupBy) { |
| 206 | $sql .= "\n\t\t\tGROUP BY\n\t\t\t\t{$groupBy}"; |
| 207 | if ($withRollup) { |
| 208 | $sql .= "\n WITH ROLLUP"; |
| 209 | } |
| 210 | } |
| 211 | if ($orderBy) { |
| 212 | if ($withRollup) { |
| 213 | $sql = "\n SELECT * FROM (\n {$sql}\n ) AS rollupQuery"; |
| 214 | } |
| 215 | $sql .= "\n\t\t\tORDER BY\n\t\t\t\t{$orderBy}"; |
| 216 | } |
| 217 | $sql = $this->appendLimitClauseToQuery($sql, $limitAndOffset); |
| 218 | return $sql; |
| 219 | } |
| 220 | /** |
| 221 | * @param $sql |
| 222 | * @param $limit LIMIT clause eg. "10, 50" (offset 10, limit 50) |
| 223 | * @return string |
| 224 | */ |
| 225 | private function appendLimitClauseToQuery($sql, $limit) |
| 226 | { |
| 227 | $limitParts = explode(',', (string) $limit); |
| 228 | $isLimitWithOffset = 2 === count($limitParts); |
| 229 | if ($isLimitWithOffset) { |
| 230 | // $limit = "10, 5". We would not have to do this but we do to prevent possible injections. |
| 231 | $offset = trim($limitParts[0]); |
| 232 | $limit = trim($limitParts[1]); |
| 233 | $sql .= sprintf(' LIMIT %d, %d', $offset, $limit); |
| 234 | } else { |
| 235 | // $limit = "5" |
| 236 | $limit = (int) $limit; |
| 237 | if ($limit >= 1) { |
| 238 | $sql .= " LIMIT {$limit}"; |
| 239 | } |
| 240 | } |
| 241 | return $sql; |
| 242 | } |
| 243 | /** |
| 244 | * @param $where |
| 245 | * @param $segmentWhere |
| 246 | * @return string |
| 247 | */ |
| 248 | protected function getWhereMatchBoth($where, $segmentWhere) |
| 249 | { |
| 250 | if (empty($segmentWhere) && empty($where)) { |
| 251 | throw new \Exception("Segment where clause should be non empty."); |
| 252 | } |
| 253 | if (empty($segmentWhere)) { |
| 254 | return $where; |
| 255 | } |
| 256 | if (empty($where)) { |
| 257 | return $segmentWhere; |
| 258 | } |
| 259 | return "( {$where} )\n AND\n ({$segmentWhere})"; |
| 260 | } |
| 261 | } |
| 262 |