public function setUp()
{
dbsteward::set_sql_format('pgsql8');
dbsteward::$quote_schema_names = TRUE;
dbsteward::$quote_table_names = TRUE;
dbsteward::$quote_column_names = TRUE;
dbsteward::$quote_function_names = TRUE;
dbsteward::$quote_object_names = TRUE;
$xml = <<<XML
<dbsteward>
<database>
<host>db-host</host>
<name>dbsteward</name>
<role>
<application>dbsteward_phpunit_app</application>
<owner>deployment</owner>
<replication/>
<readonly/>
</role>
</database>
</dbsteward>
XML;
$db = new SimpleXMLElement($xml);
dbsteward::$new_database = $db;
dbsteward::$old_database = $db;
}
private function diff($old, $new, $expected1, $expected3, $message = '')
{
dbsteward::$old_database = new SimpleXMLElement($this->db_doc_xml . $old . '</dbsteward>');
dbsteward::$new_database = new SimpleXMLElement($this->db_doc_xml . $new . '</dbsteward>');
$ofs1 = new mock_output_file_segmenter();
$ofs3 = new mock_output_file_segmenter();
// same structure as mysql5_diff::update_structure
foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) {
$old_schema = dbx::get_schema(dbsteward::$old_database, $new_schema['name']);
mysql5_diff_constraints::diff_constraints($ofs1, $old_schema, $new_schema, 'constraint', TRUE);
mysql5_diff_constraints::diff_constraints($ofs1, $old_schema, $new_schema, 'primaryKey', TRUE);
mysql5_diff_tables::drop_tables($ofs3, $old_schema, $new_schema);
mysql5_diff_tables::diff_tables($ofs1, $ofs3, $old_schema, $new_schema);
// mysql5_diff_indexes::diff_indexes($ofs1, $old_schema, $new_schema);
mysql5_diff_constraints::diff_constraints($ofs1, $old_schema, $new_schema, 'primaryKey', FALSE);
}
foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) {
$old_schema = dbx::get_schema(dbsteward::$old_database, $new_schema['name']);
mysql5_diff_constraints::diff_constraints($ofs1, $old_schema, $new_schema, 'constraint', FALSE);
}
$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}");
}
/**
* Setup file pointers then call diff_doc_work() to do the actual diffing
* @param string $old_xml_file
* @param string $new_xml_file
* @param SimpleXMLElement $old_database
* @param SimpleXMLElement $new_database
* @param string $upgrade_prefix
*/
public static function diff_doc($old_xml_file, $new_xml_file, $old_database, $new_database, $upgrade_prefix)
{
$timestamp = date('r');
$old_set_new_set = "-- Old definition: " . $old_xml_file . "\n" . "-- New definition: " . $new_xml_file . "\n";
// setup file pointers, depending on stage file mode -- single (all the same) or multiple
if (dbsteward::$single_stage_upgrade) {
$single_stage_upgrade_file = $upgrade_prefix . '_single_stage.sql';
$single_stage_fp = fopen($single_stage_upgrade_file, 'w');
if ($single_stage_fp === false) {
throw new exception("failed to open upgrade single stage output file " . $single_stage_upgrade_file . ' for write');
}
$stage1_ofs = new output_file_segmenter($single_stage_upgrade_file, 1, $single_stage_fp, $single_stage_upgrade_file);
$stage1_ofs->set_header("-- DBSteward single stage upgrade changes - generated " . $timestamp . "\n" . $old_set_new_set);
$stage2_ofs =& $stage1_ofs;
$stage3_ofs =& $stage1_ofs;
$stage4_ofs =& $stage1_ofs;
} else {
$stage1_ofs = new output_file_segmenter($upgrade_prefix . '_stage1_schema', 1);
$stage1_ofs->set_header("-- DBSteward stage 1 structure additions and modifications - generated " . $timestamp . "\n" . $old_set_new_set);
$stage2_ofs = new output_file_segmenter($upgrade_prefix . '_stage2_data', 1);
$stage2_ofs->set_header("-- DBSteward stage 2 data definitions removed - generated " . $timestamp . "\n" . $old_set_new_set);
$stage3_ofs = new output_file_segmenter($upgrade_prefix . '_stage3_schema', 1);
$stage3_ofs->set_header("-- DBSteward stage 3 structure changes, constraints and removals - generated " . $timestamp . "\n" . $old_set_new_set);
$stage4_ofs = new output_file_segmenter($upgrade_prefix . '_stage4_data', 1);
$stage4_ofs->set_header("-- DBSteward stage 4 data definition changes and additions - generated " . $timestamp . "\n" . $old_set_new_set);
}
dbsteward::$old_database = $old_database;
dbsteward::$new_database = $new_database;
static::diff_doc_work($stage1_ofs, $stage2_ofs, $stage3_ofs, $stage4_ofs);
}
/**
* Creates SQL diff of two SQL dumps
*
* @return array output files list
*/
public static function diff_sql($old_sql_files, $new_sql_files, $upgrade_prefix)
{
dbsteward::$old_database = pgsql8_dump_loader::load_database($old_sql_files);
$old_sql_xml_file = $upgrade_prefix . '_old_sql.xml';
xml_parser::save_xml($old_sql_xml_file, dbsteward::$old_database->saveXML());
dbsteward::$new_database = pgsql8_dump_loader::load_database($new_sql_files);
$new_sql_xml_file = $upgrade_prefix . '_new_sql.xml';
xml_parser::save_xml($new_sql_xml_file, dbsteward::$new_database->saveXML());
self::diff_xml(array($old_sql_xml_file), array($new_sql_xml_file), $upgrade_prefix);
}
private function common_diff($xml_a, $xml_b, $expected1, $expected3, $message = '')
{
dbsteward::$old_database = new SimpleXMLElement($this->db_doc_xml . $xml_a . '</dbsteward>');
dbsteward::$new_database = new SimpleXMLElement($this->db_doc_xml . $xml_b . '</dbsteward>');
$ofs1 = new mock_output_file_segmenter();
$ofs3 = new mock_output_file_segmenter();
pgsql8_diff_tables::diff_tables($ofs1, $ofs3, dbsteward::$old_database->schema, dbsteward::$new_database->schema);
$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}");
}
/**
* @group pgsql8
*/
public function testTableColumnTypeQuotingPgsql8()
{
dbsteward::set_sql_format('pgsql8');
dbsteward::$quote_all_names = TRUE;
dbsteward::$single_stage_upgrade = TRUE;
$doc_empty = simplexml_load_string($this->xml_empty);
$doc_empty = xml_parser::composite_doc(FALSE, $doc_empty);
dbsteward::$old_database = $doc_empty;
$doc = simplexml_load_string($this->xml);
$doc = xml_parser::composite_doc(FALSE, $doc);
dbsteward::$new_database = $doc;
$table_dependency = xml_parser::table_dependency_order($doc);
//var_dump(xml_parser::format_xml($doc_empty->saveXML()));
//var_dump(xml_parser::format_xml($doc->saveXML()));
$schema = $doc->schema;
$table = $schema->table;
// make sure the type is named with quoting as part of a definition build
$expected = "CREATE TYPE \"schema1\".\"enumCamelCaseType\" AS ENUM ('Read','Write','Delete');";
$mofs = new mock_output_file_segmenter();
pgsql8::build_schema($doc, $mofs, $table_dependency);
$actual = trim($mofs->_get_output());
$this->assertContains($expected, $actual);
// make sure the type is referred to with quoting in a table creation as part of a definition build
$expected_column = '"table_shable_mode" "enumCamelCaseType"';
$this->assertContains($expected_column, $actual);
// make sure the type is referred to with quoting when generating table create statements
$expected = '"table_shable_mode" "enumCamelCaseType"';
$sql = pgsql8_table::get_creation_sql($schema, $table);
$this->assertContains($expected, $sql);
// make sure create table quotes the type name
$expected = '"table_shable_mode" "enumCamelCaseType"';
$mofs = new mock_output_file_segmenter();
var_dump(dbx::get_tables($schema));
pgsql8_diff_tables::diff_tables($mofs, $mofs, NULL, $schema);
$actual = trim($mofs->_get_output());
$this->assertContains($expected, $actual);
// make sure insert statements are made that match the XML definition
$expected = "INSERT INTO \"schema1\".\"table_shable\" (\"table_shable_id\", \"table_shable_value\", \"table_shable_mode\") VALUES (1, E'shim sham', BETA);";
$actual = trim(pgsql8_diff_tables::get_data_sql(NULL, NULL, $schema, $table, FALSE));
$this->assertContains($expected, $actual);
}
public function setUp()
{
dbsteward::set_sql_format('mysql5');
dbsteward::$quote_schema_names = TRUE;
dbsteward::$quote_table_names = TRUE;
dbsteward::$quote_column_names = TRUE;
dbsteward::$quote_function_names = TRUE;
dbsteward::$quote_object_names = TRUE;
mysql5::$use_auto_increment_table_options = FALSE;
mysql5::$use_schema_name_prefix = FALSE;
$db_doc_xml = <<<XML
<dbsteward>
<database>
<role>
<owner>the_owner</owner>
<customRole>SOMEBODY</customRole>
</role>
</database>
</dbsteward>
XML;
dbsteward::$old_database = new SimpleXMLElement($db_doc_xml);
dbsteward::$new_database = new SimpleXMLElement($db_doc_xml);
}
private function doTestUpgradesInOrder($format)
{
dbsteward::set_sql_format($format);
$doc = $this->doc_with;
$actual = $this->capture(function ($ofs) use($doc) {
dbsteward::$new_database = $doc;
dbsteward::$old_database = $doc;
dbsteward::$single_stage_upgrade = true;
format_diff::$as_transaction = false;
format_diff::update_structure($ofs, $ofs);
});
if ($format == 'pgsql8') {
$expected = <<<SQL
DROP VIEW IF EXISTS "public"."view2";
DROP VIEW IF EXISTS "public"."view1";
CREATE OR REPLACE VIEW "public"."view2" AS SELECT * FROM elsewhere;
ALTER VIEW "public"."view2" OWNER TO deployment;
CREATE OR REPLACE VIEW "public"."view1" AS SELECT * FROM view2;
ALTER VIEW "public"."view1" OWNER TO deployment;
SQL;
} else {
$expected = <<<SQL
DROP VIEW IF EXISTS `view2`;
DROP VIEW IF EXISTS `view1`;
CREATE OR REPLACE DEFINER = deployment SQL SECURITY DEFINER VIEW `view2` AS SELECT * FROM elsewhere;
CREATE OR REPLACE DEFINER = deployment SQL SECURITY DEFINER VIEW `view1` AS SELECT * FROM view2;
SQL;
}
$this->assertEquals($expected, $actual);
}
protected function upgrade_db($format, $ofs1, $ofs2, $ofs3, $ofs4)
{
dbsteward::set_sql_format($format);
$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;
$diff_class = $format . '_diff';
$diff_class::$old_table_dependency = xml_parser::table_dependency_order($doc_a);
$diff_class::$new_table_dependency = xml_parser::table_dependency_order($doc_b);
$diff_class::diff_doc_work($ofs1, $ofs2, $ofs3, $ofs4);
}
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}");
}
private function common($a, $b, $expected, $type = 'all')
{
$dbs_a = new SimpleXMLElement($a);
$dbs_b = new SimpleXMLElement($b);
$ofs = new mock_output_file_segmenter();
dbsteward::$old_database = $dbs_a;
dbsteward::$new_database = $dbs_b;
mysql5_diff_constraints::diff_constraints_table($ofs, $dbs_a->schema, $dbs_a->schema->table, $dbs_b->schema, $dbs_b->schema->table, $type, true);
mysql5_diff_constraints::diff_constraints_table($ofs, $dbs_a->schema, $dbs_a->schema->table, $dbs_b->schema, $dbs_b->schema->table, $type, false);
$actual = trim(preg_replace("/--.*\n/", '', $ofs->_get_output()));
$this->assertEquals($expected, $actual);
}
public function testExceptionIsThrownWithDupes()
{
dbsteward::set_sql_format('pgsql8');
dbsteward::$quote_all_names = TRUE;
dbsteward::$single_stage_upgrade = TRUE;
$doc_empty = simplexml_load_string($this->xml_empty);
$doc_empty = xml_parser::composite_doc(FALSE, $doc_empty);
dbsteward::$old_database = $doc_empty;
$doc = simplexml_load_string($this->xml);
$doc = xml_parser::composite_doc(FALSE, $doc);
dbsteward::$new_database = $doc;
$table_dependency = xml_parser::table_dependency_order($doc);
$schema = $doc->schema;
$table = $schema->table;
// make sure the type is named with quoting as part of a definition build
$mofs = new mock_output_file_segmenter();
try {
pgsql8::build_schema($doc, $mofs, $table_dependency);
} catch (Exception $e) {
$this->assertContains('duplicate index name', strtolower($e->getMessage()));
return;
}
$this->fail("build_schema did not detect duplicate index names");
}
protected function transaction_statement_check($is_transactional)
{
dbsteward::$old_database = new SimpleXMLElement($this->oldxml);
dbsteward::$new_database = new SimpleXMLElement($this->newxml);
pgsql8_diff::$new_table_dependency = xml_parser::table_dependency_order(dbsteward::$new_database);
pgsql8_diff::$old_table_dependency = xml_parser::table_dependency_order(dbsteward::$old_database);
$ofs = new mock_output_file_segmenter();
pgsql8_diff::diff_doc_work($ofs, $ofs, $ofs, $ofs);
if ($is_transactional) {
$this->assertRegExp('/^BEGIN;/im', $ofs->_get_output(), 'output contains BEGIN statement');
$this->assertRegExp('/^COMMIT;/im', $ofs->_get_output(), 'output contains COMMIT statement');
} else {
$this->assertRegExp('/^(?!BEGIN;).*$/im', $ofs->_get_output(), 'output contains BEGIN statement');
$this->assertRegExp('/^(?!COMMIT;).*$/im', $ofs->_get_output(), 'output contains COMMIT statement');
}
}
private function common_drop($xml_a, $xml_b, $expected, $message = NULL)
{
dbsteward::$old_database = new SimpleXMLElement($this->db_doc_xml . $xml_a . '</dbsteward>');
dbsteward::$new_database = new SimpleXMLElement($this->db_doc_xml . $xml_b . '</dbsteward>');
$ofs = new mock_output_file_segmenter();
mysql5_diff_tables::drop_tables($ofs, dbsteward::$old_database->schema, dbsteward::$new_database->schema);
$actual = trim($ofs->_get_output());
$this->assertEquals($expected, $actual, $message);
}
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);
}
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, $expected)
{
$ofs = new mock_output_file_segmenter();
$old = '<dbsteward><database/>' . $old . '</dbsteward>';
$new = '<dbsteward><database/>' . $new . '</dbsteward>';
$old_doc = simplexml_load_string($old);
$new_doc = simplexml_load_string($new);
dbsteward::$old_database = $old_doc;
dbsteward::$new_database = $new_doc;
pgsql8_diff::$old_table_dependency = xml_parser::table_dependency_order($old_doc);
pgsql8_diff::$new_table_dependency = xml_parser::table_dependency_order($new_doc);
pgsql8_diff_types::apply_changes($ofs, $old_doc->schema, $new_doc->schema);
$sql = trim(preg_replace('/\\n\\n+/', "\n", preg_replace('/^--.*$/m', '', $ofs->_get_output())));
$this->assertEquals($expected, $sql);
}
protected function diff_definitions($output_prefix)
{
dbsteward::$old_database = new SimpleXMLElement($this->oldxml);
dbsteward::$new_database = new SimpleXMLElement($this->newxml);
pgsql8_diff::$new_table_dependency = xml_parser::table_dependency_order(dbsteward::$new_database);
pgsql8_diff::$old_table_dependency = xml_parser::table_dependency_order(dbsteward::$old_database);
$output_prefix_path = dirname(__FILE__) . '/../testdata/' . $output_prefix;
pgsql8::build_upgrade('', 'old_SlonyStageTransactionalityTest', dbsteward::$old_database, array(), $output_prefix_path, 'new_SlonyStageTransactionalityTest', dbsteward::$new_database, array());
return $output_prefix_path;
}