Jekyll and Hyde or The case of the multiple identity syndrome – Using Identity Cross References in Oracle 11g SOA Suite

The 11g SOA Suite brings us the Service Fabric that has messages flowing from one SCA Service Component to the next – Mediators and BPEL processes are called from inbound adapter services or external consumers and may call out to references to external providers or outbound adapter service. In this idyllic world, much of the work actually going on is the mapping of messages from one (service’s) domain to the next. Of course we try to converse as much as possible in terms of canonical message structures, but sooner or later we come to the boundary of our domain and need to transform again to some domain or service specific message structure.

Domain Value Mapping

In addition to the mapping of the message structure, we usually have to put up with some level of value mapping as well: different domains frequently use -slightly or hugely -different labels for the same concept. One domain may use M, F and N for gender whereas another has MALE, FEMALE and UNKNOWN or even MAN, WOMAN and UNDECIDED. Messages that have to convey the gender will have to abide by the vocabulary in the target domain. That means that frequently message have to undergo domain value mapping in addition to plain structure-based transformation. The Mappings in the SOA Suite can make use of Domain Value Maps – dictionaries of domain specific values that are mapped across domains – and associated XPath functions that can do lookups in the DVM to replace a value from one domain – M – with the corresponding value from another domain – MALE.

Identity Mapping

A special type of value mapping concerns identity mapping. It is not uncommon for entities to exist in various domains. Patients, Suppliers, Doctors, Equipment and the likes more often than not are recorded in multiple systems – for example in the Financial and HR system, the Planning and Medical Information Systems. A specific entity has an identifier in each of these systems. However, this identifier – frequently a meaningless numerical value – is different in each system. For example: Dr. Boris Jankovic is known as 90210 in the Financial domain, has id-value 666 in the Planning application and is HMSA007 in the Medical Information system. When a message concerning this doctor is sent between services, we need to map the good doctor’s identity to maintain a reference to the same person in the context of each domain.

The Cross Reference functionality that the SOA Suite provides can be used to map identities across domains. This facility uses a database table – default name is XREF_DATA – that contains records per entity with the identity values in all domains. The definition of a cross reference is stored in a file, for example Doctor.xref. The file contains the name of the cross reference as well as all the End Systems (identity domains) that are mapped. This file is exposed from the MDS, to be used across components and composite applications. In addition to all real identity domains, it is a good idea to add a ‘canonical’ domain that contains the common or generic identifier that is used to identify the entity in all canonical, domain independent, messages that flow through the service fabric.

This article discusses and demonstrates the use of this cross reference functionality

The Cross Reference Table then logically looks like this:

	FINANCIAL	CANONICAL	PLANNING	MEDICAL
Boris Jankovic, Shrink	90210	92182172178326721	666	HMSA007
John Minder, Pediatrician	71243	82172178817273631	–	MYMY123

Note that not all entities need to exist – already or at all – in all End Systems.

Interacting with the Cross Reference at run time

The mapping itself is invoked through XPath functions in Mappings between XSD documents representing distinct (identity) domains. There is of course an XPath function to find the identity of an entity in a certain domain given the identity in another domain:

xref:lookupXRef(“Doctor.xref”,”FINANCIAL”,”90210″,”CANONICAL”,true())

This function call will access the Doctor cross reference to try to find the canonical identity for a Doctor identified by 90210 in the Financial domain. If no identity is found for this entity, an exception will be thrown, due to the last parameter being set to true. Note how similar this function is to the dvm:lookupValue() function.

However, the values in the Cross Reference are not static, as new entities are created, updated and even removed all the time. We have a set of XPath functions at our disposal to add, modify and remove entries from the Cross Reference (table). This allows us for example to ensure that when the ‘CreateDoctorInPlanningSystem’ service is invoked from the Financial domain, the Doctor is added to the Cross Reference with her identity in the Financial domain along with the new identity in the Planning System. The XPath function populateXRefRow is used like this to create a new record in the Cross Reference that contains the FINANCIAL identity as well as the CANONICAL identity. The latter identity is generated as GUID value. This value is also returned by the function and assigned in this XSL snippet to the identifier element in the target document:

<inp1:identifier>

<xsl:value-of select=’xref:populateXRefRow(“Doctor.xref”,”FINANCIAL”,/top:FinDoctorsCollection/top:FinDoctors/top:id,”CANONICAL”,orcl:generate-guid(),”ADD”)’/>

</inp1:identifier>

When we want to add the identity in another End System for an entity that already exists in the Cross Reference, we can use this XPath expression:

xref:populateXRefRow(“Doctor.xref”,”CANONICAL”,$initial.request/inp1:Doctor/inp1:identifier,”PLANNING”,/db:OutputParameters/db:ADD_STAFF_MEMBER,”LINK”)

Here we add the value in element ADD_STAFF_MEMBER as the Identity in the PLANNING domain to the Cross Reference record that has the value in inp1:identifier as its CANONICAL identity.

Example of using the Cross Reference facility

Let’s take a closer look at an example. The hospital has a FINANCIAL application; this is where doctors – records for doctors working in the hospital – are first created and get assigned their first identity. When a new doctor is created, this new doctor needs to be registered in the PLANNING application. This Planning domain has its own stubborn way of assigning identities to its records, so the doctor will get a second identity when registered in the Planning application.

We need to make sure that future message exchange between FINANCIAL and PLANNING regarding a specific doctor are correctly interpreted. Therefore, we need to build the Cross Reference to map doctor identities from and to these two systems. We will create a Composite Application that finds new doctor records in the FINANCIAL system and propagates them to PLANNING, meanwhile recording the identities from both FINANCIAL and PLANNING in the Cross Reference, as well as a canonical identity, assigned by the Composite Application.

The steps we go through:

Start JDeveloper; Create a new Application; Create a new SOA Project with Composite Application

Create a Mediator called PropagateAndXRefFDoctor. Its WSDL will be specified later.

Create a new Cross Reference, called Doctor:

Specify the first two End Systems – FINANCIAL and PLANNING. The press OK.

In the property page that now appear, add the third End System: CANONICAL.

Create an XSD with the Canonical Doctor message format:

Now we can create the WSDL for Mediator PropagateAndXRefFDoctor based on this XSD. The Mediator’s OfferNewDoctorForProcessing operation is one way (to reply) and takes a Canonical Doctor message as input.

Create a Database Adapter Service that polls for new records in the FIN_DOCTORS table (using the XREFFED_YN column as the logical delete flag). Note: the DoctorTables.sql script contains the DDL and DML statements for creating the table and its data.

Now wire the Database Adapter Service ReadNewDoctorRecords to a new Mediator ProcessNewDoctorsFromFinancialDomain.This Mediator has a simple task: map the messages from the Database Adapter Service to the Canonical Doctor format.

Create the required mapping DoctorFromFinancialDB_To_CanonicalDoctor.xsl.

The key element in this mapping is the function in the mapping from top:id in the source tree to inp1:identifier in the target tree: the code under the icon is:

<inp1:identifier>

<xsl:value-of select=’xref:populateXRefRow(“Doctor.xref”,”FINANCIAL”,/top:FinDoctorsCollection/top:FinDoctors/top:id,”CANONICAL”,orcl:generate-guid(),”ADD”)’/>

</inp1:identifier>

Here we use the populateXRefRow function to add a row to the Cross Reference Doctor. This new row has an Identity value for the FINANCIAL End System – derived from the top:id element which has the value from the FIN_DOCTORS table’s ID column for this doctor – and the CANONICAL identity, derived using the generate-guid() XPath function. When a message has been mapped using this XSL Map, an entry is created in the Cross Reference that maps a doctor between FINANCIAL and CANONICAL (two database records are created for this).

Wire this Mediator top to the second Mediator PropagateAndXRefFDoctor that deals in canonical messages only:

Now create a new Database Adapter Service CreateDoctorInPlanningDomain, outbound, that calls the Database PLSQL function add_staff_member, that will add the doctor to the table PLN_STAFF. The function will assign an identity to the doctor record in the PLANNING domain. It will return this identity to the invoker of the function.

create or replace function add_staff_member

( p_given_name in varchar2

, p_family_name in varchar2

, p_staff_role in varchar2

) return varchar2

is

l_id pln_staff.id%type;

begin

l_id := substr(extract(day from sysdate )|| to_char(sysdate,’SS’)||p_family_name,1,15);

insert into pln_staff

(id, given_name, family_name, staff_role)

values

( l_id

, p_given_name, p_family_name, p_staff_role

);

return l_id;

end;

Create the mapping from the Canonical Doctor message to the Input Message. This mapping is straightforward, no Cross Reference issues at this point.

However, the return message from the CreateDoctorInPlanningDomain service is something else altogether: that message contains the identity assigned to the doctor in the PLANNING domain. And that identity should somehow be added to the Cross Reference, associating it with the Canonical and the Financial identities for this same doctor.

We need to invoke the XPath function populateXRef() again, this time in the mode “LINK” – since we want to link a new identity value reference to an already existing entity record in the cross reference. The record we want to add this link to is found using the Canonical Identity – that we have in the Canonical Doctor message that was the input for the PropagateAndXRefFDoctor Mediator.

We first need the context for this XPath function to be executed. That means there has to be a Mapping for the response from the CreateDoctorInPlanningDomain service to another target service. The Mediator itself is one-way, it does not return a reply. So what we can do is send the response to another target service, meaningful or not, to have the identity in the PLANNING domain added to the Cross Reference.

Let’s create a File Adapter Service – WriteNewXReffedDoctorToLogFile – that writes every new doctor with his or her various identities to a log file. We go through the usual steps: create a sample file, run the File Adater Configuration Wizard and have it bring up the Native Format Builder wizard. Pretty soon we have the outbound File Adapter Service available.

We can now configure the Synchronous Reply part of the Routing Rule in the PropagateAndXRefDoctor Mediator. The target service for the reply is the outbound File Adapter Service.

In order to have access to the original request message sent to the mediator in the mapping of the reply from CreateDoctorInPlanningDomain to the WriteNewXReffedDoctorToLogFile service, we need to check the checkbox Include Request in Reply Payload when creating the mapping.

The Canonical Doctor Request message is available in the $initial.request XSL variable and is visible in the Source Tree in the visual mapper.

We will need to special XPath functions this time: one that adds the PLANNING identity to the Cross Reference, in much the same way as we saw before – using the Canonical Identifier from the original message:

The XPath function we need here is:

<imp1:PlanningIdentifier>

<xsl:value-of select=’xref:populateXRefRow(“Doctor.xref”,”CANONICAL”,$initial.request/inp1:Doctor/inp1:identifier,”PLANNING”,/db:OutputParameters/db:ADD_STAFF_MEMBER,”LINK”)’/>

</imp1:PlanningIdentifier>

We use the CANONICAL identifier to find the proper record in the Cross Reference Doctor.xref and add a link to this same record for the PLANNING identifier found in the ADD_STAFF_MEMBER element.

Next we also need to provide the value of the FINANCIAL identity to the Log File. However, that identity is not available in the Canonical Doctor message – as it should not of course. That means we have to use the Cross Reference to look up this financial identity – a perfect demonstration of the main purpose of that Cross Reference.

Instead of the populateXRefRow() function, this time we use lookupXRef(). Using the CANONICAL identifier in the Doctor(.xref) Cross Reference, we retrieve the identifier for the FINANCIAL End System:

In plain XSL code that looks like this:

<imp1:FinancialIdentifier>

<xsl:value-of select=’xref:lookupXRef(“Doctor.xref”,”CANONICAL”,$initial.request/inp1:Doctor/inp1:identifier,”FINANCIAL”,false())’/>

</imp1:FinancialIdentifier>

Summary

This completes our composite application. New doctor records in the FIN_DOCTORS table are found by the inbound database adapter service. This services pushes messages with doctor data to the first Mediator that maps them to the proper canonical format and creates a record in the Cross Reference with the Financial and Canonical identities for the new doctor record.

The second Mediator receives a canonical message and sends it – properly transformed – to the outbound database adapter service that uses the PL/SQL function add_staff_member to create the doctor in its PLN_STAFF table. The function and the service return the identifier assigned to the doctor in the PLANNING domain.

Finally, the second Mediator routes the response from the CreateDoctorInPlanningDomain – combined with the original request message – to the WriteNewXReffedDoctorToLogFile service. In the mapping to this outbound log-file writing service, the Planning identity of the doctor is added to the cross reference. The financial identity is retrieved from the cross reference to be added to the log file.

Deploying and Running the Composite Application

We deploy the composite application.

It picks up the records in the FIN_TABLE. When processing is complete, this is what the FIN_TABLE tells us:

And here is the now new contents in PLN_STAFF:

When we look in the XREF_DATA table that holds the data for the Cross References, we find these records:

Finally we check the log file that is written and there we find entries for each new doctor along with the three identities for each doctor.