OData - the fine print
OData's current version is 4.0 and that's the only version I will focus on in this article.
OData is the love child of an effort that joined Microsoft and SAP at the hip to create an universal internet data access standard, that on first sight has a lot of NIH issues to sort out. But I'm sure on a second (or third or forth) look it will all make sense.
- a service document in
JSONformat. It describes what data endpoints are available and where. Depending on the requested scope (more on that later) that JSON contains different levels of details
- a meta data document. That document is referenced in the service document. The format is
XML. It describes the data format found in each record, relations to other record and actions that can be taken. Looks like XML Schema, but not quite. Record is used loosely here, data can be hierarchical and nested
- a query language squeezed into an URL, so data can be fetched using
HTTP GETwithout using a body submitted. Query allows data selections, filtering, output size and sorting to be specified
- Query results in JSON format enhanced with OData specific Metadata
So dealing with OData you can enjoy a mix of technologies:
- JSON for service document and payloads
- XML for meta data
- a dot notation (like dotNet or Java) for property values like data types (go learn about it). E.g.
Edm.Stringdefines a String value or
Org.OData.Core.V1.ResourcePaththe path to data
- another query language
- individual items by default get addressed in an uncommon format:
serviceurl/People('johndoe'). Luckily this is optional and you can use the more common format for a route:
serviceurl/People/johndoe. This seems to be born out of squezing the square pin URL into the round hole of database schemas and flexible primary keys
On the upside: Your Salesforce and Excel users will love it. Import and CRUD operations are well supported.
Initially I was looking at Apache Olingo to implement my needs. Unfortunately the library is too close to the Java servlet model and static defined data models (like your one application, not your data platform), so I disected my needs the hard way.
- A OData client (think Excel, Tableau, Salesforce) will initially connect to the service document url. It sends a
GETrequest. To specify what level of detail is required the
ACCEPTheader is used. So when testing your provider implementation you need to take that into account
- Excel sends
- Salesforce sends
- OpenAPI can specify the top level
application/jsonor the full qualified content type in the response. If you fully qualify it, you might end up with a 406 and wonder what happens
- The meta data url gets specified in your service document and is usually the service document url extended by
- I ran into a potential Salesforce bug, where the meta data URL was ignored and the service document URL extended by
$metadata. Note the missing
/. Caused a bit of grief since Excel does
- Everything is case sensitive, which makes sense, but is fun in Domino that treats item names case insensitive
- I couldn't get Salesforce to play nice until I added a field
ExternalIdto my form. Might be my lack of understanding how to change the primary key Salesforce will play nice with or something missing in my Metadata
Swap out the GET for a POST and the XML for something not-quite-JSON and you get GraphQL that tries to address a similar set of functionalities. We really love wheels reinvented
More lessons to be learned, stay tuned!