Перейти к содержимому
BXWiki

Код, совместимый с PostgreSQL - кавычки, функции, индексы

Готовим свой код к работе на PostgreSQL: убираем привычки MySQL из запросов, переводим функции на помощник и проверяем имена индексов и типы колонок.

Решение

Прикладной код на ORM обычно переносится вообще без правок. Проблемы живут там, где писали прямой SQL: кавычки, функции базы и предположения о регистре имён.

Убираем кавычки и лишнее квотирование:

// было: SELECT `ID`, `NAME` FROM `b_vendor_item` WHERE `NAME` = "тест"
// стало: одинарные кавычки для значений, идентификаторы без кавычек
$sql = "SELECT ID, NAME FROM b_vendor_item WHERE NAME = 'тест'";
// двойные кавычки в PostgreSQL означают идентификатор, а не строку

Обратные кавычки вокруг имён понимает только MySQL. Двойные кавычки вокруг значения в PostgreSQL превращают его в имя колонки, и запрос падает с сообщением о несуществующем поле.

Экранируем значения помощником:

$connection = \Bitrix\Main\Application::getConnection();
$helper = $connection->getSqlHelper();


$sql = 'SELECT ID FROM b_vendor_item WHERE CODE = \'' . $helper->forSql($code) . '\'
        AND IBLOCK_ID = ' . (int)$iblockId;
// значения - через forSql и приведение типов, имена - через quote

Помощник запросов делает две совершенно разные вещи. Одно средство экранирует значения, другое квотирует имена таблиц и колонок, и путать их - готовая ошибка или уязвимость.

Заменяем функции базы:

$sql = 'SELECT ' . $helper->getConcatFunction("NAME", "' '", "CODE") . ' AS TITLE
        FROM b_vendor_item';
// склейка строк, дата и подстроки у баз называются по-разному

Функции склейки, работы с датой и подстроками в разных базах называются иначе. Помощник собирает нужный вариант сам, и запрос перестаёт зависеть от конкретной базы.

Проверяем имена индексов:

-- имя индекса уникально в пределах всей схемы, а не таблицы
CREATE INDEX ix_vendor_item_code ON b_vendor_item (CODE);
-- одинаковое имя на двух таблицах в PostgreSQL даст конфликт

В MySQL два индекса с одним именем на разных таблицах допустимы. В PostgreSQL имена индексов глобальны, поэтому в имя включают таблицу и колонки.

Регистр имён колонок в результате выборки у баз различается. Штатное чтение строки приводит имена к верхнему регистру само, а свой разбор результата этого не делает и ломается на другой базе.

Беззнаковых чисел в PostgreSQL нет вовсе, а часть типов называется иначе. Такие колонки проверяют заранее и переводят на подходящие типы ещё на прежней базе.

Типичные проблемы

Запрос падает с ошибкой о несуществующей колонке.

Значение в запросе заключено в двойные кавычки. В PostgreSQL это имя идентификатора, а не строка, и база ищет колонку с таким названием.

Запрос не выполняется после переезда.

В нём остались обратные кавычки вокруг имён таблиц. Их понимает только MySQL, и убрать их нужно во всём своём коде.

Данные читаются, но поля в массиве пустые.

Свой разбор результата выборки ждёт имена колонок в другом регистре. Штатное чтение строки результата приводит их к верхнему регистру само.

Создание индекса падает с конфликтом имени.

Такое имя индекса уже занято индексом другой таблицы. Имена индексов уникальны в пределах схемы, поэтому в них включают имя таблицы.

Склейка строк работает не везде.

В запросе используется функция конкретной базы данных. Помощник запросов собирает нужный вариант функции под текущую базу сам.

Частые вопросы

Нужно ли переписывать код на ORM?

Обычно нет: он собирает запросы средствами ядра и переносится как есть. Править приходится прямой SQL и код, завязанный на особенности MySQL.

Что делать со сторонними решениями?

Проверять по способу разработки: решения на ORM переносятся, решения с прямыми запросами - нет. Встроенный переход работает только с основным продуктом.

Можно ли вернуться обратно на MySQL?

После запуска - только вручную. Поэтому переход обязательно проверяют на отдельном контуре до перевода боевого сайта.

Почему нельзя менять данные во время переноса?

Копирование идёт по таблицам, и правки в этот момент теряются или дублируются. На время переноса сайт закрывают от изменений.

Как найти проблемные места заранее?

Поиском по своему коду: обратные кавычки, двойные кавычки в значениях, имена функций базы и прямые запросы через соединение.

Смежное

Первоисточник