diff options
| -rw-r--r-- | core/Archive/ArchivePurger.php | 73 | ||||
| -rw-r--r-- | core/DataAccess/Model.php | 69 | ||||
| -rw-r--r-- | core/Segment.php | 10 | ||||
| -rw-r--r-- | plugins/CoreAdminHome/Tasks.php | 51 | ||||
| -rw-r--r-- | plugins/CoreAdminHome/tests/Integration/TasksTest.php | 101 | ||||
| -rw-r--r-- | tests/PHPUnit/Fixtures/RawArchiveDataWithTempAndInvalidated.php | 124 | ||||
| -rw-r--r-- | tests/PHPUnit/Integration/Archive/ArchivePurgerTest.php | 30 |
7 files changed, 450 insertions, 8 deletions
diff --git a/core/Archive/ArchivePurger.php b/core/Archive/ArchivePurger.php index 078203cb8f..c97a893f83 100644 --- a/core/Archive/ArchivePurger.php +++ b/core/Archive/ArchivePurger.php @@ -9,11 +9,13 @@ namespace Piwik\Archive; use Piwik\ArchiveProcessor\Rules; +use Piwik\Common; use Piwik\Config; use Piwik\Container\StaticContainer; use Piwik\DataAccess\ArchiveTableCreator; use Piwik\DataAccess\Model; use Piwik\Date; +use Piwik\Db; use Piwik\Piwik; use Psr\Log\LoggerInterface; use Psr\Log\LogLevel; @@ -154,6 +156,77 @@ class ArchivePurger return $deletedRowCount; } + public function purgeDeletedSiteArchives(Date $dateStart) + { + $idArchivesToDelete = $this->getDeletedSiteArchiveIds($dateStart); + + return $this->purge($idArchivesToDelete, $dateStart, 'deleted sites'); + } + + /** + * @param Date $dateStart + * @param array $segmentHashesByIdSite List of valid segment hashes, indexed by site ID + * @return int + */ + public function purgeDeletedSegmentArchives(Date $dateStart, array $segmentHashesByIdSite) + { + $idArchivesToDelete = $this->getDeletedSegmentArchiveIds($dateStart, $segmentHashesByIdSite); + + return $this->purge($idArchivesToDelete, $dateStart, 'deleted segments'); + } + + /** + * Purge all numeric and blob archives with the given IDs from the database. + * @param array $idArchivesToDelete + * @param Date $dateStart + * @param string $reason + * @return int + */ + protected function purge(array $idArchivesToDelete, Date $dateStart, $reason) + { + $deletedRowCount = 0; + if (!empty($idArchivesToDelete)) { + $deletedRowCount = $this->deleteArchiveIds($dateStart, $idArchivesToDelete); + + $this->logger->info( + "Deleted {count} rows in archive tables (numeric + blob) for {reason} for {date}.", + array( + 'count' => $deletedRowCount, + 'date' => $dateStart, + 'reason' => $reason + ) + ); + + $this->logger->debug("[Deleted IDs: {deletedIds}]", array( + 'deletedIds' => implode(',', $idArchivesToDelete) + )); + } else { + $this->logger->debug( + "No archives for {reason} found in archive numeric table for {date}.", + array('date' => $dateStart, 'reason' => $reason) + ); + } + + return $deletedRowCount; + } + + protected function getDeletedSiteArchiveIds(Date $date) + { + $archiveTable = ArchiveTableCreator::getNumericTable($date); + return $this->model->getArchiveIdsForDeletedSites( + $archiveTable, + $this->getOldestTemporaryArchiveToKeepThreshold() + ); + } + + protected function getDeletedSegmentArchiveIds(Date $date, array $segmentHashesByIdSite) + { + $archiveTable = ArchiveTableCreator::getNumericTable($date); + return $this->model->getArchiveIdsForDeletedSegments( + $archiveTable, $segmentHashesByIdSite, $this->getOldestTemporaryArchiveToKeepThreshold() + ); + } + protected function getOutdatedArchiveIds(Date $date, $purgeArchivesOlderThan) { $archiveTable = ArchiveTableCreator::getNumericTable($date); diff --git a/core/DataAccess/Model.php b/core/DataAccess/Model.php index d5da1e33ed..fe8c26d598 100644 --- a/core/DataAccess/Model.php +++ b/core/DataAccess/Model.php @@ -334,6 +334,75 @@ class Model } /** + * Get a list of IDs of archives that don't have any matching rows in the site table. Excludes temporary archives + * that may still be in use, as specified by the $oldestToKeep passed in. + * @param string $archiveTableName + * @param string $oldestToKeep Datetime string + * @return array of IDs + */ + public function getArchiveIdsForDeletedSites($archiveTableName, $oldestToKeep) + { + $sql = "SELECT DISTINCT idarchive FROM " . $archiveTableName . " a " + . " LEFT JOIN " . Common::prefixTable('site') . " s USING (idsite)" + . " WHERE s.idsite IS NULL" + . " AND ts_archived < ?"; + + $rows = Db::fetchAll($sql, array($oldestToKeep)); + + return array_column($rows, 'idarchive'); + } + + /** + * Get a list of IDs of archives with segments that no longer exist in the DB. Excludes temporary archives that + * may still be in use, as specified by the $oldestToKeep passed in. + * @param string $archiveTableName + * @param array $segmentHashesById Whitelist of existing segments, indexed by site ID + * @param string $oldestToKeep Datetime string + * @return array With keys idarchive, name, idsite + */ + public function getArchiveIdsForDeletedSegments($archiveTableName, array $segmentHashesById, $oldestToKeep) + { + $validSegmentClauses = []; + + foreach ($segmentHashesById as $idSite => $segments) { + // segments are md5 hashes and such not a problem re sql injection. for performance etc we don't want to use + // bound parameters for the query + foreach ($segments as $segment) { + if (!ctype_xdigit($segment)) { + throw new Exception($segment . ' expected to be an md5 hash'); + } + } + + // Special case as idsite=0 means the segments are not site-specific + if ($idSite === 0) { + foreach ($segments as $segmentHash) { + $validSegmentClauses[] = '(name LIKE "done' . $segmentHash . '%")'; + } + continue; + } + + $idSite = (int)$idSite; + + // Vanilla case - segments that are valid for a single site only + $sql = '(idsite = ' . $idSite . ' AND ('; + $sql .= 'name LIKE "done' . implode('%" OR name LIKE "done', $segments) . '%"'; + $sql .= '))'; + $validSegmentClauses[] = $sql; + } + + $isValidSegmentSql = implode(' OR ', $validSegmentClauses); + + $sql = 'SELECT idarchive FROM ' . $archiveTableName + . ' WHERE name LIKE "done%" AND name != "done"' + . ' AND ts_archived < ?' + . ' AND NOT (' . $isValidSegmentSql . ')'; + + $rows = Db::fetchAll($sql, array($oldestToKeep)); + + return array_map(function($row) { return $row['idarchive']; }, $rows); + } + + /** * Returns the SQL condition used to find successfully completed archives that * this instance is querying for. */ diff --git a/core/Segment.php b/core/Segment.php index 8fcef9c932..456172e5d0 100644 --- a/core/Segment.php +++ b/core/Segment.php @@ -307,9 +307,13 @@ class Segment if (empty($this->string)) { return ''; } - // normalize the string as browsers may send slightly different payloads for the same archive - $normalizedSegmentString = urldecode($this->string); - return md5($normalizedSegmentString); + return self::getSegmentHash($this->string); + } + + public static function getSegmentHash($definition) + { + // urldecode to normalize the string, as browsers may send slightly different payloads for the same archive + return md5(urldecode($definition)); } /** diff --git a/plugins/CoreAdminHome/Tasks.php b/plugins/CoreAdminHome/Tasks.php index 5d8dadb3a5..136dfdf7e0 100644 --- a/plugins/CoreAdminHome/Tasks.php +++ b/plugins/CoreAdminHome/Tasks.php @@ -11,6 +11,7 @@ namespace Piwik\Plugins\CoreAdminHome; use Piwik\API\Request; use Piwik\ArchiveProcessor\Rules; use Piwik\Archive\ArchivePurger; +use Piwik\Common; use Piwik\Config; use Piwik\Container\StaticContainer; use Piwik\DataAccess\ArchiveTableCreator; @@ -24,6 +25,7 @@ use Piwik\Plugins\CoreAdminHome\Emails\TrackingFailuresEmail; use Piwik\Plugins\CoreAdminHome\Tasks\ArchivesToPurgeDistributedList; use Piwik\Plugins\SitesManager\SitesManager; use Piwik\Scheduler\Schedule\SpecificTime; +use Piwik\Segment; use Piwik\Settings\Storage\Backend\MeasurableSettingsTable; use Piwik\Tracker\Failures; use Piwik\Site; @@ -64,6 +66,8 @@ class Tasks extends \Piwik\Plugin\Tasks // general data purge on invalidated archive records, executed daily $this->daily('purgeInvalidatedArchives', null, self::LOW_PRIORITY); + $this->weekly('purgeOrphanedArchives', null, self::NORMAL_PRIORITY); + // lowest priority since tables should be optimized after they are modified $this->monthly('optimizeArchiveTable', null, self::LOWEST_PRIORITY); @@ -259,6 +263,53 @@ class Tasks extends \Piwik\Plugin\Tasks } /** + * To test execute the following command: + * `./console core:run-scheduled-tasks "Piwik\Plugins\CoreAdminHome\Tasks.purgeOrphanedArchives"` + * + * @throws \Exception + */ + public function purgeOrphanedArchives() + { + $segmentHashesByIdSite = $this->getSegmentHashesByIdSite(); + $archiveTables = ArchiveTableCreator::getTablesArchivesInstalled('numeric'); + + $datesPurged = array(); + foreach ($archiveTables as $table) { + $date = ArchiveTableCreator::getDateFromTableName($table); + list($year, $month) = explode('_', $date); + + $dateObj = Date::factory("$year-$month-15"); + + $this->archivePurger->purgeDeletedSiteArchives($dateObj); + $this->archivePurger->purgeDeletedSegmentArchives($dateObj, $segmentHashesByIdSite); + + $datesPurged[$date] = true; + } + } + + /** + * Get a list of all segment hashes that currently exist, indexed by idSite. + * @return array + */ + public function getSegmentHashesByIdSite() + { + //Get a list of hashes of all segments that exist now + $sql = "SELECT DISTINCT definition, enable_only_idsite FROM " . Common::prefixTable('segment') + . " WHERE deleted = 0"; + $rows = Db::fetchAll($sql); + $segmentHashes = array(); + foreach ($rows as $row) { + $idSite = (int)$row['enable_only_idsite']; + if (! isset($segmentHashes[$idSite])) { + $segmentHashes[$idSite] = array(); + } + $segmentHashes[$idSite][] = Segment::getSegmentHash($row['definition']); + } + + return $segmentHashes; + } + + /** * we should only purge outdated & custom range archives if we know cron archiving has just run, * or if browser triggered archiving is enabled. if cron archiving has run, then we know the latest * archives are in the database, and we can remove temporary ones. if browser triggered archiving is diff --git a/plugins/CoreAdminHome/tests/Integration/TasksTest.php b/plugins/CoreAdminHome/tests/Integration/TasksTest.php index ffab94c01d..446bf66b86 100644 --- a/plugins/CoreAdminHome/tests/Integration/TasksTest.php +++ b/plugins/CoreAdminHome/tests/Integration/TasksTest.php @@ -17,6 +17,9 @@ use Piwik\Plugins\CoreAdminHome\Emails\JsTrackingCodeMissingEmail; use Piwik\Plugins\CoreAdminHome\Emails\TrackingFailuresEmail; use Piwik\Plugins\CoreAdminHome\Tasks; use Piwik\Plugins\CoreAdminHome\Tasks\ArchivesToPurgeDistributedList; +use Piwik\Plugins\CustomDimensions\CustomDimensions; +use Piwik\Plugins\CustomDimensions\Dao\Configuration; +use Piwik\Plugins\SegmentEditor\Model; use Piwik\Scheduler\Task; use Piwik\Tests\Fixtures\RawArchiveDataWithTempAndInvalidated; use Piwik\Tests\Framework\Fixture; @@ -131,6 +134,7 @@ class TasksTest extends IntegrationTestCase $expected = [ 'purgeOutdatedArchives.', 'purgeInvalidatedArchives.', + 'purgeOrphanedArchives.', 'optimizeArchiveTable.', 'cleanupTrackingFailures.', 'notifyTrackingFailures.', @@ -215,6 +219,103 @@ class TasksTest extends IntegrationTestCase $this->assertEquals(2, $mail->getNumFailures()); } + public function test_getSegmentHashesByIdSite_emptyWhenNoSegments() + { + $segmentsByIdSite = $this->tasks->getSegmentHashesByIdSite(); + $this->assertEquals(array(), $segmentsByIdSite); + } + + public function test_getSegmentHashesByIdSite_allWebsiteAndSiteSpecificSegments() + { + $model = new Model(); + $model->createSegment(array( + 'name' => 'Test Segment 1', + 'definition' => 'continentCode==eur', + 'enable_only_idsite' => 0, + 'deleted' => 0 + )); + $model->createSegment(array( + 'name' => 'Test Segment 2', + 'definition' => 'countryCode==nz', + 'enable_only_idsite' => 0, + 'deleted' => 0 + )); + $model->createSegment(array( + 'name' => 'Test Segment 3', + 'definition' => 'countryCode==au', + 'enable_only_idsite' => 2, + 'deleted' => 0 + )); + + $segmentsByIdSite = $this->tasks->getSegmentHashesByIdSite(); + $expected = array( + 0 => array('be90051048558489e1d62f4245a6dc65', 'b92fbb3009b32cf632965802de2fb760'), + 2 => array('cffd4336c22c6782211f853495076b1a') + ); + $this->assertEquals($expected, $segmentsByIdSite); + } + + public function test_getSegmentHashesByIdSite_invalidSegment() + { + $model = new Model(); + $model->createSegment(array( + 'name' => 'Test Segment 4', + 'definition' => 'countryCode=nz', //The single "=" is invalid - we should generate a hash anyway + 'enable_only_idsite' => 0, + 'deleted' => 0 + )); + $model->createSegment(array( + 'name' => 'Test Segment 5', + 'definition' => 'countryCode==au', + 'enable_only_idsite' => 0, + 'deleted' => 0 + )); + + $expected = array( + 0 => array('5ffe7e116fae7576c047b1fb811584a5', 'cffd4336c22c6782211f853495076b1a'), + ); + + $segmentsByIdSite = $this->tasks->getSegmentHashesByIdSite(); + $this->assertEquals($expected, $segmentsByIdSite); + } + + public function test_getSegmentHashesByIdSite_siteSpecificCustomDimension() + { + // Insert a custom dimension for idsite = 1 + $configuration = new Configuration(); + $configuration->configureNewDimension( + 1, + 'mydimension', + CustomDimensions::SCOPE_VISIT, + 1, + 1, + array(), + true + ); + + $model = new Model(); + $model->createSegment(array( + 'name' => 'Test Segment 6', + 'definition' => 'mydimension==red', + 'enable_only_idsite' => 1, + 'deleted' => 0 + )); + $model->createSegment(array( + 'name' => 'Test Segment 7', + 'definition' => 'countryCode==au', + 'enable_only_idsite' => 2, + 'deleted' => 0 + )); + + $expected = array( + 1 => array('240d2a84a309debd26bdbaa8eb3d363c'), + 2 => array('cffd4336c22c6782211f853495076b1a') + ); + + $segmentsByIdSite = $this->tasks->getSegmentHashesByIdSite(); + $this->assertEquals($expected, $segmentsByIdSite); + } + /** * @param Date[] $dates */ diff --git a/tests/PHPUnit/Fixtures/RawArchiveDataWithTempAndInvalidated.php b/tests/PHPUnit/Fixtures/RawArchiveDataWithTempAndInvalidated.php index b93e6d42e7..6d094132b5 100644 --- a/tests/PHPUnit/Fixtures/RawArchiveDataWithTempAndInvalidated.php +++ b/tests/PHPUnit/Fixtures/RawArchiveDataWithTempAndInvalidated.php @@ -241,6 +241,110 @@ class RawArchiveDataWithTempAndInvalidated extends Fixture ), ); + + private static $segmentArchiveData = array( + array( + 'idarchive' => 20, + 'idsite' => 1, + 'name' => 'doneabcd1234abcd5678', + 'value' => ArchiveWriter::DONE_OK, + 'date1' => '2015-02-03', + 'date2' => '2015-02-03', + 'period' => 1, + 'ts_archived' => '2015-02-03 12:12:12' + ), + array( + 'idarchive' => 21, + 'idsite' => 1, + 'name' => 'doneabcd1234abcd5678.MyPlugin', + 'value' => ArchiveWriter::DONE_OK, + 'date1' => '2015-02-03', + 'date2' => '2015-02-03', + 'period' => 1, + 'ts_archived' => '2015-02-03 12:12:12' + ), + array( + 'idarchive' => 22, + 'idsite' => 2, + 'name' => 'doneabcd1234abcd5678', + 'value' => ArchiveWriter::DONE_OK, + 'date1' => '2015-02-03', + 'date2' => '2015-02-03', + 'period' => 1, + 'ts_archived' => '2015-02-03 12:12:12' + ), + array( + 'idarchive' => 23, + 'idsite' => 2, + 'name' => 'doneabcd1234abcd5678.MyPlugin', + 'value' => ArchiveWriter::DONE_OK, + 'date1' => '2015-02-03', + 'date2' => '2015-02-03', + 'period' => 1, + 'ts_archived' => '2015-02-03 12:12:12' + ), + array( + 'idarchive' => 24, + 'idsite' => 1, + 'name' => 'done9876fedc5432abcd', + 'value' => ArchiveWriter::DONE_OK, + 'date1' => '2015-02-03', + 'date2' => '2015-02-03', + 'period' => 1, + 'ts_archived' => '2015-02-03 12:12:12' + ), + array( + 'idarchive' => 25, + 'idsite' => 2, + 'name' => 'donehash1', + 'value' => ArchiveWriter::DONE_OK, + 'date1' => '2015-02-03', + 'date2' => '2015-02-03', + 'period' => 1, + 'ts_archived' => '2015-02-03 12:12:12' + ), + array( + 'idarchive' => 26, + 'idsite' => 2, + 'name' => 'donehash2', + 'value' => ArchiveWriter::DONE_OK, + 'date1' => '2015-02-03', + 'date2' => '2015-02-03', + 'period' => 1, + 'ts_archived' => '2015-02-03 12:12:12' + ), + array( + 'idarchive' => 27, + 'idsite' => 2, + 'name' => 'donehash2.MyPlugin', + 'value' => ArchiveWriter::DONE_OK, + 'date1' => '2015-02-03', + 'date2' => '2015-02-03', + 'period' => 1, + 'ts_archived' => '2015-02-03 12:12:12' + ), + array( + 'idarchive' => 28, + 'idsite' => 2, + 'name' => 'donehash3', + 'value' => ArchiveWriter::DONE_OK, + 'date1' => '2015-02-03', + 'date2' => '2015-02-03', + 'period' => 1, + 'ts_archived' => '2015-02-03 12:12:12' + ), + array( + 'idarchive' => 29 , + 'idsite' => 2, + 'name' => 'donehash3', + 'value' => ArchiveWriter::DONE_OK, + 'date1' => '2015-02-03', + 'date2' => '2015-02-03', + 'period' => 1, + 'ts_archived' => '2015-02-27 12:12:12' + ), + ); + /** * @var Date */ @@ -262,10 +366,21 @@ class RawArchiveDataWithTempAndInvalidated extends Fixture $this->insertOutdatedArchives($this->february); } + public function insertSegmentArchives(Date $archiveDate) + { + $dummyArchiveData = $this->setDatesOnArchiveData($archiveDate, self::$segmentArchiveData); + $this->insertArchiveRows($archiveDate, $dummyArchiveData); + } + private function insertOutdatedArchives(Date $archiveDate) { - $dummyArchiveData = $this->getDummyArchiveDataForDate($archiveDate); + $dummyArchiveData = $this->setDatesOnArchiveData($archiveDate, self::$dummyArchiveData); + $this->insertArchiveRows($archiveDate, $dummyArchiveData); + } + + private function insertArchiveRows(Date $archiveDate, array $dummyArchiveData) + { $numericTable = ArchiveTableCreator::getNumericTable($archiveDate); foreach ($dummyArchiveData as $row) { // done row @@ -301,14 +416,13 @@ class RawArchiveDataWithTempAndInvalidated extends Fixture Db::exec(sprintf($insertSqlTemplate, $table, implode("','", $row))); } - private function getDummyArchiveDataForDate($archiveDate) + private function setDatesOnArchiveData($archiveDate, $data) { - $rows = self::$dummyArchiveData; - foreach ($rows as &$row) { + foreach ($data as &$row) { $row['date1'] = $this->setDateMonthAndYear($row['date1'], $archiveDate); $row['date2'] = $this->setDateMonthAndYear($row['date1'], $archiveDate); } - return$rows; + return $data; } private function setDateMonthAndYear($dateString, Date $archiveDate) diff --git a/tests/PHPUnit/Integration/Archive/ArchivePurgerTest.php b/tests/PHPUnit/Integration/Archive/ArchivePurgerTest.php index d520e72fb9..f7051937e9 100644 --- a/tests/PHPUnit/Integration/Archive/ArchivePurgerTest.php +++ b/tests/PHPUnit/Integration/Archive/ArchivePurgerTest.php @@ -12,6 +12,7 @@ use Piwik\Config; use Piwik\Date; use Piwik\Db; use Piwik\Tests\Fixtures\RawArchiveDataWithTempAndInvalidated; +use Piwik\Tests\Framework\Fixture; use Piwik\Tests\Framework\TestCase\IntegrationTestCase; /** @@ -107,6 +108,35 @@ class ArchivePurgerTest extends IntegrationTestCase $this->assertEquals(3 * RawArchiveDataWithTempAndInvalidated::ROWS_PER_ARCHIVE, $deletedRowCount); } + public function test_purgeNoSiteArchives_PurgesAllNoSiteArchives() + { + //Create two websites (IDs #1 and #2). Existing rows for website #3 will be invalid. + Fixture::createWebsite($this->january); + Fixture::createWebsite($this->january); + + //There are 5 rows for website #3. We leave the other two because they're before our purge threshold. + $deletedRowCount = $this->archivePurger->purgeDeletedSiteArchives($this->january); + $this->assertEquals(3 * RawArchiveDataWithTempAndInvalidated::ROWS_PER_ARCHIVE, $deletedRowCount); + self::$fixture->assertArchivesDoNotExist(array(3, 7, 10), $this->january); + } + + public function test_purgeNoSegmentArchives_PurgesSegmentForAppropriateSitesOnly() + { + //Extra data set with segment and plugin archives + self::$fixture->insertSegmentArchives($this->january); + + $validSegmentIds = array( + 0 => ['DUMMYHASHSTR'], //valid for all sites + 1 => ['abcd1234abcd5678'], //valid for site 1. should be ignored for site 2 + 2 => ['hashthatdontexist', 'hash1', 'hash2'] + ); + + //Archive #29 also has a deleted segment but it's before the purge threshold so it stays for now. + $deletedRowCount = $this->archivePurger->purgeDeletedSegmentArchives($this->january, $validSegmentIds); + $this->assertEquals(4 * RawArchiveDataWithTempAndInvalidated::ROWS_PER_ARCHIVE, $deletedRowCount); + self::$fixture->assertArchivesDoNotExist(array(22, 23, 24, 28), $this->january); + } + private function configureCustomRangePurging() { Config::getInstance()->General['purge_date_range_archives_after_X_days'] = 3; |