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

Leave a Comment / Java / By /

[ad_1]

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 remodel SQL, e.g. to implement row stage safety in jOOQ.

However often, even with bizarre 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 referred to 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.

Whenever you create a SUBSTRING() perform 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 perform arguments:

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

Some issues which may 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 execs and cons to each, although there’s a slight choice 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 ought to be clear for customers whether or not they’re establishing 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 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 likely be accessible 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 so forth. For this, we now have 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 information construction. However the Traverser operates on a tree, and thus has a couple of further options:

  • It may obtain occasions earlier than and after visiting a tree factor (and their subtrees!)
  • It may resolve for every tree factor, whether or not the traversal ought to recurse into the subtree. That is very helpful, for instance, if you happen to don’t care about traversing subqueries of any type.
  • It may resolve 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 believe that might be helpful, there, too. (It’s doable with a Spliterator, however that’s far more cumbersome)
  • It’s not parallel succesful. Parallelism is already an optionally available function with streams, however with timber, we haven’t discovered the good thing about supporting that but, maintaining traversals a lot easier.

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

In the event you needed to easily accumulate 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 might be rewritten like this:

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

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

Wish to accumulate 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.amassing(
            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 substitute one expression by one other? There are numerous use-cases, which we’ll finally help out of the field within the business jOOQ editions, however you can even roll your personal extensions utilizing this API.

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

// Accommodates redundant operators
Situation c = not(not(BOOK.ID.eq(1)));
System.out.println(c.$substitute(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 possibly can flip them on for various causes:

  • To usually optimise your SQL output
  • To detect issues in your queries, each applied through jOOQ API, or when intercepting them through the parser – the rule of thumb being that if this sample recognition function finds one thing to remodel, then your personal SQL question ought to 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 possibly can already at present 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 alternative API, it is going to be a lot easier each for customers, in addition to for us to help an out of the field row stage safety function. 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 ought to 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.$substitute(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 data
                return s.$the place(DSL.and(s.$the place(), 
                    ACCOUNT.ID.in(1, 2, 3)));
            }
        }
        return p;
    })
);

The results of the above will likely 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 function with out a 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 function set. These embrace:

  • Delicate 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 help the place we replace “audit” fields similar to 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 if you happen to’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 primarily based on a legacy ORM
  • As a diagnostics utility to run checks in your legacy ORM primarily based software

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

Limitations (as of jOOQ 3.16)

As talked about earlier than, to this point, that is an experimental function, not actually manufacturing prepared but. There are fairly a couple of recognized limitations of the present design and implementation. Please take into account this difficulty for open points:

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

An important limitations to date 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 an important ones. So, keep tuned for extra thrilling developments on this space coming quickly within the subsequent jOOQ releases.

[ad_2]

Leave a Comment

Your email address will not be published. Required fields are marked *