Help Needed with DBTuner Explain Views

We encourage users to post events happening in the community to the community events group on https://www.drupal.org.
sah62's picture

I've been looking at what it takes to create a Drupal 7 version of the DB Tuner module. I've run into some database issues and I'm not familiar enough with the D7 database and views APIs to know how to fix the issues. Could someone please take a look at this code and explain what needs to be done to get it working with D7?

function dbtuner_explain_get_views() {
  // Disable Query Cache for this session temp
  $query_cache = db_query("SHOW VARIABLES WHERE Variable_name = :query", array(':query' => 'query_cache_type'))->fetchAssoc();
  $query_cache = $query_cache['Value'];
  db_query("SET SESSION query_cache_type = OFF");

  // Load up all views
  $views = views_get_all_views();
  $data = array();
  $output = '';
  foreach ($views as $key => $view) {
    // disabled views get nothing.
    if (!empty($view->disabled)) {
      unset($views[$key]);
      continue;
    }

    $view->init_display();
    // Check each view display
    foreach ($view->display as $display_id => $display) {
      $good = $view->set_display($display_id);
      if (!$good) {
        continue;
      }
      $filters = $view->get_items('filter', $display_id);
      $hash = $key . ' - ' . $display_id;

      // Run View
      $view->pre_execute();
      $view->execute();

      // Skip if view query ran in less then 9 ms
      if ($view->execute_time < 0.009) {
        $output .= $view->name.'('.$display_id.'): '.$view->execute_time . '<br />';
        continue;
      }

      // Prep view for explain
      $query = $view->build_info['query'];
      $query_args = $view->build_info['query_args'];
      $replacements = module_invoke_all('views_query_substitutions', $view);
      $query = str_replace(array_keys($replacements), $replacements, $query);
      $query_args = str_replace(array_keys($replacements), $replacements, $query_args);
      $view_arguments = $view->display_handler->options['arguments'];

      // can not handle view arguments very well ATM; skip.
      if ((empty($query) && count($view_arguments) < 1) || (count($view_arguments) > 0)) {
        array_unshift($data, array($hash => array(
            'time-build' => $view->build_time,
            'time-run' => $view->execute_time,
          )));
        continue;
      }

      $data[$hash]['time-build'] = $view->build_time;
      $data[$hash]['time-run'] = $view->execute_time;
      $data[$hash]['rows returned'] = count($view->result);
      $data[$hash]['query'] = "\n" . dbtuner_db_query($query, $query_args);

      // Add hash to query incase something goes wrong; you can figure out what view caused the error.
      $query .= ' /* ' . $hash . '*/';
      // TODO Please convert this statement to the D7 database API syntax.
      $explain = db_query('EXPLAIN ' . $query, $query_args);
      while ($value = $explain->fetchArray()) {
        $data[$hash][] = $value;
      }
    }
  }

  // Set query cache back to orginal state
  db_query("SET SESSION query_cache_type = :query", array(':query' => $query_cache));

  // Generate output
  if($data){
    $output .= t('Number of slow views: %num <br />', array('%num' => count($data)));
    $output .= str_replace('    ', '&nbsp;&nbsp;&nbsp;&nbsp;', nl2br(htmlentities(print_r($data, TRUE))));
  }else{
    $output .= t('You have no slow views!');
  }
  return $output;
}

Here is an example that describes the value of $query prior to calling "db_query('EXPLAIN ' . $query, $query_args);"

SELECT node.title AS node_title, node.nid AS nid
FROM
{node} node
LEFT JOIN {taxonomy_index} taxonomy_index ON node.nid = taxonomy_index.nid
LEFT JOIN {taxonomy_term_data} taxonomy_term_data_node ON taxonomy_index.tid = taxonomy_term_data_node.tid
WHERE (( (node.status = :db_condition_placeholder_0) AND (taxonomy_term_data_node.name LIKE :db_condition_placeholder_1 ESCAPE '\') ))
ORDER BY node_title ASC
LIMIT 30 OFFSET 0 /* articles - default*/

$view->build_info['query_args'] and $query_args are arrays with zero elements.

When the code runs now it produces a "PDOException: SQLSTATE[HY093]: Invalid parameter number: no parameters were bound" error when executing the "$explain = db_query('EXPLAIN ' . $query, $query_args);" statement. I think (and this is where I need help) this is because the filter values for the view need to be stored in $query_args and that's not happening. How can this code be rewritten to retrieve the filter values?

Views Developers

Group organizers

Group notifications

This group offers an RSS feed. Or subscribe to these personalized, sitewide feeds:

Hot content this week