Subiendo Fragmentos de SQL - Java, SQL y jOOQ.

Durante los últimos 13 años, jOOQ ha acumulado muchas características internas a las que usted, el usuario, no está expuesto. Una característica muy interesante es la posibilidad de que cualquier elemento arbitrario del árbol de expresiones jOOQ empuje un fragmento SQL a un nivel superior.

¿Como funciona?

El modelo de árbol de expresión jOOQ

Cuando escribe una consulta jOOQ, en realidad está creando un árbol de expresión de su declaración SQL que se parece a la sintaxis SQL real. Por ejemplo:

ctx.select(BOOK.ID, BOOK.TITLE)
   .from(BOOK)
   .where(BOOK.AUTHOR_ID.eq(1))
   .fetch();

En jOOQ no es más que un árbol de expresión de esta forma

                     Select (Statement)
                             |
          +------------------+-----------------+
          |                  |                 |
        Select             From              Where
          |                  |                 |
    +-----+------+           |                 |
    |            |           |                 |
TableField  TableField     Table        CompareCondition
  |     |     |     |        |           |     |       |
Table Field Table Field    BOOK    TableField  EQ     Val
  |     |     |     |                |     |           |
BOOK    ID   BOOK TITLE            Table Field         1
                                     |     |
                                    BOOK AUTHOR_ID

Al generar el SQL, jOOQ en su mayoría itera primero el ancho del árbol (es broma. Es principalmente la profundidad primero, pero a menudo se consideran algunos elementos secundarios en el mismo nivel, antes de descender de un nivel), recopilando cada elemento en un StringBuilder en la forma esperada:

SELECT book.id, book.title
FROM book
WHERE book.author_id = 1

Cada uno de estos llamados elementos del árbol de expresión QueryPart puede decidir por sí mismo cómo representará su SQL. por ejemplo, el CompareCondition generará aproximadamente esta secuencia:

…delegando aún más la generación SQL a sus hijos, sean quienes sean. A TableField decidirá calificar total/parcialmente/o no su Field referencia en absoluto, etc., por ejemplo, basado en la funcionalidad de mapeo de esquemas (multiusuario).

Si utiliza funciones, como Substring, esta función puede decidir por sí misma cómo debe generar su SQL. Como se puede ver en el manual, son todos iguales:

-- ACCESS
mid('hello world', 7)

-- ASE, SQLDATAWAREHOUSE, SQLSERVER
substring('hello world', 7, 2147483647)

-- AURORA_MYSQL, AURORA_POSTGRES, COCKROACHDB, CUBRID, H2, 
-- HANA, HSQLDB, IGNITE, MARIADB, MEMSQL, MYSQL, POSTGRES, REDSHIFT, 
-- SNOWFLAKE, SYBASE, VERTICA
substring('hello world', 7)

-- DB2, DERBY, INFORMIX, ORACLE, SQLITE
substr('hello world', 7)

-- FIREBIRD, TERADATA
substring('hello world' FROM 7)

De esta forma, un árbol de expresión jOOQ puede emular cualquier sintaxis en cualquier dialecto.

Pero, ¿y si la emulación no es local?

Emulaciones no locales

A veces, un QueryPart debe asumir la presencia de un elemento sintáctico no local para funcionar. Un caso reciente fue https://github.com/jOOQ/jOOQ/issues/11366.

Al escribir esta lógica procesal en jOOQ:

for_(i).in(1, 10).loop(
    insertInto
)

Por supuesto, no harías eso. ¡Escribiría una declaración de inserción masiva en su lugar y resolvería esto solo con SQL! Pero tienes tus razones, ¿no?

Luego, es posible que sea necesario emular el bucle FOR indexado en algunos dialectos, utilizando una instrucción WHILE equivalente. Entonces, en lugar de esta generación de SQL procesal simple, que podríamos obtener en Oracle, por ejemplo:

FOR i IN 1 .. 10 LOOP
  INSERT INTO t (a) VALUES (i);
END LOOP;

… generamos esto en MySQL, más o menos:

DECLARE i INT DEFAULT 1;
WHILE i <= 10 DO
  INSERT INTO t (a) VALUES (i);
  SET i = i + 1;
END WHILE;

Todavía se puede hacer completamente localmente, como se mencionó anteriormente. Hay un FOR elemento del árbol de expresión que puede generar localmente el DECLARE y WHILE solicitar piezas, en su lugar. Pero, ¿y si las variables locales no son posibles? ¿Qué pasa si no hay alcance de bloque, como en Firebird?

En Firebird, todas sus variables locales deben declararse en una sección de declaración de nivel superior. Si ejecutamos lo anterior en un bloque anónimo, el SQL de procedimiento generado correcto sería este:

EXECUTE BLOCK AS
  DECLARE i INT;
BEGIN
  :i = 1; -- The loop variable must still be initialised locally
  WHILE (:i <= 10) DO BEGIN
    INSERT INTO t (a) VALUES (i);
    :i = :i + 1;
  END
END

Este sigue siendo el caso cuando integramos el bucle más en elementos de flujo de control de procedimiento, como:

for_(i).in(1, 10).loop(
    for_(j).in(1, 10).loop(
        insertInto
    )
)

Por supuesto, todavía no lo harías. ¡Preferiría escribir una declaración de inserción masiva de un producto cartesiano y resolver eso solo con SQL! Pero, por desgracia, mantengamos el ejemplo simple.

Ahora tenemos declaraciones de variables anidadas, que aún funcionan bien en MySQL

DECLARE i INT DEFAULT 1;
WHILE i <= 10 DO 
  BEGIN
    DECLARE j INT DEFAULT 1;
    WHILE j <= 10 DO
      INSERT INTO t (a, b) VALUES (i, j);
      SET j = j + 1;
    END WHILE;
  END
  SET i = i + 1;
END WHILE;

Pero en Firebird, ambas declaraciones deberán ser empujadas hacia arriba:

EXECUTE BLOCK AS
  DECLARE i INT;
  DECLARE j INT;
BEGIN
  :i = 1; -- The loop variable must still be initialised locally
  WHILE (:i <= 10) DO BEGIN
    :j = 1; -- The loop variable must still be initialised locally
    WHILE (:j <= 10) DO BEGIN
      INSERT INTO t (a, b) VALUES (i, j);
      :j = :j + 1;
    END
    :i = :i + 1;
  END
END

Esto todavía no maneja todos los casos extremos (por ejemplo, algunos dialectos permiten "ocultar" variables locales, como PL/SQL), pero es muy útil para procedimientos, funciones y activadores simples, todos los cuales serán compatibles. de jOOQ 3.15.

¿Como funciona?

Alternativa 1: transformación del árbol de expresión

Hay muchas formas de hacer que una transformación de árbol de expresión funcione. A largo plazo, rediseñaremos nuestro modelo de árbol de expresión interno y también lo pondremos a disposición como una API pública para aquellos que deseen utilizar el analizador jOOQ y la transformación del árbol de expresión como un producto independiente. Hasta cierto punto, esto ya es posible utilizando el VisitListener SPI como se describe en este artículo de seguridad de nivel de fila, pero la implementación actual es compleja.

Además, es relativamente raro (hasta ahora) que un árbol de expresión requiera una transformación no local. Esto significa tratar ansiosamente de buscar candidatos potenciales. cada vez es probablemente exagerado.

Alternativa 2: Transformación perezosa del árbol de expresión

Podríamos transformar el árbol de expresiones "perezosamente", es decir, siempre asumiendo que no es necesario, y cuando lo es, lanzando una excepción y comenzando de nuevo. De hecho, hacemos esto, el "modelo" en jOOQ se llama un ControlFlowSignal, y se utiliza principalmente para eludir el número máximo de parámetros de enlace por límite de instrucción de diferentes dialectos. Es decir, solo contamos los valores de vinculación, y si hay más de 2000 en SQL Server (2100 son compatibles con SQL Server, pero solo 2000 con jtds), simplemente regeneramos SQL desde cero usando valores en línea en una estática. declaración.

Como siempre con jOOQ, puede reconfigurar estos límites a su propio valor.

Otro caso es cuando olvidaste encender el ROWNUM para LIMIT transformación al migrar desde Oracle antiguo ROWNUM filtración. Sería tonto buscar con impaciencia ROWNUM instancias cada vez. En cambio, simplemente regeneramos la consulta SQL completa cuando encontramos una y cuando no está usando Oracle.

La suposición aquí es que estas cosas suceden muy rara vez, y si lo hacen, no lo ha pensado y no quiere que la consulta falle en producción. El hecho de que una consulta probablemente ya lenta esté tomando sólo un poco más tiempo para generar jOOQ es un precio que vale la pena pagar para que la consulta siga funcionando Just Work™

Alternativa 3: Cadenas SQL generadas por parches

Ahora eso es lo que realmente hacemos.

Es mejor asumir que casi todas las transformaciones de SQL son locales (como en el Substring ejemplo), y corregir el SQL en caso de que no lo sea. ¡Al final, solo generamos cadenas SQL! Y como tal, ¿por qué no introducir un marco en el que podamos colocar marcadores especiales en cuadros de texto especiales y luego reemplazar ese cuadro con contenido SQL alternativo?

Sin la solución #11366, el código generado podría haberse visto así:

EXECUTE BLOCK AS
  -- Special marker here
BEGIN 
  -- Entering scope
  DECLARE i INT DEFAULT 1;
  WHILE (:i <= 10) DO BEGIN
    DECLARE j INT DEFAULT 1;
    WHILE (:j <= 10) DO BEGIN
      INSERT INTO t (a, b) VALUES (i, j);
      :j = :j + 1;
    END
    :i = :i + 1;
  END
  -- Exiting scope
END

No funciona en Firebird, por lo que estamos aplicando la corrección. Tenga en cuenta que hay un marcador especial barato que se coloca mediante la generación SQL de bloques anónimos, pero también para procedimientos, funciones y activadores, por ejemplo.

CREATE FUNCTION func1()
RETURNS INTEGER
AS 
  -- Special marker here
BEGIN
  -- Entering scope
  RETURN 1;
  -- Exiting scope
END

Ahora cada vez que el org.jooq.impl.DeclarationImpl la parte de consulta genera su SQL localmente, luego, en lugar de generar algo como:

DECLARE i INT DEFAULT 1;
DECLARE j INT;

Generamos (localmente)

:i = 1;
-- j isn't initialised here, so nothing needs to be done locally

Al mismo tiempo, empujamos el org.jooq.impl.DeclarationImpl en una variable de contexto visible de todo el alcance (consulte los comentarios "dentro del alcance" y "fuera del alcance").

Tan pronto como dejamos el alcance, tenemos que representar todas las declaraciones recopiladas, esta vez sin los valores predeterminados, por ejemplo

DECLARE i INT;
DECLARE j INT;

... luego inserte este procesamiento SQL justo donde estaba el marcador. Todos los marcadores posteriores, si los hay, se desplazan por la diferencia en la longitud del texto, por supuesto.

Aplicaciones en jOOQ

Esto se usa actualmente algunas veces en jOOQ:

  • Para emular llamadas a funciones Oracle PL/SQL con BOOLEAN parámetros/valores devueltos. Corregimos el SQL generado produciendo un resumen WITH cláusula con algunos BOOLEAN para NUMBER lógica de traducción. A partir de Oracle 12c, Oracle admite PL/SQL integrado en WITH, ¡lo cual es una característica bastante ingeniosa!
  • ¡Toda la funcionalidad JOIN implícita se implementa de esta manera! Los marcadores delimitan cada tabla en el FROM cláusula (por ejemplo FROM BOOK), y si en la consulta se encuentra una ruta desde una tabla marcada (p. ej. SELECT BOOK.AUTHOR.FIRST_NAME), entonces 1) en lugar de generar la ruta, se usa un alias sintético para la ruta para calificar la columna, 2) en lugar de generar la FROM BOOK mesa, un sintético LEFT JOIN Donde INNER JOIN se genera uniendo todas las relaciones uno a uno necesarias. A continuación se mostrará un ejemplo.
  • Las correcciones de alcance de variables locales de procedimiento de Firebird (y tal vez también T-SQL, veamos) anteriores se implementan de esta manera.
  • Algunas emulaciones que requieren que se agregue SQL a la declaración completa, como CREATE OR REPLACE PROCEDURE x emulaciones anteriores DROP PROCEDURE x para CREATE PROCEDURE x trabajar de la misma manera. Estos tipos de programas son "especiales" porque agregan otra declaración al conjunto de declaraciones. Esto significa que también tendremos que tener cuidado de ignorar cualquier posible conjunto de resultados o actualizaciones que esto produzca al llamar al lote desde JDBC.

Las aplicaciones futuras pueden incluir:

  • Más CTE de alto nivel, que son bastante útiles para una variedad de emulaciones

Un ejemplo de uniones implícitas:

SELECT
  book.author.first_name,
  book.author.last_name
FROM book -- Special marker around book

El SQL anterior no funciona en ningún dialecto, solo es específico de jOOQ. Generamos un alias para cada ruta única, según el código hash de la ruta, por lo que la consulta podría verse así:

SELECT
  -- The path isn't generated, but an alias for it is
  alias_xyz.first_name,
  alias_xyz.last_name
FROM (
  -- The marked "book" table is replaced by a join tree
  book 
    LEFT JOIN author AS alias_xyz 
    ON book.author_id = author.id
)

Solo reemplazamos book por (book LEFT JOIN ...) en la cadena SQL resultante. Con nuestro marco para definir ámbitos y guardar contexto y variables para cada ámbito, esto funciona para niveles arbitrarios de anidamiento. Siempre podemos identificar lo bueno book identificador para cada expresión de ruta, incluso para cosas como esta:

SELECT
  book.author.first_name,
  book.author.last_name,

  -- Different book tables here, because the nested scope 
  -- hides the "book" identifier from the outer scope
  (SELECT count(*) FROM book),
  (SELECT count(DISTINCT book.author.first_name) FROM book),

  -- Outer book, again
  (SELECT book.author.first_name)
FROM 
  book

Lo anterior siendo emulado así, corrigiendo dos ocurrencias marcadas de book por el mismo gráfico de unión:

SELECT
  alias_xyz.first_name,
  alias_xyz.last_name,

  -- No patching done to this book
  (SELECT count(*) FROM book),

  -- The alias_xyz alias is used again, the path is the same
  (SELECT count(DISTINCT alias_xyz.first_name) 

  -- And the book table is patched again with the same left join
   FROM (
     book 
       LEFT JOIN author AS alias_xyz 
       ON book.author_id = author.id
  )),

  -- Outer book, again
  (SELECT alias_xyz.first_name)
FROM (
  book 
    LEFT JOIN author AS alias_xyz 
    ON book.author_id = author.id
)

Parece tan sofisticado como complejo, pero en realidad funciona muy bien.

Tal vez en el futuro se prefiera transformar el árbol de expresión a modificar la cadena de resultados. Hasta ahora, con las aplicaciones actuales, era la forma menos resistente y la más potente.

Si quieres conocer otros artículos parecidos a Subiendo Fragmentos de SQL - 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