On Sat, May 05, 2012 at 08:00:20PM -0400, Michael Bayer wrote:
>
> On May 5, 2012, at 7:33 PM, Ryan Kelly wrote:
>
> > 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
>
> OK, this looks like a nice patch, though I'm not sure it's consistent with
> how we've approached this kind of SQL feature in other cases. "ONLY" looks
> a lot like an optimization hint added to the FROM clause. We already have an
> API for this, called with_hint(). with_hint() is intended to be supported
> by all of INSERT, UPDATE, DELETE in addition to SELECT. I'm not sure at
> the moment what it does with PG right now but it might be a more appropriate
> approach. Take a look and let me know if you have thoughts on that.
I had seen that when I started implementing this, but I felt that 'logically'
speaking, ONLY was more like an alias than a hint. I tried just now to
implement it using hints, but a lot of the code makes assumptions about
the location of hints with regards to the table name, i.e., the hint
always comes after the table name. ONLY always appears before. And I'm
not entirely sure how it would work if the same table is used twice in a
statement. ONLY essentially is a different kind of table object. Maybe I'm
missing something here.
> >
> > 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.
>
> Also it seems a little awkward that DELETE now defers to generic compilation
> to format the table, but still not INSERT or UPDATE which still hardcode to
> preparer.format_table().
For update this should probably be changed, and I've attached a patch to
do so. I'm not sure how much sense this makes for insert. I don't think you
can use anything but the name of the table (schema qualified) in an insert in
any DB. I do not believe that hints/aliases/ONLY could ever make sense in
this context. Maybe I'm missing something, besides the symmetry aspects of
it?
> >
> > 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].
>
> is aliasing of a table also a different feature need here ? Which DBs
> support this ?
As far as I know, it works on PostgreSQL and Firebird. It does not work
on SQLite, MSSQL, DB2, and Oracle. I have not tried on Sybase.
> >
> > 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?
>
>
> sure, there's also sqlalchemy-devel, though for features/patches you can also
> use trac tickets or bitbucket forks/pull requests....
>
> thanks for the patches and interest !
Welcome. Sorry it took so long to follow up on this, I've been busy.
-Ryan Kelly
>
>
>
>
>
> >
> > -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.
> >
> > <compiler_dispatch_deletes.patch><from_only.patch>
>
> -- 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.
>
--
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 ea4bd6b54789 lib/sqlalchemy/dialects/mssql/base.py
--- a/lib/sqlalchemy/dialects/mssql/base.py Fri Jun 08 15:56:58 2012 -0400
+++ b/lib/sqlalchemy/dialects/mssql/base.py Sat Jun 09 18:50:44 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 ea4bd6b54789 lib/sqlalchemy/sql/compiler.py
--- a/lib/sqlalchemy/sql/compiler.py Fri Jun 08 15:56:58 2012 -0400
+++ b/lib/sqlalchemy/sql/compiler.py Sat Jun 09 18:50:44 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 {}
@@ -1116,7 +1117,7 @@
MySQL overrides this.
"""
- return self.preparer.format_table(from_table)
+ return from_table._compiler_dispatch(self, asfrom=True, **kw)
def update_from_clause(self, update_stmt,
from_table, extra_froms,
@@ -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 ea4bd6b54789 test/sql/test_compiler.py
--- a/test/sql/test_compiler.py Fri Jun 08 15:56:58 2012 -0400
+++ b/test/sql/test_compiler.py Sat Jun 09 18:50:44 2012 -0400
@@ -2866,6 +2866,17 @@
"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 = {
@@ -2948,6 +2959,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'
