public void test_0() throws Exception {
String sql =
"select id, name, beanId, \"algLable.id\", \"algLable.name\""
+ //
", \"algLable.schemaName\", \"algLable.tableName\", \"algLable.fieldName\""
+ //
" from cnres.function_select_algmodule_rule() "
+ //
" as a( id text,name text,beanId text, \"algLable.id\" text,\"algLable.name\" text"
+ //
", \"algLable.schemaName\" text,\"algLable.tableName\" text, \"algLable.fieldName\" text)";
PGSQLStatementParser parser = new PGSQLStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
print(statementList);
Assert.assertEquals(1, statementList.size());
PGSchemaStatVisitor visitor = new PGSchemaStatVisitor();
statemen.accept(visitor);
System.out.println("Tables : " + visitor.getTables());
System.out.println("fields : " + visitor.getColumns());
System.out.println("coditions : " + visitor.getConditions());
Assert.assertEquals(8, visitor.getColumns().size());
Assert.assertEquals(0, visitor.getTables().size());
}
public void test_0() throws Exception {
String sql =
"WITH RECURSIVE included_parts(sub_part, part, quantity) AS ("
+ " SELECT sub_part, part, quantity FROM parts WHERE part = 'our_product'"
+ " UNION ALL"
+ " SELECT p.sub_part, p.part, p.quantity"
+ " FROM included_parts pr, parts p"
+ " WHERE p.part = pr.sub_part"
+ " )\n"
+ "SELECT sub_part, SUM(quantity) as total_quantity\n"
+ "FROM included_parts\n"
+ "GROUP BY sub_part";
PGSQLStatementParser parser = new PGSQLStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
// print(statementList);
Assert.assertEquals(1, statementList.size());
PGSchemaStatVisitor visitor = new PGSchemaStatVisitor();
statemen.accept(visitor);
// System.out.println("Tables : " + visitor.getTables());
// System.out.println("fields : " + visitor.getColumns());
// System.out.println("coditions : " + visitor.getConditions());
Assert.assertEquals(5, visitor.getColumns().size());
Assert.assertEquals(2, visitor.getTables().size());
}
public void test_0() throws Exception {
String sql =
"CREATE TABLE products ("
+ //
" product_no integer,"
+ //
" name text,"
+ //
" price numeric DEFAULT 9.99"
+ //
");";
PGSQLStatementParser parser = new PGSQLStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
// print(statementList);
Assert.assertEquals(1, statementList.size());
PGSchemaStatVisitor visitor = new PGSchemaStatVisitor();
statemen.accept(visitor);
// System.out.println("Tables : " + visitor.getTables());
// System.out.println("fields : " + visitor.getColumns());
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("products")));
Assert.assertTrue(
visitor.getTables().get(new TableStat.Name("products")).getCreateCount() == 1);
Assert.assertTrue(visitor.getColumns().size() == 3);
}
public void test_0() throws Exception {
String sql = "INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';";
PGSQLStatementParser parser = new PGSQLStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
print(statementList);
Assert.assertEquals(1, statementList.size());
PGSchemaStatVisitor visitor = new PGSchemaStatVisitor();
statemen.accept(visitor);
System.out.println("Tables : " + visitor.getTables());
System.out.println("fields : " + visitor.getColumns());
System.out.println("coditions : " + visitor.getConditions());
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("films")));
Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("tmp_films")));
Assert.assertEquals(2, visitor.getColumns().size());
Assert.assertTrue(
visitor.getColumns().contains(new TableStat.Column("tmp_films", "date_prod")));
Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("tmp_films", "*")));
}
public void test_0() throws Exception {
String sql =
"select id, name, parameters, \"algModuleRule.id\", \"algModuleRule.name\""
+ //
", \"algModuleRule.assembleType\", \"algModuleRule.algmIds\", \"algModuleRule.subAlgmIds\""
+ //
", \"algModuleRule.algmRatio\", \"algModuleRule.algmMaxRatio\", \"algModuleRule.objectType\""
+ //
", \"cateRule.id\", \"cateRule.name\", \"cateRule.assembleType\", \"cateRule.algmIds\""
+ //
", \"cateRule.algmRatio\", \"cateRule.algmMaxRatio\", \"appObject.id\",\"appObject.name\""
+ //
", \"appObject.beanId\" "
+ //
"from cnres.function_select_irecom_scene()"
+ //
" as a( id text,name text,parameters text, \"algModuleRule.id\" text,\"algModuleRule.name\" text"
+ //
" , \"algModuleRule.assembleType\" text,\"algModuleRule.algmIds\" text"
+ //
" , \"algModuleRule.subAlgmIds\" text,\"algModuleRule.algmRatio\" text"
+ //
" , \"algModuleRule.algmMaxRatio\" text, \"algModuleRule.objectType\" text"
+ //
" , \"cateRule.id\" text, \"cateRule.name\" text, \"cateRule.assembleType\" text"
+ //
" , \"cateRule.algmIds\" text, \"cateRule.algmRatio\" text, \"cateRule.algmMaxRatio\" text"
+ //
" , \"appObject.id\" text,\"appObject.name\" text, \"appObject.beanId\" text)";
PGSQLStatementParser parser = new PGSQLStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
// print(statementList);
Assert.assertEquals(1, statementList.size());
PGSchemaStatVisitor visitor = new PGSchemaStatVisitor();
statemen.accept(visitor);
// System.out.println("Tables : " + visitor.getTables());
// System.out.println("fields : " + visitor.getColumns());
// System.out.println("coditions : " + visitor.getConditions());
Assert.assertEquals(20, visitor.getColumns().size());
Assert.assertEquals(0, visitor.getTables().size());
}
public void test_1() throws Exception {
String sql = "(select * from a) union select * from b";
PGSQLStatementParser parser = new PGSQLStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
// print(statementList);
Assert.assertEquals(1, statementList.size());
assertTrue(statemen instanceof PGSelectStatement);
PGSelectStatement select = (PGSelectStatement) statemen;
assertTrue(select.getSelect().getQuery() instanceof SQLUnionQuery);
SQLUnionQuery unionQuery = (SQLUnionQuery) select.getSelect().getQuery();
assertTrue(unionQuery.getLeft() instanceof SQLSelectQueryBlock);
assertTrue(unionQuery.getRight() instanceof SQLSelectQueryBlock);
SQLSelectQueryBlock leftQueryBlock = (SQLSelectQueryBlock) unionQuery.getLeft();
assertTrue(leftQueryBlock.isParenthesized());
}
public void test_0() throws Exception {
String sql = "select pkvalue from dbtpktable where tablename = 'taturvisit' for update";
PGSQLStatementParser parser = new PGSQLStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
print(statementList);
Assert.assertEquals(1, statementList.size());
PGSchemaStatVisitor visitor = new PGSchemaStatVisitor();
statemen.accept(visitor);
System.out.println("Tables : " + visitor.getTables());
System.out.println("fields : " + visitor.getColumns());
System.out.println("coditions : " + visitor.getConditions());
Assert.assertEquals(2, visitor.getColumns().size());
Assert.assertEquals(1, visitor.getTables().size());
}
public void test_0() throws Exception {
String sql = "SELECT * FROM t1 NATURAL INNER JOIN t2;";
PGSQLStatementParser parser = new PGSQLStatementParser(sql);
List<SQLStatement> statementList = parser.parseStatementList();
SQLStatement statemen = statementList.get(0);
print(statementList);
Assert.assertEquals(1, statementList.size());
PGSchemaStatVisitor visitor = new PGSchemaStatVisitor();
statemen.accept(visitor);
System.out.println("Tables : " + visitor.getTables());
System.out.println("fields : " + visitor.getColumns());
System.out.println("coditions : " + visitor.getConditions());
Assert.assertEquals(0, visitor.getColumns().size());
Assert.assertEquals(2, visitor.getTables().size());
}