Coverage for utilities/migration.py: 94%
47 statements
« prev ^ index » next coverage.py v7.15.2, created at 2026-10-10 18:35 +0000
1from django.db import migrations, models
3from netbox.config import ConfigItem
5__all__ = (
6 'InstallDenormalizationTrigger',
7 'custom_deconstruct',
8)
11EXEMPT_ATTRS = (
12 'choices',
13 'help_text',
14 'verbose_name',
15)
17_deconstruct = models.Field.deconstruct
20def custom_deconstruct(field):
21 """
22 Imitate the behavior of the stock deconstruct() method, but ignore the field attributes listed above.
23 """
24 name, path, args, kwargs = _deconstruct(field)
26 # Remove any ignored attributes
27 for attr in EXEMPT_ATTRS:
28 kwargs.pop(attr, None)
30 # Ignore any field defaults which reference a ConfigItem
31 kwargs = {
32 k: v for k, v in kwargs.items() if not isinstance(v, ConfigItem)
33 }
35 return name, path, args, kwargs
38class InstallDenormalizationTrigger(migrations.operations.base.Operation):
39 """
40 Install a PostgreSQL trigger that keeps denormalized columns on a dependent table in sync with their
41 source object.
43 When a row in `source_table` is updated, the trigger copies the values of the mapped source columns into
44 the corresponding denormalized columns on every `dependent_table` row that references it via `fk_column`.
45 This replaces the Python `post_save` handlers formerly defined in `netbox.denormalized` and `dcim.signals`.
47 Args:
48 dependent_table: The table carrying the denormalized columns (e.g. 'ipam_prefix').
49 source_table: The table whose changes are propagated (e.g. 'dcim_site').
50 fk_column: The column on `dependent_table` referencing `source_table` (e.g. '_site_id').
51 mappings: A mapping of {dependent_column: source_column}, using actual database column names
52 (e.g. {'_region_id': 'region_id', '_site_group_id': 'group_id'}). Each is copied directly:
53 `dependent_column = NEW.source_column`.
54 related_mappings: An optional iterable of related-table lookups for columns that live one hop
55 beyond `source_table`. Each entry is a dict with keys `table` (the related table), `source_fk`
56 (a column on `source_table` referencing `related_table.id`), and `mappings`
57 ({dependent_column: related_column}). Each is resolved with a single multi-column subquery
58 (`(cols) = (SELECT cols FROM table WHERE id = NEW.source_fk)`), so the related row is read once.
59 This closes the chain gap when a denormalized column is derived through an intermediate object
60 (e.g. a Location's Site change must refresh the dependent's region/site-group, not just its site).
61 If `source_fk` is NULL the subquery returns no row and all its target columns are set to NULL,
62 which is the correct result (the source object has no related object); current callers use a
63 non-nullable `source_fk` (Location.site), so this does not arise in practice.
65 The trigger fires AFTER UPDATE of the watched source columns (the direct `mappings` sources plus each
66 related `source_fk`), and only when at least one of them actually changed. It does not fire on INSERT (a
67 newly created source row has no dependents yet) and it does not recurse: the dependent tables carry no
68 triggers of their own.
70 !!! warning "Watched columns must be of a type whose `=` lives in `pg_catalog`"
71 The generated WHEN clause compares each watched column with `IS DISTINCT FROM`, which expands to
72 that column type's `=` operator, resolved from `search_path` at CREATE TRIGGER time and with no
73 syntax available to schema-qualify it. Every current caller watches integer FK columns, whose `=` is
74 a built-in in `pg_catalog` and therefore always resolvable. Do NOT pass a column of an extension
75 type (`ltree`, `hstore`, PostGIS `geometry`, ...): its operators live in the extension's schema, so
76 the resulting trigger would fail to restore from a `pg_dump`, which replays DDL with an empty
77 `search_path` — and because `psql` does not stop on error by default, the restore would appear to
78 succeed with the trigger silently missing. See `utilities/ltree.py` and #23130.
80 Example: refresh a CircuitTermination's cached region/sitegroup when its Site's region or group changes::
82 InstallDenormalizationTrigger(
83 dependent_table='circuits_circuittermination',
84 source_table='dcim_site',
85 fk_column='_site_id',
86 mappings={'_region_id': 'region_id', '_site_group_id': 'group_id'},
87 )
89 Note: this is a row-level trigger, so a bulk source update of N rows fires it N times. A statement-level
90 trigger with transition tables would batch this, but PostgreSQL forbids transition tables on a trigger
91 with an `UPDATE OF <columns>` list, and dropping that column list would fire the trigger on every source
92 update (including unrelated columns) — a worse trade on hot-write tables like dcim_device.
93 """
94 reversible = True
96 def __init__(self, dependent_table, source_table, fk_column, mappings, related_mappings=()):
97 self.dependent_table = dependent_table
98 self.source_table = source_table
99 self.fk_column = fk_column
100 self.mappings = mappings
101 self.related_mappings = list(related_mappings)
103 @property
104 def function_name(self):
105 return f'{self.dependent_table}_denorm_from_{self.source_table}_fn'
107 @property
108 def trigger_name(self):
109 return f'{self.dependent_table}_denorm_from_{self.source_table}'
111 def state_forwards(self, app_label, state):
112 # Triggers are not part of Django's model state.
113 pass
115 def database_forwards(self, app_label, schema_editor, from_state, to_state):
116 # Direct column copies from the changed source row.
117 set_parts = [f'"{dest}" = NEW."{src}"' for dest, src in self.mappings.items()]
118 watched = list(self.mappings.values())
119 # One-hop lookups: a single multi-column subquery per related table reads its row only once.
120 for rel in self.related_mappings:
121 dests = ', '.join(f'"{d}"' for d in rel['mappings'].keys())
122 cols = ', '.join(f'"{c}"' for c in rel['mappings'].values())
123 set_parts.append(f'({dests}) = (SELECT {cols} FROM "{rel["table"]}" WHERE id = NEW."{rel["source_fk"]}")')
124 watched.append(rel['source_fk'])
126 # Deduplicate watched columns while preserving order (a direct mapping and a related lookup may
127 # both key off the same source column, e.g. site_id).
128 watched_columns = list(dict.fromkeys(watched))
130 set_clause = ', '.join(set_parts)
131 update_of = ', '.join(f'"{col}"' for col in watched_columns)
132 when_clause = ' OR '.join(f'OLD."{col}" IS DISTINCT FROM NEW."{col}"' for col in watched_columns)
134 schema_editor.execute(f'''
135 CREATE OR REPLACE FUNCTION "{self.function_name}"() RETURNS TRIGGER AS $$
136 BEGIN
137 UPDATE "{self.dependent_table}"
138 SET {set_clause}
139 WHERE "{self.fk_column}" = NEW.id;
140 RETURN NULL;
141 END
142 $$ LANGUAGE plpgsql;
143 ''')
144 # Drop first so the operation is idempotent (re-run / partially-applied migration, or a
145 # trigger pre-installed during testing); CREATE TRIGGER alone errors if one already exists.
146 schema_editor.execute(f'DROP TRIGGER IF EXISTS "{self.trigger_name}" ON "{self.source_table}";')
147 schema_editor.execute(f'''
148 CREATE TRIGGER "{self.trigger_name}"
149 AFTER UPDATE OF {update_of} ON "{self.source_table}"
150 FOR EACH ROW WHEN ({when_clause})
151 EXECUTE FUNCTION "{self.function_name}"();
152 ''')
154 def database_backwards(self, app_label, schema_editor, from_state, to_state):
155 schema_editor.execute(f'DROP TRIGGER IF EXISTS "{self.trigger_name}" ON "{self.source_table}";')
156 schema_editor.execute(f'DROP FUNCTION IF EXISTS "{self.function_name}"();')
158 def describe(self):
159 return f'Install denormalization trigger on {self.source_table} updating {self.dependent_table}'