/**
* Purges old data from the following tables:
* - log_visit
* - log_link_visit_action
* - log_conversion
* - log_conversion_item
* - log_action
*/
public function purgeData()
{
$maxIdVisit = $this->getDeleteIdVisitOffset();
// break if no ID was found (nothing to delete for given period)
if (empty($maxIdVisit)) {
return;
}
$logTables = self::getDeleteTableLogTables();
// delete data from log tables
$where = "WHERE idvisit <= ?";
foreach ($logTables as $logTable) {
// deleting from log_action must be handled differently, so we do it later
if ($logTable != Common::prefixTable('log_action')) {
Db::deleteAllRows($logTable, $where, "idvisit ASC", $this->maxRowsToDeletePerQuery, array($maxIdVisit));
}
}
// delete unused actions from the log_action table (but only if we can lock tables)
if (Db::isLockPrivilegeGranted()) {
$this->purgeUnusedLogActions();
} else {
$logMessage = get_class($this) . ": LOCK TABLES privilege not granted; skipping unused actions purge";
Log::warning($logMessage);
}
// optimize table overhead after deletion
Db::optimizeTables($logTables);
}
/**
* @group Core
*/
public function testOptimize()
{
// make sure optimizing myisam tables works
$this->assertTrue(Db::optimizeTables(array('table1', 'table2')) !== false);
// make sure optimizing both myisam & innodb results in optimizations
$this->assertTrue(Db::optimizeTables(array('table1', 'table2', 'table3', 'table4')) !== false);
// make sure innodb tables are skipped
$this->assertTrue(Db::optimizeTables(array('table3', 'table4')) === false);
}
private function optimizeTable(OutputInterface $output, $dryRun, $table)
{
$output->write("Optimizing table '{$table}'...");
if ($dryRun) {
$output->write("[dry-run, not optimising table]");
} else {
Db::optimizeTables(Common::prefixTable($table), $force = true);
}
$output->writeln("Done.");
}
function delete()
{
$this->log("Deleting logs for today...");
$db = \Zend_Registry::get('db');
$sql = "DELETE FROM " . Common::prefixTable('log_visit') . "\n\t\t\t\tWHERE date(visit_last_action_time) = CURRENT_DATE();";
$db->query($sql);
foreach (array('log_link_visit_action', 'log_conversion', 'log_conversion_item') as $table) {
$sql = "DELETE FROM " . Common::prefixTable($table) . "\n\t\t \t\tWHERE date(server_time) = CURRENT_DATE();";
$db->query($sql);
}
$tablesToOptimize = array(Common::prefixTable('log_link_visit_action'), Common::prefixTable('log_conversion'), Common::prefixTable('log_conversion_item'), Common::prefixTable('log_visit'));
\Piwik\Db::optimizeTables($tablesToOptimize);
$this->log("done");
}
/**
* Purges old data from the following tables:
* - log_visit
* - log_link_visit_action
* - log_conversion
* - log_conversion_item
* - log_action
*
* @param int $deleteLogsOlderThan The number of days after which log entires are considered old.
* Visits and related data whose age is greater than this number
* will be purged.
*/
public function purgeData($deleteLogsOlderThan)
{
$dateUpperLimit = Date::factory("today")->subDay($deleteLogsOlderThan);
$this->logDeleter->deleteVisitsFor($start = null, $dateUpperLimit->getDatetime());
$logTables = self::getDeleteTableLogTables();
// delete unused actions from the log_action table (but only if we can lock tables)
if (Db::isLockPrivilegeGranted()) {
$this->rawLogDao->deleteUnusedLogActions();
} else {
$logMessage = get_class($this) . ": LOCK TABLES privilege not granted; skipping unused actions purge";
Log::warning($logMessage);
}
// optimize table overhead after deletion
Db::optimizeTables($logTables);
}
/**
* Purges old report/metric data.
*
* If $keepBasicMetrics is false, old numeric tables will be dropped, otherwise only
* the metrics not in $metricsToKeep will be deleted.
*
* If $reportPeriodsToKeep is an empty array, old blob tables will be dropped. Otherwise,
* specific reports will be deleted, except reports for periods in $reportPeriodsToKeep.
*
* @param bool $optimize If tables should be optimized after rows are deleted. Normally,
* this is handled by a scheduled task.
*/
public function purgeData($optimize = false)
{
// find archive tables to purge
list($oldNumericTables, $oldBlobTables) = $this->getArchiveTablesToPurge();
// process blob tables first, since archive status is stored in the numeric archives
if (!empty($oldBlobTables)) {
// if no reports should be kept, drop tables, otherwise drop individual reports
if (empty($this->reportPeriodsToKeep) && !$this->keepSegmentReports) {
Db::dropTables($oldBlobTables);
} else {
foreach ($oldBlobTables as $table) {
$where = $this->getBlobTableWhereExpr($oldNumericTables, $table);
if (!empty($where)) {
$where = "WHERE {$where}";
}
Db::deleteAllRows($table, $where, "idarchive ASC", $this->maxRowsToDeletePerQuery);
}
if ($optimize) {
Db::optimizeTables($oldBlobTables);
}
}
}
// deal with numeric tables
if (!empty($oldNumericTables)) {
// if keep_basic_metrics is set, empty all numeric tables of metrics to purge
if ($this->keepBasicMetrics == 1 && !empty($this->metricsToKeep)) {
$where = "WHERE name NOT IN ('" . implode("','", $this->metricsToKeep) . "') AND name NOT LIKE 'done%'";
foreach ($oldNumericTables as $table) {
Db::deleteAllRows($table, $where, "idarchive ASC", $this->maxRowsToDeletePerQuery);
}
if ($optimize) {
Db::optimizeTables($oldNumericTables);
}
} else {
Db::dropTables($oldNumericTables);
}
}
}
public function optimizeArchiveTable()
{
$archiveTables = ArchiveTableCreator::getTablesArchivesInstalled();
Db::optimizeTables($archiveTables);
}
private function optimizeTables(OutputInterface $output)
{
foreach (self::$logTables as $table) {
$output->write("Optimizing table {$table}... ");
$timer = new Timer();
$prefixedTable = Common::prefixTable($table);
$done = Db::optimizeTables($prefixedTable);
if ($done) {
$output->writeln("done. <comment>" . $timer . "</comment>");
} else {
$output->writeln("skipped! <comment>" . $timer . "</comment>");
}
}
$this->writeSuccessMessage($output, array("Table optimization finished."));
}
/**
* Purges old report/metric data.
*
* If $keepBasicMetrics is false, old numeric tables will be dropped, otherwise only
* the metrics not in $metricsToKeep will be deleted.
*
* If $reportPeriodsToKeep is an empty array, old blob tables will be dropped. Otherwise,
* specific reports will be deleted, except reports for periods in $reportPeriodsToKeep.
*
* @param bool $optimize If tables should be optimized after rows are deleted. Normally,
* this is handled by a scheduled task.
*/
public function purgeData($optimize = false)
{
list($oldNumericTables, $oldBlobTables) = $this->getArchiveTablesToPurge();
// process blob tables first, since archive status is stored in the numeric archives
if (!empty($oldBlobTables)) {
foreach ($oldBlobTables as $table) {
$where = $this->getBlobTableWhereExpr($oldNumericTables, $table);
if (!empty($where)) {
$where = "WHERE {$where}";
}
Db::deleteAllRows($table, $where, "idarchive ASC", $this->maxRowsToDeletePerQuery);
}
if ($optimize) {
Db::optimizeTables($oldBlobTables);
}
}
$this->segmentArchiveIds = null;
if (!empty($oldNumericTables)) {
foreach ($oldNumericTables as $table) {
$conditions = array("name NOT LIKE 'done%'");
$bind = array();
if ($this->keepBasicMetrics && !empty($this->metricsToKeep)) {
$metricFields = Common::getSqlStringFieldsArray($this->metricsToKeep);
$bind = $this->metricsToKeep;
$conditions[] = sprintf("name NOT IN (%s)", $metricFields);
}
$keepWhere = $this->getBlobTableWhereExpr($oldNumericTables, $table);
if (!empty($keepWhere)) {
$conditions[] = $keepWhere;
}
$where = 'WHERE ' . implode(' AND ', $conditions);
Db::deleteAllRows($table, $where, "idarchive ASC", $this->maxRowsToDeletePerQuery, $bind);
}
if ($optimize) {
Db::optimizeTables($oldNumericTables);
}
}
}
/**
* @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);
});
}
}
