Dimuthu’s Blog

With WSF/PHP Data Service library, you can convert a SQL query to a Data Service very easily in few steps.

1. Decide your SQL query first, For the query you may require some input parameters, and you have to decide what should be returned by the query, Say your query is

   $sql_query = "SELECT name, age, email FROM users where country = ?";

   Then the “country” is our input parameter and the name, age, email are our return values.

2. Define the input Format. For the above query it will be something like,

   $inputFormat = array("country" => "INT");

3. Define the output format. We are giving the name “Users” for the out most wrapper element and the name “user” for the wrapper element of each user. Here is how we define it,

   $outputFormat = array("resultElement" => "users",
                   "rowElement" => "user",
                   "elements" => array(
                               "name" => "name",
                               "age" => "age",
                               "email" => "email"));

   For this output format, our expected result payload would be like,

   <users>
     <user>
        <name>xxx</name>
        <age>23</age>
        <email>xxx@xxx.xx</email>
     </user>
     <user>
        <name>yyy</name>
        <age>23</age>
        <email>yyy@yyy.yy</email>
     </user>
     ....
   </users>

4. Define your operation using the sql query, input and output formats.

   $operations = array("getUsersByCountry" => array(
                                 "inputFormat" => $inputFormat,
                                 "sql" => $sql_query,
                                 "outputFormat" => $outputFormat));

5. Define your database configuration in an array like this,

   $config = array(
       "db" => "mysql", // your db engine
       "username" => "myname", // name & password for the db server
       "password" => "mypasswd",
       "dbname" => "db", // the db
       "dbhost" => "localhost");

6. Create a DataService instance using the database configuration and the operations we just created. And call DataServices reply method.

   $ds_service = new DataService(array(
                  "config" => $config,
                  "operations" => $operations));
   
   $ds_service->reply();

That is it. You just exposed your query as a web service. The PHP script URL will be the endpoint URL for the web service.

WSO2 Carbon is getting ready to come out very soon. You want to know what exactly WSO2 Carbon is?. Just check this samisa’s blog about “Carbon In Pictures”.

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.

WS-SecurityPolicy specification defines standards for defining security policies for your web service. WSF/PHP allows you to declare your security policies according to these standards.

You can take one of following approaches to associate policies to your web service or client.

• PHP Array to represent your policies
• Policy file compliant with WS-Security Policy.
• Declaring policies inline with the WSDL.

Declaring Policies with a PHP Array

This is a WSF/PHP specific API to declare policies for a web service. You don’t need to learn WS-Security Policy to write policies with this approach. You can set whether you want to use encryption, signing or usernameToken in a PHP array and create a WSPolicy object using it.

// here is the security array to declare your policies in simple manner
$sec_array = array("encrypt" => TRUE,
 "algorithmSuite" => "Basic256Rsa15",
 "securityTokenReference" => "IssuerSerial");

// creating WSPolicy instance using the policy array
$policy = new WSPolicy(array("security"=> $sec_array));

You can use this policy object to create a service along with a WSSecurityToken which contain the user parameters like the server private key and the client certificate.

$sec_token = new WSSecurityToken(array(
 "privateKey" => $server_pvt_key,
 "receiverCertificate" => $client_pub_key));

$svr = new WSService(array("actions" => $actions,
 "operations" => $operations,
 "policy" => $policy,
 "securityToken" => $sec_token)); // here is the policy object you just created

$svr->reply();

You can invoke this service just by writing a simple web service client. There also you need to provide the policies declared in the service, so the client can build his request to validate with server policies. You will be using a similar WSPolicy object to set these policies at the client side too, as show in the below code segment.

 $sec_token = new WSSecurityToken(array(
   "privateKey" => $pvt_key,
   "receiverCertificate" => $rec_cert));

 $client = new WSClient(array("useWSA" => TRUE,
    "policy" => $policy, /* the policy object */
    "securityToken" => $sec_token));

 $resMessage = $client->request($reqMessage);

Declaring Policies with a Policy File

You can set your policies in the server or client side using a policy file compliant with WS-Security Policy specification. You have to take this approach if your policy requirements are too complicated, like you want to sign only some parts of the message or you want to encrypt some soap headers.

Similar to the above method, here too you will use the WSPolicy object to set your policies. But unlike the above where you give the policies as a PHP array , here you can just give the policy file as an argument to the WSPolicy constructor.

// creating the WSPolicy instance from a policy file
$policy_xml = file_get_contents("policy.xml");
$policy = new WSPolicy($policy_xml);

Here is an example of a complete policy file written according to the WS-Security Policy standards. And you can find a quick guide on WS-Security Policy from the article Understanding WS-Security Policy Language written by Nandana, a key leader of Apache Rampart project.

Declaring Policies inline in a WSDL

We use WSDL to describe our web services. WSDL has the information about the service endpoint, the transport protocols (e.g. http), messaging protocols (e.g. SOAP) and the message schemas and many others about the service. You can attach your policies inside a WSDL.

Here is an example of a WSDL with inline policies. The difference in this approach is you can set your policies separately for each messages or each operations or each endpoints of your service. The following segment of a WSDL shows how you refer to different policies which are declared in the early part of the WSDL.

     <wsdl:binding name="CalendarSOAP12Binding" type="ns1:CalendarPortType">
       <!-- Endpoint policies are declared here.
          these are common to all messages transferring
          through this protocols (i.e. SOAP12, http)-->
        <wsp:PolicyReference URI="#transport_binding_policy"/>
        <soap12:binding transport="http://schemas.xmlsoap.org/soap/http" style="document"/>
        <wsdl:operation name="login">
           <soap12:operation soapAction="urn:login" style="document"/>
           <wsdl:input>
              <!-- policy specific to the 'login' operation -->
              <wsp:PolicyReference URI="#username_token_policy"/>
              <soap12:body use="literal"/>
           </wsdl:input>
           <wsdl:output>
              <soap12:body use="literal"/>
           </wsdl:output>
         </wsdl:operation>
         <wsdl:operation name="register">
            <!-- no specific policies are set for the 'register' operation
            <soap12:operation soapAction="urn:register" style="document"/>
            <wsdl:input>
              <soap12:body use="literal"/>
            </wsdl:input>
            <wsdl:output>
               <soap12:body use="literal"/>
            </wsdl:output>
         </wsdl:operation>
           ....
       </wsdl:binding>

This is the binding section of a WSDL where we bind messaging protocol and transport protocols with a service endpoint. Here we have “login” and “register” operations. Note that we are referring to “transport_binding_policy” from the parent level of each operation elements. That means these policies are common to all the operation in that binding. And inside the “login” operation we are referring to “username_token_policy”, so in order to invoke this operation, you have to send username token headers. And “register” doesn’t require any operation specific policies allowing users to register without any prior authentications.

You can select any of the above mentioned approach to define policies of your web service or to invoke a web service that support WS-Policy. If your policy requirements are simple, it will be easy to use the array based approach. If your policy requirements are complex or you have a good understanding of WS-Policy and WS-Security Policy you can rely on the policy file based approach or defining policy inline with WSDL. And the former 2 methods will give you a nice separation of the logic code and security configurations. The selection is yours:)

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

We use ‘GROUP BY’ SQL construct to query the data with aggregating some rows according to a field. For an example say if your blog database store your blogs in a table call ‘Blog’ and it has ‘Date’ as a field. If so

SELECT count(*) FROM Blog GROUP BY Date

will give you a set of numbers that represent the number of blog you posted each day.

SELECT Date, count(*) FROM Blog GROUP BY Date

will give you a map of ‘date’ to ‘number of blog posted for that date’ without much trouble. 

Anyway the problem is most of the databases of blogs don’t just keep the ‘date’ for a blog, rather it keep both ‘date and time’ (say in a field called ‘Time’). But you still want to group by date. You may use the MySQL ‘DATE’ function to convert the ‘Date and Time value’ to just ‘Date’ and use it in GROUP BY statement.

SELECT Date, count(*) FROM Blog GROUP BY DATE(Time)

If you take Drupal for a blog database, it save the time of the blog entry as a unix timestamp. So you have to derive the Date from the timestamp using the infamous FROM_UNIXTIME mysql function,

In Drupal the database table name to store blog is ‘node’ and the field name to store the create time is ‘created’ . So your query to get statistics of Drupal would be something like this.

SELECT DATE( FROM_UNIXTIME(`created`)), count(*)
FROM `node`
GROUP BY DATE( FROM_UNIXTIME(`created`))

Rather than converting the timestamp to SQL Date format, You can convert Date to timestamp and your sql statement may look little mathematical.

SELECT ROUND( (
UNIX_TIMESTAMP( NOW( ) ) - `created` ) / ( 24 *60 *60 )
), count( * )
FROM `node`
GROUP BY ROUND( (
UNIX_TIMESTAMP( NOW( ) ) - `created` ) / ( 24 *60 *60 )
)

In fact the expression “UNIX_TIMESTAMP( NOW( ) ) – `created` ) / ( 24 *60 *60 )” derives a number that represent the age of the post in days.

So this way you can derive statistics of your data with the use of ‘GROUP BY” construct. The ability to write complex queries in SQL syntax like this is really useful, specially when you access a remote database through a web services (i.e. Data Services) or using database drivers, you have to minimize the number of sql queries to execute as minimum as possible.

Here are some of the other aggregate function that you may use with GROUP BY, http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

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.