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