Как писать код, совместимый с PostgreSQL

Перед миграцией на PostgreSQL проверьте сторонние модули и доработки проекта. Найдите прямые SQL-запросы, замените специфичные функции MySQL и используйте совместимые методы Bitrix Framework.

Встроенный мигратор предназначен только для основного продукта. Он не работает со сторонними решениями.

Если сторонний модуль не поддерживает PostgreSQL, мастер миграции отключит его автоматически.

Миграция сторонних модулей

Модули из Битрикс24.Маркет работают через REST и не обращаются напрямую к базе данных.

Работоспособность модулей из Маркетплейс 1С-Битрикс зависит от способа разработки модуля.

Если модуль на ядре D7 и использует Bitrix ORM, он работает без доработок.
Если модуль не на ядре D7, он требует отдельной проверки совместимости с PostgreSQL.
Если модуль на ядре D7 использует прямые запросы к СУБД или специфичные функции, он может потребовать доработки. За изменениями в коде обратитесь к разработчикам модуля.

Как адаптировать модуль

Типы колонок

Замените типы колонок, которые могут работать по-разному в MySQL и PostgreSQL:

timestamp — на datetime с переносом логики на PHP;
enum — на char, чтобы PostgreSQL не создавал отдельный тип для колонки;
char — на varchar, если в колонке хранится строка переменной длины. PostgreSQL дополняет значение char пробелами справа до указанной длины поля.

В PostgreSQL нет модификатора unsigned, поэтому для него увеличивается разрядность хранения.

MySQL	PostgreSQL
smallint	smallint
unsigned smallint	int
int	int
unsigned int	int8

Чаще всего unsigned используют не для ограничения хранения только положительных чисел, а как способ увеличить диапазон хранимых данных, например для результата ip2long. Чтобы упростить поддержку кода в разных базах, откажитесь от unsigned в MySQL с возможным увеличением разрядности хранения.

Имена индексов

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

CREATE TABLE author (id int, name varchar(50), primary key (id), key ix_search(name));
        CREATE TABLE book (id int, title varchar(50), primary key (id), key ix_search(title));

В PostgreSQL имена индексов должны быть уникальны в рамках схемы данных. Составляйте имена индексов:

из префикса: ux_ для уникальных, tx_ для полнотекстовых и ix_ для остальных,
имени таблицы и разделителя _,
имен столбцов, разделенных _.

Максимальная длина имени индекса в PostgreSQL — 63 символа. Если имя индекса длиннее, обрежьте его. Если имя будет конфликтовать с другим индексом, добавьте числовой суффикс через _.

Конвертация install.sql

$cd mymodule
        $mkdir install/db/pgsql
        $cp install/mysql/uninstall.sql install/pgsql/uninstall.sql
        $php -f ../perfmon/tools/mysql_to_pgsql.php — install/mysql/install.sql > install/pgsql/install.sql

Использование специфичного квотирования

Использование обратных кавычек для экранирования идентификаторов в MySQL не подходит для PostgreSQL.

Такие конструкции нужно переписать с использованием методов CDatabase::quote или SqlHelper::quote либо убрать как ненужные или избыточные. Названия таблиц всегда в нижнем регистре, а названия столбцов — в верхнем.

Строковые литералы

В отличие от MySQL, в PostgreSQL нельзя использовать двойные кавычки для строковых литералов. Замените их на одиночные кавычки.

MySQL и PostgreSQL по-разному интерпретируют обратные косые черты в строковых литералах.

Например, запрос:

select '\Bitrix'

В MySQL запрос вернет "Bitrix", а в PostgreSQL — "\Bitrix".

А запрос:

select '\\Bitrix'

В MySQL запрос вернет "\Bitrix", а в PostgreSQL — "\\Bitrix".

Чтобы избежать различий, используйте функцию ForSql из SqlHelper или CDatabase:

$rs = $DB->Query("SELECT '" . $DB->ForSql("\\Bitrix") . "'")

Для обеих баз данных запрос вернет "\Bitrix".

Функции

Замените функции MySQL на стандартные SQL-конструкции или методы Bitrix Framework. Если функция используется в прямом SQL-запросе, перепишите запрос перед миграцией.

ifnull()

Замените ifnull() на coalesce().

Было:

SELECT ID,ifnull(NAME, '') AS NAME FROM b_user

Стало:

SELECT ID,coalesce(NAME, '') AS NAME FROM b_user

mid()

Замените mid() на substr().

if()

Замените if() на оператор case when.

Было:

SELECT ID, if(TIMESTAMP_X > now() , 'red', 'green') AS STATUS FROM b_user

Стало:

SELECT ID, case when TIMESTAMP_X > now() then 'red' else 'green' end AS STATUS FROM b_user

YEAR(), MONTH(), DAY()

Замените YEAR(), MONTH() и DAY() на extract(... from ...).

Было:

SELECT ID, YEAR(TIMESTAMP_X) AS A_YEAR FROM b_user

Стало:

SELECT ID, extract(YEAR FROM TIMESTAMP_X) AS A_YEAR FROM b_user

LOCATE()

Замените LOCATE() на position(... in ...).

Было:

SELECT ID, LOCATE(',',NAME) AS A_POS FROM b_user

Стало:

SELECT ID, POSITION(',' IN NAME) AS A_POS FROM b_user

get_lock() и release_lock()

Перепишите прямые запросы на методы DatabaseConnection.

$lockName = 'mylock';
        $connection = \Bitrix\Main\Application::getConnection();
        if ($connection->lock($lockName))
        {
            // ...
            $connection->unlock($lockName);
        }

date_add() и date_sub()

Перепишите прямые запросы на методы SqlHelper.

Было:

$sql = 'SELECT ID, date_add(TIMESTAMP_X, interval 60 second) AS EXPIRATION_TIME FROM b_user';

Стало:

$connection = \Bitrix\Main\Application::getConnection();
        $helper = $connection->getSqlHelper();
        
        $sql = 'SELECT ID, ' . $helper->addSecondsToDateTime(60, 'TIMESTAMP_X') . ' AS EXPIRATION_TIME FROM b_user';

Для date_sub вызовите с отрицательным значением.

Для date_add(..., interval day) используйте метод addDaysToDateTime.

date_format()

Перепишите с использованием метода formatDate из \Bitrix\Main\Application::getConnection()->getSqlHelper().

concat()

Перепишите с использованием метода getConcatFunction из \Bitrix\Main\Application::getConnection()->getSqlHelper().

date()

Перепишите с использованием метода getCurrentDateFunction из \Bitrix\Main\Application::getConnection()->getSqlHelper().

rand()

Перепишите с использованием метода getRandomFunction из \Bitrix\Main\Application::getConnection()->getSqlHelper().

sha1()

Перепишите с использованием метода getSha1Function из \Bitrix\Main\Application::getConnection()->getSqlHelper().

group_concat()

Не используйте эту функцию, так как она может вернуть обрезанные данные. Подробности описаны в документации MySQL для функции group_concat.

Запросы

INSERT IGNORE

Перепишите прямые запросы на методы SqlHelper.

$sql = 'INSERT IGNORE INTO b_user_group (USER_ID, GROUP_ID, DATE_ACTIVE_FROM, DATE_ACTIVE_TO) VALUES (...)';
        $sql = 'INSERT IGNORE INTO b_user_access_check (USER_ID, PROVIDER_ID) SELECT ...';

Замените на:

$connection = \Bitrix\Main\Application::getConnection();
        $helper = $connection->getSqlHelper();
        
        $sql = $helper->getInsertIgnore("b_user_group", "(USER_ID, GROUP_ID, DATE_ACTIVE_FROM, DATE_ACTIVE_TO)", "VALUES (...)");
        $sql = $helper->getInsertIgnore("b_user_access_check", "(USER_ID, PROVIDER_ID)", "SELECT ...");

Различия в CDatabase::PrepareInsert и SqlHelper::prepareInsert

UPDATE IGNORE

В PostgreSQL нет запроса с аналогичной логикой выполнения.

Обновление значений первичного или альтернативного ключа лучше заменить двумя запросами. Такая замена не полностью соответствует исходной логике, но обе БД будут вести себя одинаково.

INSERT IGNORE INTO ... SELECT ... WHERE
        DELETE FROM ... WHERE

REPLACE INTO

Перепишите прямые запросы на методы SqlHelper.

$sql = 'REPLACE INTO b_module_table (KEY_ID, DATA) VALUES (...)';

Измените на:

$connection = \Bitrix\Main\Application::getConnection();
        $helper = $connection->getSqlHelper();
        
        $update = [
            'KEY_ID' => 1,
            'DATA' => 'a',
        ];
        $merge = $helper->prepareMerge('b_module_table', ['KEY_ID'], $update, $update);
        if ($merge[0])
        {
            $connection->query($merge[0]);
        }
        //or another helper method
        //$update - is a row in a rows array
        foreach ($helper->prepareMergeMultiple('b_module_table', ['KEY_ID'], [$update]) as $sql)
        {
            $connection->query($sql);
        }

Различия в CDatabase::PrepareInsert и SqlHelper::prepareInsert

DELETE ... LIMIT

Перепишите прямые запросы на методы SqlHelper:

$sql = 'DELETE FROM b_test WHERE ACTIVE = 'N' ORDER BY TIMESTAMP_X ASC LIMIT 50';

Измените на:

$connection = \Bitrix\Main\Application::getConnection();
        $helper = $connection->getSqlHelper();
        
        $sql = $helper->prepareDeleteLimit('b_test', ['ID'], "ACTIVE = 'N'", ['TIMESTAMP_X' => 'ASC'], 50);

Коррелированный UPDATE.
Коррелированный DELETE.

Эти запросы зависят от конкретной логики выборки и обновления данных. Перепишите их перед миграцией.

Использование классов

MysqlCommonConnection
MssqlConnection
OracleConnection

Проверьте код, который использует эти классы.

Некоторые особенности

CDatabase::Add в таблицу без автоинкремента:

$DB->Add("b_iblock_fields", $arAdd, array("DEFAULT_VALUE"));

Перепишите на прямой запрос:

$arInsert = $DB->PrepareInsert("b_iblock_fields", $arAdd);
        $DB->Query("INSERT INTO b_iblock_fields (".$arInsert[0].") VALUES (".$arInsert[1].")");

Различия в CDatabase::PrepareInsert и SqlHelper::prepareInsert

Поля типа дата/время обрабатываются по-разному.
CDatabase учитывает настройку часовых поясов, а SqlHelper — нет.

Поэтому в методы хелпера передавайте объект даты и времени, созданный методом \Bitrix\Main\Type\DateTime::createFromUserTime. Например:

$fields = [
            'DATE_REGISTER' => '01.01.2023 00:00:00',
        ];
        print_r($DB->PrepareInsert('b_user', $fields));
        
        $fields = [
            'DATE_REGISTER' => new \Bitrix\Main\Type\DateTime('01.01.2023 00:00:00'),
        ];
        print_r(\Bitrix\Main\Application::getConnection()->getSqlHelper()->prepareInsert('b_user', $fields));
        
        $fields = [
            'DATE_REGISTER' => \Bitrix\Main\Type\DateTime::createFromUserTime('01.01.2023 00:00:00'),
        ];
        print_r(\Bitrix\Main\Application::getConnection()->getSqlHelper()->prepareInsert('b_user', $fields));

Выведет

Array
        (
            [0] => `DATE_REGISTER`
            [1] => DATE_ADD('2023-01-01 00:00:00', INTERVAL 3600 SECOND)
        )
        Array
        (
            [0] => `DATE_REGISTER`
            [1] => '2023-01-01 00:00:00'
            [2] => Array
                (
                )
        )
        Array
        (
            [0] => `DATE_REGISTER`
            [1] => '2023-01-01 01:00:00'
            [2] => Array
                (
                )

Использование двойных кавычек

В PostgreSQL нельзя использовать двойные кавычки для строковых литералов. Заменяйте их на одиночные кавычки.

Была ли статья полезна?

Поддержка PostgreSQL в модулях

Установка PostgreSQL на macOS

Как писать код, совместимый с PostgreSQL

Миграция сторонних модулейМиграция сторонних модулей

Как адаптировать модульКак адаптировать модуль

Схема данныхСхема данных

Типы колонокТипы колонок

Имена индексовИмена индексов

Конвертация install.sqlКонвертация install.sql

Использование специфичного квотированияИспользование специфичного квотирования

Строковые литералыСтроковые литералы

ФункцииФункции

ifnull()ifnull()

mid()mid()

if()if()

YEAR(), MONTH(), DAY()YEAR(), MONTH(), DAY()

LOCATE()LOCATE()

get_lock() и release_lock()get_lock() и release_lock()

date_add() и date_sub()date_add() и date_sub()

date_format()date_format()

concat()concat()

date()date()

rand()rand()

sha1()sha1()

group_concat()group_concat()

ЗапросыЗапросы

Использование классовИспользование классов

Некоторые особенностиНекоторые особенности

Различия в CDatabase::PrepareInsert и SqlHelper::prepareInsertРазличия в CDatabase::PrepareInsert и SqlHelper::prepareInsert

Использование двойных кавычекИспользование двойных кавычек

Была ли статья полезна?

Миграция сторонних модулей

Как адаптировать модуль

Схема данных

Типы колонок

Имена индексов

Конвертация install.sql

Использование специфичного квотирования

Строковые литералы

Функции

ifnull()

mid()

if()

YEAR(), MONTH(), DAY()

LOCATE()

get_lock() и release_lock()

date_add() и date_sub()

date_format()

concat()

date()

rand()

sha1()

group_concat()

Запросы

Использование классов

Некоторые особенности

Различия в CDatabase::PrepareInsert и SqlHelper::prepareInsert

Использование двойных кавычек