Create Dynamic Views with jOOQ 3.17’s new Virtual Client Side Computed Columns

One of jOOQ 3.17‘s coolest new features are client side computed columns. jOOQ 3.16 already added support for server side computed columns, which many of you appreciate for various reasons. What’s a computed column? A computed column is a column that is derived (“computed”) from an expression. It cannot be written to. It works like … Continue reading Create Dynamic Views with jOOQ 3.17’s new Virtual Client Side Computed Columns →

Create Dynamic Views with jOOQ 3.17’s new Virtual Client Side Computed Columns

Posted on June 30, 2022 by lukaseder

One of jOOQ 3.17‘s coolest new features are client side computed columns. jOOQ 3.16 already added support for server side computed columns, which many of you appreciate for various reasons.

What’s a computed column?

A computed column is a column that is derived (“computed”) from an expression. It cannot be written to. It works like any column in a view. There are two types of computed columns:

• VIRTUAL computed columns, which are computed “on read”
• STORED computed columns, which are computed “on write”

Some SQL dialects use these exact terms to distinguish between the two features. Some dialects support both of them, some only support one of them.

Some typical use-cases for server side computed columns include:

CREATE TABLE customer (
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, try to insert some data into this table:

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

And you will get:

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

What are some limitations?

That’s a wonderful feature. Unfortunately, as always:

• Not all dialects support it
• Not all dialects support both VIRTUAL and/or STORED (both approaches have their benefits)
• The feature itself is quite limited in SQL

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

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

Why?

• It doesn’t support VIRTUAL, only STORED
• Even if it did support VIRTUAL, it currently throws SQL Error [0A000]: ERROR: cannot use subquery in column generation expression

There isn’t really any good reason that I can see for this limitation. After all, you can easily create a view like this:

CREATE VIEW v_customer AS
SELECT
id, first_name, last_name, address_id,
SELECT a.address
FROM address AS a
WHERE a.address_id = customer.address_id
) AS full_address
FROM customer

And now, you have the desired behaviour. This approach has its own caveats, including:

• The view is not the table. Every dialect has limitations with respect to updatable views, i.e. it can be difficult to write to this view.
• Views are stored objects, and as such need to be versioned and installed. This isn’t a huge problem per se, but there are folks who try to avoid this, because of… well, the extra effort of doing database change management correctly?
• You always have to decide whether to query the view or the table.

Enter jOOQ’s client side computed columns

This is why jOOQ 3.17 now offers this wonderful feature on the client side. Both versions are supported:

• VIRTUAL computed columns are columns that are replaced by their respective expression when the column appears in any non-write position, e.g. SELECT, WHERE, but also RETURNING
• STORED computed columns are columns that are computed when written to, e.g. in INSERT, UPDATE, MERGE

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

<configuration>
<generator>
<database>

<!-- Tell the code generator to add synthetic columns, i.e.
columns that the server does not know anything about -->
<syntheticObjects>
<columns>
<column>
<tables>customer|staff|store</tables>
<name>full_address</name>
<type>text</type>
</column>
<column>
<tables>customer|staff</tables>
<name>full_name</name>
<type>text</type>
</column>
</columns>
</syntheticObjects>

<!-- Now tell the code generator how 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(
address().ADDRESS_,
DSL.inline(", "),
address().POSTAL_CODE,
DSL.inline(", "),
address().city().CITY_,
DSL.inline(", "),
address().city().country().COUNTRY_
)</generator>
<includeExpression>full_address</includeExpression>
</forcedType>
</forcedTypes>
</database>
</generator>
</configuration>

The above example uses two new code generation features:

• Synthetic columns (i.e. the column doesn’t really exist in the schema)
• Computed columns

That’s it. With these two things, you can register a single jOOQ Field expression that computes the value of your desired columns. Notice how the FULL_ADDRESS makes use of implicit joins to simplify access to other tables. Of course, you could have also written a correlated subquery, which is one way to implement these implicit joins. It would have just been a bit more laborious.

You can query these columns like any other in jOOQ:

Result<Record2<String, String>> result =
ctx.select(CUSTOMER.FULL_NAME, CUSTOMER.FULL_ADDRESS)
.from(CUSTOMER)
.fetch();

The generated query does all the joining for you, transparently:

select
customer.first_name || ' ' || customer.last_name
as full_name,
alias_114975355.address || ', ' ||
alias_114975355.postal_code || ', ' ||
alias_57882359.city || ', ' ||
alias_1060004.country
as full_address
from (
customer
join (
address as alias_114975355
join (
city as alias_57882359
join country as alias_1060004
on alias_57882359.country_id = alias_1060004.country_id
on alias_114975355.city_id = alias_57882359.city_id
on customer.address_id = alias_114975355.address_id

The result 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 Street, 94239, La Paz, Mexico   |
|DANNY ISOM      |734 Bchar Place, 30586, Okara, Pakistan         |
|VALERIE BLACK   |782 Mosul Street, 25545, Brockton, United States|
|...             |...                                             |
+----------------+------------------------------------------------+

Note that as you’d expect, if you omit one of these columns, the relevant parts of the query aren’t generated, including implicit joins. So, for example, if you query this:

[...]

---

*[Original source](https://blog.jooq.org/create-dynamic-views-with-jooq-3-17s-new-virtual-client-side-computed-columns/)*