Loopback Custom Adapter for Lightning Connect

1/**
2 *   Extends the DataSource.Connection class to enable 
3 *   Salesforce to sync the external system’s schema 
4 *   and to handle queries and searches of the external data. 
5 **/
6global class LoopbackDataSourceConnection 
7    extends DataSource.Connection {
8
9    /**
10     *   Constructors
11     **/
12    global LoopbackDataSourceConnection(
13        DataSource.ConnectionParams connectionParams) {
14    }
15    global LoopbackDataSourceConnection() {}
16    
17    /**
18     *   Called when an external object needs to get a list of 
19     *   schema from the external data source, for example when 
20     *   the administrator clicks “Validate and Sync” in the 
21     *   user interface for the external data source.   
22     **/
23    override global List<DataSource.Table> sync() {
24        List<DataSource.Table> tables = 
25            new List<DataSource.Table>();        
26        List<DataSource.Column> columns;
27        columns = new List<DataSource.Column>();
28        columns.add(DataSource.Column.text('ExternalId', 255));
29        columns.add(DataSource.Column.url('DisplayUrl'));
30        columns.add(DataSource.Column.text('Name', 255));
31        columns.add(
32            DataSource.Column.number('NumberOfEmployees', 18, 0));
33        tables.add(
34            DataSource.Table.get('Looper', 'Name', columns));
35        return tables;
36    }
37    
38    /**
39     *   Called to query and get results from the external 
40     *   system for SOQL queries, list views, and detail pages 
41     *   for an external object that’s associated with the 
42     *   external data source.
43     *   
44     *   The QueryContext argument represents the query to run 
45     *   against a table in the external system.
46     *   
47     *   Returns a list of rows as the query results.
48     **/
49    override global DataSource.TableResult 
50        query(DataSource.QueryContext context) {
51        if (context.tableSelection.columnsSelected.size() == 1 &&
52            context.tableSelection.columnsSelected.get(0).aggregation ==
53                DataSource.QueryAggregation.COUNT) {
54            integer count = execCount(getCountQuery(context));
55            List<Map<String, Object>> countResponse =
56                new List<Map<String, Object>>();
57            Map<String, Object> countRow =
58                new Map<String, Object>();
59            countRow.put(
60                context.tableSelection.columnsSelected.get(0).columnName,
61                count);
62            countResponse.add(countRow);
63            return DataSource.TableResult.get(context,countResponse);
64        } else {
65            List<Map<String,Object>> rows = execQuery(
66                getSoqlQuery(context));
67            return DataSource.TableResult.get(context,rows);
68        }
69    }
70    
71    /**
72     *   Called to do a full text search and get results from
73     *   the external system for SOSL queries and Salesforce
74     *   global searches.
75     *   
76     *   The SearchContext argument represents the query to run 
77     *   against a table in the external system.
78     *   
79     *   Returns results for each table that the SearchContext 
80     *   requested to be searched.
81     **/
82    override global List<DataSource.TableResult> 
83        search(DataSource.SearchContext context) {        
84        return DataSource.SearchUtils.searchByName(context, this);
85    }
86
87    /**
88     *   Helper method to execute the SOQL query and 
89     *   return the results
90     **/
91    private List<Map<String,Object>> 
92        execQuery(String soqlQuery) {
93        List<Account> objs = Database.query(soqlQuery);
94        List<Map<String,Object>> rows = 
95            new List<Map<String,Object>>();
96        for (Account obj : objs) {
97            Map<String,Object> row = new Map<String,Object>();
98            row.put('Name', obj.Name);
99            row.put('NumberOfEmployees', obj.NumberOfEmployees);
100            row.put('ExternalId', obj.Id);
101            row.put('DisplayUrl', 
102                URL.getSalesforceBaseUrl().toExternalForm() + 
103                    obj.Id);
104            rows.add(row);
105        }
106        return rows;
107    }
108
109    /**
110     *   Helper method to get aggregate count 
111     **/
112    private integer execCount(String soqlQuery) {
113        integer count = Database.countQuery(soqlQuery);
114        return count;
115    }
116
117    /**
118     *   Helper method to create default aggregate query 
119     **/
120    private String getCountQuery(DataSource.QueryContext context) {
121        String baseQuery = 'SELECT COUNT() FROM Account';
122        String filter = getSoqlFilter('', 
123            context.tableSelection.filter);
124        if (filter.length() > 0)
125            return baseQuery + ' WHERE ' + filter;
126        return baseQuery;
127    }
128
129    /**
130     *   Helper method to create default query 
131     **/
132    private String getSoqlQuery(DataSource.QueryContext context) {
133        String baseQuery = 
134            'SELECT Id,Name,NumberOfEmployees FROM Account';
135        String filter = getSoqlFilter('', 
136            context.tableSelection.filter);
137        if (filter.length() > 0)
138            return baseQuery + ' WHERE ' + filter;
139        return baseQuery;
140    }
141
142    /**
143     *   Helper method to handle query filter 
144     **/
145    private String getSoqlFilter(String query, 
146        DataSource.Filter filter) {
147        if (filter == null) {
148            return query;
149        }
150        String append;
151        DataSource.FilterType type = filter.type;
152        List<Map<String,Object>> retainedRows = 
153            new List<Map<String,Object>>();
154        if (type == DataSource.FilterType.NOT_) {
155            DataSource.Filter subfilter = filter.subfilters.get(0);
156            append = getSoqlFilter('NOT', subfilter);
157        } else if (type == DataSource.FilterType.AND_) {
158            append =  
159                getSoqlFilterCompound('AND', filter.subfilters);
160        } else if (type == DataSource.FilterType.OR_) {
161            append = 
162                getSoqlFilterCompound('OR', filter.subfilters);
163        } else {
164            append = getSoqlFilterExpression(filter);
165        }
166        return query + ' ' + append;
167    }
168    
169    /**
170     *   Helper method to handle query subfilters 
171     **/
172    private String getSoqlFilterCompound(String operator, 
173        List<DataSource.Filter> subfilters) {
174        String expression = ' (';
175        boolean first = true;
176        for (DataSource.Filter subfilter : subfilters) {
177            if (first)
178                first = false;
179            else
180                expression += ' ' + operator + ' ';
181            expression += getSoqlFilter('', subfilter);
182        }
183        expression += ') ';
184        return expression;
185    }
186    
187    /**
188     *   Helper method to handle query filter expressions 
189     **/
190    private String getSoqlFilterExpression(
191        DataSource.Filter filter) {
192        String columnName = filter.columnName;
193        String operator;
194        Object expectedValue = filter.columnValue;
195        if (filter.type == DataSource.FilterType.EQUALS) {
196            operator = '=';
197        } else if (filter.type == 
198            DataSource.FilterType.NOT_EQUALS) {
199            operator = '<>';
200        } else if (filter.type == 
201            DataSource.FilterType.LESS_THAN) {
202            operator = '<';
203        } else if (filter.type == 
204            DataSource.FilterType.GREATER_THAN) {
205            operator = '>';
206        } else if (filter.type == 
207            DataSource.FilterType.LESS_THAN_OR_EQUAL_TO) {
208            operator = '<=';
209        } else if (filter.type == 
210            DataSource.FilterType.GREATER_THAN_OR_EQUAL_TO) {
211            operator = '>=';
212        } else if (filter.type == 
213            DataSource.FilterType.STARTS_WITH) {
214            return mapColumnName(columnName) + 
215            ' LIKE \'' + String.valueOf(expectedValue) + '%\'';
216        } else if (filter.type == 
217            DataSource.FilterType.ENDS_WITH) {
218            return mapColumnName(columnName) + 
219            ' LIKE \'%' + String.valueOf(expectedValue) + '\'';
220        } else {
221            throwException(
222            'Implementing other filter types is left as an exercise for the reader: ' 
223            + filter.type);
224        }
225        return mapColumnName(columnName) + 
226            ' ' + operator + ' ' + wrapValue(expectedValue);
227    }
228    
229    /**
230     *   Helper method to map column names 
231     **/
232    private String mapColumnName(String apexName) {
233        if (apexName.equalsIgnoreCase('ExternalId'))
234            return 'Id';
235        if (apexName.equalsIgnoreCase('DisplayUrl'))
236            return 'Id';
237        return apexName;
238    }
239
240    /**
241    *   Helper method to wrap expression Strings with quotes 
242    **/
243    private String wrapValue(Object foundValue) {
244        if (foundValue instanceof String)
245            return '\'' + String.valueOf(foundValue) + '\'';
246        return String.valueOf(foundValue);
247    }
248}

1/**
2 *   Extends the DataSource.Provider base class to create a 
3 *   custom adapter for Lightning Connect. The class informs 
4 *   Salesforce of the functional and authentication 
5 *   capabilities that are supported by or required to connect 
6 *   to an external system.
7 **/
8global class LoopbackDataSourceProvider 
9    extends DataSource.Provider {
10    
11    /**
12     *   Declares the types of authentication that can be used 
13     *   to access the external system
14     **/
15    override global List<DataSource.AuthenticationCapability> 
16        getAuthenticationCapabilities() {
17        List<DataSource.AuthenticationCapability> capabilities = 
18            new List<DataSource.AuthenticationCapability>();
19        capabilities.add(
20            DataSource.AuthenticationCapability.ANONYMOUS);
21        capabilities.add(
22            DataSource.AuthenticationCapability.BASIC);
23        return capabilities;
24    }
25
26    /**
27     *   Declares the functional capabilities that the 
28     *   external system supports.
29     **/
30    override global List<DataSource.Capability> 
31        getCapabilities() {
32        List<DataSource.Capability> capabilities = 
33            new List<DataSource.Capability>();
34        capabilities.add(DataSource.Capability.ROW_QUERY);
35        capabilities.add(DataSource.Capability.SEARCH);
36        return capabilities;
37    }
38
39    /**
40     *   Declares the associated DataSource.Connection class.
41     **/
42    override global DataSource.Connection 
43        getConnection(DataSource.ConnectionParams connectionParams) {
44        return new LoopbackDataSourceConnection();
45    }
46}