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

Ranging from jOOQ 3.16, we’re investing so much into opening up our inside 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 remodel SQL, e.g. to implement row stage safety in jOOQ.

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

Please notice that as of jOOQ 3.16, all of this new API is experimental and thus topic to incompatible adjustments 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 sort known as org.jooq.impl.QOM incorporates all of this new public API, whereas the implementations are nonetheless the identical previous ones, within the org.jooq.impl bundle, however with package-private visibility.

Whenever you create a SUBSTRING() operate name expression, you’re getting a Subject<String> expression that implements QOM.Substring. On that sort, you possibly can name numerous accessor strategies all the time beginning with a "$" signal to entry the operate arguments:

// Create an expression utilizing the DSL API:
Subject<String> subject = substring(BOOK.TITLE, 2, 4);
// Entry the expression's internals utilizing the mannequin API
if (subject 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 sort Substring, or the DSL sort Subject. There are professionals and cons to each, although there’s a slight desire for maintaining the QOM sort 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 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 technique that produces a brand new QOM sort containing the mutated worth. All the QOM sort 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));

All the above API, the accessors, and the mutators will probably be out there to all jOOQ editions, together with the jOOQ Open Supply Version.

Expression tree traversal

The actual enjoyable begins once you wish to traverse the expression tree, e.g. to search for the presence of objects, to gather objects, and many others. For this, we’ve 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 components into some information construction. However the Traverser operates on a tree, and thus has just a few extra options:

• It may well obtain occasions earlier than and after visiting a tree factor (and their subtrees!)
• It may well determine for every tree factor, 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 form.
• It may well 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, although I feel that will be helpful, there, too. (It’s doable with a Spliterator, however that’s rather more cumbersome)
• It’s not parallel succesful. Parallelism is already an optionally available function with streams, however with timber, we haven’t discovered the advantage of supporting that but, maintaining 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 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 information 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

For those who needed to easily gather 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>(),
            (record, p) -> {
                record.add(p);
                return record;
            }
        )
);

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 will 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 gather 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
• Gather 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 finally assist out of the field within the industrial jOOQ editions, however you may as well 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 embody:

Optimisations and replacements of frequent patterns

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

Ranging from jOOQ 3.17, we’ll supply loads of these transformations out of the field. You’ll be able to 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 function finds one thing to remodel, then your personal SQL question must be improved. A linter, so to talk.

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

Row stage safety or shared schema multi tenancy

You’ll be able to already at present implement client-side row stage 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 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 function. In brief, think about that each time you question a restricted desk, reminiscent of 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 could be to write down:

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

            // Verify 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've not already added our IN record
                !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 include any such predicate. Clearly, that is removed from full. It doesn’t deal with outer joins accurately (the place the predicate may need to enter the ON clause), and different caveats. Keep tuned for extra, on this space!

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

Extra use-cases

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

• Mushy deletion, remodeling 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 reminiscent of CREATED_AT, CREATED_BY, MODIFIED_AT, MODIFIED_BY every 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 when you’re utilizing jOOQ:

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

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, to this point, that is an experimental function, not really manufacturing prepared but. There are fairly just a few recognized limitations of the present design and implementation. Please think about this challenge for open points:

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

Crucial limitations to date embody:

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