Examples with MySQLLexer com.alibaba.cobar.parser.recognizer.mysql.lexer.MySQLLexer used on opensource projects

Example 6 with MySQLLexer

use of com.alibaba.cobar.parser.recognizer.mysql.lexer.MySQLLexer in project cobar by alibaba.

the class MySQLExprParserTest method testAssignment.

public void testAssignment() throws Exception {
    String sql = "a /*dd*/:=b:=c";
    MySQLExprParser parser = new MySQLExprParser(new MySQLLexer(sql));
    Expression expr = parser.expression();
    String output = output2MySQL(expr, sql);
    Assert.assertEquals("a := b := c", output);
    Assert.assertEquals(AssignmentExpression.class, expr.getClass());
    AssignmentExpression ass = (AssignmentExpression) expr;
    Assert.assertEquals(AssignmentExpression.class, ass.getRightOprand().getClass());
    ass = (AssignmentExpression) ass.getRightOprand();
    Assert.assertEquals("b", ((Identifier) ass.getLeftOprand()).getIdText());
    sql = "c=@var:=1";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("c = (@var := 1)", output);
    Assert.assertEquals(ComparisionEqualsExpression.class, expr.getClass());
    ass = (AssignmentExpression) ((BinaryOperatorExpression) expr).getRightOprand();
    UsrDefVarPrimary usr = (UsrDefVarPrimary) ass.getLeftOprand();
    Assert.assertEquals("@var", usr.getVarText());
    sql = "a:=b or c &&d :=0b1101 or b'01'&0xabc";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("a := b OR c AND d := b'1101' OR b'01' & x'abc'", output);
    Assert.assertEquals(AssignmentExpression.class, expr.getClass());
    ass = (AssignmentExpression) expr;
    Assert.assertEquals(AssignmentExpression.class, ass.getRightOprand().getClass());
    ass = (AssignmentExpression) ass.getRightOprand();
    Assert.assertEquals(LogicalOrExpression.class, ass.getLeftOprand().getClass());
    Assert.assertEquals(LogicalOrExpression.class, ass.getRightOprand().getClass());
    LogicalOrExpression lor = (LogicalOrExpression) ass.getLeftOprand();
    Assert.assertEquals(LogicalAndExpression.class, lor.getOperand(1).getClass());
    lor = (LogicalOrExpression) ass.getRightOprand();
    Assert.assertEquals(LiteralBitField.class, lor.getOperand(0).getClass());
    Assert.assertEquals(BitAndExpression.class, lor.getOperand(1).getClass());
    sql = "a:=((b or (c &&d)) :=((0b1101 or (b'01'&0xabc))))";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("a := b OR c AND d := b'1101' OR b'01' & x'abc'", output);
    Assert.assertEquals(AssignmentExpression.class, expr.getClass());
    ass = (AssignmentExpression) expr;
    Assert.assertEquals(AssignmentExpression.class, ass.getRightOprand().getClass());
    ass = (AssignmentExpression) ass.getRightOprand();
    Assert.assertEquals(LogicalOrExpression.class, ass.getLeftOprand().getClass());
    Assert.assertEquals(LogicalOrExpression.class, ass.getRightOprand().getClass());
    lor = (LogicalOrExpression) ass.getLeftOprand();
    Assert.assertEquals(LogicalAndExpression.class, lor.getOperand(1).getClass());
    lor = (LogicalOrExpression) ass.getRightOprand();
    Assert.assertEquals(LiteralBitField.class, lor.getOperand(0).getClass());
    Assert.assertEquals(BitAndExpression.class, lor.getOperand(1).getClass());
    sql = "(a:=b) or c &&(d :=0b1101 or b'01')&0xabc ^null";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("(a := b) OR c AND (d := b'1101' OR b'01') & x'abc' ^ NULL", output);
    Assert.assertEquals(LogicalOrExpression.class, expr.getClass());
    lor = (LogicalOrExpression) expr;
    Assert.assertEquals(AssignmentExpression.class, lor.getOperand(0).getClass());
    Assert.assertEquals(LogicalAndExpression.class, lor.getOperand(1).getClass());
    LogicalAndExpression land = (LogicalAndExpression) lor.getOperand(1);
    Assert.assertEquals(Identifier.class, land.getOperand(0).getClass());
    Assert.assertEquals(BitAndExpression.class, land.getOperand(1).getClass());
    BitAndExpression band = (BitAndExpression) land.getOperand(1);
    Assert.assertEquals(AssignmentExpression.class, band.getLeftOprand().getClass());
    Assert.assertEquals(BitXORExpression.class, band.getRightOprand().getClass());
    ass = (AssignmentExpression) band.getLeftOprand();
    Assert.assertEquals(LogicalOrExpression.class, ass.getRightOprand().getClass());
    BitXORExpression bxor = (BitXORExpression) band.getRightOprand();
    Assert.assertEquals(LiteralHexadecimal.class, bxor.getLeftOprand().getClass());
    Assert.assertEquals(LiteralNull.class, bxor.getRightOprand().getClass());
}

Example 7 with MySQLLexer

use of com.alibaba.cobar.parser.recognizer.mysql.lexer.MySQLLexer in project cobar by alibaba.

the class MySQLExprParserTest method testArithmetic.

public void testArithmetic() throws Exception {
    String sql = "? + @usrVar1 * c/@@version- e % -f diV g";
    MySQLExprParser parser = new MySQLExprParser(new MySQLLexer(sql));
    Expression expr = parser.expression();
    String output = output2MySQL(expr, sql);
    Assert.assertEquals("? + @usrVar1 * c / @@version - e % - f DIV g", output);
    ArithmeticSubtractExpression sub = (ArithmeticSubtractExpression) expr;
    ArithmeticAddExpression add = (ArithmeticAddExpression) sub.getLeftOprand();
    ArithmeticIntegerDivideExpression idiv = (ArithmeticIntegerDivideExpression) sub.getRightOprand();
    ArithmeticModExpression mod = (ArithmeticModExpression) idiv.getLeftOprand();
    ArithmeticDivideExpression div = (ArithmeticDivideExpression) add.getRightOprand();
    ArithmeticMultiplyExpression mt = (ArithmeticMultiplyExpression) div.getLeftOprand();
    MinusExpression mi = (MinusExpression) mod.getRightOprand();
    Assert.assertEquals("c", ((Identifier) mt.getRightOprand()).getIdText());
    Assert.assertEquals("f", ((Identifier) mi.getOperand()).getIdText());
    sql = "a+-b";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("a + - b", output);
    sql = "a+--b";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("a + - - b", output);
    sql = "a++b";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("a + b", output);
    sql = "a+++b";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("a + b", output);
    sql = "a++-b";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("a + - b", output);
    sql = "a + b mod (-((select id from t1 limit 1)- e) ) ";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("a + b % - ((SELECT id FROM t1 LIMIT 0, 1) - e)", output);
    add = (ArithmeticAddExpression) expr;
    mod = (ArithmeticModExpression) add.getRightOprand();
    mi = (MinusExpression) mod.getRightOprand();
    sub = (ArithmeticSubtractExpression) mi.getOperand();
    Assert.assertTrue(QueryExpression.class.isAssignableFrom(sub.getLeftOprand().getClass()));
    Assert.assertEquals("e", ((Identifier) sub.getRightOprand()).getIdText());
}

Example 8 with MySQLLexer

use of com.alibaba.cobar.parser.recognizer.mysql.lexer.MySQLLexer in project cobar by alibaba.

the class MySQLExprParserTest method testStartedFromIdentifier.

public void testStartedFromIdentifier() throws Exception {
    // QS_TODO
    String sql = "cast(CAST(1-2 AS UNSIGNED) AS SIGNED)";
    MySQLExprParser parser = new MySQLExprParser(new MySQLLexer(sql));
    Expression expr = parser.expression();
    String output = output2MySQL(expr, sql);
    Assert.assertEquals("CAST(CAST(1 - 2 AS UNSIGNED) AS SIGNED)", output);
    sql = "position('a' in \"abc\")";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("LOCATE('a', 'abc')", output);
    sql = "cast(CAST(1-2 AS UNSIGNED integer) AS SIGNED integer)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("CAST(CAST(1 - 2 AS UNSIGNED) AS SIGNED)", output);
    sql = "CAST(expr as char)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("CAST(expr AS CHAR)", output);
    sql = "CAST(6/4 AS DECIMAL(3,1))";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("CAST(6 / 4 AS DECIMAL(3, 1))", output);
    sql = "CAST(6/4 AS DECIMAL(3))";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("CAST(6 / 4 AS DECIMAL(3))", output);
    sql = "CAST(6/4 AS DECIMAL)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("CAST(6 / 4 AS DECIMAL)", output);
    sql = "CAST(now() as date)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("CAST(NOW() AS DATE)", output);
    sql = "CAST(expr as char(5))";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("CAST(expr AS CHAR(5))", output);
    sql = "SUBSTRING('abc',pos,len)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("SUBSTRING('abc', pos, len)", output);
    sql = "SUBSTRING('abc' FROM pos FOR len)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("SUBSTRING('abc', pos, len)", output);
    sql = "SUBSTRING(str,pos)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("SUBSTRING(str, pos)", output);
    sql = "SUBSTRING('abc',1,2)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("SUBSTRING('abc', 1, 2)", output);
    sql = "row(1,2,str)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("ROW(1, 2, str)", output);
    sql = "position(\"abc\" in '/*abc*/')";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("LOCATE('abc', '/*abc*/')", output);
    sql = "locate(localtime,b)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("LOCATE(NOW(), b)", output);
    sql = "locate(locate(a,b),`match`)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("LOCATE(LOCATE(a, b), `match`)", output);
    sql = "TRIM(LEADING 'x' FROM 'xxxbarxxx')";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("TRIM(LEADING 'x' FROM 'xxxbarxxx')", output);
    sql = "TRIM(BOTH 'x' FROM 'xxxbarxxx')";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("TRIM(BOTH 'x' FROM 'xxxbarxxx')", output);
    sql = "TRIM(TRAILING 'xyz' FROM 'barxxyz')";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("TRIM(TRAILING 'xyz' FROM 'barxxyz')", output);
    sql = "TRIM('  if   ')";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("TRIM('  if   ')", output);
    sql = "TRIM( 'x' FROM 'xxxbarxxx')";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("TRIM('x' FROM 'xxxbarxxx')", output);
    sql = "TRIM(both  FROM 'barxxyz')";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("TRIM(BOTH  FROM 'barxxyz')", output);
    sql = "TRIM(leading  FROM 'barxxyz')";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("TRIM(LEADING  FROM 'barxxyz')", output);
    sql = "TRIM(TRAILING  FROM 'barxxyz')";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("TRIM(TRAILING  FROM 'barxxyz')", output);
    sql = "avg(DISTINCT results)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("AVG(DISTINCT results)", output);
    sql = "avg(results)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("AVG(results)", output);
    sql = "max(DISTINCT results)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("MAX(DISTINCT results)", output);
    sql = "max(results)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("MAX(results)", output);
    sql = "min(DISTINCT results)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("MIN(DISTINCT results)", output);
    sql = "min(results)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("MIN(results)", output);
    sql = "sum(DISTINCT results)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("SUM(DISTINCT results)", output);
    sql = "sum(results)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("SUM(results)", output);
    sql = "count(DISTINCT expr1,expr2,expr3)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("COUNT(DISTINCT expr1, expr2, expr3)", output);
    sql = "count(*)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("COUNT(*)", output);
    sql = "GROUP_CONCAT(DISTINCT expr1,expr2,expr3 ORDER BY col_name1 DESC,col_name2 SEPARATOR ' ')";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("GROUP_CONCAT(DISTINCT expr1, expr2, expr3 ORDER BY col_name1 DESC, col_name2 SEPARATOR  )", output);
    sql = "GROUP_CONCAT(a||b,expr2,expr3 ORDER BY col_name1 asc,col_name2 SEPARATOR '@ ')";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("GROUP_CONCAT(a OR b, expr2, expr3 ORDER BY col_name1 ASC, col_name2 SEPARATOR @ )", output);
    sql = "GROUP_CONCAT(expr1 ORDER BY col_name1 asc,col_name2 SEPARATOR 'str_val ')";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("GROUP_CONCAT(expr1 ORDER BY col_name1 ASC, col_name2 SEPARATOR str_val )", output);
    sql = "GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC )";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("GROUP_CONCAT(DISTINCT test_score ORDER BY test_score DESC SEPARATOR ,)", output);
    sql = "GROUP_CONCAT(DISTINCT test_score ORDER BY test_score asc )";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("GROUP_CONCAT(DISTINCT test_score ORDER BY test_score ASC SEPARATOR ,)", output);
    sql = "GROUP_CONCAT(c1)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("GROUP_CONCAT(c1 SEPARATOR ,)", output);
    sql = "GROUP_CONCAT(c1 separator '')";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("GROUP_CONCAT(c1 SEPARATOR )", output);
    sql = "default";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("DEFAULT", output);
    sql = "default(col)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("DEFAULT(col)", output);
    sql = "database()";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("DATABASE()", output);
    sql = "if(1>2,a+b,a:=1)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("IF(1 > 2, a + b, a := 1)", output);
    sql = "insert('abc',1,2,'')";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("INSERT('abc', 1, 2, '')", output);
    sql = "left(\"hjkafag\",4)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("LEFT('hjkafag', 4)", output);
    sql = "repeat('ag',2.1e1)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("REPEAT('ag', 21)", output);
    sql = "replace('anjd',\"df\",'af')";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("REPLACE('anjd', 'df', 'af')", output);
    sql = "right(\"hjkafag\",4)";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("RIGHT('hjkafag', 4)", output);
    sql = "schema()";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("DATABASE()", output);
    sql = "utc_date()";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("UTC_DATE()", output);
    sql = "Utc_time()";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("UTC_TIME()", output);
    sql = "Utc_timestamp()";
    parser = new MySQLExprParser(new MySQLLexer(sql));
    expr = parser.expression();
    output = output2MySQL(expr, sql);
    Assert.assertEquals("UTC_TIMESTAMP()", output);
}

Example 9 with MySQLLexer

use of com.alibaba.cobar.parser.recognizer.mysql.lexer.MySQLLexer in project cobar by alibaba.

the class MySQLDMLInsertParserTest method testInsert.

public void testInsert() throws SQLSyntaxErrorException {
    String sql = "insErt HIGH_PRIORITY intO test.t1 seT t1.id1=?, id2 := '123'";
    MySQLLexer lexer = new MySQLLexer(sql);
    MySQLDMLInsertParser parser = new MySQLDMLInsertParser(lexer, new MySQLExprParser(lexer));
    DMLInsertStatement insert = parser.insert();
    parser.match(MySQLToken.EOF);
    String output = output2MySQL(insert, sql);
    Assert.assertNotNull(insert);
    Assert.assertEquals("INSERT HIGH_PRIORITY INTO test.t1 (t1.id1, id2) VALUES (?, '123')", output);
    sql = "insErt  IGNORE test.t1 seT t1.id1:=? oN dupLicatE key UPDATE ex.col1=?, col2=12";
    lexer = new MySQLLexer(sql);
    parser = new MySQLDMLInsertParser(lexer, new MySQLExprParser(lexer));
    insert = parser.insert();
    parser.match(MySQLToken.EOF);
    output = output2MySQL(insert, sql);
    Assert.assertEquals("INSERT IGNORE INTO test.t1 (t1.id1) VALUES (?) ON DUPLICATE KEY UPDATE ex.col1 = ?, col2 = 12", output);
    sql = "insErt t1 value (123,?) oN dupLicatE key UPDATE ex.col1=?";
    lexer = new MySQLLexer(sql);
    parser = new MySQLDMLInsertParser(lexer, new MySQLExprParser(lexer));
    insert = parser.insert();
    parser.match(MySQLToken.EOF);
    output = output2MySQL(insert, sql);
    Assert.assertEquals("INSERT INTO t1 VALUES (123, ?) ON DUPLICATE KEY UPDATE ex.col1 = ?", output);
    sql = "insErt LOW_PRIORITY t1 valueS (12e-2,1,2), (?),(default)";
    lexer = new MySQLLexer(sql);
    parser = new MySQLDMLInsertParser(lexer, new MySQLExprParser(lexer));
    insert = parser.insert();
    parser.match(MySQLToken.EOF);
    output = output2MySQL(insert, sql);
    Assert.assertEquals("INSERT LOW_PRIORITY INTO t1 VALUES (0.12, 1, 2), (?), (DEFAULT)", output);
    sql = "insErt LOW_PRIORITY t1 select id from t1";
    lexer = new MySQLLexer(sql);
    parser = new MySQLDMLInsertParser(lexer, new MySQLExprParser(lexer));
    insert = parser.insert();
    parser.match(MySQLToken.EOF);
    output = output2MySQL(insert, sql);
    Assert.assertEquals("INSERT LOW_PRIORITY INTO t1 SELECT id FROM t1", output);
    sql = "insErt delayed t1 select id from t1";
    lexer = new MySQLLexer(sql);
    parser = new MySQLDMLInsertParser(lexer, new MySQLExprParser(lexer));
    insert = parser.insert();
    parser.match(MySQLToken.EOF);
    output = output2MySQL(insert, sql);
    Assert.assertEquals("INSERT DELAYED INTO t1 SELECT id FROM t1", output);
    sql = "insErt LOW_PRIORITY t1 (select id from t1) oN dupLicatE key UPDATE ex.col1=?, col2=12";
    lexer = new MySQLLexer(sql);
    parser = new MySQLDMLInsertParser(lexer, new MySQLExprParser(lexer));
    insert = parser.insert();
    parser.match(MySQLToken.EOF);
    output = output2MySQL(insert, sql);
    Assert.assertEquals("INSERT LOW_PRIORITY INTO t1 SELECT id FROM t1 ON DUPLICATE KEY UPDATE ex.col1 = ?, col2 = 12", output);
    sql = "insErt LOW_PRIORITY t1 (t1.col1) valueS (123),('12''34')";
    lexer = new MySQLLexer(sql);
    parser = new MySQLDMLInsertParser(lexer, new MySQLExprParser(lexer));
    insert = parser.insert();
    parser.match(MySQLToken.EOF);
    output = output2MySQL(insert, sql);
    Assert.assertEquals("INSERT LOW_PRIORITY INTO t1 (t1.col1) VALUES (123), ('12\\'34')", output);
    sql = "insErt LOW_PRIORITY t1 (col1, t1.col2) VALUE (123,'123\\'4') oN dupLicatE key UPDATE ex.col1=?";
    lexer = new MySQLLexer(sql);
    parser = new MySQLDMLInsertParser(lexer, new MySQLExprParser(lexer));
    insert = parser.insert();
    parser.match(MySQLToken.EOF);
    output = output2MySQL(insert, sql);
    Assert.assertEquals("INSERT LOW_PRIORITY INTO t1 (col1, t1.col2) VALUES (123, '123\\'4') ON DUPLICATE KEY UPDATE ex.col1 = ?", output);
    sql = "insErt LOW_PRIORITY t1 (col1, t1.col2) select id from t3 oN dupLicatE key UPDATE ex.col1=?";
    lexer = new MySQLLexer(sql);
    parser = new MySQLDMLInsertParser(lexer, new MySQLExprParser(lexer));
    insert = parser.insert();
    parser.match(MySQLToken.EOF);
    output = output2MySQL(insert, sql);
    Assert.assertEquals("INSERT LOW_PRIORITY INTO t1 (col1, t1.col2) SELECT id FROM t3 ON DUPLICATE KEY UPDATE ex.col1 = ?", output);
    sql = "insErt LOW_PRIORITY IGNORE intO t1 (col1) ( select id from t3) ";
    lexer = new MySQLLexer(sql);
    parser = new MySQLDMLInsertParser(lexer, new MySQLExprParser(lexer));
    insert = parser.insert();
    parser.match(MySQLToken.EOF);
    output = output2MySQL(insert, sql);
    Assert.assertEquals("INSERT LOW_PRIORITY IGNORE INTO t1 (col1) SELECT id FROM t3", output);
}

Example 10 with MySQLLexer

use of com.alibaba.cobar.parser.recognizer.mysql.lexer.MySQLLexer in project cobar by alibaba.

the class MySQLDMLParserTest method testGroupBy.

public void testGroupBy() throws SQLSyntaxErrorException {
    String sql = "group by c1 asc, c2 desc  , c3 with rollup";
    MySQLLexer lexer = new MySQLLexer(sql);
    MySQLDMLParser parser = getDMLParser(lexer);
    GroupBy groupBy = parser.groupBy();
    String output = output2MySQL(groupBy, sql);
    ListUtil.isEquals(ListUtil.createList(new Pair<Expression, SortOrder>(new Identifier(null, "c1"), SortOrder.ASC), new Pair<Expression, SortOrder>(new Identifier(null, "c2"), SortOrder.DESC), new Pair<Expression, SortOrder>(new Identifier(null, "c3"), SortOrder.ASC)), groupBy.getOrderByList());
    Assert.assertEquals("GROUP BY c1, c2 DESC, c3 WITH ROLLUP", output);
    sql = "group by c1 asc, c2 desc  , c3 ";
    lexer = new MySQLLexer(sql);
    parser = getDMLParser(lexer);
    groupBy = parser.groupBy();
    output = output2MySQL(groupBy, sql);
    ListUtil.isEquals(ListUtil.createList(new Pair<Expression, SortOrder>(new Identifier(null, "c1"), SortOrder.ASC), new Pair<Expression, SortOrder>(new Identifier(null, "c2"), SortOrder.DESC), new Pair<Expression, SortOrder>(new Identifier(null, "c3"), SortOrder.ASC)), groupBy.getOrderByList());
    Assert.assertEquals("GROUP BY c1, c2 DESC, c3", output);
    sql = "group by c1   ";
    lexer = new MySQLLexer(sql);
    parser = getDMLParser(lexer);
    groupBy = parser.groupBy();
    output = output2MySQL(groupBy, sql);
    ListUtil.isEquals(ListUtil.createList(new Pair<Expression, SortOrder>(new Identifier(null, "c1"), SortOrder.ASC)), groupBy.getOrderByList());
    Assert.assertEquals("GROUP BY c1", output);
    sql = "group by c1 asc  ";
    lexer = new MySQLLexer(sql);
    parser = getDMLParser(lexer);
    groupBy = parser.groupBy();
    output = output2MySQL(groupBy, sql);
    ListUtil.isEquals(ListUtil.createList(new Pair<Expression, SortOrder>(new Identifier(null, "c1"), SortOrder.ASC)), groupBy.getOrderByList());
    Assert.assertEquals("GROUP BY c1", output);
    sql = "group by c1 desc  ";
    lexer = new MySQLLexer(sql);
    parser = getDMLParser(lexer);
    groupBy = parser.groupBy();
    output = output2MySQL(groupBy, sql);
    ListUtil.isEquals(ListUtil.createList(new Pair<Expression, SortOrder>(new Identifier(null, "c1"), SortOrder.DESC)), groupBy.getOrderByList());
    Assert.assertEquals("GROUP BY c1 DESC", output);
    sql = "group by c1 with rollup  ";
    lexer = new MySQLLexer(sql);
    parser = getDMLParser(lexer);
    groupBy = parser.groupBy();
    output = output2MySQL(groupBy, sql);
    ListUtil.isEquals(ListUtil.createList(new Pair<Expression, SortOrder>(new Identifier(null, "c1"), SortOrder.ASC)), groupBy.getOrderByList());
    Assert.assertEquals("GROUP BY c1 WITH ROLLUP", output);
}