mysqldumpで外部キーの依存がなんとかなるようにdump順を並び替えるための殴り書きスクリプト。並び替え部分がかなりひどい。

mysqldump_order.php

<?php
$host = 'localhost';
$port = 3306;
$user = 'DBUSER';
$pass = 'DBPASS';
$database = 'DBNAME';

// Autoloader http://www.revulo.com/blog/20090524.html
require_once('Zend/Loader/Autoloader.php');
$autoloader = Zend_Loader_Autoloader::getInstance()
    ->unregisterNamespace(array('Zend_', 'ZendX_'))
    ->setFallbackAutoloader(true);

$conn = Zend_Db::factory('Pdo_Mysql', array(
    'host'    => $host,
    'port'    => $port,
    'username'  => $user,
    'password'  => $pass,
    'dbname'  => $database,
));

echo "テーブル一覧を取得中...\n";
$tables = getTableList();
if(!$tables) {
  echo "テーブルがありません\n";
  exit(1);
}

echo "依存関係を取得中...\n";
$refs = array();
foreach($tables as $table) {
  echo "    {$table}\n";
  $refs[$table] = getReference($table);
}

echo "並び替え中...\n";
do {
  $changed = false;
  foreach($tables as $i => $a) {
    if(!$refs[$a]) {
      continue;
    }
    $insert_pos = false;
    foreach($tables as $j => $b) {
      if($i >= $j) {
        continue;
      }
      if(in_array($b, $refs[$a], true)) {
        if(in_array($a, $refs[$b], true)) {
          echo "依存関係が循環: $a $b\n";
          exit(1);
        }
        $insert_pos = $j + 1;
      }
    }
    if($insert_pos !== false) {
      $changed = true;
      $new_tables = array();
      foreach(range(0, $insert_pos - 1) as $i2) {
        if($tables[$i2] === $a) {
          continue;
        }
        $new_tables[] = $tables[$i2];
      }
      $new_tables[] = $a;
      foreach(range($insert_pos, count($tables) - 1) as $j2) {
        $new_tables[] = $tables[$j2];
      }
      $tables = $new_tables;
      break;
    }
  }
} while($changed);

echo "\n";
echo "並び替え結果:\n";
foreach($tables as $t) {
  echo escapeshellarg($t) . " ";
}
echo "\n";


function getTableList() {
  global $conn;
  $list = array_map(
    function ($row) { return $row[0]; },
    $conn->fetchAll('SHOW TABLES', array(), Zend_Db::FETCH_NUM)
  );
  natcasesort($list);
  return $list;
}

function getReference($table) {
  global $conn, $database;
  $sql = 'SELECT `REFERENCED_TABLE_NAME` FROM `INFORMATION_SCHEMA`.`KEY_COLUMN_USAGE` WHERE `CONSTRAINT_SCHEMA` = :database AND `TABLE_NAME` = :table AND `REFERENCED_TABLE_NAME` IS NOT NULL';
  $params = array(
    ':database' => $database,
    ':table' => $table,
  );
  $list = array_map(
    function ($row) { return $row[0]; },
    $conn->fetchAll($sql, $params, Zend_Db::FETCH_NUM)
  );
  return $list;
}