There's a module for that! Drupal architecture and module development

I wrote last time about the latest developments to my Views Auto-Refresh module, which periodically refreshes a Views page, either by reloading the whole view, or by incrementally inserting new items only. It's a useful tool for activity streams and other Twitter-like, real-time lists.

Still, I had a nagging feeling that my code was endangering the server. Consider this: every 15 seconds, each connected browser invokes a full Drupal bootstrap plus a full View render, just to ask the server if there are new items. This doesn't sound right.

So I came up with a solution: before invoking the secondary View refresh, the client code can use a "ping URL" that more efficiently determines whether there are new items or not. If yes, then full View render is invoked. The idea of the ping URL is that it can be implemented with any technology, not necessarily Drupal - and can be arbitrarily optimized.

The result is now committed to the latest Views Hacks dev. Here's how to use it:

An additional theme('views_autorefresh') parameter called ping_base_path is used to inform Views Auto-Refresh that some URL should be hit before the secondary view is invoked:

print theme('views_autorefresh', 5000, views_get_current_view(), array(
  'view_base_path' => 'liveblog/autorefresh', // the path of the update view
  'view_display_id' => 'page_2', // the display of the update view
  'view_name' => 'liveblog', // the name of the update view
  'sourceSelector' => '.view-content', // selector for items container in update view
  'targetSelector' => '.view-content', // selector for items container in on-page view
  'firstClass' => 'first', // class name for first element (optional),
  'lastClass' => 'last', // class name for last element (optional),
  'oddClass' => 'odd', // class name for odd elements (optional)
  'evenClass' => 'even', // class name for even elements (optional)
  'ping_base_path' => drupal_get_path('module', 'liveblog') . '/liveblog.php?type=report, // ping URL (optional)
));

The script at this ping URL should accept a GET parameter called timestamp and return a JSON response of the following form:

where $count is 0 if there are no new items since timestamp, > 0 otherwise.

For example, here's a complete ping script:

require('../../../default/settings.php');

global $db_url;
$p = parse_url($db_url);
$mysql = mysql_pconnect($p['host'], $p['user'], @$p['pass']);
if (!$mysql) die('Could not connect to database server.');
mysql_set_charset('utf8', $mysql);
if (!mysql_select_db(str_replace('/', '', $p['path']), $mysql)) die('Could not select database.');

$timestamp = mysql_real_escape_string($_REQUEST['timestamp']);
$type = mysql_real_escape_string($_REQUEST['type']);
$sql = "SELECT COUNT(nid) FROM node WHERE type='" . $type .  "' AND created > " . $timestamp;
if (!empty($_REQUEST['uid'])) {
  $uid = mysql_real_escape_string($_REQUEST['uid']);
  $sql .= " AND uid = " . $uid;
}
$count = mysql_result(mysql_query($sql, $mysql), 0);

print json_encode(array(
  'pong' => $count,
));

This script illustrates parsing the Drupal MySQL connection string, receiving a number of parameters including timestamp, and returning the expected JSON response.

Two considerations are important in this script:

1. Query parameters should be sanitized before they're used in the SQL query, to avoid SQL injections and other nasty surprises.
2. The ping SQL query does not have to emulate the Views query exactly. If you make the ping query broader, it will create false positives, whereby the View render is invoked but no actual new items are found. If you make the ping query more restricted, it will create false negatives: no new items will be returned whereas the View would have returned new items. You probably want to avoid this last condition.

That's it! The results on my test server were encouraging: The auto-refresh round-trip time was reduced by an order of magnitude. I haven't measured CPU savings though - I leave it as the proverbial "exercise to the reader" :-)