diff options
10 files changed, 192 insertions, 305 deletions
diff --git a/libraries/plugins/export/ExportSql.class.php b/libraries/plugins/export/ExportSql.class.php index 510140cf2d..428f0e590d 100644 --- a/libraries/plugins/export/ExportSql.class.php +++ b/libraries/plugins/export/ExportSql.class.php @@ -2541,216 +2541,164 @@ class ExportSql extends ExportPlugin $sql_query, $aliases, $db, $table = '', &$flag = null ) { $flag = false; - // Return original sql query if no aliases are provided. - if (!is_array($aliases) || empty($aliases) || empty($sql_query)) { + + /** + * The parser of this query. + * @var SqlParser\Parser + */ + $parser = new SqlParser\Parser($sql_query); + + if (empty($parser->statements[0])) { return $sql_query; } - $supported_query_types = array( - 'CREATE' => true, - ); - $supported_query_ons = array( - 'TABLE' => true, - 'VIEW' => true, - 'TRIGGER' => true, - 'FUNCTION' => true, - 'PROCEDURE' => true - ); - $identifier_types = array( - 'alpha_identifier', - 'quote_backtick' - ); - $query_type = ''; - $query_on = ''; - // Adjustment value for each pos value - // of token after replacement - $offset = 0; - $open_braces = 0; - $in_create_table_fields = false; - // flag to force end query parsing - $query_end = false; - // Convert all line feeds to Unix style - $sql_query = str_replace("\r\n", "\n", $sql_query); - $sql_query = str_replace("\r", "\n", $sql_query); - $tokens = PMA_SQP_parse($sql_query); - $ref_seen = false; - $ref_table_seen = false; + + /** + * The statement that represents the query. + * @var SqlParser\CreateStatement + */ + $statement = $parser->statements[0]; + + /** + * Old database name. + * @var string + */ + $old_database = $db; + + /** + * Old table name. + * @var string + */ $old_table = $table; - $on_seen = false; - $size = $tokens['len']; - - for ($i = 0; $i < $size && !$query_end; $i++) { - $type = $tokens[$i]['type']; - $data = $tokens[$i]['data']; - $data_next = isset($tokens[$i+1]['data']) - ? $tokens[$i+1]['data'] : ''; - $data_prev = ($i > 0) ? $tokens[$i-1]['data'] : ''; - $d_unq = PMA_Util::unQuote($data); - $d_unq_next = PMA_Util::unQuote($data_next); - $d_unq_prev = PMA_Util::unQuote($data_prev); - $d_upper = /*overload*/mb_strtoupper($d_unq); - $d_upper_next = /*overload*/mb_strtoupper($d_unq_next); - $d_upper_prev = /*overload*/mb_strtoupper($d_unq_prev); - $pos = $tokens[$i]['pos'] + $offset; - if ($type === 'alpha_reservedWord') { - if ($query_type === '' - && !empty($supported_query_types[$d_upper]) - ) { - $query_type = $d_upper; - } elseif ($query_on === '' - && !empty($supported_query_ons[$d_upper]) - ) { - $query_on = $d_upper; + + // Replacing aliases in `CREATE TABLE` statement. + if ($statement->options->has('TABLE')) { + + // Extracting the name of the old database and table from the + // statement to make sure the parameters are corect. + if (!empty($statement->name->database)) { + $old_database = $statement->name->database; + } + file_put_contents('/tmp/tests.txt', "------------\n", FILE_APPEND); + file_put_contents('/tmp/tests.txt', $sql_query . "\n", FILE_APPEND); + file_put_contents('/tmp/tests.txt', print_r($statement, true), FILE_APPEND); + file_put_contents('/tmp/tests.txt', "------------\n", FILE_APPEND); + $old_table = $statement->name->table; + + // Finding the aliased database name. + // The database might be empty so we have to add a few checks. + $new_database = null; + if (!empty($statement->name->database)) { + $new_database = $statement->name->database; + if (!empty($aliases[$old_database]['alias'])) { + $new_database = $aliases[$old_database]['alias']; } } - // CREATE TABLE - Alias replacement - if ($query_type === 'CREATE' && $query_on === 'TABLE') { - // replace create table name - if (!$in_create_table_fields - && in_array($type, $identifier_types) - && !empty($aliases[$db]['tables'][$table]['alias']) - ) { - $sql_query = $this->substituteAlias( - $sql_query, $data, - $aliases[$db]['tables'][$table]['alias'], - $pos, $offset - ); - $flag = true; - } elseif ($type === 'punct_bracket_open_round') { - // CREATE TABLE fields started - if (!$in_create_table_fields) { - $in_create_table_fields = true; - } - $open_braces++; - } elseif ($type === 'punct_bracket_close_round') { - // end our parsing after last ) - // no columns appear after that - if ($in_create_table_fields && $open_braces === 0) { - $query_end = true; - } - // End of Foreign key reference - if ($ref_seen) { - $ref_seen = $ref_table_seen = false; - $table = $old_table; - } - $open_braces--; - // handles Foreign key references - } elseif ($type === 'alpha_reservedWord' - && $d_upper === 'REFERENCES' - ) { - $ref_seen = true; - } elseif (in_array($type, $identifier_types) - && $ref_seen === true && !$ref_table_seen - ) { - $table = $d_unq; - $ref_table_seen = true; - if (!empty($aliases[$db]['tables'][$table]['alias'])) { - $sql_query = $this->substituteAlias( - $sql_query, $data, - $aliases[$db]['tables'][$table]['alias'], - $pos, $offset - ); + + // Finding the aliases table name. + $new_table = $old_table; + if (!empty($aliases[$old_database]['tables'][$old_table]['alias'])) { + $new_table = $aliases[$old_database]['tables'][$old_table]['alias']; + } + + // Replacing new values. + if (($statement->name->database !== $new_database) + || ($statement->name->table !== $new_table) + ) { + $statement->name->database = $new_database; + $statement->name->table = $new_table; + $statement->name->expr = null; // Force rebuild. + $flag = true; + } + + foreach ($statement->fields as $field) { + + // Column name. + if (!empty($field->type)) { + if (!empty($aliases[$old_database]['tables'][$old_table]['columns'][$field->name])) { + $field->name = $aliases[$old_database]['tables'] + [$old_table]['columns'][$field->name]; $flag = true; } - // Replace column names - } elseif (in_array($type, $identifier_types) - && !empty($aliases[$db]['tables'][$table]['columns'][$d_unq]) - ) { - $sql_query = $this->substituteAlias( - $sql_query, $data, - $aliases[$db]['tables'][$table]['columns'][$d_unq], - $pos, $offset - ); - $flag = true; } - // CREATE TRIGGER - Alias replacement - } elseif ($query_type === 'CREATE' && $query_on === 'TRIGGER') { - // Skip till 'ON' in encountered - if (!$on_seen && $type === 'alpha_reservedWord' - && $d_upper === 'ON' - ) { - $on_seen = true; - } elseif ($on_seen && in_array($type, $identifier_types)) { - if (!$ref_table_seen - && !empty($aliases[$db]['tables'][$d_unq]['alias']) - ) { - $ref_table_seen = true; - $sql_query = $this->substituteAlias( - $sql_query, $data, - $aliases[$db]['tables'][$d_unq]['alias'], - $pos, $offset - ); + + // Key's columns. + if (!empty($field->key)) { + foreach ($field->key->columns as $key => $column) { + if (!empty($aliases[$old_database]['tables'][$old_table]['columns'][$column])) { + $field->key->columns[$key] = $aliases[$old_database] + ['tables'][$old_table]['columns'][$column]; + $flag = true; + } + } + } + + // References. + if (!empty($field->references)) { + $ref_table = $field->references->table; + // Replacing table. + if (!empty($aliases[$old_database]['tables'][$ref_table]['alias'])) { + $field->references->table = $aliases[$old_database]['tables'][$ref_table]['alias']; $flag = true; - } else { - // search for identifier alias - $alias = $this->getAlias($aliases, $d_unq); - if (!empty($alias)) { - $sql_query = $this->substituteAlias( - $sql_query, $data, $alias, $pos, $offset - ); + } + // Replacing column names. + foreach ($field->references->columns as $key => $column) { + if (!empty($aliases[$old_database]['tables'][$ref_table]['columns'][$column])) { + $field->references->columns[$key] = $aliases[$old_database]['tables'][$ref_table]['columns'][$column]; $flag = true; } } } - // CREATE PROCEDURE|FUNCTION|VIEW - Alias replacement - } elseif ($query_type === 'CREATE' - && ($query_on === 'FUNCTION' - || $query_on === 'PROCEDURE' - || $query_on === 'VIEW') - ) { - // LANGUAGE SQL | (READS|MODIFIES) SQL DATA - // characteristics are skipped - if ($type === 'alpha_identifier' - && (($d_upper === 'LANGUAGE' && $d_upper_next === 'SQL') - || ($d_upper === 'DATA' && $d_upper_prev === 'SQL')) - ) { - continue; - // No need to process further in case of VIEW - // when 'WITH' keyword has been detected - } elseif ($query_on === 'VIEW' - && $type === 'alpha_reservedWord' && $d_upper === 'WITH' + } + } elseif ($statement->options->has('TRIGGER')) { + + // Extracting the name of the old database and table from the + // statement to make sure the parameters are corect. + if (!empty($statement->table->database)) { + $old_database = $statement->table->database; + } + $old_table = $statement->table->table; + + if (!empty($aliases[$old_database]['tables'][$old_table]['alias'])) { + $statement->table->table = $aliases[$old_database]['tables'][$old_table]['alias']; + $statement->table->expr = null; // Force rebuild. + $flag = true; + } + } + + if (($statement->options->has('TRIGGER')) + || ($statement->options->has('PROCEDURE')) + || ($statement->options->has('FUNCTION')) + || ($statement->options->has('VIEW')) + ) { + + // Repalcing the body. + for ($i = 0, $count = count($statement->body); $i < $count; ++$i) { + + /** + * Token parsed at this moment. + * @var Token + */ + $token = $statement->body[$i]; + + // Replacing only symbols (that are not variables) and unknown + // identifiers. + if ((($token->type === SqlParser\Token::TYPE_SYMBOL) + && (!($token->flags & SqlParser\Token::FLAG_SYMBOL_VARIABLE))) + || ((($token->type === SqlParser\Token::TYPE_KEYWORD) + && (!($token->flags & SqlParser\Token::FLAG_KEYWORD_RESERVED))) + || ($token->type === SqlParser\Token::TYPE_NONE)) ) { - $query_end = true; - } elseif (in_array($type, $identifier_types)) { - // search for identifier alias - $alias = $this->getAlias($aliases, $d_unq); + $alias = $this->getAlias($aliases, $token->value); if (!empty($alias)) { - $sql_query = $this->substituteAlias( - $sql_query, $data, $alias, $pos, $offset - ); + // Replacing the token. + $token->token = SqlParser\Context::escape($alias); $flag = true; - }; + } } } } - return $sql_query; - } - /** - * substitutes alias in query at given position - * Note: pos is the value from PMA_SQP_parse() + offset - * - * @param string $sql_query the SQL query - * @param string $data the data to be replaced - * @param string $alias the replacement - * @param string $pos the position of alias - * @param string &$offset the change in pos occurred after substitution - * - * @return string replaced query with alias - */ - public function substituteAlias($sql_query, $data, $alias, $pos, &$offset = null) - { - if (!empty($GLOBALS['sql_backquotes'])) { - $alias = PMA_Util::backquote($alias); - } - $alias_len = /*overload*/mb_strlen($alias); - $data_len = /*overload*/mb_strlen($data); - if (isset($offset)) { - $offset += ($alias_len - $data_len); - } - $sql_query = substr_replace( - $sql_query, $alias, $pos - $data_len, $data_len - ); - return $sql_query; + return $statement->build(); } /** diff --git a/libraries/sql-parser/src/Contexts/ContextMySql50000.php b/libraries/sql-parser/src/Contexts/ContextMySql50000.php index 7db5fc3a76..312a53e81d 100644 --- a/libraries/sql-parser/src/Contexts/ContextMySql50000.php +++ b/libraries/sql-parser/src/Contexts/ContextMySql50000.php @@ -149,7 +149,7 @@ class ContextMySql50000 extends Context 'FOR EACH ROW' => 7, 'SQL SECURITY' => 7, 'CHARACTER SET' => 7, 'IF NOT EXISTS' => 7, 'DATA DIRECTORY' => 7, - 'DEFAULT COLLATE' => 7, 'INDEX DIRECTORY' => 7, + 'DEFAULT CHARSET' => 7, 'DEFAULT COLLATE' => 7, 'INDEX DIRECTORY' => 7, 'DEFAULT CHARACTER SET' => 7, 'XML' => 9, diff --git a/libraries/sql-parser/src/Contexts/ContextMySql50100.php b/libraries/sql-parser/src/Contexts/ContextMySql50100.php index a5c4bef0bf..0e5a574a82 100644 --- a/libraries/sql-parser/src/Contexts/ContextMySql50100.php +++ b/libraries/sql-parser/src/Contexts/ContextMySql50100.php @@ -160,7 +160,7 @@ class ContextMySql50100 extends Context 'FOR EACH ROW' => 7, 'SQL SECURITY' => 7, 'CHARACTER SET' => 7, 'IF NOT EXISTS' => 7, 'DATA DIRECTORY' => 7, - 'DEFAULT COLLATE' => 7, 'INDEX DIRECTORY' => 7, + 'DEFAULT CHARSET' => 7, 'DEFAULT COLLATE' => 7, 'INDEX DIRECTORY' => 7, 'DEFAULT CHARACTER SET' => 7, 'XML' => 9, diff --git a/libraries/sql-parser/src/Contexts/ContextMySql50500.php b/libraries/sql-parser/src/Contexts/ContextMySql50500.php index 3faf785315..426bd5233e 100644 --- a/libraries/sql-parser/src/Contexts/ContextMySql50500.php +++ b/libraries/sql-parser/src/Contexts/ContextMySql50500.php @@ -165,7 +165,7 @@ class ContextMySql50500 extends Context 'FOR EACH ROW' => 7, 'SQL SECURITY' => 7, 'CHARACTER SET' => 7, 'IF NOT EXISTS' => 7, 'DATA DIRECTORY' => 7, - 'DEFAULT COLLATE' => 7, 'INDEX DIRECTORY' => 7, + 'DEFAULT CHARSET' => 7, 'DEFAULT COLLATE' => 7, 'INDEX DIRECTORY' => 7, 'DEFAULT CHARACTER SET' => 7, 'XML' => 9, diff --git a/libraries/sql-parser/src/Contexts/ContextMySql50600.php b/libraries/sql-parser/src/Contexts/ContextMySql50600.php index 6a1af74921..e091314ca2 100644 --- a/libraries/sql-parser/src/Contexts/ContextMySql50600.php +++ b/libraries/sql-parser/src/Contexts/ContextMySql50600.php @@ -171,7 +171,7 @@ class ContextMySql50600 extends Context 'FOR EACH ROW' => 7, 'SQL SECURITY' => 7, 'CHARACTER SET' => 7, 'IF NOT EXISTS' => 7, 'DATA DIRECTORY' => 7, - 'DEFAULT COLLATE' => 7, 'INDEX DIRECTORY' => 7, + 'DEFAULT CHARSET' => 7, 'DEFAULT COLLATE' => 7, 'INDEX DIRECTORY' => 7, 'DEFAULT CHARACTER SET' => 7, 'XML' => 9, diff --git a/libraries/sql-parser/src/Contexts/ContextMySql50700.php b/libraries/sql-parser/src/Contexts/ContextMySql50700.php index ac81e8a8d8..207ffd2837 100644 --- a/libraries/sql-parser/src/Contexts/ContextMySql50700.php +++ b/libraries/sql-parser/src/Contexts/ContextMySql50700.php @@ -179,7 +179,7 @@ class ContextMySql50700 extends Context 'FOR EACH ROW' => 7, 'SQL SECURITY' => 7, 'CHARACTER SET' => 7, 'IF NOT EXISTS' => 7, 'DATA DIRECTORY' => 7, - 'DEFAULT COLLATE' => 7, 'INDEX DIRECTORY' => 7, + 'DEFAULT CHARSET' => 7, 'DEFAULT COLLATE' => 7, 'INDEX DIRECTORY' => 7, 'DEFAULT CHARACTER SET' => 7, 'XML' => 9, diff --git a/libraries/sql-parser/src/Fragments/AlterFragment.php b/libraries/sql-parser/src/Fragments/AlterFragment.php index b5c66e81e9..fbcce4ce21 100644 --- a/libraries/sql-parser/src/Fragments/AlterFragment.php +++ b/libraries/sql-parser/src/Fragments/AlterFragment.php @@ -69,6 +69,7 @@ class AlterFragment extends Fragment 'INDEX' => 4, 'DEFAULT CHARACTER SET' => array(5, 'var'), + 'DEFAULT CHARSET' => array(5, 'var'), 'COLLATE' => array(6, 'var'), ); diff --git a/libraries/sql-parser/src/Fragments/KeyFragment.php b/libraries/sql-parser/src/Fragments/KeyFragment.php index 1964cd2bc7..c27d7450de 100644 --- a/libraries/sql-parser/src/Fragments/KeyFragment.php +++ b/libraries/sql-parser/src/Fragments/KeyFragment.php @@ -67,7 +67,14 @@ class KeyFragment extends Fragment */ public $options; - + /** + * Constructor. + * + * @param string $name The name of the key. + * @param array $columns The columns covered by this key. + * @param string $type The type of this key. + * @param OptionsFragment $options The options of this key. + */ public function __construct($name = null, array $columns = array(), $type = null, $options = null ) { diff --git a/libraries/sql-parser/src/Statements/CreateStatement.php b/libraries/sql-parser/src/Statements/CreateStatement.php index 71557c7e10..0fb51932bd 100644 --- a/libraries/sql-parser/src/Statements/CreateStatement.php +++ b/libraries/sql-parser/src/Statements/CreateStatement.php @@ -40,7 +40,6 @@ class CreateStatement extends Statement // CREATE TABLE 'TEMPORARY' => 1, - 'IF NOT EXISTS' => 2, // CREATE FUNCTION / PROCEDURE and CREATE VIEW 'DEFINER' => array(1, 'var='), @@ -62,6 +61,9 @@ class CreateStatement extends Statement 'TRIGGER' => 6, 'USER' => 6, 'VIEW' => 6, + + // CREATE TABLE + 'IF NOT EXISTS' => 7, ); /** @@ -73,12 +75,14 @@ class CreateStatement extends Statement 'ENGINE' => array(1, 'var='), 'AUTO_INCREMENT' => array(2, 'var='), 'AVG_ROW_LENGTH' => array(3, 'var'), - 'DEFAULT CHARACTER SET' => array(4, 'var'), - 'CHARACTER SET' => array(4, 'var'), + 'CHARACTER SET' => array(4, 'var='), + 'CHARSET' => array(4, 'var='), + 'DEFAULT CHARACTER SET' => array(4, 'var='), + 'DEFAULT CHARSET' => array(4, 'var='), 'CHECKSUM' => array(5, 'var'), - 'DEFAULT COLLATE' => array(5, 'var'), - 'COLLATE' => array(6, 'var'), - 'COMMENT' => array(7, 'var'), + 'DEFAULT COLLATE' => array(5, 'var='), + 'COLLATE' => array(6, 'var='), + 'COMMENT' => array(7, 'var='), 'CONNECTION' => array(8, 'var'), 'DATA DIRECTORY' => array(9, 'var'), 'DELAY_KEY_WRITE' => array(10, 'var'), @@ -101,7 +105,7 @@ class CreateStatement extends Statement * @var array */ public static $FUNC_OPTIONS = array( - 'COMMENT' => array(1, 'var'), + 'COMMENT' => array(1, 'var='), 'LANGUAGE SQL' => 2, 'DETERMINISTIC' => 3, 'NOT DETERMINISTIC' => 3, @@ -257,9 +261,9 @@ class CreateStatement extends Statement $parser, $list, array( - 'skipColumn' => true, - 'noBrackets' => true, 'noAlias' => true, + 'noBrackets' => true, + 'skipColumn' => true, ) ); ++$list->idx; // Skipping field. @@ -304,11 +308,6 @@ class CreateStatement extends Statement for (; $list->idx < $list->count; ++$list->idx) { $token = $list->tokens[$list->idx]; $this->body[] = $token; - if (($token->type === Token::TYPE_KEYWORD) - && ($token->value === 'END') - ) { - break; - } } } else if ($this->options->has('VIEW')) { $token = $list->getNext(); // Skipping whitespaces and comments. @@ -342,13 +341,13 @@ class CreateStatement extends Statement ++$list->idx; // Skipping `ON`. // Parsing the name of the table. - $this->fields = FieldFragment::parse( + $this->table = FieldFragment::parse( $parser, $list, array( - 'skipColumn' => true, 'noAlias' => true, 'noBrackets' => true, + 'skipColumn' => true, ) ); ++$list->idx; @@ -359,11 +358,6 @@ class CreateStatement extends Statement for (; $list->idx < $list->count; ++$list->idx) { $token = $list->tokens[$list->idx]; $this->body[] = $token; - if (($token->type === Token::TYPE_KEYWORD) - && ($token->value === 'END') - ) { - break; - } } } } diff --git a/test/classes/plugin/export/PMA_ExportSql_test.php b/test/classes/plugin/export/PMA_ExportSql_test.php index 7d18c2ba6d..a646f8a3b8 100644 --- a/test/classes/plugin/export/PMA_ExportSql_test.php +++ b/test/classes/plugin/export/PMA_ExportSql_test.php @@ -2227,56 +2227,6 @@ class PMA_ExportSql_Test extends PHPUnit_Framework_TestCase } /** - * Test for ExportSql::substituteAlias - * - * @return void - */ - public function testSubstituteAlias() - { - $GLOBALS['sql_backquotes'] = '`'; - $sql_query = 'CREATE TABLE `data` ( xyz int )'; - $data = '`data`'; - $alias = 'sample'; - $pos = 19; - $offset = 0; - $result =$this->object->substituteAlias( - $sql_query, $data, $alias, $pos, $offset - ); - - $this->assertEquals( - 'CREATE TABLE `sample` ( xyz int )', - $result - ); - $this->assertEquals(2, $offset); - - $GLOBALS['sql_backquotes'] = false; - $result =$this->object->substituteAlias( - $sql_query, $data, $alias, $pos - ); - - $this->assertEquals( - 'CREATE TABLE sample ( xyz int )', - $result - ); - - $GLOBALS['sql_backquotes'] = '`'; - $sql_query = 'CREATE TABLE `sample` ( qwerty int )'; - $data = 'qwerty'; - $alias = 'f'; - $offset = 2; - $pos = 28 + 2; - $result =$this->object->substituteAlias( - $sql_query, $data, $alias, $pos, $offset - ); - - $this->assertEquals( - 'CREATE TABLE `sample` ( `f` int )', - $result - ); - $this->assertEquals(-1, $offset); - } - - /** * Test for ExportSql::replaceWithAlias * * @return void @@ -2304,7 +2254,6 @@ class PMA_ExportSql_Test extends PHPUnit_Framework_TestCase ) ); - $GLOBALS['sql_backquotes'] = '`'; $db = 'a'; $table = 'foo'; $sql_query = "CREATE TABLE IF NOT EXISTS foo (" @@ -2314,7 +2263,7 @@ class PMA_ExportSql_Test extends PHPUnit_Framework_TestCase . "pqr varchar(10) COLLATE latin1_general_ci NOT NULL " . "COMMENT 'pqr'," . "CONSTRAINT fk_om_dept FOREIGN KEY (baz) " - . "REFERENCES dept_master (baz)," + . "REFERENCES dept_master (baz)" . ") ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=" . "latin1_general_ci COMMENT='List' AUTO_INCREMENT=5"; $result = $this->object->replaceWithAliases( @@ -2322,60 +2271,48 @@ class PMA_ExportSql_Test extends PHPUnit_Framework_TestCase ); $this->assertEquals( - "CREATE TABLE IF NOT EXISTS `bartest` (" - . "`p` tinyint(3) unsigned NOT NULL COMMENT 'Primary Key'," - . "xyz varchar(255) COLLATE latin1_general_ci NOT NULL " - . "COMMENT 'xyz'," - . "`pphymdain` varchar(10) COLLATE latin1_general_ci NOT NULL " - . "COMMENT 'pqr'," - . "CONSTRAINT fk_om_dept FOREIGN KEY (`p`) " - . "REFERENCES dept_master (baz)," - . ") ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=" - . "latin1_general_ci COMMENT='List' AUTO_INCREMENT=5", + "CREATE TABLE IF NOT EXISTS `bartest` (" . + "`p` TINYINT (3) UNSIGNED NOT NULL COMMENT 'Primary Key', " . + "`xyz` VARCHAR (255) COLLATE latin1_general_ci NOT NULL COMMENT 'xyz', " . + "`pphymdain` VARCHAR (10) COLLATE latin1_general_ci NOT NULL COMMENT 'pqr', " . + "CONSTRAINT `fk_om_dept` FOREIGN KEY (`p`) REFERENCES `dept_master` (`baz`)" . + ") ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='List'", $result ); $result = $this->object->replaceWithAliases($sql_query, array(), '', ''); $this->assertEquals( - "CREATE TABLE IF NOT EXISTS foo (" - . "baz tinyint(3) unsigned NOT NULL COMMENT 'Primary Key'," - . "xyz varchar(255) COLLATE latin1_general_ci NOT NULL " - . "COMMENT 'xyz'," - . "pqr varchar(10) COLLATE latin1_general_ci NOT NULL " - . "COMMENT 'pqr'," - . "CONSTRAINT fk_om_dept FOREIGN KEY (baz) " - . "REFERENCES dept_master (baz)," - . ") ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=" - . "latin1_general_ci COMMENT='List' AUTO_INCREMENT=5", + "CREATE TABLE IF NOT EXISTS foo (" . + "`baz` TINYINT (3) UNSIGNED NOT NULL COMMENT 'Primary Key', " . + "`xyz` VARCHAR (255) COLLATE latin1_general_ci NOT NULL COMMENT 'xyz', " . + "`pqr` VARCHAR (10) COLLATE latin1_general_ci NOT NULL COMMENT 'pqr', " . + "CONSTRAINT `fk_om_dept` FOREIGN KEY (`baz`) REFERENCES `dept_master` (`baz`)" . + ") ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='List'", $result ); - $GLOBALS['sql_backquotes'] = null; $table = 'bar'; $sql_query = "CREATE TRIGGER `BEFORE_bar_INSERT` " - . "BEFORE INSERT ON `bar`\r\n" - . "FOR EACH ROW BEGIN\r\n" + . "BEFORE INSERT ON `bar` " + . "FOR EACH ROW BEGIN " . "SET @cnt=(SELECT count(*) FROM bar WHERE " . "xy=NEW.xy AND id=NEW.id AND " - . "abc=NEW.xy LIMIT 1);\r\n" - . "IF @cnt<>0 THEN\n" - . "SET NEW.xy=1;\r\n" - . "END IF;\nEND\n$$"; + . "abc=NEW.xy LIMIT 1); " + . "IF @cnt<>0 THEN " + . "SET NEW.xy=1; " + . "END IF; END"; $result = $this->object->replaceWithAliases( $sql_query, $aliases, $db, $table ); $this->assertEquals( - "CREATE TRIGGER `BEFORE_bar_INSERT` " - . "BEFORE INSERT ON f\n" - . "FOR EACH ROW BEGIN\n" - . "SET @cnt=(SELECT count(*) FROM f WHERE " - . "n=NEW.n AND id=NEW.id AND " - . "abc=NEW.n LIMIT 1);\n" - . "IF @cnt<>0 THEN\n" - . "SET NEW.n=1;\n" - . "END IF;\nEND\n$$", + "CREATE TRIGGER `BEFORE_bar_INSERT` BEFORE INSERT ON `f` FOR EACH ROW BEGIN " . + "SET @cnt=(SELECT count(*) FROM `f` WHERE `n`=NEW.`n` AND id=NEW.id AND abc=NEW.`n` LIMIT 1); " . + "IF @cnt<>0 THEN " . + "SET NEW.`n`=1; " . + "END IF; " . + "END", $result ); } |