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 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('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;
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 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);
}
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;
}
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 testBuildWithPrefixes()
{
mysql5::$use_schema_name_prefix = TRUE;
$xml = <<<XML
<dbsteward>
<database>
<role>
<owner>the_owner</owner>
</role>
</database>
<schema name="schema1" owner="ROLE_OWNER">
<table name="table1" owner="ROLE_OWNER" primaryKey="col1">
<column name="col1" type="int"/>
<column name="col2" type="type1"/>
</table>
<type name="type1" type="enum">
<enum name="A"/>
<enum name="B"/>
</type>
<function name="function1" returns="text">
<functionParameter name="a" type="text"/>
<functionParameter name="b" type="int"/>
<functionParameter name="c" type="date"/>
<functionDefinition language="sql" sqlFormat="mysql5">
RETURN 'xyz';
</functionDefinition>
</function>
</schema>
<schema name="schema2" owner="ROLE_OWNER">
<table name="table2" owner="ROLE_OWNER" primaryKey="col2">
<column name="col2" type="int"/>
</table>
<sequence name="sequence1" owner="ROLE_OWNER" />
<trigger name="trigger1" sqlFormat="mysql5" table="table2" when="before" event="insert" function="EXECUTE xyz"/>
<view name="view" owner="ROLE_OWNER">
<viewQuery sqlFormat="mysql5">SELECT * FROM table2</viewQuery>
</view>
</schema>
</dbsteward>
XML;
$expected = <<<'SQL'
DROP FUNCTION IF EXISTS `schema1_function1`;
DELIMITER $_$
CREATE DEFINER = CURRENT_USER FUNCTION `schema1_function1` (`a` text, `b` int, `c` date)
RETURNS text
LANGUAGE SQL
MODIFIES SQL DATA
NOT DETERMINISTIC
SQL SECURITY INVOKER
RETURN 'xyz'$_$
DELIMITER ;
CREATE TABLE `schema1_table1` (
`col1` int,
`col2` ENUM('A','B')
);
CREATE TABLE `schema2_table2` (
`col2` int
);
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 ;
INSERT INTO `__sequences`
(`name`, `increment`, `min_value`, `max_value`, `cur_value`, `start_value`, `cycle`)
VALUES
('sequence1', DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);
DROP TRIGGER IF EXISTS `schema2_trigger1`;
CREATE TRIGGER `schema2_trigger1` BEFORE INSERT ON `schema2_table2`
FOR EACH ROW EXECUTE xyz;
ALTER TABLE `schema1_table1`
ADD PRIMARY KEY (`col1`);
ALTER TABLE `schema2_table2`
ADD PRIMARY KEY (`col2`);
CREATE OR REPLACE DEFINER = the_owner SQL SECURITY DEFINER VIEW `schema2_view`
AS SELECT * FROM table2;
SQL;
$this->common($xml, $expected);
}
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;
}
