1) I suppose, ideally JOOQ should give choice to user: use native syntax for hierarchical queries (recursive CTEs, connect by, or maybe smth else:)) or use some crossDB syntax (maybe based on Oracle one), but with some restrictions (I mean pseudo columns) 2) It's better to have partially implemented feature and possibilities for improving it in future than not to have it at all :) (I mean extended list of pseudo columns too) 3) I suppose, recursive CTEs need to be defined in two steps to allow recursive use of columns: at first we should define header of CTE/view with column list but without actual implementation, and actual implementation, maybe, included in SELECT statement. I'm not so good in JOOQ internal structure, but I can try to express it in code... 4) I think, here, in CTEs, we touch the same problem as problem of defining reusable parts of SQL with implementation and some field contract.
Nikolay вторник, 10 сентября 2013 г., 21:13:33 UTC+4 пользователь Lukas Eder написал: > > Hello, > > 2013/9/10 Nicholas Ustinov <[email protected] <javascript:>> > >> Hello! >> I tried to search info on supporting by JOOQ of common table expressions >> and recursive ones (for hierarchical queries), but didn't find too much >> info. >> I found >> http://blog.jooq.org/2011/10/02/recursive-queries-with-oracles-connect-by-clause/with >> some promises about applying oracle syntax ("In the near future, jOOQ >> is going to project the CONNECT BY syntax and API to other RDBMS's Common >> Table Expression syntax. That way, you can express hierarchical queries in >> any database supporting CTE's using Oracle's CONNECT BY syntax.") >> > > Yes, that was an optimistic perception of "near future". I have given this > some thought. Emulation of CONNECT BY through CTE is a non-trivial task, if > all pseudo-columns should be supported as well. And then, unfortunately, > there had also been other priorities. > > So at all: does JOOQ support CTE (it may be useful in oracle too for other >> reasons than hierarchical queries), recursive CTE or another syntax for >> hierarchical queries in other databases than Oracle? (for now I need >> PostgreSQL and MSSQL Server) >> > > I agree that CTE are a very useful feature. They have been on the roadmap > for a while: > https://github.com/jOOQ/jOOQ/issues/454<https://github.com/jOOQ/jOOQ/issues/454?source=cc> > > So, let's reason about syntax translation: > > 1. The WITH [ RECURSIVE ] clause can easily be preprended to SELECT > statements (in PostgreSQL also to other statements) > 2. This clause would accept an AliasedTable<?>... argument > > To make CTE somewhat useful within jOOQ, a new marker type should probably > be introduced, marking aliased tables: > > AliasedTable<R> a = MY_TABLE.as("a"); > AliasedField<T> f = MY_TABLE.MY_FIELD.as("f"); > > While maintaining all existing Table<?> functionality, these marker types > can then be used in jOOQ's DSL to ensure that only previously aliased > tables (e.g. derived tables) can be passed to the with method. Users are > free to chose whether they want to alias only table names, or also column > names through derived column lists. > > Example from the PostgreSQL documentation > http://www.postgresql.org/docs/9.3/static/queries-with.html: > > WITH RECURSIVE t(n) AS ( > SELECT 1 > UNION ALL > SELECT n+1 FROM t > ) > SELECT n FROM t LIMIT 100; > > This would then translate to this usage in jOOQ: > > AliasedTable<Record1<Integer>> t = table( > select(val(1)) > .unionAll( > select(field("n").add(1)) // Cannot refer to n here... > .from("t")) // Cannot refer to t here... > ).as("t", "n") > > DSL.withRecursive(t [, ...]) > .select(t.field("n")) > .from(t) > .limit(1000); > > > This is just one example. As you can see, there isn't a lot of typesafety > in the above, especially when recursive CTE are involved. I'm very open to > discussing alternative / complementary APIs. For instance (complete lack of > typesafety): > > DSL.withRecursive("t", "n").as( > > select(val(1)) > > .unionAll( > > select(field("n").add(1)) > > .from("t")) > > ) > > [.with(...) ...] > > .select(field("n")) > .from("t") > .limit(1000); > > Feedback welcome! > > Cheers > Lukas > > > -- You received this message because you are subscribed to the Google Groups "jOOQ User Group" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
