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

Example 66 with OracleSchemaStatVisitor

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

the class OracleBlockTest12 method test_0.

public void test_0() throws Exception {
    String sql = "DECLARE" + "  emp_id        NUMBER(6);" + "  emp_lastname  VARCHAR2(25);" + "  emp_salary    NUMBER(8,2);" + "  emp_jobid     VARCHAR2(10);" + "BEGIN" + "  SELECT employee_id, last_name, salary, job_id" + "  INTO emp_id, emp_lastname, emp_salary, emp_jobid" + "  FROM employees" + "  WHERE employee_id = 120;" + "" + "  INSERT INTO emp_name (employee_id, last_name)" + "  VALUES (emp_id, emp_lastname); " + "  INSERT INTO emp_sal (employee_id, salary) " + "  VALUES (emp_id, emp_salary);" + "" + "  INSERT INTO emp_job (employee_id, job_id)" + "  VALUES (emp_id, emp_jobid);" + " " + "EXCEPTION" + "  WHEN DUP_VAL_ON_INDEX THEN" + "    ROLLBACK;" + "    DBMS_OUTPUT.PUT_LINE('Inserts were rolled back');" + //
    "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(4, visitor.getTables().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("employees")));
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("emp_name")));
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("emp_sal")));
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("emp_job")));
    Assert.assertEquals(11, visitor.getColumns().size());
    Assert.assertEquals(1, visitor.getConditions().size());
//        Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees", "salary")));
}

Example 67 with OracleSchemaStatVisitor

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

the class OracleBlockTest14 method test_0.

public void test_0() throws Exception {
    String sql = //
    "DECLARE" + //
    "  emp_id        employees.employee_id%TYPE;" + //
    "  emp_lastname  employees.last_name%TYPE;" + //
    "  emp_salary    employees.salary%TYPE;" + //
    "" + //
    "BEGIN" + //
    "  SELECT employee_id, last_name, salary" + //
    "  INTO emp_id, emp_lastname, emp_salary" + //
    "  FROM employees" + //
    "  WHERE employee_id = 120;" + //
    "  SAVEPOINT my_savepoint;" + //
    " " + //
    "  UPDATE emp_name" + //
    "  SET salary = salary * 1.1" + //
    "  WHERE employee_id = emp_id;" + //
    " " + //
    "  DELETE FROM emp_name" + //
    "  WHERE employee_id = 130;" + //
    " " + //
    "  SAVEPOINT my_savepoint;" + //
    " " + //
    "  INSERT INTO emp_name (employee_id, last_name, salary)" + //
    "  VALUES (emp_id, emp_lastname, emp_salary);" + //
    "" + //
    "EXCEPTION" + //
    "  WHEN DUP_VAL_ON_INDEX THEN" + //
    "    ROLLBACK TO my_savepoint;" + //
    "    DBMS_OUTPUT.PUT_LINE('Transaction rolled back.');" + //
    "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("emp_name")));
    Assert.assertEquals(7, visitor.getColumns().size());
    Assert.assertEquals(3, visitor.getConditions().size());
    Assert.assertEquals(1, visitor.getRelationships().size());
// Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("employees", "salary")));
}

Example 68 with OracleSchemaStatVisitor

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

the class OracleCreateTableTest22 method test_types.

public void test_types() throws Exception {
    //
    String sql = //
    "CREATE TABLE promotions_var2" + //
    "    ( promo_id         NUMBER(6)" + //
    "    , promo_name       VARCHAR2(20)" + //
    "    , promo_category   VARCHAR2(15)" + //
    "    , promo_cost       NUMBER(10,2)" + //
    "    , promo_begin_date DATE" + //
    "    , promo_end_date   DATE" + //
    "    , CONSTRAINT promo_id_u UNIQUE (promo_id)" + //
    "   USING INDEX PCTFREE 20" + //
    "      TABLESPACE stocks" + "      STORAGE (INITIAL 8M) );";
    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 promotions_var2 (" + "\n\tpromo_id NUMBER(6)," + "\n\tpromo_name VARCHAR2(20)," + "\n\tpromo_category VARCHAR2(15)," + "\n\tpromo_cost NUMBER(10, 2)," + "\n\tpromo_begin_date DATE," + "\n\tpromo_end_date DATE," + "\n\tCONSTRAINT promo_id_u UNIQUE (promo_id)" + "\n\tUSING INDEX PCTFREE 20 TABLESPACE stocks" + "\n\tSTORAGE (INITIAL 8M)" + //
    "\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(6, visitor.getColumns().size());
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("promotions_var2", "promo_id")));
}

Example 69 with OracleSchemaStatVisitor

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

the class OracleCreateTableTest25 method test_types.

public void test_types() throws Exception {
    //
    String sql = //
    "CREATE TABLE locations_demo" + //
    "    ( location_id    NUMBER(4) " + //
    "    , street_address VARCHAR2(40)" + //
    "    , postal_code    VARCHAR2(12)" + //
    "    , city           VARCHAR2(30)" + //
    "    , state_province VARCHAR2(25)" + //
    "    , country_id     CHAR(2)" + "    , CONSTRAINT loc_id_pk PRIMARY KEY (location_id));";
    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 locations_demo (" + //
    "\n\tlocation_id NUMBER(4)," + //
    "\n\tstreet_address VARCHAR2(40)," + //
    "\n\tpostal_code VARCHAR2(12)," + //
    "\n\tcity VARCHAR2(30)," + //
    "\n\tstate_province VARCHAR2(25)," + "\n\tcountry_id CHAR(2)," + //
    "\n\tCONSTRAINT loc_id_pk PRIMARY KEY (location_id)" + //
    "\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(6, visitor.getColumns().size());
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("locations_demo", "location_id")));
}

Example 70 with OracleSchemaStatVisitor

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

the class OracleCreateTableTest27 method test_types.

public void test_types() throws Exception {
    //
    String sql = //
    "CREATE TABLE dept_20 " + //
    "   (employee_id     NUMBER(4), " + //
    "    last_name       VARCHAR2(10), " + //
    "    job_id          VARCHAR2(9), " + //
    "    manager_id      NUMBER(4), " + //
    "    hire_date       DATE, " + //
    "    salary          NUMBER(7,2), " + //
    "    commission_pct  NUMBER(7,2), " + //
    "    department_id, " + //
    "   CONSTRAINT fk_deptno " + //
    "      FOREIGN  KEY (department_id) " + "      REFERENCES  departments(department_id) ); ";
    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 dept_20 (" + //
    "\n\temployee_id NUMBER(4)," + //
    "\n\tlast_name VARCHAR2(10)," + //
    "\n\tjob_id VARCHAR2(9)," + //
    "\n\tmanager_id NUMBER(4)," + //
    "\n\thire_date DATE," + //
    "\n\tsalary NUMBER(7, 2)," + //
    "\n\tcommission_pct NUMBER(7, 2)," + //
    "\n\tdepartment_id," + //
    "\n\tCONSTRAINT fk_deptno FOREIGN KEY (department_id) REFERENCES departments (department_id)" + //
    "\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(2, visitor.getTables().size());
    Assert.assertEquals(9, visitor.getColumns().size());
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("dept_20", "employee_id")));
}