Add informational diagnostic for archive invalidations table. (#17110)

* Add informational diagnostic for archive invalidations table.

* remove TODO

* fix test + remove other diagnostics

* add file

* text and value tweak

* update submodule

* renormalize lfs

* In diagnostics ui test, replace date times w/ DATETIME string.

* update expected screenshot

6 files changed, 210 insertions, 18 deletions

diff --git a/plugins/Diagnostics/Diagnostic/ArchiveInvalidationsInformational.php b/plugins/Diagnostics/Diagnostic/ArchiveInvalidationsInformational.php
new file mode 100644
index 0000000000..e07e6276b0
--- /dev/null
+++ b/plugins/Diagnostics/Diagnostic/ArchiveInvalidationsInformational.php
@@ -0,0 +1,115 @@
+<?php
+/**
+ * Matomo - free/libre analytics platform
+ *
+ * @link https://matomo.org
+ * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
+ */
+namespace Piwik\Plugins\Diagnostics\Diagnostic;
+
+use Piwik\Archive\ArchiveInvalidator;
+use Piwik\Common;
+use Piwik\Db;
+use Piwik\SettingsPiwik;
+use Piwik\Translation\Translator;
+
+/**
+ * Information about the archive invalidations.
+ */
+class ArchiveInvalidationsInformational implements Diagnostic
+{
+    /**
+     * @var Translator
+     */
+    private $translator;
+
+    public function __construct(Translator $translator)
+    {
+        $this->translator = $translator;
+    }
+
+    public function execute()
+    {
+        $results = [];
+
+        if (SettingsPiwik::isMatomoInstalled()) {
+            $invalidationCounts = $this->getInvalidationCounts();
+            $results[] = DiagnosticResult::informationalResult('Total Invalidation Count', $invalidationCounts['all'] ?? '0');
+            $results[] = DiagnosticResult::informationalResult('In Progress Invalidation Count', $invalidationCounts[ArchiveInvalidator::INVALIDATION_STATUS_IN_PROGRESS] ?? '0');
+            $results[] = DiagnosticResult::informationalResult('Scheduled Invalidation Count', $invalidationCounts[ArchiveInvalidator::INVALIDATION_STATUS_QUEUED] ?? '0');
+
+            $minMaxes = $this->getInvalidationMinMaxes();
+            $results[] = DiagnosticResult::informationalResult('Earliest invalidation ts_started', $minMaxes['min_ts_started']);
+            $results[] = DiagnosticResult::informationalResult('Latest invalidation ts_started', $minMaxes['max_ts_started']);
+            $results[] = DiagnosticResult::informationalResult('Earliest invalidation ts_invalidated', $minMaxes['min_ts_invalidated']);
+            $results[] = DiagnosticResult::informationalResult('Latest invalidation ts_invalidated', $minMaxes['max_ts_invalidated']);
+
+            $invalidationTypes = $this->getInvalidationTypes();
+            $results[] = DiagnosticResult::informationalResult('Number of segment invalidations', $invalidationTypes['count_segment']);
+            $results[] = DiagnosticResult::informationalResult('Number of plugin invalidations', $invalidationTypes['count_plugin']);
+            $results[] = DiagnosticResult::informationalResult('List of plugins being invalidated', implode(', ', $invalidationTypes['plugins']));
+        }
+
+        return $results;
+    }
+
+    public function getInvalidationCounts()
+    {
+        $table = Common::prefixTable('archive_invalidations');
+        $sql = "SELECT COUNT(*) as `count`, status FROM `$table` GROUP BY status";
+
+        $rows = Db::fetchAll($sql);
+
+        $result = [];
+        foreach ($rows as $row) {
+            $result[$row['status']]  = $row['count'];
+        }
+        $result['all'] = array_sum($result);
+        return $result;
+    }
+
+    public function getInvalidationMinMaxes()
+    {
+        $sql = "SELECT MIN(ts_started) as min_ts_started, MAX(ts_started) as max_ts_started, MIN(ts_invalidated) as min_ts_invalidated, MAX(ts_invalidated) as max_ts_invalidated FROM "
+            . Common::prefixTable('archive_invalidations');
+        $row = Db::fetchRow($sql);
+        return $row;
+    }
+
+    public function getInvalidationTypes()
+    {
+        $table = Common::prefixTable('archive_invalidations');
+
+        $pluginSql = 'IF(INSTR(`name`, \'.\') > 0, SUBSTRING_INDEX(`name`, \'.\', -1), NULL)';
+        $nonPluginDoneFlagSql = 'IF(INSTR(`name`, \'.\') > 0, SUBSTRING_INDEX(`name`, \'.\', 1), `name`)';
+
+        $sql = "SELECT COUNT(*) as `count`, $pluginSql AS plugin, CHAR_LENGTH($nonPluginDoneFlagSql) > 32 AS is_segment_archive
+                  FROM `$table`
+              GROUP BY plugin, is_segment_archive";
+
+        $result = [
+            'count_segment' => 0,
+            'count_plugin' => 0,
+            'plugins' => [],
+        ];
+
+        $rows = Db::fetchAll($sql);
+        foreach ($rows as $row) {
+            if (!empty($row['is_segment_archive'])) {
+                $result['count_segment'] += $row['count'];
+            }
+
+            if (!empty($row['plugin'])) {
+                $result['count_plugin'] += $row['count'];
+            }
+
+            $result['plugins'][] = $row['plugin'];
+        }
+
+        $result['plugins'] = array_unique($result['plugins']);
+        $result['plugins'] = array_filter($result['plugins']);
+        $result['plugins'] = array_values($result['plugins']);
+
+        return $result;
+    }
+}
\ No newline at end of file
diff --git a/plugins/Diagnostics/Diagnostic/ReportInformational.php b/plugins/Diagnostics/Diagnostic/ReportInformational.php
index 37daaacb8a..e49d51c4aa 100644
--- a/plugins/Diagnostics/Diagnostic/ReportInformational.php
+++ b/plugins/Diagnostics/Diagnostic/ReportInformational.php
@@ -48,27 +48,11 @@ class ReportInformational implements Diagnostic
             $results[] = DiagnosticResult::informationalResult('Had visits in last 5 days', $this->hadVisitsInLastDays(5));
             $results[] = DiagnosticResult::informationalResult('Archive Time Last Started', Option::get(CronArchive::OPTION_ARCHIVING_STARTED_TS));
             $results[] = DiagnosticResult::informationalResult('Archive Time Last Finished', Option::get(CronArchive::OPTION_ARCHIVING_FINISHED_TS));
-            $numQueued = $this->getNumInvalidationEntries(ArchiveInvalidator::INVALIDATION_STATUS_QUEUED);
-            $numInProgress = $this->getNumInvalidationEntries(ArchiveInvalidator::INVALIDATION_STATUS_IN_PROGRESS);
-            $results[] = DiagnosticResult::informationalResult('Num invalidations', sprintf('%s queued, %s in progress', $numQueued, $numInProgress));
         }
 
         return $results;
     }
 
-    private function getNumInvalidationEntries($status)
-    {
-        $table = Common::prefixTable('archive_invalidations');
-
-        try {
-            $numEntries = Db::fetchOne('SELECT count(*) from ' . $table . ' WHERE `status` = ?' , $status);
-        } catch ( \Exception $e ) {
-            $numEntries = '';
-        }
-
-        return $numEntries;
-    }
-
     private function hadVisitsInLastDays($numDays)
     {
         $table = Common::prefixTable('log_visit');
diff --git a/plugins/Diagnostics/config/config.php b/plugins/Diagnostics/config/config.php
index 3dbefaf339..e5be0cf756 100644
--- a/plugins/Diagnostics/config/config.php
+++ b/plugins/Diagnostics/config/config.php
@@ -40,6 +40,7 @@ return array(
         DI\get('Piwik\Plugins\Diagnostics\Diagnostic\ServerInformational'),
         DI\get('Piwik\Plugins\Diagnostics\Diagnostic\ReportInformational'),
         DI\get('Piwik\Plugins\Diagnostics\Diagnostic\UserInformational'),
+        DI\get(\Piwik\Plugins\Diagnostics\Diagnostic\ArchiveInvalidationsInformational::class),
     ),
     // Allows other plugins to disable diagnostics that were previously registered
     'diagnostics.disabled' => array(),
diff --git a/plugins/Diagnostics/tests/Integration/Diagnostic/ArchiveInvalidationsInformationalTest.php b/plugins/Diagnostics/tests/Integration/Diagnostic/ArchiveInvalidationsInformationalTest.php
new file mode 100644
index 0000000000..fec8cb5a73
--- /dev/null
+++ b/plugins/Diagnostics/tests/Integration/Diagnostic/ArchiveInvalidationsInformationalTest.php
@@ -0,0 +1,85 @@
+<?php
+/**
+ * Matomo - free/libre analytics platform
+ *
+ * @link https://matomo.org
+ * @license http://www.gnu.org/licenses/gpl-3.0.html GPL v3 or later
+ */
+
+namespace Piwik\Plugins\Diagnostics\tests\Integration\Diagnostic;
+
+use Piwik\Common;
+use Piwik\Container\StaticContainer;
+use Piwik\Db;
+use Piwik\Plugins\Diagnostics\Diagnostic\ArchiveInvalidationsInformational;
+use Piwik\Tests\Framework\TestCase\IntegrationTestCase;
+use Piwik\Translation\Translator;
+
+class ArchiveInvalidationsInformationalTest extends IntegrationTestCase
+{
+    public function test_execute()
+    {
+        $segmentHash = md5('something');
+        $anotherSegmentHash = md5('anothersomething');
+        $this->insertInvalidations([
+            ['idsite' => '1', 'name' => 'done', 'date1' => '2020-01-13', 'date2' => '2020-01-13', 'period' => '1', 'report' => '', 'ts_started' => '2020-01-13 02:00:00', 'ts_invalidated' => '2020-01-12 02:00:00', 'status' => 1],
+            ['idsite' => '1', 'name' => 'done.MyPlugin', 'date1' => '2020-01-13', 'date2' => '2020-01-19', 'period' => '2', 'report' => '', 'ts_started' => NULL, 'ts_invalidated' => '2020-01-12 06:00:00', 'status' => 0],
+            ['idsite' => '1', 'name' => 'done' . $segmentHash, 'date1' => '2020-01-01', 'date2' => '2020-01-31', 'period' => '3', 'report' => '', 'ts_started' => NULL, 'ts_invalidated' => '2020-01-12 04:00:00', 'status' => 0],
+            ['idsite' => '2', 'name' => 'done' . $segmentHash . '.MyPlugin', 'date1' => '2020-01-15', 'date2' => '2020-01-15', 'period' => '1', 'report' => '', 'ts_started' => '2020-01-14 02:00:00', 'ts_invalidated' => '2020-01-12 03:00:00', 'status' => 1],
+            ['idsite' => '2', 'name' => 'done.MyOtherPlugin', 'date1' => '2020-01-15', 'date2' => '2020-01-15', 'period' => '1', 'report' => '', 'ts_started' => NULL, 'ts_invalidated' => '2020-01-12 12:00:00', 'status' => 0],
+            ['idsite' => '3', 'name' => 'done' . $anotherSegmentHash, 'date1' => '2020-01-06', 'date2' => '2020-01-12', 'period' => '2', 'report' => '', 'ts_started' => NULL, 'ts_invalidated' => '2020-01-12 02:30:00', 'status' => 0],
+            ['idsite' => '3', 'name' => 'done' . $anotherSegmentHash . '.AThirdPlugin', 'date1' => '2020-01-01', 'date2' => '2020-01-31', 'period' => '3', 'report' => 'aReport', 'ts_started' => '2020-01-13 07:00:00', 'ts_invalidated' => '2020-01-11 02:00:00', 'status' => 1],
+            ['idsite' => '3', 'name' => 'done', 'date1' => '2020-02-06', 'date2' => '2020-02-06', 'period' => '1', 'report' => '', 'ts_started' => NULL, 'ts_invalidated' => '2020-01-10 02:00:00', 'status' => 0],
+            ['idsite' => '3', 'name' => 'done', 'date1' => '2020-01-16', 'date2' => '2020-01-16', 'period' => '1', 'report' => '', 'ts_started' => NULL, 'ts_invalidated' => '2020-01-20 02:00:00', 'status' => 0],
+            ['idsite' => '3', 'name' => 'done' . $segmentHash, 'date1' => '2020-01-16', 'date2' => '2020-01-16', 'period' => '1', 'report' => '', 'ts_started' => NULL, 'ts_invalidated' => '2020-01-12 02:00:00', 'status' => 0],
+        ]);
+
+        $diagnostic = new ArchiveInvalidationsInformational(StaticContainer::get(Translator::class));
+
+        $counts = $diagnostic->getInvalidationCounts();
+        $this->assertEquals([
+            0 => '7',
+            1 => '3',
+            'all' => 10,
+        ], $counts);
+
+        $minMaxes = $diagnostic->getInvalidationMinMaxes();
+        $this->assertEquals([
+            'min_ts_started' => '2020-01-13 02:00:00',
+            'max_ts_started' => '2020-01-14 02:00:00',
+            'min_ts_invalidated' => '2020-01-10 02:00:00',
+            'max_ts_invalidated' => '2020-01-20 02:00:00',
+        ], $minMaxes);
+
+        $types = $diagnostic->getInvalidationTypes();
+        $this->assertEquals([
+            'count_segment' => 5,
+            'count_plugin' => 4,
+            'plugins' => [
+                'AThirdPlugin',
+                'MyOtherPlugin',
+                'MyPlugin',
+            ],
+        ], $types);
+    }
+
+
+    private function insertInvalidations(array $invalidations)
+    {
+        $table = Common::prefixTable('archive_invalidations');
+        foreach ($invalidations as $invalidation) {
+            $sql = "INSERT INTO $table (name, idsite, date1, date2, period, report, status, ts_started, ts_invalidated) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
+            Db::query($sql, [
+                $invalidation['name'],
+                $invalidation['idsite'],
+                $invalidation['date1'],
+                $invalidation['date2'],
+                $invalidation['period'],
+                $invalidation['report'],
+                $invalidation['status'],
+                $invalidation['ts_started'],
+                $invalidation['ts_invalidated'],
+            ]);
+        }
+    }
+}
\ No newline at end of file
diff --git a/plugins/Diagnostics/tests/UI/Diagnostics_spec.js b/plugins/Diagnostics/tests/UI/Diagnostics_spec.js
index ce6d4b9ccd..2c5b7c40c7 100644
--- a/plugins/Diagnostics/tests/UI/Diagnostics_spec.js
+++ b/plugins/Diagnostics/tests/UI/Diagnostics_spec.js
@@ -16,6 +16,13 @@ describe("Diagnostics", function () {
         await page.goto(url);
 
         const content = await page.$('#content');
+        await page.evaluate(() => {
+            $('#systemCheckInformational td').each(function () {
+                let html = $(this).html();
+                html = html.replace(/\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}/g, 'DATETIME');
+                $(this).html(html);
+            });
+        });
         expect(await content.screenshot()).to.matchImage('page');
     });
 });
\ No newline at end of file
diff --git a/plugins/Diagnostics/tests/UI/expected-screenshots/Diagnostics_page.png b/plugins/Diagnostics/tests/UI/expected-screenshots/Diagnostics_page.png
index 968d93226a..e4bee09a59 100644
--- a/plugins/Diagnostics/tests/UI/expected-screenshots/Diagnostics_page.png
+++ b/plugins/Diagnostics/tests/UI/expected-screenshots/Diagnostics_page.png
@@ -1,3 +1,3 @@
 version https://git-lfs.github.com/spec/v1
-oid sha256:769ba523c34fdd174dbba710c179d8181758fec824c6dc022f0f156001242140
-size 393749
+oid sha256:4ada32d101322f25d0d3d9a100c92dd0a88c82475bb2a4fce676485672315ed9
+size 422307