Hi, I managed to setup the H2 Dev environment (really easy, hat tip to
Thomas) and patch the org.h2.command.dml.Select class to address the gaps I
was talking about earlier.
Pls review the patch attached with a simple test class. It has some dead
simple getter method fixes. If this looks good, I hope you guys can
integrate it with the H2 Trunk.
Thanks!
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
Index: src/main/org/h2/command/dml/TestSelectAstFixes.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>windows-1252
===================================================================
--- src/main/org/h2/command/dml/TestSelectAstFixes.java (revision )
+++ src/main/org/h2/command/dml/TestSelectAstFixes.java (revision )
@@ -0,0 +1,96 @@
+package org.h2.command.dml;
+
+import org.h2.Driver;
+import org.h2.command.Parser;
+import org.h2.engine.Session;
+import org.h2.expression.Expression;
+import org.h2.expression.Subquery;
+import org.h2.jdbc.JdbcConnection;
+import org.h2.table.Table;
+
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.List;
+
+/*
+ * Author: Ashwin Jayaprakash / Date: 4/22/13 / Time: 4:40 PM
+ */
+public class TestSelectAstFixes {
+ public static void main(String[] args) throws SQLException {
+ Driver.load();
+ Connection c = DriverManager.getConnection("jdbc:h2:mem:test");
+
+ Statement s = c.createStatement();
+
+ s.execute("create table if not exists people_crm" +
+ " (crm_id varchar(256) primary key, id varchar(256), name varchar(256), age int)");
+
+ JdbcConnection h2c = (JdbcConnection) c;
+ Session session = (Session) h2c.getSession();
+ Parser parser = new Parser(session);
+
+ testQueries(parser);
+ }
+
+ private static void testQueries(Parser parser) {
+ String q = "select distinct name, count(*), min(age), max(age)/3, upper(name)" +
+ " from people_crm" +
+ " where age < 100" +
+ " group by name" +
+ " having count(*) > 100 and name like '%'" +
+ " order by upper(name)" +
+ " limit 5 offset 1";
+ parseAndPrint(parser, q);
+
+ System.out.println("--------------");
+ q = "select * from people_crm";
+ parseAndPrint(parser, q);
+
+ System.out.println("--------------");
+ q = "select p1.age, p2.age from people_crm as p1, people_crm as p2 where p1.age < p2.age";
+ parseAndPrint(parser, q);
+
+ System.out.println("--------------");
+ q = "select age, count(*) from (select * from people_crm where name like 'abc%') as q where q.age > 10";
+ parseAndPrint(parser, q);
+ }
+
+ private static void parseAndPrint(Parser parser, String q) {
+ Subquery subquery = (Subquery) parser.parseExpression(q);
+
+ Select select = (Select) subquery.getQuery();
+
+ java.util.Set<Table> tables = select.getTables();
+ boolean distinct = select.isDistinctQuery();
+ List<Expression> projections = select.getProjections();
+ Expression where = select.getCondition();
+ List<Expression> groups = select.getGroupBy();
+ Expression having = select.getHaving();
+ List<SelectOrderBy> sorts = select.getOrderList();
+ Expression limit = select.getLimit();
+ Expression offset = select.getOffset();
+
+ System.out.println("Testing:\n" + q);
+
+ System.out.println("\nFull AST:\n" + select.getPlanSQL());
+
+ System.out.println("\nComponents:");
+ System.out.println("Tables:\n" + tables);
+ System.out.println("\nDistinct:\n" + distinct);
+ System.out.println("\nProjections:\n" + projections);
+ System.out.println("\nWhere:\n" + where);
+ System.out.println("\nGroups:\n" + groups);
+ System.out.println("\nHaving:\n" + having);
+ System.out.print("\nSorts:\n");
+ if (sorts != null) {
+ for (SelectOrderBy sort : sorts) {
+ System.out.print(sort.getSQL() + " ");
+ }
+ System.out.println();
+ }
+ System.out.println("\nLimit:\n" + limit);
+ System.out.println("\nOffset:\n" + offset);
+ }
+}
Index: src/main/org/h2/command/dml/Select.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>windows-1252
===================================================================
--- src/main/org/h2/command/dml/Select.java (revision 4750)
+++ src/main/org/h2/command/dml/Select.java (revision )
@@ -6,10 +6,6 @@
*/
package org.h2.command.dml;
-import java.util.ArrayList;
-import java.util.Arrays;
-import java.util.HashMap;
-import java.util.HashSet;
import org.h2.api.Trigger;
import org.h2.command.CommandInterface;
import org.h2.constant.ErrorCode;
@@ -17,28 +13,13 @@
import org.h2.engine.Constants;
import org.h2.engine.Database;
import org.h2.engine.Session;
-import org.h2.expression.Comparison;
-import org.h2.expression.ConditionAndOr;
-import org.h2.expression.Expression;
-import org.h2.expression.ExpressionColumn;
-import org.h2.expression.ExpressionVisitor;
-import org.h2.expression.Parameter;
-import org.h2.expression.Wildcard;
+import org.h2.expression.*;
import org.h2.index.Cursor;
import org.h2.index.Index;
import org.h2.index.IndexType;
import org.h2.message.DbException;
-import org.h2.result.LocalResult;
-import org.h2.result.ResultInterface;
-import org.h2.result.ResultTarget;
-import org.h2.result.Row;
-import org.h2.result.SearchRow;
-import org.h2.result.SortOrder;
-import org.h2.table.Column;
-import org.h2.table.ColumnResolver;
-import org.h2.table.IndexColumn;
-import org.h2.table.Table;
-import org.h2.table.TableFilter;
+import org.h2.result.*;
+import org.h2.table.*;
import org.h2.util.New;
import org.h2.util.StatementBuilder;
import org.h2.util.StringUtils;
@@ -47,6 +28,8 @@
import org.h2.value.ValueArray;
import org.h2.value.ValueNull;
+import java.util.*;
+
/**
* This class represents a simple SELECT statement.
*
@@ -128,6 +111,18 @@
}
public ArrayList<Expression> getGroupBy() {
+ if (group == null && groupByExpression != null) {
+ ArrayList<Expression> list = new ArrayList<Expression>(groupByExpression.length);
+ int i = 0;
+ for (boolean isGroup : groupByExpression) {
+ if (isGroup) {
+ list.add(expressions.get(i));
+ }
+ i++;
+ }
+ return list;
+ }
+
return group;
}
@@ -1090,10 +1085,34 @@
this.having = having;
}
+ public List<Expression> getProjections(){
+ return (visibleColumnCount < expressions.size()) ? expressions.subList(0, visibleColumnCount) : expressions;
+ }
+
+ public Expression getCondition() {
+ return condition;
+ }
+
public Expression getHaving() {
+ if (having == null && havingIndex >= 0) {
+ return expressions.get(havingIndex);
+ }
+
return having;
}
+ public boolean isGroupQuery() {
+ return isGroupQuery;
+ }
+
+ public boolean isDistinctQuery() {
+ return distinct;
+ }
+
+ public ArrayList<SelectOrderBy> getOrderList() {
+ return orderList;
+ }
+
public int getColumnCount() {
return visibleColumnCount;
}
@@ -1276,5 +1295,4 @@
public SortOrder getSortOrder() {
return sort;
}
-
}
