On Jul 8, 2010, at 3:28 PM, Mike Lewis wrote:
> That's a fair point.
>
> I think one interface might be casting a FromClause into a WithClause
> similar to how one would alias something.
>
> With postgres it seams like when going from a WITH to WITH recursive
> is adding a UNION ALL and the recursive term. Throwing out an idea for
> an interface (for postgres at least):
>
> Say you want to make
>
> WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
>SELECT sub_part, part, quantity FROM parts WHERE part =
> 'our_product'
> UNION ALL
>SELECT p.sub_part, p.part, p.quantity
>FROM included_parts pr, parts p
>WHERE p.part = pr.sub_part
> )
>
>
> First:
>
>included_parts = with_([parts.c.subpart, parts.c.part,
> parts.c.quantity], whereclause=parts.c.part=='our_product') # could
> also cast a SelectClause (or maybe even selectable) to a with_ by
> using selectable.with_()
>
>included_parts would compile to "WITH include_parts AS (SELECT
> sub_part, part, quantity FROM parts WHERE part = 'our_product')"
>
> then we do:
>
>included_parts = base.recursive(
>[included_parts.c.sub_part, included_parts.c.part,
> included_parts.c.quantity],
>whereclause=included_parts.c.part==parts.c.part,
>all=True)
>
> Which would compile to the desired with clause.
>
>
> then a select(included_parts.c.part) would give you:
>
> WITH RECURSIVE included_parts(sub_part, part, quantity) AS (
>SELECT sub_part, part, quantity FROM parts WHERE part =
> 'our_product'
> UNION ALL
>SELECT p.sub_part, p.part, p.quantity
>FROM included_parts pr, parts p
>WHERE p.part = pr.sub_part
> )
> SELECT included_parts.part,
>
>
>
> Where can I find information on the Oracle syntax and other ones you'd
> like to support?
Oracle seems to be beginning to have WITH support:
http://www.dba-oracle.com/t_with_clause.htm
but their recursive functionality is CONNECT BY:
http://www.dba-oracle.com/t_sql_patterns_recursive.htm
I used "connect by" just a little bit years ago, and thats as much as I've
gotten my head around recursive queries. I havent yet taken the time to work
up a modernized mental picture of them.
>
> On Jul 8, 11:46 am, Michael Bayer wrote:
>> I'd really be interested in defining a system that covers WITH / RECURSIVE
>> entirely, and makes sense both with PG / SQL server as well as Oracle.
>> The work here is figuring out what is common about those two approaches and
>> what an API that is meaningful for both would look like.Implementation
>> and tests are just the grunt work here.
>>
>> On Jul 8, 2010, at 2:03 PM, Mike Lewis wrote:
>>
>>
>>
>>> I'd be interested in prototyping a WithClause or something similar if
>>> you think it might be useful.
>>
>>> I imagine it would have similar semantics to a FromClause but would be
>>> prepended to the query. Currently, I'm not too interested in
>>> prototyping the RECURSIVE part and only care about Postgres.
>>
>>> For me to implement this would it be possible to do this in a non-
>>> intrusive manner (outside of modifying core SA code?) I'd guess it
>>> would have similar semantics to how select() will automatically
>>> include FromClauses that for columns that reference them.
>>
>>> Also, it would probably chain the WITHs automatically too.
>>
>>> Any thoughts?
>>
>>> Thanks,
>>> Mike
>>
>>> On Jul 6, 4:31 pm, Michael Bayer wrote:
this is the current status of that:
>>
http://groups.google.com/group/sqlalchemy/browse_thread/thread/c20a4f...
>>
WITHand OVER seem to be the upcoming things we'll have to work on (PG,
MSSQL, DB2 supportWITH). Oracle makesWITHdifficult.Its also getting
to be time to do a DB2 dialect.
>>
On Jul 6, 2010, at 7:07 PM, Mike Lewis wrote:
>>
> Does SA support this syntax?
>>
> http://www.postgresql.org/docs/9.0/static/queries-with.html
>>
> Thanks,
> Mike
>>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group
> athttp://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 sqlalch...@googlegroups.com.
>>> To unsubscribe from this group, send email to
>>> sqlalchemy+unsubscr...@googlegroups.com.
>>> For more options, visit this group
>>> athttp://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 sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options,
