PHP and OData: we are moving from bicycles to technology from Microsoft

Today it is fashionable to do API and many of us have already implemented some API in PHP. One of the tasks of the REST API is to give away data sets in order to ultimately display them in a table. To do this, among other things, you have to solve such problems:

• validate the request
• filter the data
• sort the data
• request and give not all columns, but only some,
• implement pagination.

I do not know about you, but I see that this is often done by bicycle decisions. The tasks are not complicated in appearance, but in order to solve them qualitatively, you have to spend a lot of time on developing, documenting and explaining to colleagues how your invention works. I will talk about how you can accomplish these tasks very technologically with OData.



I understand that many people who are friends with LAMP are strangers to the enemy fronts of all kinds of Microsoft and Windows. But let's first see what OData is.
')

What is OData

As written on the official website ,

OData - the best way to REST

Here is a short definition that does not differ by modesty. It also says that it is an open protocol and that you can use it to make the RESTful API simple and standard. Then you will see that some of this is true and that there is power in it.

It is noteworthy that this standard is promoted not only by Microsoft. The standard is approved by OASIS . The standard is universally used in Microsoft products and in many large systems of other companies.

How is OData useful?

If you have implemented data exchange between the client and server parts of your application using the OData protocol, then those who will use the protocol just need to give a link to your service. Colleagues who will use your service, just read the standard URL format to access your collections.

In addition, you have the opportunity to save time on the development of the frontend. To do this, you can get ready-made libraries that can work with OData. For example, Kendo UI or free OpenUI5 from SAP.

This allows for roughly an hour to create a good technological framework for the application, where it remains to add business-specific logic to your task.

But why then OData is not used in PHP projects

Or almost not used. Indeed, I diligently googled on PHP + OData, but found not so much. And what I found made me break my head - how to make it work.

From the point of view of implementation, two important components can be distinguished: the client (requests data) and the server (gives data). Next, we will discuss the implementation of the server part.

What was found. There is an excellent project from Microsoft, with open source, it is laid out right on the githaba: odataphpprod . In fact, this is a framework for creating a server part for PHP, which works using the OData protocol.

I tried to use it and immediately ran into two obstacles. First, the project did not work on Linux, although the readme of the project says that both systems are supported - Windows and Linux. To make the project work on Linux, I had to edit the source files where the files are connected - there is a mess in the paths and registers. Secondly, in order to realize a simple return of the list, I had to read not the shortest instruction .

In addition, over the past 3 years there has not been a single commit in this project.

I continued the search, but did not find other worthy implementations, except for the fork of the aforementioned project. Fork is called POData . The readme of this project says that PHP developers are poor, because they do not have good tools for working with OData, but the library is taken to level this misunderstanding and bring the advantages of OData to the masses. I tried to use this fork, it went much better with it. The project earned on Linux without any extra effort. There is also a step by step simple dock, how to use the project together with the Zend Framework.

I tried to use POData for my needs, but in this case I ran into minor troubles. In particular, it was not possible to make friends with POData with a grid from OpenUI5, until I made several minor edits to POData. And a lot of code I had to write myself. And most of this code is quite reusable and could be part of the framework.

In general, I see two reasons why PHP developers are still not friends with OData:

• entry threshold - add a lot of code, documentation is not very simple, few step-by-step examples,
• rawish tools.

My attempt to lower the threshold of entry and the example of an OData service

As I already mentioned above, a lot of code that has to be implemented to work with POData is quite reusable and will most likely be transferred from project to project without changes. Therefore, I moved this code to a separate library, SimplePOData . These are the implementation of IQueryProvider (for relational SQL databases such as MySQL) and IService.

Next is an example of building a server part in pure PHP without using any frameworks, except perhaps POData itself.

Step 0. Install the necessary libraries

Create a www/odata-example directory for the first project with OData. Create a file composer.json with the following contents:

 { "require": { "qeti/simple-podata": ">=0.9.1" } } 

A small lyrical digression. The POData / POData project on a githaba is, by and large, a good working draft. But, judging by the githab data, active work on it ended two years ago. To use the project for my own needs, I made minor improvements, sent pull requests, but the author of the project hasn’t yet responded, even when I tried to contact him through a social network. I hope he gets in touch. But for now, I have registered my own fork, Qeti / POData, into SimplePOData, where I committed my changes to it. For convenience, everything is added to Packagist. If the author of POData contacts and actively accepts the changes, there will be no sense in another fork and I will switch to his fork.

So, to install the necessary packages, run from the console:

 composer install 

Step 1. Work with URL

Metadata description will be available at localhost/odata-example/odata.svc$metadata localhost/odata-example/odata.svc$metadata .
So, we need to make all requests to our OData service ( localhost/odata-example/odata.svc localhost/odata-example/odata.svc ) went to index.php . To do this, in the root of the project, place the .htaccess file with the following contents:

 <IfModule mod_rewrite.c> RewriteEngine on RewriteRule (\.svc.*) index.php </IfModule> 

POData will take care of the rest of the work on parsing the URL.

Step 2. Implement the IHTTPRequest

We need to implement the IHTTPRequest interface. This class will use POData to get URL parameters. The implementation may vary depending on the framework used. We will go in the simple way and we will transfer in the designer $ _GET. Create a file RequestAdapter.php with the following contents:

 <?php use POData\Common\ODataConstants; use POData\OperationContext\HTTPRequestMethod; use POData\OperationContext\IHTTPRequest; class RequestAdapter implements IHTTPRequest { protected $request; public function __construct($request) { $this->request = $request; } /** * get the raw incoming url * * @return string RequestURI called by User with the value of QueryString */ public function getRawUrl() { return $_SERVER['REQUEST_SCHEME'] . '://' . $_SERVER['HTTP_HOST'] . '/' . $_SERVER['REQUEST_URI']; } /** * get the specific request headers * * @param string $key The header name * * @return string|null value of the header, NULL if header is absent. */ public function getRequestHeader($key) { if (isset($this->request[$key])) { return $headers = $this->request[$key]; } return null; } /** * Returns the Query String Parameters (QSPs) as an array of KEY-VALUE pairs. If a QSP appears twice * it will have two entries in this array * * @return array[] */ public function getQueryParameters() { $data = []; if (is_array($this->request)) { foreach ($this->request as $key => $value) { $data[] = [$key => $value]; } } return $data; } /** * Get the HTTP method/verb of the HTTP Request * * @return HTTPRequestMethod */ public function getMethod() { return new HTTPRequestMethod('GET'); } } 

Step 3. Implement the IOperationContext

This class is even simpler; it has to implement only two methods — return request and response objects. The request object is an instance of the above class. The response object is an instance of OutgoingResponse , which is already implemented in POData so you can not deal with this class at this stage. Create an OperationContextAdapter.php file:

 <?php use POData\OperationContext\IHTTPRequest; use POData\OperationContext\IOperationContext; use POData\OperationContext\Web\OutgoingResponse; class OperationContextAdapter implements IOperationContext { /** * @var RequestAdapter; */ protected $request; protected $response; /** * @param yii\base\Request $request */ public function __construct($request) { $this->request = new RequestAdapter($request); $this->response = new OutgoingResponse(); } /** * Gets the Web request context for the request being sent. * * @return OutgoingResponse reference of OutgoingResponse object */ public function outgoingResponse() { return $this->response; } /** * Gets the Web request context for the request being received. * * @return IHTTPRequest reference of IncomingRequest object */ public function incomingRequest() { return $this->request; } } 

Step 4. Implementing IQueryProvider

The task of this class is to select data from your source. As far as I understand from the documentation for the framework, the developers propose to implement in this class not only the functionality for extracting data, but the mapping of the data source with the names of tables and columns in the database. In principle, in most cases, the names of the tables will clearly correspond to the name of the services in the URLs. Therefore, the necessary methods are implemented in SimplePOData, but a restriction is imposed. The service name in the DocumentHasProduct URL will be converted to the name of the document_has_product table. If this does not suit you, you can override the getTableName() method.

All you have to do is implement a method for getting a set of rows and a method for getting one value from your data source. In our example, we will work with PDO. Create a QueryProvider.php file:

 <?php use qeti\SimplePOData\BaseQueryProvider; class QueryProvider extends BaseQueryProvider { public function __construct(\PDO $db){ parent::__construct($db); } /** * Get associated array with rows * @param string $sql SQL query * @param array $parameters Parameters for SQL query * @return mixed[]|null */ protected function queryAll($sql, $parameters = null) { $statement = $this->db->prepare($sql); $statement->execute($parameters); return $statement->fetchAll(PDO::FETCH_ASSOC); } /** * Get one value * @param string $sql SQL query * @param array $parameters Parameters for SQL query * @return mixed|null */ protected function queryScalar($sql, $parameters = null) { $statement = $this->db->prepare($sql); $statement->execute($parameters); $data = $statement->fetchAll(PDO::FETCH_COLUMN); if ($data) { return $data[0]; } return null; } } 

Step 5. Classes describing data

We dealt with the basic things, we proceed to the description of specific data. For example, create a product table in the database:

 CREATE TABLE product ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, added_at TIMESTAMP DEFAULT NOW(), name VARCHAR(250), weight DECIMAL(10, 4), code VARCHAR(45) ); 

And describe the class for this table, create a file models/Product.php :

 <?php namespace models; use qeti\SimplePOData\EntityTrait; class Product { // This trait contains method for fields mapping (between database table and this class) use EntityTrait; public $id; public $added_at; public $name; public $weight; public $code; } 

As you can see, all that is in this class is the enumeration of the fields of the product table and the connection of the EntityTrait trait, which implements the mapping of the names of the properties of the class and the fields of the base. In this implementation, the names of the database fields exactly match the name of the class properties. Those who are not satisfied for some reason, can simply make another implementation of the static fromRecord() method.

Step 6. Metadata

Now we need to describe our data so that the service understands what kind of data it is and how it is related to each other.

Create a file models/MetadataProvider.php :

 <?php namespace models; use POData\Providers\Metadata\Type\EdmPrimitiveType; use POData\Providers\Metadata\SimpleMetadataProvider; class MetadataProvider { const MetaNamespace = "Data"; /** * Description of service * * @return IMetadataProvider */ public static function create() { $metadata = new SimpleMetadataProvider('Data', self::MetaNamespace); $metadata->addResourceSet('Products', self::createProductEntityType($metadata)); return $metadata; } /** * Describtion of Products */ private static function createProductEntityType(SimpleMetadataProvider $metadata) { $et = $metadata->addEntityType(new \ReflectionClass('\models\Product'), 'Products', self::MetaNamespace); $metadata->addKeyProperty($et, 'id', EdmPrimitiveType::INT32); $metadata->addPrimitiveProperty($et, 'added_at', EdmPrimitiveType::DATETIME); $metadata->addPrimitiveProperty($et, 'name', EdmPrimitiveType::STRING); $metadata->addPrimitiveProperty($et, 'weight', EdmPrimitiveType::DECIMAL); $metadata->addPrimitiveProperty($et, 'code', EdmPrimitiveType::STRING); return $et; } } 

Here we described that we have a Products collection ( localhost/odata-example/odata.svc/Products localhost/odata-example/odata.svc/Products ) and what are the fields in it.
addKeyProperty() defines a key field. This field is used to filter when you select a specific entry by requesting localhost/odata-example/odata.svc/Products(1) localhost/odata-example/odata.svc/Products(1) . addPrimitiveProperty() defines a regular field.

Step 7. index.php

Well that's all. It remains to create index.php , in which you need to connect the created classes, create a connection to the database and ask POData to process the request.

 <?php use POData\OperationContext\ServiceHost; use qeti\SimplePOData\DataService; require(__DIR__ . '/vendor/autoload.php'); require(__DIR__ . '/OperationContextAdapter.php'); require(__DIR__ . '/RequestAdapter.php'); require(__DIR__ . '/QueryProvider.php'); require(__DIR__ . '/models/MetadataProvider.php'); require(__DIR__ . '/models/Product.php'); // DB Connection $dsn = 'mysql:dbname=yourdbname;host=127.0.0.1'; $user = 'username'; $password = 'password; $db = new \PDO($dsn, $user, $password); // Realisation of QueryProvider $db->queryProviderClassName = '\\QueryProvider'; // Controller $op = new OperationContextAdapter($_GET); $host = new ServiceHost($op); $host->setServiceUri("/odata.svc/"); $service = new DataService($db, \models\MetadataProvider::create()); $service->setHost($host); $service->handleRequest(); $odataResponse = $op->outgoingResponse(); // Headers for response foreach ($odataResponse->getHeaders() as $headerName => $headerValue) { if (!is_null($headerValue)) { header($headerName . ': ' . $headerValue); } } // Body of response echo $odataResponse->getStream(); 

What is the result?

As a result of the above, we get a service that can handle such requests.

odata.svc
Returns a list of collections.

 <service xmlns:atom="http://www.w3.org/2005/Atom" xmlns:app="http://www.w3.org/2007/app" xmlns="http://www.w3.org/2007/app"xml:base="http://localhost:80/OData-base-example/odata.svc"> <workspace> <atom:title>Default</atom:title> <collection href="Products"> <atom:title>Products</atom:title> </collection> </workspace> </service> 

odata.svc/$metadata returns a description of the entities.

 <edmx:Edmx xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx" Version="1.0"> <edmx:DataServices xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx"m:DataServiceVersion="1.0"> <Schema xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"xmlns="http://schemas.microsoft.com/ado/2007/05/edm" Namespace="Data"> <EntityType Name="Products"> <Key> <PropertyRef Name="id"/> </Key> <Property Name="id" Type="Edm.Int32" Nullable="false"/> <Property Name="added_at" Type="Edm.DateTime" Nullable="true"/> <Property Name="name" Type="Edm.String" Nullable="true"/> <Property Name="weight" Type="Edm.Decimal" Nullable="true"/> <Property Name="code" Type="Edm.String" Nullable="true"/> </EntityType> <EntityContainer Name="Data" m:IsDefaultEntityContainer="true"> <EntitySet Name="Products" EntityType="Data.Products"/> </EntityContainer> </Schema> </edmx:DataServices> </edmx:Edmx> 

odata.svc/Products returns all records from the Products collection. If this is a large collection, then you should not call it that way - it is better to limit the selection. For example, if you call odata.svc/Products?&$format=json&$filter=id le 5&$orderby=id desc ,
then the following will happen. Records with id no more than 5 will be selected, data will be sorted by id in reverse order. The result will be given in json format:

 {"odata.metadata":"http://localhost:80/odata-example/odata.svc/$metadata#Products","value": [{"id":5,"added_at":"2006-07-14T00:00:00","name":"Kon","weight":"14.1700","code":"Kon-59"}, {"id":4,"added_at":"2014-03-16T00:00:00","name":"Kon","weight":"2.4100","code":"Kon-89"}, {"id":3,"added_at":"2009-07-23T00:00:00","name":"Bicycle","weight":"4.3100","code":"Bic-18"}, {"id":2,"added_at":"2000-03-25T00:00:00","name":"Samokat","weight":"8.0200","code":"Sam-96"}, {"id":1,"added_at":"2006-10-22T00:00:00","name":"Kolyaska","weight":"10.1300","code":"Kol-97"}]} 

As for $filter , here it is worth paying special attention that conditions can be complicated, use brackets, and, or operators and much more. When parsing the conditions, the necessary checks are performed and the possibility of introducing SQL injections is excluded.

Example of organizing pagination and selecting only specified columns: odata.svc/Products?$skip=10&$top=5&$format=json&$select=id,name&$inlinecount=allpages

 {"odata.metadata":"http://localhost:80/odata-example/odata.svc/$metadata#Products", "odata.count":"1002", "value": [{"id":11,"name":"Motoroller"}, {"id":12,"name":"Kolyaska"}, {"id":13,"name":"Kedi"}, {"id":14,"name":"Roliki"}, {"id":15,"name":"Doska"}]} 

When specifying $inlinecount=allpages you receive in the response in the odata.count field the number of records in the sample, as if the LIMIT operator was not used in the SQL query.

odata.svc/Products(5)?$format=json
Returns product data with id = 5

 { "odata.metadata":"http://localhost:80/odata-example/odata.svc/$metadata#Products/@Element", "id":1, "added_at":"2006-10-22T00:00:00", "name":"Kolyaska", "weight":"10.1300", "code":"Kol-97" } 

odata.svc/Products/$count - the number of records.

Those who do not want to make save-pastes

If you are interested in using OData in your PHP project, you want to make an example, but you don’t want to copy-paste, you can do even easier. The above example is on a githaba - follow the instructions in the readme.

Bonus

And if you want a live confirmation that now you can program anything without getting a ready implementation of the frontend, take a look at this example . You can sort by columns, use pagination - everything works. Here is what it will look like:

Summary

What good

• It turns out that the working implementation of the server part of OData is in PHP.
• It allows you to save a lot of power when developing a frontend.
• Ready implementation of primary validation, filtering and data sorting, pagination.
• Protection against SQL injection.

What are the cons

• So far, POData has support for GET requests only.
• At the moment there are not enough examples of use.

Personally, having weighed the pros and cons, I tend to think that the technology should be used in my projects. If something is missing in POData (for example, INSERT, UPDATE, DELETE operations), then it’s easy to add the missing one. I hope that you will be able to benefit from the material.

I would like to get more feedback. Will you work with OData in your projects? What do you think of the above?