public static function PrintCompareTables()
{
$one = Framework::Model();
$tmp = DBToXML::AsXML(DB::tables())->asXML();
$two = new Config($tmp);
$two = $two->get();
$xml = new XMLToSQL($one);
$compare = new CompareXML($one, $two);
$total = $incorrect = 0;
$one_empty = !count($one->xpath('//table'));
$two_empty = !count($two->xpath('//table'));
if ($one_empty || $two_empty) {
return;
}
foreach ($one->table as $table) {
$table_name = (string) $table['name'];
if (!DB::tableExists($table_name)) {
continue;
}
$total++;
$problem = false;
list($indexes_missing_one, $columns_missing_two) = $compare->CompareColumns($table_name);
if (count($indexes_missing_one)) {
$problem = true;
foreach ($indexes_missing_one as $column) {
$field = $table->find("//field[@name='{$column}']");
$sql = $xml->FieldSQL($field);
$sql = "ALTER TABLE `{$table_name}` ADD {$sql};";
print self::output_sql($sql, "Column <em>{$column}</em> is missing from the <em>{$table_name}</em> table.", 'Add it');
}
}
if (count($columns_missing_two)) {
$problem = true;
foreach ($columns_missing_two as $column) {
$sql = "ALTER TABLE `{$table_name}` DROP {$column};";
print self::output_sql($sql, "Existing column <em>{$column}</em> is not present in the configuration for the <em>{$table_name}</em> table.", 'Remove it');
}
}
// compare column attributes
foreach ($table->field as $column) {
$column_name = (string) $column['name'];
list($diff_one, $diff_two) = $compare->CompareAttributes($table_name, $column_name);
if (count($diff_one) || count($diff_two)) {
$problem = true;
$text = self::column_diff_to_string($diff_two) . ' vs ' . self::column_diff_to_string($diff_one);
$sql = "ALTER TABLE `{$table['name']}` CHANGE `{$column_name}` " . $xml->FieldSQL($column);
//ALTER TABLE `site_css_backup` CHANGE `created` `created` INT( 4 ) UNSIGNED NULL DEFAULT NULL
print self::output_sql($sql, "Different column attributes in XML/{$table_name}.{$column_name}: {$text}", 'Change it');
}
}
list($pri_one, $pri_two) = $compare->ComparePrimaryKey($table_name);
if ($pri_one != $pri_two) {
$problem = true;
print self::output_sql("DROP TABLE `{$table_name}`;", "Table <em>{$table_name}</em> has incorrect primary key \"{$pri_two}\" instead of \"{$pri_one}\":", 'Drop the table');
}
// indexes
list($indexes_missing_one, $indexes_missing_two) = $compare->CompareIndexes($table_name);
if (count($indexes_missing_one) || count($indexes_missing_two)) {
$problem = true;
}
self::PrintMissingDBIndexes($xml, $table_name, $indexes_missing_one);
self::PrintMissingXMLIndexes($xml, $table_name, $indexes_missing_two);
// unique indexes
list($indexes_missing_one, $indexes_missing_two) = $compare->CompareUniques($table_name);
if (count($indexes_missing_one) || count($indexes_missing_two)) {
$problem = true;
}
self::PrintMissingDBUniques($xml, $table_name, $indexes_missing_one);
self::PrintMissingXMLUniques($xml, $table_name, $indexes_missing_two);
if ($problem) {
$incorrect++;
}
}
$correct = $total - $incorrect;
$type = $incorrect ? 'Fail' : 'Pass';
$answer = $incorrect ? 'No' : 'Yes';
$_t = $total == 1 ? 'table is correct' : 'tables are correct';
$message = " - <em>{$answer}</em>, {$correct}/{$total} {$_t}.";
print self::output($type, 'Are table definitions correct?', $message);
}
