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

Example 6 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 7 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 8 with SQLCreateViewStatement

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

the class MySqlCreateViewTest method test_0.

public void test_0() throws Exception {
    String sql = // 
    "CREATE OR REPLACE VIEW view_name AS\n" + // 
    "SELECT fname\n" + // 
    "FROM table_name\n" + "WHERE fid = ?";
    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(2, visitor.getColumns().size());
    Assert.assertEquals(1, visitor.getConditions().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("table_name")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("table_name", "fid")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("table_name", "fname")));
}

Example 9 with SQLCreateViewStatement

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

the class MySqlCreateViewTest1 method test_0.

public void test_0() throws Exception {
    String sql = "CREATE ALGORITHM=UNDEFINED DEFINER=root@localhost SQL SECURITY DEFINER VIEW view_audit_enroll AS " + "SELECT a.enroll_id AS 'enrollId', case when ((SELECT audit FROM actvty_audit WHERE enroll_id = a.enroll_id AND rankjurisdiction = 1) > 0) then \"县站已审核\" else NULL end AS 'countyAudit', case when ((SELECT audit FROM actvty_audit WHERE enroll_id = a.enroll_id AND rankjurisdiction = 2) > 0) then \"市馆已审核\" else NULL end AS 'cityAudit', case when ((SELECT audit FROM actvty_audit WHERE enroll_id = a.enroll_id AND rankjurisdiction = 3) > 0) then \"省馆已审核\" else NULL end AS 'provinceAudit' FROM actvty_audit a GROUP BY a.enroll_id";
    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 ALGORITHM = UNDEFINED\n" + "\tDEFINER = 'root'@'localhost'\n" + "\tSQL SECURITY = DEFINER\n" + "\tVIEW view_audit_enroll\n" + "AS\n" + "SELECT a.enroll_id AS \"enrollId\"\n" + "\t, CASE \n" + "\t\tWHEN (\n" + "\t\t\t\tSELECT audit\n" + "\t\t\t\tFROM actvty_audit\n" + "\t\t\t\tWHERE enroll_id = a.enroll_id\n" + "\t\t\t\t\tAND rankjurisdiction = 1\n" + "\t\t\t) > 0\n" + "\t\tTHEN '县站已审核'\n" + "\t\tELSE NULL\n" + "\tEND AS \"countyAudit\"\n" + "\t, CASE \n" + "\t\tWHEN (\n" + "\t\t\t\tSELECT audit\n" + "\t\t\t\tFROM actvty_audit\n" + "\t\t\t\tWHERE enroll_id = a.enroll_id\n" + "\t\t\t\t\tAND rankjurisdiction = 2\n" + "\t\t\t) > 0\n" + "\t\tTHEN '市馆已审核'\n" + "\t\tELSE NULL\n" + "\tEND AS \"cityAudit\"\n" + "\t, CASE \n" + "\t\tWHEN (\n" + "\t\t\t\tSELECT audit\n" + "\t\t\t\tFROM actvty_audit\n" + "\t\t\t\tWHERE enroll_id = a.enroll_id\n" + "\t\t\t\t\tAND rankjurisdiction = 3\n" + "\t\t\t) > 0\n" + "\t\tTHEN '省馆已审核'\n" + "\t\tELSE NULL\n" + "\tEND AS \"provinceAudit\"\n" + "FROM actvty_audit a\n" + // 
    "GROUP BY a.enroll_id", SQLUtils.toMySqlString(stmt));
    Assert.assertEquals("create algorithm = UNDEFINED\n" + "\tdefiner = 'root'@'localhost'\n" + "\tsql security = DEFINER\n" + "\tview view_audit_enroll\n" + "as\n" + "select a.enroll_id as \"enrollId\"\n" + "\t, case \n" + "\t\twhen (\n" + "\t\t\t\tselect audit\n" + "\t\t\t\tfrom actvty_audit\n" + "\t\t\t\twhere enroll_id = a.enroll_id\n" + "\t\t\t\t\tand rankjurisdiction = 1\n" + "\t\t\t) > 0\n" + "\t\tthen '县站已审核'\n" + "\t\telse null\n" + "\tend as \"countyAudit\"\n" + "\t, case \n" + "\t\twhen (\n" + "\t\t\t\tselect audit\n" + "\t\t\t\tfrom actvty_audit\n" + "\t\t\t\twhere enroll_id = a.enroll_id\n" + "\t\t\t\t\tand rankjurisdiction = 2\n" + "\t\t\t) > 0\n" + "\t\tthen '市馆已审核'\n" + "\t\telse null\n" + "\tend as \"cityAudit\"\n" + "\t, case \n" + "\t\twhen (\n" + "\t\t\t\tselect audit\n" + "\t\t\t\tfrom actvty_audit\n" + "\t\t\t\twhere enroll_id = a.enroll_id\n" + "\t\t\t\t\tand rankjurisdiction = 3\n" + "\t\t\t) > 0\n" + "\t\tthen '省馆已审核'\n" + "\t\telse null\n" + "\tend as \"provinceAudit\"\n" + "from actvty_audit a\n" + // 
    "group by a.enroll_id", 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(3, visitor.getColumns().size());
    Assert.assertEquals(2, visitor.getConditions().size());
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("actvty_audit")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("actvty_audit", "audit")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("actvty_audit", "enroll_id")));
}

Example 10 with SQLCreateViewStatement

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

the class MySqlCreateViewTest3 method test_0.

public void test_0() throws Exception {
    String sql = "create algorithm = merge view my_view2(col1, col2) as select * from t2 with check option;\n";
    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 ALGORITHM = merge\n" + "\tVIEW my_view2 (\n" + "\tcol1, \n" + "\tcol2\n" + ")\n" + "AS\n" + "SELECT *\n" + "FROM t2\n" + // 
    "WITH CHECK OPTION;", SQLUtils.toMySqlString(stmt));
    Assert.assertEquals("create algorithm = merge\n" + "\tview my_view2 (\n" + "\tcol1, \n" + "\tcol2\n" + ")\n" + "as\n" + "select *\n" + "from t2\n" + // 
    "with check option;", 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("t2")));
    Assert.assertTrue(visitor.getColumns().contains(new Column("t2", "*")));
// Assert.assertTrue(visitor.getColumns().contains(new Column("t2", "l_suppkey")));
}