[icinga-checkins] icinga.org: icinga-web/mhein/default: * Fixed count query in apisearchaction, removed views and used temptables instead in mysql

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


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

Author: Jannis Mosshammer <jannis.mosshammer at netways.de>
Date:   Tue Nov 15 03:29:26 2011 +0100

* Fixed count query in apisearchaction, removed views and used temptables instead in mysql
This requires performance monitoring

---

 app/modules/Api/actions/ApiSearchAction.class.php  |   12 ++++++++++--
 .../lib/database/models/IcingaSlahistoryTable.php  |   16 +++++++++-------
 2 files changed, 19 insertions(+), 9 deletions(-)

diff --git a/app/modules/Api/actions/ApiSearchAction.class.php b/app/modules/Api/actions/ApiSearchAction.class.php
index e2d6d2a..0049301 100644
--- a/app/modules/Api/actions/ApiSearchAction.class.php
+++ b/app/modules/Api/actions/ApiSearchAction.class.php
@@ -73,16 +73,24 @@ class Api_ApiSearchAction extends IcingaApiBaseAction {
 
         //Setup count
         if ($rd->getParameter("countColumn")) {
-            $search->setSearchType(IcingaApiConstants::SEARCH_TYPE_COUNT);
+            $search = @$API->createSearch()->setSearchTarget($target);
+            $this->addFilters($search,$rd);
+
+            $this->setColumns($search,$rd);
+            $this->setGrouping($search,$rd);
+            $this->setOrder($search,$rd);
+            $this->setLimit($search,$rd);
 
+            $search->setSearchType(IcingaApiConstants::SEARCH_TYPE_COUNT);
 
             IcingaPrincipalTargetTool::applyApiSecurityPrincipals($search);
+           
             $rd->setParameter("searchCount",$search->fetch()->getAll());
         }
         
         if($rd->getParameter("withSLA") && ($target == "host" || $target == "service")) {
             $slaDefaults = AgaviConfig::get("modules.api.sla_settings");
-            print_r($slaDefaults);die();
+
             if(isset($slaDefaults["enabled"]) && $slaDefaults["enabled"]) {
                 if(!isset($slaDefaults["default_timespan"]))
                     $slaDefaults["default_timespan"] = "-1 Month";
diff --git a/app/modules/Api/lib/database/models/IcingaSlahistoryTable.php b/app/modules/Api/lib/database/models/IcingaSlahistoryTable.php
index 82638d6..145e4ca 100644
--- a/app/modules/Api/lib/database/models/IcingaSlahistoryTable.php
+++ b/app/modules/Api/lib/database/models/IcingaSlahistoryTable.php
@@ -271,7 +271,7 @@ class IcingaSlahistoryTable extends Doctrine_Table {
                         UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(start_time)
                     )
                 ) as duration          
-             FROM  timerange  s
+             FROM  timerange1 s
              INNER JOIN ".$prefix."objects obj ON obj.object_id = s.object_id ";
         
         if($filter) {
@@ -290,9 +290,12 @@ class IcingaSlahistoryTable extends Doctrine_Table {
         $result = $dbh->query($query = "
 
             START TRANSACTION;
-            DROP VIEW IF EXISTS timerange;
-            CREATE ALGORITHM=MERGE VIEW timerange AS
-                ".self::getTimeRangeQuery($c,$filter).";");
+            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();
@@ -317,7 +320,7 @@ class IcingaSlahistoryTable extends Doctrine_Table {
                              UNIX_TIMESTAMP(end_time)-UNIX_TIMESTAMP(start_time),
                              UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(start_time)
                          )         
-                    ) as complete FROM timerange
+                    ) as complete FROM timerange2
                     GROUP BY 
                         object_id     
                  ) as s ON slahistory_main.object_id = s.object_id  
@@ -325,12 +328,11 @@ class IcingaSlahistoryTable extends Doctrine_Table {
                 slahistory_main.object_id,
                 slahistory_main.sla_state,
                 slahistory_main.objecttype_id;
-             DROP VIEW timerange;
          ");
        
         foreach($filterParts["params"] as $param=>$value)
             $stmt->bindValue($param,$value);
-        
+
         return $stmt;
        
     }





More information about the icinga-checkins mailing list