Examples with PGSchemaStatVisitor com.alibaba.druid.sql.dialect.postgresql.visitor.PGSchemaStatVisitor used on opensource projects

Example 91 with PGSchemaStatVisitor

use of com.alibaba.druid.sql.dialect.postgresql.visitor.PGSchemaStatVisitor 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 92 with PGSchemaStatVisitor

use of com.alibaba.druid.sql.dialect.postgresql.visitor.PGSchemaStatVisitor 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 stmt = statementList.get(0);
    // print(statementList);
    assertEquals(1, statementList.size());
    PGSchemaStatVisitor visitor = new PGSchemaStatVisitor();
    stmt.accept(visitor);
    System.out.println("Tables : " + visitor.getTables());
    System.out.println("fields : " + visitor.getColumns());
    // System.out.println("coditions : " + visitor.getConditions());
    assertEquals(3, visitor.getColumns().size());
    assertEquals(1, visitor.getTables().size());
}

Example 93 with PGSchemaStatVisitor

use of com.alibaba.druid.sql.dialect.postgresql.visitor.PGSchemaStatVisitor in project druid by alibaba.

the class PGSelectTest16 method test_0.

public void test_0() throws Exception {
    String sql = "WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS (" + "        SELECT g.id, g.link, g.data, 1," + "          ARRAY[g.id]," + "          false" + "        FROM graph g" + "      UNION ALL" + "        SELECT g.id, g.link, g.data, sg.depth + 1," + "          path || g.id," + "          g.id = ANY(path)" + "        FROM graph g, search_graph sg" + "        WHERE g.id = sg.link AND NOT cycle" + ")" + "SELECT * FROM search_graph;";
    PGSQLStatementParser parser = new PGSQLStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    System.out.println(stmt);
    assertEquals(1, statementList.size());
    PGSchemaStatVisitor visitor = new PGSchemaStatVisitor();
    stmt.accept(visitor);
    System.out.println("Tables : " + visitor.getTables());
    System.out.println("fields : " + visitor.getColumns());
    // System.out.println("coditions : " + visitor.getConditions());
    assertEquals(3, visitor.getColumns().size());
    assertEquals(1, visitor.getTables().size());
    assertTrue(visitor.getColumns().contains(new TableStat.Column("graph", "id")));
    assertTrue(visitor.getColumns().contains(new TableStat.Column("graph", "link")));
    assertTrue(visitor.getColumns().contains(new TableStat.Column("graph", "data")));
// assertTrue(visitor.getColumns().contains(new TableStat.Column("search_graph", "depth")));
// assertTrue(visitor.getColumns().contains(new TableStat.Column("graph", "path")));
// assertTrue(visitor.getColumns().contains(new TableStat.Column("search_graph", "link")));
// assertTrue(visitor.getColumns().contains(new TableStat.Column("graph", "cycle")));
}

Example 94 with PGSchemaStatVisitor

use of com.alibaba.druid.sql.dialect.postgresql.visitor.PGSchemaStatVisitor in project druid by alibaba.

the class PGInsertTest14 method test_0.

public void test_0() throws Exception {
    String sql = "insert into wf_task_geom\n" + "    (task_id,geom_in_gds,polygon,lseg)\n" + "     values                     \n" + "    (?,ST_GeomFromText(?,4326),?,?)";
    PGSQLStatementParser parser = new PGSQLStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    // print(statementList);
    assertEquals(1, statementList.size());
    PGSchemaStatVisitor visitor = new PGSchemaStatVisitor();
    stmt.accept(visitor);
    System.out.println("Tables : " + visitor.getTables());
    System.out.println("fields : " + visitor.getColumns());
    // System.out.println("coditions : " + visitor.getConditions());
    assertTrue(visitor.containsTable("wf_task_geom"));
    assertEquals(4, visitor.getColumns().size());
    // 
    // assertTrue(visitor.getColumns().contains(new TableStat.Column("distributors", "did")));
    // assertTrue(visitor.getColumns().contains(new TableStat.Column("distributors", "dname")));
    assertEquals("INSERT INTO wf_task_geom (task_id, geom_in_gds, polygon, lseg)\n" + "VALUES (?, ST_GeomFromText(?, 4326), ?, ?)", stmt.toString());
}

Example 95 with PGSchemaStatVisitor

use of com.alibaba.druid.sql.dialect.postgresql.visitor.PGSchemaStatVisitor in project druid by alibaba.

the class PGInsertTest7 method test_0.

public void test_0() throws Exception {
    String sql = "insert into test(k,v) values (1,2) ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v; ";
    PGSQLStatementParser parser = new PGSQLStatementParser(sql);
    List<SQLStatement> statementList = parser.parseStatementList();
    SQLStatement stmt = statementList.get(0);
    // print(statementList);
    Assert.assertEquals(1, statementList.size());
    PGSchemaStatVisitor visitor = new PGSchemaStatVisitor();
    stmt.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("test")));
    Assert.assertEquals(2, visitor.getColumns().size());
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("test", "k")));
    Assert.assertTrue(visitor.getColumns().contains(new TableStat.Column("test", "v")));
    assertEquals("INSERT INTO test (k, v)\n" + "VALUES (1, 2)\n" + "ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v;", stmt.toString());
}