public static function build($output_prefix, $db_doc)
{
if (strlen($output_prefix) == 0) {
throw new exception("mssql10::build() sanity failure: output_prefix is blank");
}
// build full db creation script
$build_file = $output_prefix . '_build.sql';
dbsteward::notice("Building complete file " . $build_file);
$build_file_fp = fopen($build_file, 'w');
if ($build_file_fp === FALSE) {
throw new exception("failed to open full file " . $build_file . ' for output');
}
$build_file_ofs = new output_file_segmenter($build_file, 1, $build_file_fp, $build_file);
if (count(dbsteward::$limit_to_tables) == 0) {
$build_file_ofs->write("-- full database definition file generated " . date('r') . "\n");
}
// error encountered: ALTER DATABASE statement not allowed within multi-statement transaction.
// so do the assembly configuration before the db structure / data creation transaction
// see BEGIN TRANSACTION below
if (isset($db_doc->inlineAssembly)) {
foreach ($db_doc->inlineAssembly as $assembly) {
$assembly_file_name = dirname($files[0]) . '/' . $assembly['name'];
if (!is_readable($assembly_file_name)) {
throw new exception("assembly file " . $assembly_file_name . " not readable");
}
$assembly_name = substr(basename($assembly_file_name), 0, -4);
dbsteward::info("Including " . $assembly_name . " assembly inline from " . $assembly_file_name);
$afh = fopen($assembly_file_name, "rb");
$assembly_contents = fread($afh, filesize($assembly_file_name));
fclose($afh);
$assembly_binary_hex = '0x' . bin2hex($assembly_contents);
$ddl = "CREATE ASSEMBLY " . $assembly_name . "\n" . "FROM " . $assembly_binary_hex . "\n" . "WITH PERMISSION_SET = SAFE;\n\n";
$build_file_ofs->write($ddl);
}
unset($db_doc->inlineAssembly);
}
$build_file_ofs->write("BEGIN TRANSACTION;\n\n");
dbsteward::info("Calculating table foreign key dependency order..");
$table_dependency = xml_parser::table_dependency_order($db_doc);
// database-specific implementation refers to dbsteward::$new_database when looking up roles/values/conflicts etc
dbsteward::$new_database = $db_doc;
dbx::set_default_schema($db_doc, 'dbo');
// language defintions
if (dbsteward::$create_languages) {
foreach ($db_doc->language as $language) {
//@TODO: implement mssql10_language ? no relevant conversion exists see other TODO's stating this
}
}
if (dbsteward::$only_schema_sql || !dbsteward::$only_data_sql) {
dbsteward::notice("Defining structure");
mssql10::build_schema($db_doc, $build_file_ofs, $table_dependency);
}
if (!dbsteward::$only_schema_sql || dbsteward::$only_data_sql) {
dbsteward::notice("Defining data inserts");
mssql10::build_data($db_doc, $build_file_ofs, $table_dependency);
}
dbsteward::$new_database = NULL;
$build_file_ofs->write("COMMIT TRANSACTION;\n\n");
return $db_doc;
}
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);
}
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;
}
/**
* 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;
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);
}
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();
}
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 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)
{
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 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);
}
private function common($xml, $expected)
{
$dbs = new SimpleXMLElement($xml);
$ofs = new mock_output_file_segmenter();
dbsteward::$new_database = $dbs;
$table_dependency = xml_parser::table_dependency_order($dbs);
mysql5::build_data($dbs, $ofs, $table_dependency);
$actual = $ofs->_get_output();
// get rid of extra whitespace
$expected = preg_replace("/^ +/m", "", $expected);
$expected = trim(preg_replace("/\n+/", "\n", $expected));
// echo $actual;
// get rid of comments
$actual = preg_replace("/\\s*-- .*\$/m", '', $actual);
// get rid of extra whitespace
$actual = preg_replace("/^ +/m", "", $actual);
$actual = trim(preg_replace("/\n+/", "\n", $actual));
$this->assertEquals($expected, $actual);
}
public function testBuildSchema()
{
$xml = <<<XML
<dbsteward>
<database>
<host>db-host</host>
<name>dbsteward</name>
<role>
<application>dbsteward_phpunit_app</application>
<owner>deployment</owner>
<replication/>
<readonly/>
</role>
<!-- should be ignored -->
<slony>
<masterNode id="1"/>
<replicaNode id="2" providerId="1"/>
<replicaNode id="3" providerId="2"/>
<replicationSet id="1"/>
<replicationUpgradeSet id="2"/>
</slony>
<!-- should be ignored -->
<configurationParameter name="TIME ZONE" value="America/New_York"/>
</database>
<!-- should be ignored -->
<language name="plpgsql" procedural="true" owner="ROLE_OWNER"/>
<schema name="public" owner="ROLE_OWNER">
<grant operation="SELECT,UPDATE,DELETE" role="ROLE_OWNER"/>
<type type="enum" name="permission_level">
<enum name="guest"/>
<enum name="user"/>
<enum name="admin"/>
</type>
<function name="a_function" returns="text" owner="ROLE_OWNER" cachePolicy="VOLATILE" description="a test function">
<functionParameter name="config_parameter" type="text"/>
<functionParameter name="config_value" type="text"/>
<!-- should be ignored -->
<functionDefinition language="plpgsql" sqlFormat="pgsql8">
DECLARE
q text;
name text;
n text;
BEGIN
SELECT INTO name current_database();
q := 'ALTER DATABASE ' || name || ' SET ' || config_parameter || ' ''' || config_value || ''';';
n := 'DB CONFIG CHANGE: ' || q;
RAISE NOTICE '%', n;
EXECUTE q;
RETURN n;
END;
</functionDefinition>
<functionDefinition language="sql" sqlFormat="mysql5">
BEGIN
RETURN config_parameter;
END
</functionDefinition>
<grant operation="EXECUTE" role="ROLE_APPLICATION"/>
</function>
<table name="user" owner="ROLE_OWNER" primaryKey="user_id" slonyId="1">
<column name="user_id" type="int auto_increment" null="false"/>
<column name="group_id" foreignSchema="public" foreignTable="group" foreignColumn="group_id" null="false"/>
<column name="username" type="varchar(80)"/>
<column name="user_age" type="numeric"/>
<constraint name="username_unq" type="Unique" definition="(`username`)"/>
<grant operation="SELECT,UPDATE,DELETE" role="ROLE_APPLICATION"/>
</table>
<table name="group" owner="ROLE_OWNER" primaryKey="group_id" slonyId="2">
<column name="group_id" type="int auto_increment" null="false"/>
<column name="permission_level" type="permission_level"/> <!-- enum type -->
<column name="group_name" type="character varying(100)" unique="true"/>
<column name="group_enabled" type="boolean" null="false" default="true"/>
<grant operation="SELECT,UPDATE,DELETE" role="ROLE_APPLICATION"/>
</table>
<sequence name="a_sequence" owner="ROLE_OWNER">
<grant operation="SELECT,UPDATE,DELETE" role="ROLE_APPLICATION"/>
</sequence>
<trigger name="a_trigger" sqlFormat="mysql5" table="user" when="before" event="insert" function="EXECUTE xyz"/>
<!-- should be ignored -->
<trigger name="a_trigger" sqlFormat="pgsql8" table="group" when="before" event="delete" function="EXECUTE xyz;"/>
<view name="a_view" owner="ROLE_OWNER" description="Description goes here">
<viewQuery sqlFormat="mysql5">SELECT * FROM user, group</viewQuery>
<!-- should be ignored -->
<viewQuery sqlFormat="pgsql8">SELECT * FROM pgsql8table</viewQuery>
<grant operation="SELECT,UPDATE,DELETE" role="ROLE_APPLICATION"/>
</view>
</schema>
<schema name="hotel" owner="ROLE_OWNER">
<table name="rate" owner="ROLE_OWNER" primaryKey="rate_id" slonyId="1">
<column name="rate_id" type="integer" null="false"/>
<column name="rate_group_id" foreignSchema="hotel" foreignTable="rate_group" foreignColumn="rate_group_id" null="false"/>
<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" slonyId="2">
<column name="rate_group_id" type="integer" null="false"/>
<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 = <<<SQL
GRANT SELECT, UPDATE, DELETE ON * TO `deployment`;
DROP FUNCTION IF EXISTS `public_a_function`;
CREATE DEFINER = deployment FUNCTION `public_a_function` (`config_parameter` text, `config_value` text)
RETURNS text
LANGUAGE SQL
MODIFIES SQL DATA
NOT DETERMINISTIC
SQL SECURITY INVOKER
COMMENT 'a test function'
BEGIN
RETURN config_parameter;
END;
GRANT EXECUTE ON FUNCTION `public_a_function` TO `dbsteward_phpunit_app`;
CREATE TABLE `public_user` (
`user_id` int NOT NULL,
`group_id` int NOT NULL,
`username` varchar(80),
`user_age` numeric
);
GRANT SELECT, UPDATE, DELETE ON `public_user` TO `dbsteward_phpunit_app`;
CREATE TABLE `public_group` (
`group_id` int NOT NULL,
`permission_level` ENUM('guest','user','admin'),
`group_name` character varying(100),
`group_enabled` boolean NOT NULL DEFAULT true
);
GRANT SELECT, UPDATE, DELETE ON `public_group` TO `dbsteward_phpunit_app`;
CREATE TABLE IF NOT EXISTS `__sequences` (
`name` VARCHAR(100) NOT NULL,
`increment` INT(11) unsigned NOT NULL DEFAULT 1,
`min_value` INT(11) unsigned NOT NULL DEFAULT 1,
`max_value` BIGINT(20) unsigned NOT NULL DEFAULT 18446744073709551615,
`cur_value` BIGINT(20) unsigned DEFAULT 1,
`start_value` BIGINT(20) unsigned DEFAULT 1,
`cycle` BOOLEAN NOT NULL DEFAULT FALSE,
`should_advance` BOOLEAN NOT NULL DEFAULT TRUE,
PRIMARY KEY (`name`)
) ENGINE = MyISAM;
DROP FUNCTION IF EXISTS `currval`;
CREATE FUNCTION `currval` (`seq_name` varchar(100))
RETURNS BIGINT(20) NOT DETERMINISTIC
BEGIN
DECLARE val BIGINT(20);
IF @__sequences_lastval IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'nextval() has not been called yet this session';
ELSE
SELECT `currval` INTO val FROM `__sequences_currvals` WHERE `name` = seq_name;
RETURN val;
END IF;
END;
DROP FUNCTION IF EXISTS `lastval`;
CREATE FUNCTION `lastval` ()
RETURNS BIGINT(20) NOT DETERMINISTIC
BEGIN
IF @__sequences_lastval IS NULL THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'nextval() has not been called yet this session';
ELSE
RETURN @__sequences_lastval;
END IF;
END;
DROP FUNCTION IF EXISTS `nextval`;
CREATE FUNCTION `nextval` (`seq_name` varchar(100))
RETURNS BIGINT(20) NOT DETERMINISTIC
BEGIN
DECLARE advance BOOLEAN;
CREATE TEMPORARY TABLE IF NOT EXISTS `__sequences_currvals` (
`name` VARCHAR(100) NOT NULL,
`currval` BIGINT(20),
PRIMARY KEY (`name`)
);
SELECT `cur_value` INTO @__sequences_lastval FROM `__sequences` WHERE `name` = seq_name;
SELECT `should_advance` INTO advance FROM `__sequences` WHERE `name` = seq_name;
IF @__sequences_lastval IS NOT NULL THEN
IF advance = TRUE THEN
UPDATE `__sequences`
SET `cur_value` = IF (
(`cur_value` + `increment`) > `max_value`,
IF (`cycle` = TRUE, `min_value`, NULL),
`cur_value` + `increment`
)
WHERE `name` = seq_name;
SELECT `cur_value` INTO @__sequences_lastval FROM `__sequences` WHERE `name` = seq_name;
ELSE
UPDATE `__sequences`
SET `should_advance` = TRUE
WHERE `name` = seq_name;
END IF;
REPLACE INTO `__sequences_currvals` (`name`, `currval`)
VALUE (seq_name, @__sequences_lastval);
END IF;
RETURN @__sequences_lastval;
END;
DROP FUNCTION IF EXISTS `setval`;
CREATE FUNCTION `setval` (`seq_name` varchar(100), `value` bigint(20), `advance` BOOLEAN)
RETURNS bigint(20) NOT DETERMINISTIC
BEGIN
UPDATE `__sequences`
SET `cur_value` = value,
`should_advance` = advance
WHERE `name` = seq_name;
IF advance = FALSE THEN
CREATE TEMPORARY TABLE IF NOT EXISTS `__sequences_currvals` (
`name` VARCHAR(100) NOT NULL,
`currval` BIGINT(20),
PRIMARY KEY (`name`)
);
REPLACE INTO `__sequences_currvals` (`name`, `currval`)
VALUE (seq_name, value);
SET @__sequences_lastval = value;
END IF;
RETURN value;
END;
INSERT INTO `__sequences`
(`name`, `increment`, `min_value`, `max_value`, `cur_value`, `start_value`, `cycle`)
VALUES
('a_sequence', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
GRANT SELECT, UPDATE, DELETE ON `__sequences` TO `dbsteward_phpunit_app`;
DROP TRIGGER IF EXISTS `public_a_trigger`;
CREATE TRIGGER `public_a_trigger` BEFORE INSERT ON `public_user`
FOR EACH ROW EXECUTE xyz;
CREATE TABLE `hotel_rate` (
`rate_id` integer NOT NULL,
`rate_group_id` integer NOT NULL,
`rate_name` character varying(120),
`rate_value` numeric
);
CREATE TABLE `hotel_rate_group` (
`rate_group_id` integer NOT NULL,
`rate_group_name` character varying(100),
`rate_group_enabled` boolean NOT NULL DEFAULT true
);
ALTER TABLE `public_user`
ADD INDEX `group_id` (`group_id`) USING BTREE,
ADD PRIMARY KEY (`user_id`),
MODIFY `user_id` int NOT NULL AUTO_INCREMENT;
ALTER TABLE `public_group`
ADD UNIQUE INDEX `group_name` (`group_name`) USING BTREE,
ADD PRIMARY KEY (`group_id`),
MODIFY `group_id` int NOT NULL AUTO_INCREMENT;
ALTER TABLE `hotel_rate`
ADD INDEX `rate_group_id` (`rate_group_id`) USING BTREE,
ADD PRIMARY KEY (`rate_id`);
ALTER TABLE `hotel_rate_group`
ADD PRIMARY KEY (`rate_group_id`);
ALTER TABLE `public_user`
ADD UNIQUE INDEX `username_unq` (`username`),
ADD CONSTRAINT `user_group_id_fkey` FOREIGN KEY `user_group_id_fkey` (`group_id`) REFERENCES `public_group` (`group_id`);
ALTER TABLE `hotel_rate`
ADD CONSTRAINT `rate_rate_group_id_fkey` FOREIGN KEY `rate_rate_group_id_fkey` (`rate_group_id`) REFERENCES `hotel_rate_group` (`rate_group_id`);
CREATE OR REPLACE DEFINER = deployment SQL SECURITY DEFINER VIEW `public_a_view`
AS SELECT * FROM user, group;
SQL;
$dbs = new SimpleXMLElement($xml);
$ofs = new mock_output_file_segmenter();
dbsteward::$new_database = $dbs;
$table_dependency = xml_parser::table_dependency_order($dbs);
mysql5::build_schema($dbs, $ofs, $table_dependency);
$actual = $ofs->_get_output();
// var_dump($actual);
// get rid of comments
// $expected = preg_replace('/\s*-- .*(\n\s*)?/','',$expected);
// // get rid of extra whitespace
// $expected = trim(preg_replace("/\n\n/","\n",$expected));
$expected = preg_replace("/^ +/m", "", $expected);
$expected = trim(preg_replace("/\n+/", "\n", $expected));
// echo $actual;
// get rid of comments
$actual = preg_replace("/\\s*-- .*\$/m", '', $actual);
// get rid of extra whitespace
// $actual = trim(preg_replace("/\n\n+/","\n",$actual));
$actual = preg_replace("/^ +/m", "", $actual);
$actual = trim(preg_replace("/\n+/", "\n", $actual));
$this->assertEquals($expected, $actual);
}
public static function build($output_prefix, $db_doc)
{
if (strlen($output_prefix) == 0) {
throw new exception("mysql5::build() sanity failure: output_prefix is blank");
}
// build full db creation script
$build_file = $output_prefix . '_build.sql';
dbsteward::notice("Building complete file " . $build_file);
$build_file_fp = fopen($build_file, 'w');
if ($build_file_fp === FALSE) {
throw new exception("failed to open full file " . $build_file . ' for output');
}
$build_file_ofs = new output_file_segmenter($build_file, 1, $build_file_fp, $build_file);
if (count(dbsteward::$limit_to_tables) == 0) {
$build_file_ofs->write("-- full database definition file generated " . date('r') . "\n");
}
// $build_file_ofs->write("START TRANSACTION;\n\n");
dbsteward::info("Calculating table foreign key dependency order..");
$table_dependency = xml_parser::table_dependency_order($db_doc);
// database-specific implementation refers to dbsteward::$new_database when looking up roles/values/conflicts etc
dbsteward::$new_database = $db_doc;
// language defintions
if (dbsteward::$create_languages) {
foreach ($db_doc->language as $language) {
dbsteward::warning("Ignoring language {$language['name']} declaration because MySQL does not support languages other than 'sql'");
}
}
if (dbsteward::$only_schema_sql || !dbsteward::$only_data_sql) {
dbsteward::info("Defining structure");
mysql5::build_schema($db_doc, $build_file_ofs, $table_dependency);
}
if (!dbsteward::$only_schema_sql || dbsteward::$only_data_sql) {
dbsteward::info("Defining data inserts");
mysql5::build_data($db_doc, $build_file_ofs, $table_dependency);
}
dbsteward::$new_database = NULL;
// $build_file_ofs->write("COMMIT TRANSACTION;\n\n");
return $db_doc;
}
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");
}
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);
}
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 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');
}
}
public function testAutoIncrement()
{
$xml = <<<XML
<schema name="public" owner="NOBODY">
<table name="test" primaryKey="id" owner="NOBODY">
<column name="id" type="int auto_increment"/>
</table>
</schema>
XML;
$schema = new SimpleXMLElement($xml);
$this->assertEquals("CREATE TABLE `test` (\n `id` int\n);", mysql5_table::get_creation_sql($schema, $schema->table));
// ensure that foreign auto_increment flags aren't transferred to the table definition
$xml = <<<XML
<dbsteward>
<database/>
<schema name="public" owner="NOBODY">
<table name="test" primaryKey="id" owner="NOBODY">
<column name="id" type="int auto_increment"/>
<column name="fk" foreignSchema="public" foreignTable="other" foreignColumn="id"/>
</table>
<table name="other" primaryKey="id" onwer="NOBODY">
<column name="id" type="int auto_increment"/>
</table>
</schema>
</dbsteward>
XML;
$dbs = new SimpleXMLElement($xml);
dbsteward::$new_database = $dbs;
$this->assertEquals("CREATE TABLE `test` (\n `id` int,\n `fk` int\n);", mysql5_table::get_creation_sql($dbs->schema, $dbs->schema->table[0]));
}
public static function build($output_prefix, $db_doc)
{
if (strlen($output_prefix) == 0) {
throw new exception("pgsql8::build() sanity failure: output_prefix is blank");
}
// build full db creation script
$build_file = $output_prefix . '_build.sql';
dbsteward::info("Building complete file " . $build_file);
$build_file_fp = fopen($build_file, 'w');
if ($build_file_fp === FALSE) {
throw new exception("failed to open full file " . $build_file . ' for output');
}
$build_file_ofs = new output_file_segmenter($build_file, 1, $build_file_fp, $build_file);
if (count(dbsteward::$limit_to_tables) == 0) {
$build_file_ofs->write("-- full database definition file generated " . date('r') . "\n");
}
if (!dbsteward::$generate_slonik) {
$build_file_ofs->write("BEGIN;\n\n");
}
dbsteward::info("Calculating table foreign key dependency order..");
$table_dependency = xml_parser::table_dependency_order($db_doc);
// database-specific implementation code refers to dbsteward::$new_database when looking up roles/values/conflicts etc
dbsteward::$new_database = $db_doc;
dbx::set_default_schema($db_doc, 'public');
// language defintions
if (dbsteward::$create_languages) {
foreach ($db_doc->language as $language) {
$build_file_ofs->write(pgsql8_language::get_creation_sql($language));
}
}
// by default, postgresql will validate the contents of LANGUAGE SQL functions during creation
// because we are creating all functions before tables, this doesn't work when LANGUAGE SQL functions
// refer to tables yet to be created.
// scan language="sql" functions for <functionDefiniton>s that contain FROM (<TABLE>) statements
$set_check_function_bodies = TRUE;
// on in default postgresql configs
dbx::set_default_schema($db_doc, 'public');
foreach ($db_doc->schema as $schema) {
foreach ($schema->function as $function) {
if (pgsql8_function::has_definition($function)) {
$definition = pgsql8_function::get_definition($function);
if (strcasecmp($definition['language'], 'sql') == 0 && $definition['sqlFormat'] == 'pgsql8' && !is_null($referenced_table_name = static::function_definition_references_table($definition))) {
$table_schema_name = sql_parser::get_schema_name($referenced_table_name, $db_doc);
$node_schema = dbx::get_schema($db_doc, $table_schema_name);
$node_table = dbx::get_table($node_schema, sql_parser::get_object_name($referenced_table_name));
if ($node_table) {
// the referenced table is in the definition
// turn off check_function_bodies
$set_check_function_bodies = FALSE;
$set_check_function_bodies_info = "Detected LANGUAGE SQL function " . $schema['name'] . '.' . $function['name'] . " referring to table " . $table_schema_name . '.' . $node_table['name'] . " in the database definition";
dbsteward::info($set_check_function_bodies_info);
break 2;
}
}
}
}
}
if (!$set_check_function_bodies) {
$build_file_ofs->write("\n");
$build_file_ofs->write("SET check_function_bodies = FALSE; -- DBSteward " . $set_check_function_bodies_info . "\n\n");
}
if (dbsteward::$only_schema_sql || !dbsteward::$only_data_sql) {
dbsteward::info("Defining structure");
pgsql8::build_schema($db_doc, $build_file_ofs, $table_dependency);
}
if (!dbsteward::$only_schema_sql || dbsteward::$only_data_sql) {
dbsteward::info("Defining data inserts");
pgsql8::build_data($db_doc, $build_file_ofs, $table_dependency);
}
dbsteward::$new_database = NULL;
if (!dbsteward::$generate_slonik) {
$build_file_ofs->write("COMMIT;\n\n");
}
if (dbsteward::$generate_slonik) {
$replica_sets = static::get_slony_replica_sets($db_doc);
foreach ($replica_sets as $replica_set) {
// output preamble file standalone for tool chains that use the preamble to do additional slonik commands
pgsql8::build_slonik_preamble($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_preamble.slonik");
// output paths specificity standalone for tool chains that use the store path slonik statements separately
pgsql8::build_slonik_paths($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_paths.slonik");
// output create set file standalone for tool chains that use the create_set slonik separately
$create_set_filename = $output_prefix . '_slony_replica_set_' . $replica_set['id'] . '_create_set.slonik';
pgsql8::build_slonik_create_set($db_doc, $replica_set, $create_set_filename);
pgsql8::build_slonik_preamble($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_create_nodes.slonik");
pgsql8::build_slonik_store_nodes($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_create_nodes.slonik");
pgsql8::build_slonik_paths($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_create_nodes.slonik");
// build full subscribe steps that creates sets and subscribes nodes
$subscribe_filename = $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_subscribe.slonik";
pgsql8::build_slonik_preamble($db_doc, $replica_set, $subscribe_filename);
// create_set does one time slony configuration comparison.
// so append the content of _create_set into _subscribe built earlier
file_put_contents($subscribe_filename, file_get_contents($create_set_filename), FILE_APPEND);
foreach ($replica_set->slonyReplicaSetNode as $replica_set_node) {
pgsql8::build_slonik_subscribe_set_node($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_subscribe.slonik", $replica_set_node);
}
static::slony_ids_required_during_build($replica_set, $db_doc);
}
$count = 0;
foreach (array_keys(self::$sequence_slony_ids) as $slony_set_id) {
$count += count(self::$sequence_slony_ids[$slony_set_id]);
}
dbsteward::notice("[slony] ID summary: " . count(self::$table_slony_ids) . " tables " . $count . " sequences");
dbsteward::notice("[slony] table ID segments: " . static::slony_id_segment_summary(self::$table_slony_ids));
// keep this from bombing on there being no ids in $sequence_slony_ids
// if there were none returned (i.e. either there weren't any defined
// or they were all set to IGNORE_REQUIRED which hopefully doesn't happen
// because why would you do that for all of them)
if (!empty(self::$sequence_slony_ids)) {
foreach (array_keys(self::$sequence_slony_ids) as $slony_set_id) {
$console_line = "[slony] sequence ID segments";
if ($slony_set_id != 'NoSlonySet') {
$console_line .= " for slonySetId {$slony_set_id}";
}
$console_line .= ": ";
dbsteward::notice($console_line . static::slony_id_segment_summary(self::$sequence_slony_ids[$slony_set_id]));
}
}
}
return $db_doc;
}
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;
}
