Orders for Commissioned Employees Only

Requirement - Orders for Commissioned Employees Only

We want to ensure that the EmployeeId for an order references an Employee that IsCommissioned.

Organizational Constraint: cannot alter Employees schema

As we'll see below, we will want to declare an Employee.order_count rule. But, this particular table is used by non-SQLAlchemy applications that do not enforce new rules.

Logic

Model

Since we cannot alter the schema, we define a hybrid attribute. We add the following to the nw/db/models.py for the Employees class:

    @hybrid_property
    def order_count(self):
        if not hasattr(self, "_order_count"):
            self._order_count = self.order_count_sql
        return self._order_count

    @order_count.setter
    def order_count(self, value):
        self._order_count = value

We must also add the following - it occurs later in the code to avoid making references to the not-yet-defined Order class:

Employee.order_count_sql = column_property(
    select([func.count(Order.Id)]). \
    where(Order.Id == Order.EmployeeId))

Rule

The model changes will make the attribute visible, and SQLAlchemy will return the proper value as data is read. But, we also need to adjust the value as orders are added.

The addition of the setter enables Logic Bank to adjust the value as orders are added. We define the rule in nw/logic/rules_bank.py, just as if the order_count were a persistent attribute:

    Rule.constraint(validate=Employee,
                    as_condition=lambda row: row.IsCommissioned == 1 or row.order_count == 0,
                    error_msg="{row.LastName} is not commissioned - cannot have orders")

    Rule.count(derive=Employee.order_count, as_count_of=Order)

Illustrates using count as existence check

One of the key logic patterns is to define counts, not so much for their actual value, but to see if there are any children for a parent row. Then, we can test the count as 0 to detect children.

Logical Data Independence

A key principle is to shield applications from changes in database structure, and logic. SQLAlchemy hybrid attributes are a clean approach for enabling applications to use mapped classes, which may or may not directly map to disk. Logic Bank provides the functionality to define rules over such non-persistent data.