public function testSlonikOutputIsCorrect()
{
$xml = <<<OUTXML
<?xml version="1.0" encoding="UTF-8"?>
<dbsteward>
<database>
<sqlformat>pgsql8</sqlformat>
<role>
<application>application</application>
<owner>dba</owner>
<replication>slony</replication>
<readonly>readonly</readonly>
</role>
<slony clusterName="aim">
<slonyNode id="1" comment="Master" dbPassword="******" dbUser="******" dbHost="db00" dbName="mrh"/>
<slonyNode id="2" comment="Replica" dbPassword="******" dbUser="******" dbHost="db01" dbName="mrh"/>
<slonyReplicaSet id="101" comment="only set" originNodeId="1" upgradeSetId="2">
<slonyReplicaSetNode id="2" providerNodeId="1"/>
</slonyReplicaSet>
<slonyReplicaSet id="201" comment="only set" originNodeId="1" upgradeSetId="3">
<slonyReplicaSetNode id="2" providerNodeId="1"/>
</slonyReplicaSet>
</slony>
</database>
<schema name="public" owner="ROLE_OWNER">
<table name="log" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="log_pkey" slonySetId="101" slonyId="101">
<column name="id" type="bigserial" slonySetId="101" slonyId="101"/>
<grant role="ROLE_APPLICATION" operation="INSERT, SELECT, UPDATE, DELETE"/>
</table>
<table name="log" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="log_pkey" slonySetId="101" slonyId="102">
<column name="id" type="bigserial" slonySetId="101" slonyId="102"/>
<grant role="ROLE_APPLICATION" operation="INSERT, SELECT, UPDATE, DELETE"/>
</table>
<table name="log" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="log_pkey" slonySetId="201" slonyId="105">
<column name="id" type="bigserial" slonySetId="201" slonyId="105"/>
<grant role="ROLE_APPLICATION" operation="INSERT, SELECT, UPDATE, DELETE"/>
</table>
<table name="log" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="log_pkey" slonySetId="201" slonyId="106">
<column name="id" type="bigserial" slonySetId="201" slonyId="106"/>
<grant role="ROLE_APPLICATION" operation="INSERT, SELECT, UPDATE, DELETE"/>
</table>
<table name="log" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="log_pkey" slonyId="1098">
<column name="id" type="bigserial" slonyId="1098"/>
<grant role="ROLE_APPLICATION" operation="INSERT, SELECT, UPDATE, DELETE"/>
</table>
<!-- here for additional changes -->
</schema>
</dbsteward>
OUTXML;
$old_db_doc = simplexml_load_string($xml);
dbsteward::$generate_slonik = TRUE;
$output_prefix_path = dirname(__FILE__) . '/../testdata/' . 'slony_id_output';
pgsql8::build($output_prefix_path, $old_db_doc);
$this->assertLogged(Monolog\Logger::NOTICE, '/101:\\s101-102/');
// before 1098 wasn't getting put into first natural order, now it should be
$this->assertLogged(Monolog\Logger::NOTICE, '/101:\\s[\\d\\-]+,\\s*1098/', "SlonyIds without slonySetIds are not put into first natural order slonySet");
$this->assertLogged(Monolog\Logger::NOTICE, '/201:\\s105-106/');
}
/**
* There was a bug in streaker where it wasn't counting the entire first streak, output used to be for below: 1, 5-6, 98-98
*/
public function testSlonikStreakerIsGood()
{
$xml = <<<SLONXML
<?xml version="1.0" encoding="UTF-8"?>
<dbsteward>
<database>
<sqlformat>pgsql8</sqlformat>
<role>
<application>application</application>
<owner>dba</owner>
<replication>slony</replication>
<readonly>readonly</readonly>
</role>
<slony clusterName="aim">
<slonyNode id="1" comment="Master" dbPassword="******" dbUser="******" dbHost="db00" dbName="mrh"/>
<slonyNode id="2" comment="Replica" dbPassword="******" dbUser="******" dbHost="db01" dbName="mrh"/>
<slonyReplicaSet id="1" comment="only set" originNodeId="1" upgradeSetId="2">
<slonyReplicaSetNode id="2" providerNodeId="1"/>
</slonyReplicaSet>
</slony>
</database>
<schema name="public" owner="ROLE_OWNER">
<table name="log_sl" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="log_pkey" slonyId="1">
<column name="id" type="bigserial" slonyId="1"/>
<grant role="ROLE_APPLICATION" operation="INSERT, SELECT, UPDATE, DELETE"/>
</table>
<table name="log_sl2" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="log_pkey" slonyId="2">
<column name="id" type="bigserial" slonyId="2"/>
<grant role="ROLE_APPLICATION" operation="INSERT, SELECT, UPDATE, DELETE"/>
</table>
<table name="log_sl3" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="log_pkey" slonyId="5">
<column name="id" type="bigserial" slonyId="5"/>
<grant role="ROLE_APPLICATION" operation="INSERT, SELECT, UPDATE, DELETE"/>
</table>
<table name="log_sl4" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="log_pkey" slonyId="6">
<column name="id" type="bigserial" slonyId="6"/>
<grant role="ROLE_APPLICATION" operation="INSERT, SELECT, UPDATE, DELETE"/>
</table>
<table name="log_sl5" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="log_pkey" slonyId="98">
<column name="id" type="bigserial" slonyId="98"/>
<grant role="ROLE_APPLICATION" operation="INSERT, SELECT, UPDATE, DELETE"/>
</table>
<!-- here for additional changes -->
</schema>
</dbsteward>
SLONXML;
$old_db_doc = simplexml_load_string($xml);
dbsteward::$generate_slonik = TRUE;
$output_prefix_path = dirname(__FILE__) . '/../testdata/' . 'slony_id_streak';
pgsql8::build($output_prefix_path, $old_db_doc);
$this->assertLogged(Monolog\Logger::NOTICE, '/sequence ID segments.*:\\s1-2, 5-6, 98/');
}
public function setUp()
{
parent::setUp();
dbsteward::set_sql_format('pgsql8');
// reset these flags before each test
pgsql8_diff::$as_transaction = TRUE;
dbsteward::$require_slony_set_id = FALSE;
dbsteward::$require_slony_id = FALSE;
dbsteward::$generate_slonik = FALSE;
dbsteward::$slonyid_set_value = 1;
dbsteward::$slonyid_start_value = 1;
// clear these before each test so we don't run into conflicts
pgsql8::$table_slony_ids = array();
pgsql8::$sequence_slony_ids = array();
pgsql8_diff::$new_table_dependency = null;
pgsql8_diff::$old_table_dependency = null;
// rest test fixtures
$this->in_doc = null;
$this->slonyid_doc = null;
}
protected function setup_pgsql8()
{
$xml = <<<XML
<dbsteward>
<database>
<sqlformat>pgsql8</sqlformat>
<role>
<application>app_application</application>
<owner>postgres</owner>
<replication>app_slony</replication>
<readonly>app_readonly</readonly>
</role>
</database>
<schema name="app" owner="ROLE_OWNER">
<table name="my_table" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="my_table_pk" slonyId="0">
<column name="id" type="character varying(32)" null="false"/>
<column name="action" type="character varying(32)"/>
<column name="description" type="character varying(200)"/>
<rows columns="id, action, description">
<row>
<col>1</col>
<col>Row 1</col>
<col>Action 1 Description</col>
</row>
<row>
<col>2</col>
<col>Row 2</col>
<col>Action 2 Description</col>
</row>
<row>
<col>3</col>
<col>Row 3</col>
<col>Action 3 Description</col>
</row>
<row>
<col>4</col>
<col>Row 4</col>
<col>Action 4 Description</col>
</row>
<row>
<col>5</col>
<col>Row 5</col>
<col>Action 5 Description</col>
</row>
</rows>
</table>
</schema>
</dbsteward>
XML;
$xml_data_overlay = <<<XML
<dbsteward>
<database>
<role>
<application>client_app_application</application>
<owner>postgres</owner>
<replication>client_app_slony</replication>
<readonly>client_app_readonly</readonly>
</role>
</database>
<schema name="app" owner="ROLE_OWNER">
<table name="my_table" owner="ROLE_OWNER" primaryKey="id" primaryKeyName="my_table_pk" slonyId="0">
<rows columns="id, description">
<row>
<col>2</col>
<col>Action 2 Alternate Description</col>
</row>
<row>
<col>4</col>
<col>Action 4 Alternate Description</col>
</row>
<row>
<col>5</col>
<col>Action 5 Alternate Description</col>
</row>
</rows>
</table>
</schema>
</dbsteward>
XML;
$this->xml_file_a = dirname(__FILE__) . '/../testdata/pgsql8_unit_test_xml_a.xml';
$this->xml_file_b = dirname(__FILE__) . '/../testdata/pgsql8_unit_test_xml_b.xml';
$this->xml_file_c = dirname(__FILE__) . '/../testdata/pgsql8_unit_test_xml_c.xml';
$this->set_xml_content_a($xml);
$this->set_xml_content_b($xml);
$this->set_xml_content_c($xml_data_overlay);
$this->output_prefix = dirname(__FILE__) . '/../testdata/pgsql8_test_identical';
dbsteward::$single_stage_upgrade = TRUE;
dbsteward::$generate_slonik = FALSE;
$old_db_doc_comp = xml_parser::xml_composite(array($this->xml_file_a, $this->xml_file_c));
$new_db_doc_comp = xml_parser::xml_composite(array($this->xml_file_b, $this->xml_file_c));
pgsql8::build_upgrade('', 'identical_diff_test_pgsql8_old', $old_db_doc_comp, array(), $this->output_prefix, 'identical_diff_test_pgsql8_new', $new_db_doc_comp, array());
}
protected function setup_pgsql8()
{
$base_xml = '';
$strict_overlay_xml = '';
$new_table_xml = '';
$this->setup_definition_xml($base_xml, $strict_overlay_xml, $new_table_xml);
$this->xml_file_a = dirname(__FILE__) . '/../testdata/pgsql8_unit_test_xml_a.xml';
$this->xml_file_b = dirname(__FILE__) . '/../testdata/pgsql8_unit_test_xml_b.xml';
$this->xml_file_c = dirname(__FILE__) . '/../testdata/pgsql8_unit_test_xml_c.xml';
$this->set_xml_content_a($base_xml);
$this->set_xml_content_b($strict_overlay_xml);
$this->set_xml_content_c($new_table_xml);
$this->output_prefix = dirname(__FILE__) . '/../testdata/pgsql8_test_column_nulls';
dbsteward::$quote_column_names = TRUE;
dbsteward::$single_stage_upgrade = TRUE;
dbsteward::$generate_slonik = FALSE;
}
/**
* @group pgsql8
*/
public function testSerialStartPGSQL8()
{
dbsteward::$generate_slonik = FALSE;
// build version a
$ofs = new mock_output_file_segmenter();
$this->build_db('pgsql8', $ofs);
$xml_a_sql = $ofs->_get_output();
// 1) Confirm serial starts are applied when creating new tables
$this->assertRegExp('/-- serialStart 1234 specified for user_info\\.user.user_id/i', $xml_a_sql, "serialStart specification not announced in a comment");
$this->assertRegExp("/SELECT setval\\(pg_get_serial_sequence\\('user_info.user', 'user_id'\\), 1234, TRUE\\);/i", $xml_a_sql, "sequence start not being set via setval");
// diff and apply upgrade
$ofs4 = new mock_output_file_segmenter();
$this->upgrade_db('pgsql8', $ofs, $ofs, $ofs, $ofs4);
$xml_b_upgrade_stage4_data1_sql = $ofs4->_get_output();
// 2) Confirm when adding new tables with serial columns that serial starts are applied in stage 4
$this->assertRegExp('/-- serialStart 5678 specified for user_info.user_attribute.user_attribute_id/i', $xml_b_upgrade_stage4_data1_sql, "serialStart specification not announced in a comment in stage 4");
$this->assertRegExp("/SELECT setval\\(pg_get_serial_sequence\\('user_info.user_attribute', 'user_attribute_id'\\), 5678, TRUE\\);/i", $xml_b_upgrade_stage4_data1_sql, "sequence start not being set via setval in stage 4");
}
/**
* @group pgsql8
*/
public function testGoodSerialIdPGSQL8()
{
// reset options
$this->apply_options_pgsql8();
dbsteward::$require_slony_id = TRUE;
dbsteward::$generate_slonik = TRUE;
$this->set_xml_content_a($this->pgsql8_xml_good_serial_id);
// build the DDL first, incase dbsteward code wants to throw about something
pgsql8::build($this->output_prefix, xml_parser::xml_composite(array($this->xml_file_a)));
// if that worked, build a db with this definition
$this->pgsql8->create_db();
$this->assertStringNotEqualsFile($this->output_prefix . '_build.sql', '');
$this->pgsql8->run_file($this->output_prefix . '_build.sql');
}
public function arg_parse($argv)
{
$short_opts = 'hvq';
$long_opts = array("sqlformat::", "xml::", "oldxml::", "newxml::", "pgdataxml::", "xmldatainsert::", "outputfile::", "dbschemadump::", "slonikconvert::", "slonycompare::", "slonydiffold::", "slonydiffnew::", "slonyidin::", "slonyidout::", "slonyidstartvalue::", "slonyidsetvalue::", "oldsql::", "newsql::", "dbhost::", "dbport::", "dbname::", "dbuser::", "dbpassword::", "requireslonyid::", "requireslonysetid::", "generateslonik::", "quoteschemanames::", "quotetablenames::", "quotecolumnnames::", "quoteallnames::", "quoteillegalnames::", "quotereservednames::", "onlyschemasql::", "onlydatasql::", "onlytable::", "singlestageupgrade::", "maxstatementsperfile::", "ignoreoldnames::", "ignorecustomroles::", "ignoreprimarykeyerrors::", "dbdatadiff::", "xmlsort::", "xmlconvert::", "xmlcollectdataaddendums::", "useautoincrementoptions::", "useschemaprefix::", "outputdir::", "outputfileprefix::", "debug");
$options = getopt($short_opts, $long_opts);
self::set_verbosity($options);
if (count($argv) == 1 || isset($options['help']) || isset($options['h'])) {
$c = new Colors\Color();
$c->setTheme(array('header' => array('underline', 'dark_gray'), 'keyword' => array('green'), 'value' => array('yellow')));
echo $c->colorize(self::usage()) . PHP_EOL;
exit(1);
}
$files = array('old' => array(), 'new' => array(), 'pgdata' => array());
///// XML file parameter sanity checks
if (isset($options['xml'])) {
if (count($options['xml']) > 0 && isset($options['oldxml']) && count($options['oldxml']) > 0) {
dbsteward::error("Parameter error: xml and oldxml options are not to be mixed. Did you mean newxml?");
exit(1);
}
if (count($options['xml']) > 0 && isset($options['newxml']) && count($options['newxml']) > 0) {
dbsteward::error("Parameter error: xml and newxml options are not to be mixed. Did you mean oldxml?");
exit(1);
}
}
if (isset($options['oldxml']) && count($options['oldxml']) > 0 && (!isset($options['newxml']) || count($options['newxml']) == 0)) {
dbsteward::error("Parameter error: oldxml needs newxml specified for differencing to occur");
exit(1);
}
if ((!isset($options['oldxml']) || count($options['oldxml']) == 0) && (isset($options['newxml']) && count($options['newxml']) > 0)) {
dbsteward::error("Parameter error: oldxml needs newxml specified for differencing to occur");
exit(1);
}
///// database connectivity values
$dbhost = FALSE;
if (isset($options["dbhost"]) && strlen($options["dbhost"]) > 0) {
$dbhost = $options["dbhost"];
}
// $dbport set in sql_format defaults section
$dbport = NULL;
if (isset($options["dbport"]) && strlen($options["dbport"]) > 0) {
$dbport = $options["dbport"];
}
$dbname = FALSE;
if (isset($options["dbname"]) && strlen($options["dbname"]) > 0) {
$dbname = $options["dbname"];
}
$dbuser = FALSE;
if (isset($options["dbuser"]) && strlen($options["dbuser"]) > 0) {
$dbuser = $options["dbuser"];
}
if (isset($options['dbpassword'])) {
if ($options['dbpassword'] === false) {
// treat --dbpassword as the empty password, because
// --dbpassword='' doesn't show up in $options
$this->cli_dbpassword = '';
} else {
$this->cli_dbpassword = $options['dbpassword'];
}
}
///// SQL DDL DML DCL output flags
if (isset($options["onlyschemasql"])) {
dbsteward::$only_schema_sql = TRUE;
}
if (isset($options["onlydatasql"])) {
dbsteward::$only_data_sql = TRUE;
}
if (isset($options['onlytable'])) {
$onlytables = $options['onlytable'];
if (!is_array($onlytables)) {
$onlytables = array($onlytables);
}
foreach ($onlytables as $onlytable) {
$onlytable_schema = 'public';
$onlytable_table = $onlytable;
if (strpos($onlytable_table, '.') !== FALSE) {
$chunks = explode('.', $onlytable_table);
$onlytable_schema = $chunks[0];
$onlytable_table = $chunks[1];
}
if (!isset(dbsteward::$limit_to_tables[$onlytable_schema])) {
dbsteward::$limit_to_tables[$onlytable_schema] = array();
}
dbsteward::$limit_to_tables[$onlytable_schema][] = $onlytable_table;
}
}
///// common parameter for output file for converter functions
// for modes that can do it, omitting this parameter will cause output to be directed to stdout
$output_file = FALSE;
if (isset($options["outputfile"]) && strlen($options["outputfile"]) > 0) {
$output_file = $options["outputfile"];
}
if (isset($options["maxstatementsperfile"])) {
if (!is_numeric($options["maxstatementsperfile"])) {
throw new exception("maxstatementsperfile passed is not a number");
}
dbsteward::$output_file_statement_limit = $options["maxstatementsperfile"];
}
///// XML parsing switches
if (isset($options["singlestageupgrade"])) {
dbsteward::$single_stage_upgrade = TRUE;
// don't recreate views when in single stage upgrade mode
// @TODO: make view diffing smart enough that this doesn't need to be done
dbsteward::$always_recreate_views = FALSE;
}
if (isset($options["ignoreoldnames"])) {
dbsteward::$ignore_oldnames = TRUE;
}
if (isset($options["ignorecustomroles"])) {
dbsteward::$ignore_custom_roles = TRUE;
}
if (isset($options["ignoreprimarykeyerrors"])) {
dbsteward::$ignore_primary_key_errors = TRUE;
}
if (isset($options["requireslonyid"])) {
dbsteward::$require_slony_id = TRUE;
}
if (isset($options["requireslonysetid"])) {
dbsteward::$require_slony_set_id = TRUE;
}
if (isset($options["generateslonik"])) {
dbsteward::$generate_slonik = TRUE;
}
if (isset($options["slonyidstartvalue"])) {
if ($options["slonyidstartvalue"] < 1) {
throw new exception("slonyidstartvalue must be greater than 0");
}
dbsteward::$slonyid_start_value = $options["slonyidstartvalue"];
}
if (isset($options["slonyidsetvalue"])) {
if ($options["slonyidsetvalue"] < 1) {
throw new exception("slonyidsetvalue must be greater than 0");
}
dbsteward::$slonyid_set_value = $options["slonyidsetvalue"];
}
///// determine the operation and check arguments for each
$mode = dbsteward::MODE_UNKNOWN;
if (isset($options['xmldatainsert'])) {
if (!isset($options['xml'])) {
throw new exception("xmldatainsert needs xml parameter defined");
}
$mode = dbsteward::MODE_XML_DATA_INSERT;
} elseif (isset($options["xmlsort"])) {
$mode = dbsteward::MODE_XML_SORT;
} elseif (isset($options["xmlconvert"])) {
$mode = dbsteward::MODE_XML_CONVERT;
} elseif (isset($options['xml']) && count($options['xml']) > 0) {
$mode = dbsteward::MODE_BUILD;
} elseif (isset($options['newxml']) && count($options['newxml']) > 0) {
$mode = dbsteward::MODE_DIFF;
} elseif (isset($options["dbschemadump"])) {
if (strlen($dbhost) === FALSE) {
throw new exception("dbschemadump error: dbhost not specified");
} elseif (strlen($dbname) === FALSE) {
throw new exception("dbschemadump error: dbname not specified");
} elseif (strlen($dbuser) === FALSE) {
throw new exception("dbschemadump error: dbuser not specified");
} elseif ($output_file === FALSE) {
throw new exception("dbschemadump error: outputfile not specified");
}
$mode = dbsteward::MODE_EXTRACT;
} elseif (isset($options['dbdatadiff'])) {
if (strlen($dbhost) === FALSE) {
throw new exception("dbdatadiff error: dbhost not specified");
} elseif (strlen($dbname) === FALSE) {
throw new exception("dbdatadiff error: dbname not specified");
} elseif (strlen($dbuser) === FALSE) {
throw new exception("dbdatadiff error: dbuser not specified");
}
$mode = dbsteward::MODE_DB_DATA_DIFF;
} elseif (isset($options["oldsql"]) || isset($options["newsql"])) {
if ($output_file === FALSE) {
throw new exception("sql diff error: you must specify an outputfile for this mode");
}
$mode = dbsteward::MODE_SQL_DIFF;
} elseif (isset($options["slonikconvert"])) {
$mode = dbsteward::MODE_SLONIK_CONVERT;
} elseif (isset($options["slonycompare"])) {
$mode = dbsteward::MODE_SLONY_COMPARE;
} elseif (isset($options["slonydiffold"])) {
$mode = dbsteward::MODE_SLONY_DIFF;
} elseif (isset($options["slonyidin"])) {
// check to make sure output is not same as input
if (isset($options["slonyidout"])) {
if (strcmp($options["slonyidin"], $options["slonyidout"]) == 0) {
throw new exception("slonyidin and slonyidout file paths should not be the same");
}
}
$mode = dbsteward::MODE_XML_SLONY_ID;
}
///// File output location specificity
if (isset($options['outputdir'])) {
if (strlen($options['outputdir']) == 0) {
throw new exception("outputdir is blank, must specify a value for this option");
}
if (!is_dir($options['outputdir'])) {
throw new exception("outputdir is not a directory; this must be a writable directory");
}
dbsteward::$file_output_directory = $options['outputdir'];
}
if (isset($options['outputfileprefix'])) {
if (strlen($options['outputfileprefix']) == 0) {
throw new exception("outputfileprefix is blank, must specify a value for this option");
}
dbsteward::$file_output_prefix = $options['outputfileprefix'];
}
///// For the appropriate modes, composite the input XML
///// and figure out the SQL format of it
$force_sql_format = FALSE;
if (isset($options['sqlformat'])) {
$force_sql_format = $options['sqlformat'];
}
$target_sql_format = FALSE;
switch ($mode) {
case dbsteward::MODE_BUILD:
$files = (array) $options['xml'];
$target_sql_format = xml_parser::get_sql_format($files);
break;
case dbsteward::MODE_DIFF:
$old_files = (array) $options['oldxml'];
$new_files = (array) $options['newxml'];
$old_target = xml_parser::get_sql_format($old_files);
$new_target = xml_parser::get_sql_format($new_files);
// prefer the new sql_format
$target_sql_format = $new_target ?: $old_target;
break;
}
$xml_collect_data_addendums = 0;
if (isset($options["xmlcollectdataaddendums"]) && $options["xmlcollectdataaddendums"] > 0) {
$xml_collect_data_addendums = (int) $options["xmlcollectdataaddendums"];
if ($mode != dbsteward::MODE_BUILD) {
throw new Exception("--xmlcollectdataaddendums is only supported for fresh builds");
}
if ($xml_collect_data_addendums > count($files)) {
throw new Exception("Cannot collect more data addendums then files provided");
}
}
// announce our defined version before doing any configuration announcements or tasks
dbsteward::notice("DBSteward Version " . self::VERSION);
///// set the global SQL format
$sql_format = dbsteward::reconcile_sql_format($target_sql_format, $force_sql_format);
dbsteward::notice("Using sqlformat={$sql_format}");
dbsteward::set_sql_format($sql_format);
if (is_null($dbport)) {
$dbport = dbsteward::define_sql_format_default_values($sql_format, $options);
}
// user-specified overrides for identifier quoting
if (isset($options["quoteschemanames"])) {
dbsteward::$quote_schema_names = TRUE;
}
if (isset($options["quotetablenames"])) {
dbsteward::$quote_table_names = TRUE;
}
if (isset($options["quotecolumnnames"])) {
dbsteward::$quote_column_names = TRUE;
}
if (isset($options["quoteallnames"])) {
dbsteward::$quote_all_names = TRUE;
}
if (isset($options["quoteillegalnames"])) {
dbsteward::$quote_illegal_identifiers = TRUE;
}
if (isset($options["quotereservednames"])) {
dbsteward::$quote_reserved_identifiers = TRUE;
}
switch ($mode) {
case dbsteward::MODE_XML_DATA_INSERT:
dbsteward::xml_data_insert($options['xml'], $options['xmldatainsert']);
break;
case dbsteward::MODE_XML_SORT:
dbsteward::xml_sort($options['xmlsort']);
break;
case dbsteward::MODE_XML_CONVERT:
dbsteward::xml_convert($options['xmlconvert']);
break;
case dbsteward::MODE_XML_SLONY_ID:
dbsteward::info("Compositing XML file for Slony ID processing..");
$files = (array) $options['slonyidin'];
$db_doc = xml_parser::xml_composite($files);
dbsteward::info("XML files " . implode(' ', $files) . " composited");
$output_prefix = dbsteward::calculate_file_output_prefix($files);
$composite_file = $output_prefix . '_composite.xml';
$db_doc = xml_parser::sql_format_convert($db_doc);
xml_parser::vendor_parse($db_doc);
dbsteward::notice("Saving composite as " . $composite_file);
xml_parser::save_doc($composite_file, $db_doc);
dbsteward::notice("Slony ID numbering any missing attributes");
dbsteward::info("slonyidstartvalue = " . dbsteward::$slonyid_start_value);
dbsteward::info("slonyidsetvalue = " . dbsteward::$slonyid_set_value);
$slonyid_doc = xml_parser::slonyid_number($db_doc);
$slonyid_numbered_file = $output_prefix . '_slonyid_numbered.xml';
// if specified, use output file value instead of auto suffix
if (isset($options["slonyidout"])) {
$slonyid_numbered_file = $options["slonyidout"];
}
dbsteward::notice("Saving Slony ID numbered XML as " . $slonyid_numbered_file);
xml_parser::save_doc($slonyid_numbered_file, $slonyid_doc);
break;
case dbsteward::MODE_BUILD:
dbsteward::info("Compositing XML files..");
$addendums_doc = NULL;
if ($xml_collect_data_addendums > 0) {
dbsteward::info("Collecting {$xml_collect_data_addendums} data addendums");
}
$db_doc = xml_parser::xml_composite($files, $xml_collect_data_addendums, $addendums_doc);
if (isset($options['pgdataxml']) && count($options['pgdataxml'])) {
$pg_data_files = (array) $options['pgdataxml'];
dbsteward::info("Compositing pgdata XML files on top of XML composite..");
xml_parser::xml_composite_pgdata($db_doc, $pg_data_files);
dbsteward::info("postgres data XML files [" . implode(' ', $pg_data_files) . "] composited.");
}
dbsteward::info("XML files " . implode(' ', $files) . " composited");
$output_prefix = dbsteward::calculate_file_output_prefix($files);
$composite_file = $output_prefix . '_composite.xml';
$db_doc = xml_parser::sql_format_convert($db_doc);
xml_parser::vendor_parse($db_doc);
dbsteward::notice("Saving composite as " . $composite_file);
xml_parser::save_doc($composite_file, $db_doc);
if ($addendums_doc !== NULL) {
$addendums_file = $output_prefix . '_addendums.xml';
dbsteward::notice("Saving addendums as {$addendums_file}");
xml_parser::save_doc($addendums_file, $addendums_doc);
}
format::build($output_prefix, $db_doc);
break;
case dbsteward::MODE_DIFF:
dbsteward::info("Compositing old XML files..");
$old_db_doc = xml_parser::xml_composite($old_files);
dbsteward::info("Old XML files " . implode(' ', $old_files) . " composited");
dbsteward::info("Compositing new XML files..");
$new_db_doc = xml_parser::xml_composite($new_files);
if (isset($options['pgdataxml']) && count($options['pgdataxml'])) {
$pg_data_files = (array) $options['pgdataxml'];
dbsteward::info("Compositing pgdata XML files on top of new XML composite..");
xml_parser::xml_composite_pgdata($new_db_doc, $pg_data_files);
dbsteward::info("postgres data XML files [" . implode(' ', $pg_data_files) . "] composited");
}
dbsteward::info("New XML files " . implode(' ', $new_files) . " composited");
$old_output_prefix = dbsteward::calculate_file_output_prefix($old_files);
$old_composite_file = $old_output_prefix . '_composite.xml';
$old_db_doc = xml_parser::sql_format_convert($old_db_doc);
xml_parser::vendor_parse($old_db_doc);
dbsteward::notice("Saving oldxml composite as " . $old_composite_file);
xml_parser::save_doc($old_composite_file, $old_db_doc);
$new_output_prefix = dbsteward::calculate_file_output_prefix($new_files);
$new_composite_file = $new_output_prefix . '_composite.xml';
$new_db_doc = xml_parser::sql_format_convert($new_db_doc);
xml_parser::vendor_parse($new_db_doc);
dbsteward::notice("Saving newxml composite as " . $new_composite_file);
xml_parser::save_doc($new_composite_file, $new_db_doc);
format::build_upgrade($old_output_prefix, $old_composite_file, $old_db_doc, $old_files, $new_output_prefix, $new_composite_file, $new_db_doc, $new_files);
break;
case dbsteward::MODE_EXTRACT:
$output = format::extract_schema($dbhost, $dbport, $dbname, $dbuser, $this->cli_dbpassword);
dbsteward::notice("Saving extracted database schema to " . $output_file);
if (!file_put_contents($output_file, $output)) {
throw new exception("Failed to save extracted database schema to " . $output_file);
}
break;
case dbsteward::MODE_DB_DATA_DIFF:
// dbdatadiff files are defined with --dbdatadiff not --xml
$dbdatadiff_files = (array) $options['dbdatadiff'];
dbsteward::info("Compositing XML files..");
$addendums_doc = NULL;
if ($xml_collect_data_addendums > 0) {
dbsteward::info("Collecting {$xml_collect_data_addendums} data addendums");
}
$db_doc = xml_parser::xml_composite($dbdatadiff_files, $xml_collect_data_addendums, $addendums_doc);
if (isset($options['pgdataxml']) && count($options['pgdataxml'])) {
$pg_data_files = (array) $options['pgdataxml'];
dbsteward::info("Compositing pgdata XML files on top of XML composite..");
xml_parser::xml_composite_pgdata($db_doc, $pg_data_files);
dbsteward::info("postgres data XML files [" . implode(' ', $pg_data_files) . "] composited.");
}
dbsteward::info("XML files " . implode(' ', $dbdatadiff_files) . " composited");
$output_prefix = dbsteward::calculate_file_output_prefix($dbdatadiff_files);
$composite_file = $output_prefix . '_composite.xml';
$db_doc = xml_parser::sql_format_convert($db_doc);
xml_parser::vendor_parse($db_doc);
dbsteward::notice("Saving composite as " . $composite_file);
xml_parser::save_doc($composite_file, $db_doc);
$output = format::compare_db_data($db_doc, $dbhost, $dbport, $dbname, $dbuser, $this->cli_dbpassword);
if (!file_put_contents($output_file, $output)) {
throw new exception("Failed to save extracted database schema to " . $output_file);
}
break;
case dbsteward::MODE_SQL_DIFF:
format::sql_diff($options["oldsql"], $options["newsql"], $output_file);
break;
case dbsteward::MODE_SLONIK_CONVERT:
$output = slony1_slonik::convert($options["slonikconvert"]);
if ($output_file !== FALSE) {
dbsteward::notice("Saving slonikconvert output to " . $output_file);
if (!file_put_contents($output, $output_file)) {
throw new exception("Failed to save slonikconvert output to " . $output_file);
}
} else {
echo $output;
}
break;
case dbsteward::MODE_SLONY_COMPARE:
pgsql8::slony_compare($options["slonycompare"]);
break;
case dbsteward::MODE_SLONY_DIFF:
pgsql8::slony_diff($options["slonydiffold"], $options["slonydiffnew"]);
break;
case dbsteward::MODE_UNKNOWN:
default:
throw new Exception("No operation specified!");
}
}
public function testGenerateSlonikRemovesTransactionStatements()
{
pgsql8_diff::$as_transaction = FALSE;
dbsteward::$generate_slonik = TRUE;
$this->transaction_statement_check(FALSE);
pgsql8_diff::$as_transaction = TRUE;
dbsteward::$generate_slonik = FALSE;
$this->transaction_statement_check(TRUE);
pgsql8_diff::$as_transaction = TRUE;
dbsteward::$generate_slonik = TRUE;
$this->transaction_statement_check(TRUE);
}
/**
* Structure Extraction Testing
*
* 1) Build a database from definition A
* 2) Extract database schema to definition B
* 3) Compare and expect zero differences between A and B with DBSteward difference engine
* 4) Check for and validate tables in resultant XML definiton
*/
public function do_structure_test($format, $xml)
{
$this->set_xml_content_a($xml);
// 1) Build a database from definition A
$this->build_db($format);
// test db built above most likely will not have slony; test will fail if
// this is not set false because it will look for slony definitions
// in extracted schema
dbsteward::$generate_slonik = FALSE;
// 2) Extract database schema to definition B
$conn = $this->get_connection($format);
$this->set_xml_content_b($format::extract_schema($conn->get_dbhost(), $conn->get_dbport(), $conn->get_dbname(), $conn->get_dbuser(), $conn->get_dbpass()));
// $this->write_xml_definition_to_disk();
// 3) Compare and expect zero differences between A and B
$this->apply_options($format);
dbsteward::$single_stage_upgrade = FALSE;
$old_db_doc = xml_parser::xml_composite(array($this->xml_file_a));
$new_db_doc = xml_parser::xml_composite(array($this->xml_file_b));
$format::build_upgrade('', $old_db_doc, $old_db_doc, array(), $this->output_prefix, $new_db_doc, $new_db_doc, array());
$upgrade_stage1_schema1_sql = $this->get_script($this->output_prefix . '_upgrade_stage1_schema1.sql');
$upgrade_stage2_data1_sql = $this->get_script($this->output_prefix . '_upgrade_stage2_data1.sql');
$upgrade_stage3_schema1_sql = $this->get_script($this->output_prefix . '_upgrade_stage3_schema1.sql');
$upgrade_stage4_data1_sql = $this->get_script($this->output_prefix . '_upgrade_stage4_data1.sql');
// check for no differences as expressed in DDL / DML
$this->assertNotRegExp('/^\\s*(ALTER|CREATE|DROP|UPDATE|DROP|INSERT)/im', $upgrade_stage1_schema1_sql);
$this->assertNotRegExp('/^\\s*(ALTER|CREATE|DROP|UPDATE|DROP|INSERT)/im', $upgrade_stage2_data1_sql);
$this->assertNotRegExp('/^\\s*(ALTER|CREATE|DROP|UPDATE|DROP|INSERT)/im', $upgrade_stage3_schema1_sql);
$this->assertNotRegExp('/^\\s*(ALTER|CREATE|DROP|UPDATE|DROP|INSERT)/im', $upgrade_stage4_data1_sql);
// 4) Check for and validate tables in resultant XML definiton
$this->compare_xml_definition();
}
/**
* @group mysql5
*/
public function testSingleStageColumnAndDataChangeMysql5()
{
$this->xml_content_a = $this->mysql5_single_xml_a;
$this->xml_file_a = __DIR__ . '/testdata/type_diff_xml_a.xml';
file_put_contents($this->xml_file_a, $this->xml_content_a);
$this->xml_content_b = $this->mysql5_single_xml_b;
$this->xml_file_b = __DIR__ . '/testdata/type_diff_xml_b.xml';
file_put_contents($this->xml_file_b, $this->xml_content_b);
$this->apply_options_mysql5();
// these options are applied when specifying --singlestageupgrade
dbsteward::$single_stage_upgrade = TRUE;
dbsteward::$always_recreate_views = FALSE;
$this->do_upgrade('mysql5');
//mysql5::build_upgrade($this->xml_file_a, $this->xml_file_b);
dbsteward::$generate_slonik = FALSE;
$upgrade_single_stage_sql = file_get_contents($this->output_prefix . '_upgrade_single_stage.sql');
$upgrade_single_stage_sql = preg_replace('/\\s+/', ' ', $upgrade_single_stage_sql);
$this->assertTrue((bool) preg_match('/' . preg_quote("ALTER TABLE `user` MODIFY COLUMN `user_name` varchar(64)", '/') . '/i', $upgrade_single_stage_sql, $matches), "user_name column type change was not found in upgrade_single_stage_sql.sql:\n{$upgrade_single_stage_sql}");
$this->assertTrue((bool) preg_match('/' . preg_quote("UPDATE `user` SET `user_name` = 'Administrator' WHERE (`user_id` = 1);", '/') . '/i', $upgrade_single_stage_sql), "Update of user_name column static value not found in upgrade_single_stage.sql:\n{$upgrade_single_stage_sql}");
}
/** Generates DDL for a build or upgrade given dbxml fragments **/
private function common($old, $new = FALSE, $generate_slonik = TRUE)
{
pgsql8::$table_slony_ids = array();
pgsql8::$sequence_slony_ids = array();
pgsql8::$known_pg_identifiers = array();
if (is_string($old) && empty($old)) {
// $old = <<<XML
//<table name="foo" owner="ROLE_OWNER" primaryKey="id" slonyId="1">
// <column name="id" type="int"/>
//</table>
//<sequence name="seq" owner="ROLE_OWNER" slonyId="4"/>
//XML;
}
$xml_a = <<<XML
<dbsteward>
<database>
<host>db-host</host>
<name>dbsteward</name>
<role>
<application>dbsteward_phpunit_app</application>
<owner>deployment</owner>
<replication/>
<readonly/>
</role>
<slony clusterName="duplicate_slony_ids_testsuite">
<slonyNode id="1" comment="DSI - Local Primary" dbName="test" dbHost="db-dev1" dbUser="******" dbPassword="******"/>
<slonyNode id="2" comment="DSI - Local Backup" dbName="test" dbHost="db-dev1" dbUser="******" dbPassword="******"/>
<slonyNode id="3" comment="DSI - Local Backup" dbName="test" dbHost="db-dev1" dbUser="******" dbPassword="******"/>
<slonyReplicaSet id="100" originNodeId="1" upgradeSetId="101" comment="common duplicate testing database definition">
<slonyReplicaSetNode id="2" providerNodeId="1"/>
<slonyReplicaSetNode id="3" providerNodeId="2"/>
</slonyReplicaSet>
</slony>
<configurationParameter name="TIME ZONE" value="America/New_York"/>
</database>
<schema name="dbsteward" owner="ROLE_OWNER">
{$old}
</schema>
</dbsteward>
XML;
$this->set_xml_content_a($xml_a);
if ($new) {
$xml_b = <<<XML
<dbsteward>
<database>
<host>db-host</host>
<name>dbsteward</name>
<role>
<application>dbsteward_phpunit_app</application>
<owner>deployment</owner>
<replication/>
<readonly/>
</role>
<slony clusterName="duplicate_slony_ids_testsuite">
<slonyNode id="1" comment="DSI - Local Primary" dbName="test" dbHost="db-dev1" dbUser="******" dbPassword="******"/>
<slonyNode id="2" comment="DSI - Local Backup" dbName="test" dbHost="db-dev1" dbUser="******" dbPassword="******"/>
<slonyNode id="3" comment="DSI - Local Backup" dbName="test" dbHost="db-dev1" dbUser="******" dbPassword="******"/>
<slonyReplicaSet id="100" originNodeId="1" upgradeSetId="101" comment="common duplicate testing database definition">
<slonyReplicaSetNode id="2" providerNodeId="1"/>
<slonyReplicaSetNode id="3" providerNodeId="2"/>
</slonyReplicaSet>
</slony>
<configurationParameter name="TIME ZONE" value="America/New_York"/>
</database>
<schema name="dbsteward" owner="ROLE_OWNER">
{$new}
</schema>
</dbsteward>
XML;
$this->set_xml_content_b($xml_b);
ob_start();
try {
// new parameters for function:
// $old_output_prefix, $old_composite_file, $old_db_doc, $old_files, $new_output_prefix, $new_composite_file, $new_db_doc, $new_files
$old_db_doc = simplexml_load_file($this->xml_file_a);
$new_db_doc = simplexml_load_file($this->xml_file_b);
dbsteward::$generate_slonik = $generate_slonik;
pgsql8::build_upgrade('', $old_db_doc, $old_db_doc, array(), $this->output_prefix, $new_db_doc, $new_db_doc, array());
ob_end_clean();
} catch (Exception $ex) {
ob_end_clean();
throw $ex;
}
} else {
ob_start();
try {
$db_doc = simplexml_load_file($this->xml_file_a);
dbsteward::$generate_slonik = $generate_slonik;
pgsql8::build($this->output_prefix, $db_doc);
ob_end_clean();
} catch (Exception $ex) {
ob_end_clean();
throw $ex;
}
}
}
protected function apply_options_pgsql8()
{
dbsteward::set_sql_format('pgsql8');
dbsteward::$quote_all_names = TRUE;
dbsteward::$create_languages = TRUE;
dbsteward::$quote_table_names = TRUE;
dbsteward::$quote_column_names = TRUE;
dbsteward::$quote_object_names = TRUE;
dbsteward::$always_recreate_views = TRUE;
// slony default options
dbsteward::$require_slony_id = FALSE;
dbsteward::$require_slony_set_id = FALSE;
dbsteward::$generate_slonik = FALSE;
}
public function testStageNotTransactionalWithGenerateSlonik()
{
dbsteward::$generate_slonik = TRUE;
$output_file_prefix = $this->diff_definitions('slony_stage_not_transaction');
$regexp_begin = '/^BEGIN;/im';
$regexp_not_begin = '/^(?!BEGIN;).*$/im';
$this->check_stage_content($output_file_prefix, '_upgrade_slony_replica_set_1_stage1_schema1.sql', $regexp_not_begin);
$this->check_stage_content($output_file_prefix, '_upgrade_slony_replica_set_1_stage2_data1.sql', $regexp_begin);
$this->check_stage_content($output_file_prefix, '_upgrade_slony_replica_set_1_stage3_schema1.sql', $regexp_not_begin);
$this->check_stage_content($output_file_prefix, '_upgrade_slony_replica_set_1_stage4_data1.sql', $regexp_begin);
}