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?
On Jul 8, 11:46 am, Michael Bayer <[email protected]> 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 <[email protected]> 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 [email protected].
> >>> To unsubscribe from this group, send email to
> >>> [email protected].
> >>> 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 [email protected].
> > To unsubscribe from this group, send email to
> > [email protected].
> > 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 [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.
