diff options
Diffstat (limited to 'test')
| -rw-r--r-- | test/orm/test_update_delete.py | 253 | ||||
| -rw-r--r-- | test/requirements.py | 4 | ||||
| -rw-r--r-- | test/sql/test_delete.py | 8 |
3 files changed, 237 insertions, 28 deletions
diff --git a/test/orm/test_update_delete.py b/test/orm/test_update_delete.py index 933d2bb1f..adaed8f6f 100644 --- a/test/orm/test_update_delete.py +++ b/test/orm/test_update_delete.py @@ -1715,8 +1715,8 @@ class UpdateDeleteFromTest(fixtures.MappedTest): ), ) - @testing.requires.delete_from - def test_delete_from_joined_subq_test(self): + @testing.requires.delete_using + def test_delete_using_joined_subq_test(self): Document = self.classes.Document s = fixture_session() @@ -1958,6 +1958,11 @@ class InheritTest(fixtures.DeclarativeMappedTest): id = Column(Integer, ForeignKey("person.id"), primary_key=True) engineer_name = Column(String(50)) + class Programmer(Engineer): + __tablename__ = "programmer" + id = Column(Integer, ForeignKey("engineer.id"), primary_key=True) + primary_language = Column(String(50)) + class Manager(Person): __tablename__ = "manager" id = Column(Integer, ForeignKey("person.id"), primary_key=True) @@ -1965,10 +1970,11 @@ class InheritTest(fixtures.DeclarativeMappedTest): @classmethod def insert_data(cls, connection): - Engineer, Person, Manager = ( + Engineer, Person, Manager, Programmer = ( cls.classes.Engineer, cls.classes.Person, cls.classes.Manager, + cls.classes.Programmer, ) s = Session(connection) s.add_all( @@ -1977,11 +1983,14 @@ class InheritTest(fixtures.DeclarativeMappedTest): Manager(name="m1", manager_name="m1"), Engineer(name="e2", engineer_name="e2"), Person(name="p1"), + Programmer( + name="pp1", engineer_name="pp1", primary_language="python" + ), ] ) s.commit() - @testing.only_on("mysql", "Multi table update") + @testing.only_on(["mysql", "mariadb"], "Multi table update") def test_update_from_join_no_problem(self): person = self.classes.Person.__table__ engineer = self.classes.Engineer.__table__ @@ -1998,53 +2007,253 @@ class InheritTest(fixtures.DeclarativeMappedTest): eq_(obj.name, "updated") eq_(obj.engineer_name, "e2a") - def test_update_subtable_only(self): + @testing.combinations(None, "fetch", "evaluate") + def test_update_sub_table_only(self, synchronize_session): Engineer = self.classes.Engineer s = Session(testing.db) - s.query(Engineer).update({"engineer_name": "e5"}) + s.query(Engineer).update( + {"engineer_name": "e5"}, synchronize_session=synchronize_session + ) - eq_(s.query(Engineer.engineer_name).all(), [("e5",), ("e5",)]) + eq_(s.query(Engineer.engineer_name).all(), [("e5",), ("e5",), ("e5",)]) + + @testing.combinations(None, "fetch", "evaluate") + def test_update_sub_sub_table_only(self, synchronize_session): + Programmer = self.classes.Programmer + s = Session(testing.db) + s.query(Programmer).update( + {"primary_language": "c++"}, + synchronize_session=synchronize_session, + ) + + eq_( + s.query(Programmer.primary_language).all(), + [ + ("c++",), + ], + ) @testing.requires.update_from - def test_update_from(self): + @testing.combinations(None, "fetch", "fetch_w_hint", "evaluate") + def test_update_from(self, synchronize_session): + """test an UPDATE that uses multiple tables. + + The limitation that MariaDB has with DELETE does not apply here + at the moment as MariaDB doesn't support UPDATE..RETURNING at all. + However, the logic from DELETE is still implemented in + persistence.py. If MariaDB adds UPDATE...RETURNING, or SQLite adds + UPDATE..FROM, etc., then it will be useful. + + """ Engineer = self.classes.Engineer Person = self.classes.Person s = Session(testing.db) - s.query(Engineer).filter(Engineer.id == Person.id).filter( - Person.name == "e2" - ).update({"engineer_name": "e5"}) + + # we don't have any backends with this combination right now. + db_has_hypothetical_limitation = ( + testing.db.dialect.update_returning + and not testing.db.dialect.update_returning_multifrom + ) + + e2 = s.query(Engineer).filter_by(name="e2").first() + + with self.sql_execution_asserter() as asserter: + eq_(e2.engineer_name, "e2") + q = ( + s.query(Engineer) + .filter(Engineer.id == Person.id) + .filter(Person.name == "e2") + ) + if synchronize_session == "fetch_w_hint": + q.execution_options(is_update_from=True).update( + {"engineer_name": "e5"}, + synchronize_session="fetch", + ) + elif ( + synchronize_session == "fetch" + and db_has_hypothetical_limitation + ): + with expect_raises_message( + exc.CompileError, + 'Dialect ".*" does not support RETURNING with ' + "UPDATE..FROM;", + ): + q.update( + {"engineer_name": "e5"}, + synchronize_session=synchronize_session, + ) + return + else: + q.update( + {"engineer_name": "e5"}, + synchronize_session=synchronize_session, + ) + + if synchronize_session is None: + eq_(e2.engineer_name, "e2") + else: + eq_(e2.engineer_name, "e5") + + if synchronize_session in ("fetch", "fetch_w_hint") and ( + db_has_hypothetical_limitation + or not testing.db.dialect.update_returning + ): + asserter.assert_( + CompiledSQL( + "SELECT person.id FROM person INNER JOIN engineer " + "ON person.id = engineer.id WHERE engineer.id = person.id " + "AND person.name = %s", + [{"name_1": "e2"}], + dialect="mariadb", + ), + CompiledSQL( + "UPDATE engineer, person SET engineer.engineer_name=%s " + "WHERE engineer.id = person.id AND person.name = %s", + [{"engineer_name": "e5", "name_1": "e2"}], + dialect="mariadb", + ), + ) + elif synchronize_session in ("fetch", "fetch_w_hint"): + asserter.assert_( + CompiledSQL( + "UPDATE engineer SET engineer_name=%(engineer_name)s " + "FROM person WHERE engineer.id = person.id " + "AND person.name = %(name_1)s RETURNING engineer.id", + [{"engineer_name": "e5", "name_1": "e2"}], + dialect="postgresql", + ), + ) + else: + asserter.assert_( + CompiledSQL( + "UPDATE engineer SET engineer_name=%(engineer_name)s " + "FROM person WHERE engineer.id = person.id " + "AND person.name = %(name_1)s", + [{"engineer_name": "e5", "name_1": "e2"}], + dialect="postgresql", + ), + ) eq_( set(s.query(Person.name, Engineer.engineer_name)), - set([("e1", "e1"), ("e2", "e5")]), + set([("e1", "e1"), ("e2", "e5"), ("pp1", "pp1")]), ) - @testing.requires.delete_from - def test_delete_from(self): + @testing.requires.delete_using + @testing.combinations(None, "fetch", "fetch_w_hint", "evaluate") + def test_delete_using(self, synchronize_session): + """test a DELETE that uses multiple tables. + + due to a limitation in MariaDB, we have an up front "hint" that needs + to be passed for this backend if DELETE USING is to be used in + conjunction with "fetch" strategy, so that we know before compilation + that we won't be able to use RETURNING. + + """ + Engineer = self.classes.Engineer Person = self.classes.Person s = Session(testing.db) - s.query(Engineer).filter(Engineer.id == Person.id).filter( - Person.name == "e2" - ).delete() + db_has_mariadb_limitation = ( + testing.db.dialect.delete_returning + and not testing.db.dialect.delete_returning_multifrom + ) + + e2 = s.query(Engineer).filter_by(name="e2").first() + + with self.sql_execution_asserter() as asserter: + + assert e2 in s + + q = ( + s.query(Engineer) + .filter(Engineer.id == Person.id) + .filter(Person.name == "e2") + ) + + if synchronize_session == "fetch_w_hint": + q.execution_options(is_delete_using=True).delete( + synchronize_session="fetch" + ) + elif synchronize_session == "fetch" and db_has_mariadb_limitation: + with expect_raises_message( + exc.CompileError, + 'Dialect ".*" does not support RETURNING with ' + "DELETE..USING;", + ): + q.delete(synchronize_session=synchronize_session) + return + else: + q.delete(synchronize_session=synchronize_session) + + if synchronize_session is None: + assert e2 in s + else: + assert e2 not in s + + if synchronize_session in ("fetch", "fetch_w_hint") and ( + db_has_mariadb_limitation + or not testing.db.dialect.delete_returning + ): + asserter.assert_( + CompiledSQL( + "SELECT person.id FROM person INNER JOIN engineer ON " + "person.id = engineer.id WHERE engineer.id = person.id " + "AND person.name = %s", + [{"name_1": "e2"}], + dialect="mariadb", + ), + CompiledSQL( + "DELETE FROM engineer USING engineer, person WHERE " + "engineer.id = person.id AND person.name = %s", + [{"name_1": "e2"}], + dialect="mariadb", + ), + ) + elif synchronize_session in ("fetch", "fetch_w_hint"): + asserter.assert_( + CompiledSQL( + "DELETE FROM engineer USING person WHERE " + "engineer.id = person.id AND person.name = %(name_1)s " + "RETURNING engineer.id", + [{"name_1": "e2"}], + dialect="postgresql", + ), + ) + else: + asserter.assert_( + CompiledSQL( + "DELETE FROM engineer USING person WHERE " + "engineer.id = person.id AND person.name = %(name_1)s", + [{"name_1": "e2"}], + dialect="postgresql", + ), + ) + + # delete actually worked eq_( set(s.query(Person.name, Engineer.engineer_name)), - set([("e1", "e1")]), + set([("pp1", "pp1"), ("e1", "e1")]), ) - @testing.only_on("mysql", "Multi table update") - def test_update_from_multitable(self): + @testing.only_on(["mysql", "mariadb"], "Multi table update") + @testing.requires.delete_using + @testing.combinations(None, "fetch", "evaluate") + def test_update_from_multitable(self, synchronize_session): Engineer = self.classes.Engineer Person = self.classes.Person s = Session(testing.db) s.query(Engineer).filter(Engineer.id == Person.id).filter( Person.name == "e2" - ).update({Person.name: "e22", Engineer.engineer_name: "e55"}) + ).update( + {Person.name: "e22", Engineer.engineer_name: "e55"}, + synchronize_session=synchronize_session, + ) eq_( set(s.query(Person.name, Engineer.engineer_name)), - set([("e1", "e1"), ("e22", "e55")]), + set([("e1", "e1"), ("e22", "e55"), ("pp1", "pp1")]), ) diff --git a/test/requirements.py b/test/requirements.py index c7c5beed9..c6776533c 100644 --- a/test/requirements.py +++ b/test/requirements.py @@ -467,11 +467,11 @@ class DefaultRequirements(SuiteRequirements): ) @property - def delete_from(self): + def delete_using(self): """Target must support DELETE FROM..FROM or DELETE..USING syntax""" return only_on( ["postgresql", "mssql", "mysql", "mariadb"], - "Backend does not support DELETE..FROM", + "Backend does not support DELETE..USING or equivalent", ) @property diff --git a/test/sql/test_delete.py b/test/sql/test_delete.py index 45ead811f..f98e7297d 100644 --- a/test/sql/test_delete.py +++ b/test/sql/test_delete.py @@ -307,7 +307,7 @@ class DeleteFromRoundTripTest(fixtures.TablesTest): ), ) - @testing.requires.delete_from + @testing.requires.delete_using def test_exec_two_table(self, connection): users, addresses = self.tables.users, self.tables.addresses dingalings = self.tables.dingalings @@ -326,7 +326,7 @@ class DeleteFromRoundTripTest(fixtures.TablesTest): ] self._assert_table(connection, addresses, expected) - @testing.requires.delete_from + @testing.requires.delete_using def test_exec_three_table(self, connection): users = self.tables.users addresses = self.tables.addresses @@ -342,7 +342,7 @@ class DeleteFromRoundTripTest(fixtures.TablesTest): expected = [(2, 5, "ding 2/5")] self._assert_table(connection, dingalings, expected) - @testing.requires.delete_from + @testing.requires.delete_using def test_exec_two_table_plus_alias(self, connection): users, addresses = self.tables.users, self.tables.addresses dingalings = self.tables.dingalings @@ -359,7 +359,7 @@ class DeleteFromRoundTripTest(fixtures.TablesTest): expected = [(1, 7, "x", "jack@bean.com"), (5, 9, "x", "fred@fred.com")] self._assert_table(connection, addresses, expected) - @testing.requires.delete_from + @testing.requires.delete_using def test_exec_alias_plus_table(self, connection): users, addresses = self.tables.users, self.tables.addresses dingalings = self.tables.dingalings |