Можно ли создать новую базу данных или таблицы PostgreSQL из 1С через Внешние Источники Данных и как выполнять произвольные SQL-скрипты?

← На главную

При работе с платформой 1С:Предприятие часто возникает задача взаимодействия со сторонними базами данных, такими как PostgreSQL. Для этих целей в 1С предусмотрен мощный механизм «Внешние Источники Данных». Однако, пользователи иногда сталкиваются с ограничениями при попытке выполнения определенных операций, например, создания новой базы данных или таблиц.

В этой статье мы подробно разберем, почему прямые DDL-команды (Data Definition Language), такие как CREATE DATABASE или CREATE TABLE, не могут быть выполнены через Внешние Источники Данных 1С. Мы также рассмотрим кроссплатформенные альтернативные подходы для выполнения произвольных SQL-скриптов из 1С, особенно в среде Linux, и выясним, как можно эффективно управлять структурой сторонних баз данных.

Почему команды DDL, такие как `CREATE DATABASE` или `CREATE TABLE`, не работают через Внешние Источники Данных в 1С

Давайте проанализируем основную проблему, с которой столкнулся автор сообщения: попытка выполнить команду CREATE DATABASE через объект внешнего источника данных в 1С, приводящая к синтаксической ошибке 42601. Причина этого кроется в фундаментальном предназначении механизма «Внешние Источники Данных» в платформе 1С:Предприятие.

Назначение механизма Внешних Источников Данных: Этот механизм изначально разработан для работы с уже существующими данными в сторонних базах данных. Он предоставляет абстракцию, позволяющую нам, как разработчикам, взаимодействовать с таблицами и представлениями внешних СУБД (PostgreSQL, MS SQL, Oracle и др.) с помощью встроенного языка запросов 1С. Его основная задача — выполнение операций манипуляции данными (DML – Data Manipulation Language):
- выборка данных (SELECT);
- вставка новых записей (INSERT);
- изменение существующих записей (UPDATE);
- удаление записей (DELETE).
Механизм Внешних Источников Данных не предназначен для выполнения команд определения данных (DDL – Data Definition Language), которые изменяют или создают саму структуру базы данных, такие как CREATE DATABASE, CREATE TABLE, ALTER TABLE, DROP TABLE и другие.
Контекст соединения: Когда мы устанавливаем соединение с внешним источником данных, мы уже подключаемся к конкретной, существующей базе данных. Все дальнейшие операции выполняются в контексте этой подключенной базы. Попытка создать новую базу данных в рамках уже установленного соединения с другой базой является логически некорректной. СУБД (в нашем случае PostgreSQL) просто не понимает, в каком контексте должна быть выполнена эта команда, и воспринимает ее как синтаксическую ошибку, так как она не ожидается в данном потоке операций или уровне привилегий. Именно поэтому мы получаем ошибку SQLSTATE: 42601, которая является общим сообщением PostgreSQL о синтаксической ошибке.
Отсутствие поддержки создания таблиц: Аналогично созданию баз данных, Внешние Источники Данных не позволяют напрямую создавать таблицы. Даже если бы команда CREATE TABLE была синтаксически принята СУБД, платформа 1С не смогла бы автоматически “подхватить” и добавить эту новую таблицу в дерево метаданных внешнего источника. Для использования новой таблицы ее необходимо было бы вручную описывать в конфигурации 1С как объект метаданных внешнего источника.

Таким образом, мы видим, что ограничение на выполнение DDL-команд является частью архитектуры механизма Внешних Источников Данных и не является ошибкой в SQL-синтаксисе самой команды, а скорее следствием неверного использования интерфейса.

Стандартные способы создания баз данных для 1С:Предприятие в PostgreSQL

Поскольку механизм Внешних Источников Данных не предназначен для создания самих баз данных, мы должны использовать стандартные инструменты и подходы, предусмотренные как платформой 1С, так и самой СУБД PostgreSQL.

Через консоль администрирования серверов 1С:
Когда мы разворачиваем новую информационную базу в клиент-серверном варианте с использованием PostgreSQL, платформа 1С предоставляет удобный инструмент для этого. В консоли администрирования серверов 1С, при создании или изменении информационной базы, мы можем указать параметры подключения к PostgreSQL (сервер, порт, имя пользователя, пароль). Здесь есть очень важная опция: «Создать базу данных в случае ее отсутствия». Если мы активируем этот флаг, сервер 1С:Предприятия сам выполнит необходимые команды для создания новой, пустой базы данных на указанном сервере PostgreSQL, которая будет готова для развертывания конфигурации. Это наиболее предпочтительный способ для создания информационных баз 1С.
С помощью утилит СУБД PostgreSQL:
Стандартной и очень гибкой практикой является предварительное создание базы данных администратором PostgreSQL с использованием встроенных инструментов СУБД. Это особенно актуально, когда требуется тонкая настройка параметров базы данных или когда создание баз данных является задачей системного администратора, а не разработчика 1С.
- Утилита командной строки createdb: Это простая и эффективная утилита, поставляемая с PostgreSQL. Мы можем выполнить ее из командной строки сервера, где установлен PostgreSQL. createdb -h localhost -U postgres test_db
  Здесь -h localhost указывает хост, -U postgres – пользователя с правами на создание баз данных, а test_db – имя новой базы данных.
- Клиент командной строки psql: Более мощный инструмент, позволяющий выполнять произвольные SQL-команды. Мы можем подключиться к любой существующей базе (например, postgres) и выполнить DDL-команду.
```
psql -h localhost -U postgres
-- Внутри psql
CREATE DATABASE test_db WITH OWNER = postgres ENCODING = 'UTF8' CONNECTION LIMIT = -1;
```
- Графический интерфейс pgAdmin: Для тех, кто предпочитает графические инструменты, pgAdmin (официальный клиент для администрирования PostgreSQL) предоставляет удобный интерфейс для создания и управления базами данных, ролями, таблицами и другими объектами СУБД.
После создания базы данных одним из этих способов, мы можем указать ее имя при регистрации новой информационной базы в кластере 1С, и платформа будет использовать уже готовую базу.

Кроссплатформенные подходы для выполнения произвольных SQL-скриптов из 1С

Итак, мы выяснили, что напрямую создавать базы данных или таблицы через Внешние Источники Данных в 1С невозможно. Однако, задача выполнения произвольных SQL-скриптов (включая DDL) из 1С в кроссплатформенной среде (например, Linux) остается актуальной. Рассмотрим наиболее эффективные методы.

Подход 1: Использование внешнего приложения (`ЗапуститьПриложение()`)

Наиболее универсальным, надежным и кроссплатформенным способом для выполнения произвольных SQL-скриптов является вызов консольного клиента СУБД из 1С с помощью функции ЗапуститьПриложение(). Этот подход позволяет нам полностью использовать функционал клиентских утилит СУБД, таких как psql для PostgreSQL.

Описание метода:
Функция ЗапуститьПриложение() позволяет запустить любую внешнюю программу или скрипт, доступный в операционной системе, из кода 1С. Мы можем передать этой программе необходимые аргументы, включая сам SQL-скрипт или путь к файлу с ним.

Для PostgreSQL клиент psql умеет принимать SQL-команды непосредственно в строке или читать их из файла.

Пример вызова psql из 1С для выполнения SQL-скрипта:

Предположим, у нас есть SQL-скрипт create_db_and_sp.sql, который создает базу данных и хранимые процедуры:


-- create_db_and_sp.sql
CREATE DATABASE new_test_db WITH OWNER = postgres ENCODING = 'UTF8' CONNECTION LIMIT = -1;
\c new_test_db; -- Подключаемся к новой базе для создания SP в ней
CREATE OR REPLACE PROCEDURE public.execute_arbitrary_sql(sql_command text)
LANGUAGE plpgsql
AS $$
BEGIN
    EXECUTE sql_command;
END;
$$;

Мы можем запустить этот скрипт из 1С следующим образом:


&НаСервере
Функция ВыполнитьSQLСкриптВнешнейУтилитой(ПутьКСкрипту, ИмяПользователяБД, ПарольБД, ХостБД, ПортБД)
            
    // Определяем путь к утилите psql. 
    // Это может быть "/usr/bin/psql" для Linux или "C:\Program Files\PostgreSQL\...\bin\psql.exe" для Windows.
    // Путь должен быть известен и доступен для сервера 1С.
    #Если ЛНУКС
        ИмяПриложения = "/usr/bin/psql";
    #Иначе
        ИмяПриложения = "C:\Program Files\PostgreSQL\14\bin\psql.exe"; // Пример
    #КонецЕсли
            
    // Формируем параметры для psql
    // -h <хост>          - сервер БД
    // -p <порт>          - порт БД
    // -U <пользователь>  - имя пользователя БД
    // -f <файл>          - путь к файлу со скриптом
    // -d postgres        - база данных, к которой подключаемся для выполнения DDL (создание БД)
    //                      Важно: DDL на создание БД не может выполняться в контексте создаваемой БД.
    Параметры = СтрШаблон("-h %1 -p %2 -U %3 -d postgres -f %4", ХостБД, ПортБД, ИмяПользователяБД, ПутьКСкрипту);
            
    // Устанавливаем переменную окружения для пароля, чтобы psql не запрашивал его интерактивно
    // Это безопасно, так как пароль не будет виден в аргументах процесса
    ПеременныеОкружения = Новый Соответствие();
    ПеременныеОкружения.Вставить("PGPASSWORD", ПарольБД);
            
    // Запускаем приложение
    РезультатЗапуска = ЗапуститьПриложение(ИмяПриложения, Параметры, , ПеременныеОкружения);
            
    Если РезультатЗапуска.КодВозврата = 0 Тогда
        Сообщить("SQL-скрипт успешно выполнен.");
        Возврат Истина;
    Иначе
        Сообщить("Ошибка при выполнении SQL-скрипта. Код возврата: " + РезультатЗапуска.КодВозврата);
        Сообщить("Вывод консоли: " + РезультатЗапуска.ВыводСтандартнойОшибки);
        Возврат Ложь;
    КонецЕсли;
КонецФункции

// Пример вызова:
// ПутьКФайлуСкрипта = "C:\temp\create_db_and_sp.sql"; // Или "/tmp/create_db_and_sp.sql" для Linux
// ИмяПользователяPostgres = "postgres";
// ПарольPostgres = "mysecretpassword";
// ХостPostgres = "localhost";
// ПортPostgres = "5432";
// ВыполнитьSQLСкриптВнешнейУтилитой(ПутьКФайлуСкрипта, ИмяПользователяPostgres, ПарольPostgres, ХостPostgres, ПортPostgres);

Преимущества этого подхода:
- Кроссплатформенность: Код 1С остается практически одинаковым для Windows и Linux. Изменяется только путь к исполняемому файлу psql и, возможно, синтаксис пути к скрипту.
- Независимость: Метод не зависит от ограничений драйверов ODBC или каких-либо внутренних ограничений платформы 1С на выполнение SQL-запросов. Мы используем нативный инструмент СУБД.
- Безопасность: Мы можем безопасно передавать учетные данные (логин, пароль) через переменные окружения (например, PGPASSWORD для PostgreSQL) или специальный файл паролей .pgpass. Это предотвращает отображение пароля в списке аргументов процесса, что является хорошей практикой безопасности.
- Гибкость: Мы можем выполнять абсолютно любые команды, которые поддерживает консольный клиент СУБД, включая сложные DDL-скрипты с условной логикой, транзакциями и т.д.

Подход 2: Выполнение произвольных SQL-скриптов через хранимые процедуры (решение автора)

Авторы сообщений в форуме нашли другой способ решения задачи, который также является достаточно гибким, но имеет свои нюансы. Их подход заключался в том, что сначала БД создается внешним SQL-скриптом (как мы рассмотрели в стандартных методах), а затем в этой новой БД создается специальная хранимая процедура, через которую можно отправлять произвольные SQL-скрипты. После этого из 1С можно вызывать эту хранимую процедуру, передавая ей нужный SQL-код.

Идея реализации:

На стороне PostgreSQL создается хранимая процедура (или функция), которая принимает на вход строковый параметр, содержащий SQL-команду, и выполняет ее.


CREATE OR REPLACE PROCEDURE public.execute_dynamic_sql(sql_command text)
LANGUAGE plpgsql
AS $$
BEGIN
    EXECUTE sql_command;
END;
$$;

После создания такой процедуры, мы можем вызвать ее из 1С через Внешний Источник Данных:


&НаСервере
Функция ВыполнитьДинамическийSQLЧерезХранимуюПроцедуру(ТекстSQL)
    Попытка
        ВнешнийИсточник = ВнешниеИсточникиДанных.ИмяВашегоВнешнегоИсточника;
        ВнешнийИсточник.ВызватьХранимуюПроцедуру("execute_dynamic_sql", ТекстSQL);
        Сообщить("Динамический SQL-скрипт успешно выполнен через хранимую процедуру.");
        Возврат Истина;
    Исключение
        Сообщить("Ошибка при выполнении динамического SQL-скрипта: " + ОписаниеОшибки());
        Возврат Ложь;
    КонецПопытки;
КонецФункции

// Пример вызова:
// ВыполнитьДинамическийSQLЧерезХранимуюПроцедуру("CREATE TABLE MyNewTable (ID SERIAL PRIMARY KEY, Name VARCHAR(255));");
// ВыполнитьДинамическийSQLЧерезХранимуюПроцедуру("INSERT INTO MyNewTable (Name) VALUES ('Тестовая запись');");

Преимущества и недостатки:
- Преимущества:
  - Позволяет выполнять DDL-команды внутри уже существующей базы данных через интерфейс внешних источников 1С (после первоначального создания БД).
  - Можно централизовать логику выполнения SQL на стороне СУБД.
- Недостатки:
  - Первоначальное создание БД и самой хранимой процедуры все равно требует внешнего инструмента (psql, pgAdmin или ЗапуститьПриложение() из 1С).
  - Проблемы безопасности: Такая хранимая процедура, которая позволяет выполнять *произвольный* SQL, представляет собой огромную уязвимость безопасности. Любой, кто сможет вызвать эту процедуру из 1С, получит полный контроль над базой данных. Использовать такой подход следует крайне осторожно и только в контролируемых средах с очень ограниченными правами доступа для пользователя 1С.
  - Обработка ошибок может быть сложнее, так как ошибки SQL будут возвращаться через механизм вызова хранимых процедур 1С.

Подход 3: Использование COM-объектов (ADO) (не для кроссплатформенных решений)

В одном из сообщений упоминался вариант использования COM-объектов (ADO). Этот подход действительно позволяет устанавливать прямое соединение с любой базой данных (включая SQL Server, PostgreSQL с соответствующим ODBC драйвером) и выполнять произвольные SQL-команды. Однако, для кроссплатформенных решений (особенно для Linux) этот метод не подходит, так как COM-технология является специфичной для операционных систем семейства Windows. Мы не будем останавливаться на нем подробно, поскольку автор запроса явно указал на необходимость кроссплатформенности и использования Linux.

Заключение

В ходе нашего анализа мы подтвердили, что механизм «Внешние Источники Данных» в 1С:Предприятие предназначен исключительно для работы с уже существующими данными (DML-операции) и не поддерживает прямое выполнение DDL-команд, таких как CREATE DATABASE или CREATE TABLE. Попытки выполнения таких команд приведут к ошибкам синтаксиса, таким как SQLSTATE: 42601.

Для создания баз данных 1С на PostgreSQL мы должны использовать стандартные инструменты: либо консоль администрирования серверов 1С с опцией автоматического создания БД, либо утилиты СУБД (createdb, psql, pgAdmin).

Когда же возникает необходимость выполнения произвольных SQL-скриптов (включая DDL) из 1С в кроссплатформенной среде, наиболее рекомендованными и надежными подходами являются:

Использование функции ЗапуститьПриложение() для вызова консольных утилит СУБД (например, psql). Этот метод обеспечивает максимальную гибкость, кроссплатформенность и безопасность.
В некоторых случаях, можно использовать хранимые процедуры на стороне СУБД для выполнения динамического SQL, которые затем вызываются из 1С. Однако, этот подход требует очень тщательного рассмотрения вопросов безопасности.

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