Всем привет! Сегодня мы расскажем о полезной возможности СУБД Ред База Данных - создании внешних подпрограмм, то есть процедур, функций и триггеров на языке Java. Например, язык PSQL не позволяет работать с объектами файловой системы или сети, а Java запросто решает такие задачи и существенно расширяет возможности встроенного языка.

Настройка Java

СУБД Ред База Данных для работы с jar-файлами использует движок FBJava, который позволяет загружать и запускать подпрограммы на платформе Java не ниже версии 1.8.

Давайте установим более новую версию - 11. 

sudo dnf install java-11-openjdk-devel

Теперь настроим параметры взаимодействия сервера СУБД Ред База Данных с виртуальной машиной Java с помощью конфигурационного файла plugins.conf, следующим образом: 

1. Раскомментируем секции:

   1. Plugin=JAVA 

   2. и Config=JAVA_config  в /opt/RedDatabase. 

2. А также установим путь к Java в JavaHome.

   1. JavaHome = /usr/lib/jvm/jre-11

Далее в файле конфигурации fbjava.yaml зададим путь к каталогу, где будут располагаться jar-файлы с методами, реализующие внешние подпрограммы. Можно указать для всех баз данных одновременно:

classpath:
- $(root)/jar/data/*.jar

Или для каждой отдельно:

databases:
".*/employee.fdb":
classpath:
- /home/rdb/jars/*.jar # путь до jar-файлов

Объявление внешних подпрограмм

Для начала рассмотрим общие правила объявления внешних подпрограмм, реализованных с помощью Java-методов. 

Для всех внешних объектов общим является обязательное указание места расположения Java-метода во внешнем модуле с помощью предложения EXTERNAL NAME.

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

EXTERNAL NAME '<полное имя класса>.<имя static метода>!(
[<Java тип> [, <Java тип>...]])'
[!<определяемая пользователем информация>]
ENGINE JAVA

Синтаксис операторов создания, изменения и пересоздания внешних подпрограмм, написанных на Java:

Создание/изменение/пересоздание триггера:

{CREATE [ OR ALTER ] | RECREATE | ALTER} TRIGGER <имя триггера> {
<объявление табличного триггера>
| <объявление табличного триггера в стандарте SQL-2003>
| <объявление триггера базы данных>
| <объявление DDL триггера> }
EXTERNAL NAME '<полное имя класса>.<имя static метода>!(
[<Java тип> [, <Java тип>...]])'
[!<определяемая пользователем информация>]
ENGINE JAVA

Создание/изменение/пересоздание процедуры:

{CREATE [ OR ALTER ] | RECREATE | ALTER} PROCEDURE <имя хранимой процедуры>
[(<входной параметр> [, <входной параметр> ...])]
[RETURNS (<выходной параметр> [, <выходной параметр> ...])]
EXTERNAL NAME '<полное имя класса>.<имя static метода>!(
[<Java тип> [, <Java тип>...]])'
[!<определяемая пользователем информация>]
ENGINE JAVA

Создание/изменение/пересоздание функции:

{CREATE [ OR ALTER ] | RECREATE | ALTER} FUNCTION <имя функции>
[(<входной параметр> [, <входной параметр> ...])]
RETURNS (<тип данных>)
EXTERNAL NAME '<полное имя класса>.<имя static метода>!(
[<Java тип> [, <Java тип>...]])'
[!<определяемая пользователем информация>]
ENGINE JAVA

Существует два основных способа сопоставить функции и процедуры базы данных с методами Java. Это фиксированные и обобщенные сигнатуры.

Фиксированные сигнатуры означают, что для каждого параметра программы (функции, процедуры) базы данных должен быть параметр в Java-методе.

А вот обобщенные сигнатуры не имеют параметров. Java-код с помощью интерфейса контекстов может получить все параметры или значения полей, переданные программой базы данных.

Триггеры могут отображаться только с помощью обобщенных сигнатур.

Соответствие типов Java типам SQL

Интерфейсы доступа к контексту подпрограмм

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

Пример работы с внешними хранимыми процедурами, функциями и триггерами

Напишем несколько внешних подпрограмм на языке Java в среде разработки  IntelliJ IDEA.

Создаем новый проект. Имя и расположение проекта выбираем любое, язык - Java, сборочная система - Maven, JDK - 11 версии. В Advanced Setting укажем группу (GroupId) - ru.reddatabase.external и ArtifactId - ExternalJava. Нажимаем создать проект.

Добавим в файл конфигурации pom.xml библиотеку, в которой указаны интерфейсы для доступа к контекстам подпрограмм. 

<dependencies>
    <dependency>
        <groupId>ru.reddatabase</groupId>
        <artifactId>fbjava</artifactId>
        <version>1.1.18</version>
        <scope>system</scope>
        <systemPath>/opt/RedDatabase/jar/fbjava-1.1.18.jar</systemPath>
   </dependency>
</dependencies>

После этого обновим проект.

Работа с триггерами

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

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

public static void saveContextTrigger() throws SQLException {

   TriggerContext context = TriggerContext.get();
   String action = context.getAction().toString();
   ValuesMetadata fieldsM = context.getFieldsMetadata();
   Values newValues = context.getNewValues();
   Values oldValues = context.getOldValues();
   String tableName = context.getTableName();
   String type = context.getType().toString();

   String nameInfo = context.getNameInfo();
   String objectName = context.getObjectName();

   String info = "";
   info = info + "Действие: " + action + "\n";
   info = info + "Таблица: " + tableName + "\n";
   info = info + "Тип: " + type + "\n";
   info = info + "Сохраненные метаданные: " + nameInfo + "\n";
   info = info + "Имя объекта: " + objectName + "\n";

   if (fieldsM != null) {
       int count = fieldsM.getParameterCount();
       for (int i = 1; i <= count - 1; ++i) {

           info = info + "Имя поля: " + fieldsM.getName(i) + "\n";
           info = info + "Класс поля: " + fieldsM.getJavaClass(i).toString() + "\n";
           info = info + "Старое значение: " + (oldValues == null || oldValues.getObject(i) == null ? null : oldValues.getObject(i).toString()) + "\n";
           info = info + "Новое значение: " + (newValues == null || newValues.getObject(i) == null ? null : newValues.getObject(i).toString()) + "\n";

           if (newValues != null) {
               Object obj = newValues.getObject(i);
               if (obj == null)
                   newValues.setObject(i, null);
               else if (obj instanceof java.math.BigDecimal) {
                   BigDecimal val = (BigDecimal) obj;
                   newValues.setObject(i, new BigDecimal(val.intValue() * -1));
               }
               else if (obj instanceof java.lang.Double)
                   newValues.setObject(i, (Double) obj * -2.0);
               else if (obj instanceof java.lang.Float)
                   newValues.setObject(i, (Float) obj * -3.0);
               else if (obj instanceof java.lang.Boolean)
                   newValues.setObject(i, !((Boolean) obj));
               else if (obj instanceof java.sql.Date)
                   newValues.setObject(i, java.sql.Date.valueOf(LocalDateTime.now().toLocalDate()));
               else if (obj instanceof java.sql.Time)
                   newValues.setObject(i, java.sql.Time.valueOf(LocalDateTime.now().toLocalTime()));
               else if (obj instanceof java.sql.Timestamp)
                   newValues.setObject(i, java.sql.Timestamp.valueOf(LocalDateTime.now()));
               else
                   newValues.setObject(i, obj.toString() + ". Длина строки: " + obj.toString().length());
               info = info + "Новое значение установленное внешним триггером: "
                       + (newValues.getObject(i) == null ? null : newValues.getObject(i).toString()) + "\n";
           }
           if (newValues != null)
               newValues.setObject(count, info);
       }
   }
}

В этом примере мы получаем контекст триггера через интерфейс TriggerContext:

        TriggerContext context = TriggerContext.get();

Информацию о событии, которое вызвало триггер, метаданные записи и объектах записи получаем вызовом следующих методов:

        String action = context.getAction().toString();
        ValuesMetadata fieldsM = context.getFieldsMetadata();
        String tableName = context.getTableName();
        String type = context.getType().toString();
        Values newValues = context.getNewValues();
        Values oldValues = context.getOldValues();
        String nameInfo = context.getNameInfo();
        String objectName = context.getObjectName();

Через объекты oldValues и newValues получаем старые значения записи и устанавливаем новые:

if (newValues != null) {}

Object obj = newValues.getObject - Получаем значение столбца, которое было добавлено в результате INSERT или UPDATE запроса;

newValues.setObject - Устанавливаем новое значение столбца.

После этого соберем проект.

Далее откроем терминал и скопируем собранную Jar-библиотеку в каталог data (/opt/RedDatabase/jar/data) следующей командой:

sudo cp ~/reddatabase/ExternalJava/target/ExternalJava-1.0-SNAPSHOT.jar /opt/RedDatabase/jar/data

Перезапустим сервер базы данных:

sudo service firebird restart

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

CREATE TABLE TRIGGER_CONTEXT (
    ID BIGINT NOT NULL,
    F_VCHAR VARCHAR(100) NOT NULL,
    F_DOUBLE DOUBLE PRECISION NOT NULL,
    F_TIMESTAMP TIMESTAMP NOT NULL,
    INFO VARCHAR(16384),
    CONSTRAINT PK_TRIGGER_CONTEXT PRIMARY KEY (ID)
);

Добавим внешний триггер. Мы хотим, чтобы триггер изменял данные до вставки в таблицу, поэтому создадим его с ключевым словом BEFORE и выполним скрипт.

CREATE OR ALTER TRIGGER EXTERNAL_TRIGGER_SAVE_CONTEXT
BEFORE DELETE OR INSERT OR UPDATE ON TRIGGER_CONTEXT
EXTERNAL NAME 'ru.reddatabase.external.Triggers.saveContextTrigger()'
ENGINE JAVA;

Теперь проверим его работу. Откроем таблицу и вставим строку:

INSERT INTO TRIGGER_CONTEXT(ID, F_VCHAR, F_DOUBLE, F_TIMESTAMP) VALUES (1, 'Уж небо осенью дышало...', 73.522, '01-01-2012 12:00');

Добавленные значения изменились.

А в поле INFO добавилась запись, сформированная внутри внешнего триггера:

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

public static void saveContextToFileTrigger() throws SQLException {

   TriggerContext context = TriggerContext.get();
   String action = context.getAction().toString();
   ValuesMetadata fieldsM = context.getFieldsMetadata();
   Values newValues = context.getNewValues();
   Values oldValues = context.getOldValues();
   String tableName = context.getTableName();
   String type = context.getType().toString();

   String nameInfo = context.getNameInfo();
   String objectName = context.getObjectName();

   String info = "";
   info = info + "Действие: " + action + "\n";
   info = info + "Таблица: " + tableName + "\n";
   info = info + "Тип: " + type + "\n";
   info = info + "Сохраненные метаданные: " + nameInfo + "\n";
   info = info + "Имя объекта: " + objectName + "\n";

   if (fieldsM != null) {
       int count = fieldsM.getParameterCount();
       for (int i = 1; i <= count - 1; ++i) {

           info = info + "Имя поля: " + fieldsM.getName(i) + "\n";
           info = info + "Класс поля: " + fieldsM.getJavaClass(i).toString() + "\n";
           info = info + "Старое значение: " + (oldValues == null || oldValues.getObject(i) == null ? null : oldValues.getObject(i).toString()) + "\n";
           info = info + "Новое значение: " + (newValues == null || newValues.getObject(i) == null ? null : newValues.getObject(i).toString()) + "\n";

           if (newValues != null) {
               PrintWriter writer = null;
               try {
                   writer = new PrintWriter("/tmp/external_trigger.txt");
               } catch (FileNotFoundException e) {
                   throw new RuntimeException(e);
               }
               writer.println(info);
               writer.close();
           }
       }
   }
}

В этом примере мы получаем значения триггера через интерфейсы и выводим их в файл через объект PrintWriter: 

if (newValues != null) {}

Проверим работу метода. Создадим внешний триггер:

CREATE OR ALTER TRIGGER EXTERNAL_TRIGGER_TO_FILE
BEFORE DELETE OR INSERT OR UPDATE ON TRIGGER_CONTEXT
EXTERNAL NAME 'ru.reddatabase.external.Triggers.saveContextToFileTrigger()'
ENGINE JAVA;

И выполним следующий запрос:

INSERT INTO TRIGGER_CONTEXT(ID, F_VCHAR, F_DOUBLE, F_TIMESTAMP) VALUES (2, 'Я помню чудное...', 29.12, '07-13-1999 13:51');

По пути, указанному в Java-коде, создался текстовый файл.

Работа с внешними процедурами

Процедура более сложная конструкция, у нее могут быть входные и выходные параметры. В примере мы будем во входном параметре  передавать имя Java-свойства, значение которого хотим получить в выходном параметре. 

Создадим новый класс Procedures:

public static ExternalResultSet getPropertyProcedure(final String property, final String[] result) {

   return new ExternalResultSet() {
       boolean first = true;

       public boolean fetch() throws Exception {
           if (this.first) {
               result[0] = System.getProperty(property.trim());
               this.first = false;
               return true;
           } else {
               return false;
           }
       }
   };
}

Реализация тела внешней процедуры имеет фиксированный вид из-за указанных параметров в скобках. Входные параметры перечислены вначале, выходные - в конце и обязательно объявляются как массивы.

final String property, final String[] result. 

Метод возвращает объект ExternalResultSet, реализующий доступ к получаемому набору данных. У него необходимо реализовать единственный метод fetch(), перемещающий набор к следующей записи. Метод fetch() будет вызываться до тех пор, пока возвращает значение true. Это позволяет процедуре возвращать больше одной  записи.

Проверим работу. Соберем проект, скопируем собранную Jar-библиотеку в каталог data (/opt/RedDatabase/jar/data):

sudo cp ~/reddatabase/ExternalJava/target/ExternalJava-1.0-SNAPSHOT.jar /opt/RedDatabase/jar/data

И перезапустим сервер:

sudo service firebird restart

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

CREATE OR ALTER PROCEDURE EXTERNAL_PROCEDURE_PROPERTY(PROPERTY CHAR(50))
RETURNS (RESULT CHAR(150))
EXTERNAL NAME 'ru.reddatabase.external.Procedures.getPropertyProcedure(String, String[])'
ENGINE JAVA;

Узнаем значение переменной среды JAVA_HOME, выполнив процедуру:

SELECT * FROM EXTERNAL_PROCEDURE_PROPERTY("java.home");

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

public static ExternalResultSet getAttachmentsProcedure(final int[] attId, final String[] attName) {

   try {
       return new ExternalResultSet() {
           Connection con = DriverManager.getConnection("jdbc:default:connection:");
           PreparedStatement statement = con.prepareStatement("select mon$attachment_id, mon$attachment_name from mon$attachments");
           ResultSet resultSet = statement.executeQuery();

           public boolean fetch() throws Exception {
               if (resultSet.next()) {
                   attId[0] = resultSet.getInt(1);
                   attName[0] = resultSet.getString(2);
                   return true;
               } else {
                   resultSet.close();
                   statement.close();
                   con.close();
                   return false;
               }
           }
       };
   } catch (SQLException e) {
       throw new RuntimeException(e);
   }
}

Этот пример похож на предыдущий, также имеет фиксированный вид, но содержит выходные параметры (final int[] attId, final String[] attName).

В этом примере мы создали подключение в той же транзакции с помощью метода DriverManager.getConnection() с URL вида jdbc:default:connection:. Суть в том, что в этой же транзакции мы выполнили отдельный запрос, который вернул набор данных. Для получения доступа из java-метода к базе данных с отдельной транзакцией необходимо использовать URL вида jdbc:new:connection:.

Проверим работу Java-метода. Пересоберем библиотеку:

sudo cp ~/reddatabase/ExternalJava/target/ExternalJava-1.0-SNAPSHOT.jar /opt/RedDatabase/jar/data

И перезапустим сервер:

sudo service firebird restart

Теперь подключимся к тестовой базе данных и создадим внешнюю процедуру:

CREATE OR ALTER PROCEDURE EXTERNAL_PROCEDURE_ATTACHMENTS()
RETURNS (ID INTEGER, NAME CHAR(150))
EXTERNAL NAME 'ru.reddatabase.external.Procedures.getAttachmentsProcedure(int[], String[])'
ENGINE JAVA;

Проверим ее работу:

SELECT * FROM EXTERNAL_PROCEDURE_ATTACHMENTS;

Рассмотрим пример сопоставления через обобщенные сигнатуры. Создадим метод, в котором будем вызывать процедуру получения случайного числа, максимальное значение которого будет передано во входном параметре:

public static ExternalResultSet randomAdditionProcedure() {

   try {
       ProcedureContext context = ProcedureContext.get();
       final ValuesMetadata inputMetadata = context.getInputMetadata();
       final ValuesMetadata outputMetadata = context.getOutputMetadata();
       final Values input = context.getInputValues();
       final Values output = context.getOutputValues();

       return new ExternalResultSet() {
           Connection conDefault = DriverManager.getConnection("jdbc:default:connection:");
           PreparedStatement attachments = conDefault.prepareStatement("select mon$attachment_id, mon$attachment_name from mon$attachments");
           Connection conNew = DriverManager.getConnection("jdbc:new:connection:");
           PreparedStatement rand = conNew.prepareStatement(
                   String.format("select cast(trunc(rand() * %d) as integer) from rdb$database", ((BigDecimal) input.getObject(1)).intValue()));
           ResultSet rsAttachments = attachments.executeQuery();

           public boolean fetch() throws Exception {
               if (rsAttachments.next()) {
                   ResultSet rsRand = rand.executeQuery();
                   rsRand.next();
                   output.setObject(1, rsAttachments.getBigDecimal(1));
                   output.setObject(2, rsAttachments.getString(2));
                   output.setObject(3, String.format(
                           "Count of input parameters: '%d', " +
                                   "Count of output parameters: '%d', " +
                                   "Rand result: '%s', " +
                                   "Attachment ID: '%s', " +
                                   "Attachment name: '%s'",
                           inputMetadata.getParameterCount(),
                           outputMetadata.getParameterCount(),
                           rsRand.getBigDecimal(1).toString(),
                           rsAttachments.getBigDecimal(1).toString(),
                           rsAttachments.getString(2)));
                   rsRand.close();
                   return true;
               } else {
                   rsAttachments.close();
                   attachments.close();
                   rand.close();
                   conDefault.close();
                   conNew.close();
                   return false;
               }
           }
       };
   } catch (SQLException e) {
       throw new RuntimeException(e);
   }
}

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

ProcedureContext context = ProcedureContext.get();

Информацию о параметрах получаем через метаданные.

final ValuesMetadata inputMetadata = context.getInputMetadata();
final ValuesMetadata outputMetadata = context.getOutputMetadata();

А значения получаем с помощью getInputValues() и getOutputValues().  В примере вызывается процедура в автономной транзакции,  которая создается открытием подключения с URL  jdbc:new:connection:.

Connection conNew = DriverManager.getConnection("jdbc:new:connection:");
PreparedStatement rand = conNew.prepareStatement(
                   String.format("select cast(trunc(rand() * %d) as integer) from rdb$database", ((BigDecimal) input.getObject(1)).intValue()));

Метод fetch() возвращает true до тех пор, пока из набора данных rsAttachments не будут получены все строки. Значение, возвращенное функцией rand() добавляем в выходные значения.

Проверим работу процедуры:

CREATE OR ALTER PROCEDURE EXTERNAL_PROCEDURE_ADD_RAND("RANGE" INTEGER)
RETURNS (ID INTEGER, NAME CHAR(150), RESULT VARCHAR(500))
EXTERNAL NAME 'ru.reddatabase.external.Procedures.randomAdditionProcedure()'
ENGINE JAVA;

Внешние функции

Рассмотрим внешние функции. Они похожи на процедуры, но возвращают только одно значение. 

Напишем внешнюю функцию, которая вычисляет факториал числа, переданного в параметре.

Создадим класс Functions, в котором опишем метод, вычисляющий факториал:

public static long factorialFunction(int x) {

   if ((x == 0) || (x == 1)) {
       return 1;
   } else {
       long fact = 1;
       for (int i = 1; i <= x; i++) {
           fact = fact * i;
       }
       return fact;
   }
}

В этом примере только 1 входной параметр X. Возвращаемый параметр не требует объявления массива и возвращается  вызовом return fact. 

Соберем проект, скопируем собранную Jar-библиотеку в каталог /opt/RedDatabase/jar/data:

sudo cp ~/reddatabase/ExternalJava/target/ExternalJava-1.0-SNAPSHOT.jar /opt/RedDatabase/jar/data

И перезапустим сервер: 

sudo service firebird restart

Подключимся к тестовой БД и создадим внешнюю функцию.

CREATE OR ALTER FUNCTION EXTERNAL_FUNCTION_FACTORIAL(X INTEGER)
RETURNS BIGINT
EXTERNAL NAME 'ru.reddatabase.external.Functions.factorialFunction(int)'
ENGINE JAVA;

Вычислим факториал 7:

SELECT EXTERNAL_FUNCTION_FACTORIAL(7) FROM RDB$DATABASE;

При необходимости можно скачать файл из интернета.

Создадим метод в классе Functions, который позволит скачать файл и вернуть его в формате блоб.

public static Blob downloadFileFunction(String fileURL) {

   SerialBlob blob = null;

   try (BufferedInputStream in = new BufferedInputStream(new URL(fileURL).openStream())) {

       byte[] dataBuffer = new byte[1024];
       int bytesRead;

       ByteArrayOutputStream baos = new ByteArrayOutputStream();
       while ((bytesRead = in.read(dataBuffer, 0, 1024)) != -1) {
           baos.write(dataBuffer, 0, bytesRead);
       }
       blob = new SerialBlob(baos.toByteArray());
   } catch (IOException | SQLException e) {
       throw new RuntimeException(e);
   }

   return blob;
}

Создаем блоб SerialBlob. 

 blob = new SerialBlob(baos.toByteArray());

 И возвращаем его как результат. 

Собираем проект и копируем собранную Jar-библиотеку в каталог /opt/RedDatabase/jar/data:

sudo cp ~/reddatabase/ExternalJava/target/ExternalJava-1.0-SNAPSHOT.jar /opt/RedDatabase/jar/data

Перезапускаем сервер:

sudo service firebird restart

Подключимся к текстовой БД и создадим внешнюю функцию:

CREATE OR ALTER FUNCTION EXTERNAL_FUNCTION_DOWNLOAD_FILE(URL VARCHAR(255))
RETURNS BLOB
EXTERNAL NAME 'ru.reddatabase.external.Functions.downloadFileFunction(String)'
ENGINE JAVA;

Проверим работу функции и загрузим картинку в блоб:

SELECT EXTERNAL_FUNCTION_DOWNLOAD_FILE('https://reddatabase.ru/media/articles/%D1%80%D0%B0%D0%B7%D1%80%D0%B0%D0%B1%D0%BE%D1%82%D0%BA%D0%B0-10.jpg') FROM RDB$DATABASE;

Заключение

Сегодня мы познакомились с разработкой внешних хранимых процедур, функций и триггеров на Java для СУБД Ред Базы Данных!