Uso de claves foráneas sintéticas jOOQ 3.14 para escribir uniones implícitas en vistas: Java, SQL y jOOQ.

jOOQ ha estado soportando una de las características más geniales de JPQL desde hace un tiempo: uniones implícitas. Al usar jOOQ, puede navegar de forma segura en sus relaciones uno a uno, generando LEFT JOIN implícitamente sin el esfuerzo de tener que pensar en los predicados de combinación y el orden de combinación correcto. Considere esta consulta de base de datos de Sakila aquí, si SQL admite uniones implícitas de forma nativa:



SELECT
cu.first_name,
cu.last_name
FROM customer AS cu
WHERE cu.address.city.country.country = 'Switzerland'


Se traduce a esta consulta en SQL nativo:



SELECT
cu.first_name,
cu.last_name
FROM customer AS cu
JOIN address AS ad ON cu.address_id = ad.address_id
JOIN city AS ci ON a.city_id = ci.city_id
JOIN country AS co ON ci.country_id = co.country_id
WHERE co.country = 'Switzerland'


Notar: Las uniones internas son compatibles a partir de jOOQ 3.14, dependiendo de si la clave externa es necesaria o no nula. El comportamiento predeterminado es producir LEFT JOIN que son la forma correcta de unir implícitamente claves foráneas opcionales. Las uniones implícitas no son una panacea. No todo JOIN el gráfico se puede transformar completamente en uso de unión implícita, y todo el uso de unión implícita no es más legible que SQL nativo JOIN gráficos. Pero tener esta opción es genial. Especialmente cuando sus claves son claves compuestas.

Uniones clásicas en vistas

En el diseño relacional clásico, las claves sustitutas a menudo se evitan y creo que aún deberíamos evitarlas en muchos casos. Incluso si no está de acuerdo, a veces puede trabajar en un esquema en el que hay pocas o ninguna clave sustituta. Un ejemplo de ello es SQL estándar INFORMATION_SCHEMA que se implementa, por ejemplo, en H2, HSQLDB, MariaDB, MySQL, PostgreSQL o SQL Server. Por ejemplo, al consultar HSQLDB DOMAIN_CONSTRAINTS vista para aplicar ingeniería inversa a los tipos de DOMINIO. La consulta jOOQ para esto fue:



Domains d = DOMAINS.as("d");
DomainConstraints dc = DOMAIN_CONSTRAINTS.as("dc");
CheckConstraints cc = CHECK_CONSTRAINTS.as("cc");

for (Record record : create()
.select(
d.DOMAIN_SCHEMA,
d.DOMAIN_NAME,
d.DATA_TYPE,
d.CHARACTER_MAXIMUM_LENGTH,
d.NUMERIC_PRECISION,
d.NUMERIC_SCALE,
d.DOMAIN_DEFAULT,
cc.CHECK_CLAUSE)
.from(d)
.join(dc)
.on(row(d.DOMAIN_CATALOG, d.DOMAIN_SCHEMA, d.DOMAIN_NAME)
.eq(dc.DOMAIN_CATALOG, dc.DOMAIN_SCHEMA, dc.DOMAIN_NAME))
.join(cc)
.on(row(dc.CONSTRAINT_CATALOG,
dc.CONSTRAINT_SCHEMA,
dc.CONSTRAINT_NAME)
.eq(cc.CONSTRAINT_CATALOG,
cc.CONSTRAINT_SCHEMA,
cc.CONSTRAINT_NAME))
.where(d.DOMAIN_SCHEMA.in(getInputSchemata()))
.orderBy(d.DOMAIN_SCHEMA, d.DOMAIN_NAME)
) { ... }


Por lo tanto, la consulta se unió a la relación de muchos a muchos entre DOMAINS - DOMAIN_CONSTRAINTS - CHECK_CONSTRAINTS para obtener toda la información necesaria para generar tipos de dominio. Estas vistas no se pueden actualizar y no tienen información de restricciones asociada con ellas, pero ¿y si pudiéramos definir restricciones sintéticas? jOOQ admite claves primarias sintéticas para facilitar la actualización de vistas.

Claves foráneas sintéticas

A partir de jOOQ 3.14, hemos modificado el funcionamiento de las claves sintéticas y las ediciones comerciales también admitirán claves externas sintéticas. Puede especificar una configuración como esta:



<configuration>
<generator>
<database>
<syntheticObjects>
<primaryKeys>
<primaryKey>
<tables>
CHECK_CONSTRAINTS|CONSTRAINTS|TABLE_CONSTRAINTS
</tables>
<fields>
<field>CONSTRAINT_(CATALOG|SCHEMA|NAME)</field>
</fields>
</primaryKey>
<primaryKey>
<tables>DOMAINS</tables>
<fields>
<field>DOMAIN_(CATALOG|SCHEMA|NAME)</field>
</fields>
</primaryKey>
</primaryKeys>
<foreignKeys>
<foreignKey>
<tables>DOMAIN_CONSTRAINTS</tables>
<fields>
<field>CONSTRAINT_(CATALOG|SCHEMA|NAME)</field>
</fields>
<referencedTable>CHECK_CONSTRAINTS</referencedTable>
</foreignKey>
<foreignKey>
<tables>DOMAIN_CONSTRAINTS</tables>
<fields>
<field>DOMAIN_(CATALOG|SCHEMA|NAME)</field>
</fields>
<referencedTable>DOMAINS</referencedTable>
</foreignKey>
</foreignKeys>
</syntheticObjects>
</database>
</generator>
</configuration>


Y ya el generador de código jOOQ pensará que estas vistas eran tablas que en realidad tenían restricciones como las siguientes:



ALTER TABLE CHECK_CONSTRAINTS
ADD PRIMARY KEY (
CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME
);

ALTER TABLE DOMAINS
ADD PRIMARY KEY (
DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME
);

ALTER TABLE DOMAIN_CONSTRAINTS
ADD FOREIGN KEY (
CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME
)
REFERENCES CHECK_CONSTRAINTS;

ALTER TABLE DOMAIN_CONSTRAINTS
ADD FOREIGN KEY (
DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME
)
REFERENCES DOMAINS;


Es posible una configuración más sofisticada, por ejemplo, para asignar nombres a restricciones, para tener restricciones compuestas usando un orden de campo diferente al orden en la tabla, o claves foráneas que hacen referencia a claves únicas en lugar de claves primarias. Para obtener una descripción completa de lo que está disponible, consulte el manual. Con los metadatos sintéticos anteriores disponibles para el generador de código, todos los muchos beneficios ahora también están disponibles en las vistas, que incluyen:

Uniones implícitas

Las uniones implícitas ahora también son posibles en estas vistas, lo que significa:

  1. Nunca más tendrá que acordarse de incluir todas las columnas clave en los predicados de combinación (adiós productos cartesianos accidentales)
  2. ¡Su código seguirá siendo correcto en caso de que su clave compuesta cambie a otra cosa!

Así que es más que una cuestión de conveniencia, también es una cuestión de precisión. Nuestra consulta de antes ahora se puede escribir así, de una manera mucho más concisa:



DomainConstraints dc = DOMAIN_CONSTRAINTS.as("dc");

for (Record record : create()
.select(
dc.domains().DOMAIN_SCHEMA,
dc.domains().DOMAIN_NAME,
dc.domains().DATA_TYPE,
dc.domains().CHARACTER_MAXIMUM_LENGTH,
dc.domains().NUMERIC_PRECISION,
dc.domains().NUMERIC_SCALE,
dc.domains().DOMAIN_DEFAULT,
dc.checkConstraints().CHECK_CLAUSE)
.from(dc)
.where(dc.domains().DOMAIN_SCHEMA.in(getInputSchemata()))
.orderBy(dc.domains().DOMAIN_SCHEMA, dc.domains().DOMAIN_NAME)
) { ... }


Observe cómo usamos la tabla de relaciones como la única tabla para poner en el FROM cláusula. De esta manera podemos navegar de un lado a otro de las relaciones uno a uno de DOMAIN_CONSTRAINTS -> DOMAINS y DOMAIN_CONSTRAINTS -> CHECK_CONSTRAINTS. La consulta SQL resultante es equivalente a la anterior, pero se ha ido toda la maldad de unir claves compuestas de 3 columnas. Personalmente, lo encuentro mucho más legible.

Trabajo futuro

Hasta ahora, solo las relaciones uno a uno se pueden navegar de esta manera. JPQL también ofrece navegación a muchas relaciones con algunas restricciones. Es una pendiente resbaladiza. Cuando ofrece relaciones a muchos, algunos casos de uso son obvios, pero la semántica de otros lo es menos. Por ejemplo, no es una buena idea dejar el SELECT La cláusula produce más (o menos) filas dependiendo de la presencia de una columna proyectada. Esta es la razón por la cual jOOQ hasta ahora solo ha producido LEFT JOIN para uniones implícitas, ya que esto garantizaría que una columna unida implícitamente no reduzca el número de filas debido a una INNER JOIN sin producir coincidencias. Sin embargo, hay muchas cosas que aún podemos agregar en #7536, que incluyen:

  • Uniones muchos-muchos implícitos en el FROM cláusula, donde no causan ningún problema
  • Uniones implícitas en DML
  • Compatibilidad con Parser para ofrecer esta funcionalidad también en https://www.jooq.org/translate y para todos los que trabajan con jOOQ SQL a través de jOOQ's ParsingConnection

¡Y mucho más!

Si quieres conocer otros artículos parecidos a Uso de claves foráneas sintéticas jOOQ 3.14 para escribir uniones implícitas en vistas: Java, SQL y jOOQ. puedes visitar la categoría Código.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Subir

Esta página web utiliza cookies para analizar de forma anónima y estadística el uso que haces de la web, mejorar los contenidos y tu experiencia de navegación. Para más información accede a la Política de Cookies . Ver mas