Example #1
0
  public void test_0() throws Exception {
    String sql = "DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a1.id=a2.id;";

    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);

    Assert.assertEquals(
        "DELETE FROM a1, a2 USING (t1) AS a1" + "\n\tINNER JOIN t2 a2" + "\nWHERE a1.id = a2.id",
        SQLUtils.toMySqlString(stmt));
    Assert.assertEquals(
        "delete from a1, a2 using (t1) as a1" + "\n\tinner join t2 a2" + "\nwhere a1.id = a2.id",
        SQLUtils.toMySqlString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));

    Assert.assertEquals(1, statementList.size());

    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);

    //        System.out.println("Tables : " + visitor.getTables());
    //        System.out.println("fields : " + visitor.getColumns());
    //        System.out.println("coditions : " + visitor.getConditions());
    //        System.out.println("orderBy : " + visitor.getOrderByColumns());

    Assert.assertEquals(3, visitor.getTables().size());
    Assert.assertEquals(2, visitor.getColumns().size());
    Assert.assertEquals(2, visitor.getConditions().size());

    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("a1")));
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("a2")));
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t2")));

    Assert.assertTrue(visitor.getColumns().contains(new Column("a1", "id")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("a2", "id")));
  }
Example #2
0
  public void test_0() throws Exception {
    String sql = "GRANT CREATE TEMPORARY TABLE ON mydb.* TO 'someuser'@'somehost';";

    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    //        print(statementList);

    Assert.assertEquals(1, statementList.size());

    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);

    Assert.assertEquals(
        "GRANT CREATE TEMPORARY TABLE ON mydb.* TO 'someuser'@'somehost'", //
        SQLUtils.toMySqlString(stmt));
    Assert.assertEquals(
        "grant CREATE TEMPORARY TABLE on mydb.* to 'someuser'@'somehost'", //
        SQLUtils.toMySqlString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));

    //        System.out.println("Tables : " + visitor.getTables());
    //        System.out.println("fields : " + visitor.getColumns());
    //        System.out.println("coditions : " + visitor.getConditions());
    //        System.out.println("orderBy : " + visitor.getOrderByColumns());

    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(0, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getConditions().size());

    //        Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("City")));
    //        Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t2")));

    //        Assert.assertTrue(visitor.getColumns().contains(new Column("t2", "id")));
  }
Example #3
0
  private String getSqlStat(Integer id) {
    Map<String, Object> map = statManagerFacade.getSqlStatData(id);

    if (map == null) {
      return returnJSONResult(RESULT_CODE_ERROR, null);
    }

    String dbType = (String) map.get("DbType");
    String sql = (String) map.get("SQL");

    map.put("formattedSql", SQLUtils.format(sql, dbType));
    List<SQLStatement> statementList = SQLUtils.parseStatements(sql, dbType);

    if (!statementList.isEmpty()) {
      SQLStatement sqlStmt = statementList.get(0);
      SchemaStatVisitor visitor = SQLUtils.createSchemaStatVisitor(dbType);
      sqlStmt.accept(visitor);
      map.put("parsedTable", visitor.getTables().toString());
      map.put("parsedFields", visitor.getColumns().toString());
      map.put("parsedConditions", visitor.getConditions().toString());
      map.put("parsedRelationships", visitor.getRelationships().toString());
      map.put("parsedOrderbycolumns", visitor.getOrderByColumns().toString());
    }

    DateFormat format = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss:SSS");
    Date maxTimespanOccurTime = (Date) map.get("MaxTimespanOccurTime");
    if (maxTimespanOccurTime != null) {
      map.put("MaxTimespanOccurTime", format.format(maxTimespanOccurTime));
    }

    return returnJSONResult(map == null ? RESULT_CODE_ERROR : RESULT_CODE_SUCCESS, map);
  }
Example #4
0
  public void test_0() throws Exception {
    String sql =
        "UPDATE ROLLBACK_ON_FAIL TARGET_AFFECT_ROW 1 "
            + "`table_3966` AS `table_3966_11` SET `version` = `version` + 3, `gmt_modified` = NOW(), `optype` = ?, `feature` = ? "
            + "WHERE `sub_biz_order_id` = ? AND `biz_order_type` = ? AND `id` = ? AND `ti_id` = ? AND `optype` = ? AND `root_id` = ?";

    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    print(statementList);

    Assert.assertEquals(1, statementList.size());

    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);

    //        System.out.println("Tables : " + visitor.getTables());
    //        System.out.println("fields : " + visitor.getColumns());
    //        System.out.println("coditions : " + visitor.getConditions());
    //        System.out.println("orderBy : " + visitor.getOrderByColumns());

    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(9, visitor.getColumns().size());
    Assert.assertEquals(6, visitor.getConditions().size());

    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("table_3966")));

    Assert.assertTrue(visitor.getColumns().contains(new Column("table_3966", "sub_biz_order_id")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("table_3966", "feature")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("table_3966", "id")));

    {
      String output = SQLUtils.toMySqlString(stmt);
      Assert.assertEquals(
          "UPDATE ROLLBACK_ON_FAIL TARGET_AFFECT_ROW 1 `table_3966` `table_3966_11`"
              + "\nSET `version` = `version` + 3, `gmt_modified` = NOW(), `optype` = ?, `feature` = ?"
              + "\nWHERE `sub_biz_order_id` = ?"
              + "\n\tAND `biz_order_type` = ?"
              + "\n\tAND `id` = ?"
              + "\n\tAND `ti_id` = ?"
              + "\n\tAND `optype` = ?"
              + "\n\tAND `root_id` = ?", //
          output);
    }
    {
      String output = SQLUtils.toMySqlString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION);
      Assert.assertEquals(
          "update rollback_on_fail target_affect_row 1 `table_3966` `table_3966_11`"
              + "\nset `version` = `version` + 3, `gmt_modified` = NOW(), `optype` = ?, `feature` = ?"
              + "\nwhere `sub_biz_order_id` = ?"
              + "\n\tand `biz_order_type` = ?"
              + "\n\tand `id` = ?"
              + "\n\tand `ti_id` = ?"
              + "\n\tand `optype` = ?"
              + "\n\tand `root_id` = ?", //
          output);
    }
  }
  @Test
  public void test_one() throws Exception {
    String sql =
        "create table hp_db.g20_relationship_communication_daily(                   "
            + " a_iden_string    varchar,"
            + " b_iden_string    varchar,"
            + " counter          bigint,"
            + " durationtime     bigint"
            + ") "
            + "\nPARTITION BY HASH KEY(a_iden_string) PARTITION NUM 100"
            + "\nSUBPARTITION BY LIST(bdt bigint)"
            + "\nSUBPARTITION OPTIONS(available_Partition_Num=90)"
            + "\nTABLEGROUP g20_test_group;";

    MySqlStatementParser parser = new MySqlStatementParser(sql);
    SQLStatement stmt = parser.parseCreateTable();

    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);

    {
      String output = SQLUtils.toMySqlString(stmt);
      Assert.assertEquals(
          "CREATE TABLE hp_db.g20_relationship_communication_daily ("
              + "\n\ta_iden_string varchar, "
              + "\n\tb_iden_string varchar, "
              + "\n\tcounter bigint, "
              + "\n\tdurationtime bigint"
              + "\n)"
              + "\nPARTITION BY HASH KEY(a_iden_string) PARTITION NUM 100"
              + "\nSUBPARTITION BY LIST (bdt bigint)"
              + "\nSUBPARTITION OPTIONS (available_Partition_Num = 90)"
              + "\nTABLEGROUP g20_test_group",
          output);
    }
    {
      String output = SQLUtils.toMySqlString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION);
      Assert.assertEquals(
          "create table hp_db.g20_relationship_communication_daily ("
              + "\n\ta_iden_string varchar, "
              + "\n\tb_iden_string varchar, "
              + "\n\tcounter bigint, "
              + "\n\tdurationtime bigint"
              + "\n)"
              + "\npartition by hash key(a_iden_string) partition num 100"
              + "\nsubpartition by list (bdt bigint)"
              + "\nsubpartition options (available_Partition_Num = 90)"
              + "\ntablegroup g20_test_group",
          output);
    }
  }
Example #6
0
  public void test_0() throws Exception {
    String sql = "SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);";

    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);

    SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;

    SQLSelect select = selectStmt.getSelect();
    Assert.assertNotNull(select.getQuery());
    MySqlSelectQueryBlock queryBlock = (MySqlSelectQueryBlock) select.getQuery();
    Assert.assertNull(queryBlock.getOrderBy());

    //        print(statementList);

    Assert.assertEquals(1, statementList.size());

    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);

    Assert.assertEquals(2, visitor.getTables().size());
    Assert.assertEquals(3, visitor.getColumns().size());
    Assert.assertEquals(1, visitor.getConditions().size());
    Assert.assertEquals(0, visitor.getOrderByColumns().size());

    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t1")));
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t2")));

    String output = SQLUtils.toMySqlString(stmt);
    Assert.assertEquals(
        "SELECT *" //
            + "\nFROM t1" //
            + "\nWHERE column1 = (" //
            + "\n\tSELECT column1" //
            + "\n\tFROM t2" //
            + "\n\t)", //
        output);

    String output_lcase = SQLUtils.toMySqlString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION);
    Assert.assertEquals(
        "select *" //
            + "\nfrom t1" //
            + "\nwhere column1 = (" //
            + "\n\tselect column1" //
            + "\n\tfrom t2" //
            + "\n\t)", //
        output_lcase);
  }
  public void test() throws Exception {
    String sql =
        "INSERT [dbo].[SurveyAnswer]([CustomerId], [QuestionId], [OptionId], [CreateTime], [LastUpdateTime]) VALUES (@0, @1, @2, @3, @4)";

    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    parser.setParseCompleteValues(false);
    parser.setParseValuesSize(3);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);

    SQLServerInsertStatement insertStmt = (SQLServerInsertStatement) stmt;

    Assert.assertEquals(1, insertStmt.getValuesList().size());
    Assert.assertEquals(5, insertStmt.getValues().getValues().size());
    Assert.assertEquals(5, insertStmt.getColumns().size());
    Assert.assertEquals(1, statementList.size());

    SQLServerSchemaStatVisitor visitor = new SQLServerSchemaStatVisitor();
    stmt.accept(visitor);

    String formatSql =
        "INSERT INTO [dbo].[SurveyAnswer]" //
            + "\n\t([CustomerId], [QuestionId], [OptionId], [CreateTime], [LastUpdateTime])" //
            + "\nVALUES" //
            + "\n(@0, @1, @2, @3, @4)";
    Assert.assertEquals(formatSql, SQLUtils.toSQLServerString(insertStmt));
  }
  public void test_alter_first() throws Exception {
    String sql = "alter table test   add  dspcode  char(200)";
    MySqlStatementParser parser = new MySqlStatementParser(sql);
    SQLStatement stmt = parser.parseStatementList().get(0);
    parser.match(Token.EOF);

    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);

    //        System.out.println("Tables : " + visitor.getTables());
    //        System.out.println("fields : " + visitor.getColumns());
    //        System.out.println("coditions : " + visitor.getConditions());
    //        System.out.println("orderBy : " + visitor.getOrderByColumns());

    String output = SQLUtils.toMySqlString(stmt);
    Assert.assertEquals(
        "ALTER TABLE test"
            + //
            "\n\tADD COLUMN dspcode char(200)",
        output);

    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(1, visitor.getColumns().size());
    Assert.assertTrue(visitor.getColumns().contains(new Column("test", "dspcode")));
  }
  public void test_0() throws Exception {
    String sql = "CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';";

    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    print(statementList);

    Assert.assertEquals(1, statementList.size());

    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);

    String output = SQLUtils.toMySqlString(stmt);
    Assert.assertEquals(
        "CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass'", //
        output);

    System.out.println("Tables : " + visitor.getTables());
    System.out.println("fields : " + visitor.getColumns());
    System.out.println("coditions : " + visitor.getConditions());
    System.out.println("orderBy : " + visitor.getOrderByColumns());

    Assert.assertEquals(0, visitor.getTables().size());
    Assert.assertEquals(0, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getConditions().size());

    //        Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("City")));
    //        Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t2")));

    //        Assert.assertTrue(visitor.getColumns().contains(new Column("t2", "id")));
  }
Example #10
0
  public void test_0() {
    String sql = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";

    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    List<SQLStatement> stmtList = parser.parseStatementList();
    SQLStatement stmt = stmtList.get(0);

    Assert.assertEquals(1, stmtList.size());

    SQLServerSchemaStatVisitor visitor = new SQLServerSchemaStatVisitor();
    stmt.accept(visitor);

    System.out.println("Tables : " + visitor.getTables());
    System.out.println("fields : " + visitor.getColumns());
    System.out.println("coditions : " + visitor.getConditions());
    System.out.println("orderBy : " + visitor.getOrderByColumns());

    Assert.assertEquals(0, visitor.getTables().size());
    Assert.assertEquals(0, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getConditions().size());

    String text = SQLUtils.toSQLString(stmtList, JdbcUtils.SQL_SERVER);

    Assert.assertEquals("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED", text);
  }
Example #11
0
  public void test_0() throws Exception {
    String sql = "GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90";

    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    //        print(statementList);

    Assert.assertEquals(1, statementList.size());

    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);

    String output = SQLUtils.toMySqlString(stmt);
    Assert.assertEquals(
        "GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90", //
        output);

    //        System.out.println("Tables : " + visitor.getTables());
    //        System.out.println("fields : " + visitor.getColumns());
    //        System.out.println("coditions : " + visitor.getConditions());
    //        System.out.println("orderBy : " + visitor.getOrderByColumns());

    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(0, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getConditions().size());

    //        Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("City")));
    //        Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t2")));

    //        Assert.assertTrue(visitor.getColumns().contains(new Column("t2", "id")));
  }
Example #12
0
  public void test_0() throws Exception {
    String sql = "SELECT 1--1 from t where id = 1";

    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);

    SQLSelectStatement selectStmt = (SQLSelectStatement) stmt;

    SQLSelect select = selectStmt.getSelect();
    Assert.assertNotNull(select.getQuery());
    MySqlSelectQueryBlock queryBlock = (MySqlSelectQueryBlock) select.getQuery();
    Assert.assertNull(queryBlock.getOrderBy());

    //        print(statementList);

    Assert.assertEquals(1, statementList.size());

    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);

    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(1, visitor.getColumns().size());
    Assert.assertEquals(1, visitor.getConditions().size());
    Assert.assertEquals(0, visitor.getOrderByColumns().size());

    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t")));

    String output = SQLUtils.toMySqlString(stmt);
    Assert.assertEquals(
        "SELECT 1 - -1" //
            + "\nFROM t" //
            + "\nWHERE id = 1", //
        output);
  }
Example #13
0
  public void test_0() throws Exception {
    String sql = "SELECT * FROM DSN81010.EMP;";

    DB2StatementParser parser = new DB2StatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    print(statementList);

    Assert.assertEquals(1, statementList.size());

    DB2SchemaStatVisitor visitor = new DB2SchemaStatVisitor();
    stmt.accept(visitor);

    System.out.println("Tables : " + visitor.getTables());
    System.out.println("fields : " + visitor.getColumns());
    System.out.println("coditions : " + visitor.getConditions());
    System.out.println("orderBy : " + visitor.getOrderByColumns());

    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(1, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getConditions().size());

    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("DSN81010.EMP")));

    // Assert.assertTrue(visitor.getColumns().contains(new Column("mytable", "last_name")));
    // Assert.assertTrue(visitor.getColumns().contains(new Column("mytable", "first_name")));
    // Assert.assertTrue(visitor.getColumns().contains(new Column("mytable", "full_name")));

    String output = SQLUtils.toSQLString(stmt, JdbcConstants.DB2);
    Assert.assertEquals(
        "SELECT *" //
            + "\nFROM DSN81010.EMP", //
        output);
  }
Example #14
0
  public void test_0() throws Exception {
    String sql = "GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';";

    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    print(statementList);

    Assert.assertEquals(1, statementList.size());

    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);

    String output = SQLUtils.toMySqlString(stmt);
    Assert.assertEquals(
        "GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost'", //
        output);

    System.out.println("Tables : " + visitor.getTables());
    System.out.println("fields : " + visitor.getColumns());
    System.out.println("coditions : " + visitor.getConditions());
    System.out.println("orderBy : " + visitor.getOrderByColumns());

    Assert.assertEquals(0, visitor.getTables().size());
    Assert.assertEquals(0, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getConditions().size());

    //        Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("City")));
    //        Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t2")));

    //        Assert.assertTrue(visitor.getColumns().contains(new Column("t2", "id")));
  }
Example #15
0
 public void testReplace_7() throws Exception {
   String sql = "ReplaCe LOW_PRIORITY t1 (t1.col1) valueS (123),('12''34')";
   MySqlStatementParser parser = new MySqlStatementParser(sql);
   SQLStatement stmt = parser.parseStatementList().get(0);
   parser.match(Token.EOF);
   String output = SQLUtils.toMySqlString(stmt);
   Assert.assertEquals("REPLACE LOW_PRIORITY INTO t1 (t1.col1)\nVALUES (123), ('12''34')", output);
 }
Example #16
0
 public void testReplace_2() throws Exception {
   String sql = "ReplaCe t1 value (123,?) ";
   MySqlStatementParser parser = new MySqlStatementParser(sql);
   SQLStatement stmt = parser.parseStatementList().get(0);
   parser.match(Token.EOF);
   String output = SQLUtils.toMySqlString(stmt);
   Assert.assertEquals("REPLACE INTO t1\nVALUES (123, ?)", output);
 }
Example #17
0
 public void testReplace_1() throws Exception {
   String sql = "ReplaCe   test.t1 seT t1.id1:=? ";
   MySqlStatementParser parser = new MySqlStatementParser(sql);
   SQLStatement stmt = parser.parseStatementList().get(0);
   parser.match(Token.EOF);
   String output = SQLUtils.toMySqlString(stmt);
   Assert.assertEquals("REPLACE INTO test.t1 (t1.id1)\nVALUES (?)", output);
 }
Example #18
0
 public void test_distribute_by() throws Exception {
   String sql =
       "select total_day_cnt * EXP(-datediff(to_date('20150819', 'yyyymmdd'), last_time, 'dd') / 60) from dual"; //
   Assert.assertEquals(
       "SELECT total_day_cnt * EXP(-datediff(TO_DATE('20150819', 'yyyymmdd'), last_time, 'dd') / 60)"
           + "\nFROM dual",
       SQLUtils.formatOdps(sql));
 }
Example #19
0
 public void testReplace_6() throws Exception {
   String sql = "ReplaCe LOW_PRIORITY t1 (select id from t1) ";
   MySqlStatementParser parser = new MySqlStatementParser(sql);
   SQLStatement stmt = parser.parseStatementList().get(0);
   parser.match(Token.EOF);
   String output = SQLUtils.toMySqlString(stmt);
   Assert.assertEquals("REPLACE LOW_PRIORITY INTO t1\n\tSELECT id\n\tFROM t1", output);
 }
Example #20
0
 public void testReplace_0() throws Exception {
   String sql = "ReplaCe LOW_PRIORITY intO test.t1 seT t1.id1:=?, id2='123'";
   MySqlStatementParser parser = new MySqlStatementParser(sql);
   SQLStatement stmt = parser.parseStatementList().get(0);
   parser.match(Token.EOF);
   String output = SQLUtils.toMySqlString(stmt);
   Assert.assertEquals(
       "REPLACE LOW_PRIORITY INTO test.t1 (t1.id1, id2)\nVALUES (?, '123')", output);
 }
Example #21
0
  public String evaluate(String sql, String dbType) {
    List<SQLStatement> statementList = SQLUtils.parseStatements(sql, dbType);
    SchemaStatVisitor visitor = SQLUtils.createSchemaStatVisitor(dbType);

    for (SQLStatement stmt : statementList) {
      stmt.accept(visitor);
    }

    StringBuffer buf = new StringBuffer();

    for (TableStat.Column column : visitor.getColumns()) {
      if (buf.length() != 0) {
        buf.append(',');
      }
      buf.append(column.toString());
    }

    return buf.toString();
  }
 public void test_alter_first() throws Exception {
   String sql = "ALTER TABLE `rules` DROP `enabled`";
   MySqlStatementParser parser = new MySqlStatementParser(sql);
   SQLStatement stmt = parser.parseStatementList().get(0);
   parser.match(Token.EOF);
   String output = SQLUtils.toMySqlString(stmt);
   Assert.assertEquals(
       "ALTER TABLE `rules`" //
           + "\n\tDROP COLUMN `enabled`",
       output);
 }
 public void test_alter_first() throws Exception {
   String sql = "alter table tabelname alter column operateVersion set default 0";
   MySqlStatementParser parser = new MySqlStatementParser(sql);
   SQLStatement stmt = parser.parseStatementList().get(0);
   parser.match(Token.EOF);
   String output = SQLUtils.toMySqlString(stmt);
   Assert.assertEquals(
       "ALTER TABLE tabelname"
           + //
           "\n\tALTER COLUMN operateVersion SET DEFAULT 0",
       output);
 }
Example #24
0
 public void test_0() throws Exception {
   String text = "CREATE TABLE customer (a INT, b CHAR (20), INDEX (a));";
   Assert.assertEquals(
       "CREATE TABLE customer (\n"
           + //
           "\ta INT, \n"
           + //
           "\tb CHAR(20), \n"
           + //
           "\tINDEX(a)\n"
           + //
           ");",
       SQLUtils.format(text, JdbcUtils.MYSQL));
 }
  public void test_0() throws Exception {
    String sql = //
        "CREATE TRIGGER \"AO_4AEACD_WEBHOOK_D367380484\" " //
            + "BEFORE INSERT" //
            + "    ON \"AO_4AEACD_WEBHOOK_DAO\"   FOR EACH ROW " //
            + "BEGIN" //
            + "    SELECT \"AO_4AEACD_WEBHOOK_DAO_ID_SEQ\".NEXTVAL INTO :NEW.\"ID\" FROM DUAL;" //
            + "END;";

    OracleStatementParser parser = new OracleStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    print(statementList);

    Assert.assertEquals(1, statementList.size());

    Assert.assertEquals(
        "CREATE TRIGGER \"AO_4AEACD_WEBHOOK_D367380484\"" //
            + "\n\tBEFORE INSERT" //
            + "\n\tON \"AO_4AEACD_WEBHOOK_DAO\"" //
            + "\n\tFOR EACH ROW" //
            + "\nBEGIN" //
            + "\n\tSELECT \"AO_4AEACD_WEBHOOK_DAO_ID_SEQ\".NEXTVAL" //
            + "\n\tINTO :NEW.\"ID\"" //
            + "\n\tFROM DUAL;"
            + "\nEND", //
        SQLUtils.toSQLString(stmt, JdbcConstants.ORACLE));

    OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
    stmt.accept(visitor);

    System.out.println("Tables : " + visitor.getTables());
    System.out.println("fields : " + visitor.getColumns());
    System.out.println("coditions : " + visitor.getConditions());
    System.out.println("relationships : " + visitor.getRelationships());
    System.out.println("orderBy : " + visitor.getOrderByColumns());

    Assert.assertEquals(0, visitor.getTables().size());

    // Assert.assertTrue(visitor.getTables().containsKey(new
    // TableStat.Name("cdc.en_complaint_ipr_stat_fdt0")));

    Assert.assertEquals(0, visitor.getColumns().size());

    // Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("pivot_table", "*")));
    // Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("pivot_table",
    // "YEAR")));
    // Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("pivot_table",
    // "order_mode")));
  }
  public void test_0() throws Exception {
    String sql =
        "CREATE TABLE dbo.customfieldoption (" //
            + "ID NUMERIC NOT NULL, CUSTOMFIELD NUMERIC, CUSTOMFIELDCONFIG NUMERIC, " //
            + "PARENTOPTIONID NUMERIC, SEQUENCE NUMERIC, customvalue NVARCHAR(255), " //
            + "optiontype NVARCHAR(60), disabled NVARCHAR(60), " //
            + "CONSTRAINT PK_customfieldoption PRIMARY KEY (ID))";

    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLCreateTableStatement stmt = (SQLCreateTableStatement) statementList.get(0);

    Assert.assertEquals(1, statementList.size());

    String output = SQLUtils.toSQLString(stmt, JdbcConstants.SQL_SERVER);
    Assert.assertEquals(
        "CREATE TABLE dbo.customfieldoption (" //
            + "\n\tID NUMERIC NOT NULL," //
            + "\n\tCUSTOMFIELD NUMERIC," //
            + "\n\tCUSTOMFIELDCONFIG NUMERIC," //
            + "\n\tPARENTOPTIONID NUMERIC," //
            + "\n\tSEQUENCE NUMERIC," //
            + "\n\tcustomvalue NVARCHAR(255)," //
            + "\n\toptiontype NVARCHAR(60)," //
            + "\n\tdisabled NVARCHAR(60)," //
            + "\n\tCONSTRAINT PK_customfieldoption PRIMARY KEY (ID)" //
            + "\n)",
        output);

    SQLServerSchemaStatVisitor visitor = new SQLServerSchemaStatVisitor();
    stmt.accept(visitor);

    System.out.println("Tables : " + visitor.getTables());
    System.out.println("fields : " + visitor.getColumns());
    System.out.println("coditions : " + visitor.getConditions());
    System.out.println("orderBy : " + visitor.getOrderByColumns());

    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(9, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getConditions().size());

    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("dbo.customfieldoption")));

    Assert.assertTrue(visitor.getColumns().contains(new Column("dbo.customfieldoption", "ID")));
    Assert.assertTrue(
        visitor.getColumns().contains(new Column("dbo.customfieldoption", "CUSTOMFIELD")));
    Assert.assertTrue(
        visitor.getColumns().contains(new Column("dbo.customfieldoption", "CUSTOMFIELDCONFIG")));
  }
Example #27
0
  public static Object eval(
      String dbType, SQLObject sqlObject, List<Object> parameters, boolean throwError) {
    SQLEvalVisitor visitor = createEvalVisitor(dbType);
    visitor.setParameters(parameters);
    sqlObject.accept(visitor);

    Object value = getValue(sqlObject);
    if (value == null) {
      if (throwError && !sqlObject.getAttributes().containsKey(EVAL_VALUE)) {
        throw new DruidRuntimeException("eval error : " + SQLUtils.toSQLString(sqlObject, dbType));
      }
    }

    return value;
  }
  @Test
  public void test_two() throws Exception {
    String sql =
        "CREATE TABLE `t_cpi_driskconfig_bak` ("
            + "`Sequence` bigint(20) NOT NULL AUTO_INCREMENT,"
            + "`comcode` binary(20) NOT NULL,"
            + "`riskcode` varchar(10) binary NOT NULL,"
            + "`configcodehead` varchar(30) binary NOT NULL,"
            + "`configcodebody` varchar(100) binary,"
            + "`configvalue` varchar(200) binary ,"
            + "`inputdate` datetime NOT NULL,"
            + "`validstatus` char(1) NOT NULL,"
            + "`remark` varchar(3000),"
            + "`flag` varchar(10) ,"
            + "PRIMARY KEY (`Sequence`)"
            + ") ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8;";

    MySqlStatementParser parser = new MySqlStatementParser(sql);
    SQLStatement stmt = parser.parseCreateTable();

    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);

    //        System.out.println("Tables : " + visitor.getTables());
    //        System.out.println("fields : " + visitor.getColumns());
    //        System.out.println("coditions : " + visitor.getConditions());

    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(10, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getConditions().size());

    String output = SQLUtils.toMySqlString(stmt);
    Assert.assertEquals(
        "CREATE TABLE `t_cpi_driskconfig_bak` ("
            + "\n\t`Sequence` bigint(20) NOT NULL AUTO_INCREMENT, "
            + "\n\t`comcode` binary(20) NOT NULL, "
            + "\n\t`riskcode` varchar(10) BINARY  NOT NULL, "
            + "\n\t`configcodehead` varchar(30) BINARY  NOT NULL, "
            + "\n\t`configcodebody` varchar(100) BINARY , "
            + "\n\t`configvalue` varchar(200) BINARY , "
            + "\n\t`inputdate` datetime NOT NULL, "
            + "\n\t`validstatus` char(1) NOT NULL, "
            + "\n\t`remark` varchar(3000), "
            + "\n\t`flag` varchar(10), "
            + "\n\tPRIMARY KEY (`Sequence`)"
            + "\n) ENGINE = InnoDB AUTO_INCREMENT = 49 CHARSET = utf8",
        output);
  }
  public void test_0() throws Exception {
    String sql =
        "CREATE TABLE t1 ("
            + //
            "c1 INT STORAGE DISK,"
            + //
            "c2 INT STORAGE MEMORY "
            + //
            ") TABLESPACE ts_1 ENGINE NDB;";

    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    print(statementList);

    Assert.assertEquals(1, statementList.size());

    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);

    System.out.println("Tables : " + visitor.getTables());
    System.out.println("fields : " + visitor.getColumns());
    System.out.println("coditions : " + visitor.getConditions());
    System.out.println("orderBy : " + visitor.getOrderByColumns());

    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(2, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getConditions().size());

    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t1")));

    String output = SQLUtils.toMySqlString(stmt);
    Assert.assertEquals(
        "CREATE TABLE t1 ("
            + //
            "\n\tc1 INT STORAGE DISK, "
            + //
            "\n\tc2 INT STORAGE MEMORY"
            + //
            "\n) TABLESPACE ts_1 ENGINE = NDB",
        output);
  }
Example #30
0
  public void test_3() {
    String sql = "SET @state = N'Oregon'";

    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    List<SQLStatement> stmtList = parser.parseStatementList();
    SQLStatement stmt = stmtList.get(0);

    Assert.assertEquals(1, stmtList.size());

    SQLServerSchemaStatVisitor visitor = new SQLServerSchemaStatVisitor();
    stmt.accept(visitor);

    Assert.assertEquals(0, visitor.getTables().size());
    Assert.assertEquals(0, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getConditions().size());

    String text = SQLUtils.toSQLString(stmtList, JdbcUtils.SQL_SERVER);

    Assert.assertEquals("SET @state = N'Oregon'", text);
  }