PHP and OData: we change from bicycles to technology from Microsoft

Today it’s fashionable to make APIs and many of us have already implemented some kind of API in PHP. One of the tasks of the REST API is to render datasets so that they can ultimately be displayed in tabular form. For this, among other things, it is necessary to solve the following tasks:

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

I don’t know about you, but I see that this is often done by bicycle solutions. The tasks are not complicated at first sight, but in order to solve them qualitatively, you have to spend a lot of time developing, documentation and explaining to your colleagues how your invention works. I will talk about how you can implement these tasks very technologically using OData.



I understand that many who are friends with LAMP are alien to the influences of 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 is not modest. It also says that it is an open protocol and that with its help you can make a RESTful API in a simple and standard way. Further you will see that part of it is true and that there is power in it.

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

What is OData useful for?

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

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

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

But why then OData is not used in PHP projects

Or almost never used. Indeed, I carefully googled on the topic of PHP + OData, but found not so much. And what I found made me rack my brains - how to get it to 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 focus on the implementation of the server side.

What managed to find. There is an excellent project from Microsoft, with open source, it is posted right on the github: odataphpprod . In fact, this is a framework for creating a server part in PHP, which runs on the OData protocol.

I tried to use it and immediately ran into two obstacles. Firstly, the project did not work on Linux, although the readme of the project says that both systems are supported - Windows and Linux. In order for the project to work on Linux, it was necessary to correct in the sources the places where the files are connected - there is confusion in the paths and registers. Secondly, to implement a simple list return, I had to read not the shortest instruction .

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

I continued the search, but did not find any other worthy implementations, except forka of the above project. Fork called podata. The readme of this project says that PHP developers are poor because they don’t have a good toolkit for working with OData, but the library undertakes to mitigate this misunderstanding and bring the benefits of OData to the masses. I tried to use this fork, things went much better with it. The project made money on Linux without any extra effort. There is also a simple step-by-step dock on how to use the project with the Zend Framework.

I tried using POData for my needs, but in this case I ran into minor troubles. In particular, it was not possible to make POData friends with the OpenUI5 grid until it made several minor edits to POData. Yes, and I had to write a lot of code myself. Moreover, 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 friendly with OData:

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

My attempt to lower the entry threshold and example OData service

As I said 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. So I put this code in a separate SimplePOData library . This is an implementation of IQueryProvider (for relational SQL databases such as MySQL) and IService.

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

Step 0. Install the necessary libraries

Create a directory www/odata-examplefor the first project with OData. Create a file composer.jsonwith the following contents:

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

A slight lyrical digression. The POData / POData project on the github is, by and large, a good working project. But judging by the github, active work on it ended two years ago. To use the project for my needs, I made some minor improvements, sent pull requests, but the author of the project has not answered yet, even when I tried to contact him through a social network. I hope he gets in touch. But so far, I have registered my fork Qeti / POData in SimplePOData, where I have registered my changes. For convenience, I added everything to Packagist. If the author of POData gets in touch and actively accepts the changes, then 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

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

<IfModule mod_rewrite.c>RewriteEngineonRewriteRule    (\.svc.*) index.php
</IfModule>

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

Step 2. Implementation IHTTPRequest

We must implement the interface IHTTPRequest. This class will use POData in order to get URL parameters. Implementation may vary depending on the framework used. We will go the simple way and pass $ _GET to the constructor. Create a file RequestAdapter.phpwith the following contents:

<?phpusePOData\Common\ODataConstants;
usePOData\OperationContext\HTTPRequestMethod;
usePOData\OperationContext\IHTTPRequest;
classRequestAdapterimplementsIHTTPRequest{
    protected $request;
    publicfunction__construct($request){
        $this->request = $request;
    }
    /**
     * get the raw incoming url
     *
     * @return string RequestURI called by User with the value of QueryString
     */publicfunctiongetRawUrl(){
        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.
     */publicfunctiongetRequestHeader($key){
        if (isset($this->request[$key])) {
            return $headers = $this->request[$key];
        }
        returnnull;
    }
    /**
     * 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[]
     */publicfunctiongetQueryParameters(){
        $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
     */publicfunctiongetMethod(){
        returnnew HTTPRequestMethod('GET');
    }
}

Step 3. Implementation IOperationContext

This class is even simpler, it should 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 OutgoingResponsethat is already implemented in POData so you can not understand this class at this stage. Create a file OperationContextAdapter.php:

<?phpusePOData\OperationContext\IHTTPRequest;
usePOData\OperationContext\IOperationContext;
usePOData\OperationContext\Web\OutgoingResponse;
classOperationContextAdapterimplementsIOperationContext{
    /**
     * @var RequestAdapter;
     */protected $request;
    protected $response;
    /**
     * @param yii\base\Request $request
     */publicfunction__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
     */publicfunctionoutgoingResponse(){
        return$this->response;
    }
    /**
     * Gets the Web request context for the request being received.
     *
     * @return IHTTPRequest reference of IncomingRequest object
     */publicfunctionincomingRequest(){
        return$this->request;
    }
}

Step 4. Implementation IQueryProvider

The objective of this class is to select data from your source. As I understand it from the documentation for the framework, the developers propose to implement in this class not only functionality for data extraction, but a 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 match the name of the services in the URLs. Therefore, SimplePOData implements the necessary methods, but a restriction is imposed. The name of the service in the URL DocumentHasProductwill be converted to the name of the table document_has_product. If this does not suit you, then you can override the method getTableName().

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

<?phpuseqeti\SimplePOData\BaseQueryProvider;
classQueryProviderextendsBaseQueryProvider{
    publicfunction__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
     */protectedfunctionqueryAll($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
     */protectedfunctionqueryScalar($sql, $parameters = null){
        $statement = $this->db->prepare($sql);
        $statement->execute($parameters);
        $data = $statement->fetchAll(PDO::FETCH_COLUMN);
        if ($data) {
            return $data[0];
        }
        returnnull;
    }
}

Step 5. Classes that describe data

With the basic things we figured out, we proceed to the description of specific data. For example, create a product table in the database:

CREATETABLE product (
  idINTNOTNULL AUTO_INCREMENT PRIMARY KEY,
  added_at TIMESTAMPDEFAULTNOW(),
  nameVARCHAR(250),
  weight DECIMAL(10, 4),
  code VARCHAR(45)
);

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

<?phpnamespacemodels;
useqeti\SimplePOData\EntityTrait;
classProduct{
    // This trait contains method for fields mapping (between database table and this class)useEntityTrait;
    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 trait EntityTrait, which implements the mapping of class property names and base fields. In this implementation, the names of the database fields exactly match the name of the class properties. Those who are not satisfied with this for some reason can simply make another implementation of the static method fromRecord().

Step 6. Metadata

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

Create a file models/MetadataProvider.php:

<?phpnamespacemodels;
usePOData\Providers\Metadata\Type\EdmPrimitiveType;
usePOData\Providers\Metadata\SimpleMetadataProvider;
classMetadataProvider{
    const MetaNamespace = "Data";
    /**
     * Description of service
     *
     * @return IMetadataProvider
     */publicstaticfunctioncreate(){
        $metadata = new SimpleMetadataProvider('Data', self::MetaNamespace);
        $metadata->addResourceSet('Products', self::createProductEntityType($metadata));
        return $metadata;
    }
    /**
     * Describtion of Products
     */privatestaticfunctioncreateProductEntityType(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 ( localhost/odata-example/odata.svc/Products) collection and what fields it has.
addKeyProperty()defines a key field. This field is used to filter when you select a specific record by querying localhost/odata-example/odata.svc/Products(1). addPrimitiveProperty()defines an ordinary field.

Step 7. index.php

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

<?phpusePOData\OperationContext\ServiceHost;
useqeti\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 did you get as a result

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

odata.svc
Returns a list of collections

<servicexmlns: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><collectionhref="Products"><atom:title>Products</atom:title></collection></workspace></service>

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

<edmx:Edmxxmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx"Version="1.0"><edmx:DataServicesxmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx"m:DataServiceVersion="1.0"><Schemaxmlns: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"><EntityTypeName="Products"><Key><PropertyRefName="id"/></Key><PropertyName="id"Type="Edm.Int32"Nullable="false"/><PropertyName="added_at"Type="Edm.DateTime"Nullable="true"/><PropertyName="name"Type="Edm.String"Nullable="true"/><PropertyName="weight"Type="Edm.Decimal"Nullable="true"/><PropertyName="code"Type="Edm.String"Nullable="true"/></EntityType><EntityContainerName="Data"m:IsDefaultEntityContainer="true"><EntitySetName="Products"EntityType="Data.Products"/></EntityContainer></Schema></edmx:DataServices></edmx:Edmx>

odata.svc/Productsreturns all records from the Products collection. If this is a large collection, then you should not call it like this - 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, it is worth paying special attention to the fact that the conditions can be complicated, using brackets, and, or operators, and much more. When parsing the condition, the necessary checks are performed and the possibility of introducing SQL injections is excluded.

An example of organizing pagination and selecting only the 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 specified, $inlinecount=allpagesyou get in the response in the odata.count field the number of records in the selection, as if the LIMIT operator were 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 - number of records.

For those who do not want to make copy-paste

If you are interested in using OData in your PHP project, you want to make an example, but do not want to copy, you can do it even easier. The above example is on the github - follow the instructions from readme.

Bonus

And if you want a live confirmation that now you can program without getting anything ready to implement a front-end, then take a look at this example . You can sort by columns, use pagination - everything works. Here's how it would look:

Summary

What good

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

What are the cons

• So far, POData only supports GET requests.
• At the moment, not enough examples of use.

Personally, after weighing the pros and cons, I am inclined to believe that technology should be used in my projects. If something is missing in POData (for example, INSERT, UPDATE, DELETE operations), then adding the missing one is easy. I hope that you will be able to benefit from the material for yourself.

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