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. Our model, then, is:

Logic

We could add logic to "add order", but we also want to prevent employees from being reclassified as non-commissioned if they are assigned orders. So, our design is:

1. Define Employee.order_count
2. Constraint to ensure order_count can be > 0 only for commissioned

Model

Since we cannot alter the schema, we define a hybrid attribute. We add the following to the examples/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 examples/nw/logic/logic.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 Pattern: 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.

Illustrates Pattern: constrain a derived result

The constraint rule governs the result of the count rule. Note their declaration order is unimportant.

Illustrates 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.