[teiid-commits] teiid SVN: r1267 - in trunk/connectors/connector-salesforce/src: main/java/com/metamatrix/connector/salesforce/connection and 4 other directories.

Author: jdoyle Date: 2009-08-21 14:59:08 -0400 (Fri, 21 Aug 2009) New Revision: 1267 Added: trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/Constants.java trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/execution/visitors/JoinQueryVisitor.java Modified: trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/SalesforceCapabilities.java trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/connection/SalesforceConnection.java trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/connection/impl/ConnectionImpl.java trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/execution/QueryExecutionImpl.java trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/execution/visitors/CriteriaVisitor.java trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/execution/visitors/SelectVisitor.java trunk/connectors/connector-salesforce/src/test/java/com/metamatrix/connector/salesforce/execution/visitors/TestVisitors.java Log: TEIID-202 TEIID-209 Support for SOQL relationship queries via outer joins. Removed 'ping timeout' from the connection. Added: trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/Constants.java =================================================================== --- trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/Constants.java (rev 0) +++ trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/Constants.java 2009-08-21 18:59:08 UTC (rev 1267) @@ -0,0 +1,49 @@ +package com.metamatrix.connector.salesforce; + +public interface Constants { + + public static final String PICKLIST_TYPE = "picklist"; //$NON-NLS-1$ + + public static final String MULTIPICKLIST_TYPE = "multipicklist"; //$NON-NLS-1$ + + public static final String COMBOBOX_TYPE = "combobox"; //$NON-NLS-1$ + + public static final String ANYTYPE_TYPE = "anyType"; //$NON-NLS-1$ + + public static final String REFERENCE_TYPE = "reference"; //$NON-NLS-1$ + + public static final String STRING_TYPE = "string"; //$NON-NLS-1$ + + public static final String BASE64_TYPE = "base64"; //$NON-NLS-1$ + + public static final String BOOLEAN_TYPE = "boolean"; //$NON-NLS-1$ + + public static final String CURRENCY_TYPE = "currency"; //$NON-NLS-1$ + + public static final String TEXTAREA_TYPE = "textarea"; //$NON-NLS-1$ + + public static final String INT_TYPE = "int"; //$NON-NLS-1$ + + public static final String DOUBLE_TYPE = "double"; //$NON-NLS-1$ + + public static final String PERCENT_TYPE = "percent"; //$NON-NLS-1$ + + public static final String PHONE_TYPE = "phone"; //$NON-NLS-1$ + + public static final String ID_TYPE = "id"; //$NON-NLS-1$ + + public static final String DATE_TYPE = "date"; //$NON-NLS-1$ + + public static final String DATETIME_TYPE = "datetime"; //$NON-NLS-1$ + + public static final String URL_TYPE = "url"; //$NON-NLS-1$ + + public static final String EMAIL_TYPE = "email"; //$NON-NLS-1$ + + public static final String RESTRICTED_PICKLIST_TYPE = "restrictedpicklist"; //$NON-NLS-1$ + + public static final String RESTRICTED_MULTISELECT_PICKLIST_TYPE = "restrictedmultiselectpicklist"; //$NON-NLS-1$ + + public static final String SUPPORTS_QUERY = "Supports Query"; + +} Modified: trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/SalesforceCapabilities.java =================================================================== --- trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/SalesforceCapabilities.java 2009-08-20 13:14:46 UTC (rev 1266) +++ trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/SalesforceCapabilities.java 2009-08-21 18:59:08 UTC (rev 1267) @@ -80,4 +80,16 @@ return true; } + @Override + public SupportedJoinCriteria getSupportedJoinCriteria() { + return SupportedJoinCriteria.KEY; + } + + @Override + public boolean supportsOuterJoins() { + return true; + } + + + } Modified: trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/connection/SalesforceConnection.java =================================================================== --- trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/connection/SalesforceConnection.java 2009-08-20 13:14:46 UTC (rev 1266) +++ trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/connection/SalesforceConnection.java 2009-08-21 18:59:08 UTC (rev 1267) @@ -64,8 +64,19 @@ } } catch (NumberFormatException e) { throw new ConnectorException(Messages.getString("SalesforceConnection.bad.ping.value")); - } - connection = new ConnectionImpl(username, password, url, pingInterval, env.getLogger()); + } + //600000 - 10 minutes + int timeout = 120000; // two minutes + try { + String timeoutString = env.getProperties().getProperty("SourceConnectionTimeout"); + if(null != timeoutString) { + timeout = Integer.decode(timeoutString).intValue(); + } + } catch (NumberFormatException e) { + throw new ConnectorException(Messages.getString("SalesforceConnection.bad.timeout.value")); + } + + connection = new ConnectionImpl(username, password, url, pingInterval, env.getLogger(), timeout); } catch(Throwable t) { env.getLogger().logError("SalesforceConnection() ErrorMessage: " + t.getMessage()); if(t instanceof ConnectorException) { Modified: trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/connection/impl/ConnectionImpl.java =================================================================== --- trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/connection/impl/ConnectionImpl.java 2009-08-20 13:14:46 UTC (rev 1266) +++ trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/connection/impl/ConnectionImpl.java 2009-08-21 18:59:08 UTC (rev 1267) @@ -58,22 +58,18 @@ import com.sforce.soap.partner.SforceServiceLocator; import com.sforce.soap.partner.SoapBindingStub; import com.sforce.soap.partner.fault.ApiFault; +import com.sforce.soap.partner.fault.InvalidQueryLocatorFault; import com.sforce.soap.partner.fault.InvalidSObjectFault; import com.sforce.soap.partner.fault.UnexpectedErrorFault; -import com.sforce.soap.partner.fault.InvalidQueryLocatorFault; import com.sforce.soap.partner.sobject.SObject; public class ConnectionImpl { private SoapBindingStub binding; private ConnectorLogger logger; - private long prevTime; - private long pingInterval; - public ConnectionImpl(String username, String password, URL url, long pingInterval, ConnectorLogger logger) throws ConnectorException { - this.pingInterval = pingInterval; + public ConnectionImpl(String username, String password, URL url, long pingInterval, ConnectorLogger logger, int timeout) throws ConnectorException { this.logger = logger; - login(username, password, url); - prevTime = System.currentTimeMillis(); + login(username, password, url, timeout); } String getUserName() throws ConnectorException { @@ -90,7 +86,7 @@ return binding; } - private void login(String username, String password, URL url) + private void login(String username, String password, URL url, int timeout) throws ConnectorException { if (!isAlive()) { LoginResult loginResult = null; @@ -108,6 +104,7 @@ CallOptions co = new CallOptions(); co.setClient("RedHat/MetaMatrix/"); binding.setHeader("SforceService", "CallOptions", co); + binding.setTimeout(timeout); loginResult = binding.login(username, password); } catch (ApiFault ex) { throw new ConnectorException(ex.getExceptionMessage()); @@ -157,20 +154,15 @@ public boolean isAlive() { boolean result = true; - if(null != binding) { + if(binding == null) { + result = false; + } else { try { - long currentTime = System.currentTimeMillis(); - if ((currentTime - prevTime)/1000 > pingInterval) { - prevTime = currentTime; - binding.getServerTimestamp(); - } - } catch (UnexpectedErrorFault e) { + binding.getServerTimestamp(); + } catch (Throwable t) { + logger.logDetail("Caught Throwable in isAlive", t); result = false; - } catch (RemoteException e) { - result = false; } - } else { - result = false; } return result; } Modified: trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/execution/QueryExecutionImpl.java =================================================================== --- trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/execution/QueryExecutionImpl.java 2009-08-20 13:14:46 UTC (rev 1266) +++ trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/execution/QueryExecutionImpl.java 2009-08-21 18:59:08 UTC (rev 1267) @@ -27,9 +27,12 @@ import java.util.Arrays; import java.util.Date; import java.util.HashMap; +import java.util.Iterator; import java.util.List; import java.util.Map; +import javax.xml.namespace.QName; + import org.apache.axis.message.MessageElement; import org.teiid.connector.api.ConnectorEnvironment; import org.teiid.connector.api.ConnectorException; @@ -39,6 +42,9 @@ import org.teiid.connector.api.ResultSetExecution; import org.teiid.connector.basic.BasicExecution; import org.teiid.connector.language.IAggregate; +import org.teiid.connector.language.IFrom; +import org.teiid.connector.language.IJoin; +import org.teiid.connector.language.IQuery; import org.teiid.connector.language.IQueryCommand; import org.teiid.connector.metadata.runtime.Element; import org.teiid.connector.metadata.runtime.RuntimeMetadata; @@ -46,6 +52,7 @@ import com.metamatrix.connector.salesforce.Messages; import com.metamatrix.connector.salesforce.Util; import com.metamatrix.connector.salesforce.connection.SalesforceConnection; +import com.metamatrix.connector.salesforce.execution.visitors.JoinQueryVisitor; import com.metamatrix.connector.salesforce.execution.visitors.SelectVisitor; import com.sforce.soap.partner.QueryResult; import com.sforce.soap.partner.sobject.SObject; @@ -63,6 +70,8 @@ private SelectVisitor visitor; private QueryResult results; + + private List<List<Object>> resultBatch; // Identifying values private String connectionIdentifier; @@ -75,11 +84,11 @@ private String logPreamble; - private Map<String,Integer> fieldMap; - private IQueryCommand query; - private int i; + Map<String, Map<String,Integer>> sObjectToResponseField = new HashMap<String, Map<String,Integer>>(); + + private int topResultIndex = 0; public QueryExecutionImpl(IQueryCommand command, SalesforceConnection connection, RuntimeMetadata metadata, ExecutionContext context, @@ -108,7 +117,12 @@ public void execute() throws ConnectorException { connectorEnv.getLogger().logInfo( getLogPreamble() + "Incoming Query: " + query.toString()); - visitor = new SelectVisitor(metadata); + IFrom from = ((IQuery)query).getFrom(); + if(from.getItems().get(0) instanceof IJoin) { + visitor = new JoinQueryVisitor(metadata); + } else { + visitor = new SelectVisitor(metadata); + } visitor.visitNode(query); String finalQuery; finalQuery = visitor.getQuery().trim(); @@ -118,66 +132,138 @@ results = connection.query(finalQuery, this.context.getBatchSize(), visitor.getQueryAll()); } + @SuppressWarnings("unchecked") @Override public List next() throws ConnectorException, DataNotAvailableException { - while (results != null) { - if (query.getProjectedQuery().getSelect().getSelectSymbols().get(0).getExpression() instanceof IAggregate) { - List<?> result = Arrays.asList(results.getSize()); - results = null; - return result; - } - if (i < results.getSize()) { - SObject sObject = results.getRecords(i++); - org.apache.axis.message.MessageElement[] fields = sObject.get_any(); - if (null == fieldMap) { - logAndMapFields(fields); + List<?> result; + if (query.getProjectedQuery().getSelect().getSelectSymbols().get(0) + .getExpression() instanceof IAggregate) { + result = Arrays.asList(results.getSize()); + results = null; + + } else { + result = getRow(results); + } + return result; + } + + private List<Object> getRow(QueryResult result) throws ConnectorException { + List<Object> row; + if(null == resultBatch) { + loadBatch(); + } + if(resultBatch.size() == topResultIndex) { + row = null; + } else { + row = resultBatch.get(topResultIndex); + topResultIndex++; + if(resultBatch.size() == topResultIndex) { + if(!result.isDone()) { + loadBatch(); } - return extractRowFromFields(sObject, fields); } - - if (results.isDone()) { // no more batches on sf. - results = null; - break; - } - results = connection.queryMore(results.getQueryLocator()); - i = 0; + } - return null; + return row; } - private List<Object> extractRowFromFields(SObject sObject, MessageElement[] fields) throws ConnectorException { - List<Object> row = new ArrayList<Object>(); - for (int j = 0; j < visitor.getSelectSymbolCount(); j++) { - Element element = visitor.getSelectSymbolMetadata(j); - Integer index = fieldMap.get(element.getNameInSource()); - // id gets dropped from the result if it is not the - // first field in the querystring. Add it back in. - if (null == index) { - if (element.getNameInSource().equalsIgnoreCase("id")) { - row.add(sObject.getId()); - } else { - throw new ConnectorException("SalesforceQueryExecutionImpl.missing.field" - + element.getNameInSource()); + private void loadBatch() throws ConnectorException { + if(null != resultBatch) { // if we have an old batch, then we have to get new results + results = connection.queryMore(results.getQueryLocator()); + } + resultBatch = new ArrayList<List<Object>>(); + + for(int resultIndex = 0; resultIndex < results.getSize(); resultIndex++) { + SObject sObject = results.getRecords(resultIndex); + List<Object[]> result = getObjectData(sObject); + for(Iterator<Object[]> i = result.iterator(); i.hasNext(); ) { + resultBatch.add(Arrays.asList(i.next())); } - } else { - Object cell; - cell = getCellDatum(element, fields[index]); - row.add(cell); } } - return row; + + private List<Object[]> getObjectData(SObject sObject) throws ConnectorException { + org.apache.axis.message.MessageElement[] topFields = sObject.get_any(); + logAndMapFields(sObject.getType(), topFields); + List<Object[]> result = new ArrayList<Object[]>(); + for(int i = 0; i < topFields.length; i++) { + QName qName = topFields[i].getType(); + if(null != qName) { + String type = qName.getLocalPart(); + if(type.equals("sObject")) { + SObject parent = (SObject)topFields[i].getObjectValue(); + result.addAll(getObjectData(parent)); + } else if(type.equals("QueryResult")) { + QueryResult subResult = (QueryResult)topFields[i].getObjectValue(); + for(int resultIndex = 0; resultIndex < subResult.getSize(); resultIndex++) { + SObject subObject = subResult.getRecords(resultIndex); + result.addAll(getObjectData(subObject)); + } + } + } + } + return extractDataFromFields(sObject, topFields,result); + + } + + private List<Object[]> extractDataFromFields(SObject sObject, + MessageElement[] fields, List<Object[]> result) throws ConnectorException { + Map<String,Integer> fieldToIndexMap = sObjectToResponseField.get(sObject.getType()); + for (int j = 0; j < visitor.getSelectSymbolCount(); j++) { + Element element = visitor.getSelectSymbolMetadata(j); + if(element.getParent().getNameInSource().equals(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. + if (null == index) { + if (element.getNameInSource().equalsIgnoreCase("id")) { + setValueInColumn(j, sObject.getId(), result); + } else { + throw new ConnectorException("SalesforceQueryExecutionImpl.missing.field" + + element.getNameInSource()); + } + } else { + Object cell; + cell = getCellDatum(element, fields[index]); + setValueInColumn(j, cell, result); + } + } + } + return result; } + + private void setValueInColumn(int columnIndex, Object value, List<Object[]> result) { + if(result.isEmpty()) { + Object[] row = new Object[visitor.getSelectSymbolCount()]; + result.add(row); + } + Iterator<Object[]> iter = result.iterator(); + while (iter.hasNext()) { + Object[] row = iter.next(); + row[columnIndex] = value; + } + } - private void logAndMapFields(org.apache.axis.message.MessageElement[] fields) { - logFields(fields); - fieldMap = new HashMap<String, Integer>(); - for (int x = 0; x < fields.length; x++) { - fieldMap.put(fields[x].getLocalName(), x); + /** + * Load the map of response field names to index. + * @param fields + */ + private void logAndMapFields(String sObjectName, + org.apache.axis.message.MessageElement[] fields) { + if (!sObjectToResponseField.containsKey(sObjectName)) { + logFields(sObjectName, fields); + Map<String, Integer> responseFieldToIndexMap; + responseFieldToIndexMap = new HashMap<String, Integer>(); + for (int x = 0; x < fields.length; x++) { + responseFieldToIndexMap.put(fields[x].getLocalName(), x); + } + sObjectToResponseField.put(sObjectName, responseFieldToIndexMap); } } - private void logFields(MessageElement[] fields) { + private void logFields(String sObjectName, MessageElement[] fields) { ConnectorLogger logger = connectorEnv.getLogger(); + logger.logDetail("SalesForce Object Name = " + sObjectName); logger.logDetail("FieldCount = " + fields.length); for(int i = 0; i < fields.length; i++) { logger.logDetail("Field # " + i + " is " + fields[i].getLocalName()); Modified: trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/execution/visitors/CriteriaVisitor.java =================================================================== --- trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/execution/visitors/CriteriaVisitor.java 2009-08-20 13:14:46 UTC (rev 1266) +++ trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/execution/visitors/CriteriaVisitor.java 2009-08-21 18:59:08 UTC (rev 1267) @@ -277,25 +277,35 @@ Element column = left.getMetadataObject(); String columnName = column.getNameInSource(); StringBuffer queryString = new StringBuffer(); + queryString.append(column.getParent().getNameInSource()); + queryString.append('.'); queryString.append(columnName).append(SPACE); queryString.append(comparisonOperators.get(compCriteria.getOperator())); queryString.append(' '); - ILiteral literal = (ILiteral)compCriteria.getRightExpression(); - 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()); + IExpression rExp = compCriteria.getRightExpression(); + if(rExp instanceof ILiteral) { + ILiteral literal = (ILiteral)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 IElement) { + IElement right = (IElement)lExpr; + column = left.getMetadataObject(); + columnName = column.getNameInSource(); + queryString.append(columnName); } criteriaList.add(queryString.toString()); Added: trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/execution/visitors/JoinQueryVisitor.java =================================================================== --- trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/execution/visitors/JoinQueryVisitor.java (rev 0) +++ trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/execution/visitors/JoinQueryVisitor.java 2009-08-21 18:59:08 UTC (rev 1267) @@ -0,0 +1,167 @@ +package com.metamatrix.connector.salesforce.execution.visitors; + +import org.teiid.connector.api.ConnectorException; +import org.teiid.connector.language.IAggregate; +import org.teiid.connector.language.ICompareCriteria; +import org.teiid.connector.language.IElement; +import org.teiid.connector.language.IExpression; +import org.teiid.connector.language.IFromItem; +import org.teiid.connector.language.IGroup; +import org.teiid.connector.language.IJoin; +import org.teiid.connector.language.ISelectSymbol; +import org.teiid.connector.metadata.runtime.Element; +import org.teiid.connector.metadata.runtime.Group; +import org.teiid.connector.metadata.runtime.RuntimeMetadata; + +import com.metamatrix.connector.salesforce.Util; + +/** + * Salesforce supports joins only on primary key/foreign key relationships. The connector + * is supporting these joins through the OUTER JOIN syntax. All RIGHT OUTER JOINS are + * rewritten by the query processor as LEFT OUTER JOINS, so that is all this visitor has + * to expect. + * + * Salesforce also requires a different syntax depending upon if you are joining from parent + * to child, or from child to parent. + * http://www.salesforce.com/us/developer/docs/api/index_Left.htm#StartTopic... + * + */ + +public class JoinQueryVisitor extends SelectVisitor implements + IQueryProvidingVisitor { + + private Group leftTableInJoin; + private Group rightTableInJoin; + private Group childTable; + + public JoinQueryVisitor(RuntimeMetadata metadata) { + super(metadata); + } + + // Has to be a left outer join + @Override + public void visit(IJoin join) { + try { + IFromItem left = join.getLeftItem(); + if (left instanceof IGroup) { + IGroup leftGroup = (IGroup) left; + leftTableInJoin = leftGroup.getMetadataObject(); + loadColumnMetadata(leftGroup); + } + + IFromItem right = join.getRightItem(); + if (right instanceof IGroup) { + IGroup rightGroup = (IGroup) right; + rightTableInJoin = rightGroup.getMetadataObject(); + loadColumnMetadata((IGroup) right); + } + super.visit(join); + } catch (ConnectorException ce) { + exceptions.add(ce); + } + + } + + @Override + public void visit(ICompareCriteria criteria) { + + // Find the criteria that joins the two tables + try { + IExpression rExp = criteria.getRightExpression(); + if (rExp instanceof IElement) { + IExpression lExp = criteria.getLeftExpression(); + if (isIdColumn((IExpression) rExp) || isIdColumn(lExp)) { + + Element rColumn = ((IElement) rExp).getMetadataObject(); + String rTableName = rColumn.getParent().getNameInSource(); + + Element lColumn = ((IElement) 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. + IExpression fKey = !isIdColumn(lExp) ? lExp : rExp; + table = childTable = ((IElement) 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 (ConnectorException e) { + exceptions.add(e); + } + } + + @Override + public String getQuery() throws ConnectorException { + + if (isParentToChildJoin()) { + return super.getQuery(); + } else { + if (!exceptions.isEmpty()) { + throw ((ConnectorException) exceptions.get(0)); + } + StringBuffer select = new StringBuffer(); + select.append(SELECT).append(SPACE); + addSelectSymbols(leftTableInJoin.getNameInSource(), select); + select.append(COMMA).append(SPACE).append(OPEN); + + StringBuffer subselect = new StringBuffer(); + subselect.append(SELECT).append(SPACE); + addSelectSymbols(rightTableInJoin.getNameInSource(), subselect); + subselect.append(SPACE); + subselect.append(FROM).append(SPACE); + subselect.append(rightTableInJoin.getNameInSource()).append('s'); + subselect.append(CLOSE).append(SPACE); + select.append(subselect); + select.append(FROM).append(SPACE); + select.append(leftTableInJoin.getNameInSource()).append(SPACE); + addCriteriaString(select); + select.append(limitClause); + Util.validateQueryLength(select); + return select.toString(); + } + } + + public boolean isParentToChildJoin() { + return childTable.equals(leftTableInJoin); + } + + protected void addSelectSymbols(String tableNameInSource, StringBuffer result) throws ConnectorException { + boolean firstTime = true; + for (ISelectSymbol symbol : selectSymbols) { + IExpression expression = symbol.getExpression(); + if (expression instanceof IElement) { + Element element = ((IElement) expression).getMetadataObject(); + String tableName = element.getParent().getNameInSource(); + if(!isParentToChildJoin() && tableNameInSource.equals(tableName) || + isParentToChildJoin()) { + if (!firstTime) { + result.append(", "); + } else { + firstTime = false; + } + result.append(tableName); + result.append('.'); + result.append(element.getNameInSource()); + } + } else if (expression instanceof IAggregate) { + if (!firstTime) { + result.append(", "); + } else { + firstTime = false; + } + result.append("count()"); //$NON-NLS-1$ + } + } + } + +} Modified: trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/execution/visitors/SelectVisitor.java =================================================================== --- trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/execution/visitors/SelectVisitor.java 2009-08-20 13:14:46 UTC (rev 1266) +++ trunk/connectors/connector-salesforce/src/main/java/com/metamatrix/connector/salesforce/execution/visitors/SelectVisitor.java 2009-08-21 18:59:08 UTC (rev 1267) @@ -31,6 +31,7 @@ import org.teiid.connector.language.IElement; import org.teiid.connector.language.IExpression; import org.teiid.connector.language.IFrom; +import org.teiid.connector.language.IFromItem; import org.teiid.connector.language.IGroup; import org.teiid.connector.language.IQuery; import org.teiid.connector.language.ISelect; @@ -38,6 +39,7 @@ import org.teiid.connector.metadata.runtime.Element; import org.teiid.connector.metadata.runtime.RuntimeMetadata; +import com.metamatrix.connector.salesforce.Constants; import com.metamatrix.connector.salesforce.Messages; import com.metamatrix.connector.salesforce.Util; @@ -47,10 +49,9 @@ private Map<String, Integer> selectSymbolNameToIndex = new HashMap<String, Integer>(); private int selectSymbolCount; private int idIndex = -1; // index of the ID select symbol. - private StringBuffer selectSymbols = new StringBuffer(); - private StringBuffer limitClause = new StringBuffer(); - //private StringBuffer orderByClause = new StringBuffer(); - + protected List<ISelectSymbol> selectSymbols; + protected StringBuffer limitClause = new StringBuffer(); + public SelectVisitor(RuntimeMetadata metadata) { super(metadata); } @@ -71,10 +72,9 @@ Messages.getString("SelectVisitor.distinct.not.supported"))); } try { - List<ISelectSymbol> symbols = select.getSelectSymbols(); - selectSymbolCount = symbols.size(); - Iterator<ISelectSymbol> symbolIter = symbols.iterator(); - boolean firstTime = true; + selectSymbols = select.getSelectSymbols(); + selectSymbolCount = selectSymbols.size(); + Iterator<ISelectSymbol> symbolIter = selectSymbols.iterator(); int index = 0; while (symbolIter.hasNext()) { ISelectSymbol symbol = symbolIter.next(); @@ -94,14 +94,6 @@ if (nameInSource.equalsIgnoreCase("id")) { idIndex = index; } - if (!firstTime) { - selectSymbols.append(", "); - } else { - firstTime = false; - } - selectSymbols.append(nameInSource); - } else if (expression instanceof IAggregate) { - selectSymbols.append("count()"); //$NON-NLS-1$ } ++index; } @@ -114,27 +106,23 @@ public void visit(IFrom from) { super.visit(from); try { - IGroup group = (IGroup) from.getItems().get(0); - loadColumnMetadata(group); + // could be a join here, but if so we do nothing and handle + // it in visit(IJoin join). + IFromItem fromItem = (IFromItem) from.getItems().get(0); + if(fromItem instanceof IGroup) { + table = ((IGroup)fromItem).getMetadataObject(); + String supportsQuery = (String)table.getProperties().get(Constants.SUPPORTS_QUERY); + if (!Boolean.valueOf(supportsQuery)) { + throw new ConnectorException(table.getNameInSource() + " " + + Messages.getString("CriteriaVisitor.query.not.supported")); + } + loadColumnMetadata((IGroup)fromItem); + } } catch (ConnectorException ce) { exceptions.add(ce); } } - -/* - @Override - public void visit(IOrderBy orderBy) { - super.visit(orderBy); - List items = orderBy.getItems(); - if(items.size() > 1) { - exceptions.add(new ConnectorException("Salesforce cannot support more than one item in the ORDER BY clause")); - } - StringBuffer result = new StringBuffer(); - result.append(ORDER_BY).append(SPACE); - result.append(items.get(0)); - orderByClause = result; - } -*/ + /* * The SOQL SELECT command uses the following syntax: SELECT fieldList FROM * objectType [WHERE The Condition Expression (WHERE Clause)] [ORDER BY] @@ -147,7 +135,8 @@ } StringBuffer result = new StringBuffer(); result.append(SELECT).append(SPACE); - result.append(selectSymbols).append(SPACE); + addSelectSymbols(table.getNameInSource(), result); + result.append(SPACE); result.append(FROM).append(SPACE); result.append(table.getNameInSource()).append(SPACE); addCriteriaString(result); @@ -157,6 +146,28 @@ return result.toString(); } + protected void addSelectSymbols(String tableNameInSource, StringBuffer result) throws ConnectorException { + boolean firstTime = true; + for (ISelectSymbol symbol : selectSymbols) { + if (!firstTime) { + result.append(", "); + } else { + firstTime = false; + } + IExpression expression = symbol.getExpression(); + if (expression instanceof IElement) { + Element element = ((IElement) expression).getMetadataObject(); + String tableName = element.getParent().getNameInSource(); + result.append(tableName); + result.append('.'); + result.append(element.getNameInSource()); + } else if (expression instanceof IAggregate) { + result.append("count()"); //$NON-NLS-1$ + } + } + } + + public int getSelectSymbolCount() { return selectSymbolCount; } Modified: trunk/connectors/connector-salesforce/src/test/java/com/metamatrix/connector/salesforce/execution/visitors/TestVisitors.java =================================================================== --- trunk/connectors/connector-salesforce/src/test/java/com/metamatrix/connector/salesforce/execution/visitors/TestVisitors.java 2009-08-20 13:14:46 UTC (rev 1266) +++ trunk/connectors/connector-salesforce/src/test/java/com/metamatrix/connector/salesforce/execution/visitors/TestVisitors.java 2009-08-21 18:59:08 UTC (rev 1267) @@ -40,37 +40,64 @@ public static FakeMetadataFacade exampleSalesforce() { // Create models FakeMetadataObject salesforceModel = FakeMetadataFactory.createPhysicalModel("SalesforceModel"); //$NON-NLS-1$ + + // Create Account group + FakeMetadataObject accounTable = FakeMetadataFactory.createPhysicalGroup("SalesforceModel.Account", salesforceModel); //$NON-NLS-1$ + accounTable.putProperty(FakeMetadataObject.Props.NAME_IN_SOURCE, "Account"); //$NON-NLS-1$ + accounTable.setExtensionProp("Supports Query", Boolean.TRUE.toString()); //$NON-NLS-1$ + // Create Account Columns + String[] acctNames = new String[] { + "ID", "Name", "Stuff", "Industry" //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ + }; + String[] acctTypes = new String[] { + DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING + }; - // Create physical groups - FakeMetadataObject table = FakeMetadataFactory.createPhysicalGroup("SalesforceModel.Account", salesforceModel); //$NON-NLS-1$ - table.putProperty(FakeMetadataObject.Props.NAME_IN_SOURCE, "Account"); //$NON-NLS-1$ - table.setExtensionProp("Supports Query", Boolean.TRUE.toString()); //$NON-NLS-1$ - // Create physical elements + List<FakeMetadataObject> acctCols = FakeMetadataFactory.createElements(accounTable, acctNames, acctTypes); + acctCols.get(2).putProperty(FakeMetadataObject.Props.NATIVE_TYPE, "multipicklist"); //$NON-NLS-1$ + acctCols.get(2).putProperty(FakeMetadataObject.Props.SEARCHABLE_COMPARE, false); + acctCols.get(2).putProperty(FakeMetadataObject.Props.SEARCHABLE_LIKE, true); + // Set name in source on each column + String[] accountNameInSource = new String[] { + "id", "AccountName", "Stuff", "Industry" //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + }; + for(int i=0; i<2; i++) { + FakeMetadataObject obj = acctCols.get(i); + obj.putProperty(FakeMetadataObject.Props.NAME_IN_SOURCE, accountNameInSource[i]); + } + + // Add all Account to the store + FakeMetadataStore store = new FakeMetadataStore(); + store.addObject(salesforceModel); + store.addObject(accounTable); + store.addObjects(acctCols); + + // Create Contact group + FakeMetadataObject contactTable = FakeMetadataFactory.createPhysicalGroup("SalesforceModel.Contact", salesforceModel); //$NON-NLS-1$ + contactTable.putProperty(FakeMetadataObject.Props.NAME_IN_SOURCE, "Contact"); //$NON-NLS-1$ + contactTable.setExtensionProp("Supports Query", Boolean.TRUE.toString()); //$NON-NLS-1$ + // Create Contact Columns String[] elemNames = new String[] { - "AccountID", "Name", "Stuff" //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + "ContactID", "Name", "AccountId" //$NON-NLS-1$ //$NON-NLS-2$ }; String[] elemTypes = new String[] { DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING, DataTypeManager.DefaultDataTypes.STRING }; - List<FakeMetadataObject> cols = FakeMetadataFactory.createElements(table, elemNames, elemTypes); - cols.get(2).putProperty(FakeMetadataObject.Props.NATIVE_TYPE, "multipicklist"); //$NON-NLS-1$ - cols.get(2).putProperty(FakeMetadataObject.Props.SEARCHABLE_COMPARE, false); - cols.get(2).putProperty(FakeMetadataObject.Props.SEARCHABLE_LIKE, true); + List<FakeMetadataObject> contactCols = FakeMetadataFactory.createElements(contactTable, elemNames, elemTypes); // Set name in source on each column - String[] nameInSource = new String[] { - "id", "AccountName", "Stuff" //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + String[] contactNameInSource = new String[] { + "id", "ContactName", "accountid" //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ }; for(int i=0; i<2; i++) { - FakeMetadataObject obj = cols.get(i); - obj.putProperty(FakeMetadataObject.Props.NAME_IN_SOURCE, nameInSource[i]); + FakeMetadataObject obj = contactCols.get(i); + obj.putProperty(FakeMetadataObject.Props.NAME_IN_SOURCE, contactNameInSource[i]); } - // Add all objects to the store - FakeMetadataStore store = new FakeMetadataStore(); + // Add all Account to the store store.addObject(salesforceModel); - store.addObject(table); - store.addObjects(cols); + store.addObject(contactTable); + store.addObjects(contactCols); // Create the facade from the store return new FakeMetadataFacade(store); @@ -82,14 +109,14 @@ IQuery command = (IQuery)translationUtility.parseCommand("select * from Account where Name = 'foo' or Stuff = 'bar'"); //$NON-NLS-1$ SelectVisitor visitor = new SelectVisitor(translationUtility.createRuntimeMetadata()); visitor.visit(command); - assertEquals("SELECT id, AccountName, Stuff FROM Account WHERE (AccountName = 'foo') OR (Stuff = 'bar')", visitor.getQuery().toString().trim()); //$NON-NLS-1$ + 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 testNot() throws Exception { IQuery command = (IQuery)translationUtility.parseCommand("select * from Account where not (Name = 'foo' and Stuff = 'bar')"); //$NON-NLS-1$ SelectVisitor visitor = new SelectVisitor(translationUtility.createRuntimeMetadata()); visitor.visit(command); - assertEquals("SELECT id, AccountName, Stuff FROM Account WHERE (AccountName != 'foo') OR (Stuff != 'bar')", visitor.getQuery().toString().trim()); //$NON-NLS-1$ + assertEquals("SELECT Account.id, Account.AccountName, Account.Stuff, Account.Industry FROM Account WHERE NOT ((Account.AccountName = 'foo') AND (Account.Stuff = 'bar'))", visitor.getQuery().toString().trim()); //$NON-NLS-1$ } @Test public void testCountStart() throws Exception { @@ -103,7 +130,28 @@ IQuery command = (IQuery)translationUtility.parseCommand("select * from Account where Name not like '%foo' or Stuff = 'bar'"); //$NON-NLS-1$ SelectVisitor visitor = new SelectVisitor(translationUtility.createRuntimeMetadata()); visitor.visit(command); - assertEquals("SELECT id, AccountName, Stuff FROM Account WHERE (NOT (Account.AccountName LIKE '%foo')) OR (Stuff = 'bar')", visitor.getQuery().toString().trim()); //$NON-NLS-1$ + 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$ } + + // I can't really write this test until I see what teiid is going to pass the connectro based upon the user query. + // SELECT Account.Name AS c_0, Contact.Name AS c_1 FROM Contact LEFT OUTER JOIN Account ON Account.Id = Contact.AccountId + @Test public void testJoin() throws Exception { + IQuery command = (IQuery)translationUtility.parseCommand("SELECT Account.Name, Contact.Name FROM Contact LEFT OUTER JOIN Account ON Account.Id = Contact.AccountId"); //$NON-NLS-1$ + + SelectVisitor visitor = new JoinQueryVisitor(translationUtility.createRuntimeMetadata()); + visitor.visit(command); + assertEquals("SELECT Account.AccountName, Contact.ContactName FROM Contact", visitor.getQuery().toString().trim()); //$NON-NLS-1$ + // SELECT Contact.Id, Contact.Name, Account.Name FROM Contact WHERE Account.Industry != 'media' + // Looks like using q names will work, but we need to distinguish between the parent and child table somehow cannot send + // SELECT Contact.Id, Contact.Name, Account.Name FROM Contact, Account WHERE Account.Industry != 'media' + } + + @Test public void testJoin2() throws Exception { + IQuery command = (IQuery)translationUtility.parseCommand("SELECT Account.Name, Contact.Name FROM Account LEFT OUTER JOIN Contact ON Account.Id = Contact.AccountId"); //$NON-NLS-1$ + + SelectVisitor visitor = new JoinQueryVisitor(translationUtility.createRuntimeMetadata()); + visitor.visit(command); + assertEquals("SELECT Account.AccountName, (SELECT Contact.ContactName FROM Contacts) FROM Account", visitor.getQuery().toString().trim()); //$NON-NLS-1$ + } }