function getDiffWith(dbInfo $db_info2)
{
$diff_sql = '';
foreach ($db_info2->tables as $tablename => $tabledata) {
if (!isset($this->tables[$tablename])) {
$diff_sql .= "\n" . $db_info2->tables[$tablename]['create'] . "\n";
continue;
}
foreach ($tabledata['fields'] as $field => $fielddata) {
$mycode = $fielddata['code'];
$othercode = @$this->tables[$tablename]['fields'][$field]['code'];
if ($mycode and !$othercode) {
$diff_sql .= "ALTER TABLE `{$tablename}` ADD `{$field}` {$mycode};\n";
}
}
if ($tabledata['keys']) {
foreach ($tabledata['keys'] as $field => $fielddata) {
$mycode = $fielddata['code'];
$otherdata = @$this->tables[$tablename]['keys'][$field];
$othercode = @$otherdata['code'];
if ($mycode and !$othercode) {
if ($otherdata['type'] == 'PRIMARY') {
$diff_sql .= "ALTER TABLE `{$tablename}` ADD PRIMARY KEY {$mycode};\n";
} else {
$diff_sql .= "ALTER TABLE `{$tablename}` ADD {$fielddata['type']} INDEX `{$field}` {$mycode};\n";
}
}
}
}
if ($tabledata['fkeys']) {
foreach ($tabledata['fkeys'] as $fkeyname => $data) {
$mycode = $data['code'];
$otherfkname = $this->get_fk_name_by_field($tablename, $data['field']);
$otherdata = @$this->tables[$tablename]['fkeys'][$otherfkname];
if ($data['ref_table'] != $otherdata['ref_table'] or $data['ref_field'] != $otherdata['ref_field'] or $data['on_delete'] != $otherdata['on_delete'] or $data['on_update'] != $otherdata['on_update']) {
$diff_sql .= "ALTER TABLE `{$tablename}` ADD {$mycode};\n";
}
}
}
}
foreach ($this->tables as $tablename => $tabledata) {
if (!isset($db_info2->tables[$tablename])) {
$diff_sql .= "DROP TABLE `{$tablename}`;\n";
continue;
}
//drop, alter foreign key
if ($tabledata['fkeys']) {
foreach ($tabledata['fkeys'] as $fkeyname => $data) {
$mycode = $data['code'];
$otherfkname = $db_info2->get_fk_name_by_field($tablename, $data['field']);
$othercode = @$db_info2->tables[$tablename]['fkeys'][$otherfkname]['code'];
if ($mycode and !$othercode) {
$diff_sql .= "ALTER TABLE `{$tablename}` DROP FOREIGN KEY `{$fkeyname}`;\n";
} else {
$data2 = $db_info2->tables[$tablename]['fkeys'][$otherfkname];
if ($data['ref_table'] != $data2['ref_table'] || $data['ref_field'] != $data2['ref_field'] || $data['on_delete'] != $data2['on_delete'] || $data['on_update'] != $data2['on_update']) {
if ($this->debug) {
$diff_sql .= "/* old definition: {$mycode}\n new definition: {$othercode} */\n";
}
$diff_sql .= "ALTER TABLE `{$tablename}` DROP FOREIGN KEY `{$fkeyname}`;\n";
$diff_sql .= "ALTER TABLE `{$tablename}` ADD {$othercode};\n";
}
}
}
}
//drop, alter index
if ($tabledata['keys']) {
foreach ($tabledata['keys'] as $field => $fielddata) {
$otherdata = @$db_info2->tables[$tablename]['keys'][$field];
$ind_name = @$otherdata['type'] == 'PRIMARY' ? 'PRIMARY KEY' : "{$otherdata['type']} INDEX";
if ($fielddata['code'] and !$otherdata['code']) {
if ($fielddata['type'] == 'PRIMARY') {
$diff_sql .= "ALTER TABLE `{$tablename}` DROP PRIMARY KEY;\n";
} else {
$diff_sql .= "ALTER TABLE `{$tablename}` DROP INDEX {$field};\n";
}
} elseif ($fielddata['fields'] != $otherdata['fields'] or $fielddata['type'] != $otherdata['type']) {
if ($this->debug) {
$diff_sql .= "/* old definition: {$fielddata['code']}\n new definition: {$otherdata['code']} */\n";
}
if ($fielddata['type'] == 'PRIMARY') {
$diff_sql .= "ALTER TABLE `{$tablename}` DROP PRIMARY KEY,";
} else {
$diff_sql .= "ALTER TABLE `{$tablename}` DROP INDEX {$field},";
}
$diff_sql .= " ADD {$ind_name} " . ($field ? "`{$field}`" : "") . " {$otherdata['code']};\n";
}
}
}
//drop, alter field
foreach ($tabledata['fields'] as $field => $fielddata) {
$mycode = $fielddata['code'];
$otherdata = @$db_info2->tables[$tablename]['fields'][$field];
$othercode = @$otherdata['code'];
if ($mycode and !$othercode) {
$diff_sql .= "ALTER TABLE `{$tablename}` DROP `{$field}`;\n";
} elseif ($fielddata['type'] != $otherdata['type'] or $fielddata['type'] != $otherdata['type'] or $fielddata['null'] != $otherdata['null'] or $fielddata['default'] != $otherdata['default']) {
if ($this->debug) {
$diff_sql .= "/* old definition: {$mycode}\n new definition: {$othercode} */\n";
}
$diff_sql .= "ALTER TABLE `{$tablename}` CHANGE `{$field}` `{$field}` {$othercode};\n";
}
}
}
return $diff_sql;
}