salesforce-rest-customwrapper is a Virtual DataPort set of custom wrappers created to be able to perform both CRUD and complex-query operations against SalesForce from VDP in an integrated manner. This custom wrapper communicates with SalesForce using the REST API enabled by SalesForce and using JSON to exchange data with it.
The salesforce-rest-customwrapper library includes several different wrappers, all in the com.denodo.salesforce.rest.wrapper package:
Using the entities wrappers you can perform CRUD operations against SalesForce entities and custom entities in an SQL-way. Thanks to SalesforceSOQLWrapper, you can execute arbitrary SOQL (Salesforce Object Query Language) queries and fetch their results both as a piece of JSON and also as a VDP complex object (a set of fieldName/fieldValue pairs in the shape of a JDBC array of structs). SalesforcePredefinedSOQLWrapper allows you the configuration of a SOQL query to be executed always and see its results as simple properties and, for those that are objects, in a similar way to the SalesforceSOQLWrapper: as a piece of JSON and as a VDP complex object.
All the wrappers use the new SalesForce.com REST API, based on JSON information interchange and OAuth 2.0 authentication tokens (it also supports XML but we use JSON). Using this REST API instead of the older contract-first SOAP API gives us much greater flexibility and doesn’t require us to maintain a heavy AXIS-based Web Service skeleton generated from the service’s WSDL. OAuth-based web authentication flow allows VDP admins to configure these wrappers without the need to store SalesForce user/password information in VDP (OAuth is the authentication system developed by twitter for their client apps).
OAuth functionality includes auto-refresh of tokens, so that once the authentication token has expired (SalesForce does not allow these tokens to be valid forever), the custom wrappers will execute automatic refresh logic that will obtain a new one.
Sometimes Salesforce may change the instance you have to connect to. In those cases, as the refresh flow returns the instance URL, the custom wrapper will adapt by performing a refresh in order to get the new instance url. This way, requests will be executed against the new instance. No manual changes will be necessary at all.
All the wrappers include, also, configuration parameters that allow you to (optionally) use an HTTP/HTTPS proxy to connect to Salesforce.
All the wrappers allow you to set the Salesforce REST API version that will be used. By default v30.0 will be used although there is a parameter you can set to use a different one: v29.0, v33.0 and so on.
The wrappers access Salesforce resources by creating connections to the Salesforce instance url (something like https://na2.salesforce.com) using httpclient library. SSL connections are configured to use TLSv1.2.
When calling SalesForce, you can let it know you accept compressed responses. This is also a configuration option for these custom wrappers. Anyway, you should take into account custom wrappers only use compression if configured so and you are fetching all the data or performing a select with filtering. No compression will be used by these custom wrappers if deleting an entity or adding a new one. Apart from that, REST API is not required to compress responses even if you tell it you accept them. Based on this, it’s recommended that, in case you are fetching a big amount of data, you try if compression makes a difference for you in performance.
The SalesforceRestWrapper and SalesforceRestSelectWrapper can delegate to SalesForce the following query artifacts and operators:
Thanks to the delegation of so many operations, the execution of this custom wrapper is faster as most part of the filtering operations will be performed by Salesforce itself instead of having to rely on VDP in-memory post-filtering.
The other custom wrappers SalesforcePredefinedSOQLWrapper and SalesforceSOQLWrapper do not execute VQL sentences as they already contain a SOQL query that will be directly sent to Salesforce and executed there.
Salesforce has a limitation in the length of the queries, if we send a request too large to the server, SalesForce would respond with a message as this "Request Header Fields Too Large". In this case we would have to reduce the size of the request.
The following limitations described in this section are related to VQL sentences so they only affect SalesforceRestWrapper and SalesforceRestSelectWrapper as the other custom wrappers use SOQL sent to SalesForce directly. They should be taken into account when using those custom wrappers.
The SalesforceRestSelectWrapper can only work with the entities available at the entity selector dropdown whereas the SalesforceRestWrapper can work with almost every entity available at your Salesforce account (some entities do not support queries or are restricted to be used under certain conditions). For example, some of the mostly used entities you may end up using are:
But you can also use less known entities such as OpportunityHistory, ContactHistory or a Custom Object defined by yourself at your Salesforce instance (e.g. MyCustomLicense__c).
Note that all the entities are supported in a specific manner, which means that the data type of each of their fields is respected, and any custom field added to them at Salesforce.com is automatically detected and added to the base view during creation. If any custom field is added once the base view has been created, you can perform a source refresh of the view at VDP so that the new field can be added to the view.
The SalesforceSOQLWrapper does not depend on any entities as it just receives an SOQL query and then simply sends it over to SalesForce. That’s also the case of the SalesforcePredifinedSOQLWrapper.
Whenever you communicate with SalesForce, you need to be authenticated. That’s why the usage of these custom wrappers needs authentication details in order to work. Authentication when using the SalesForce REST API is done by means of OAuth 2.0. Before configuring any SalesForce base view in VDP you will have to obtain the authentication tokens from your SalesForce instance.
This section will give you just a brief introduction to OAuth 2.0 authentication process as Salesforce already provides good documentation on how to get the authentication tokens required to use these Denodo custom wrappers.
The SalesForce REST Custom Wrappers can use two different authentication flows supported by Salesforce:
The token may not be refreshable if “Refresh Token Policy” is set to “Immediately expire Refresh Token”. In that case, we may have an invalid refresh token or not have refresh token at all (depending on how we configured our remote access).
Further information on how OAuth authentication works can be found at SalesForce:
In order to use the SalesForce REST Custom Wrappers with OAuth 2.0 Web server authentication flow, you need to provide the custom wrappers with the following parameters:
You can configure in your SalesForce instance the duration of the access token, but it has to expire at some point. The VDP automatically refreshes the access token (once expired) using the client id and refresh token of the application in case you provide them. Note that, if policy is “Immediately expire Refresh Token” and you have a refresh token, this will not be refreshable as it will have been invalidated by SalesForce when policy was set to expire.
In order to get those tokens, you have to:
With the VDP OAuth 2.0 Credentials Wizard (recommended if you are using Denodo 5.0 or above) you can input your SalesForce authentication details and follow the steps to obtain your tokens:
Note the above screenshot configures access to a SalesForce SandBox instance. For configuring access to a production instance, you should use login.salesforce.com instead of test.salesforce.com (check the SalesForce documentation for more details). Also note that in order to use this wizard for the SalesForce wrapper, we recommend that the VDP Admin Tool is executed in the same machine as the VDP Server.
Once you have obtained all the tokens, we can start configuring VDP.
In order to use the custom wrappers with the username-password flow, you need to provide them with these authentication parameters:
All those tokens (except the access token) are necessary to get an access token either for the first time or once the current one expires. You can configure, in your SalesForce instance, the duration of this access token (but it has to expire anyway).
Although, explaining this is not the aim of this manual, in order to get those tokens, you have to:
Once you have done so, we can start configuring VDP. Remember the fetched tokens and your SalesForce instance URL as these are the parameters VDP will request from you.
Further information on how to get these authentication tokens can be found at SalesForce: http://help.salesforce.com/help/doc/en/remoteaccess_oauth_username_password_flow.htm
In order to install it, you have to import the corresponding jar file (denodo-salesforce-rest-wrapper-[DENODO_PLATFORM_VERSION]-[WRAPPER_VERSION]-jar-with-dependencies.jar) in VDP (through File/Extensions/Jar management) and add all the wrappers (or the one/s you need) as Data Sources. Once done, you can create the base views.
The custom wrapper SalesforceRestSelectWrapper allows you to create one base view for each SalesForce entity available in the entity dropdown whereas the custom wrapper SalesforceRestWrapper allows you to create one base view for each entity available in Salesforce (even custom entities). Base views created from any of them need the following mandatory parameters:
There are also some optional parameters (some of them will be required depending on the used authentication flow):
Once you provide these parameters, the wrapper will connect to SalesForce in order to ask for a description of all the fields existing for the given entity in your SalesForce installation, including custom ones. Each field will be given its own column with the correct data type.
The image below displays the form to fill in with the entity details explained above. Here we are creating a base view for the account entity using the web flow. Note how, in this case, we provide data for the proxy host and port but not the username and password as our proxy does not require authentication.
The image below displays the fields for the Opportunity entity. Once we create a base view for the Opportunity, we can see its fields. Those fields are being fetched at that moment from Salesforce (and when you refresh the base view later). Note how as our entity contains custom fields they are displayed at the bottom of the entity (the ones ending with __c).
The SalesforceSOQLWrapper is a read only wrapper that allows you to perform SOQL queries from VDP so that you can execute more specific queries against SalesForce. The base view created from the SalesforceSOQLWrapper needs the same parameters as the entity-based ones except for the ENTITY field, which has no use in this case. This generated base view will have the following columns:
The image below shows the VQL Shell with an SOQL query (using the sf_soql base view we have created) so that you can see the response. It includes the result as a JSON String and the result as an array.
The previous image does not let you see the array itself so here you can see it for the first record:
The SalesforcePredefinedSOQLWrapper is a read only wrapper that allows you to perform a predefined SOQL query from VDP. The wrapper always executes the same query with which it was defined. The base view created from this wrapper needs the same parameters as the SalesforceSOQLWrapper and also this one:
The generated base view columns depend on the SOQL_QUERY. Every simple field returned by the query will be a column and, every field that is returned from Salesforce as an object, will be represented in two different columns: one with the JSON itself and another with a VDP complex object containing the pairs key - value of that JSON object.
Below we can see the fields for the query:
SELECT Contact.Id, Contact.Owner.Id, Contact.Name, Contact.Owner.email, Contact.Account.Id, Contact.Account.Name, Contact.Account.Phone, Contact.Account.Type, Contact.Account.External_Customer_Code__c from Contact |
In the above image you can see two simple properties: id and name and two complex ones: owner and account as explained before.
The execution of the previous base view will return what you can see below:
Note that, in order to fetch the structure of the query, the query needs to be performed when we define the base view and, the output, analyzed to know which fields are simple and which ones complex. If we have no information on a field (=all the data returned by the query has a null value for that property) it will be displayed as a simple property.
Once you have created the base views for your entities, you can start using them. Let’s see some sentences we can execute:
The result of this sentence will be a String as the value of the RESULT_JSON column (e.g. {"Id":"0014000000VLyoiAAD","Name":"GenePoint España"}) and an array as the value of the RESULT_COMPLEX column with the pairs key - value in the JSON, for example:
Id 0014000000VLyoiAAD
Name GenePoint España
The result of this base view will be a list of tuples where each one contains a complex object that represents the account for the contact in that tuple.