Статьи

Одиссея трансформации SQL MS Access

Апрель 22, 2018

Недавно мы добавили поддержку базы данных MS Access в jOOQ 3.3 . Возможно, это была наша самая сложная интеграция. База данных MS Access имеет свои способы, и многие из них тоже. Но, к счастью, возможности внутреннего преобразования jOOQ в SQL уже очень продвинуты, как мы показали ранее в посте блога об эмуляции предикатов IN выражения-значения строки .

В этом посте мы хотим показать вам, как мы можем прозрачно эмулировать многозначный оператор INSERT с помощью различных шагов, пока он не будет работать и для MS Access.

Рассмотрим следующий стандартный оператор SQL-92 INSERT:

INSERT INTO books (author, title)
VALUES ('George Orwell', '1984'),
       ('Leo Tolstoy', 'War and Peace');

С помощью jOOQ мы можем написать это так:

DSL.using(configuration)
   .insertInto(BOOKS, AUTHOR, TITLE)
   .values("George Orwell", "1984")
   .values("Leo Tolstoy", "War and Peace")
   .execute();

Смотрите руководство для более подробной информации . Вышеприведенный INSERTсинтаксис нескольких записей поддерживается различными базами данных, но не этими:

  • жар-птица
  • Энгр
  • MS Access
  • оракул
  • SQLite
  • Sybase Adaptive Server Enterprise

Но, к счастью, приведенный выше синтаксис можно эмулировать с помощью INSERT .. SELECT

INSERT INTO books (author, title)
SELECT 'George Orwell', '1984'
UNION ALL
SELECT 'Leo Tolstoy', 'War and Peace';

Обратите внимание, что для некоторых баз данных FROMв большинстве операторов SQL требуется условие. Как и MS Access. Простой способ эмулировать то, что Oracle называет DUAL с помощью MS Access:

INSERT INTO books (author, title)
SELECT 'George Orwell', '1984'
FROM (SELECT count(*) FROM MSysResources) AS DUAL
UNION ALL
SELECT 'Leo Tolstoy', 'War and Peace';
FROM (SELECT count(*) FROM MSysResources) AS DUAL

Для простоты, давайте просто предположим, что DUAL действительно существует:

INSERT INTO books (author, title)
SELECT 'George Orwell', '1984'
FROM DUAL
UNION ALL
SELECT 'Leo Tolstoy', 'War and Peace';
FROM DUAL

Но этот синтаксис не поддерживается, опять же, MS Access, как вы можете видеть в руководстве .

Это гласит:

Синтаксис
Запрос на добавление нескольких записей:

INSERT INTO target [(field1[, field2[, …]])] 
[IN externaldatabase]
SELECT field1[, field2[, …]
FROM tableexpression

табличное выражение: имя таблицы или таблиц, из которых вставляются записи. Этот аргумент может быть одним именем таблицы или составным элементом, полученным в результате операции INNER JOIN, LEFT JOIN, RIGHT JOIN или сохраненного запроса.

В предложениях явно нет места UNION ALL, но мы можем использовать «сохраненный запрос» в FROMпредложении. Учитывая наше первоначальное намерение, это примерно соответствует:

INSERT INTO books (author, title)
SELECT *
FROM (
  SELECT 'George Orwell', '1984'
  FROM DUAL
  UNION ALL
  SELECT 'Leo Tolstoy', 'War and Peace';
  FROM DUAL
)

… или вы можете подумать. К сожалению, вышеупомянутая попытка приводит к этому сообщению об ошибке:

SELECT * нельзя использовать в запросе INSERT INTO, если таблица источника или назначения содержит многозначное поле

Поэтому нам нужно явно выбрать каждый столбец из нашей вновь созданной производной таблицы. Но эти столбцы не имеют имен (пока). Стандартный способ присвоения имен столбцам производной таблицы заключается в использовании списков производных столбцов, которые переименовывают таблицу и все ее столбцы за один раз. Ранее мы писали об этом здесь .

Для нашего оператора SQL это означает:

INSERT INTO books (author, title)
SELECT a, b
FROM (
  SELECT 'George Orwell', '1984'
  FROM DUAL
  UNION ALL
  SELECT 'Leo Tolstoy', 'War and Peace';
  FROM DUAL
) t(a, b)

К настоящему времени вы, возможно, догадались, что наша одиссея преобразования SQL еще не закончена. MS Access не поддерживает производные списки столбцов, и это в хорошей компании. Эти базы данных также не поддерживают их:

  • H2
  • Энгр
  • MariaDB
  • MS Access
  • MySQL
  • оракул
  • SQLite

Это означает, что нам нужно эмулировать еще одну функцию SQL, которая приводит к следующему запросу:

INSERT INTO books (author, title)
SELECT a, b
FROM (
  -- This subselect defines column names
  SELECT '' AS a, '' AS b
  FROM DUAL
  WHERE 1 = 0
  UNION ALL
  -- This subselect provides our data
  SELECT *
  FROM (
    SELECT 'George Orwell', '1984'
    FROM DUAL
    UNION ALL
    SELECT 'Leo Tolstoy', 'War and Peace';
    FROM DUAL
  ) t
) t

И мы настроены. Почти. Давайте подставим реальное выражение DUAL обратно в запрос

INSERT INTO books (author, title)
SELECT a, b
FROM (
  -- This subselect defines column names
  SELECT '' AS a, '' AS b
  FROM (SELECT count(*) FROM MSysResources) AS DUAL
  WHERE 1 = 0
  UNION ALL
  -- This subselect provides our data
  SELECT *
  FROM (
    SELECT 'George Orwell', '1984'
    FROM (SELECT count(*) FROM MSysResources) AS DUAL
    UNION ALL
    SELECT 'Leo Tolstoy', 'War and Peace';
    FROM (SELECT count(*) FROM MSysResources) AS DUAL
  ) t
) t

Разве это не красота !?

Сценарий реальной жизни

В реальной жизни вы, вероятно, так или иначе обойдете это ограничение, потому что никто не пишет (или не читает и не поддерживает) такой SQL-код вручную. Вы, вероятно, прибегаете к множественному выполнению INSERTоператоров с одной записью , к пакетной обработке или как угодно. Но в реальной жизни вы бы также поддерживали SQL Server или Oracle или какую-либо другую базу данных в дополнение к MS Access, и вы постоянно сталкивались с такими проблемами, которые вам пришлось бы исправлять вручную. Это может очень расстраивать!

Если, конечно, вы не используете jOOQ и забудете о вышеупомянутых деталях, чтобы просто написать следующий, интуитивно понятный, стандартный оператор SQL:

DSL.using(configuration)
   .insertInto(BOOKS, AUTHOR, TITLE)
   .values("George Orwell", "1984")
   .values("Leo Tolstoy", "War and Peace")
   .execute();

… Который работает именно так во всех 16 поддерживаемых СУБД jOOQ. Чего же ты ждешь?

32
Share: