summaryrefslogtreecommitdiff
path: root/test
diff options
context:
space:
mode:
Diffstat (limited to 'test')
-rw-r--r--test/dialect/mssql/test_compiler.py17
-rw-r--r--test/dialect/mysql/test_compiler.py17
-rw-r--r--test/dialect/postgresql/test_compiler.py16
-rw-r--r--test/dialect/test_sybase.py17
-rw-r--r--test/orm/test_update_delete.py33
-rw-r--r--test/requirements.py6
-rw-r--r--test/sql/test_compiler.py2
-rw-r--r--test/sql/test_delete.py145
-rw-r--r--test/sql/test_update.py4
9 files changed, 252 insertions, 5 deletions
diff --git a/test/dialect/mssql/test_compiler.py b/test/dialect/mssql/test_compiler.py
index 1f4a4da4b..d62753b9d 100644
--- a/test/dialect/mssql/test_compiler.py
+++ b/test/dialect/mssql/test_compiler.py
@@ -139,6 +139,23 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
"sometable.somecolumn = :somecolumn_1"
)
+ def test_delete_extra_froms(self):
+ t1 = table('t1', column('c1'))
+ t2 = table('t2', column('c1'))
+ q = sql.delete(t1).where(t1.c.c1 == t2.c.c1)
+ self.assert_compile(
+ q, "DELETE FROM t1 FROM t1, t2 WHERE t1.c1 = t2.c1"
+ )
+
+ def test_delete_extra_froms_alias(self):
+ a1 = table('t1', column('c1')).alias('a1')
+ t2 = table('t2', column('c1'))
+ q = sql.delete(a1).where(a1.c.c1 == t2.c.c1)
+ self.assert_compile(
+ q, "DELETE FROM a1 FROM t1 AS a1, t2 WHERE a1.c1 = t2.c1"
+ )
+ self.assert_compile(sql.delete(a1), "DELETE FROM t1 AS a1")
+
def test_update_from_hint(self):
t = table('sometable', column('somecolumn'))
t2 = table('othertable', column('somecolumn'))
diff --git a/test/dialect/mysql/test_compiler.py b/test/dialect/mysql/test_compiler.py
index cebbfc896..fdf799539 100644
--- a/test/dialect/mysql/test_compiler.py
+++ b/test/dialect/mysql/test_compiler.py
@@ -232,6 +232,23 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = %s LOCK IN SHARE MODE")
+ def test_delete_extra_froms(self):
+ t1 = table('t1', column('c1'))
+ t2 = table('t2', column('c1'))
+ q = sql.delete(t1).where(t1.c.c1 == t2.c.c1)
+ self.assert_compile(
+ q, "DELETE FROM t1 USING t1, t2 WHERE t1.c1 = t2.c1"
+ )
+
+ def test_delete_extra_froms_alias(self):
+ a1 = table('t1', column('c1')).alias('a1')
+ t2 = table('t2', column('c1'))
+ q = sql.delete(a1).where(a1.c.c1 == t2.c.c1)
+ self.assert_compile(
+ q, "DELETE FROM a1 USING t1 AS a1, t2 WHERE a1.c1 = t2.c1"
+ )
+ self.assert_compile(sql.delete(a1), "DELETE FROM t1 AS a1")
+
class SQLTest(fixtures.TestBase, AssertsCompiledSQL):
diff --git a/test/dialect/postgresql/test_compiler.py b/test/dialect/postgresql/test_compiler.py
index 18940ed5f..db142a657 100644
--- a/test/dialect/postgresql/test_compiler.py
+++ b/test/dialect/postgresql/test_compiler.py
@@ -1091,6 +1091,22 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
"FROM table1 AS foo"
)
+ def test_delete_extra_froms(self):
+ t1 = table('t1', column('c1'))
+ t2 = table('t2', column('c1'))
+ q = delete(t1).where(t1.c.c1 == t2.c.c1)
+ self.assert_compile(
+ q, "DELETE FROM t1 USING t2 WHERE t1.c1 = t2.c1"
+ )
+
+ def test_delete_extra_froms_alias(self):
+ a1 = table('t1', column('c1')).alias('a1')
+ t2 = table('t2', column('c1'))
+ q = delete(a1).where(a1.c.c1 == t2.c.c1)
+ self.assert_compile(
+ q, "DELETE FROM t1 AS a1 USING t2 WHERE a1.c1 = t2.c1"
+ )
+
class InsertOnConflictTest(fixtures.TestBase, AssertsCompiledSQL):
__dialect__ = postgresql.dialect()
diff --git a/test/dialect/test_sybase.py b/test/dialect/test_sybase.py
index fee72a5b7..602747106 100644
--- a/test/dialect/test_sybase.py
+++ b/test/dialect/test_sybase.py
@@ -32,3 +32,20 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL):
"Sybase ASE does not support OFFSET",
stmt.compile, dialect=self.__dialect__
)
+
+ def test_delete_extra_froms(self):
+ t1 = sql.table('t1', sql.column('c1'))
+ t2 = sql.table('t2', sql.column('c1'))
+ q = sql.delete(t1).where(t1.c.c1 == t2.c.c1)
+ self.assert_compile(
+ q, "DELETE FROM t1 FROM t1, t2 WHERE t1.c1 = t2.c1"
+ )
+
+ def test_delete_extra_froms_alias(self):
+ a1 = sql.table('t1', sql.column('c1')).alias('a1')
+ t2 = sql.table('t2', sql.column('c1'))
+ q = sql.delete(a1).where(a1.c.c1 == t2.c.c1)
+ self.assert_compile(
+ q, "DELETE FROM a1 FROM t1 AS a1, t2 WHERE a1.c1 = t2.c1"
+ )
+ self.assert_compile(sql.delete(a1), "DELETE FROM t1 AS a1")
diff --git a/test/orm/test_update_delete.py b/test/orm/test_update_delete.py
index e387ad9e6..98fcc4f00 100644
--- a/test/orm/test_update_delete.py
+++ b/test/orm/test_update_delete.py
@@ -726,6 +726,7 @@ class UpdateDeleteIgnoresLoadersTest(fixtures.MappedTest):
class UpdateDeleteFromTest(fixtures.MappedTest):
+ __backend__ = True
@classmethod
def define_tables(cls, metadata):
@@ -802,6 +803,25 @@ class UpdateDeleteFromTest(fixtures.MappedTest):
(5, True), (6, None)])
)
+ @testing.requires.delete_from
+ def test_delete_from_joined_subq_test(self):
+ Document = self.classes.Document
+ s = Session()
+
+ subq = s.query(func.max(Document.title).label('title')).\
+ group_by(Document.user_id).subquery()
+
+ s.query(Document).filter(Document.title == subq.c.title).\
+ delete(synchronize_session=False)
+
+ eq_(
+ set(s.query(Document.id, Document.flag)),
+ set([
+ (2, None),
+ (3, None),
+ (6, None)])
+ )
+
def test_no_eval_against_multi_table_criteria(self):
User = self.classes.User
Document = self.classes.Document
@@ -1016,6 +1036,19 @@ class InheritTest(fixtures.DeclarativeMappedTest):
set([('e1', 'e1', ), ('e2', 'e5')])
)
+ @testing.requires.delete_from
+ def test_delete_from(self):
+ 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()
+
+ eq_(
+ set(s.query(Person.name, Engineer.engineer_name)),
+ set([('e1', 'e1', )])
+ )
+
@testing.only_on('mysql', 'Multi table update')
def test_update_from_multitable(self):
Engineer = self.classes.Engineer
diff --git a/test/requirements.py b/test/requirements.py
index 39a78dfa5..be85c1c0d 100644
--- a/test/requirements.py
+++ b/test/requirements.py
@@ -325,6 +325,12 @@ class DefaultRequirements(SuiteRequirements):
"Backend does not support UPDATE..FROM")
@property
+ def delete_from(self):
+ """Target must support DELETE FROM..FROM or DELETE..USING syntax"""
+ return only_on(['postgresql', 'mssql', 'mysql', 'sybase'],
+ "Backend does not support UPDATE..FROM")
+
+ @property
def update_where_target_in_subquery(self):
"""Target must support UPDATE where the same table is present in a
subquery in the WHERE clause.
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
View Lorry Controller Status