Здравствуйте! Меня зовут Алексей Калинин. Я Oracle PL/SQL-разработчик. Это моя первая статья. По работе 90% времени занимает написание серверного кода на языке PL/SQL. Всем хорош PL/SQL – и простой синтаксис, и неявное преобразование типов, и поддержка SQL-инструкция внутри кода для обработки данных в БД. Из минусов только то, что PL/SQL- код может выполнятся только в БД Oracle. А хотелось, чтобы был бы язык с синтаксисом, похожим на PL/SQL, но выполнялся он на клиенте (не был привязан к какой-нибудь БД). Умел бы соединяться с различными БД для выполнения операций с данными.
Для чего же можно было бы применять этот клиентский PL/SQL.
-
Для того, чтобы можно было бы работать с локальными файлами: загружать данные из файлов напрямик в БД, выгружать данные из БД в локальные файлы.
-
Открывать в одном PL/SQL-коде несколько соединений к различным БД (Oracle, PostgreSQL, MsSQL, MySQL, SQLLite и т.д.) для сверки, анализа данных, перегрузки данных между различными БД и т.д.
Тема парсинга и интерпретации программного кода интересовала мне давно. И я решил попробовать реализовать интерпретатор языка, похожего по синтаксису на PL/SQL. В качестве языка реализации интерпретатора я выбрал Delphi.
Описание придуманного языка программирования
По синтаксису данный язык очень похож на Oracle PL/SQL. Что то было привнесено в него из PLpg/SQL для Postgres.
Опишу только то, что из базового синтаксиса PL/SQL было реализовано, и что было изменено и добавлено.
1. Описание типов данных (какие типы данных поддерживаются).
-
VARCHAR2 - Этот тип данных хранит строку. Если указана длина строки - то будет происходить проверка длины в коде, если длина не указана - то можно записывать данные длиной не более 100000000.
-
INTEGER - Этот тип данных используется для работы с полями, которые будут содержать только целые числовые данные.
-
NUMBER - Этот тип данных используется для работы с полями, которые будут содержать только числовые данные с фиксированной или плавающей точкой.
-
DATE - Этот тип данных хранит значения в формате даты (dd.mm.yyyy hh24:mi:ss).
-
JSON - Этот тип данных является форматом для хранения и передачи данных в форме объектов, массивов и других значений, которые можно легко преобразовать в текстовую строку.
-
RECORD - Этот тип данных похож на переменную строкового типа, но он не имеют предопределённой структуры. Он приобретают фактическую структуру от строки, которая им присваивается командами SELECT или FOR. Структура переменной типа record может меняться каждый раз при присвоении значения.
-
BOOLEAN - Этот тип данных хранит логические значения (true/false).
2. Курсоры.
Курсор – это средство извлечения данных из базы данных. Курсоры содержат определения столбцов и объектов (таблиц, представлений и т.п.) из которых будут извлекаться данные, а также набор критериев, определяющих какие именно строки должны быть выбраны.
Пользователю доступны следующие способы выполнения запроса к базе данных:
-
Неявные курсоры - простой оператор SELECT ... INTO извлекает одну строку данных непосредственно в переменные локальной программы. Это удобный (и часто наиболее эффективный) способ доступа к данным, использование которого, однако, может приводить к необходимости повторного кодирования оператора SELECT (или похожих операторов) в нескольких местах программы.
-
Явные курсоры - вы можете явно объявить курсов в разделе объявлений. В этом случае курсор можно будет открывать и извлекать данные в одной или нескольких программах, причем возможности контроля будут шире, чем при использовании неявных курсоров.
3. Операторы FOR, WHILE, OPEN, IF.
В данной реализации можно использовать следующие операторы:
-
Операторы цикла: FOR, LOOP, WHILE.
-
Условный оператор: IF THEN ELSE.
-
Оператор: OPEN FETCH.
4. Также добавлены встроенные процедуры/функции.
-
dbms_output.put_line(text) - вывод текстового сообщения (как в Oracle).
-
upper(text) - перевод текста в верхний регистр.
-
lower(text) - перевод текста в нижний регистр.
-
rtrim(text) - отрезать пробелы справа.
-
ltrim(text) - отрезать пробелы слева.
-
trim(text) - отрезать пробелы со всех сторон.
-
replace(text, text_to_replace, replacement_text) - замена текста по образцу.
-
substr(text, start_position, length) - извлечь подстроку из строки.
-
instr(text, substring) - возвращает n-е вхождение подстроки в строке.
-
length(text) - возвращает длину строки.
-
chr(number_code) - возвращает символ, который основан на числовом коде.
-
host(command, work_dir) - выполним cmd-команду и вернем результат (work_dir - рабочий каталог (например: c:)).
Также были реализованы дополнительные пакеты для работы с локальными файлами, для отправки http/https запросов, для работы с json-объектами.
5. Пакет TEXT_IO.
Для работы с локальными файлами имеется встроенный пакет - TEXT_IO. (похожий пакет когда-то был в Oracle Forms)
Он содержит следующие методы:
-
text_io.fopen( 'полный путь к файлу', 'ключ', кодировка) - Открытие файла. Ключ содержит следующие значения - 'r' - только чтение файла, 'w' - только запись в файл. Кодировка - UTF8 или ANSI.
-
text_io.count_lines(file1) - Прочитать количество строк из файла.
-
text_io.get_line(file1, index) - Прочитать строку из файла по индексу (index).
-
text_io.get_file_content(file1) - Прочитать все содержимое файла.
-
text_io.put_line(file1, 'строка') - Записать строку в файл.
-
text_io.fclose(file1) - Закрытие файла.
6. Пакет HTTP_IO.
(Что то похожее также есть в Oracle)
Для доступа к данным в Интернете через протокол HTTP/HTTPS имеется встроенный пакет - HTTP_IO. Я его использую для отладки http/https-запросов к rest-серверам. Он содержит следующие методы:
-
http_io.begin_request(url, method) - инициализация переменной.
-
http_io.set_header(http_req, 'ключ', 'значение') - установить заголовок
-
http_io.set_resp_encoding(http_req, 'UTF8') - установить кодировку ответа.
-
http_io.write_text(http_req, 'тело отправляемого сообщения') - установить тело отправляемого сообщения.
-
http_io.get_response(http_req) - перейдем по url и получим ответ.
-
status_code - код ответа
-
response_text - текст ответа
7. Пакет JSON_IO.
Для работы с JSON-объектами имеется встроенный пакет - JSON_IO. Он содержит следующие методы:
-
json_io.add(js_obj1, 'ключ', 'значение') - добавить в json-объект пару ключ-значение.
-
json_io.format(js_obj1) - вывод json-кода в отформатированном виде.
-
json_io.record_to_json(rec) - преобразовать значение переменной типа record в json-формат.
-
json_io.count_keys(js_obj1) - вывести количество пар ключ-значение.
-
json_io.count_rows(js_obj1) - вывести количество значений внутри json-массива.
-
json_io.get_key_name(js_obj1, index) - вывести название ключа по индексу (index).
-
json_io.get_key_value(js_obj1, index) - вывести значение ключа по индексу (index).
-
json_io.get_row(js_obj1, index) - вывести json-код элемента массива по индексу (index).
8. Пакет RECORD_IO.
Для работы с переменными типа record имеется встроенный пакет - RECORD_IO. Он содержит следующие методы:
-
record_io.add_field(rec1, ['название_поля', 'тип_поля']) - добавить в переменную типа record новое поле (новые поля...)
-
record_io.print_record(rec1) - вывод содержимого переменной типа record (rec1).
-
record_io.json_to_record(obj1, rec1) - преобразовать значение переменной типа json (obj1) в переменную типа record (rec1).
Так как интерпретируемый код выполняется на клиенте, то он не знает в какой БД выполнять SQL-инструкцию.
Для этого было добавлено следующее обязательное условие:
Внутри описания любой SQL-инструкции (курсора, select/insert/update/delete-выражения) в обязательном порядке должна быть указана метка /*##db=dbname##*/, в которой указано название БД, для которой будет выполнятся данная конструкция. Описание параметров соединения с БД заранее передаются в интерпретатор.
Описание работы интерпретатора
Реализация интерпретатора была разделена на три этапа:
На первом этапе мною был реализован парсер текста выполняемой программы. Весь тест был разделен на токены и помещен в массив. В интернете масса примеров кода парсеров, которые разделяют код на токены разного типа.
На втором этапе по разобранным токенам я построил AST (абстрактное синтаксическое дерево) дерево.
На третьем этапе реализуется сам интерпретатор, который обходит это дерево и выполняет его. Если AST-дерево построено достаточно подробно - то сам механизм интерпретации получается несложным.
Рассмотрим пример:
Интерпретируемый (выполняемый) код
declare
ind integer;
header_id_ number(10);
cursor cur_main_1(arg_ varchar2) is
select *
from /*##db=db_name##*/
tbl_name d
where d.status = 0 and
d.billhead_id = header_id_ and
(arg_ is null or d.name like '%'||arg_||'%')
order by d.name;
cnt integer;
patt_name varchar2(255);
begin
header_id_ := 911988;
patt_name := 'L-тироксин 150';
dbms_output.put_line('---Test 1-----');
-- Откроем курсор и внутри цикла for выполним курсор
for rec in cur_main_1(patt_name) loop
select count(*)
into cnt
from /*##db=db_name##*/
tbl_name_2 mr
where mr.name = rec.name;
dbms_output.put_line(' Name='||rec.name||', cnt='||cnt);
dbms_output.put_line('rec.id='||rec.id);
end loop;
end;
Разделяем текст на токены (парсинг текста) и строим абстрактное синтаксическое дерево (AST). Чем точнее написано AST дерево – тем проще дальнейшая интерпретация (выполнение) кода.
Построенное AST-дерево.
<Root xmlns="Root">
<DECLARE value="DECLARE" line="1">
<VARIABLE line="2" name="ind" type="integer" default_value="" type_size="" select="" is_null="Y"/>
<VARIABLE line="3" name="header_id_" type="number" default_value="" type_size="10" select="" is_null="Y"/>
<CURSOR line="1" name="cur_main_1" select="select *
from /*##db=db_name##*/
tbl_name d
where d.status = 0 and
d.billhead_id = header_id_ and
(arg_ is null or d.name like '%'||arg_||'%')
order by d.name" conn_name="db_name">
<ARGUMENTS>
<ARGUMENT name="arg_" type="varchar2"/>
</ARGUMENTS>
</CURSOR>
<VARIABLE line="14" name="cnt" type="integer" default_value="" type_size="" select="" is_null="Y"/>
<VARIABLE line="15" name="patt_name" type="varchar2" default_value="" type_size="255" select="" is_null="Y"/>
</DECLARE>
<BEGIN value="BEGIN" line="16" exception="false">
<STATEMENT line="17" tp="assignment">
<IDENTIFIER val="header_id_"/>
<ASSIGNMENT>
<BLOCK_VAL>
<CSTI_INTEGER val="911988"/>
</BLOCK_VAL>
</ASSIGNMENT>
</STATEMENT>
<STATEMENT line="18" tp="assignment">
<IDENTIFIER val="patt_name"/>
<ASSIGNMENT>
<BLOCK_VAL>
<CSTI_STRING val="L-тироксин 150"/>
</BLOCK_VAL>
</ASSIGNMENT>
</STATEMENT>
<STATEMENT line="19" tp="run">
<IDENTIFIER val="dbms_output.put_line">
<ARGUMENTS>
<ARGUMENT>
<CSTI_STRING val="---Test 1-----"/>
</ARGUMENT>
</ARGUMENTS>
</IDENTIFIER>
</STATEMENT>
<FOR line="22">
<PARAMS>
<FOR_VAR var_name="rec"/>
<FOR_CURSOR cur_name="cur_main_1">
<ARGUMENTS>
<ARGUMENT>
<CSTI_IDENTIFIER val="patt_name"/>
</ARGUMENT>
</ARGUMENTS>
</FOR_CURSOR>
</PARAMS>
<SELECT line="23" name="select_67484" select="select count(*)
into cnt
from /*##db=db_name##*/
tbl_name_2 mr
where mr.name = rec.name" conn_name="db_name"/>
<STATEMENT line="28" tp="run">
<IDENTIFIER val="dbms_output.put_line">
<ARGUMENTS>
<ARGUMENT>
<CSTI_STRING val=" Name="/>
<CSTI_CONCATENATION val="||"/>
<CSTI_IDENTIFIER val="rec.name"/>
<CSTI_CONCATENATION val="||"/>
<CSTI_STRING val=", cnt="/>
<CSTI_CONCATENATION val="||"/>
<CSTI_IDENTIFIER val="cnt"/>
</ARGUMENT>
</ARGUMENTS>
</IDENTIFIER>
</STATEMENT>
<STATEMENT line="29" tp="run">
<IDENTIFIER val="dbms_output.put_line">
<ARGUMENTS>
<ARGUMENT>
<CSTI_STRING val="rec.id="/>
<CSTI_CONCATENATION val="||"/>
<CSTI_IDENTIFIER val="rec.id"/>
</ARGUMENT>
</ARGUMENTS>
</IDENTIFIER>
</STATEMENT>
</FOR>
</BEGIN>
</Root>
Для некоторых вершины внутри раздела BEGIN указан номер строки. Это нужно для того, чтобы можно было бы выводит номер строки при возникновении ошибки при выполнении кода программы.
Текст XML-дерева не комментирую, так как его легко соотнести с исполняемым кодом примера.
На следующем этапе происходит интерпретация (выполнение) программного кода. Интерпретатор обходит XML-дерево и выполняет в нем указанные команды. Значения переменных типа varchar2, integer, number, date и Boolean хранятся как строки. Для переменной типа json сразу создается json-объект. Для переменной типа record при добавлении новых полей создаются переменные с именем - record_name.field_name.
Когда интерпретатор доходит до выполнения SQL-инструкций (открытия курсоров, выполнения select/insert/update/delete-выражений) то происходит создания подключения к указанной БД (если оно еще было не создано). Название БД должно быть указано в метке /*##db=dbname##*/, которая должна присутствовать в каждой SQL-инструкций. Общий перечень параметров соединения к различным БД может быть передан интерпретатору в виде отдельного файла.
После подключения к БД для SQL-инструкций создается DataSource (DataSet), необходимый для получения данных (select-выражение) или выполнения insert/update/delete-выражения.
Часть орфографических ошибок (целостность операторов, отсутствие точки с запятой после операторов и т.д.) в выполняемом коде отлавливается на этапе построения AST-дерева. Другая часть ошибок может быть отловлена в момент интерпретации кода (ошибки в названиях переменных и внутренних встроенных пакетов и т.д.). В этом случае выдается ошибка с указанием номера строки.
Вот некоторые примеры кода, которые исполняются созданным интерпретатором.
Запись данных в файл:
declare
cursor c is
select /*##db=db_name##*/ * from table;
res varchar2;
file1 text_io.file_type;
ind integer;
begin
-- Все данные из курсора запишем в файл в виде JSON-массива
-- Откроем файл для записи
file1 := text_io.fopen('my_json_file.txt','w','UTF8');
-- Запишем строку в файл
text_io.put_line( file1, '[');
ind := 0;
-- Откроем курсор
for rec in c loop
ind := ind + 1;
-- Преобразуем record в json
res := json_io.record_to_json(rec);
--Запишем данные в файл
if ind = 1 then
text_io.put_line(file1, res);
else
text_io.put_line(file1, ','||chr(10)||res);
end if;
end loop;
-- Запишем строку в файл
text_io.put_line(file1, ']');
-- Закроем файл
text_io.fclose(file1);
dbms_output.put_line('ok');
end;Отправка POST-запроса:
declare
http_req http_io.req;
http_resp http_io.resp;
begin
http_req := http_io.begin_request('http://localhost:1234', 'POST');
http_io.set_header(http_req, 'Content-Type', 'application/json');
http_io.set_resp_encoding(http_req, 'UTF8');
http_io.write_text(http_req, '{"command":"FIND_INCOME_DOC",
"body":{"filter":{"start_date":"2024-06-26T00:00:00","end_date":"2024-06-26T23:59:59"},"start_from":0,"count":100}
}');
http_resp := http_io.get_response(http_req);
dbms_output.put_line('HTTP response status code: ' || http_resp.status_code);
dbms_output.put_line('HTTP response text: ' || http_resp.response_text);
end;Пример работы с JSON
declare
obj json := '{"main_obj": {"1": 123, "2": 456}}';
new_obj json;
arr json := '[]';
begin
dbms_output.put_line('1. obj = '||obj);
-- Добавим две пары ключ-значение
obj := json_io.add(obj, 'id', '1000', 'name', 'aaaaaa');
dbms_output.put_line('2. obj = '||obj);
--Добавим новый подобъект
new_obj := '{"dddd": 890}';
obj := json_io.add(obj, 'new_obj', new_obj);
dbms_output.put_line('3. obj = '||obj);
------------
--В массив arr добавим новый элемент
arr := json_io.add(arr, '', new_obj);
dbms_output.put_line('4. arr = '||arr);
------------
--В массив arr добавим новый элемент
arr := json_io.add(arr, '', '{"aaaa": 111}');
dbms_output.put_line('5. arr = '||arr);
--Добавим массив в объект - obj
obj := json_io.add(obj, 'rows', arr);
dbms_output.put_line('5. Результат: obj = '||json_io.format(obj));
end;Открытие курсоров в разных соединениях (Oracle и PostgreSQL) и отдельный запуск функций в них:
declare
--
cursor cur_main_pg is
select *
from /*##db=db_PG##*/
table_pg d
where d.billhead_id = 123456;
--
cursor cur_main_ora is
select *
from /*##db=db_ORACLE##*/
table_oracle d
where d.billhead_id = 123456;
--
f_name varchar2;
num_val varchar2;
begin
-- Открытие курсоров в разных соединениях и отдельный запуск функций в них
--
dbms_output.put_line('--Start--PG');
--
for rec in cur_main_pg loop
-- Выполним функцию в БД и вернем ее результат
f_name := apt.n_get_reg_name/*##db=db_PG##*/(rec.registry_id);
-- Выполним функцию в БД и вернем ее результат
num_val := apt.apt_contracts__clear_numeric_val/*##db=db_PG##*/(' 123.5676');
--
dbms_output.put_line(' num_val='||num_val);
dbms_output.put_line('Name = '||f_name||', meas_name='||apt.n_get_measure_name/*##db=db_PG##*/(rec.input_measure_id));
end loop;
--
dbms_output.put_line(' ');
dbms_output.put_line('--Start--Oracle');
--
for rec in cur_main_ora loop
-- Выполним функцию в БД и вернем ее результат
f_name := apt.n_get_reg_name/*##db=db_ORACLE##*/(rec.registry_id);
--
dbms_output.put_line('Name = '||f_name||', meas_name='||apt.n_get_measure_name/*##db=db_ORACLE##*/(rec.input_measure_id));
end loop;
end;
В заключение хочу добавить, что реализованный интерпретатор позволяет упростить операции по загрузке/выгрузке данных из локальных файлов, сравнение данных , полученных из разных БД (Oracle, PostgreSQL, MsSQL, MySQL, SQLLite и т.д.), прямой импорт/экспорт данных между разными БД, отладка http/https запросов и т.д. Благодаря тому, что придуманный язык поддерживает встроенные SQL-инструкции, программного кода для реализации всего этого нужно меньше, чем в других языках программирования, таких как Java, JavaScript, C#, Delphi и т.д.
Интерпретатор может быть оформлен в виде отдельного консольного приложения (по аналогии с python.exe, node.exe и т.д.). В таком случае исполняемый код должен быть сохранен в виде файла и будет передаваться отдельным параметром в командной строке.
Автор: ank_75
