Examples with OracleSchemaStatVisitor com.alibaba.druid.sql.dialect.oracle.visitor.OracleSchemaStatVisitor used on opensource projects

Example 41 with OracleSchemaStatVisitor

use of com.alibaba.druid.sql.dialect.oracle.visitor.OracleSchemaStatVisitor in project druid by alibaba.

the class OracleCreateTableTest31 method test_types.

public void test_types() throws Exception {
    //
    String sql = //
    "CREATE TABLE students (name person_name, age INTEGER," + //
    "   CHECK (name.first_name IS NOT NULL AND " + "          name.last_name IS NOT NULL));";
    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 TABLE students (" + "\n\tname person_name," + "\n\tage INTEGER," + "\n\tCHECK (name.first_name IS NOT NULL" + "\n\t\tAND name.last_name IS NOT NULL)" + //
    "\n)", 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(1, visitor.getTables().size());
    Assert.assertEquals(4, visitor.getColumns().size());
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("students", "name")));
}

Example 42 with OracleSchemaStatVisitor

use of com.alibaba.druid.sql.dialect.oracle.visitor.OracleSchemaStatVisitor in project druid by alibaba.

the class OracleCreateTableTest34 method test_types.

public void test_types() throws Exception {
    //
    String sql = "CREATE TABLE long_tab (pic_id NUMBER, long_pics LONG RAW);";
    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 TABLE long_tab (" + //
    "\n\tpic_id NUMBER," + //
    "\n\tlong_pics LONG RAW" + //
    "\n)", 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(1, visitor.getTables().size());
    Assert.assertEquals(2, visitor.getColumns().size());
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("long_tab", "pic_id")));
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("long_tab", "long_pics")));
}

Example 43 with OracleSchemaStatVisitor

use of com.alibaba.druid.sql.dialect.oracle.visitor.OracleSchemaStatVisitor in project druid by alibaba.

the class OracleCreateTableTest36 method test_types.

public void test_types() throws Exception {
    //
    String sql = "CREATE TABLE composite_sales" + "    ( prod_id        NUMBER(6)" + "    , cust_id        NUMBER" + "    , time_id        DATE" + "    , channel_id     CHAR(1)" + "    , promo_id       NUMBER(6)" + "    , quantity_sold  NUMBER(3)" + "    , amount_sold         NUMBER(10,2)" + "    ) " + "PARTITION BY RANGE (time_id)" + "SUBPARTITION BY HASH (channel_id)" + "  (PARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY'))," + "   PARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY'))," + "   PARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY'))," + "   PARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY'))," + "   PARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY'))," + "   PARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY'))," + "   PARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY'))," + "   PARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY'))," + "   PARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY'))," + "   PARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY'))" + "      SUBPARTITIONS 8," + "   PARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY'))" + "     (SUBPARTITION ch_c," + "      SUBPARTITION ch_i," + "      SUBPARTITION ch_p," + "      SUBPARTITION ch_s," + "      SUBPARTITION ch_t)," + "   PARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE)" + "      SUBPARTITIONS 4)" + ";";
    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 TABLE composite_sales (" + "\n\tprod_id NUMBER(6)," + "\n\tcust_id NUMBER," + "\n\ttime_id DATE," + "\n\tchannel_id CHAR(1)," + "\n\tpromo_id NUMBER(6)," + "\n\tquantity_sold NUMBER(3)," + "\n\tamount_sold NUMBER(10, 2)" + "\n)" + "\nPARTITION BY RANGE (time_id)" + "\nSUBPARTITION BY HASH (channel_id)" + "\n(" + "\n\tPARTITION SALES_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998', 'DD-MON-YYYY'))," + "\n\tPARTITION SALES_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998', 'DD-MON-YYYY'))," + "\n\tPARTITION SALES_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998', 'DD-MON-YYYY'))," + "\n\tPARTITION SALES_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999', 'DD-MON-YYYY'))," + "\n\tPARTITION SALES_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999', 'DD-MON-YYYY'))," + "\n\tPARTITION SALES_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999', 'DD-MON-YYYY'))," + "\n\tPARTITION SALES_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999', 'DD-MON-YYYY'))," + "\n\tPARTITION SALES_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000', 'DD-MON-YYYY'))," + "\n\tPARTITION SALES_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000', 'DD-MON-YYYY'))," + "\n\tPARTITION SALES_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000', 'DD-MON-YYYY'))" + "\n\t\tSUBPARTITIONS 8," + "\n\tPARTITION SALES_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000', 'DD-MON-YYYY'))" + "\n\t(" + "\n\t\tSUBPARTITION ch_c," + "\n\t\tSUBPARTITION ch_i," + "\n\t\tSUBPARTITION ch_p," + "\n\t\tSUBPARTITION ch_s," + "\n\t\tSUBPARTITION ch_t" + "\n\t)," + "\n\tPARTITION SALES_Q4_2000 VALUES LESS THAN (MAXVALUE)" + "\n\t\tSUBPARTITIONS 4" + //
    "\n)", 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(1, visitor.getTables().size());
    Assert.assertEquals(7, visitor.getColumns().size());
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("composite_sales", "prod_id")));
}

Example 44 with OracleSchemaStatVisitor

use of com.alibaba.druid.sql.dialect.oracle.visitor.OracleSchemaStatVisitor in project druid by alibaba.

the class OracleBlockTest2 method test_0.

public void test_0() throws Exception {
    String sql = //
    "declare   i integer := 0; " + //
    "begin   " + //
    "  for c in (" + //
    "      select id " + //
    "      from wl_ship_order" + //
    "      where forwarder_service is null or status is null) " + //
    "  loop" + //
    "      update wl_ship_order" + //
    "          set forwarder_service = nvl(forwarder_service, 'UPS'), status = nvl(status, 500)" + //
    "      where id = c.id;" + //
    "      i := i + 1;" + //
    "      if mod(i, 100) = 0 then" + //
    "          commit;" + //
    "      end if;" + //
    "  end loop;" + //
    "  commit; " + "end;";
    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("wl_ship_order")));
    Assert.assertEquals(4, visitor.getColumns().size());
    Assert.assertEquals(3, visitor.getConditions().size());
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("wl_ship_order", "id")));
}

Example 45 with OracleSchemaStatVisitor

use of com.alibaba.druid.sql.dialect.oracle.visitor.OracleSchemaStatVisitor in project druid by alibaba.

the class OracleBlockTest7 method test_0.

public void test_0() throws Exception {
    String sql = //
    "DROP TABLE employees_temp; " + //
    "CREATE TABLE employees_temp AS " + //
    "  SELECT employee_id, first_name, last_name " + //
    "  FROM employees;" + //
    " " + //
    "DECLARE" + //
    "  emp_id          employees_temp.employee_id%TYPE := 299;" + //
    "  emp_first_name  employees_temp.first_name%TYPE  := 'Bob';" + //
    "  emp_last_name   employees_temp.last_name%TYPE   := 'Henry';" + //
    "BEGIN" + //
    "  INSERT INTO employees_temp (employee_id, first_name, last_name) " + //
    "  VALUES (emp_id, emp_first_name, emp_last_name);" + //
    " " + //
    "  UPDATE employees_temp" + //
    "  SET first_name = 'Robert'" + //
    "  WHERE employee_id = emp_id;" + //
    " " + //
    "  DELETE FROM employees_temp" + //
    "  WHERE employee_id = emp_id" + //
    "  RETURNING first_name, last_name" + //
    "  INTO emp_first_name, emp_last_name;" + //
    "" + //
    "  COMMIT;" + //
    "  DBMS_OUTPUT.PUT_LINE (emp_first_name || ' ' || emp_last_name);" + //
    "END;";
    OracleStatementParser parser = new OracleStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    print(statementList);
    Assert.assertEquals(3, 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("employees_temp")));
    Assert.assertEquals(6, visitor.getColumns().size());
    Assert.assertEquals(2, visitor.getConditions().size());
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees", "employee_id")));
}