database_mysql_dump.inc in Demonstration site (Sandbox / Snapshot) 7

File

database_mysql_dump.inc

<?php

/**
 * @file
 * Some older mysql client libs are missing this constant.
 */
if (!defined('MYSQLI_BINARY_FLAG')) {
  define('MYSQLI_BINARY_FLAG', 128);
}

/**
 * Dump active database.
 *
 * @param $filename
 *   The filename including path to write the dump to.
 * @param $options
 *   An associative array of snapshot options, as described in demo_dump().
 */
function demo_dump_db($filename, $options = array()) {

  // Make sure we have permission to save our backup file.
  $directory = dirname($filename);
  if (!file_prepare_directory($directory, FILE_CREATE_DIRECTORY)) {
    return FALSE;
  }
  if ($fp = fopen($filename, 'wb')) {
    $header = array();
    $header[] = '-- Demo module database dump';
    $header[] = '-- Version ' . DEMO_DUMP_VERSION;
    $header[] = '-- http://drupal.org/project/demo';
    $header[] = '--';
    $header[] = '-- Database: ' . _demo_get_database();
    $header[] = '-- Date: ' . format_date(REQUEST_TIME, 'small');
    $header[] = '-- Server version: ' . db_query('SELECT version()')
      ->fetchField();
    $header[] = '-- PHP version: ' . PHP_VERSION;
    $header[] = '-- Drupal version: ' . VERSION;

    // Avoid auto value for zero values (required for user id 0).
    $header[] = '';
    $header[] = 'SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";';

    // Temporarily disable foreign key checks for the time of import.
    $header[] = 'SET FOREIGN_KEY_CHECKS = 0;';
    $header[] = '';

    // Set collations for the import. PMA and mysqldump use conditional comments
    // to exclude MySQL <4.1, but D6 requires >=4.1.
    $header[] = 'SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;';
    $header[] = 'SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;';
    $header[] = 'SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;';
    $header[] = 'SET NAMES utf8;';
    $header[] = '';
    fwrite($fp, implode("\n", $header));
    foreach ($options['tables'] as $table => $dump_options) {
      if (!_demo_table_is_view($table)) {
        if ($dump_options['schema']) {
          _demo_dump_table_schema($fp, $table);
        }
        if ($dump_options['data']) {
          _demo_dump_table_data($fp, $table);
        }
      }
    }
    $footer = array();
    $footer[] = '';

    // Re-enable foreign key checks.
    $footer[] = 'SET FOREIGN_KEY_CHECKS = 1;';

    // Revert collations for potential subsequent database queries not belonging
    // to this module.
    // @todo Double-check this behavior according to the results of
    //   http://drupal.org/node/772678
    $footer[] = 'SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;';
    $footer[] = 'SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;';
    $footer[] = 'SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;';
    $footer[] = '';
    $footer[] = '';
    fwrite($fp, implode("\n", $footer));
    fclose($fp);
    return TRUE;
  }
  return FALSE;
}

/**
 * Returns the name of the active database.
 */
function _demo_get_database() {
  $database = array_keys(db_query('SHOW TABLES')
    ->fetchAssoc());
  $database = preg_replace('/^Tables_in_/i', '', $database[0]);
  return $database;
}

/**
 * Enumerate database tables.
 */
function _demo_enum_tables() {
  return db_query('SHOW TABLES')
    ->fetchCol();
}

/**
 * Dump table schema.
 *
 * @param $fp
 *   The file handle of the output file.
 * @param $table
 *   A table name to export the schema for.
 */
function _demo_dump_table_schema($fp, $table) {
  $output = "\n";
  $output .= "--\n";
  $output .= "-- Table structure for table '{$table}'\n";
  $output .= "--\n\n";
  $data = db_query("SHOW CREATE TABLE `{$table}`")
    ->fetchAssoc();
  $status = db_query('SHOW TABLE STATUS LIKE :table', array(
    ':table' => $table,
  ))
    ->fetchAssoc();

  // Capitalization of keys depends on PDO database connection options.
  // Convert them to lowercase.
  foreach ($data as $key => $value) {
    unset($data[$key]);
    $data[strtolower($key)] = $value;
  }
  foreach ($status as $key => $value) {
    unset($status[$key]);
    $status[strtolower($key)] = $value;
  }

  // Add IF NOT EXISTS to CREATE TABLE, replace double quotes with MySQL quotes.
  $output .= preg_replace(array(
    '/^CREATE TABLE/',
    '/"/',
  ), array(
    'CREATE TABLE IF NOT EXISTS',
    '`',
  ), $data['create table']);

  // @todo Rethink the following code. Perhaps try to strip + parse the existing
  //   table definition (after leading ")" on last line) and merge anything
  //   missing into it, and re-append it again. There are too many differences
  //   between MySQL 5.0 and 5.1+, and PHP mysql(i) and pdo_mysql extensions.
  // PDO is missing the table engine.
  if (!strpos($output, ' ENGINE=')) {
    $output .= ' ENGINE=' . $status['engine'];
  }

  // Always add charset and collation info to table definitions.
  // SHOW CREATE TABLE does not contain collation information, if the collation
  // is equal to the default collation of the connection. Since dumps can be
  // moved across servers, we need to ensure correct collations.
  // Note that [DEFAULT] CHARSET or [DEFAULT] CHARACTER SET is always contained
  // on MySQL 5.1, even if it is equal to the default.
  // This addition assumes that a collation specified for a table is taken over
  // for the table's columns. The MySQL manual does not state whether this is
  // the case, but manual tests confirmed that it works that way.
  // Like Drupal core, we need to enforce UTF8 as character set and
  // utf8_general_ci as default database collation, if not overridden via
  // settings.php.
  if (!strpos($output, 'COLLATE=')) {

    // If there is a character set defined already, just append the collation.
    // Only look for the table definition (on the last line); column definitions
    // may use a special collation, which must not be changed.
    if (preg_match('@^\\).+(?:CHARSET|CHARACTER SET)@m', $output)) {
      $output = preg_replace('@((?:DEFAULT )?(?:CHARSET|CHARACTER SET) \\w+)@', '$1 ', $output);
    }
    else {
      $output .= ' DEFAULT CHARSET=utf8 ';
    }
  }

  // Add the table comment, if any.
  if (!preg_match('@^\\) .*COMMENT.+$@', $output) && !empty($status['comment'])) {

    // On PHP 5.2.6/Win32 with PDO MySQL 5.0 with InnoDB, the table comment has
    // a trailing "; InnoDB free: 84992 kB".
    $status['comment'] = preg_replace('@; InnoDB free: .+$@', '', $status['comment']);
    $output .= " COMMENT='" . $status['comment'] . "'";
  }

  // @todo Depends on whether we dump data and table existence on import.
  //   if (!empty($status['auto_increment'])) {
  //    $output .= ' AUTO_INCREMENT=' . $status['auto_increment'];
  //  }
  $output .= ";\n";
  fwrite($fp, $output);
}

/**
 * Dump table data.
 *
 * This code has largely been stolen from the phpMyAdmin project.
 *
 * @param $fp
 *   The file handle of the output file.
 * @param $table
 *   A table name to export the data for.
 */
function _demo_dump_table_data($fp, $table) {
  $output = "\n";
  $output .= "--\n";
  $output .= "-- Dumping data for table '{$table}'\n";
  $output .= "--\n\n";

  // Dump table data.
  $result = db_query("SELECT * FROM `{$table}`", array(), array(
    'fetch' => PDO::FETCH_ASSOC,
  ));

  // Get table fields.
  if ($fields = _demo_get_fields($result)) {

    // Disable indices to speed up import.
    $output .= "/*!40000 ALTER TABLE {$table} DISABLE KEYS */;\n";

    // Escape backslashes, PHP code, special chars.
    $search = array(
      '\\',
      "'",
      "\0",
      "\n",
      "\r",
      "\32",
    );
    $replace = array(
      '\\\\',
      "''",
      '\\0',
      '\\n',
      '\\r',
      '\\Z',
    );
    $insert_cmd = "INSERT INTO `{$table}` VALUES\n";
    $insert_buffer = '';
    $current_row = 0;
    $query_size = 0;
    foreach ($result as $row) {
      $current_row++;
      $values = array();
      $field = 0;
      foreach ($row as $value) {

        // NULL.
        if (!isset($value) || is_null($value)) {
          $values[] = 'NULL';
        }
        elseif ($fields[$field]->numeric && !$fields[$field]->timestamp && !$fields[$field]->blob) {
          $values[] = $value;
        }
        elseif ($fields[$field]->binary && $fields[$field]->blob) {

          // Empty blobs need to be different, but '0' is also empty :-(.
          if (empty($value) && $value != '0') {
            $values[] = "''";
          }
          else {
            $values[] = '0x' . bin2hex($value);
          }
        }
        else {
          $values[] = "'" . str_replace($search, $replace, $value) . "'";
        }
        $field++;
      }
      if ($current_row == 1) {
        $insert_buffer = $insert_cmd . '(' . implode(', ', $values) . ')';
      }
      else {
        $insert_buffer = '(' . implode(', ', $values) . ')';

        // Check if buffer size exceeds 50KB.
        if ($query_size + strlen($insert_buffer) > 50000) {

          // Flush to disc and start new buffer.
          fwrite($fp, $output . ";\n");
          $output = '';
          $current_row = 1;
          $query_size = 0;
          $insert_buffer = $insert_cmd . $insert_buffer;
        }
      }
      $query_size += strlen($insert_buffer);
      $output .= ($current_row == 1 ? '' : ",\n") . $insert_buffer;
    }
    if ($current_row > 0) {
      $output .= ";\n";
    }

    // Enable indices again.
    $output .= "/*!40000 ALTER TABLE {$table} ENABLE KEYS */;\n";
  }
  fwrite($fp, $output);
}

/**
 * Return table fields and their properties.
 */
function _demo_get_fields($result) {
  $fields = array();
  switch (db_driver()) {
    case 'mysql':
      $i = 0;
      while ($meta = $result
        ->getColumnMeta($i)) {
        settype($meta, 'object');

        // pdo_mysql does not add a native type for INT fields.
        if (isset($meta->native_type)) {

          // Enhance the field definition for mysql-extension compatibilty.
          $meta->numeric = strtolower($meta->native_type) == 'short';
          $meta->blob = strtolower($meta->native_type) == 'blob';

          // Add custom properties.
          $meta->timestamp = strtolower($meta->native_type) == 'long';
        }
        else {
          $meta->numeric = $meta->blob = $meta->timestamp = FALSE;
        }
        $meta->binary = array_search('not_null', $meta->flags);
        $fields[] = $meta;
        $i++;
      }
      break;
  }
  return $fields;
}

/**
 * Determine whether the given table is a VIEW.
 */
function _demo_table_is_view($table) {
  static $tables = array();
  if (!isset($tables[$table])) {
    $status = db_query('SHOW TABLE STATUS LIKE :table', array(
      ':table' => $table,
    ))
      ->fetchAssoc();

    // Capitalization of keys depends on PDO database connection options.
    $comment = isset($status['Comment']) ? $status['Comment'] : $status['comment'];
    $tables[$table] = strtoupper(substr($comment, 0, 4)) == 'VIEW';
  }
  return $tables[$table];
}

Functions

Name	Description
demo_dump_db	Dump active database.
_demo_dump_table_data	Dump table data.
_demo_dump_table_schema	Dump table schema.
_demo_enum_tables	Enumerate database tables.
_demo_get_database	Returns the name of the active database.
_demo_get_fields	Return table fields and their properties.
_demo_table_is_view	Determine whether the given table is a VIEW.