valhuber/python-rules

Obsolete - see Logic-Bank - Python rules for sqlalchemy transactions (multi-table derivations, constraints)

Obsolete

See Logic Bank

Rules to Automate Business Logic

Transaction Logic: half the system

For most transaction-oriented database applications, backend database logic is a substantial portion of the effort. It includes multi-table derivation and constraint logic, and actions such as sending mail or messages.

Such backend logic is typically coded in before_flush events, database triggers, and/or stored procedures. The prevailing assumption is that such domain-specific logic must surely be domain-specific code.

Problem: Code-intensive - time-consuming, error prone

The problem is that this is a lot of code. Often nearly half the effort for a transactional database-oriented systems, it is time-consuming, complex and error-prone.

Rules: 40X more concise, extensible, performant, manageable

This project introduces a declarative alternative: you specify a set of spreadsheet-like rules, which are then executed by a login engine operating as a plugin to sqlalchemy. As in a spreadsheet, there are dramatic gains in conciseness and clarity:

• 5 spreadsheet-like rules implement the check-credit requirement (shown below). The same logic requires 200 hundred of lines of code (shown here) - a factor of 40:1.

• This can represent meaningful improvements in project delivery and agility. Experience has shown that such rules can address over 95% of the backend logic, reducing such logic by 40X.

Skeptical? You should be. There are many types of rule engines, and experience has shown they are not appropriate to transaction processing. For more information, see Rule Engines.

This implementation is specifically designed to meet the demands of transaction processing:

• Scalable - rule execution is optimized to eliminate and optimize SQL
• Extensible - use Python to extend rule automation
• Manageable - use Python tools for code editing, debugging, code management, etc
• Agile - iterate just by changing the rules; the system re-orders execution with automatic dependency management, and re-optimizes

For more information, see the Rules Engine Overview.

Architecture

Architecture

1. Your logic is declared as Python functions (see example below).

2. Your application makes calls on sqlalchemy for inserts, updates and deletes.

   • By bundling transaction logic into sqlalchemy data access, your logic is automatically shared, whether for hand-written code (Flask apps, APIs) or via generators such as Flask AppBuilder.

3. The python-rules logic engine handles sqlalchemy before_flush events on Mapped Tables

4. The logic engine operates much like a spreadsheet: watch for changes at the attribute level, react by running rules that referenced changed attributes, which can chain to still other attributes that refer to those changes. Note these might be in different tables, providing automation for multi-table logic.

Logic does not apply to updates outside sqlalchemy, nor to sqlalchemy batch updates or unmapped sql updates.

Declaring Logic as Spreadsheet-like Rules

To illustrate, let's use an adaption of the Northwind database, with a few rollup columns added. For those not familiar, this is basically Customers, Orders, OrderDetails and Products, as shown in the diagrams below.

Declare rules using Python

Logic is declared as spreadsheet-like rules as shown below from nw/nw_logic/nw_rules_bank.py, which implements the check credit requirement:

def activate_basic_check_credit_rules():
    """ Check Credit Requirement:
        * the balance must not exceed the credit limit,
        * where the balance is the sum of the unshipped order totals
        * which is the rollup of OrderDetail Price * Quantities:
    """

    Rule.constraint(validate=Customer, as_condition=lambda row: row.Balance <= row.CreditLimit,
                    error_msg="balance ({row.Balance}) exceeds credit ({row.CreditLimit})")
    Rule.sum(derive=Customer.Balance, as_sum_of=Order.AmountTotal,
             where=lambda row: row.ShippedDate is None)  # *not* a sql select sum
    
    Rule.sum(derive=Order.AmountTotal, as_sum_of=OrderDetail.Amount)
   
    Rule.formula(derive=OrderDetail.Amount, as_expression=lambda row: row.UnitPrice * row.Quantity)
    Rule.copy(derive=OrderDetail.UnitPrice, from_parent=Product.UnitPrice)

The specification is fully executable, and governs around a dozen transactions. Let's look at Add Order (Check Credit) - enter an Order / OrderDetails, and rollup to AmountTotal / Balance to check CreditLimit.

This representatively complex transaction illustrates common logic execution patterns, described below.

Activate Rules

To test our rules, we use nw/trans_tests/add_order.py. It activates the rules using this import:

from nw.nw_logic import session  # opens db, activates logic listener <--

This executes nw/nw_logic/__init__.py, which sets up the rule engine:

by_rules = True  # True => use rules, False => use hand code (for comparison)
if by_rules:
    rule_bank_setup.setup(session, engine)     # setup rules engine
    activate_basic_check_credit_rules()        # loads rules above
    rule_bank_setup.validate(session, engine)  # checks for cycles, etc
else:
    # ... conventional after_flush listeners (to see rules/code contrast)

This is what replaces 200 lines of conventional code. Let's see how it operates.

Logic Execution: Watch, React, Chain

The engine operates much as you might imagine a spreadsheet:

• Watch - for inserts, deletes, and updates at the attribute level

• React - derivation rules referencing changes are (re)executed (forward chaining rule inference); unreferenced rules are pruned.

• Chain - if recomputed values are referenced by still other rules, these are re-executed. Note this can be in other tables, thus automating multi-table transaction logic.

Let's see how.

Example: Add Order - Multi-Table Adjustment, Chaining

The Add Order example illustrates chaining as OrderDetails are added:

1. The OrderDetail.UnitPrice is referenced from the Product so it is copied

2. OrderDetails are referenced by the Orders' AmountTotal sum rule, so AmountTotal is adjusted (chaining)

3. The AmountTotal is referenced by the Customers' Balance, so it is adjusted (chaining)

4. And the Credit Limit constraint is checked (exceptions are raised if constraints are violated)

All of the dependency management to see which attribute have changed, logic ordering, the SQL commands to read and adjust rows, and the chaining are fully automated by the engine, based solely on the rules above. This is how 5 rules represent the same logic as 200 lines of code.

Let's explore the multi-table chaining, and how it's optimized.

Optimizations: Multi-table Adjustment (vs. nested sum queries)

The sum rule that "watches" OrderDetail.AmountTotal is in a different table: Orders. So, the "react" logic has to perform a multi-table transaction. And this means we need to be careful about performance.

Note that rules declare end conditions, enabling / obligating the engine to optimize execution (like a SQL query optimizer). Consider the rule for Customer.Balance.

As in commonly the case (e.g. Rete engines, some ORM systems), you may reasonably expect this is executed as a SQL select sum.

It is not.

Instead, it is executed as an adjustment: as single row update to the Orders balance. This optimization dramatically reduces the SQL cost, often by orders of magnitude:

• select sum queries are expensive - imagine a customer with thousands of Orders.

• Here, it's lots worse, since it's a chained sum, so computing the balance requires not only we read all the orders, but all the OrderDetails of each order.

See here for more information on Rule Execution.

An Agile Perspective

The core tenant of agile is working software, driving collaboration, for rapid iterations. Here's how rules can help.

Working Software Now

The examples above illustrate how just a few rules can replace pages of code.

Collaboration - Running Screens

Certainly business users are more easily able to read rules than code. But honestly, rules are pretty abstract.

Business users relate best to actual working pages - their intepretation of working software. The fab-quick-start project enables you to build a basic web app in minutes.

This project has already generated such an app, which you can run like this:

cd nw_app
export FLASK_APP=app
flask run

Login: user = admin, password = p

Iteration - Automatic Ordering

Rules are self-organizing - they recognize their interdependencies, and order their execution and database access (pruning, adjustments etc) accordingly. This means:

• order is independent - you can state the rules in any order and get the same result

• maintenance is simple - just make changes, additions and deletions, the engine will reorganize execution order and database access, automatically

Installation

Contents

This is the development project for python-rules:

• Explore project contents here
• This project also includes a sample application used to test and illustrate rules. Importantly, it inclues comparisons of Business logic, both by-code and by-rules,

Stand-alone projects illustrating how to use python-rules can be found here. These more closely resemble your use of python-logic, including usage of the pip mechanism to install.

Installation Procedure

To get started, you will need:

• Python3.8 (Relies on from __future__ import annotations, so requires Python 3.8)

  • Run the windows installer; on mac/Unix, consider using brew

• virtualenv - see here (e.g., pip install virtualenv)

• An IDE - any will do (I've used PyCharm and VSCode, install notes here) - ide will do, though different install / generate / run instructions apply for running programs

Issues? Try here.

Using your IDE or command line:

cd your-project
virtualenv venv
source venv/bin/activate

Testing

You can run the .py files under nw/trans_tests, and/or run the FAB application as described above.

Status: Running, Under Development

Essential functions running on 9/6/2020: multi-table transactions - key paths of copy, formula, constraint, sum and event rules.

Not complete, under active development.

Ready to explore and provide feedback on general value, and features.