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