Allow delete where clause to refer multiple tables.

Implemented "DELETE..FROM" syntax for Postgresql, MySQL, MS SQL Server
(as well as within the unsupported Sybase dialect) in a manner similar
to how "UPDATE..FROM" works.  A DELETE statement that refers to more than
one table will switch into "multi-table" mode and render the appropriate
"USING" or multi-table "FROM" clause as understood by the database.
Pull request courtesy Pieter Mulder.

For SQL syntaxes see:

Postgresql: https://www.postgresql.org/docs/current/static/sql-delete.html
MySQL: https://dev.mysql.com/doc/refman/5.7/en/delete.html#multiple-table_syntax
MSSQL: https://docs.microsoft.com/en-us/sql/t-sql/statements/delete-transact-sql
Sybase: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00801.1510/html/iqrefso/X315721.htm

Co-authored by: Mike Bayer <mike_mp@zzzcomputing.com>
Change-Id: I6dfd57b49e44a095d076dc493cd2360bb5d920d3
Pull-request: https://github.com/zzzeek/sqlalchemy/pull/392
Fixes: #959

3 files changed, 146 insertions, 5 deletions

diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index 053619f46..988230ac5 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -2926,7 +2926,7 @@ class ExecutionOptionsTest(fixtures.TestBase):
 
 
 class CRUDTest(fixtures.TestBase, AssertsCompiledSQL):
-    __dialect__ = 'default'
+    __dialect__ = 'default_enhanced'
 
     def test_insert_literal_binds(self):
         stmt = table1.insert().values(myid=3, name='jack')
diff --git a/test/sql/test_delete.py b/test/sql/test_delete.py
index 904dcee3f..7d18db9c9 100644
--- a/test/sql/test_delete.py
+++ b/test/sql/test_delete.py
@@ -1,9 +1,11 @@
 #! coding:utf-8
 
-from sqlalchemy import Column, Integer, String, Table, delete, select, and_, \
+from sqlalchemy import Integer, String, ForeignKey, delete, select, and_, \
     or_
 from sqlalchemy.dialects import mysql
-from sqlalchemy.testing import AssertsCompiledSQL, fixtures
+from sqlalchemy import testing
+from sqlalchemy.testing import AssertsCompiledSQL, fixtures, eq_
+from sqlalchemy.testing.schema import Table, Column
 
 
 class _DeleteTestBase(object):
@@ -99,3 +101,142 @@ class DeleteTest(_DeleteTestBase, fixtures.TablesTest, AssertsCompiledSQL):
                             'FROM myothertable '
                             'WHERE myothertable.otherid = mytable.myid'
                             ')')
+
+
+class DeleteFromRoundTripTest(fixtures.TablesTest):
+    __backend__ = True
+
+    @classmethod
+    def define_tables(cls, metadata):
+        Table('mytable', metadata,
+              Column('myid', Integer),
+              Column('name', String(30)),
+              Column('description', String(50)))
+        Table('myothertable', metadata,
+              Column('otherid', Integer),
+              Column('othername', String(30)))
+        Table('users', metadata,
+              Column('id', Integer, primary_key=True,
+                     test_needs_autoincrement=True),
+              Column('name', String(30), nullable=False))
+        Table('addresses', metadata,
+              Column('id', Integer, primary_key=True,
+                     test_needs_autoincrement=True),
+              Column('user_id', None, ForeignKey('users.id')),
+              Column('name', String(30), nullable=False),
+              Column('email_address', String(50), nullable=False))
+        Table('dingalings', metadata,
+              Column('id', Integer, primary_key=True,
+                     test_needs_autoincrement=True),
+              Column('address_id', None, ForeignKey('addresses.id')),
+              Column('data', String(30)))
+        Table('update_w_default', metadata,
+              Column('id', Integer, primary_key=True),
+              Column('x', Integer),
+              Column('ycol', Integer, key='y'),
+              Column('data', String(30), onupdate=lambda: "hi"))
+
+    @classmethod
+    def fixtures(cls):
+        return dict(
+            users=(
+                ('id', 'name'),
+                (7, 'jack'),
+                (8, 'ed'),
+                (9, 'fred'),
+                (10, 'chuck')
+            ),
+            addresses=(
+                ('id', 'user_id', 'name', 'email_address'),
+                (1, 7, 'x', 'jack@bean.com'),
+                (2, 8, 'x', 'ed@wood.com'),
+                (3, 8, 'x', 'ed@bettyboop.com'),
+                (4, 8, 'x', 'ed@lala.com'),
+                (5, 9, 'x', 'fred@fred.com')
+            ),
+            dingalings=(
+                ('id', 'address_id', 'data'),
+                (1, 2, 'ding 1/2'),
+                (2, 5, 'ding 2/5')
+            ),
+        )
+
+    @testing.requires.delete_from
+    def test_exec_two_table(self):
+        users, addresses = self.tables.users, self.tables.addresses
+        dingalings = self.tables.dingalings
+
+        with testing.db.connect() as conn:
+            conn.execute(dingalings.delete())  # fk violation otherwise
+
+            conn.execute(
+                addresses.delete().
+                where(users.c.id == addresses.c.user_id).
+                where(users.c.name == 'ed')
+            )
+
+            expected = [
+                (1, 7, 'x', 'jack@bean.com'),
+                (5, 9, 'x', 'fred@fred.com')
+            ]
+        self._assert_table(addresses, expected)
+
+    @testing.requires.delete_from
+    def test_exec_three_table(self):
+        users = self.tables.users
+        addresses = self.tables.addresses
+        dingalings = self.tables.dingalings
+
+        testing.db.execute(
+            dingalings.delete().
+            where(users.c.id == addresses.c.user_id).
+            where(users.c.name == 'ed').
+            where(addresses.c.id == dingalings.c.address_id))
+
+        expected = [
+            (2, 5, 'ding 2/5')
+        ]
+        self._assert_table(dingalings, expected)
+
+    @testing.requires.delete_from
+    def test_exec_two_table_plus_alias(self):
+        users, addresses = self.tables.users, self.tables.addresses
+        dingalings = self.tables.dingalings
+
+        with testing.db.connect() as conn:
+            conn.execute(dingalings.delete())  # fk violation otherwise
+            a1 = addresses.alias()
+            conn.execute(
+                addresses.delete().
+                where(users.c.id == addresses.c.user_id).
+                where(users.c.name == 'ed').
+                where(a1.c.id == addresses.c.id)
+            )
+
+        expected = [
+            (1, 7, 'x', 'jack@bean.com'),
+            (5, 9, 'x', 'fred@fred.com')
+        ]
+        self._assert_table(addresses, expected)
+
+    @testing.requires.delete_from
+    def test_exec_alias_plus_table(self):
+        users, addresses = self.tables.users, self.tables.addresses
+        dingalings = self.tables.dingalings
+
+        d1 = dingalings.alias()
+
+        testing.db.execute(
+            delete(d1).
+            where(users.c.id == addresses.c.user_id).
+            where(users.c.name == 'ed').
+            where(addresses.c.id == d1.c.address_id))
+
+        expected = [
+            (2, 5, 'ding 2/5')
+        ]
+        self._assert_table(dingalings, expected)
+
+    def _assert_table(self, table, expected):
+        stmt = table.select().order_by(table.c.id)
+        eq_(testing.db.execute(stmt).fetchall(), expected)
diff --git a/test/sql/test_update.py b/test/sql/test_update.py
index 71ac82ce0..9ebaddffd 100644
--- a/test/sql/test_update.py
+++ b/test/sql/test_update.py
@@ -391,7 +391,7 @@ class UpdateTest(_UpdateFromTestBase, fixtures.TablesTest, AssertsCompiledSQL):
             where(table1.c.name == sel.c.othername).\
             values(name='foo')
 
-        dialect = default.DefaultDialect()
+        dialect = default.StrCompileDialect()
         dialect.positional = True
         self.assert_compile(
             upd,
@@ -419,7 +419,7 @@ class UpdateTest(_UpdateFromTestBase, fixtures.TablesTest, AssertsCompiledSQL):
 
 class UpdateFromCompileTest(_UpdateFromTestBase, fixtures.TablesTest,
                             AssertsCompiledSQL):
-    __dialect__ = 'default'
+    __dialect__ = 'default_enhanced'
 
     run_create_tables = run_inserts = run_deletes = None