Logic Bank Now Extensible: Drive 95% Automation Even Higher
A developer discusses an open source project he has created using Python and SQLAlchemy that allows users to automate huge amounts of processes. Check it out!
Join the DZone community and get the full member experience.Join For Free
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.
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.
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 the rules while opening your database:
- 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
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.
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):
Event handlers are Python functions (lines 32-38):
The rules engine calls it, providing arguments:
row- an instance of a SQLAlchemy mapped class (here,
- Use code completion to access attributes (column values).
- Observe SQLAlchemy rows provide access to related rows (e.g.,
old_row- prior contents.
old_row, and includes useful states such as
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.
There are actually three types of events. The Logic Bank rules engine invokes them at different points in logic execution, as follows.
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
Early Eventsrun prior to rule execution.
Eventsrun 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.
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...
There are still some tedious things we'd like to automate...
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:
This illustrates how you can provide reusable services (e.g., time/date stamping), based on your naming conventions.
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.
In this example, we want to define a reusable audit rule that monitors
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
Our rule extension operates are summarized in the diagram:
logic.pyrule declaration (upper-left code window) calls...
nw.rule_extensions.py(lower-left code window), which invokes the constructor of our extended rule...
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.
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.
NWCopyRow constructor (
__init__) creates a rule instance of our “audit” rule, and saves the rule parameters in the rule instance (here,
Note this class extends
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
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
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:
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:
The Logic Bank runtime also includes a much more powerful example: Allocation.
The entire implementation is in
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
AmountAllocatedand adjust the
unpaid_ordersis 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.
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.
Opinions expressed by DZone contributors are their own.