[icinga-checkins] icinga.org: icinga-web/master: * Added method to add main order parts to limit subquery (fixes #2215)

git at icinga.org git at icinga.org
Tue Feb 14 11:03:03 CET 2012


Module: icinga-web
Branch: master
Commit: e95a217428c0c793b3744c2d1884a7cfbc061654
URL:    https://git.icinga.org/?p=icinga-web.git;a=commit;h=e95a217428c0c793b3744c2d1884a7cfbc061654

Author: Marius Hein <marius.hein at netways.de>
Date:   Tue Feb 14 11:02:25 2012 +0100

* Added method to add main order parts to limit subquery (fixes #2215)

---

 .../data/xml/grid/icinga-host-history-template.xml |    2 +-
 .../Doctrine/Adapter/Statement/IcingaOracle.php    |   79 ++++++++++++++++++--
 2 files changed, 72 insertions(+), 9 deletions(-)

diff --git a/app/modules/Cronks/data/xml/grid/icinga-host-history-template.xml b/app/modules/Cronks/data/xml/grid/icinga-host-history-template.xml
index 796d4db..16fa6f1 100644
--- a/app/modules/Cronks/data/xml/grid/icinga-host-history-template.xml
+++ b/app/modules/Cronks/data/xml/grid/icinga-host-history-template.xml
@@ -233,7 +233,7 @@
             </filter>
             
             <order>
-                <parameter name="enabled">false</parameter>
+                <parameter name="enabled">true</parameter>
                 <parameter name="default">false</parameter>
             </order>
         </field>
diff --git a/lib/doctrine/lib/Doctrine/Adapter/Statement/IcingaOracle.php b/lib/doctrine/lib/Doctrine/Adapter/Statement/IcingaOracle.php
index dbf485f..4e7b6c4 100644
--- a/lib/doctrine/lib/Doctrine/Adapter/Statement/IcingaOracle.php
+++ b/lib/doctrine/lib/Doctrine/Adapter/Statement/IcingaOracle.php
@@ -25,6 +25,7 @@
  * @package     Doctrine
  * @subpackage  Adapter
  * @author      Jannis Moßhammer <jannis.mosshammer at netways.de>
+ * @author      Marius Hein <marius.hein at netways.de>
  * @author      Konsta Vesterinen <kvesteri at cc.hut.fi>
  * @author      vadik56
  * @author      Miloslav Kmet <adrive-nospam at hip-hop.sk> 
@@ -106,13 +107,8 @@ class Doctrine_Adapter_Statement_IcingaOracle implements Doctrine_Adapter_Statem
         $this->createAliasMap($query);
         $this->removeInvalidAliases($query);
         $this->fixOrderFields($query);
-        
-        
-        // dirty icinga specific fixes  
-        $query = preg_replace("/notification_timeperiod_object_id/", "notif_timeperiod_object_id",$query);
-        $query = preg_replace("/([^ ]*long_output)/i", "TO_CHAR($1)",$query);
-        $query = preg_replace("/([^ ]*perfdata)/i", "TO_CHAR($1)",$query);
-        $query = preg_replace("/([^ ]*logentry_data)/i", "TO_CHAR($1)",$query);
+        $this->fixOrdersInSubqueries($query);
+        $this->addFieldConversion($query);
         
         if(substr_count($query,"(") != substr_count($query,")"))  
             $query .= ")";
@@ -127,6 +123,73 @@ class Doctrine_Adapter_Statement_IcingaOracle implements Doctrine_Adapter_Statem
     }
     
     /**
+     * Add TO_CHAR conversions for CLOB fields for oracle
+     * @param string $query
+     * @return string Parsed query
+     */
+    private function addFieldConversion(&$query) {
+        $query = preg_replace("/notification_timeperiod_object_id/", "notif_timeperiod_object_id",$query);
+        $query = preg_replace("/([^ ]*long_output)/i", "TO_CHAR($1)",$query);
+        $query = preg_replace("/([^ ]*perfdata)/i", "TO_CHAR($1)",$query);
+        $query = preg_replace("/([^ ]*logentry_data)/i", "TO_CHAR($1)",$query);
+        return $query;
+    }
+    
+    /**
+     * Adding order parts from main query to subquery which delivers
+     * the recordset
+     * https://dev.icinga.org/issues/2215
+     * @param string $query
+     * @return string
+     */
+    private function fixOrdersInSubqueries(&$query) {
+        $m = array();
+        
+        list($mainTable, $mainAlias) = $this->getTableParts($query);
+        
+        if ($mainTable && $mainAlias) {
+            
+            if (preg_match('/ORDER\s+BY\s+(.+)(;|$)/i', $query, $m)) {
+                $fields = AppKitArrayUtil::trimSplit($m[1]);
+                if (preg_match_all('/FROM\s+\(\s*(SELECT\s+[\w\.\,\s]+\s+FROM\s+'. $mainTable. '[^\)]+)\)/i', $query, $m, PREG_SET_ORDER)) {
+                    foreach ($m as $match) {
+                        $replaceMarker = $match[0];
+                        $subQuery = $match[1];
+                        list($subTable, $subAlias) = $this->getTableParts($subQuery);
+                        if ($subTable == $mainTable && !preg_match('/ORDER BY/i', $subQuery)) {
+                            $orderParts = array();
+                            foreach ($fields as $field) {
+                                $newOrder = preg_replace('/^\w+\./', $subAlias. '.', $field);
+                                $newField = preg_replace('/\s+.+$/', '', $newOrder);
+                                $orderParts[] = $newOrder;
+                                $subQuery = preg_replace('/^\s*(SELECT\s+(DISTINCT)?\s*)'. $subAlias. '/i', '\1 '. $newField. ', '. $subAlias, $subQuery);
+                            }
+                            $newSub = ' FROM ( '. $subQuery. ' ORDER BY '. implode(', ', $orderParts). ' ) ';
+                            
+                            $query = preg_replace('/'. preg_quote($replaceMarker, '/'). '/', $newSub, $query);
+                        }
+                    }
+                }
+            }
+        }
+        
+        return $query;
+    }
+    
+    /**
+     * Extracts table name and corresponding alias from query (part)
+     * @param string $queryPart
+     * @return string[TABLE_NAME, ALIAS]
+     */
+    private function getTableParts($queryPart) {
+        if (preg_match('/from (\w+)\s(\w+)\s/i', $queryPart, $m)) {
+            return array($m[1], $m[2]);
+        }
+        
+        return array(null, null);
+    }
+    
+    /**
      * Search for all ORDER tags and check if they selected. If not
      * remove the error-prone statement part
      * @param string $query
@@ -747,4 +810,4 @@ class Doctrine_Adapter_Statement_IcingaOracle implements Doctrine_Adapter_Statem
 
         return $this->statement;
     }
-}
+}
\ No newline at end of file





More information about the icinga-checkins mailing list