Author: shawkins Date: 2011-11-30 14:08:23 -0500 (Wed, 30 Nov 2011) New Revision: 3709 Modified: branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/SalesForceExecutionFactory.java branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/Util.java branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/QueryExecutionImpl.java branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/CriteriaVisitor.java branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/JoinQueryVisitor.java branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/SelectVisitor.java branches/7.6.x/connectors/translator-salesforce/src/test/java/org/teiid/translator/salesforce/execution/TestQueryExecutionImpl.java branches/7.6.x/connectors/translator-salesforce/src/test/java/org/teiid/translator/salesforce/execution/visitors/TestVisitors.java Log: TEIID-1852 fix for a host of sf issues Modified: branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/SalesForceExecutionFactory.java =================================================================== --- branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/SalesForceExecutionFactory.java 2011-11-29 19:41:45 UTC (rev 3708) +++ branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/SalesForceExecutionFactory.java 2011-11-30 19:08:23 UTC (rev 3709) @@ -167,5 +167,10 @@ public boolean supportsCompareCriteriaOrdered() { return true; } + + @Override + public boolean supportsIsNullCriteria() { + return true; + } } Modified: branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/Util.java =================================================================== --- branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/Util.java 2011-11-29 19:41:45 UTC (rev 3708) +++ branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/Util.java 2011-11-30 19:08:23 UTC (rev 3709) @@ -21,8 +21,8 @@ */ package org.teiid.translator.salesforce; -import java.text.DateFormat; import java.text.SimpleDateFormat; +import java.util.Date; import org.teiid.translator.TranslatorException; @@ -56,16 +56,14 @@ } } - public static SimpleDateFormat getSalesforceDateTimeFormat() { - return new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS"); //$NON-NLS-1$ - } + private static String timeZone; - public static SimpleDateFormat getTimeZoneOffsetFormat() { - return new SimpleDateFormat("Z"); //$NON-NLS-1$ + public static String getDefaultTimeZoneString() { + if (timeZone == null) { + String s = new SimpleDateFormat("Z").format(new Date(0)); //$NON-NLS-1$ + timeZone = s.substring(0, 3) + ':' + s.substring(3, 5); + } + return timeZone; } - public static DateFormat getSalesforceDateFormat() { - return new SimpleDateFormat("yyyy-MM-dd"); //$NON-NLS-1$ - } - } Modified: branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/QueryExecutionImpl.java =================================================================== --- branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/QueryExecutionImpl.java 2011-11-29 19:41:45 UTC (rev 3708) +++ branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/QueryExecutionImpl.java 2011-11-30 19:08:23 UTC (rev 3709) @@ -21,18 +21,23 @@ */ package org.teiid.translator.salesforce.execution; +import java.sql.Time; import java.sql.Timestamp; -import java.text.ParseException; import java.util.ArrayList; import java.util.Arrays; +import java.util.Calendar; import java.util.Date; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; +import java.util.TimeZone; +import java.util.regex.Matcher; +import java.util.regex.Pattern; import javax.resource.ResourceException; +import org.teiid.core.util.TimestampWithTimezone; import org.teiid.language.AggregateFunction; import org.teiid.language.Join; import org.teiid.language.QueryExpression; @@ -51,7 +56,6 @@ import org.teiid.translator.TranslatorException; import org.teiid.translator.salesforce.SalesForcePlugin; import org.teiid.translator.salesforce.SalesforceConnection; -import org.teiid.translator.salesforce.Util; import org.teiid.translator.salesforce.execution.visitors.JoinQueryVisitor; import org.teiid.translator.salesforce.execution.visitors.SelectVisitor; import org.w3c.dom.Element; @@ -63,6 +67,8 @@ public class QueryExecutionImpl implements ResultSetExecution { + 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$ private static final String SF_TYPE = "sf:type"; //$NON-NLS-1$ @@ -78,7 +84,6 @@ private RuntimeMetadata metadata; private ExecutionContext context; - private SelectVisitor visitor; @@ -103,6 +108,8 @@ private int topResultIndex = 0; + private Calendar cal; + public QueryExecutionImpl(QueryExpression command, SalesforceConnection connection, RuntimeMetadata metadata, ExecutionContext context) { this.connection = connection; this.metadata = metadata; @@ -355,7 +362,7 @@ } } - private void logFields(String sObjectName, List<Object> fields) throws TranslatorException { + private void logFields(String sObjectName, List<Object> fields) { if (!LogManager.isMessageToBeRecorded(LogConstants.CTX_CONNECTOR, MessageLevel.DETAIL)) { return; } @@ -369,53 +376,75 @@ } + /** + * 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$ } + if (Boolean.parseBoolean(elem.getAttribute(XSI_NIL))) { + return null; + } String value = elem.getTextContent(); - Object result = null; + Object result = value; Class type = element.getJavaType(); if(type.equals(String.class)) { result = value; - } - else if (type.equals(Boolean.class)) { - result = Boolean.valueOf(value); - } else if (type.equals(Double.class)) { - if (null != value) { - if(!value.isEmpty()) { - result = Double.valueOf(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); } - } else if (type.equals(Integer.class)) { - if (null != value) { - if(!value.isEmpty()) { - result = Integer.valueOf(value); + } + return result; + } + + static Object parseDateTime(String value, Class<?> type, Calendar cal) + throws TranslatorException { + try { + Matcher m = dateTimePattern.matcher(value); + if (m.matches()) { + String date = m.group(1); + String time = m.group(2); + String timeZone = m.group(3); + Date d = null; + if (date == null) { + //sql times don't care about fractional seconds + int milli = time.lastIndexOf('.'); + if (milli > 0) { + time = time.substring(0, milli); + } + d = Time.valueOf(time); + } else { + d = Timestamp.valueOf(date + " " + time); //$NON-NLS-1$ } - } - } else if (type.equals(java.sql.Date.class)) { - if (null != value) { - if(!value.isEmpty()) { - result = java.sql.Date.valueOf(value); - } - } - } else if (type.equals(java.sql.Timestamp.class)) { - if (null != value) { - if(!value.isEmpty()) { - try { - Date date = Util.getSalesforceDateTimeFormat().parse(value); - result = new Timestamp(date.getTime()); - } catch (ParseException e) { - throw new TranslatorException(e, SalesForcePlugin.Util.getString("SalesforceQueryExecutionImpl.datatime.parse") + value); //$NON-NLS-1$ + TimeZone tz = null; + if (timeZone != null) { + if (timeZone.equals("Z")) { //$NON-NLS-1$ + tz = TimeZone.getTimeZone("GMT"); //$NON-NLS-1$ + } else if (timeZone.contains(":")) { //$NON-NLS-1$ + tz = TimeZone.getTimeZone("GMT" + timeZone); //$NON-NLS-1$ + } else { + //this is probably an exceptional case + tz = TimeZone.getTimeZone(timeZone); } + cal.setTimeZone(tz); + } else { + cal = null; } + return TimestampWithTimezone.create(d, TimeZone.getDefault(), cal, type); } - } else { - result = value; + throw new TranslatorException(SalesForcePlugin.Util.getString("SalesforceQueryExecutionImpl.datatime.parse") + value); //$NON-NLS-1$ + } catch (IllegalArgumentException e) { + throw new TranslatorException(e, SalesForcePlugin.Util.getString("SalesforceQueryExecutionImpl.datatime.parse") + value); //$NON-NLS-1$ } - return result; } private boolean isSObject(Element element) { Modified: branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/CriteriaVisitor.java =================================================================== --- branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/CriteriaVisitor.java 2011-11-29 19:41:45 UTC (rev 3708) +++ branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/CriteriaVisitor.java 2011-11-30 19:08:23 UTC (rev 3709) @@ -24,18 +24,21 @@ import java.sql.Timestamp; 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; @@ -80,7 +83,7 @@ protected Table table; boolean onlyIDCriteria; protected Boolean queryAll = Boolean.FALSE; - + // support for invoking a retrieve when possible. protected In idInCriteria = null; @@ -98,33 +101,28 @@ @Override public void visit( Comparison criteria ) { - super.visit(criteria); - try { - addCompareCriteria(criteriaList, criteria); - boolean isAcceptableID = (Operator.EQ == criteria.getOperator() && isIdColumn(criteria.getLeftExpression())); - setHasCriteria(true, isAcceptableID); - if (isAcceptableID) { - this.idInCriteria = new In(criteria.getLeftExpression(), Arrays.asList(criteria.getRightExpression()), false); - } - } catch (TranslatorException e) { - exceptions.add(e); + addCompareCriteria(criteria); + boolean isAcceptableID = (Operator.EQ == criteria.getOperator() && isIdColumn(criteria.getLeftExpression())); + setHasCriteria(true, isAcceptableID); + if (isAcceptableID) { + this.idInCriteria = new In(criteria.getLeftExpression(), Arrays.asList(criteria.getRightExpression()), false); } } - + + public void visit(IsNull obj) { + visit(new Comparison(obj.getExpression(), new Literal(null, obj.getExpression().getType()), obj.isNegated()?Comparison.Operator.NE:Comparison.Operator.EQ)); + } + @Override public void visit( Like criteria ) { - try { - if (isIdColumn(criteria.getLeftExpression())) { - TranslatorException e = new TranslatorException(SalesForcePlugin.Util.getString("CriteriaVisitor.LIKE.not.supported.on.Id")); //$NON-NLS-1$ - exceptions.add(e); - } - if (isMultiSelectColumn(criteria.getLeftExpression())) { - TranslatorException e = new TranslatorException(SalesForcePlugin.Util.getString("CriteriaVisitor.LIKE.not.supported.on.multiselect")); //$NON-NLS-1$ - exceptions.add(e); - } - } catch (TranslatorException e) { + if (isIdColumn(criteria.getLeftExpression())) { + TranslatorException e = new TranslatorException(SalesForcePlugin.Util.getString("CriteriaVisitor.LIKE.not.supported.on.Id")); //$NON-NLS-1$ exceptions.add(e); } + if (isMultiSelectColumn(criteria.getLeftExpression())) { + TranslatorException e = new TranslatorException(SalesForcePlugin.Util.getString("CriteriaVisitor.LIKE.not.supported.on.multiselect")); //$NON-NLS-1$ + exceptions.add(e); + } boolean negated = criteria.isNegated(); criteria.setNegated(false); criteriaList.add(criteria.toString()); @@ -135,7 +133,7 @@ // don't check if it's ID, Id LIKE '123%' still requires a query setHasCriteria(true, false); } - + @Override public void visit(AndOr obj) { List<String> savedCriteria = new LinkedList<String>(); @@ -170,23 +168,19 @@ @Override public void visit( In criteria ) { - try { - Expression lExpr = criteria.getLeftExpression(); - if (lExpr instanceof ColumnReference) { - ColumnReference cr = (ColumnReference)lExpr; - Column column = cr.getMetadataObject(); - if (column != null && (MULTIPICKLIST.equalsIgnoreCase(column.getNativeType()) || RESTRICTEDMULTISELECTPICKLIST.equalsIgnoreCase(column.getNativeType()))) { - appendMultiselectIn(column, criteria); - } else { - appendCriteria(criteria); - } + Expression lExpr = criteria.getLeftExpression(); + if (lExpr instanceof ColumnReference) { + ColumnReference cr = (ColumnReference)lExpr; + Column column = cr.getMetadataObject(); + if (column != null && (MULTIPICKLIST.equalsIgnoreCase(column.getNativeType()) || RESTRICTEDMULTISELECTPICKLIST.equalsIgnoreCase(column.getNativeType()))) { + appendMultiselectIn(column, criteria); } else { - appendCriteria(criteria); + appendCriteria(criteria); } - setHasCriteria(true, isIdColumn(criteria.getLeftExpression())); - } catch (TranslatorException e) { - exceptions.add(e); + } else { + appendCriteria(criteria); } + setHasCriteria(true, isIdColumn(criteria.getLeftExpression())); } public void parseFunction( Function func ) { @@ -215,7 +209,7 @@ } private void appendMultiselectIn( Column column, - In criteria ) throws TranslatorException { + In criteria ) { StringBuffer result = new StringBuffer(); result.append(column.getNameInSource()).append(SPACE); if (criteria.isNegated()) { @@ -270,9 +264,8 @@ } criterion.append(CLOSE); } - - protected void addCompareCriteria( List criteriaList, - Comparison compCriteria ) throws TranslatorException { + + protected void addCompareCriteria(Comparison compCriteria ) { Expression lExpr = compCriteria.getLeftExpression(); if (lExpr instanceof Function) { parseFunction((Function)lExpr); @@ -280,38 +273,13 @@ ColumnReference left = (ColumnReference)lExpr; Column column = left.getMetadataObject(); String columnName = column.getNameInSource(); - StringBuffer queryString = new StringBuffer(); - queryString.append(column.getParent().getNameInSource()); - queryString.append('.'); - queryString.append(columnName).append(SPACE); + StringBuilder queryString = new StringBuilder(); + appendColumnReference(queryString, left); + queryString.append(SPACE); queryString.append(comparisonOperators.get(compCriteria.getOperator())); queryString.append(' '); Expression rExp = compCriteria.getRightExpression(); - if(rExp instanceof Literal) { - Literal literal = (Literal)rExp; - if (column.getJavaType().equals(Boolean.class)) { - queryString.append(((Boolean)literal.getValue()).toString()); - } else if (column.getJavaType().equals(java.sql.Timestamp.class)) { - Timestamp datetime = (java.sql.Timestamp)literal.getValue(); - String value = Util.getSalesforceDateTimeFormat().format(datetime); - String zoneValue = Util.getTimeZoneOffsetFormat().format(datetime); - queryString.append(value).append(zoneValue.subSequence(0, 3)).append(':').append(zoneValue.subSequence(3, 5)); - } else if (column.getJavaType().equals(java.sql.Time.class)) { - String value = Util.getSalesforceDateTimeFormat().format((java.sql.Time)literal.getValue()); - queryString.append(value); - } else if (column.getJavaType().equals(java.sql.Date.class)) { - String value = Util.getSalesforceDateFormat().format((java.sql.Date)literal.getValue()); - queryString.append(value); - } else { - queryString.append(compCriteria.getRightExpression().toString()); - } - } else if(rExp instanceof ColumnReference) { - ColumnReference right = (ColumnReference)lExpr; - column = left.getMetadataObject(); - columnName = column.getNameInSource(); - queryString.append(columnName); - } - + queryString.append(getValue(rExp, false)); criteriaList.add(queryString.toString()); if (columnName.equals("IsDeleted")) { //$NON-NLS-1$ @@ -324,56 +292,86 @@ } } - private void appendCriteria( In criteria ) throws TranslatorException { + private void appendColumnReference(StringBuilder queryString, + ColumnReference ref) { + queryString.append(ref.getMetadataObject().getParent().getNameInSource()); + queryString.append('.'); + queryString.append(ref.getMetadataObject().getNameInSource()); + } + + private void appendCriteria( In criteria ) { StringBuffer queryString = new StringBuffer(); Expression leftExp = criteria.getLeftExpression(); if(isIdColumn(leftExp)) { idInCriteria = criteria; } - queryString.append(getValue(leftExp)); + queryString.append(getValue(leftExp, false)); queryString.append(' '); if (criteria.isNegated()) { queryString.append("NOT "); //$NON-NLS-1$ } queryString.append("IN"); //$NON-NLS-1$ queryString.append('('); - Column column = ((ColumnReference)criteria.getLeftExpression()).getMetadataObject(); - boolean timeColumn = isTimeColumn(column); - boolean first = true; - Iterator iter = criteria.getRightExpressions().iterator(); + Iterator<Expression> iter = criteria.getRightExpressions().iterator(); while (iter.hasNext()) { - if (!first) queryString.append(','); - if (!timeColumn) queryString.append('\''); - queryString.append(getValue((Expression)iter.next())); - if (!timeColumn) queryString.append('\''); - first = false; + queryString.append(getValue(iter.next(), false)); + if (iter.hasNext()) { + queryString.append(','); + } } queryString.append(')'); criteriaList.add(queryString.toString()); } - - private boolean isTimeColumn( Column column ) throws TranslatorException { - boolean result = false; - if (column.getJavaType().equals(java.sql.Timestamp.class) || column.getJavaType().equals(java.sql.Time.class) - || column.getJavaType().equals(java.sql.Date.class)) { - result = true; - } - return result; - } - - protected String getValue( Expression expr ) throws TranslatorException { - String result; + + protected String getValue( Expression expr, boolean raw) { + StringBuilder result = new StringBuilder(); if (expr instanceof ColumnReference) { - ColumnReference element = (ColumnReference)expr; - Column element2 = element.getMetadataObject(); - result = element2.getNameInSource(); + appendColumnReference(result, (ColumnReference)expr); } else if (expr instanceof Literal) { - Literal literal = (Literal)expr; - result = literal.getValue().toString(); + Literal literal = (Literal)expr; + if (literal.getValue() == null) { + if (raw) { + return null; + } + return "NULL"; //$NON-NLS-1$ + } + if (raw) { + return literal.getValue().toString(); + } + if (literal.getValue().getClass().equals(Boolean.class)) { + result.append(((Boolean)literal.getValue()).toString()); + } else if (literal.getValue().getClass().equals(java.sql.Timestamp.class)) { + Timestamp datetime = (java.sql.Timestamp)literal.getValue(); + String value = datetime.toString(); + int fractionalPlace = value.lastIndexOf('.'); + int fractionalLength = value.length() - fractionalPlace - 1; + if (fractionalLength > 3) { + value = value.substring(0, fractionalPlace + 3); + } else if (fractionalLength < 3) { + value += "00".substring(fractionalLength - 1); //$NON-NLS-1$ + } + result.append(value).setCharAt(result.length()-value.length()+10, 'T'); + Calendar c = TimestampWithTimezone.getCalendar(); + c.setTime(datetime); + int minutes = (c.get(Calendar.ZONE_OFFSET) + + c.get(Calendar.DST_OFFSET)) / 60000; + int val = minutes/60; + result.append(String.format("%1$+03d", val)); //$NON-NLS-1$ + result.append(':'); + val = minutes%60; + result.append(val/10); + result.append(val%10); + } else if (literal.getValue().getClass().equals(java.sql.Time.class)) { + result.append(literal.getValue()).append(".000").append(Util.getDefaultTimeZoneString()); //$NON-NLS-1$ + } else if (literal.getValue().getClass().equals(java.sql.Date.class)) { + result.append(literal.getValue()); + } else { + result.append(expr.toString()); + } } else { throw new RuntimeException("unknown type in SalesforceQueryExecution.getValue(): " + expr.toString()); //$NON-NLS-1$ } - return result; + return result.toString(); } protected void loadColumnMetadata( NamedTable group ) throws TranslatorException { @@ -394,7 +392,7 @@ } } - protected boolean isIdColumn( Expression expression ) throws TranslatorException { + protected boolean isIdColumn( Expression expression ) { boolean result = false; if (expression instanceof ColumnReference) { Column element = ((ColumnReference)expression).getMetadataObject(); @@ -406,7 +404,7 @@ return result; } - protected boolean isMultiSelectColumn( Expression expression ) throws TranslatorException { + protected boolean isMultiSelectColumn( Expression expression ) { boolean result = false; if (expression instanceof ColumnReference) { Column element = ((ColumnReference)expression).getMetadataObject(); Modified: branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/JoinQueryVisitor.java =================================================================== --- branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/JoinQueryVisitor.java 2011-11-29 19:41:45 UTC (rev 3708) +++ branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/JoinQueryVisitor.java 2011-11-30 19:08:23 UTC (rev 3709) @@ -63,39 +63,34 @@ @Override public void visit(Comparison criteria) { - // Find the criteria that joins the two tables - try { - Expression rExp = criteria.getRightExpression(); - if (rExp instanceof ColumnReference) { - Expression lExp = criteria.getLeftExpression(); - if (isIdColumn(rExp) || isIdColumn(lExp)) { + Expression rExp = criteria.getRightExpression(); + if (rExp instanceof ColumnReference) { + Expression lExp = criteria.getLeftExpression(); + if (isIdColumn(rExp) || isIdColumn(lExp)) { - Column rColumn = ((ColumnReference) rExp).getMetadataObject(); - String rTableName = rColumn.getParent().getNameInSource(); + Column rColumn = ((ColumnReference) rExp).getMetadataObject(); + String rTableName = rColumn.getParent().getNameInSource(); - Column lColumn = ((ColumnReference) lExp).getMetadataObject(); - String lTableName = lColumn.getParent().getNameInSource(); + Column lColumn = ((ColumnReference) lExp).getMetadataObject(); + String lTableName = lColumn.getParent().getNameInSource(); - if (leftTableInJoin.getNameInSource().equals(rTableName) - || leftTableInJoin.getNameInSource().equals(lTableName) - && rightTableInJoin.getNameInSource().equals(rTableName) - || rightTableInJoin.getNameInSource().equals(lTableName) - && !rTableName.equals(lTableName)) { - // This is the join criteria, the one that is the ID is the parent. - Expression fKey = !isIdColumn(lExp) ? lExp : rExp; - table = childTable = (Table)((ColumnReference) fKey).getMetadataObject().getParent(); - } 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); - } + if (leftTableInJoin.getNameInSource().equals(rTableName) + || leftTableInJoin.getNameInSource().equals(lTableName) + && rightTableInJoin.getNameInSource().equals(rTableName) + || rightTableInJoin.getNameInSource().equals(lTableName) + && !rTableName.equals(lTableName)) { + // This is the join criteria, the one that is the ID is the parent. + Expression fKey = !isIdColumn(lExp) ? lExp : rExp; + table = childTable = (Table)((ColumnReference) fKey).getMetadataObject().getParent(); + } 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); } - } else { - super.visit(criteria); } - } catch (TranslatorException e) { - exceptions.add(e); + } else { + super.visit(criteria); } } Modified: branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/SelectVisitor.java =================================================================== --- branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/SelectVisitor.java 2011-11-29 19:41:45 UTC (rev 3708) +++ branches/7.6.x/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/SelectVisitor.java 2011-11-30 19:08:23 UTC (rev 3709) @@ -61,7 +61,7 @@ public void visit(Select query) { super.visit(query); if (query.isDistinct()) { - exceptions.add(new TranslatorException(SalesForcePlugin.Util.getString("SelectVisitor.distinct.not.supported"))); + exceptions.add(new TranslatorException(SalesForcePlugin.Util.getString("SelectVisitor.distinct.not.supported"))); //$NON-NLS-1$ } selectSymbols = query.getDerivedColumns(); selectSymbolCount = selectSymbols.size(); @@ -78,10 +78,10 @@ selectSymbolNameToIndex .put(qualifiedName, index); String nameInSource = element.getNameInSource(); if (null == nameInSource || nameInSource.length() == 0) { - exceptions.add(new TranslatorException("name in source is null or empty for column "+ symbol.toString())); + exceptions.add(new TranslatorException("name in source is null or empty for column "+ symbol.toString())); //$NON-NLS-1$ continue; } - if (nameInSource.equalsIgnoreCase("id")) { + if (nameInSource.equalsIgnoreCase("id")) { //$NON-NLS-1$ idIndex = index; } } @@ -96,7 +96,7 @@ String supportsQuery = table.getProperties().get(Constants.SUPPORTS_QUERY); objectSupportsRetrieve = Boolean.valueOf(table.getProperties().get(Constants.SUPPORTS_RETRIEVE)); if (!Boolean.valueOf(supportsQuery)) { - throw new TranslatorException(table.getNameInSource() + " " + SalesForcePlugin.Util.getString("CriteriaVisitor.query.not.supported")); + throw new TranslatorException(table.getNameInSource() + " " + SalesForcePlugin.Util.getString("CriteriaVisitor.query.not.supported")); //$NON-NLS-1$ //$NON-NLS-2$ } loadColumnMetadata(obj); } catch (TranslatorException ce) { @@ -118,7 +118,7 @@ public String getQuery() throws TranslatorException { if (!exceptions.isEmpty()) { - throw ((TranslatorException) exceptions.get(0)); + throw exceptions.get(0); } StringBuffer result = new StringBuffer(); result.append(SELECT).append(SPACE); @@ -137,9 +137,9 @@ boolean firstTime = true; for (DerivedColumn symbol : selectSymbols) { if (!firstTime) { - result.append(", "); + result.append(", "); //$NON-NLS-1$ } else { - firstTime = false; + firstTime = false; } Expression expression = symbol.getExpression(); if (expression instanceof ColumnReference) { @@ -153,7 +153,7 @@ if(parent instanceof Table) { table = (Table)parent; } else { - throw new TranslatorException("Could not resolve Table for column " + element.getName()); + throw new TranslatorException("Could not resolve Table for column " + element.getName()); //$NON-NLS-1$ } } result.append(table.getNameInSource()); @@ -209,19 +209,19 @@ } - public List<String> getIdInCriteria() throws TranslatorException { + public List<String> getIdInCriteria() { assertRetrieveValidated(); List<Expression> expressions = this.idInCriteria.getRightExpressions(); List<String> result = new ArrayList<String>(expressions.size()); for(int i = 0; i < expressions.size(); i++) { - result.add(getValue(expressions.get(i))); + result.add(getValue(expressions.get(i), true)); } return result; } private void assertRetrieveValidated() throws AssertionError { if(!hasOnlyIDCriteria()) { - throw new AssertionError("Must call hasOnlyIdInCriteria() before this method"); + throw new AssertionError("Must call hasOnlyIdInCriteria() before this method"); //$NON-NLS-1$ } } Modified: branches/7.6.x/connectors/translator-salesforce/src/test/java/org/teiid/translator/salesforce/execution/TestQueryExecutionImpl.java =================================================================== --- branches/7.6.x/connectors/translator-salesforce/src/test/java/org/teiid/translator/salesforce/execution/TestQueryExecutionImpl.java 2011-11-29 19:41:45 UTC (rev 3708) +++ branches/7.6.x/connectors/translator-salesforce/src/test/java/org/teiid/translator/salesforce/execution/TestQueryExecutionImpl.java 2011-11-30 19:08:23 UTC (rev 3709) @@ -24,11 +24,19 @@ import static org.junit.Assert.*; +import java.sql.Time; +import java.sql.Timestamp; +import java.util.Calendar; +import java.util.TimeZone; + +import org.junit.AfterClass; +import org.junit.BeforeClass; import org.junit.Test; import org.mockito.Mockito; import org.teiid.cdk.api.TranslationUtility; import org.teiid.language.Select; import org.teiid.metadata.RuntimeMetadata; +import org.teiid.query.unittest.TimestampUtil; import org.teiid.translator.ExecutionContext; import org.teiid.translator.salesforce.SalesforceConnection; import org.teiid.translator.salesforce.execution.visitors.TestVisitors; @@ -66,4 +74,20 @@ assertNull(qei.next()); } + @BeforeClass static public void oneTimeSetup() { + TimeZone.setDefault(TimeZone.getTimeZone("GMT-06:00")); + } + + @AfterClass static public void oneTimeTearDown() { + TimeZone.setDefault(null); + } + + @Test public void testValueParsing() throws Exception { + assertEquals(TimestampUtil.createTime(2, 0, 0), QueryExecutionImpl.parseDateTime("08:00:00.000Z", Time.class, Calendar.getInstance())); + } + + @Test public void testValueParsing1() throws Exception { + assertEquals(TimestampUtil.createTimestamp(101, 0, 1, 2, 0, 0, 1000000), QueryExecutionImpl.parseDateTime("2001-01-01T08:00:00.001Z", Timestamp.class, Calendar.getInstance())); + } + } Modified: branches/7.6.x/connectors/translator-salesforce/src/test/java/org/teiid/translator/salesforce/execution/visitors/TestVisitors.java =================================================================== --- branches/7.6.x/connectors/translator-salesforce/src/test/java/org/teiid/translator/salesforce/execution/visitors/TestVisitors.java 2011-11-29 19:41:45 UTC (rev 3708) +++ branches/7.6.x/connectors/translator-salesforce/src/test/java/org/teiid/translator/salesforce/execution/visitors/TestVisitors.java 2011-11-30 19:08:23 UTC (rev 3709) @@ -28,6 +28,8 @@ import java.util.List; import java.util.TimeZone; +import org.junit.AfterClass; +import org.junit.BeforeClass; import org.junit.Test; import org.mockito.Mockito; import org.teiid.cdk.api.TranslationUtility; @@ -90,16 +92,16 @@ contactTable.setProperty("Supports Query", Boolean.TRUE.toString()); //$NON-NLS-1$ // Create Contact Columns String[] elemNames = new String[] { - "ContactID", "Name", "AccountId", "InitialContact" //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + "ContactID", "Name", "AccountId", "InitialContact", "LastTime" //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ }; String[] elemTypes = new String[] { - DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.TIMESTAMP + DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.TIMESTAMP, DataTypeManager.DefaultDataTypes.TIME }; List<Column> contactCols = RealMetadataFactory.createElements(contactTable, elemNames, elemTypes); // Set name in source on each column String[] contactNameInSource = new String[] { - "id", "ContactName", "accountid", "InitialContact" //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + "id", "ContactName", "accountid", "InitialContact", "LastTime" //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ }; for(int i=0; i<2; i++) { Column obj = contactCols.get(i); @@ -154,7 +156,7 @@ 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 Industry IN('1','2','3')", visitor.getQuery().toString().trim()); //$NON-NLS-1$ + 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$ } @@ -194,9 +196,23 @@ Select command = (Select)translationUtility.parseCommand("SELECT Contacts.Name FROM Contacts WHERE Contacts.Name in ('x', 'y')"); //$NON-NLS-1$ SelectVisitor visitor = new SelectVisitor(translationUtility.createRuntimeMetadata()); visitor.visit(command); - assertEquals("SELECT Contact.ContactName FROM Contact WHERE ContactName IN('x','y')", visitor.getQuery().toString().trim()); //$NON-NLS-1$ + assertEquals("SELECT Contact.ContactName FROM Contact WHERE Contact.ContactName IN('x','y')", visitor.getQuery().toString().trim()); //$NON-NLS-1$ } + @Test public void testEqualsElement() throws Exception { + Select command = (Select)translationUtility.parseCommand("SELECT Contacts.Name FROM Contacts WHERE Contacts.Name = Contacts.AccountId"); //$NON-NLS-1$ + SelectVisitor visitor = new SelectVisitor(translationUtility.createRuntimeMetadata()); + visitor.visit(command); + assertEquals("SELECT Contact.ContactName FROM Contact WHERE Contact.ContactName = Contact.AccountId", visitor.getQuery().toString().trim()); //$NON-NLS-1$ + } + + @Test public void testIsNull() throws Exception { + Select command = (Select)translationUtility.parseCommand("SELECT Contacts.Name FROM Contacts WHERE Contacts.Name is not null"); //$NON-NLS-1$ + SelectVisitor visitor = new SelectVisitor(translationUtility.createRuntimeMetadata()); + visitor.visit(command); + assertEquals("SELECT Contact.ContactName FROM Contact WHERE Contact.ContactName != NULL", visitor.getQuery().toString().trim()); //$NON-NLS-1$ + } + @Test public void testIDCriteria() throws Exception { Select command = (Select)translationUtility.parseCommand("select id, name from Account where id = 'bar'"); //$NON-NLS-1$ SalesforceConnection sfc = Mockito.mock(SalesforceConnection.class); @@ -205,16 +221,33 @@ Mockito.verify(sfc).retrieve("Account.id, Account.AccountName", "Account", Arrays.asList("bar")); } - @Test public void testDateTimeFormating() throws Exception { + @BeforeClass static public void oneTimeSetup() { TimeZone.setDefault(TimeZone.getTimeZone("GMT-06:00")); - try { - 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$ - } finally { - TimeZone.setDefault(null); - } } + + @AfterClass static public void oneTimeTearDown() { + TimeZone.setDefault(null); + } + + @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$ + } + + @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$ + 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$ + } + + @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$ + } }