Tutorial

Orientation

This exercise explores the smallest possible example of multi-table logic, to illustrate the use of Logic Bank. It's interactive - you'll implement some rules, with virtually no Python background required.

Background - Logic Bank

Use Logic Bank to govern SQLAlchemy update transaction logic - multi-table derivations, constraints, and actions such as sending mail or messages. Logic consists of both:

• Rules - 40X more concise using a spreadsheet-like paradigm, and

• Python - control and extensibility, using standard tools and techniques

Logic Bank is based on SQLAlchemy - it handles before_flush events to enforce your logic.

Sample Problem

We will address a 2 table application - Customers and Orders:

• Add an Order to a Customer
• Declare rules to
  • Adjust the Customer Balance, and
  • Verify the Balance <= 2000

Verify Readiness

1. Install as described in the Readme

2. Verify that the following runs, but fails:

cd examples/tutorial/tests
python run_tutorial.py

It failed our assert that the balance increased. This is expected, because there are no rules to derive the balance.

Exercise Objectives

Let's fix that. In the following sections, we'll see how to:

1. Declare Rules
2. Activate Rules
3. Execute Rules
4. Extend Rules

1. Declaring Rules

Enable your logic in examples/tutorial/logic/tutorial_logic.py by changing False to True, as shown below:

    explore_rules = True   # set True to explore rules
    if explore_rules:
        Rule.constraint(validate=Customer,
                        error_msg="balance ({row.Balance}) exceeds 2000)",
                        as_condition=lambda row: row.Balance <= 2000)

        Rule.sum(derive=Customer.Balance, as_sum_of=Order.AmountTotal)

Normally, you build rules with an IDE (e.g., PyCharm) using code completion:

2. Activating Rules

examples/tutorial/tests/run_tutorial.py illustrates the code that opens the database and registers the rules (see the yellow highlight):

LogicBank.activate(session=session, activator=declare_logic)

Note rules are based on your data model. Important considerations apply, as described in the Logic Bank Wiki.

3. Rule Execution - Like a Spreadsheet

You don't invoke the rules directly; the Logic Base Rule Engine handles SQLAlchemy before_flush events to

• watch for changes to referenced attributes
• react by running the referencing rules
• changes can chain to other rules

You can re-run the test, which should now succeed.

In the screen shot above, note the log, which shows all the rules that fire.

Rule Execution - Sum

Like spreadsheet cell formulas, derivation rules (like sum) are end-conditions that the system will ensure are met on commit:

Rule.sum(derive=Customer.Balance, as_sum_of=Order.AmountTotal)

Our sum rule watches Orders for Inserts, updates and deletes. In our test, the rule reacts to the insert by adjusting the customer balance.

Automatic Reuse

Just as a spreadsheet reacts to inserts, updates and deletes to a summed column, rules automate adding, deleting and updating orders. Let's see this in action.

Find this line in examples/tutorial/tests/run_tutorial.py, and change False to True:

show_reuse = False  # set True to observe reuse in console log

This will update to inserted order to reduce the Order.AmountTotal. Now re-run the test; in the console log below, note how the Customer.Balance is reduced - via the same rule that caused it to be increased, above:

Logic Phase:		BEFORE COMMIT          						 - 2021-01-05 13:25:43,925 - logic_logger - DEBUG
Logic Phase:		ROW LOGIC (sqlalchemy before_flush)			 - 2021-01-05 13:25:43,925 - logic_logger - DEBUG
..Order[11081] {Update - client} Id: 11081, CustomerId: ALFKI, OrderDate: None, AmountTotal:  [500.00-->] 490.00  row@: 0x109479340 - 2021-01-05 13:25:43,925 - logic_logger - DEBUG
....Customer[ALFKI] {Update - Adjusting Customer} Id: ALFKI, CompanyName: Alfreds Futterkiste, Balance:  [1516.00-->] 1506.00, CreditLimit: 2000.00  row@: 0x1095702e0 - 2021-01-05 13:25:43,926 - logic_logger - DEBUG
Logic Phase:		COMMIT   									 - 2021-01-05 13:25:43,926 - logic_logger - DEBUG
Logic Phase:		FLUSH   (sqlalchemy flush processing       	 - 2021-01-05 13:25:43,926 - logic_logger - DEBUG

Rule Execution - Constraint

Constraint rules are boolean expressions that must be true for transactions to commit. Find this line in examples/tutorial/tests/run_tutorial.py, and change the 500 to 1000:

amount_total = 500  # 500 should work; change to 1000 to see constraint fire

Re-run the test; it should now fail with a constraint exception.

Set it back to 500 to prepare for the next test.

Dependency Based Ordering

Note the constraint fired after the sum. The system understands rule dependencies, and orders their execution accordingly.

This pays off in maintenance. No more hours of archaeology to study existing code, and hope you understand the dependencies. You just add / change rules, and the system ensures they are called, and in the proper order.

4. Extend Rules with Python

Declarative Rules address over 95% of database-oriented transaction logic. That said, you still need to a procedural language for particularly complex logic, or to address non-database aspects such as sending email or messages.

Logic consists of rules, and standard Python code using a familiar event paradigm. Let's introduce a stub to send email if an orders' AmountTotal decreases.

Enable your event in examples/tutorial/logic/tutorial_logic.py by changing False to True, as shown below:

    explore_extensibility = True  # set True to explore extensibility
    if explore_extensibility:

        def follow_up(row: Order, old_row: Order, logic_row: LogicRow):
            if logic_row.ins_upd_dlt == "upd":
                if logic_row.row.AmountTotal < logic_row.old_row.AmountTotal:
                    print("\nStub: send email to sales manager to follow up\n")

        Rule.commit_row_event(on_class=Order, calling=follow_up)

You can set a breakpoint in your Python code. Re-running the test should produce the following log:

Logic Phase:		BEFORE COMMIT          						 - 2021-01-05 18:00:29,857 - logic_logger - DEBUG
Logic Phase:		ROW LOGIC (sqlalchemy before_flush)			 - 2021-01-05 18:00:29,857 - logic_logger - DEBUG
..Order[11081] {Update - client} Id: 11081, CustomerId: ALFKI, OrderDate: None, AmountTotal:  [500.00-->] 490.00  row@: 0x10a67e2b0 - 2021-01-05 18:00:29,857 - logic_logger - DEBUG
....Customer[ALFKI] {Update - Adjusting Customer} Id: ALFKI, CompanyName: Alfreds Futterkiste, Balance:  [1516.00-->] 1506.00, CreditLimit: 2000.00  row@: 0x10b2d0430 - 2021-01-05 18:00:29,858 - logic_logger - DEBUG
Logic Phase:		COMMIT   									 - 2021-01-05 18:00:29,858 - logic_logger - DEBUG
..Order[11081] {Commit Event} Id: 11081, CustomerId: ALFKI, OrderDate: None, AmountTotal:  [500.00-->] 490.00  row@: 0x10a67e2b0 - 2021-01-05 18:00:29,858 - logic_logger - DEBUG

Stub: send email to sales manager to follow up

Key Concepts

This completes our simple example. It has illustrated some key concepts of logic, as described below.

Time to Market, Agility

In this more complex example, observe that

 5 rules represent the same logic as 200 lines of code

This is because rules are declarative, providing

• Automatic Reuse: we might have designed the sum rule to address adding orders. But, like a spreadsheet, they are reused to address deleting orders, updating Order Amounts, etc, as we saw above

• Automatic Ordering: rule execution is automatically ordered by dependencies, eliminating the maintenance headache of where to introduce new logic so that it is always called, in the proper order

Complexity Handling

In this simple example, the sum rule changes leads to (chains to) the execution of the constraint rule. Chaining is an extremely powerful mechanism that enables you to address complex transactions with a small number of simple rules.

You can explore more complex examples here.

Extensibility

In this example, we introduced a simple event handler. To learn more, see extensibility, here.

Just as with regular Python, you can set breakpoints, perform logging, etc.

Scalability

Scalability depends on SQL tuning. Logic Base addresses this with techniques such as:

• Adjustments - in the example above, observe the system added 500 to the existing Balance with a 1 row adjustment update - not an expensive Select sum query

• Pruning - parent (Customer) updates are eliminated altogether if there is no change to the summed field, the qualification, or the foreign key.

You can explore scalability, here.