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);
}
/**
* This works differently than sql99_diff_indexes::diff_indexes_table because of the way
* mysql5 handles indexes on foreign keys. If we DROP then CREATE an index being used in an FK
* in separate statements, like we do for other formats, MySQL errors because an FK constraint
* relies on that index. If you DROP and CREATE in the same statement, though, MySQL is happy.
* So, just mash all the index changes for each table into a single ALTER TABLE and be done with it.
*/
public static function diff_indexes_table($ofs, $old_schema, $old_table, $new_schema, $new_table)
{
$bits = self::diff_indexes_table_bits($old_schema, $old_table, $new_schema, $new_table);
if (!empty($bits)) {
$ofs->write('ALTER TABLE ' . mysql5::get_fully_qualified_table_name($new_schema['name'], $new_table['name']) . "\n " . implode(",\n ", $bits) . ";\n\n");
}
}
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;
}
/**
* 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();
}
protected function extract($sql)
{
$schemaname = __CLASS__;
$sql = rtrim($sql, ';');
$sql = "DROP DATABASE IF EXISTS {$schemaname};\nCREATE DATABASE {$schemaname};\nUSE {$schemaname};\n{$sql};";
$this->query($sql);
$xml = mysql5::extract_schema($this->conn->get_dbhost(), $this->conn->get_dbport(), $schemaname, $this->conn->get_dbuser(), $this->conn->get_dbpass());
$dbdoc = simplexml_load_string($xml);
foreach ($dbdoc->schema as $schema) {
if (strcmp($schema['name'], $schemaname) == 0) {
if (static::DEBUG) {
echo "Got schema:\n" . $schema->asXML() . "\n";
}
return $schema;
}
}
echo $dbdoc->asXML() . "\n";
throw new exception("No schema named {$schemaname} was found!?");
}
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;
}
}
public function setUp()
{
dbsteward::set_sql_format('mysql5');
dbsteward::$quote_schema_names = TRUE;
dbsteward::$quote_table_names = TRUE;
dbsteward::$quote_column_names = TRUE;
dbsteward::$quote_function_names = TRUE;
dbsteward::$quote_object_names = TRUE;
mysql5::$use_auto_increment_table_options = FALSE;
mysql5::$use_schema_name_prefix = FALSE;
$db_doc_xml = <<<XML
<dbsteward>
<database>
<role>
<owner>the_owner</owner>
<customRole>SOMEBODY</customRole>
</role>
</database>
</dbsteward>
XML;
dbsteward::$new_database = new SimpleXMLElement($db_doc_xml);
}
public function testCreateTable()
{
$xml = <<<XML
<schema name="public" owner="NOBODY">
<table name="test" primaryKey="id" owner="NOBODY" description="test description">
<tableOption sqlFormat="mysql5" name="auto_increment" value="5"/>
<tableOption sqlFormat="mysql5" name="engine" value="InnoDB"/>
<tableOption sqlFormat="pgsql8" name="inherits" value="other"/>
<column name="id" type="int"/>
<column name="foo" type="int"/>
</table>
</schema>
XML;
$schema = new SimpleXMLElement($xml);
mysql5::$use_auto_increment_table_options = TRUE;
$expected = <<<SQL
CREATE TABLE `test` (
`id` int,
`foo` int
)
AUTO_INCREMENT=5
ENGINE=InnoDB
COMMENT 'test description';
SQL;
$actual = mysql5_table::get_creation_sql($schema, $schema->table);
$this->assertEquals($expected, $actual);
mysql5::$use_auto_increment_table_options = FALSE;
$expected = <<<SQL
CREATE TABLE `test` (
`id` int,
`foo` int
)
ENGINE=InnoDB
COMMENT 'test description';
SQL;
$actual = mysql5_table::get_creation_sql($schema, $schema->table);
$this->assertEquals($expected, $actual);
}
public static function get_permission_sql($db_doc, $node_schema, $node_object, $node_permission, $action = 'grant')
{
if (strcasecmp($node_permission->getName(), 'grant') != 0 && strcasecmp($node_permission->getName(), 'revoke') != 0) {
throw new exception("Cannot extract permission rights from node that is not grant or revoke");
}
if (!isset($node_permission['operation']) || strlen($node_permission['operation']) == 0) {
throw new exception("node_permission operation definition is empty");
}
$object_name = '';
$object_type = strtoupper($node_object->getName());
$privileges = array_map(function ($p) use($object_type) {
return mysql5_permission::get_real_privilege($p, $object_type);
}, static::get_permission_privileges($node_permission));
$roles = static::get_permission_roles($db_doc, $node_permission);
$with = static::get_permission_options_sql($node_permission);
switch ($object_type) {
case 'SCHEMA':
// all tables on current database, because no schemas
$object_name = '*';
break;
case 'VIEW':
return "-- Ignoring permissions on view '{$node_object['name']}' because MySQL uses SQL SECURITY DEFINER semantics\n";
case 'TABLE':
$object_name = mysql5::get_fully_qualified_table_name($node_schema['name'], $node_object['name']);
break;
case 'FUNCTION':
$object_name = "FUNCTION " . mysql5::get_fully_qualified_object_name($node_schema['name'], $node_object['name'], 'function');
break;
case 'SEQUENCE':
// sequences exist as rows in a table for mysql
$object_name = mysql5::get_fully_qualified_table_name($node_schema['name'], mysql5_sequence::TABLE_NAME);
break;
default:
throw new exception("unknown object type encountered: " . $object_type);
}
$sql = static::get_sql(strtoupper($action), $object_name, $privileges, array_map('mysql5::get_quoted_object_name', $roles), $with) . "\n";
return $sql;
}
/**
* @group mysql5
*/
public function testUpgradeNewTableMysql5()
{
$this->apply_options_mysql5();
$this->setup_mysql5();
// upgrade from base
// to base + strict action table + new resolution table
// check null specificity
$base_db_doc = xml_parser::xml_composite(array($this->xml_file_a));
$newtable_db_doc = xml_parser::xml_composite(array($this->xml_file_a, $this->xml_file_b, $this->xml_file_c));
mysql5::build_upgrade('', 'newtable_upgrade_test_pgsql8_base', $base_db_doc, array(), $this->output_prefix, 'newtable_upgrade_test_pgsql8_newtable', $newtable_db_doc, array());
$text = file_get_contents($this->output_prefix . '_upgrade_single_stage.sql');
// make sure NOT NULL is specified for description column
$this->assertContains('`description` character varying(200) NOT NULL', $text);
// make sure NOT NULL is specified for resolution column
$this->assertContains('`resolution` character varying(16) NOT NULL', $text);
// make sure NOT NULL is NOT specified for points column
$this->assertNotContains('`points` int NOT NULL', $text);
}
public function testDropSchemaWithObjects()
{
mysql5::$use_schema_name_prefix = TRUE;
$old = <<<XML
<schema name="s1" owner="NOBODY">
<table name="table1" owner="NOBODY" primaryKey="col1">
<column name="col1" type="int" />
</table>
</schema>
<schema name="s2" owner="NOBODY">
<table name="table2" owner="NOBODY" primaryKey="col1">
<column name="col1" type="int" />
</table>
<type name="yesno" type="enum">
<enum name="yes"/>
<enum name="no"/>
</type>
<function name="test_concat" returns="text" owner="ROLE_OWNER" cachePolicy="VOLATILE" description="a test function that concats strings">
<functionParameter name="param1" type="text" />
<functionParameter name="param2" type="text" />
<functionDefinition language="sql" sqlFormat="mysql5">
RETURN CONCAT(param1, param2);
</functionDefinition>
</function>
<sequence name="the_sequence" owner="NOBODY" max="10" cycle="true" inc="3" start="2"/>
<trigger name="trigger" sqlFormat="mysql5" table="table2" when="BEFORE" event="insert" function="EXECUTE xyz"/>
<view name="view" owner="NOBODY" description="Description goes here">
<viewQuery sqlFormat="mysql5">SELECT * FROM table2</viewQuery>
</view>
</schema>
XML;
$new = <<<XML
<schema name="s1" owner="NOBODY">
<table name="table1" owner="NOBODY" primaryKey="col1">
<column name="col1" type="int" />
</table>
</schema>
XML;
$expected1 = <<<SQL
DROP VIEW IF EXISTS `s2_view`;
SQL;
$expected3 = <<<SQL
-- dropping enum type yesno. references to type yesno will be replaced with the type 'text'
DROP FUNCTION IF EXISTS `s2_test_concat`;
DELETE FROM `__sequences` WHERE `name` IN ('the_sequence');
-- `s2_table2` triggers, indexes, constraints will be implicitly dropped when the table is dropped
-- `s2_table2` will be dropped later according to table dependency order
DROP TABLE `s2_table2`;
SQL;
$this->diff($old, $new, $expected1, $expected3);
}
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);
}
private static function get_recreate_table_sql($schema, $table)
{
$fq_name = mysql5::get_fully_qualified_table_name($schema['name'], $table['name']);
$fq_tmp_name = mysql5::get_fully_qualified_table_name($schema['name'], $table['name'] . '_DBSTEWARD_MIGRATION');
// utilize MySQL's CREATE TABLE ... SELECT syntax for cleaner recreation
// see: http://dev.mysql.com/doc/refman/5.5/en/create-table-select.html
$sql = "CREATE TABLE {$fq_tmp_name}";
$opt_sql = mysql5_table::get_table_options_sql(mysql5_table::get_table_options($schema, $table));
if (!empty($opt_sql)) {
$sql .= "\n" . $opt_sql;
}
if (strlen($table['description']) > 0) {
$sql .= "\nCOMMENT " . mysql5::quote_string_value($table['description']);
}
$sql .= "\nSELECT * FROM {$fq_name};\n";
$sql .= "DROP TABLE {$fq_name};\n";
$sql .= "RENAME TABLE {$fq_tmp_name} TO {$fq_name};";
return $sql;
}
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);
}
private static function diff_single($ofs, $old_seq, $new_seq)
{
$sql = array();
if (!dbsteward::$ignore_oldnames && !empty($new_seq['oldSequenceName'])) {
$sql[] = mysql5::get_quoted_column_name(mysql5_sequence::SEQ_COL) . " = '{$new_seq['name']}'";
}
if ($new_seq['inc'] == null && $old_seq['inc'] != null) {
$sql[] = mysql5::get_quoted_column_name(mysql5_sequence::INC_COL) . ' = DEFAULT';
}
if ($new_seq['inc'] != null && strcasecmp($new_seq['inc'], $old_seq['inc']) != 0) {
$sql[] = mysql5::get_quoted_column_name(mysql5_sequence::INC_COL) . ' = ' . $new_seq['inc'];
}
if ($new_seq['min'] == null && $old_seq['min'] != null) {
$sql[] = mysql5::get_quoted_column_name(mysql5_sequence::MIN_COL) . ' = DEFAULT';
} elseif ($new_seq['min'] != null && strcasecmp($new_seq['min'], $old_seq['min']) != 0) {
$sql[] = mysql5::get_quoted_column_name(mysql5_sequence::MIN_COL) . ' = ' . $new_seq['min'];
}
if ($new_seq['max'] == null && $old_seq['max'] != null) {
$sql[] = mysql5::get_quoted_column_name(mysql5_sequence::MAX_COL) . ' = DEFAULT';
} elseif ($new_seq['max'] != null && strcasecmp($new_seq['max'], $old_seq['max']) != 0) {
$sql[] = mysql5::get_quoted_column_name(mysql5_sequence::MAX_COL) . ' = ' . $new_seq['max'];
}
if ($new_seq['start'] != null && strcasecmp($new_seq['start'], $old_seq['start']) != 0) {
$sql[] = mysql5::get_quoted_column_name(mysql5_sequence::CUR_COL) . ' = ' . $new_seq['start'];
}
if ($new_seq['cycle'] == null && $old_seq['cycle'] != null) {
$sql[] = mysql5::get_quoted_column_name(mysql5_sequence::CYC_COL) . ' = DEFAULT';
} elseif ($new_seq['cycle'] != null && strcasecmp($new_seq['cycle'], $old_seq['cycle']) != 0) {
$value = strcasecmp($new_seq['cycle'], 'false') == 0 ? 'FALSE' : 'TRUE';
$sql[] = mysql5::get_quoted_column_name(mysql5_sequence::CYC_COL) . ' = ' . $value;
}
if (!empty($sql)) {
$out = "UPDATE " . mysql5::get_quoted_table_name(mysql5_sequence::TABLE_NAME);
$out .= "\nSET " . implode(",\n ", $sql);
$out .= "\nWHERE " . mysql5::get_quoted_column_name(mysql5_sequence::SEQ_COL) . " = ";
$out .= "'" . $old_seq['name'] . "'";
$ofs->write("{$out};\n");
}
}
public function testDelimiters()
{
mysql5::$swap_function_delimiters = TRUE;
$actual = mysql5_sequence::get_shim_creation_sql();
$actual = trim(preg_replace('/--.*(\\n\\s*)?/', '', $actual));
$expected = <<<SQL
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;
DELIMITER \$_\$
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\$_\$
DELIMITER ;
SQL;
$this->assertEquals($expected, $actual);
}
public function testNullToNotNullDoesNotUpdate()
{
mysql5::$use_schema_name_prefix = TRUE;
$old = <<<XML
<schema name="s1" owner="NOBODY">
<table name="t1" owner="NOBODY" primaryKey="col1">
<column name="col1" type="int" default="2" null="true" />
</table>
</schema>
XML;
$new = <<<XML
<schema name="s1" owner="NOBODY">
<table name="t1" owner="NOBODY" primaryKey="col1">
<column name="col1" type="int" default="2" null="false" />
</table>
</schema>
XML;
$expected1 = "ALTER TABLE `s1_t1`\n MODIFY COLUMN `col1` int NOT NULL DEFAULT 2;";
$this->common_diff($old, $new, $expected1, '', 'Changing NULL->NOT NULL should only result in a single stage 1 ALTER');
}
public function testDropAddAlter()
{
$old = <<<XML
<schema name="public" owner="NOBODY">
<table name="test" primaryKey="a" owner="NOBODY">
<tableOption sqlFormat="mysql5" name="engine" value="InnoDB"/>
<tableOption sqlFormat="mysql5" name="auto_increment" value="5"/>
<column name="a" type="int"/>
</table>
</schema>
XML;
$new = <<<XML
<schema name="public" owner="NOBODY">
<table name="test" primaryKey="a" owner="NOBODY">
<tableOption sqlFormat="mysql5" name="auto_increment" value="10"/>
<tableOption sqlFormat="mysql5" name="row_format" value="compressed"/>
<column name="a" type="int"/>
</table>
</schema>
XML;
mysql5::$use_auto_increment_table_options = TRUE;
$expected = <<<SQL
-- Table `test` must be recreated to drop options: engine
CREATE TABLE `test_DBSTEWARD_MIGRATION`
AUTO_INCREMENT=10
ROW_FORMAT=compressed
SELECT * FROM `test`;
DROP TABLE `test`;
RENAME TABLE `test_DBSTEWARD_MIGRATION` TO `test`;
SQL;
$this->common($old, $new, $expected);
mysql5::$use_auto_increment_table_options = FALSE;
$expected = <<<SQL
-- Table `test` must be recreated to drop options: engine
CREATE TABLE `test_DBSTEWARD_MIGRATION`
ROW_FORMAT=compressed
SELECT * FROM `test`;
DROP TABLE `test`;
RENAME TABLE `test_DBSTEWARD_MIGRATION` TO `test`;
SQL;
$this->common($old, $new, $expected);
}
/**
* Creates and returns SQL command for dropping the sequence.
*
* @return string
*/
public static function get_drop_sql($node_schema, $node_sequences)
{
$table_name = mysql5::get_quoted_table_name(self::TABLE_NAME);
$seq_col = mysql5::get_quoted_column_name(self::SEQ_COL);
$sequences = dbx::to_array($node_sequences);
if (count($sequences) === 0) {
return '';
}
$sequence_names = "('" . implode("', '", array_map(function ($n) {
if ($n instanceof SimpleXMLElement) {
return $n['name'];
}
return $n;
}, $sequences)) . "')";
return "DELETE FROM {$table_name} WHERE {$seq_col} IN {$sequence_names};";
}
protected static function get_dimension_list($node_schema, $node_table, $node_index)
{
$dimensions = array();
foreach ($node_index->indexDimension as $dimension) {
// mysql only supports indexed columns, not indexed expressions like in pgsql or mssql
if (!mysql5_table::contains_column($node_table, $dimension)) {
throw new Exception("Table " . mysql5::get_fully_qualified_table_name($node_schema['name'], $node_table['name']) . " does not contain column '{$dimension}'");
}
$dimensions[] = mysql5::get_quoted_column_name($dimension);
}
return $dimensions;
}
protected static function drop_old_schemas($ofs)
{
$drop_sequences = array();
if (is_array(mysql5_diff::$old_table_dependency)) {
$deps = mysql5_diff::$old_table_dependency;
$processed_schemas = array();
foreach ($deps as $dep) {
$old_schema = $dep['schema'];
if (!dbx::get_schema(dbsteward::$new_database, $old_schema['name'])) {
// this schema is being dropped, drop all children objects in it
if (!in_array(trim($old_schema['name']), $processed_schemas)) {
// this schema hasn't been processed yet, go ahead and drop views, types, functions, sequences
// only do it once per schema
foreach ($old_schema->type as $node_type) {
$ofs->write(mysql5_type::get_drop_sql($old_schema, $node_type) . "\n");
}
foreach ($old_schema->function as $node_function) {
$ofs->write(mysql5_function::get_drop_sql($old_schema, $node_function) . "\n");
}
foreach ($old_schema->sequence as $node_sequence) {
$ofs->write(mysql5_sequence::get_drop_sql($old_schema, $node_sequence) . "\n");
}
$processed_schemas[] = trim($old_schema['name']);
}
if ($dep['table']['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) {
// don't do anything with this table, it is a magic internal DBSteward value
continue;
}
// constraints, indexes, triggers will be deleted along with the tables they're attached to
// tables will drop themselves later on
// $ofs->write(mysql5_table::get_drop_sql($old_schema, $dep['table']) . "\n");
$table_name = mysql5::get_fully_qualified_table_name($dep['schema']['name'], $dep['table']['name']);
$ofs->write("-- {$table_name} triggers, indexes, constraints will be implicitly dropped when the table is dropped\n");
$ofs->write("-- {$table_name} will be dropped later according to table dependency order\n");
// table sequences need dropped separately
foreach (mysql5_table::get_sequences_needed($old_schema, $dep['table']) as $node_sequence) {
$ofs->write(mysql5_sequence::get_drop_sql($old_schema, $node_sequence) . "\n");
}
}
}
} else {
foreach (dbsteward::$old_database->schema as $old_schema) {
if (!dbx::get_schema(dbsteward::$new_database, $old_schema['name'])) {
foreach ($old_schema->type as $node_type) {
$ofs->write(mysql5_type::get_drop_sql($old_schema, $node_type) . "\n");
}
foreach ($old_schema->function as $node_function) {
$ofs->write(mysql5_function::get_drop_sql($old_schema, $node_function) . "\n");
}
foreach ($old_schema->sequence as $node_sequence) {
$ofs->write(mysql5_sequence::get_drop_sql($old_schema, $node_sequence) . "\n");
}
foreach ($old_schema->table as $node_table) {
// tables will drop themselves later on
// $ofs->write(mysql5_table::get_drop_sql($old_schema, $node_table) . "\n");
$table_name = mysql5::get_fully_qualified_table_name($old_schema['name'], $node_table['name']);
$ofs->write("-- {$table_name} triggers, indexes, constraints will be implicitly dropped when the table is dropped\n");
$ofs->write("-- {$table_name} will be dropped later according to table dependency order\n");
foreach (mysql5_table::get_sequences_needed($old_schema, $node_table) as $node_sequence) {
$ofs->write(mysql5_sequence::get_drop_sql($old_schema, $node_sequence) . "\n");
}
}
}
}
}
}
public function testDelimiters()
{
$xml = <<<XML
<schema name="test" owner="ROLE_OWNER">
<function name="test_fn" returns="text">
<functionParameter name="a" type="text"/>
<functionParameter name="b" type="int"/>
<functionParameter name="c" type="date"/>
<functionDefinition language="sql" sqlFormat="mysql5">
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;
</functionDefinition>
</function>
</schema>
XML;
$schema = new SimpleXMLElement($xml);
$expected = <<<SQL
DROP FUNCTION IF EXISTS `test_fn`;
CREATE DEFINER = CURRENT_USER FUNCTION `test_fn` (`a` text, `b` int, `c` date)
RETURNS text
LANGUAGE SQL
MODIFIES SQL DATA
NOT DETERMINISTIC
SQL SECURITY INVOKER
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;
SQL;
$actual = trim(mysql5_function::get_creation_sql($schema, $schema->function));
$this->assertEquals($expected, $actual);
mysql5::$swap_function_delimiters = TRUE;
$expected = <<<SQL
DROP FUNCTION IF EXISTS `test_fn`;
DELIMITER \$_\$
CREATE DEFINER = CURRENT_USER FUNCTION `test_fn` (`a` text, `b` int, `c` date)
RETURNS text
LANGUAGE SQL
MODIFIES SQL DATA
NOT DETERMINISTIC
SQL SECURITY INVOKER
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\$_\$
DELIMITER ;
SQL;
$actual = trim(mysql5_function::get_creation_sql($schema, $schema->function));
$this->assertEquals($expected, $actual);
}
public static function get_triggers_needed($schema, $table)
{
$triggers = array();
foreach ($table->column as $column) {
// we need a trigger for each serial column
if (mysql5_column::is_serial($column['type'])) {
$trigger_name = mysql5_column::get_serial_trigger_name($schema, $table, $column);
$sequence_name = mysql5_column::get_serial_sequence_name($schema, $table, $column);
$table_name = $table['name'];
$column_name = mysql5::get_quoted_column_name($column['name']);
$xml = <<<XML
<trigger name="{$trigger_name}"
sqlFormat="mysql5"
when="BEFORE"
event="INSERT"
table="{$table_name}"
forEach="ROW"
function="SET NEW.{$column_name} = COALESCE(NEW.{$column_name}, nextval('{$sequence_name}'));"/>
XML;
$triggers[] = new SimpleXMLElement($xml);
}
// @TODO: convert DEFAULT expressions (not constants) to triggers for pgsql compatibility
}
return $triggers;
}
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());
}
protected function do_upgrade($sql_format)
{
$old_db_doc = simplexml_load_file($this->xml_file_a);
$new_db_doc = simplexml_load_file($this->xml_file_b);
$this->output_prefix = dirname(__FILE__) . '/testdata/' . $sql_format . '_unit_test_xml_a';
// need to unfortunately do the one thing austin told me not to:
// use more than one format type per run
if (strcasecmp($sql_format, 'pgsql8') == 0) {
pgsql8::build_upgrade('', $old_db_doc, $old_db_doc, array(), $this->output_prefix, $new_db_doc, $new_db_doc, array());
} else {
if (strcasecmp($sql_format, 'mysql5') == 0) {
mysql5::build_upgrade('', $old_db_doc, $old_db_doc, array(), $this->output_prefix, $new_db_doc, $new_db_doc, array());
} else {
$this->fail("This test only uses pgsql8 and mysql5 formats, but can be expanded.");
}
}
}
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;
}
/**
* escape data types that need it
*
* @param mixed $value value to check for escaping
*
* @value mixed value, escaped as necessary
*/
public static function value_escape($type, $value, $db_doc = NULL)
{
if (strlen($value) > 0) {
// data types that should be quoted
$enum_regex = dbx::enum_regex($db_doc);
if (strlen($enum_regex) > 0) {
$enum_regex = '|' . $enum_regex;
}
$PATTERN_QUOTED_TYPES = "/^char.*|^string|^date.*|^time.*|^varchar.*|^interval|^money.*|^inet" . $enum_regex . "/i";
// strip quoting if it is a quoted type, it will be added after conditional conversion
if (preg_match($PATTERN_QUOTED_TYPES, $type) > 0) {
$value = mysql5::strip_single_quoting($value);
}
// complain when assholes use colon time notation instead of postgresql verbose for interval expressions
if (dbsteward::$require_verbose_interval_notation) {
if (preg_match('/interval/i', $type) > 0) {
if (substr($value, 0, 1) != '@') {
throw new exception("bad interval value: " . $value . " -- interval types must be postgresql verbose format: '@ 2 hours 30 minutes' etc for cfxn comparisons to work");
}
}
}
// mssql doesn't understand epoch
if (stripos('date', $type) !== FALSE && strcasecmp($value, 'epoch') == 0) {
$value = '1970-01-01';
}
// special case for postgresql type value conversion
// the boolean type for the column would have been translated to char(1) by xml_parser::mysql5_type_convert()
if (strcasecmp($type, 'char(1)') == 0) {
$value = mysql5::boolean_value_convert($value);
} else {
if (strcasecmp($type, 'datetimeoffset(7)') == 0) {
$value = date('c', strtotime($value));
// use date()'s ISO 8601 date format to be conformant
} else {
if (strcasecmp($type, 'datetime2') == 0) {
$value = date('Y-m-dTG:i:s', strtotime($value));
// use date() to make date format conformant
} else {
if (strcasecmp($type, 'time') == 0 && strlen($value) > 8) {
$value = substr($value, 0, 8);
}
}
}
}
if (preg_match($PATTERN_QUOTED_TYPES, $type) > 0) {
$value = static::quote_string_value($value);
}
} else {
// value is zero length, make it NULL
$value = "NULL";
}
return $value;
}
public static function get_drop_sql($node_schema, $node_view)
{
return "DROP VIEW IF EXISTS " . mysql5::get_fully_qualified_table_name($node_schema['name'], $node_view['name']) . ";";
}
protected static function define_sql_format_default_values($sql_format, $options)
{
///// sql_format-specific default options
$dbport = FALSE;
if (strcasecmp($sql_format, 'pgsql8') == 0) {
dbsteward::$create_languages = TRUE;
dbsteward::$quote_schema_names = FALSE;
dbsteward::$quote_table_names = FALSE;
dbsteward::$quote_column_names = FALSE;
$dbport = '5432';
} else {
if (strcasecmp($sql_format, 'mssql10') == 0) {
// needed for MSSQL keyword-named-columns like system_user
dbsteward::$quote_table_names = TRUE;
dbsteward::$quote_column_names = TRUE;
$dbport = '1433';
} else {
if (strcasecmp($sql_format, 'mysql5') == 0) {
dbsteward::$quote_schema_names = TRUE;
dbsteward::$quote_table_names = TRUE;
dbsteward::$quote_column_names = TRUE;
$dbport = '3306';
if (isset($options['useautoincrementoptions'])) {
mysql5::$use_auto_increment_table_options = TRUE;
}
if (isset($options['useschemaprefix'])) {
mysql5::$use_schema_name_prefix = TRUE;
}
}
}
}
if (strcasecmp($sql_format, 'pgsql8') != 0) {
if (isset($options['pgdataxml'])) {
dbsteward::error("pgdataxml parameter is not supported by " . dbsteward::get_sql_format() . " driver");
exit(1);
}
}
return $dbport;
}
