/mysqldiff.php
PHP | 708 lines | 557 code | 118 blank | 33 comment | 104 complexity | c43e06d5ef777b4f1b30b9fa772310d9 MD5 | raw file
- <?php
- /**
- * mysqldiff
- *
- * First of all: THIS IS AN ALPHA VERSION. DO NOT USE ON PRODUCTION!
- *
- * Compares the schema of two MySQL databases and produces a script
- * to "alter" the second schema to match the first one.
- *
- * Copyright (c) 2010-2011, Albert Almeida (caviola@gmail.com)
- * All rights reserved.
- *
- * THE SOFTWARE IS PROVIDED 'AS IS', WITHOUT WARRANTY OF ANY KIND,
- * EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
- * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
- * IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY
- * CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT,
- * TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE
- * SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
- *
- * https://github.com/caviola/mysqldiff
- */
- function drop_schema_db($db)
- {
- if (!$db->schema)
- return;
- mysqli_query($db->link, "drop database {$db->database}");
- }
- function create_schema_db($db)
- {
- if (!$db->schema)
- return;
- if (!mysqli_query($db->link, "create database {$db->database}"))
- error('Error of create database ' . mysqli_error($db->link));
- }
- function load_schema_db(&$db)
- {
- if (!$db->schema)
- return;
- $sql = explode(";", file_get_contents($db->schema));
- foreach ($sql as $q) {
- if (!trim($q))
- continue;
- if (preg_match('/^\s*\/\*.*\*\/\s*$/', $q))
- continue;
- if (preg_match('/^\s*drop /i', $q))
- continue;
- if (!mysqli_query($db->link, $q))
- error("Error in load schema db '$q'" . mysqli_error($db->link));
- }
- }
- function populate_schemata_info(&$db)
- {
- if (!($result = mysqli_query($db->link, "select * from information_schema.schemata where schema_name='$db->database'")))
- return FALSE;
- if ($info = mysqli_fetch_object($result)) {
- $db->charset = $info->DEFAULT_CHARACTER_SET_NAME;
- $db->collation = $info->DEFAULT_COLLATION_NAME;
- }
- }
- function list_tables($db)
- {
- if (!($result = mysqli_query($db->link, "select TABLE_NAME, ENGINE, TABLE_COLLATION, ROW_FORMAT, CHECKSUM, TABLE_COMMENT from information_schema.tables where table_schema='$db->database'")))
- return FALSE;
- $tables = array();
- while ($row = mysqli_fetch_object($result)) {
- $tables[$row->TABLE_NAME] = $row;
- }
- return $tables;
- }
- function list_columns($table, $db)
- {
- // Note the columns are returned in ORDINAL_POSITION ascending order.
- if (!($result = mysqli_query($db->link, "select * from information_schema.columns where table_schema='$db->database' and table_name='$table' order by ordinal_position")))
- return FALSE;
- $columns = array();
- while ($row = mysqli_fetch_object($result)) {
- $columns[$row->COLUMN_NAME] = $row;
- }
- return $columns;
- }
- function list_indexes($table, $db)
- {
- if (!($result = mysqli_query($db->link, "show indexes from `$table`")))
- return FALSE;
- $indexes = array();
- $prev_key_name = NULL;
- while ($row = mysqli_fetch_object($result)) {
- // Get the information about the index column.
- $index_column = (object) array(
- 'sub_part' => $row->Sub_part,
- 'seq' => $row->Seq_in_index,
- 'type' => $row->Index_type,
- 'collation' => $row->Collation,
- 'comment' => $row->Comment,
- );
- if ($row->Key_name != $prev_key_name) {
- // Add a new index to the list.
- $indexes[$row->Key_name] = (object) array(
- 'key_name' => $row->Key_name,
- 'table' => $row->Table,
- 'non_unique' => $row->Non_unique,
- 'columns' => array($row->Column_name => $index_column)
- );
- $prev_key_name = $row->Key_name;
- } else {
- // Add a new column to an existing index.
- $indexes[$row->Key_name]->columns[$row->Column_name] = $index_column;
- }
- }
- return $indexes;
- }
- function get_create_table_sql($name, $db)
- {
- if (!($result = mysqli_query($db->link, "show create table `$name`")))
- return FALSE;
- $row = mysqli_fetch_row($result);
- return $row[1];
- }
- function create_tables($db1, $tables1, $tables2)
- {
- global $options;
- $sql = '';
- $table_names = array_diff(array_keys($tables1), array_keys($tables2));
- foreach ($table_names as $t) {
- $sql .= get_create_table_sql($t, $db1) . ";\n\n";
- }
- fputs($options->ofh, $sql);
- }
- function format_default_value($value, $db)
- {
- if (strcasecmp($value, 'CURRENT_TIMESTAMP') == 0)
- return $value;
- elseif (is_string($value))
- return "'" . mysqli_real_escape_string($db->link, $value) . "'";
- else
- return $value;
- }
- function drop_tables($tables1, $tables2)
- {
- global $options;
- $sql = '';
- $table_names = array_diff(array_keys($tables2), array_keys($tables1));
- foreach ($table_names as $t) {
- $sql .= "DROP TABLE `$t`;\n";
- }
- if ($sql)
- $sql .= "\n";
- fputs($options->ofh, $sql);
- }
- function build_column_definition_sql($column, $db)
- {
- $result = $column->COLUMN_TYPE;
- if ($column->COLLATION_NAME)
- $result .= " COLLATE '$column->COLLATION_NAME'";
- $result .= strcasecmp($column->IS_NULLABLE, 'NO') == 0 ? ' NOT NULL' : ' NULL';
- if (isset($column->COLUMN_DEFAULT))
- $result .= ' DEFAULT ' . format_default_value($column->COLUMN_DEFAULT, $db);
- if ($column->EXTRA)
- $result .= " $column->EXTRA";
- if ($column->COLUMN_COMMENT)
- $result .= " COMMENT '" . mysqli_real_escape_string($db->link, $column->COLUMN_COMMENT) . "'";
- return $result;
- }
- function alter_table_add_column($column, $after_column, $table, $db)
- {
- global $options;
- $sql = "ALTER TABLE `$table` ADD COLUMN `$column->COLUMN_NAME` " .
- build_column_definition_sql($column, $db) .
- ($after_column ? " AFTER `$after_column`" : ' FIRST') .
- ";\n";
- fputs($options->ofh, $sql);
- }
- function alter_table_modify_column($column1, $column2, $after_column, $table, $db)
- {
- global $options;
- $modify = array();
- if ($column1->COLUMN_TYPE != $column2->COLUMN_TYPE)
- $modify['type'] = " $column1->COLUMN_TYPE";
- if ($column1->COLLATION_NAME != $column2->COLLATION_NAME)
- $modify['collation'] = " COLLATE $column1->COLLATION_NAME";
- if ($column1->IS_NULLABLE != $column2->IS_NULLABLE)
- $modify['null'] = strcasecmp($column1->IS_NULLABLE, 'NO') == 0 ? ' NOT NULL' : ' NULL';
- if ($column1->COLUMN_DEFAULT != $column2->COLUMN_DEFAULT) {
- // FALSE is an special value that indicates we should DROP this column's default value,
- // causing MySQL to assign it the "default default".
- $modify['default'] = isset($column1->COLUMN_DEFAULT) ? ' DEFAULT ' . format_default_value($column1->COLUMN_DEFAULT, $db) : FALSE;
- }
- if ($column1->EXTRA != $column2->EXTRA)
- $modify['extra'] = " $column1->EXTRA";
- if ($column1->COLUMN_COMMENT != $column2->COLUMN_COMMENT)
- $modify['comment'] = " COMMENT '$column1->COLUMN_COMMENT'";
- if ($column1->ORDINAL_POSITION != $column2->ORDINAL_POSITION)
- $modify['position'] = $after_column ? " AFTER `$after_column`" : ' FIRST';
- if ($modify) {
- $sql = "ALTER TABLE `$table` MODIFY `$column1->COLUMN_NAME`";
- $sql .= isset($modify['type']) ? $modify['type'] : " $column2->COLUMN_TYPE";
- if (isset($modify['collation']))
- $sql .= $modify['collation'];
- if (isset($modify['null']))
- $sql .= $modify['null'];
- else
- $sql .= strcasecmp($column2->IS_NULLABLE, 'NO') == 0 ? ' NOT NULL' : ' NULL';
- if (isset($modify['default']) && $modify['default'] !== FALSE) {
- $sql .= $modify['default'];
- } elseif (isset($column2->COLUMN_DEFAULT))
- $sql .= ' DEFAULT ' . format_default_value($column2->COLUMN_DEFAULT, $db);
- if (isset($modify['extra']))
- $sql .= $modify['extra'];
- elseif ($column2->EXTRA != '')
- $sql .= " $column2->EXTRA";
- if (isset($modify['comment']))
- $sql .= $modify['comment'];
- elseif ($column2->COLUMN_COMMENT != '')
- $sql .= " COMMENT '$column2->COLUMN_COMMENT'";
- if (isset($modify['position']))
- $sql .= $modify['position'];
- $sql .= ";\n";
- fputs($options->ofh, $sql);
- }
- }
- function alter_table_drop_columns($columns1, $columns2, $table)
- {
- global $options;
- $sql = '';
- $columns = array_diff_key($columns2, $columns1);
- foreach ($columns as $c) {
- $sql .= "ALTER TABLE `$table` DROP COLUMN `$c->COLUMN_NAME`;\n";
- }
- fputs($options->ofh, $sql);
- }
- function alter_tables_columns($db1, $db2)
- {
- global $options;
- $tables1 = list_tables($db1);
- $tables2 = list_tables($db2);
- $tables = array_intersect(array_keys($tables1), array_keys($tables2));
- foreach ($tables as $t) {
- $columns1 = list_columns($t, $db1);
- $columns2 = list_columns($t, $db2);
- $columns_index = array_keys($columns1);
- foreach ($columns1 as $c1) {
- $after_column = $c1->ORDINAL_POSITION == 1 ? NULL : $columns_index[$c1->ORDINAL_POSITION - 2];
- if (!isset($columns2[$c1->COLUMN_NAME]))
- alter_table_add_column($c1, $after_column, $t, $db2);
- else
- alter_table_modify_column($c1, $columns2[$c1->COLUMN_NAME], $after_column, $t, $db2);
- }
- if ($options->drop_columns)
- alter_table_drop_columns($columns1, $columns2, $t);
- }
- }
- function alter_tables($tables1, $tables2)
- {
- global $options;
- $sql = '';
- $table_names = array_intersect(array_keys($tables2), array_keys($tables1));
- foreach ($table_names as $t) {
- $t1 = $tables1[$t];
- $t2 = $tables2[$t];
- if ($t1->ENGINE != $t2->ENGINE)
- $sql .= "ALTER TABLE `$t` ENGINE=$t1->ENGINE;\n";
- if ($t1->TABLE_COLLATION != $t2->TABLE_COLLATION)
- $sql .= "ALTER TABLE `$t` COLLATE=$t1->TABLE_COLLATION;\n";
- if ($t1->ROW_FORMAT != $t2->ROW_FORMAT)
- $sql .= "ALTER TABLE `$t` ROW_FORMAT=$t1->ROW_FORMAT;\n";
- if ($t1->CHECKSUM != $t2->CHECKSUM)
- $sql .= "ALTER TABLE `$t` CHECKSUM=$t1->CHECKSUM;\n";
- /* if ($t1->TABLE_COMMENT != $t2->TABLE_COMMENT)
- $sql .= "ALTER TABLE `$t` COMMENT='$t1->TABLE_COMMENT';\n";
- */
- if ($sql)
- $sql .= "\n";
- }
- fputs($options->ofh, $sql);
- }
- function are_indexes_eq($index1, $index2)
- {
- if ($index1->non_unique != $index2->non_unique)
- return FALSE;
- if (count($index1->columns) != count($index2->columns))
- return FALSE;
- if (empty($index1->columns)) {
- return false;
- }
- foreach ((array) $index1->columns as $name => $column1) {
- if (!isset($index2->columns[$name]))
- return FALSE;
- if ($column1->seq != $index2->columns[$name]->seq)
- return FALSE;
- if ($column1->sub_part != $index2->columns[$name]->sub_part)
- return FALSE;
- if ($column1->type != $index2->columns[$name]->type)
- return FALSE;
- /* if ($column1->collation != $index2->columns[$name]->collation)
- return FALSE; */
- }
- return TRUE;
- }
- function build_drop_index_sql($index)
- {
- return $index->key_name == 'PRIMARY' ?
- "ALTER TABLE `$index->table` DROP PRIMARY KEY;" :
- "ALTER TABLE `$index->table` DROP INDEX $index->key_name;";
- }
- function build_create_index_sql($index)
- {
- $column_list = array();
- foreach ($index->columns as $name => $column) {
- $column_list[] = $name . ($column->sub_part ? "($column->sub_part)" : '');
- }
- $column_list = '(' . implode(',', $column_list) . ')';
- if ($index->key_name == 'PRIMARY')
- $result = "ALTER TABLE `$index->table` ADD PRIMARY KEY $column_list;";
- else {
- if (isset($index->type) && $index->type == 'FULLTEXT')
- $index_type = ' FULLTEXT';
- elseif (!$index->non_unique)
- $index_type = ' UNIQUE';
- else
- $index_type = '';
- $result = "CREATE$index_type INDEX $index->key_name ON `$index->table` $column_list;";
- }
- return $result;
- }
- function alter_table_add_indexes($idx1, $idx2)
- {
- global $options;
- $indexes = array_diff_key($idx1, $idx2);
- $sql = '';
- foreach ($indexes as $index_name => $index)
- $sql .= build_create_index_sql($index) . "\n";
- fputs($options->ofh, $sql);
- }
- function alter_table_drop_indexes($idx1, $idx2)
- {
- global $options;
- $indexes = array_diff_key($idx2, $idx1);
- $sql = '';
- foreach ($indexes as $index_name => $index)
- $sql .= build_drop_index_sql($index) . "\n";
- fputs($options->ofh, $sql);
- }
- function alter_table_alter_indexes($idx1, $idx2)
- {
- global $options;
- $sql = '';
- $indexes = (array) array_intersect_key((array) $idx1, (array) $idx2);
- foreach ($indexes as $index_name => $index)
- if (!are_indexes_eq($index, $idx2[$index_name])) {
- $sql .= build_drop_index_sql($idx2[$index_name]) . "\n";
- $sql .= build_create_index_sql($index) . "\n";
- }
- fputs($options->ofh, $sql);
- }
- function process_database($db1, $db2)
- {
- global $options;
- if (!$db2->schema)
- $sql = "USE `$db2->database`;\n";
- if ($db1->charset != $db2->charset)
- $sql .= "ALTER DATABASE `$db2->database` CHARACTER SET=$db1->charset;\n";
- if ($db1->collation != $db2->collation)
- $sql .= "ALTER DATABASE `$db2->database` COLLATE=$db1->collation;\n";
- $sql .= "\n";
- fputs($options->ofh, $sql);
- }
- function process_indexes($tables1, $tables2, $db1, $db2)
- {
- $tables = array_intersect_key((array) $tables1, (array) $tables2);
- foreach (array_keys((array) $tables) as $t) {
- $idx1 = list_indexes($t, $db1);
- $idx2 = list_indexes($t, $db2);
- alter_table_drop_indexes($idx1, $idx2);
- alter_table_add_indexes($idx1, $idx2);
- alter_table_alter_indexes($idx1, $idx2);
- }
- }
- function process_tables($db1, $db2)
- {
- global $options;
- $tables1 = list_tables($db1);
- $tables2 = list_tables($db2);
- create_tables($db1, $tables1, $tables2);
- if ($options->drop_tables)
- drop_tables($tables1, $tables2);
- alter_tables($tables1, $tables2);
- alter_tables_columns($db1, $db2);
- process_indexes($tables1, $tables2, $db1, $db2);
- }
- function usage()
- {
- echo <<<MSG
- THIS IS AN ALPHA VERSION. DO NOT USE ON PRODUCTION!
- Usage:
- php mysqldiff.php <options>
- Options:
- --schema-file1 <schema-file> Filename of the file which contain the db schema in sql
- Program will create a temp database and load schema
- --database1 <database-name> Name of source db.
- --host1 <hostname> Server hosting source db.
- --user1 <username> Username for connectiong to source db.
- --pwd1 <pwd> Password for connectiong to source db.
- --schema-file2 <schema-file> Filename of the file which contain the db schema in sql
- Program will create a temp database and load schema
- --database2 <database-name> Name of destination db.
- --host2 <hostname> Server hosting destination db.
- --user2 <username> Username for connectiong to destination db.
- --pwd2 <pwd> Password for connectiong to destination db.
- --drop-tables Whether to generate DROP TABLE statements
- for tables present in destination but not
- on source database.
- Note this can happen when you simply rename
- a table. Default is NOT TO DROP.
- --drop-columns Whether to generate ALTER TABLE...DROP COLUMN
- statements for columns present in destination
- but not on source database.
- Note this can happen when you simply rename
- a column. Default is NOT TO DROP.
- --output-file <filename> Filename to save the generated MySQL script.
- Default is to write to SDTOUT.
- --overwrite Overwrite the output file without asking for
- confirmation. Default is to ask.
- If source and destination databases share some connection data,
- you can specify them using:
- --database <database-name> Name of both dbs.
- --host <hostname> Server hosting both dbs.
- --user <username> Username for connectiong to both dbs.
- --pwd <pwd> Password for connectiong to both dbs.
- The default hostname is "localhost".
- Both passwords are empty by default.
- MSG;
- exit(0);
- }
- function error($msg)
- {
- fputs(STDERR, "mysqldiff: $msg\n");
- exit(1);
- }
- function prompt($msg)
- {
- echo $msg;
- return trim(fgets(STDIN));
- }
- $options = (object) array(
- 'drop_columns' => FALSE,
- 'drop_tables' => FALSE,
- 'new_table_data' => FALSE,
- 'db1' => (object) array(
- 'host' => 'localhost',
- 'pwd' => NULL,
- 'schema' => NULL
- ),
- 'db2' => (object) array(
- 'host' => 'localhost',
- 'pwd' => NULL,
- 'schema' => NULL
- ),
- 'output_file' => NULL,
- 'ofh' => fopen('php://stdout', 'w'), // output file handle
- );
- date_default_timezone_set('Europe/Zurich');
- $db1 = &$options->db1;
- $db2 = &$options->db2;
- if ($argc == 1)
- usage();
- // Parse command line arguments.
- for ($i = 1; $i < $argc; $i++) {
- switch ($argv[$i]) {
- case '--schema-file1':
- $db1->schema = $argv[++$i];
- break;
- case '--host1':
- $db1->host = $argv[++$i];
- break;
- case '--database1':
- $db1->database = $argv[++$i];
- break;
- case '--user1':
- $db1->user = $argv[++$i];
- break;
- case '--pwd1':
- $db1->pwd = $argv[++$i];
- break;
- case '--schema-file2':
- $db2->schema = $argv[++$i];
- break;
- case '--host2':
- $db2->host = $argv[++$i];
- break;
- case '--database2':
- $db2->database = $argv[++$i];
- break;
- case '--user2':
- $db2->user = $argv[++$i];
- break;
- case '--pwd2':
- $db2->pwd = $argv[++$i];
- break;
- case '--host':
- $db1->host = $db2->host = $argv[++$i];
- break;
- case '--database':
- $db1->database = $db2->database = $argv[++$i];
- break;
- case '--user':
- $db1->user = $db2->user = $argv[++$i];
- break;
- case '--pwd':
- $db1->pwd = $db2->pwd = $argv[++$i];
- break;
- case '--drop-columns':
- $options->drop_columns = TRUE;
- break;
- case '--drop-tables':
- $options->drop_tables = TRUE;
- break;
- case '--new-table-data':
- $options->new_table_data = TRUE;
- break;
- case '--output-file':
- $options->output_file = $argv[++$i];
- break;
- case '--overwrite':
- $options->overwrite = TRUE;
- break;
- case '--help':
- case '-h':
- usage();
- default:
- error("don't know what to do with \"{$argv[$i]}\"");
- }
- }
- /*
- $db1->database = 'diskstoragecatalog';
- $db2->database = 'diskstoragecatalog2';
- $db1->user = $db2->user = 'root';
- $options->output_dir = 'c:/temp/perico';
- $options->overwrite = TRUE;
- */
- if (!$db1->database && !$db1->schema)
- error("source database or schema file must be specified with --schema-file1, --database1 or --database");
- if ($db1->schema) {
- if (!file_exists($db1->schema))
- error("schema file 1 does not exist");
- $db1->database = "tmp_schema_" . uniqid();
- }
- if (!$db2->database && !$db2->schema)
- error("destination database or schema file must be specified with --schema-file2, --database2 or --database");
- if ($db2->schema) {
- if (!file_exists($db1->schema))
- error("schema file 2 does not exist");
- $db2->database = "tmp_schema_" . uniqid();
- }
- if ($db1->host == $db2->host && $db1->database == $db2->database && !$db1->schema && !$db2->schema)
- error("databases names must be different if they reside on the same host");
- if ($options->output_file) {
- if (file_exists($options->output_file) && !$options->overwrite) {
- if (prompt("Output file $options->output_file exists. Overwrite it (y/n)? ") != 'y')
- exit(0);
- }
- $options->ofh = @fopen($options->output_file, 'w') or error("error creating output file $options->output_file");
- }
- $db1->link = mysqli_connect($db1->host, $db1->user, $db1->pwd) or error('Connection 1 failed');
- create_schema_db($db1);
- mysqli_select_db($db1->link, $db1->database) or error(mysqli_error($db1->link));
- $db2->link = mysqli_connect($db2->host, $db2->user, $db2->pwd) or error('Connection 2 failed');
- create_schema_db($db2);
- mysqli_select_db($db2->link, $db2->database) or error(mysqli_error($db2->link));
- load_schema_db($db1);
- load_schema_db($db2);
- populate_schemata_info($db1);
- populate_schemata_info($db2);
- process_database($db1, $db2);
- process_tables($db1, $db2);
- drop_schema_db($db1);
- drop_schema_db($db2);