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);
    }
Пример #2
0
 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);
    }
Пример #6
0
 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;
 }