Nested Queries with PHP Data Services

We use the term ‘DataService’ when we expose a database as a web service. WSO2 has a separate product for the DataService space which was earlier shipped bundled with WSAS. There you write a configuration xml explaining what data to be exposed in your database and drop it in the WSAS java app server and you get the DataServices deployed without much effort. Couple of months ago we published PHP DataServices library that allow you do deploy Your DataService within a popular LAMP or WAMP stack. With the WSF/PHP 2.0.0 release, you can have the PHP DataService library bundled with the release, hopefully within next couple of weeks.

One of the main problem DataService developers face is how to design an elegant web service API that will provide the requested information from a row database straightaway. Because when we normalize a database, the very closely related data are spread in different tables just keeping the association with foreign keys. So in order to retrieve the useful information database developers have to write multiple queries to the database. Is that same with DataServices?, Do you have to write multiple web service requests to return some useful data?

No. You don’t. You have Nested Queries to save you. With nested queries you can configure your DataServices, so all the relevent information can be extracted and send as a single SOAP message. I will take a demonstration to explain this.

Say you are working for a company that manufacture or sell electrical equipments, say TVs. And your boss is asking you to prepare a whole list of customers who ordered good from them. He want order information (like order date, status of the order) plus the name of the customers in the list.

Here is the table structure that you have.
Orders

Customers

From this you have to find a list of orders with order date, status (available in Orders table) and ordered customer information available in Customers table. As you may seen you have to query both the tables to get the required data.

First you have to decide a service operation that you going to expose as a web service. Operation is uniquely identified by its name. We will give our operation the name “getOrders”. In fact in our PHP Data Service API we treat a Operation same as a query. In general operation/query has following properties associated with it. Note for the complete API please check this Oxygen Tank Page.
Properties of Data Service Operation/Query

<orders>
  <order>
    order 1 details
  </order>
  <order>
    next order details
  </order>

  <order>
    etc
  </order>
</orders>

array("Order_number" => "ORDERNUMBER",
      "Order_date" => "ORDERDATE",
      "Status" => "STATUS");

<order>
  <Order_number>
    3
  </Order_number>
  <Order_date>
    2008-06-09
  </Order_date>

  <Status>
    Shipped
  </Status>
</order>

getCustomer(customerId)

SELECT o.ORDERNUMBER,o.ORDERDATE, o.STATUS,o.CUSTOMERNUMBER FROM ds.Orders o

So as I described in the queries section you have to write the whole thing for the getCustomer operation as well.

$getCustomer = array(
    "inputFormat" => array("customerNumber" => "INTEGER"),
    "outputFormat" => array(

            "resultElement" => "Customers",
            "rowElement" => "Customer",
            "elements" => array("name" => "CUSTOMERNAME")
            ),
    "sql" => "select c.CUSTOMERNAME from ds.Customers c where c.CUSTOMERNUMBER = ?",
    "inputMapping" => array("CUSTOMERNUMBER" => "customerNumber")

    );

And here is the getOrders operation/query that actually use the getCustomer as a part of it.

$getOrders = array(
    "outputFormat" => array(
        "resultElement" => "Orders",
        "rowElement" => "Order",
        "elements" =>  array(

            "Order_number" => "ORDERNUMBER",
            "Order_date" => "ORDERDATE",
            "Status" => "STATUS"),
         "queries" =>  array($getCustomer)
         ),
    "sql" => "select c.CUSTOMERNAME from ds.Customers c where c.CUSTOMERNUMBER = ?"
    );

Then we will to define the operations array which we are going feed to the DataService class. Note that now we have two operations getOrders and getCustomer. From that I used getCustomer to help the building of getOrders. And I don’t want to publish getCustomer as a separate operation. Simply the boss doesn’t ask for that operation :). So my operation map is like this,

$operations = array("getOrders" => $getOrders);

This operation map will be given as a constructor argument of DataService class. If you are familiar with WSF/PHP WSService class, there is no much different between these two classes. You can give all the options available in the WSService class to the DataService class. Say you want to encrypt the messages, just use the “policy” and the “securityToken” options. So with DataService class you have the luxury of using WS-Security as well as WS-Reliable Messaging for free.

To finish this post I will put the complete code for the above scenario. You can check the same service option in action in the WSF/PHP Demo site (Try the demo4).

<?php
// include the DataService library.
// NOTE: this path is valid only from wsf/php 2.0.0
require_once("wso2/DataServices/DataService.php");

// set the configurations
$config = array("db" => "mysql",
   "username" => "dimuthu",
   "password" => "thiswillnotbeasecretanymore",
   "dbname" => "ds",
   "dbhost" => "localhost");

// getCustomer Query - this used by the getOrders query/operation
$getCustomer = array(
    "inputFormat" => array("customerNumber" => "INTEGER"),
    "outputFormat" => array(

            "resultElement" => "Customers",
            "rowElement" => "Customer",
            "elements" => array("name" => "CUSTOMERNAME")
            ),
    "sql" => "select c.CUSTOMERNAME from ds.Customers c where c.CUSTOMERNUMBER = ?",
    "inputMapping" => array("CUSTOMERNUMBER" => "customerNumber")

    );

// The operation/query getOrders - Read the complete blog to see what 
// each of these option stand for
$getOrders = array(
    "outputFormat" => array(
        "resultElement" => "Orders",
        "rowElement" => "Order",
        "elements" =>  array(

            "Order_number" => "ORDERNUMBER",
            "Order_date" => "ORDERDATE",
            "Status" => "STATUS"),
         "queries" =>  array($getCustomer)
         ),
    "sql" => "select c.CUSTOMERNAME from ds.Customers c where c.CUSTOMERNUMBER = ?"
    );

// operation array
$operations = array("getOrders" => $getOrders);

// create the DataService object and reply
$ds = new DataService(array("operations" => $operations,
          "config" => $config,
          "serviceName" => NextedQuerySample));

$ds->reply();

?>