private function common_structure($old, $new)
{
dbsteward::$old_database = new SimpleXMLElement($old);
dbsteward::$new_database = new SimpleXMLElement($new);
mysql5_diff::$new_table_dependency = xml_parser::table_dependency_order(dbsteward::$new_database);
$ofs1 = new mock_output_file_segmenter();
$ofs3 = new mock_output_file_segmenter();
mysql5_diff::revoke_permissions($ofs1, $ofs3);
mysql5_diff::update_structure($ofs1, $ofs3);
mysql5_diff::update_permissions($ofs1, $ofs3);
// @TODO: assert expected = actual
// echo "\n\nofs 1:\n\n";
// echo $ofs1->_get_output();
// echo "\n\nofs 3:\n\n";
// echo $ofs3->_get_output();
}
private function diff($old, $new, $expected1, $expected3, $message = '')
{
dbsteward::$old_database = xml_parser::composite_doc(NULL, simplexml_load_string($this->db_doc_xml . $old . '</dbsteward>'));
dbsteward::$new_database = xml_parser::composite_doc(NULL, simplexml_load_string($this->db_doc_xml . $new . '</dbsteward>'));
$ofs1 = new mock_output_file_segmenter();
$ofs3 = new mock_output_file_segmenter();
mysql5_diff::$old_table_dependency = xml_parser::table_dependency_order(dbsteward::$old_database);
mysql5_diff::$new_table_dependency = xml_parser::table_dependency_order(dbsteward::$new_database);
mysql5_diff::update_structure($ofs1, $ofs3);
$actual1 = trim($ofs1->_get_output());
$actual3 = trim($ofs3->_get_output());
$this->assertEquals($expected1, $actual1, "during stage 1: {$message}");
$this->assertEquals($expected3, $actual3, "during stage 3: {$message}");
}
public function testNullAndDefaultColumnChanges()
{
$old = <<<XML
<schema name="public" owner="ROLE_OWNER">
<table name="table" primaryKey="id" owner="ROLE_OWNER">
<column name="id" type="int"/>
<column name="col" type="text" default="'xyz'"/>
</table>
</schema>
XML;
$new = <<<XML
<schema name="public" owner="ROLE_OWNER">
<table name="table" primaryKey="id" owner="ROLE_OWNER">
<column name="id" type="int"/>
<column name="col" type="text"/>
</table>
</schema>
XML;
// drop defaults: stage 1 drop default
$this->common_diff($old, $new, "ALTER TABLE `table`\n ALTER COLUMN `col` DROP DEFAULT;", '');
// add defaults: stage 1 set default
$this->common_diff($new, $old, "ALTER TABLE `table`\n ALTER COLUMN `col` SET DEFAULT 'xyz';", '');
$nullable = <<<XML
<schema name="public" owner="ROLE_OWNER">
<table name="table" primaryKey="id" owner="ROLE_OWNER">
<column name="id" type="int"/>
<column name="col" type="text" null="true"/>
</table>
</schema>
XML;
$notnullable = <<<XML
<schema name="public" owner="ROLE_OWNER">
<table name="table" primaryKey="id" owner="ROLE_OWNER">
<column name="id" type="int"/>
<column name="col" type="text" null="false"/>
</table>
</schema>
XML;
// NULL -> NOT NULL: stage 1 alter to NOT NULL (no update or stage 3 alter)
$this->common_diff($nullable, $notnullable, "ALTER TABLE `table`\n MODIFY COLUMN `col` text NOT NULL;", '');
// NOT NULL -> NULL: stage 1 alter to NULL
$this->common_diff($notnullable, $nullable, "ALTER TABLE `table`\n MODIFY COLUMN `col` text;", '');
$nullable_with_default = <<<XML
<schema name="public" owner="ROLE_OWNER">
<table name="table" primaryKey="id" owner="ROLE_OWNER">
<column name="id" type="int"/>
<column name="col" type="text" null="true" default="'xyz'"/>
</table>
</schema>
XML;
$notnullable_with_default = <<<XML
<schema name="public" owner="ROLE_OWNER">
<table name="table" primaryKey="id" owner="ROLE_OWNER">
<column name="id" type="int"/>
<column name="col" type="text" null="false" default="'xyz'"/>
</table>
</schema>
XML;
mysql5_diff::$add_defaults = true;
// NULL -> NOT NULL with defaults: stage 1 alter, updating nulls to default is done implicitly by mysql
$this->common_diff($nullable_with_default, $notnullable_with_default, "ALTER TABLE `table`\n MODIFY COLUMN `col` text NOT NULL DEFAULT 'xyz';", '');
// NOT NULL -> NULL with defaults: stage 1 alter, no need to change anything else
$this->common_diff($notnullable_with_default, $nullable_with_default, "ALTER TABLE `table`\n MODIFY COLUMN `col` text DEFAULT 'xyz';", '');
$notnullable_without_default = <<<XML
<schema name="public" owner="ROLE_OWNER">
<table name="table" primaryKey="id" owner="ROLE_OWNER">
<column name="id" type="int"/>
<column name="col" type="text" null="false"/>
</table>
</schema>
XML;
// NULL DEFAULT 'xyz' -> NOT NULL: stage 1 alter, no need to update nulls
$this->common_diff($nullable_with_default, $notnullable_without_default, "ALTER TABLE `table`\n MODIFY COLUMN `col` text NOT NULL;", '');
// NOT NULL -> NULL DEFAULT 'xyz': stage 1 alter
$this->common_diff($notnullable_without_default, $nullable_with_default, "ALTER TABLE `table`\n MODIFY COLUMN `col` text DEFAULT 'xyz';", "");
}
public static function build_upgrade($old_output_prefix, $old_composite_file, $old_db_doc, $old_files, $new_output_prefix, $new_composite_file, $new_db_doc, $new_files)
{
// place the upgrade files with the new_files set
$upgrade_prefix = $new_output_prefix . '_upgrade';
// mysql5_diff needs these to intelligently create SQL difference statements in dependency order
dbsteward::info("Calculating old table foreign key dependency order..");
mysql5_diff::$old_table_dependency = xml_parser::table_dependency_order($old_db_doc);
dbsteward::info("Calculating new table foreign key dependency order..");
mysql5_diff::$new_table_dependency = xml_parser::table_dependency_order($new_db_doc);
mysql5_diff::diff_doc($old_composite_file, $new_composite_file, $old_db_doc, $new_db_doc, $upgrade_prefix);
return $new_db_doc;
}
protected function upgrade_db($format)
{
dbsteward::set_sql_format($format);
$ofs = new mock_output_file_segmenter();
$doc_a = new SimpleXMLElement($this->{$format . '_xml_a'});
$doc_b = new SimpleXMLElement($this->{$format . '_xml_b'});
dbsteward::$old_database = $doc_a;
dbsteward::$new_database = $doc_b;
mysql5_diff::diff_doc_work($ofs, $ofs, $ofs, $ofs);
}