Changeset 61469 in vbox for trunk/src/VBox/ValidationKit/testmanager/core

Timestamp:

Jun 5, 2016 3:57:06 AM (9 years ago)

Author:

vboxsync

Message:

testresults.py: Help the postgresql planner order the tables by explictly joining with TestBoxesWithStrings. (According to their docs, join forces a stronger from table lookup odering. We want our subqueries to be executed before touching TestBoxesWithStrings of course.)

File:

• trunk/src/VBox/ValidationKit/testmanager/core/testresults.py (modified) (9 diffs)

trunk/src/VBox/ValidationKit/testmanager/core/testresults.py

@@ -937 +937 @@
                   '       array_agg(TestResultFailures.sComment        ORDER BY TestResultFailures.idTestResult),\n' \
                   '       (TestSets.tsDone IS NULL) SortRunningFirst' + sSortColumns + '\n' \
-                  'FROM   BuildCategories,\n' \
-                  '       Builds,\n' \
-                  '       TestBoxesWithStrings,\n' \
-                  '       TestResults,\n' \
-                  '       TestCases,\n' \
-                  '       TestCaseArgs,\n' \
-                  '       (  SELECT TestSets.idTestSet AS idTestSet,\n' \
+                  'FROM   (  SELECT TestSets.idTestSet AS idTestSet,\n' \
                   '                 TestSets.tsDone AS tsDone,\n' \
                   '                 TestSets.tsCreated AS tsCreated,\n' \
@@ -980 +974 @@
 
         sQuery += '       ) AS TestSets\n' \
+                  '            INNER JOIN TestBoxesWithStrings\n' \
+                  '                    ON TestSets.idGenTestBox     = TestBoxesWithStrings.idGenTestBox' \
                   '       LEFT OUTER JOIN Builds AS TestSuiteBits\n' \
                   '                    ON TestSets.idBuildTestSuite = TestSuiteBits.idBuild\n' \
                   '       LEFT OUTER JOIN TestResultFailures\n' \
                   '                    ON     TestSets.idTestSet          = TestResultFailures.idTestSet\n' \
-                  '                       AND TestResultFailures.tsExpire = \'infinity\'::TIMESTAMP\n';
+                  '                       AND TestResultFailures.tsExpire = \'infinity\'::TIMESTAMP';
         if sSortOrderBy is not None and sSortOrderBy.find('FailureReason') >= 0:
             sQuery += '\n' \
                       '       LEFT OUTER JOIN FailureReasons\n' \
                       '                    ON     TestResultFailures.idFailureReason = FailureReasons.idFailureReason\n' \
-                      '                       AND FailureReasons.tsExpire            = \'infinity\'::TIMESTAMP\n';
+                      '                       AND FailureReasons.tsExpire            = \'infinity\'::TIMESTAMP';
+        sQuery += ',\n' \
+                  '       BuildCategories,\n' \
+                  '       Builds,\n' \
+                  '       TestResults,\n' \
+                  '       TestCases,\n' \
+                  '       TestCaseArgs\n';
         sQuery += 'WHERE  TestSets.idTestSet         = TestResults.idTestSet\n' \
                   '   AND TestResults.idTestResultParent is NULL\n' \
@@ -996 +998 @@
                   '   AND Builds.tsEffective        <= TestSets.tsCreated\n' \
                   '   AND Builds.idBuildCategory     = BuildCategories.idBuildCategory\n' \
-                  '   AND TestSets.idGenTestBox      = TestBoxesWithStrings.idGenTestBox\n' \
                   '   AND TestSets.idGenTestCase     = TestCases.idGenTestCase\n' \
                   '   AND TestSets.idGenTestCaseArgs = TestCaseArgs.idGenTestCaseArgs\n';
@@ -1181 +1182 @@
 
         self._oDb.execute('SELECT TestBoxesWithStrings.*\n'
-                          'FROM   TestBoxesWithStrings,\n'
-                          '       ( SELECT idTestBox         AS idTestBox,\n'
+                          'FROM   ( SELECT idTestBox         AS idTestBox,\n'
                           '                MAX(idGenTestBox) AS idGenTestBox\n'
                           '         FROM   TestSets\n'
@@ -1188 +1188 @@
                           '         GROUP BY idTestBox\n'
                           '       ) AS TestBoxIDs\n'
-                          'WHERE  TestBoxesWithStrings.idGenTestBox = TestBoxIDs.idGenTestBox\n'
+                          '       INNER JOIN TestBoxesWithStrings\n'
+                          '               ON TestBoxesWithStrings.idGenTestBox = TestBoxIDs.idGenTestBox\n'
                           'ORDER BY TestBoxesWithStrings.sName\n' );
         aoRet = []
@@ -1202 +1203 @@
 
         self._oDb.execute('SELECT TestCases.*\n'
-                          'FROM   TestCases,\n'
-                          '       ( SELECT idTestCase         AS idTestCase,\n'
+                          'FROM   ( SELECT idTestCase         AS idTestCase,\n'
                           '                MAX(idGenTestCase) AS idGenTestCase\n'
                           '         FROM   TestSets\n'
@@ -1209 +1209 @@
                           '         GROUP BY idTestCase\n'
                           '       ) AS TestCasesIDs\n'
-                          'WHERE  TestCases.idGenTestCase = TestCasesIDs.idGenTestCase\n'
+                          '       INNER JOIN TestCases ON TestCases.idGenTestCase = TestCasesIDs.idGenTestCase\n'
                           'ORDER BY TestCases.sName\n' );
         aoRet = [];
@@ -1223 +1223 @@
 
         self._oDb.execute('SELECT SchedGroups.*\n'
-                          'FROM   SchedGroups,\n'
-                          '       ( SELECT TestBoxes.idSchedGroup  AS idSchedGroup,\n'
+                          'FROM   ( SELECT TestBoxes.idSchedGroup  AS idSchedGroup,\n'
                           '                MAX(TestSets.tsCreated) AS tsNow\n'
                           '         FROM   TestSets,\n'
@@ -1232 +1231 @@
                           '         GROUP BY idSchedGroup\n'
                           '       ) AS SchedGroupIDs\n'
-                          'WHERE  SchedGroups.idSchedGroup = SchedGroupIDs.idSchedGroup\n'
-                          '   AND SchedGroups.tsExpire     > SchedGroupIDs.tsNow\n'
-                          '   AND SchedGroups.tsEffective <= SchedGroupIDs.tsNow\n'
+                          '       INNER JOIN SchedGroups\n'
+                          '               ON SchedGroups.idSchedGroup = SchedGroupIDs.idSchedGroup\n'
+                          '              AND SchedGroups.tsExpire     > SchedGroupIDs.tsNow\n'
+                          '              AND SchedGroups.tsEffective <= SchedGroupIDs.tsNow\n'
                           'ORDER BY SchedGroups.sName\n' );
         aoRet = []