Logic Bank Now Extensible: Drive 95% Automation Even Higher

Logic Bank introduces spreadsheet-like rules to automate transaction logic for databases – multi-table constraints and derivations, and actions such as sending messages or emails. It can reduce 95% of your transactional logic by 40X. 

It is based on Python and SQLAlchemy, an Object Relational Mapper (ORM). It's open source on GitHub.

Problem: Transactional Logic Is Large, Code-Intensive

Transactional backend logic takes up a significant position of database systems, often nearly half. Legacy approaches - triggers, events - are code-intensive, error prone, and difficult to maintain.

But the Cocktail Napkin Is Clear... and Concise

We’ve all seen how a clear specification - just a cocktail napkin spec - balloons into hundreds of lines of code.

Solution: Spreadsheet-Like Rules for Transactional Logic

This leads to the key design objective for Logic Bank:

Introduce Spreadsheet-like Rules to
Automate the "Cocktail Napkin Spec"

The next section shows how to declare, activate and execute rules.

1. Spreadsheet-Like Rules Automate the "Cocktail Napkin Spec"

Logic Bank relies on SQLAlchemy, which requires an object model: a set of Python classes describing your database. Your model is consumed by SQLAlchemy and by your IDE (to provide code completion for accessing attributes).

SQLAlchemy can create the database from the model, or you can use sqlacodegen to create the model from the database. Then, pip install LogicBase and you're ready to declare, activate, and execute rules.

Declare Spreadsheet-Like Rules in Python

Below is the implementation of our cocktail napkin spec (blue rectangle, upper right) to check credit:

In the diagram above, the rules are declared on lines 40-49 – 1:1 with the spec. These five rules replace several hundred lines of code (40X), as shown here. 

Activate

Activate the rules while opening your database:

session_maker = sqlalchemy.orm.sessionmaker()

session_maker.configure(bind=engine)

session = session_maker()

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

The activate function:

• invokes your declare_logic() function (shown in the screen shot above) to load your rules.
• verifies them (i.e., checks for cycles).
• installs the Logic Bank event handlers to listen to SQLAlchemy before_flush events.

Execute Like a Spreadsheet

Rules are declarative: you do not need to call them or order them. Internally, the Logic Bank event handlers call the Logic Bank rules engine to execute your logic. For more on declarative, click here.

We often use the spreadsheet metaphor to describe rule operation. Just as a spreadsheet reacts to inserts, updates, and deletes to a summed column, rules automate adding, deleting, and updating orders. This is how five rules represent the same logic as 200 lines of code.

2. Event Handlers - Ad Hoc Python Functions

While 95% automation is impressive, it's not enough. Virtually all systems include complex database logic that cannot be automated, as well as non-database logic such as sending emails or messages.  That leads to the second key design objective:

Rules must be complemented by code for extensibility,
and manageability (debugging, source control, etc). 

Python event handlers are straightforward. You declare your event handler function (line 51, above):

xxxxxxxxxx

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

Event handlers are Python functions (lines 32-38):

xxxxxxxxxx

def congratulate_sales_rep(row: Order, old_row: Order, logic_row: LogicRow):

    if logic_row.ins_upd_dlt == "ins":  # logic engine fills parents for insert

        sales_rep = row.SalesRep  # type : Employee

        if sales_rep is None:

            logic_row.log("no salesrep for this order")

        else:

            logic_row.log(f'Hi, {sales_rep.Manager.FirstName}, congratulate {sales_rep.FirstName} on their new order')

The rules engine calls it, providing arguments:

• row - an instance of a SQLAlchemy mapped class (here, Order).

  • Use code completion to access attributes (column values).
  • Observe SQLAlchemy rows provide access to related rows (e.g., sales_rep.Manager.FirstName).

• old_row - prior contents.

• logic_row - wraps row and old_row, and includes useful states such as nest_level, ins_upd_dlt, etc. It is also the rule engine executor for the wrapped row instance, via methods for insert/update and delete.

This is just a simple example that logs a message... a real event might generate a RESTful call, or send an email message.

In the screenshot, the red dot in the left margin is a breakpoint (line 35), illustrating that you can use standard Python debugger services – breakpoints, variable inspection, single step, etc.

Event Types: Early, (Normal) Event, Commit Event

There are actually three types of events. The Logic Bank rules engine invokes them at different points in logic execution, as follows.

SQLAlchemy before_flush events expose a list of rows altered by the client to the Logic Bank rule engine. For each row, Logic Bank creates a logic_row, and calls the insert/update/delete method as appropriate. In the course of rule execution, it invokes Early Events and normal Events:

• Early Events run prior to rule execution.
• Events run after rule execution.

After all the rows have been processed, the rule engine cycles through them again, executing any CommitEvents. Since this is subsequent to row logic, all derivations have been performed, including multi-table rules. In particular, parent rows reflect child sums and counts.

Enough?

At this point, we can define rules for 95% of our logic, and code Python Event Handlers for the rest. For most people, that should be enough...

Not us.

There are still some tedious things we'd like to automate...

Generic Event Handlers

Imagine we wish to set date/time stamps. Our database follows naming conventions, so we want to set CreatedOn for all tables that have this column.

We could certainly write a rule or an event. But we may have hundreds of tables, and we want to do this just once. The problem is, events and rules are "tied" to a particular table (mapped class).

To enable us to address this with a single event, Logic Bank provides a rule called early_row_event_all_classes. This is a Generic Event Handler that applies to all tables, typically driven by meta data.

Let's use it to address date/time stamps. In nw/logic/logic.py, you will find:

xxxxxxxxxx

def handle_all(logic_row: LogicRow):

    row = logic_row.row

    if logic_row.ins_upd_dlt == "ins" and hasattr(row, "CreatedOn"):

        row.CreatedOn = datetime.datetime.now()

        logic_row.log("early_row_event_all_classes - handle_all sets 'Created_on"'')

Rule.early_row_event_all_classes(early_row_event_all_classes=handle_all)

This illustrates how you can provide reusable services (e.g., time/date stamping), based on your naming conventions.

3. Extended Rules

Ok, good, but what if we want a new rule altogether? After all, the trick of Logic Bank is to identify patterns, and then create rules to automate them. The fun should not be restricted to Logic Bank authors – it should be enabled for everybody.

Here's how.

In this example, we want to define a reusable audit rule that monitors Employee changes:

if the ```Salary``` or ```Title``` are changed,
insert a row into ```EmployeeAudit```,
initialized with like-named ```Employee``` attributes.

Logic Bank enables providers to create Rule extensions that extend the three Event classes and publish them as new rule types. Here, we utilize such a rule called nw_copy_row:

xxxxxxxxxx

NWRuleExtension.nw_copy_row(copy_from=Employee,

                            copy_to=EmployeeAudit,

                            copy_when=lambda logic_row:

                                logic_row.are_attributes_changed([Employee.Salary, Employee.Title]))

Our rule extension operates are summarized in the diagram:

1. Our logic.py rule declaration (upper-left code window) calls...

2. nw.rule_extensions.py (lower-left code window), which invokes the constructor of our extended rule...

3. nw_copy.py (upper-right panel, above), which is the implementation of our extended rule.

Let's review some key aspects of this technique in the sections below.

Rule Discovery

Creating an extended rule is of little value if colleagues don't find it. Emails get lost. The new rule needs to be integrated into their IDE.

nw.rule_extensions.py "publishes" the rule, and provides documentation, in conjunction with the Python/IDE (here, PyCharm – press Ctl+Space for a list of rules, hover for documentation). This enables you to build multiple rules, and make them all IDE-discoverable as shown below, just by adding these helper functions.

Rule Definition

The NWCopyRow constructor (__init__) creates a rule instance of our “audit” rule, and saves the rule parameters in the rule instance (here, copy_to etc).

Note this class extends Event.

It then calls the superclass constructor. This logs the rule into the “rule bank,” an internal data structure that stores the rules for subsequent rule execution on session.commit().

Rule Execution

Since our rule extends Event and has been logged into the "rule bank," it's visible to the Logic Bank rule engine. That means its execute() instance method is called when events are executed, with the logic_row as a parameter.

Our code thus executes at runtime, with access to:

• with rule parameters available as instance variables.
• the row (wrapped in logic_row).

For example, the NWCopyRow code below executes the lambda function self.copy_when (saved by the constructor in the rule instance), passing  logic_row as an argument:

xxxxxxxxxx

copy_from = logic_row

do_copy = self.copy_when(copy_from)

Powerful Example: Allocation

Copy was a pretty simple example. The objective was to illustrate the technique, so that you can detect your own patterns, and devise reusable solutions, and publish them to your team.

In fact, the Logic Bank runtime engine includes copy_row in the runtime. That means you have access to the audit service when you pip install logicbank:

xxxxxxxxxx

RuleExtension.copy_row(copy_from=Employee,

                       copy_to=EmployeeAudit,

                       copy_when=lambda logic_row:

                          logic_row.are_attributes_changed([Employee.Salary, Employee.Title]))

The Logic Bank runtime also includes a much more powerful example: Allocation.

The entire implementation is in payment_allocation/logic/rules_bank.py:

xxxxxxxxxx

def unpaid_orders(provider: LogicRow):

    """ returns Payments' Customers' Orders, where AmountOwed > 0, by OrderDate """

    customer_of_payment = provider.row.Customer

    unpaid_orders_result = provider.session.query(Order)\

        .filter(Order.AmountOwed > 0, Order.CustomerId == customer_of_payment.Id)\

        .order_by(Order.OrderDate).all()

    return unpaid_orders_result

def declare_logic():

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

    Rule.formula(derive=Order.AmountOwed, as_expression=lambda row: row.AmountTotal - row.AmountPaid)

    Rule.sum(derive=Order.AmountPaid, as_sum_of=PaymentAllocation.AmountAllocated)

    Rule.formula(derive=PaymentAllocation.AmountAllocated, as_expression=lambda row:

        min(Decimal(row.Payment.AmountUnAllocated), Decimal(row.Order.AmountOwed)))

    RuleExtension.allocate(provider=Payment,

                           recipients=unpaid_orders,

                           creating_allocation=PaymentAllocation)

Details are provided here, but to summarize – when we insert a payment:

• the last rule allocates the payment to a set of outstanding orders.
• the other rules determine the AmountAllocated and adjust the Customer.Balance.
• unpaid_orders is a function that returns the recipient orders

A Proven Technology, Gaining Traction

While you may not have encountered Logic in the past, it's been successful over a number of technology generations:

• It was the core technology behind PACE from Wang Labs, with over 6,500 installed sites.
• It was also the core technology behind Versata, a J2EE platform with over 700 customers. Versata went public with an IPO in excess of $3 billion, with the backing of the founders of Microsoft, SAP, Ingres, and Informix.
• It is currently available in CA Live API Creator.

This particular implementation has been available for 2 months. The DZone articles have garnered over 28,000 reads, and the package has been downloaded over 4,000 times (blue line). FAB Quick Start, a companion project for Instant Web Apps, has 1,000 downloads (orange line).

Author Note: I am having a ball with Open Source, but it's kind of... lonely.  I'd love to hear from you - what you think of Logic Bank, how you are using it, questions, etc. Don't worry, no sales rep will call. There aren't any.

Summary

So, to summarize:

Rules can reduce 95% of your backend logic by 40X.
Rules are extensible with Python - drive 95% up.

Python has been enjoying massive growth in popularity. Maybe you've already been considering it as a backend for web apps and web services.

Logic Bank makes Python an option you can't afford not to consider.  It's open source on GitHub, and you can explore it in minutes.