Dimuthu’s Blog

In this case study “ PHP Data Services Extract Content from Drupal Database“, I intended to present how Data Service concepts can be applied to extract data with marketing value from  a CMS database and publish it as web services.  I used the drupal instance deployed at http://wso2.org as the CMS for the use case. And as the data service framework, I used WSF/PHP data services library, as it requires minimum changes to the existing infrastructure (the LAMP stack).

The case study also talks about how to consume the data service by any third party mashup to present textual/ graphical views of analyzed data. These mashups can be extended up to intergreate with social networks like facebook, twitter and etc to communicate back and forth a wider community and may be it can be used to track the distribution of active users using Google maps. Simply it makes easier to analyze business data + engagement with the community.

In simple term, data services are exposing data as web services. Anyway it is not a complete definition. Actually there are situations where we use data services not only to read data, but also to create, update or delete data. So it is better say data services are doing CRUD (Create, Read, Update, Delete) operations for data through web services. Simply it is like providing a web service interface for the database.

Anyway exposing  a database directly as a web service is like violating the first principles of software engineering. It will tightly couple the database structure with the interface, so whenever you do a simple change to the database schema, you will have to change the web service interface which will no doubt break all the clients depending on it.

So first of all, you have to design the service interface independent of the database schema you have. Most of the time you will able to find some query that would map the service interface to the database schema.

For an example think of publishing data in database table (say for table name “Games”) like this.

You will directly able to map these data to data service. So the response payload for a “getGames” operation would be something like,

<getGamesResponse>
   <Game>
      <Venue>
         xxx stadium
      </Venue>
      <Date>
         2008-12-18
      </date>
      <Team1>
          Italy
      </Team1>
      <Team2>
          Sweden
      </Team2>
      <Team1Score>
          34
      </Team1Score>
      <Team2Score>
          33
      </Team2Score>
   </Game>
   <Game>
      <Venue>
         yyy stadium
      </Venue>
      <Date>
         2008-12-19
      </date>
      <Team1>
          France
      </Team1>
      <Team2>
          Spain
      </Team2>
      <Team1Score>
          51
      </Team1Score>
      <Team2Score>
          50
      </Team2Score>
   </Game>
</getGamesResponse>

You can get this done with a SQL query simply as this,

SELECT * FROM `Games`

Say later if you decided to restructure the database table so the new database schema would be like this,

Games


GameId
Venue
Date


1
xxx stadium
2008-12-18


1
yyy stadium
2008-12-19

GamesTeams


GameId
TeamId
score


1
1
70


1
2
33


2
1
51


2
3
50

(Note here the Games and Teams are associated in the GamesTeams table.)

You can still use a query like the one in following to provide the same service interface, because it returns the same result set as the earlier one.

   SELECT Games.Venue,
              Games.Date,
              Team1.Name AS Team1,
              Team2.Name AS Team2,
              GameTeam1.Score AS Score1,
              GameTeam2.Score AS Score2
         FROM Teams Team1,
              Teams Team2,
              GamesTeams GameTeam1,
              GamesTeams GameTeam2,
              Games
        WHERE GameTeam1.gameId = Games.gameId  AND
              GameTeam2.gameId = Games.gameId AND
              GameTeam1.teamId = Team1.teamId AND
              GameTeam2.teamId = Team2.teamId AND
              Team1.teamId <> Team2.teamId AND
              Team1.name=?

This allows you to keep the service interface unchanged, regardless of the changes you done to the database schema.

In addition to that, we can uses the features of the data service libraries to give meaningful names for the response elements. If we take above example itself, say you want to rename ‘Score1′ to ‘Team1-Score’ and ‘Score2′ to ‘Team2-Score’. But the dash character (‘-’) cannot be used as a variable in a database query. But you can provide that in the map of sql name to element name when you are writing the data service.

If you are using WSF/PHP php data services Here is how you provide that mapping,

$outputFormat = array("resultElement" => "getGamesResponse",
                      "rowElement" => "game",  // this is the repeating wrapper element for each game
                      "elements" => array( "Venue" => "Venue", // this is the mapping of xml name => sql name
                                           "Date" => "Date",
                                           "Team1" => "Team1",
                                           "Team2" => "Team2",
                                           "Team1-score" => "Score1", // we are using different names for sql and xml
                                           "Team2-score" => "Score2"));

If you write it using Java Data Services, you can use do this with the following configuration xml,

        <result element="getGamesResponse" rowName="game">
            <element name="Venue" column="Venu" />
            <element name="Date" column="Date" />
            <element name="Team1" column="Team1" />
            <element name="Team2" column="Team2" />
            <element name="Team1-Score" column="Score1" />
            <element name="Team2-Score" column="Score2" />
        </result>

Another consideration, when talking about the best practices of designing web services or data services is the granularity of the service interface. We say a web service is fine grained if the service contains tons of small operations. The opposite of that is the coarse grained services, which contains large but few operations. And it is recommended to use the later approach, you can see why from the following example.

Lets say you are developing a web service to upload some information. In order to upload the information first the user have to be authenticated and then the content should be validated. Then only he can submit the actual information. Say you design a fine grained web service for that. So it has three operations.

1. authenticateUser
2. validateContent
3. submitInformation

If you design a coarse grained service interface it will be just the ‘uploadInformation’ operation. And all the three operation defined earlier will be called within the service logic and the clients will not be aware of that.

So here are some disadvantages that I see in the fine grained interface design compared with coarse grained design.

1. The coupling is too high. Since the client is linked with the service in three adapters. Say you decided to change the service so that you first validate the content and depending on the content it sometime bypasses the authentication. You can’t do this with the former approach (fine grained approach) without changing the clients. But if it were designed as a coarse grained service (just one uploadInformation operation), the client need not to be changed.
2. Two much time consumed for the transmission. Since we used three web service calls to do a single task the latency of the operation will largely depend on the network latency which will no doubt is comparatively very low. So the performance of the operation is degraded.
3. The clients can bypass some steps!, Say in your fine grained service, some client bypass the authentication and validateContent steps and jump directly to the sumbitContent operation. In fact you have to write special code to make sure the clients call the service in the correct sequence, otherwise it will be a big security hole.

So I think these three points will be enough to explain why you should try to design a coarse grained interface for your web service.

You can apply these principle when you write data services as well. Lets take the same example explained above. Say there is another table that keep the scoring shots of each game like the one in following.

Here also you can keep the operation like ‘getGames’ which we defined in the above section. That operation only provide the basic information like the winner and the scores. So if the clients want to know about scoring shots as well, he have to call another operation, say getScoringShots(game) that will return the scoring shots results for a given game. If in practice the clients only need to know about scoring shots of few selected games, then this approach is ok.
But say normally clients need to know about the scoring shots of each and every games. Then they have to call the operation ‘getScoringShots’ multiple times. That’s when the lesson we just learn about the granularity can be applied. We can actually provide another operation, say ‘getGamesDetailed’ that actually bundle the details of scoring shots for all games with other information about the game. Here is snip of the response XML, I’m talking about.

<getGamesDetailedResponse>
   <Game>
      <Venue>
         xxx stadium
      </Venue>
      <Date>
         2008-12-18
      </date>
      <Team1>
          Italy
      </Team1>
      <Team2>
          Sweden
      </Team2>
      <Team1Score>
          34
      </Team1Score>
      <Team2Score>
          33
      </Team2Score>
      <!-- additionally we have ScoringShots element -->
      <ScoringShots>
          <Shot>
              <Time> xxx </Time>
              <ScoringPlayer> xxx </ScoringPlayer>
              <ScoringTeam> xxx </ScoringTeam>
              <Score> xxx </Score>
          </Shot>
          <Shot>
              <Time> yyy </Time>
              <ScoringPlayer> yyy </ScoringPlayer>
              <ScoringTeam> yyy </ScoringTeam>
              <Score> yyy </Score>
          </Shot>
      </ScoringShots>
   </Game>

   <Game>
    <!-- Another game details are mentioned here -->
   </Game>

   <!-- More Games -->
</getGamesDetailedResponse>

You can generate this kind of response using the nested query support of the data services libraries.
You can checkout more details about nested queries in php data services from my old post about php data service API.

LAMP (Linux + Apache + Mysql + PHP) stack powers many servers in the Internet today. For a LAMP  server, PostgreSQL could be the first alternative to Mysql. Similar to PHP + MySQL,  PHP + PostgreSQL too can be easily used in to host data services. Here are the steps to do it.

1. If you already don’t have Apache + PHP + PostgreSQL download them from the following locations. Apache – http://httpd.apache.org/download.cgi, PHP – http://php.net and PostgreSQL – http://www.postgresql.org/download/
2. You have to enable the PHP pdo_pgsql, pdo and pgsql plugins. Read here for the instructions to setup these libraries. (For an example: if you are windows you have to set the system ‘PATH’ variable to the <postgresql_installed_dir>/bin directory.
3. If you already don’t have WSF/PHP, download and install it according to the guidelines provided in wsf/php installation guide.NOTE: You can check pdo_pgsql and wsf/php has properly installed with the help of phpinfo() function.
4. Now lets start with creating a sample Database table. For this example I created a database called ‘workshop’, schema called ‘workshop’ and inside there the table ‘Employee’ with the following schema.
   

   Column Name	Column Type
   employId	integer
   name	character varying
   email	character varying
   jobTitle	character varying
   project	character varying

   Note: You can use phpPgAdmin (web based) or pgAdmin III to create tables from GUI

5. Then you can write a small php script to expose the data in the above table as a web service.

   <?php
   
   //Including the Data Services library
   require_once("wso2/DataServices/DataService.php");
   
   // Including the connection information (i.e. PGSQUL USERNAME
   // and PGSQL_PASSWORD) for my PGSQL Connection
   require_once("constants.php");
   
   // database configurations
   $config = array(
   		"db" => "pgsql",
   		"username" => PGSQL_USERNAME,
   		"password" => PGSQL_PASSWORD,
   		"dbname" => "workshop",
   		"dbhost" => "localhost"
   		);
   
   $output_format = array(
                       "resultElement" => "employees",
                       "rowElement" => "employee",
                       "elements" => array(
   			    "id" => "employeeId",
                               "name" => "name",
                               "email" => "email",
                               "jobTitle" => "jobTitle",
                               "project" => "project"));
   
   $sql = "SELECT * FROM workshop.Employees";
   
   $get_employees_op = array("outputFormat" => $output_format, "sql" => $sql);
   
   $get_employees_url = array("HTTPMethod" => "GET", "RESTLocation" => "employees");
   
   // list of operations
   $operations = array(
                   "getEmployees" => $get_employees_op,
                   );
   
   // list of rest url mappping (operation => url)
   $restmap = array(
                   "getEmployees" => $get_employees_url,
                   );
   
   // creating DSService and reply
   $service = new DataService(array(
            "config" => $config,
            "operations" => $operations, "RESTMapping"=>$restmap));
   $service->reply();

   ?>

6. We just wrote a PostgreSQL Data Services that provides its service as both REST and SOAP form. To deploy this service, We just need to copy this in to the web root directory. And the web URL for the script will be the endpoint to the web service.
7. We can test the service either by calling its SOAP interface, which we may need to write a small SOAP client or by calling its REST interface, which only need a GET request from the browser. Say my script name is “my_dataservice.php” and I’ve put it in the web root directory, then the URL to call the REST interface of the service is

   http://localhost/my_dataservice.php/employees

WSDL Generation for PostgreSQL Data Service
You can get the WSDL for the service from the URL formed adding the suffix “?wsdl” (or “?wsdl2″ to wsdl v2.0) to the service URL,

http://localhost/my_dataservice.php?wsdl

Here all the schema data types are shown as xsd:anyType, which may not be the behavior that you want. In fact you can provide the schema data types to the fields from the code itself. Lets change the $outputFormat variable to provide the schema information as well using the following code snip.

$output_format = array(
                    "resultElement" => "employees",
                    "rowElement" => "employee",
                    "elements" => array(
			    "id" => array("column" => "employeeId",
		    			  "xsdType" => "xsd:int"),
			    "name" => array("column" => "name",
		    			  "xsdType" => "xsd:string"),
			    "email" => array("column" => "email",
		    			  "xsdType" => "xsd:string"),
			    "jobTitle" => array("column" => "jobTitle",
		    			  "xsdType" => "xsd:string"),
			    "project" => array("column" => "project",
		    			  "xsdType" => "xsd:string")));

Note that you provide the xsd type for each field explicitly. In fact this change is not needed for mysql pdo extension since it allows identifying field types programatically. Since this feature is not available in all the other pdo drivers, we have to explicitly give xsd type information for them.

If you wan to provide data services with SQLite or MSSQL, You can check my other posts on MSSQL(Microsoft SQL) Data Services In PHP and Data Services with SQLite in PHP.

Database plays a big role in any day-today application. It is a major component from accounting, web portal, CMS, SaaS applications, search engines to all enterprise applications. In traditional MVC(Model-View-Controller) applications we talk about the Model component which represent the database.. And in 3-tier architectural pattern it is the ‘Data layer’ which represent the database and provide data to the ‘Logic Layer’ as per request.

As SOA evolves, database is becoming just one part of the ‘Data Layer’ or the ‘Model’ component. There are many data sources, data providers which are mostly deployed as web services which you can ask for data as you do with traditional databases. This gives you the advantages of the use of web services like interoperability, security and reliability and other WS-* support. And it helps you to get rid of the headache of installing different drivers for different databases and most importantly it removes the tight binding of your application to the database. So with adaption of SOA, the ‘Data Layer’ has been replaced by the ‘Service Consuming Layer’.

The story of the ‘Data Provider’ also changed with the SOA adaption. The new data sources are designed with the SOA in mind. And the legacy systems are wrapped by a service layer to make it more easier to consume. We use the term ‘Data Services’ for the data sources deployed as web services.

There are many public data services available as ‘REST’ which is a lighter way of providing services. The other way is the use of WS-* features like WS-Security, WS-Reliable Messaging to deploy the data services which are mostly adopted by the enterprise.

Today there are many tools around, that helps you to develop data services from existing databases. WSO2 provides an open source data service framework that allows you to build data services from simple xml configuration files without the need of writing a single line of code. And WSF/PHP also packed with a data services library that allow you to write a simple PHP script to build a data service.

PHP is one of the favorite language to write database back-ended web applications. Specially considering the fact that there are thousands of servers powered by the LAMP (Linux  + Apache + MySQL +PHP) stack, PHP data service library would comes useful to build around a web service around these existing data sources and make them SOA-aware.

In a RESTful design, choose of URLs for resources are really important. The URL uniquely represents a resource. Service consumers can change some parts in the URL to access different other resources. So it is clear that the URL consists of some constant parts which describe the resource group or catalog in general and some variable parts which have different and unique values for different resources.

As an example look at the following URL patterns

• students/{name} – The constant ’students’ represent the students group in general and the variable ‘name’ is used to identify each student individually.
• students/{name}/marks/{subject} – The constants ’students’ and ‘marks’ shows that this resource is a marks of some students, The two variables ‘name’ and ’subjects’ addresses which student and marks of which subject is presented in the URL.

You can have a look at some of the uses of such mappings from RESTful School demo.

WSF/PHP allows you to create RESTful Web Services and further more RESTful Data Services in PHP.

In a RESTful Data Service you expose a database query as a web service. There you can write a prepared statement and feed arguments for the statement through the variable parameters of the URL. For an example take the RESTfulSchool Demo Code.

To retrieve a particular student, we can use the following prepared statement and the URL pattern ( This URL Pattern+  HTTP ‘GET’ method is matched to execute this query. )

$sql = "SELECT * FROM Students where StudentName = ?"

$get_students_with_name_url = array("HTTPMethod" => "GET",
                 "RESTLocation" => "students/{name}");

So you can execute this prepared statement with the subject name ‘John’ using the following URL.

http://labs.wso2.org/wsf/php/solutions/RESTFulSchool/school_service.php/students/john

If your service is not exposing the database directly, then you have to choose the general web service API rather than the data service specific API. In there you will be able to write your business logic for publishing student information in a PHP function and expose it as a web service.

In such a cas,e your function is taking an argument which of type WSMessage. This structure hold an XML that contains values for all the variable parameters as in the users request URL. For an example for above REST Mapping, we can expect the following XML.

<getSubject>
    <name>Chemistry</name>
</getSubject>

And your function to expose as a service, would be look like this,

function getSubject($input) {
    /* retrieve the subject name from the
       $input xml using simple xml */

    $input_xml = new SimpleXMLElement($input->str);

    $subject_name = $input->name;

    /* write the logic to retrieve subject information
      for the $subject_name */

    ....
}

Here is a simple categorization of the WSF/PHP samples. You can access all the wsf/php samples from http://labs.wso2.org/wsf/php/solutions/samples/index.html.

If you have downloaded the WSF/PHP binaries or souce code package you can find all these samples, inside the ’samples’ directory

Yesterday I wrote a how-to on doing MSSQL DataService in PHP. It was just about installing necessary PDO drivers and setting the following configurations about your Database.

// database configurations
$config = array(
		"db" => "mssql",
		"username" => DB_USERNAME
		"password" => DB_PASSWORD
		"dbname" => DB_NAME,
		"dbhost" => DB_SERVER_NAME
		);

But when it comes to SQLite you don’t have such thing called database server, name, username or password. It is just a file in your computer. In this kind of situation you can directly use the PDO DSN (standards for Data Source Name) string to configure your database.

// giving my sqlite database file information
$config = array(
    "dsn" => "sqlite2:mydbfile.db");

So this is all specific to SQLite. For the rest of the code, you don’t need to worry about the underlying database you use. Please read the blog about the PHP DataServices 2 Minutes Introduction for short guide on PHP data services in general.

PHP in most percentage is used with MySQL Server in LAMP or WAMP stacks. But there may be situations where PHP uses MSSQL databases in back-end. And PHP scripts can be used to make Web services exposing MSSQL Databases. That is enabled by the  Data Services capability in WSF/PHP 2.0.0.

Here are the steps to get the PHP Data Services working with MSSQL in windows. You can safelty skip the starting point if you have already have the necessory software installed.

• Download Apache Web Server, PHP and MSSQL server. (You can download the MSSQL server 2005 expess edition from here.
• Enable the php_mssql, php_pdo and php_pdo_mssql extension from the php.ini.
• If you have MSSQL server 2005, the default client DLL (ntwdblib.dll) use to connect to MSSQL server will not work. So you need to download a newer version of this dll. You can easily find this googling the name of the dll. (The dll version worked for me is 2000.80.194.0). Copy this dll to following directories.
  • <your_windows_drive>/windows/system32
  • <your_apache_installation>/apache2/bin
• To Demo purpose I have created a MSSQL database called “RESTFulSchool” and and table called ‘Subjects” with the following schema. If you already have a MSSQL database you can just continue with that.
  

  Column Name	Data Type
  subjectID	int
  subjectName	text
  subjectTeacher	text

  And make sure you fill some data into the table. You can get this done easily with MSSQL Server Management Studio downloadable from http://www.microsoft.com/sql/editions/express/default.mspx

• So here is my code to the Data Service. Note in the config variable I have given the information about my MSSQL connection.

  <?php
  
  //Including the Data Services library
  require_once("wso2/DataServices/DataService.php");
  
  //Including the connection information for my MSSQL Connection
  require_once("constants.php");
  
  // database configurations
  $config = array(
  		"db" => "mssql",
  		"username" => MSSQL_USERNAME
  		"password" => MSSQL_PASSWORD
  		"dbname" => "RESTfulSchool",
  		"dbhost" => MSSSQL_SERVER_NAME
  		);
  
  $output_format = array(
                      "resultElement" => "subjects",
                      "rowElement" => "subject",
                      "elements" => array(
                              "name" => "subjectName",
                              "teacher" => "subjectTeacher"));
  
  $sql = "SELECT subjectName, subjectTeacher FROM Subjects";
  
  $get_subjects_op = array("outputFormat" => $output_format, "sql" => $sql);
  
  $get_subjects_url = array("HTTPMethod" => "GET", "RESTLocation" => "subjects");
  
  // list of operations
  $operations = array(
                  "getSubjects" => $get_subjects_op,
                  );
  
  // list of rest url mappping (operation => url)
  $restmap = array(
                  "getSubjects" => $get_subjects_url,
                  );
  
  // creating DSService and reply
  $service = new DataService(array("config" => $config, "operations" => $operations, "RESTMapping"=>$restmap));
  $service->reply();
  
  ?>

• You just wrote a SOAP+REST Data Service to expose your MSSQL Data as a Web Service. Now Put this script (say school_service.php) in to the Apache web directory and access it using a browser with the following url.

  http://localhost//school_service.php/subjects

  If you have done every thing right, you should see a nice xml showing your data. Note that here we used REST interface to test our service. You don’t need to do any thing special to expose it as SOAP. Its SOAP endpoint would be simply

  http://localhost//school_service.php

Get the WSDL Generation working with MSSQL
If you try to retrieve the WSDL for the above service using this URL

http://localhost//school_service.php?wsdl

You will get an error message saying that the meta data retrieval is not supported for this particular driver (MSSQL Driver). For the time being, in order to get rid of this warning you have to disable showing warnings in your PHP programs. You can get it done by editing the php.ini with this entry

error_reporting = E_ALL & ~E_NOTICE & ~E_WARNING

But even with this fix, the schema types of the name and teacher entries will be shown as “xsd:anyType”. If you want to have a more specific type associated with them, Just change the $output_format variable to the following.

$output_format = array(
                    "resultElement" => "subjects",
                    "rowElement" => "subject",
		    "elements" => array(
			"name" => array("column" => "subjectName", "xsdType" => "xsd:string"),
			"teacher" => array("column" => "subjectTeacher", "xsdType" => "xsd:string")));

Here instead of giving a string to ‘teacher’ and ‘name’ entries, we give an array containing both column name and the the custom xsd type we want to appear in the WSDL.  Note that this fix is needed only for databases that doesn’t provide the meta data interface through the PHP PDO layer. For database like mysql wsdl generation works straight away.

PHP Web Services Demo Site contains a set of nice tools that help development of web services in PHP.

• WSDL2PHP tool – This allow you to generate PHP code for your WSDL. Note that this need your wsdl to be in a URL that it can access.
• PHP2WSDL tool – Here you can paste your annotated PHP code and get the WSDL (both version 1.1 and 2.0) generated.  You can find the annotation syntax in here.
• DBS2PHP tool – WSO2 has Data Services library implemented in both Java and PHP. In Java Data Services you give the configuration via an XML (in .dbs extension). Whereas in PHP you give the configuration via a simple PHP code which use arrays to feed the configuration parameters. If you are more familiar in writing XML than PHP, you can first write the XML and then convert it to PHP using DBS2PHP tool.

When you are developing Web Service for CRUD (Create, Read, Update, Delete) operations you may find it is easy to implement it as RESTful service. In this Demo on RESTful CRUD Service You can have an idea how you develop such a service with WSO2 WSF/PHP.

Here we take a scenario of submitting applications (say for a school).

In RESTful world we map a resource to a unique URL. In this demo, application is a resource. We use the URL “application/{id}” to represent a particular application with the id {id}.

You can  use  HTTP verb + Resource URL touples to manipulate the resource with CRUD operations.  Here is how it is done in this particular demonstration.

Go for the wsf/php demo sitefor the live demo of this service. Visit the demo service source code to see how easy to implement it with WSF/PHP Data Services library.

I wrote a similar blog on Data Services last week to demonstrate how you design the mapping of url to different resources in a RESTful Service.