You are here

Home » API reference » Webform MySQL Views 6.2

webform_mysql_views.module in Webform MySQL Views 6.2

Same filename and directory in other branches
  1. 6 webform_mysql_views.module
  2. 7 webform_mysql_views.module

webform_mysql_views.module

The Webform MySQL Views module allows you to automatically build flattened MySQL views of submitted Webform module data, for convenient use by external applications.

@author Andy Chase <andychase@gmail.com>

File

webform_mysql_views.module
View source 
<?php

/**
 * @file webform_mysql_views.module
 *
 * The Webform MySQL Views module allows you to automatically build flattened
 * MySQL views of submitted Webform module data, for convenient use by external
 * applications.
 *
 * @author Andy Chase <andychase@gmail.com>
 */
define('WEBFORM_MYSQL_VIEWS_MAXSIZE', 64);
define('WEBFORM_MYSQL_VIEWS_PARENT_SIZE', 8);

/**
 * Implements hook_menu().
 */
function webform_mysql_views_menu() {
  $items = array();

  // Submissions listing.
  $items['admin/content/webform/webform'] = array(
    'title' => 'Webforms',
    'page callback' => 'webform_admin_content',
    'access callback' => 'user_access',
    'access arguments' => array(
      'access all webform results',
    ),
    'description' => 'View and edit all the available webforms on your site.',
    'file' => 'includes/webform.admin.inc',
    'file path' => drupal_get_path('module', 'webform'),
    'type' => MENU_DEFAULT_LOCAL_TASK,
    'weight' => 0,
  );

  // MySQL Views settings
  $items['admin/content/webform/mysql'] = array(
    'title' => 'MySQL Views',
    'page callback' => 'drupal_get_form',
    'page arguments' => array(
      'webform_mysql_views_admin_form',
    ),
    'access callback' => 'user_access',
    'access arguments' => array(
      'access all webform results',
    ),
    'description' => 'Create MySQL views of submitted Webform data.',
    'type' => MENU_LOCAL_TASK,
    'weight' => 2,
  );
  return $items;
}

/**
 * Implements hook_form_alter().
 */
function webform_mysql_views_form_alter(&$form, &$form_state, $form_id) {
  switch ($form_id) {
    case 'webform_components_form':
    case 'webform_component_edit_form':
    case 'webform_component_delete_form':
      $form['#submit'][] = 'webform_mysql_views_component_submit';
  }
}

/**
 * Submit handler for the webform component edit/delete forms.
 */
function webform_mysql_views_component_submit($form, $form_state) {

  //If this node has a MySQL view, update it.
  switch ($form_state['values']['form_id']) {
    case 'webform_components_form':
    case 'webform_component_edit_form':
      $nid = $form_state['values']['nid'];
      break;
    case 'webform_component_delete_form':
      $nid = $form_state['values']['node']->nid;
      break;
  }
  webform_mysql_views_rebuild($nid);
}

/**
 * Implements hook_nodeapi().
 */
function webform_mysql_views_nodeapi(&$node, $op, $a3 = NULL, $a4 = NULL) {

  // If node is being deleted, check to see if it has a corresponding MySQL view
  // and if so, drop it.
  if ($op == 'delete') {
    webform_mysql_views_drop($node->nid);
  }
}

/**
 * Rebuild the view for the specified nid, if any. If $add_new is TRUE, will
 * build a new view even if an existing one is not found.
 *
 * @param $nid
 *   The node ID of the webform whose view should be rebuilt.
 *
 * @param boolean $add_new
 *   Indicates whether or not view should be added if it does not already exist.
 */
function webform_mysql_views_rebuild($nid, $add_new = FALSE) {
  global $db_prefix;
  $views = variable_get('webform_mysql_views_views', array());
  $name = NULL;
  if (empty($views[$nid]) && $add_new) {

    //Get a name for the new view
    $node = node_load($nid);
    $name = webform_mysql_views_get_view_name($node->title, $nid);
    $views[$nid] = $db_prefix . $name;
    variable_set('webform_mysql_views_views', $views);
  }
  if ($views[$nid]) {

    // Remove the $db_prefix from the view name so we don't wind up with double
    // db_prefixes:
    if ($db_prefix) {
      $name = substr($views[$nid], strlen($db_prefix));
    }
    else {
      $name = $views[$nid];
    }
    $query = webform_mysql_views_build_query($nid, $name);
    db_query($query, $name, $nid);
  }

  // If $add_new was TRUE, return the name of the newly created view.
  if ($name) {
    return $db_prefix . $name;
  }
}

/**
 * Drops the view belonging to the specified nid, if any
 *
 * @param $nid
 *   The node ID of the webform whose view should be dropped.
 */
function webform_mysql_views_drop($nid) {
  $views = variable_get('webform_mysql_views_views', array());
  if (!empty($views[$nid])) {
    db_query("DROP VIEW {%s}", $views[$nid]);
    unset($views[$nid]);
    variable_set('webform_mysql_views_views', $views);
  }
}

/**
 * Implements hook_help().
 */
function webform_mysql_views_help($path) {
  switch ($path) {
    case 'admin/help#webform_mysql_views':
      return '<p>' . t("This module builds flattened, read-only MySQL views of webform submission data. These views may be useful when you need to  access this data from an external application in an automated fashion without exporting, importing, or the use of a web-based API.") . '</p><p>' . t("To manage MySQL views for your webforms, log in as an administrator and go to the <code>Administer &gt; Content Management &gt; Web Forms</code> page and click on the <code>MySQL Views</code> tab.") . '</p><p>' . t("<h3 id=\"requirements\">Requirements</h3><ul><li>Your Drupal database must be using the MySQL backend.</li><li>Your MySQL server must be version 5.0 or later</li><li>The MySQL user specified in your Drupal <code>settings.php</code> file must have permission to create views.</li><li>Webform Module</li><li>Elements Module</li></ul>");
    case 'admin/content/webform/mysql':
      return '<p>' . t("This page lets you add or remove MySQL views containing submission data for your Webforms.") . '</p>' . '<p>' . t("To add a MySQL view for a Webform, check the box next to its name and submit the form") . '</p>' . '<p>' . t("To remove a MySQL view for a Webform, uncheck the box next to its name and submit the form.") . '</p>' . '<p>' . t("Note: <a href=\"http://dev.mysql.com/tech-resources/articles/mysql-views.html\">MySQL Views</a> should not be confused with the <a href=\"http://drupal.org/project/views\"> Drupal Views module</a>.") . '</p>';
  }
}

/**
 * Form builder for the Webform MySQL Views admin form.
 *
 * Implemented as its own admin page instead of integrating with the node
 * form because of chicken-and-egg logic hassles.
 *
 * @see webform_mysql_views_admin_form_submit()
 * @ingroup forms
 */
function webform_mysql_views_admin_form() {

  // Get list of webform-enabled content types
  $types = webform_variable_get('webform_node_types', array());

  // Create query placeholders for these types.
  $placeholders = db_placeholders($types, 'varchar');

  //Get list of nids that already have views
  $views = variable_get('webform_mysql_views_views', array());
  $view_nids = array_keys($views);

  //Get list of all webform nodes
  $query = db_rewrite_sql('SELECT nid, title, type, status FROM {node} AS n WHERE type IN (' . $placeholders . ') ORDER BY nid ASC');
  $result = db_query($query, $types);
  $options = array();
  $titles = array();
  $default_value = array();
  while ($node = db_fetch_object($result)) {
    $titles[$node->nid] = $node->title;
    if (in_array($node->nid, $view_nids)) {
      $default_value[$node->nid] = $node->nid;
    }
    if (in_array($node->nid, $view_nids)) {
      $view_str = $views[$node->nid];
    }
    else {
      $view_str = t('-');
    }
    $options[$node->nid] = array(
      'nid' => $node->nid,
      'title' => l($node->title, 'node/' . $node->nid),
      'type' => $node->type,
      'status' => $node->status ? t('Published') : t('Unpublished'),
      'view' => $view_str,
    );
  }
  $form = array();

  //Pass the titles along
  $form['titles'] = array(
    '#type' => 'value',
    '#value' => $titles,
  );
  $header = array(
    'nid' => t('Node ID'),
    'title' => t('Webform Name'),
    'type' => t('Node Type'),
    'status' => t('Node Status'),
    'view' => t('View Name'),
  );
  $form['help'] = array(
    '#value' => t("Select the webform(s) you would like to generate MySQL views for. To drop a webform's MySQL view, uncheck its box and click 'Submit'."),
  );

  //TODO: Display 'No webforms defined' error instead of tableselect if $options is empty.
  $form['nodes'] = array(
    '#type' => 'tableselect',
    '#header' => $header,
    '#options' => $options,
    '#default_value' => $default_value,
  );
  $form['submit'] = array(
    '#type' => 'submit',
    '#value' => t('Submit'),
  );
  return $form;
}

/**
 * Form submission handler for the admin form.
 *
 * @see webform_mysql_views_admin_form()
 */
function webform_mysql_views_admin_form_submit($form, &$form_state) {
  global $db_prefix;

  //Load existing view info
  $webform_views = $previous_webform_views = variable_get('webform_mysql_views_views', array());
  $webform_view_nids = array_keys($webform_views);

  //Parse submitted settings;
  $submitted_nids = array();
  foreach ($form_state['values']['nodes'] as $key => $val) {
    if ($key == $val) {
      $submitted_nids[] = $key;
    }
  }

  //If there are any nids in the saved array not in the newly submitted one, drop those views.
  $drop_nids = array_diff($webform_view_nids, $submitted_nids);
  foreach ($drop_nids as $nid) {
    $query = "DROP VIEW {%s}";

    // (Drop the prefix from the stored view name and let the DB API handle it)
    db_query($query, substr($webform_views[$nid], strlen($db_prefix)));
    unset($webform_views[$nid]);
  }
  if (count($drop_nids)) {
    drupal_set_message(format_plural(count($drop_nids), 'Dropped 1 view.', 'Dropped @count views.', array(
      '@count' => count($drop_nids),
    )));
  }

  //If there are any nids in the new array not in the saved one, create new views
  $new_nids = array_diff($submitted_nids, $webform_view_nids);
  foreach ($new_nids as $nid) {
    $webform_views[$nid] = webform_mysql_views_rebuild($nid, TRUE);
  }
  if (count($new_nids)) {
    drupal_set_message(format_plural(count($new_nids), 'Created 1 view.', 'Created @count views.', array(
      '@count' => count($drop_nids),
    )));
  }

  //Save the webform_views variable
  variable_set('webform_mysql_views_views', $webform_views);

  //Data module integration
  if (module_exists('data')) {
    data_include('DataTable');

    // adopt new mysql views
    foreach ($new_nids as $nid) {
      $webform_mysql_view = $webform_views[$nid];
      $table = DataTable::instance($webform_mysql_view);
      if ($table
        ->adopt()) {
        drupal_set_message(t('Data module has adopted view @webform_mysql_view.', array(
          '@webform_mysql_view' => $webform_mysql_view,
        )));
      }
      else {
        drupal_set_message(t('Data module was unable to adopt view @webform_mysql_view.', array(
          '@webform_mysql_view' => $webform_mysql_view,
        )), 'error');
      }
      unset($table);
    }

    // drop old mysql views from data.module
    foreach ($drop_nids as $nid) {
      $webform_mysql_view = $previous_webform_views[$nid];
      $table = DataTable::instance($webform_mysql_view);
      if ($table
        ->drop()) {
        drupal_set_message(t('Data module has dropped view @webform_mysql_view.', array(
          '@webform_mysql_view' => $webform_mysql_view,
        )));
      }
      else {
        drupal_set_message(t('Data module was unable to drop view @webform_mysql_view.', array(
          '@webform_mysql_view' => $webform_mysql_view,
        )), 'error');
      }
      unset($table);
    }
    DataTable::clearCaches();
  }
}

/**
 * Build an SQL query to create a flat view.
 *
 * Given a webform node ID, build an SQL query that will create a flat MySQL
 * view of submissions for that webform
 *
 * @param $nid
 *   The node ID of the webform this view is being built for.
 *
 * @param $view_name
 *   The name to use for this view in the MySQL database.
 *
 * @todo Generate a query that uses placeholders, not one that concatenates
 *   variables and uses sprintf.
 */
function webform_mysql_views_build_query($nid, $view_name) {

  // Build query with individual rows instead of GROUP_CONCAT, because
  // the MySQL server group_concat_max_length setting is usually limited to
  // 1024 characters, and it's easier to work around that here than tinkering
  // with the my.cnf file.
  $components = '';
  $result = db_query("SELECT c.cid, c.form_key, p.form_key AS parent_key FROM {webform_component} AS c LEFT JOIN {webform_component} AS p ON (c.pid = p.cid) WHERE c.nid = %d AND c.type != 'fieldset' ORDER BY c.weight ASC, c.cid ASC", $nid);
  while ($row = db_fetch_array($result)) {

    // To allow fields with identical keys (but in different fieldsets) and
    // handle keys over 64 characters in length, use a helper to generate
    // sensible column names for the view (including fieldset form keys)
    $column = _webform_mysql_views_column_name($row);
    $components .= db_result(db_query('SELECT CONCAT("(SELECT GROUP_CONCAT(data) FROM {webform_submitted_data} AS child WHERE child.sid = parent.sid AND cid = ", c.cid,") AS ", \'%s\', ", ") FROM {webform_component} c WHERE c.cid = %d', $column, $row['cid']));
  }
  $query = "CREATE OR REPLACE VIEW {%s} AS SELECT parent.sid, s.uid," . $components . ' s.submitted, s.remote_addr FROM {webform_submitted_data} AS parent JOIN {webform_submissions} s ON s.sid = parent.sid WHERE parent.nid = %d GROUP BY parent.sid ORDER BY parent.sid DESC';
  return $query;
}

/**
 * Generate a sane (and unique) data column name for a view.
 */
function _webform_mysql_views_column_name($data) {

  // A column name can be 64 characters max. We shall use the field key, then
  // append the component id. Supporting up to 999 components. If you need
  // more than that you may be doin' it wrong.
  if (empty($data['parent_key'])) {
    return sprintf("%s_%d", substr($data['form_key'], 0, WEBFORM_MYSQL_VIEWS_MAXSIZE - 4), $data['cid']);
  }
  else {
    return sprintf("%s_%s_%d", substr($data['form_key'], 0, WEBFORM_MYSQL_VIEWS_MAXSIZE - WEBFORM_MYSQL_VIEWS_PARENT_SIZE - 5), substr($data['parent_key'], 0, WEBFORM_MYSQL_VIEWS_PARENT_SIZE), $data['cid']);
  }
}

/**
 * Get a unique view name from a given string and node ID.
 *
 * @param $title
 *   The string from which to build the view name.
 *
 * @param $nid
 *   The node ID from which to build the view name.
 */
function webform_mysql_views_get_view_name($title, $nid) {
  global $db_url, $db_prefix;

  //Discard non-alphanumeric chars
  $title = strtolower(str_replace(' ', '_', $title));

  // Remove multiple concurrent underscores to tidy up the name.
  $title = preg_replace('/__+/', '_', $title);
  $title = 'webform_views_' . preg_replace('/[^a-z0-9_]/', '', $title);

  // Limit the title to the maximum allowed table name length in MySQL.
  $title = substr($title, 0, WEBFORM_MYSQL_VIEWS_MAXSIZE);
  $db_name = substr(parse_url($db_url, PHP_URL_PATH), 1);

  // Check whether the default view name is already being used
  // (For example duplicate node titles). Append $nid if necessary to ensure
  // uniqueness.  Table names not escaped as they are not a part of the Drupal DB.
  $view_exists = db_result(db_query("SELECT COUNT(table_name) AS view_exists FROM information_schema.views where table_schema = '%s' AND table_name = '%s%s'", $db_name, $db_prefix, $title));
  if (!empty($view_exists)) {
    $title = substr($title, 0, WEBFORM_MYSQL_VIEWS_MAXSIZE - strlen($nid) - 1) . '_' . $nid;
  }
  return $title;
}

/**
 * Designate a column of a mysql view as primary key.
 *
 * This is required to expose a view via views (and data.module).
 *
 * @param $dbview_name
 *   Name of the database view
 * @param $pseudo_pk
 *   Name of the column acting as primary key
 * @param $views_data
 *   Array passed into hook_views_data_alter
 *
 * @return
 *   Boolean
 */
function _webform_mysql_views_designate_primarykey($dbview_name, $pseudo_pk, &$views_data) {
  $tables = data_get_all_tables();

  // Sanity checking
  if (!isset($tables[$dbview_name]) || !isset($views_data[$dbview_name])) {
    return false;
  }
  if (!isset($views_data[$dbview_name][$pseudo_pk])) {
    return false;
  }
  $table = $tables[$dbview_name];

  // Expose the database view as a base table for views.
  $views_data[$dbview_name]['table']['base'] = array(
    'field' => $pseudo_pk,
    'title' => $table
      ->get('title'),
    'help' => t('Data table for webform_mysql_view @dbview_name', array(
      '@dbview_name' => $dbview_name,
    )),
    'weight' => 10,
  );

  // In order to avoid schema.module getting confused, we do not actually set the primary key.
  // data_ui_views_default_views() will though not pick be able up on the view.
  // You will have to create your own "default" drupal view.
  return true;
}

/**
 * Implements hook_views_api().
 */
function webform_mysql_views_views_api() {
  return array(
    'api' => 2,
    'path' => drupal_get_path('module', 'webform_mysql_views') . '/views',
  );
}

/**
 * Implement hook_date_api_tables().
 */
function webform_mysql_views_date_api_tables() {
  return variable_get('webform_mysql_views_views', array());
}

/**
 * Implements hook_date_api_fields().
 *
 * It would appear that the views field handler *MUST* be set to
 * views_handler_field_date for this hook to be triggered, but that
 * said field handler cannot interpret this VARCHAR database field
 * as containing date information, so on display it'll default to
 * 1970-01-01. Oops.
 */
function webform_mysql_views_date_api_fields($field) {
  static $columns;
  $values = array(
    'type' => 'custom',
    'sql_type' => 'date',
    'granularity' => array(
      'year',
      'month',
      'day',
    ),
    'custom' => array(
      'type' => 'custom',
      'sql_type' => 'date',
    ),
  );

  // Charge the webforms date columns cache.
  if (empty($columns)) {
    $columns = array();
    $webform_mysql_views = variable_get('webform_mysql_views_views', array());
    foreach ($webform_mysql_views as $nid => $dbview_name) {

      // Set the correct label on the fields.
      $result = db_query('SELECT c.cid, c.name, c.form_key, p.form_key AS parent_key, p.name AS parent, c.type FROM {webform_component} c LEFT JOIN {webform_component} p ON (c.pid = p.cid) WHERE c.nid = %d AND c.type = "date" ORDER BY c.weight ASC, c.cid ASC', $nid);
      while ($row = db_fetch_array($result)) {
        $column = _webform_mysql_views_column_name($row);
        $columns[$column] = $dbview_name . '.' . $column;
      }
    }
  }

  // Check the cache of webform date columns.
  foreach ($columns as $name => $full_name) {
    if ($field == $full_name) {
      return $values;
    }
  }
}

/**
 * Returns a list of select options.
 *
 * This function is a straight copy of a helper from the webform select
 * component, which is sadly not available globally.
 *
 * @see _webform_select_options_from_text()
 *
 * @param $nid
 *   A webform node nid.
 * @param $cid
 *   A webform component cid.
 * @return 
 *   An options array.
 */
function _webform_mysql_views_select_options($nid, $cid) {
  $component = unserialize(db_result(db_query("SELECT extra FROM {webform_component} WHERE nid = %d AND cid = %d", $nid, $cid)));
  $flat = FALSE;
  static $option_cache = array();

  // Keep each processed option block in an array indexed by the MD5 hash of
  // the option text and the value of the $flat variable.
  $md5 = md5($component['items']);

  // Check if this option block has been previously processed.
  if (!isset($option_cache[$flat][$md5])) {
    $options = array();
    $rows = array_filter(explode("\n", trim($component['items'])));
    $group = NULL;
    foreach ($rows as $option) {
      $option = trim($option);

      /**
       * If the Key of the option is within < >, treat as an optgroup
       *
       * <Group 1>
       *   creates an optgroup with the label "Group 1"
       *
       * <>
       *   Unsets the current group, allowing items to be inserted at the root element.
       */
      if (preg_match('/^\\<([^>]*)\\>$/', $option, $matches)) {
        if (empty($matches[1])) {
          unset($group);
        }
        elseif (!$flat) {
          $group = $filter ? _webform_filter_values($matches[1], NULL, NULL, NULL, FALSE) : $matches[1];
        }
      }
      elseif (preg_match('/^([^|]+)\\|(.*)$/', $option, $matches)) {
        $key = $filter ? _webform_filter_values($matches[1], NULL, NULL, NULL, FALSE) : $matches[1];
        $value = $filter ? _webform_filter_values($matches[2], NULL, NULL, NULL, FALSE) : $matches[2];
        isset($group) ? $options[$group][$key] = $value : ($options[$key] = $value);
      }
      else {
        $filtered_option = $filter ? _webform_filter_values($option, NULL, NULL, NULL, FALSE) : $option;
        isset($group) ? $options[$group][$filtered_option] = $filtered_option : ($options[$filtered_option] = $filtered_option);
      }
    }
    $option_cache[$flat][$md5] = $options;
  }

  // Return our options from the option_cache array.
  return $option_cache[$flat][$md5];
}

Functions

Namesort descending Description
webform_mysql_views_admin_form Form builder for the Webform MySQL Views admin form.
webform_mysql_views_admin_form_submit Form submission handler for the admin form.
webform_mysql_views_build_query Build an SQL query to create a flat view.
webform_mysql_views_component_submit Submit handler for the webform component edit/delete forms.
webform_mysql_views_date_api_fields Implements hook_date_api_fields().
webform_mysql_views_date_api_tables Implement hook_date_api_tables().
webform_mysql_views_drop Drops the view belonging to the specified nid, if any
webform_mysql_views_form_alter Implements hook_form_alter().
webform_mysql_views_get_view_name Get a unique view name from a given string and node ID.
webform_mysql_views_help Implements hook_help().
webform_mysql_views_menu Implements hook_menu().
webform_mysql_views_nodeapi Implements hook_nodeapi().
webform_mysql_views_rebuild Rebuild the view for the specified nid, if any. If $add_new is TRUE, will build a new view even if an existing one is not found.
webform_mysql_views_views_api Implements hook_views_api().
_webform_mysql_views_column_name Generate a sane (and unique) data column name for a view.
_webform_mysql_views_designate_primarykey Designate a column of a mysql view as primary key.
_webform_mysql_views_select_options Returns a list of select options.

Constants

Namesort descending Description
WEBFORM_MYSQL_VIEWS_MAXSIZE @file webform_mysql_views.module
WEBFORM_MYSQL_VIEWS_PARENT_SIZE