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 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 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_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);
}
