Exemplo n.º 1
0
  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")));
  }
Exemplo n.º 2
0
  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());
  }
Exemplo n.º 3
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")));
  }
Exemplo n.º 4
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")));
  }
Exemplo n.º 5
0
  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")));
  }
Exemplo n.º 6
0
  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")));
  }
Exemplo n.º 7
0
  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")));
  }
Exemplo n.º 8
0
  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());
  }
Exemplo n.º 10
0
  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")));
  }
Exemplo n.º 11
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")));
  }
Exemplo n.º 12
0
  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")));
  }
Exemplo n.º 13
0
  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);
  }
Exemplo n.º 14
0
  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);
  }
Exemplo n.º 15
0
  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());
  }
Exemplo n.º 18
0
  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);
  }
Exemplo n.º 20
0
  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")));
  }