Dimuthu’s Blog

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.

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 */

    ....
}

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 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.

In RESTful paradigm we give a piece of data ( or in other word ‘Resource’) a unique URL. And in order to manipulate data we use HTTP verbs POST/PUT (create, update), GET (read), DELETE (delete). For an example
take the scenario of manipulating Students data in a high school. Here is how each operation is mapped to a http request (URL + HTTP verb)

With the addition of all these HTTP verbs WSO2 WSF/PHP 2.0.0 become a great tool for RESTful developers. Specially with the introducing Data Services library it was so easy to make your database a REST service. I m thinking of preparing a series of application to demonstrate the power of WSF/PHP with all these new features.

This demo -RESTful School- shows how you map a URL to a peice of data. Here we use only the http “GET” method (which is the most to used in practicle data service).

Here is some description of the operations you find in there. Just check the source code for RESTful School demo to see how this is done in code level.

subjects

SELECT subjectName, subjectTeacher FROM Subjects

subjects/{name}

SELECT subjectName, subjectTeacher FROM Subjects where SubjectName = ?

students

SELECT * FROM Students

students/{name}

SELECT subjectName, marks FROM Marks m, Subjects s ".
        " where m.studentId = ? and m.subjectID = s.subjectId

SELECT * FROM Students where StudentName = ?

students/{student}/marks/{subject}

SELECT marks FROM Marks, Subjects, Students where StudentName = ?".
        " and SubjectName = ? and Marks.subjectId = Subjects.subjectId".
        " and Marks.studentID = Students.StudentId;

Earlier I wrote a blog about how to make your wordpress blog a web service using the WSF/PHP Data Services library. I will expand that post to demonstrate the use of WS-Security features with WSF/PHP.

This time it is a Tag Search service for my wordpress blog. Check the ‘Tag Search’ Data Services Demo from http://ws.dimuthu.org/. The only difference is here you are authenticated before accessing the service using the username tokens as specified in WS-Security.

Just look at the WSSecurity constructor in the Data Service Demo Code. You can observe 4 new parameters passed in to it. (In addition to the “config” and “operations” options)

• policy – This is where you specify the policy governed by the service.  Here you can either use the WS-Policy compliant policy file or just a simple PHP array that contain the required security token informations.

  $sec_array = array("useUsernameToken" => TRUE);
  $policy = new WSPolicy(array("security"=>$sec_array));

• securityToken: You specify the user parameters like how you handle the authentication and the encoding type in this option.

  $security_token = new WSSecurityToken(array("passwordCallback" => "password_callback_function",
                                         "passwordType" => "Digest"));
  
  /* callback function
   * @param string $username username of the client request
   * @return string $password password for that username
   */
  function password_callback_function($username) {
      // In the real word I should authenticate users from database.
      // for this demo I have a simple if-else block
  
      if($username == "visitor") {
          return "visitor123";
      }
  
      return "notavistor";
  }

  Note that here you specify a callback function to the security token parameter. Inside this function you retrieve the password for the user (mostly from the database) and return. WSF/PHP will authenticate the user from these information.

• useWSA : You need to set this option in order to generate the WS-Addressing parameters (like action) for your WSDL. WS-Addressing is required to run web services with WS-Security in WSF/PHP.
• actions: You should provide a map of action to service operations in order to get the WS Addressing information generated with your WSDL.

  $actions = array("http://ws.dimuthu.org/blog/getPosts" => "getPosts");

  Just have a look at how these information are rendered in the generated WSDL, http://ws.dimuthu.org/blog/WordpressTagSearchService.php?wsdl. (Note the wsaw:action attribute in the messages inside the portType element.

After you deploy the service, it is very easy to generate a client with the WSDL. If you write clients in PHP you can use the wsdl2php tool shipping with WSF/PHP.  The code for my demo client can be found in http://ws.dimuthu.org/source.php?src=tag.search.client. (There I have hard coded the username and password just for the demo purpose)

We have been waiting for a while to do the WSF/PHP 2.0.0 release with a bundle of new features. We took time to test all the features and to make sure they are working smoothly. And it is heavily tested for the interoperability specially with .NET for WS-Security and WS-Reliable Messaging scenarios. And the newly added Data Services library will be a great tool for the PHP community to bring their LAMP/WAMP applications to the SOA platform. WSF/PHP 2.0.0 is not just a SOAP library. It is ready for the RESTful applications as well with the improved support for HTTP verbs.

Here is the list of new features introduced in this release

• Added PKCS12 Keystore Support
• Added Secure Conversation Support
• Added Replay Detection Support
• Contract First Web Services support for MTOM
• SWA ( Soap With Attachments ) Support added
• MTOM Attachment caching support added
• HTTP Chunking support added
• REST API Improved to support HTTP verbs GET,DELETE,PUT and POST
• New PHP Data Services Solution
• WS-RM 1.1 added

Have look at the WSF/PHP 2.0.0 Official Release Note for the complete feature list

We use the term DataServices for exposing a Database as a WebService. PHP DataServices library make this task easy by providing a very simple API to the developers.

The API is designed to let the service developer to provide the database configurations, input message format, output message format and the SQL query information. So reading and parsing the input SOAP message, building the response SOAP message from the queried data, serving the WSDL and providing the Database independent layer (with the help of PDO extensions) are done by the Dataserivce library.

Configurations

You give your database configurations in this format.

// database configuraitons
$config = array(
      "db" => "mysql", //your sql
      "username" => DB_USERNAME, //your username
      "password" => DB_PASSWORD, //your password
      "dbname" => "ds", //your database name
      "dbhost" => "localhost"); //your database host

Input Format

You have to declare the names and the types of the content of your input message. This is needed to generate the WSDL and invoke the database query with parameters.

// input format array(param_name => SQL_TYPE)
$inputFormat = array("customerNumber" => "INT");

Output Format

You can customize your response SOAP message by providing the names of the wrapper elements  for both the message and the rows.

// output format, plese check the API from http://wso2.org/wiki/display/wsfphp/API+for+Data+Services+Revised
$outputFormat = array("resultElement" => "Orders", //the name of the wrapper element of the message
                      "rowElement" => "Order", //the name of the wrapper element of each row
                      "elements" => array( "order-number" => "OrderNumber",
                                           "order-date" => "OrderDate",
                                           "status" => "status"));

Create the Query

The query in the PHP dataserivces term is not just the SQL query that you are familiar in accessing a database, It contains the input and output formats in addition to the actual SQL query to invoke.

// sql statment to execute
$sql="select o.OrderNumber, o.OrderDate, o.status from Customers c, Orders o where c.customerNumber=o.customerNumber and c.customerNumber=?";

// operations are consist of inputFormat (optional), outputFormat(required), sql(sql), input_mapping(optional)
$operations = array("customerOrders" =>array("inputFormat" => $inputFormat, "outputFormat" => $outputFormat, "sql" => $sql));

DataService object

Finally you create the DataService object with the provided configurations and the query information.

require_once("wso2/DataServices/DataService.php");

$my_data_service = new DataService(array("config" => $config,"operations" => $operations));

$my_data_service->reply();

If you have a shared hosting environment you will find it is not straight forward to install WSO2 WSF/PHP + PHP DataServices Library since you have very limited authority on your environment. Here in this post, I’m explaining the steps I followed in setting up my custom PHP, WSF/PHP Extension and DataServices library for http://ws.dimuthu.org (This is a shared hosting environment from dreamhost based on LAMP stack) which I’m using to expose my blog as a WebService.

Prerequisites:

1. You should be able to run PHP using mod_fastcgi (not using mod_php) in Apache. That allows you to configure your own php environment.
2. The server configurations managed by the hosting service should allow you to handle .php extension from your own  CGI executable (here it is php.cgi), If not, you can still try with setting some other extension (say .phq or .qhq, anything not .php) to be handled by your php-cgi. Anyway if your hosting service allow you to run CGI then most probably you have this permission.
3. You should have the access to the shell (using SSH) and should be able to use development libraries(libxml, libxsl, libopenssl) + build tools (make)
4. There can be some other restrictions that your hosting service has put on you, that I have not experienced with my hosting service. So please check your hosting service support and documentation for possibility of building your own PHP environment.

Install PHP:

I have extracted out most of the steps from dreamhost documentation on custom php.ini.

1. Download PHP and compile. You should at least enable –enable-fastcgi –enable-force-cgi-redirect in the configuration.

   ./configure --enable-fastcgi --enable-force-cgi-redirect --with-xsl --with-openssl --prefix=`pwd`/dist
   make
   make install

   Note: from here on, php source directory is referred as <php_source_directory>
   I have installed the php to <php_source_directory>/dist since we mostly don’t have permission to install it in to default location. (/usr/local). From here on I will be referring php installation directory as <php_install_dir>

2. create a directory called ‘cgi-bin’ to your web document root directory(<document_root_directory>) and copy the <php_source_directory>/dist/bin/php-cgi in to that. (before php version 5.2.1 there is no php-cgi generated, you can just copy the php to the cgi-bin directory). And rename the php-cgi to php.cgi.

   cp <php_install_direcory>/bin/php-cgi <document_root_directory>/cgi-bin/php.cgi

3. copy <php_source_direcory/php.ini-dist to the cgi-bin directory and rename it as php.ini, this can be used to configure your php environment.

   cp <php_source_directory/php.ini-dist <document_root_directory>/cgi-bin/php.ini

4. Create .htaccess file in the cgi-bin directory to allow the access to php-cgi and php.ini files.

   cat << EOF ><document_root_directory>/cgi-bin/.htaccess
   Options +ExecCGI -Indexes +FollowSymLinks
   <FilesMatch "php(.?)\\.(cgi|ini)$">
   Order Deny,Allow
   Deny from All
   Allow from env=REDIRECT_STATUS
   </FilesMatch>
   
   EOF

5. Create .htaccess file in the root directory redirect all the requests for .php extensions to go through our php-cgi.

   cat << EOF > <document_root_directory>/.htaccess
   AddHandler php-cgi .php
   Action php-cgi /cgi-bin/php.cgi
   EOF

   If your hosting service doesn’t allow to handle .php just rename the end of the first line to some other extension.

6. Check the necessary permissions levels in files.

   chmod 644 <document_root_directory>/.htaccess
   chmod 755 <document_root_directory>/cgi-bin
   chmod 644 <document_root_directory>/cgi-bin/.htaccess
   chmod 644 <document_root_directory>/cgi-bin/php.ini
   chmod 755 <document_root_directory>/cgi-bin/php.cgi

That’s it. Now your .php files should be run on your own php environment. Just to check whether it is working or not create a phpinfo() file.

cat <<EOF > phpinfo.php
<?php
phpinfo();
?>
EOF

And go to the http://yourdoman.com/phpinfo.php and verify that it is your custom php environment .

Installing WSF/PHP

1. Download WSF/PHP latest version and extract it to any of your preferred directory(<wsf_php_source_directory>).
2. Set the PATH environment variable to search for your custom php installation first.

export PATH=<php_install_dir>/bin:$PATH

3. From the same shell go to the <wsf_php_source_directory> and compile it.

   ./configure
   make
   make install

4. Now check where you have installed wsf/php. it is in fact the extension dir shown by the php-config command.

   php-config --extension-dir

   I will refer this directory as <php_extension_directory>

5. Open the <document_root_directory>/cgi-bin/php.ini an set the extension directory, enable wsf extension and set the include path to the wsf scripts directory.cat <<EOF >> <wsf_php_source_directory>/cgi-bin/php.ini

   cat <<EOF >> <wsf_php_source_directory>/cgi-bin/php.ini
   extension_dir=<php_extension_directory>
   extension=wsf.so
   include_path=".:<wsf_php_source_directory>/scripts"
   
   EOF

   If you found that you can only put relative directories in your php.ini (that happens when you don’t even have the read access to your root directory ‘/’), follow the workaround mentioned in here, http://phpwebservices.blogspot.com/2008/08/installing-wsfphp-in-third-party.html

That’s all. Just check the phpinfo (created earlier section) whether your configurations and the wsf extension are loaded correctly. In order to test the functionality copy the wsf/php samples to the web document root and access them from the browser. Go to each sample and verify they function as expected.

cp /samples <wsf_php_source_directory>/ -R

And go to http://yourdomain.com/samples and click on each samples. If this not working most probably your localhost to ip mapping is wrong, most probably this problem is there in your shared host. Just go to the samples directory and change the endpoint to the domain name in place of ‘localhost’ in each client and try it again.

Installing PHP DataServices:

1. Download and unpack PHP DataServices. <php_data_services_directory>
2. Update the include_path entry in php.ini to DataServices libraries directory as well. So the <wsf_php_source_directory>/cgi-bin/php.ini  file now has the following entry.

   include_path=".:<wsf_php_source_directory>/scripts:<php_data_services_directory>/lib"

That’ all for the DataService installation. Please read <php_data_services_directory>/README to run the samples. Anyway if you are sure you did followed these simple steps correctly, go on with your DataService  development. Specially if you have wordpress database, you may start straight away with the making wordpress a DataService.