Процедуры и функции - rpc

XDAC позволяет вызывать процедуры и функции из источников данных следующих типов БД:

postgresql

oracle

mssql

firebird

Примечание

Для ydb данный функционал не поддерживается в виду того, что ydb не использует процедуры и функции.

Сканирование процедур и функций

При старте XDAC устанавливает соединение с базой данных в соответствии с параметрами, указанными в конфигурационном файле config.json.

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

В кэш записываются только процедуры и функции удовлетворяющие следующим условиям:

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

Вызов процедур и функций

Каждая процедура и функция, полученная в результате сканирования схемы (пакета), публикуется сервисом с префиксом /xdac/[datasource_name]/rpc/, где datasource_name - имя источника данных в config.json

http://localhost:8887/xdac/pg_db/rpc/function_name

Примечание

Так как регистрация эндпоинтов по именам источников данных происходит при старте XDAC, то при запросе url с некорректным именем источника данных будет возвращен код ошибки - 404.

В общем случае, для вызова процедуры или функции могут быть использованы GET и POST - запросы. При использовании GET-запроса, передаваемые параметры указываются в URL-адресе с использованием строки запроса:

curl "http://localhost:8887/xdac/pg_db/rpc/function_name?first_param=value&second_param=value"

При использовании POST-запроса, передаваемые параметры необходимо включить в тело запроса json, наименование полей которого будет соответствовать наименованию аргументов, либо передавать как данные формы (multipart/form-data):

Параметры в теле json:

curl -d '{ "first_param": value, "second_param": value  }' http://localhost:8887/xdac/pg_db/rpc/function_name

Параметры как данные формы:

curl --form first_param=value --form second_param=value localhost:8887/xdac/pg_db/rpc/function_name

Для примера создадим для базы данных PostgreSQL функцию для сложения двух чисел следующего вида:

CREATE OR REPLACE FUNCTION api.add_numbers (
    first integer,
    second integer
)
RETURNS integer AS
$body$
BEGIN
    return first + second;
END;
$body$
LANGUAGE 'plpgsql'

Пример вызова функции add_numbers с помощью GET-запроса:

curl "http://localhost:8887/xdac/pg_db/rpc/add_numbers?first=20&second=30"

Ответ: 50

Пример вызова функции add_numbers с помощью POST-запроса с параметрами в теле json:

curl -d '{ "second": 5, "first": 6  }' http://localhost:8887/xdac/pg_db/rpc/add_numbers

Ответ: 11

Пример вызова функции add_numbers с помощью POST-запроса с параметрами multipart/form-data:

curl --form first=12 --form second=34 http://localhost:8887/xdac/pg_db/rpc/add_numbers

Ответ: 46

В случае некорректного имени параметра, отсутствия параметра или передачи лишнего параметра в ответ будет получено сообщение об ошибке в формате json. Например:

curl -d '{ "second1": 5, "first": 6  }' http://localhost:8887/xdac/pg_db/rpc/add_numbers

Ответ:

{
  "code": "XDAC005",
  "hint": "Make sure that routine name is correct or try to reload application",
  "message": "Routine add_numbers(first,second1) doesn't exists in app cache",
  "details": ""
}

Если у параметров процедуры или функции есть значения по умолчанию, то данные параметры будут считаться необязательными и они могут отсутствовать в запросе. Например, если наша функция add_numbers имеет следующий формат:

create function add_numbers(first integer DEFAULT 1, second integer DEFAULT 2)

в этом случае функцию можно вызвать без параметров:

curl http://localhost:8887/xdac/pg_db/rpc/add_numbers

Ответ: 3

Примечание

Каждый вызов процедуры или функции осуществляется в своей транзакции. Наличие оператора COMMIT в теле функции приведет к ошибке.

Правила наименования

Для баз данных PostgeSQL, MS SQL Server все наименования (имена процедур и функций, наименования аргументов) - регистрозависимые.

Для вызова функции с именем StRaNgeNamE необходимо вызвать метод следующим образом:

curl "http://localhost:8887/xdac/pg_db/rpc/StRaNgeNamE"

Вызов в другом формате приведет к ошибке, что такая функция не найдена:

{
  "code": "XDAC001",
  "hint": "Make sure that Routine Name is correct or try to reload application",
  "message": "Routine strangename was not found in app cache",
  "details": ""
}

Для баз данных Oracle и Firebird все наименования будут автоматически приведены к верхнему регистру.

Передача JSON в качестве параметра

Если в качестве параметра функции или процедуры требуется передать json, то в запросе необходимо передать заголовки Content-Type: application-json и Prefer:params=single-object:

curl -H "Content-Type: application/json" -H "Prefer: params=single-object" -d '{"text":"Test message","input":[1,2,3]}' "http://localhost:8887/xdac/pg_db/rpc/jsonInput"

Сама процедура или функция должна иметь только один аргумент с типом данных подходящим для обработки json. Например для PostgreSQL:

CREATE OR REPLACE FUNCTION api."jsonInput" (
    "myPar" json
)
RETURNS json AS
$body$
begin
return "myPar";
END;
$body$
LANGUAGE 'plpgsql'

Передача бинарного файла в качестве параметра

Если в качестве параметра функции или процедуры требуется бинарный файл, то в запросе необходимо передать заголовок Content-Type: application/octet-stream:

curl "http://localhost:8887/xdac/pg_db/rpc/upload_binary" \
    -X POST -H "Content-Type: application/octet-stream" \
    --data-binary "@file_name.ext"

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

Например для PostgreSQL:

CREATE OR REPLACE PROCEDURE api."upload_binary" (
    bytea
)
AS
$body$
BEGIN
    insert into api.files(blob)
    values ($1);
END;
$body$
LANGUAGE 'plpgsql'

Также вы можете передать бинарный файл с помощью multipart/form-data запроса. XDAC произведет поиск процедуры или функции в соответствии с наименованием передаваемых параметров, за исключением параметров, которые содержат файл. Все файлы которые передаются в запросе будут размещены в «служебную» xdac_files.

Данная таблица содержит следующие поля:

name - имя параметра в запросе
file_name - имя файла
file_mime - MIME-тип файла
file_content - содержимое файла

В зависимости от типа базы данных таблица xdac_files будет создана разными способами:

PostgreSQL – временная таблица pgtemp.xdac_files

CREATE TEMP TABLE IF NOT EXISTS pg_temp.xdac_files

Oracle – глобальная временная таблица XDAC_FILES

CREATE GLOBAL TEMPORARY TABLE XDAC_FILES

MS SQL Server – временная таблица xdac_files

CREATE TABLE #xdac_files

Firebird – глобальная временная таблица XDAC_FILES

CREATE GLOBAL TEMPORARY TABLE XDAC_FILES

Примечание

После завершения текущего запроса (транзакции) информация о загруженных файлах будет удалена.

Пример работы с файлами для базы данных PostgreSQL

Создадим функцию save_document в схеме api:

CREATE OR REPLACE FUNCTION api.save_document (
  document varchar,
  document_date varchar
)
RETURNS jsonb LANGUAGE 'plpgsql'
AS
$body$
declare
  l_rec record;
BEGIN
  select f.file_name, f.file_mime, length(f.file_content) file_length
  into l_rec
  from pg_temp.xdac_files f
  where f.name = 'document_scan';

  return jsonb_build_object(
          'document',"document",
          'document_date',"document_date",
          'file_name',l_rec.file_name,
          'file_mime',l_rec.file_mime,
          'file_length',l_rec.file_length
    );
END;
$body$;

Выполним запрос к серверу приложений, который передает файл my_image.svg:

curl --form document=passport --form document_date=10.01.2002 --form document_scan='@my_image.svg' localhost:8887/xdac/pg_db/rpc/save_document

Ответ:

{
  "document": "passport",
  "document_date": "10.01.2002",
  "file_length": 9710,
  "file_mime": "image/svg+xml",
  "file_name": "my_image.svg"
}

Работа с заголовками

Все заголовки передаваемые в сервис записываются в параметры сессии.

Следующие параметры доступны для доступа:

request.headers – заголовки запроса
request.method – метод запроса
request.path – путь по которому был вызван сервис

Для того чтобы установить заголовки ответа и код ответа используются параметры response.headers и response.status.

Заголовок Content-Type XDAC проставляет самостоятельно. Любое значение переданное в этот заголовок будет перезаписано.

Хранение данных параметров реализовано в зависимости от базы данных:

PostgreSQL – параметры записываются в Grand Unified Configuration (GUC), таким образом работа с параметрами осуществляется посредством вызовов set_config и current_settings.

Например:

-- Получить все заголовки. Они преобразуются в json
SELECT current_setting('request.headers', true)::json;

-- Метод с помощью которого был вызван сервис
SELECT current_setting('request.method', true); --GET/POST

-- Путь по которому был вызван сервис
SELECT current_setting('request.path', true); --/xdac/pg_db/rpc/test

— Чтобы установить заголовки ответа, необходимо передать массив объектов json
SELECT set_config('response.headers','[{"Content-Type":"application/json"},{"Set-Cookie":"foo=bar"}]',true);

-- устанавливает код ответа в 405
SELECT set_config('response.status','405',true);

Oracle – параметры записываются в пакет XDAC_VARIABLES, для записи используется процедура SET_CONFIG, для чтения используется функция GET_CONFIG, которые работают с таблицей CONFIG_TABLE:

CREATE OR REPLACE PACKAGE XDAC_VARIABLES AS
TYPE config_record IS RECORD (
    key_name CLOB,
    value_name CLOB
);
TYPE config_table IS TABLE OF config_record INDEX BY PLS_INTEGER;
g_config config_table;
PROCEDURE SET_CONFIG(p_key CLOB, p_value CLOB);
FUNCTION GET_CONFIG(p_key CLOB) RETURN CLOB;
END XDAC_VARIABLES

MS SQL Server – параметры записываются в контекст сессии через sp_set_session_context, получить значения можно посредством SELECT SESSION_CONTEXT
Firebird – параметры записываются в контекст USER_TRANSACTION пользовательской сессии с помощью rdb$set_context, для чтения используется rdb$get_context.
Например:
SELECT rdb$set_context('USER_TRANSACTION', 'request.headers', '') FROM rdb$database SELECT rdb$get_context('USER_TRANSACTION', 'request.headers'') FROM rdb$database

Обработка ошибок

В случае возникновения ошибки сервис вернёт её в формате:

{
  "code": "",
  "hint": "",
  "message": "",
  "details": ""
}

Для базы данных PostgreSQL сервис предоставляет возможность обрабатывать пользовательские ошибки вызванные оператором raise

raise 'This is postgresql error'
      USING ERRCODE = 'SRV01',
      HINT = 'Try GET method',
      DETAIL = 'This is detail';

XDAC преобразует ошибку в следующий вид:

{
  "code": "SRV01",
  "hint": "Try GET method",
  "message": "This is postgresql error",
  "details": "This is detail"
}

При этом транзакция будет завершена, а все изменения отменены с помощью оператора ROLLBACK. Если необходимо передать пользовательскую ошибку в сервис, при этом сохранить результаты выполнения функции, можно использовать установку статуса ответа через GUC.

perform set_config('response.status','405',true);
perform set_config('response.headers','[{"Content-Type":"application/json"}]',true);
return json_build_object('message','Only GET method is allowed');

Обновление кэша процедур и функций

Все процедуры и функции в схеме или пакете сканируются при запуске приложения и помещаются в кэш. При изменении сигнатуры процедур или создании новых функций они не будут доступны для вызова через API. Для того, чтобы стали доступны обновленные процедуры или функции необходимо обновить кэш. Этого можно достигнуть следующим образом:

Перезагрузить приложение
Задать в config.json параметр reloadCacheInterval в секундах, отвечающий за интервал времени для обновления кэша
Обратиться к endpoint RELOAD для принудительного обновления кэша:

curl http://localhost:8887/reload

Также для базы данных PostgreSQL доступен механизм обновления кэша с помощью уведомлений NOTIFY. Для этого необходимо отправить в канал xdac уведомление о перезагрузке кэша:

NOTIFY xdac, 'reload';

Чтобы автоматизировать отправку уведомлений в канал можно создать event trigger на операции ddl. Пользователь с правами SUPERUSER может создать следующий триггер:

CREATE OR REPLACE FUNCTION public.xdac_drop_watch () RETURNS event_trigger
LANGUAGE plpgsql
AS
$body$
DECLARE
  obj record;
BEGIN
 FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
  LOOP
   IF obj.object_type in ('function','procedure') AND not obj.is_temporary then
     NOTIFY xdac, 'reload';
   end if;
 END LOOP;
END;
$body$;



CREATE OR REPLACE FUNCTION public.xdac_ddl_watch () RETURNS event_trigger
LANGUAGE plpgsql
$body$
DECLARE
  cmd record;
BEGIN
 FOR cmd IN SELECT * FROM pg_event_trigger_ddl_commands()
 LOOP
    IF cmd.command_tag IN ('CREATE FUNCTION', 'ALTER FUNCTION','CREATE PROCEDURE','ALTER PROCEDURE')
      and cmd.schema_name != 'pg_temp'
   then
     NOTIFY xdac, 'reload';
   end if;
 END LOOP;
END;
$body$;


CREATE EVENT TRIGGER xdac_ddl_watch  ON ddl_command_end  EXECUTE PROCEDURE public.xdac_ddl_watch();
CREATE EVENT TRIGGER xdac_drop_watch ON sql_drop  EXECUTE PROCEDURE public.xdac_drop_watch();