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

Ranging from jOOQ 3.16, we’re investing rather a lot 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 stage safety in jOOQ.

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

Please be aware 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 personal threat.

The Question Object Mannequin (QOM) API

The primary enchancment is to offer an API to the question object mannequin itself. A brand new kind known as org.jooq.impl.QOM incorporates 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.

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

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

Some issues which may be topic to vary:

1. It’s not clear but if the DSL methodology substring() returns the QOM kind Substring, or the DSL kind Discipline. There are professionals and cons to each, although there’s a slight choice for preserving 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 sort hierarchy is now shared between the 2 APIs, and it ought to be clear for customers whether or not they’re developing 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 way 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 will probably 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 on. For this, we have now launched the brand new Traverser API within the business jOOQ distributions.

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

• It will probably obtain occasions earlier than and after visiting a tree aspect (and their subtrees!)
• It will probably determine for every tree aspect, whether or not the traversal ought to recurse into the subtree. That is very helpful, for instance, when you don’t care about traversing subqueries of any variety.
• It will probably 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 quick circuiting strategies, regardless that I believe that might be helpful, there, too. (It’s attainable with a Spliterator, however that’s far more cumbersome)
• It’s not parallel succesful. Parallelism is already an elective characteristic with streams, however with timber, we haven’t discovered the good thing about supporting that but, preserving traversals a lot easier.

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

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

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

Why does it depend 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

In case 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 reveals 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 drawback!

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 business jOOQ editions, however you too can roll your personal extensions utilizing this API.

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

// Comprises 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 frequent patterns

There are a number of causes to normalise and enhance frequent patterns of SQL strings:

Ranging from jOOQ 3.17, we’ll provide a number of these transformations out of the field. You possibly can flip them on for various causes:

• To usually 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 personal SQL question ought to 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 stage safety or shared schema multi tenancy

You possibly can already as we speak implement client-side row stage safety utilizing jOOQ’s VisitListener SPI, a predecessor to those SQL transformation options which are primarily based on the brand new question object mannequin. However with the brand new substitute API, will probably be a lot easier each for customers, in addition to for us to assist an out of the field row stage safety characteristic. In brief, think about that each time you question a restricted desk, comparable to ACCOUNT:

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

SELECT * FROM account WHERE account_id IN (:userAccountList)

A easy algorithm could 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 incorporates 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 information
                return s.$the place(DSL.and(s.$the place(), 
                    ACCOUNT.ID.in(1, 2, 3)));
            }
        }
        return p;
    })
);

The results of the above will probably 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 with no launch goal but:
https://github.com/jOOQ/jOOQ/points/2682

Extra use-cases

There are lots of extra use-cases, which we’re planning on supporting out of the field, primarily based 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 comparable to CREATED_AT, CREATED_BY, MODIFIED_AT, MODIFIED_BY at any time when 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 when 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 counterpoint your utility that’s primarily based on a legacy ORM
• As a diagnostics utility to run checks in your legacy ORM primarily based utility

No matter the use-case, you should use this API to analyse and remodel your SQL queries.

Limitations (as of jOOQ 3.16)

As talked about earlier than, up to now, that is an experimental characteristic, not actually manufacturing prepared but. There are fairly a number of identified limitations of the present design and implementation. Please contemplate this problem for open points:

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

Crucial limitations thus far embrace:

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

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