This project is a complement to logicbank, which explains the concepts of rules, as well being used for development of logicbank.

Here we focus on the practicalities of installation and configuration, with 2 (unrelated) samples (copied from LogicBank):

• nw
• banking

This page shows the common install / configure tasks common to both. In both cases, they use fab-quickstart, which is optional but recommended since it makes it really easy to explore your database.

Installation

First, follow the instructions to verify / install Python, then install Logic Bank.

Python Installation

The first section below verifies whether your Python environment is current. The following section explains how to install a current Python environment.

Verify Pre-reqs: Python 3.8, virtualenv, pip3

Ensure you have these pre-reqs:

python --version
# requires 3.8 or higher (Relies on `from __future__ import annotations`, so requires Python 3.8)

pip --version
# version 19.2.3 or higher... you might be using pip3

pyenv --version
# 1.2.19 or higher

Install Python (if required)

If you are missing any, install them as described here. Skip this step if your pre-reqs are fine.

To install Python:

• Python3.8

  • Run the windows installer
    • Suggestion: check the box to add Python to your path
  • On mac/Unix, consider using homebrew, as described here

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

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

Issues? Try here.

Install LogicBankExamples

In your IDE or Command Line:

# optionally fork, and then (WARNING - remove hyphens if you download the zip)
git clone https://github.com/valhuber/LogicBankExamples.git
cd LogicBankExamples
# windows may require: python -m venv venv
# small venv: virtualenv --no-setuptools --no-wheel venv
virtualenv venv
# windows: .\venv\Scripts\activate
source venv/bin/activate
pip install -r requirements.txt

Warning - if you just download the zip, be sure to remove the hyphen from the name.

Note the option for small env: virtualenv --no-setuptools --no-wheel venv. For the smallest environment, you can also disable the the line for Flask-AppBuilder in requirements.txt. This is not recommended since you won't be able to run the web app, but you do get a small project like this:

Verify and Run

Run basic_web_app

cd LogicBankExamples
cd nw/basic_web_app
# windows set FLASK_APP=app
export FLASK_APP=app
flask run

You then start the app (use new window) with http://127.0.0.1:5000/

Login (upper right): user = admin, password = p

You can

1. Navigate to Order 11011 (a multi-page web app)
   • Click Menu > Customer List
   • Click the magnifying glass for the first customer
   • Click the List Order tab
   • Click the *magnifying glass for Order 11011
2. Click Edit so you can make changes
3. Change the Shipped Date
4. Click save
5. Verify logic enforcement
   • The web app has been configured to activate the rules
   • The logic for this update is interesting - check out the console log

Run the nw/tests

Run the nw/tests programs under your IDE or the command line; start with add_order and upd_order_shipped, and see the walk-throughs here.

cd nw/tests
python add_order.py

Note: the log depicts logic execution

Project Setup Cookbook

This project has already been set up. Here's how we did it. We'll be using nw as an example.

Create Environment

# create your project root (check this into scs)
mkdir nw
cd nw
virtualenv venv
# configure SCS to ignore venv
# windows venv\Scripts\activate
source venv/bin/activate

(venv)$ pip install logicbank
(venv)$ pip install SQLAlchemy
(venv)$ pip install sqlacodegen

# if using fab
(venv)$ pip install flask-appbuilder
(venv)$ pip install fab-quick-start

Creating a New Project

We'll recreate the logicbankexamples. We'll follow the same structure to make things definite, so you can compare. Of course, use whatever structure you like, here's how we did it for nw and banking:

# in nw...
(venv)$ mkdir nw_logic
(venv)$ mkdir db

Create Models

There are many ways to create models.

• You can create the models file by hand, and use that to generate the database, or
• You can use an existing database, and create a models file to match it.

For existing databases, consider using sqlacodegen. Here, we'll use nw as our example; we already have a sqlite database in our nw/db folder (download a copy) so:

(venv)$ cd db
(venv)$ sqlacodegen sqlite:///database.db --noviews > nw/nw_logic/app/models.py

The first parameter identifies your database location; consult the sqlacodegen documentation.

Important notes about models - mind the relationships

Both logicbank and fab-quickstart depend on relationships. Ideally, they exist in your database, in which as sqlcodegen will find them. If that's not practical, SQLAlchemy also lets to define them in your models:

• declare the foreign keys, eg, Orders has a foreign key to customers
  • CustomerId = Column(ForeignKey('Customer.Id'))
• declare the references in the parent (not child), eg, declare orders for customer like this
  • OrderDetailList = relationship("OrderDetail", backref="OrderHeader", cascade_backrefs=True)

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 declare_logic():
    """ 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)

Activate Rules

To test our rules, we use nw/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/logic/__init__.py, which sets up the rule engine:

by_rules = True  # True => use rules, False => use hand code (for comparison)
if by_rules:
    LogicBank.activate(session=session, activator=declare_logic)
else:
    # ... conventional after_flush listeners (to see rules/code contrast)

FAB Quick Start

Python Flask Application Builder (fab) creates "basic" applications for database crud operations quickly, with minimal coding. Typical fab pages can look like this:

1. Multi-page: apps include 1 page per table
2. Multi-table: pages include related_views for each related child table, and join in parent data
3. Favorite field first: first-displayed field is "name", or contains "name" (configurable)
4. Predictive joins: favorite field of each parent is shown (product name - not the foreign key product_id_)
5. Ids last: such boring fields are not shown on lists, and at the end on other pages

Multi-Table Page

---

1 - Create Empty fab Project

Create a default empty FAB app:

# cd to nw folder
(venv)$ flask fab create-app

You will then be prompted for the project name and your db engine type. When prompted:

• Use the default engine
• Name the project basic_web_app

You should see a structure as shown in the screen shot in the next section.

We now have a well-formed empty project. We now need to acquire and configure a database, set up SQLAlchemy ORM models.py, and define our pages with views.py.

2 - Configure Database

Update your nw/basic_web_app/config.py file to denote this database name (illustrated below). Your project will look something like this:

Project Structure

Key FAB inputs can become tedious: models.py and views.py

FAB requires that we edit 2 key files to make our "empty" project interact with the database. These can get tedious, due to per-page code required for each table / page. For more information, see here.

The following sections show how to use generators to avoid the tedious hand creation of the views.py and the models.py files.

3 - Create models.py

We have already created our models.py file. However, it has not been been discovered how to use it, so copy the models.py to your nw/app folder.

4 - Define views.py

Finally, we need to define some pages. That's also a bit of work to do that by hand, so let's use fab-quick-start to create the views.py file from the app/models.py file (hit enter to accept defaults when prompted):

(venv)$ fab-quick-start run --favorites="name description" --non_favorites="id" > app/views.py

This overwrites your nw/basic_web_app/app/views.py file. For more information, see the FAB Quick Start Utility docs.

5 - Create Admin

The FAB system can create tables in your database for authenticating and authorizing users (tables such as ab_user, ab_user_role, etc). You create these as follows (Username: admin, Password: p):

(venv)$ export FLASK_APP=app
(venv)$ flask fab create-admin
Username [admin]:
User first name [admin]:
User last name [user]:
Email [admin@fab.org]:
Password:
Repeat for confirmation:

Ignore the error "user already exists", since the admin data was pre-loaded.

You can verify your data and admin data like this (mac/unix only):

sqlite3 nw.db  # mac only
> .tables
> .schema Order
> select * from ab_user;
> select * from Territory;
> .quit

6 - Run nw App

You've now created a app with a dozen pages or so; run it like this:

(venv)$ # still cd'd to basic_web_app
(venv)$ export FLASK_APP=app
(venv)$ flask run

Start your browser here.