REST API and Entity Frameworks

Entity Frameworks have become very popular, with major implementations in recent years including Microsoft .NET and Java Enterprise Edition. The aim is to support an application data model that uses a database for persistence. In the right circumstances such frameworks are an excellent solution, with responsive user and web interfaces. But they all tend to create a complex state on the client side and so are less suitable if:

·         the database schema is subject to change, or applications use different data models

·         the network of relationships between data objects is likely to change during an application session

·         consistency and transactional behaviour are important for the business

These are all really matters of discipline and scale. The schema and data models can be made subject to rigorous control, and provided application sessions are short enough some form of locking or verification of data could be implemented.  In the entity framework approach a set of object types in the client correspond to entities in the data model, and hence to base tables in the database. Each entity is then a row in such a base table.

At the opposite extreme are RESTful services where efforts are made to use only stateless interactions with the service. A typical data retrieval operation will transfer the current state of a single entity. For a RESTful API, the best solution for implementing transactions is to use row versioning RVV as developed by (Laiho and Laux 2010), since the RVV is a kind of cookie which can be presented in later transactions as reliable means of verifying that the previously obtained data is still valid. This approach has been well documented elsewhere and versions have been implemented in Microsoft’s SQL Server 2008 R2 to support optimistic transactions. Other DBMSs use versioning ideas for concurrency control in other ways. In this article, we remain close to the original RVV concept: to enable data fetched on the client side to be used safely in later transactions, and avoid blind overwriting of data, with the help of an RVV field in entity types.

In this article, we explore the combination of this concept with a disciplined approach to client side data models. To begin with, there should obvious be agreement between the server and client on the data types being transferred. It is then best for the DBMS to generate the entity types as viewed from a proposed client Role. Since we plan to use RVV it makes sense to have a Versioned base class for all entity types containing the versioning information in uniform and supported way.

Pyrrho has a great advantage over other DBMSs in that the versioning information is not part of a rollback segment or connection data, and so will remain valid long after the connection is closed. Two of the portions of the check field form an actual address in the transaction logs (partition file name and offsets). It is not strictly speaking an object identity since it is only given its value when the object is committed to the database, but it can be tested at any later time by using any connection to the same database and role. The database owner can use it to obtain a complete history from creation to deletion. The rowversion field cannot be used in quite the same way since it gives the address of the latest committed change to the entity, and so its role is to detect that such a change has occurred. (The RVV described by Laiho and Laux is incremented by 1 on each change.)

For example Pyrrho will generate a C# class definition similar to the following:

[Schema(179)]

///

/// Class AUTHOR from Database haikus, Role haikus

///

public class AUTHOR : Versioned

{

    [Key(0)]

    public String ID;

    public String NAME;

    public Byte[] PIC;

}

 The Versioned base class contains only the check and version fields (String and Int64 respectively). The Connect class gives access to the database itself. It conforms to normal ADO.NET/ODBC rules: it is opened for a database, may have a current transaction that can be committed or rolled back. Additional data annotations are added for fields with specified precision or scale, or that require special processing (e.g. Date).

This leads to a very tidy RESTful API, consisting of the following methods for the PyrrhoConnect class, where E is Versioned or a subclass of Versioned defined by code obtained from the Role$Class system table in Pyrrho. Classically, REST uses the HTTP 1.1 verbs of GET, POST, PUT, and DELETE, and the strongly typed FindXXX methods are recommended over Get(..).

Name	Explanation
void Delete(E ob)	Delete the given entity from the database table E. An exception will be thrown if the object is no longer valid.
E[] FindAll()	Retrieve all entities of the given Versioned type. †
E FindOne(params IComparable[] w)	Retrieve a single entity of a given Versioned type E with key fields w. †
E[] FindWith(string w)	Retrieve a set of Versioned entities satisfying a given condition. w is a comma-separated set of conditions of form field=value. Field names are case sensitive and values are in SQL format (single quotes on strings are optional in the absence of ambiguity). †
void Get(Versioned ob, string rurl)	Use the given relative url (starting with a / after the Role component) to obtain a single versioned object: ob must actually be of the correct type to receive the referenced entity. †
Versioned[] Get(string rurl)	Use the given relative url (starting with a / after the Role component) to obtain a set of Versioned objects. †
void Post(E ob)	The object should be a new entity. Components of keys that are null will be populated with suitable values. There is a second optional autoKey parameter for Post (default true) to request that key components that contain empty strings or 0 are to be overwritten with suitable values. Database triggers defined on E may also modify fields of the given object. †
void Put(E ob)	The object should be an updated version of an entity retrieved from or committed to the database. An exception will be thrown if the object is no longer valid. Database triggers defined on E may also modify fields of the given object. †

† If there is an explicit transaction in progress, the fields of E inherited from Versioned are populated only when the transaction creating or updating them commits. All of these methods will throw an exception if the database schema information for base table E and its role has been modified.

In the respects noted, the Versioned base class departs from normal POCO behaviour. Data obtained within an explicit transaction will be 100% consistent. It remains appropriate to commit transactions and close connections as quickly as the business logic allows, as the overall protocol is optimistic and conflicting updates cannot be reported before they are committed. The data that has been retrieved, including its versioning information, will be just as good when a connection to the database is re-established.

As Laiho and Laux observed, the excessive use of middleware caching of database objects in middleware can complicate transaction processing and lead to data coming from a mixture of database states. The versioning mechanisms can ensure consistency is not violated but it is best to minimise the amount of client-side state. For example, if they are small, there is really no need to maintain arrays of reverse foreign keys to implement navigational properties, and if they are large they are more likely to contain stale data.

The above API is very fast in operation, and the approach described here is recommended over the use of complex entity frameworks and persistence architectures. This blog contains a fully worked example showing the use of this API in an ASP.NET MVC application.

In the literature, discussions on these topics are closely related to transaction isolation issues. Pyrrho supports only fully isolated serializable transactions and does so using a safe optimistic protocol (locking is done only by the server; no client locking, no server retries on conflict).

Finally it is important to mention that Pyrrho continues to support HTTP REST with Xml and Json formats, and ADO.NET ExecuteReader() for iteration-based data retrieval and LINQ support.

References

Laiho, M., Laux, F. (2010): Implementing Optimistic Concurrency Control for Persistence Middleware Using Row Version Verification, Advances in Databases Knowledge and Data Applications (DBKDA), 2010 Second International Conference on, IEEE, ISBN 978-1-4244-6081-6 p. 45-50, DOI: 10.1109/DBKDA.2010.25.