Sum

The Sum Business Logic Rule declares a Parent Domain Objects attribute to be maintained as the sum of a designated Children Role's summed attribute, with an optional qualification to filter child objects. For example:

  @Sum("purchaseorders.amountUnPaid where isReady = true") 

Context

Computing the Customer's Balance is part of the Check Credit Requirement of the Use Case Place Order, which illustrates one of our key Patterns - Forward Chaining: constraining chained derivations. When a Lineitem is inserted, the following Business Logic executes:

Suggestion: open the thumbnail above in a new, full resolution window
  1. Formula: Derive Lineitem.partPrice as Copy (product.price)
    • Note the ability for child derivations to reference Parent data
  2. Formula: Derive Lineitem.amount as partPrice * qtyOrdered
  3. Sum: Derive Purchaseorder.amountTotal as Sum(lineitems.amount where kitItem == null)
    Adjusted when lineitems.amount changes (as on an insert)
  4. Formula: Derive Purchaseorder.amountUnPaid as amountDiscounted - amountPaid
    • AmountPaid is the Sum(PaymentPurchaseorderAllocations.amount) (an advanced concept)
  5. Sum: Derive Customer.balance as Sum (purchaseorders.amountUnPaid where isReady=true)
    Chained aggregate
  6. Constraint: Customer Constraint as CreditLimit: fail if Balance > CreditLimit
    (also visible at the bottom of the screenshot)

Usage

The following screen shot declares the Customer's Balance as the sum of that customer's Purchase Order's AmountUnPaid for Purchase Orders that are Ready. The detailed procedure is described in the subsections below.


Define a Business Logic Component

If you have not already done so, create a Business Logic Component, shown as CustomerLogic, above.

Add a Business Logic Method derive<ParentAttributeName>

This need not have content - it will only be called for debug purposes.

By convention, we recommend the method name shown in the title. This designates the attribute being derived, Balance in the example above. If you would rather use a different name for the method, you can specify the attributeName value in the annotation.

Precede the method with the @Sum annotation

Unlike other Business Logic Rule methods, the method body has no semantic significance. It is provided for debug purposes only. All of the Business Logic is specified by the annotation parameters, described in the following sub sections.

There are two different forms - the simplest and most common (see the example at the top of this page)

  @Sum("SumExpression")

or

  @Sum(value="sumExpression", inMemory=[True | False], 
            persistent=[True | False], attributeName="ParentAttributeName")


value

The value is the default (only) parameter in the simpler form. It is an expression of the following form:

 <Children Role>.<ChildAttribute> [where ChildExpression]

where

  1. <Children Role> is a role in the parent that returns the Child rows
  2. <ChildAttribute> is the name of the attribute being summed
  3. <ChildExpression> filters the children that contribute to the sum
    1. It may reference any Child attribute, and does not require dot-notation qualification
    2. It may not reference Parent attributes of the child
      If these are required, simply define other child attributes to derive these values
    3. It is a general Boolean expression, so can include and or ( ) > etc

inMemory

persistent

attributeName

Use this to specify the Parent Attribute Name when your Logic Method Name does not encode this.


System Operation

Sum is a core Business Logic Rule. While simple and natural to state, they imply significant performance and re-use implications as described in the sub-sections below.

Business Logic Execution triggered by Child changes

As note above, your Logic Method is never called. The Business Logic Runtime reads the @Sum annotation, and executes the following logic in response to Child inserts, updates and deletes.

Determine whether adjustment(s) are required

The Business Logic Engine analyzes the Child changes to determine when adjustments are required:

Note: If none of the conditions below are met, no adjustment (or access) of the Parent is made.
Condition Example
Summed Attribute Changes PurchaseOrder.AmountUnPaid in our example.
Note this change may itself be the Forward Chained result of other derivations, as described above
Qualification Condition Changes Purchaseorder.IsReady in our example
Foreign Key Changes Purchaseorder is assigned to a different customer.
Note this will result in adjustments to the old and new parent (Customer)
Child is inserted or deleted

Execute Adjustment (or Recompute)

If the prior step determines the parent must be altered, the parent is retrieved. The parent is not retrieved (the SQL is pruned).

How the parent change is made can occur in three different ways


Algorithm Description Used When
Adjustment: this is the Best Practice since it performs best by avoiding an aggregate SQL
  1. The system computes the difference between the old/new summed field
  2. Adds that difference to the Parent summed field

parent field is not transient

In Memory: use for transient attributes with many sums/counts along the same relationship
  1. All the child rows are read into memory (they may already be in cache, e.g., for another sum/count already processed)
  2. The Business Logic Engine then sums the relevant attributes
InMemory is used if so specified
SQL: use for transient attributes and Parent can have many children
  1. A Select Sum... is issued for each aggregate
used for transient attributes and InMemory is not specified

Forward Chain to Parent Logic

Once the parent is altered, the Business Logic Engine must Forward Chain to run the Parent's logic. This process can nest several levels as explained in Context:

  • The Client inserts, updates or deletes a LineItem that results in a change to Amount
    • That summed field change adjusts Purchaseorder.AmountTotal
      • Purchaseorder Business Logic Formulas are executed, resulting in a change to Purchaseorder.AmountUnPaid
        • That summed field change adjusts Customer.Balance
          • That summed field change executes Customer Logic, e.g. to test constraints

From this description, note a transaction may result in multiple executions of the Business Logic for a specific row. Also note that Hibernate caching means that the adjustment nesting above (which may occur for each Lineitem in a Purchaseorder) will read/adjust the Purchaseorder and Customer only once.


Performance: pruning and adjustment

Sum operations are critical to performance since they can involve many related child rows.  The system provides important optimizations:

  1. Pruning: sum operations fire only when there are changes to the summed attribute, the qualification condition, or the foreign key.  Sum logic is pruned when updates do not affect any of these - for example, changing an orders date does initiate sum processing for the balance.

  2. Adjustment: The Customer.Balance rule is optimized to minimize SQLs by using adjustment logic. The system does 'not' issue a select sum query; instead, one row updates occur for each affected parent.

Adjust logic occurs for persistent (not transient) aggregates.  It is therefore a Best Practice to make sums and counts persistent - a performance denormalization (ala relational index).


Automatic Re-use over related transactions

The Customer.Balance rule, while declared for Place Order, is encapsulated into the Order Domain Object, so that it is automatically re-used over all these related transactions

  1. Delete Order - the balance is reduced
  2. Make Order Ready - the balance is increased
  3. Pay Order - the balance is reduced
  4. Reassign Order to a new customer - new customer balance increased, old balance decreased (for IsReady Orders)
  5. All the various cases of Change Order:
    1. Change a Line Item Quantity
    2. Change a Line Item Part
    3. Add a Line Item
    4. Delete a Line Item

Notes

Be aware of the following.

Multiple Relationship Disambiguation

Defaults and Alterability

Unlike Formulas where client code can alter values, the semantics for sums/counts preclude direct client setting of values. You can control how this performs with the property aggregateDefaultOverride in SBLConfig.properties:

  • Missing or non-negative means ignore updates, and force values to 0 on insert
    This option can reduce exceptions when introducing Business Logic into projects that already directly manipulate sums and counts - the system will simply manage these per your rule specification, ignoring program code
  • Negative value means throw exception
    Specifying a value such as n or no will raise an exception; this forces clarity among client applications 

Database Initialization


Adjustment logic depends on the database containing the correct values.  Therefore, when you create or alter a non-transient sum definition, you must create the column in your database, and initialize it to the correct value.  Consider SQL update statements such as:

update customer 
set customer.balance =
  (select sum(Purchaseorder.AmountUnPaid) 
  from Purchaseorder
  where (purchaseorder.customerName=customer.name))
where name > " "
Comments