Author: shawkins Date: 2012-01-25 13:40:44 -0500 (Wed, 25 Jan 2012) New Revision: 3821 Modified: branches/7.7.x/api/src/main/java/org/teiid/language/visitor/AbstractLanguageVisitor.java branches/7.7.x/api/src/main/java/org/teiid/translator/ExecutionFactory.java branches/7.7.x/build/kits/jboss-container/teiid-releasenotes.html branches/7.7.x/connectors/connector-salesforce/src/main/java/org/teiid/resource/adapter/salesforce/SalesforceConnectionImpl.java branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/SalesForceExecutionFactory.java branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/Util.java branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/QueryExecutionImpl.java branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/CriteriaVisitor.java branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/DeleteVisitor.java branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/ICriteriaVisitor.java branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/JoinQueryVisitor.java branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/SelectVisitor.java branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/UpdateVisitor.java branches/7.7.x/connectors/translator-salesforce/src/main/resources/org/teiid/translator/salesforce/i18n.properties branches/7.7.x/connectors/translator-salesforce/src/test/java/org/teiid/translator/salesforce/execution/visitors/TestVisitors.java branches/7.7.x/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml branches/7.7.x/engine/src/main/java/org/teiid/dqp/internal/datamgr/CapabilitiesConverter.java branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/capabilities/SourceCapabilities.java branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestAggregatePushdown.java Log: TEIID-1903 adding aggregation support to salesforce Modified: branches/7.7.x/api/src/main/java/org/teiid/language/visitor/AbstractLanguageVisitor.java =================================================================== --- branches/7.7.x/api/src/main/java/org/teiid/language/visitor/AbstractLanguageVisitor.java 2012-01-25 17:28:17 UTC (rev 3820) +++ branches/7.7.x/api/src/main/java/org/teiid/language/visitor/AbstractLanguageVisitor.java 2012-01-25 18:40:44 UTC (rev 3821) @@ -24,42 +24,7 @@ import java.util.Collection; -import org.teiid.language.AggregateFunction; -import org.teiid.language.AndOr; -import org.teiid.language.Argument; -import org.teiid.language.BatchedUpdates; -import org.teiid.language.Call; -import org.teiid.language.ColumnReference; -import org.teiid.language.Comparison; -import org.teiid.language.Delete; -import org.teiid.language.DerivedColumn; -import org.teiid.language.DerivedTable; -import org.teiid.language.Exists; -import org.teiid.language.ExpressionValueSource; -import org.teiid.language.Function; -import org.teiid.language.GroupBy; -import org.teiid.language.In; -import org.teiid.language.Insert; -import org.teiid.language.IsNull; -import org.teiid.language.IteratorValueSource; -import org.teiid.language.Join; -import org.teiid.language.LanguageObject; -import org.teiid.language.Like; -import org.teiid.language.Limit; -import org.teiid.language.Literal; -import org.teiid.language.NamedTable; -import org.teiid.language.Not; -import org.teiid.language.OrderBy; -import org.teiid.language.ScalarSubquery; -import org.teiid.language.SearchedCase; -import org.teiid.language.SearchedWhenClause; -import org.teiid.language.Select; -import org.teiid.language.SetClause; -import org.teiid.language.SetQuery; -import org.teiid.language.SortSpecification; -import org.teiid.language.SubqueryComparison; -import org.teiid.language.SubqueryIn; -import org.teiid.language.Update; +import org.teiid.language.*; /** @@ -148,4 +113,12 @@ public void visit(SetClause obj) {} public void visit(SearchedWhenClause obj) {} public void visit(IteratorValueSource obj) {} + @Override + public void visit(WindowFunction windowFunction) {} + @Override + public void visit(WindowSpecification windowSpecification) {} + @Override + public void visit(With obj) {} + @Override + public void visit(WithItem obj) {} } Modified: branches/7.7.x/api/src/main/java/org/teiid/translator/ExecutionFactory.java =================================================================== --- branches/7.7.x/api/src/main/java/org/teiid/translator/ExecutionFactory.java 2012-01-25 17:28:17 UTC (rev 3820) +++ branches/7.7.x/api/src/main/java/org/teiid/translator/ExecutionFactory.java 2012-01-25 18:40:44 UTC (rev 3821) @@ -522,6 +522,14 @@ public boolean supportsGroupBy() { return false; } + + /** + * Whether the source supports grouping only over a single table + * @return + */ + public boolean supportsOnlySingleTableGroupBy() { + return false; + } /** * Whether the source supports the HAVING clause Modified: branches/7.7.x/build/kits/jboss-container/teiid-releasenotes.html =================================================================== --- branches/7.7.x/build/kits/jboss-container/teiid-releasenotes.html 2012-01-25 17:28:17 UTC (rev 3820) +++ branches/7.7.x/build/kits/jboss-container/teiid-releasenotes.html 2012-01-25 18:40:44 UTC (rev 3821) @@ -26,7 +26,8 @@ </UL> <H2><A NAME="Highlights"></A>Highlights</H2> <UL> - <LI><B>Excel JDBC Translator</B> - for use with Excel using the JDBC-ODBC bridge. + <LI><B>Excel JDBC Translator</B> - for use with Excel using the JDBC-ODBC bridge. + <LI><B>Salesforce Aggregates</B> - salesforce pushdown queries now support GROUP BY, HAVING, and the standard aggregate functions. </UL> <h2><a name="Compatibility">Compatibility Issues</a></h2> Modified: branches/7.7.x/connectors/connector-salesforce/src/main/java/org/teiid/resource/adapter/salesforce/SalesforceConnectionImpl.java =================================================================== --- branches/7.7.x/connectors/connector-salesforce/src/main/java/org/teiid/resource/adapter/salesforce/SalesforceConnectionImpl.java 2012-01-25 17:28:17 UTC (rev 3820) +++ branches/7.7.x/connectors/connector-salesforce/src/main/java/org/teiid/resource/adapter/salesforce/SalesforceConnectionImpl.java 2012-01-25 18:40:44 UTC (rev 3821) @@ -29,9 +29,6 @@ import javax.resource.ResourceException; import javax.xml.datatype.XMLGregorianCalendar; import javax.xml.ws.BindingProvider; -import javax.xml.ws.handler.Handler; -import javax.xml.ws.handler.HandlerResolver; -import javax.xml.ws.handler.PortInfo; import org.apache.cxf.Bus; import org.apache.cxf.BusFactory; @@ -44,27 +41,7 @@ import org.teiid.translator.salesforce.execution.DeletedResult; import org.teiid.translator.salesforce.execution.UpdatedResult; -import com.sforce.soap.partner.CallOptions; -import com.sforce.soap.partner.DeleteResult; -import com.sforce.soap.partner.DeletedRecord; -import com.sforce.soap.partner.DescribeGlobalResult; -import com.sforce.soap.partner.DescribeSObjectResult; -import com.sforce.soap.partner.GetDeletedResult; -import com.sforce.soap.partner.GetUpdatedResult; -import com.sforce.soap.partner.InvalidFieldFault; -import com.sforce.soap.partner.InvalidIdFault; -import com.sforce.soap.partner.LoginFault; -import com.sforce.soap.partner.LoginResult; -import com.sforce.soap.partner.MalformedQueryFault; -import com.sforce.soap.partner.MruHeader; -import com.sforce.soap.partner.ObjectFactory; -import com.sforce.soap.partner.PackageVersionHeader; -import com.sforce.soap.partner.QueryOptions; -import com.sforce.soap.partner.QueryResult; -import com.sforce.soap.partner.SaveResult; -import com.sforce.soap.partner.SessionHeader; -import com.sforce.soap.partner.SforceService; -import com.sforce.soap.partner.Soap; +import com.sforce.soap.partner.*; import com.sforce.soap.partner.sobject.SObject; public class SalesforceConnectionImpl extends BasicConnection implements SalesforceConnection { Modified: branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/SalesForceExecutionFactory.java =================================================================== --- branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/SalesForceExecutionFactory.java 2012-01-25 17:28:17 UTC (rev 3820) +++ branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/SalesForceExecutionFactory.java 2012-01-25 18:40:44 UTC (rev 3821) @@ -56,7 +56,6 @@ private static final String SALESFORCE = "salesforce"; //$NON-NLS-1$ private static final String EXCLUDES = "excludes";//$NON-NLS-1$ private static final String INCLUDES = "includes";//$NON-NLS-1$ - private String connectorStateClass; private boolean auditModelFields = false; public SalesForceExecutionFactory() { @@ -67,13 +66,6 @@ setSupportedJoinCriteria(SupportedJoinCriteria.KEY); } - public String getConnectorStateClass() { - return this.connectorStateClass; - } - public void setConnectorStateClass(String connectorStateClass) { - this.connectorStateClass = connectorStateClass; - } - @TranslatorProperty(display="Audit Model Fields", advanced=true) public boolean isModelAuditFields() { return this.auditModelFields; @@ -152,8 +144,43 @@ public boolean supportsAggregatesCountStar() { return true; } + + @Override + public boolean supportsAggregatesCount() { + return true; + } + + @Override + public boolean supportsAggregatesMax() { + return true; + } @Override + public boolean supportsAggregatesMin() { + return true; + } + + @Override + public boolean supportsAggregatesSum() { + return true; + } + + @Override + public boolean supportsAggregatesAvg() { + return true; + } + + @Override + public boolean supportsGroupBy() { + return true; + } + + @Override + public boolean supportsOnlySingleTableGroupBy() { + return true; + } + + @Override public boolean supportsNotCriteria() { return true; } @@ -172,5 +199,20 @@ public boolean supportsIsNullCriteria() { return true; } - + + @Override + public boolean supportsHaving() { + return true; + } + + @Override + public int getMaxFromGroups() { + return 2; + } + + @Override + public boolean useAnsiJoin() { + return true; + } + } Modified: branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/Util.java =================================================================== --- branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/Util.java 2012-01-25 17:28:17 UTC (rev 3820) +++ branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/Util.java 2012-01-25 18:40:44 UTC (rev 3821) @@ -24,9 +24,7 @@ import java.text.SimpleDateFormat; import java.util.Date; -import org.teiid.translator.TranslatorException; - public class Util { public static String stripQutes(String id) { @@ -50,12 +48,6 @@ return result.toString(); } - public static void validateQueryLength(StringBuffer query) throws TranslatorException { - if(query.length() >= 10000) { - throw new TranslatorException(SalesForcePlugin.Util.getString("Util.query.exceeds.max.length")); //$NON-NLS-1$ - } - } - private static String timeZone; public static String getDefaultTimeZoneString() { Modified: branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/QueryExecutionImpl.java =================================================================== --- branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/QueryExecutionImpl.java 2012-01-25 17:28:17 UTC (rev 3820) +++ branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/QueryExecutionImpl.java 2012-01-25 18:40:44 UTC (rev 3821) @@ -39,6 +39,8 @@ import org.teiid.core.util.TimestampWithTimezone; import org.teiid.language.AggregateFunction; +import org.teiid.language.ColumnReference; +import org.teiid.language.Expression; import org.teiid.language.Join; import org.teiid.language.QueryExpression; import org.teiid.language.Select; @@ -67,6 +69,8 @@ public class QueryExecutionImpl implements ResultSetExecution { + private static final String AGGREGATE_RESULT = "AggregateResult"; //$NON-NLS-1$ + private static final Pattern dateTimePattern = Pattern.compile("^(?:(\\d{4}-\\d{2}-\\d{2})T)?(\\d{2}:\\d{2}:\\d{2}(?:.\\d+)?)(.*)"); //$NON-NLS-1$ private static final String SF_ID = "sf:Id"; //$NON-NLS-1$ @@ -162,18 +166,7 @@ @SuppressWarnings("unchecked") @Override public List next() throws TranslatorException, DataNotAvailableException { - List<?> result; - if (query.getProjectedQuery().getDerivedColumns().get(0) - .getExpression() instanceof AggregateFunction) { - if (results == null) { - return null; - } - result = Arrays.asList(results.getSize()); - results = null; - - } else { - result = getRow(results); - } + List<?> result = getRow(results); return result; } @@ -256,19 +249,8 @@ String sObjectName = typeElement.getFirstChild().getNodeValue(); Object[] row = new Object[visitor.getSelectSymbolCount()]; for (int j = 0; j < visitor.getSelectSymbolCount(); j++) { - Column element = visitor.getSelectSymbolMetadata(j); - AbstractMetadataRecord parent = element.getParent(); - Table table; - if(parent instanceof Table) { - table = (Table)parent; - } else { - parent = parent.getParent(); - if(parent instanceof Table) { - table = (Table)parent; - } else { - throw new TranslatorException("Could not resolve Table for column " + element.getName()); //$NON-NLS-1$ - } - } + Column element = ((ColumnReference)visitor.getSelectSymbolMetadata(j)).getMetadataObject(); + AbstractMetadataRecord table = element.getParent(); if(table.getNameInSource().equals(sObjectName)) { Integer index = visitor.getSelectSymbolIndex(sObjectName + ':' + element.getNameInSource()); // id gets dropped from the result if it is not the @@ -280,8 +262,7 @@ throw new TranslatorException(SalesForcePlugin.Util.getString("SalesforceQueryExecutionImpl.missing.field")+ element.getNameInSource()); //$NON-NLS-1$ } } else { - Object cell; - cell = sObject.getElementsByTagName("sf:" + element.getNameInSource()).item(0); //$NON-NLS-1$ + Object cell = sObject.getElementsByTagName("sf:" + element.getNameInSource()).item(0); //$NON-NLS-1$ setElementValueInColumn(j, cell, row); } } @@ -290,13 +271,16 @@ return result; } - private List<Object[]> extractDataFromFields(SObject sObject, - List<Object> fields, List<Object[]> result) throws TranslatorException { - Map<String,Integer> fieldToIndexMap = sObjectToResponseField.get(sObject.getType()); - for (int j = 0; j < visitor.getSelectSymbolCount(); j++) { - Column element = visitor.getSelectSymbolMetadata(j); + private List<Object[]> extractDataFromFields(SObject sObject, + List<Object> fields, List<Object[]> result) throws TranslatorException { + Map<String,Integer> fieldToIndexMap = sObjectToResponseField.get(sObject.getType()); + int aggCount = 0; + for (int j = 0; j < visitor.getSelectSymbolCount(); j++) { + Expression ex = visitor.getSelectSymbolMetadata(j); + if (ex instanceof ColumnReference) { + Column element = ((ColumnReference)ex).getMetadataObject(); Table table = (Table)element.getParent(); - if(table.getNameInSource().equals(sObject.getType())) { + if(table.getNameInSource().equals(sObject.getType()) || AGGREGATE_RESULT.equalsIgnoreCase(sObject.getType())) { Integer index = fieldToIndexMap.get(element.getNameInSource()); // id gets dropped from the result if it is not the // first field in the querystring. Add it back in. @@ -307,28 +291,36 @@ throw new TranslatorException(SalesForcePlugin.Util.getString("SalesforceQueryExecutionImpl.missing.field")+ element.getNameInSource()); //$NON-NLS-1$ } } else { - Object cell; - cell = getCellDatum(element, (Element)fields.get(index)); + Object cell = getCellDatum(element.getNameInSource(), element.getJavaType(), (Element)fields.get(index)); setValueInColumn(j, cell, result); } } + } else if (ex instanceof AggregateFunction) { + String name = SelectVisitor.AGG_PREFIX + (aggCount++); + Integer index = fieldToIndexMap.get(name); + if (null == index) { + throw new TranslatorException(SalesForcePlugin.Util.getString("SalesforceQueryExecutionImpl.missing.field")+ ex); //$NON-NLS-1$ + } + Object cell = getCellDatum(name, ex.getType(), (Element)fields.get(index)); + setValueInColumn(j, cell, result); } - return result; + } + return result; } private void setElementValueInColumn(int columnIndex, Object value, Object[] row) { - if(value instanceof Element) { - Element element = (Element)value; - if (!Boolean.parseBoolean(element.getAttribute(XSI_NIL))) { - if (element.getFirstChild() != null) { - row[columnIndex] = element.getFirstChild().getNodeValue(); - } else { - row[columnIndex] = ""; //$NON-NLS-1$ - } + if(value instanceof Element) { + Element element = (Element)value; + if (!Boolean.parseBoolean(element.getAttribute(XSI_NIL))) { + if (element.getFirstChild() != null) { + row[columnIndex] = element.getFirstChild().getNodeValue(); + } else { + row[columnIndex] = ""; //$NON-NLS-1$ } - } else { - row[columnIndex] = value; } + } else { + row[columnIndex] = value; + } } private void setValueInColumn(int columnIndex, Object value, List<Object[]> result) { @@ -352,8 +344,7 @@ List<Object> fields) throws TranslatorException { if (!sObjectToResponseField.containsKey(sObjectName)) { logFields(sObjectName, fields); - Map<String, Integer> responseFieldToIndexMap; - responseFieldToIndexMap = new HashMap<String, Integer>(); + Map<String, Integer> responseFieldToIndexMap = new HashMap<String, Integer>(); for (int x = 0; x < fields.size(); x++) { Element element = (Element) fields.get(x); responseFieldToIndexMap.put(element.getLocalName(), x); @@ -380,30 +371,29 @@ * TODO: the logic here should be aware of xsi:type information and use a standard conversion * library. Conversion to teiid types should then be a secondary effort - and will be automatically handled above here. */ - @SuppressWarnings("unchecked") - private Object getCellDatum(Column element, Element elem) throws TranslatorException { - if(!element.getNameInSource().equals(elem.getLocalName())) { - throw new TranslatorException(SalesForcePlugin.Util.getString("SalesforceQueryExecutionImpl.column.mismatch1") + element.getNameInSource() + SalesForcePlugin.Util.getString("SalesforceQueryExecutionImpl.column.mismatch2") + elem.getLocalName()); //$NON-NLS-1$ //$NON-NLS-2$ + private Object getCellDatum(String name, Class<?> type, Element elem) throws TranslatorException { + if(!name.equals(elem.getLocalName())) { + throw new TranslatorException(SalesForcePlugin.Util.getString("SalesforceQueryExecutionImpl.column.mismatch1") + name + SalesForcePlugin.Util.getString("SalesforceQueryExecutionImpl.column.mismatch2") + elem.getLocalName()); //$NON-NLS-1$ //$NON-NLS-2$ } if (Boolean.parseBoolean(elem.getAttribute(XSI_NIL))) { return null; } String value = elem.getTextContent(); - Object result = value; - Class type = element.getJavaType(); - - if(type.equals(String.class)) { - result = value; - } else if (!value.isEmpty()) { - result = value; - if (type.equals(java.sql.Timestamp.class) || type.equals(java.sql.Time.class)) { - if (cal == null) { - cal = Calendar.getInstance(); - } - result = parseDateTime(value, type, cal); + if (value == null) { + return null; + } + if (value.isEmpty()) { + if (type == String.class) { + return value; } + return null; + } else if (type.equals(java.sql.Timestamp.class) || type.equals(java.sql.Time.class)) { + if (cal == null) { + cal = Calendar.getInstance(); + } + return parseDateTime(value, type, cal); } - return result; + return value; } static Object parseDateTime(String value, Class<?> type, Calendar cal) Modified: branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/CriteriaVisitor.java =================================================================== --- branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/CriteriaVisitor.java 2012-01-25 17:28:17 UTC (rev 3820) +++ branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/CriteriaVisitor.java 2012-01-25 18:40:44 UTC (rev 3821) @@ -25,24 +25,12 @@ import java.util.ArrayList; import java.util.Arrays; import java.util.Calendar; -import java.util.HashMap; import java.util.Iterator; import java.util.LinkedList; import java.util.List; -import java.util.Map; import org.teiid.core.util.TimestampWithTimezone; -import org.teiid.language.AndOr; -import org.teiid.language.ColumnReference; -import org.teiid.language.Comparison; -import org.teiid.language.Expression; -import org.teiid.language.Function; -import org.teiid.language.In; -import org.teiid.language.IsNull; -import org.teiid.language.Like; -import org.teiid.language.Literal; -import org.teiid.language.NamedTable; -import org.teiid.language.Not; +import org.teiid.language.*; import org.teiid.language.Comparison.Operator; import org.teiid.language.visitor.HierarchyVisitor; import org.teiid.metadata.Column; @@ -56,7 +44,7 @@ /** * Parses Criteria in support of all of the ExecutionImpl classes. */ -public abstract class CriteriaVisitor extends HierarchyVisitor implements ICriteriaVisitor { +public class CriteriaVisitor extends HierarchyVisitor implements ICriteriaVisitor { private static final String RESTRICTEDMULTISELECTPICKLIST = "restrictedmultiselectpicklist"; //$NON-NLS-1$ private static final String MULTIPICKLIST = "multipicklist"; //$NON-NLS-1$ @@ -75,10 +63,9 @@ protected static final String CLOSE = ")"; //$NON-NLS-1$ protected RuntimeMetadata metadata; - private HashMap<Comparison.Operator, String> comparisonOperators; + protected List<String> criteriaList = new ArrayList<String>(); protected boolean hasCriteria; - protected Map<String, Column> columnElementsByName = new HashMap<String, Column>(); protected List<TranslatorException> exceptions = new ArrayList<TranslatorException>(); protected Table table; boolean onlyIDCriteria; @@ -90,13 +77,6 @@ public CriteriaVisitor( RuntimeMetadata metadata ) { this.metadata = metadata; - comparisonOperators = new HashMap<Comparison.Operator, String>(); - comparisonOperators.put(Operator.EQ, "="); //$NON-NLS-1$ - comparisonOperators.put(Operator.GE, ">="); //$NON-NLS-1$ - comparisonOperators.put(Operator.GT, ">"); //$NON-NLS-1$ - comparisonOperators.put(Operator.LE, "<="); //$NON-NLS-1$ - comparisonOperators.put(Operator.LT, "<"); //$NON-NLS-1$ - comparisonOperators.put(Operator.NE, "!="); //$NON-NLS-1$ } @Override @@ -270,19 +250,16 @@ if (lExpr instanceof Function) { parseFunction((Function)lExpr); } else { - ColumnReference left = (ColumnReference)lExpr; - Column column = left.getMetadataObject(); - String columnName = column.getNameInSource(); StringBuilder queryString = new StringBuilder(); - appendColumnReference(queryString, left); + queryString.append(getValue(lExpr, false)); queryString.append(SPACE); - queryString.append(comparisonOperators.get(compCriteria.getOperator())); + queryString.append(compCriteria.getOperator()==Operator.NE?"!=":compCriteria.getOperator()); //$NON-NLS-1$ queryString.append(' '); Expression rExp = compCriteria.getRightExpression(); queryString.append(getValue(rExp, false)); criteriaList.add(queryString.toString()); - if (columnName.equals("IsDeleted")) { //$NON-NLS-1$ + if (lExpr instanceof ColumnReference && "IsDeleted".equalsIgnoreCase(((ColumnReference)lExpr).getMetadataObject().getNameInSource())) { //$NON-NLS-1$ Literal isDeletedLiteral = (Literal)compCriteria.getRightExpression(); Boolean isDeleted = (Boolean)isDeletedLiteral.getValue(); if (isDeleted) { @@ -292,7 +269,7 @@ } } - private void appendColumnReference(StringBuilder queryString, + void appendColumnReference(StringBuilder queryString, ColumnReference ref) { queryString.append(ref.getMetadataObject().getParent().getNameInSource()); queryString.append('.'); @@ -368,11 +345,23 @@ } else { result.append(expr.toString()); } + } else if (expr instanceof AggregateFunction) { + appendAggregateFunction(result, (AggregateFunction)expr); } else { throw new RuntimeException("unknown type in SalesforceQueryExecution.getValue(): " + expr.toString()); //$NON-NLS-1$ } return result.toString(); } + + protected void appendAggregateFunction(StringBuilder result, + AggregateFunction af) { + if (af.getName().equalsIgnoreCase(SQLConstants.NonReserved.COUNT) + && (af.getExpression() == null || af.getExpression() instanceof Literal)) { + result.append("COUNT(Id)"); //$NON-NLS-1$ + } else { + result.append(af.getName() + "(" + getValue(af.getExpression(), false) + ")"); //$NON-NLS-1$ //$NON-NLS-2$ + } + } protected void loadColumnMetadata( NamedTable group ) throws TranslatorException { table = group.getMetadataObject(); @@ -442,9 +431,13 @@ return table.getNameInSource(); } - protected void addCriteriaString(StringBuffer result) { + protected void addCriteriaString(StringBuilder result) { + addCriteriaString(WHERE, result); + } + + protected void addCriteriaString(String clause, StringBuilder result) { if(hasCriteria()) { - result.append(WHERE).append(SPACE); + result.append(clause).append(SPACE); for (String string : criteriaList) { result.append(string); } Modified: branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/DeleteVisitor.java =================================================================== --- branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/DeleteVisitor.java 2012-01-25 17:28:17 UTC (rev 3820) +++ branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/DeleteVisitor.java 2012-01-25 18:40:44 UTC (rev 3821) @@ -21,8 +21,6 @@ */ package org.teiid.translator.salesforce.execution.visitors; -import java.util.Iterator; - import org.teiid.language.Delete; import org.teiid.metadata.RuntimeMetadata; import org.teiid.translator.TranslatorException; @@ -54,9 +52,9 @@ if (!exceptions.isEmpty()) { throw exceptions.get(0); } - StringBuffer result = new StringBuffer(); + StringBuilder result = new StringBuilder(); result.append(SELECT).append(SPACE); - result.append("Id").append(SPACE); + result.append("Id").append(SPACE); //$NON-NLS-1$ result.append(FROM).append(SPACE); result.append(table.getNameInSource()).append(SPACE); addCriteriaString(result); Modified: branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/ICriteriaVisitor.java =================================================================== --- branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/ICriteriaVisitor.java 2012-01-25 17:28:17 UTC (rev 3820) +++ branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/ICriteriaVisitor.java 2012-01-25 18:40:44 UTC (rev 3821) @@ -27,8 +27,6 @@ public boolean hasCriteria(); - public void setHasCriteria(boolean hasCriteria, boolean isIdCriteria); - public boolean hasOnlyIDCriteria(); public String getTableName() throws TranslatorException; Modified: branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/JoinQueryVisitor.java =================================================================== --- branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/JoinQueryVisitor.java 2012-01-25 17:28:17 UTC (rev 3820) +++ branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/JoinQueryVisitor.java 2012-01-25 18:40:44 UTC (rev 3821) @@ -12,7 +12,6 @@ import org.teiid.metadata.RuntimeMetadata; import org.teiid.metadata.Table; import org.teiid.translator.TranslatorException; -import org.teiid.translator.salesforce.Util; /** @@ -37,38 +36,23 @@ super(metadata); } - // Has to be a left outer join + // Has to be a left outer join of only 2 tables. criteria must be a compare @Override public void visit(Join join) { try { TableReference left = join.getLeftItem(); - if (left instanceof NamedTable) { - NamedTable leftGroup = (NamedTable) left; - leftTableInJoin = leftGroup.getMetadataObject(); - loadColumnMetadata(leftGroup); - } + NamedTable leftGroup = (NamedTable) left; + leftTableInJoin = leftGroup.getMetadataObject(); + loadColumnMetadata(leftGroup); TableReference right = join.getRightItem(); - if (right instanceof NamedTable) { - NamedTable rightGroup = (NamedTable) right; - rightTableInJoin = rightGroup.getMetadataObject(); - loadColumnMetadata((NamedTable) right); - } - super.visit(join); - } catch (TranslatorException ce) { - exceptions.add(ce); - } - - } - - @Override - public void visit(Comparison criteria) { - // Find the criteria that joins the two tables - Expression rExp = criteria.getRightExpression(); - if (rExp instanceof ColumnReference) { + NamedTable rightGroup = (NamedTable) right; + rightTableInJoin = rightGroup.getMetadataObject(); + loadColumnMetadata((NamedTable) right); + Comparison criteria = (Comparison) join.getCondition(); Expression lExp = criteria.getLeftExpression(); + Expression rExp = criteria.getRightExpression(); if (isIdColumn(rExp) || isIdColumn(lExp)) { - Column rColumn = ((ColumnReference) rExp).getMetadataObject(); String rTableName = rColumn.getParent().getNameInSource(); @@ -86,11 +70,13 @@ } else { // Only add the criteria to the query if it is not the join criteria. // The join criteria is implicit in the salesforce syntax. - super.visit(criteria); + super.visit(criteria); //TODO: not valid } + } else { + super.visit(criteria); //TODO: not valid } - } else { - super.visit(criteria); + } catch (TranslatorException ce) { + exceptions.add(ce); } } @@ -103,12 +89,12 @@ if (!exceptions.isEmpty()) { throw exceptions.get(0); } - StringBuffer select = new StringBuffer(); + StringBuilder select = new StringBuilder(); select.append(SELECT).append(SPACE); addSelect(leftTableInJoin.getNameInSource(), select, true); select.append(OPEN); - StringBuffer subselect = new StringBuffer(); + StringBuilder subselect = new StringBuilder(); subselect.append(SELECT).append(SPACE); addSelect(rightTableInJoin.getNameInSource(), subselect, false); subselect.append(SPACE); @@ -119,8 +105,8 @@ select.append(FROM).append(SPACE); select.append(leftTableInJoin.getNameInSource()).append(SPACE); addCriteriaString(select); + appendGroupByHaving(select); select.append(limitClause); - Util.validateQueryLength(select); return select.toString(); } @@ -128,33 +114,29 @@ return childTable.equals(leftTableInJoin); } - void addSelect(String tableNameInSource, StringBuffer result, boolean addComma) { + void addSelect(String tableNameInSource, StringBuilder result, boolean addComma) { boolean firstTime = true; for (DerivedColumn symbol : selectSymbols) { Expression expression = symbol.getExpression(); if (expression instanceof ColumnReference) { Column element = ((ColumnReference) expression).getMetadataObject(); String tableName = element.getParent().getNameInSource(); - if(!isParentToChildJoin() && tableNameInSource.equals(tableName) || - isParentToChildJoin()) { - if (!firstTime) { - result.append(", "); //$NON-NLS-1$ - } else { - firstTime = false; - } - result.append(tableName); - result.append('.'); - result.append(element.getNameInSource()); - } else { + if(!isParentToChildJoin() && !tableNameInSource.equals(tableName)) { continue; } + if (!firstTime) { + result.append(", "); //$NON-NLS-1$ + } else { + firstTime = false; + } + appendColumnReference(result, (ColumnReference) expression); } else if (expression instanceof AggregateFunction) { if (!firstTime) { result.append(", "); //$NON-NLS-1$ } else { firstTime = false; } - result.append("count()"); //$NON-NLS-1$ + appendAggregateFunction(result, (AggregateFunction)expression); } else { throw new AssertionError("Unknown select symbol type" + symbol); //$NON-NLS-1$ } Modified: branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/SelectVisitor.java =================================================================== --- branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/SelectVisitor.java 2012-01-25 17:28:17 UTC (rev 3820) +++ branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/SelectVisitor.java 2012-01-25 18:40:44 UTC (rev 3821) @@ -27,31 +27,25 @@ import java.util.List; import java.util.Map; -import org.teiid.language.AggregateFunction; -import org.teiid.language.ColumnReference; -import org.teiid.language.DerivedColumn; -import org.teiid.language.Expression; -import org.teiid.language.Limit; -import org.teiid.language.NamedTable; -import org.teiid.language.Select; -import org.teiid.metadata.AbstractMetadataRecord; +import org.teiid.language.*; import org.teiid.metadata.Column; import org.teiid.metadata.RuntimeMetadata; -import org.teiid.metadata.Table; import org.teiid.translator.TranslatorException; import org.teiid.translator.salesforce.Constants; import org.teiid.translator.salesforce.SalesForcePlugin; -import org.teiid.translator.salesforce.Util; public class SelectVisitor extends CriteriaVisitor implements IQueryProvidingVisitor { - private Map<Integer, Column> selectSymbolIndexToElement = new HashMap<Integer, Column>(); + public static final String AGG_PREFIX = "expr"; //$NON-NLS-1$ + private Map<Integer, Expression> selectSymbolIndexToElement = new HashMap<Integer, Expression>(); private Map<String, Integer> selectSymbolNameToIndex = new HashMap<String, Integer>(); private int selectSymbolCount; private int idIndex = -1; // index of the ID select symbol. protected List<DerivedColumn> selectSymbols; - protected StringBuffer limitClause = new StringBuffer(); + protected StringBuilder limitClause = new StringBuilder(); + protected StringBuilder groupByClause = new StringBuilder(); + protected StringBuilder havingClause = new StringBuilder(); private Boolean objectSupportsRetrieve; public SelectVisitor(RuntimeMetadata metadata) { @@ -59,21 +53,35 @@ } public void visit(Select query) { - super.visit(query); + super.visitNodes(query.getFrom()); + super.visitNode(query.getWhere()); + super.visitNode(query.getGroupBy()); + if (query.getHaving() != null) { + //since the base is a criteria hierarchy visitor, + //we must separately visit the having clause + //TODO: if further uses of criteria come up, we should not use hierarchy visitor as the base + Condition c = query.getHaving(); + CriteriaVisitor cv = new CriteriaVisitor(this.metadata); + cv.visitNode(c); + cv.addCriteriaString(SQLConstants.Reserved.HAVING, this.havingClause); + if (this.havingClause.length() > 0) { + this.havingClause.append(SPACE); + } + } + super.visitNode(query.getLimit()); if (query.isDistinct()) { exceptions.add(new TranslatorException(SalesForcePlugin.Util.getString("SelectVisitor.distinct.not.supported"))); //$NON-NLS-1$ } selectSymbols = query.getDerivedColumns(); selectSymbolCount = selectSymbols.size(); - Iterator<DerivedColumn> symbolIter = selectSymbols.iterator(); - int index = 0; - while (symbolIter.hasNext()) { - DerivedColumn symbol = symbolIter.next(); + int aggCount = 0; + for (int index = 0; index < selectSymbols.size(); index++) { + DerivedColumn symbol = selectSymbols.get(index); // get the name in source Expression expression = symbol.getExpression(); + selectSymbolIndexToElement.put(index, expression); if (expression instanceof ColumnReference) { Column element = ((ColumnReference) expression).getMetadataObject(); - selectSymbolIndexToElement.put(index, element); String qualifiedName = element.getParent().getNameInSource() + ':' + element.getNameInSource(); selectSymbolNameToIndex .put(qualifiedName, index); String nameInSource = element.getNameInSource(); @@ -84,12 +92,26 @@ if (nameInSource.equalsIgnoreCase("id")) { //$NON-NLS-1$ idIndex = index; } + } else if (expression instanceof AggregateFunction) { + selectSymbolNameToIndex.put(AGG_PREFIX + (aggCount++), index); } - ++index; } } @Override + public void visit(GroupBy obj) { + this.groupByClause.append("GROUP BY "); //$NON-NLS-1$ + for (Iterator<Expression> iter = obj.getElements().iterator(); iter.hasNext();) { + Expression expr = iter.next(); + this.groupByClause.append(getValue(expr, false)); + if (iter.hasNext()) { + this.groupByClause.append(", "); //$NON-NLS-1$ + } + } + this.groupByClause.append(SPACE); + } + + @Override public void visit(NamedTable obj) { try { table = obj.getMetadataObject(); @@ -120,69 +142,50 @@ if (!exceptions.isEmpty()) { throw exceptions.get(0); } - StringBuffer result = new StringBuffer(); + StringBuilder result = new StringBuilder(); result.append(SELECT).append(SPACE); addSelectSymbols(result); result.append(SPACE); result.append(FROM).append(SPACE); result.append(table.getNameInSource()).append(SPACE); addCriteriaString(result); + appendGroupByHaving(result); //result.append(orderByClause).append(SPACE); result.append(limitClause); - Util.validateQueryLength(result); return result.toString(); } - private void addSelectSymbols(StringBuffer result) throws TranslatorException { - boolean firstTime = true; - for (DerivedColumn symbol : selectSymbols) { - if (!firstTime) { + protected void appendGroupByHaving(StringBuilder result) { + result.append(this.groupByClause); + result.append(this.havingClause); + } + + private void addSelectSymbols(StringBuilder result) { + for (int i = 0; i < selectSymbols.size(); i++) { + DerivedColumn symbol = selectSymbols.get(i); + if (i > 0) { result.append(", "); //$NON-NLS-1$ - } else { - firstTime = false; } Expression expression = symbol.getExpression(); if (expression instanceof ColumnReference) { - Column element = ((ColumnReference) expression).getMetadataObject(); - AbstractMetadataRecord parent = element.getParent(); - Table table; - if(parent instanceof Table) { - table = (Table)parent; - } else { - parent = parent.getParent(); - if(parent instanceof Table) { - table = (Table)parent; - } else { - throw new TranslatorException("Could not resolve Table for column " + element.getName()); //$NON-NLS-1$ - } - } - result.append(table.getNameInSource()); - result.append('.'); - result.append(element.getNameInSource()); + appendColumnReference(result, (ColumnReference) expression); } else if (expression instanceof AggregateFunction) { - result.append("count()"); //$NON-NLS-1$ + AggregateFunction af = (AggregateFunction)expression; + appendAggregateFunction(result, af); + } else { + throw new AssertionError("Unknown select symbol type" + symbol); //$NON-NLS-1$ } } } - public int getSelectSymbolCount() { return selectSymbolCount; } - public Column getSelectSymbolMetadata(int index) { + public Expression getSelectSymbolMetadata(int index) { return selectSymbolIndexToElement.get(index); } - public Column getSelectSymbolMetadata(String name) { - Column result = null; - Integer index = selectSymbolNameToIndex.get(name); - if(null != index) { - result = selectSymbolIndexToElement.get(index); - } - return result; - } - public Integer getSelectSymbolIndex(String name) { return selectSymbolNameToIndex.get(name); } @@ -201,9 +204,9 @@ } - public String getRetrieveFieldList() throws TranslatorException { + public String getRetrieveFieldList() { assertRetrieveValidated(); - StringBuffer result = new StringBuffer(); + StringBuilder result = new StringBuilder(); addSelectSymbols(result); return result.toString(); } @@ -230,7 +233,7 @@ } public boolean canRetrieve() { - return objectSupportsRetrieve && hasOnlyIDCriteria(); + return objectSupportsRetrieve && hasOnlyIDCriteria() && this.limitClause.length() == 0 && groupByClause.length() == 0; } } Modified: branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/UpdateVisitor.java =================================================================== --- branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/UpdateVisitor.java 2012-01-25 17:28:17 UTC (rev 3820) +++ branches/7.7.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/UpdateVisitor.java 2012-01-25 18:40:44 UTC (rev 3821) @@ -52,11 +52,11 @@ public String getQuery() throws TranslatorException { if (!exceptions.isEmpty()) { - throw ((TranslatorException) exceptions.get(0)); + throw exceptions.get(0); } - StringBuffer result = new StringBuffer(); + StringBuilder result = new StringBuilder(); result.append(SELECT).append(SPACE); - result.append("Id").append(SPACE); + result.append("Id").append(SPACE); //$NON-NLS-1$ result.append(FROM).append(SPACE); result.append(table.getNameInSource()).append(SPACE); addCriteriaString(result); Modified: branches/7.7.x/connectors/translator-salesforce/src/main/resources/org/teiid/translator/salesforce/i18n.properties =================================================================== --- branches/7.7.x/connectors/translator-salesforce/src/main/resources/org/teiid/translator/salesforce/i18n.properties 2012-01-25 17:28:17 UTC (rev 3820) +++ branches/7.7.x/connectors/translator-salesforce/src/main/resources/org/teiid/translator/salesforce/i18n.properties 2012-01-25 18:40:44 UTC (rev 3821) @@ -36,5 +36,3 @@ CriteriaVisitor.function.not.literal.arg=The second parameter of the includes and excludes functions must be a literal value. CriteriaVisitor.LIKE.not.supported.on.Id=LIKE criteria are not allowed on columns of native type Id CriteriaVisitor.LIKE.not.supported.on.multiselect=LIKE criteria are not allowed on columns of native type Multi-Select Picklist - -Util.query.exceeds.max.length=Queries cannot exceed 10,000 characters Modified: branches/7.7.x/connectors/translator-salesforce/src/test/java/org/teiid/translator/salesforce/execution/visitors/TestVisitors.java =================================================================== --- branches/7.7.x/connectors/translator-salesforce/src/test/java/org/teiid/translator/salesforce/execution/visitors/TestVisitors.java 2012-01-25 17:28:17 UTC (rev 3820) +++ branches/7.7.x/connectors/translator-salesforce/src/test/java/org/teiid/translator/salesforce/execution/visitors/TestVisitors.java 2012-01-25 18:40:44 UTC (rev 3821) @@ -48,6 +48,7 @@ import org.teiid.query.sql.lang.SPParameter; import org.teiid.query.unittest.RealMetadataFactory; import org.teiid.translator.ExecutionContext; +import org.teiid.translator.TranslatorException; import org.teiid.translator.TypeFacility; import org.teiid.translator.salesforce.Constants; import org.teiid.translator.salesforce.SalesforceConnection; @@ -136,11 +137,11 @@ assertEquals("SELECT Account.id, Account.AccountName, Account.Stuff, Account.Industry FROM Account WHERE (Account.AccountName != 'foo') OR (Account.Stuff != 'bar')", visitor.getQuery().toString().trim()); //$NON-NLS-1$ } - @Test public void testCountStart() throws Exception { + @Test public void testCountStar() throws Exception { Select command = (Select)translationUtility.parseCommand("select count(*) from Account"); //$NON-NLS-1$ SelectVisitor visitor = new SelectVisitor(translationUtility.createRuntimeMetadata()); visitor.visit(command); - assertEquals("SELECT count() FROM Account", visitor.getQuery().toString().trim()); //$NON-NLS-1$ + assertEquals("SELECT COUNT(Id) FROM Account", visitor.getQuery().toString().trim()); //$NON-NLS-1$ } @Test public void testNotLike() throws Exception { @@ -150,14 +151,12 @@ assertEquals("SELECT Account.id, Account.AccountName, Account.Stuff, Account.Industry FROM Account WHERE (NOT (Account.AccountName LIKE '%foo')) OR (Account.Stuff = 'bar')", visitor.getQuery().toString().trim()); //$NON-NLS-1$ } - @Test public void testIN() throws Exception { Select command = (Select)translationUtility.parseCommand("select * from Account where Industry IN (1,2,3)"); //$NON-NLS-1$ SelectVisitor visitor = new SelectVisitor(translationUtility.createRuntimeMetadata()); visitor.visit(command); assertFalse(visitor.hasOnlyIDCriteria()); assertEquals("SELECT Account.id, Account.AccountName, Account.Stuff, Account.Industry FROM Account WHERE Account.Industry IN('1','2','3')", visitor.getQuery().toString().trim()); //$NON-NLS-1$ - } @Test public void testOnlyIDsIN() throws Exception { @@ -230,24 +229,40 @@ } @Test public void testDateTimeFormating() throws Exception { - Select command = (Select)translationUtility.parseCommand("select name from contacts where initialcontact = {ts'2003-03-11 11:42:10.5'}"); //$NON-NLS-1$ - SelectVisitor visitor = new SelectVisitor(translationUtility.createRuntimeMetadata()); - visitor.visit(command); - assertEquals("SELECT Contact.ContactName FROM Contact WHERE Contact.InitialContact = 2003-03-11T11:42:10.500-06:00", visitor.getQuery().toString().trim()); //$NON-NLS-1$ + String sql = "select name from contacts where initialcontact = {ts'2003-03-11 11:42:10.5'}"; + String source = "SELECT Contact.ContactName FROM Contact WHERE Contact.InitialContact = 2003-03-11T11:42:10.500-06:00"; + helpTest(sql, source); } - @Test public void testDateTimeFormating1() throws Exception { - Select command = (Select)translationUtility.parseCommand("select name from contacts where initialcontact in ({ts'2003-03-11 11:42:10.506'}, {ts'2003-03-11 11:42:10.8088'})"); //$NON-NLS-1$ + private void helpTest(String sql, String source) throws TranslatorException { + Select command = (Select)translationUtility.parseCommand(sql); //$NON-NLS-1$ SelectVisitor visitor = new SelectVisitor(translationUtility.createRuntimeMetadata()); visitor.visit(command); - assertEquals("SELECT Contact.ContactName FROM Contact WHERE Contact.InitialContact IN(2003-03-11T11:42:10.506-06:00,2003-03-11T11:42:10.80-06:00)", visitor.getQuery().toString().trim()); //$NON-NLS-1$ + assertEquals(source, visitor.getQuery().toString().trim()); //$NON-NLS-1$ } + + @Test public void testDateTimeFormating1() throws Exception { + String sql = "select name from contacts where initialcontact in ({ts'2003-03-11 11:42:10.506'}, {ts'2003-03-11 11:42:10.8088'})"; + String source = "SELECT Contact.ContactName FROM Contact WHERE Contact.InitialContact IN(2003-03-11T11:42:10.506-06:00,2003-03-11T11:42:10.80-06:00)"; + helpTest(sql, source); + } @Test public void testTimeFormatting() throws Exception { - Select command = (Select)translationUtility.parseCommand("select name from contacts where lasttime = {t'11:42:10'}"); //$NON-NLS-1$ - SelectVisitor visitor = new SelectVisitor(translationUtility.createRuntimeMetadata()); - visitor.visit(command); - assertEquals("SELECT Contact.ContactName FROM Contact WHERE Contact.LastTime = 11:42:10.000-06:00", visitor.getQuery().toString().trim()); //$NON-NLS-1$ + String sql = "select name from contacts where lasttime = {t'11:42:10'}"; + String source = "SELECT Contact.ContactName FROM Contact WHERE Contact.LastTime = 11:42:10.000-06:00"; + helpTest(sql, source); } + + @Test public void testAggregateSelect() throws Exception { + String sql = "select max(name), count(1) from contacts"; + String source = "SELECT MAX(Contact.ContactName), COUNT(Id) FROM Contact"; + helpTest(sql, source); + } + + @Test public void testAggregateGroupByHaving() throws Exception { + String sql = "select max(name), lasttime from contacts group by lasttime having min(InitialContact) in ({ts'2003-03-11 11:42:10.506'}, {ts'2003-03-11 11:42:10.8088'})"; + String source = "SELECT MAX(Contact.ContactName), Contact.LastTime FROM Contact GROUP BY Contact.LastTime HAVING MIN(Contact.InitialContact) IN(2003-03-11T11:42:10.506-06:00,2003-03-11T11:42:10.80-06:00)"; + helpTest(sql, source); + } } Modified: branches/7.7.x/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml =================================================================== --- branches/7.7.x/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml 2012-01-25 17:28:17 UTC (rev 3820) +++ branches/7.7.x/documentation/developer-guide/src/main/docbook/en-US/content/translator-api.xml 2012-01-25 18:40:44 UTC (rev 3821) @@ -1069,6 +1069,17 @@ </row> <row> <entry> + <para>OnlySingleTableGroupBy</para> + </entry> + <entry> + <para>GroupBy</para> + </entry> + <entry> + <para>GROUP BY is restricted to only non-join queries.</para> + </entry> + </row> + <row> + <entry> <para>Having</para> </entry> <entry> Modified: branches/7.7.x/engine/src/main/java/org/teiid/dqp/internal/datamgr/CapabilitiesConverter.java =================================================================== --- branches/7.7.x/engine/src/main/java/org/teiid/dqp/internal/datamgr/CapabilitiesConverter.java 2012-01-25 17:28:17 UTC (rev 3820) +++ branches/7.7.x/engine/src/main/java/org/teiid/dqp/internal/datamgr/CapabilitiesConverter.java 2012-01-25 18:40:44 UTC (rev 3821) @@ -95,6 +95,7 @@ tgtCaps.setCapabilitySupport(Capability.QUERY_FROM_ANSI_JOIN, srcCaps.useAnsiJoin()); tgtCaps.setCapabilitySupport(Capability.REQUIRES_CRITERIA, srcCaps.requiresCriteria()); tgtCaps.setCapabilitySupport(Capability.QUERY_GROUP_BY, srcCaps.supportsGroupBy()); + tgtCaps.setCapabilitySupport(Capability.QUERY_ONLY_SINGLE_TABLE_GROUP_BY, srcCaps.supportsOnlySingleTableGroupBy()); tgtCaps.setCapabilitySupport(Capability.QUERY_HAVING, srcCaps.supportsHaving()); tgtCaps.setCapabilitySupport(Capability.INSERT_WITH_QUERYEXPRESSION, srcCaps.supportsInsertWithQueryExpression()); tgtCaps.setCapabilitySupport(Capability.QUERY_ORDERBY_UNRELATED, srcCaps.supportsOrderByUnrelated()); Modified: branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/capabilities/SourceCapabilities.java =================================================================== --- branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/capabilities/SourceCapabilities.java 2012-01-25 17:28:17 UTC (rev 3820) +++ branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/capabilities/SourceCapabilities.java 2012-01-25 18:40:44 UTC (rev 3821) @@ -328,9 +328,9 @@ WINDOW_FUNCTION_ORDER_BY_AGGREGATES("WindowOrderByAggregates"), //$NON-NLS-1$ CRITERIA_SIMILAR, CRITERIA_LIKE_REGEX, - WINDOW_FUNCTION_DISTINCT_AGGREGATES("WindowDistinctAggregates"); //$NON-NLS-1$ + WINDOW_FUNCTION_DISTINCT_AGGREGATES("WindowDistinctAggregates"), //$NON-NLS-1$ + QUERY_ONLY_SINGLE_TABLE_GROUP_BY; - private final String toString; Capability(String toString) { Modified: branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java =================================================================== --- branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java 2012-01-25 17:28:17 UTC (rev 3820) +++ branches/7.7.x/engine/src/main/java/org/teiid/query/optimizer/relational/rules/RuleRaiseAccess.java 2012-01-25 18:40:44 UTC (rev 3821) @@ -314,6 +314,11 @@ recordDebug("cannot push group by, since group by is not supported by source", groupNode, record); //$NON-NLS-1$ return false; } + if (CapabilitiesUtil.supports(Capability.QUERY_ONLY_SINGLE_TABLE_GROUP_BY, modelID, metadata, capFinder) + && !NodeEditor.findAllNodes(groupNode, NodeConstants.Types.JOIN, NodeConstants.Types.SOURCE).isEmpty()) { + recordDebug("cannot push group by, since joined group by is not supported by source", groupNode, record); //$NON-NLS-1$ + return false; + } if (groupCols != null) { for (Expression expr : groupCols) { if (!canPushSymbol(expr, false, modelID, metadata, capFinder, record)) { Modified: branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestAggregatePushdown.java =================================================================== --- branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestAggregatePushdown.java 2012-01-25 17:28:17 UTC (rev 3820) +++ branches/7.7.x/engine/src/test/java/org/teiid/query/optimizer/TestAggregatePushdown.java 2012-01-25 18:40:44 UTC (rev 3821) @@ -1134,5 +1134,34 @@ 0 // UnionAll }); } + + @Test public void testSingleTableRestriction() throws Exception { + String sql = "select count(*) from bqt1.smallb, bqt1.smalla"; + BasicSourceCapabilities bsc = getAggregateCapabilities(); + bsc.setCapabilitySupport(Capability.QUERY_ONLY_SINGLE_TABLE_GROUP_BY, true); + // Plan query + ProcessorPlan plan = TestOptimizer.helpPlan(sql, + RealMetadataFactory.exampleBQTCached(), + null, new DefaultCapabilitiesFinder(bsc), + new String[] {"SELECT 1 FROM bqt1.smallb AS g_0, bqt1.smalla AS g_1"}, + TestOptimizer.ComparisonMode.EXACT_COMMAND_STRING ); + + TestOptimizer.checkNodeTypes(plan, new int[] { + 1, // Access + 0, // DependentAccess + 0, // DependentSelect + 0, // DependentProject + 0, // DupRemove + 1, // Grouping + 0, // Join + 0, // MergeJoin + 0, // Null + 0, // PlanExecution + 1, // Project + 0, // Select + 0, // Sort + 0 // UnionAll + }); + } }