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 = //
"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 = //
"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 = //
"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")));
}
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")));
}
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")));
}