Enabling Change Tracking¶
Change Tracking and Delta Links¶
Client applications may utilize OData change tracking with delta links to restrict downloads to data that has changed since a previous download.
If the generated OData service is to be consumed by occasionally-connected mobile clients, together with CloudSyncProvider or OfflineODataProvider in one of the SAP Business Technology Platform client SDKs,
then it is highly beneficial for data synchronization performance to enable change tracking.
In particular, the performance of delta downloads, where the client wishes to download only the back-end data changes since the previous download, can be expected to be achieved in seconds if change tracking is enabled, compared with minutes to hours if change tracking is not enabled (assuming there was a high volume of data in the initial 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.Immutableannotation term are treated as immutable. For example, theStoreIDproperty 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.Immutableannotation term wherever applicable to immutable properties that are used in$filterqueries with change tracking, since it enables optimal server-side processing of change tracking queries with minimal cost.Caution
Mistakenly using the
Core.Immutableannotation 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.
-
The property is updated less frequently than other properties in the entity.
-
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>
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>
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>
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>
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>
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>