Ejemplo n.º 1
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);
  }
Ejemplo n.º 2
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")));
  }
Ejemplo n.º 3
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")));
  }
Ejemplo n.º 4
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() + " ------");
  }
Ejemplo n.º 5
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);
  }
Ejemplo n.º 6
0
  public void route(
      SystemConfig sysConfig,
      SchemaConfig schema,
      int sqlType,
      String realSQL,
      String charset,
      ServerConnection sc,
      LayerCachePool cachePool) {
    int rs = ServerParse.parse(realSQL);
    this.sqltype = rs & 0xff;
    this.sysConfig = sysConfig;
    this.schema = schema;
    this.charset = charset;
    this.sc = sc;
    this.cachePool = cachePool;
    try {
      //  RouteStrategy routes=RouteStrategyFactory.getRouteStrategy();
      // rrs =RouteStrategyFactory.getRouteStrategy().route(sysConfig, schema, sqlType2,
      // realSQL,charset, sc, cachePool);
      MySqlStatementParser parser = new MySqlStatementParser(realSQL);
      SQLStatement statement = parser.parseStatement();
      if (statement instanceof SQLSelectStatement) {
        SQLSelectStatement st = (SQLSelectStatement) statement;
        SQLSelectQuery sqlSelectQuery = st.getSelect().getQuery();
        if (sqlSelectQuery instanceof MySqlSelectQueryBlock) {
          MySqlSelectQueryBlock mysqlSelectQuery =
              (MySqlSelectQueryBlock) st.getSelect().getQuery();
          joinParser = new JoinParser(mysqlSelectQuery, realSQL);
          joinParser.parser();
        }
      }
      /*
       if (routes instanceof DruidMysqlRouteStrategy) {
        SQLSelectStatement st=((DruidMysqlRouteStrategy) routes).getSQLStatement();
        SQLSelectQuery sqlSelectQuery =st.getSelect().getQuery();
      if(sqlSelectQuery instanceof MySqlSelectQueryBlock) {
      	MySqlSelectQueryBlock mysqlSelectQuery = (MySqlSelectQueryBlock)st.getSelect().getQuery();
      	joinParser=new JoinParser(mysqlSelectQuery,realSQL);
      	joinParser.parser();
      }
       }
       */
    } catch (Exception e) {

    }
  }
Ejemplo n.º 7
0
  public void test_crossJoin() throws Exception {
    String sql = "SELECT * FROM t1 CROSS JOIN t2 ON t1.id = t2.id;";

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

    Assert.assertEquals(1, statementList.size());
    {
      SQLSelectStatement selectStmt = (SQLSelectStatement) statement;
      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(), "CROSS_JOIN");
      Assert.assertEquals(joinSource.getLeft().toString(), "t1");
      Assert.assertEquals(joinSource.getRight().toString(), "t2");
      Assert.assertNotNull(joinSource.getCondition());
    }

    TeradataSchemaStatVisitor visitor = new TeradataSchemaStatVisitor();
    statement.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());
    System.out.println(
        "------ end of " + new Object() {}.getClass().getEnclosingMethod().getName() + " ------");
  }