예제 #1
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);
  }
예제 #2
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")));
  }
예제 #3
0
  public void test_0() throws Exception {
    String sql = //
        "ALTER TABLE ws_i18n_translate_item ADD CONSTRAINT ws_i18n_translate_item_pk " //
            + "PRIMARY KEY (id) USING INDEX ws_i18n_translate_item_pk";

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

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

    OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
    statemen.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(1, visitor.getTables().size());

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

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

    Assert.assertTrue(
        visitor.getColumns().contains(new TableStat.Column("ws_i18n_translate_item", "id")));
    // Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("pivot_table",
    // "YEAR")));
    // Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("pivot_table",
    // "order_mode")));
  }
예제 #4
0
  public void test_0() throws Exception {
    String sql = //
        "alter index PRODUCT_ADDITION_IND1 monitoring usage";

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

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

    OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
    statemen.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")));
  }
예제 #5
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);
  }
예제 #6
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")));
  }
예제 #7
0
  public void test_0() throws Exception {
    String sql = "SELECT `group`.* FROM `group` WHERE (group.group_id=159754)";

    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(2, visitor.getColumns().size());
    Assert.assertEquals(1, visitor.getConditions().size());
    Assert.assertEquals(0, visitor.getOrderByColumns().size());

    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("group")));
  }
예제 #8
0
  public void test_0() throws Exception {
    String sql = //
        "select (select decode(count(*),1,'YES','NO') FROM sys.obj$ o, sys.user$ u WHERE u.name = 'PERFSTAT' AND o.owner# = u.user# AND o.name = 'STATSPACK' AND o.type# = 11 AND o.status = 1) is_installed , (select  nvl(INTERVAL,'') from dba_jobs where what like 'statspack.snap%' and SCHEMA_USER='******' and rownum =1) freq from dual"; //

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

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

    OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
    statemen.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(3, visitor.getTables().size());

    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("sys.obj$")));
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("sys.user$")));
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("dba_jobs")));

    Assert.assertEquals(10, 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")));
  }
예제 #9
0
  public void test_0() throws Exception {
    String sql =
        "insert into \"OPS$ADMIN\".\"ORASTAT\" select /*+ rule */ :5 statid, 'C' type, :6 version, bitand(h.spare2,7) flags,       ot.name c1, null c2, null c3, c.name c4, u.name c5,       h.distcnt n1, h.density n2, h.spare1 n3, h.sample_size n4, h.null_cnt n5,       h.minimum n6, h.maximum n7, h.avgcln n8,       decode(h.cache_cnt,0,null,1) n9, hg.bucket n10, hg.endpoint n11,       null n12,       h.timestamp# d1,       h.lowval r1, h.hival r2, hg.epvalue ch1     from sys.user$ u,  sys.obj$ ot, sys.col$ c,   sys.hist_head$ h, histgrm$ hg     where       :3 is null and       u.name = :1 and ot.owner# = u.user# and       ot.name = :2 and ot.type# = 2 and        c.obj# = ot.obj# and       (:4 is null or c.name = :4) and       h.obj# = ot.obj# and h.intcol# = c.intcol# and       hg.obj#(+) = h.obj# and hg.intcol#(+) = h.intcol#     union all     select        :5 statid, 'C' type, :6 version, bitand(h.spare2,7) flags,       ot.name c1, op.subname c2, null c3, c.name c4, u.name c5,       h.distcnt n1, h.density n2, h.spare1 n3, h.sample_size n4, h.null_cnt n5,       h.minimum n6, h.maximum n7, h.avgcln n8,       decode(h.cache_cnt,0,null,1) n9, hg.bucket n10, hg.endpoint n11,       null n12,       h.timestamp# d1,       h.lowval r1, h.hival r2, hg.epvalue ch1     from sys.user$ u,  sys.obj$ ot, sys.col$ c,   sys.tabpart$ tp, sys.obj$ op,   sys.hist_head$ h, histgrm$ hg     where       u.name = :1 and ot.owner# = u.user# and       ot.name = :2 and ot.type# = 2 and        c.obj# = ot.obj# and       (:4 is null or c.name = :4) and       tp.bo# = ot.obj# and tp.obj# = op.obj# and       ((:3 is null and :vc_cascade_parts is not null)         or op.subname = :3) and       h.obj# = op.obj# and h.intcol# = c.intcol# and       hg.obj#(+) = h.obj# and hg.intcol#(+) = h.intcol#     union all     select        :5 statid, 'C' type, :6 version, bitand(h.spare2,7) flags,       op.name c1, op.subname c2, null c3, c.name c4, u.name c5,       h.distcnt n1, h.density n2, h.spare1 n3, h.sample_size n4, h.null_cnt n5,       h.minimum n6, h.maximum n7, h.avgcln n8,       decode(h.cache_cnt,0,null,1) n9, hg.bucket n10, hg.endpoint n11,       null n12,       h.timestamp# d1,       h.lowval r1, h.hival r2, hg.epvalue ch1     from sys.user$ u, sys.col$ c,   sys.tabcompart$ tp, sys.obj$ op,   sys.hist_head$ h, histgrm$ hg     where       u.name = :1 and op.owner# = u.user# and       op.name = :2 and op.type# = 19 and        ((:3 is null and :vc_cascade_parts is not null)         or op.subname = :3) and       tp.obj# = op.obj# and c.obj# = tp.bo# and       (:4 is null or c.name = :4) and       h.obj# = op.obj# and h.intcol# = c.intcol# and       hg.obj#(+) = h.obj# and hg.intcol#(+) = h.intcol#     union all     select        :5 statid, 'C' type, :6 version, bitand(h.spare2,7) flags,       op.name c1, op.subname c2, os.subname c3, c.name c4, u.name c5,       h.distcnt n1, h.density n2, h.spare1 n3, h.sample_size n4, h.null_cnt n5,       h.minimum n6, h.maximum n7, h.avgcln n8,       decode(h.cache_cnt,0,null,1) n9, hg.bucket n10, hg.endpoint n11,       null n12,       h.timestamp# d1,       h.lowval r1, h.hival r2, hg.epvalue ch1     from sys.user$ u, sys.col$ c,   sys.tabcompart$ tp, sys.obj$ op,   sys.tabsubpart$ ts, sys.obj$ os,   sys.hist_head$ h, histgrm$ hg     where       u.name = :1 and op.owner# = u.user# and       op.name = :2 and op.type# = 19 and        tp.obj# = op.obj# and c.obj# = tp.bo# and       (:4 is null or c.name = :4) and       ts.pobj# = tp.obj# and ts.obj# = os.obj# and       ((:3 is null and :vc_cascade_parts is not null)         or (op.subname = :3             and (:vc_cascade_parts is not null                    or os.subname is null))              or os.subname = :3) and                 h.obj# = os.obj# and h.intcol# = c.intcol# and       hg.obj#(+) = h.obj# and hg.intcol#(+) = h.intcol#     order by c5,c1,c2,c3,c4,n10";

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

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

    OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
    statemen.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());

    Assert.assertEquals(9, visitor.getTables().size());
    Assert.assertEquals(42, visitor.getColumns().size());
    //        Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("raises")));
    //        Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("employees")));
    //
    //        Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees",
    // "employee_id")));
    //        Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees",
    // "salary")));
    //        Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees",
    // "commission_pct")));
  }
예제 #10
0
  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));
  }
예제 #11
0
  void validateOracle(int id, String sql) throws Exception {
    sql = sql.trim();
    boolean sqlFlag = false;
    String lowerSql = sql.toLowerCase();
    if (lowerSql.startsWith("insert")
        || lowerSql.startsWith("select")
        || lowerSql.startsWith("upate")
        || lowerSql.startsWith("delete")
        || lowerSql.startsWith("create")
        || lowerSql.startsWith("drop")) {
      sqlFlag = true;
    }

    if (!sqlFlag) {
      update(id, sql, 2);
      return;
    }

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

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

    StringBuilder out = new StringBuilder();
    OracleParameterizedOutputVisitor visitor = new OracleParameterizedOutputVisitor(out);
    statemen.accept(visitor);

    update(id, out.toString(), 1);
    System.out.println(sql);
    System.out.println(out.toString());
  }
예제 #12
0
  public void exec_test(String resource) throws Exception {
    //        System.out.println(resource);
    InputStream is = null;

    is = Thread.currentThread().getContextClassLoader().getResourceAsStream(resource);
    Reader reader = new InputStreamReader(is, "UTF-8");
    String input = Utils.read(reader);
    JdbcUtils.close(reader);
    String[] items = input.split("---------------------------");
    String sql = items[0].trim();
    String expect = items[1].trim();

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

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

    SchemaStatVisitor visitor = new OdpsSchemaStatVisitor();
    stmt.accept(visitor);

    //        System.out.println(sql);
    //        System.out.println("Tables : " + visitor.getTables());
    //        System.out.println("fields : " + visitor.getColumns());
    //
    //        System.out.println();
    //        System.out.println("---------------------------");
    //        System.out.println(SQLUtils.toOdpsString(stmt));
  }
예제 #13
0
  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")));
  }
예제 #14
0
  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")));
  }
예제 #15
0
  public void test_0() throws Exception {
    String sql =
        "CREATE TABLE products ("
            + //
            "    product_no integer,"
            + //
            "    name text,"
            + //
            "    price numeric DEFAULT 9.99"
            + //
            ");";

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

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

    PGSchemaStatVisitor visitor = new PGSchemaStatVisitor();
    statemen.accept(visitor);

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

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

    Assert.assertTrue(
        visitor.getTables().get(new TableStat.Name("products")).getCreateCount() == 1);

    Assert.assertTrue(visitor.getColumns().size() == 3);
  }
예제 #16
0
  public void test_0() throws Exception {
    String sql =
        "WITH RECURSIVE included_parts(sub_part, part, quantity) AS ("
            + "    SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'"
            + "  UNION ALL"
            + "    SELECT p.sub_part, p.part, p.quantity"
            + "    FROM included_parts pr, parts p"
            + "    WHERE p.part = pr.sub_part"
            + "  )\n"
            + "SELECT sub_part, SUM(quantity) as total_quantity\n"
            + "FROM included_parts\n"
            + "GROUP BY sub_part";

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

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

    PGSchemaStatVisitor visitor = new PGSchemaStatVisitor();
    statemen.accept(visitor);

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

    Assert.assertEquals(5, visitor.getColumns().size());
    Assert.assertEquals(2, visitor.getTables().size());
  }
예제 #17
0
  public void test_0() throws Exception {
    String sql = "SELECT * FROM t_department  WHERE name IN ('0000','4444') ORDER BY name ASC";

    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.assertNotNull(queryBlock.getOrderBy());

    //        print(statementList);

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

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

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

    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t_department")));
  }
예제 #18
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);
  }
예제 #19
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);
  }
예제 #20
0
  public void test_0() throws Exception {
    String sql = //
        "alter table WRH$_SERVICE_WAIT_CLASS drop partition WRH$_SERVIC_1870432296_13478";

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

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

    OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
    statemen.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(1, visitor.getTables().size());

    Assert.assertTrue(
        visitor.getTables().containsKey(new TableStat.Name("WRH$_SERVICE_WAIT_CLASS")));

    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")));
  }
예제 #21
0
  public void test_0() throws Exception {
    String sql = "INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';";

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

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

    PGSchemaStatVisitor visitor = new PGSchemaStatVisitor();
    statemen.accept(visitor);

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

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

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

    Assert.assertTrue(
        visitor.getColumns().contains(new TableStat.Column("tmp_films", "date_prod")));
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("tmp_films", "*")));
  }
예제 #22
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")));
  }
예제 #23
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")));
  }
예제 #24
0
  public void test_0() throws Exception {
    String sql =
        "select id, name, beanId, \"algLable.id\", \"algLable.name\""
            + //
            ", \"algLable.schemaName\", \"algLable.tableName\", \"algLable.fieldName\""
            + //
            " from cnres.function_select_algmodule_rule() "
            + //
            " as a( id text,name text,beanId text, \"algLable.id\" text,\"algLable.name\" text"
            + //
            ", \"algLable.schemaName\" text,\"algLable.tableName\" text, \"algLable.fieldName\" text)";

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

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

    PGSchemaStatVisitor visitor = new PGSchemaStatVisitor();
    statemen.accept(visitor);

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

    Assert.assertEquals(8, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getTables().size());
  }
예제 #25
0
  public void test_0() throws Exception {
    String sql = "UPDATE `z_code` SET `key`='INTRANT_NOTALLOWED_CATEGORY_C'";

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

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

    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    statemen.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("z_code")));

    Assert.assertTrue(visitor.getColumns().contains(new Column("z_code", "key")));
  }
예제 #26
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);
    }
  }
예제 #27
0
  public void test_leftJoin() throws Exception {
    String sql =
        "SELECT host.id as id" //
            + ",   host.item_id as itemId" //
            + ",   host.node_id as nodeId" //
            + ",   host.node_type as nodeType" //
            + ",   host.begin_time as beginTime" //
            + ",   host.end_time as endTime" //
            + ",   host.gmt_create as gmtCreate" //
            + ",   host.gmt_modify as gmtModify" //
            + ",   host.reason as reason" //
            + ",   host.creator_id as creatorId" //
            + ",   host.modifier_id as modifierId" //
            + ",   user.name as creator" //
            + ",   user.name as modifier" //
            + ",   user.nick_name as nickName   " //
            + " FROM notice_close_node host left join sys_user user on user.id = host.modifier_id";

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

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

    {
      SQLSelectStatement selectStmt = (SQLSelectStatement) statemen;
      SQLSelect selectSQL = selectStmt.getSelect();
      TeradataSelectQueryBlock queryBlock = (TeradataSelectQueryBlock) selectSQL.getQuery();

      Assert.assertTrue(queryBlock.getFrom() instanceof SQLJoinTableSource);

      SQLJoinTableSource joinSource = (SQLJoinTableSource) queryBlock.getFrom();

      Assert.assertEquals(joinSource.getJoinType().toString(), "LEFT_OUTER_JOIN");
      Assert.assertEquals(joinSource.getLeft().toString(), "notice_close_node");
      Assert.assertEquals(joinSource.getRight().toString(), "sys_user");
      Assert.assertNotNull(joinSource.getCondition());
    }

    TeradataSchemaStatVisitor visitor = new TeradataSchemaStatVisitor();
    statemen.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(2, visitor.getTables().size());
    Assert.assertEquals(15, visitor.getColumns().size());
    Assert.assertEquals(2, visitor.getConditions().size());

    Assert.assertTrue(visitor.getColumns().contains(new Column("sys_user", "id")));
    Assert.assertTrue(
        visitor.getColumns().contains(new Column("notice_close_node", "modifier_id")));
    System.out.println(
        "------ end of " + new Object() {}.getClass().getEnclosingMethod().getName() + " ------");
  }
예제 #28
0
  protected String output(List<SQLStatement> stmtList) {
    StringBuilder out = new StringBuilder();
    HiveOutputVisitor visitor = new HiveOutputVisitor(out);

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

    return out.toString();
  }
예제 #29
0
  private String output(List<SQLStatement> stmtList) {
    StringBuilder out = new StringBuilder();

    for (SQLStatement stmt : stmtList) {
      stmt.accept(new MySqlOutputVisitor(out));
      out.append(";");
    }

    return out.toString();
  }
예제 #30
0
  public void test_0() throws Exception {
    String sql =
        "SELECT e.employee_id, e.salary, e.commission_pct"
            + //
            "   FROM employees e JOIN departments d"
            + //
            "   USING (department_id)"
            + //
            "   WHERE job_id = 'SA_REP'"
            + //
            "   AND location_id = 2500"
            + //
            "   ORDER BY e.employee_id"
            + //
            "   FOR UPDATE OF e.salary;";

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

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

    OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
    statemen.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(2, visitor.getTables().size());

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

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

    Assert.assertTrue(
        visitor.getColumns().contains(new TableStat.Column("departments", "department_id")));
    Assert.assertTrue(
        visitor.getColumns().contains(new TableStat.Column("employees", "department_id")));
    Assert.assertTrue(
        visitor.getColumns().contains(new TableStat.Column("employees", "employee_id")));
    Assert.assertTrue(
        visitor.getColumns().contains(new TableStat.Column("employees", "commission_pct")));
    Assert.assertTrue(
        visitor.getColumns().contains(new TableStat.Column("employees", "department_id")));
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("UNKNOWN", "job_id")));
    Assert.assertTrue(
        visitor.getColumns().contains(new TableStat.Column("UNKNOWN", "location_id")));
  }