Creating APIs‎ > ‎Logic‎ > ‎

Synchronize Data with Logic

As an application developer, you want to understand how logic processing interacts with values currently in the database and understand the strategies to synchronize data with logic.

Background  

To maintain high performance, API Server presumes that existing persistent attribute values match your logic. For example, in the Logic Demo database, we assume that the Customer balance is correct. You can avoid expensive aggregate (select sum) queries by adjusting it on purchaseorder changes.

Such logic presumes that the currently stored values on disk are correct with respect to the rules. Imagine the following sequence: 
  1. Customer balance is initially defined as the sum of the Order AmountTotals (unqualified).
  2. Data is inserted. The balance is now $100 consisting of:
    • A paid order for $30.
    • An unpaid order for $70.
  3. The Customer balance is altered to the the sum of the unpaid order totals. At this point, the balance should be $70, but is stored as $100. Altering the rule does not alter the existing data.
  4. Deleting the paid order does not reduce the balance. It remains $100 (still wrong).
You can address this using one of the following strategies:
  • Just-in-time recompute. When you add new rules, you can specify that API Creator recompute the data values.
  • Use SQL to ensure conformance. You can correct the data by issuing SQL queries.
The following explores these alternatives and identifies best practices on which strategy to use.

Just-In-Time Recompute

You can specify to have API Creator recompute your rules when/as it accesses the data and detects null values, whether for update or retrievalSelect the Just In Time checkbox on the Manage, Rules page.

Having API Creator recompute the rules value is particularly useful when you add a new column and a new rule. Newly-added columns are blank and therefore the values are null. Like normal rule operation, Just-in-time recompute addresses dependencies, so you can add new chained rules and API Creator recomputes your rules when/as it detects null values.

 The following image shows the option on this page:

Use SQL to Ensure Conformance

Just-In-Time recompute may not fit all of your scenarios. For example:
  • If you use business intelligence tools to access the data, the not-yet-recomputed data will be wrong.
  • As in the Demo example, you might have existing non-null data that API Creator needs to correct. API Creator does not detect this data since it is looking for null values.

You can use SQL to bring your current schema into conformance. For example, the following are the (MySql) queries used for Demo API project:

update lineitem item set item.product_price = (select price from product p where p.product_number = item.product_number);

update lineitem item set item.amount = item.qty_ordered * item.product_price;

update purchaseorder po set po.amount_total = 
(select sum(item.amount) from lineitem item where (item.order_number = po.order_number));

update customer cust set cust.balance = 
(select sum(po.amount_total) from purchaseorder po where po.customer_name = cust.name and po.paid = false);

For the following rules:

If you are using SQL Server, a similar script is:

update [dbo].[Customers] set Balance = 
  (select sum(orders.AmountTotal) from [dbo].[Orders] orders 
          where (orders.CustomerID = [dbo].[Customers].CustomerID));

Best Practices

Use Just-In-Time when:
  • Your columns are new (so the values are null).
  • Other non-API-based software does not depend on correct data.

Note: You can use a hybrid approach. If you have existing now-incorrect data, you can issue SQL update commands to nullify the values. This triggers the recompute logic as described in Just-In-Time Recompute.