Red Hat Training

A Red Hat training course is available for Red Hat JBoss Data Virtualization

Chapter 9. OData Support

9.1. OData Support

9.1.1. OData

OData (Open Data Protocol) is a web protocol that utilizes URIs to identify and interact with resources allowing for a great level of data integration and interoperability across clients, servers and services.

9.1.2. Use Case

REST is commonly used by companies that have implemented service-oriented architectures. However, it does not provide unified calling semantics nor does it provide a data model, meaning that each company has needed to define its own. OData is a solution to this problem because it provides a specification that defines standard ways to define data source operations and also standardizes the way in which you define your data schema.
By standardising your service and providing metadata that applications can use, you can avoid vendor lock-in.

9.1.3. Implementation in Red Hat JBoss Data Virtualization

When you deploy a virtual database, the OData protocol is supported by default. OData support is implemented and deployed through two WAR files that you can find at EAP_HOME/dataVirtualization/vdb/teiid-odata.war and EAP_HOME/dataVirtualization/vdb/teiid-olingo-odata4.war.

9.1.4. Accessing Information via OData

If you have a virtual database called "northwind" deployed and you wish to access a customers table in an NW model, use HTTP GET via this URL: 
http://localhost:8080/odata/northwind.1/NW.customers
The equivalent SQL command via a JDBC/ODBC connection is this: 
SELECT * FROM NW.customers

Note

Make sure that you fully qualify the table name along with the model name. Also, use the correct case (upper or lower) to match what you have in the virtual database.
You can output your query results in AtomPub XML (the default), Atom or JSON format.

Note

Sometimes you may see this message: 
<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
<code>NotFoundException</code>
<message lang="en-US">EdmEntitySet NW.customer is not found</message>
</error>
This means you either supplied an incorrect model-name.table-name combination so please check the spelling and case. Alternatively, it may mean that your table did not have any PRIMARY KEY or UNIQUE KEY(s) on them. Since OData access is key oriented, every table must have a primary key or at least one unique key. (Note that Red Hat JBoss Data Virtualization does support composite primary keys.)
If you do not see all the rows, issue another call with the same URL, but with the $skiptoken query option specified from the previous result: 
http://localhost:8080/odata/northwind.1/NW.customers?$skiptoken=xxx
You can submit criteria with your query to filter the results: 
http://localhost:8080/odata/northwind.1/NW.customers?$filter=name eq 'bob'
This is the equivalent of making a JDBC/ODBC connection and issuing the SQL 
SELECT * FROM NW.customers where name = 'bob'
To request the result in JSON format, add this option: $format=json. Here is an example: 
http://localhost:8080/odata/northwind.1/NW.customers?$format=JSON
You can combine query options as needed. Here is an example: 
http://localhost:8080/odata/northwind.1/NW.customers?$filter=name eq 'bob'&$format=JSON
You can navigate from one entity to another like this: 
http://localhost:8080/odata/northwind.1/NW.customers(1234)/NW.orders?$filter=orderdate gt datetime'2012-12-31T21:23:38Z'
That is the equivalent to this SQL query: 
SELECT o.* FROM NW.orders o join NW.customers c on o.customer_id = c.id where c.id=1234 and o.orderdate > {ts '2012-12-31 21:23:38'}
You can also use OData to perform CREATE/UPDATE/DELETE operations along with READ operations. Here are the HTTP methods involved:
INSERT/CREATE is accomplished through the "POST" HTTP method: 
POST /service.svc/Customers HTTP/1.1
Host: host
Content-Type: application/atom+xml
Accept: application/atom+xml
Content-Length: nnn
<?xml version="1.0" encoding="utf-8"?>
<entry xml:base="http://host/service.svc/"
xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices"
xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"
xmlns="http://www.w3.org/2005/Atom">
    <id>http://host/service.svc/Customers('ASDFG')</id>
    <title type="text" />
    <updated>2008-12-07T8:00:00Z</updated>
    <author>
        <name />
    </author>
    <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Orders" href="Orders(1)" />
    <link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Orders" href="Orders(2)" />
    <content type="application/xml">
        <m:properties>
            <d:CustomerID>ASDFG</d:CustomerID>
            <d:CompanyName>Contoso Widgets</d:CompanyName>
            <d:Address>
                <d:Street>58 Contoso St</d:Street>
                <d:City>Seattle</d:City>
            </d:Address>
        </m:properties>
    </content>
</entry>
An UPDATE is performed with an HTTP "PUT" command. Here is an example: 
PUT /service.svc/Customers('ALFKI') HTTP/1.1
Host: host
Content-Type: application/atom+xml
Accept: application/atom+xml
Content-Length: nnn
DataServiceVersion: 1.0
MaxDataServiceVersion: 3.0
Prefer: return-content
<?xml version="1.0" encoding="utf-8"?>
<entry xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns="http://www.w3.org/2005/Atom">
    <content type="application/xml">
    <m:properties>
        <d:CustomerID>ALFKI</d:CustomerID>
        <d:CompanyName>Updated Company Name</d:CompanyName>
        <d:Address>
        <d:Street>Updated Street</d:Street>
        </d:Address>
    </m:properties>
    </content>
</entry>
Here is an example delete command: 
DELETE /service.svc/Customers('ALFKI') HTTP/1.1
Host: host
Content-Type: application/atom+xml
Accept: application/atom+xml
Content-Length: nnn
DataServiceVersion: 1.0

9.1.5.  Security

OData access is restricted by HTTPBasic authentication which is authenticated against the "teiid-security" domain. You must assign users the odata role.
In order to change the default security domain, use a JBoss AS deployment-overlay to override the web.xml file.

9.1.6. Proxy and Cloud Environments

If the Red Hat JBoss Data Virtualization server is configured behind a proxy server or deployed in cloud environment, or using a load-balancer then the URI of the server which is handling the OData request is different from URI of the proxy. To generate valid links in the OData responses, you must configure the "proxy-base-uri" property in the web.xml file. If it is a system-wide property, configure it like this: 
<context-param>
    <param-name>proxy-base-uri</param-name>
    <param-value>${system-property-name}</param-value>
</context-param>

Note

When the volume of result rows exceed the configured batch size, cursoring logic is implemented. On every request, only batch-size number of rows are returned. Each such request is considered an active cursor, with a specified amount of idle time specified by the skip-token-cache-time parameter. After the cursor has timed out, it closes and the remaining results and will not be available for any further queries. Since there is no session-based tracking of these cursors, if the request for skiptoken comes after the expiry, the original query will be executed again and tries to reposition the cursor to relative absolute position, however the results are not guaranteed to be same as the underlying sources may have been updated with new information meanwhile.

9.1.7. How Red Hat JBoss Data Virtualization exposes schema for OData

OData defines its schema using Conceptual Schema Definition Language (CSDL). Every VDB, that is deployed in an ACTIVE state in the Red Hat JBoss Data Virtualization server exposes its metadata in CSDL format. For example if you want retrieve metadata for the Northwind database, you need to issue a query like this: 
http://localhost:8080/odata/northwind/$metadata
Since the OData schema model is not a relational schema model, Red Hat JBoss Data Vritualization maps its relational schema model to OData’s schema model like this:

Table 9.1. Table Mappings

Relational EntityMapped OData Entity
Model Name Schema Namespace, EntityContainer Name
Table/View EntityType, EntitySet
Table Columns EntityType’s Properties
Primary Key EntityType’s Key Properties
Foreign Key FunctionImport
Procedure Navigation Property on EntityType, Association, AssosiationSet
Procedure’s Table Return ComplexType. (Note that Red Hat JBoss Data Virtualization does not define any "embedded" ComplexType in the EntityType. It also does not define any one EntityContainer that resulted from different VDB models as a default container. Therefore, you can only access entities by supplying their full paths.)

9.1.8. Native Queries

Native and direct queries are not supported However, you can use the web service translator’s invokehttp method directly to issue a REST-based call and parse the results of this via SQLXML.

9.1.9. Server Use

Red Hat JBoss Data Virtualization can expose any data source as an OData-based web service.

9.1.10. Configuration

The OData WAR file can be configured with the following properties in the web.xml file.

Table 9.2. web.xml Settings

Property NameDescriptionDefault Value
batch-sizeNumber of rows to send back each time, -1 returns all rows256
skiptoken-cache-timeTime interval between the results being recycled/expired between $skiptoken requests300000
local-transport-nameData Virtualization local transport name for connection odata
invalid-xml10-character-replacementReplacement string if an invalid XML 1.0 character appears in the data - note that this replacement will occur even if JSON is requested. No value (the default) means that an exception will be thrown with XML results if such a character is encountered.
proxy-base-uriDefines the proxy server’s URI to be used in OData responses.n/a
connection.XXX Sets XXX as an execution property on the local connection. Can be used for example to enable result set cache mode. n/a
Red Hat JBoss Data Virtualization OData server implements cursoring logic when the result rows exceed the configured batch size. On every request, only batch-size number of rows are returned. Each such request is considered an active cursor, with a specified amount of idle time specified by skip-token-cache-time. After the cursor is timed out, the cursor will be closed and remaining results will be cleaned up, and will no longer be available for further queries. Since there is no session based tracking of these cursors, if the request for skiptoken comes after the expired time, the original query will be executed again and tries to reposition the cursor to relative absolute position, however the results are not guaranteed to be same as the underlying sources may have been updated with new information meanwhile.

9.1.11. Limitations

The following feature limitations currently apply.
  • JBoss Data Virtualization now supports OData Version 4.
  • Blob support for media types are not supported.
  • $value construct to retrieve individual column value is supported.
  • create/update/delete $links is not supported.
  • $expand support is limited to a single parent/child.

9.1.12. Client Tools for Access

There are various ways you write your OData access layer. Your choice depends upon your needs and your programming model. Here are some suggestions:
  • Your Browser: The OData Explorer is an online tool for browsing an OData data service.
  • Microsoft .NET Framework 3.51: the WCF Data Services framework is available as a separate download for .NET 3.x.
  • Microsoft .NET Framework 4.0: the WCF Data Services framework built into .NET 4.0.
  • Silverlight 3: the Data Services client library for Silverlight is available for download.
  • Java: the Restlet 2.0 library for Java (including Java on your Android phone) supports the OData protocol.
  • Java: Use a library like OData4J for Java based access, or any Rest based framework
  • JavaScript: the XMLHttpRequest object is standard in modern browsers or you can use jQuery, which comes out of the box with .NET 4.0 or is available for download.
  • PHP: the Toolkit for PHPprovides OData support for PHP clients.
  • AJAX: if you’re using AJAX for ASP.NET, Microsoft provides the ASP.NET Ajax Library for getting to OData.
  • Excel 2010 PowerPivot: PowerPivot comes with OData support built right in.
  • Windows Desktop: LINQPad is a good tool for building OData queries interactively but it has some limitations: firstly, it is not able to handle FunctionImports (procedures). If the model contains only procedures, no tables then also it acts error. Secondly, it does not work with multiple schema, as it does not show all the schemas, only the default one. Since no default schema is set in the VDB’s $metadata, it finds the first one and uses it. OData V2 does allow multiple Schemas and multiple EntityContainers in a single $metadata so it appears that LINQPad is not yet fully compliant with this specification.
  • Shell Scripts: use CURL tool