<< Click to Display Table of Contents >> Navigation:  REST API > EQuIS REST API OData

•Overview

•Service Document

•Service Metadata Model

•Retrieving Database Records

•Creating a Database Record

•Updating a Database Record

•Deleting a Database Record

 

Overview

 

OData (Open Data protocol) is a standard that defines best practices for building and consuming RESTful APIs that interact with relational data. This article is not intended to provide documentation or detailed information about OData. To learn more about OData generally, please see https://www.odata.org.

 

The EQuIS REST API includes an implementation of OData that allows users to interact directly with tables, columns, and records in the underlying EQuIS database. The EQuIS REST API implementation of OData includes most of the functionality defined in OData version 4.0, but there are some limitations, exclusions, and EQuIS-specific functionality. This document provides information and examples specific to the EQuIS REST API implementation of OData.

 

Before using OData to interact with the EQuIS REST API, users should first understand the following:

•The OData implementation is part of the EQuIS REST API, and therefore, requires a license for the EQuIS REST API (it will not work without an EQuIS REST API license).

•Any user who is accessing OData via the EQuIS REST API will need all of the following:

•To be a member of the “EQuIS Enterprise – REST API” license role. This role is automatically created when the REST API license is added to the site.

•To be a member of one properly configured EQuIS ALS role (see the Application Level Security article).

•To create and use an EQuIS API token for authenticated OData requests (see the Security and Tokens article).

•Except for the service document and $metadata document endpoints described below, all OData endpoints will operate using the ALS connection for the authenticated user. For example, a read-only ALS role would give the user access to GET data, but not PUT, UPDATE, or DELETE data. This is true even for administrators—they must also be a member of an ALS role and the operations that will use that role.

 

All REST API interactions use URIs that are based on the main URL for the EQuIS Enterprise site. For example, if the EQuIS Enterprise web application is accessed at https://mysite.equisonline.com, then the site’s base URI is https://mysite.equisonline.com. The following examples and explanations will use https://mysite.equisonline.com as the base URI, however, users will need to substitute their actual Enterprise site’s base URI when using OData to access their data.

 

The EQuIS REST API implementation of OData supports multiple operations that are described below. Swagger documentation for the OData endpoints is available as part of the standard EQuIS REST API Swagger documentation (e.g., see https://mysite.equisonline.com/swagger/ui/index#!/OData).

All operations require an authenticated user. The EQuIS REST API token must be passed as the Authorization header of the request as follows: Authorization: Bearer <token>. For example, the header may look like the following (token has been shortened for display purposes):

 

Authorization: Bearer eyJhtsxeelk…lcK

 

 

Service Document

 

The OData specification states the response returned to a GET request to the root of an OData service is the service document. The service document is a JSON (JavaScript Object Notation) object that lists the “EntitySets” that are part of the service. For the EQuIS OData API, an EntitySet is either (with a couple of exceptions) a database table or a database view.

 

It is important to know that the Service Document and the Service Metadata Model (see below) use the default system connection string (not the ALS connection string for the user), which means that the response may include tables that the user cannot actually access because of limitations on their ALS role. Every incoming OData request requires access to the model and it takes several seconds to generate the model from the database. As such, the model is generated once (per w3wp.exe process) and then re-used for subsequent requests. For this to work properly, the model must be generated using the default system connection and not the connection associated with any specific ALS role.

 

Example: Retrieving the OData Service Document

 

Request:

 

GET https://mysite.equisonline.com/api/odata/

 

Response:

 

 

 

Service Metadata Model

 

The OData specification includes a specific endpoint (./$metadata) that returns the full data model that is applicable to this OData service. The $metadata model, known as EDMX, is XML and lists each table/view with its corresponding columns. For tables, the key field(s) are also indicated. As with the Service Document, the $metadata model is created with the system default connection, so tables may show up in the model that cannot actually be accessed by the user (because of limited permissions on her/his ALS role). The OData service is case sensitive and uses an uppercase convention for all table and field names (i.e., “DT_FACILITY” instead of “dt_facility”).

 

The key field(s) for a given table are used by OData to create unique URIs for each record and may or may not match the primary key of that table as defined in the database. All tables in the EQuIS database contain a field named “EUID” (which stands for EQuIS Unique Identifier). In some tables, such as DT_FACILITY, the EUID field is a computed field based on the underlying ID column (i.e., FACILITY_ID). In other tables, such as DT_LOCATION, EUID is a separate, required column. In other tables, such as DT_RESULT, the EUID column is nullable and only populated if/when necessary. The OData model will use EUID as the key field for a given table if it is computed or if it is required. If the EUID column is not a required/computed column, then the OData model will use the field(s) included in the table’s unique key (e.g., the primary key). It is important to note that the key field(s) used by the OData model may change over time. For example, if auditing is enabled on a given table, such as DT_RESULT, then the EUID column will become a required column. After that change, EUID will become the key field used by the OData model. For this reason, “@odata.id” and “@odata.editLink” URIs (explained below) may change over time and should not be considered permanent.

 

 

Example: Retrieving the OData Model (i.e., $metadata)

 

Request:

 

GET https://mysite.equisonline.com/api/odata/$metadata

 

Response:

 

 

 

Retrieving Database Records

 

A common use of OData is to retrieve records from the database (using an HTTP GET request). Records may be retrieved from any table or view defined in the model that is accessible to the authenticated user based on her/his ALS role and corresponding EQuIS permissions. For example, a user would only be able to retrieve records from the DT_FACILITY table for which s/he has at least Viewer permission.

 

Records are retrieved from a table by adding the uppercase table name to the root service URI (i.e., “https://mysite.equisonline.com/api/odata/”) as shown in the following examples. A maximum of one thousand (1000) records will be returned to any request. If there are more rows in the table/view that are not included in the initial response, then the response object will include a property called @odata.nextLink, which is a URI that may be used to retrieve the next set of applicable records. If the @odata.nextLink property is not returned, then no additional records are available.

 

Each record that is returned is a JSON object that contains the values of each field, as well as two special OData properties call @odata.id and @odata.editLink. These properties both contain the same URI—which points directly to that record and may be used for further operations on that record (e.g., updating the record or deleting the record).

 

Example: Retrieving Rows from a Table

 

Request:

 

GET https://mysite.equisonline.com/api/odata/DT_WATER_LEVEL

 

Response:

 

 

Example: Retrieving a Single Record by Entity ID (aka Key)

 

Request:

 

GET https://mysite.equisonline.com/api/odata/RT_ANALYTE('100-41-4')

 

Response:

 

 

Querying records via OData is similar to running a “SELECT” statement in the database. OData provides special URL syntax for refining the query based on certain logic. The EQuIS OData service currently supports the following options: $filter, $orderby, $top and $skip,, $count, and $select. The following options are not currently supported: $expand and $search. The following restrictions apply when using the supported options:

•If $skip is specified, then $top is required.

•If $top is specified, then $orderby is required.

•If $top is specified, then $count is not allowed (and vice versa).

•If $top is specified, it may not exceed one thousand (1000).

 

Example: Retrieving a Subset of Fields Using $select

 

Request:

 

GET https://mysite.equisonline.com/api/odata/RT_ANALYTE?$select=CAS_RN,CHEMICAL_NAME

 

Response:

 

 

The $filter option current supports the following binary operators:

•Mathematical operators: + (addition), / (division), * (multiplication), % (modulo), - (subtraction)

•Logical operators: and, or

•Comparison operators: eq (equals), gt (greater than), ge (greater than or equal to), lt (less than), le (less than or equal to), ne (not equals)

 

The $filter option currently supports the following functions:

•String functions: concat, contains, endswith, indexof, length, replace, startswith, substring, tolower, toupper, trim

•Date functions: year, month, day, hour, minute, second

•Numeric functions: round

 

Example: Retrieve a Subset of Rows Using $filter

 

Request:

 

GET https://mysite.equisonline.com/api/odata/DT_LOCATION?$filter=FACILITY_ID eq 17501 and endswith(SYS_LOC_CODE,'-30')&$select=SYS_LOC_CODE,LOC_TYPE

 

Response:

 

 

Example: Retrieving a Count of Records Using $count

 

Request:

 

GET https://mysite.equisonline.com/api/odata/DT_LOCATION?$filter=FACILITY_ID eq 1&$count=true

 

Response:

 

117

 

 

Creating a Database Record

 

The OData service supports creating records in the database. Creating records assumes that the authenticated user’s ALS role includes INSERT permission on the target table and that the user has appropriate permission on related objects (e.g., Creator permission on facilities to create a new facility, or Editor permission on the facility to create a new record within a facility). A record is created by sending an HTTP POST request to the table containing a JSON object (as the request body) with the values for each field. Assuming the process is successful, the response will contain a JSON object representing the record that was inserted.

 

At a minimum, all required fields must be included. The values in the request must adhere to all data integrity rules enforced by the database (e.g., foreign keys, check constraints, etc.). For tables that have a required ID column (such as DT_FACILITY.FACILITY_ID), the ID value will be assigned by the service and return with the response object.

 

Example: Creating a New Record

 

Request:

 

 

Response:

 

 

 

Updating a Database Record

 

The OData service supports updating existing records in the database. Updating records assumes that the authenticated user’s ALS role includes UPDATE permission on the target table and that the user has appropriate permission on related objects (e.g. Editor permission on the facility). A record is created by sending an HTTP PUT request to the record’s @odata.editLink URI with a JSON object (as the request body) with the values to be updated. Assuming the process is successful, the response will be HTTP status code 204 (no content) per the OData specification.

You cannot update any of the key field values in an existing record.

 

Making an HTTP PATCH request is equivalent to a PUT request—both methods function identically.

 

Example: Updating an Existing Record

 

Request:

 

 

Response:

 

HTTP 204 – No Content

 

 

Deleting a Database Record

 

The OData service supports deleting existing records from the database. Deleting records assumes that the authenticated user’s ALS role includes DELETE permission on the target table and that the user has appropriate permission on related objects (e.g., Owner permission on the facility). A record is created by sending an HTTP DELETE request to the record’s @odata.editLink URI. Assuming the process is successful, the response will be HTTP status code 204 (no content) per the OData specification.

 

While not an official part of the OData protocol specification, the EQuIS REST API OData service includes limited support for cascade delete of child records when a parent record is deleted (by including $cascade=true as the query string when making the DELETE request). The primary limitation is due to performance. Attempting to cascade delete a record from a table with many children (such as DT_FACILITY) is a time-consuming process because of the large number of child tables that are affected. Because OData operates over HTTP and each HTTP request has a limited execution time (e.g., 230 seconds), the request will fail if the cascade delete operation does not complete within the execution time. As such, cascade delete should only be used on tables with relatively few child tables.

 

Example: Deleting an Existing Record

 

Request:

 

DELETE https://mysite.equisonline.com/api/odata/DT_FACILITY(483371)

 

Response:

 

HTTP 204 – No Content