#!/usr/bin/php -q
<?php
// Convert mysql tables from utf-8 in latin1 (drupal 4.6) to utf8 (drupal 4.7)
// For personal use, mr700.

if (!$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')) {
  die("Can't connect to the mysql server: " . mysql_error() . "\n");
}
// set mysql to UTF-8
$result = mysql_query('SET NAMES utf8')
    OR die('Error: ' . mysql_errno() . ': ' . mysql_error());
$result = mysql_query('SET CHARACTER SET utf8')
    OR die('Error: ' . mysql_errno() . ': ' . mysql_error());
mysql_select_db('sport1', $link)
  OR die("Can't select the database 'test': " . mysql_error() . "\n");

// If you need to migrate only a few tables, hack this part
$tables = array();
print "--- Discovering tables...";
$sql = "SHOW TABLES";
$result = mysql_query($sql)
  OR die("Can't select: $sql: " . mysql_error() . "\n");
while ($row = mysql_fetch_row($result)) {
  $tables[] = $row[0];
}
print " done.\n";
print "--- Processing tables:\n";
//$tables[] = 'announce_section';

foreach ($tables as $table) {
  print "--- $table ---\n";
  $sql = "SHOW CREATE TABLE `$table`";
  $result = mysql_query($sql)
    OR die("Can't get table create statement: $sql: " . mysql_error() . "\n");
  $row = mysql_fetch_row($result)
    OR die("Can't fetch table structure: $sql: " . mysql_error() . "\n");
  $rows = split("\n", $row[1]);
  $chrows = array();
  $keys = array();
  foreach (array_keys($rows) as $key) {
    if (preg_match('/^ *`/', $rows[$key])) {
      // [   `link` varchar(255) NOT NULL default '',]
      $r = trim($rows[$key], ' ,');
      preg_match('/`([^`]+)`\s+(\S+)/ui', $r, $matches);
      unset($matches[0]);
      if (isset($matches[2]) &&  preg_match('/((VAR)?CHAR|(TINY|MEDIUM|LONG)?TEXT)/uSi', $matches[2])) {
        $chrows[$matches[1]] = $matches[2];
      }
    } elseif (preg_match('/ KEY /i', $rows[$key])) {
      // [  PRIMARY KEY  (`cid`),]
      // [  UNIQUE KEY `title` (`title`)]
      $r = trim($rows[$key], ' ,');
      $r1 = preg_replace('/[^`]*`([^`]+)`[^`]*/umS', '${1},', $r);
      $r1 = trim($r1, ',');
      $a = explode(',', $r1);
      // Is this a PRIMARY KEY ?
      if (preg_match('/PRIMARY KEY/ui', $r)) {
        // $a contains only row names in this case
        foreach ($a as $aa) {
          if (isset($chrows[$aa])) {
            $keys['PRIMARY KEY'] = $r;
          }
        }
      }
      else {
        $key_name = $a[0];
        unset($a[0]); // This is just the key name, not it's elements
        // Drop only keys we need to
        foreach ($a as $key) {
          if (isset($chrows[$key])) {
            // I add 'PRIMARY KEY' as a key because one can have
            // a row as PRIMARY KEY and normal KEY...
            // yea, stupid but possible (and I've seen this)!
            $keys[$key_name] = $r;
          }
        }
      }
    }
  }
  // Drop any offending keys (can't have key on BLOB row)
  foreach ($keys as $key => $kstatement) {
    if ($key == 'PRIMARY KEY') {
      $sql = "ALTER TABLE `$table` DROP PRIMARY KEY";
    } else {
      $sql = "ALTER TABLE `$table` DROP KEY `$key`";
    }
    print "$sql;\n";
    mysql_query($sql)
      OR die("ERROR: $sql: " . mysql_error() . "\n");
  }
  // change text rows' type to BLOB
  foreach (array_keys($chrows) as $row) {
    $sql = "ALTER TABLE `$table` CHANGE `$row` `$row` BLOB";
    print "$sql;\n";
    mysql_query($sql)
      OR die("ERROR: $sql: " . mysql_error() . "\n");
  }
  // convert the table charset
  $sql = "ALTER TABLE `$table` CONVERT TO CHARACTER SET utf8";
  print "$sql;\n";
  mysql_query($sql)
    OR die("ERROR: $sql: " . mysql_error() . "\n");
  // restore text rows' type
  foreach ($chrows as $row => $cstatement) {
    $sql = "ALTER TABLE `$table` CHANGE `$row` `$row` $cstatement";
    print "$sql;\n";
    mysql_query($sql)
      OR die("ERROR: $sql: " . mysql_error() . "\n");
  }
  // restore any dropped keys
  foreach ($keys as $key => $kstatement) {
    $sql = "ALTER TABLE `$table` ADD $kstatement";
    print "$sql;\n";
    mysql_query($sql)
      OR die("ERROR: $sql: " . mysql_error() . "\n");
  }
}
print "--- DONE ---\n";