Qt Metrics 2 (v0.10): Flaky list performance

Performance improvement to the flaky list database search. The COUNT function in the database query was very slow. Replaced with simple list query and the counting the flaky testsets in the code. Change-Id: I7ec0e4dfd0a5df16ab517ea004bb6fe3fcedf423 Reviewed-by: Tony Sarajärvi <tony.sarajarvi@theqtcompany.com>
author: Juha Sippola <juhasippola@outlook.com> 2015-07-10 15:06:55 +0300
committer: Tony Sarajärvi <tony.sarajarvi@theqtcompany.com> 2015-09-16 07:32:57 +0000
commit: 961933d0c5dd9109a34fd4281aaf648fae7d324b (patch)
tree: 14ec3edd6184f034b8fc27b5ca766e21cfd7e622
parent: 53b4d6210103d39850bb4f76ca63f541711fb0e9 (diff)
4 files changed, 94 insertions, 57 deletions
diff --git a/non-puppet/qtmetrics2/src/Database.php b/non-puppet/qtmetrics2/src/Database.php
index 92bc163..db81c05 100644
--- a/non-puppet/qtmetrics2/src/Database.php
+++ b/non-puppet/qtmetrics2/src/Database.php
@@ -34,8 +34,8 @@
 
 /**
  * Database class
- * @version   0.7
- * @since     01-07-2015
+ * @version   0.8
+ * @since     06-07-2015
  * @author    Juha Sippola
  */
 
@@ -517,14 +517,14 @@ class Database {
 
     /**
      * Get counts of all passed and failed runs for a testset in specified builds since specified date
-     * If several testsets found with the same name in different projects, all are listed
      * @param string $testset
+     * @param string $testsetProject
      * @param string $runProject
      * @param string $runState
      * @param string $date
      * @return array (string name, string project, int passed, int failed)
      */
-    public function getTestsetResultCounts($testset, $runProject, $runState, $date)
+    public function getTestsetResultCounts($testset, $testsetProject, $runProject, $runState, $date)
     {
         $result = array();
         $query = $this->db->prepare("
@@ -540,6 +540,7 @@ class Database {
                 INNER JOIN project_run ON conf_run.project_run_id = project_run.id
                 INNER JOIN state ON project_run.state_id = state.id
             WHERE
+                project.name = ? AND
                 testset.name = ? AND
                 project_run.project_id = (SELECT id FROM project WHERE name = ?) AND
                 project_run.state_id = (SELECT id FROM state WHERE name = ?) AND
@@ -548,6 +549,7 @@ class Database {
             ORDER BY project.name;
         ");
         $query->execute(array(
+            $testsetProject,
             $testset,
             $runProject,
             $runState,
@@ -575,34 +577,65 @@ class Database {
     public function getTestsetsFlakyCounts($date, $limit)
     {
         $result = array();
-        $query = $this->db->prepare('
+        // Get all flaky test runs
+        $query = $this->db->prepare("
             SELECT
                 testset.name AS testset,
-                project.name AS project,
-                COUNT(CASE WHEN testset_run.run > 1 AND (testset_run.result = "passed" OR testset_run.result = "ipassed") THEN testset_run.run END) AS flaky,
-                COUNT(testset_run.id) AS total
+                project.name AS project
             FROM testset_run
                 INNER JOIN testset ON testset_run.testset_id = testset.id
                 INNER JOIN project ON testset.project_id = project.id
                 INNER JOIN conf_run ON testset_run.conf_run_id = conf_run.id
                 INNER JOIN project_run ON conf_run.project_run_id = project_run.id
-            WHERE project_run.timestamp >= ?
-            GROUP BY testset.name
-            ORDER BY flaky DESC, testset.name ASC
-            LIMIT ?;
-        ');
-        $query->bindParam(1, $date);
-        $query->bindParam(2, $limit, PDO::PARAM_INT);       // int data type must be separately set
-        $query->execute();
+            WHERE
+                project_run.timestamp >= ? AND
+                testset_run.run > 1 AND
+                testset_run.result LIKE '%passed'
+            ORDER BY project.name, testset.name;
+        ");
+        $query->execute(array(
+            $date
+        ));
+        // Calculate flaky count per testset (calculated here instead of in the query above for performance reasons)
+        $testset = '';
+        $testsets = array();
+        $projects = array();
+        $counts = array();
         while($row = $query->fetch(PDO::FETCH_ASSOC)) {
-            if ($row['flaky'] > 0) {                        // return only those where flaky identified
-                $result[] = array(
-                    'name' => $row['testset'],
-                    'project' => $row['project'],
-                    'flaky' => $row['flaky'],
-                    'total' => $row['total']
-                );
+            if ($testset === '') {                                                  // Initialize
+                $key = 0;
+                $flaky = 0;
+                $testset = $row['testset'];
+                $project = $row['project'];
+            }
+            if ($row['testset'] !== $testset OR $row['project'] !== $project) {    // New testset
+                $key++;
+                $flaky = 0;
+                $testset = $row['testset'];
+                $project = $row['project'];
+            }
+            $flaky++;
+            $testsets[$key] = $row['testset'];
+            $projects[$key] = $row['project'];
+            $counts[$key] = $flaky;
+        }
+        // List top n flaky testsets
+        arsort($counts);
+        $i = 0;
+        foreach ($counts as $key => $value) {
+            $data = self::getTestsetFlakyCounts($testsets[$key], $projects[$key], $date);
+            foreach($data as $row) {
+                $total = $row['total'];
             }
+            $result[] = array(
+                'name' => $testsets[$key],
+                'project' => $projects[$key],
+                'flaky' => $value,
+                'total' => $total
+            );
+            $i++;
+            if ($i >= $limit)
+                break;
         }
         return $result;
     }
@@ -611,10 +644,11 @@ class Database {
      * Get counts of flaky runs for a testset since specified date
      * Scope is all builds (state and any)
      * @param string $testset
+     * @param string $testsetProject
      * @param string $date
      * @return array (string name, string project, int flaky, int total)
      */
-    public function getTestsetFlakyCounts($testset, $date)
+    public function getTestsetFlakyCounts($testset, $testsetProject, $date)
     {
         $result = array();
         $query = $this->db->prepare('
@@ -628,11 +662,15 @@ class Database {
                 INNER JOIN project ON testset.project_id = project.id
                 INNER JOIN conf_run ON testset_run.conf_run_id = conf_run.id
                 INNER JOIN project_run ON conf_run.project_run_id = project_run.id
-            WHERE testset.name = ? AND project_run.timestamp >= ?
+            WHERE
+                project.name = ? AND
+                testset.name = ? AND
+                project_run.timestamp >= ?
             GROUP BY testset.name
             ORDER BY project.name;
         ');
         $query->execute(array(
+            $testsetProject,
             $testset,
             $date
         ));
diff --git a/non-puppet/qtmetrics2/src/Factory.php b/non-puppet/qtmetrics2/src/Factory.php
index 3712013..b1a86c3 100644
--- a/non-puppet/qtmetrics2/src/Factory.php
+++ b/non-puppet/qtmetrics2/src/Factory.php
@@ -34,8 +34,8 @@
 
 /**
  * Factory class
- * @version   0.5
- * @since     01-07-2015
+ * @version   0.6
+ * @since     06-07-2015
  * @author    Juha Sippola
  */
 
@@ -218,23 +218,20 @@ class Factory {
         // Failure result counts (from specified builds only)
         $days = intval($ini['top_failures_last_days']) - 1;
         $since = self::getSinceDate($days);
-        $dbTestsetDetails = self::db()->getTestsetResultCounts($name, $runProject, $runState, $since);
+        $dbTestsetDetails = self::db()->getTestsetResultCounts($name, $testsetProject, $runProject, $runState, $since);
         foreach($dbTestsetDetails as $detail) {
-            if ($detail['project'] === $testsetProject)
-                $obj->setTestsetResultCounts($detail['passed'], $detail['failed']);
+            $obj->setTestsetResultCounts($detail['passed'], $detail['failed']);
         }
         // Flaky counts (all builds)
         $days = intval($ini['flaky_testsets_last_days']) - 1;
         $since = self::getSinceDate($days);
-        $dbTestsetDetails = self::db()->getTestsetFlakyCounts($name, $since);
+        $dbTestsetDetails = self::db()->getTestsetFlakyCounts($name, $testsetProject, $since);
         foreach($dbTestsetDetails as $detail) {
-            if ($detail['project'] === $testsetProject)
-                $obj->setTestsetFlakyCounts($detail['flaky'], $detail['total']);
+            $obj->setTestsetFlakyCounts($detail['flaky'], $detail['total']);
         }
         return $obj;
     }
 
-/* NEW */
     /**
      * Create ConfRun objects for those in database
      * @param string $runProject
diff --git a/non-puppet/qtmetrics2/src/test/DatabaseTest.php b/non-puppet/qtmetrics2/src/test/DatabaseTest.php
index 00a0d9d..cff443a 100644
--- a/non-puppet/qtmetrics2/src/test/DatabaseTest.php
+++ b/non-puppet/qtmetrics2/src/test/DatabaseTest.php
@@ -38,8 +38,8 @@ require_once(__DIR__.'/../Factory.php');
  * Database unit test class
  * Some of the tests require the test data as inserted into database with qtmetrics_insert.sql
  * @example   To run (in qtmetrics root directory): php <path-to-phpunit>/phpunit.phar ./src/test
- * @version   0.7
- * @since     01-07-2015
+ * @version   0.8
+ * @since     06-07-2015
  * @author    Juha Sippola
  */
 
@@ -422,12 +422,12 @@ class DatabaseTest extends PHPUnit_Framework_TestCase
      * Test getTestsetResultCounts
      * @dataProvider testGetTestsetResultCountsData
      */
-    public function testGetTestsetResultCounts($testset, $runProject, $runState, $date, $exp_project, $exp_testset_count_min, $exp_failed_min)
+    public function testGetTestsetResultCounts($testset, $testsetProject, $runProject, $runState, $date, $exp_project, $exp_testset_count_min, $exp_failed_min)
     {
         $testsets = array();
         $failed = 0;
         $db = Factory::db();
-        $result = $db->getTestsetResultCounts($testset, $runProject, $runState, $date);
+        $result = $db->getTestsetResultCounts($testset, $testsetProject, $runProject, $runState, $date);
         foreach($result as $row) {
             $this->assertArrayHasKey('name', $row);
             $this->assertArrayHasKey('project', $row);
@@ -448,13 +448,14 @@ class DatabaseTest extends PHPUnit_Framework_TestCase
     public function testGetTestsetResultCountsData()
     {
         return array(
-            array('tst_qftp', 'Qt5', 'state', '2013-05-01', 'qtbase', 1, 1),
-            array('tst_qftp', 'Qt5', 'state', '2013-05-28', 'qtbase', 1, 1),
-            array('tst_qftp', 'Qt5', 'state', '2013-05-29', 'qtbase', 0, 0),
-            array('tst_qftp', 'Qt5', 'state', '2999-05-29', 'qtbase', 0, 0),
-            array('tst_qftp', 'qtbase', 'state', '2013-05-01', '', 0, 0),               // QtBase build not run (Qt5 only)
-            array('tst_networkselftest', 'Qt5', 'state', '2013-05-01', 'qtbase', 1, 0), // tst_networkselftest has been run but not failed
-            array('invalid-name', 'Qt5', 'state', '2013-05-29', '', 0, 0)
+            array('tst_qftp', 'qtbase', 'Qt5', 'state', '2013-05-01', 'qtbase', 1, 1),
+            array('tst_qftp', 'qtbase', 'Qt5', 'state', '2013-05-28', 'qtbase', 1, 1),
+            array('tst_qftp', 'qtbase', 'Qt5', 'state', '2013-05-29', 'qtbase', 0, 0),
+            array('tst_qftp', 'qtbase', 'Qt5', 'state', '2999-05-29', 'qtbase', 0, 0),
+            array('tst_qftp', 'qtbase', 'qtbase', 'state', '2013-05-01', '', 0, 0),               // QtBase build not run (Qt5 only)
+            array('tst_networkselftest', 'qtbase', 'Qt5', 'state', '2013-05-01', 'qtbase', 1, 0), // tst_networkselftest has been run but not failed
+            array('invalid-name', 'qtbase', 'Qt5', 'state', '2013-05-29', '', 0, 0),
+            array('tst_qftp', 'invalid-name', 'Qt5', 'state', '2013-05-29', '', 0, 0)
         );
     }
 
@@ -488,8 +489,8 @@ class DatabaseTest extends PHPUnit_Framework_TestCase
     {
         return array(
             array('2013-05-01', 10, 'tst_qfont', 'tst_networkselftest', 2, 1),  // in test data only tst_qfont and tst_qftp are flaky
-            array('2013-05-01', 1, 'tst_qfont', 'tst_networkselftest', 1, 1),
-            array('2013-05-28', 10, 'tst_qfont', 'tst_qftp', 1, 1),             // in test data only tst_qfont is flaky
+            array('2013-05-28', 10, 'tst_qfont', 'tst_networkselftest', 1, 1),
+            array('2013-05-01', 1, 'tst_qftp', 'tst_networkselftest', 1, 2),
             array('2013-05-29', 10, '', '', 0, 0),
             array('2999-05-29', 10, '', '', 0, 0)
         );
@@ -499,12 +500,12 @@ class DatabaseTest extends PHPUnit_Framework_TestCase
      * Test getTestsetFlakyCounts
      * @dataProvider testGetTestsetFlakyCountsData
      */
-    public function testGetTestsetFlakyCounts($testset, $date, $exp_project, $exp_testset_count_min, $exp_flaky_min)
+    public function testGetTestsetFlakyCounts($testset, $testsetProject, $date, $exp_project, $exp_testset_count_min, $exp_flaky_min)
     {
         $testsets = array();
         $flaky = 0;
         $db = Factory::db();
-        $result = $db->getTestsetFlakyCounts($testset, $date);
+        $result = $db->getTestsetFlakyCounts($testset, $testsetProject, $date);
         foreach($result as $row) {
             $this->assertArrayHasKey('name', $row);
             $this->assertArrayHasKey('project', $row);
@@ -525,12 +526,13 @@ class DatabaseTest extends PHPUnit_Framework_TestCase
     public function testGetTestsetFlakyCountsData()
     {
         return array(
-            array('tst_qfont', '2013-05-01', 'qtbase', 1, 1),
-            array('tst_qfont', '2013-05-28', 'qtbase', 1, 1),
-            array('tst_qfont', '2013-05-29', 'qtbase', 0, 0),
-            array('tst_qfont', '2999-05-29', 'qtbase', 0, 0),
-            array('tst_networkselftest', '2013-05-01', 'qtbase', 1, 0), // tst_networkselftest has been run but not flaky
-            array('invalid-name', '2013-05-29', '', 0, 0)
+            array('tst_qfont', 'qtbase', '2013-05-01', 'qtbase', 1, 1),
+            array('tst_qfont', 'qtbase', '2013-05-28', 'qtbase', 1, 1),
+            array('tst_qfont', 'qtbase', '2013-05-29', 'qtbase', 0, 0),
+            array('tst_qfont', 'qtbase', '2999-05-29', 'qtbase', 0, 0),
+            array('tst_networkselftest', 'qtbase', '2013-05-01', 'qtbase', 1, 0), // tst_networkselftest has been run but not flaky
+            array('invalid-name', 'qtbase', '2013-05-29', '', 0, 0),
+            array('tst_qfont', 'invalid-name', '2013-05-29', '', 0, 0)
         );
     }
 
diff --git a/non-puppet/qtmetrics2/templates/about.html b/non-puppet/qtmetrics2/templates/about.html
index b9c61bb..b8f48c4 100644
--- a/non-puppet/qtmetrics2/templates/about.html
+++ b/non-puppet/qtmetrics2/templates/about.html
@@ -34,7 +34,7 @@
 
 /**
  * About window content
- * @version   0.9
+ * @version   0.10
  * @since     06-07-2015
  * @author    Juha Sippola
  */
@@ -44,4 +44,4 @@
 <p>This is Qt Metrics revision 2 with redesigned UI and database.</p>
 <p>These pages are still <strong>under construction</strong> and therefore the views and functionality is limited.</p>
 <p>See the <a href="https://wiki.qt.io/Qt_Metrics_2_Backlog" target="_blank">backlog</a> for development items currently identified or in progress.</p>
-<p><small>Version 0.9 (06-Jul-2015)</small></p>
+<p><small>Version 0.10 (06-Jul-2015)</small></p>
author	Juha Sippola <juhasippola@outlook.com>	2015-07-10 15:06:55 +0300
committer	Tony Sarajärvi <tony.sarajarvi@theqtcompany.com>	2015-09-16 07:32:57 +0000
commit	961933d0c5dd9109a34fd4281aaf648fae7d324b (patch)
tree	14ec3edd6184f034b8fc27b5ca766e21cfd7e622
parent	53b4d6210103d39850bb4f76ca63f541711fb0e9 (diff)