Traversing jOOQ Expression Timber with the brand new Traverser API – Java, SQL and jOOQ.

Ranging from jOOQ 3.16, we’re investing loads into opening up our inner question object mannequin (QOM) as a public API. That is primarily helpful for individuals who use jOOQ’s parser and want to entry the parsed expression tree, or to rework SQL, e.g. to implement row degree safety in jOOQ.

However sometimes, even with abnormal jOOQ dynamic SQL utilization, it may be helpful to entry the expression tree.

Please observe that as of jOOQ 3.16, all of this new API is experimental and thus topic to incompatible modifications sooner or later. Use it at your individual danger.

The Question Object Mannequin (QOM) API

The primary enchancment is to supply an API to the question object mannequin itself. A brand new kind known as org.jooq.impl.QOM comprises all of this new public API, whereas the implementations are nonetheless the identical outdated ones, within the org.jooq.impl bundle, however with package-private visibility.

While you create a SUBSTRING() operate name expression, you’re getting a Subject<String> expression that implements QOM.Substring. On that kind, you’ll be able to name varied accessor strategies all the time beginning with a "$" signal to entry the operate arguments:

// Create an expression utilizing the DSL API:
Subject<String> discipline = substring(BOOK.TITLE, 2, 4);
// Entry the expression's internals utilizing the mannequin API
if (discipline instanceof QOM.Substring substring) {
    Subject<String> string = substring.$string();
    Subject<? extends Quantity> startingPosition = 
        substring.$startingPosition();
    Subject<? extends Quantity> size = substring.$size();
}

Some issues that could be topic to alter:

1. It’s not clear but if the DSL methodology substring() returns the QOM kind Substring, or the DSL kind Subject. There are professionals and cons to each, although there’s a slight desire for maintaining the QOM kind out of sight for DSL customers.

2. The "$" prefix is used to obviously distinguish between the DSL API (no prefix) and the QOM API ("$" prefix) as the kind hierarchy is now shared between the 2 APIs, and it must be clear for customers whether or not they’re setting up jOOQ objects for utilization within the DSL, or whether or not they’re manipulating objects of the expression tree.

For every accessor, there’s additionally a “mutator”, a technique that produces a brand new QOM kind containing the mutated worth. The entire QOM kind is immutable, so the unique Substring occasion isn’t affected by a modification like this:

Substring substring1 = (Substring) substring(BOOK.TITLE, 2, 4);
Substring substring2 = substring1
    .$startingPosition(val(3))
    .$size(val(5));

assertEquals(substring2, substring(BOOK.TITLE, 3, 5));

The entire above API, the accessors, and the mutators can be accessible to all jOOQ editions, together with the jOOQ Open Supply Version.

Expression tree traversal

The true enjoyable begins once you wish to traverse the expression tree, e.g. to search for the presence of objects, to gather objects, and so forth. For this, we’ve got launched the brand new Traverser API within the industrial jOOQ distributions.

A Traverser works fairly equally as a JDK Collector, which traverses a Stream and collects parts into some information construction. However the Traverser operates on a tree, and thus has a couple of further options:

• It could possibly obtain occasions earlier than and after visiting a tree ingredient (and their subtrees!)
• It could possibly determine for every tree ingredient, whether or not the traversal ought to recurse into the subtree. That is very helpful, for instance, in the event you don’t care about traversing subqueries of any type.
• It could possibly determine whether or not to abort traversal early, e.g. when the primary object has been discovered. I’m not conscious of JDK Collector providing such brief circuiting strategies, though I feel that will be helpful, there, too. (It’s doable with a Spliterator, however that’s way more cumbersome)
• It’s not parallel succesful. Parallelism is already an non-obligatory characteristic with streams, however with bushes, we haven’t discovered the advantage of supporting that but, maintaining traversals a lot less complicated.

A easy traversal instance can be to rely all of the QueryPart objects in an expression, like this:

// Accommodates 7 question components
System.out.println(
    T_BOOK.ID.eq(1).or(T_BOOK.ID.eq(2))
        .$traverse(() -> 0, (c, p) -> c + 1)
);

The easy comfort methodology gives an auxiliary information construction (right here an int), and a operate that accumulates each question half into that information construction. The result’s the info construction (the int) itself.

Why does it rely 7? As a result of it traverses the next tree:

1: T_BOOK.ID.eq(1).or(T_BOOK.ID.eq(2))
2: T_BOOK.ID.eq(1)
3: T_BOOK.ID
4: 1
5: T_BOOK.ID.eq(2)
6: T_BOOK.ID
7: 2

Or visually:

OR
├── EQ
│   ├── T_BOOK.ID
│   └── 1
└── EQ
    ├── T_BOOK.ID
    └── 2

Should you needed to easily acquire every particular person QueryPart, simply do it like this:

System.out.println(
    T_BOOK.ID.eq(1).or(T_BOOK.ID.eq(2))
        .$traverse(
            () -> new ArrayList<QueryPart>(),
            (checklist, p) -> {
                checklist.add(p);
                return checklist;
            }
        )
);

The output of that is (not native formatting):

[
  ("PUBLIC"."T_BOOK"."ID" = 1 or "PUBLIC"."T_BOOK"."ID" = 2),
  "PUBLIC"."T_BOOK"."ID" = 1,
  "PUBLIC"."T_BOOK"."ID",
  1,
  "PUBLIC"."T_BOOK"."ID" = 2,
  "PUBLIC"."T_BOOK"."ID",
  2
]

This instance exhibits that the tree is traversed in a depth-first method.

However you don’t have to write down such easy Traversers your self. Any JDK Collector can be utilized as a Traverser, so the above two examples may be rewritten like this:

System.out.println(
    T_BOOK.ID.eq(1).or(T_BOOK.ID.eq(2))
        .$traverse(Traversers.accumulating(Collectors.counting()))
);

System.out.println(
    T_BOOK.ID.eq(1).or(T_BOOK.ID.eq(2))
        .$traverse(Traversers.accumulating(Collectors.toList()))
);

Need to acquire all of the concerned tables of a question? No downside!

System.out.println(
    T_BOOK.ID.eq(1).and(T_AUTHOR.ID.eq(3))
        .$traverse(Traversers.accumulating(
            Collectors.mapping(
                p -> p instanceof TableField<?, ?> tf 
                    ? tf.getTable() 
                    : p,
                Collectors.filtering(
                    p -> p instanceof Desk,
                    Collectors.toSet()
                )
            )
        ))
);

This may be learn as:

• Map all TableField references to their Desk containers
• Filter out all Desk references
• Acquire them to a definite Set of tables.

Producing:

["PUBLIC"."T_BOOK", "PUBLIC"."T_AUTHOR"]

Expression tree transformations

What if you wish to exchange one expression by one other? There are numerous use-cases, which we’ll ultimately assist out of the field within the industrial jOOQ editions, however you can too roll your individual extensions utilizing this API.

A quite simple instance of such a change would take away redundant boolean negation:

// Accommodates redundant operators
Situation c = not(not(BOOK.ID.eq(1)));
System.out.println(c.$exchange(q ->
    q instanceof Not n1 && n1.$arg1() instanceof Not n2
        ? n2.$arg1()
        : q
));

Regardless of having explicitly written not(not(x)), the output is simply x, or particularly:

"BOOK"."ID" = 1

Precise instance use-cases for such transformations embrace:

Optimisations and replacements of widespread patterns

There are a couple of causes to normalise and enhance widespread patterns of SQL strings:

Ranging from jOOQ 3.17, we’ll supply plenty of these transformations out of the field. You’ll be able to flip them on for various causes:

• To typically optimise your SQL output
• To detect issues in your queries, each applied by way of jOOQ API, or when intercepting them by way of the parser – the rule of thumb being that if this sample recognition characteristic finds one thing to rework, then your individual SQL question must be improved. A linter, so to talk.

Out of the field characteristic ranging from jOOQ 3.17: https://github.com/jOOQ/jOOQ/points/7284

Row degree safety or shared schema multi tenancy

You’ll be able to already as we speak implement client-side row degree safety utilizing jOOQ’s VisitListener SPI, a predecessor to those SQL transformation options which might be based mostly on the brand new question object mannequin. However with the brand new alternative API, it will likely be a lot less complicated each for customers, in addition to for us to assist an out of the field row degree safety characteristic. In brief, think about that each time you question a restricted desk, similar to ACCOUNT:

What you need is to make sure customers can solely entry their very own accounts, i.e. this must be patched into the question, transparently for the developer:

SELECT * FROM account WHERE account_id IN (:userAccountList)

A easy algorithm can be to write down:

QueryPart q = choose(ACCOUNT.ID).from(ACCOUNT);
System.out.println(
    q.$exchange(p -> {
        if (p instanceof Choose<?> s) {

            // Examine if the question comprises the related desk(s) in
            // the FROM clause
            if (s.$from().$traverse(Traversers.containing(ACCOUNT)) && (

                // Within the absence of a WHERE clause
                s.$the place() == null ||

                // Or, if we have not already added our IN checklist
                !s.$the place().$traverse(Traversers.containing(
                    x -> x instanceof InList<?> i 
                        && ACCOUNT.ID.equals(i.$arg1())
                ))
            )) {

                // Append a predicate to the question
                // Think about this studying some context data
                return s.$the place(DSL.and(s.$the place(), 
                    ACCOUNT.ID.in(1, 2, 3)));
            }
        }
        return p;
    })
);

The results of the above can be:

choose "PUBLIC"."ACCOUNT"."ID"
from "PUBLIC"."ACCOUNT"
the place "PUBLIC"."ACCOUNT"."ID" in (
  1, 2, 3
)

Discover how the enter SQL question doesn’t comprise any such predicate. Clearly, that is removed from full. It doesn’t deal with outer joins accurately (the place the predicate might need to enter the ON clause), and different caveats. Keep tuned for extra, on this space!

Out of the field characteristic and not using a launch goal but:
https://github.com/jOOQ/jOOQ/points/2682

Extra use-cases

There are various extra use-cases, which we’re planning on supporting out of the field, based mostly on the above characteristic set. These embrace:

• Gentle deletion, reworking DELETE statements into “equal” UPDATE .. SET deleted = true statements, in addition to SELECT statements into “equal” SELECT .. WHERE NOT deleted, see https://github.com/jOOQ/jOOQ/points/2683
• Audit column assist the place we replace “audit” fields similar to CREATED_AT, CREATED_BY, MODIFIED_AT, MODIFIED_BY each time they’re touched by any DML question, see https://github.com/jOOQ/jOOQ/points/1592

Use-case agnosticity

Do not forget that like most different jOOQ options, this one, too, is totally use-case agnostic. It doesn’t matter in the event you’re utilizing jOOQ:

• As an inner DSL to create dynamic (or “static”) SQL queries
• As a parser to translate between SQL dialects
• As a parser to complement your software that’s based mostly on a legacy ORM
• As a diagnostics utility to run checks in your legacy ORM based mostly software

Regardless of the use-case, you need to use this API to analyse and rework your SQL queries.

Limitations (as of jOOQ 3.16)

As talked about earlier than, up to now, that is an experimental characteristic, not really manufacturing prepared but. There are fairly a couple of identified limitations of the present design and implementation. Please take into account this situation for open points:

https://github.com/jOOQ/jOOQ/points/12772

Crucial limitations to this point embrace:

• Help just for SELECT, no different statements
• Traversal doesn’t but go into JOIN bushes or UNION / INTERSECT / EXCEPT subqueries

There are extra limitations, however these ones are an important ones. So, keep tuned for extra thrilling developments on this space coming quickly within the subsequent jOOQ releases.