Synchronize Data with Logic

Synchronize Data with Logic
lac52
The logic processing in 
CA Live API Creator
 interacts with the values that are in the database. Logic processing synchronizes data with logic using one of the following approaches:
 You can synchronize data with logic using a hybrid approach. If you have existing now-incorrect non-null data, you can have 
CA Live API Creator
 nullify the values using SQL 
update
 commands. This triggers the recompute logic that is described in the "Just-In-Time Recompute" section.
This article explores these approaches and identifies best practices on which approach to use.
In this article:
 
 
Logic Processing  
 
CA Live API Creator
 maintains high performance by presuming that the attribute values in the database schema (the persisted attributes) match your logic. For example, in the 
Demo
 API sample, 
CA Live API Creator
 assumes that the 
Customer balance
 is correct. You can avoid expensive aggregate (
select sum
) queries by adjusting the values on 
purchaseorder
 changes. 
CA Live API Creator
 defines the 
Customer balance
 initially as the sum of the 
Order AmountTotals
 (unqualified). You insert data, and now the balance is now $100 consisting of a paid order for $30 and an unpaid order for $70. 
CA Live API Creator
 alters the 
Customer balance
 to the sum of the unpaid order totals. At this point, the balance should be $70, but 
CA Live API Creator
 stores it as $100. Altering the rule does not alter the existing data. Deleting the paid order does not reduce the balance. It remains $100, which is still incorrect.
Just In Time (JIT) Recompute
With this approach, 
CA Live API Creator
 recomputes the data value for derivation rules (formula, sun, count, and min/max) when/as it accesses the data and detects null values, whether for update or retrieval. Having 
CA Live API Creator
 recompute the value for the rules is useful when you add a column or a rule. Newly-added columns are blank and therefore the values are null. Like normal rule operation, the JIT Recompute approach addresses dependencies, so that you can add chained rules and 
CA Live API Creator
 recomputes your rules when/as it detects null values.
Best Practice:
 If your columns are new, and therefore, the values are null, or if other non-API-based software does not depend on correct data, synchronize data with logic using the JIT Recompute approach.
For a list of all best practices, see 
CA Live API Creator
 Best Practices.
Use SQL to Ensure Conformance
If you use business intelligence tools to access data, bring your current schema into conformance by synchronizing data with logic using the SQL approach. With this approach, you use SQL 
update
 commands.
For example, in the 
Demo
 sample API, 
CA Live API Creator
 must nullify the existing non-null data. Because it is looking for null values, it does not detect this data. The sample API uses the following (MySQL) queries in the rules:
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);
If you are using SQL Server, the following is a similar script:
update [dbo].[Customers] set Balance =
(select sum(orders.AmountTotal) from [dbo].[Orders] orders
where (orders.CustomerID = [dbo].[Customers].CustomerID));