Tuesday, August 23, 2016

Using WSO2 DSS retrieve data from multiple databases in a single Query

I came across an issue of having same table in multiple databases and having to write data multiple times when syncing these data with external systems. Therefore I thought of transferring these duplicated tables into one single common database. Then this common database will to be used by several of our internal systems. However I recently came across an issue that I needed to retrieve data from multiple databases using a single query. Since I'm already using WSO2 DSS, I found a solution to tackle this problem.

Here's how to do it. For the ease of understanding I have divided the steps into two parts.

Part 1 - Configure MySQL Database

Login to your local MySQL db and create two databases.
Then create two tables and insert some test data as follows.

mysql> create database db1;
mysql> create database db2;

mysql> use db1; 
mysql> CREATE TABLE employees ( EmployeeID int(11) NOT NULL AUTO_INCREMENT, FirstName varchar(255) DEFAULT NULL, LastName varchar(255) DEFAULT NULL, Team varchar(255) DEFAULT NULL, PRIMARY KEY (EmployeeID));
mysql> insert into employees (FirstName, LastName, Team) values('Suhan', 'Dharmasuriya', 'InternalIT');
mysql> insert into employees (FirstName, LastName, Team) values('Thilina', 'Perera', 'Finance');
mysql> select * from employees;
| EmployeeID | FirstName | LastName     | Team       |
|          1 | Suhan     | Dharmasuriya | InternalIT |
|          2 | Thilina   | Perera       | Finance    |
2 rows in set (0.00 sec)

mysql> use db2;
mysql> CREATE TABLE engagements ( EngagementID int(11) NOT NULL AUTO_INCREMENT, EmployeeID int(11), PRIMARY KEY (EngagementID));
mysql> insert into engagements (EmployeeID) values(1);
mysql> select * from engagements;
| EngagementID | EmployeeID |
|            1 |          1 |
1 row in set (0.00 sec)

Now lets test the following MySQL query and get results. Here I have used a LEFT OUTER JOIN.
Result will be obtained from the two databases. We will save this query in WSO2 DSS. 

mysql> SELECT A.EmployeeID, A.FirstName, A.LastName, A.Team, B.EngagementID from db1.employees AS A LEFT OUTER JOIN db2.engagements AS B ON A.EmployeeID=B.EmployeeID;
| EmployeeID | FirstName | LastName     | Team       | EngagementID |
|          1 | Suhan     | Dharmasuriya | InternalIT |            1 |
|          2 | Thilina   | Perera       | Finance    |         NULL |
2 rows in set (0.00 sec)

Part 2 - Configure WSO2 DSS

  1. Download WSO2 DSS 3.5.0 from here (http://wso2.com/products/data-services-server/). If you already have the product zip file (wso2dss-3.5.0.zip) continue with the next step.
  2. Unzip the product to a path containing no spaces in the path name. This is your <DSS_HOME>
  3. Download the MySQL connector jar here (http://dev.mysql.com/downloads/connector/j/) and copy it to <DSS_HOME>/repository/components/lib/
  4. Start the WSO2 DSS server.
To start the server, your have to run the script wso2server.bat (on Windows) or
wso2server.sh (on Linux/Solaris) from the <DSS_HOME>/bin folder.
  1. Log in to DSS by using the default credentials (username: admin/ password: admin).

Creating the data service

  1. Create a new data service 'sampleDS'
  2. Home -> Manage -> Services -> Add -> Data Service -> Create
  3. Create a datasource as follows by referring to the above local MySQL database.
  4. Add new Query; query ID: 'getEngagementsPerEmployee', query: above tested query, and press generate response link
  5. No operation is defined in this example, therefore press next
  6. Add new Resource; Resource Path: '/engagements' by selecting Resource Method: 'GET' and selecting Query ID: 'getEngagementsPerEmployee'
  7. Press Finish to create the data service

Refer following screenshots for more information.

Now you can test the datasource as follows.

Open a new tab in your browser and type the following URL: http://localhost:9763/services/sampleDS/engagements

You will get the following response:
<Entries xmlns="http://ws.wso2.org/dataservice">
<EngagementID xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true"/>

Refer following Data Service synapse configuration for more information.
<data name="sampleDS" transports="http https local">
   <config enableOData="false" id="datasource">
      <property name="driverClassName">com.mysql.jdbc.Driver</property>
      <property name="url">jdbc:mysql://localhost:3306</property>
      <property name="username">root</property>
      <property name="password">root</property>
   <query id="getEngagementsPerEmployee" useConfig="datasource">
      <sql>SELECT A.EmployeeID, A.FirstName, A.LastName, A.Team, B.EngagementID from db1.employees AS A LEFT OUTER JOIN db2.engagements AS B ON A.EmployeeID=B.EmployeeID;</sql>
      <result element="Entries" rowName="Entry">
         <element column="EmployeeID" name="EmployeeID" xsdType="string"/>
         <element column="FirstName" name="FirstName" xsdType="string"/>
         <element column="LastName" name="LastName" xsdType="string"/>
         <element column="Team" name="Team" xsdType="string"/>
         <element column="EngagementID" name="EngagementID" xsdType="string"/>
   <resource method="GET" path="/engagements">
      <call-query href="getEngagementsPerEmployee"/>