List:
I am currently trying to add support for FROM ONLY to the postgresql
dialect. FROM ONLY is used when you wish to query a table which has
other tables that inherit from it, but you ONLY want results from the
parent table you are referencing. More information can be found here:
http://www.postgresql.org/docs/current/static/sql-select.html#SQL-FROM
During the course of implementing this, I found that the visit_delete
method did not call _compiler_dispatch on the table targeted by the
delete, preventing table aliases in a delete, and preventing my
implementation of ONLY for delete. I changed this, but the mssql dialect
has some strange aliasing rules, which needed some TLC to function
correctly in the presence of _compiler_dispatch.
Of course, table aliasing in a delete isn't supported by all dialects,
and my current implementation doesn't do anything to protect Jimmy[1].
So there are two patches attached to this email, the first being for
_compiler_dispatch in visit_delete (compiler_dispatch_deletes.patch)
and the other for FROM ONLY in postgres (from_only.patch). The second
one could probably be considering more of a work-in-progress and I'm
actually interested in feedback on whether or not I'm headed in the
right direction with it. It also depends on the first patch.
Also, is this the right list?
-Ryan Kelly
[1] http://www.globalnerdy.com/2010/05/09/new-programming-jargon/
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
diff -r 408388e5faf4 lib/sqlalchemy/dialects/mssql/base.py
--- a/lib/sqlalchemy/dialects/mssql/base.py Fri May 04 23:18:52 2012 -0400
+++ b/lib/sqlalchemy/dialects/mssql/base.py Sat May 05 19:23:12 2012 -0400
@@ -830,6 +830,10 @@
return super(MSSQLCompiler, self).visit_table(table, **kwargs)
def visit_alias(self, alias, **kwargs):
+ if (self.isupdate and not kwargs.get('mssql_update_from', False)
+ or self.isdelete) and not self.isselect:
+ return self.preparer.format_table(alias.original)
+
# translate for schema-qualified table aliases
kwargs['mssql_aliased'] = alias.original
return super(MSSQLCompiler, self).visit_alias(alias, **kwargs)
@@ -951,6 +955,7 @@
well. Otherwise, it is optional. Here, we add it regardless.
"""
+ kw['mssql_update_from'] = True
return "FROM " + ', '.join(
t._compiler_dispatch(self, asfrom=True,
fromhints=from_hints, **kw)
diff -r 408388e5faf4 lib/sqlalchemy/sql/compiler.py
--- a/lib/sqlalchemy/sql/compiler.py Fri May 04 23:18:52 2012 -0400
+++ b/lib/sqlalchemy/sql/compiler.py Sat May 05 19:23:12 2012 -0400
@@ -183,10 +183,10 @@
compound_keywords = COMPOUND_KEYWORDS
- isdelete = isinsert = isupdate = False
+ isselect = isdelete = isinsert = isupdate = False
"""class-level defaults which can be set at the instance
level to define if this Compiled instance represents
- INSERT/UPDATE/DELETE
+ SELECT/INSERT/UPDATE/DELETE
"""
returning = None
@@ -871,6 +871,7 @@
def visit_select(self, select, asfrom=False, parens=True,
iswrapper=False, fromhints=None,
compound_index=1, **kwargs):
+ self.isselect = True
entry = self.stack and self.stack[-1] or {}
@@ -1423,7 +1424,8 @@
self.stack.append({'from': set([delete_stmt.table])})
self.isdelete = True
- text = "DELETE FROM " + self.preparer.format_table(delete_stmt.table)
+ text = "DELETE FROM "
+ text += delete_stmt.table._compiler_dispatch(self, asfrom=True)
if delete_stmt._hints:
dialect_hints = dict([
@@ -1447,7 +1449,8 @@
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 -r 408388e5faf4 test/sql/test_compiler.py
--- a/test/sql/test_compiler.py Fri May 04 23:18:52 2012 -0400
+++ b/test/sql/test_compiler.py Sat May 05 19:23:12 2012 -0400
@@ -2948,6 +2948,12 @@
"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)
diff -r 935a3fa11c41 lib/sqlalchemy/dialects/postgresql/base.py
--- a/lib/sqlalchemy/dialects/postgresql/base.py Sat May 05 18:59:56 2012 -0400
+++ b/lib/sqlalchemy/dialects/postgresql/base.py Sat May 05 19:31:49 2012 -0400
@@ -113,6 +113,23 @@
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.
+
+ # SELECT ... FROM ONLY
+ result = only(table).select()
+ print result.fetchall()
+
+ # UPDATE ONLY ...
+ only(table).update(values=dict(foo='bar'))
+
+ # DELETE FROM ONLY ...
+ only(table).delete()
.. _postgresql_indexes:
@@ -592,7 +609,42 @@
'interval day to second':INTERVAL,
}
+class ONLY(expression.TableClause):
+ """Represents an 'FROM ONLY' table type query, as used in PostgreSQL.
+ This object is constructed from the :func:`~.only` module level function.
+
+ """
+
+ __visit_name__ = 'only'
+
+ def __init__(self, table):
+ super(ONLY, self).__init__(table.name, *table.columns)
+ if not isinstance(table, expression.TableClause):
+ raise ValueError("'%r' is not an instance of '%r'" %
+ (table, expression.TableClause))
+
+ if isinstance(table, ONLY):
+ table = table.element
+
+ self.element = table
+ self._columns = table.columns
+
+ @property
+ def bind(self):
+ return self.element.bind
+
+def only(table):
+ """Return an :class:`.ONLY` object.
+
+ When an :class:`.ONLY` is created with a :class:`.Table` object,
+ this has the effect of the table being rendered as ``ONLY tablename`` in,
+ e.g., a SELECT statement's FROM.
+
+ :param table: any :class:`.TableClause` subclass, e.g. a table, only, etc.
+
+ """
+ return ONLY(table)
class PGCompiler(compiler.SQLCompiler):
@@ -692,6 +744,20 @@
return "EXTRACT(%s FROM %s)" % (
field, self.process(expr))
+ def visit_only(self, table, asfrom=False, **kwargs):
+ if asfrom:
+ table = table.element
+ if getattr(table, "schema", None):
+ schema = self.preparer.quote_schema(table.schema,
+ table.quote_schema)
+ name = self.preparer.quote(table.name, table.quote)
+ ret = schema + "." + name
+ else:
+ ret = self.preparer.quote(table.name, table.quote)
+ return "ONLY " + ret
+ else:
+ return ""
+
class PGDDLCompiler(compiler.DDLCompiler):
def get_column_specification(self, column, **kwargs):
colspec = self.preparer.format_column(column)
diff -r 935a3fa11c41 test/dialect/test_postgresql.py
--- a/test/dialect/test_postgresql.py Sat May 05 18:59:56 2012 -0400
+++ b/test/dialect/test_postgresql.py Sat May 05 19:31:49 2012 -0400
@@ -272,6 +272,58 @@
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 = postgresql.only(tbl1).select()
+ expected = 'SELECT testtbl1.id FROM ONLY testtbl1'
+ self.assert_compile(stmt, expected)
+
+ stmt = postgresql.only(tbl1).alias('foo').select()
+ expected = 'SELECT foo.id FROM ONLY testtbl1 AS foo'
+ self.assert_compile(stmt, expected)
+
+ stmt = select([postgresql.only(tbl1), tbl2])
+ expected = ('SELECT testtbl1.id, testtbl2.id FROM ONLY testtbl1, '
+ 'testtbl2')
+ self.assert_compile(stmt, expected)
+
+ stmt = select([tbl1, postgresql.only(tbl2)])
+ expected = ('SELECT testtbl1.id, testtbl2.id FROM testtbl1, ONLY '
+ 'testtbl2')
+ self.assert_compile(stmt, expected)
+
+ stmt = select([postgresql.only(tbl1), postgresql.only(tbl2)])
+ expected = ('SELECT testtbl1.id, testtbl2.id FROM ONLY testtbl1, '
+ 'ONLY testtbl2')
+ self.assert_compile(stmt, expected)
+
+ stmt = update(postgresql.only(tbl1), values=dict(id=1))
+ expected = 'UPDATE testtbl1 SET id=%(id)s'
+ self.assert_compile(stmt, expected)
+
+ stmt = postgresql.only(tbl1).update().values(id=1)
+ expected = 'UPDATE testtbl1 SET id=%(id)s'
+ self.assert_compile(stmt, expected)
+
+ stmt = delete(postgresql.only(tbl1))
+ expected = 'DELETE FROM ONLY testtbl1'
+ self.assert_compile(stmt, expected)
+
+ stmt = postgresql.only(tbl1).delete()
+ expected = 'DELETE FROM ONLY testtbl1'
+ self.assert_compile(stmt, expected)
+
+ stmt = select([postgresql.only(tbl1).alias('sq')])
+ expected = 'SELECT sq.id FROM ONLY testtbl1 AS sq'
+ self.assert_compile(stmt, expected)
+
+ tbl3 = Table('testtbl3', m, Column('id', Integer), schema='testschema')
+ stmt = postgresql.only(tbl3).select()
+ expected = 'SELECT testtbl3.id FROM ONLY testschema.testtbl3'
+ self.assert_compile(stmt, expected)
class FloatCoercionTest(fixtures.TablesTest, AssertsExecutionResults):
__only_on__ = 'postgresql'
