Examples with SQLCreateViewStatement com.alibaba.druid.sql.ast.statement.SQLCreateViewStatement used on opensource projects

Example 1 with SQLCreateViewStatement

use of com.alibaba.druid.sql.ast.statement.SQLCreateViewStatement in project druid by alibaba.

the class MySqlCreateTableTest9 method test_0.

public void test_0() throws Exception {
    String sql = //
    "CREATE VIEW v AS\n" + //
    "SELECT ProductID,ProductName\n" + //
    "FROM Products\n" + "WHERE Discontinued=No";
    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLCreateViewStatement stmt = (SQLCreateViewStatement) statementList.get(0);
    //        print(statementList);
    Assert.assertEquals(1, statementList.size());
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);
    //        System.out.println("Tables : " + visitor.getTables());
    //        System.out.println("fields : " + visitor.getColumns());
    //        System.out.println("coditions : " + visitor.getConditions());
    //        System.out.println("orderBy : " + visitor.getOrderByColumns());
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(4, visitor.getColumns().size());
    Assert.assertEquals(2, visitor.getConditions().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("Products")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("Products", "ProductID")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("Products", "ProductName")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("Products", "Discontinued")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("Products", "No")));
}

Example 2 with SQLCreateViewStatement

use of com.alibaba.druid.sql.ast.statement.SQLCreateViewStatement in project druid by alibaba.

the class SQLServerCreateTableTest method test_0.

public void test_0() throws Exception {
    String sql = //
    "CREATE VIEW [Current Product List] AS\n" + //
    "SELECT ProductID,ProductName\n" + //
    "FROM Products\n" + "WHERE Discontinued=No";
    SQLServerStatementParser parser = new SQLServerStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLCreateViewStatement stmt = (SQLCreateViewStatement) statementList.get(0);
    Assert.assertEquals(1, statementList.size());
    SQLServerSchemaStatVisitor visitor = new SQLServerSchemaStatVisitor();
    stmt.accept(visitor);
    //        System.out.println("Tables : " + visitor.getTables());
    //        System.out.println("fields : " + visitor.getColumns());
    //        System.out.println("coditions : " + visitor.getConditions());
    //        System.out.println("orderBy : " + visitor.getOrderByColumns());
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(4, visitor.getColumns().size());
    Assert.assertEquals(2, visitor.getConditions().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("Products")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("Products", "ProductID")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("Products", "ProductName")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("Products", "Discontinued")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("Products", "No")));
}

Example 3 with SQLCreateViewStatement

use of com.alibaba.druid.sql.ast.statement.SQLCreateViewStatement in project druid by alibaba.

the class MySqlCreateViewTest5 method test_0.

public void test_0() throws Exception {
    String sql = "create or replace definer = current_user sql security invoker view my_view4(c1, 1c, _, c1_2) \n" + "\tas select * from  (t1 as tt1, t2 as tt2) inner join t1 on t1.col1 = tt1.col1;";
    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLCreateViewStatement stmt = (SQLCreateViewStatement) statementList.get(0);
    // print(statementList);
    assertEquals(1, statementList.size());
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);
    assertEquals("CREATE OR REPLACE DEFINER = current_user\n" + "\tSQL SECURITY = invoker\n" + "\tVIEW my_view4 (\n" + "\tc1, \n" + "\t1c, \n" + "\t_, \n" + "\tc1_2\n" + ")\n" + "AS\n" + "SELECT *\n" + "FROM (t1 tt1, t2 tt2)\n" + // 
    "\tINNER JOIN t1 ON t1.col1 = tt1.col1;", SQLUtils.toMySqlString(stmt));
    assertEquals("create or replace definer = current_user\n" + "\tsql security = invoker\n" + "\tview my_view4 (\n" + "\tc1, \n" + "\t1c, \n" + "\t_, \n" + "\tc1_2\n" + ")\n" + "as\n" + "select *\n" + "from (t1 tt1, t2 tt2)\n" + // 
    "\tinner join t1 on t1.col1 = tt1.col1;", SQLUtils.toMySqlString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
    // System.out.println("Tables : " + visitor.getTables());
    System.out.println("fields : " + visitor.getColumns());
    // System.out.println("coditions : " + visitor.getConditions());
    // System.out.println("orderBy : " + visitor.getOrderByColumns());
    assertEquals(2, visitor.getTables().size());
    assertEquals(3, visitor.getColumns().size());
    assertEquals(1, visitor.getConditions().size());
    assertTrue(visitor.getTables().containsKey(new TableStat.Name("t1")));
    assertTrue(visitor.getColumns().contains(new Column("t1", "col1")));
    assertTrue(visitor.getColumns().contains(new Column("t1", "*")));
    assertTrue(visitor.getColumns().contains(new Column("t2", "*")));
// assertTrue(visitor.getColumns().contains(new Column("t2", "l_suppkey")));
}

Example 4 with SQLCreateViewStatement

use of com.alibaba.druid.sql.ast.statement.SQLCreateViewStatement in project druid by alibaba.

the class MySqlCreateViewTest4 method test_0.

public void test_0() throws Exception {
    String sql = "create or replace definer = 'ivan'@'%' view my_view3 as select count(*) from t3;";
    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLCreateViewStatement stmt = (SQLCreateViewStatement) statementList.get(0);
    // print(statementList);
    Assert.assertEquals(1, statementList.size());
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);
    assertEquals("CREATE OR REPLACE DEFINER = 'ivan'@'%'\n" + "\tVIEW my_view3\n" + "AS\n" + "SELECT count(*)\n" + // 
    "FROM t3;", SQLUtils.toMySqlString(stmt));
    Assert.assertEquals("create or replace definer = 'ivan'@'%'\n" + "\tview my_view3\n" + "as\n" + "select count(*)\n" + // 
    "from t3;", SQLUtils.toMySqlString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
    // System.out.println("Tables : " + visitor.getTables());
    System.out.println("fields : " + visitor.getColumns());
    // System.out.println("coditions : " + visitor.getConditions());
    // System.out.println("orderBy : " + visitor.getOrderByColumns());
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(1, visitor.getColumns().size());
    Assert.assertEquals(0, visitor.getConditions().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("t3")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("t3", "*")));
// Assert.assertTrue(visitor.getColumns().contains(new Column("t2", "l_suppkey")));
}

Example 5 with SQLCreateViewStatement

use of com.alibaba.druid.sql.ast.statement.SQLCreateViewStatement in project druid by alibaba.

the class MySqlCreateViewTest2 method test_0.

public void test_0() throws Exception {
    String sql = "create view revenue0 as \n" + "select l_suppkey as supplier_no, sum(l_extendedprice * (1 - l_discount)) as total_revenue \n" + "from lineitem \n" + "where l_shipdate >= date '1993-01-01' and l_shipdate < date '1993-01-01' + interval '3' month \n" + "group by l_suppkey";
    MySqlStatementParser parser = new MySqlStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLCreateViewStatement stmt = (SQLCreateViewStatement) statementList.get(0);
    // print(statementList);
    Assert.assertEquals(1, statementList.size());
    MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
    stmt.accept(visitor);
    assertEquals("CREATE VIEW revenue0\n" + "AS\n" + "SELECT l_suppkey AS supplier_no, sum(l_extendedprice * (1 - l_discount)) AS total_revenue\n" + "FROM lineitem\n" + "WHERE l_shipdate >= DATE '1993-01-01'\n" + "\tAND l_shipdate < DATE '1993-01-01' + INTERVAL '3' MONTH\n" + // 
    "GROUP BY l_suppkey", SQLUtils.toMySqlString(stmt));
    Assert.assertEquals("create view revenue0\n" + "as\n" + "select l_suppkey as supplier_no, sum(l_extendedprice * (1 - l_discount)) as total_revenue\n" + "from lineitem\n" + "where l_shipdate >= date '1993-01-01'\n" + "\tand l_shipdate < date '1993-01-01' + interval '3' month\n" + // 
    "group by l_suppkey", SQLUtils.toMySqlString(stmt, SQLUtils.DEFAULT_LCASE_FORMAT_OPTION));
    // System.out.println("Tables : " + visitor.getTables());
    // System.out.println("fields : " + visitor.getColumns());
    // System.out.println("coditions : " + visitor.getConditions());
    // System.out.println("orderBy : " + visitor.getOrderByColumns());
    Assert.assertEquals(1, visitor.getTables().size());
    Assert.assertEquals(4, visitor.getColumns().size());
    Assert.assertEquals(2, visitor.getConditions().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("lineitem")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("lineitem", "l_shipdate")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("lineitem", "l_suppkey")));
}