One in all jOOQ 3.17‘s coolest new options are shopper aspect computed columns. jOOQ 3.16 already added help for server aspect computed columns, which lots of you recognize 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 every column in a view. There are two sorts of computed columns:
VIRTUALcomputed columns, that are computed “on learn”STOREDcomputed columns, that are computed “on write”
Some SQL dialects use these actual phrases to differentiate between the 2 options. Some dialects help each of them, some solely help certainly one of them.
Some typical use-cases for server aspect 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’re going to 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 help it
- Not all dialects help each
VIRTUALand/orSTORED(each approaches have their advantages) - The characteristic itself is sort of restricted in SQL
Let’s have a look at the third bullet. What if we needed 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 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, solelySTORED - Even when it did help
VIRTUAL, it at present throws SQL Error [0A000]: ERROR: can not use subquery in column technology expression
There isn’t actually any good purpose 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 could have the specified behaviour. This strategy has its personal caveats, together with:
- The view will not be 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 should 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… properly, the additional effort of doing database change administration accurately?
- You all the time need to 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 presents this excellent characteristic on the shopper aspect. Each variations are supported:
VIRTUALcomputed columns are columns which might be changed by their respective expression when the column seems in any non-write place, e.g.SELECT,WHERE, but additionallyRETURNINGSTOREDcomputed columns are columns which might be computed when written to, e.g. inINSERT,UPDATE,MERGE
Let’s first have a look at VIRTUAL computed columns. The above two use-cases will be configured as follows within the code generator, assuming a Maven config.
buyer|workers|retailer
full_address
textual content
buyer|workers
full_name
textual content
ctx -> DSL.concat(
FIRST_NAME, DSL.inline(" "), LAST_NAME)
full_name
ctx -> DSL.concat(
handle().ADDRESS_,
DSL.inline(", "),
handle().POSTAL_CODE,
DSL.inline(", "),
handle().metropolis().CITY_,
DSL.inline(", "),
handle().metropolis().nation().COUNTRY_
)
full_address
The above instance makes use of two new code technology 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 possibly can have additionally written a correlated subquery, which is one approach to implement these implicit joins. It might have simply been a bit extra laborious.
You may question these columns like every other in jOOQ:
Outcome> 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 a part of (
handle as alias_114975355
be a part of (
metropolis as alias_57882359
be a 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 anticipate, in the event you omit certainly one of these columns, the related components of the question aren’t generated, together with implicit joins. So, for instance, in the event you question this:
Outcome> outcome =
ctx.choose(CUSTOMER.FULL_NAME)
.from(CUSTOMER)
.fetch();
The generated SQL is far easier:
choose buyer.first_name || ' ' || buyer.last_name as full_name
from buyer
A extra advanced instance
SQL JOINs will be fairly boring and repetitive at occasions. 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 ensure we will calculate the proper conversion fee at any given date, if wanted.
Now, any time we need to sum up transactions, we’ve to transform the quantity to some person foreign money, regardless of the transaction foreign money. Isn’t that boring? We shouldn’t need to 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.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 may assist summing up all transactions in USD. If we wanted a unique foreign money, we will 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 immediately?
TRANSACTION
AMOUNT_USD
NUMERIC
TRANSACTION
AMOUNT_USER_CURRENCY
NUMERIC
ctx -> AMOUNT.occasions(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")))))
TRANSACTION.AMOUNT_USD
ctx -> AMOUNT.occasions(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")))))
TRANSACTION.AMOUNT_USER_CURRENCY
It does what you’d anticipate it does. Connect a correlated subquery to the 2 columns. The fascinating 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 move 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 person 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 all of the sudden, 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 software the place you:
- Get a connection from the connection pool
- Initialise just a few context variables, such because the person, 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 person dream.
Extra prospects
The above examples had been simply displaying easy instances 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 while 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. Which means the characteristic is principally helpful for projections and aggregations / computations, much less for queries. For instance, possibly don’t run filters on such columns.
Saved shopper aspect computed columns
A future weblog publish will speak concerning the STORED model of shopper aspect computed columns, which additionally consists of 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 fully completely different.
Extra details about this:
