From d5609d77841ab4e607e6b372a15396b38ddace9a Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 21 Jun 2012 16:32:38 -0400 Subject: [PATCH] - [feature] Added "MATCH" clause to ForeignKey, ForeignKeyConstraint, courtesy Ryan Kelly. [ticket:2502] - [feature] Added support for DELETE and UPDATE from an alias of a table, which would assumedly be related to itself elsewhere in the query, courtesy Ryan Kelly. [ticket:2507] - [feature] Added support for the Postgresql ONLY keyword, which can appear corresponding to a table in a SELECT, UPDATE, or DELETE statement. The phrase is established using with_hint(). Courtesy Ryan Kelly [ticket:2506] --- CHANGES | 12 ++++ lib/sqlalchemy/dialects/mssql/base.py | 4 +- lib/sqlalchemy/dialects/postgresql/base.py | 23 ++++++++ lib/sqlalchemy/sql/compiler.py | 66 ++++++++++++++-------- test/dialect/test_postgresql.py | 50 ++++++++++++++++ test/sql/test_compiler.py | 18 ++++++ 6 files changed, 149 insertions(+), 24 deletions(-) diff --git a/CHANGES b/CHANGES index 7e59d638a1..5143cc218d 100644 --- a/CHANGES +++ b/CHANGES @@ -203,6 +203,11 @@ underneath "0.7.xx". ForeignKeyConstraint, courtesy Ryan Kelly. [ticket:2502] + - [feature] Added support for DELETE and UPDATE from + an alias of a table, which would assumedly + be related to itself elsewhere in the query, + courtesy Ryan Kelly. [ticket:2507] + - [feature] select() features a correlate_except() method, auto correlates all selectables except those passed. @@ -252,6 +257,13 @@ underneath "0.7.xx". performance of bind/result processing. [ticket:2441] + - [feature] Added support for the Postgresql ONLY + keyword, which can appear corresponding to a + table in a SELECT, UPDATE, or DELETE statement. + The phrase is established using with_hint(). + Courtesy Ryan Kelly [ticket:2506] + + - mysql - [bug] Dialect no longer emits expensive server collations query, as well as server casing, diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index 4d0af7cbe0..4303660bff 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -818,8 +818,8 @@ class MSSQLCompiler(compiler.SQLCompiler): else: return None - def visit_table(self, table, mssql_aliased=False, **kwargs): - if mssql_aliased is table: + def visit_table(self, table, mssql_aliased=False, iscrud=False, **kwargs): + if mssql_aliased is table or iscrud: return super(MSSQLCompiler, self).visit_table(table, **kwargs) # alias schema-qualified tables diff --git a/lib/sqlalchemy/dialects/postgresql/base.py b/lib/sqlalchemy/dialects/postgresql/base.py index 7363b23340..248d39ed69 100644 --- a/lib/sqlalchemy/dialects/postgresql/base.py +++ b/lib/sqlalchemy/dialects/postgresql/base.py @@ -114,6 +114,24 @@ use the :meth:`._UpdateBase.returning` method on a per-statement basis:: where(table.c.name=='foo') print result.fetchall() +FROM ONLY ... +------------------------ + +The dialect supports PostgreSQL's ONLY keyword for targeting only a particular +table in an inheritance hierarchy. This can be used to produce the +``SELECT ... FROM ONLY``, ``UPDATE ONLY ...``, and ``DELETE FROM ONLY ...`` +syntaxes. It uses SQLAlchemy's hints mechanism: + + # SELECT ... FROM ONLY ... + result = table.select().with_hint(table, 'ONLY', 'postgresql') + print result.fetchall() + + # UPDATE ONLY ... + table.update(values=dict(foo='bar')).with_hint('ONLY', + dialect_name='postgresql') + + # DELETE FROM ONLY ... + table.delete().with_hint('ONLY', dialect_name='postgresql') .. _postgresql_indexes: @@ -642,6 +660,11 @@ class PGCompiler(compiler.SQLCompiler): text += " OFFSET " + self.process(sql.literal(select._offset)) return text + def format_from_hint_text(self, sqltext, table, hint, iscrud): + if hint.upper() != 'ONLY': + raise exc.CompileError("Unrecognized hint: %r" % hint) + return "ONLY " + sqltext + def get_select_precolumns(self, select): if select._distinct is not False: if select._distinct is True: diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 8a8c773f82..e0cdbe24c5 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -808,6 +808,7 @@ class SQLCompiler(engine.Compiled): return text def visit_alias(self, alias, asfrom=False, ashint=False, + iscrud=False, fromhints=None, **kwargs): if asfrom or ashint: if isinstance(alias.name, sql._truncated_label): @@ -824,9 +825,8 @@ class SQLCompiler(engine.Compiled): self.preparer.format_alias(alias, alias_name) if fromhints and alias in fromhints: - hinttext = self.get_from_hint_text(alias, fromhints[alias]) - if hinttext: - ret += " " + hinttext + ret = self.format_from_hint_text(ret, alias, + fromhints[alias], iscrud) return ret else: @@ -863,6 +863,12 @@ class SQLCompiler(engine.Compiled): else: return column + def format_from_hint_text(self, sqltext, table, hint, iscrud): + hinttext = self.get_from_hint_text(table, hint) + if hinttext: + sqltext += " " + hinttext + return sqltext + def get_select_hint_text(self, byfroms): return None @@ -1025,7 +1031,7 @@ class SQLCompiler(engine.Compiled): text += " OFFSET " + self.process(sql.literal(select._offset)) return text - def visit_table(self, table, asfrom=False, ashint=False, + def visit_table(self, table, asfrom=False, iscrud=False, ashint=False, fromhints=None, **kwargs): if asfrom or ashint: if getattr(table, "schema", None): @@ -1036,9 +1042,8 @@ class SQLCompiler(engine.Compiled): else: ret = self.preparer.quote(table.name, table.quote) if fromhints and table in fromhints: - hinttext = self.get_from_hint_text(table, fromhints[table]) - if hinttext: - ret += " " + hinttext + ret = self.format_from_hint_text(ret, table, + fromhints[table], iscrud) return ret else: return "" @@ -1073,7 +1078,8 @@ class SQLCompiler(engine.Compiled): if prefixes: text += " " + " ".join(prefixes) - text += " INTO " + preparer.format_table(insert_stmt.table) + text += " INTO " + table_text = preparer.format_table(insert_stmt.table) if insert_stmt._hints: dialect_hints = dict([ @@ -1083,11 +1089,15 @@ class SQLCompiler(engine.Compiled): if dialect in ('*', self.dialect.name) ]) if insert_stmt.table in dialect_hints: - text += " " + self.get_crud_hint_text( + table_text = self.format_from_hint_text( + table_text, insert_stmt.table, - dialect_hints[insert_stmt.table] + dialect_hints[insert_stmt.table], + True ) + text += table_text + if colparams or not supports_default_values: text += " (%s)" % ', '.join([preparer.format_column(c[0]) for c in colparams]) @@ -1123,7 +1133,8 @@ class SQLCompiler(engine.Compiled): MySQL overrides this. """ - return self.preparer.format_table(from_table) + return from_table._compiler_dispatch(self, asfrom=True, + iscrud=True, **kw) def update_from_clause(self, update_stmt, from_table, extra_froms, @@ -1149,10 +1160,9 @@ class SQLCompiler(engine.Compiled): colparams = self._get_colparams(update_stmt, extra_froms) - text = "UPDATE " + self.update_tables_clause( - update_stmt, - update_stmt.table, - extra_froms, **kw) + text = "UPDATE " + table_text = self.update_tables_clause(update_stmt, update_stmt.table, + extra_froms, **kw) if update_stmt._hints: dialect_hints = dict([ @@ -1162,13 +1172,17 @@ class SQLCompiler(engine.Compiled): if dialect in ('*', self.dialect.name) ]) if update_stmt.table in dialect_hints: - text += " " + self.get_crud_hint_text( + table_text = self.format_from_hint_text( + table_text, update_stmt.table, - dialect_hints[update_stmt.table] + dialect_hints[update_stmt.table], + True ) else: dialect_hints = None + text += table_text + text += ' SET ' if extra_froms and self.render_table_with_column_in_update_from: text += ', '.join( @@ -1430,7 +1444,9 @@ class SQLCompiler(engine.Compiled): self.stack.append({'from': set([delete_stmt.table])}) self.isdelete = True - text = "DELETE FROM " + self.preparer.format_table(delete_stmt.table) + text = "DELETE FROM " + table_text = delete_stmt.table._compiler_dispatch(self, + asfrom=True, iscrud=True) if delete_stmt._hints: dialect_hints = dict([ @@ -1440,13 +1456,18 @@ class SQLCompiler(engine.Compiled): if dialect in ('*', self.dialect.name) ]) if delete_stmt.table in dialect_hints: - text += " " + self.get_crud_hint_text( - delete_stmt.table, - dialect_hints[delete_stmt.table] + table_text = self.format_from_hint_text( + table_text, + delete_stmt.table, + dialect_hints[delete_stmt.table], + True ) + else: dialect_hints = None + text += table_text + if delete_stmt._returning: self.returning = delete_stmt._returning if self.returning_precedes_values: @@ -1454,7 +1475,8 @@ class SQLCompiler(engine.Compiled): delete_stmt, delete_stmt._returning) if delete_stmt._whereclause is not None: - text += " WHERE " + self.process(delete_stmt._whereclause) + text += " WHERE " + text += delete_stmt._whereclause._compiler_dispatch(self) if self.returning and not self.returning_precedes_values: text += " " + self.returning_clause( diff --git a/test/dialect/test_postgresql.py b/test/dialect/test_postgresql.py index 5729bf3541..b50b0dcbbf 100644 --- a/test/dialect/test_postgresql.py +++ b/test/dialect/test_postgresql.py @@ -272,6 +272,56 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile(x, '''SELECT pg_table.col1, pg_table."variadic" FROM pg_table''') + def test_from_only(self): + m = MetaData() + tbl1 = Table('testtbl1', m, Column('id', Integer)) + tbl2 = Table('testtbl2', m, Column('id', Integer)) + + stmt = tbl1.select().with_hint(tbl1, 'ONLY', 'postgresql') + expected = 'SELECT testtbl1.id FROM ONLY testtbl1' + self.assert_compile(stmt, expected) + + talias1 = tbl1.alias('foo') + stmt = talias1.select().with_hint(talias1, 'ONLY', 'postgresql') + expected = 'SELECT foo.id FROM ONLY testtbl1 AS foo' + self.assert_compile(stmt, expected) + + stmt = select([tbl1, tbl2]).with_hint(tbl1, 'ONLY', 'postgresql') + expected = ('SELECT testtbl1.id, testtbl2.id FROM ONLY testtbl1, ' + 'testtbl2') + self.assert_compile(stmt, expected) + + stmt = select([tbl1, tbl2]).with_hint(tbl2, 'ONLY', 'postgresql') + expected = ('SELECT testtbl1.id, testtbl2.id FROM testtbl1, ONLY ' + 'testtbl2') + self.assert_compile(stmt, expected) + + stmt = select([tbl1, tbl2]) + stmt = stmt.with_hint(tbl1, 'ONLY', 'postgresql') + stmt = stmt.with_hint(tbl2, 'ONLY', 'postgresql') + expected = ('SELECT testtbl1.id, testtbl2.id FROM ONLY testtbl1, ' + 'ONLY testtbl2') + self.assert_compile(stmt, expected) + + stmt = update(tbl1, values=dict(id=1)) + stmt = stmt.with_hint('ONLY', dialect_name='postgresql') + expected = 'UPDATE ONLY testtbl1 SET id=%(id)s' + self.assert_compile(stmt, expected) + + stmt = delete(tbl1).with_hint('ONLY', selectable=tbl1, dialect_name='postgresql') + expected = 'DELETE FROM ONLY testtbl1' + self.assert_compile(stmt, expected) + + tbl3 = Table('testtbl3', m, Column('id', Integer), schema='testschema') + stmt = tbl3.select().with_hint(tbl3, 'ONLY', 'postgresql') + expected = 'SELECT testschema.testtbl3.id FROM ONLY testschema.testtbl3' + self.assert_compile(stmt, expected) + + assert_raises( + exc.CompileError, + tbl3.select().with_hint(tbl3, "FAKE", "postgresql").compile, + dialect=postgresql.dialect() + ) class FloatCoercionTest(fixtures.TablesTest, AssertsExecutionResults): __only_on__ = 'postgresql' diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index 61dcf61abc..4bf0eb70ea 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -2906,6 +2906,18 @@ class CRUDTest(fixtures.TestBase, AssertsCompiledSQL): "WHERE mytable.myid = hoho(:hoho_1) AND mytable.name = :param_2 || " "mytable.name || :param_3") + def test_aliased_update(self): + talias1 = table1.alias('t1') + self.assert_compile( + update(talias1, talias1.c.myid == 7), + "UPDATE mytable AS t1 SET name=:name WHERE t1.myid = :myid_1", + params = {table1.c.name:'fred'}) + self.assert_compile( + update(talias1, table1.c.myid == 7), + "UPDATE mytable AS t1 SET name=:name FROM " + "mytable WHERE mytable.myid = :myid_1", + params = {table1.c.name:'fred'}) + def test_correlated_update(self): # test against a straight text subquery u = update(table1, values = { @@ -2988,6 +3000,12 @@ class CRUDTest(fixtures.TestBase, AssertsCompiledSQL): "DELETE FROM mytable WHERE mytable.myid = :myid_1 " "AND mytable.name = :name_1") + def test_aliased_delete(self): + talias1 = table1.alias('t1') + self.assert_compile( + delete(talias1).where(talias1.c.myid == 7), + "DELETE FROM mytable AS t1 WHERE t1.myid = :myid_1") + def test_correlated_delete(self): # test a non-correlated WHERE clause s = select([table2.c.othername], table2.c.otherid == 7) -- 2.47.3