10 Issues You Didn’t Know About jOOQ – Java, SQL and jOOQ.

Naming public API! The unique jOOQ had strategies like these to assemble predicates:

However phrases equivalent to greaterOrEqual() and others are kinda “heavy” in the course of what is meant to be a really readable, SQL type DSL, so a brief model wanted to be added. However what to call the quick model? There are completely different opinions.

Since I really like XSLT virtually as a lot as I really like SQL, it was pure to decide on eq, ne, lt, le, gt, ge for these operators, identical to in XSLT. Effectively, XPath, truly, to be exact. These are additionally out there as HTML entities.

Enjoyable truth, beginning with jOOQ, we assist additionally parsing these abbreviations within the SQL parser to assist various Teradata syntax, see https://github.com/jOOQ/jOOQ/points/11844 (sure, the almighty Teradata!)

-- Legitimate in Teradata, I imply, why not?
SELECT *
FROM t
WHERE id EQ 1

Different comparable libraries use eq, ne, lt, loe, gt, goe. However I might by no means sleep at night time realizing the inconsistent size of names of those very related operators.

2. API naming regrets

Some names, I want I hadn’t chosen. The three most outstanding ones are:

• Area (it’s actually extra of a column or column expression. Area sounds so MS Excel-ish)
• Situation (The SQL customary calls it predicate)
• Report (the title is sound, but it surely now conflicts with java.lang.Report, which is a ache. If solely I had known as it Row)

Alas, there’s no manner these phrases are ever going to be modified with out breaking each jOOQ software on the market for no good motive. Such is lifetime of an API developer.

3. Overloads in jOOQ’s API are actually untagged union sorts

Oh, if solely Java had been extra like TypeScript. They’ve these lovely first-class untagged union sorts, which we poor Java of us solely know from exception catch blocks, the place they don’t exist as a first-class language characteristic, however simply as syntactic sugar:

sort F<T> = String | Title | Area<T> | Choose<? extends Record1<T>>

And that isn’t protecting all attainable permutations, by far. A practical choice was made that it’s not too seemingly for the primary argument to be a string bind variable. We rarely assist Title until the argument is actually a couple of column reference, not an arbitary column expression, and the scalar subquery case (Choose<? extends Record1<T>>) nicely, that’s simply comfort.

If customers nonetheless want the bind variable, they will wrap it with DSL.val("worth"). If Java did have untagged union sorts, nonetheless, the API would look extra like this:

static Area<String> substring(
    F<String> string,
    F<? extends Quantity> startingPosition,
    F<? extends Quantity> size = null
) { ... }

The place F is the above union sort. This could add lots of comfort to a DSL like jOOQ as a result of now, each permutation of argument sorts is supported. Alas, right here we’re, hand rolling the union sort emulation by means of closely overloaded strategies, hoping IDE auto-completion doesn’t fail or develop into too sluggish (howdy IntelliJ / Kotlin) 😅

4. Some SQL issues are extremely onerous to summary

Even in any case these 12 years, there are nonetheless onerous to unravel bugs when attempting to translate some SQL characteristic combos to all 30 presently supported RDBMS.

I imply, do this on the almighty PostgreSQL, which isn’t even impressed by my lame makes an attempt of making not-everyday-SQL:

with t (a) as (
  with u (b) as (
    values (1)
  )
  choose c
  from (
    with v (c) as (choose b from u)
    choose c from v
    union (
      choose c from v
      union all
      choose c from v
      order by c
    )
    order by c
  ) v
)
choose a from t

I imply, why not? However what about different dialects? Strive changing this to various dialects on https://www.jooq.org/translate/, in case you dare. Every a type of bullets (or a number of at a time), don’t work on some dialect. And never all of our translations work but, for thus many causes.

These aren’t an important bugs. They’re normally edge circumstances (e.g. the ORDER BY clauses are meaningless), however even then you definitely need as a lot SQL to work on your entire dialects as attainable, so we’re being stored busy, that’s for positive.

5. The pronunciation

Now it’s official (it all the time has been)

It’s pronounced dʒuːk (as in juke)

jOOQ is a recursive acronym that stands for jOOQ Object Oriented Querying. The “Object Oriented” stands for the API design, not the way you’re supposed to make use of it. You’re supposed to make use of it in a practical programming type, duh. The jOOQ expression tree is following a composite sample, if you’ll, and the SQL technology is carried out utilizing a customer sample type method, all the things is encapsulated, and so forth. Identical to you shouldn’t be tempted to say ess queue ell, you shouldn’t be tempted to say jay o o queue. It’s simply dʒuːk for sequel. Hah!

6. RDBMS Bugs

jOOQ has helped uncover a ton of RDBMS bugs possibly much more than the superior https://github.com/sqlancer/sqlancer. When jOOQ integrates with a brand new dialect (e.g. EXASOL, just lately), we uncover a ton of bugs. See an inventory right here: https://github.com/jOOQ/jOOQ/points/1985, or for the just lately supported Apache Ignite: https://github.com/jOOQ/jOOQ/points/10551.

That’s as a result of our integration assessments are huge and canopy all types of bizarre combos of syntax that hardly anybody ever worries about such because the earlier merchandise 4. I all the time doc every bug I discover, both on the RDBMS concern tracker, if it’s public, or on Stack Overflow.

So, in case you’re an RDBMS vendor and need us to check your SQL implementation, tell us! We’re for rent.

7. Mutability was a mistake

One of many greatest API design errors in jOOQ was mutability of the DSL, which might now hardly be eliminated. It’s even tougher to alter behaviour incompatibly than API. When API adjustments incompatibly, there are compilation errors. They’re a ache, however at the least there aren’t any surprises.

Altering behaviour is an enormous no-go for a library. Right here’s what I’m speaking about:

SelectWhereStep<?> s =
ctx.choose(T.A, T.B)
   .from(T);

// Dynamic SQL the way you should not do it:
if (one thing)
    s.the place(T.C.eq(1));

if (somethingElse)
    s.the place(T.D.eq(2));

End result<?> consequence = s.fetch();

Sure, the DSL API is mutable, which is why the above works. It shouldn’t work, and also you shouldn’t use this, however right here we’re. Our personal little solar.misc.Unsafe catastrophe. It’s too late. Everyone seems to be utilizing it already.

Situation c = noCondition();

// Has no impact
if (one thing)
    c.and(T.C.eq(1));

if (somethingElse)
    c.and(T.D.eq(2));

Situation c = noCondition();

if (one thing)
    c = c.and(T.C.eq(1));

if (somethingElse)
    c = c.and(T.D.eq(2));

So, there’s nonetheless mutation, however solely of your native variable, not any jOOQ objects. That’s how all the DSL must work. And even higher, you may use a practical programming type to implement dynamic SQL.

In any case, it is going to be very onerous to alter this behaviour with out breaking all the things, in very delicate methods, as a result of you’ll be able to’t simply detect mutable API utilization. In jOOQ 3.15, we’ve began annotating the DSL API with a @CheckReturnValue annotation, which is getting picked up by some instruments and IDEs, see e.g. https://youtrack.jetbrains.com/concern/IDEA-265263.

Fortunately, this annotation will even trigger a warning whenever you use the DSL API in a mutable style, since you’re purported to devour the return worth of that the place(T.C.eq(1)) name. Maybe there’s a method to change this, in any case, although likelihood is slim. In all probability not definitely worth the injury brought on.

Yeah, such is the destiny of each “light-weight library”, as soon as it reaches maturity. It’s virtually unimaginable to alter its basic flaws anymore

8. Supply and behavioural compatibility is essential in jOOQ

Behavioural incompatibilities

Behavioural incompatibilities are an absolute no-go in virtually all libraries / merchandise.

Supply incompatibilities

Supply incompatibilities are typically inevitable, at the least in main releases, if there’s a very compelling motive. Since we haven’t launched a serious launch in virtually a decade, we deal with our minor releases as main.

An instance of such an incompatibility in jOOQ was after we eliminated the comfort overloads that accepted Situation|Area<Boolean>|Boolean. The three issues are the identical in jOOQ:

• Situation is a SQL predicate (see merchandise 2)
• Area<Boolean> is a Situation wrapped as Area<Boolean>, which is kind of good in dialects with native assist for the BOOLEAN sort
• Boolean is only a boolean bind variable, wrapped in DSL.val(boolean)

// Flip off all the question or subquery, dynamically
boolean featureFlag = ...;
.the place(featureFlag)

.the place(USER.NAME.equals(userName))

Can you see the bug? It might not have occurred if they’d written USER.NAME.eq(userName), i.e. if they’d used the XSLT type abbreviations.

Sure, they wrote equals() (as in Object::equals), not equal() (as in Area::equal). Merely typo. Nonetheless compiled. Selected the mistaken overload. And appeared virtually right. These would have been right.

.the place(USER.NAME.equal(userName))
.the place(USER.NAME.eq(userName))

So, we deprecated, after which eliminated the overload, such that there’s now a compilation error when utilizing Object::equals. Appears an affordable case for breaking supply code, as a result of that API’s utilization was virtually completely unintended.

The best way to check these items?

With a purpose to be certain that we don’t break behavioural or supply compatibility, we’ve a ton (and I imply a ton) of assessments. Behavioural compatibility is checked by way of unit and integration assessments, ensuring complicated, and bizarre usages of jOOQ API proceed to supply the identical outcomes on all of our supported RDBMS.

Supply compatibility is checked by way of a ton of hand-written jOOQ API utilization, which can be a bit extra verbose than your common utilization. For instance, we don’t use var a lot in our personal assessments, although we closely suggest you use it in your shopper code!

var consequence = ctx.choose(T.A, multiset(..).as("fancy stuff")).fetch();

As a substitute, even in probably the most fancy statements utilizing the new MULTISET operator, which may be fairly heavy on the structural typing abuse of jOOQ, we all the time assign all the things to explicitly typed variables that will look as threatening as this:

End result<Record4<
    String,                   // FILM.TITLE
    End result<Record2<
        String,               // ACTOR.FIRST_NAME
        String                // ACTOR.LAST_NAME
    >>,                       // "actors"
    End result<Record1<String>>,  // CATEGORY.NAME
    End result<Record4<
        String,               // CUSTOMER.FIRST_NAME
        String,               // CUSTOMER.LAST_NAME
        End result<Record2<
            LocalDateTime,    // PAYMENT.PAYMENT_DATE
            BigDecimal        // PAYMENT.AMOUNT
        >>, 
        BigDecimal            // "whole"
    >>                        // "clients"
>> consequence = 
dsl.choose(...)

Even when that sort will not be truly consumed or utilized in any manner, e.g. when calling consequence.formatXML() on it. Extra assessments = higher. Any supply incompatibility would instantly trigger our assessments to cease compiling, so we may be assured to not run into any surprises.

There are all the time bizarre issues, nonetheless. It’s onerous to attain perfection. An instance is that this concern the place rawtype compatibility was ignored. I’m wondering if anybody truly makes positive their generics can be utilized safely and compatibly with uncooked sorts? Appears very onerous in jOOQ’s case…

9. Binary compatibility is nearly unimaginable to offer in jOOQ

However binary incompatibilities? In a DSL like jOOQ’s they’re most likely utterly unimaginable. An instance is after we launched assist for the Teradata QUALIFY clause in jOOQ 3.12. Right here’s a weblog put up explaining how our DSL works. Earlier than supporting QUALIFY, the jOOQ WINDOW clause, supported by way of the SelectWindowStep had a number of window() methodology overloads like this:

// "extends SelectOrderByStep", as a result of is window() strategies are
// clearly optionally available. It is a hardly used characteristic
interface SelectWindowStep<R extends Report> 
extends SelectOrderByStep<R> {
    SelectOrderByStep<R> window(WindowDefinition... w);
}

Instance utilization:

choose(T.A, rely().over(w))
.from(T)
.window(w)
.orderBy(T.A)
.fetch();

Now, the QUALIFY clause comes after WINDOW (Teradata doesn’t assist WINDOW, but when they did, they’d should prepend it to QUALIFY, as a result of WINDOW declares named window definitions, and QUALIFY consumes them, although you by no means know with SQL)

// "extends SelectQualifyStep" is a suitable change
interface SelectWindowStep<R extends Report> 
extends SelectQualifyStep<R> {

    // However these adjustments will not be
    SelectQualifyStep<R> window(WindowDefinition... w);
}

Whereas the JVM permits overloading by return sort (and that characteristic is used to implement generics and covariant overloads since Java 5), the Java language doesn’t enable this. There’s no manner a brand new model of this API can preserve the previous byte code, at the least not in Java. Kotlin helps such issues, i.e. the flexibility to emit (artificial?) strategies in byte code that can not be known as from supply code straight, purely for backwards compatibility causes.

So, in case you’re upgrading from jOOQ 3.11 to three.12, and also you had been utilizing the WINDOW clause, powerful luck. You need to recompile your shopper code, in any other case your pal NoSuchMethodError may have a phrase at runtime solely (as a result of at compile time, all the things nonetheless compiles).

I assume that binary compatibility will not be such an enormous drawback anymore typically. We run CI/CD jobs on a regular basis, and recompile all the things on a regular basis for varied causes. It’s unlikely that you simply preserve your personal inside libraries binary suitable for different groups to devour. However, it might be good if it weren’t an issue. Finally, pragmatism dictates that we will’t supply this service in jOOQ (such because the JDK does), and check out actually onerous to not break binary compatibility at the least in patch releases, however minor releases should not have any such assure.

10. The Emblem

The present emblem has been designed when jOOQ went industrial

Enjoyable truth, I designed each of those logos myself. My spouse by no means favored the previous one. It appeared like some bloody samurai to her with all bloody eyes 😅

The brand new one went by means of a number of iterations. All bizarre and vibrant. When lastly, a pal who’s working in design informed me a number of quite simple tips:

• Black and white may be utilized to all media (screens, print, and so forth.) very simply, in comparison with colored logos.
• It will probably even be inverted to white and black.
• Squares are simpler to handle in icons, headers, thumbnails, and so forth. than rectangles.

I took this a step additional and made all the emblem quadratic, together with the font, such that it doesn’t even require an SVG format to scale. It’s primarily a 20×20 bitmap. Can’t go extra low price range but efficient than this! 😁