public void test_0() throws Exception {
String sql = //
"ALTER TABLE ws_i18n_translate_item ADD CONSTRAINT ws_i18n_translate_item_pk " //
+ "PRIMARY KEY (id) USING INDEX ws_i18n_translate_item_pk";
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(1, visitor.getTables().size());
Assert.assertTrue(
visitor.getTables().containsKey(new TableStat.Name("ws_i18n_translate_item")));
Assert.assertEquals(1, visitor.getColumns().size());
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("ws_i18n_translate_item", "id")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("pivot_table",
// "YEAR")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("pivot_table",
// "order_mode")));
}
void validateOracle(int id, String sql) throws Exception {
sql = sql.trim();
boolean sqlFlag = false;
String lowerSql = sql.toLowerCase();
if (lowerSql.startsWith("insert")
|| lowerSql.startsWith("select")
|| lowerSql.startsWith("upate")
|| lowerSql.startsWith("delete")
|| lowerSql.startsWith("create")
|| lowerSql.startsWith("drop")) {
sqlFlag = true;
}
if (!sqlFlag) {
update(id, sql, 2);
return;
}
OracleStatementParser parser = new OracleStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
Assert.assertEquals(1, statementList.size());
StringBuilder out = new StringBuilder();
OracleParameterizedOutputVisitor visitor = new OracleParameterizedOutputVisitor(out);
statemen.accept(visitor);
update(id, out.toString(), 1);
System.out.println(sql);
System.out.println(out.toString());
}
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")));
}
public void test_0() throws Exception {
String sql = //
"alter table WRH$_SERVICE_WAIT_CLASS drop partition WRH$_SERVIC_1870432296_13478";
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(1, visitor.getTables().size());
Assert.assertTrue(
visitor.getTables().containsKey(new TableStat.Name("WRH$_SERVICE_WAIT_CLASS")));
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")));
}
public void test_0() throws Exception {
String sql = //
"select (select decode(count(*),1,'YES','NO') FROM sys.obj$ o, sys.user$ u WHERE u.name = 'PERFSTAT' AND o.owner# = u.user# AND o.name = 'STATSPACK' AND o.type# = 11 AND o.status = 1) is_installed , (select nvl(INTERVAL,'') from dba_jobs where what like 'statspack.snap%' and SCHEMA_USER='******' and rownum =1) freq from dual"; //
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(3, visitor.getTables().size());
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("sys.obj$")));
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("sys.user$")));
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("dba_jobs")));
Assert.assertEquals(10, 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")));
}
public void test_0() throws Exception {
String sql =
"insert into \"OPS$ADMIN\".\"ORASTAT\" select /*+ rule */ :5 statid, 'C' type, :6 version, bitand(h.spare2,7) flags, ot.name c1, null c2, null c3, c.name c4, u.name c5, h.distcnt n1, h.density n2, h.spare1 n3, h.sample_size n4, h.null_cnt n5, h.minimum n6, h.maximum n7, h.avgcln n8, decode(h.cache_cnt,0,null,1) n9, hg.bucket n10, hg.endpoint n11, null n12, h.timestamp# d1, h.lowval r1, h.hival r2, hg.epvalue ch1 from sys.user$ u, sys.obj$ ot, sys.col$ c, sys.hist_head$ h, histgrm$ hg where :3 is null and u.name = :1 and ot.owner# = u.user# and ot.name = :2 and ot.type# = 2 and c.obj# = ot.obj# and (:4 is null or c.name = :4) and h.obj# = ot.obj# and h.intcol# = c.intcol# and hg.obj#(+) = h.obj# and hg.intcol#(+) = h.intcol# union all select :5 statid, 'C' type, :6 version, bitand(h.spare2,7) flags, ot.name c1, op.subname c2, null c3, c.name c4, u.name c5, h.distcnt n1, h.density n2, h.spare1 n3, h.sample_size n4, h.null_cnt n5, h.minimum n6, h.maximum n7, h.avgcln n8, decode(h.cache_cnt,0,null,1) n9, hg.bucket n10, hg.endpoint n11, null n12, h.timestamp# d1, h.lowval r1, h.hival r2, hg.epvalue ch1 from sys.user$ u, sys.obj$ ot, sys.col$ c, sys.tabpart$ tp, sys.obj$ op, sys.hist_head$ h, histgrm$ hg where u.name = :1 and ot.owner# = u.user# and ot.name = :2 and ot.type# = 2 and c.obj# = ot.obj# and (:4 is null or c.name = :4) and tp.bo# = ot.obj# and tp.obj# = op.obj# and ((:3 is null and :vc_cascade_parts is not null) or op.subname = :3) and h.obj# = op.obj# and h.intcol# = c.intcol# and hg.obj#(+) = h.obj# and hg.intcol#(+) = h.intcol# union all select :5 statid, 'C' type, :6 version, bitand(h.spare2,7) flags, op.name c1, op.subname c2, null c3, c.name c4, u.name c5, h.distcnt n1, h.density n2, h.spare1 n3, h.sample_size n4, h.null_cnt n5, h.minimum n6, h.maximum n7, h.avgcln n8, decode(h.cache_cnt,0,null,1) n9, hg.bucket n10, hg.endpoint n11, null n12, h.timestamp# d1, h.lowval r1, h.hival r2, hg.epvalue ch1 from sys.user$ u, sys.col$ c, sys.tabcompart$ tp, sys.obj$ op, sys.hist_head$ h, histgrm$ hg where u.name = :1 and op.owner# = u.user# and op.name = :2 and op.type# = 19 and ((:3 is null and :vc_cascade_parts is not null) or op.subname = :3) and tp.obj# = op.obj# and c.obj# = tp.bo# and (:4 is null or c.name = :4) and h.obj# = op.obj# and h.intcol# = c.intcol# and hg.obj#(+) = h.obj# and hg.intcol#(+) = h.intcol# union all select :5 statid, 'C' type, :6 version, bitand(h.spare2,7) flags, op.name c1, op.subname c2, os.subname c3, c.name c4, u.name c5, h.distcnt n1, h.density n2, h.spare1 n3, h.sample_size n4, h.null_cnt n5, h.minimum n6, h.maximum n7, h.avgcln n8, decode(h.cache_cnt,0,null,1) n9, hg.bucket n10, hg.endpoint n11, null n12, h.timestamp# d1, h.lowval r1, h.hival r2, hg.epvalue ch1 from sys.user$ u, sys.col$ c, sys.tabcompart$ tp, sys.obj$ op, sys.tabsubpart$ ts, sys.obj$ os, sys.hist_head$ h, histgrm$ hg where u.name = :1 and op.owner# = u.user# and op.name = :2 and op.type# = 19 and tp.obj# = op.obj# and c.obj# = tp.bo# and (:4 is null or c.name = :4) and ts.pobj# = tp.obj# and ts.obj# = os.obj# and ((:3 is null and :vc_cascade_parts is not null) or (op.subname = :3 and (:vc_cascade_parts is not null or os.subname is null)) or os.subname = :3) and h.obj# = os.obj# and h.intcol# = c.intcol# and hg.obj#(+) = h.obj# and hg.intcol#(+) = h.intcol# order by c5,c1,c2,c3,c4,n10";
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.assertEquals(9, visitor.getTables().size());
Assert.assertEquals(42, visitor.getColumns().size());
// Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("raises")));
// Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("employees")));
//
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees",
// "employee_id")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees",
// "salary")));
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees",
// "commission_pct")));
}
public void test_0() throws Exception {
String sql =
"SELECT e.employee_id, e.salary, e.commission_pct"
+ //
" FROM employees e JOIN departments d"
+ //
" USING (department_id)"
+ //
" WHERE job_id = 'SA_REP'"
+ //
" AND location_id = 2500"
+ //
" ORDER BY e.employee_id"
+ //
" FOR UPDATE OF e.salary;";
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("departments")));
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("employees")));
Assert.assertEquals(7, visitor.getColumns().size());
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("departments", "department_id")));
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("employees", "department_id")));
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("employees", "employee_id")));
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("employees", "commission_pct")));
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("employees", "department_id")));
Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("UNKNOWN", "job_id")));
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("UNKNOWN", "location_id")));
}
public void test_0() throws Exception {
String sql =
"DECLARE"
+ //
" CURSOR c1 IS"
+ //
" SELECT last_name, department_name"
+ //
" FROM employees, departments"
+ //
" WHERE employees.department_id = departments.department_id "
+ //
" AND job_id = 'SA_MAN'"
+ //
" FOR UPDATE OF salary;"
+ //
"BEGIN"
+ //
" NULL;"
+ //
"END;"; //
OracleStatementParser parser = new OracleStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
print(statementList);
Assert.assertEquals(1, statementList.size());
OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
for (SQLStatement statement : statementList) {
statement.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("employees")));
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("departments")));
Assert.assertEquals(5, visitor.getColumns().size());
Assert.assertEquals(3, visitor.getConditions().size());
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees",
// "salary")));
}
void validateOracle(String sql, String expect) {
OracleStatementParser parser = new OracleStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
Assert.assertEquals(1, statementList.size());
StringBuilder out = new StringBuilder();
OracleParameterizedOutputVisitor visitor = new OracleParameterizedOutputVisitor(out, false);
statemen.accept(visitor);
Assert.assertEquals(expect, out.toString());
}
public void test_0() throws Exception {
String sql = //
"CREATE TRIGGER \"AO_4AEACD_WEBHOOK_D367380484\" " //
+ "BEFORE INSERT" //
+ " ON \"AO_4AEACD_WEBHOOK_DAO\" FOR EACH ROW " //
+ "BEGIN" //
+ " SELECT \"AO_4AEACD_WEBHOOK_DAO_ID_SEQ\".NEXTVAL INTO :NEW.\"ID\" FROM DUAL;" //
+ "END;";
OracleStatementParser parser = new OracleStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement stmt = statementList.get(0);
print(statementList);
Assert.assertEquals(1, statementList.size());
Assert.assertEquals(
"CREATE TRIGGER \"AO_4AEACD_WEBHOOK_D367380484\"" //
+ "\n\tBEFORE INSERT" //
+ "\n\tON \"AO_4AEACD_WEBHOOK_DAO\"" //
+ "\n\tFOR EACH ROW" //
+ "\nBEGIN" //
+ "\n\tSELECT \"AO_4AEACD_WEBHOOK_DAO_ID_SEQ\".NEXTVAL" //
+ "\n\tINTO :NEW.\"ID\"" //
+ "\n\tFROM DUAL;"
+ "\nEND", //
SQLUtils.toSQLString(stmt, JdbcConstants.ORACLE));
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(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")));
}
public void test_0() throws Exception {
String sql = //
"SELECT ALL * FROM t_department "
+ //
"WHERE name IN ('0000','4444') "
+ //
"ORDER BY name ASC"; //
OracleStatementParser parser = new OracleStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement stmt = statementList.get(0);
print(statementList);
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.assertEquals(2, visitor.getColumns().size());
String text = TestUtils.outputOracle(stmt);
Assert.assertEquals(
"SELECT ALL *"
+ //
"\nFROM t_department"
+ //
"\nWHERE name IN ('0000', '4444')"
+ //
"\nORDER BY name ASC;"
+ //
"\n",
text);
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("acduser.vw_acd_info",
// "xzqh")));
// Assert.assertTrue(visitor.getOrderByColumns().contains(new TableStat.Column("employees",
// "last_name")));
}
public void test_0() throws Exception {
String sql = //
"select *" //
+ " FROM MT_PRODUCT_ORDER T1,MT_ORDER T2 WHERE " //
+ " T1.MT_ORDER_ID = T2.MT_ORDER_ID" //
+ " and SELLER_SSOID = 1" //
+ " AND T1.MT_ORDER_ID = '1'" //
+ " AND T1.MT_BATCH_ORDER_ID IN '1'"; //
OracleStatementParser parser = new OracleStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement stmt = statementList.get(0);
print(statementList);
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(2, visitor.getTables().size());
Assert.assertEquals(4, visitor.getColumns().size());
String text = TestUtils.outputOracle(stmt);
Assert.assertEquals(
"SELECT *" //
+ "\nFROM MT_PRODUCT_ORDER T1, MT_ORDER T2" //
+ "\nWHERE T1.MT_ORDER_ID = T2.MT_ORDER_ID" //
+ "\n\tAND SELLER_SSOID = 1" //
+ "\n\tAND T1.MT_ORDER_ID = '1'" //
+ "\n\tAND T1.MT_BATCH_ORDER_ID IN ('1');" //
+ "\n",
text);
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("acduser.vw_acd_info",
// "xzqh")));
// Assert.assertTrue(visitor.getOrderByColumns().contains(new TableStat.Column("employees",
// "last_name")));
}
public void test_isEmpty() throws Exception {
String sql =
"SELECT product_id, TO_CHAR(ad_finaltext) FROM print_media WHERE ad_textdocs_ntab IS NOT EMPTY;";
String expect =
"SELECT product_id, TO_CHAR(ad_finaltext)\n"
+ "FROM print_media\n"
+ "WHERE ad_textdocs_ntab IS NOT EMPTY;\n";
OracleStatementParser parser = new OracleStatementParser(sql);
SQLSelectStatement stmt = (SQLSelectStatement) parser.parseStatementList().get(0);
String text = TestUtils.outputOracle(stmt);
Assert.assertEquals(expect, text);
System.out.println(text);
}
public void test_0() throws Exception {
String sql =
"SELECT last_name, salary, STDDEV(salary) OVER (ORDER BY hire_date) \"StdDev\" "
+ "FROM employees "
+ "WHERE department_id = 30;";
String expect =
"SELECT last_name, salary, STDDEV(salary) OVER (ORDER BY hire_date) AS \"StdDev\"\n"
+ "FROM employees\n"
+ "WHERE department_id = 30;\n";
OracleStatementParser parser = new OracleStatementParser(sql);
SQLSelectStatement stmt = (SQLSelectStatement) parser.parseStatementList().get(0);
String text = TestUtils.outputOracle(stmt);
Assert.assertEquals(expect, text);
System.out.println(text);
}
public void test_1() throws Exception {
String sql =
"SELECT submit_date, num_votes, TRUNC(AVG(num_votes) OVER(PARTITION BY submit_date ORDER BY submit_date ROWS UNBOUNDED PRECEDING)) AVG_VOTE_PER_DAY\n"
+ "FROM vote_count\n"
+ "ORDER BY submit_date;\n";
String expect =
"SELECT submit_date, num_votes, TRUNC(AVG(num_votes) OVER (PARTITION BY submit_date ORDER BY submit_date ROWS UNBOUNDED PRECEDING)) AS AVG_VOTE_PER_DAY\n"
+ "FROM vote_count\n"
+ "ORDER BY submit_date;\n";
OracleStatementParser parser = new OracleStatementParser(sql);
SQLSelectStatement stmt = (SQLSelectStatement) parser.parseStatementList().get(0);
String text = TestUtils.outputOracle(stmt);
Assert.assertEquals(expect, text);
System.out.println(text);
}
public void test_0() throws Exception {
String sql = "delete from users where id = ? or name = ?";
OracleStatementParser parser = new OracleStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
Assert.assertEquals(1, statementList.size());
OracleSchemaStatVisitor visitor = new OracleSchemaStatVisitor();
statemen.accept(visitor);
System.out.println(sql);
System.out.println("Tables : " + visitor.getTables());
System.out.println("fields : " + visitor.getColumns());
Assert.assertEquals(1, visitor.getTables().size());
Assert.assertEquals(true, visitor.containsTable("users"));
Assert.assertEquals(2, visitor.getColumns().size());
Assert.assertEquals(true, visitor.getColumns().contains(new Column("users", "id")));
Assert.assertEquals(true, visitor.getColumns().contains(new Column("users", "name")));
}
public void test_0() throws Exception {
String sql = "SELECT salary from employee for update";
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("employee"));
Assert.assertEquals(1, visitor.getColumns().size());
Assert.assertEquals(true, visitor.getColumns().contains(new Column("employee", "salary")));
StringBuilder buf = new StringBuilder();
OracleOutputVisitor outputVisitor = new OracleOutputVisitor(buf);
stmt.accept(outputVisitor);
Assert.assertEquals("SELECT salary\nFROM employee\nFOR UPDATE;\n", buf.toString());
}
public void test_0() throws Exception {
String sql =
"INSERT INTO raises"
+ //
" SELECT employee_id, salary*1.1 FROM employees"
+ //
" WHERE commission_pct > .2"
+ //
" LOG ERRORS INTO errlog ('my_bad') REJECT LIMIT 10;";
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("raises")));
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("employees")));
Assert.assertEquals(2, visitor.getTables().size());
Assert.assertEquals(3, visitor.getColumns().size());
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("employees", "employee_id")));
Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees", "salary")));
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("employees", "commission_pct")));
}
public void test_isEmpty() throws Exception {
String sql =
"SELECT DECODE(GROUPING(department_name), 1, 'All Departments', department_name) AS department,"
+ "DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job, COUNT(*) AS \"Total Empl\", AVG(salary) * 12 AS \"Average Sal\" "
+ "FROM employees e, departments d\n"
+ "WHERE d.department_id = e.department_id\n"
+ "GROUP BY ROLLUP (department_name, job_id);\n";
String expect =
"SELECT DECODE(GROUPING(department_name), 1, 'All Departments', department_name) AS department, "
+ "DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job, COUNT(*) AS \"Total Empl\", AVG(salary) * 12 AS \"Average Sal\"\n"
+ "FROM employees e, departments d\n"
+ "WHERE d.department_id = e.department_id\n"
+ "GROUP BY ROLLUP(department_name, job_id);\n";
OracleStatementParser parser = new OracleStatementParser(sql);
SQLSelectStatement stmt = (SQLSelectStatement) parser.parseStatementList().get(0);
String text = TestUtils.outputOracle(stmt);
Assert.assertEquals(expect, text);
System.out.println(text);
}
public void test_0() throws Exception {
String sql =
"INSERT ALL"
+ //
" INTO sales (prod_id, cust_id, time_id, amount)"
+ //
" VALUES (product_id, customer_id, weekly_start_date, sales_sun)"
+ //
" INTO sales (prod_id, cust_id, time_id, amount)"
+ //
" VALUES (product_id, customer_id, weekly_start_date+1, sales_mon)"
+ //
" INTO sales (prod_id, cust_id, time_id, amount)"
+ //
" VALUES (product_id, customer_id, weekly_start_date+2, sales_tue)"
+ //
" INTO sales (prod_id, cust_id, time_id, amount)"
+ //
" VALUES (product_id, customer_id, weekly_start_date+3, sales_wed)"
+ //
" INTO sales (prod_id, cust_id, time_id, amount)"
+ //
" VALUES (product_id, customer_id, weekly_start_date+4, sales_thu)"
+ //
" INTO sales (prod_id, cust_id, time_id, amount)"
+ //
" VALUES (product_id, customer_id, weekly_start_date+5, sales_fri)"
+ //
" INTO sales (prod_id, cust_id, time_id, amount)"
+ //
" VALUES (product_id, customer_id, weekly_start_date+6, sales_sat)"
+ //
" SELECT product_id, customer_id, weekly_start_date, sales_sun,"
+ //
" sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat"
+ //
" FROM sales_input_table;"; //
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("sales")));
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("sales_input_table")));
Assert.assertEquals(2, visitor.getTables().size());
Assert.assertEquals(14, visitor.getColumns().size());
Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("sales", "prod_id")));
Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("sales", "cust_id")));
Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("sales", "time_id")));
Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("sales", "amount")));
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("sales_input_table", "product_id")));
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("sales_input_table", "customer_id")));
Assert.assertTrue(
visitor
.getColumns()
.contains(new TableStat.Column("sales_input_table", "weekly_start_date")));
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("sales_input_table", "sales_sun")));
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("sales_input_table", "sales_mon")));
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("sales_input_table", "sales_tue")));
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("sales_input_table", "sales_wed")));
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("sales_input_table", "sales_thu")));
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("sales_input_table", "sales_fri")));
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("sales_input_table", "sales_sat")));
}