Red Hat Training

A Red Hat training course is available for Red Hat JBoss Data Virtualization

13.26. Salesforce Translator

13.26.1. Salesforce Translator

The Salesforce translator supports the SELECT, DELETE, INSERT and UPDATE operations against a Salesforce.com account.
The Salesforce translator is implemented by the org.teiid.translator.salesforce.SalesForceExecutionFactory class and known by the translator type name salesforce.

Note

The resource adapter for this translator is provided by configuring the salesforce data source in the JBoss EAP instance. See the Red Hat JBoss Data Virtualization Administration and Configuration Guide for more configuration information.

13.26.2. Salesforce Translator: Execution Properties

Table 13.24. Execution Properties

Name Description Default
ModelAuditFields Audit Model Fields false
MaxBulkInsertBatchSize Batch size to use when inserting in bulk 2048
The Salesforce translator can import metadata.

Table 13.25. Import Properties

Name Description Required Default
NormalizeNames If the importer should attempt to modify the object/field names so that they can be used unquoted. false true
excludeTables A case-insensitive regular expression that when matched against a table name will exclude it from import. Applied after table names are retrieved. Use a negative look-ahead inclusion pattern to act as an inclusion filter. false n/a
includeTables A case-insensitive regular expression that when matched against a table name will be included during import. Applied after table names are retrieved from source. false n/a
importStatstics Retrieves cardinalities during import using the REST API explain plan feature. false false
When both includeTables and excludeTables patterns are present during the import, the includeTables pattern matched first, then the excludePatterns will be applied.

13.26.3. Salesforce Translator: SQL Processing

Salesforce does not provide the same set of functionality as a relational database. For example, Salesforce does not support arbitrary joins between tables. However, working in combination with the JBoss Data Virtualization Query Planner, the Salesforce connector supports nearly all of the SQL syntax supported by JBoss Data Virtualization.
The Salesforce Connector executes SQL commands by pushing down the command to Salesforce whenever possible, based on the supported capabilities. JBoss Data Virtualization will automatically provide additional database functionality when the Salesforce Connector does not explicitly provide support for a given SQL construct. In these cases, the SQL construct cannot be pushed down€to the data source, so it will be evaluated in JBoss Data Virtualization, in order to ensure that the operation is performed.
In cases where certain SQL capabilities cannot be pushed down to Salesforce, JBoss Data Virtualization will push down the capabilities that are supported, and fetch a set of data from Salesforce. Then, JBoss Data Virtualization will evaluate the additional capabilities, creating a subset of the original data set. Finally, JBoss Data Virtualization will pass the result to the client. 
SELECT sum(Reports) FROM Supervisor where Division = 'customer support';
Neither Salesforce nor the Salesforce Connector support the sum() scalar function, but they do support CompareCriteriaEquals, so the query that is passed to Salesforce by the connector will be transformed to this query. 
SELECT Reports FROM Supervisor where Division = 'customer support';
The sum() scalar function will be applied by the JBoss Data Virtualization Query Engine to the result set returned by the connector.
In some cases multiple calls to the Salesforce application will be made to support the SQL passed to the connector. 
DELETE From Case WHERE Status = 'Closed';
The API in Salesforce to delete objects only supports deleting by ID. In order to accomplish this, the Salesforce connector will first execute a query to get the IDs of the correct objects, and then delete those objects. So the above DELETE command will result in the following two commands. 
SELECT ID From Case WHERE Status = 'Closed';
DELETE From Case where ID IN (<result of query>);
The Salesforce API DELETE call is not expressed in SQL, but the above is an SQL equivalent expression.
It is useful to be aware of unsupported capabilities, in order to avoid fetching large data sets from Salesforce and making your queries perform as well as possible.

13.26.4. Salesforce Translator: Multi-Select Picklists

A multi-select pick list is a field type in Salesforce that can contain multiple values in a single field. Query criteria operators for fields of this type in Salesforce Object Query Language (SOQL) are limited to EQ, NE, includes and excludes. The full Salesforce documentation for selecting from mullti-select pick lists can be found at Querying Mulit-select Picklists.
JBoss Data Virtualization SQL does not support the includes or excludes operators, but the Salesforce connector provides user defined function definitions for these operators that provide equivalent functionality for fields of type multi-select. The definition for the functions are: 
boolean includes(Column column, String param)
boolean excludes(Column column, String param)
For example, take a single multi-select picklist column called Status that contains all of these values.
  • current
  • working
  • critical
For that column, all of the below are valid queries: 
SELECT * FROM Issue WHERE true = includes (Status, 'current, working' );
SELECT * FROM Issue WHERE true = excludes (Status, 'current, working' );
SELECT * FROM Issue WHERE true = includes (Status, 'current;working, critical' );
EQ and NE criteria will pass to Salesforce as supplied. For example, these queries will not be modified by the connector. 
SELECT * FROM Issue WHERE Status = 'current';
SELECT * FROM Issue WHERE Status = 'current;critical';
SELECT * FROM Issue WHERE Status != 'current;working';

13.26.5. Salesforce Translator: Selecting All Objects

The Salesforce connector supports calling the queryAll operation from the Salesforce API. The queryAll operation is equivalent to the query operation with the exception that it returns data about all current and deleted objects in the system.
The connector determines if it will call the query or queryAll operation via reference to the isDeleted property present on each Salesforce object, and modeled as a column on each table generated by the importer. By default this value is set to False when the model is generated and thus the connector calls query. Users are free to change the value in the model to True, changing the default behavior of the connector to be queryAll.
The behavior is different if isDeleted is used as a parameter in the query. If the isDeleted column is used as a parameter in the query, and the value is 'true' the connector will call queryAll. 
select * from Contact where isDeleted = true;
If the isDeleted column is used as a parameter in the query, and the value is 'false' the connector performing the default behavior will call the query. 
select * from Contact where isDeleted = false;

13.26.6. Salesforce Translator: Selecting Updated Objects

If the option is selected when importing metadata from Salesforce, a GetUpdated procedure is generated in the model with the following structure: 
GetUpdated (ObjectName IN string,
   StartDate IN datetime,
   EndDate IN datetime,
   LatestDateCovered OUT datetime)
returns
   ID string
See the description of the GetUpdated operation in the Salesforce documentation for usage details.

13.26.7. Salesforce Translator: Selecting Deleted Objects

If the option is selected when importing metadata from Salesforce, a GetDeleted procedure is generated in the model with the following structure: 
GetDeleted (ObjectName IN string,
   StartDate IN datetime,
   EndDate IN datetime,
   EarliestDateAvailable OUT datetime,
   LatestDateCovered OUT datetime)
returns
   ID string,
   DeletedDate datetime
See the description of the GetDeleted operation in the Salesforce documentation for usage details.

13.26.8. Salesforce Translator: Relationship Queries

Salesforce does not support joins like a relational database, but it does have support for queries that include parent-to-child or child-to-parent relationships between objects. These are termed Relationship Queries. The SalesForce connector supports Relationship Queries through Outer Join syntax. 
SELECT Account.name, Contact.Name from Contact LEFT OUTER JOIN Account
on Contact.Accountid = Account.id
This query shows the correct syntax to query a SalesForce model with to produce a relationship query from child to parent. It resolves to the following query to SalesForce. 
SELECT Contact.Account.Name, Contact.Name FROM Contact
select Contact.Name, Account.Name from Account Left outer Join Contact
on Contact.Accountid = Account.id
This query shows the correct syntax to query a SalesForce model with to produce a relationship query from parent to child. It resolves to the following query to SalesForce. 
SELECT Account.Name, (SELECT Contact.Name FROM
Account.Contacts) FROM Account
See the description of the Relationship Queries operation in the SalesForce documentation for limitations.

13.26.9. Salesforce Translator: Bulk Insert Queries

SalesForce translator also supports bulk insert statements using JDBC batch semantics or SELECT INTO semantics. The batch size is determined by the execution property MaxBulkInsertBatchSize , which can be overridden in the vdb.xml file. The default value of the batch is 2048. The bulk insert feature uses the async REST based API exposed by Salesforce for execution for better performance.

13.26.10. Salesforce Translator: Supported Capabilities

The following are the connector capabilities supported by the Salesforce Connector. These SQL constructs will be pushed down to Salesforce.
  • SELECT command
  • INSERT Command
  • UPDATE Command
  • DELETE Command
  • CompareCriteriaEquals
  • InCriteria
  • LikeCriteria - Supported for String fields only.
  • RowLimit
  • AggregatesCountStar
  • NotCriteria
  • OrCriteria
  • CompareCriteriaOrdered
  • OuterJoins with join criteria KEY

13.26.11. Salesforce Translator: Native Queries

Salesforce procedures may optionally have native queries associated with them. See Section 13.7, “Parameterizable Native Queries”. The operation prefix (for example, select;, insert;, update;, delete; - see the native procedure logic below) must be present in the native query, but it will not be issued as part of the query to the source.

Example 13.11. Example DDL for a SF native procedure

CREATE FOREIGN PROCEDURE proc (arg1 integer, arg2 string) OPTIONS ("teiid_rel:native-query" 'search;SELECT ... complex SOQL ... WHERE col1 = $1 and col2 = $2') returns (col1 string, col2 string, col3 timestamp);

13.26.12. Salesforce Translator: Native Procedure

Warning

This feature is turned off by default because of the security risk this exposes to execute any command against the source. To enable this feature, override the translator property called "SupportsNativeQueries" to true. See Section 13.6, “Override Execution Properties”.
SalesForce translator provides a procedure with name native that gives ability to execute any ad hoc native Salesforce queries directly against the source without any JBoss Data Virtualization parsing or resolving. The metadata of this procedure's execution results are not known to JBoss Data Virtualization, and they are returned as object array. User can use an ARRAYTABLE construct ( Section 3.6.10, “Nested Tables: ARRAYTABLE”) to build a tabular output for consumption by client applications. JBoss Data Virtualization exposes this procedure with a simple query structure as below.

13.26.13. Salesforce Translator Example: Select

Example 13.12. Select Example

SELECT x.* FROM (call pm1.native('search;SELECT Account.Id, Account.Type, Account.Name FROM Account')) w,
 ARRAYTABLE(w.tuple COLUMNS "id" string , "type" string, "name" String) AS x
In the above code, the "search" keyword is followed by a query statement.

Note

The Salesforce Object Query Language (SOQL) is treated as a parameterized native query so that parameter values may be inserted in the query string properly. See Section 13.7, “Parameterizable Native Queries”.
The results returned by search may contain the object Id as the first column value regardless of whether it was selected. Also queries that select columns from multiple object types will not be correct.

13.26.14. Salesforce Translator Example: Delete

Example 13.13. Delete Example

SELECT x.* FROM (call pm1.native('delete;', 'id1', 'id2')) w,
 ARRAYTABLE(w.tuple COLUMNS "updatecount" integer) AS x
In the above code, the "delete;" keyword is followed by the ids to delete as varargs.

13.26.15. Salesforce Translator Example: Create and Update

Example 13.14. Create Example

SELECT x.* FROM
 (call pm1.native('create;type=table;attributes=one,two,three', 'one', 2, 3.0)) w,
 ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x
In the above code, the "create" or "update" keyword must be followed by the following properties. Attributes must be matched positionally by the procedure variables - thus in the example attribute two will be set to 2.
Property Name
Description
Required
type
Table Name
Yes
attributes
comma separated list of names of the columns
The values for each attribute is specified as separate argument to the "native" procedure.
Update is similar to create, with one more extra property called "id", which defines identifier for the record.

Example 13.15. Update Example

SELECT x.* FROM
 (call pm1.native('update;id=pk;type=table;attributes=one,two,three', 'one', 2, 3.0)) w,
 ARRAYTABLE(w.tuple COLUMNS "update_count" integer) AS x

Important

By default the name of the procedure that executes the queries directly is called native, however user can set override execution property vdb.xml file to change it.