From 19700f6d62f69361d88ef6b6da61ac65719399e5 Mon Sep 17 00:00:00 2001 From: RamonWill Date: Sun, 30 Aug 2020 09:05:06 -0400 Subject: [PATCH] Include PostgreSQL in trigger test and correct documentation example Include PostgreSQL dialect in trigger test and correct DDL example in documentation A user highlighted that the syntax in the DDL trigger example was incorrect for PostgreSQL. The trigger tests where also skipping the PostgreSQL dialect until the syntax was corrected. This PR fixes both of these issues. This pull request is: - [X ] A documentation / typographical error fix - Good to go, no issue or tests are needed - [ ] A short code fix - please include the issue number, and create an issue if none exists, which must include a complete example of the issue. one line code fixes without an issue and demonstration will not be accepted. - Please include: `Fixes: #` in the commit message - please include tests. one line code fixes without tests will not be accepted. - [ ] A new feature implementation - please include the issue number, and create an issue if none exists, which must include a complete example of how the feature would look. - Please include: `Fixes: #` in the commit message - please include tests. **Have a nice day!** Fixes: #4037 Closes: #5548 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/5548 Pull-request-sha: 1db5e47adb90f9d51e247711dcfdbb274fb7bf73 Change-Id: I06edbcab99c82a3ce25581b81f8d2a4a028c07c3 (cherry picked from commit 2e887c7a4105a973a015b9ecbb4ea5a11dafb26a) --- doc/build/core/ddl.rst | 19 ++++++++++++--- test/orm/test_defaults.py | 51 +++++++++++++++++++++++++++++++++++---- test/requirements.py | 5 ---- 3 files changed, 62 insertions(+), 13 deletions(-) diff --git a/doc/build/core/ddl.rst b/doc/build/core/ddl.rst index 30619a419a..9c2fed198d 100644 --- a/doc/build/core/ddl.rst +++ b/doc/build/core/ddl.rst @@ -59,9 +59,24 @@ the PostgreSQL backend, we could invoke this as:: Column('data', String(50)) ) + func = DDL( + "CREATE FUNCTION my_func() " + "RETURNS TRIGGER AS $$ " + "BEGIN " + "NEW.data := 'ins'; " + "RETURN NEW; " + "END; $$ LANGUAGE PLPGSQL" + ) + trigger = DDL( "CREATE TRIGGER dt_ins BEFORE INSERT ON mytable " - "FOR EACH ROW BEGIN SET NEW.data='ins'; END" + "FOR EACH ROW EXECUTE PROCEDURE my_func();" + ) + + event.listen( + mytable, + 'after_create', + func.execute_if(dialect='postgresql') ) event.listen( @@ -296,5 +311,3 @@ DDL Expression Constructs API .. autoclass:: DropSchema :members: :undoc-members: - - diff --git a/test/orm/test_defaults.py b/test/orm/test_defaults.py index 5cadea5ffc..4184483e84 100644 --- a/test/orm/test_defaults.py +++ b/test/orm/test_defaults.py @@ -40,6 +40,9 @@ class TriggerDefaultsTest(fixtures.MappedTest): sa.schema.FetchedValue(for_update=True), ), ) + + dialect_name = testing.db.dialect.name + for ins in ( sa.DDL( "CREATE TRIGGER dt_ins AFTER INSERT ON dt " @@ -59,18 +62,38 @@ class TriggerDefaultsTest(fixtures.MappedTest): "BEGIN " ":NEW.col2 := 'ins'; :NEW.col4 := 'ins'; END;" ).execute_if(dialect="oracle"), + sa.DDL( + "CREATE TRIGGER dt_ins BEFORE INSERT " + "ON dt " + "FOR EACH ROW " + "EXECUTE PROCEDURE my_func_ins();" + ).execute_if(dialect="postgresql"), sa.DDL( "CREATE TRIGGER dt_ins BEFORE INSERT ON dt " "FOR EACH ROW BEGIN " "SET NEW.col2='ins'; SET NEW.col4='ins'; END" ).execute_if( callable_=lambda ddl, target, bind, **kw: bind.engine.name - not in ("oracle", "mssql", "sqlite") + not in ("oracle", "mssql", "sqlite", "postgresql") ), ): - event.listen(dt, "after_create", ins) + my_func_ins = sa.DDL( + "CREATE OR REPLACE FUNCTION my_func_ins() " + "RETURNS TRIGGER AS $$ " + "BEGIN " + "NEW.col2 := 'ins'; NEW.col4 := 'ins'; " + "RETURN NEW; " + "END; $$ LANGUAGE PLPGSQL" + ).execute_if(dialect="postgresql") + event.listen(dt, "after_create", my_func_ins) - event.listen(dt, "before_drop", sa.DDL("DROP TRIGGER dt_ins")) + event.listen(dt, "after_create", ins) + if dialect_name == "postgresql": + event.listen( + dt, "before_drop", sa.DDL("DROP TRIGGER dt_ins ON dt") + ) + else: + event.listen(dt, "before_drop", sa.DDL("DROP TRIGGER dt_ins")) for up in ( sa.DDL( @@ -89,18 +112,36 @@ class TriggerDefaultsTest(fixtures.MappedTest): "FOR EACH ROW BEGIN " ":NEW.col3 := 'up'; :NEW.col4 := 'up'; END;" ).execute_if(dialect="oracle"), + sa.DDL( + "CREATE TRIGGER dt_up BEFORE UPDATE ON dt " + "FOR EACH ROW " + "EXECUTE PROCEDURE my_func_up();" + ).execute_if(dialect="postgresql"), sa.DDL( "CREATE TRIGGER dt_up BEFORE UPDATE ON dt " "FOR EACH ROW BEGIN " "SET NEW.col3='up'; SET NEW.col4='up'; END" ).execute_if( callable_=lambda ddl, target, bind, **kw: bind.engine.name - not in ("oracle", "mssql", "sqlite") + not in ("oracle", "mssql", "sqlite", "postgresql") ), ): + my_func_up = sa.DDL( + "CREATE OR REPLACE FUNCTION my_func_up() " + "RETURNS TRIGGER AS $$ " + "BEGIN " + "NEW.col3 := 'up'; NEW.col4 := 'up'; " + "RETURN NEW; " + "END; $$ LANGUAGE PLPGSQL" + ).execute_if(dialect="postgresql") + event.listen(dt, "after_create", my_func_up) + event.listen(dt, "after_create", up) - event.listen(dt, "before_drop", sa.DDL("DROP TRIGGER dt_up")) + if dialect_name == "postgresql": + event.listen(dt, "before_drop", sa.DDL("DROP TRIGGER dt_up ON dt")) + else: + event.listen(dt, "before_drop", sa.DDL("DROP TRIGGER dt_up")) @classmethod def setup_classes(cls): diff --git a/test/requirements.py b/test/requirements.py index 25b2e5880f..3f83ca967c 100644 --- a/test/requirements.py +++ b/test/requirements.py @@ -367,11 +367,6 @@ class DefaultRequirements(SuiteRequirements): # no access to same table no_support("mysql", "requires SUPER priv"), exclude("mysql", "<", (5, 0, 10), "not supported by database"), - # huh? TODO: implement triggers for PG tests, remove this - no_support( - "postgresql", - "PG triggers need to be implemented for tests", - ), ] ) -- 2.47.2