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