Хранимые процедуры, функции и триггеры на Java

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

Настройка Java

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

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

sudo dnf install java-11-openjdk-deve

Теперь настроим параметры взаимодействия сервера СУБД Ред База Данных с виртуальной машиной 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 для СУБД Ред Базы Данных!