Bring Legacy Data To Your Mashups With The WSO2 Mashup Server

Introduction

Spreadsheets, CSV files and Databases are among the most basic forms of data representation and manipulation. They have been around for ages and probably will be for many years to come. The word 'legacy', meaning that which is handed down from a predecessor, is often used in the context of these technologies and data withinm. In a 'service oriented' world, challenges of integrating such legacies with the present generation of applications is addressed by products such as the WSO2 Data Services Solution. The WSO2 Mashup Server embeds the WSO2 Data Services solution from version 1.5, enhancing the agile service composition capabilities already present in the Mashup Server. In this tutorial, we will go through the steps involved in exposing a Microsoft Excel preadsheet as a service using the WSO2 Mashup Server Web Console.

Applies To

Tutorial Scenario - The Blog Aggregator

We will build an RSS aggregator, that would scan the blogosphere for a given list of RSS feeds and display them it a single view. meta-data for the targeted blogs will reside in a spreadsheet. We will first expose these data as a service, and then use the WSO2 Mashup Server's RSS feed reading support to retrieve feeds on a regular basis.

Creating the Data Service

Step 0: Create a Spreadsheet with sample data

For the purpose of this tutorial, we will be using a spreadsheet with two columns of data containing the name of the blog author and the RSS feed for that blog as illustrated below. I saved mine in /home/tyrell/data/blog-data.xls and will be using this path throughout the tutorial.

Fig 1 - Sample data

Step 1: Launch the Data Service Creation Wizard

Once you are signed-in to the WSO2 Mashup Server Web console, look for the Management Tasks Panel as illustrated in Fig 2. Click Create a new Data Service link found there.

Fig 2 - The service creation link

Step 2: Give a name to your data service and select the data source type

Once Step 1 is completed, you will be presented with a form to be filled, which asks you to fill a name for the data service and select a data source type. Select the type MS Excel (97-2003) since that would be our data source. Give the full path to your spreadsheet containing blog meta-data when asked. Click Next when done.

 Fig 3 - Service name and data source type selection

Step 3: Type a Query to harvest data from our source

 The next step of the wizard allows us to create a query to retrieve necessary data for the data service. Click New Query button, and you will be presented with a dialog to provide necessary parameters as illustrated below:

Fig 4 - Adding a new query

The above dialog has two sections. One to input data extraction parameters and another to cinfigure how data is returned by our new data service. In a nutshell, data extraction parameters mean;

• Query ID - A unique name for this query. There can be one or many queries in a data service. We will name ours as getBlogsQuery
• Workbook Name - Since our data source is a spreadsheet, we have to specify which workbook to use. In our spreadsheet the data required is in sheet1. So we specify that here.
• Start reading from - From which row should the data reading start. Since the first row of our data is allocated to the titles, let's specify 2 here.
• Rows to read - How many rows should be read from the starting point. Here, the value -1 means all rows. That's what we'll use as well.
• Headers available - We will set this to true, since we have named our columns.

The second section allows us to configure the output XML as follows;

• Grouped by element - The name for the Root element of the output XML. Let's use Blogs as our group element.
• Row name - What name should be given to a row of results. Blog sounds appropriate for our service.
• Row namespace - This is an optional parameter. You can specify a namespace here if required. But we'll leave it blank.
• Output Mappings - Each row of results will contain one or more data elements and the 'Add New Output Mapping' button allows us to map an element name for each data item. We'll map Author to the Author element and RSS Feed to the Feed element.

The dialog should look as illustrated below once done. Click the Next button to continue.

 Fig 5 - Add new query dialog completed with input

Step 4: Map your Query to a Service Operation

The next step of the wizard allows us to map the query created in the previous step to a Service Operation, which will in turn expose it to the outside world. Since our data source is a spreadsheet, we do not have input parameters. Therefore, this step only requires us to give a name for the operation and specify the corresponding query. Click the Add New Operation button and enter the operation name in the resulting dialog. We will use the name getBlogs for the operation as illustrated below.

Fig 6 - Mapping a query to a service operation

Once you are done, press the Finish button and your data service will be scheduled for deployment. Go to the home page of the WSO2 Mashup Server Web console, and you should see your newly deployed data service under My Mashups. You might have to refresh the page if it is not listed there already.

Fig 7 - New Data Service, meet the world!

Step 5: Congratulations! your Data Service is now deployed. Let's test it.

If you have used the WSO2 Mashup Server Web Console before, you should be familiar with the TryIt feature. If not, let's have a look at how to use this feature to make sure our service is returning the data we expect. In the home page, click the name of our new data service (BlogMetaData). You will see a page as illustrated below, which contains a collection of useful information about the service. Note the TryIt links there.

Fig 8 - Details of the BlogMetaData service

Click the first Tryit link, and you will be presented with the page below. You will see a button to test our getBlogs service operation there. When you click it, a service call will be made to the BlogMetaData service and the results displayed. The data contained in your spreadsheet will be represented in XML, indicating that the service is ready for prime-time.

Fig 9 - getBlogs operation results 

Mashing It Up. Data Service Meets Blogosphere

Now it's time to use feed URLs provided by our BlogMetaData service to aggregate blog posts by the corresponding authors. For this, we will create another service. This would be a JavaScript service and in a way the 'actual' mashup. Creating a JavaScript service is as easy as it was in the previous part. Click the Create a new Mashup link under Management Tasks and you will be presented with a dialog to enter a name for your mashup. Let's name our mashup BlogAggregator and continue. You will see a new page with an editor interface. This page allows you to write the JavaScript source for your mashup, as well as the client user interface in HTML. You will also notice a third tab to write a Google Gadget, since the WSO2 Mashup Server itself is a Gadget container

from version 1.5 onwards. Skeleton code for you to get things started can be generated as well.

Let's start with the JavaScript editor. In the button panel at the bottom of the page, click Apply Changes to save your Mashup source. This should be the starting point for any Mashup you write because it will create your mashup in the file system, which in turn schedule it to be deployed as a service. Once done, you can generate a skeleton code for the HTML UI and Gadget, if required.

Fig 10 - Creating a new Mashup

Fig 11 - The 'Mashup Editor' with skeleton code for the Mashup

Once our new mashup is deployed (an you can verify this as you did before from the Web console's home page), we can generate a skeleton HTML UI from the mashup editor and save it. This will be useful as an additional testing method (apart from the TryIt utility) as well as being the face of your mashup to the world. Click the tab named Custom UI Code and in that tab click the Generate Template button. You will see the generated code as illustrated below. Just click Apply Changes and the file will be saved in the server.

Fig 12 - The generated skeleton code for the Custom UI

We can access the Custom UI we just created through the link https://localhost:7762/services/tyrell/BlogAggregator/. This link can be found on the Service Details page for BlogAggregator as well. Now that we have our Mashup running with skeleton code, let's write some of our own code to complete this Mashup. Since it's been a long article, let's go straight to the code and do a walk through later. 

function aggregateBlogs(){
   // Step 1 - Call our Data Service and get the list of blog RSS Feeds
   
   // The address of BlogMetaData Data Service
   var endPoint = "https://localhost:7762/services/tyrell/BlogMetaData";
   // The Service Operation to call
   var operation = "urn:getBlogs";  

   // Creating a new WSRequest Host Object, which can call Web Services
   var request = new WSRequest();
   var options = new Array();
   options.useSOAP = 1.2;
   options.useWSA = 1.0;
   options.action = operation;
   request.open(options, endPoint, false);
   request.send(null);
   
   // Getting the response received from the Data Service.
   // This response will contain a list of Blog RSS Feeds
   var result = request.responseXML;

   
   // Step 2 - Iterate through the Feed list and get recent posts
   var namespace = new Namespace("");

   var aggregatedPosts = <div></div>;

   for each (var record in result.namespace::Blog){
      var currentPostBlock = <div></div>;

      var blogAuthor = record.namespace::Author.toString();
      currentPostBlock.appendChild(<h2>{blogAuthor}</h2>);

      // fetch the RSS Feed
      var feedURL = record.namespace::Feed.toString();         
      
      try{
         var reader = new FeedReader();
         var feed = reader.get(feedURL);     
      
         // Get the latest post form the Feed and add it to the block
         var post = feed.getEntries()[0];      
         currentPostBlock.appendChild(<h3>{post.title}</h3>);
         currentPostBlock.appendChild(post.content);

         // Add this post block to the aggregated content
         aggregatedPosts.appendChild(currentPostBlock);
       }catch(e){
           system.log(e);
         }
   }
   
   return aggregatedPosts.toString();
}

Fig 13 -  TryIt results for the aggregateBlogs operation

Don't panic. Here's the walk through promised

Our Mashup above has two steps (as mentioned in the code comments).

Step 1 involves calling our BlogMetaData service and getting a list of blog author names and associated RSS Feed URLs. In the WSO2 Mashup Server, we get a few objects that we can use to gather data from various sources. The WSRequest Host Object is one such object, which allows us to call a SOAP Web service with the necessary payload required. Once a service endpoint address and the appropriate operation to invoke is passed (with operation parameters, if necessary), we can obtain the result as XML. This works well because of the extensive support for E4X we have in the WSO2 Mashup Server.

Step 2 utilizes E4X to iterate through the list of author-feed pairs and uses the Feed Host Objects (namely Feed, Entry and FeedReader) provided by the WSO2 Mashup Server to harvest the latest posts from those feeds. We also use E4x to format our aggregated result into an HTML div, which can be directly plugged to the DOM (Document Object Model) of a Web Page.

Modifying the Custom UI to call the aggregateBlogs operation and display the aggregated posts

Since we get the aggreagated posts as an HTML div element, we can easily attach them to the Custom UI page of our Mashup. Let's try to modify the already generated skeleton code to call the aggregateBlogs operation. We will firast edit the init() function to call aggregateBlogs instead of the default toString operation as demonstrated below:

function init() {
        
        // Set up a callback and an error handler for the aggregateBlogs operation.
        BlogAggregator.aggregateBlogs.callback = showPayload;
        BlogAggregator.aggregateBlogs.onError = handleError;

        // Invoke the operation/method.  Since there is a callback defined, the call is asynchronous.        
        BlogAggregator.aggregateBlogs();

    }

Then, we will modify the showPayload() function to inject the returned HTML div element to the results console.

function showPayload(payload) {        

        document.getElementById("result-console").innerHTML = payload;        
    }

Once we save the custom UI and access it from a browser, our blog aggregator will look as illustrated below with the latest posts from selected authors.

Fig 14 - The final result. Our Blog Aggregator in action 

Summary

In this article we looked at the data services support in the WSO2 Mashup Server, which allows us to expose legacy data as Web services. We succeeded in exposing a spreadsheet as a service and created a mashup of the data within the spreadsheet and RSS feeds from the blogosphere. In doing so we got familiar with a host of other powerful features, such as the TryIt utility, the Data Service Creation Wizard and the Mashup Editor.

Resources

• The WSO2 Mashup Server project page
• The WSO2 Mashup Server online User Guide also found in your local distribution
• The source code of services created in this tutorial and the spreadsheet used can be found below.

Author

Tyrell Perera, Senior Software Engineer, WSO2 Inc. [email protected]