Author: shawkins Date: 2012-10-02 22:25:48 -0400 (Tue, 02 Oct 2012) New Revision: 4507 Modified: trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java trunk/api/src/main/resources/org/teiid/connector/i18n.properties trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/SQLConversionVisitor.java trunk/connectors/translator-jdbc/src/main/resources/org/teiid/translator/jdbc/i18n.properties trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/SalesForceExecutionFactory.java trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/SalesForcePlugin.java trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/DirectQueryExecution.java trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/ProcedureExecutionParentImpl.java trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/CriteriaVisitor.java trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/InsertVisitor.java trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/JoinQueryVisitor.java trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/SelectVisitor.java trunk/connectors/translator-salesforce/src/main/resources/org/teiid/translator/salesforce/i18n.properties trunk/connectors/translator-salesforce/src/test/java/org/teiid/translator/salesforce/execution/TestSalesForceDirectQueryExecution.java trunk/connectors/translator-salesforce/src/test/java/org/teiid/translator/salesforce/execution/visitors/TestVisitors.java trunk/test-integration/common/src/test/java/org/teiid/connector/visitor/util/TestSQLStringVisitor.java Log: TEIID-2177 using a sf native/canned query procedure Modified: trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java =================================================================== --- trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java 2012-10-03 02:15:04 UTC (rev 4506) +++ trunk/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java 2012-10-03 02:25:48 UTC (rev 4507) @@ -26,11 +26,15 @@ import java.util.Arrays; import java.util.HashSet; +import java.util.LinkedList; import java.util.List; import java.util.Set; +import java.util.regex.Matcher; +import java.util.regex.Pattern; import org.teiid.core.types.DataTypeManager; import org.teiid.core.util.StringUtil; +import org.teiid.jdbc.JDBCPlugin; import org.teiid.language.*; import org.teiid.language.Argument.Direction; import org.teiid.language.SQLConstants.NonReserved; @@ -45,7 +49,8 @@ * are not reusable, and are not thread-safe. */ public class SQLStringVisitor extends AbstractLanguageVisitor { - + public static final String TEIID_NATIVE_QUERY = AbstractMetadataRecord.RELATIONAL_URI + "native-query"; //$NON-NLS-1$ + private Set<String> infixFunctions = new HashSet<String>(Arrays.asList("%", "+", "-", "*", "+", "/", "||", //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$ "&", "|", "^", "#")); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ @@ -974,4 +979,40 @@ protected boolean useSelectLimit() { return false; } + + public static List<Object> parseNativeQueryParts(String nativeQuery, List<Argument> list) { + Pattern pattern = Pattern.compile("\\$+\\d+"); //$NON-NLS-1$ + List<Object> parts = new LinkedList<Object>(); + Matcher m = pattern.matcher(nativeQuery); + for (int i = 0; i < nativeQuery.length();) { + if (!m.find(i)) { + parts.add(nativeQuery.substring(i)); + break; + } + if (m.start() != i) { + parts.add(nativeQuery.substring(i, m.start())); + } + String match = m.group(); + int end = match.lastIndexOf('$'); + if ((end&0x1) == 1) { + //escaped + parts.add(match.substring((end+1)/2)); + } else { + if (end != 0) { + parts.add(match.substring(0, end/2)); + } + int index = Integer.parseInt(match.substring(end + 1))-1; + if (index < 0 || index >= list.size()) { + throw new IllegalArgumentException(JDBCPlugin.Util.getString("SQLConversionVisitor.invalid_parameter", index+1, list.size())); //$NON-NLS-1$ + } + Argument arg = list.get(index); + if (arg.getDirection() != Direction.IN) { + throw new IllegalArgumentException(JDBCPlugin.Util.getString("SQLConversionVisitor.not_in_parameter", index+1)); //$NON-NLS-1$ + } + parts.add(index); + } + i = m.end(); + } + return parts; + } } Modified: trunk/api/src/main/resources/org/teiid/connector/i18n.properties =================================================================== --- trunk/api/src/main/resources/org/teiid/connector/i18n.properties 2012-10-03 02:15:04 UTC (rev 4506) +++ trunk/api/src/main/resources/org/teiid/connector/i18n.properties 2012-10-03 02:25:48 UTC (rev 4507) @@ -38,3 +38,7 @@ TEIID60001=Unsupported Execution {0} TEIID60010=No unique key defined for table {0} for columns {1} TEIID60004=Neither class name nor default class specified to create an instance + +SQLConversionVisitor.invalid_parameter=Invalid parameter {0}. Must be between 1 and {1}. +SQLConversionVisitor.not_in_parameter=Invalid parameter {0}. Native query procedures cannot use non IN parameters. + Modified: trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/SQLConversionVisitor.java =================================================================== --- trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/SQLConversionVisitor.java 2012-10-03 02:15:04 UTC (rev 4506) +++ trunk/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/SQLConversionVisitor.java 2012-10-03 02:25:48 UTC (rev 4507) @@ -32,12 +32,9 @@ import java.util.ArrayList; import java.util.Collections; import java.util.IdentityHashMap; -import java.util.LinkedList; import java.util.List; import java.util.Map; import java.util.Set; -import java.util.regex.Matcher; -import java.util.regex.Pattern; import org.teiid.language.*; import org.teiid.language.Argument.Direction; @@ -56,7 +53,6 @@ * to produce a SQL String. This class is expected to be subclassed. */ public class SQLConversionVisitor extends SQLStringVisitor{ - public static final String TEIID_NATIVE_QUERY = AbstractMetadataRecord.RELATIONAL_URI + "native-query"; //$NON-NLS-1$ public static final String TEIID_NON_PREPARED = AbstractMetadataRecord.RELATIONAL_URI + "non-prepared"; //$NON-NLS-1$ private static DecimalFormat DECIMAL_FORMAT = @@ -182,7 +178,7 @@ if (p != null) { String nativeQuery = p.getProperty(TEIID_NATIVE_QUERY, false); if (nativeQuery != null) { - List<Object> parts = parseNativeQueryParts(nativeQuery); + List<Object> parts = parseNativeQueryParts(nativeQuery, obj.getArguments()); this.prepared = !Boolean.valueOf(p.getProperty(TEIID_NON_PREPARED, false)); if (this.prepared) { this.preparedValues = new ArrayList<Object>(); @@ -191,18 +187,11 @@ if (o instanceof String) { buffer.append(o); } else { - Integer i = (Integer)o; - if (i < 0 || i >= obj.getArguments().size()) { - throw new IllegalArgumentException(JDBCPlugin.Util.getString("SQLConversionVisitor.invalid_parameter", i+1, obj.getArguments().size())); //$NON-NLS-1$ - } - if (obj.getArguments().get(i).getDirection() != Direction.IN) { - throw new IllegalArgumentException(JDBCPlugin.Util.getString("SQLConversionVisitor.not_in_parameter", i+1)); //$NON-NLS-1$ - } if (this.prepared) { buffer.append('?'); this.preparedValues = obj.getArguments(); } else { - this.visit(obj.getArguments().get(i).getArgumentValue()); + visit(obj.getArguments().get((Integer)o)); } } } @@ -217,34 +206,6 @@ buffer.append(generateSqlForStoredProcedure(obj)); } - private List<Object> parseNativeQueryParts(String nativeQuery) { - Pattern pattern = Pattern.compile("\\$+\\d+"); //$NON-NLS-1$ - List<Object> parts = new LinkedList<Object>(); - Matcher m = pattern.matcher(nativeQuery); - for (int i = 0; i < nativeQuery.length();) { - if (!m.find(i)) { - parts.add(nativeQuery.substring(i)); - break; - } - if (m.start() != i) { - parts.add(nativeQuery.substring(i, m.start())); - } - String match = m.group(); - int end = match.lastIndexOf('$'); - if ((end&0x1) == 1) { - //escaped - parts.add(match.substring((end+1)/2)); - } else { - if (end != 0) { - parts.add(match.substring(0, end/2)); - } - parts.add(Integer.parseInt(match.substring(end + 1))-1); - } - i = m.end(); - } - return parts; - } - @Override public void visit(Parameter obj) { buffer.append(UNDEFINED_PARAM); @@ -434,5 +395,4 @@ super.appendBaseName(obj); } - } Modified: trunk/connectors/translator-jdbc/src/main/resources/org/teiid/translator/jdbc/i18n.properties =================================================================== --- trunk/connectors/translator-jdbc/src/main/resources/org/teiid/translator/jdbc/i18n.properties 2012-10-03 02:15:04 UTC (rev 4506) +++ trunk/connectors/translator-jdbc/src/main/resources/org/teiid/translator/jdbc/i18n.properties 2012-10-03 02:25:48 UTC (rev 4507) @@ -26,8 +26,6 @@ TEIID11004=Error executing statement(s): {0} -SQLConversionVisitor.invalid_parameter=Invalid parameter {0}. Must be between 1 and {1}. -SQLConversionVisitor.not_in_parameter=Invalid parameter {0}. Native query procedures cannot use non IN parameters. TEIID11003=Not using oracle execution payload {0} as hint, since it appears to contain more than just a single comment. TEIID11002=Failed to report the JDBC driver and connection information TEIID11006=Teiid runtime names, which are case insensitive, for the imported metadata are not unique. If not already set, use the setting importer.useFullSchemaName to create Teiid names that include the source schema. Modified: trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/SalesForceExecutionFactory.java =================================================================== --- trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/SalesForceExecutionFactory.java 2012-10-03 02:15:04 UTC (rev 4506) +++ trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/SalesForceExecutionFactory.java 2012-10-03 02:25:48 UTC (rev 4507) @@ -33,6 +33,7 @@ import org.teiid.language.Call; import org.teiid.language.Command; import org.teiid.language.QueryExpression; +import org.teiid.language.visitor.SQLStringVisitor; import org.teiid.logging.LogConstants; import org.teiid.logging.LogManager; import org.teiid.metadata.MetadataFactory; @@ -55,10 +56,10 @@ import org.teiid.translator.salesforce.execution.ProcedureExecutionParentImpl; import org.teiid.translator.salesforce.execution.QueryExecutionImpl; import org.teiid.translator.salesforce.execution.UpdateExecutionImpl; +import org.teiid.translator.salesforce.execution.visitors.CriteriaVisitor; @Translator(name="salesforce", description="A translator for Salesforce") public class SalesForceExecutionFactory extends ExecutionFactory<ConnectionFactory, SalesforceConnection> { - 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$ @@ -113,12 +114,30 @@ @Override public ProcedureExecution createProcedureExecution(Call command,ExecutionContext executionContext, RuntimeMetadata metadata, SalesforceConnection connection) throws TranslatorException { + Procedure metadataObject = command.getMetadataObject(); + String nativeQuery = metadataObject.getProperty(SQLStringVisitor.TEIID_NATIVE_QUERY, false); + if (nativeQuery != null) { + if (nativeQuery.startsWith(DirectQueryExecution.SEARCH)) { + StringBuilder buffer = new StringBuilder(); + List<Object> parts = SQLStringVisitor.parseNativeQueryParts(nativeQuery, command.getArguments()); + for (Object o : parts) { + if (o instanceof String) { + buffer.append(o); + } else { + Integer i = (Integer)o; + CriteriaVisitor.appendLiteralValue(buffer, command.getArguments().get(i).getArgumentValue()); + } + } + nativeQuery = buffer.toString(); + } + return new DirectQueryExecution(command.getArguments(), command, connection, metadata, executionContext, nativeQuery); + } return new ProcedureExecutionParentImpl(command, connection, metadata, executionContext); } @Override public ProcedureExecution createDirectExecution(List<Argument> arguments, Command command, ExecutionContext executionContext, RuntimeMetadata metadata, SalesforceConnection connection) throws TranslatorException { - return new DirectQueryExecution(arguments, command, connection, metadata, executionContext); + return new DirectQueryExecution(arguments.subList(1, arguments.size()), command, connection, metadata, executionContext, (String)arguments.get(0).getArgumentValue().getValue()); } @Override Modified: trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/SalesForcePlugin.java =================================================================== --- trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/SalesForcePlugin.java 2012-10-03 02:15:04 UTC (rev 4506) +++ trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/SalesForcePlugin.java 2012-10-03 02:25:48 UTC (rev 4507) @@ -45,7 +45,6 @@ public static enum Event implements BundleUtil.Event{ TEIID13001, TEIID13002, - TEIID13003, TEIID13004, TEIID13005, } Modified: trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/DirectQueryExecution.java =================================================================== --- trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/DirectQueryExecution.java 2012-10-03 02:15:04 UTC (rev 4506) +++ trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/DirectQueryExecution.java 2012-10-03 02:25:48 UTC (rev 4507) @@ -39,6 +39,7 @@ 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.w3c.dom.Element; import com.sforce.soap.partner.QueryResult; @@ -46,33 +47,43 @@ public class DirectQueryExecution implements ProcedureExecution { - private static final String DELETE_IDS = "ids"; //$NON-NLS-1$ + public static final String SEARCH = "search;"; //$NON-NLS-1$ private static final String ATTRIBUTES = "attributes"; //$NON-NLS-1$ private static final String TYPE = "type"; //$NON-NLS-1$ private static final String ID = "id"; //$NON-NLS-1$ - private List<Argument> arguments; - private Command command; + protected List<Argument> arguments; + protected Command command; private SalesforceConnection connection; - private RuntimeMetadata metadata; + protected RuntimeMetadata metadata; private ExecutionContext context; private QueryResult results; private List<List<Object>> currentBatch; private int updateCount = -1; - private boolean updateQuery = false; + private boolean updateQuery = false; + private String query; - public DirectQueryExecution(List<Argument> arguments, Command command, SalesforceConnection connection, RuntimeMetadata metadata, ExecutionContext context) { + /** + * + * @param arguments parameter arguments only + * @param command + * @param connection + * @param metadata + * @param context + * @param query + */ + public DirectQueryExecution(List<Argument> arguments, Command command, SalesforceConnection connection, RuntimeMetadata metadata, ExecutionContext context, String query) { this.arguments = arguments; this.command = command; this.connection = connection; this.metadata = metadata; this.context = context; + this.query = query; } @Override public void execute() throws TranslatorException { - String query = (String)this.arguments.get(0).getArgumentValue().getValue(); - if (query.startsWith("search;")) { //$NON-NLS-1$ + if (query.startsWith(SEARCH)) { doSelect(query.substring(7)); } else if (query.startsWith("create;")) { //$NON-NLS-1$ @@ -82,16 +93,21 @@ doUpdate(query.substring(7)); } else if (query.startsWith("delete;")) { //$NON-NLS-1$ - doDelete(query.substring(7)); + doDelete(); } else { throw new TranslatorException(SalesForcePlugin.Util.gs(SalesForcePlugin.Event.TEIID13002)); } - } - private void doDelete(String query) throws TranslatorException { - List<String> ids = getIds(query); + private void doDelete() throws TranslatorException { + List<String> ids = new ArrayList<String>(); + for (Argument arg : arguments) { + Object val = arg.getArgumentValue().getValue(); + if (val != null) { + ids.add(Util.stripQutes(val.toString())); + } + } try { this.updateCount = this.connection.delete(ids.toArray(new String[ids.size()])); this.updateQuery = true; @@ -100,8 +116,8 @@ } } - private void doUpdate(String query) throws TranslatorException { - DataPayload payload = buildDataPlayload(query, this.arguments); + private void doUpdate(String update) throws TranslatorException { + DataPayload payload = buildDataPlayload(update); try { this.updateCount = this.connection.update(Arrays.asList(payload)); this.updateQuery = true; @@ -110,8 +126,8 @@ } } - private void doInsert(String query) throws TranslatorException { - DataPayload payload = buildDataPlayload(query, this.arguments); + private void doInsert(String insert) throws TranslatorException { + DataPayload payload = buildDataPlayload(insert); try { this.updateCount = this.connection.create(payload); this.updateQuery = true; @@ -120,9 +136,9 @@ } } - private void doSelect(String query) throws TranslatorException { + private void doSelect(String select) throws TranslatorException { try { - this.results = this.connection.query(query, this.context.getBatchSize(), Boolean.FALSE); + this.results = this.connection.query(select, this.context.getBatchSize(), Boolean.FALSE); } catch (ResourceException e) { throw new TranslatorException(e); } @@ -139,12 +155,12 @@ return row; } - private List<Object> getRow(QueryResult result) throws TranslatorException { + private List<?> getRow(QueryResult result) throws TranslatorException { // for insert/update/delete clauses if (this.updateQuery) { if (this.updateCount != -1) { - List updateResult = Arrays.asList(this.updateCount); + List<?> updateResult = Arrays.asList(this.updateCount); this.updateCount = -1; return updateResult; } @@ -203,36 +219,9 @@ @Override public void cancel() throws TranslatorException { } - - private ArrayList<String> getIds(String query) throws TranslatorException { - StringTokenizer st = new StringTokenizer(query, ";"); //$NON-NLS-1$ - if (!st.hasMoreTokens()) { - throw new TranslatorException(SalesForcePlugin.Util.gs(SalesForcePlugin.Event.TEIID13003)); - } - ArrayList<String> ids = new ArrayList<String>(); - - while(st.hasMoreElements()) { - String var = st.nextToken(); - int index = var.indexOf('='); - if (index == -1) { - continue; - } - String key = var.substring(0, index).trim().toLowerCase(); - String value = var.substring(index+1).trim(); - - if (key.equalsIgnoreCase(DELETE_IDS)) { - StringTokenizer attrTokens = new StringTokenizer(value, ","); //$NON-NLS-1$ - while (attrTokens.hasMoreElements()) { - ids.add(attrTokens.nextToken()); - } - } - } - return ids; - } - - private DataPayload buildDataPlayload(String query, List<Argument> arguments) throws TranslatorException { - StringTokenizer st = new StringTokenizer(query, ";"); //$NON-NLS-1$ + private DataPayload buildDataPlayload(String update) throws TranslatorException { + StringTokenizer st = new StringTokenizer(update, ";"); //$NON-NLS-1$ if (!st.hasMoreTokens()) { throw new TranslatorException(SalesForcePlugin.Util.gs(SalesForcePlugin.Event.TEIID13004)); } @@ -254,7 +243,7 @@ if (key.equalsIgnoreCase(ATTRIBUTES)) { StringTokenizer attrTokens = new StringTokenizer(value, ","); //$NON-NLS-1$ - int attrCount = 1; + int attrCount = 0; while(attrTokens.hasMoreElements()) { String name = attrTokens.nextToken().trim(); if (arguments.size() <= attrCount) { @@ -262,6 +251,10 @@ } Argument argument = arguments.get(attrCount++); Object anObj = argument.getArgumentValue().getValue(); + if (anObj == null) { + continue; + } + anObj = Util.stripQutes(anObj.toString()); QName qname = new QName(name); @SuppressWarnings( "unchecked" ) JAXBElement jbe = new JAXBElement( qname, String.class, anObj ); Modified: trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/ProcedureExecutionParentImpl.java =================================================================== --- trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/ProcedureExecutionParentImpl.java 2012-10-03 02:15:04 UTC (rev 4506) +++ trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/ProcedureExecutionParentImpl.java 2012-10-03 02:25:48 UTC (rev 4507) @@ -24,6 +24,7 @@ import java.util.List; +import org.teiid.core.TeiidRuntimeException; import org.teiid.language.Call; import org.teiid.metadata.RuntimeMetadata; import org.teiid.translator.DataNotAvailableException; @@ -82,7 +83,7 @@ } else if(GET_DELETED.equalsIgnoreCase(name)) { execution = new GetDeletedExecutionImpl(this); } else { - throw new AssertionError("Unknown procedure " + getCommand().getProcedureName() + " with name in source " + getCommand().getMetadataObject().getNameInSource()); //$NON-NLS-1$ //$NON-NLS-2$ + throw new TeiidRuntimeException("Unknown procedure " + getCommand().getProcedureName() + " with name in source " + name); //$NON-NLS-1$ //$NON-NLS-2$ } execution.execute(this); } Modified: trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/CriteriaVisitor.java =================================================================== --- trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/CriteriaVisitor.java 2012-10-03 02:15:04 UTC (rev 4506) +++ trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/CriteriaVisitor.java 2012-10-03 02:25:48 UTC (rev 4507) @@ -308,44 +308,15 @@ } else if (expr instanceof Literal) { Literal literal = (Literal)expr; if (literal.getValue() == null) { - if (raw) { - return null; - } - return "NULL"; //$NON-NLS-1$ + 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()); - } + if (raw) { + return literal.getValue().toString(); + } + appendLiteralValue(result, literal); } else if (expr instanceof AggregateFunction) { appendAggregateFunction(result, (AggregateFunction)expr); } else { @@ -353,6 +324,39 @@ } return result.toString(); } + + public static void appendLiteralValue(StringBuilder result, Literal literal) { + 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(literal.toString()); + } + } protected void appendAggregateFunction(StringBuilder result, AggregateFunction af) { Modified: trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/InsertVisitor.java =================================================================== --- trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/InsertVisitor.java 2012-10-03 02:15:04 UTC (rev 4506) +++ trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/InsertVisitor.java 2012-10-03 02:25:48 UTC (rev 4507) @@ -35,6 +35,7 @@ import org.teiid.metadata.Column; import org.teiid.metadata.RuntimeMetadata; import org.teiid.translator.TranslatorException; +import org.teiid.translator.salesforce.Util; public class InsertVisitor extends CriteriaVisitor { @@ -69,7 +70,7 @@ Literal literalValue = (Literal)value; val = literalValue.getValue().toString(); if(null != val && !val.isEmpty()) { - val = this.stripQutes(val); + val = Util.stripQutes(val); } } else { val = value.toString(); @@ -89,13 +90,5 @@ public List<JAXBElement> getMessageElements() { return elements; } - - private String stripQutes(String id) { - if((id.startsWith("'") && id.endsWith("'"))) { - id = id.substring(1,id.length()-1); - } else if ((id.startsWith("\"") && id.endsWith("\""))) { - id = id.substring(1,id.length()-1); - } - return id; - } + } Modified: trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/JoinQueryVisitor.java =================================================================== --- trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/JoinQueryVisitor.java 2012-10-03 02:15:04 UTC (rev 4506) +++ trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/JoinQueryVisitor.java 2012-10-03 02:25:48 UTC (rev 4507) @@ -99,29 +99,17 @@ addSelect(rightTableInJoin.getNameInSource(), subselect, false); subselect.append(SPACE); - String nativeQuery = this.rightTableInJoin.getProperty(TEIID_NATIVE_QUERY, false); - if (nativeQuery != null) { - subselect.append(nativeQuery); - } - else { - subselect.append(FROM).append(SPACE); - subselect.append(rightTableInJoin.getNameInSource()).append('s'); - } + subselect.append(FROM).append(SPACE); + subselect.append(rightTableInJoin.getNameInSource()).append('s'); subselect.append(CLOSE).append(SPACE); select.append(subselect); - nativeQuery = this.leftTableInJoin.getProperty(TEIID_NATIVE_QUERY, false); - if (nativeQuery != null) { - select.append(nativeQuery); - } - else { - select.append(FROM).append(SPACE); - select.append(leftTableInJoin.getNameInSource()).append(SPACE); - addCriteriaString(select); - appendGroupByHaving(select); - select.append(limitClause); - } + select.append(FROM).append(SPACE); + select.append(leftTableInJoin.getNameInSource()).append(SPACE); + addCriteriaString(select); + appendGroupByHaving(select); + select.append(limitClause); return select.toString(); } Modified: trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/SelectVisitor.java =================================================================== --- trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/SelectVisitor.java 2012-10-03 02:15:04 UTC (rev 4506) +++ trunk/connectors/translator-salesforce/src/main/java/org/teiid/translator/salesforce/execution/visitors/SelectVisitor.java 2012-10-03 02:25:48 UTC (rev 4507) @@ -150,18 +150,12 @@ addSelectSymbols(result); result.append(SPACE); - String nativeQuery = this.table.getProperty(TEIID_NATIVE_QUERY, false); - if (nativeQuery != null) { - result.append(nativeQuery); - } - else { - result.append(FROM).append(SPACE); - result.append(table.getNameInSource()).append(SPACE); - addCriteriaString(result); - appendGroupByHaving(result); - //result.append(orderByClause).append(SPACE); - result.append(limitClause); - } + result.append(FROM).append(SPACE); + result.append(table.getNameInSource()).append(SPACE); + addCriteriaString(result); + appendGroupByHaving(result); + //result.append(orderByClause).append(SPACE); + result.append(limitClause); return result.toString(); } Modified: trunk/connectors/translator-salesforce/src/main/resources/org/teiid/translator/salesforce/i18n.properties =================================================================== --- trunk/connectors/translator-salesforce/src/main/resources/org/teiid/translator/salesforce/i18n.properties 2012-10-03 02:15:04 UTC (rev 4506) +++ trunk/connectors/translator-salesforce/src/main/resources/org/teiid/translator/salesforce/i18n.properties 2012-10-03 02:25:48 UTC (rev 4507) @@ -39,6 +39,5 @@ TEIID13001=Unknown type returned by SalesForce: {0} TEIID13002=Unknown request; query must start with one of [search|create|update|delete] -TEIID13003=The query is missing "id=x,y.." values. TEIID13004=The query is missing type, id and attribute values. TEIID13005=The attribute count does not match with the value parameters supplied. \ No newline at end of file Modified: trunk/connectors/translator-salesforce/src/test/java/org/teiid/translator/salesforce/execution/TestSalesForceDirectQueryExecution.java =================================================================== --- trunk/connectors/translator-salesforce/src/test/java/org/teiid/translator/salesforce/execution/TestSalesForceDirectQueryExecution.java 2012-10-03 02:15:04 UTC (rev 4506) +++ trunk/connectors/translator-salesforce/src/test/java/org/teiid/translator/salesforce/execution/TestSalesForceDirectQueryExecution.java 2012-10-03 02:25:48 UTC (rev 4507) @@ -21,9 +21,7 @@ */ package org.teiid.translator.salesforce.execution; -import static org.junit.Assert.assertArrayEquals; -import static org.junit.Assert.assertEquals; -import static org.junit.Assert.fail; +import static org.junit.Assert.*; import java.util.ArrayList; import java.util.List; @@ -102,7 +100,7 @@ } - @Test public void testWithoutMarker() throws Exception { + @Test(expected=TranslatorException.class) public void testWithoutMarker() throws Exception { String input = "exec native('salesforce query')"; TranslationUtility util = FakeTranslationFactory.getInstance().getExampleTranslationUtility(); @@ -111,16 +109,12 @@ RuntimeMetadata rm = Mockito.mock(RuntimeMetadata.class); SalesforceConnection connection = Mockito.mock(SalesforceConnection.class); - try { - DirectQueryExecution execution = (DirectQueryExecution)TRANSLATOR.createExecution(command, ec, rm, connection); - execution.execute(); - fail("the above should have thrown exception"); - } catch (TranslatorException e) { - } + DirectQueryExecution execution = (DirectQueryExecution)TRANSLATOR.createExecution(command, ec, rm, connection); + execution.execute(); } @Test public void testDelete() throws Exception { - String input = "exec native('delete;ids=id1,id2')"; + String input = "exec native('delete;', 'id1','id2')"; TranslationUtility util = FakeTranslationFactory.getInstance().getExampleTranslationUtility(); Command command = util.parseCommand(input); @@ -192,7 +186,7 @@ assertArrayEquals(new Object[] {23}, (Object[])execution.next().get(0)); } - @Test public void testCreateFail() throws Exception { + @Test(expected=TranslatorException.class) public void testCreateFail() throws Exception { String input = "exec native('create;id=pk;type=table;attributes=one,two,three', 'one')"; TranslationUtility util = FakeTranslationFactory.getInstance().getExampleTranslationUtility(); @@ -201,11 +195,7 @@ RuntimeMetadata rm = Mockito.mock(RuntimeMetadata.class); SalesforceConnection connection = Mockito.mock(SalesforceConnection.class); - try { - DirectQueryExecution execution = (DirectQueryExecution)TRANSLATOR.createExecution(command, ec, rm, connection); - execution.execute(); - fail("should have failed because there are not enough values"); - } catch (TranslatorException e) { - } + DirectQueryExecution execution = (DirectQueryExecution)TRANSLATOR.createExecution(command, ec, rm, connection); + execution.execute(); } } Modified: trunk/connectors/translator-salesforce/src/test/java/org/teiid/translator/salesforce/execution/visitors/TestVisitors.java =================================================================== --- trunk/connectors/translator-salesforce/src/test/java/org/teiid/translator/salesforce/execution/visitors/TestVisitors.java 2012-10-03 02:15:04 UTC (rev 4506) +++ trunk/connectors/translator-salesforce/src/test/java/org/teiid/translator/salesforce/execution/visitors/TestVisitors.java 2012-10-03 02:25:48 UTC (rev 4507) @@ -35,7 +35,9 @@ import org.mockito.Mockito; import org.teiid.cdk.api.TranslationUtility; import org.teiid.core.types.DataTypeManager; +import org.teiid.language.Command; import org.teiid.language.Select; +import org.teiid.language.visitor.SQLStringVisitor; import org.teiid.metadata.Column; import org.teiid.metadata.MetadataStore; import org.teiid.metadata.Procedure; @@ -49,9 +51,8 @@ import org.teiid.query.metadata.TransformationMetadata; import org.teiid.query.sql.lang.SPParameter; import org.teiid.query.unittest.RealMetadataFactory; +import org.teiid.translator.Execution; import org.teiid.translator.ExecutionContext; -import org.teiid.translator.ResultSetExecution; -import org.teiid.translator.TranslatorException; import org.teiid.translator.TypeFacility; import org.teiid.translator.salesforce.Constants; import org.teiid.translator.salesforce.SalesForceExecutionFactory; @@ -115,17 +116,6 @@ obj.setNameInSource(contactNameInSource[i]); } - Table nativeTable = RealMetadataFactory.createPhysicalGroup("Native", salesforceModel); //$NON-NLS-1$ - nativeTable.setNameInSource("Native"); //$NON-NLS-1$ - nativeTable.setProperty("Supports Query", Boolean.TRUE.toString()); //$NON-NLS-1$ - nativeTable.setProperty(SelectVisitor.TEIID_NATIVE_QUERY, "FROM MyTable WHERE Anything='goes'"); - - List<Column> contactCols2 = RealMetadataFactory.createElements(nativeTable, elemNames, elemTypes); - for(int i=0; i<2; i++) { - Column obj = contactCols2.get(i); - obj.setNameInSource(contactNameInSource[i]); - } - List<ProcedureParameter> params = new LinkedList<ProcedureParameter>(); params.add(RealMetadataFactory.createParameter("type", SPParameter.IN, TypeFacility.RUNTIME_NAMES.STRING)); params.add(RealMetadataFactory.createParameter("start", SPParameter.IN, TypeFacility.RUNTIME_NAMES.TIMESTAMP)); @@ -133,7 +123,14 @@ Procedure getUpdated = RealMetadataFactory.createStoredProcedure("GetUpdated", salesforceModel, params); getUpdated.setResultSet(RealMetadataFactory.createResultSet("rs", new String[] {"updated"}, new String[] {TypeFacility.RUNTIME_NAMES.STRING})); + + params = new LinkedList<ProcedureParameter>(); + params.add(RealMetadataFactory.createParameter("x", SPParameter.IN, TypeFacility.RUNTIME_NAMES.STRING)); + Procedure nativeProc = RealMetadataFactory.createStoredProcedure("native", salesforceModel, params); + nativeProc.setProperty(SQLStringVisitor.TEIID_NATIVE_QUERY, "search;select accountname from account where accountid = $1"); + nativeProc.setResultSet(RealMetadataFactory.createResultSet("rs", new String[] {"accountname"}, new String[] {TypeFacility.RUNTIME_NAMES.STRING})); + return new TransformationMetadata(null, new CompositeMetadataStore(store), null, RealMetadataFactory.SFM.getSystemFunctions(), null); } @@ -269,7 +266,7 @@ } private void helpTest(String sql, String expected) throws Exception { - Select command = (Select)translationUtility.parseCommand(sql); + Command command = translationUtility.parseCommand(sql); SalesForceExecutionFactory factory = new SalesForceExecutionFactory(); ExecutionContext ec = Mockito.mock(ExecutionContext.class); RuntimeMetadata rm = Mockito.mock(RuntimeMetadata.class); @@ -278,7 +275,7 @@ ArgumentCaptor<String> queryArgument = ArgumentCaptor.forClass(String.class); Mockito.stub(connection.query(queryArgument.capture(), Mockito.anyInt(), Mockito.anyBoolean())).toReturn(Mockito.mock(QueryResult.class)); - ResultSetExecution execution = factory.createResultSetExecution(command, ec, rm, connection); + Execution execution = factory.createExecution(command, ec, rm, connection); execution.execute(); Mockito.verify(connection, Mockito.times(1)).query(queryArgument.capture(), Mockito.anyInt(), Mockito.anyBoolean()); @@ -286,15 +283,10 @@ assertEquals(expected, queryArgument.getValue().trim()); } - @Test public void testNativeQuery() throws Exception { - String sql = "select name, accountid From Native"; - String source = "SELECT Native.ContactName, Native.AccountId FROM MyTable WHERE Anything='goes'"; + @Test public void testNativeProc() throws Exception { + String sql = "exec native('1')"; + String source = "select accountname from account where accountid = '1'"; helpTest(sql, source); } - @Test public void testNativeQueryWithJoin() throws Exception { - String sql = "select n.name, n.accountid, a.industry From Native n left outer join Account a on n.ContactID = a.id"; - String expected = "SELECT Native.ContactName, Native.AccountId, (SELECT Account.Industry FROM Accounts) FROM MyTable WHERE Anything='goes'"; - helpTest(sql, expected); - } } Modified: trunk/test-integration/common/src/test/java/org/teiid/connector/visitor/util/TestSQLStringVisitor.java =================================================================== --- trunk/test-integration/common/src/test/java/org/teiid/connector/visitor/util/TestSQLStringVisitor.java 2012-10-03 02:15:04 UTC (rev 4506) +++ trunk/test-integration/common/src/test/java/org/teiid/connector/visitor/util/TestSQLStringVisitor.java 2012-10-03 02:25:48 UTC (rev 4507) @@ -32,41 +32,9 @@ import org.junit.Test; import org.teiid.cdk.unittest.FakeTranslationFactory; -import org.teiid.dqp.internal.datamgr.TestAggregateImpl; -import org.teiid.dqp.internal.datamgr.TestCompareCriteriaImpl; -import org.teiid.dqp.internal.datamgr.TestDeleteImpl; -import org.teiid.dqp.internal.datamgr.TestElementImpl; -import org.teiid.dqp.internal.datamgr.TestExistsCriteriaImpl; -import org.teiid.dqp.internal.datamgr.TestFunctionImpl; -import org.teiid.dqp.internal.datamgr.TestGroupByImpl; -import org.teiid.dqp.internal.datamgr.TestGroupImpl; -import org.teiid.dqp.internal.datamgr.TestInCriteriaImpl; -import org.teiid.dqp.internal.datamgr.TestInsertImpl; -import org.teiid.dqp.internal.datamgr.TestIsNullCriteriaImpl; -import org.teiid.dqp.internal.datamgr.TestJoinImpl; -import org.teiid.dqp.internal.datamgr.TestLikeCriteriaImpl; -import org.teiid.dqp.internal.datamgr.TestLiteralImpl; -import org.teiid.dqp.internal.datamgr.TestNotCriteriaImpl; -import org.teiid.dqp.internal.datamgr.TestOrderByImpl; -import org.teiid.dqp.internal.datamgr.TestProcedureImpl; -import org.teiid.dqp.internal.datamgr.TestQueryImpl; -import org.teiid.dqp.internal.datamgr.TestScalarSubqueryImpl; -import org.teiid.dqp.internal.datamgr.TestSearchedCaseExpressionImpl; -import org.teiid.dqp.internal.datamgr.TestSelectSymbolImpl; -import org.teiid.dqp.internal.datamgr.TestSetQueryImpl; -import org.teiid.dqp.internal.datamgr.TestSubqueryCompareCriteriaImpl; -import org.teiid.dqp.internal.datamgr.TestSubqueryInCriteriaImpl; -import org.teiid.dqp.internal.datamgr.TestUpdateImpl; -import org.teiid.dqp.internal.datamgr.TstLanguageBridgeFactory; -import org.teiid.language.AggregateFunction; -import org.teiid.language.ColumnReference; -import org.teiid.language.Command; -import org.teiid.language.Expression; -import org.teiid.language.Function; -import org.teiid.language.Insert; -import org.teiid.language.LanguageObject; -import org.teiid.language.Literal; -import org.teiid.language.Select; +import org.teiid.dqp.internal.datamgr.*; +import org.teiid.language.*; +import org.teiid.language.Argument.Direction; import org.teiid.language.SQLConstants.NonReserved; import org.teiid.language.visitor.SQLStringVisitor; import org.teiid.metadata.RuntimeMetadata; @@ -439,5 +407,17 @@ Command command = FakeTranslationFactory.getInstance().getBQTTranslationUtility().parseCommand(sql, true, true); assertEquals("SELECT trim('x' FROM g_0.StringKey) FROM SmallA AS g_0", command.toString()); //$NON-NLS-1$ } + + @Test public void testNativeParsing() throws Exception { + String sql = "select $1 from $2"; + List<Object> parts = SQLStringVisitor.parseNativeQueryParts(sql, Arrays.asList(new Argument(Direction.IN, null, String.class, null), new Argument(Direction.IN, null, String.class, null))); + assertEquals(Arrays.asList((Object)"select ", 0, " from ", 1), parts); + } + + @Test public void testNativeParsing1() throws Exception { + String sql = "select $$1 from $$$2"; + List<Object> parts = SQLStringVisitor.parseNativeQueryParts(sql, Arrays.asList(new Argument(Direction.IN, null, String.class, null), new Argument(Direction.IN, null, String.class, null))); + assertEquals(Arrays.asList((Object)"select ", "$1", " from ", "$", 1), parts); + } }