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

Considered one of jOOQ 3.17‘s coolest new options are shopper aspect computed columns. jOOQ 3.16 already added assist for server aspect computed columns, which a lot of you respect for numerous 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 several column in a view. There are two varieties of computed columns:

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

Some SQL dialects use these actual phrases to differentiate between the 2 options. Some dialects assist each of them, some solely assist certainly one of them.

Some typical use-cases for server aspect computed columns embody:

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 characteristic. Sadly, as all the time:

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

Let’s take a look at the third bullet. What if we wished to “compute” a column through the use of joins or correlated subqueries? We can not, 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 tackle,
  full_address TEXT GENERATED ALWAYS AS ((
    SELECT a.tackle 
    FROM tackle AS a
    WHERE a.address_id = buyer.address_id
  )) VIRTUAL
);

Why?

• It doesn’t assist VIRTUAL, solely STORED
• Even when it did assist VIRTUAL, it at present throws SQL Error [0A000]: ERROR: can not 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’ll be able to simply create a view like this:

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

And now, you will have the specified behaviour. This strategy has its personal caveats, together with:

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

Enter jOOQ’s shopper aspect computed columns

That is why jOOQ 3.17 now gives this excellent characteristic on the shopper aspect. Each variations are supported:

• VIRTUAL computed columns are columns which are changed by their respective expression when the column seems in any non-write place, e.g. SELECT, WHERE, but additionally RETURNING
• STORED computed columns are columns which are 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 may 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>
                        <sort>textual content</sort>
                    </column>
                    <column>
                        <tables>buyer|employees</tables>
                        <title>full_name</title>
                        <sort>textual content</sort>
                    </column>
                </columns>
            </syntheticObjects>

            <!-- Now inform the code generator the way 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(
                        tackle().ADDRESS_, 
                        DSL.inline(", "), 
                        tackle().POSTAL_CODE, 
                        DSL.inline(", "), 
                        tackle().metropolis().CITY_, 
                        DSL.inline(", "), 
                        tackle().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’ll be able to register a single jOOQ Discipline 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 can have additionally written a correlated subquery, which is one technique to implement these implicit joins. It will have simply been a bit extra laborious.

You’ll be able to question these columns like some other in jOOQ:

Consequence<Record2<String, String>> consequence =
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.tackle || ', ' || 
  alias_114975355.postal_code || ', ' || 
  alias_57882359.metropolis || ', ' || 
  alias_1060004.nation
    as full_address
from (
  buyer
    be part of (
      tackle 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 consequence 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|
|...             |...                                             |
+----------------+------------------------------------------------+

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

Consequence<Record1<String>> consequence =
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 advanced instance

SQL JOINs may be fairly boring and repetitive at instances. Think about 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,
  fee 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 software that has transactions with quantities and a foreign money related to the quantity. Think about the CONVERSION desk may have bitemporal versioning to verify we are able to calculate the proper conversion fee at any given date, if wanted.

Now, any time we wish to sum up transactions, we’ve got to transform the quantity to some consumer foreign money, no matter the transaction foreign money. Isn’t that boring? We shouldn’t should repeat this logic all over.

You’ll be able to in fact write views like this:

CREATE VIEW v_transaction AS
SELECT
  id, quantity, foreign money,
  quantity * (
    SELECT c.fee
    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 wanted a unique foreign money, we are able to both create completely different views, create saved (desk valued, even?) capabilities, 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>
                        <sort>NUMERIC</sort>
                    </column>
                    <column>
                        <tables>TRANSACTION</tables>
                        <title>AMOUNT_USER_CURRENCY</title>
                        <sort>NUMERIC</sort>
                    </column>
                </columns>
            </syntheticObjects>

            <!-- And outline the computations -->
            <forcedTypes>
                <forcedType>
                    <generator>ctx -> AMOUNT.instances(DSL.subject(
   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.subject(
    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, nonetheless 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 anyplace.

Whenever 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 duplicate, use configuration().derive()
ctx.configuration().knowledge("USER_CURRENCY", "CHF");

And instantly, we get a unique consequence:

+----+------+--------+----------+--------------------+---------+-------------------+
|  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 software the place you:

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

A real jOOQ and SQL energy consumer dream.

Extra prospects

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

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

Caveats

Not like server aspect digital computed columns, you can’t put an index on these, as a result of the server doesn’t know something concerning the column, or the expression. Meaning the characteristic is especially helpful for projections and aggregations / computations, much less for queries. For instance, perhaps don’t run filters on such columns.

Saved shopper aspect computed columns

A future weblog put up will discuss concerning the STORED model of shopper aspect computed columns, which additionally contains the brand new audit column characteristic. 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 utterly completely different.

Extra details about this: