Saturday, May 13, 2023
HomeJavaCreate Dynamic Views with jOOQ 3.17’s new Digital Consumer Aspect Computed Columns...

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


One in all jOOQ 3.17‘s coolest new options are shopper facet computed columns. jOOQ 3.16 already added assist for server facet computed columns, which a lot 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 several column in a view. There are two kinds 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 differentiate between the 2 options. Some dialects assist each of them, some solely assist one in every of them.

Some typical use-cases for server facet 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’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 at all times:

  • 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 have 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 presently throws SQL Error [0A000]: ERROR: can not use subquery in column technology 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.tackle 
    FROM tackle AS a
    WHERE a.address_id = buyer.address_id
  ) AS full_address
FROM buyer

And now, you’ve 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 tough to jot down to this view.
  • Views are saved objects, and as such have to 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 accurately?
  • You at all times must determine whether or not to question the view or the desk.

Enter jOOQ’s shopper facet computed columns

For this reason jOOQ 3.17 now provides this glorious characteristic on the shopper 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 have 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|workers|retailer</tables>
                        <identify>full_address</identify>
                        <sort>textual content</sort>
                    </column>
                    <column>
                        <tables>buyer|workers</tables>
                        <identify>full_name</identify>
                        <sort>textual content</sort>
                    </column>
                </columns>
            </syntheticObjects>

            <!-- Now inform the code generator easy 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 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. After all, you can have additionally written a correlated subquery, which is one technique to implement these implicit joins. It could have simply been a bit extra laborious.

You possibly can question these columns like some other in jOOQ:

Outcome<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 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 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 anticipate, when you omit one in every of these columns, the related elements of the question aren’t generated, together with implicit joins. So, for instance, when you question this:

Outcome<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 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,
  price 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 right conversion price at any given date, if wanted.

Now, any time we wish to sum up transactions, now we have to transform the quantity to some person foreign money, regardless of the transaction foreign money. Isn’t that boring? We shouldn’t must repeat this logic in all places.

You possibly can after all write views like this:

CREATE VIEW v_transaction AS
SELECT
  id, quantity, foreign money,
  quantity * (
    SELECT c.price
    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 would have liked a distinct 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 straight?

<configuration>
    <generator>
        <database>

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

            <!-- And outline the computations -->
            <forcedTypes>
                <forcedType>
                    <generator>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")))))
                    </generator>
                    <includeExpression>
                        TRANSACTION.AMOUNT_USD
                    </includeExpression>
                </forcedType>
                <forcedType>
                    <generator>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")))))
                    </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 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 cross round jOOQ and entry from wherever.

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

And instantly, we get a distinct 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 person, 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 person dream.

Extra potentialities

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

In spite of everything, a shopper facet computed column is only a “variable” referencing an expression that’s expanded once 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 concerning the column, or the expression. Meaning the characteristic is especially helpful for projections and aggregations / computations, much less for queries. For instance, possibly don’t run filters on such columns.

Saved shopper facet computed columns

A future weblog submit will discuss concerning the STORED model of shopper facet 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 fully completely different.

Extra details about this:

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments