Home

Abstract

Logic Bank is a Python package for SQLAlchemy ORM that automates transaction logic - complex multi-table derivations, constraints, and actions such as sending mail or messages. Logic consists of:

• Rules - 40X more concise than legacy code, using a spreadsheet-like paradigm, and
• Python - control and extensibility, using standard tools and techniques

Background: Transaction Logic is Slow and Error Prone

If you've ever written a web app on top of a database, you know how much work it is.  About half the effort is in the UI, and about half in backend transaction logic - complex multi-table derivations, constraints, and actions such as sending mail or messages.

There are ORM frameworks such as SQLAlchemy that simplify database access, but the domain logic itself remains domain-specific code.  You might code it in triggers and stored procedures, in SQLAlchemy events, or in UI controllers.  However you do it, such legacy approaches are slow to build and change, tedious, and error-prone -- business friction.

But the Cocktail Napkin Spec is Clear

It's easy to dismiss this business friction -- "domain-specific logic requires domain-specific code". But what's hard to dismiss is that we can state specifications that are clear, concise and complete. 

Consider this specification of the check credit requirement, typical of so many transactions - we roll up some values and apply some constraints:

Our cocktail napkin spec is really nothing more than a set of spreadsheet-like rules that govern how to derive and constrain our data.  And by conceiving of the rules as associated with the data (instead of a UI button), we address all of these Use Cases:

• add order
• ship / unship order
• delete order
• assign order to different customer
• re-assign an Order Detail to a different Product, with a different quantity
• add/delete Order Detail

The legacy implementation of this spec turns our 5 simple, transparent rules into over 200 lines of code (view them here):

And that's the problem Logic Bank is trying to solve: let's make these rules executable.

Logic Bank: The Executable Cocktail Napkin

We're introducing Logic Bank, an open source Python package that automates a shocking amount of this tedium, and goes far beyond the usual CRUD-in-a-box approach offered by other solutions.

Using Logic Bank, you can create -- and customize -- a full-blown back end on top of your database in (literally) a minute, and declare advanced back-end behavior using reactive spreadsheet-like rules.  You can even build a basic web app in literally minutes.  And your logic-aware database access layer is the perfect foundation for creating APIs and custom UIs.

The level of agility offered by this approach is, we believe, unparalleled, with sufficient potential to influence your choice of technology stack.

Why? Meaningful Reduction in Time to Market, Cost

Since transaction logic is nearly half of most database systems, and rules automate over 95% of the logic 40X more concisely, Logic Bank can return remarkable savings in time and cost.  

These are substantial claims.  Let's put them to the test.  First, let's see what you do to use Logic Bank, and how Logic Bank executes your logic.  Then, let's ask:

• Does it deliver - is the 40X real?
• Are There Automation Issues - many automation technologies have not met real world requirements for:
  • Extensibility - how do I address logic not automated with rules?
  • Scalability - does automation reduce performance?
  • Manageability - how do I debug and source-control logic?

What: Declare Logic as Spreadsheet-like Rules

To illustrate, we'll use the check credit example above.

Declare Rules using Python

Logic is declared as spreadsheet-like rules as shown below (lines 40-49) from this example, which implements the check credit requirement (the balance - a rollup of unshipped order totals - cannot exceed the credit limit).  This is exactly our cocktail napkin spec, expressed in Python (here shown in PyCharm, a Python IDE):

Activate

To activate the rules (declare_logic is the function shown in the screen above):

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

Execute - session.commit()

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.

Observe these are not simple single-field validations - they address complex multi-field, multi-table logic.

How: Logic Bank Execution

Logic Bank operates as a plugin to SQLAlchemy:

1. You declare logic as multi-table rules and Python (see "Declare Rules Using Python" as shown above).

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

3. Logic Bank handles SQLAlchemy before_flush events on Mapped Tables.

   • By injecting transaction logic into SQLAlchemy data access, your logic is automatically invoked.  So, it governs updates from hand-written code (Flask apps, APIs) or via generators such as Flask AppBuilder.

4. The logic engine operates much like a spreadsheet:

   • Watch - for inserts, deletes, and updates at the attribute level.
   • React - derivation rules referencing changed attributes are (re)executed (forward chaining rule inference); rules are pruned if their dependent data is not changed.
   • 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.

Logic  expressed in Logic Bank does not apply to updates outside SQLAlchemy, or to SQLAlchemy batch updates or unmapped sql updates.

Logic Execution: Watch, React, Chain

Let's see how.  Consider inserting the OrderDetails for an order: roll up to AmountTotal / Balance and _check credit:

The diagram illustrates chaining as each OrderDetail is inserted:

1. The OrderDetail.UnitPrice (copy, line 49) references Product, so inserts cause it to be copied

2. Amount (formula, line 48) watches UnitPrice, so its new value recomputes Amount

3. AmountTotal (sum, line 46) watches Amount, so AmountTotal is adjusted (more on adjustment, below)

4. Balance (sum, line 43) watches AmountTotal, so it is adjusted

5. And the Credit Limit constraint (line 40) is checked (exceptions are raised if constraints are violated, and the transaction is rolled back)

Check out more examples:

• Ship Order illustrates cascade, another form of multi-table logic
• Banking is a complex transaction using the command pattern

Technology Evaluation

We have 2 data points:

• the Logic Bank “Add Order” example, and
• technology heritage of Logic Bank

Logic Bank “Add Order” Example - Legacy Code Comparison

The 5 rules above represent the same logic as 200 lines of Python legacy code - view them here.  This 40X (5 rules vs. 200 lines) is proportional - even in a medium sized system, this is tens of thousands of lines of code.  Such code is a corporate liability - it reduces time to market, is costly to develop and maintain, and is not transparent to business users.

Automated Dependency Management, Automatic Reuse

This 40X is quite striking.  It comes from automated dependency management, and automatic reuse.

Rules automate all of the dependency management -- see which attributes have changed, logic ordering, the SQL commands to read and adjust rows, and chaining are fully automated by the engine, based solely on the rules above.

Note both approaches address not only "add," but also related transactions such as "update" and "delete."  In fact, these 5 rules automate all of these Use Cases listed above (add order, ship / unship order, re-assign order etc).

Such reuse over Use Cases is automatic in Logic Bank; in legacy approaches, solving all these Use Cases results in considerable code.  Or worse, errors -- it's easy to miss corner cases, e.g., “re-assign an Order Detail to a different Product with a different quantity” requires tricky adjustments to the Order.

Clarity - “What Not How” Makes Logic Transparent

Also, consider clarity.  The legacy code is not just tedious - all you can see is "how it works", obscuring the critical point:  "what is it trying to do?". 

By contrast, rules are an executable specification - the "what" is clear for maintenance, and for business users.  As Chris Date puts it in his book that champions the use of rules, the proper focus is What, Not How.

Technology Heritage - Real World Experience

It's always easy to cherry pick an example.  It's usually hard to have confidence that these results are borne out in real projects.

But in this case, there is some interesting evidence.  Logic Bank is built on a technology with a proven track record of success:

• Wang Labs - the PACE DBMS enforced these rules, highly valued by over 6500 customers, and was regarded as one of Wang's top products

• Versata - a J2EE startup backed by the founders of Microsoft, SAP, Ingres and Informix, Versata had a 2000 IPO of $3.4B, with over 700 customers

  • These systems tending to be large (typically 200-800 tables), with high demands for throughput and complexity

  • Studies of a dozen systems indicated over 97% of the backend logic was automated by rules, and that each rule represented about a page of code (generally consistent with our 5 vs. 200 example)

• CA Live API Creator - over 100 customers, where rules were integrated with API creation and execution.

The Versata metrics are particularly interesting:

• Backends are nearly half the project
• Rules can automate over 95% of your logic
• With a compression factor of 40:1

Logic Bank brings the same technology to the Python / open source community.  Let's see how this implementation addresses Extensibility, Scalability, and Manageability.

Extensibility: Standard Python

While rules automate much, they don't automate everything. Every app has elements that integrate with outside systems, such as sending mail or messages.

The screenshot above (see heading - "Declare Rules Using Python") includes a rule on line 51 that invokes the Python function on line 32.  So, whatever you can't do in rules, you have the full power of Python.

Scalability: Automatic Pruning and Optimization

We typically think of scalability in terms of clustering.  And containers like Flask support that.

But all the clustering in the world cannot cover inefficient database access.

Let’s face it, many automation initiatives have failed to provide effective automation for transaction logic, including Rete-based rule engines, and Object Relational Managers (ORMs).  The primary issue has been scalability: excessive and expensive SQL queries.

However, Logic Bank is specifically designed to address this.  Let’s see how.

Optimizations: Adjustment (vs. nested sum queries)

The Customer.Balance sum rule (line 43) "watches" changes to Order.AmountTotal - a different table.  So, the "react" logic has to perform a multi-table transaction, which brings our performance issue to bear.

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

In Logic Bank, it is not.

Instead, Logic Bank optimizes it as an adjustment: as single row update to the Customers 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.

• In this example, it's particularly critical, since it's a chained sum. So, a Rete approach to computing the balance would require not only reading all the orders, but all the OrderDetails of each order.  This is often discovered late in a project when testing live data volumes, and requires substantial recoding.

In addition, Logic Bank provides automatic pruning. If an update occurs that does not affect the sum, no SQL is issued at all.  Contrast this to ORM / Rete engines, where the typical approach is to run all the sum queries, all the time.

Manageability: Standard Language, Tools

Code Management: Standard Language, IDE Enabled

The "Declare Rules" screenshot shows that rules are defined in Python.  You can use standard source control systems and procedures to manage your logic.  There are no databases, xml or json files to configure and manage.

Python has recently been extended with type support, enabling your IDE to provide code completion, and spot many errors.  Logic Bank uses this type support (see line 32):

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

Debugging: Standard Debugger, Logic Logging

The "Declare Rules" screenshot also illustrates that you can stop in your rule logic (the red dot on line 29), and use your IDE (here, PyCharm) to see variables, step through execution, etc.

Logging is performed using standard Python logging, with a logger named logic_logger. Use info for tracing, and debug for additional information (e.g., all declared rules are logged).

In addition, Logic Bank logs all rules that fire, to aid in debugging.  If we run add_order.py, we get the following log (see the lower pane in the following screenshot):

• Each line represents a rule execution, showing row state (old/new values), and the {reason} that caused the update (e.g., client, sum adjustment)
• Log indention shows multi-table chaining

An Agile Perspective

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

Working Software Now

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

Collaboration: Working Screens, with Logic

Certainly business users are more easily able to read rules than code. But let's face it, rules are still pretty abstract.

Business users relate best to actual working screens - their interpretation of working software. The fab-quick-start project enables you to build a basic web app in minutes, like this:

Iteration Cycles: Automatic Logic Ordering

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

• errors are reduced - you can declare 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

Logic as a Corporate Asset

Legacy code is a liability: costly to write, hard to change, and not transparent to Business Users.  And tightly bound to a technology platform (language, architecture, etc).

Logic is a corporate asset: reduced time to market, reduced cost, easier to change, and transparent to Business Users.  Logic helps you cope with business change.

And since it is declarative, Logic largely technology independent:

• logic is parseable, therefore translatable
• it is independent of the lower level platform (e.g., Flask)
• persistence is automated

So Logic can not only help cope with business change, it can also help cope with technology change.

Use Cases: Web Apps, APIs, Integration

Logic Bank is useful pretty much anywhere you are processing database transactions:

• Web Apps, whether server or client based
• APIs for client-based apps or integration can be developed over SQLAlchemy - such APIs can not only retrieve data, but enforce business logic on updates

Reuse over Transaction Sources - Architectural Automation

In far too many web apps, legacy logic is placed in the buttons (controllers).  This means it is hard to share between apps, and impossible to share with not-UI services such as APIs.  It's an architectural bug.

Logic Bank encourages logic to be centralized in SQLAlchemy, automatically partitioning it from web apps.

---

Summary: a Great Reason to Try Python

We've seen how Logic Bank can reduce over 95% of backend effort by 40X, with extensibility, scalability and manageability.  If you are using SQLAlchemy, it's worth checking out.

But it's more than that.This level of advantage can influence your technology stack decision.  You may already be considering Python for its productivity, simplicity and popularity.  Coupled with declarative business logic, the question may really be whether you can afford not to check it out.

Check It Out

If you're new to Python, there are also instructions on how to install it for Windows and Mac.