In a previous article – https://technology.amis.nl/2015/03/03/preparation-for-live-mobile-hacking-with-an-ofm-12c-red-stack-budapest-2015/– I introduced the challenge Luc Bors and I were facing for the Live Mobile Hacking session at the Oracle Fusion Middleware EMEA Partner Forum. For an audience of some 200 SOA Suite and Oracle Middleware experts – including the product management teams for several of the products we were going to demonstrate – we were to develop live both a mobile app as well as a mobile backend (REST APIs) to support the mobile app.
The use case was defined – an app for flight attendants to be used on tables to look up flight details, inspect the passenger list, update the flight status and record passenger complaints. Luc handled the mobile app based on the agreements we made on the REST resources, services and payloads. It was up to me to implement the backend – consisting of a database, a SOA composite to expose the database data and operations at middleware level (in SOAP/XML) and a Service Bus project to expose the required REST service interface with JSON payload, leveraging the SOAP/XML service published by the SCA Composite.
This article will briefly describe the steps I went through. All source code for the mobile backend can be downloaded from a GitHub repository.
Step One – Database
The database created for Flying High contains just five tables – as shown in the next figure.
However, these tables are encapsulated – hidden from view from external consumers. This I consider to be a best practice. For various reasons that include the encapsulation of (potentially complex) SQL statements in APIs in the database rather than in client applications and the freedom of changing the actual implementation of tables and the way data is stored. The API that is externally exposed is a PL/SQL package FLIGHT_SERVICE. The package specification contains three unit – two functions and a procedure – for retrieving flight details, the passenger list for a flight and for updating the status of a flight.
The input and output parameters for these units are all based on user defined types. This allows me to exchange nested data structures in single round trips, provide data structures that map very well to XML messages and that can very well be handled by the database adapter. Additionally, use of these UDTs allow the database developers to work in a rather elegant way – leveraging their tools to their full potential.
When you check the script database.sql in the source code you will find the definitions of the tables, the UDTs and the PL/SQL package.
Step Two – SOA Composite, Database Adapter, SOAP/XML Service
Each of the operation in the database package Flight_Service is exposed as Reference in a SOA Composite application using three database adapter bindings. The User Defined Type based inputs and outputs to these operations are represented in (very similar) XML structures in the interface definitions for these three bindings.
In parallel with the creation of the PL/SQL package, a WSDL was created for the FlightService, with a similar granularity in operations and message data structures. Note: this is not required, but it certainly makes life simpler when you have to do a pressure cooker demo. The WSDL is visualized here:
You can access the source itself through this link. The FlightService.wsdl has a companion XSD document that describes the request and response messages as we want to expose them to consumer of the SOAP/XML service.
The structures in the XML Schema Definitions are quite close to the User Defined Types in the PL/SQL API. However, they are defined from a canonical model – not from database table and column definitions.
A Mediator component was added to the
For each of six back-and-forth hand overs, I have created XSLT maps to convert from canonical input message to the input required by one of the three database adapter bindings as well as to convert from each of three outputs from the database adapters bindings to the response message format specified in the FlightService WSDL and XSD.
The Mediator has three routing rules configured that each use two XSLT Maps. The maps are quite straightforward, thanks to the similarities between the PL/SQL API object structures and the XSD definitions. One example of an XSLT map is shown here:
This map uses a DVM (domain value map) lookup to convert between the code used for FlightStatus in the database (sch, brd,cld) and the canonical values to be used in the response message (scheduled, boarding, closed). The DVM definition shown next. The document was transferred to the database MDS under the runtime SOA Suite.
After deploying the SOA composite (and configuring both the Data Source and the Database Adapter connection), the FlightService can be invoked, for example from SoapUI:
This screenshot shows a call to the getFlightDetails operation and the resulting response – wrapped in a SOAP envelope and of course all XML in nature.
Each service call will start a new instance of the FlightService SOA Composite. The instance will make the call to the database. All interaction can be tracked – with a fine grained enough audit level at least – as shown in the next figure.
This figure shows the XML structure that came out of the Database Adapter binding – and that was converted from the User Defined Type at database level. Note the value of BRD for FLIGHT_STATUS that is converted by the DVM lookup to boarding.
Unfortunately this beautiful service is still not good enough for Luc and his Mobile App. A REST/JSON service was the agreement. So my next challenge: expose the FlightService as a REST service that uses JSON payloads. That story is told in a follow up article.
