Create Dynamic Views with jOOQ 3.17’s new Digital Consumer Facet Computed Columns – Java, SQL and jOOQ.

Certainly one of jOOQ 3.17‘s coolest new options are consumer facet computed columns. jOOQ 3.16 already added help for server facet computed columns, which a lot of you respect for varied causes.

What’s a computed column?

A computed column is a column that’s derived (“computed”) from an expression. It can’t be written to. It really works like all column in a view. There are two forms of computed columns:

• VIRTUAL computed columns, that are computed “on learn”
• STORED computed columns, that are computed “on write”

Some SQL dialects use these precise phrases to tell apart between the 2 options. Some dialects help each of them, some solely help one among them.

Some typical use-cases for server facet computed columns embrace:

CREATE TABLE buyer (
  id BIGINT NOT NULL PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  full_name TEXT GENERATED ALWAYS AS 
    (first_name || ' ' || last_name) STORED
);

Now, attempt to insert some knowledge into this desk:

INSERT INTO buyer (id, first_name, last_name)
VALUES (1, 'John', 'Doe')
RETURNING *;

And you’ll get:

|id |first_name|last_name|full_name|
|---|----------|---------|---------|
|1  |John      |Doe      |John Doe |

What are some limitations?

That’s an exquisite function. Sadly, as at all times:

• Not all dialects help it
• Not all dialects help each VIRTUAL and/or STORED (each approaches have their advantages)
• The function itself is sort of restricted in SQL

Let’s take a look at the third bullet. What if we wished to “compute” a column by utilizing joins or correlated subqueries? We can’t, in SQL. E.g. PostgreSQL rejects this:

CREATE TABLE buyer (
  id BIGINT NOT NULL PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  address_id BIGINT REFERENCES handle,
  full_address TEXT GENERATED ALWAYS AS ((
    SELECT a.handle 
    FROM handle AS a
    WHERE a.address_id = buyer.address_id
  )) VIRTUAL
);

Why?

• It doesn’t help VIRTUAL, solely STORED
• Even when it did help VIRTUAL, it at the moment throws SQL Error [0A000]: ERROR: can’t use subquery in column era expression

There isn’t actually any good motive that I can see for this limitation. In spite of everything, you may simply create a view like this:

CREATE VIEW v_customer AS
SELECT 
  id, first_name, last_name, address_id,
  (
    SELECT a.handle 
    FROM handle AS a
    WHERE a.address_id = buyer.address_id
  ) AS full_address
FROM buyer

And now, you’ve gotten the specified behaviour. This strategy has its personal caveats, together with:

• The view shouldn’t be the desk. Each dialect has limitations with respect to updatable views, i.e. it may be troublesome to write down to this view.
• Views are saved objects, and as such must be versioned and put in. This isn’t an enormous downside per se, however there are people who attempt to keep away from this, due to… effectively, the additional effort of doing database change administration appropriately?
• You at all times should determine whether or not to question the view or the desk.

Enter jOOQ’s consumer facet computed columns

This is the reason jOOQ 3.17 now provides this excellent function on the consumer facet. Each variations are supported:

• VIRTUAL computed columns are columns which can be changed by their respective expression when the column seems in any non-write place, e.g. SELECT, WHERE, but in addition RETURNING
• STORED computed columns are columns which can be computed when written to, e.g. in INSERT, UPDATE, MERGE

Let’s first take a look at VIRTUAL computed columns. The above two use-cases might be configured as follows within the code generator, assuming a Maven config.

<configuration>
    <generator>
        <database>

            <!-- Inform the code generator so as to add artificial columns, i.e.
                 columns that the server doesn't know something about -->
            <syntheticObjects>
                <columns>
                    <column>
                        <tables>buyer|employees|retailer</tables>
                        <title>full_address</title>
                        <kind>textual content</kind>
                    </column>
                    <column>
                        <tables>buyer|employees</tables>
                        <title>full_name</title>
                        <kind>textual content</kind>
                    </column>
                </columns>
            </syntheticObjects>

            <!-- Now inform the code generator methods to compute the values -->
            <forcedTypes>
                <forcedType>
                    <generator>ctx -> DSL.concat(
                        FIRST_NAME, DSL.inline(" "), LAST_NAME)
                    </generator>
                    <includeExpression>full_name</includeExpression>
                </forcedType>
                <forcedType>
                    <generator>ctx -> DSL.concat(
                        handle().ADDRESS_, 
                        DSL.inline(", "), 
                        handle().POSTAL_CODE, 
                        DSL.inline(", "), 
                        handle().metropolis().CITY_, 
                        DSL.inline(", "), 
                        handle().metropolis().nation().COUNTRY_
                    )</generator>
                    <includeExpression>full_address</includeExpression>
                </forcedType>
            </forcedTypes>
        </database>
    </generator>
</configuration>

The above instance makes use of two new code era options:

That’s it. With these two issues, you may register a single jOOQ Area expression that computes the worth of your required columns. Discover how the FULL_ADDRESS makes use of implicit joins to simplify entry to different tables. In fact, you could possibly have additionally written a correlated subquery, which is one method to implement these implicit joins. It could have simply been a bit extra laborious.

You may question these columns like another in jOOQ:

End result<Record2<String, String>> outcome =
ctx.choose(CUSTOMER.FULL_NAME, CUSTOMER.FULL_ADDRESS)
   .from(CUSTOMER)
   .fetch();

The generated question does all of the becoming a member of for you, transparently:

choose
  buyer.first_name || ' ' || buyer.last_name 
    as full_name,
  alias_114975355.handle || ', ' || 
  alias_114975355.postal_code || ', ' || 
  alias_57882359.metropolis || ', ' || 
  alias_1060004.nation
    as full_address
from (
  buyer
    be part of (
      handle as alias_114975355
        be part of (
          metropolis as alias_57882359
            be part of nation as alias_1060004
              on alias_57882359.country_id = alias_1060004.country_id
        )
          on alias_114975355.city_id = alias_57882359.city_id
    )
      on buyer.address_id = alias_114975355.address_id
  )

The outcome being:

+----------------+------------------------------------------------+
|full_name       |full_address                                    |
+----------------+------------------------------------------------+
|ANDREA HENDERSON|320 Baiyin Parkway, 37307, Mahajanga, Madagascar|
|CLARA SHAW      |1027 Songkhla Manor, 30861, Molodetno, Belarus  |
|SHANE MILLARD   |184 Mandaluyong Avenue, 94239, La Paz, Mexico   |
|DANNY ISOM      |734 Bchar Place, 30586, Okara, Pakistan         |
|VALERIE BLACK   |782 Mosul Avenue, 25545, Brockton, United States|
|...             |...                                             |
+----------------+------------------------------------------------+

Be aware that as you’d count on, when you omit one among these columns, the related elements of the question aren’t generated, together with implicit joins. So, for instance, when you question this:

End result<Record1<String>> outcome =
ctx.choose(CUSTOMER.FULL_NAME)
   .from(CUSTOMER)
   .fetch();

The generated SQL is far less complicated:

choose buyer.first_name || ' ' || buyer.last_name as full_name
from buyer

A extra complicated instance

SQL JOINs might be fairly boring and repetitive at instances. Take into account this schema:

CREATE TABLE foreign money (
  code CHAR(3) NOT NULL,
  
  PRIMARY KEY (code)
);

CREATE TABLE conversion (
  from_currency CHAR(3) NOT NULL,
  to_currency CHAR(3) NOT NULL,
  charge NUMERIC(18, 2) NOT NULL,
  
  PRIMARY KEY (from_currency, to_currency),
  FOREIGN KEY (from_currency) REFERENCES foreign money,
  FOREIGN KEY (to_currency) REFERENCES foreign money
);

CREATE TABLE transaction (
  id BIGINT NOT NULL,
  quantity NUMERIC(18, 2) NOT NULL,
  foreign money CHAR(3) NOT NULL,
  
  PRIMARY KEY (id),
  FOREIGN KEY (foreign money) REFERENCES foreign money
);

A typical (however simplified) finance utility that has transactions with quantities and a foreign money related to the quantity. Think about the CONVERSION desk might have bitemporal versioning to verify we are able to calculate the proper conversion charge at any given date, if wanted.

Now, any time we need to sum up transactions, we’ve to transform the quantity to some consumer foreign money, regardless of the transaction foreign money. Isn’t that boring? We shouldn’t should repeat this logic in every single place.

You may in fact write views like this:

CREATE VIEW v_transaction AS
SELECT
  id, quantity, foreign money,
  quantity * (
    SELECT c.charge
    FROM conversion AS c
    WHERE c.from_currency = t.foreign money
    AND c.to_currency = 'USD'
  ) AS amount_usd
FROM transaction AS t

That will assist summing up all transactions in USD. If we would have liked a unique foreign money, we are able to both create totally different views, create saved (desk valued, even?) features, or generate the SQL with jOOQ.

However why not simply retailer the computation with the jOOQ generated desk instantly?

<configuration>
    <generator>
        <database>

            <!-- Once more, add the artificial columns -->
            <syntheticObjects>
                <columns>
                    <column>
                        <tables>TRANSACTION</tables>
                        <title>AMOUNT_USD</title>
                        <kind>NUMERIC</kind>
                    </column>
                    <column>
                        <tables>TRANSACTION</tables>
                        <title>AMOUNT_USER_CURRENCY</title>
                        <kind>NUMERIC</kind>
                    </column>
                </columns>
            </syntheticObjects>

            <!-- And outline the computations -->
            <forcedTypes>
                <forcedType>
                    <generator>ctx -> AMOUNT.instances(DSL.area(
   DSL.choose(Conversion.CONVERSION.RATE)
      .from(Conversion.CONVERSION)
      .the place(Conversion.CONVERSION.FROM_CURRENCY.eq(CURRENCY))
      .and(Conversion.CONVERSION.TO_CURRENCY.eq(
           DSL.inline("USD")))))
                    </generator>
                    <includeExpression>
                        TRANSACTION.AMOUNT_USD
                    </includeExpression>
                </forcedType>
                <forcedType>
                    <generator>ctx -> AMOUNT.instances(DSL.area(
    DSL.choose(Conversion.CONVERSION.RATE)
       .from(Conversion.CONVERSION)
       .the place(Conversion.CONVERSION.FROM_CURRENCY.eq(CURRENCY))
       .and(Conversion.CONVERSION.TO_CURRENCY.eq(
           (String) ctx.configuration().knowledge("USER_CURRENCY")))))
                    </generator>
                    <includeExpression>
                        TRANSACTION.AMOUNT_USER_CURRENCY
                    </includeExpression>
                </forcedType>
            </forcedTypes>
        </database>
    </generator>
</configuration>

It does what you’d count on it does. Connect a correlated subquery to the 2 columns. The attention-grabbing bit, nevertheless is the AMOUNT_USER_CURRENCY column. It accesses ctx.configuration().knowledge("USER_CURRENCY"). That’s simply arbitrary user-defined knowledge, which you’ll be able to cross round jOOQ and entry from wherever.

If you run this question with out setting that "USER_CURRENCY" info like this:

ctx.choose(
        TRANSACTION.ID,
        TRANSACTION.AMOUNT,
        TRANSACTION.CURRENCY,
        TRANSACTION.AMOUNT_USD,
        TRANSACTION.AMOUNT_USER_CURRENCY,
        sum(TRANSACTION.AMOUNT_USD).over().as("total_usd"),
        sum(TRANSACTION.AMOUNT_USER_CURRENCY).over()
            .as("total_user_currency"))
   .from(TRANSACTION)
   .orderBy(TRANSACTION.ID))
   .fetch()

The generated SQL is that this:

choose
  TRANSACTION.ID,
  TRANSACTION.AMOUNT,
  TRANSACTION.CURRENCY,
  (TRANSACTION.AMOUNT * (
    choose CONVERSION.RATE
    from CONVERSION
    the place (
      CONVERSION.FROM_CURRENCY = TRANSACTION.CURRENCY
      and CONVERSION.TO_CURRENCY = 'USD'
    )
  )) AMOUNT_USD,
  (TRANSACTION.AMOUNT * (
    choose CONVERSION.RATE
    from CONVERSION
    the place (
      CONVERSION.FROM_CURRENCY = TRANSACTION.CURRENCY
      and CONVERSION.TO_CURRENCY = null
    )
  )) AMOUNT_USER_CURRENCY,
  sum((TRANSACTION.AMOUNT * (
    choose CONVERSION.RATE
    from CONVERSION
    the place (
      CONVERSION.FROM_CURRENCY = TRANSACTION.CURRENCY
      and CONVERSION.TO_CURRENCY = 'USD'
    )
  ))) over () total_usd,
  sum((TRANSACTION.AMOUNT * (
    choose CONVERSION.RATE
    from CONVERSION
    the place (
      CONVERSION.FROM_CURRENCY = TRANSACTION.CURRENCY
      and CONVERSION.TO_CURRENCY = null
    )
  ))) over () total_user_currency
from TRANSACTION
order by TRANSACTION.ID

The consumer foreign money is undefined (i.e. NULL), so we don’t get something for it:

+----+------+--------+----------+--------------------+---------+-------------------+
|  ID|AMOUNT|CURRENCY|AMOUNT_USD|AMOUNT_USER_CURRENCY|total_usd|total_user_currency|
+----+------+--------+----------+--------------------+---------+-------------------+
|   1| 12.25|EUR     |   12.7400|              {null}| 150.0978|             {null}|
|   2| 15.37|USD     |   15.3700|              {null}| 150.0978|             {null}|
|   3| 99.99|GBP     |  121.9878|              {null}| 150.0978|             {null}|
+----+------+--------+----------+--------------------+---------+-------------------+

Now, let’s run the question once more after setting the configuration().knowledge() worth like this:

// This mutates the configuration.
// To make a replica, use configuration().derive()
ctx.configuration().knowledge("USER_CURRENCY", "CHF");

And immediately, we get a unique outcome:

+----+------+--------+----------+--------------------+---------+-------------------+
|  ID|AMOUNT|CURRENCY|AMOUNT_USD|AMOUNT_USER_CURRENCY|total_usd|total_user_currency|
+----+------+--------+----------+--------------------+---------+-------------------+
|   1| 12.25|EUR     |   12.7400|             12.2500| 150.0978|           142.9936|
|   2| 15.37|USD     |   15.3700|             14.7552| 150.0978|           142.9936|
|   3| 99.99|GBP     |  121.9878|            115.9884| 150.0978|           142.9936|
+----+------+--------+----------+--------------------+---------+-------------------+

That is extraordinarily highly effective! Think about an utility the place you:

• Get a connection from the connection pool
• Initialise just a few context variables, such because the consumer, and their settings
• Routinely have arbitrary “views” (i.e. jOOQ expressions, each from dynamic SQL or from this function) replace their contents

A real jOOQ and SQL energy consumer dream.

Extra potentialities

The above examples had been simply exhibiting easy instances of scalar subqueries. However nothing retains you from utilizing:

In spite of everything, a consumer facet computed column is only a “variable” referencing an expression that’s expanded whenever you run the question utilizing that column.

Caveats

Not like server facet digital computed columns, you can not put an index on these, as a result of the server doesn’t know something in regards to the column, or the expression. Which means the function is principally helpful for projections and aggregations / computations, much less for queries. For instance, perhaps don’t run filters on such columns.

Saved consumer facet computed columns

A future weblog put up will discuss in regards to the STORED model of consumer facet computed columns, which additionally contains the brand new audit column function. As a teaser, simply implement a Generator just like the above on a non-synthetic column (i.e. an precise column out of your schema), and voilà, the behaviour is now fully totally different.

Extra details about this: