[icinga-checkins] icinga.org: icinga-web/mhein/default: * removed temptables and made timerange 2 subqueries instead

git at icinga.org git at icinga.org
Wed Nov 16 14:38:39 CET 2011


Module: icinga-web
Branch: mhein/default
Commit: 00006964743b5104a7e10eea991014848fdf8ea0
URL:    https://git.icinga.org/?p=icinga-web.git;a=commit;h=00006964743b5104a7e10eea991014848fdf8ea0

Author: Jannis Mosshammer <jannis.mosshammer at netways.de>
Date:   Tue Nov 15 06:36:00 2011 +0100

* removed temptables and made timerange 2 subqueries instead

---

 .../lib/database/models/IcingaSlahistoryTable.php  |   29 +++++--------------
 1 files changed, 8 insertions(+), 21 deletions(-)

diff --git a/app/modules/Api/lib/database/models/IcingaSlahistoryTable.php b/app/modules/Api/lib/database/models/IcingaSlahistoryTable.php
index 145e4ca..ba0d206 100644
--- a/app/modules/Api/lib/database/models/IcingaSlahistoryTable.php
+++ b/app/modules/Api/lib/database/models/IcingaSlahistoryTable.php
@@ -253,12 +253,15 @@ class IcingaSlahistoryTable extends Doctrine_Table {
         
         foreach($filterParts["params"] as $param=>$value)
             $stmt->bindValue($param,$value);
+
         return $stmt;
     }
     
     private static function getMySQLSummaryQuery(Doctrine_Connection $c, $filter = null) {   
         $dbh = $c->getDbh();
         $prefix = $c->getPrefix();
+        $timeQuery = self::getTimeRangeQuery($c,$filter);
+
         // MYSQL has no with clause, so we need two queries
         $mainQuery = "(SELECT 
                 s.object_id,
@@ -271,7 +274,7 @@ class IcingaSlahistoryTable extends Doctrine_Table {
                         UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(start_time)
                     )
                 ) as duration          
-             FROM  timerange1 s
+             FROM  ($timeQuery) s
              INNER JOIN ".$prefix."objects obj ON obj.object_id = s.object_id ";
         
         if($filter) {
@@ -279,28 +282,12 @@ class IcingaSlahistoryTable extends Doctrine_Table {
             $mainQuery .= $filterParts["wherePart"];
         }
         
-        $mainQuery .= "
-            
+        $mainQuery .= "            
              GROUP BY 
                  state*(scheduled_downtime-1)*-1,
                  object_id,
                  objecttype_id
              ) slahistory_main";
-        
-        $result = $dbh->query($query = "
-
-            START TRANSACTION;
-            CREATE TEMPORARY TABLE timerange1 LIKE ".$prefix."slahistory;
-            CREATE TEMPORARY TABLE timerange2 LIKE ".$prefix."slahistory;
-            INSERT INTO timerange1 (
-                ".self::getTimeRangeQuery($c,$filter).");
-            INSERT INTO timerange2 (
-                ".self::getTimeRangeQuery($c,$filter).");");
-
-        if($result == false) {
-            $err = $dbh->errorInfo();
-            throw new PDOException($err[0],$err[1],$err[2]);
-        }
          
        
         $stmt = $dbh->prepare($query = "
@@ -313,14 +300,14 @@ class IcingaSlahistoryTable extends Doctrine_Table {
             FROM
                  $mainQuery 
                  INNER JOIN (
-                     SELECT object_id,
+                     SELECT t1.object_id,
                      SUM(
                          COALESCE(
                              UNIX_TIMESTAMP(acknowledgement_time)-UNIX_TIMESTAMP(start_time),
                              UNIX_TIMESTAMP(end_time)-UNIX_TIMESTAMP(start_time),
                              UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(start_time)
                          )         
-                    ) as complete FROM timerange2
+                    ) as complete FROM ($timeQuery) t1
                     GROUP BY 
                         object_id     
                  ) as s ON slahistory_main.object_id = s.object_id  
@@ -332,7 +319,7 @@ class IcingaSlahistoryTable extends Doctrine_Table {
        
         foreach($filterParts["params"] as $param=>$value)
             $stmt->bindValue($param,$value);
-
+//         echo $query;die();
         return $stmt;
        
     }





More information about the icinga-checkins mailing list