CTEs, recursive CTEs and JOOQ

[Nicholas Ustinov]

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.")

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)

Thank you,

Nkolay

[Lukas Eder]

Hello,

2013/9/10 Nicholas Ustinov <nust...@gmail.com>

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

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

[Ben Hood]

Hey Nkolay,

On Tue, Sep 10, 2013 at 5:41 PM, Nicholas Ustinov <nust...@gmail.com> wrote:
> 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)

FWIW we had a requirement to support cross database hierarchical
queries with JOOQ. We ended up taking a closure tree approach, which
doesn't take advantage of any native DB support for hierarchical
queries and forced us to redesign the the layout of the affect tables.
But it is portable and appears to scale (at least for our workloads).

HTH,

Ben

[Nicholas Ustinov]

Thank you for sharing experience!

Have you ever experienced troubles with recursive CTEs in Postgres or MSSQL? 

I worked only with Oracle's connect by and it works good enough, but I have no experience with scalability of recursive CTEs in Postgres.

Nikolay

среда, 11 сентября 2013 г., 12:58:02 UTC+4 пользователь Ben Hood написал:

[Nicholas Ustinov]

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 написал:

[Ben Hood]

Hey Nikolay,

On Wed, Sep 11, 2013 at 10:26 AM, Nicholas Ustinov <nust...@gmail.com> wrote:
> Thank you for sharing experience!
> Have you ever experienced troubles with recursive CTEs in Postgres or MSSQL?

I haven't experienced specific troubles with recursive CTEs, but to
put this into context, I was talking about an alternative to recursive
CTEs and CONNECT BY. I modified the recursive table using a closure
tree pattern, which boils down to some pretty plain jane SQL-90 joins,
hence avoiding any constructs that don't have middle of the road
support across different DBs.

> I worked only with Oracle's connect by and it works good enough, but I have
> no experience with scalability of recursive CTEs in Postgres.

I imagine that the closure tree pattern would have similar algorithmic
complexities across different DBs. Whether it will be good enough for
what you are doing could only be established by testing it. Also, it
generally means that you need to change your table layout somewhat.

Another option you have is to programmatically construct DB specific
SQL based on the run time dialect. Whilst this might mean more
maintenance, JOOQ at least provides a neat API to manage SQL statement
building blocks, so you've at least got a chance of keeping your code
both well factored and portable.

Does that answer your question?

Ben

[Ben Hood]

On Wed, Sep 11, 2013 at 10:40 AM, Nicholas Ustinov <nust...@gmail.com> wrote:
> 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.

I would argue that this is precisely what JOOQ allows to do, namely to
mix common DB access code with DB specific code in single statements.
The JOOQ API is designed to let you reuse substantial amounts of
common DB code whilst still leaving the flexibility to tweak your
queries for specific dialects.

[Lukas Eder]

2013/9/11 Ben Hood <0x6e...@gmail.com>

What about factoring out this specific logic to equivalent database views using CONNECT BY with Oracle, CTE with all others?

[Lukas Eder]

2013/9/11 Nicholas Ustinov <nust...@gmail.com>

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)

In general, there's no such thing as a "cross DB" syntax. There are just db-specific features that can be emulated in *some* other databases. Such as CONNECT BY, which can be simulated with CTE.

 

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)

Maybe :-)

 

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...

Yes, please attempt to express it in code. I think I get your idea. You mean something similar to how PL/SQL, Ada allow for declaring stuff first and then providing the actual implementation further down?

 

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.

I'm not sure what you mean by this.

Cheers

Lukas

[Nicholas Ustinov]

Well, thank you for clarifications!

We'd prefer to use db-specific implementations of hierarchical queries, because all target DB platform have support of them. 

So we will try to do smth with JOOQ..

Nikolay

среда, 11 сентября 2013 г., 17:54:23 UTC+4 пользователь Ben Hood написал:

[Eric Schwarzenbach]

You could also implement a transitive closure as a table, as a materialized view, possibly via a trigger. This can be advantageous for performance in many cases. I think of such a table as effectively acting like an index. It would be nice if the database provided such a hierarchical index type, but AFAIK most don't, so such an approach is sometimes unavoidable when scalability of hierarchical queries is a concerns. FWIW, I've done this for an app that had to work both with Postgresql and Sql Server (though it wasn't with JOOQ).