Search in sources :

Example 91 with SchemaStatVisitor

use of com.alibaba.druid.sql.visitor.SchemaStatVisitor in project druid by alibaba.

the class PGSelectTest50 method test_0.

public void test_0() throws Exception {
    String sql = "delete from itsm_system_role_menu where menu_id in(\n" + "with RECURSIVE menuTemp(menu_id,parent_id)\n" + "as (\n" + "select menu_id ,parent_id from itsm_system_menu where menu_id in\n" + "(\n" + "'M00006'\n" + ")\n" + "union all\n" + "select a.menu_id ,a.parent_id from itsm_system_menu as a INNER JOIN menuTemp b on a.parent_id=b.menu_id\n" + ")\n" + "select menu_id from menuTemp);";
    List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, dbType);
    SQLStatement stmt = stmtList.get(0);
    assertEquals("DELETE FROM itsm_system_role_menu\n" + "WHERE menu_id IN (\n" + "\t\tWITH RECURSIVE menuTemp (menu_id, parent_id) AS (\n" + "\t\t\t\tSELECT menu_id, parent_id\n" + "\t\t\t\tFROM itsm_system_menu\n" + "\t\t\t\tWHERE menu_id IN ('M00006')\n" + "\t\t\t\tUNION ALL\n" + "\t\t\t\tSELECT a.menu_id, a.parent_id\n" + "\t\t\t\tFROM itsm_system_menu a\n" + "\t\t\t\t\tINNER JOIN menuTemp b ON a.parent_id = b.menu_id\n" + "\t\t\t)\n" + "\t\tSELECT menu_id\n" + "\t\tFROM menuTemp\n" + "\t);", SQLUtils.toPGString(stmt));
    assertEquals("delete from itsm_system_role_menu\n" + "where menu_id in (\n" + "\t\twith recursive menuTemp (menu_id, parent_id) as (\n" + "\t\t\t\tselect menu_id, parent_id\n" + "\t\t\t\tfrom itsm_system_menu\n" + "\t\t\t\twhere menu_id in ('M00006')\n" + "\t\t\t\tunion all\n" + "\t\t\t\tselect a.menu_id, a.parent_id\n" + "\t\t\t\tfrom itsm_system_menu a\n" + "\t\t\t\t\tinner join menuTemp b on a.parent_id = b.menu_id\n" + "\t\t\t)\n" + "\t\tselect menu_id\n" + "\t\tfrom menuTemp\n" + "\t);", SQLUtils.toPGString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
    assertEquals(1, stmtList.size());
    SchemaStatVisitor visitor = SQLUtils.createSchemaStatVisitor(dbType);
    stmt.accept(visitor);
    System.out.println("Tables : " + visitor.getTables());
    System.out.println("fields : " + visitor.getColumns());
    // System.out.println("coditions : " + visitor.getConditions());
    assertEquals(3, visitor.getColumns().size());
    assertEquals(2, visitor.getTables().size());
    assertTrue(visitor.containsColumn("itsm_system_role_menu", "menu_id"));
    assertTrue(visitor.containsColumn("itsm_system_menu", "menu_id"));
    assertTrue(visitor.containsColumn("itsm_system_menu", "parent_id"));
}
Also used : SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) SchemaStatVisitor(com.alibaba.druid.sql.visitor.SchemaStatVisitor)

Example 92 with SchemaStatVisitor

use of com.alibaba.druid.sql.visitor.SchemaStatVisitor in project druid by alibaba.

the class PGSelectTest54 method test_0.

public void test_0() throws Exception {
    String sql = "select gid, kind, mapid, poi_id, telephone, admincode, x, y, zipcode, name, address, street, number,geom from public.v_poi order by public.v_poi.geom <-> st_point(?,?) limit 1";
    List<SQLStatement> stmtList = SQLUtils.parseStatements(sql, dbType);
    SQLStatement stmt = stmtList.get(0);
    assertEquals("SELECT gid, kind, mapid, poi_id, telephone\n" + "\t, admincode, x, y, zipcode, name\n" + "\t, address, street, number, geom\n" + "FROM public.v_poi\n" + "ORDER BY public.v_poi.geom <-> st_point(?, ?)\n" + "LIMIT 1", SQLUtils.toPGString(stmt));
    assertEquals("select gid, kind, mapid, poi_id, telephone\n" + "\t, admincode, x, y, zipcode, name\n" + "\t, address, street, number, geom\n" + "from public.v_poi\n" + "order by public.v_poi.geom <-> st_point(?, ?)\n" + "limit 1", SQLUtils.toPGString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
    assertEquals(1, stmtList.size());
    SchemaStatVisitor visitor = SQLUtils.createSchemaStatVisitor(dbType);
    stmt.accept(visitor);
    System.out.println("Tables : " + visitor.getTables());
    System.out.println("fields : " + visitor.getColumns());
    // System.out.println("coditions : " + visitor.getConditions());
    assertEquals(14, visitor.getColumns().size());
    assertEquals(1, visitor.getTables().size());
}
Also used : SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) SchemaStatVisitor(com.alibaba.druid.sql.visitor.SchemaStatVisitor)

Example 93 with SchemaStatVisitor

use of com.alibaba.druid.sql.visitor.SchemaStatVisitor in project druid by alibaba.

the class SchemaStatTest20_issue3993 method test_schemaStat.

public void test_schemaStat() throws Exception {
    SchemaRepository repository = new SchemaRepository(JdbcConstants.MYSQL);
    String sql = "select * from tableA where COALESCE(service_code,'/') <> '0821';";
    SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, JdbcConstants.MYSQL);
    SQLStatement stmt = parser.parseStatementList().get(0);
    SchemaStatVisitor statVisitor = SQLUtils.createSchemaStatVisitor(repository);
    stmt.accept(statVisitor);
    System.out.println("Tables : " + statVisitor.getTables());
    System.out.println("columns : " + statVisitor.getColumns());
    // System.out.println(statVisitor.getGroupByColumns()); // group by
    // group by
    System.out.println("relationships : " + statVisitor.getRelationships());
    System.out.println("conditions : " + statVisitor.getConditions());
    assertEquals(2, statVisitor.getColumns().size());
    assertEquals(1, statVisitor.getConditions().size());
    assertEquals(1, statVisitor.getFunctions().size());
}
Also used : SQLStatementParser(com.alibaba.druid.sql.parser.SQLStatementParser) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) SchemaStatVisitor(com.alibaba.druid.sql.visitor.SchemaStatVisitor) SchemaRepository(com.alibaba.druid.sql.repository.SchemaRepository)

Example 94 with SchemaStatVisitor

use of com.alibaba.druid.sql.visitor.SchemaStatVisitor in project druid by alibaba.

the class SchemaStatTest22_yiran method test_schemaStat.

public void test_schemaStat() throws Exception {
    SchemaRepository repository = new SchemaRepository(JdbcConstants.MYSQL);
    repository.console("CREATE TABLE DEPT (depno INT4, dname ENC_TEXT)");
    String sql = "SELECT * FROM ( SELECT dname FROM DEPT) X WHERE X.dname='cs';";
    // sql = "SELECT * FROM ( SELECT * FROM DEPT) X WHERE X.dname='cs'";
    SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, JdbcConstants.MYSQL);
    SQLStatement stmt = parser.parseStatementList().get(0);
    SchemaStatVisitor statVisitor = SQLUtils.createSchemaStatVisitor(repository);
    stmt.accept(statVisitor);
    SQLSelectQueryBlock q = ((SQLSelectStatement) stmt).getSelect().getFirstQueryBlock();
    SQLPropertyExpr whereLeft = (SQLPropertyExpr) ((SQLBinaryOpExpr) q.getWhere()).getLeft();
    SQLColumnDefinition resolvedColumn = whereLeft.getResolvedColumn();
    assertNotNull(resolvedColumn);
    System.out.println("Tables : " + statVisitor.getTables());
    System.out.println("columns : " + statVisitor.getColumns());
    // System.out.println(statVisitor.getGroupByColumns()); // group by
    // group by
    System.out.println("relationships : " + statVisitor.getRelationships());
    System.out.println("conditions : " + statVisitor.getConditions());
    assertEquals(1, statVisitor.getColumns().size());
    assertEquals(1, statVisitor.getConditions().size());
    assertEquals(0, statVisitor.getFunctions().size());
}
Also used : SQLStatementParser(com.alibaba.druid.sql.parser.SQLStatementParser) SQLSelectQueryBlock(com.alibaba.druid.sql.ast.statement.SQLSelectQueryBlock) SQLPropertyExpr(com.alibaba.druid.sql.ast.expr.SQLPropertyExpr) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) SQLColumnDefinition(com.alibaba.druid.sql.ast.statement.SQLColumnDefinition) SchemaStatVisitor(com.alibaba.druid.sql.visitor.SchemaStatVisitor) SchemaRepository(com.alibaba.druid.sql.repository.SchemaRepository)

Example 95 with SchemaStatVisitor

use of com.alibaba.druid.sql.visitor.SchemaStatVisitor in project druid by alibaba.

the class SchemaStatTest6 method test_schemaStat.

public void test_schemaStat() throws Exception {
    String sql = "select count(1), name from tg_rpc_user where id < 5 group by name order by id desc";
    DbType dbType = JdbcConstants.MYSQL;
    SQLStatementParser parser = SQLParserUtils.createSQLStatementParser(sql, dbType);
    SQLStatement stmt = parser.parseStatementList().get(0);
    SchemaStatVisitor statVisitor = SQLUtils.createSchemaStatVisitor(dbType);
    stmt.accept(statVisitor);
    System.out.println(statVisitor.getColumns());
    // group by
    System.out.println(statVisitor.getGroupByColumns());
    Assert.assertEquals(2, statVisitor.getColumns().size());
}
Also used : SQLStatementParser(com.alibaba.druid.sql.parser.SQLStatementParser) SQLStatement(com.alibaba.druid.sql.ast.SQLStatement) DbType(com.alibaba.druid.DbType) SchemaStatVisitor(com.alibaba.druid.sql.visitor.SchemaStatVisitor)

Aggregations

SchemaStatVisitor (com.alibaba.druid.sql.visitor.SchemaStatVisitor)437 SQLStatement (com.alibaba.druid.sql.ast.SQLStatement)434 TableStat (com.alibaba.druid.stat.TableStat)68 SQLStatementParser (com.alibaba.druid.sql.parser.SQLStatementParser)61 MySqlStatementParser (com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser)51 SQLUtils (com.alibaba.druid.sql.SQLUtils)35 DbType (com.alibaba.druid.DbType)21 SQLSelectStatement (com.alibaba.druid.sql.ast.statement.SQLSelectStatement)12 MySqlSchemaStatVisitor (com.alibaba.druid.sql.dialect.mysql.visitor.MySqlSchemaStatVisitor)12 Test (org.junit.Test)12 OracleStatementParser (com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser)11 SchemaRepository (com.alibaba.druid.sql.repository.SchemaRepository)11 Column (com.alibaba.druid.stat.TableStat.Column)10 InputStream (java.io.InputStream)9 InputStreamReader (java.io.InputStreamReader)9 Reader (java.io.Reader)9 OdpsStatementParser (com.alibaba.druid.sql.dialect.odps.parser.OdpsStatementParser)6 OdpsSchemaStatVisitor (com.alibaba.druid.sql.dialect.odps.visitor.OdpsSchemaStatVisitor)6 SQLColumnDefinition (com.alibaba.druid.sql.ast.statement.SQLColumnDefinition)5 MySqlCreateTableStatement (com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlCreateTableStatement)4