It was fairly difficult to come up with a title for this article that sort of covers the content. To me it is quite clear what this is about – but how to convey that in a title? Let me explain: today in our project we discussed the implementation of a data service. The service operation under scrutiny takes a city as input and returns a list all open orders from customers located in that city. Nothing very special there. The interesting complication lies in the fact that the customers are part of a different domain than the orders. This means – under our architecture guidelines – that we cannot create a single SQL query that joins together the customers table with the orders table. A database link to join the tables across databases is out of the question and even if these tables currently reside in the same database – such a join is not allowed. Different data domains are treated as independent entities and no direct dependencies between the two should be created. Every design has pass the check ‘will it still work if one of the domains involved were to be relocated to the cloud or be replaced by a third party application’.
The architecture is service oriented. Every domain exposes services that provide access to data and business logic. The implementation of these services and the underlying domain is encapsulated. Consumers of the domain services are unaware of the domain internals, therefore they have no dependencies on such internals and will not be affected if the internals change. For as long as the domain adheres to its service contracts, all consumers can continue to function. This even applies if the domain is moved to a different physical location or reimplemented using a COTS (commercial off the shelf) product.
So there we had it: a sound service oriented architecture with fairly strict guidelines and a clear business requirement. The composite service we were tasked with implementing would somehow have to make use of two domain services – one on the CRM domain and one on the SALES domain – to find the customers in the location specified and find all open orders for these customers.
The call to the CustomerService’s findCustomers operation would return a list of customer identifier values. What to do next? Loop over all identifiers and retrieve the orders for the customer identifier – merging all results returned by all calls to OrderServicer.retrieveOrdersForCustomers? Potentially making dozens or more calls to the OrderService? Or perhaps we could transfer the list of customer identifiers to the OrderService and let it take care of getting all orders for all the customers in the list. But how can we implement this in an efficient manner? How do we prevent executing the query to fetch the orders as many times as there are customer identifiers?
It turned out to be quite simple to address this challenge. Using the Oracle SQL Table Function and the database adapter we can very easily create a SQL query that joins the orders table with the list of customer identifiers. Only a single query is executed against the SALES database and a single round trip suffices to get all order records. The whole approach is of course not as efficient as doing a straight join across the two tables, but in this service oriented context it is not bad at all.
Let’s take a look at the implementation.
Starting at the end
The final result will be like this: three SOA Suite composites are used, one for each of the three services from the original design. Two of these are Domain Services; these use a database adapter to retrieve data from their Domain data store – which happens to be a relational database in both instances.
When the CustomerOrderService is invoked to return all Open Orders for Customers located in the specified location, the BPEL process in the CustomerOrderService Composite will first invoke the CustomerService that – using its database adapter against the CRM database – will return a list of customer identifiers. Next, the BPEL process will use this list of identifiers as input in its call to the OrderService. This latter service is exposed by another SCA composite that has its own database adapter invoking a PL/SQL package. In the call to this package, the entire list of identifiers is passed in. Inside the package, a SQL query is performed (that joins the customer identifiers list to the ORDERS table); this query returns the Orders (for the specified customers and with the appropriate status). The important message is that only one query is performed against the ORDERS table in the SALES database. And regardless of the number of customers involved – it will always be a single query.
Even though the service discussed in this article stretches across two data domains and although joins across these domains are not allowed in SQL queries, we still only required two queries (rather than a single query for each customer). In addition: the consumer of the operation is none the wiser about the underlying structure or whereabouts of the data. Whether the composite service enlisted Cloud based resources, a file system or a number of relational data stores: it is completely hidden from view. As it should be. The remainder of this article demonstrates exactly how this was realized.
Implementation of the CustomerService
Let’s assume a very simple CRM system. A single database table called CUSTOMERS with just four columns. It is enough to serve the purpose of this article. A number of customer records is created in this table.
The CustomerService is implemented using a SOA Suite Composite Application; note: we could just as well have used the Service Bus in this case.
The composite exposes the service with the agreed upon CustomerService interface (described by a WSDL and associated XSD document). A Mediator component implements that interface and maps it to a database adapter service that has been configured to query from the CUSTOMERS table all those customer records that have the required value in their CITY column.
The database adapter configuration is fairly straightforward: the adapter performs a query against table CUSTOMERS and retrieves all records with a CITY value equal to the location parameter.
The Mediator maps the input and output messages specified in the CustomerService contract to the input and output required by the database adapter service based on this configuration.
After deployment, the CustomerService can easily be tested. Given a location, it will return a list of customer identifiers.
Implementation of the OrderService
The implementation of the OrderService takes place at two levels. First at the database level – where a PL/SQL package is created to produce a collection of Order objects based on a collection of Customer identifiers. This package is created, deployed and tested on its own. The second step involves the SOA Suite: a database adapter configuration is created to invoke this PL/SQL package and using a Mediator mapping a pretty service interface to this database adapter the composite is completed, deployed and also tested.
Step one is not SOA Suite specific. The PL/SQL package that returns the collection of Order Records is an example of a encapsulated service – not your typical web service but instead a PL/SQL based API and implementation which is a service just as well. The package contains a SQL query that retrieves records from the ORDERS table. The records are filtered by CTR_ID (the column that contains the customer identifier). This is done using a join to a Nested Table rather than a WHERE condition. Read on, and the what and how are revealed.
The ORDER details are returned from the package as a nested table of objects – using the very powerful yet fairly little known database mechanism of objects and table of objects. Using these next SQL statements, the object and the table of object types are created in the database:
These statements create a type ORDER_TABLE_T – a collection of ORDER_T objects – and ORDER_T itself that describes an object with five attributes. Instances of these objects can be created – in SQL and PL/SQL – and they can be passed around between for example the database adapter and a PL/SQL package.
The package specification that the database adapter will be created against is defined as follows:
The function find_orders is invoked with a number_table_t – defined as CREATE TYPE NUMBER_TABLE_T AS TABLE OF NUMBER – that contains the identifiers of the customers whose orders should be retrieved. Other search criteria are the status of the orders and an upper and lower boundary for the order amount. The function returns an instance of order_table_t – which means it returns a collection of order_t objects.
The implementation of the function is fairly straightforward – if you are familiar with the use of nested table collections, the COLLECT aggregation operator and the TABLE FUNCTION operator in SQL. Note that these features were introduced in Oracle Database 8.0, 10g and 9i respectively. They have been around for a while.
The most interesting part of the function is highlighted in the red rectangle: table ORDERS is joined with something that is not a real table but that behaves as one. Using the TABLE function on the nested table collection with customer identifiers (p_customers_tbl) the query behaves as if a table with customer identifiers does indeed exist (with a single column whose value we access using the pseudo function column_value) and is joined on the CTR_ID column with the ORDERS table. Every ORDER record that is returned by the query is transformed into an OBJECT_T instance. All these OBJECT_T instances are taken together – with the COLLECT operator – and cast to the ORDER_TABLE_T type. An instance of that latter type is what we need – because it is the return type of the function.
A simple test – in PL/SQL – of this function looks like this:
and the output through the server output in this case:
With this PL/SQL package in place, the OrderService is quickly created using a database adapter. Note that the database adapter is remarkably good at dealing with Object Types and Nested Table collections.
The composite is further fleshed out – based on the predefined WSDL and XSD for the OrderService and using a Mediator to map from that external interface to whatever the database adapter is offering:
The mapping in the transformation is fairly simple – because of the very adequate conversion performed by the database adapter from the database types of ORDER_TABLE_T and ORDER_T to their counterpart XSD types:
After deployment of the composite OrderService, we can test it – with the same input as the PL/SQL test of the ORDER_SERVICE_IMPL package:
At this point both the domain services have been created and deployed. We are ready to create the composite CustomerOrderService. Note that the hardest work has been done by now, inside the PL/SQL package.
Implementation of the Composite CustomerOrderService
BPEL’s core strength is orchestrating web service calls. It is the perfect tool in many occasions to create a composite service. The CustomerOrderService is such a composite service – a service that uses multiple other services for its implementation. This architecture view tells the story of the CustomerOrderService:
This service invokes the CustomerService (in the CRM Domain) as well as the OrderService (in the SALES domain). BPEL is used to implement the composite service. This overview of the SCA Composite for the CustomerOrderService says it all:
WebService References are created in the composite for each of the two domain level services that need to be invoked. A BPEL component is added and wired to these two references. The CustomerOrderService is exposed as service and wired to the BPEL component. In go a location and out comes a list of orders:
The BPEL process itself is easily described. Step one – the first scope – consists of invoking the CustomerService to retrieve a list of customer identifiers for all customers located at the designated location. The second step – scope number two – entails calling the OrderService with this list of identifiers in order to retrieve the sought after order details.
A global BPEL variable is used to store the list of identifiers and carry over this list from the first to the second scope. The second scope contains two Transform activities – one to map the customer identifiers to the input variable for the OrderService and the second one to map the output from the OrderService call (the order records) to the output variable of the BPEL process.
Let us do a simple test: find all the open orders for customers located in Zoetermeer:
The message flow trace makes it clear what happened during the execution of the CustomerOrderService:
A BPEL process is instantiated. It invokes the CustomerService – that in turn makes a single call to a database adapter service (for the query against CUSTOMERS). Next the BPEL process goes on to invoke the OrderService (just a single invocation). This composite too invokes a database adapter service (for the query against the ORDERS table). This is also a single call – a single round trip from SOA Suite run time to the database.
We have retrieved records based on a join across the domains CRM and SALES – without actually creating a dependency between the two databases involved – and without sacrificing [a lot] in terms of elegance, performance and scalability.
Resources
Download the JDeveloper 11.1.1.7 (11gR1 PS6) workspace with all the sources discussed in this article: CrossServiceJoin,zip.
