Utilities to build trigger conditions based on field changes.

The following utility classes have been added to help trigger authors construct trigger conditions based on changes to underlying fields: - `pgtrigger.AnyChange`: If any supplied fields change, trigger the condition. - `pgtrigger.AnyDontChange`: If any supplied fields don't change, trigger the condition. - `pgtrigger.AllChange`: If all supplied fields change, trigger the condition. - `pgtrigger.AllDontChange`: If all supplied fields don't change, trigger the condition. A section in the docs was added for more examples on their use. Type: feature
BillSchumacher · Oct 16, 2023 · 1471a35 · 1471a35
1 parent 866c0d5
commit 1471a35
Show file tree

Hide file tree

Showing 12 changed files with 483 additions and 10 deletions.
diff --git a/README.md b/README.md
@@ -74,6 +74,7 @@ If triggers are new to you, don't worry. The [pgtrigger docs](https://django-pgt
 * Trigger basics and motivation for using triggers.
 * How to use the built-in triggers and how to build custom ones.
 * Installing triggers on third-party models, many-to-many fields, and other advanced scenarios.
+* Writing conditional triggers.
 * Ignoring triggers dynamically and deferring trigger execution.
 * Multiple database, schema, and partitioning support.
 * Frequently asked questions, common issues, and upgrading.

diff --git a/docs/basics.md b/docs/basics.md
@@ -35,7 +35,7 @@ The [pgtrigger.Trigger][] object is the base class for all triggers in `django-p
 
     Conditionally execute the trigger based on the `OLD` or `NEW` rows.
 
-    [pgtrigger.Condition][] objects accept [pgtrigger.Q][] and [pgtrigger.F][] objects for constructing `WHERE` clauses with the `OLD` and `NEW` rows. Conditions can also be created from raw SQL. See the [Cookbook](cookbook.md) for  more examples.
+    Use [pgtrigger.Q][] and [pgtrigger.F][] objects for constructing `WHERE` clauses with the `OLD` and `NEW` rows. See [the conditional triggers section](conditional.md) for more details and other utilities.
 
     !!! note
 

diff --git a/docs/conditional.md b/docs/conditional.md
@@ -0,0 +1,144 @@
+# Conditional Triggers
+
+Here's a brief guide on the many ways one can create conditional row-level triggers using `django-pgtrigger`. We start with the high-level utilities and make our way towards lower-level ones.
+
+Remember, row-level triggers have access to either the `NEW` row being inserted or updated, or the `OLD` row being updated or deleted. These variables are copies of the row and can be used in the conditions of the trigger. Updates triggers, for example, can conditionally execute based on both the values of the row before the update (the `OLD` row) and the row after the modification (the `NEW` row).
+
+!!! note
+
+    Consult the [Postgres docs](https://www.postgresql.org/docs/current/plpgsql-trigger.html) for more information on these variables.
+
+We'll first dive into update-based triggers and the utilities `django-pgtrigger` provides for detecting changes on models.
+
+## Field Change Conditions
+
+The following conditions are provided out of the box for conveniently expressing field changes:
+
+- [pgtrigger.AnyChange][]: If any supplied fields change, trigger the condition.
+- [pgtrigger.AnyDontChange][]: If any supplied fields don't change, trigger the condition.
+- [pgtrigger.AllChange][]: If all supplied fields change, trigger the condition.
+- [pgtrigger.AllDontChange][]: If all supplied fields don't change, trigger the condition.
+
+For example, let's use this model:
+
+```python
+class MyModel(models.Model):
+    int_field = models.IntegerField()
+    char_field = models.CharField(null=True)
+    dt_field = models.DateTimeField(auto_now=True)
+```
+
+The following trigger will raise an exception if an update happens that doesn't change a single field.
+
+```python
+pgtrigger.Protect(operation=pgtrigger.Update, condition=~pgtrigger.AnyChange())
+```
+
+This is also equivalent to doing:
+
+```python
+pgtrigger.Protect(operation=pgtrigger.Update, condition=pgtrigger.AllDontChange())
+```
+
+!!! remember
+
+    If no arguments are provided to any of these utilities, they operate over all fields on the model.
+
+Let's say we want to block updates if any changes happen to the int or char fields:
+
+```python
+pgtrigger.Protect(
+    operation=pgtrigger.Update,
+    condition=pgtrigger.AnyChange("int_field", "char_field")
+)
+```
+
+This is how the [pgtrigger.ReadOnly][] trigger is implemented. Underneath the hood, the condition looks like this:
+
+```sql
+OLD.int_field IS DISTINCT FROM NEW.int_field
+OR OLD.char_field IS DISTINCT FROM NEW.char_field
+```
+
+!!! note
+
+    `IS DISTINCT FROM` helps ensure that nullable objects are correctly compared since null never equals null.
+
+One can also exclude fields in the condition. For example, this condition fires only if every field but the excluded ones change:
+
+```python
+pgtrigger.AllChange(exclude=["dt_field"])
+```
+
+To automatically ignore `auto_now` and `auto_now_add` datetime fields, do:
+
+```python
+# Fires on changes to any fields except auto_now and auto_now_add fields
+pgtrigger.AnyChange(exclude_auto=True)
+```
+
+!!! remember
+
+    Included and excluded fields can both be supplied. Included fields are used as the initial fields before `exclude` and `exclude_auto` remove fields.
+
+## Targetting old and new fields with `pgtrigger.Q` and `pgtrigger.F`
+
+We previously covered various change condition utilties. These only operate over update-based triggers. One can create fine-grained trigger conditions for all operations by using [pgtrigger.Q][] and [pgtrigger.F][] constructs.
+
+For example, let's use our model from above again:
+
+```python
+class MyModel(models.Model):
+    int_field = models.IntegerField()
+    char_field = models.CharField(null=True)
+    dt_field = models.DateTimeField(auto_now=True)
+```
+
+The following condition will fire whenever the old row has an `int_field` greater than zero:
+
+```python
+pgtrigger.Q(old__int_field__gt=0)
+```
+
+Similar to Django's syntax, the [pgtrigger.Q][] object can reference the `old__` and `new__` row. The [pgtrigger.F][] object can also be used for doing comparisons. For example, here we only fire when the `int_field` of the old row is greater than the int field of the new row.
+
+```python
+pgtrigger.Q(old__int_field__gt=pgtrigger.F("new__int_field"))
+```
+
+Remember to use the `__df` operator for `DISTINCT FROM` and `__ndf` for `NOT DISTINCT FROM`. This is generally the behavior one desires when checking for changes of nullable fields. For example, this condition fires only when `char_field` is not distinct from its old version.
+
+```python
+pgtrigger.Q(old__char_field__ndf=pgtrigger.F("new__char_field"))
+```
+
+!!! note
+
+    The above is equivalent to doing `pgtrigger.AnyDontChange("char_field")`
+
+Finally, [pgtrigger.Q][] objects can be negated, and-ed, and or-ed just like django `Q` objects:
+
+```python
+pgtrigger.Q(old__char_field__ndf=pgtrigger.F("new__char_field"))
+| pgtrigger.Q(new__int_field=0)
+```
+
+## Raw SQL conditions
+
+The utilities above should handle the majority of use cases when expressing conditions; however, users can still express raw SQL with [pgtrigger.Condition][]. For example, here's a condition that fires if any field changes:
+
+```python
+pgtrigger.Condition("OLD.* IS DISTINCT FROM NEW.*")
+```
+
+!!! note
+
+    The above is equivalent to `pgtrigger.AnyChange()`.
+
+## Conditions across multiple models
+
+Remember, trigger conditions can only be expressed based on the rows of the current model. One can't, for example, reference a joined foreign key's value. This isn't a limitation in `django-pgtrigger` but rather a limitation in the database.
+
+Custom conditional logic than spans multiple tables must happen inside the function as an `if/else` type of statement. [See this resource](https://www.postgresqltutorial.com/postgresql-plpgsql/plpgsql-if-else-statements/) for an example of what this looks like.
+
+Currently `django-pgtrigger` doesn't handle this case out of the box; one must write raw SQL to express `if/else` logic. Reach out [to the author](mailto:[email protected]) if you have a need for this to be more easily expressed in `django-pgtrigger`.
diff --git a/docs/cookbook.md b/docs/cookbook.md
@@ -228,9 +228,7 @@ class RedundantUpdateModel(models.Model):
             pgtrigger.Protect(
                 name="protect_redundant_updates",
                 operation=pgtrigger.Update,
-                condition=pgtrigger.Condition(
-                    "OLD.* IS NOT DISTINCT FROM NEW.*"
-                )
+                condition=pgtrigger.AnyDontChange()
             )
         ]
 ```
@@ -297,7 +295,7 @@ class Versioned(models.Model):
             pgtrigger.Protect(
                 name="protect_updates",
                 operation=pgtrigger.Update,
-                condition=pgtrigger.Q(old__version__df=pgtrigger.F("new__version"))
+                condition=pgtrigger.AnyChange("version")
             ),
             # Increment the version field on changes
             pgtrigger.Trigger(
@@ -306,7 +304,7 @@ class Versioned(models.Model):
                 operation=pgtrigger.Update,
                 func="NEW.version = NEW.version + 1; RETURN NEW;",
                 # Don't increment version on redundant updates.
-                condition=pgtrigger.Condition("OLD.* IS DISTINCT FROM NEW.*")
+                condition=pgtrigger.AnyChange()
             )
         ]
 ```

diff --git a/docs/index.md b/docs/index.md
@@ -70,6 +70,7 @@ We recommend everyone first read:
 After this, there are several usage guides:
 
 * [Cookbook](cookbook.md) for trigger examples.
+* [Conditional Triggers](conditional.md) for all the ways one can create conditional triggers.
 * [Ignoring Execution](ignoring_triggers.md) for dynamically ignoring triggers.
 * [Deferrable Triggers](deferrable.md) for deferring trigger execution.
 * [Advanced Installation](advanced_installation.md) for installing triggers on third-party models, many-to-many models, programmatic installation, and more.

diff --git a/docs/module.md b/docs/module.md
@@ -37,6 +37,10 @@ Below are the core classes and functions of the `pgtrigger` module.
 ## Conditions
 
 :::pgtrigger.Condition
+:::pgtrigger.AnyChange
+:::pgtrigger.AnyDontChange
+:::pgtrigger.AllChange
+:::pgtrigger.AllDontChange
 :::pgtrigger.Q
 :::pgtrigger.F
 :::pgtrigger.IsDistinctFrom

diff --git a/mkdocs.yml b/mkdocs.yml
@@ -82,6 +82,7 @@ nav:
   - Usage Guides:
       - Basics: basics.md
       - Trigger Cookbook: cookbook.md
+      - Conditional Triggers: conditional.md
       - Ignoring Execution: ignoring_triggers.md
       - Deferrable Triggers: deferrable.md
       - Advanced Installation: advanced_installation.md

diff --git a/pgtrigger/__init__.py b/pgtrigger/__init__.py
@@ -9,6 +9,10 @@
 )
 from pgtrigger.core import (
     After,
+    AllChange,
+    AllDontChange,
+    AnyChange,
+    AnyDontChange,
     Before,
     Condition,
     Deferred,
@@ -61,6 +65,10 @@
 
 __all__ = [
     "After",
+    "AllChange",
+    "AllDontChange",
+    "AnyChange",
+    "AnyDontChange",
     "Before",
     "Condition",
     "constraints",