webform_mysql_views.module in Webform MySQL Views 6

Same filename and directory in other branches
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

// $Id$

/**
 * @file
 * 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>
 */

/**
 * Defines used by the module.
 *
 * Back-ported from 6.x-2.x
 */
define('WEBFORM_MYSQL_VIEWS_MAXSIZE', 64);

/**
 * Implementation of 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;
}

/**
 * Implementation of 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);
}

/**
 * Implementation of 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);
  }

  //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);
  }
}

/**
 * Implementation of 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' => $node->title,
      '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();
  }
}

/**
 * Given a webform node ID, build an SQL query that will create a flat MySQL view
 * of subnissions 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.
 */
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.
  $result = db_query('SELECT c.cid, c.form_key, c.type FROM {webform_component} c WHERE c.nid = %d AND c.type != "fieldset" ORDER BY c.weight ASC, c.cid ASC', $nid);
  $components = '';
  while ($row = db_fetch_array($result)) {
    switch ($row['type']) {

      //Collapse grid values in a string of key=val pairs
      case 'grid':
        $component = sprintf("(SELECT GROUP_CONCAT(no,'=',data) FROM {webform_submitted_data} AS child WHERE child.sid = parent.sid AND cid = %d) AS `%s`, ", $row['cid'], $row['form_key']);
        break;

      //Otherwise collapse multi-value fields into simple comma-separated lists
      default:
        $component = sprintf("(SELECT GROUP_CONCAT(data) FROM {webform_submitted_data} AS child WHERE child.sid = parent.sid AND cid = %d) AS `%s`, ", $row['cid'], $row['form_key']);
    }
    $components .= $component;
  }
  $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 sprintf($query, $view_name, $nid);
}

/**
 * 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 string $dbview_name 	name of the database view
 * @param string $pseudo_pk			name of the column acting as primary key
 * @param array $views_data 		array passed into hook_views_data_alter
 * @return bool	false on error
 */
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;
}

/**
 * Implementation of hook_views_data_alter().
 */
function webform_mysql_views_views_data_alter(&$views) {
  if (module_exists('data')) {

    // expose the webform_mysql_views by designating a "pseudo"-primary key
    $webform_mysql_views = variable_get('webform_mysql_views_views', array());
    foreach ($webform_mysql_views as $nid => $dbview_name) {

      // sid (submission id) is unique
      _webform_mysql_views_designate_primarykey($dbview_name, 'sid', $views);

      // added to convert the submitted field into a date field handler.
      $views[$dbview_name]['submitted'] = array(
        'title' => t('submitted'),
        'help' => t('The date and time the webform was submitted.'),
        'field' => array(
          'handler' => 'views_handler_field_date',
          'click sortable' => TRUE,
        ),
        'sort' => array(
          'handler' => 'views_handler_sort_date',
        ),
        'filter' => array(
          'handler' => 'views_handler_filter_date',
        ),
        'argument' => array(
          'handler' => 'views_handler_argument_date',
        ),
      );
    }
  }
}
Functions

Name	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	Given a webform node ID, build an SQL query that will create a flat MySQL view of subnissions for that webform
webform_mysql_views_component_submit	Submit handler for the webform component edit/delete forms.
webform_mysql_views_drop	Drops the view belonging to the specified nid, if any
webform_mysql_views_form_alter	Implementation of 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	Implementation of hook_help().
webform_mysql_views_menu	Implementation of hook_menu().
webform_mysql_views_nodeapi	Implementation of 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_data_alter	Implementation of hook_views_data_alter().
_webform_mysql_views_designate_primarykey	Designate a column of a mysql view as primary key. This is required to expose a view via views (and data.module).
Constants

Name	Description
WEBFORM_MYSQL_VIEWS_MAXSIZE	Defines used by the module.
You are here

webform_mysql_views.module in Webform MySQL Views 6

File

Functions

Constants

API Navigation
