Ship Order

When Orders are shipped, reduce the Customer.Balance, and Product.UnitsInStock.

cd examples/nw/tests
python test_upd_order_shipped.py

Background: Multi-table Logic

In the Place Order example, we saw several examples of multi-table logic:

• Adjustment - child changes cause a 1-row adjustment to the parent (e.g, adjust customer balance)
• Copy - child rows copy parent data (e.g., OrderDetail.UnitPrice)

This example introduces a third multi-table rule, described in the next section.

Cascade (vs. copy)

The copy rule does not propagate parent changes to child rows. And for Product UnitPrice, that's exactly what we want: Monday's orders should not reflect Tuesday price changes.

In some cases, we do want parent changes to cascade to child rows. Instead of using a copy, we merely reference the parent attribute. The logic engine will automatically propagate changes. See the reference to row.OrderHeader.ShippedDate in this rule:

    Rule.formula(derive=OrderDetail.ShippedDate, as_expression=lambda row: row.OrderHeader.ShippedDate)

Logic Definition: Ship Order - Pruning, Adjustment and Cascade

Requirements

It's aways a good idea map our rules (what) to requirements (why).

Reduce Customer Balance

The customer balance rule, previously defined for Add Order, is re-used and ensures the balance is reduced when the shipped date is set:

Rule.sum(derive=Customer.Balance, as_sum_of=Order.AmountTotal,
             where=lambda row: row.ShippedDate is None)  # *not* a sql select sum...

Reduce Product Units In Stock

In addition, we want to reduce the UnitsInStock value. We require 3 more rules in logic.py

    Rule.formula(derive=OrderDetail.ShippedDate, as_expression=lambda row: row.OrderHeader.ShippedDate)
    
    Rule.sum(derive=Product.UnitsShipped, as_sum_of=OrderDetail.Quantity,
             where="row.ShippedDate is not None")
    Rule.formula(derive=Product.UnitsInStock, calling=units_in_stock)

Logic Execution: Ship Order - Pruning, Adjustment and Cascade

The upd_order_shipped example illustrates pruning and adjustment: If DueDate is altered, nothing is dependent on that, so the rule is pruned from the logic execution. The result is a 1 row transaction - zero SQL overhead from rules.

If ShippedDate is altered, 2 kinds of multi-table logic are triggered - adjustment and cascade:

1. the logic engine adjusts the Customer.Balance with a 1 row update, as described above

   • Note that in this case, the triggering event is a change to the where condition, rather than a change to the summed value

   • The watch logic is monitoring changes to summed fields, where conditions, foreign keys, and inserts / updates / delete. This eliminates large amounts of clumsy, boring and error prone code

2. the ShippedDate is also referenced by the OrderDetail.ShippedDate rule, so the system cascades the change to each OrderDetail to reevaluate referring rules

3. This further chains to adjust Product.UnitsInStock, whose change recomputes Product.UnitsInStock (see below)

State Transition Logic (old values)

Logic often depends on the old vs. new state of a row. For example, here is the function used to compute Product.UnitsInStock:

def units_in_stock(row: Product, old_row: Product, logic_row: LogicRow):
    result = row.UnitsInStock - (row.UnitsShipped - old_row.UnitsShipped)
    return result

Note this logic is in Python: you can invoke Python functions, set breakpoints, etc.

Ship Order: Log

The log makes clear the chain of logic:

Logic Phase:		BEFORE COMMIT          						 - 2020-10-07 08:33:21,422 - logic_logger - DEBUG
Logic Phase:		ROW LOGIC (sqlalchemy before_flush)			 - 2020-10-07 08:33:21,423 - logic_logger - DEBUG
..Order[11011] {Update - client} AmountTotal: 960.0000000000, CustomerId: ALFKI, EmployeeId: 3, Freight: 1.2100000000, Id: 11011, OrderDate: 2014-04-09, RequiredDate: 2014-05-07, ShipAddress: Obere Str. 57, ShipCity: Berlin, ShipCountry: Germany, ShipName: Alfred's Futterkiste, ShipPostalCode: 12209, ShipRegion: Western Europe, ShipVia: 1, ShippedDate:  [None-->] 2020-10-07 08:33:21.422547  row@: 0x10b2d1a90 - 2020-10-07 08:33:21,423 - logic_logger - DEBUG
....Customer[ALFKI] {Update - Adjusting Customer} Address: Obere Str. 57, Balance:  [960.0000000000-->] 0E-10, City: Berlin, CompanyName: Alfreds Futterkiste, ContactName: Maria Anders, ContactTitle: Sales Representative, Country: Germany, CreditLimit: 2000.0000000000, Fax: 030-0076545, Id: ALFKI, OrderCount: 8, Phone: 030-0074321, PostalCode: 12209, Region: Western Europe, UnpaidOrderCount:  [3-->] 2  row@: 0x10b3ca640 - 2020-10-07 08:33:21,425 - logic_logger - DEBUG
....OrderDetail[1972] {Update - Cascading OrderHeader.ShippedDate (,...)} Amount: 530.0000000000, Discount: 0.05, Id: 1972, OrderId: 11011, ProductId: 58, Quantity: 40, ShippedDate: None, UnitPrice: 13.2500000000  row@: 0x10b4fd070 - 2020-10-07 08:33:21,429 - logic_logger - DEBUG
....OrderDetail[1972] {Prune Formula: Amount [['UnitPrice', 'Quantity']]} Amount: 530.0000000000, Discount: 0.05, Id: 1972, OrderId: 11011, ProductId: 58, Quantity: 40, ShippedDate: None, UnitPrice: 13.2500000000  row@: 0x10b4fd070 - 2020-10-07 08:33:21,429 - logic_logger - DEBUG
....OrderDetail[1972] {Formula ShippedDate} Amount: 530.0000000000, Discount: 0.05, Id: 1972, OrderId: 11011, ProductId: 58, Quantity: 40, ShippedDate:  [None-->] 2020-10-07 08:33:21.422547, UnitPrice: 13.2500000000  row@: 0x10b4fd070 - 2020-10-07 08:33:21,429 - logic_logger - DEBUG
......Product[58] {Update - Adjusting ProductOrdered} CategoryId: 8, Discontinued: 0, Id: 58, ProductName: Escargots de Bourgogne, QuantityPerUnit: 24 pieces, ReorderLevel: 20, SupplierId: 27, UnitPrice: 13.2500000000, UnitsInStock: 102, UnitsOnOrder: 0, UnitsShipped:  [0-->] 40  row@: 0x10b5057f0 - 2020-10-07 08:33:21,432 - logic_logger - DEBUG
......Product[58] {Formula UnitsInStock} CategoryId: 8, Discontinued: 0, Id: 58, ProductName: Escargots de Bourgogne, QuantityPerUnit: 24 pieces, ReorderLevel: 20, SupplierId: 27, UnitPrice: 13.2500000000, UnitsInStock:  [102-->] 62, UnitsOnOrder: 0, UnitsShipped:  [0-->] 40  row@: 0x10b5057f0 - 2020-10-07 08:33:21,432 - logic_logger - DEBUG
....OrderDetail[1973] {Update - Cascading OrderHeader.ShippedDate (,...)} Amount: 430.0000000000, Discount: 0.0, Id: 1973, OrderId: 11011, ProductId: 71, Quantity: 20, ShippedDate: None, UnitPrice: 21.5000000000  row@: 0x10b4fd160 - 2020-10-07 08:33:21,433 - logic_logger - DEBUG
....OrderDetail[1973] {Prune Formula: Amount [['UnitPrice', 'Quantity']]} Amount: 430.0000000000, Discount: 0.0, Id: 1973, OrderId: 11011, ProductId: 71, Quantity: 20, ShippedDate: None, UnitPrice: 21.5000000000  row@: 0x10b4fd160 - 2020-10-07 08:33:21,433 - logic_logger - DEBUG
....OrderDetail[1973] {Formula ShippedDate} Amount: 430.0000000000, Discount: 0.0, Id: 1973, OrderId: 11011, ProductId: 71, Quantity: 20, ShippedDate:  [None-->] 2020-10-07 08:33:21.422547, UnitPrice: 21.5000000000  row@: 0x10b4fd160 - 2020-10-07 08:33:21,433 - logic_logger - DEBUG
......Product[71] {Update - Adjusting ProductOrdered} CategoryId: 4, Discontinued: 0, Id: 71, ProductName: Flotemysost, QuantityPerUnit: 10 - 500 g pkgs., ReorderLevel: 0, SupplierId: 15, UnitPrice: 21.5000000000, UnitsInStock: 46, UnitsOnOrder: 0, UnitsShipped:  [-20-->] 0  row@: 0x10b505460 - 2020-10-07 08:33:21,436 - logic_logger - DEBUG
......Product[71] {Formula UnitsInStock} CategoryId: 4, Discontinued: 0, Id: 71, ProductName: Flotemysost, QuantityPerUnit: 10 - 500 g pkgs., ReorderLevel: 0, SupplierId: 15, UnitPrice: 21.5000000000, UnitsInStock:  [46-->] 26, UnitsOnOrder: 0, UnitsShipped:  [-20-->] 0  row@: 0x10b505460 - 2020-10-07 08:33:21,436 - logic_logger - DEBUG
Logic Phase:		COMMIT   									 - 2020-10-07 08:33:21,437 - logic_logger - DEBUG
..Order[11011] {Commit Event} AmountTotal: 960.0000000000, CustomerId: ALFKI, EmployeeId: 3, Freight: 1.2100000000, Id: 11011, OrderDate: 2014-04-09, RequiredDate: 2014-05-07, ShipAddress: Obere Str. 57, ShipCity: Berlin, ShipCountry: Germany, ShipName: Alfred's Futterkiste, ShipPostalCode: 12209, ShipRegion: Western Europe, ShipVia: 1, ShippedDate:  [None-->] 2020-10-07 08:33:21.422547  row@: 0x10b2d1a90 - 2020-10-07 08:33:21,437 - logic_logger - DEBUG
..Order[11011] {Hi, Andrew, congratulate Janet on their new order} AmountTotal: 960.0000000000, CustomerId: ALFKI, EmployeeId: 3, Freight: 1.2100000000, Id: 11011, OrderDate: 2014-04-09, RequiredDate: 2014-05-07, ShipAddress: Obere Str. 57, ShipCity: Berlin, ShipCountry: Germany, ShipName: Alfred's Futterkiste, ShipPostalCode: 12209, ShipRegion: Western Europe, ShipVia: 1, ShippedDate:  [None-->] 2020-10-07 08:33:21.422547  row@: 0x10b2d1a90 - 2020-10-07 08:33:21,448 - logic_logger - DEBUG
Logic Phase:		FLUSH   (sqlalchemy flush processing       	 - 2020-10-07 08:33:21,448 - logic_logger - DEBUG