public function setUp()
{
dbsteward::set_sql_format('mysql5');
dbsteward::$quote_all_names = true;
mysql5::$swap_function_delimiters = TRUE;
mysql5::$use_auto_increment_table_options = FALSE;
mysql5::$use_schema_name_prefix = FALSE;
}
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();
}
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::$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::$old_database = new SimpleXMLElement($db_doc_xml);
dbsteward::$new_database = new SimpleXMLElement($db_doc_xml);
}
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 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);
}
protected function apply_options_mysql5()
{
dbsteward::set_sql_format('mysql5');
dbsteward::$quote_schema_names = TRUE;
dbsteward::$quote_table_names = TRUE;
dbsteward::$quote_column_names = TRUE;
dbsteward::$quote_all_names = TRUE;
mysql5::$swap_function_delimiters = TRUE;
mysql5::$use_auto_increment_table_options = FALSE;
mysql5::$use_schema_name_prefix = FALSE;
}