Examples with PGSQLStatementParser com.alibaba.druid.sql.dialect.postgresql.parser.PGSQLStatementParser used on opensource projects

Example 76 with PGSQLStatementParser

use of com.alibaba.druid.sql.dialect.postgresql.parser.PGSQLStatementParser in project druid by alibaba.

the class PGSelectTest12 method test_0.

public void test_0() throws Exception {
    String sql = "WITH regional_sales AS (" + "        SELECT region, SUM(amount) AS total_sales" + "        FROM orders" + "        GROUP BY region" + "     ), top_regions AS (" + "        SELECT region" + "        FROM regional_sales" + "        WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)" + "     )\n" + "SELECT region," + "       product," + "       SUM(quantity) AS product_units," + "       SUM(amount) AS product_sales\n" + "FROM orders\n" + "WHERE region IN (SELECT region FROM top_regions)\n" + "GROUP BY region, product;";
    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(4, visitor.getColumns().size());
    Assert.assertEquals(1, visitor.getTables().size());
}

Example 77 with PGSQLStatementParser

use of com.alibaba.druid.sql.dialect.postgresql.parser.PGSQLStatementParser in project druid by alibaba.

the class PGSelectTest13 method test_0.

public void test_0() throws Exception {
    String sql = "WITH RECURSIVE t(n) AS (" + "    VALUES (1)" + "  UNION ALL" + "    SELECT n+1 FROM t WHERE n < 100" + ")\n" + "SELECT sum(n) FROM t;";
    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(1, visitor.getColumns().size());
    Assert.assertEquals(1, visitor.getTables().size());
}

Example 78 with PGSQLStatementParser

use of com.alibaba.druid.sql.dialect.postgresql.parser.PGSQLStatementParser in project druid by alibaba.

the class PGSelectTest14 method test_0.

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());
}

Example 79 with PGSQLStatementParser

use of com.alibaba.druid.sql.dialect.postgresql.parser.PGSQLStatementParser in project druid by alibaba.

the class PGDeleteTest6 method test_0.

public void test_0() throws Exception {
    String sql = "WITH moved_rows AS (" + "    DELETE FROM products" + "    WHERE" + "        \"date\" >= '2010-10-01' AND" + "        \"date\" < '2010-11-01'" + "    RETURNING *" + ")" + "INSERT INTO products_log\n" + "SELECT * FROM moved_rows;";
    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("products")));
    Assert.assertTrue(visitor.getTables().containsKey(new TableStat.Name("products")));
    Assert.assertTrue(visitor.getColumns().size() == 1);
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("products", "date")));
}

Example 80 with PGSQLStatementParser

use of com.alibaba.druid.sql.dialect.postgresql.parser.PGSQLStatementParser in project druid by alibaba.

the class PGExportParameterVisitorTest method test_pg.

public void test_pg() throws Exception {
    String sql = "select fname, count(*) from t where fid = 1 group by fname order by 1";
    PGSQLStatementParser parser = new PGSQLStatementParser(sql);
    SQLStatement stmt = parser.parseStatementList().get(0);
    List<Object> parameters = new ArrayList<Object>();
    PGExportParameterVisitor visitor = new PGExportParameterVisitor(parameters);
    stmt.accept(visitor);
    Assert.assertEquals(1, parameters.size());
    Assert.assertEquals(1, parameters.get(0));
}