flat3/lodata

OData v4.01 Producer for Laravel

v1.6.0 2021-04-12 16:50 UTC

README

GitHub Workflow Status Packagist Version License Code Climate maintainability Code Climate coverage

Contents

  1. Introduction
    1. What is OData?
    2. Why OData for Laravel?
  2. Basic usage
    1. Getting started
    2. Authentication
    3. Authorization
    4. Discovery
    5. Applications
  3. Drivers
    1. Database
    2. Redis
    3. Filesystems
    4. CSV Files
  4. Advanced usage
    1. Annotations
    2. Generated properties
    3. Asynchronous Requests
    4. Filter expressions
    5. Alternative keys
    6. Operations
    7. Function composition
    8. Laravel Octane
  5. Internals
    1. Class documentation
    2. Transactions
    3. Streaming JSON
    4. Drivers
    5. Types
  6. Specification compliance
  7. License

Introduction

What is OData?

The OData Protocol is an application-level protocol for interacting with data via RESTful interfaces. The protocol supports the description of data models and the editing and querying of data according to those models. It provides facilities for:

  • Metadata: a machine-readable description of the data model exposed by a particular service.
  • Data: sets of data entities and the relationships between them.
  • Querying: requesting that the service perform a set of filtering and other transformations to its data, then return the results.
  • Editing: creating, updating, and deleting data.
  • Operations: invoking custom logic
  • Vocabularies: attaching custom semantics

OData consumer support exists in a wide variety of applications, particularly those from Microsoft, SAP and SalesForce.

If you're new to OData it is recommended to refer to the description of the Data Model as the terminology used here is OData-specific.

Why OData for Laravel?

Many Laravel applications are used in an agency/customer context that have these kinds of requirements:

  • Our customer wants to access our data using applications such as Excel, PowerBI and Tableau to generate reports and dashboards, but doesn't like the complexity of logging in and performing manual, error-prone CSV/XLSX downloads to fetch their data
  • Our customer requires authorized third party developers to query our application's database, possibly modifying the data and running internal functions and we want to manage how these processes work in Laravel
  • Our customer has internal stakeholders (non-expert data users) that need access to different sets of data that we hold, based on their role, but do not need or desire administrative access to our application

Lodata is easy to integrate into existing Laravel projects, provides an out-of-the-box discoverable API for third-party developers and a straightforward data workflow for business users.

Basic usage

Getting started

Step 1: Install Lodata into your Laravel app using Composer

composer require flat3/lodata

Now start your app. The OData API endpoint will now be available at: http://127.0.0.1:8000/odata/ (or whichever URL prefix your application normally runs on). Accessing that endpoint in an API client such as Postman will show you the Service Document.

Step 2: Discover your first model

Edit a service provider such as app/Providers/AppServiceProvider.php to add the following to the boot() method: (using your own Model instead of Example)

\Lodata::discoverEloquentModel(\App\Models\Example::class)

You can now access http://127.0.0.1:8000/odata/Example and see the data in your database stored by that model.

Step 3: Load your data into an application

Lodata has specific support for Excel and PowerBI service discovery. Use one of the following URLs in a browser to prompt Windows to open the feed in the relevant application:

  • To load the Example model in Excel use http://127.0.0.1:8000/odata/_lodata/Example.odc
  • For PowerBI use http://127.0.0.1:8000/odata/_lodata/odata.pbids.

Both Excel and PowerBI can now refresh the data source themselves using the Refresh buttons in those interfaces.

Any other consumer service requesting your "OData Endpoint" should accept the service document at http://127.0.0.1:8000/odata/

To make changes from this point, you should install Lodata's configuration into your Laravel application:

php artisan vendor:publish --provider="Flat3\Lodata\ServiceProvider" --tag="config"

Authentication

Out of the box Lodata does not wrap the API in authentication. The only authentication type in the OData standard is HTTP Basic, but many consumers support additional types. To add basic authentication to all Lodata endpoints modify config/lodata.php to include auth.basic in the array of middleware.

Authorization

Lodata supports authorization via Laravel gates. Each API request will be checked via a gate tagged lodata. The gate will receive the standard user argument, and a Flat3\Lodata\Helper\Gate object. This object contains the type of request being made, the Lodata object it is being made against, the Lodata 'transaction' and in the case of an operation the arguments array will be provided. This is all the information needed for a gate policy to decide whether to allow the request.

At install time, Lodata runs in a readonly mode. To enable updates, change the value of the readonly property in config/lodata.php.

Discovery

Lodata can 'discover' Eloquent models, and the relationships between the models. This metadata is presented to the client, so it can understand how the entity sets are related and navigate between them.

Discovery is performed first using DBAL to introspect the database table, then Eloquent casts are used for further type specification. During requests, the Eloquent model getter/setter functions are used to refer to the properties, so any additional field processing being performed by the model will be preserved.

To discover a model the Lodata facade that exists in the root namespace can be used. For example to discover two models:

\Lodata::discoverEloquentModel(\App\Models\Flight::class);
\Lodata::discoverEloquentModel(\App\Models\Passenger::class);

If model Flight has a method passengers that returns a relationship to Passenger such as hasOne, hasMany, hasManyThrough, this can be discovered by Lodata as a navigation property on the Flights entity set. Note that similar to Laravel itself, Lodata typically refers to 'entity types' in the singular form and 'entity sets' in the plural form. An entity set and its related entity set must both be defined through discovery before a relationship can be created.

\Lodata::getEntitySet('Flights')
  ->discoverRelationship('passengers')

A navigation property now exists in the Flight entity set for Passengers. This enables the client to navigate by using the navigation property in a URL similar to http://127.0.0.1:8000/odata/Flights(1)/passengers to choose the flight with ID 1, and to get the passengers related to this flight. This navigation property can also be used in $expand requests.

If Lodata is able to determine the relationship cardinality it will be represented in the service metadata document.

Applications

Using Lodata with Excel

Excel 2019 (and some earlier versions) support OData Feeds natively using Power Query.

As well as being able to create a connection in Excel using the UI, Lodata provides an easy to add an "Open in Excel" button in your application. The URL provided for this button will be for a specific entity set, for example for the Flights entity set:

\Lodata::getOdcUrl('Flights')

Using Lodata with PowerBI

Microsoft PowerBI supports the autoloading of a data source via a PBIDS URL. This can be used in a "Open in PowerBI" feature button. Unlike Excel which works on a single entity set, this button provides PowerBI with access to the whole model:

\Lodata::getPbidsUrl()

Using Lodata with PowerApps

Microsoft PowerApps also support importing OData Feeds. Using a dataflow the data exposed by Lodata can be imported into the Common Data Service. When creating an OData data source the UI will request the 'OData Endpoint', which can be programatically generated and presented by your app using:

\Lodata::getEndpoint()

Drivers

A Lodata 'driver' represents any storage system that could implement one or more of the \Flat3\Lodata\Interfaces\EntitySet interfaces including QueryInterface, ReadInterface, UpdateInterface, DeleteInterface, and CreateInterface. In addition to the query interface the driver may implement SearchInterface and FilterInterface to support $search and $filter, and other system query parameters can be supported through ExpandInterface, TokenPaginationInterface, PaginationInterface and OrderByInterface. Implementation of any of these interfaces is optional, and Lodata will detect support and return a 'Not Implemented' exception to a client trying to use an interface that is not available.

A wide variety of different services can support these interfaces in whatever way makes sense to that service. Services could be other databases, NoSQL services, other REST APIs or simple on-disk text files.

A list of provided drivers is below.

Database

In addition to the Eloquent model driver described above, Lodata can discover database tables directly. This can be used to expose tables through OData that are not used in Eloquent sets, such as through tables for many-to-many relationships. This is required for applications that treat OData Feeds as relational database models such as PowerBI and Tableau. It can also be used to expose databases that are not even used in the Laravel application, or to use Lodata as simply an OData endpoint for an existing database.

SQL database tables can be discovered using this syntax:

$passengerType = \Flat3\Lodata\EntityType::factory('passenger');
$passengerSet = \Flat3\Lodata\Drivers\SQLEntitySet::factory('passengers', $passengerType)
    ->setTable('passengers')
    ->discoverProperties();
\Lodata::add($passengerSet);

First an empty entity type is defined with the name passenger, and used to generate the entity set. Then a table passengers is assigned. When discoverProperties is run, passengerType will be filled with field types discovered by the entity set.

Redis

Lodata supports read, update, create, delete and query options on an attached Redis database.

The RedisEntityType provides an entity type starting point, with the key property set to a String key. The RedisEntitySet can then be attached to expose the database.

$entityType = new RedisEntityType('passenger');
$entityType->addDeclaredProperty('name', Type::string());
Lodata::add(new RedisEntitySet('passengers', $entityType));

The driver expects all values in the database to be encoded with PHP's serialize(). To modify this behaviour, the RedisEntitySet can be subclassed and the serialize and unserialize methods can be overridden.

The specific Redis database connection can be configured by calling setConnectionName() on the entity set.

Filesystems

Lodata supports read, update, create, delete and query options on Laravel filesystems.

The FilesystemEntityType provides an entity type starting point, with the key property set to a String key named path. The FilesystemEntitySet can then be attached to expose the filesystem. The entity set supports a setDisk method to set the filesystem to use.

$entitySet = new FilesystemEntitySet('files', new FilesystemEntityType());
$entitySet->setDisk('default');
Lodata::add($entitySet);

The filesystem entity type supports a Edm.Stream property named content that can be requested with $select which will encode the file into the body of the response. The URL to retrieve the file will also be provided in the body as the metadata content@mediaReadLink.

CSV files

Lodata can expose CSV files through the OData API using the CSVEntitySet driver and the CSVEntityType entity type. This driver is configured with a Laravel disk and a file path, and supports query and read operations including sorting and pagination.

The CSVEntityType uses the key property 'offset' referring to the CSV row number. The entity type is further configured with the same field order as found in the CSV file.

$entityType = new CSVEntityType('entry');
$entityType->addDeclaredProperty('name', Type::string());
$entityType->addDeclaredProperty('birthday', Type::datetimeoffset());

$entitySet = new CSVEntitySet('csv', $entityType);
$entitySet->setDisk('default');
$entitySet->setFilePath('example.csv');
Lodata::add($entitySet);

Advanced usage

Developer API documentation is automatically generated at https://flat3.github.io/lodata

Annotations

OData allows the creation of annotations on the schema. Annotations are classes that extend \Flat3\Lodata\Annotation and are added to the model with Lodata::add($annotation), or entity set types with EntitySet::addAnnotation($annotation), or entity type properties with Property::addAnnotation($annotation). Examples are in the \Flat3\Lodata\Annotation namespace.

Generated properties

As well as the "static" data retrieved from the database, Lodata can add properties to an entity that are generated at runtime. Lodata provides the \Flat3\Lodata\GeneratedProperty class which must be extended and provided with an invoke() method which will receive the \Flat3\Lodata\Entity currently being generated. The generated property must return an instance of a primitive type. The resulting instance of the custom generated property can then be added to the entity type.

This example creates and attaches a generated property named cp with the type int32 on the airport entity type as an anonymous class. This property will be represented in the metadata alongside the other declared properties.

$airport = Lodata::getEntityType('airport');

$property = new class('cp', Type::int32()) extends GeneratedProperty {
    public function invoke(Entity $entity)
    {
        return new Int32(4);
    }
};

$airport->addProperty($property);

Asynchronous requests

The OData specification defines asynchronous requests where the client indicates that it prefers the server to respond asynchronously via the respond-async Prefer header. This is helpful for long-running operations.

Lodata handles this by generating a Laravel job which is then processed by Laravel in the same way it handles any other queued job. For this to work your Laravel installation must have a working job queue.

When the client sends a request in this way, the server dispatches the job and returns to the client a monitoring URL. The client can use this URL to retrieve the job output, or its status if not completed or failed.

The job runner will execute the OData request in the normal way, but will write the output to a Laravel disk for it to be picked up later. The name of this disk is set in the disk option in config/lodata.php. In a multi-server environment this should be some type of shared storage such as NFS or AWS S3. The storage does not need to be client-facing, when the job output is retrieved it is streamed to the client by the Laravel application.

Filter expressions

Lodata contains an expression parser in \Flat3\Lodata\Expression that handles both $search and $filter expressions. The parser decodes the incoming expression into an abstract syntax tree. During entity set query processing the entity set driver will be passed every element of the tree in the correct parsing order, enabling it to convert the OData query into a native query such as an SQL query.

Because not every possible OData function or operation is supported by every Laravel database driver, or the internal semantics of the underlying database do not support the required data types, then a "Not Supported" exception may be thrown by some database drivers and not others.

The OData specification describes that the behaviour of the $search system query parameter is application-specific. Simple support for converting $search to a series of field LIKE %param% requests is available.

The properties that should be used in search queries can be "tagged" using this example. Here the entity type 'airport' is retrieved, which may have been generated via autodiscovery. The 'name' property is also retrieved, and its 'searchable' property is updated.

$airportType = Lodata::getEntityType('airport');
$airportType->getProperty('name')->setSearchable();

Any property marked in this way is added to the query by the SQL driver.

The behaviour of both the $search and $filter parameters can be overridden by extending the driver class, and the relevant methods.

Alternative keys

In addition to the standard 'id' key that is typical in a database table, any other unique field can be added as an alternative key. This can then be used to reference an entity.

The properties that should be used as alternative keys can be "tagged" using this example. Here the entity type 'airport' is retrieved, which may have been generated via autodiscovery. The 'name' property is also retrieved, and its 'alternativeKey' property is updated.

$airportType = Lodata::getEntityType('airport');
$airportType->getProperty('code')->setAlternativeKey();

With this in place, an airport can be queried with its code using the request style http://localhost/odata/Airports(code='elo')

Operations

Lodata supports both Functions and Actions. By OData convention operations that define themselves as Functions MUST return data and MUST have no observable side effects, and Actions MAY have side effects when invoked and MAY return data. Lodata does not enforce the side-effect restriction, but does enforce the return data requirement.

Operations extend the \Flat3\Lodata\Operation class, and implement one of the \Flat3\Lodata\Interfaces\Operation\ActionInterface or \Flat3\Lodata\Interfaces\Operation\FunctionInterface interfaces. The class must also implement an invoke() method, which takes primitive type parameters. These parameter types and names will be read through PHP reflection and added to the metadata document.

The class can optionally define the name to use for the binding parameter using the bindingParameterName property and the returnType using the returnType property during construction. A primitive return type will be resolved through reflection on the invoke() method. When returning an entity the entity type must be attached using the setReturnType method, and the invoke method should return Entity.

This Function defined as an anonymous class instance does not receive any parameters, and has a primitive return type of Edm.String resolved through reflection. This function can be invoked via http://localhost/odata/helloworld()

Lodata::add((new class('helloworld') extends Operation implements FunctionInterface {
    public function invoke(): String_
    {
        return new String_('Hello world!');
    }
});

This Function receives two Edm.String parameters, and returns an Edm.String that concatenates them. The names of the parameters and their types are resolved through reflection. This function can be invoked via http://localhost/odata/helloworld(one='hello',two='world)

Lodata::add((new class('concat') extends Operation implements FunctionInterface {
    public function invoke(String_ $one, String_ $two): String_
    {
        return new String_($one->get().$two->get());
    }
})

This Function requests that the bound parameter be provided as the 'code' parameter to the method, and sends it back unmodified. This can be invoked via a URL for example http://localhost/odata/Airports(1)/code/identity().

Lodata::add((new class('identity') extends Operation implements FunctionInterface {
    public function invoke(String_ $code): String_
    {
      return $code;
    }
})->setBoundParameter('code');

This Function requests the bound parameter be provided as the 'entity' parameter to the method, and additionally defines a provided parameter 'prefix' and then returns an Edm.String. This can be invoked via a URL for example http://localhost/odata/Airports(1)/codeprefix(prefix='example').

Lodata::add((new class('code') extends Operation implements FunctionInterface {
    public function invoke(Entity $entity, String_ $prefix): String_
    {
      return $prefix->get() . $entity->code->get();
    }
})->setBoundParameter('entity');

Finally, entities can themselves be generated and returned. This Function requests the bound parameter be provided as the texts parameter, and indicates that it returns an Entity. Because the entity type cannot be determined through reflection, it must be explicitly pulled from the model and provided to the operation. This can be invoked using a URL for example http://localhost/odata/Airports/egen() which would provide the Airports entity set to the egen function as the bound parameter.

Lodata::add((new class('egen') extends Operation implements FunctionInterface {
    public function invoke(EntitySet $texts): Entity
    {
        $entity = $texts->makeEntity();
        $entity['code'] = new String_('example');
        return $entity;
    }
})->setBindingParameterName('texts')->setReturnType(Lodata::getEntityType('text')));

To provide additional context to a Function that may require it, the Function can ask for the current Transaction by adding that argument to the invoke method. In this example the invoke method would receive the Transaction on the $transaction method parameter. The transaction contains all of the available context for the request, and can provide items such as the current system query options.

Lodata::add((new class('hello') extends Operation implements FunctionInterface {
    public function invoke(Transaction $transaction): String_
    {
      return new String_('hello');
    }
});

All of the above techniques also apply to Action operations.

Function composition

OData URLs are parsed using composition, with each path segment being piped to the next using a static pipe() method on path segment classes, with the final segment in the chain being responsible for handling the system query options and generating the response via the response() method.

Operations can therefore act on path segments that precede them as bound parameters, and the output of one operation can be piped into the next. The output can therefore pass through several functions before being output.

Laravel Octane

Lodata is compatible with Laravel Octane in both Swoole and Roadrunner configurations.

The Lodata model is shared as a singleton across all requests, which allows it to be dynamically updated at runtime without requiring a server restart. Lodata does not mutate any internal data structures during the request cycle, making it safe for multiple requests.

Note that Roadrunner does not currently support streaming responses, so all output is buffered in memory before being sent to the client. Swoole responses are streamed correctly.

Internals

Transactions

A \Flat3\Lodata\Controller\Transaction object is a representation of both the request (\Flat3\Lodata\Controller\Request) and response (\Flat3\Lodata\Controller\Response) objects, handles piping the request from one path segment to the next, and provides a variety of helper methods to generate context and get aspects of the request. Transaction also implements the streaming JSON encoder.

The OData $expand system query option, which can itself take system query parameters, creates a new transaction that represents a sub-request within the main request, with a subclass of the Request object as a NavigationRequest. These can be further nested in subrequests of $expand requests.

Transactions are also serializable for the purposes of async requests, and can therefore be handled offline, replayed, retried etc.

Transactions handle wrapping requests with database transactions, following OData rules for commit / rollback based on the success or failure of the request.

Streaming JSON

Responses to OData requests can be of unlimited size. The request for an entity set without server-side pagination, of a database table of many gigabytes, would generate a JSON document of at least that size. In order to process this efficiently, and without running out of memory, Lodata implements a streaming JSON encoder. Through this method the memory usage of the responding PHP process will stay very low.

Even if the request for the entity set is made with no pagination parameters, internally \Flat3\Lodata\EntitySet will implement pagination against the database or other storage system so that that system is not overloaded. This process is invisible to the client.

When a path segment refers to an entity set, the initialization of that path segment sets up the query including all the filtering options, but it is not executed to receive data from the data source until the content is actually emitted or an operation requests data from it. For example in the SQL driver, the path segment generates the query, prepares and executes the query, but not until the data is emitted does PDO start drawing data from the server and outputting it.

Types

OData specifies many primitive types that can be used in Lodata. PHP's type system is less specific than OData, so type conversion and coercion is implemented by each type to marshal between PHP and OData types. Lodata will force PHP data into the specified type, for example converting a PHP int to an OData Edm.Int16 may cause truncation or overflow, but will ensure the type is in the correct format when a client receives it.

PHP supports higher precision floating point types than JSON itself, so Lodata implements IEEE754 compatibility in OData by returning Edm.Double (and similar) types as strings if requested to do so by the client.

Lodata implements Edm.Date, Edm.DateTimeOffset and Edm.TimeOfDay using DateTime objects, and retrieving the value of (eg) a \Flat3\Lodata\Type\DateTimeOffset using its get() method will return a DateTime.

Specification compliance

The relevant parts of the specification used for Lodata are:

Lodata supports many sections of the OData specification, these are the major areas of support:

  • Publishing a service document at the service root
  • Publishing a metadata document in both JSON and XML formats
  • Adding custom annotations
  • Strict type model for primitive types, supporting Eloquent casts and getter/setters
  • Returning data according to the OData-JSON specification
  • Streaming JSON support
  • Using server-driven-pagination when returning partial results
  • The $expand system query option
  • The $select system query option
  • The $orderby system query option, including multiple orders on individual properties
  • The $top system query option
  • The $skip system query option
  • The $count system query option
  • The $search system query option
  • The $value path segment
  • The $filter system query option, with all expressions, functions, operators, and supports query parameter aliases
  • Asynchronous requests using Laravel jobs, with monitoring, cancellation and callbacks
  • Batch requests in both multipart and JSON formats, including entity back-references and asynchronous batch requests
  • Deep insert support at any depth
  • Deep update support at any depth
  • Resolving entity IDs into representations
  • Stream properties in the payload and via read links
  • Edit links, and POST/PATCH/DELETE requests for new or existing entities
  • Use of ETags for avoiding update conflicts
  • Composable URLs
  • Declared and navigation properties
  • Referential constraints
  • Entity singletons
  • Passing query options in the request body
  • Database transactions
  • Requesting entity references
  • IEEE754 number-as-string support
  • Primitive literals including duration and enumeration in URLs.
  • Full, minimal and no metadata requests
  • Function and Action operations, including bound operations and inline parameters
  • Automatic discovery of PDO or Eloquent model tables, and relationships between Eloquent models
  • All database backends that Laravel supports (MySQL, PostgreSQL, SQLite and Microsoft SQL Server) including all possible $filter expressions
  • Automatic discovery of OData feeds by PowerBI (using PBIDS) and Excel (using ODCFF)
  • Custom entity type, primitive type and entity set support
  • Extensible driver model enabling the integration of data stores such as Redis, local files and third party REST APIs.

License

Copyright © Chris Lloyd

Flat3 Lodata is open-sourced software licensed under the MIT license.