Data Versioning with View Entities

Introduction

View entities allow you to work with versioned data. In this example, you will implement versioned data within a domain model.

Use Case

For this purpose of this use case, the following domain model is used:

The domain model above is for an ordering system of a shop. The entities contain the following:

• OrderInfo - information about an order, such as the fulfillment deadline
• OrderLine - information about purchased items, such as price or quantity
• Customer - information about the purchasing customer
• OrderUpdate - information about the status of an order, such as when the order ships or is delivered; a new row is added every time an order status changes

An advantage of separating OrderUpdate from OrderLine is that it is easier to keep track of the order history. You can create an entity that includes information from both entities, but it works on the assumption that the workflow remains the same with each order (for example, order placed > order processed > order shipped). However, this may make it difficult to see other statuses, such as which orders have been processed. To avoid this, use a view entity to implement data versioning.

Data Versioning

To view the latest status of an order, follow the steps below:

1. Open your domain model and add a view entity named LatestOrderStatusVE.

2. Add the following query to the OQL editor:

   SELECT
       o.OrderId as OrderId,
       o.RequiredDate as RequiredDate,
       u.OrderStatus as OrderStatus,
       u.UpdateDate as UpdateDate
   FROM Shop.OrderInfo o
   JOIN o/Shop.OrderUpdate_OrderInfo/Shop.OrderUpdate u
   JOIN (
       SELECT
           u.OrderId as OrderId,
           MAX(u.UpdateDate) as UpdateDate
       FROM Shop.OrderUpdate u
       GROUP BY u.OrderId
   ) latest ON (latest.OrderId = o.OrderId AND latest.UpdateDate = u.UpdateDate)
   

The final result is a list with only the latest update of each order:

Order Status on Specific Date

Another advantage of data versioning is that you can take a snapshot of the order status on any given date. For example, if you want to know the status of orders on 8/17/1997, add the following line at the end of your existing query:

WHERE u.UpdateDate < CAST('1997/08/17' as DATETIME)

This addition retrieves the order status for one specific date: