The power of OData - Part 4 - Creating an OData service

[marc_augustin2]

Content of this series:

The power of OData - Part 1 - Introduction

The power of OData - Part 2 - Setting up the Environment

The power of OData - Part 3 - Creating a data Model

      The power of OData - Part 4 - Creating an OData service (this part)

The power of OData - Part 5 - Consuming the service with SAPUI5

Hey everyone and welcome to the next part of my series. Today, we are going to create the needed source code to create an OData feed from our database. It took me a little longer to write this part because I had to make some changes to one of the tools we will use in this part and pushed them into the repository. Until my pull-request is accepted, we have to use my repository link; I will update this post if the code is integrated into the official branch.

So far, we should have a working Apache Webserver running with PHP 5.5 and a MySQL server with our “customer” database and some sample data in it. In our website’s root directory, we should have a “customer_api” directory with the standard OData PHP Producer library inside. If you need more information about setting up the environment, begin reading the other posts of this series as mentioned above.

First of all, we won’t code much. There exist some libraries and scripts that will create the needed source code for us based on our database design. Furthermore, the documentation on the classes, interfaces and methods is very good. As the created filed will suit our need here, I won’t go much into detail but explain what kind of file is needed for.

Let’s start by getting the required packages. Download the following libraries:

• OData MySql Connector for PHP (https://odatamysqlphpconnect.codeplex.com/SourceControl/network/forks/Aug/ODataMySqlConnector?branch...) – Make sure to download the latest one. As soon as the pull-request is accepted, I’ll update the link
• Doctrine DBAL (http://www.doctrine-project.org/downloads/DoctrineDBAL-2.3.4-full.tar.gz)

The first download will create the scripts for us based on our database design. Doctrine here is needed in order to access and analyse the database. In general, it is a nice framework for creating abstract versions of a database in PHP. With the help of doctrine, we can have PHP classes that refer to our database. The benefit is that we work with objects here instead of database queries. (So far about Doctrine, it’s only a very very rough description).

Now extract both archives. Within your Doctrine-extract, you should find a Doctrine folder:

Copy this folder inside your connectors' folder, so that its structure looks like this:

1. That was the configuration needed so far. Now let’s have fun :smile:

Open up your XAMPP control panel and start your MySQL server.

Fire up a console and navigate to your Connector’s folder and execute the following command:

php MySQLConnector.php /db=customer /u=root /pw= /srvc=Customer /h=127.0.0.1

Here are the options:

• /db= name of the database you want to use
• /u= username of the database user. I take root in this clean XAMPP installation
• /pw= password of the database user. For root in a XAMPP installation the password is not set
• /srvc= the name of the service we want to create, we can change this later again
• /h= the host of the database system. Localhost would also be possible but I read that there might be some issues with this.

The system will ask you of you want to modify the EDMX file. Type “n” here and hit return. It should now create the needed script files.

We don’t have to care much about this EDMX file. It is generated by Doctrine and describes our database and all included tables in a xml format.

Within your Connector’s folder navigate now to the OutputFiles folder lying under ODataConnectorForMySQL:

Within that, there is a folder called Customers

As I said the script to create the service with a capital C. Now copy this folder and go to your services folder under C:\xampp\htdocs\customer_api\:

Delete anything in this folder and insert your service folder:

Finally copy the service.config.xml file from your Customer folder to the services folder. The final folder structure should look like this:

Now let’s see if it works. Start Apache from your XAMPP control panel (MySQL should be started because of the above configuration), start a browser and open this URL: http://localhost/customer_api/Customer.svc

If you get some XML listing showing your tables, everything went well.

Now open http://localhost/customer_api/Customer.svc/$metadata

You will get a description of your tables and columns.

To access, let’s say all customers, go here: http://localhost/customer_api/Customer.svc/customers

As I said, we can change the service name now, if we want. Open the xml file you just copied to the services folder:

The content here says how the service is called (here “Customer.svc”), where it is located and what URL should be called.

If you look into the Customer folder, there are 5 files that were previously created. The EDMX file is not relevant here and could also be deleted. Important of those 5 files are the following 3:

• CustomerDataService.php
• CustomerMetadata.php
• CustomerQueryProvider.php

In the first file, a reference to the others is done and objects are instantiated. So if any of those classes change or you create everything manually, here is the mapping part. Within the metadata file, a class representation of our database structure is created. This includes key fields as well as all relations between the tables. Within the QueryProvider file, the queries itself are created. This is the place where we also could define custom queries or add some Join statements for example.

More about those files can be found in the documentation of the OData PHP producer. I think that the MySQLConnector here is a good tool for starting with custom services.

In the next and last part, I will consume the services from an UI5 application The power of OData - Part 5 - Consuming the service with SAPUI5.