convert_db.sh

#!/usr/bin/php
<?php

if (!class_exists("OutputWriter")) {
  // To write to log file, include this file
  // with OutputWriter already defined.
  class OutputWriter {

    function write($message) {
      echo $message;
    }

    function writeln($message) {
      echo $this->write($message).PHP_EOL;
    }

  }
}

$output = new OutputWriter();

$cmd = 'index.php';
define('DRUPAL_ROOT', getcwd());
$_SERVER['HTTP_HOST']       = 'default';
$_SERVER['PHP_SELF']        = '/index.php';
$_SERVER['REMOTE_ADDR']     = '127.0.0.1';
$_SERVER['SERVER_SOFTWARE'] = NULL;
$_SERVER['REQUEST_METHOD']  = 'GET';
$_SERVER['QUERY_STRING']    = '';
$_SERVER['PHP_SELF']        = $_SERVER['REQUEST_URI'] = '/';
$_SERVER['HTTP_USER_AGENT'] = 'console';
if (is_dir($_SERVER['HOME'].'/.acquia/DevDesktop/DrupalSettings')) {
  $_SERVER['DEVDESKTOP_DRUPAL_SETTINGS_DIR'] = $_SERVER['HOME'].'/.acquia/DevDesktop/DrupalSettings';
}

// Bootstrap Drupal.
include_once './includes/bootstrap.inc';
drupal_bootstrap(DRUPAL_BOOTSTRAP_CONFIGURATION);
// Create a PDO object from drupal's db settings.
$pdo = convert_db_get_pdo();
// Get a second PDO object to do schema queries.
$schema = convert_db_get_pdo("information_schema");

$tables = $pdo->query("show tables;");
while($table = $tables->fetchColumn()) {
  // Ignore tables that begin with "_".
  $output->writeln(str_pad("TABLE {$table} ", 80, "=", STR_PAD_RIGHT)."|");
  if (substr($table, 0, 1) != "_") {
    $columns = convert_db_get_columns_of_table($table);
    if ($columns->count()) {
      $output->writeln("{$columns->count()} column(s) need to be updated...");
      while ($columns->valid()) {
        convert_db_fix_column($columns->current(), $table);
        $columns->next();
      }
    }
    convert_db_fix_table($table);
  } else {
    $output->writeln("{$table} skipped.");
  }
}
convert_db_fix_database();
$output->writeln("Conversion complete.");



/**
 * @param $database
 * @return \PDO
 */
function convert_db_get_pdo($db_name = null){
  global $db_url;
  $params = parse_url($db_url);
  if ($db_name == null) {
    $db_name = str_replace("/", "", $params['path']);
  }
  if ($params['scheme'] == 'mysqli') {
    $params['driver'] = "mysql";
  }
  try {
    $toReturn = new \PDO(
      "{$params['driver']}:dbname={$db_name};host={$params['host']};port={$params['port']}",
      $params['user'],
      $params['pass']
    );
  } catch(Exception $e) {
    print_r($e);
    exit();
  }
  $toReturn->exec("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci");
  $toReturn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
  return $toReturn;
}


/**
 * @param $table
 * @return \ArrayIterator
 */
function convert_db_get_columns_of_table($table) {
  global $schema, $pdo;
  $sql = "select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH AS LENGTH from columns where `TABLE_SCHEMA` = '"
    .$pdo->query('select database()')->fetchColumn()
    ."' and table_name = '".$table
    ."' and collation_name is not null and collation_name != 'utf8mb4_general_ci'";
  $cols = $schema->query($sql)->fetchAll();
  if (!empty($cols)) {
    return new ArrayIterator($cols);
  } else {
    return new ArrayIterator();
  }
}

/**
 * @param \Symfony\Component\Console\Output\OutputInterface $output
 * @return bool success|failure
 */
function convert_db_fix_database() {
  global $pdo;
  $sql = "ALTER DATABASE `".$pdo->query('select database()')->fetchColumn()."` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;";
  return convert_db_query($sql);
}

/**
 * @param string $table
 * @return bool success|failure
 */
function convert_db_fix_table($table) {
  $sql = "ALTER TABLE `{$table}` CONVERT to CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;";
  $sql .= " REPAIR TABLE `{$table}`;";
  $sql .= " OPTIMIZE TABLE `{$table}`;";
  return convert_db_query($sql);
}

/**
 * @param string $column
 * @param string $table
 * @return bool success|failure
 */
function convert_db_fix_column( $column, $table) {
  switch($column['DATA_TYPE']) {
    case "varchar":
      $coltype = "varchar(191)";
      break;

    CASE "char":
      $coltype = "char({$column['LENGTH']})";
      break;

    default:
      $coltype = $column['DATA_TYPE'];
  }
  $sql = "ALTER TABLE ".$table." CHANGE ".$column['COLUMN_NAME']." ".$column['COLUMN_NAME']." ".$coltype." CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;";
  return convert_db_query($sql);
}

/**
 * @param $message
 * @param $sql
 */
function convert_db_write_error($message, $sql) {
  global $output, $pdo;
  $error = $pdo->errorInfo();
  $output->writeln("====================");
  $output->writeln($message);
  $output->writeln($sql);
  $output->writeln($error[2]);
  $output->writeln("====================");
}

/**
 * @param $sql
 * @return bool
 */
function convert_db_query($sql) {
  global $pdo;
  $pdo->exec($sql);
  $success = (int) $pdo->errorCode();
  if ($success == 0) {
    return true;
  } else {
    convert_db_write_error("Query error!", $sql);
    return false;
  }
}