Permalink
Browse files

Add Trigger.alter to compare and, if needed, recreate triggers. Fixes #…

  • Loading branch information...
1 parent e2967dd commit 72d205ff57264634254ba4ab402cbce06945cece @jmafc jmafc committed Dec 21, 2017
Showing with 54 additions and 0 deletions.
  1. +14 −0 pyrseas/dbobject/trigger.py
  2. +40 −0 tests/dbobject/test_trigger.py
@@ -183,6 +183,20 @@ def create(self, dbversion=None):
self._table.qualname(), defer,
self.level.upper(), cond, self.procedure)]
+ def alter(self, inobj):
+ """Generate SQL to transform an existing trigger
+
+ :param inobj: a YAML map defining the new trigger
+ :return: list of SQL statements
+ """
+ stmts = []
+ if self.procedure != inobj.procedure or self.events != inobj.events \
+ or self.level != inobj.level or self.timing != inobj.timing:
+ stmts.append(self.drop())
+ stmts.append(inobj.create())
+ stmts.append(self.diff_description(inobj))
+ return stmts
+
def get_implied_deps(self, db):
deps = super(Trigger, self).get_implied_deps(db)
@@ -9,6 +9,8 @@
"RETURN NULL; END"
CREATE_TABLE_STMT = "CREATE TABLE t1 (c1 integer, c2 text, " \
"c3 timestamp with time zone)"
+CREATE_TABLE_STMT2 = "CREATE TABLE t1 (c1 integer, c2 text, " \
+ "c3 text, tsidx tsvector)"
CREATE_FUNC_STMT = "CREATE FUNCTION f1() RETURNS trigger LANGUAGE plpgsql " \
"AS $_$%s$_$" % FUNC_SRC
CREATE_STMT = "CREATE TRIGGER tr1 BEFORE INSERT OR UPDATE ON t1 " \
@@ -216,6 +218,44 @@ def test_create_trigger_instead(self):
assert fix_indent(sql[3]) == "CREATE TRIGGER tr1 INSTEAD OF INSERT " \
"ON v1 FOR EACH ROW EXECUTE PROCEDURE f1()"
+ def test_add_tsvector_trigger(self):
+ "Add a text search (tsvector) trigger"
+ inmap = self.std_map()
+ inmap['schema public'].update({'table t1': {
+ 'columns': [{'c1': {'type': 'integer'}}, {'c2': {'type': 'text'}},
+ {'c3': {'type': 'text'}},
+ {'tsidx': {'type': 'tsvector'}}],
+ 'triggers': {'t1_tsidx_update': {'timing': 'before',
+ 'events': ['insert', 'update'], 'level': 'row',
+ 'procedure': "tsvector_update_trigger('tsidx', "
+ "'pg_catalog.english', 'c2')"}}}})
+ sql = self.to_sql(inmap, [CREATE_TABLE_STMT2])
+ assert fix_indent(sql[0]) == "CREATE TRIGGER t1_tsidx_update BEFORE" \
+ " INSERT OR UPDATE ON t1 FOR EACH ROW EXECUTE PROCEDURE " \
+ "tsvector_update_trigger('tsidx', 'pg_catalog.english', 'c2')"
+
+ def test_change_tsvector_trigger(self):
+ "Change a text search (tsvector) trigger"
+ inmap = self.std_map()
+ inmap['schema public'].update({'table t1': {
+ 'columns': [{'c1': {'type': 'integer'}}, {'c2': {'type': 'text'}},
+ {'c3': {'type': 'text'}},
+ {'tsidx': {'type': 'tsvector'}}],
+ 'triggers': {'t1_tsidx_update': {'timing': 'before',
+ 'events': ['insert', 'update'], 'level': 'row',
+ 'procedure': "tsvector_update_trigger('tsidx', "
+ "'pg_catalog.english', 'c2', 'c3')"}}}})
+ stmts = [CREATE_TABLE_STMT2,
+ "CREATE TRIGGER t1_tsidx_update BEFORE INSERT OR UPDATE ON "
+ "t1 FOR EACH ROW EXECUTE PROCEDURE tsvector_update_trigger"
+ "('tsidx', 'pg_catalog.english', 'c2')"]
+ sql = self.to_sql(inmap, stmts)
+ assert sql[0] == "DROP TRIGGER t1_tsidx_update ON t1"
+ assert fix_indent(sql[1]) == "CREATE TRIGGER t1_tsidx_update BEFORE" \
+ " INSERT OR UPDATE ON t1 FOR EACH ROW EXECUTE PROCEDURE " \
+ "tsvector_update_trigger('tsidx', 'pg_catalog.english', " \
+ "'c2', 'c3')"
+
def test_create_trigger_in_schema(self):
"Create a trigger within a non-public schema"
inmap = self.std_map(plpgsql_installed=True)

0 comments on commit 72d205f

Please sign in to comment.