[ad_1]
Considered one of 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 admire 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 varieties of computed columns:
VIRTUALcomputed columns, that are computed “on learn”STOREDcomputed columns, that are computed “on write”
Some SQL dialects use these precise phrases to differentiate between the 2 options. Some dialects help each of them, some solely help one in every 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 information 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 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 help
VIRTUAL, solelySTORED - Even when it did help
VIRTUAL, it at present throws SQL Error [0A000]: ERROR: can not use subquery in column era expression
There isn’t actually any good cause that I can see for this limitation. In spite of everything, you possibly can 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 might have the specified behaviour. This method has its personal caveats, together with:
- The view just isn’t 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 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 provides this glorious characteristic on the shopper aspect. Each variations are supported:
VIRTUALcomputed 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 additionallyRETURNINGSTOREDcomputed columns are columns which can be computed when written to, e.g. inINSERT,UPDATE,MERGE
Let’s first take a look at VIRTUAL computed columns. The above two use-cases could 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|workers|retailer</tables>
<title>full_address</title>
<kind>textual content</kind>
</column>
<column>
<tables>buyer|workers</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(
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 possibly can 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 may have additionally written a correlated subquery, which is one approach to implement these implicit joins. It could have simply been a bit extra laborious.
You possibly can question these columns like every other in jOOQ:
End result<Record2<String, String>> end result =
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 a part of (
tackle 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 end 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 Road, 94239, La Paz, Mexico | |DANNY ISOM |734 Bchar Place, 30586, Okara, Pakistan | |VALERIE BLACK |782 Mosul Road, 25545, Brockton, United States| |... |... | +----------------+------------------------------------------------+
Observe that as you’d anticipate, in the event you omit one in every 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:
End result<Record1<String>> end result =
ctx.choose(CUSTOMER.FULL_NAME)
.from(CUSTOMER)
.fetch();
The generated SQL is way less complicated:
choose buyer.first_name || ' ' || buyer.last_name as full_name
from buyer
A extra advanced instance
SQL JOINs could 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,
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 software 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 right conversion charge at any given date, if wanted.
Now, any time we need to sum up transactions, we’ve got 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 far and wide.
You possibly can after all 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 might 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>
<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.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().information("USER_CURRENCY")))))
</generator>
<includeExpression>
TRANSACTION.AMOUNT_USER_CURRENCY
</includeExpression>
</forcedType>
</forcedTypes>
</database>
</generator>
</configuration>
It does what you’d anticipate 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().information("USER_CURRENCY"). That’s simply arbitrary user-defined information, which you’ll be able to move round jOOQ and entry from wherever.
If you run this question with out setting that "USER_CURRENCY" data 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().information() worth like this:
// This mutates the configuration.
// To make a duplicate, use configuration().derive()
ctx.configuration().information("USER_CURRENCY", "CHF");
And all of the sudden, we get a unique end result:
+----+------+--------+----------+--------------------+---------+-------------------+ | 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 couple of context variables, such because the consumer, and their settings
- Routinely 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 potentialities
The above examples have been simply exhibiting 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. 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 publish 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:
[ad_2]
