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}");
}
/**
* in MSSQL indexes must contain column references, value expressions are not allowed
*
*/
public static function index_dimension_scan($node_schema, $node_table, $node_index, &$add_column_sql)
{
$dimension_list = '';
$add_column_sql = '';
// in MSSQL, index dimensions that are not explicit columns must be converted to computed columns to make the index work like it does in postgresql
$i = 0;
foreach ($node_index->indexDimension as $dimension) {
$i++;
$dimension_name = (string) $dimension;
if (mssql10_table::contains_column($node_table, $dimension_name)) {
// dimension is an explicit column
// check unique index indexDimensions for nulled columns
// mssql index constraint engine will not ignore null values for nullable columns
if (isset($node_index['unique']) && strcasecmp($node_index['unique'], 'true') == 0) {
$node_column = dbx::get_table_column($node_table, $dimension_name);
if (mssql10_column::null_allowed($node_table, $node_column)) {
dbsteward::error("dimension_name = " . $dimension_name);
//var_dump($node_column);
throw new exception("nulled column index found");
}
}
} else {
// not an explicit column, so create one
$dimension_name = $node_index['name'] . '_' . $i;
$add_column_sql .= "ALTER TABLE " . mssql10::get_quoted_schema_name($node_schema['name']) . '.' . mssql10::get_quoted_table_name($node_table['name']) . "\n" . " ADD " . $dimension_name . " AS " . (string) $dimension . ";\n";
}
$dimension_list .= $dimension_name . ', ';
}
$dimension_list = substr($dimension_list, 0, -2);
return $dimension_list;
}
public function get_partition_info($table)
{
$parts = $this->query("SELECT partition_method, partition_name,\n partition_expression, partition_description\n FROM partitions\n WHERE table_schema = ?\n AND table_name = ?\n ORDER BY partition_ordinal_position", array($this->dbname, $table->table_name));
if (count($parts) === 0) {
return null;
}
$method = strtoupper($parts[0]->partition_method);
switch ($method) {
case 'HASH':
case 'LINEAR HASH':
return (object) array('type' => $method, 'number' => count($parts), 'expression' => $parts[0]->partition_expression);
case 'KEY':
case 'LINEAR KEY':
return (object) array('type' => $method, 'number' => count($parts), 'columns' => str_replace(mysql5::QUOTE_CHAR, '', $parts[0]->partition_expression));
case 'LIST':
case 'RANGE':
case 'RANGE COLUMNS':
return (object) array('type' => $method, 'expression' => $method == 'RANGE COLUMNS' ? str_replace(mysql5::QUOTE_CHAR, '', $parts[0]->partition_expression) : $parts[0]->partition_expression, 'segments' => array_map(function ($p) {
return (object) array('name' => $p->partition_name, 'value' => $p->partition_description);
}, $parts));
default:
dbsteward::error("Unrecognized partition method {$method}!");
}
return null;
}
public function __call($m, $a)
{
$ignore_ofs_methods = array('__destruct');
if (in_array($m, $ignore_ofs_methods)) {
return 'IGNORE_OFS_COMMAND_COMPLETE';
}
// if the command is in the list of commands to run on all ofs objects, do so
$all_ofs_methods = array('append_header', 'append_footer');
if (in_array($m, $all_ofs_methods)) {
foreach ($this->ofs as $set_id => $ofs) {
call_user_func_array(array(&$ofs, $m), $a);
}
return 'ALL_OFS_COMMAND_COMPLETE';
}
$use_replica_set_id = format::get_context_replica_set_id();
if ($use_replica_set_id == -10) {
// context_replica_set_id -10 means object does not have slonySetId defined
// use the natural first replica set as the replica context
$first_replica_set = pgsql8::get_slony_replica_set_natural_first(dbsteward::$new_database);
$use_replica_set_id = (int) $first_replica_set['id'];
}
// make sure replica set id to use is known
if (!isset($this->ofs[$use_replica_set_id])) {
if ($this->skip_unknown_set_ids) {
dbsteward::notice("[OFS RSR] context replica set ID is " . $use_replica_set_id . ", but no replica set by that ID, skipping output");
return FALSE;
}
throw new exception("context replica set ID " . $use_replica_set_id . " not defined");
}
$active_set_ofs = $this->ofs[$use_replica_set_id];
dbsteward::debug("[OFS RSR] __call calling " . $use_replica_set_id . " ofs::" . $m);
return call_user_func_array(array(&$active_set_ofs, $m), $a);
}
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;
$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;
$this->dbdoc = new SimpleXMLElement($xml);
}
public static function get_view_query($node_view)
{
$q = '';
foreach ($node_view->viewQuery as $query) {
if (!isset($query['sqlFormat']) || strcasecmp($query['sqlFormat'], dbsteward::get_sql_format()) == 0) {
// sanity check to make sure not more than one viewQuery is matching the sqlFormat scenario
if (strlen($q) > 0) {
throw new exception("query already matched for sqlFormat -- extra viewQuery elements present?");
}
// sqlFormat is not present or
// sqlFormat matches the current static run-time setting
// use this viewQuery
$q = (string) $query;
}
}
if (strlen($q) == 0) {
foreach ($node_view->viewQuery as $query) {
var_dump($query);
}
throw new exception("view " . $node_view['name'] . " - failed to find viewQuery that matches active sql format " . dbsteward::get_sql_format());
}
// if last char is ;, prune it
if (substr($q, -1) == ';') {
$q = substr($q, 0, -1);
}
return $q;
}
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;
}
protected function quoteTestCommon($format, $additional_invalid = array())
{
dbsteward::set_sql_format($format);
dbsteward::$quote_all_names = FALSE;
dbsteward::$quote_illegal_identifiers = FALSE;
$invalid_prefixes = array_merge(array('in$', '0in'), $additional_invalid);
foreach (array('schema', 'table', 'column', 'object', 'function') as $object) {
foreach (array(TRUE, FALSE) as $quoted) {
dbsteward::${"quote_{$object}_names"} = $quoted;
// attempt valid identifiers
$valid_name = "valid_{$format}_{$object}_" . ($quoted ? 'quoted' : 'unquoted') . "_identifier123";
$expected = $quoted ? $format::QUOTE_CHAR . $valid_name . $format::QUOTE_CHAR : $valid_name;
$this->assertEquals($expected, call_user_func("{$format}::get_quoted_{$object}_name", $valid_name), "During call to {$format}::get_quoted_{$object}_name");
// attempt invalid identifiers - expect exceptions
$invalid_names = array_map(function ($prefix) use($valid_name) {
return $prefix . $valid_name;
}, $invalid_prefixes);
$invalid_names[] = $format::QUOTE_CHAR . $valid_name . $format::QUOTE_CHAR;
foreach ($invalid_names as $invalid_name) {
if ($quoted) {
// only expect an exception if not quoted...
} else {
try {
call_user_func("{$format}::get_quoted_{$object}_name", $invalid_name);
} catch (Exception $ex) {
$this->assertContains('Illegal identifier', $ex->getMessage());
continue;
}
$this->fail("Expected 'Illegal identifier' exception, but no exception was thrown for identifier '{$invalid_name}'");
}
}
}
}
}
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/');
}
public function setUp()
{
dbsteward::set_sql_format('mysql5');
dbsteward::$quote_schema_names = TRUE;
dbsteward::$quote_table_names = TRUE;
dbsteward::$quote_column_names = TRUE;
mysql5::$use_auto_increment_table_options = FALSE;
mysql5::$use_schema_name_prefix = FALSE;
}
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;
}
/**
* Creates new schemas (not the objects inside the schemas)
*
* @param object $ofs output file pointer
* @return void
*/
protected static function create_new_schemas($ofs)
{
foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) {
if (dbx::get_schema(dbsteward::$old_database, $new_schema['name']) == null) {
dbsteward::info("Create New Schema " . $new_schema['name']);
pgsql8::set_context_replica_set_id($new_schema);
$ofs->write(format_schema::get_creation_sql($new_schema));
}
}
}
/**
* Creates and returns SQL for creation of the view.
*
* @return string
*/
public static function get_creation_sql($node_schema, $node_view)
{
if (isset($node_view['description']) && strlen($node_view['description']) > 0) {
$ddl = "-- " . dbsteward::string_cast($node_view['description']) . "\n";
}
$ddl = "CREATE VIEW " . mssql10::get_quoted_schema_name($node_schema['name']) . '.' . mssql10::get_quoted_table_name($node_view['name']);
$ddl .= "\n\tAS " . mssql10_view::get_view_query($node_view) . ";\n";
// @IMPLEMENT: $node_view['owner'] ?
return $ddl;
}
/**
* @group pgsql8
* @group mysql5
* @group mssql10
*/
public function testThrowWhenFalse()
{
dbsteward::$ignore_custom_roles = FALSE;
try {
xml_parser::role_enum($this->dbdoc, 'invalid');
} catch (Exception $ex) {
$this->assertEquals('Failed to confirm custom role: invalid', $ex->getMessage());
return;
}
$this->fail("Expected exception when not ignoring custom roles");
}
public static function get_permission_options_sql($node_permission)
{
if (!empty($node_permission['with'])) {
// @TODO: Support MAX_*_PER_HOUR grant options
if (strcasecmp($node_permission['with'], 'grant') != 0) {
dbsteward::warning("Ignoring WITH option '{$node_permission['with']}' because MySQL only supports WITH GRANT OPTION.");
} else {
return " WITH GRANT OPTION";
}
}
}
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}");
}
/**
* Tests that functions referenced as default values for columns
* 1) do not result in build failures
* 2) generate sane SQL on build
*/
public function testBuildColumnSQL()
{
$xml = <<<XML
<dbsteward>
<database>
<role>
<application>dbsteward_phpunit_app</application>
<owner>deployment</owner>
<replication/>
<readonly/>
</role>
</database>
<schema name="dbsteward" owner="ROLE_OWNER">
<function name="test" returns="integer" owner="ROLE_OWNER" cachePolicy="VOLATILE" description="always returns 5, is a test function">
<functionDefinition language="plpgsql" sqlFormat="pgsql8">
BEGIN
RETURN 5;
END
</functionDefinition>
</function>
</schema>
<schema name="hotel" owner="ROLE_OWNER">
<table name="rate" owner="ROLE_OWNER" primaryKey="rate_id" primaryKeyName="rate_pkey">
<tableOption sqlFormat="pgsql8" name="with" value="(oids=false)"/>
<column name="rate_id" type="integer" null="false"/>
<column name="rate_group_id" null="false" foreignSchema="hotel" foreignTable="rate_group" foreignColumn="rate_group_id" foreignKeyName="rate_rate_group_id_fkey" foreignOnUpdate="NO_ACTION" foreignOnDelete="NO_ACTION"/>
<column name="rate_name" type="character varying(120)"/>
<column name="rate_value" type="numeric"/>
</table>
<table name="rate_group" owner="ROLE_OWNER" primaryKey="rate_group_id" primaryKeyName="rate_group_pkey">
<tableOption sqlFormat="pgsql8" name="with" value="(oids=false)"/>
<column name="rate_group_id" type="integer" null="false" default="dbsteward.test()"/>
<column name="rate_group_name" type="character varying(100)"/>
<column name="rate_group_enabled" type="boolean" null="false" default="true"/>
</table>
</schema>
</dbsteward>
XML;
$expected = <<<EXP
ALTER TABLE "hotel"."rate" ALTER COLUMN "rate_id" SET NOT NULL;
ALTER TABLE "hotel"."rate" ALTER COLUMN "rate_group_id" SET NOT NULL;
ALTER TABLE "hotel"."rate_group" ALTER COLUMN "rate_group_id" SET DEFAULT dbsteward.test();
ALTER TABLE "hotel"."rate_group" ALTER COLUMN "rate_group_id" SET NOT NULL;
ALTER TABLE "hotel"."rate_group" ALTER COLUMN "rate_group_enabled" SET DEFAULT true;
ALTER TABLE "hotel"."rate_group" ALTER COLUMN "rate_group_enabled" SET NOT NULL;
EXP;
dbsteward::$quote_all_names = TRUE;
$this->set_xml_content_a($xml);
$this->build_db('pgsql8');
$actual = file_get_contents(dirname(__FILE__) . '/../testdata/unit_test_xml_a_build.sql');
$this->assertContains($expected, $actual);
}
/**
* 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/');
}
/**
* Returns full definition of the column.
*
* @param add_defaults whether default value should be added in case NOT
* NULL constraint is specified but no default value is set
*
* @return full definition of the column
*/
public static function get_full_definition($db_doc, $node_schema, $node_table, $node_column, $add_defaults, $include_null_definition = true, $include_auto_increment = false)
{
// ignore AUTO_INCREMENT flags for now
$is_auto_increment = static::is_auto_increment($node_column['type']);
$orig_type = (string) $node_column['type'];
$node_column['type'] = static::un_auto_increment($node_column['type']);
$column_type = static::column_type($db_doc, $node_schema, $node_table, $node_column);
$definition = mysql5::get_quoted_column_name($node_column['name']) . ' ' . $column_type;
$nullable = static::null_allowed($node_table, $node_column);
$is_timestamp = static::is_timestamp($node_column);
if ($include_null_definition) {
if ($nullable) {
if ($is_timestamp) {
$definition .= " NULL";
}
} else {
$definition .= " NOT NULL";
}
}
if ($include_auto_increment && $is_auto_increment) {
$definition .= " AUTO_INCREMENT";
}
if (strlen($node_column['default']) > 0) {
if (static::is_serial($node_column['type'])) {
$note = "Ignoring default '{$node_column['default']}' on {$node_schema['name']}.{$node_table['name']}.{$node_column['name']} because it is a serial type";
dbsteward::warning($note . "\n");
} else {
$definition .= " DEFAULT " . $node_column['default'];
}
} else {
if ($add_defaults && $is_timestamp) {
if ($nullable) {
$definition .= " DEFAULT NULL";
} else {
$definition .= " DEFAULT CURRENT_TIMESTAMP";
}
} else {
if (!$nullable && $add_defaults) {
$default_col_value = self::get_default_value($node_column['type']);
if ($default_col_value != null) {
$definition .= " DEFAULT " . $default_col_value;
}
}
}
}
if (strlen($node_column['description']) > 0) {
$definition .= " COMMENT " . mysql5::quote_string_value($node_column['description']);
}
// restore the original type of the column
$node_column['type'] = $orig_type;
return $definition;
}
public function setUp()
{
dbsteward::set_sql_format('mysql5');
dbsteward::$quote_schema_names = TRUE;
dbsteward::$quote_table_names = TRUE;
dbsteward::$quote_column_names = TRUE;
mysql5::$swap_function_delimiters = FALSE;
mysql5::$use_auto_increment_table_options = FALSE;
mysql5::$use_schema_name_prefix = FALSE;
$this->config = $GLOBALS['db_config']->mysql5_config;
$this->connect();
$this->setup_shim();
}
private function common($format, $expected)
{
$xml = <<<XML
<schema name="public" owner="ROLE_OWNER">
<table name="table" owner="ROLE_OWNER">
<column name="column" type="serial" serialStart="5"/>
</table>
</schema>
XML;
$schema = new SimpleXMLElement($xml);
dbsteward::set_sql_format($format);
$colclass = $format . '_column';
$expected = "-- serialStart 5 specified for public.table.column\n{$expected}\n";
$actual = $colclass::get_serial_start_dml($schema, $schema->table, $schema->table->column);
$this->assertEquals($expected, $actual);
}
public static function diff_triggers_table($ofs, $old_schema, $old_table, $new_schema, $new_table)
{
// drop triggers that no longer exist or are modified
foreach (static::get_drop_triggers($old_schema, $old_table, $new_schema, $new_table) as $old_trigger) {
// only do triggers set to the current sql_format
if (strcasecmp($old_trigger['sqlFormat'], dbsteward::get_sql_format()) == 0) {
$ofs->write(format_trigger::get_drop_sql($old_schema, $old_trigger) . "\n");
}
}
// add new triggers
foreach (static::get_new_triggers($old_schema, $old_table, $new_schema, $new_table) as $new_trigger) {
// only do triggers set to the current sql format
if (strcasecmp($new_trigger['sqlFormat'], dbsteward::get_sql_format()) == 0) {
$ofs->write(format_trigger::get_creation_sql($new_schema, $new_trigger) . "\n");
}
}
}
/**
* Creates and returns SQL for creation of the view.
*
* @return string
*/
public static function get_creation_sql($db_doc, $node_schema, $node_view)
{
// set replica set context for view
if (pgsql8::set_context_replica_set_id($node_view) === -10) {
// view doesn't specify one, set from for schema object
pgsql8::set_context_replica_set_id($node_schema);
}
if (isset($node_view['description']) && strlen($node_view['description']) > 0) {
$ddl = "-- " . dbsteward::string_cast($node_view['description']) . "\n";
}
$view_name = pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($node_view['name']);
$ddl = "CREATE OR REPLACE VIEW " . $view_name;
$ddl .= "\n\tAS " . pgsql8_view::get_view_query($node_view) . ";\n";
if (isset($node_view['owner']) && strlen($node_view['owner']) > 0) {
$ddl .= "ALTER VIEW " . $view_name . "\n\tOWNER TO " . xml_parser::role_enum($db_doc, $node_view['owner']) . ";\n";
}
return $ddl;
}
public static function get_drop_sql($node_schema, $node_trigger)
{
if (strcasecmp($node_trigger['sqlFormat'], dbsteward::get_sql_format())) {
$note = "Ignoring {$node_trigger['sqlFormat']} trigger '{$node_trigger['name']}'";
dbsteward::warning($note);
return "-- {$note}\n";
}
$events = self::get_events($node_trigger);
if (count($events) == 1) {
return "DROP TRIGGER IF EXISTS " . mysql5::get_fully_qualified_table_name($node_schema['name'], $node_trigger['name']) . ";\n";
} else {
$ddl = "";
foreach ($events as $event) {
if ($event = self::validate_event($event)) {
$ddl .= "DROP TRIGGER IF EXISTS " . mysql5::get_fully_qualified_table_name($node_schema['name'], $node_trigger['name'] . "_{$event}") . ";\n";
}
}
return $ddl;
}
}
/**
* @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;
mysql5::$swap_function_delimiters = FALSE;
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::$new_database = new SimpleXMLElement($db_doc_xml);
}
public static function get_definition($node_function)
{
$definition = null;
foreach ($node_function->functionDefinition as $def) {
if (empty($def['sqlFormat']) || empty($def['language'])) {
throw new Exception("Attributes sqlFormat and language are required on functionDefinitions, in function '{$node_function['name']}'");
}
if ($def['sqlFormat'] == dbsteward::get_sql_format() && static::supported_language($def['language'])) {
if ($definition !== null) {
throw new Exception("duplicate function definition for {$def['sqlFormat']}/{$def['language']} in function '{$node_function['name']}'");
}
$definition = $def;
}
}
if ($definition === null) {
foreach ($node_function->functionDefinition as $def) {
var_dump($def);
}
$format = dbsteward::get_sql_format();
throw new Exception("no function definitions in a known language for format {$format} in function '{$node_function['name']}'");
}
return $definition;
}
/**
* Updates objects in schemas.
*
* @param $ofs1 stage1 output file segmenter
* @param $ofs3 stage3 output file segmenter
*/
public static function update_structure($ofs1, $ofs3)
{
if (!mysql5::$use_schema_name_prefix) {
if (count(dbsteward::$new_database->schema) > 1) {
throw new Exception("You cannot use more than one schema in mysql5 without schema name prefixing\nPass the --useschemaprefix flag to turn this on");
}
if (count(dbsteward::$old_database->schema) > 1) {
throw new Exception("You cannot use more than one schema in mysql5 without schema name prefixing\nPass the --useschemaprefix flag to turn this on");
}
} else {
dbsteward::info("Drop Old Schemas");
self::drop_old_schemas($ofs3);
}
mysql5_diff_views::drop_views_ordered($ofs1, dbsteward::$old_database, dbsteward::$new_database);
//@TODO: implement mysql5_language ? no relevant conversion exists see other TODO's stating this
//mysql5_diff_languages::diff_languages($ofs1);
// if the table dependency order is unknown, bang them in natural order
if (!is_array(mysql5_diff::$new_table_dependency)) {
foreach (dbx::get_schemas(dbsteward::$new_database) as $new_schema) {
//@NOTICE: @TODO: this does not honor old*Name attributes, does it matter?
$old_schema = dbx::get_schema(dbsteward::$old_database, $new_schema['name']);
mysql5_diff_types::apply_changes($ofs1, $old_schema, $new_schema);
mysql5_diff_functions::diff_functions($ofs1, $ofs3, $old_schema, $new_schema);
mysql5_diff_sequences::diff_sequences($ofs1, $ofs3, $old_schema, $new_schema);
// remove old constraints before table contraints, so the SQL statements succeed
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);
mysql5_diff_triggers::diff_triggers($ofs1, $old_schema, $new_schema);
}
// non-primary key constraints may be inter-schema dependant, and dependant on other's primary keys
// and therefore should be done after object creation sections
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);
}
} else {
$processed_schemas = array();
for ($i = 0; $i < count(mysql5_diff::$new_table_dependency); $i++) {
// find the necessary pointers
$item = mysql5_diff::$new_table_dependency[$i];
// @NOTICE: dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME is NOT checked here because these are schema operations
$new_schema = dbx::get_schema(dbsteward::$new_database, $item['schema']['name']);
$old_schema = dbx::get_schema(dbsteward::$old_database, $item['schema']['name']);
// do all types and functions on their own before table creation
// see next loop for other once per schema work
if (!in_array(trim($new_schema['name']), $processed_schemas)) {
mysql5_diff_types::apply_changes($ofs1, $old_schema, $new_schema);
mysql5_diff_functions::diff_functions($ofs1, $ofs3, $old_schema, $new_schema);
$processed_schemas[] = trim($new_schema['name']);
}
}
// remove all old constraints before new contraints, in reverse dependency order
for ($i = count(mysql5_diff::$old_table_dependency) - 1; $i >= 0; $i--) {
// find the necessary pointers
$item = mysql5_diff::$old_table_dependency[$i];
if ($item['table']['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) {
// don't do anything with this table, it is a magic internal DBSteward value
continue;
}
$new_schema = dbx::get_schema(dbsteward::$new_database, $item['schema']['name']);
$new_table = NULL;
if ($new_schema != NULL) {
$new_table = dbx::get_table($new_schema, $item['table']['name']);
}
$old_schema = dbx::get_schema(dbsteward::$old_database, $item['schema']['name']);
$old_table = NULL;
if ($old_schema != NULL) {
$old_table = dbx::get_table($old_schema, $item['table']['name']);
}
if ($old_table == NULL) {
throw new exception("old_table " . $item['schema']['name'] . "." . $item['table']['name'] . " not found. This is not expected as this reverse constraint loop was based on the old_table_dependency list!");
}
// @NOTICE: when dropping constraints, dbx::renamed_table_check_pointer() is not called for $old_table
// as mysql5_diff_tables::diff_constraints_table() will do rename checking when recreating constraints for renamed tables
mysql5_diff_constraints::diff_constraints_table($ofs1, $old_schema, $old_table, $new_schema, $new_table, 'constraint', TRUE);
mysql5_diff_constraints::diff_constraints_table($ofs1, $old_schema, $old_table, $new_schema, $new_table, 'primaryKey', TRUE);
}
$processed_schemas = array();
for ($i = 0; $i < count(mysql5_diff::$new_table_dependency); $i++) {
// find the necessary pointers
$item = mysql5_diff::$new_table_dependency[$i];
$new_schema = dbx::get_schema(dbsteward::$new_database, $item['schema']['name']);
$new_table = NULL;
if ($new_schema != NULL) {
$new_table = dbx::get_table($new_schema, $item['table']['name']);
}
$old_schema = dbx::get_schema(dbsteward::$old_database, $item['schema']['name']);
// schema level stuff should only be done once, keep track of which ones we have done
// see above for pre table creation stuff
// see below for post table creation stuff
if (!in_array($new_schema['name'], $processed_schemas)) {
mysql5_diff_sequences::diff_sequences($ofs1, $ofs3, $old_schema, $new_schema);
$processed_schemas[] = $new_schema['name'];
}
if ($item['table']['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) {
// don't do anything with this table, it is a magic internal DBSteward value
continue;
}
$old_table = NULL;
if ($old_schema != NULL) {
$old_table = dbx::get_table($old_schema, $item['table']['name']);
}
dbx::renamed_table_check_pointer($old_schema, $old_table, $new_schema, $new_table);
mysql5_diff_tables::diff_tables($ofs1, $ofs3, $old_schema, $new_schema, $old_table, $new_table);
// mysql5_diff_indexes::diff_indexes_table($ofs1, $old_schema, $old_table, $new_schema, $new_table);
mysql5_diff_constraints::diff_constraints_table($ofs1, $old_schema, $old_table, $new_schema, $new_table, 'primaryKey', FALSE);
mysql5_diff_triggers::diff_triggers_table($ofs1, $old_schema, $old_table, $new_schema, $new_table);
mysql5_diff_constraints::diff_constraints_table($ofs1, $old_schema, $old_table, $new_schema, $new_table, 'constraint', FALSE);
}
// drop old tables in reverse dependency order
for ($i = count(mysql5_diff::$old_table_dependency) - 1; $i >= 0; $i--) {
// find the necessary pointers
$item = mysql5_diff::$old_table_dependency[$i];
if ($item['table']['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) {
// don't do anything with this table, it is a magic internal DBSteward value
continue;
}
$new_schema = dbx::get_schema(dbsteward::$new_database, $item['schema']['name']);
$new_table = NULL;
if ($new_schema != NULL) {
$new_table = dbx::get_table($new_schema, $item['table']['name']);
}
$old_schema = dbx::get_schema(dbsteward::$old_database, $item['schema']['name']);
$old_table = NULL;
if ($old_schema != NULL) {
$old_table = dbx::get_table($old_schema, $item['table']['name']);
}
if ($old_table == NULL) {
throw new exception("old_table " . $item['schema']['name'] . "." . $item['table']['name'] . " not found. This is not expected as this reverse constraint loop was based on the old_table_dependency list!");
}
mysql5_diff_tables::drop_tables($ofs3, $old_schema, $new_schema, $old_table, $new_table);
}
}
mysql5_diff_views::create_views_ordered($ofs3, dbsteward::$old_database, dbsteward::$new_database);
}
public static function get_constraint_drop_sql($constraint, $with_alter_table = TRUE)
{
if (!is_array($constraint)) {
throw new exception("constraint is not an array?");
}
if (strlen($constraint['table_name']) == 0) {
var_dump(array_keys($constraint));
throw new exception("table_name is blank");
}
// because MySQL refuses to have consistent syntax
switch (strtoupper($constraint['type'])) {
case 'CHECK':
// @TODO: Implement compatibility
dbsteward::warning("Not dropping constraint '{$constraint['name']}' on table '{$constraint['table_name']}' because MySQL doesn't support the CHECK constraint");
return "-- Not dropping constraint '{$constraint['name']}' on table '{$constraint['table_name']}' because MySQL doesn't support the CHECK constraint";
break;
case 'UNIQUE':
$drop = "INDEX " . mysql5::get_quoted_object_name($constraint['name']);
break;
case 'PRIMARY KEY':
$drop = "PRIMARY KEY";
break;
case 'FOREIGN KEY':
$drop = "FOREIGN KEY " . mysql5::get_quoted_object_name($constraint['name']);
break;
case 'KEY':
$drop = "KEY " . mysql5::get_quoted_object_name($constraint['name']);
break;
default:
// we shouldn't actually ever get here.
throw new Exception("Unimplemented MySQL constraint {$constraint['type']}");
}
$sql = '';
if ($with_alter_table) {
$sql .= "ALTER TABLE " . mysql5::get_fully_qualified_table_name($constraint['schema_name'], $constraint['table_name']) . " ";
}
$sql .= "DROP {$drop}";
if ($with_alter_table) {
$sql .= ";";
}
return $sql;
}
protected function setup_mysql5()
{
$xml = <<<XML
<dbsteward>
<database>
<sqlformat>mysql5</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">
<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">
<rows columns="id, description">
<row>
<col>2</col>
<col>Action 2 Alternate Description</col>
</row>
<row>
<col>3</col>
<col>Action 3 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/mysql5_unit_test_xml_a.xml';
$this->xml_file_b = dirname(__FILE__) . '/../testdata/mysql5_unit_test_xml_b.xml';
$this->xml_file_c = dirname(__FILE__) . '/../testdata/mysql5_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/mysql5_unit_test_identical';
dbsteward::$single_stage_upgrade = TRUE;
$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));
mysql5::build_upgrade('', 'identical_diff_test_mysql5_old', $old_db_doc_comp, array(), $this->output_prefix, 'identical_diff_test_mysql5_new', $new_db_doc_comp, array());
}
