svn commit: r981640 - in /ofbiz/trunk/framework: entity/src/org/ofbiz/entity/sql/ sql/src/org/ofbiz/sql/ sql/src/org/ofbiz/sql/test/

[doogie-3]

Author: doogie
Date: Mon Aug  2 17:44:20 2010
New Revision: 981640

URL: http://svn.apache.org/viewvc?rev=981640&view=rev
Log:
FEATURE: Start of union support; parsing isn't tested yet.

Added:
    ofbiz/trunk/framework/sql/src/org/ofbiz/sql/SelectGroup.java
      - copied, changed from r981639, ofbiz/trunk/framework/sql/src/org/ofbiz/sql/SQLSelect.java
    ofbiz/trunk/framework/sql/src/org/ofbiz/sql/Unioned.java
Modified:
    ofbiz/trunk/framework/entity/src/org/ofbiz/entity/sql/EntityPlanner.java
    ofbiz/trunk/framework/sql/src/org/ofbiz/sql/Parser.jj
    ofbiz/trunk/framework/sql/src/org/ofbiz/sql/SQLSelect.java
    ofbiz/trunk/framework/sql/src/org/ofbiz/sql/test/GoodParseAll.sql

Modified: ofbiz/trunk/framework/entity/src/org/ofbiz/entity/sql/EntityPlanner.java
URL: http://svn.apache.org/viewvc/ofbiz/trunk/framework/entity/src/org/ofbiz/entity/sql/EntityPlanner.java?rev=981640&r1=981639&r2=981640&view=diff
==============================================================================
--- ofbiz/trunk/framework/entity/src/org/ofbiz/entity/sql/EntityPlanner.java (original)
+++ ofbiz/trunk/framework/entity/src/org/ofbiz/entity/sql/EntityPlanner.java Mon Aug  2 17:44:20 2010
@@ -42,6 +42,7 @@ import org.ofbiz.sql.NumberValue;
 import org.ofbiz.sql.OrderByItem;
 import org.ofbiz.sql.Planner;
 import org.ofbiz.sql.Relation;
+import org.ofbiz.sql.SelectGroup;
 import org.ofbiz.sql.SQLDelete;
 import org.ofbiz.sql.SQLInsert;
 import org.ofbiz.sql.SQLSelect;
@@ -51,6 +52,7 @@ import org.ofbiz.sql.StaticValue;
 import org.ofbiz.sql.StringValue;
 import org.ofbiz.sql.Table;
 import org.ofbiz.sql.TableName;
+import org.ofbiz.sql.Unioned;
 import org.ofbiz.sql.Value;
 
 public class EntityPlanner extends Planner<EntityPlanner, EntityCondition, EntityDeletePlan, EntityInsertPlan, EntitySelectPlan, EntityUpdatePlan, EntityViewPlan> {
@@ -68,20 +70,25 @@ public class EntityPlanner extends Plann
 
     public EntitySelectPlan planSelect(SQLSelect selectStatement) {
         DynamicViewEntity dve = new DynamicViewEntity();
-        Table table = selectStatement.getTable();
+        Unioned unioned = selectStatement.getUnioned();
+        if (unioned.getOperator() != null || unioned.getNext() != null) {
+            throw new IllegalArgumentException("union views not yet supported");
+        }
+        SelectGroup selectGroup = unioned.getGroup();
+        Table table = selectGroup.getTable();
         addMember(dve, table.getTableName());
         addJoined(dve, table.getTableName().getAlias(), table.getJoined());
-        for (FieldAll fieldAll: selectStatement.getFieldAlls()) {
+        for (FieldAll fieldAll: selectGroup.getFieldAlls()) {
             dve.addAliasAll(fieldAll.getAlias(), null);
         }
         for (Relation relation: selectStatement.getRelations().values()) {
             dve.addRelation(relation.getType(), relation.getTitle(), relation.getEntityName(), buildKeyMaps(relation));
         }
-        List<String> groupBy = selectStatement.getGroupBy();
+        List<String> groupBy = selectGroup.getGroupBy();
         if (groupBy == null) {
             groupBy = Collections.emptyList();
         }
-        for (FieldDef fieldDef: selectStatement.getFieldDefs()) {
+        for (FieldDef fieldDef: selectGroup.getFieldDefs()) {
             addFieldDef(dve, groupBy, fieldDef.getAlias(), fieldDef);
         }
         List<String> orderBy;
@@ -93,7 +100,7 @@ public class EntityPlanner extends Plann
                 orderBy.add(orderByItem.toString());
             }
         }
-        return new EntitySelectPlan(dve, plan(selectStatement.getWhereCondition()), plan(selectStatement.getHavingCondition()), orderBy);
+        return new EntitySelectPlan(dve, plan(selectGroup.getWhereCondition()), plan(selectGroup.getHavingCondition()), orderBy);
     }
 
     public EntityUpdatePlan planUpdate(SQLUpdate updateStatement) {

Modified: ofbiz/trunk/framework/sql/src/org/ofbiz/sql/Parser.jj
URL: http://svn.apache.org/viewvc/ofbiz/trunk/framework/sql/src/org/ofbiz/sql/Parser.jj?rev=981640&r1=981639&r2=981640&view=diff
==============================================================================
--- ofbiz/trunk/framework/sql/src/org/ofbiz/sql/Parser.jj (original)
+++ ofbiz/trunk/framework/sql/src/org/ofbiz/sql/Parser.jj Mon Aug  2 17:44:20 2010
@@ -104,7 +104,10 @@ TOKEN: {
     | <HAVING: "HAVING">
     | <GROUP: "GROUP">
     | <ORDER: "ORDER">
-//  | <UNION: "UNION">
+    | <INTERSECT: "INTERSECT">
+    | <EXCEPT: "EXCEPT">
+    | <UNION: "UNION">
+    | <ALL: "ALL">
     | <BY: "BY">
     | <ON: "ON">
     | <USING: "USING">
@@ -299,7 +302,51 @@ private SQLView View(): {
     name=NamePart() <AS> sqlSelect=Select() { return new SQLView(name, sqlSelect); }
 }
 
+private SelectGroup SelectGroup(): {
+    boolean isDistinct = false;
+    List<String> groupBy = null;
+    Map<String, FieldDef> fieldDefs = FastMap.newInstance();
+    List<FieldAll> fieldAlls = FastList.newInstance();
+    Set<String> fieldAllAliases = FastSet.newInstance();
+    Table table;
+    Condition whereCondition = null, havingCondition = null;
+} {
+    <SELECT> (<DISTINCT> { isDistinct = true; })? (
+        SelectField(fieldDefs, fieldAlls, fieldAllAliases)
+        ( <COMMA> SelectField(fieldDefs, fieldAlls, fieldAllAliases) )*
+    )
+    <FROM> table=Table()
+    ( <WHERE> whereCondition=ConditionExpression() )?
+    ( <HAVING> havingCondition=ConditionExpression() )?
+    ( <GROUP> <BY> groupBy=FieldList() )?
+    { return new SelectGroup(isDistinct, fieldAlls, fieldDefs, table, whereCondition, havingCondition, groupBy); }
+}
+
+private Unioned.Operator UnionOperator(): {
+} {
+      <UNION>
+      ( <ALL> { return Unioned.Operator.UNION_ALL; } )?
+      { return Unioned.Operator.UNION; }
+    | <INTERSECT>
+      ( <ALL> { return Unioned.Operator.INTERSECT_ALL; } )?
+      { return Unioned.Operator.INTERSECT; }
+    | <EXCEPT>
+      ( <ALL> { return Unioned.Operator.EXCEPT_ALL; } )?
+      { return Unioned.Operator.EXCEPT; }
+}
+
+private Unioned Unioned(): {
+    Unioned.Operator operator;
+    SelectGroup selectGroup;
+    Unioned next = null;
+} {
+    operator=UnionOperator() selectGroup=SelectGroup() ( next=Unioned() )?
+    { return new Unioned(operator, selectGroup, next); }
+}
+
 private SQLSelect Select(): {
+    boolean hadEarlyRelations = false;
+    Unioned unioned = null;
     boolean isDistinct = false;
     List<OrderByItem> orderBy = null;
     List<String> groupBy = null;
@@ -316,14 +363,16 @@ private SQLSelect Select(): {
         ( <COMMA> SelectField(fieldDefs, fieldAlls, fieldAllAliases) )*
     )
     <FROM> table=Table()
-    ( <RELATION> Relation(relations) )*
+    ( LOOKAHEAD(1) <RELATION> Relation(relations) { hadEarlyRelations = true; } )*
     ( <WHERE> whereCondition=ConditionExpression() )?
     ( <HAVING> havingCondition=ConditionExpression() )?
     ( <GROUP> <BY> groupBy=FieldList() )?
+    ( unioned=Unioned() )?
+    ( LOOKAHEAD(1, {!hadEarlyRelations}) <RELATION> Relation(relations) )*
     ( <ORDER> <BY> orderBy=OrderByList() )?
     ( <OFFSET> offset=Integer() )?
     ( <LIMIT> limit=Integer() )?
-    { return new SQLSelect(isDistinct, fieldAlls, fieldDefs, table, relations, whereCondition, havingCondition, groupBy, orderBy, offset, limit); }
+    { return new SQLSelect(new SelectGroup(isDistinct, fieldAlls, fieldDefs, table, whereCondition, havingCondition, groupBy), unioned, relations, orderBy, offset, limit); }
 }
 
 private void Relation(Map<String, Relation> relations): {

Modified: ofbiz/trunk/framework/sql/src/org/ofbiz/sql/SQLSelect.java
URL: http://svn.apache.org/viewvc/ofbiz/trunk/framework/sql/src/org/ofbiz/sql/SQLSelect.java?rev=981640&r1=981639&r2=981640&view=diff
==============================================================================
--- ofbiz/trunk/framework/sql/src/org/ofbiz/sql/SQLSelect.java (original)
+++ ofbiz/trunk/framework/sql/src/org/ofbiz/sql/SQLSelect.java Mon Aug  2 17:44:20 2010
@@ -25,27 +25,21 @@ import java.util.Map;
 import org.ofbiz.base.util.StringUtil;
 
 public final class SQLSelect extends SQLStatement<SQLSelect> implements InsertSource {
-    private final boolean isDistinct;
-    private final List<FieldAll> fieldAlls;
-    private final Map<String, FieldDef> fieldDefs;
-    private final Table table;
+    private final SelectGroup selectGroup;
+    private final Unioned unioned;
     private final Map<String, Relation> relations;
-    private final Condition whereCondition;
-    private final Condition havingCondition;
     private final int offset;
     private final int limit;
     private final List<OrderByItem> orderBy;
-    private final List<String> groupBy;
 
     public SQLSelect(boolean isDistinct, List<FieldAll> fieldAlls, Map<String, FieldDef> fieldDefs, Table table, Map<String, Relation> relations, Condition whereCondition, Condition havingCondition, List<String> groupBy, List<OrderByItem> orderBy, int offset, int limit) {
-        this.isDistinct = isDistinct;
-        this.fieldAlls = checkEmpty(fieldAlls);
-        this.fieldDefs = checkEmpty(fieldDefs);
-        this.table = table;
+        this(new SelectGroup(isDistinct, fieldAlls, fieldDefs, table, whereCondition, havingCondition, groupBy), null, relations, orderBy, offset, limit);
+    }
+
+    public SQLSelect(SelectGroup selectGroup, Unioned unioned, Map<String, Relation> relations, List<OrderByItem> orderBy, int offset, int limit) {
+        this.selectGroup = selectGroup;
+        this.unioned = unioned;
         this.relations = checkEmpty(relations);
-        this.whereCondition = whereCondition;
-        this.havingCondition = havingCondition;
-        this.groupBy = groupBy;
         this.orderBy = orderBy;
         this.offset = offset;
         this.limit = limit;
@@ -55,38 +49,18 @@ public final class SQLSelect extends SQL
         visitor.visit(this);
     }
 
-    public boolean getIsDistinct() {
-        return isDistinct;
-    }
-
-    public Collection<FieldAll> getFieldAlls() {
-        return fieldAlls;
+    public SelectGroup getSelectGroup() {
+        return selectGroup;
     }
 
-    public Collection<FieldDef> getFieldDefs() {
-        return fieldDefs.values();
-    }
-
-    public Table getTable() {
-        return table;
+    public Unioned getUnioned() {
+        return unioned;
     }
 
     public Map<String, Relation> getRelations() {
         return relations;
     }
 
-    public Condition getWhereCondition() {
-        return whereCondition;
-    }
-
-    public Condition getHavingCondition() {
-        return havingCondition;
-    }
-
-    public List<String> getGroupBy() {
-        return groupBy;
-    }
-
     public List<OrderByItem> getOrderBy() {
         return orderBy;
     }
@@ -105,51 +79,23 @@ public final class SQLSelect extends SQL
         }
 
         SQLSelect other = (SQLSelect) o;
-        return isDistinct == other.isDistinct
-            && equalsHelper(fieldAlls, other.fieldAlls)
-            && equalsHelper(fieldDefs, other.fieldDefs)
-            && table.equals(other.table)
+        return selectGroup.equals(other.selectGroup)
+            && equalsHelper(unioned, other.unioned)
             && equalsHelper(relations, other.relations)
-            && equalsHelper(whereCondition, other.whereCondition)
-            && equalsHelper(havingCondition, other.havingCondition)
             && offset == other.offset
             && limit == other.limit
-            && equalsHelper(groupBy, other.groupBy)
             && equalsHelper(orderBy, other.orderBy)
         ;
     }
 
     public StringBuilder appendTo(StringBuilder sb) {
-        sb.append("SELECT");
-        if (isDistinct) {
-            sb.append(" DISTINCT");
+        selectGroup.appendTo(sb);
+        if (unioned != null) {
+            unioned.appendTo(sb);
         }
-        if (fieldAlls != null) {
-            StringUtil.appendTo(sb, fieldAlls, " ", null, ",");
-        }
-        if (fieldAlls != null && fieldDefs != null) {
-            sb.append(',');
-        }
-        if (fieldDefs != null) {
-            StringUtil.appendTo(sb, fieldDefs.values(), " ", null, ",");
-        }
-        sb.append(" FROM ");
-        table.appendTo(sb);
         if (relations != null) {
             StringUtil.appendTo(sb, relations.values(), " ", null, null);
         }
-        if (whereCondition != null) {
-            sb.append(" WHERE ");
-            whereCondition.appendTo(sb);
-        }
-        if (havingCondition != null) {
-            sb.append(" HAVING ");
-            havingCondition.appendTo(sb);
-        }
-        if (groupBy != null) {
-            sb.append(" GROUP BY ");
-            StringUtil.append(sb, groupBy, null, null, ", ");
-        }
         if (orderBy != null) {
             sb.append(" ORDER BY ");
             StringUtil.append(sb, orderBy, null, null, ", ");

Copied: ofbiz/trunk/framework/sql/src/org/ofbiz/sql/SelectGroup.java (from r981639, ofbiz/trunk/framework/sql/src/org/ofbiz/sql/SQLSelect.java)
URL: http://svn.apache.org/viewvc/ofbiz/trunk/framework/sql/src/org/ofbiz/sql/SelectGroup.java?p2=ofbiz/trunk/framework/sql/src/org/ofbiz/sql/SelectGroup.java&p1=ofbiz/trunk/framework/sql/src/org/ofbiz/sql/SQLSelect.java&r1=981639&r2=981640&rev=981640&view=diff
==============================================================================
--- ofbiz/trunk/framework/sql/src/org/ofbiz/sql/SQLSelect.java (original)
+++ ofbiz/trunk/framework/sql/src/org/ofbiz/sql/SelectGroup.java Mon Aug  2 17:44:20 2010
@@ -24,35 +24,23 @@ import java.util.Map;
 
 import org.ofbiz.base.util.StringUtil;
 
-public final class SQLSelect extends SQLStatement<SQLSelect> implements InsertSource {
+public final class SelectGroup extends Atom {
     private final boolean isDistinct;
     private final List<FieldAll> fieldAlls;
     private final Map<String, FieldDef> fieldDefs;
     private final Table table;
-    private final Map<String, Relation> relations;
     private final Condition whereCondition;
     private final Condition havingCondition;
-    private final int offset;
-    private final int limit;
-    private final List<OrderByItem> orderBy;
     private final List<String> groupBy;
 
-    public SQLSelect(boolean isDistinct, List<FieldAll> fieldAlls, Map<String, FieldDef> fieldDefs, Table table, Map<String, Relation> relations, Condition whereCondition, Condition havingCondition, List<String> groupBy, List<OrderByItem> orderBy, int offset, int limit) {
+    public SelectGroup(boolean isDistinct, List<FieldAll> fieldAlls, Map<String, FieldDef> fieldDefs, Table table, Condition whereCondition, Condition havingCondition, List<String> groupBy) {
         this.isDistinct = isDistinct;
         this.fieldAlls = checkEmpty(fieldAlls);
         this.fieldDefs = checkEmpty(fieldDefs);
         this.table = table;
-        this.relations = checkEmpty(relations);
         this.whereCondition = whereCondition;
         this.havingCondition = havingCondition;
         this.groupBy = groupBy;
-        this.orderBy = orderBy;
-        this.offset = offset;
-        this.limit = limit;
-    }
-
-    public void accept(Visitor visitor) {
-        visitor.visit(this);
     }
 
     public boolean getIsDistinct() {
@@ -71,10 +59,6 @@ public final class SQLSelect extends SQL
         return table;
     }
 
-    public Map<String, Relation> getRelations() {
-        return relations;
-    }
-
     public Condition getWhereCondition() {
         return whereCondition;
     }
@@ -87,35 +71,19 @@ public final class SQLSelect extends SQL
         return groupBy;
     }
 
-    public List<OrderByItem> getOrderBy() {
-        return orderBy;
-    }
-
-    public int getOffset() {
-        return offset;
-    }
-
-    public int getLimit() {
-        return limit;
-    }
-
     public boolean equals(Object o) {
-        if (!(o instanceof SQLSelect)) {
+        if (!(o instanceof SelectGroup)) {
             return false;
         }
 
-        SQLSelect other = (SQLSelect) o;
+        SelectGroup other = (SelectGroup) o;
         return isDistinct == other.isDistinct
             && equalsHelper(fieldAlls, other.fieldAlls)
             && equalsHelper(fieldDefs, other.fieldDefs)
             && table.equals(other.table)
-            && equalsHelper(relations, other.relations)
             && equalsHelper(whereCondition, other.whereCondition)
             && equalsHelper(havingCondition, other.havingCondition)
-            && offset == other.offset
-            && limit == other.limit
             && equalsHelper(groupBy, other.groupBy)
-            && equalsHelper(orderBy, other.orderBy)
         ;
     }
 
@@ -135,9 +103,6 @@ public final class SQLSelect extends SQL
         }
         sb.append(" FROM ");
         table.appendTo(sb);
-        if (relations != null) {
-            StringUtil.appendTo(sb, relations.values(), " ", null, null);
-        }
         if (whereCondition != null) {
             sb.append(" WHERE ");
             whereCondition.appendTo(sb);
@@ -150,17 +115,6 @@ public final class SQLSelect extends SQL
             sb.append(" GROUP BY ");
             StringUtil.append(sb, groupBy, null, null, ", ");
         }
-        if (orderBy != null) {
-            sb.append(" ORDER BY ");
-            StringUtil.append(sb, orderBy, null, null, ", ");
-        }
-        if (offset != -1) {
-            sb.append(" OFFSET ").append(offset);
-        }
-        if (limit != -1) {
-            sb.append(" LIMIT ").append(limit);
-        }
-        sb.append(';');
         return sb;
     }
 }

Added: ofbiz/trunk/framework/sql/src/org/ofbiz/sql/Unioned.java
URL: http://svn.apache.org/viewvc/ofbiz/trunk/framework/sql/src/org/ofbiz/sql/Unioned.java?rev=981640&view=auto
==============================================================================
--- ofbiz/trunk/framework/sql/src/org/ofbiz/sql/Unioned.java (added)
+++ ofbiz/trunk/framework/sql/src/org/ofbiz/sql/Unioned.java Mon Aug  2 17:44:20 2010
@@ -0,0 +1,63 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+package org.ofbiz.sql;
+
+public final class Unioned extends Atom {
+    public enum Operator { UNION, UNION_ALL, INTERSECT, INTERSECT_ALL, EXCEPT, EXCEPT_ALL };
+
+    private final Operator operator;
+    private final SelectGroup group;
+    private final Unioned next;
+
+    public Unioned(Operator operator, SelectGroup group, Unioned next) {
+        this.operator = operator;
+        this.group = group;
+        this.next = next;
+    }
+
+    public Operator getOperator() {
+        return operator;
+    }
+
+    public SelectGroup getGroup() {
+        return group;
+    }
+
+    public Unioned getNext() {
+        return next;
+    }
+
+    public boolean equals(Object o) {
+        if (o instanceof Unioned) {
+            Unioned other = (Unioned) o;
+            return operator.equals(other.operator) && group.equals(other.group) && equalsHelper(next, other.next);
+        } else {
+            return false;
+        }
+    }
+
+    public StringBuilder appendTo(StringBuilder sb) {
+        sb.append(' ').append(operator.toString().replace('_', ' ')).append(' ');
+        group.appendTo(sb);
+        if (next != null) {
+            next.appendTo(sb);
+        }
+        return sb;
+    }
+}

Modified: ofbiz/trunk/framework/sql/src/org/ofbiz/sql/test/GoodParseAll.sql
URL: http://svn.apache.org/viewvc/ofbiz/trunk/framework/sql/src/org/ofbiz/sql/test/GoodParseAll.sql?rev=981640&r1=981639&r2=981640&view=diff
==============================================================================
--- ofbiz/trunk/framework/sql/src/org/ofbiz/sql/test/GoodParseAll.sql (original)
+++ ofbiz/trunk/framework/sql/src/org/ofbiz/sql/test/GoodParseAll.sql Mon Aug  2 17:44:20 2010
@@ -33,10 +33,6 @@ FROM
     Party a LEFT JOIN Person b USING partyId
     LEFT JOIN PartyGroup c ON b.partyId = c.partyId
     JOIN PartyRole d ON c.partyId = d.partyId AND c.partyId = d.partyId
-RELATION TYPE one TITLE MainA Person MAP partyId
-RELATION TITLE MainB Person MAP partyId
-RELATION TYPE one Person MAP partyId
-RELATION PartyGroup MAP partyId
 WHERE
     a.partyTypeId = 'PERSON'
     AND
@@ -52,6 +48,10 @@ WHERE
     
 HAVING
     b.firstName LIKE '%foo%'
+RELATION TYPE one TITLE MainA Person MAP partyId
+RELATION TITLE MainB Person MAP partyId
+RELATION TYPE one Person MAP partyId
+RELATION PartyGroup MAP partyId
 ORDER BY
     LOWER(lastName), firstName, -birthDate
 OFFSET 5