For Oracle database customers that want to access and manipulate data from Visual Builder the first step is to create a REST service on top of that database. While in Visual Builder you can map business objects to an existing database tables and views, many customers opt to use ORDS (Oracle REST Data Services) to expose database objects through REST.
ORDS lets you leverage the full capabilities of the database, including support for advanced data types, PL/SQL functions/procedures, and more. To learn more about ORDS check out the ORDS documentation. In this blog we won't cover the creation of the ORDS service or securing them – find info in the doc link on that. We'll focus on consuming the ORDS services in Visual Builder.
Here is a quick video of the end-to-end development experience:
The overall steps involved are:
Creating a Custom Backend
To start you would create a backend mapped to the ORDS server. All you need is the URL to the ORDS services and information about the security settings they use. The recommended security for ORDS is using OAuth, and in VB you'll use the OAuth2 Client Credentials authentication approach to authenticate. Note that this is a fixed user approach – the same user is used for all the VB app users. (If you need help securing your ORDS here is a blog showing how to secure with SQLDeveloper, and our doc covering the PL/SQL code for getting client id info) Defining the connection through a backend allows you to switch the server serving the request further down the road, for example if you need to use a different servers for dev/test/production.
Leveraging Open-API Spec
While you could add specific ORDS endpoints one-by-one to your backend, a faster way is to use the open-api specification that ORDS generates for the service. Visual Builder knows how to parse that document, and create the right endpoints with information about the payloads for requests and responses automatically. Use the "Create Service from Spec" option and point to the open-api-catalog. The URL for it will be similar to http://yourserver:port//ords/yourschema/open-api-catalog/yourobject/
Note that you should choose the "Copy OpenAPI to application" option to have the definition as part of your app and let VB fix any issues it identifies with the spec.
Important note – there is a known bug in version 2407 of VB Studio that strips away the endpoint from the service definition – to solve this simply go into the source view of the service created and add the object suffix back to the server definition. (employees in our example)
"servers": [
{
"url": "vb-catalog://backends/ORDSHR/employees"
}
Adding Transform Function
The syntax ORDS uses for operations such as query by example, sorting, and paginataion is a bit different to the one used by VB's business objects, and Fusion Apps REST services. To help Visual Builder learn how to properly communicate with ORDS, you'll add a transform function to the service. Transform modifies the REST requests sent from VB to the backend to match the required syntax. We have an example transform for ORDS as part of the ORDS VB cookbook sample, and you can find a similar version of a transform function at the end of this blog. This transform function was created by David Konecny from our VB team and enhanced by Amit Singh from our GSC Tech Cloud Digital, you can further modify it if you need more functionality.
Creating the User Interface
Once your services are defined, the easiest way to bind them to your user interface is to use the data palette in Visual Builder. The ORDS endpoints will show up there, and you can simply drag and drop them to the page to create tables, lists, and forms. As well as bing them to various other components.
Summary
ORDS is a popular technology among Oracle DBAs that allows them to easily expose database objects for UI layer. Visual Builder makes it very easy to consume these services and create rich user interfaces that interact with the database.
Transform Function used in the demo (EDIT an updated transforms function that has "starts with" and "ends with" capabilities can be found here):
define(['urijs/URI'], function (URI) {
'use strict';
const addQParam = (url, toAdd) => {
const query = URI(url).search(true);
if (query.q) {
toAdd = Object.assign(JSON.parse(query.q), toAdd);
}
query.q = JSON.stringify(toAdd);
return URI(url).search(query).toString();
};
class Request {
/**
* @typedef {Object} Configuration
* @property {Object} fetchConfiguration configuration for the current fetch call
* @property {Object} endpointDefinition metadata for the endpoint
* @property {function} readOnlyParameters: Path and query parameters. These are not writable.
* @property {Object} initConfig map of other configuration passed into the request. The
* 'initConfig' exactly matches the 'init' parameter of the request.
* @property {string} url full url of the request.
*/
/**
* @typedef {Object} TransformsContext a transforms context object that can be used by authors of transform
* functions to store contextual information for the duration of the request.
*/
/**
* filter builds filter expression query parameter using either the deprecated
* filterCriteria array or filterCriterion object set on the options.
* @param {Configuration} configuration
* @param {Object} options the JSON payload that defines the filterCriterion
* @param {TransformsContext} transformscontext a transforms context object that can be used by authors of transform
* functions to store contextual information for the duration of the request.
* @returns {Configuration} configuration object, the url looks like ?filter=foo eq bar
*/
filter(configuration, options, transformsContext) {
let filterCriterion = options;
if (filterCriterion === undefined) {
return configuration;
}
// following code is just an example and it assumes that
// the SDP does not have any preconfigured filter criterion and
// that there is only single "vb-textFilterAttributes" attribute set
if (transformsContext['vb-textFilterAttributes'] !== undefined) {
// in case the filtering comes from single select ui component
// the search criteria details come in transformsContext
const searchText = options && (options.text || options.value);
const textFilterAttributes = transformsContext && transformsContext['vb-textFilterAttributes'];
filterCriterion = textFilterAttributes.map(el => ({attribute: el, op: "$co", value: searchText }));
}
let operation;
let criteria = filterCriterion.criteria;
if (criteria === undefined || criteria.length === 0) {
if (filterCriterion instanceof Array && filterCriterion.every(el => el.attribute && el.op && el.value)) { // If conditon to handle if there are more than one "vb-textFilterAttributes" attribute
criteria = filterCriterion;
operation = "$or";
} else if (filterCriterion instanceof Object){
criteria = [filterCriterion];
operation = "$and";
}
else {
return configuration;
}
}
function transformOperator(fop) {
switch (fop) {
case '$co':
return '$instr';
case '$le':
return '$lte';
case '$ge':
return '$gte';
default:
return fop;
}
}
function isEmpty(val) {
return (val === undefined || val === null || val === '');
}
if (filterCriterion && criteria && criteria.length > 0) {
const q = [];
criteria.forEach(function (item) {
if (item.value === undefined || item.value === '%%' || item.value.length === 0) {
return;
}
const queryItem = {};
//Below function and If condition for date
function isDate(date) {
const regex = /\d{4}-\d{2}-\d{2}/;
const validDate = regex.test(date) && !isNaN((new Date(item.value)));
return validDate;
}
if(isDate(item.value)) {
const date = new Date(item.value).toISOString();
item.value = {
$date: date
};
}
queryItem[transformOperator(item.op)] = item.value;
const queryJSON = {};
queryJSON[item.attribute] = queryItem;
q.push(queryJSON);
});
if (q.length > 0) {
const query = {};
if (operation === undefined) {
operation = filterCriterion.op;
}
if (operation === undefined) {
operation = "$and";
}
query[operation] = q;
// ORDS query URL is for example:
// .../ords/hr/emp?q={"$or":[{"ename":{"$instr":"martin"}},{"job":{"$like":"%developer%"}}]}
configuration.url = addQParam(configuration.url, query);
}
}
return configuration;
};
/**
* @typedef {Object} PaginateOptions
* @property {number} iterationLimit
* @property {number} offset which item the response should begin from
* @property {String} pagingState
* @property {number} size how many items should be returned
*/
/**
* pagination function appends limit and offset parameters to the url
* @param {Configuration} configuration
* @param {PaginateOptions} options
* @param {TransformsContext} transformscontext
* @returns {Configuration} configuration object.
*/
paginate(configuration, options, transformscontext) {
let newUrl = configuration.url;
if (options && options.size) {
newUrl = URI(newUrl).addSearch({limit: options.size, offset: options.offset}).toString();
}
configuration.url = newUrl;
return configuration;
}
/**
* sort the 'uriParameters' property is passed in as options. Normally uriParameters are appended
* to the URL automatically, but there may be cases where the user would want to adjust the query parameters.
* @param {Configuration} configuration
* @param {Array} options
* @param {TransformsContext} transformscontext
* @returns {Configuration} configuration object, the url looks like ?orderBy=foo:asc
*/
sort(configuration, options, transformscontext) {
if (Array.isArray(options) && options.length > 0) {
const firstItem = options[0];
if (firstItem.attribute) {
const dir = firstItem.direction === 'descending' ? 'DESC' : 'ASC';
let newUrl = configuration.url;
const sort = '"$orderby":{"'+firstItem.attribute+'":"'+dir+'"}';
let query = URI(newUrl).search(true);
if (query.q) {
query.q = '{'+sort+','+query.q.substr(1);
} else {
query.q = '{'+sort+'}';
}
// ORDS sort URL is for example:
// ...ords/hr/emp?q={"$orderby":{"sal":"ASC"}}
// BUT: sorting is applied after filter() method above so sorting
// needs to be inserted into existing q param if filtering is on
newUrl = URI(newUrl).search(query).toString();
configuration.url = newUrl;
}
}
return configuration;
}
/**
* query function
* @param {Configuration} configuration
* @param {object} options
* @param {TransformsContext} transformscontext
* @returns {Configuration} configuration object
*/
/*query(configuration, options, transformscontext) {
const c = configuration;
if (options && options.search) {
let newUrl = c.url;
newUrl = URI(newUrl).addSearch( options.search, 'faq' ).toString(); // appends 'faq' to the search term
c.url = newUrl;
}
return c;
}*/
/**
* select typically uses the 'responseType' to construct a query parameter to select and expand
* the fields returned from the service
* Example:
*
* Employee
* - firstName
* - lastName
* - department
* - items[]
* - departmentName
* - location
* - items[]
* - locationName
*
* would result in this 'fields' query parameter:
*
* fields=firstName,lastName;department:departmentName;department.location:locationName
*
* @param {Configuration} configuration
* @param {object} options
* @param {TransformsContext} transformscontext
*/
/*select(configuration, options, context) {
const queryParamExists = (url, name) => {
const q = url.indexOf('?');
if (q >= 0) {
return (url.indexOf(`?${name}`) === q) || (url.indexOf(`&${name}`) > q);
}
return false;
};
// the options should contain a 'type' object, to override
const c = configuration;
// do nothing if it's not a GET
if (c.endpointDefinition && c.endpointDefinition.method !== 'GET') {
return c;
}
// do nothing if there's already a '?fields='
if(queryParamExists(c.url, 'fields')) {
return c;
}
// if there's an 'items', use its type; otherwise, use the whole type
const typeToInspect = (options && options.type && (options.type.items || options.type));
if(typeToInspect && typeToInspect === Object) {
const fields = 'TODO: query parameters'; // just an example; query parameter construction is left to the developer
if(fields) {
c.url = URI(c.url).addSearch('fields', fields).toString();
}
}
return c;
}*/
/**
* fetchByKeys allows the page author to take a key or Set of keys passed in via the options and
* tweak the URL, to fetch the data for the requested keys.
* @param {Configuration} configuration
* @param {object} options
* @param {TransformsContext} transformscontext
*/
fetchByKeys(configuration, transformOptions) {
const c = configuration;
const to = transformOptions || {};
const fetchByKeys = !!(c && c.capability === 'fetchByKeys'); // this tells us that the current fetch call is a fetchByKeys
if (fetchByKeys) {
const keysArr = Array.from(c.fetchParameters.keys);
const key = keysArr[0]; // grab the key provided by caller
if (key) {
c.url = URI(c.url).addQuery({ id: key }).toString();
}
}
return c;
}
/**
* body is used to build or tweak the body for the fetch request. With some endpoints the search is made with a
* complex search criteria set on the body that can be tweaked here.
* This transform function is the only function that is guaranteed to be called after all other request
* transform functions, (filter, sort, paginate, and so on). The reason is that any of the other transform
* functions can set info into the 'transformsContext' parameter, as a way to update the body.
* @param {Configuration} configuration
* @param {object} options
* @param {TransformsContext} transformscontext
*/
/*body(configuration, options, transformsContext) {
const c = configuration;
if (options && Object.keys(options).length > 0) {
c.initConfig.body = c.initConfig.body || {};
// update body
}
return c;
}*/
};
class Response {
/**
* @typedef {Object} PaginateResponse
* @property {number} totalSize optional what the totalSize of the result is (the total count of the records in
* the service endpoint).
* @property {boolean} hasMore usually required, the paginate response transform function is relied upon to
* inform the ServiceDataProvider when to stop requesting to fetch more data. Indicates whether there are more
* records to fetch
* @property {String} pagingState optional. This can be used to store any paging state specific to the paging
* capability supported by the endpoint. This property can be used in the response paginate transform function
* to set an additional paging state. This will then be passed as is to the request paginate transform function
* for the next fetch call.
*/
/**
* paginate is called with the response so this function can process it and return an object with
* properties set.
* @param {object} result
* @param {TransformsContext} transformscontext
* @return {PaginateResponse}
*/
paginate(result, transformscontext) {
const tr = {};
if (result && result.body) {
const cb = result.body;
// ORDS does not support "totalCount" but only "hasMore"
tr.hasMore = cb.hasMore;
}
return tr;
}
/**
* body is called last, after all the other response transforms have been called. It is a hook for authors
* to transform the response body or build an entirely new one.
* @param {object} result
* @param {TransformsContext} transformscontext
* @return {object}
*/
/*body(result) {
let tr = {};
if (result.body) {
tr = result.body;
}
// as a example store some random aggregation data
tr.aggregation = { example: 4 };
return tr;
}*/
}
return {
request: Request,
response: Response
};
});