protected static function beforeTableDataCached() { $date = Date::factory('2010-03-01'); $archiveTableCreator = new ArchiveTableCreator(); $archiveTableCreator->getBlobTable($date); $archiveTableCreator->getNumericTable($date); }
public static function setUpBeforeClass() { parent::setUpBeforeClass(); ArchiveTableCreator::getNumericTable(Date::factory('2015-01-01')); ArchiveTableCreator::getNumericTable(Date::factory('2015-02-02')); ArchiveTableCreator::getNumericTable(Date::factory('2015-03-03')); }
public function setUp() { parent::setUp(); $this->archiveTableDao = self::$fixture->piwikEnvironment->getContainer()->get('Piwik\\DataAccess\\ArchiveTableDao'); ArchiveTableCreator::getBlobTable(Date::factory('2015-01-01')); ArchiveTableCreator::getNumericTable(Date::factory('2015-01-01')); }
protected static function deleteArchiveIds(Date $date, $idArchivesToDelete) { $batches = array_chunk($idArchivesToDelete, 1000); foreach ($batches as $idsToDelete) { $query = "DELETE FROM %s WHERE idarchive IN (" . implode(',', $idsToDelete) . ")"; Db::query(sprintf($query, ArchiveTableCreator::getNumericTable($date))); try { Db::query(sprintf($query, ArchiveTableCreator::getBlobTable($date))); } catch (Exception $e) { // Individual blob tables could be missing } } }
public function assertArchivesExist($expectedPresentArchiveIds, $archiveDate) { $numericTable = ArchiveTableCreator::getNumericTable($archiveDate); $blobTable = ArchiveTableCreator::getBlobTable($archiveDate); $numericArchiveCount = $this->getArchiveRowCountWithId($numericTable, $expectedPresentArchiveIds); $expectedNumericRowCount = count($expectedPresentArchiveIds) * 3; // two metrics + 1 done row $this->assertEquals($expectedNumericRowCount, $numericArchiveCount); $blobArchiveCount = $this->getArchiveRowCountWithId($blobTable, $expectedPresentArchiveIds); $expectedBlobRowCount = count($expectedPresentArchiveIds) * 2; // two blob rows $this->assertEquals($expectedBlobRowCount, $blobArchiveCount); }
/** * Queries and returns archive data using a set of archive IDs. * * @param array $archiveIds The IDs of the archives to get data from. * @param array $recordNames The names of the data to retrieve (ie, nb_visits, nb_actions, etc.) * @param string $archiveDataType The archive data type (either, 'blob' or 'numeric'). * @param bool $loadAllSubtables Whether to pre-load all subtables * @throws Exception * @return array */ public static function getArchiveData($archiveIds, $recordNames, $archiveDataType, $loadAllSubtables) { // create the SQL to select archive data $inNames = Common::getSqlStringFieldsArray($recordNames); if ($loadAllSubtables) { $name = reset($recordNames); // select blobs w/ name like "$name_[0-9]+" w/o using RLIKE $nameEnd = strlen($name) + 2; $whereNameIs = "(name = ?\n OR (name LIKE ?\n AND SUBSTRING(name, {$nameEnd}, 1) >= '0'\n AND SUBSTRING(name, {$nameEnd}, 1) <= '9') )"; $bind = array($name, $name . '%'); } else { $whereNameIs = "name IN ({$inNames})"; $bind = array_values($recordNames); } $getValuesSql = "SELECT value, name, idsite, date1, date2, ts_archived\n FROM %s\n WHERE idarchive IN (%s)\n AND " . $whereNameIs; // get data from every table we're querying $rows = array(); foreach ($archiveIds as $period => $ids) { if (empty($ids)) { throw new Exception("Unexpected: id archive not found for period '{$period}' '"); } // $period = "2009-01-04,2009-01-04", $date = Date::factory(substr($period, 0, 10)); if ($archiveDataType == 'numeric') { $table = ArchiveTableCreator::getNumericTable($date); } else { $table = ArchiveTableCreator::getBlobTable($date); } $sql = sprintf($getValuesSql, $table, implode(',', $ids)); $dataRows = Db::fetchAll($sql, $bind); foreach ($dataRows as $row) { $rows[] = $row; } } return $rows; }
/** * Deletes by batches Archive IDs in the specified month, * * @param Date $date * @param $idArchivesToDelete * @return int Number of rows deleted from both numeric + blob table. */ protected function deleteArchiveIds(Date $date, $idArchivesToDelete) { $batches = array_chunk($idArchivesToDelete, 1000); $numericTable = ArchiveTableCreator::getNumericTable($date); $blobTable = ArchiveTableCreator::getBlobTable($date); $deletedCount = 0; foreach ($batches as $idsToDelete) { $deletedCount += $this->model->deleteArchiveIds($numericTable, $blobTable, $idsToDelete); } return $deletedCount; }
private function insertArchiveRow($idSite, $date, $periodLabel) { $periodObject = \Piwik\Period\Factory::build($periodLabel, $date); $dateStart = $periodObject->getDateStart(); $dateEnd = $periodObject->getDateEnd(); $table = ArchiveTableCreator::getNumericTable($dateStart); $idArchive = (int) Db::fetchOne("SELECT MAX(idarchive) FROM {$table} WHERE name LIKE 'done%'"); $idArchive = $idArchive + 1; $periodId = Piwik::$idPeriods[$periodLabel]; $doneFlag = 'done'; if ($idArchive % 5 == 1) { $doneFlag = Rules::getDoneFlagArchiveContainsAllPlugins(self::$segment1); } else { if ($idArchive % 5 == 2) { $doneFlag .= '.VisitsSummary'; } else { if ($idArchive % 5 == 3) { $doneFlag = Rules::getDoneFlagArchiveContainsOnePlugin(self::$segment1, 'UserCountry'); } else { if ($idArchive % 5 == 4) { $doneFlag = Rules::getDoneFlagArchiveContainsAllPlugins(self::$segment2); } } } } $sql = "INSERT INTO {$table} (idarchive, name, idsite, date1, date2, period, ts_archived)\n VALUES ({$idArchive}, 'nb_visits', {$idSite}, '{$dateStart}', '{$dateEnd}', {$periodId}, NOW()),\n ({$idArchive}, '{$doneFlag}', {$idSite}, '{$dateStart}', '{$dateEnd}', {$periodId}, NOW())"; Db::query($sql); }
protected function getTableNumeric() { return ArchiveTableCreator::getNumericTable($this->dateStart); }
protected static function getTemporaryArchiveIdsOlderThan(Date $date, $purgeArchivesOlderThan) { $query = "SELECT idarchive\n FROM " . ArchiveTableCreator::getNumericTable($date) . "\n WHERE name LIKE 'done%'\n AND (( value = " . ArchiveWriter::DONE_OK_TEMPORARY . "\n AND ts_archived < ?)\n OR value = " . ArchiveWriter::DONE_ERROR . ")"; $result = Db::fetchAll($query, array($purgeArchivesOlderThan)); $idArchivesToDelete = array(); if (!empty($result)) { foreach ($result as $row) { $idArchivesToDelete[] = $row['idarchive']; } } return $idArchivesToDelete; }
/** * Queries and returns archive data using a set of archive IDs. * * @param array $archiveIds The IDs of the archives to get data from. * @param array $recordNames The names of the data to retrieve (ie, nb_visits, nb_actions, etc.) * @param string $archiveDataType The archive data type (either, 'blob' or 'numeric'). * @param int|null|string $idSubtable null if the root blob should be loaded, an integer if a subtable should be * loaded and 'all' if all subtables should be loaded. * @throws Exception * @return array */ public function getArchiveData($archiveIds, $recordNames, $archiveDataType, $idSubtable) { $chunk = new Chunk(); // create the SQL to select archive data $loadAllSubtables = $idSubtable == Archive::ID_SUBTABLE_LOAD_ALL_SUBTABLES; if ($loadAllSubtables) { $name = reset($recordNames); // select blobs w/ name like "$name_[0-9]+" w/o using RLIKE $nameEnd = strlen($name) + 1; $nameEndAppendix = $nameEnd + 1; $appendix = $chunk->getAppendix(); $lenAppendix = strlen($appendix); $checkForChunkBlob = "SUBSTRING(name FROM {$nameEnd} FOR {$lenAppendix}) = '{$appendix}'"; $checkForSubtableId = "(SUBSTRING(name FROM {$nameEndAppendix} FOR 1) >= '0'\n AND SUBSTRING(name FROM {$nameEndAppendix} FOR 1) <= '9')"; $whereNameIs = "(name = ? OR (name LIKE ? AND ( {$checkForChunkBlob} OR {$checkForSubtableId} ) ))"; $bind = array($name, $name . '%'); } else { if ($idSubtable === null) { // select root table or specific record names $bind = array_values($recordNames); } else { // select a subtable id $bind = array(); foreach ($recordNames as $recordName) { // to be backwards compatibe we need to look for the exact idSubtable blob and for the chunk // that stores the subtables (a chunk stores many blobs in one blob) $bind[] = $chunk->getRecordNameForTableId($recordName, $idSubtable); $bind[] = ArchiveSelector::appendIdSubtable($recordName, $idSubtable); } } $inNames = Common::getSqlStringFieldsArray($bind); $whereNameIs = "name IN ({$inNames})"; } $getValuesSql = "SELECT %s, name, idsite, date1, date2, ts_archived\n FROM %s\n WHERE idarchive IN (%s)\n AND " . $whereNameIs; // get data from every table we're querying $rows = array(); foreach ($archiveIds as $period => $ids) { if (empty($ids)) { throw new Exception("Unexpected: id archive not found for period '{$period}' '"); } // $period = "2009-01-04,2009-01-04", $date = Date::factory(substr($period, 0, 10)); $isNumeric = $archiveDataType == 'numeric'; if ($isNumeric) { $table = ArchiveTableCreator::getNumericTable($date); } else { $table = ArchiveTableCreator::getBlobTable($date); } $valueCol = $this->prepareForBinary($table); $sql = sprintf($getValuesSql, $valueCol, $table, implode(',', $ids)); $dataRows = $this->db->fetchAll($sql, $bind); $dataRows = $this->binaryOutput($dataRows, true); foreach ($dataRows as $row) { if ($isNumeric) { $rows[] = $row; } else { $row['value'] = $this->uncompress($row['value']); if ($chunk->isRecordNameAChunk($row['name'])) { $this->moveChunkRowToRows($rows, $row, $chunk, $loadAllSubtables, $idSubtable); } else { $rows[] = $row; } } } } return $rows; }
public function deleteByPeriodRange(Date $date) { $query = "DELETE FROM %s WHERE period = ? AND ts_archived < ?"; $yesterday = Date::factory('yesterday')->getDateTime(); $bind = array(Piwik::$idPeriods['range'], $yesterday); $numericTable = ArchiveTableCreator::getNumericTable($date); $this->db->query(sprintf($query, $numericTable), $bind); Log::debug("Purging Custom Range archives: done [ purged archives older than {$yesterday} from {$numericTable} / blob ]"); try { $this->db->query(sprintf($query, ArchiveTableCreator::getBlobTable($date)), $bind); } catch (Exception $e) { // Individual blob tables could be missing } }
protected static function deleteArchiveIds(Date $date, $idArchivesToDelete) { $batches = array_chunk($idArchivesToDelete, 1000); $numericTable = ArchiveTableCreator::getNumericTable($date); $blobTable = ArchiveTableCreator::getBlobTable($date); foreach ($batches as $idsToDelete) { self::getModel()->deleteArchiveIds($numericTable, $blobTable, $idsToDelete); } }
/** * Queries and returns archive IDs for a set of sites, periods, and a segment. * * @param array $siteIds * @param array $periods * @param Segment $segment * @param array $plugins List of plugin names for which data is being requested. * @return array Archive IDs are grouped by archive name and period range, ie, * array( * 'VisitsSummary.done' => array( * '2010-01-01' => array(1,2,3) * ) * ) * @throws */ public static function getArchiveIds($siteIds, $periods, $segment, $plugins) { if (empty($siteIds)) { throw new \Exception("Website IDs could not be read from the request, ie. idSite="); } foreach ($siteIds as $index => $siteId) { $siteIds[$index] = (int) $siteId; } $monthToPeriods = array(); foreach ($periods as $period) { /** @var Period $period */ $table = ArchiveTableCreator::getNumericTable($period->getDateStart()); $monthToPeriods[$table][] = $period; } $nameCondition = self::getNameCondition($plugins, $segment); $Archive = Factory::getDAO('archive'); $results = $Archive->getArchiveIds($siteIds, $monthToPeriods, $nameCondition); return $results; }
/** * @param OutputInterface $output * @param Date[] $dates * @param bool $forceOptimzation */ private function optimizeArchiveTables(OutputInterface $output, $dates, $forceOptimzation = false) { $output->writeln("Optimizing archive tables..."); foreach ($dates as $date) { $numericTable = ArchiveTableCreator::getNumericTable($date); $this->performTimedPurging($output, "Optimizing table {$numericTable}...", function () use($numericTable, $forceOptimzation) { Db::optimizeTables($numericTable, $forceOptimzation); }); $blobTable = ArchiveTableCreator::getBlobTable($date); $this->performTimedPurging($output, "Optimizing table {$blobTable}...", function () use($blobTable, $forceOptimzation) { Db::optimizeTables($blobTable, $forceOptimzation); }); } }