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 <mike...@zzzcomputing.com> 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 <mike...@zzzcomputing.com> 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, 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 sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
