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);
});
}
}