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