如何用jOOQ 3.17新的虚拟客户端计算列创建动态视图

jOOQ 3.17最酷的新功能之一是客户端的计算列。jOOQ 3.16已经增加了对服务器端计算列的支持，你们中的许多人出于各种原因对此表示赞赏。

什么是计算列？

计算列是一个从表达式派生（"计算"）的列。它不能被写入。它与视图中的任何列一样工作。有两种类型的计算列：

• VIRTUAL 计算列，在 "读取 "时进行计算
• STORED 计算列，在 "写 "时进行计算。

一些SQL方言使用这些确切的术语来区分这两种功能。有些方言同时支持这两种功能，有些只支持其中一种。

服务器端计算列的一些典型用例包括：

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
);

现在，试着在这个表中插入一些数据。

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

你会得到：

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

有什么限制吗？

这是个很好的功能，不幸的是，和以往一样：

• 不是所有的方言都支持它
• 不是所有的方言都同时支持VIRTUAL 和/或STORED （两种方法都有其好处）
• 该功能本身在SQL中是相当有限的

让我们来看看第三点。如果我们想通过使用连接或相关的子查询来 "计算 "一个列呢？我们不能，在SQL中。例如，PostgreSQL就拒绝这样做：

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
);

为什么？

• 它不支持VIRTUAL ，只支持STORED
• 即使它支持VIRTUAL ，它目前也会抛出SQL错误[0A000]。ERROR: 不能在列的生成表达式中使用子查询

对于这个限制，我没有看到任何好的理由。毕竟，你可以很容易地创建一个这样的视图。

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

现在，你有了想要的行为。这种方法有它自己的注意事项，包括：

• 视图不是表。每种方言在可更新的视图方面都有限制，也就是说，向这个视图写东西可能很困难。
• 视图是存储对象，因此需要进行版本管理和安装。这本身并不是一个大问题，但有些人试图避免这样做，因为......好吧，正确进行数据库变更管理的额外努力？
• 你总是要决定是查询视图还是表。

进入jOOQ的客户端计算列

这就是为什么jOOQ 3.17现在在客户端提供这种奇妙的功能，两个版本都支持：

• VIRTUAL 计算列是指当列出现在任何非写的位置时，被其各自的表达式所取代的列，例如： , , 但同时也是SELECT WHERE RETURNING
• STORED 计算列是指在写入时被计算的列，例如在 , 。INSERT UPDATE MERGE

让我们先看看VIRTUAL 中的计算列。假设有Maven配置，上述两种用例可以在代码生成器中进行如下配置。

<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>

上面的例子使用了两个新的代码生成功能：

• 合成列（即该列在模式中并不真正存在）
• 计算列

就这样了。有了这两样东西，你就可以注册一个jOOQField 表达式，计算出你想要的列的值。请注意FULL_ADDRESS 是如何利用隐式连接来简化对其他表的访问。当然，你也可以写一个相关的子查询，这是实现这些隐式连接的一种方法。只是这样做会比较费劲。

你可以像jOOQ中的其他列一样查询这些列：

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

生成的查询为你做所有的连接，是透明的：

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
  )

其结果是：

+----------------+------------------------------------------------+
|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|
|...             |...                                             |
+----------------+------------------------------------------------+

注意，正如你所期望的那样，如果你省略了这些列中的一个，查询的相关部分就不会生成，包括隐式连接。因此，举例来说，如果你查询这个：

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

生成的SQL就简单多了：

select customer.first_name || ' ' || customer.last_name as full_name
from customer

一个更复杂的例子

SQL JOIN有时是相当无聊和重复的。考虑一下这个模式：

CREATE TABLE currency (
  code CHAR(3) NOT NULL,
  
  PRIMARY KEY (code)
);

CREATE TABLE conversion (
  from_currency CHAR(3) NOT NULL,
  to_currency CHAR(3) NOT NULL,
  rate NUMERIC(18, 2) NOT NULL,
  
  PRIMARY KEY (from_currency, to_currency),
  FOREIGN KEY (from_currency) REFERENCES currency,
  FOREIGN KEY (to_currency) REFERENCES currency
);

CREATE TABLE transaction (
  id BIGINT NOT NULL,
  amount NUMERIC(18, 2) NOT NULL,
  currency CHAR(3) NOT NULL,
  
  PRIMARY KEY (id),
  FOREIGN KEY (currency) REFERENCES currency
);

一个典型的（但简化了的）财务应用，它的交易有金额和与金额相关的货币。想象一下，CONVERSION 表可以有位时间的版本，以确保我们可以在任何给定的日期计算出正确的转换率，如果需要的话。

现在，任何时候我们想对交易进行汇总，我们都必须将金额转换成某种用户货币，而不考虑交易货币。这不是很无聊吗？我们不应该在所有地方重复这种逻辑。

当然，你可以这样写视图：

CREATE VIEW v_transaction AS
SELECT
  id, amount, currency,
  amount * (
    SELECT c.rate
    FROM conversion AS c
    WHERE c.from_currency = t.currency
    AND c.to_currency = 'USD'
  ) AS amount_usd
FROM transaction AS t

这将有助于在USD 。如果我们需要不同的货币，我们可以创建不同的视图，创建存储（表的价值，甚至？）函数，或者用jOOQ生成SQL。

但为什么不直接用jOOQ生成的表来存储计算呢？

<configuration>
    <generator>
        <database>

            <!-- Again, add the synthetic columns -->
            <syntheticObjects>
                <columns>
                    <column>
                        <tables>TRANSACTION</tables>
                        <name>AMOUNT_USD</name>
                        <type>NUMERIC</type>
                    </column>
                    <column>
                        <tables>TRANSACTION</tables>
                        <name>AMOUNT_USER_CURRENCY</name>
                        <type>NUMERIC</type>
                    </column>
                </columns>
            </syntheticObjects>

            <!-- And define the computations -->
            <forcedTypes>
                <forcedType>
                    <generator>ctx -> AMOUNT.times(DSL.field(
   DSL.select(Conversion.CONVERSION.RATE)
      .from(Conversion.CONVERSION)
      .where(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.times(DSL.field(
    DSL.select(Conversion.CONVERSION.RATE)
       .from(Conversion.CONVERSION)
       .where(Conversion.CONVERSION.FROM_CURRENCY.eq(CURRENCY))
       .and(Conversion.CONVERSION.TO_CURRENCY.eq(
           (String) ctx.configuration().data("USER_CURRENCY")))))
                    </generator>
                    <includeExpression>
                        TRANSACTION\.AMOUNT_USER_CURRENCY
                    </includeExpression>
                </forcedType>
            </forcedTypes>
        </database>
    </generator>
</configuration>

它做了你期望它做的事情。在两列上附加一个相关的子查询。然而，有趣的地方是AMOUNT_USER_CURRENCY 列。它访问了ctx.configuration().data("USER_CURRENCY") 。这只是任意的用户定义的数据，你可以在jOOQ上传递，并从任何地方访问。

当你运行这个查询时，不设置那个"USER_CURRENCY" 信息，像这样：

ctx.select(
        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()

生成的SQL是这样的：

select
  TRANSACTION.ID,
  TRANSACTION.AMOUNT,
  TRANSACTION.CURRENCY,
  (TRANSACTION.AMOUNT * (
    select CONVERSION.RATE
    from CONVERSION
    where (
      CONVERSION.FROM_CURRENCY = TRANSACTION.CURRENCY
      and CONVERSION.TO_CURRENCY = 'USD'
    )
  )) AMOUNT_USD,
  (TRANSACTION.AMOUNT * (
    select CONVERSION.RATE
    from CONVERSION
    where (
      CONVERSION.FROM_CURRENCY = TRANSACTION.CURRENCY
      and CONVERSION.TO_CURRENCY = null
    )
  )) AMOUNT_USER_CURRENCY,
  sum((TRANSACTION.AMOUNT * (
    select CONVERSION.RATE
    from CONVERSION
    where (
      CONVERSION.FROM_CURRENCY = TRANSACTION.CURRENCY
      and CONVERSION.TO_CURRENCY = 'USD'
    )
  ))) over () total_usd,
  sum((TRANSACTION.AMOUNT * (
    select CONVERSION.RATE
    from CONVERSION
    where (
      CONVERSION.FROM_CURRENCY = TRANSACTION.CURRENCY
      and CONVERSION.TO_CURRENCY = null
    )
  ))) over () total_user_currency
from TRANSACTION
order by TRANSACTION.ID

用户货币是未定义的（即NULL ），所以我们没有得到任何东西：

+----+------+--------+----------+--------------------+---------+-------------------+
|  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}|
+----+------+--------+----------+--------------------+---------+-------------------+

现在，让我们在像这样设置了configuration().data() 的值之后，再次运行查询：

// This mutates the configuration.
// To make a copy, use configuration().derive()
ctx.configuration().data("USER_CURRENCY", "CHF");

突然间，我们得到了一个不同的结果：

+----+------+--------+----------+--------------------+---------+-------------------+
|  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|
+----+------+--------+----------+--------------------+---------+-------------------+

这是很强大的!想象一下，在一个应用程序中，你：

• 从连接池中获取一个连接
• 初始化一些上下文变量，比如用户，以及他们的设置
• 自动让任意的 "视图"（即jOOQ表达式，既来自动态SQL，也来自这个功能）更新其内容

一个真正的jOOQ和SQL强大用户的梦想。

更多的可能性

上面的例子只是展示了标量子查询的简单情况，但没有什么能阻止你使用：

• 嵌套记录（通过ROW 构造函数）
• 嵌套集合（通过MULTISET 构造函数）。

毕竟，客户端的计算列只是一个引用表达式的 "变量"，当你使用该列运行查询时，该表达式会被展开。

注意事项

与服务器端的虚拟计算列不同，你不能在这些列上放一个索引，因为服务器对该列或表达式一无所知。这意味着该功能主要对投影和聚合/计算有用，对查询的作用较小。例如，也许不要在这种列上运行过滤器。

存储的客户端计算列

未来的一篇博文将谈论STORED 版本的客户端计算列，其中也包括新的审计列功能。作为一个预告，只要在一个非合成列（即你的模式中的一个实际的列）上实现一个像上面那样的Generator ，就可以看到，现在的行为已经完全不同。