Examples with OracleStatementParser com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser used on opensource projects

Example 11 with OracleStatementParser

use of com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser in project druid by alibaba.

the class OracleSubqueryFactoringTest method test_interval.

public void test_interval() throws Exception {
    String sql = "WITH dept_costs AS (\n" + "SELECT department_name, SUM(salary) dept_total\n" + "FROM employees e, departments d\n" + "WHERE e.department_id = d.department_id\n" + "GROUP BY department_name), " + "avg_cost AS (SELECT SUM(dept_total)/COUNT(*) avg FROM dept_costs)\n" + "SELECT * FROM dept_costs WHERE dept_total > (SELECT avg FROM avg_cost) ORDER BY department_name;";
    String expected = //
    "WITH\n" + //
    "\tdept_costs\n" + //
    "\tAS\n" + //
    "\t(\n" + //
    "\t\tSELECT department_name, SUM(salary) AS dept_total\n" + //
    "\t\tFROM employees e, departments d\n" + //
    "\t\tWHERE e.department_id = d.department_id\n" + //
    "\t\tGROUP BY department_name\n" + //
    "\t), \n" + //
    "\tavg_cost\n" + //
    "\tAS\n" + //
    "\t(\n" + //
    "\t\tSELECT SUM(dept_total) / COUNT(*) AS avg\n" + //
    "\t\tFROM dept_costs\n" + //
    "\t)\n" + //
    "SELECT *\n" + //
    "FROM dept_costs\n" + //
    "WHERE dept_total > (\n" + //
    "\tSELECT avg\n" + //
    "\tFROM avg_cost\n" + //
    "\t)\n" + "ORDER BY department_name;\n";
    OracleStatementParser parser = new OracleStatementParser(sql);
    SQLSelectStatement stmt = (SQLSelectStatement) parser.parseStatementList().get(0);
    String text = TestUtils.outputOracle(stmt);
    Assert.assertEquals(expected, text);
    System.out.println(text);
}

Example 12 with OracleStatementParser

use of com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser in project druid by alibaba.

the class OracleSubqueryFactoringTest2 method test_interval.

public void test_interval() throws Exception {
    String sql = "WITH org_chart (eid, emp_last, mgr_id, reportLevel, salary, job_id) AS\n" + "(\n" + "SELECT employee_id, last_name, manager_id, 0 reportLevel, salary, job_id\n" + "FROM employees\n" + "WHERE manager_id is null\n" + "UNION ALL\n" + "SELECT e.employee_id, e.last_name, e.manager_id, r.reportLevel+1 reportLevel, e.salary, e.job_id\n" + "FROM org_chart r, employees e\n" + "WHERE r.eid = e.manager_id\n" + ")\n" + "SEARCH DEPTH FIRST BY emp_last SET order1\n" + "CYCLE hire_date SET is_cycle TO 'Y' DEFAULT 'N'" + "SELECT lpad(' ',2*reportLevel)||emp_last emp_name, eid, mgr_id, salary, job_id\n" + "FROM org_chart\n" + "ORDER BY order1;\n";
    String expected = "WITH\n" + "\torg_chart (eid, emp_last, mgr_id, reportLevel, salary, job_id)\n" + "\tAS\n" + "\t(\n" + "\t\tSELECT employee_id, last_name, manager_id, 0 AS reportLevel, salary\n" + "\t\t\t, job_id\n" + "\t\tFROM employees\n" + "\t\tWHERE manager_id IS NULL\n" + "\t\tUNION ALL\n" + "\t\tSELECT e.employee_id, e.last_name, e.manager_id, r.reportLevel + 1 AS reportLevel, e.salary\n" + "\t\t\t, e.job_id\n" + "\t\tFROM org_chart r, employees e\n" + "\t\tWHERE r.eid = e.manager_id\n" + "\t)\n" + "\tSEARCH DEPTH FIRST BY emp_last SET order1\n" + "\tCYCLE hire_date SET is_cycle TO 'Y' DEFAULT 'N'\n" + "SELECT lpad(' ', 2 * reportLevel) || emp_last AS emp_name, eid, mgr_id, salary, job_id\n" + "FROM org_chart\n" + "ORDER BY order1;\n";
    OracleStatementParser parser = new OracleStatementParser(sql);
    SQLSelectStatement stmt = (SQLSelectStatement) parser.parseStatementList().get(0);
    String text = TestUtils.outputOracle(stmt);
    Assert.assertEquals(expected, text);
    System.out.println(text);
}

Example 13 with OracleStatementParser

use of com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser in project druid by alibaba.

the class OracleUpdateTest1 method test_0.

public void test_0() throws Exception {
    String sql = //
    "UPDATE table1 t_alias1 " + //
    "    SET column = " + //
    "        (SELECT expr " + //
    "            FROM table2 t_alias2 " + "            WHERE t_alias1.column = t_alias2.column); ";
    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.assertTrue(visitor.getTables().containsKey(new TableStat.Name("table1")));
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("table2")));
    Assert.assertEquals(2, visitor.getTables().size());
    Assert.assertEquals(3, visitor.getColumns().size());
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("table1", "column")));
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("table2", "expr")));
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("table2", "column")));
}

Example 14 with OracleStatementParser

use of com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser in project druid by alibaba.

the class OracleUpdateTest4 method test_0.

public void test_0() throws Exception {
    String sql = "update tabpart$ set " + //
    "dataobj# = :1, part# = :2, hiboundlen = :3, hiboundval = :4, ts# = :5, file# = :6, block# = :7, pctfree$ = :8, pctused$ = :9, initrans = :10, maxtrans = :11, flags = :12, analyzetime = :13, samplesize = :14, rowcnt = :15, blkcnt = :16, empcnt = :17, avgspc = :18, chncnt = :19, avgrln = :20, bhiboundval = EMPTY_BLOB() " + //
    "where obj# = :21 " + //
    "returning bhiboundval into :22";
    //
    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.assertTrue(visitor.getTables().containsKey(new TableStat.Name("tabpart$")));
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(22, visitor.getColumns().size());
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("tabpart$", "dataobj#")));
//        Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("sys.col_usage$", "intcol#")));
}

Example 15 with OracleStatementParser

use of com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser in project druid by alibaba.

the class OracleAlterIndexTest1 method test_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")));
}