Porting Sheetnode to Drupal 7

About a month ago, I started porting Sheetnode to D7. The natives were getting restless on the issue queue, so I thought I would pacify them with some serious porting effort. I am glad to announce that the port was completed a few days ago: Sheetnode 7.x-1.0-beta1 is now available, a fully-functional port of the latest D6 version.

The porting process was surprisingly smooth. I'd been avoiding porting my modules to D7 because it felt like rewriting the same code all over again - and I hate rework. But during this month, I got to learn many aspects of the D7 API, and it's not all bad :-) Here are some snippets of my experience:

• The Field API is actually cool. I liked that it carried forward the ideas of CCK pretty much verbatim, so porting all the Sheetnode CCK field code was relatively painless.
• The Token API is a huge improvement over D6. Specifically, it is now possible to define dynamic tokens, i.e. tokens that are dependent upon the object instance at run-time. Sheetnode needs this to expose tokens for single spreadsheet cells, given a cell coordinate (e.g. B2). In D6, the Token module required that all tokens be defined ahead of time , so I had to use a hack to implement this feature. But in D7, all I needed to do was declare the cell token to be of type dynamic:

/**
 * Implements hook_token_info().
 */
function sheetnode_token_info() {
  $info['tokens']['sheet']['cell'] = array(
    'name' => t('Cell reference'),
    'description' => t('A cell reference such as B52, C79, etc.'),
    'dynamic' => TRUE,
  );
  // more stuff here...
  return $info;
}

/**
 * Implements hook_tokens().
 */
function sheetnode_tokens($type, $tokens, array $data = array(), array $options = array()) {
  $replacements = array();
  if ($type == 'sheet' && !empty($data['sheet'])) {
    foreach ($tokens as $name => $original) {
      list($token, $coord) = explode(':', $name, 2);
      if ($token == 'cell' && $cell = _sheetnode_get_cell($data['sheet'], $coord)) {
        $replacements[$original] = $cell->value;
      }
    }
    return $replacements;
  }

• The Form API is also an improvement. AJAX support is finally sane :-) Attaching JS and CSS files to elements is a great idea.

• The Database API is a disappointment. It added complexity but gave nothing in return: simple queries are more difficult to write, and in one instance I had to split my query into two because the new API does not support the UPDATE ... SELECT idiom. I am sure that the new API caused a lot of teeth grinding from module maintainers.

• Because Sheetnode is JavaScript-heavy, I had to ensure that the SocialCalc engine and its Drupal integration script work correctly with the new Drupal themes. I spent a significant amount of time reworking the core SocialCalc engine to enhance its element positioning code. The outcome is a very flexible spreadsheet engine that can be embedded within absolute, relative or fixed containers and still behave correctly. Hint: element.getBoundingClientRect is magical. See for example the screenshot below of the template used in the Spreadsheet Views style plugin.

• One of the more interesting challenges was to make SocialCalc compatible with the jQuery UI dialog element, which is used in the Views admin interface. The problem was that the dialog element was capturing key events and not passing them to the spreadsheet engine. Furthermore, it was handling the ESC key to close the dialog, whereas in SocialCalc ESC is used to cancel editing a single cell. The solution was to read the code of the jQuery UI library and undo some of its settings during initialization of the spreadsheet engine:

 // If we're in a jQuery UI dialog, disable closeOnEscape and unbind the keypress event that interferes with our keyboard handling.
  if ($('.ui-dialog-content').length) {
    $('.ui-dialog-content').dialog('option', 'closeOnEscape', false);
    $(document).unbind('keydown.dialog-overlay');
  }

All in all, I had fun porting Sheetnode to D7. But then again, maybe it's because my expectation was so low to start with :-)