function webform_update_7416 in Webform 7.4

Add columns for serial numbered submissions. Add serial numbers to existing submissions.

File

./webform.install, line 1950

Webform module install/schema hooks.

Code

function webform_update_7416() {

  // SQL database implementations vary in how DDL statements are handled within
  // transactions. Since this update routine should be run in maintenance mode
  // without concurrent transactions, no DDL statements are executed within a
  // transaction.
  // Add next_serial column to webform.
  $spec = array(
    'description' => 'The serial number to give to the next submission to this webform.',
    'type' => 'int',
    'unsigned' => TRUE,
    'not null' => TRUE,
    'default' => 1,
  );
  if (!db_field_exists('webform', 'next_serial')) {
    db_add_field('webform', 'next_serial', $spec);
  }

  // Add serial column to webform_submissions.
  $spec = array(
    'description' => 'The serial number of this submission.',
    'type' => 'int',
    'unsigned' => TRUE,
  );
  if (!db_field_exists('webform_submissions', 'serial')) {
    db_add_field('webform_submissions', 'serial', $spec);
  }

  // Begin a transaction for updating the serial numbers. The transaction will
  // commit when $transaction is unset or goes out-of-scope.
  $transaction = db_transaction();

  // Delete stray entries from the Webform tables before adding serial numbers.
  db_query("DELETE FROM {webform_submissions} WHERE nid NOT IN (SELECT nid FROM {webform})");
  db_query("DELETE FROM {webform_submitted_data} WHERE nid NOT IN (SELECT nid FROM {webform})");

  // Add serial numbers to all submissions.
  // Repeat for every Webform.
  $nids = db_select('webform', 'w')
    ->fields('w', array(
    'nid',
  ))
    ->execute();
  while ($nid = $nids
    ->fetchColumn()) {
    $serial = 1;

    // Repeat for every submission in this Webform.
    $sids = db_select('webform_submissions', 'ws')
      ->forUpdate()
      ->fields('ws', array(
      'sid',
    ))
      ->condition('nid', $nid)
      ->orderBy('sid')
      ->execute();
    while ($sid = $sids
      ->fetchColumn()) {

      // Set the serial number.
      db_update('webform_submissions')
        ->fields(array(
        'serial' => $serial,
      ))
        ->condition('nid', $nid)
        ->condition('sid', $sid)
        ->execute();
      $serial++;
    }

    // Set the next serial number.
    db_update('webform')
      ->fields(array(
      'next_serial' => $serial,
    ))
      ->condition('nid', $nid)
      ->execute();
  }

  // Commit the transaction.
  unset($transaction);

  // Now that every submission has a serial number, make serial numbers required.
  $spec['not null'] = TRUE;
  $keys = array();

  // Create a unique index if it does not already exist.
  if (!db_index_exists('webform_submissions', 'nid_serial')) {
    $keys = array(
      'unique keys' => array(
        'nid_serial' => array(
          'nid',
          'serial',
        ),
      ),
    );
  }
  db_change_field('webform_submissions', 'serial', 'serial', $spec, $keys);
  return t('Columns for serial numbered submissions successfully added. Serial numbers added to existing submissions.');
}