LogQueryBuilder
5 years ago
Actions.php
5 years ago
ArchiveSelector.php
5 years ago
ArchiveTableCreator.php
5 years ago
ArchiveTableDao.php
5 years ago
ArchiveWriter.php
5 years ago
ArchivingDbAdapter.php
5 years ago
LogAggregator.php
5 years ago
LogQueryBuilder.php
5 years ago
LogTableTemporary.php
5 years ago
Model.php
5 years ago
RawLogDao.php
5 years ago
TableMetadata.php
5 years ago
ArchiveSelector.php
466 lines
| 1 | <?php |
| 2 | /** |
| 3 | * Matomo - 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 | namespace Piwik\DataAccess; |
| 10 | |
| 11 | use Exception; |
| 12 | use Piwik\Archive; |
| 13 | use Piwik\Archive\Chunk; |
| 14 | use Piwik\ArchiveProcessor; |
| 15 | use Piwik\ArchiveProcessor\Rules; |
| 16 | use Piwik\Common; |
| 17 | use Piwik\Date; |
| 18 | use Piwik\Db; |
| 19 | use Piwik\Period; |
| 20 | use Piwik\Period\Range; |
| 21 | use Piwik\Segment; |
| 22 | |
| 23 | /** |
| 24 | * Data Access object used to query archives |
| 25 | * |
| 26 | * A record in the Database for a given report is defined by |
| 27 | * - idarchive = unique ID that is associated to all the data of this archive (idsite+period+date) |
| 28 | * - idsite = the ID of the website |
| 29 | * - date1 = starting day of the period |
| 30 | * - date2 = ending day of the period |
| 31 | * - period = integer that defines the period (day/week/etc.). @see period::getId() |
| 32 | * - ts_archived = timestamp when the archive was processed (UTC) |
| 33 | * - name = the name of the report (ex: uniq_visitors or search_keywords_by_search_engines) |
| 34 | * - value = the actual data (a numeric value, or a blob of compressed serialized data) |
| 35 | * |
| 36 | */ |
| 37 | class ArchiveSelector |
| 38 | { |
| 39 | const NB_VISITS_RECORD_LOOKED_UP = "nb_visits"; |
| 40 | |
| 41 | const NB_VISITS_CONVERTED_RECORD_LOOKED_UP = "nb_visits_converted"; |
| 42 | |
| 43 | private static function getModel() |
| 44 | { |
| 45 | return new Model(); |
| 46 | } |
| 47 | |
| 48 | /** |
| 49 | * @param ArchiveProcessor\Parameters $params |
| 50 | * @param bool $minDatetimeArchiveProcessedUTC deprecated. Will be removed in Matomo 4. |
| 51 | * @return array An array with four values: |
| 52 | * - the latest archive ID or false if none |
| 53 | * - the latest visits value for the latest archive, regardless of whether the archive is invalidated or not |
| 54 | * - the latest visits converted value for the latest archive, regardless of whether the archive is invalidated or not |
| 55 | * - whether there is an archive that exists or not. if this is true and the latest archive is false, it means |
| 56 | * the archive found was not usable (for example, it was invalidated and we are not looking for invalidated archives) |
| 57 | * - the ts_archived for the latest usable archive |
| 58 | * @throws Exception |
| 59 | */ |
| 60 | public static function getArchiveIdAndVisits(ArchiveProcessor\Parameters $params, $minDatetimeArchiveProcessedUTC = false, $includeInvalidated = null) |
| 61 | { |
| 62 | $idSite = $params->getSite()->getId(); |
| 63 | $period = $params->getPeriod()->getId(); |
| 64 | $dateStart = $params->getPeriod()->getDateStart(); |
| 65 | $dateStartIso = $dateStart->toString('Y-m-d'); |
| 66 | $dateEndIso = $params->getPeriod()->getDateEnd()->toString('Y-m-d'); |
| 67 | |
| 68 | $numericTable = ArchiveTableCreator::getNumericTable($dateStart); |
| 69 | |
| 70 | $requestedPlugin = $params->getRequestedPlugin(); |
| 71 | $segment = $params->getSegment(); |
| 72 | $plugins = array("VisitsSummary", $requestedPlugin); |
| 73 | |
| 74 | $doneFlags = Rules::getDoneFlags($plugins, $segment); |
| 75 | $requestedPluginDoneFlags = Rules::getDoneFlags([$requestedPlugin], $segment); |
| 76 | $doneFlagValues = Rules::getSelectableDoneFlagValues($includeInvalidated === null ? true : $includeInvalidated, $params, $includeInvalidated === null); |
| 77 | |
| 78 | $results = self::getModel()->getArchiveIdAndVisits($numericTable, $idSite, $period, $dateStartIso, $dateEndIso, null, $doneFlags); |
| 79 | if (empty($results)) { // no archive found |
| 80 | return [false, false, false, false, false]; |
| 81 | } |
| 82 | |
| 83 | $result = self::findArchiveDataWithLatestTsArchived($results, $requestedPluginDoneFlags); |
| 84 | |
| 85 | $tsArchived = isset($result['ts_archived']) ? $result['ts_archived'] : false; |
| 86 | $visits = isset($result['nb_visits']) ? $result['nb_visits'] : false; |
| 87 | $visitsConverted = isset($result['nb_visits_converted']) ? $result['nb_visits_converted'] : false; |
| 88 | |
| 89 | $result['idarchive'] = empty($result['idarchive']) ? [] : [$result['idarchive']]; |
| 90 | if (isset($result['partial'])) { |
| 91 | $result['idarchive'] = array_merge($result['idarchive'], $result['partial']); |
| 92 | } |
| 93 | |
| 94 | if (isset($result['value']) |
| 95 | && !in_array($result['value'], $doneFlagValues) |
| 96 | ) { // the archive cannot be considered valid for this request (has wrong done flag value) |
| 97 | return [false, $visits, $visitsConverted, true, $tsArchived]; |
| 98 | } |
| 99 | |
| 100 | if (!empty($minDatetimeArchiveProcessedUTC) && !is_object($minDatetimeArchiveProcessedUTC)) { |
| 101 | $minDatetimeArchiveProcessedUTC = Date::factory($minDatetimeArchiveProcessedUTC); |
| 102 | } |
| 103 | |
| 104 | // the archive is too old |
| 105 | if ($minDatetimeArchiveProcessedUTC |
| 106 | && !empty($result['idarchive']) |
| 107 | && Date::factory($tsArchived)->isEarlier($minDatetimeArchiveProcessedUTC) |
| 108 | ) { |
| 109 | return [false, $visits, $visitsConverted, true, $tsArchived]; |
| 110 | } |
| 111 | |
| 112 | $idArchives = !empty($result['idarchive']) ? $result['idarchive'] : false; |
| 113 | |
| 114 | return [$idArchives, $visits, $visitsConverted, true, $tsArchived]; |
| 115 | } |
| 116 | |
| 117 | /** |
| 118 | * Queries and returns archive IDs for a set of sites, periods, and a segment. |
| 119 | * |
| 120 | * @param int[] $siteIds |
| 121 | * @param Period[] $periods |
| 122 | * @param Segment $segment |
| 123 | * @param string[] $plugins List of plugin names for which data is being requested. |
| 124 | * @param bool $includeInvalidated true to include archives that are DONE_INVALIDATED, false if only DONE_OK. |
| 125 | * @return array Archive IDs are grouped by archive name and period range, ie, |
| 126 | * array( |
| 127 | * 'VisitsSummary.done' => array( |
| 128 | * '2010-01-01' => array(1,2,3) |
| 129 | * ) |
| 130 | * ) |
| 131 | * @throws |
| 132 | */ |
| 133 | public static function getArchiveIds($siteIds, $periods, $segment, $plugins, $includeInvalidated = true) |
| 134 | { |
| 135 | if (empty($siteIds)) { |
| 136 | throw new \Exception("Website IDs could not be read from the request, ie. idSite="); |
| 137 | } |
| 138 | |
| 139 | foreach ($siteIds as $index => $siteId) { |
| 140 | $siteIds[$index] = (int) $siteId; |
| 141 | } |
| 142 | |
| 143 | $getArchiveIdsSql = "SELECT idsite, date1, date2, |
| 144 | GROUP_CONCAT(CONCAT(idarchive,'|',`name`) ORDER BY idarchive DESC SEPARATOR ',') AS archives |
| 145 | FROM %s |
| 146 | WHERE idsite IN (" . implode(',', $siteIds) . ") |
| 147 | AND " . self::getNameCondition($plugins, $segment, $includeInvalidated) . " |
| 148 | AND ts_archived IS NOT NULL |
| 149 | AND %s |
| 150 | GROUP BY idsite, date1, date2"; |
| 151 | |
| 152 | $monthToPeriods = array(); |
| 153 | foreach ($periods as $period) { |
| 154 | /** @var Period $period */ |
| 155 | if ($period->getDateStart()->isLater(Date::now()->addDay(2))) { |
| 156 | continue; // avoid creating any archive tables in the future |
| 157 | } |
| 158 | $table = ArchiveTableCreator::getNumericTable($period->getDateStart()); |
| 159 | $monthToPeriods[$table][] = $period; |
| 160 | } |
| 161 | |
| 162 | $db = Db::get(); |
| 163 | |
| 164 | // for every month within the archive query, select from numeric table |
| 165 | $result = array(); |
| 166 | foreach ($monthToPeriods as $table => $periods) { |
| 167 | $firstPeriod = reset($periods); |
| 168 | |
| 169 | $bind = array(); |
| 170 | |
| 171 | if ($firstPeriod instanceof Range) { |
| 172 | $dateCondition = "date1 = ? AND date2 = ?"; |
| 173 | $bind[] = $firstPeriod->getDateStart()->toString('Y-m-d'); |
| 174 | $bind[] = $firstPeriod->getDateEnd()->toString('Y-m-d'); |
| 175 | } else { |
| 176 | // we assume there is no range date in $periods |
| 177 | $dateCondition = '('; |
| 178 | |
| 179 | foreach ($periods as $period) { |
| 180 | if (strlen($dateCondition) > 1) { |
| 181 | $dateCondition .= ' OR '; |
| 182 | } |
| 183 | |
| 184 | $dateCondition .= "(period = ? AND date1 = ? AND date2 = ?)"; |
| 185 | $bind[] = $period->getId(); |
| 186 | $bind[] = $period->getDateStart()->toString('Y-m-d'); |
| 187 | $bind[] = $period->getDateEnd()->toString('Y-m-d'); |
| 188 | } |
| 189 | |
| 190 | $dateCondition .= ')'; |
| 191 | } |
| 192 | |
| 193 | $sql = sprintf($getArchiveIdsSql, $table, $dateCondition); |
| 194 | $archiveIds = $db->fetchAll($sql, $bind); |
| 195 | |
| 196 | // get the archive IDs. we keep all archives until the first all plugins archive. |
| 197 | // everything older than that one is discarded. |
| 198 | $pluginsFound = []; |
| 199 | foreach ($archiveIds as $row) { |
| 200 | $dateStr = $row['date1'] . ',' . $row['date2']; |
| 201 | |
| 202 | $archives = $row['archives']; |
| 203 | $pairs = explode(',', $archives); |
| 204 | foreach ($pairs as $pair) { |
| 205 | list($idarchive, $doneFlag) = explode('|', $pair); |
| 206 | |
| 207 | $result[$doneFlag][$dateStr][] = $idarchive; |
| 208 | if (strpos($doneFlag, '.') === false) { // all plugins archive |
| 209 | break; // found the all plugins archive, don't need to look in older archives |
| 210 | } else { |
| 211 | list($ignore, $plugin) = explode('.', $doneFlag); |
| 212 | if (empty($pluginsFound[$plugin])) { |
| 213 | $pluginsFound[$plugin] = true; |
| 214 | |
| 215 | $result[$doneFlag][$dateStr][] = $idarchive; |
| 216 | |
| 217 | if (count($plugins) == count($pluginsFound)) { |
| 218 | break; // found archive for every plugin, don't need to keep looking |
| 219 | } |
| 220 | } |
| 221 | } |
| 222 | } |
| 223 | } |
| 224 | } |
| 225 | |
| 226 | return $result; |
| 227 | } |
| 228 | |
| 229 | /** |
| 230 | * Queries and returns archive data using a set of archive IDs. |
| 231 | * |
| 232 | * @param array $archiveIds The IDs of the archives to get data from. |
| 233 | * @param array $recordNames The names of the data to retrieve (ie, nb_visits, nb_actions, etc.). |
| 234 | * Note: You CANNOT pass multiple recordnames if $loadAllSubtables=true. |
| 235 | * @param string $archiveDataType The archive data type (either, 'blob' or 'numeric'). |
| 236 | * @param int|null|string $idSubtable null if the root blob should be loaded, an integer if a subtable should be |
| 237 | * loaded and 'all' if all subtables should be loaded. |
| 238 | * @return array |
| 239 | *@throws Exception |
| 240 | */ |
| 241 | public static function getArchiveData($archiveIds, $recordNames, $archiveDataType, $idSubtable) |
| 242 | { |
| 243 | $chunk = new Chunk(); |
| 244 | |
| 245 | $db = Db::get(); |
| 246 | |
| 247 | // create the SQL to select archive data |
| 248 | $loadAllSubtables = $idSubtable === Archive::ID_SUBTABLE_LOAD_ALL_SUBTABLES; |
| 249 | if ($loadAllSubtables) { |
| 250 | $name = reset($recordNames); |
| 251 | |
| 252 | // select blobs w/ name like "$name_[0-9]+" w/o using RLIKE |
| 253 | $nameEnd = strlen($name) + 1; |
| 254 | $nameEndAppendix = $nameEnd + 1; |
| 255 | $appendix = $chunk->getAppendix(); |
| 256 | $lenAppendix = strlen($appendix); |
| 257 | |
| 258 | $checkForChunkBlob = "SUBSTRING(name, $nameEnd, $lenAppendix) = '$appendix'"; |
| 259 | $checkForSubtableId = "(SUBSTRING(name, $nameEndAppendix, 1) >= '0' |
| 260 | AND SUBSTRING(name, $nameEndAppendix, 1) <= '9')"; |
| 261 | |
| 262 | $whereNameIs = "(name = ? OR (name LIKE ? AND ( $checkForChunkBlob OR $checkForSubtableId ) ))"; |
| 263 | $bind = array($name, $name . '%'); |
| 264 | } else { |
| 265 | if ($idSubtable === null) { |
| 266 | // select root table or specific record names |
| 267 | $bind = array_values($recordNames); |
| 268 | } else { |
| 269 | // select a subtable id |
| 270 | $bind = array(); |
| 271 | foreach ($recordNames as $recordName) { |
| 272 | // to be backwards compatibe we need to look for the exact idSubtable blob and for the chunk |
| 273 | // that stores the subtables (a chunk stores many blobs in one blob) |
| 274 | $bind[] = $chunk->getRecordNameForTableId($recordName, $idSubtable); |
| 275 | $bind[] = self::appendIdSubtable($recordName, $idSubtable); |
| 276 | } |
| 277 | } |
| 278 | |
| 279 | $inNames = Common::getSqlStringFieldsArray($bind); |
| 280 | $whereNameIs = "name IN ($inNames)"; |
| 281 | } |
| 282 | |
| 283 | $getValuesSql = "SELECT value, name, idsite, date1, date2, ts_archived |
| 284 | FROM %s |
| 285 | WHERE idarchive IN (%s) |
| 286 | AND " . $whereNameIs . " |
| 287 | ORDER BY ts_archived ASC"; // ascending order so we use the latest data found |
| 288 | |
| 289 | // get data from every table we're querying |
| 290 | $rows = array(); |
| 291 | foreach ($archiveIds as $period => $ids) { |
| 292 | if (empty($ids)) { |
| 293 | throw new Exception("Unexpected: id archive not found for period '$period' '"); |
| 294 | } |
| 295 | |
| 296 | // $period = "2009-01-04,2009-01-04", |
| 297 | $date = Date::factory(substr($period, 0, 10)); |
| 298 | |
| 299 | $isNumeric = $archiveDataType === 'numeric'; |
| 300 | if ($isNumeric) { |
| 301 | $table = ArchiveTableCreator::getNumericTable($date); |
| 302 | } else { |
| 303 | $table = ArchiveTableCreator::getBlobTable($date); |
| 304 | } |
| 305 | |
| 306 | $sql = sprintf($getValuesSql, $table, implode(',', $ids)); |
| 307 | $dataRows = $db->fetchAll($sql, $bind); |
| 308 | |
| 309 | foreach ($dataRows as $row) { |
| 310 | if ($isNumeric) { |
| 311 | $rows[] = $row; |
| 312 | } else { |
| 313 | $row['value'] = self::uncompress($row['value']); |
| 314 | |
| 315 | if ($chunk->isRecordNameAChunk($row['name'])) { |
| 316 | self::moveChunkRowToRows($rows, $row, $chunk, $loadAllSubtables, $idSubtable); |
| 317 | } else { |
| 318 | $rows[] = $row; |
| 319 | } |
| 320 | } |
| 321 | } |
| 322 | } |
| 323 | |
| 324 | return $rows; |
| 325 | } |
| 326 | |
| 327 | private static function moveChunkRowToRows(&$rows, $row, Chunk $chunk, $loadAllSubtables, $idSubtable) |
| 328 | { |
| 329 | // $blobs = array([subtableID] = [blob of subtableId]) |
| 330 | $blobs = Common::safe_unserialize($row['value']); |
| 331 | |
| 332 | if (!is_array($blobs)) { |
| 333 | return; |
| 334 | } |
| 335 | |
| 336 | // $rawName = eg 'PluginName_ArchiveName' |
| 337 | $rawName = $chunk->getRecordNameWithoutChunkAppendix($row['name']); |
| 338 | |
| 339 | if ($loadAllSubtables) { |
| 340 | foreach ($blobs as $subtableId => $blob) { |
| 341 | $row['value'] = $blob; |
| 342 | $row['name'] = self::appendIdSubtable($rawName, $subtableId); |
| 343 | $rows[] = $row; |
| 344 | } |
| 345 | } elseif (array_key_exists($idSubtable, $blobs)) { |
| 346 | $row['value'] = $blobs[$idSubtable]; |
| 347 | $row['name'] = self::appendIdSubtable($rawName, $idSubtable); |
| 348 | $rows[] = $row; |
| 349 | } |
| 350 | } |
| 351 | |
| 352 | public static function appendIdSubtable($recordName, $id) |
| 353 | { |
| 354 | return $recordName . "_" . $id; |
| 355 | } |
| 356 | |
| 357 | private static function uncompress($data) |
| 358 | { |
| 359 | return @gzuncompress($data); |
| 360 | } |
| 361 | |
| 362 | /** |
| 363 | * Returns the SQL condition used to find successfully completed archives that |
| 364 | * this instance is querying for. |
| 365 | * |
| 366 | * @param array $plugins |
| 367 | * @param Segment $segment |
| 368 | * @param bool $includeInvalidated |
| 369 | * @return string |
| 370 | */ |
| 371 | private static function getNameCondition(array $plugins, Segment $segment, $includeInvalidated = true) |
| 372 | { |
| 373 | // the flags used to tell how the archiving process for a specific archive was completed, |
| 374 | // if it was completed |
| 375 | $doneFlags = Rules::getDoneFlags($plugins, $segment); |
| 376 | $allDoneFlags = "'" . implode("','", $doneFlags) . "'"; |
| 377 | |
| 378 | $possibleValues = Rules::getSelectableDoneFlagValues($includeInvalidated); |
| 379 | |
| 380 | // create the SQL to find archives that are DONE |
| 381 | return "((name IN ($allDoneFlags)) AND (value IN (" . implode(',', $possibleValues) . ")))"; |
| 382 | } |
| 383 | |
| 384 | /** |
| 385 | * This method takes the output of Model::getArchiveIdAndVisits() and selects data from the |
| 386 | * latest archives. |
| 387 | * |
| 388 | * This includes: |
| 389 | * - the idarchive with the latest ts_archived ($results will be ordered by ts_archived desc) |
| 390 | * - the visits/converted visits of the latest archive, which includes archives for VisitsSummary alone |
| 391 | * ($requestedPluginDoneFlags will have the done flag for the overall archive plus a done flag for |
| 392 | * VisitsSummary by itself) |
| 393 | * - the ts_archived for the latest idarchive |
| 394 | * - the doneFlag value for the latest archive |
| 395 | * |
| 396 | * @param $results |
| 397 | * @param $requestedPluginDoneFlags |
| 398 | * @return array |
| 399 | */ |
| 400 | private static function findArchiveDataWithLatestTsArchived($results, $requestedPluginDoneFlags) |
| 401 | { |
| 402 | // find latest idarchive for each done flag |
| 403 | $idArchives = []; |
| 404 | $tsArchiveds = []; |
| 405 | foreach ($results as $row) { |
| 406 | $doneFlag = $row['name']; |
| 407 | if (!isset($idArchives[$doneFlag]) && $row['value'] != ArchiveWriter::DONE_PARTIAL) { |
| 408 | $idArchives[$doneFlag] = $row['idarchive']; |
| 409 | $tsArchiveds[$doneFlag] = $row['ts_archived']; |
| 410 | } |
| 411 | } |
| 412 | |
| 413 | $archiveData = [ |
| 414 | self::NB_VISITS_RECORD_LOOKED_UP => false, |
| 415 | self::NB_VISITS_CONVERTED_RECORD_LOOKED_UP => false, |
| 416 | ]; |
| 417 | |
| 418 | foreach ($results as $result) { |
| 419 | if (in_array($result['name'], $requestedPluginDoneFlags) |
| 420 | && in_array($result['idarchive'], $idArchives) |
| 421 | ) { |
| 422 | $archiveData = $result; |
| 423 | if (empty($archiveData[self::NB_VISITS_RECORD_LOOKED_UP])) { |
| 424 | $archiveData[self::NB_VISITS_RECORD_LOOKED_UP] = 0; |
| 425 | } |
| 426 | if (empty($archiveData[self::NB_VISITS_CONVERTED_RECORD_LOOKED_UP])) { |
| 427 | $archiveData[self::NB_VISITS_CONVERTED_RECORD_LOOKED_UP] = 0; |
| 428 | } |
| 429 | break; |
| 430 | } |
| 431 | } |
| 432 | |
| 433 | foreach ([self::NB_VISITS_RECORD_LOOKED_UP, self::NB_VISITS_CONVERTED_RECORD_LOOKED_UP] as $metric) { |
| 434 | foreach ($results as $result) { |
| 435 | if (!in_array($result['idarchive'], $idArchives)) { |
| 436 | continue; |
| 437 | } |
| 438 | |
| 439 | if (empty($archiveData[$metric])) { |
| 440 | if (!empty($result[$metric]) || $result[$metric] === 0 || $result[$metric] === '0') { |
| 441 | $archiveData[$metric] = $result[$metric]; |
| 442 | } |
| 443 | } |
| 444 | } |
| 445 | } |
| 446 | |
| 447 | // add partial archives |
| 448 | foreach ($results as $row) { |
| 449 | if (!isset($idArchives[$row['name']])) { |
| 450 | continue; |
| 451 | } |
| 452 | |
| 453 | $mainTsArchived = $tsArchiveds[$row['name']]; |
| 454 | $thisTsArchived = $row['ts_archived']; |
| 455 | |
| 456 | if ($row['value'] === ArchiveWriter::DONE_PARTIAL |
| 457 | && ($mainTsArchived == $thisTsArchived || Date::factory($mainTsArchived)->isEarlier($thisTsArchived)) |
| 458 | ) { |
| 459 | $idArchives['partial'][] = $row['idarchive']; |
| 460 | } |
| 461 | } |
| 462 | |
| 463 | return $archiveData; |
| 464 | } |
| 465 | } |
| 466 |