ConvertDB2MB4.php

<?php

/*
 * @file
 */

use Symfony\Component\Console\Command\Command;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Output\OutputInterface;


/**
 * Class ConvertDBToMB4
 */
class ConvertDBToMB4 extends Command {

  /**
   * @var alias of drupal website being changed
   */
  private $drush_alias;
  /**
   * @var pdo object to connect to main database
   */
  protected $pdo;
  /**
   * @var pdo object to connect to information_schema for db
   */
  protected $schema;
  /**
   * @var pdo connection params
   */
  protected $dbParams;

  /**
   *
   */
  protected function configure() {
    $this
      ->setName("convert-mb4")
      ->setDescription("Convert database tables to utf8-mb4 so as to facilitate emoji in values")
      ->addArgument(
        'alias',
        InputArgument::REQUIRED,
        'From which site have you just cloned the DB?'
      );
  }

  /**
   * @param \Symfony\Component\Console\Input\InputInterface $input
   * @param \Symfony\Component\Console\Output\OutputInterface $output
   */
  protected function execute(InputInterface $input, OutputInterface $output) {
    $this->drush_alias = $input->getArgument('alias');
    $this->dbParams = json_decode(shell_exec("drush {$this->drush_alias} sql-conf --format=json --show-passwords"), true);
    $this->pdo = $this->getPDO($this->dbParams['database']);
    $this->schema = $this->getPDO("information_schema");
    $tables = $this->pdo->query("show tables;");
    while($table = $tables->fetchColumn()) {
      // ignore tables that begin with "_"
      $output->writeln("TABLE: {$table}: ==============================================================|");
      if (substr($table, 0, 1) != "_") {
        $columns = $this->getColumnsOfTable($table);
        if ($columns->count()) {
          $output->writeln("{$columns->count()} column(s) need to be updated...");
          while ($columns->valid()) {
            $this->fixColumn($columns->current(), $table, $output);
            $columns->next();
          }
        }
        $this->fixTable($table, $output);
      } else {
        $output->writeln("{$table} skipped.");
      }
    }
  }

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

  /**
   * @param $database
   * @return \PDO
   */
  public function getPDO($database){
    $toReturn = new PDO(
      "{$this->dbParams['driver']}:dbname={$database};host={$this->dbParams['host']};port={$this->dbParams['port']}",
      $this->dbParams['username'],
      $this->dbParams['password']
    );
    $toReturn->exec("SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci");
    $toReturn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
    return $toReturn;
  }

  /**
   * @param \Symfony\Component\Console\Output\OutputInterface $output
   * @return bool success|failure
   */
  public function fixDB(OutputInterface $output) {
    $sql = "ALTER DATABASE {$this->dbParams['database']} CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;";
    return $this->query($sql, $output);
  }

  /**
   * @param string $table
   * @param \Symfony\Component\Console\Output\OutputInterface $output
   * @return bool success|failure
   */
  public function fixTable($table, OutputInterface $output) {
    $sql = "ALTER TABLE `{$table}` CONVERT to CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;";
    $sql .= " REPAIR TABLE `{$table}`;";
    $sql .= " OPTIMIZE TABLE `{$table}`;";
    return $this->query($sql, $output);
  }

  /**
   * @param string $column
   * @param string $table
   * @param \Symfony\Component\Console\Output\OutputInterface $output
   * @return bool success|failure
   */
  public function fixColumn( $column, $table,  OutputInterface $output) {
    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 $this->query($sql, $output);
  }

  /**
   * @param $message
   * @param $sql
   * @param \Symfony\Component\Console\Output\OutputInterface $output
   */
  protected function writeError($message, $sql, OutputInterface $output) {
    $error = $this->pdo->errorInfo();
    $output->writeln("====================");
    $output->writeln($message);
    $output->writeln($sql);
    $output->writeln($error[2]);
    $output->writeln("====================");
  }

  /**
   * @param $sql
   * @param \Symfony\Component\Console\Output\OutputInterface $output
   * @return bool
   */
  protected function query($sql,OutputInterface $output) {
    $this->pdo->exec($sql);
    $success = (int) $this->pdo->errorCode();
    if ($success == 0) {
      return true;
    } else {
      $this->writeError("Query error!", $sql, $output);
      return false;
    }
  }
}