Example #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);
  }
Example #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")));
  }
Example #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")));
  }
  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() + " ------");
  }
Example #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);
  }
Example #6
0
 @Override
 public boolean equals(Object obj) {
   if (this == obj) {
     return true;
   }
   if (obj == null) {
     return false;
   }
   if (getClass() != obj.getClass()) {
     return false;
   }
   SQLInSubQueryExpr other = (SQLInSubQueryExpr) obj;
   if (expr == null) {
     if (other.expr != null) {
       return false;
     }
   } else if (!expr.equals(other.expr)) {
     return false;
   }
   if (not != other.not) {
     return false;
   }
   if (subQuery == null) {
     if (other.subQuery != null) {
       return false;
     }
   } else if (!subQuery.equals(other.subQuery)) {
     return false;
   }
   return true;
 }
Example #7
0
 @Override
 public int hashCode() {
   final int prime = 31;
   int result = 1;
   result = prime * result + ((expr == null) ? 0 : expr.hashCode());
   result = prime * result + (not ? 1231 : 1237);
   result = prime * result + ((subQuery == null) ? 0 : subQuery.hashCode());
   return result;
 }
  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() + " ------");
  }
  public boolean visit(SQLSelect x) {
    setCurrentTable(x);

    if (x.getOrderBy() != null) {
      x.getOrderBy().setParent(x);
    }

    accept(x.getQuery());

    String originalTable = getCurrentTable();

    setCurrentTable((String) x.getQuery().getAttribute("table"));
    x.putAttribute("_old_local_", originalTable);

    accept(x.getOrderBy());

    setCurrentTable(originalTable);

    return false;
  }