* Added feature #2988: Enable EXPLAIN SELECT in TS admin panel
authorMichael Stucki <michael.stucki@typo3.org>
Mon, 4 Feb 2008 14:05:22 +0000 (14:05 +0000)
committerMichael Stucki <michael.stucki@typo3.org>
Mon, 4 Feb 2008 14:05:22 +0000 (14:05 +0000)
git-svn-id: https://svn.typo3.org/TYPO3v4/Core/trunk@3054 709f56b5-9817-0410-a4d7-c38de5d9e867

ChangeLog
t3lib/class.t3lib_db.php
t3lib/class.t3lib_timetrack.php
typo3/sysext/cms/tslib/index_ts.php

index e6eb247..89fb80d 100755 (executable)
--- a/ChangeLog
+++ b/ChangeLog
@@ -5,6 +5,7 @@
 2008-02-04  Michael Stucki  <michael@typo3.org>
 
        * Fixed bug #7295: sysext/dbal/: Uninitialized variable in ->handler_getFromTableList() (Patch by Oliver Klee)
+       * Added feature #2988: Enable EXPLAIN SELECT in TS admin panel
 
 2008-02-04  Oliver Hader  <oh@inpublica.de>
 
index 05bf3a2..65dcc55 100755 (executable)
@@ -142,6 +142,7 @@ class t3lib_DB {
        var $debugOutput = FALSE;               // Set "TRUE" if you want database errors outputted.
        var $debug_lastBuiltQuery = '';         // Internally: Set to last built query (not necessarily executed...)
        var $store_lastBuiltQuery = FALSE;      // Set "TRUE" if you want the last built query to be stored in $debug_lastBuiltQuery independent of $this->debugOutput
+       var $explainOutput = 0;                 // Set this to 1 to get queries explained (devIPmask must match). Set the value to 2 to the same but disregarding the devIPmask. There is an alternative option to enable explain output in the admin panel under "TypoScript", which will produce much nicer output, but only works in FE.
 
                // Default link identifier:
        var $link = FALSE;
@@ -223,8 +224,16 @@ class t3lib_DB {
         * @return      pointer         MySQL result pointer / DBAL object
         */
        function exec_SELECTquery($select_fields,$from_table,$where_clause,$groupBy='',$orderBy='',$limit='')   {
-               $res = mysql_query($this->SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit), $this->link);
-               if ($this->debugOutput) $this->debug('exec_SELECTquery');
+               $query = $this->SELECTquery($select_fields,$from_table,$where_clause,$groupBy,$orderBy,$limit);
+               $res = mysql_query($query, $this->link);
+
+               if ($this->debugOutput) {
+                       $this->debug('exec_SELECTquery');
+               }
+               if ($this->explainOutput) {
+                       $this->explain($query, $this->sql_num_rows($res));
+               }
+
                return $res;
        }
 
@@ -1106,7 +1115,7 @@ class t3lib_DB {
 
        /**
         * Checks if recordset is valid and writes debugging inormation into devLog if not.
-        * 
+        *
         * @param       resource        $res    Recordset
         * @return      boolean <code>false</code> if recordset is not valid
         */
@@ -1130,6 +1139,92 @@ class t3lib_DB {
                return TRUE;
        }
 
+       /**
+        * Explain select queries
+        * If $this->explainOutput is set, SELECT queries will be explained here. Only queries with more than one possible result row will be displayed.
+        * The output is either printed as raw HTML output or embedded into the TS admin panel (checkbox must be enabled!)
+        *
+        * TODO: Feature is not DBAL-compliant
+        *
+        * @param       string          SQL query
+        * @param       integer         Number of resulting rows
+        * @return      boolean         True if explain was run, false otherwise
+        */
+       protected function explain($query,$row_count)   {
+
+               if ((int)$this->explainOutput==1 || ((int)$this->explainOutput==2 && t3lib_div::cmpIP(t3lib_div::getIndpEnv('REMOTE_ADDR'), $GLOBALS['TYPO3_CONF_VARS']['SYS']['devIPmask']))) {
+                       $explainMode = 1;       // raw HTML output
+               } elseif ((int)$this->explainOutput==3 && is_object($GLOBALS['TT'])) {
+                       $explainMode = 2;       // embed the output into the TS admin panel
+               } else {
+                       return false;
+               }
+
+               $error = $GLOBALS['TYPO3_DB']->sql_error();
+               $trail = t3lib_div::debug_trail();
+
+               $explain_output = array();
+               $res = $this->sql_query('EXPLAIN '.$query, $this->link);
+               if (is_resource($res)) {
+                       while ($tempRow = $this->sql_fetch_assoc($res)) {
+                               $explain_output[] = $tempRow;
+                       }
+                       $this->sql_free_result($res);
+               }
+
+               $indices_output = array();
+               if ($explain_output[0]['rows']>1 || t3lib_div::inList('ALL',$explain_output[0]['type'])) {
+                       $debug = true;  // only enable output if it's really useful
+
+                       $res = $this->sql_query('SHOW INDEX FROM '.$from_table, $this->link);
+                       if (is_resource($res)) {
+                               while ($tempRow = $this->sql_fetch_assoc($res)) {
+                                       $indices_output[] = $tempRow;
+                               }
+                               $this->sql_free_result($res);
+                       }
+               } else {
+                       $debug = false;
+               }
+
+               if ($debug) {
+                       if ($explainMode==1) {
+                               t3lib_div::debug('QUERY: '.$query);
+                               t3lib_div::debug(array('Debug trail:'=>$trail), 'Row count: '.$row_count);
+
+                               if ($error) {
+                                       t3lib_div::debug($error);
+                               }
+                               if (count($explain_output)) {
+                                       t3lib_div::debug($explain_output);
+                               }
+                               if (count($indices_output)) {
+                                       t3lib_div::debugRows($indices_output);
+                               }
+
+                       } elseif ($explainMode==2) {
+                               $data = array();
+                               $data['query'] = $query;
+                               $data['trail'] = $trail;
+                               $data['row_count'] = $row_count;
+
+                               if ($error) {
+                                       $data['error'] = $error;
+                               }
+                               if (count($explain_output)) {
+                                       $data['explain'] = $explain_output;
+                               }
+                               if (count($indices_output)) {
+                                       $data['indices'] = $indices;
+                               }
+                               $GLOBALS['TT']->setTSselectQuery($data);
+                       }
+                       return true;
+               }
+
+               return false;
+       }
+
 }
 
 
index af35c9b..1c318e4 100755 (executable)
@@ -217,15 +217,19 @@ class t3lib_timeTrack {
        /**
         * Set TSselectQuery - for messages in TypoScript debugger.
         *
-        * @param       string          Query string
+        * @param       array           Query array
         * @param       string          Message/Label to attach
         * @return      void
         */
-       function setTSselectQuery($query,$msg)  {
+       function setTSselectQuery(array $data,$msg='')  {
                end($this->currentHashPointer);
                $k = current($this->currentHashPointer);
 
-               $this->tsStackLog[$k]['selectQuery'][] = array('query'=>$query,'msg'=>$msg);
+               if (strlen($msg)) {
+                       $data['msg'] = $msg;
+               }
+
+               $this->tsStackLog[$k]['selectQuery'][] = $data;
        }
 
        /**
@@ -417,17 +421,7 @@ class t3lib_timeTrack {
                                }
                        }
                        if ($flag_queries && is_array($data['selectQuery'])) {
-                               reset($data['selectQuery']);
-                               while(list(,$v)=each($data['selectQuery'])) {
-                                       $res = $GLOBALS['TYPO3_DB']->sql_query('EXPLAIN '.$v['query']);
-                                       $v['mysql_error'] = $GLOBALS['TYPO3_DB']->sql_error();
-                                       if (!$GLOBALS['TYPO3_DB']->sql_error()) {
-                                               while($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
-                                                       $v['explain'][]=$row;
-                                               }
-                                       }
-                                       $msgArr[] = t3lib_div::view_array($v);
-                               }
+                               $msgArr[] = t3lib_div::view_array($data['selectQuery']);
                        }
                        if ($flag_content && strcmp($data['content'],'')) {
                                $maxlen = 120;
index 1887f13..8d8aff2 100755 (executable)
@@ -307,6 +307,13 @@ if ($TSFE->beUserLogin && $BE_USER->extAdmEnabled) {
        $TSFE->displayEditIcons = $BE_USER->extGetFeAdminValue('edit', 'displayIcons');
        $TSFE->displayFieldEditIcons = $BE_USER->extGetFeAdminValue('edit', 'displayFieldIcons');
 
+       if ($BE_USER->extGetFeAdminValue('tsdebug','displayQueries')) {
+               if ($GLOBALS['TYPO3_DB']->explainOutput == 0) {         // do not override if the value is already set in t3lib_db
+                               // Enable execution of EXPLAIN SELECT queries
+                       $GLOBALS['TYPO3_DB']->explainOutput = 3;
+               }
+       }
+
        if (t3lib_div::_GP('ADMCMD_editIcons')) {
                $TSFE->displayFieldEditIcons=1;
                $BE_USER->uc['TSFE_adminConfig']['edit_editNoPopup']=1;