Skip to content

Enabling Change Tracking

Client applications may utilize OData change tracking with delta links to restrict downloads to data that has changed since a previous download.

Change tracking can be enabled either for all entity sets in an entity container, or for individual entity sets. This is achieved by use of the SQL.TrackChanges annotation term within the appropriate element (EntityContainer, EntityType or EntitySet).

Example:

<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://docs.oasis-open.org/odata/ns/edmx http://docs.oasis-open.org/odata/odata/v4.0/os/schemas/edmx.xsd http://docs.oasis-open.org/odata/ns/edm http://docs.oasis-open.org/odata/odata/v4.0/os/schemas/edm.xsd">
    <edmx:Reference Uri="vocabularies/com.sap.cloud.server.odata.sql.v1.xml">
        <edmx:Include Namespace="com.sap.cloud.server.odata.sql.v1" Alias="SQL"/>
    </edmx:Reference>
    <edmx:DataServices>
        <Schema Namespace="example" xmlns="http://docs.oasis-open.org/odata/ns/edm" Alias="Self">
            <!-- entity types omitted for brevity ... -->
            <EntityContainer Name="ExampleService">
                <Annotation Term="SQL.TrackChanges"/>
                <!-- entity sets omitted for brevity ... -->
            </EntityContainer>
        </Schema>
    </edmx:DataServices>
</edmx:Edmx>

Note

For OData version 4.0, enabling change tracking at the server is not sufficient. Clients should also specify the Prefer: odata.track-changes header to ensure that the server will perform change tracking for individual requests.

Tip

If all entity sets within a schema should be enabled for change tracking, it is recommended to apply the SQL.TrackChanges annotation term to the EntityContainer element.

Change Tracking with $filter

When a client's queries specify the $filter System Query Option, especially for a complex $filter expression, it can sometimes be extremely difficult for an OData service to correctly respond with a minimal set of changes in response to a delta link query.

In particular, if the server does not know whether a client has previously downloaded some entities, and those entities do not currently match the client's selection criteria (as expressed in the $filter), upon the client's next download request the server may decide to return deleted-entity objects (also known as tombstones) in the delta response to ensure that the client (after download) will not retain any reference to those entities. Some of the deleted-entity objects may be unnecessary from the client's point of view. As a result the network utilization for transmission of the delta response, or the subsequent client-side resource utilization for processing of the delta response, may be greater than is desired.

Filtering on Immutable Properties

If the client specifies $filter where all referenced properties are immutable, and the filter does not use the OData now() function, then the client's downloads should be optimal.

  • All key properties are treated as immutable.

  • Any properties annotated with the OData standard Core.Immutable annotation term are treated as immutable. For example, the StoreID property for an inventory entity might be immutable.

    <edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://docs.oasis-open.org/odata/ns/edmx http://docs.oasis-open.org/odata/odata/v4.0/os/schemas/edmx.xsd http://docs.oasis-open.org/odata/ns/edm http://docs.oasis-open.org/odata/odata/v4.0/os/schemas/edm.xsd">
        <edmx:Reference Uri="https://oasis-tcs.github.io/odata-vocabularies/vocabularies/Org.OData.Core.V1.xml">
            <edmx:Include Namespace="Org.OData.Core.V1" Alias="Core"/>
        </edmx:Reference>
        <edmx:DataServices>
            <Schema Namespace="example" xmlns="http://docs.oasis-open.org/odata/ns/edm" Alias="Self">
                <EntityType Name="Inventory">
                    <Key>
                        <PropertyRef Name="ClientID"/>
                    </Key>
                    <Property Name="InventoryID" Type="Edm.Int64" Nullable="false"/>
                    <Property Name="StoreID" Type="Edm.Int64" Nullable="false">
                        <Annotation Term="Core.Immutable"/>
                    </Property>
                    ...
                </EntityType>
            </Schema>
        </edmx:DataServices>
    </edmx:Edmx>
    

    Tip

    It is recommended to use the Core.Immutable annotation term wherever applicable to immutable properties that are used in $filter queries with change tracking, since it enables optimal server-side processing of change tracking queries with minimal cost.

    Caution

    Mistakenly using the Core.Immutable annotation term on mutable properties may result in incorrect delta responses, where necessary deleted-entity objects are omitted.

Filtering on Mutable Properties

If the client specifies $filter where some referenced properties are mutable, or the filter uses the OData now() function, then the server may return unnecessary deleted-entity objects in delta responses.

To reduce or eliminate unnecessary deleted-entity objects in delta responses, which reduces the network utilization and client-side processing, one of the following techniques can be used.

Tracking on Individual Properties

In some scenarios you can improve the accuracy of delta calculation by tracking changes on individual properties. This is specified by annotating a Property with SQL.TrackChanges.

Individual property change tracking can reduce download sizes when the following two conditions are met.

  1. The property is updated less frequently than other properties in the entity.

  2. You query a different entity set using a filter that navigates to the property.

For example, if you have the a query like the following then adding individual tracking to the Name property would provide a benefit.

Orders?$filter=Customer/Name eq 'Bob'

Delta responses will be calculated more efficiently for the above query if you apply SQL.TrackChanges to the Name property on the Customer entity type. Otherwise any change to a customer entity could cause unnecessary re-downloading of orders related to the customer. The Name column would be rarely changed so adding tracking to it could reduce download times.

Note

It is not necessary to enable change tracking on individual properties if you will use Download Tracking (described below).

Optimizing with Download Tracking

Annotate the EntityContainer with the SQL.TrackChanges and SQL.TrackDownloads terms to enable the download tracking optimization.

  • Download tracking can give more precise delta responses (avoid unnecessary deleted-entity objects), although it also requires the use of Client Registrations.

  • Download tracking results in per-client retention of downloaded primary keys in the database, which will be automatically cleaned up when appropriate by Registration Expiry.

Example:

<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://docs.oasis-open.org/odata/ns/edmx http://docs.oasis-open.org/odata/odata/v4.0/os/schemas/edmx.xsd http://docs.oasis-open.org/odata/ns/edm http://docs.oasis-open.org/odata/odata/v4.0/os/schemas/edm.xsd">
    <edmx:Reference Uri="vocabularies/com.sap.cloud.server.odata.sql.v1.xml">
        <edmx:Include Namespace="com.sap.cloud.server.odata.sql.v1" Alias="SQL"/>
    </edmx:Reference>
    <edmx:DataServices>
        <Schema Namespace="my.schema" Alias="Self" xmlns="http://docs.oasis-open.org/odata/ns/edm">
            ...
            <EntityContainer Name="MyService">
                <Annotation Term="SQL.TrackChanges"/>
                <Annotation Term="SQL.TrackDownloads"/>
                ...
            </EntityContainer>
        </Schema>
    </edmx:DataServices>
</edmx:Edmx>

Complex Selection with Download Queries

The SQL.DownloadQuery term allows for complex selection criteria to be expressed at the server without clients having to use $filter in their queries. This is valuable if the desired selection criteria are too complex to be expressed using $filter. It also allows the server to restrict visibility of entities to clients for security purposes, whereas clients using $filter may be able to access any data in the entity set.

The simplest form of SQL.DownloadQuery is a query only involving the entity type within which the annotation is placed. A query is expressed using the form select entity.* from EntityType entity. Although this query is expressed using SQL syntax, the "table names" should actually be entity type names, and any "column names" should be entity property names. Thus it is a SQL query expressed using the CSDL entity schema, rather than the underlying SQL table schema.

Example:

<EntityType Name="Customer">
    <Annotation Term="SQL.DownloadQuery">
        <String><![CDATA[
            select entity.* from Customer entity
        ]]></String>
    </Annotation>
    <Key>
        <PropertyRef Name="CustomerID"/>
    </Key>
    <Property Name="CustomerID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="RegionID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="Name" Type="Edm.String" Nullable="false" MaxLength="50"/>
    ...
</EntityType>

Note

The SQL.DownloadQuery term can only be used if Download Tracking is enabled.

Note

For a given entity set (or its entity type), either the server does filtering using SQL.DownloadQuery (when specified in the metadata), or the client can use $filter, but both cannot be used together.

Download Queries Using Filter Entities

You can express complex selection criteria referencing filter entities in the SQL select statement. The idea is to define one or more extra filter entity types that express a client's selection criteria, and define the download query to join the main entity type with the filter entity type(s).

Example:

<EntityType Name="Customer">
    <Annotation Term="SQL.DownloadQuery">
        <String><![CDATA[
            select entity.* from Customer entity, RegionFilter filter
            where entity.RegionID = filter.RegionID
        ]]></String>
    </Annotation>
    <Key>
        <PropertyRef Name="CustomerID"/>
    </Key>
    <Property Name="CustomerID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="RegionID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="Name" Type="Edm.String" Nullable="false" MaxLength="50"/>
    ...
</EntityType>

A filter entity type should define a FilterID key property with type Edm.Int64, as well as using the SQL.ClientFilter term to indicate that the filter is client-specific. The filter entity type should also define one extra property for each value that is required to express the client's selection criteria.

Example:

<EntityType Name="RegionFilter">
    <Annotation Term="SQL.ClientFilter"/>
    <Key>
        <PropertyRef Name="FilterID"/>
    </Key>
    <Property Name="FilterID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="RegionID" Type="Edm.Int64" Nullable="false"/>
    ...
</EntityType>

Note

The SQL where clauses for comparison of main entities with filter entities are not limited to using the SQL equality operator ("="); any SQL operator can be used so long as it is supported by the SQL dialect of the database server.

Download Queries Using Other Entities

Download queries can also refer to other (non-filter) entity types in the SQL select statement. In the following example, the download query for Order refers to a filter entity type RegionFilter, and also refers to non-filter entity type Customer.

Example:

<EntityType Name="Order">
    <Annotation Term="SQL.DownloadQuery">
        <String><![CDATA[
            select entity.* from Order entity, Customer customer, RegionFilter filter
            where entity.CustomerID = customer.CustomerID
            and customer.RegionID = filter.RegionID
        ]]></String>
    </Annotation>
    <Key>
        <PropertyRef Name="OrderID"/>
    </Key>
    <Property Name="CustomerID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="OrderID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="RegionID" Type="Edm.Int64" Nullable="false"/>
    ...
</EntityType>

Download Queries Using Union Clauses

For more complex requirements, the download query can be multipart using SQL union clauses.

Example:

<EntityType Name="Customer">
    <Annotation Term="SQL.DownloadQuery">
        <String><![CDATA[
            select entity.* from Customer entity, RegionFilter filter where entity.RegionID = filter.RegionID
            union
            select entity.* from Customer entity, NameFilter filter where entity.Name = filter.Name
        ]]></String>
    </Annotation>
    <Key>
        <PropertyRef Name="CustomerID"/>
    </Key>
    <Property Name="CustomerID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="RegionID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="Name" Type="Edm.String" Nullable="false" MaxLength="50"/>
    ...
</EntityType>

Download Queries Using Correlated Subqueries

Download queries can also use SQL correlated subqueries. If a download query can be expressed with SQL union clauses and also with SQL correlated subqueries, then SQL union clauses should be used as a first preference, as it is likely to result in better download performance.

Example:

<EntityType Name="Customer">
    <Annotation Term="SQL.DownloadQuery">
        <String><![CDATA[
            select entity.* from Customer entity
            where exists (select filter.RegionID from RegionFilter filter where entity.RegionID = filter.RegionID)
            or exists (select filter.Name from NameFilter filter where entity.Name = filter.Name)
        ]]></String>
    </Annotation>
    <Key>
        <PropertyRef Name="CustomerID"/>
    </Key>
    <Property Name="CustomerID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="RegionID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="Name" Type="Edm.String" Nullable="false" MaxLength="50"/>
    ...
</EntityType>

Inheriting Download Queries From a Parent Entity

For a child entity type in a parent/child relationship where the parent entity type is configured with a download query, the child entity type can be defined to inherit the parent's download query by using the SQL.InheritDownloadQuery term, linking with a navigation property that refers to the parent entity. For example, with a parent Customer entity type and a child Order entity type, if clients wish to download all orders that are related to customers according to the download query for the Customer entity type, then the Order entity type could be defined as follows.

<EntityType Name="Order">
    <Annotation Term="SQL.InheritDownloadQuery" NavigationPropertyPath="Customer"/>
    <Key>
        <PropertyRef Name="OrderID"/>
    </Key>
    <Property Name="OrderID" Type="Edm.Int64" Nullable="false"/>
    <Property Name="CustomerID" Type="Edm.Int64" Nullable="false"/>
    <NavigationProperty Name="Customer" Type="Self.Customer" Nullable="false" Partner="Orders">
        <ReferentialConstraint Property="CustomerID" ReferencedProperty="CustomerID"/>
    </NavigationProperty>
    ...
</EntityType>

Last update: September 29, 2020