Row policies

A row policy is a table attribute that adds a condition to every SELECT on that table. There are three policies. A partition shows only the rows that belong to the current context. A draft field hides draft rows. A logical-deletion field hides rows that are marked as deleted. Each policy has a query option that changes its effect for one query. Policies are filters on reads. They are not database permissions.

In the course recipe the customer table already has two policies. sys_fields with draft equal True adds the boolean column underscore underscore is_draft and declares it as draftField. sys_fields also adds the column underscore underscore del_ts and declares it as logicalDeletionField. That happens by default, through the ldel option. Every table of the recipe that calls sys_fields has it. For this lesson we add a partition, copied from the example application. The attribute partition_state equal invc_state partitions the table on the column state. The value invc_state names two environment keys: current_invc_state and allowed_invc_state. Only the first partition attribute of a table counts.

When the environment key current_invc_state has a true value, the compiler adds the condition state equals that value. The value is a parameter, read from the environment at execution. Look at the order of the conditions. Your where clause comes first. Then the partition condition, then the deletion condition, then the draft condition. Each condition is in its own parentheses, and they are joined with AND. An OR in your where clause cannot widen the partition.

When current is not set and allowed_invc_state holds a list, the condition admits the listed states and also rows whose state is NULL. When both keys are set, current wins and allowed is ignored. When neither key has a true value, there is no partition condition, and the query returns rows of every state. A value of None, zero, an empty string or an empty list counts as not set. A missing context is not an error. The shape of the condition is chosen when the query compiles. A query compiled with current set keeps the condition state equals parameter. Executed later without the key, it compares with NULL and returns no rows.

ignore_partition equal True removes the partition condition for one query. The draft and deletion conditions stay. The option accepts only True or False; any other value raises ValueError. The same holds for exclude_draft.

With a draft field declared, every SELECT adds the condition draft field IS NOT TRUE. Rows with false and rows with NULL stay visible. Cy Draft is hidden. exclude_draft equal False shows the drafts too. The option does not change any stored value.

A row is logically deleted when its deletion field is not NULL. In this example Bob Ltd has a timestamp in underscore underscore del_ts. By default the query adds del_ts IS NULL, and Bob disappears. exclude_logical_deleted equal False removes the condition. The value mark removes the condition and adds a result column named underscore isdeleted. It holds the deletion value itself, here a timestamp, or NULL. It is not converted to a boolean. In a query with distinct or group_by, mark adds no column. Any value other than True, False or mark raises ValueError.

Each policy option also accepts a camelCase spelling. Both spellings have the same effect. Use one of them per option. Passing both spellings of the same option raises ValueError, even when the values are equal.

Policies apply to the root table of the SELECT only. A joined table is not filtered. The invoices of Bob Ltd still show his name, although Bob is logically deleted. Subqueries of formula columns include drafts and deleted rows and ignore the partition, unless their definition sets the options. Raw SQL through db dot execute applies no policy. Writes do not read policies either. An insert does not fill the partition column from the environment, and it can store a row outside the current partition. An update can change a draft row. Set the partition column in the record yourself. This release has no partition check on writes.

To recap. The three policy attributes filter the root table of every SELECT. Three query options change them for one query, in one spelling at a time. Without partition context there is no partition filter, and writes and raw SQL are never filtered. The next lesson covers locking rows on read.