Beispiel #1
0
  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")));
  }
Beispiel #8
0
  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")));
  }