Examples with OracleSchemaStatVisitor com.alibaba.druid.sql.dialect.oracle.visitor.OracleSchemaStatVisitor used on opensource projects

Example 81 with OracleSchemaStatVisitor

use of com.alibaba.druid.sql.dialect.oracle.visitor.OracleSchemaStatVisitor in project druid by alibaba.

the class OracleOutputVisitorTest_dblink method test_0.

public void test_0() throws Exception {
    String sql = "SELECT salary from master@emp";
    OracleStatementParser parser = new OracleStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    Assert.assertEquals(1, statementList.size());
    OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
    stmt.accept(visitor);
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(true, visitor.containsTable("master@emp"));
    Assert.assertEquals(1, visitor.getColumns().size());
    Assert.assertEquals(true, visitor.getColumns().contains(new Column("master@emp", "salary")));
    StringBuilder buf = new StringBuilder();
    OracleOutputVisitor outputVisitor = new OracleOutputVisitor(buf);
    stmt.accept(outputVisitor);
    Assert.assertEquals("SELECT salary\nFROM master@emp;\n", buf.toString());
}

Example 82 with OracleSchemaStatVisitor

use of com.alibaba.druid.sql.dialect.oracle.visitor.OracleSchemaStatVisitor in project druid by alibaba.

the class OracleSelectTest34 method test_0.

public void test_0() throws Exception {
    //
    String sql = //
    "select t.logistics_no, t.event_date, t.country, t.province" + //
    "   , t.city,t.address, t.area_code,t.received_status  " + //
    "from wl_tracking t  " + //
    "where t.logistics_no in ( " + //
    "   select el.logistics_no " + //
    "   from escrow_logistics el" + //
    "   where rownum <= 20" + //
    "       and el.gmt_send between to_date ('2011-9-1', 'yyyy-mm-dd') " + //
    "           and to_date ('2011-11-30 23:59:59','yyyy-mm-dd hh24:mi:ss')" + //
    "       and el.received_status = 'received'" + //
    "       and el.goods_direction = 'send_goods'" + //
    "       and el.country = 'US'" + "       and el.logistics_company in ('Hongkong Post Air Mail','Hongkong Post Air Parcel','China Post Air Mail','China Post Air Parcel')" + //
    "       and el.recv_status_desc is null) and t.event_date is not null order by t.logistics_no, t.event_date";
    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("wl_tracking")));
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("escrow_logistics")));
    Assert.assertEquals(15, visitor.getColumns().size());
//        Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("ESCROW_LOGISTICS", "*")));
// Assert.assertTrue(visitor.getOrderByColumns().contains(new TableStat.Column("employees", "last_name")));
}

Example 83 with OracleSchemaStatVisitor

use of com.alibaba.druid.sql.dialect.oracle.visitor.OracleSchemaStatVisitor in project druid by alibaba.

the class OracleSelectTest35 method test_0.

public void test_0() throws Exception {
    //
    String sql = //
    "select typefuncti0_.id as id104_, typefuncti0_.function_id as function2_104_, " + //
    "typefuncti0_.in_container as in3_104_, typefuncti0_.inherited as inherited104_," + //
    "typefuncti0_.overriding as overriding104_, typefuncti0_.sn as sn104_, " + //
    "typefuncti0_.type_id as type7_104_ from com_function_ontype typefuncti0_ cross " + //
    " join com_function function1_ where typefuncti0_.function_id=function1_.id " + //
    "and (typefuncti0_.type_id in (? , ? , ? , ?)) and function1_.code=?";
    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("com_function_ontype")));
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("com_function")));
    Assert.assertEquals(9, visitor.getColumns().size());
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("ESCROW_LOGISTICS", "*")));
// Assert.assertTrue(visitor.getOrderByColumns().contains(new TableStat.Column("employees", "last_name")));
}

Example 84 with OracleSchemaStatVisitor

use of com.alibaba.druid.sql.dialect.oracle.visitor.OracleSchemaStatVisitor in project druid by alibaba.

the class OracleSelectTest37 method test_0.

public void test_0() throws Exception {
    //
    String sql = "select resource_value,count(resource_value) nums,http_method " + "from ( " + "       select * from audit_url_log " + "       where project_id = ? and to_char(begin_time,'yyyy-MM-dd') > = ? and to_char(begin_time,'yyyy-MM-dd') < = ? ) " + //
    "       group by resource_value,http_method having count(resource_value) > = ?";
    OracleStatementParser parser = new OracleStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    Assert.assertEquals("SELECT resource_value, COUNT(resource_value) AS nums, http_method" + "\nFROM (" + "\n\tSELECT *" + "\n\tFROM audit_url_log" + "\n\tWHERE project_id = ?" + "\n\t\tAND to_char(begin_time, 'yyyy-MM-dd') >= ?" + "\n\t\tAND to_char(begin_time, 'yyyy-MM-dd') <= ?" + "\n)" + "\nGROUP BY resource_value, http_method" + "\nHAVING COUNT(resource_value) >= ?", SQLUtils.toOracleString(stmt));
    Assert.assertEquals("select resource_value, count(resource_value) as nums, http_method" + "\nfrom (" + "\n\tselect *" + "\n\tfrom audit_url_log" + "\n\twhere project_id = ?" + "\n\t\tand to_char(begin_time, 'yyyy-MM-dd') >= ?" + "\n\t\tand to_char(begin_time, 'yyyy-MM-dd') <= ?" + "\n)" + "\ngroup by resource_value, http_method" + "\nhaving count(resource_value) >= ?", SQLUtils.toOracleString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
    Assert.assertEquals(1, statementList.size());
    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(1, visitor.getTables().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("audit_url_log")));
    Assert.assertEquals(5, visitor.getColumns().size());
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("audit_url_log", "project_id")));
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("audit_url_log", "begin_time")));
// Assert.assertTrue(visitor.getOrderByColumns().contains(new TableStat.Column("employees", "last_name")));
}

Example 85 with OracleSchemaStatVisitor

use of com.alibaba.druid.sql.dialect.oracle.visitor.OracleSchemaStatVisitor in project druid by alibaba.

the class OracleSelectTest38 method test_0.

public void test_0() throws Exception {
    //
    String sql = "select * from " + "(with vw_kreis_statics_t as" + "  (select substr(xzqh,1,6) xzqh,swrslx,sum(swrs_count) acd_totle from" + "    (select xzqh,sglx,case when (swrs7 <  3) then '1'" + "       when (swrs7 <  5) then '2' when (swrs7 <=  9) then '3' else '4' end  swrslx,1 swrs_count" + "       from acduser.vw_acd_info where sglx='1' " + "       " + "                    and sgfssj  >=   ?" + "                 " + "                 " + "        )" + "   group by substr(xzqh,1,6),swrslx)" + "" + "   select e.\"XZQH\",e.\"LESS3\",e.\"F3TO5\",e.\"F5TO9\",e.\"MORE9\",kreis_code, kreis_name,px1,py1,px2,py2 from" + "    ( select" + "     xzqh," + "     nvl(max(decode(swrslx,'1',acd_totle)),0)  less3," + "     nvl(max(decode(swrslx,'2',acd_totle)),0)  f3to5," + "     nvl(max(decode(swrslx,'3',acd_totle)),0)  f5to9," + "     nvl(max(decode(swrslx,'4',acd_totle)),0)  more9" + "     from( select * from acduser.vw_kreis_statics_t) group by xzqh  " + "     ) e" + "" + "  left join" + " acduser.vw_sc_kreis_code_lv2 f on e.xzqh = f.short_kreis_code) " + "   where kreis_code in" + "(select * from " + "  (select tbek_code from acduser.vw_kreis_code start with tbek_code = ? connect by prior tbek_pk=tbek_parent ) " + //
    "where  tbek_code != ?)";
    OracleStatementParser parser = new OracleStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    {
        String result = SQLUtils.toOracleString(stmt);
        Assert.assertEquals("SELECT *" + "\nFROM (" + "\n\tWITH" + "\n\t\tvw_kreis_statics_t" + "\n\t\tAS" + "\n\t\t(" + "\n\t\t\tSELECT substr(xzqh, 1, 6) AS xzqh, swrslx, SUM(swrs_count) AS acd_totle" + "\n\t\t\tFROM (" + "\n\t\t\t\tSELECT xzqh, sglx, CASE WHEN swrs7 < 3 THEN '1' WHEN swrs7 < 5 THEN '2' WHEN swrs7 <= 9 THEN '3' ELSE '4' END AS swrslx, 1 AS swrs_count" + "\n\t\t\t\tFROM acduser.vw_acd_info" + "\n\t\t\t\tWHERE sglx = '1'" + "\n\t\t\t\t\tAND sgfssj >= ?" + "\n\t\t\t)" + "\n\t\t\tGROUP BY substr(xzqh, 1, 6), swrslx" + "\n\t\t)" + "\n\tSELECT e.\"XZQH\", e.\"LESS3\", e.\"F3TO5\", e.\"F5TO9\", e.\"MORE9\"" + "\n\t\t, kreis_code, kreis_name, px1, py1, px2" + "\n\t\t, py2" + "\n\tFROM (" + "\n\t\tSELECT xzqh, nvl(MAX(decode(swrslx, '1', acd_totle)), 0) AS less3, nvl(MAX(decode(swrslx, '2', acd_totle)), 0) AS f3to5, nvl(MAX(decode(swrslx, '3', acd_totle)), 0) AS f5to9, nvl(MAX(decode(swrslx, '4', acd_totle)), 0) AS more9" + "\n\t\tFROM (" + "\n\t\t\tSELECT *" + "\n\t\t\tFROM acduser.vw_kreis_statics_t" + "\n\t\t)" + "\n\t\tGROUP BY xzqh" + "\n\t) e" + "\n\t\tLEFT JOIN acduser.vw_sc_kreis_code_lv2 f ON e.xzqh = f.short_kreis_code " + "\n)" + "\nWHERE kreis_code IN (SELECT *" + "\n\tFROM (" + "\n\t\tSELECT tbek_code" + "\n\t\tFROM acduser.vw_kreis_code" + "\n\t\tSTART WITH tbek_code = ?" + "\n\t\tCONNECT BY PRIOR tbek_pk = tbek_parent" + "\n\t)" + "\n\tWHERE tbek_code != ?)", result);
    }
    {
        String result = SQLUtils.toOracleString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION);
        Assert.assertEquals("select *" + "\nfrom (" + "\n\twith" + "\n\t\tvw_kreis_statics_t" + "\n\t\tas" + "\n\t\t(" + "\n\t\t\tselect substr(xzqh, 1, 6) as xzqh, swrslx, sum(swrs_count) as acd_totle" + "\n\t\t\tfrom (" + "\n\t\t\t\tselect xzqh, sglx, case when swrs7 < 3 then '1' when swrs7 < 5 then '2' when swrs7 <= 9 then '3' else '4' end as swrslx, 1 as swrs_count" + "\n\t\t\t\tfrom acduser.vw_acd_info" + "\n\t\t\t\twhere sglx = '1'" + "\n\t\t\t\t\tand sgfssj >= ?" + "\n\t\t\t)" + "\n\t\t\tgroup by substr(xzqh, 1, 6), swrslx" + "\n\t\t)" + "\n\tselect e.\"XZQH\", e.\"LESS3\", e.\"F3TO5\", e.\"F5TO9\", e.\"MORE9\"" + "\n\t\t, kreis_code, kreis_name, px1, py1, px2" + "\n\t\t, py2" + "\n\tfrom (" + "\n\t\tselect xzqh, nvl(max(decode(swrslx, '1', acd_totle)), 0) as less3, nvl(max(decode(swrslx, '2', acd_totle)), 0) as f3to5, nvl(max(decode(swrslx, '3', acd_totle)), 0) as f5to9, nvl(max(decode(swrslx, '4', acd_totle)), 0) as more9" + "\n\t\tfrom (" + "\n\t\t\tselect *" + "\n\t\t\tfrom acduser.vw_kreis_statics_t" + "\n\t\t)" + "\n\t\tgroup by xzqh" + "\n\t) e" + "\n\t\tleft join acduser.vw_sc_kreis_code_lv2 f on e.xzqh = f.short_kreis_code " + "\n)" + "\nwhere kreis_code in (select *" + "\n\tfrom (" + "\n\t\tselect tbek_code" + "\n\t\tfrom acduser.vw_kreis_code" + "\n\t\tstart with tbek_code = ?" + "\n\t\tconnect by prior tbek_pk = tbek_parent" + "\n\t)" + "\n\twhere tbek_code != ?)", result);
    }
    Assert.assertEquals(1, statementList.size());
    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(4, visitor.getTables().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("acduser.vw_acd_info")));
    Assert.assertEquals(12, visitor.getColumns().size());
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("acduser.vw_acd_info", "xzqh")));
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("acduser.vw_acd_info", "sglx")));
// Assert.assertTrue(visitor.getOrderByColumns().contains(new TableStat.Column("employees", "last_name")));
}