ForceType on check constraint or default value

问题: I have columns in the database that look like: AKTIV VARCHAR2(1 char) default 'J' not null constraint AVCON_428946_AKTIV_000 check (AKTIV IN ('J', 'N')), How do I...

问题:

I have columns in the database that look like:

AKTIV VARCHAR2(1 char) default 'J' not null
    constraint AVCON_428946_AKTIV_000 check (AKTIV IN ('J', 'N')),

How do I have to write the forcedType to get a Boolean field generated.

So far I have:

<forcedType>
    <userType>java.lang.Boolean</userType>
    <converter>db.Varchar2ToBooleanConverter</converter>
    <types>VARCHAR2(1)</types>
    <nullability>NOT_NULL</nullability>
</forcedType>

But how do I include the default value or the check constraint?


回答1:

This is a very interesting use case, which jOOQ currently cannot satisfy out of the box yet, but you can roll your own implementation. Some background

What's a check constraint

A check constraint is a predicate that can be placed in the schema to validate your data. While in a lot of cases, you'll apply such a constraint on a single column only, the fact that you have to repeat the column name AKTIV hints at the fact that a check constraint's scope is really the table, not the column. For example, you could have a constraint

CONSTRAINT chk CHECK (col1 > 0 AND col2 IN (1, 2))

This constraint cannot be clearly attributed to a single column. But like all constraints, it can be clearly attributed to the table.

A special way of defining check constraint in PostgreSQL and the SQL standard are domains, which are often used as reusable check constraints across several tables. jOOQ currently doesn't work with domains. Some feature requests include:

How to match them in jOOQ 3.11, programmatically

jOOQ-meta already exposes the CheckConstraintDefinition type from org.jooq.meta.Database.getCheckConstraints(SchemaDefinition) If you write a programmatic code generator configuration, then you can read the check constraint definitions and programmatically create your own forcedType configurations accordingly. This may not (yet) be available in all SQL dialects.

Another alternative would be to query your database's dictionary views directly, to find the check constraints that you find interesting, and then create forcedType configurations based on those. For example, in Oracle, you could write:

SELECT regexp_replace(search_condition_vc, '(w+).*', '1')
FROM all_constraints
WHERE constraint_name LIKE 'AVCON%' -- Add further restrictions here
AND regexp_like(search_condition_vc, 'INs*(s*''J''s*,s*''N''s*)')

From the projected SEARCH_CONDITION, you can now extract the affected column names again with a regular expression. Obviously, you may need to adapt the above logic to what you know about your domain.

How to match them in jOOQ 3.12, configuratively

I've just implemented #8446 in jOOQ 3.12, using which the above programmatic approach can also be implemented configuratively. Your <forcedType> would look like this:

<forcedType>
  <userType>java.lang.Boolean</userType>
  <converter>db.Varchar2ToBooleanConverter</converter>

  <!-- Place your SQL statement here -->
  <sql>
    SELECT regexp_replace(search_condition_vc, '(w+).*', '1')
    FROM all_constraints
    WHERE constraint_name LIKE 'AVCON%' -- Add further restrictions here
    AND regexp_like(search_condition_vc, 'INs*(s*''J''s*,s*''N''s*)')
  </sql>

  <!-- These may not be strictly needed -->
  <types>VARCHAR2(1)</types>
  <nullability>NOT_NULL</nullability>
</forcedType>
  • 发表于 2019-03-27 00:29
  • 阅读 ( 207 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除