LogQueryBuilder
3 years ago
Actions.php
5 years ago
ArchiveSelector.php
3 years ago
ArchiveTableCreator.php
5 years ago
ArchiveTableDao.php
5 years ago
ArchiveWriter.php
3 years ago
ArchivingDbAdapter.php
4 years ago
LogAggregator.php
3 years ago
LogQueryBuilder.php
5 years ago
LogTableTemporary.php
5 years ago
Model.php
3 years ago
RawLogDao.php
4 years ago
TableMetadata.php
5 years ago
ArchiveSelector.php
631 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\Container\StaticContainer; |
| 18 | use Piwik\Date; |
| 19 | use Piwik\Db; |
| 20 | use Piwik\Period; |
| 21 | use Piwik\Period\Range; |
| 22 | use Piwik\Segment; |
| 23 | use Psr\Log\LoggerInterface; |
| 24 | |
| 25 | /** |
| 26 | * Data Access object used to query archives |
| 27 | * |
| 28 | * A record in the Database for a given report is defined by |
| 29 | * - idarchive = unique ID that is associated to all the data of this archive (idsite+period+date) |
| 30 | * - idsite = the ID of the website |
| 31 | * - date1 = starting day of the period |
| 32 | * - date2 = ending day of the period |
| 33 | * - period = integer that defines the period (day/week/etc.). @see period::getId() |
| 34 | * - ts_archived = timestamp when the archive was processed (UTC) |
| 35 | * - name = the name of the report (ex: uniq_visitors or search_keywords_by_search_engines) |
| 36 | * - value = the actual data (a numeric value, or a blob of compressed serialized data) |
| 37 | * |
| 38 | */ |
| 39 | class ArchiveSelector |
| 40 | { |
| 41 | const NB_VISITS_RECORD_LOOKED_UP = "nb_visits"; |
| 42 | |
| 43 | const NB_VISITS_CONVERTED_RECORD_LOOKED_UP = "nb_visits_converted"; |
| 44 | |
| 45 | private static function getModel() |
| 46 | { |
| 47 | return new Model(); |
| 48 | } |
| 49 | |
| 50 | /** |
| 51 | * @param ArchiveProcessor\Parameters $params |
| 52 | * @param bool $minDatetimeArchiveProcessedUTC deprecated. Will be removed in Matomo 4. |
| 53 | * @return array An array with four values: |
| 54 | * - the latest archive ID or false if none |
| 55 | * - the latest visits value for the latest archive, regardless of whether the archive is invalidated or not |
| 56 | * - the latest visits converted value for the latest archive, regardless of whether the archive is invalidated or not |
| 57 | * - whether there is an archive that exists or not. if this is true and the latest archive is false, it means |
| 58 | * the archive found was not usable (for example, it was invalidated and we are not looking for invalidated archives) |
| 59 | * - the ts_archived for the latest usable archive |
| 60 | * @throws Exception |
| 61 | */ |
| 62 | public static function getArchiveIdAndVisits(ArchiveProcessor\Parameters $params, $minDatetimeArchiveProcessedUTC = false, $includeInvalidated = null) |
| 63 | { |
| 64 | $idSite = $params->getSite()->getId(); |
| 65 | $period = $params->getPeriod()->getId(); |
| 66 | $dateStart = $params->getPeriod()->getDateStart(); |
| 67 | $dateStartIso = $dateStart->toString('Y-m-d'); |
| 68 | $dateEndIso = $params->getPeriod()->getDateEnd()->toString('Y-m-d'); |
| 69 | |
| 70 | $numericTable = ArchiveTableCreator::getNumericTable($dateStart); |
| 71 | |
| 72 | $requestedPlugin = $params->getRequestedPlugin(); |
| 73 | $segment = $params->getSegment(); |
| 74 | $plugins = array("VisitsSummary", $requestedPlugin); |
| 75 | $plugins = array_filter($plugins); |
| 76 | |
| 77 | $doneFlags = Rules::getDoneFlags($plugins, $segment); |
| 78 | |
| 79 | $requestedPluginDoneFlags = empty($requestedPlugin) ? [] : Rules::getDoneFlags([$requestedPlugin], $segment); |
| 80 | $allPluginsDoneFlag = Rules::getDoneFlagArchiveContainsAllPlugins($segment); |
| 81 | |
| 82 | $doneFlagValues = Rules::getSelectableDoneFlagValues($includeInvalidated === null ? true : $includeInvalidated, $params, $includeInvalidated === null); |
| 83 | |
| 84 | $results = self::getModel()->getArchiveIdAndVisits($numericTable, $idSite, $period, $dateStartIso, $dateEndIso, null, $doneFlags); |
| 85 | if (empty($results)) { // no archive found |
| 86 | return [false, false, false, false, false, false]; |
| 87 | } |
| 88 | |
| 89 | $result = self::findArchiveDataWithLatestTsArchived($results, $requestedPluginDoneFlags, $allPluginsDoneFlag); |
| 90 | |
| 91 | $tsArchived = isset($result['ts_archived']) ? $result['ts_archived'] : false; |
| 92 | $visits = isset($result['nb_visits']) ? $result['nb_visits'] : false; |
| 93 | $visitsConverted = isset($result['nb_visits_converted']) ? $result['nb_visits_converted'] : false; |
| 94 | $value = isset($result['value']) ? $result['value'] : false; |
| 95 | |
| 96 | $result['idarchive'] = empty($result['idarchive']) ? [] : [$result['idarchive']]; |
| 97 | if (isset($result['partial'])) { |
| 98 | $result['idarchive'] = array_merge($result['idarchive'], $result['partial']); |
| 99 | } |
| 100 | |
| 101 | if (empty($result['idarchive']) |
| 102 | || (isset($result['value']) |
| 103 | && !in_array($result['value'], $doneFlagValues)) |
| 104 | ) { // the archive cannot be considered valid for this request (has wrong done flag value) |
| 105 | return [false, $visits, $visitsConverted, true, $tsArchived, $value]; |
| 106 | } |
| 107 | |
| 108 | if (!empty($minDatetimeArchiveProcessedUTC) && !is_object($minDatetimeArchiveProcessedUTC)) { |
| 109 | $minDatetimeArchiveProcessedUTC = Date::factory($minDatetimeArchiveProcessedUTC); |
| 110 | } |
| 111 | |
| 112 | // the archive is too old |
| 113 | if ($minDatetimeArchiveProcessedUTC |
| 114 | && !empty($result['idarchive']) |
| 115 | && Date::factory($tsArchived)->isEarlier($minDatetimeArchiveProcessedUTC) |
| 116 | ) { |
| 117 | return [false, $visits, $visitsConverted, true, $tsArchived, $value]; |
| 118 | } |
| 119 | |
| 120 | $idArchives = !empty($result['idarchive']) ? $result['idarchive'] : false; |
| 121 | |
| 122 | return [$idArchives, $visits, $visitsConverted, true, $tsArchived, $value]; |
| 123 | } |
| 124 | |
| 125 | /** |
| 126 | * Queries and returns archive IDs for a set of sites, periods, and a segment. |
| 127 | * |
| 128 | * @param int[] $siteIds |
| 129 | * @param Period[] $periods |
| 130 | * @param Segment $segment |
| 131 | * @param string[] $plugins List of plugin names for which data is being requested. |
| 132 | * @param bool $includeInvalidated true to include archives that are DONE_INVALIDATED, false if only DONE_OK. |
| 133 | * @param bool $_skipSetGroupConcatMaxLen for tests |
| 134 | * @return array Archive IDs are grouped by archive name and period range, ie, |
| 135 | * array( |
| 136 | * 'VisitsSummary.done' => array( |
| 137 | * '2010-01-01' => array(1,2,3) |
| 138 | * ) |
| 139 | * ) |
| 140 | * @throws |
| 141 | */ |
| 142 | public static function getArchiveIds($siteIds, $periods, $segment, $plugins, $includeInvalidated = true, $_skipSetGroupConcatMaxLen = false) |
| 143 | { |
| 144 | $logger = StaticContainer::get(LoggerInterface::class); |
| 145 | if (!$_skipSetGroupConcatMaxLen) { |
| 146 | try { |
| 147 | Db::get()->query('SET SESSION group_concat_max_len=' . (128 * 1024)); |
| 148 | } catch (\Exception $ex) { |
| 149 | $logger->info("Could not set group_concat_max_len MySQL session variable."); |
| 150 | } |
| 151 | } |
| 152 | |
| 153 | if (empty($siteIds)) { |
| 154 | throw new \Exception("Website IDs could not be read from the request, ie. idSite="); |
| 155 | } |
| 156 | |
| 157 | foreach ($siteIds as $index => $siteId) { |
| 158 | $siteIds[$index] = (int) $siteId; |
| 159 | } |
| 160 | |
| 161 | $getArchiveIdsSql = "SELECT idsite, date1, date2, |
| 162 | GROUP_CONCAT(CONCAT(idarchive,'|',`name`,'|',`value`) ORDER BY idarchive DESC SEPARATOR ',') AS archives |
| 163 | FROM %s |
| 164 | WHERE idsite IN (" . implode(',', $siteIds) . ") |
| 165 | AND " . self::getNameCondition($plugins, $segment, $includeInvalidated) . " |
| 166 | AND ts_archived IS NOT NULL |
| 167 | AND %s |
| 168 | GROUP BY idsite, date1, date2"; |
| 169 | |
| 170 | $monthToPeriods = array(); |
| 171 | foreach ($periods as $period) { |
| 172 | /** @var Period $period */ |
| 173 | if ($period->getDateStart()->isLater(Date::now()->addDay(2))) { |
| 174 | continue; // avoid creating any archive tables in the future |
| 175 | } |
| 176 | $table = ArchiveTableCreator::getNumericTable($period->getDateStart()); |
| 177 | $monthToPeriods[$table][] = $period; |
| 178 | } |
| 179 | |
| 180 | $db = Db::get(); |
| 181 | |
| 182 | // for every month within the archive query, select from numeric table |
| 183 | $result = array(); |
| 184 | foreach ($monthToPeriods as $table => $periods) { |
| 185 | $firstPeriod = reset($periods); |
| 186 | |
| 187 | $bind = array(); |
| 188 | |
| 189 | if ($firstPeriod instanceof Range) { |
| 190 | $dateCondition = "date1 = ? AND date2 = ?"; |
| 191 | $bind[] = $firstPeriod->getDateStart()->toString('Y-m-d'); |
| 192 | $bind[] = $firstPeriod->getDateEnd()->toString('Y-m-d'); |
| 193 | } else { |
| 194 | // we assume there is no range date in $periods |
| 195 | $dateCondition = '('; |
| 196 | |
| 197 | foreach ($periods as $period) { |
| 198 | if (strlen($dateCondition) > 1) { |
| 199 | $dateCondition .= ' OR '; |
| 200 | } |
| 201 | |
| 202 | $dateCondition .= "(period = ? AND date1 = ? AND date2 = ?)"; |
| 203 | $bind[] = $period->getId(); |
| 204 | $bind[] = $period->getDateStart()->toString('Y-m-d'); |
| 205 | $bind[] = $period->getDateEnd()->toString('Y-m-d'); |
| 206 | } |
| 207 | |
| 208 | $dateCondition .= ')'; |
| 209 | } |
| 210 | |
| 211 | $sql = sprintf($getArchiveIdsSql, $table, $dateCondition); |
| 212 | $archiveIds = $db->fetchAll($sql, $bind); |
| 213 | |
| 214 | // get the archive IDs. we keep all archives until the first all plugins archive. |
| 215 | // everything older than that one is discarded. |
| 216 | foreach ($archiveIds as $row) { |
| 217 | $dateStr = $row['date1'] . ',' . $row['date2']; |
| 218 | |
| 219 | $archives = $row['archives']; |
| 220 | $pairs = explode(',', $archives); |
| 221 | foreach ($pairs as $pair) { |
| 222 | $parts = explode('|', $pair); |
| 223 | if (count($parts) != 3) { // GROUP_CONCAT got cut off, have to ignore the rest |
| 224 | // note: in this edge case, we end up not selecting the all plugins archive because it will be older than the partials. |
| 225 | // not ideal, but it avoids an exception. |
| 226 | $logger->info("GROUP_CONCAT got cut off in ArchiveSelector." . __FUNCTION__ . ' for idsite = ' . $row['idsite'] . ', period = ' . $dateStr); |
| 227 | continue; |
| 228 | } |
| 229 | |
| 230 | list($idarchive, $doneFlag, $value) = $parts; |
| 231 | |
| 232 | $result[$doneFlag][$dateStr][] = $idarchive; |
| 233 | if (strpos($doneFlag, '.') === false // all plugins archive |
| 234 | // sanity check: DONE_PARTIAL shouldn't be used w/ done archives, but in case we see one, |
| 235 | // don't treat it like an all plugins archive |
| 236 | && $value != ArchiveWriter::DONE_PARTIAL |
| 237 | ) { |
| 238 | break; // found the all plugins archive, don't need to look in older archives since we have everything here |
| 239 | } |
| 240 | } |
| 241 | } |
| 242 | } |
| 243 | |
| 244 | return $result; |
| 245 | } |
| 246 | |
| 247 | /** |
| 248 | * Queries and returns archive data using a set of archive IDs. |
| 249 | * |
| 250 | * @param array $archiveIds The IDs of the archives to get data from. |
| 251 | * @param array $recordNames The names of the data to retrieve (ie, nb_visits, nb_actions, etc.). |
| 252 | * Note: You CANNOT pass multiple recordnames if $loadAllSubtables=true. |
| 253 | * @param string $archiveDataType The archive data type (either, 'blob' or 'numeric'). |
| 254 | * @param int|null|string $idSubtable null if the root blob should be loaded, an integer if a subtable should be |
| 255 | * loaded and 'all' if all subtables should be loaded. |
| 256 | * @return array |
| 257 | *@throws Exception |
| 258 | */ |
| 259 | public static function getArchiveData($archiveIds, $recordNames, $archiveDataType, $idSubtable) |
| 260 | { |
| 261 | $chunk = new Chunk(); |
| 262 | $db = Db::get(); |
| 263 | |
| 264 | $loadAllSubtables = $idSubtable === Archive::ID_SUBTABLE_LOAD_ALL_SUBTABLES; |
| 265 | [$getValuesSql, $bind] = self::getSqlTemplateToFetchArchiveData($recordNames, $idSubtable); |
| 266 | |
| 267 | $archiveIdsPerMonth = self::getArchiveIdsByYearMonth($archiveIds); |
| 268 | |
| 269 | // get data from every table we're querying |
| 270 | $rows = array(); |
| 271 | foreach ($archiveIdsPerMonth as $yearMonth => $ids) { |
| 272 | if (empty($ids)) { |
| 273 | throw new Exception("Unexpected: id archive not found for period '$yearMonth' '"); |
| 274 | } |
| 275 | |
| 276 | // $yearMonth = "2022-11", |
| 277 | $date = Date::factory($yearMonth . '-01'); |
| 278 | |
| 279 | $isNumeric = $archiveDataType === 'numeric'; |
| 280 | if ($isNumeric) { |
| 281 | $table = ArchiveTableCreator::getNumericTable($date); |
| 282 | } else { |
| 283 | $table = ArchiveTableCreator::getBlobTable($date); |
| 284 | } |
| 285 | |
| 286 | $ids = array_map('intval', $ids); |
| 287 | $sql = sprintf($getValuesSql, $table, implode(',', $ids)); |
| 288 | $dataRows = $db->fetchAll($sql, $bind); |
| 289 | |
| 290 | foreach ($dataRows as $row) { |
| 291 | if ($isNumeric) { |
| 292 | $rows[] = $row; |
| 293 | } else { |
| 294 | $row['value'] = self::uncompress($row['value']); |
| 295 | |
| 296 | if ($chunk->isRecordNameAChunk($row['name'])) { |
| 297 | self::moveChunkRowToRows($rows, $row, $chunk, $loadAllSubtables, $idSubtable); |
| 298 | } else { |
| 299 | $rows[] = $row; |
| 300 | } |
| 301 | } |
| 302 | } |
| 303 | } |
| 304 | |
| 305 | return $rows; |
| 306 | } |
| 307 | |
| 308 | private static function moveChunkRowToRows(&$rows, $row, Chunk $chunk, $loadAllSubtables, $idSubtable) |
| 309 | { |
| 310 | // $blobs = array([subtableID] = [blob of subtableId]) |
| 311 | $blobs = Common::safe_unserialize($row['value']); |
| 312 | |
| 313 | if (!is_array($blobs)) { |
| 314 | return; |
| 315 | } |
| 316 | |
| 317 | // $rawName = eg 'PluginName_ArchiveName' |
| 318 | $rawName = $chunk->getRecordNameWithoutChunkAppendix($row['name']); |
| 319 | |
| 320 | if ($loadAllSubtables) { |
| 321 | foreach ($blobs as $subtableId => $blob) { |
| 322 | $row['value'] = $blob; |
| 323 | $row['name'] = self::appendIdSubtable($rawName, $subtableId); |
| 324 | $rows[] = $row; |
| 325 | } |
| 326 | } elseif (array_key_exists($idSubtable, $blobs)) { |
| 327 | $row['value'] = $blobs[$idSubtable]; |
| 328 | $row['name'] = self::appendIdSubtable($rawName, $idSubtable); |
| 329 | $rows[] = $row; |
| 330 | } |
| 331 | } |
| 332 | |
| 333 | public static function appendIdSubtable($recordName, $id) |
| 334 | { |
| 335 | return $recordName . "_" . $id; |
| 336 | } |
| 337 | |
| 338 | public static function uncompress($data) |
| 339 | { |
| 340 | return @gzuncompress($data); |
| 341 | } |
| 342 | |
| 343 | /** |
| 344 | * Returns the SQL condition used to find successfully completed archives that |
| 345 | * this instance is querying for. |
| 346 | * |
| 347 | * @param array $plugins |
| 348 | * @param Segment $segment |
| 349 | * @param bool $includeInvalidated |
| 350 | * @return string |
| 351 | */ |
| 352 | private static function getNameCondition(array $plugins, Segment $segment, $includeInvalidated = true) |
| 353 | { |
| 354 | // the flags used to tell how the archiving process for a specific archive was completed, |
| 355 | // if it was completed |
| 356 | $doneFlags = Rules::getDoneFlags($plugins, $segment); |
| 357 | $allDoneFlags = "'" . implode("','", $doneFlags) . "'"; |
| 358 | |
| 359 | $possibleValues = Rules::getSelectableDoneFlagValues($includeInvalidated, null, $checkAuthorizedToArchive = false); |
| 360 | |
| 361 | // create the SQL to find archives that are DONE |
| 362 | return "((name IN ($allDoneFlags)) AND (value IN (" . implode(',', $possibleValues) . ")))"; |
| 363 | } |
| 364 | |
| 365 | /** |
| 366 | * This method takes the output of Model::getArchiveIdAndVisits() and selects data from the |
| 367 | * latest archives. |
| 368 | * |
| 369 | * This includes: |
| 370 | * - the idarchive with the latest ts_archived ($results will be ordered by ts_archived desc) |
| 371 | * - the visits/converted visits of the latest archive, which includes archives for VisitsSummary alone |
| 372 | * ($requestedPluginDoneFlags will have the done flag for the overall archive plus a done flag for |
| 373 | * VisitsSummary by itself) |
| 374 | * - the ts_archived for the latest idarchive |
| 375 | * - the doneFlag value for the latest archive |
| 376 | * |
| 377 | * @param $results |
| 378 | * @param $doneFlags |
| 379 | * @return array |
| 380 | */ |
| 381 | private static function findArchiveDataWithLatestTsArchived($results, $requestedPluginDoneFlags, $allPluginsDoneFlag) |
| 382 | { |
| 383 | $doneFlags = array_merge($requestedPluginDoneFlags, [$allPluginsDoneFlag]); |
| 384 | |
| 385 | // find latest idarchive for each done flag |
| 386 | $idArchives = []; |
| 387 | $tsArchiveds = []; |
| 388 | foreach ($results as $row) { |
| 389 | $doneFlag = $row['name']; |
| 390 | if (!isset($idArchives[$doneFlag])) { |
| 391 | $idArchives[$doneFlag] = $row['idarchive']; |
| 392 | $tsArchiveds[$doneFlag] = $row['ts_archived']; |
| 393 | } |
| 394 | } |
| 395 | |
| 396 | $archiveData = [ |
| 397 | self::NB_VISITS_RECORD_LOOKED_UP => false, |
| 398 | self::NB_VISITS_CONVERTED_RECORD_LOOKED_UP => false, |
| 399 | ]; |
| 400 | |
| 401 | foreach ($results as $result) { |
| 402 | if (in_array($result['name'], $doneFlags) |
| 403 | && in_array($result['idarchive'], $idArchives) |
| 404 | && $result['value'] != ArchiveWriter::DONE_PARTIAL |
| 405 | ) { |
| 406 | $archiveData = $result; |
| 407 | if (empty($archiveData[self::NB_VISITS_RECORD_LOOKED_UP])) { |
| 408 | $archiveData[self::NB_VISITS_RECORD_LOOKED_UP] = 0; |
| 409 | } |
| 410 | if (empty($archiveData[self::NB_VISITS_CONVERTED_RECORD_LOOKED_UP])) { |
| 411 | $archiveData[self::NB_VISITS_CONVERTED_RECORD_LOOKED_UP] = 0; |
| 412 | } |
| 413 | break; |
| 414 | } |
| 415 | } |
| 416 | |
| 417 | foreach ([self::NB_VISITS_RECORD_LOOKED_UP, self::NB_VISITS_CONVERTED_RECORD_LOOKED_UP] as $metric) { |
| 418 | foreach ($results as $result) { |
| 419 | if (!in_array($result['idarchive'], $idArchives)) { |
| 420 | continue; |
| 421 | } |
| 422 | |
| 423 | if (empty($archiveData[$metric])) { |
| 424 | if (!empty($result[$metric]) || $result[$metric] === 0 || $result[$metric] === '0') { |
| 425 | $archiveData[$metric] = $result[$metric]; |
| 426 | } |
| 427 | } |
| 428 | } |
| 429 | } |
| 430 | |
| 431 | // add partial archives |
| 432 | $mainTsArchived = isset($tsArchiveds[$allPluginsDoneFlag]) ? $tsArchiveds[$allPluginsDoneFlag] : null; |
| 433 | foreach ($results as $row) { |
| 434 | if (!isset($idArchives[$row['name']])) { |
| 435 | continue; |
| 436 | } |
| 437 | |
| 438 | $thisTsArchived = Date::factory($row['ts_archived']); |
| 439 | if ($row['value'] == ArchiveWriter::DONE_PARTIAL |
| 440 | && (empty($mainTsArchived) || !Date::factory($mainTsArchived)->isLater($thisTsArchived)) |
| 441 | ) { |
| 442 | $archiveData['partial'][] = $row['idarchive']; |
| 443 | |
| 444 | if (empty($archiveData['ts_archived'])) { |
| 445 | $archiveData['ts_archived'] = $row['ts_archived']; |
| 446 | } |
| 447 | } |
| 448 | } |
| 449 | |
| 450 | return $archiveData; |
| 451 | } |
| 452 | |
| 453 | public static function querySingleBlob(array $archiveIds, string $recordName) |
| 454 | { |
| 455 | $chunk = new Chunk(); |
| 456 | |
| 457 | [$getValuesSql, $bind] = self::getSqlTemplateToFetchArchiveData( |
| 458 | [$recordName], Archive::ID_SUBTABLE_LOAD_ALL_SUBTABLES, true); |
| 459 | |
| 460 | $archiveIdsPerMonth = self::getArchiveIdsByYearMonth($archiveIds); |
| 461 | |
| 462 | $periodsSeen = []; |
| 463 | |
| 464 | // $yearMonth = "2022-11", |
| 465 | foreach ($archiveIdsPerMonth as $yearMonth => $ids) { |
| 466 | $date = Date::factory($yearMonth . '-01'); |
| 467 | |
| 468 | $table = ArchiveTableCreator::getBlobTable($date); |
| 469 | |
| 470 | $ids = array_map('intval', $ids); |
| 471 | $sql = sprintf($getValuesSql, $table, implode(',', $ids)); |
| 472 | |
| 473 | $cursor = Db::get()->query($sql, $bind); |
| 474 | while ($row = $cursor->fetch()) { |
| 475 | $period = $row['date1'] . ',' . $row['date2']; |
| 476 | $recordName = $row['name']; |
| 477 | |
| 478 | // FIXME: This hack works around a strange bug that occurs when getting |
| 479 | // archive IDs through ArchiveProcessing instances. When a table |
| 480 | // does not already exist, for some reason the archive ID for |
| 481 | // today (or from two days ago) will be added to the Archive |
| 482 | // instances list. The Archive instance will then select data |
| 483 | // for periods outside of the requested set. |
| 484 | // working around the bug here, but ideally, we need to figure |
| 485 | // out why incorrect idarchives are being selected. |
| 486 | if (empty($archiveIds[$period])) { |
| 487 | continue; |
| 488 | } |
| 489 | |
| 490 | // only use the first period/blob name combination seen (since we order by ts_archived descending) |
| 491 | if (!empty($periodsSeen[$period][$recordName])) { |
| 492 | continue; |
| 493 | } |
| 494 | |
| 495 | $periodsSeen[$period][$recordName] = true; |
| 496 | |
| 497 | $row['value'] = ArchiveSelector::uncompress($row['value']); |
| 498 | if ($chunk->isRecordNameAChunk($row['name'])) { |
| 499 | // $blobs = array([subtableID] = [blob of subtableId]) |
| 500 | $blobs = Common::safe_unserialize($row['value']); |
| 501 | if (!is_array($blobs)) { |
| 502 | yield $row; |
| 503 | } |
| 504 | |
| 505 | ksort($blobs); |
| 506 | |
| 507 | // $rawName = eg 'PluginName_ArchiveName' |
| 508 | $rawName = $chunk->getRecordNameWithoutChunkAppendix($row['name']); |
| 509 | foreach ($blobs as $subtableId => $blob) { |
| 510 | yield array_merge($row, [ |
| 511 | 'value' => $blob, |
| 512 | 'name' => ArchiveSelector::appendIdSubtable($rawName, $subtableId), |
| 513 | ]); |
| 514 | } |
| 515 | } else { |
| 516 | yield $row; |
| 517 | } |
| 518 | } |
| 519 | } |
| 520 | } |
| 521 | |
| 522 | /** |
| 523 | * Returns SQL to fetch data from an archive table. The SQL has two %s placeholders, one for the |
| 524 | * archive table name and another for the comma separated list of archive IDs to look for. |
| 525 | * |
| 526 | * @param array $recordNames The list of records to look for. |
| 527 | * @param string|int $idSubtable The idSubtable to look for or 'all' to load all of them. |
| 528 | * @param boolean $orderBySubtableId If true, orders the result set by start date ascending, subtable ID |
| 529 | * ascending and ts_archived descending. Only applied if loading all |
| 530 | * subtables for a single record. |
| 531 | * |
| 532 | * This parameter is used when aggregating blob data for a single record |
| 533 | * without loading entire datatable trees in memory. |
| 534 | * @return array The sql and bind values. |
| 535 | */ |
| 536 | private static function getSqlTemplateToFetchArchiveData(array $recordNames, $idSubtable, $orderBySubtableId = false) |
| 537 | { |
| 538 | $chunk = new Chunk(); |
| 539 | |
| 540 | $orderBy = 'ORDER BY ts_archived ASC'; |
| 541 | |
| 542 | // create the SQL to select archive data |
| 543 | $loadAllSubtables = $idSubtable === Archive::ID_SUBTABLE_LOAD_ALL_SUBTABLES; |
| 544 | if ($loadAllSubtables) { |
| 545 | $name = reset($recordNames); |
| 546 | |
| 547 | // select blobs w/ name like "$name_[0-9]+" w/o using RLIKE |
| 548 | $nameEnd = strlen($name) + 1; |
| 549 | $nameEndAppendix = $nameEnd + 1; |
| 550 | $appendix = $chunk->getAppendix(); |
| 551 | $lenAppendix = strlen($appendix); |
| 552 | |
| 553 | $checkForChunkBlob = "SUBSTRING(name, $nameEnd, $lenAppendix) = '$appendix'"; |
| 554 | $checkForSubtableId = "(SUBSTRING(name, $nameEndAppendix, 1) >= '0' |
| 555 | AND SUBSTRING(name, $nameEndAppendix, 1) <= '9')"; |
| 556 | |
| 557 | $whereNameIs = "(name = ? OR (name LIKE ? AND ( $checkForChunkBlob OR $checkForSubtableId ) ))"; |
| 558 | $bind = array($name, $name . '%'); |
| 559 | |
| 560 | if ($orderBySubtableId && count($recordNames) == 1) { |
| 561 | $idSubtableAsInt = self::getExtractIdSubtableFromBlobNameSql($chunk, $name); |
| 562 | |
| 563 | $orderBy = "ORDER BY date1 ASC, " . // ordering by date just so column order in tests will be predictable |
| 564 | " $idSubtableAsInt ASC, |
| 565 | ts_archived DESC"; // ascending order so we use the latest data found |
| 566 | } |
| 567 | } else { |
| 568 | if ($idSubtable === null) { |
| 569 | // select root table or specific record names |
| 570 | $bind = array_values($recordNames); |
| 571 | } else { |
| 572 | // select a subtable id |
| 573 | $bind = array(); |
| 574 | foreach ($recordNames as $recordName) { |
| 575 | // to be backwards compatible we need to look for the exact idSubtable blob and for the chunk |
| 576 | // that stores the subtables (a chunk stores many blobs in one blob) |
| 577 | $bind[] = $chunk->getRecordNameForTableId($recordName, $idSubtable); |
| 578 | $bind[] = self::appendIdSubtable($recordName, $idSubtable); |
| 579 | } |
| 580 | } |
| 581 | |
| 582 | $inNames = Common::getSqlStringFieldsArray($bind); |
| 583 | $whereNameIs = "name IN ($inNames)"; |
| 584 | } |
| 585 | |
| 586 | $getValuesSql = "SELECT value, name, idsite, date1, date2, ts_archived |
| 587 | FROM %s |
| 588 | WHERE idarchive IN (%s) |
| 589 | AND " . $whereNameIs . " |
| 590 | $orderBy"; // ascending order so we use the latest data found |
| 591 | |
| 592 | return [$getValuesSql, $bind]; |
| 593 | } |
| 594 | |
| 595 | private static function getArchiveIdsByYearMonth(array $archiveIds) |
| 596 | { |
| 597 | // We want to fetch as many archives at once as possible instead of fetching each period individually |
| 598 | // eg instead of issueing one query per day we'll merge all the IDs of a given month into one query |
| 599 | // we group by YYYY-MM as we have one archive table per month |
| 600 | $archiveIdsPerMonth = []; |
| 601 | foreach ($archiveIds as $period => $ids) { |
| 602 | $yearMonth = substr($period, 0, 7); // eg 2022-11 |
| 603 | if (empty($archiveIdsPerMonth[$yearMonth])) { |
| 604 | $archiveIdsPerMonth[$yearMonth] = []; |
| 605 | } |
| 606 | $archiveIdsPerMonth[$yearMonth] = array_merge($archiveIdsPerMonth[$yearMonth], $ids); |
| 607 | } |
| 608 | return $archiveIdsPerMonth; |
| 609 | } |
| 610 | |
| 611 | // public for tests |
| 612 | public static function getExtractIdSubtableFromBlobNameSql(Chunk $chunk, $name) |
| 613 | { |
| 614 | // select blobs w/ name like "$name_[0-9]+" w/o using RLIKE |
| 615 | $nameEnd = strlen($name) + 1; |
| 616 | $nameEndAfterUnderscore = $nameEnd + 1; |
| 617 | $appendix = $chunk->getAppendix(); |
| 618 | $lenAppendix = strlen($appendix); |
| 619 | $chunkEnd = $nameEnd + $lenAppendix; |
| 620 | |
| 621 | $checkForChunkBlob = "SUBSTRING(name, $nameEnd, $lenAppendix) = '$appendix'"; |
| 622 | |
| 623 | $extractSuffix = "SUBSTRING(name, IF($checkForChunkBlob, $chunkEnd, $nameEndAfterUnderscore))"; |
| 624 | $locateSecondUnderscore = "IF((@secondunderscore := LOCATE('_', $extractSuffix) - 1) < 0, LENGTH(name), @secondunderscore)"; |
| 625 | $extractIdSubtableStart = "IF( (@idsubtable := SUBSTRING($extractSuffix, 1, $locateSecondUnderscore)) = '', -1, @idsubtable )"; |
| 626 | $idSubtableAsInt = "CAST($extractIdSubtableStart AS SIGNED)"; |
| 627 | |
| 628 | return $idSubtableAsInt; |
| 629 | } |
| 630 | } |
| 631 |