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 beforeTableDataCached()
{
$date = Date::factory('2010-03-01');
$archiveTableCreator = new ArchiveTableCreator();
$archiveTableCreator->getBlobTable($date);
$archiveTableCreator->getNumericTable($date);
}
public function test_UpdateCommand_ReturnsCorrectExitCode_WhenErrorOccurs()
{
// create a blob table, then drop it manually so update 2.10.0-b10 will fail
$tableName = ArchiveTableCreator::getBlobTable(Date::factory('2015-01-01'));
Db::exec("DROP TABLE {$tableName}");
$result = $this->applicationTester->run(array('command' => 'core:update', '--yes' => true));
$this->assertEquals(1, $result, $this->getCommandDisplayOutputErrorMessage());
$this->assertContains("Piwik could not be updated! See above for more information.", $this->applicationTester->getDisplay());
}
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
}
}
}
/**
* Testing plain inserts (BLOB)
* @group Core
*/
public function testTableInsertBatchIterateBlob()
{
$dateLabel = '2011-03-31';
$table = ArchiveTableCreator::getBlobTable(Date::factory($dateLabel));
$data = $this->_getBlobDataInsert();
BatchInsert::tableInsertBatchIterate($table, array('idarchive', 'name', 'idsite', 'date1', 'date2', 'period', 'ts_archived', 'value'), $data);
$this->_checkTableIsExpectedBlob($table, $data);
// If we insert AGAIN, expect to throw an error because the primary key already exist
try {
BatchInsert::tableInsertBatchIterate($table, array('idarchive', 'name', 'idsite', 'date1', 'date2', 'period', 'ts_archived', 'value'), $data, $ignoreWhenDuplicate = false);
} catch (Exception $e) {
// However if we insert with keyword REPLACE, then the new data should be saved
BatchInsert::tableInsertBatchIterate($table, array('idarchive', 'name', 'idsite', 'date1', 'date2', 'period', 'ts_archived', 'value'), $data, $ignoreWhenDuplicate = true);
$this->_checkTableIsExpectedBlob($table, $data);
return;
}
$this->fail('Exception expected');
}
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);
}
private function getAllRowsFromArchiveBlobTable()
{
$table = ArchiveTableCreator::getBlobTable(Date::factory($this->date));
$rows = Db::fetchAll("SELECT * FROM " . $table);
return $rows;
}
/**
* 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;
}
protected function getTableNameToInsert($value)
{
if (is_numeric($value)) {
return $this->getTableNumeric();
}
return ArchiveTableCreator::getBlobTable($this->dateStart);
}
/**
* 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);
}
}
/**
* @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);
});
}
}
