Query to Query Parts

[P R]

To enable data isolation (and partitioning in future), our database has a customerId field in majority of the tables. So the expectation is that vast majority of the queries will have a condition like "customerId=..." and appropriate joins to the Customer table. But it is easy to make a mistake and forget to do this. Plus having each and every query in the code do this is introduces a lot of bloat given the joins involved. And hence the thinking is to have all query executions routed through a set of common API that wraps the underlying execution. It is in these common APIs, the query would be inspected prior to execution and accordingly tweaked for compliance.

 E.g. Let's say I'm given a SelectQuery. I would look at the tables involved and accordingly add condition "someTable.customerId=..." to the query. Similarly one should be able to do this for Update or Delete queries.

Now the question. With jOOQ, we can build queries. But after we build a query how do we get the constituent parts to enable the above? If this is not possible, any alternative suggestions other than trying to dissect the string representation of the query.

Thanks.

[Knut Wannheden]

Thanks for your message.

What you want to achieve is very similar to row-level security (RLS) or multi-tenancy. jOOQ does not support this directly out of the box, but you might want to look at this blog entry, which explains how to roll your own using the VisitListener SPI: https://blog.jooq.org/2015/06/17/implementing-client-side-row-level-security-with-jooq/.

As a first step you may however want to check if your database provides any RLS features out-of-the-box (e.g. as in Oracle) or what the recommendations are for implementing this. This would then most likely be the better alternative to the VisitListener approach.

Hope this helps,

Knut

--
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 jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/3d2456e3-4f8e-4af4-bb38-f86db78bc6bf%40googlegroups.com.

[P R]

Thank you very much for the pointer. The VisitListener approach does appear to be a good fit for my use case and I'll pursue it. However, I'm curious, what's the motivation for not exposing the ability to get the query parts. After all jOOQ enables query building and hence one would expect to be able to get a handle to the constituent parts - similar to expecting a getXXX() method if one can invoke setXXX() on a class.

[P R]

I implemented the VisitListener code (from the blog) pretty much as is (had to change the table names and the id field) and have run into a stack over flow error because of infinite recursion. Here's the tweak to the code from the blog to reproduce it:

public void visitEnd(VisitContext context) {
    System.out.println(context.queryPart());
    pushConditions(context, ACCOUNTS, ACCOUNTS.ID, ids);
    ...
}

All I've done is added a System.out statement. I don't know the underlying cause but perhaps may have something to do with the toString() method being called. BTW, in my real code, I'm doing a hash table lookup where the lookup key is, the value returned by context.queryPart() and run into the same issue. The System.out statement is the simplest way to reproduce the problem.

[P R]

The jOOQ version is 3.11.11 and here's a small section of the stack trace showing the toString() call.

Exception in thread "main" java.lang.StackOverflowError
    at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:509)
    at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:446)
    at org.jooq.impl.AbstractContext.visit(AbstractContext.java:207)
    at org.jooq.impl.SchemaImpl.accept(SchemaImpl.java:119)
    at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:509)
    at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:446)
    at org.jooq.impl.AbstractContext.visit(AbstractContext.java:207)
    at org.jooq.impl.TableImpl.accept0(TableImpl.java:245)
    at org.jooq.impl.TableImpl.accept(TableImpl.java:232)
    at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:446)
    at org.jooq.impl.AbstractContext.visit(AbstractContext.java:207)
    at org.jooq.impl.DefaultDSLContext.renderInlined(DefaultDSLContext.java:712)
    at org.jooq.impl.AbstractQueryPart.toString(AbstractQueryPart.java:172)
    at java.lang.String.valueOf(String.java:2994)
    at java.io.PrintStream.println(PrintStream.java:821)
    :
    :
    at com.el.db.sql.CustomerIdFilter.visitEnd(CustomerIdFilter.java:182)
    at org.jooq.impl.AbstractContext.end(AbstractContext.java:288)
    at org.jooq.impl.AbstractContext.visit(AbstractContext.java:211)
    at org.jooq.impl.DefaultDSLContext.renderInlined(DefaultDSLContext.java:712)
    at org.jooq.impl.AbstractQueryPart.toString(AbstractQueryPart.java:172)
    at java.lang.String.valueOf(String.java:2994)
    at java.io.PrintStream.println(PrintStream.java:821)
    at com.el.db.sql.CustomerIdFilter.visitEnd(CustomerIdFilter.java:182)
    at org.jooq.impl.AbstractContext.end(AbstractContext.java:288)
    at org.jooq.impl.AbstractContext.visit(AbstractContext.java:211)

[Rob Sargent]

I suspect you're in an unbounded recursion.

--
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 jooq-user+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/9fe7d630-a812-4bf4-8c29-3bbde5634af3%40googlegroups.com.

[Knut Wannheden]

I have previously also encountered a StackOverflowError in a VisitListener. The reason was that the VisitListener in some situations would always call VisitContext#queryPart(QueryPart) to replace a given QueryPart with another one. This basically resulted in an endless recursion. So the VisitListener must take care to avoid such situations.

Hope this helps,

Knut

--

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 jooq-user+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/9fe7d630-a812-4bf4-8c29-3bbde5634af3%40googlegroups.com.

[Lukas Eder]

Do note that we're working on a few features that will greatly facilitate the row level security use case in the future, without requiring users to go "all in" on SQL transformation using VisitListener. One such improvement is the new Table.where() method, which later allows for creating views in jOOQ:  https://github.com/jOOQ/jOOQ/issues/8010. It will be possible, in a future version, to add these predicates dynamically to all tables using new SPI.

On Wed, Aug 7, 2019 at 8:34 PM P R <rathip...@gmail.com> wrote:

However, I'm curious, what's the motivation for not exposing the ability to get the query parts. After all jOOQ enables query building and hence one would expect to be able to get a handle to the constituent parts - similar to expecting a getXXX() method if one can invoke setXXX() on a class.

You probably mean the fact that we don't expose the data model right now, but hide it in the package private org.jooq.impl classes. So far, this design decision was a very good decision as it allowed us to much more easily maintain those internals. However, we do recognise that a number of users want access to the query object model, including new users of the jOOQ parser.

We're currently thinking about the re-design of that object model in a way that will allow us to expose it publicly, and maintain its backwards compatibility. This is a lot of work as it would (probably) require separating the model from the SQL generation, so no promises about that yet.