- PVSM.RU - https://www.pvsm.ru -
Буквально вчера внезапно возникла задача — понадобилось разобрать данные в формате JSON [1] непосредственно в хранимой процедуре Oracle. Разумеется, именно для таких вещей в Oracle и была добавлена Java [2], но хотелось чего-то более своего и написанного непосредственно на PL/SQL. Результаты своего порыва я и выкладываю на суд общественности. Вдруг кому пригодится.
Для начала, полезно определиться с тем, что мы собираемся делать. Пусть исходные данные лежат в CLOB-поле какой-то таблицы:
create table ae_spec (
id number not null,
name varchar2(30) not null,
json CLOB
);
alter table ae_spec add
constraint pk_ae_spec primary key(id);
Результат разбора будем складывать в дерево, размещенное во временной табличке и использовать непосредственно в той-же транзакции, в которой разбираем данные:
create global temporary table ae_json (
id number not null,
parent_id number,
name varchar2(1000),
value varchar2(1000)
) on commit delete rows;
Загрузив данные в эту табличку, мы сможем использовать всю мощь SQL для их обработки.
Теперь все готово для разработки нашего маленького пакета:
create or replace package ae_spec_pkg as
procedure compile(p_name in varchar2);
end ae_spec_pkg;
/
create or replace package body ae_spec_pkg as
procedure compile(p_name in varchar2) as
begin
-- Разбираем JSON
load(p_name);
-- TODO: Обрабатываем данные
commit;
exception
when others then
rollback;
raise;
end;
end ae_spec_pkg;
/
Разбор JSON будет удобно разбить на две процедуры. Задачей сканера (процедура load) является просмотр исходного текста и выделение из него потока лексем [3]:
create or replace package body ae_spec_pkg as
g_spec_state constant number default 0;
g_name_state constant number default 1;
procedure lexem(p_state in number
,p_value in varchar2) as
begin
insert into ae_script_log(id, tp, value)
values (ae_script_log_seq.nextval, p_state, p_value);
end;
procedure load(p_name in varchar2) as
l_lob CLOB;
l_str varchar2(1000) default null;
l_len number default null;
l_pos number default 1;
l_ix number default 1;
l_st number default g_spec_state;
l_ch varchar2(1) default null;
l_val varchar2(1000) default null;
l_qt varchar2(1) default null;
l_bs number default 0;
begin
select json into l_lob from ae_spec where name = p_name for update;
dbms_lob.open(l_lob, dbms_lob.lob_readonly);
l_len := dbms_lob.getlength(l_lob);
while l_pos <= l_len loop
l_str := dbms_lob.substr(l_lob, 1000, l_pos);
l_ix := 1;
while l_ix <= length(l_str) loop
l_ch := substr(l_str, l_ix, 1);
if not l_qt is null then
if l_bs = 1 then
if not l_ch in (chr(13), chr(10)) then
l_val := l_val || l_ch;
l_bs := 0;
end if;
goto l;
end if;
if l_ch = '' then
l_bs := 1;
goto l;
end if;
if l_ch = l_qt then
lexem(l_st, l_val);
l_st := g_spec_state;
l_qt := null;
else
l_val := l_val || l_ch;
end if;
goto l;
end if;
if l_ch in ('{', '}', '[', ']', ':', ',', ' ', chr(9), chr(13), chr(10)) then
if l_st = g_name_state then
lexem(l_st, l_val);
end if;
if l_ch in ('{', '}', '[', ']', ':', ',') then
lexem(g_spec_state, l_ch);
end if;
l_st := g_spec_state;
goto l;
end if;
if l_ch in ('''', '"') then
l_val:= null;
l_qt := l_ch;
l_st := g_name_state;
l_bs := 1;
goto l;
end if;
if l_st = g_name_state then
l_val := l_val || l_ch;
else
l_val := l_ch;
l_st := g_name_state;
end if;
<<l>>
l_ix := l_ix + 1;
end loop;
l_pos := l_pos + 1000;
end loop;
if l_st = g_name_state then
lexem(l_st, l_val);
end if;
dbms_lob.close(l_lob);
exception
when others then
if dbms_lob.isopen(l_lob) = 1 then dbms_lob.close(l_lob); end if;
raise;
end;
...
end ae_spec_pkg;
/
Поскольку JSON — очень простой формат, нашему сканеру достаточно всего двух состояний (g_spec_state — ожидание очередного управляющего символа и g_name_state — ожидание продолжения ввода имени или значения).
Для того, чтобы убедиться в правильности разбора, результат пока будем помещать в табличку — лог. Убедившись на нескольких тестовых примерах, что все работает как задумано, внесем изменения в lexem, для сохранения полученных данных в дерево (попутно вносим небольшие изменения в load, чтобы все работало):
create or replace package body ae_spec_pkg as
g_spec_state constant number default 0;
g_name_state constant number default 1;
e_syntax_error EXCEPTION;
pragma EXCEPTION_INIT(e_syntax_error, -20001);
procedure lexem(p_state in number
,p_value in varchar2
,p_node in out NOCOPY number) as
l_id number default null;
l_vl ae_json.name%type;
begin
if p_state = g_spec_state then
if p_value in ('}', ']', ',') then
select parent_id into p_node from ae_json where id = p_node;
end if;
if p_value in ('{', '[', ',') then
select max(id) + 1 into l_id from ae_json;
insert into ae_json(id, parent_id) values (l_id, p_node);
p_node := l_id;
end if;
if p_value = ':' then
select name into l_vl from ae_json where id = p_node;
if l_vl is null then
RAISE_APPLICATION_ERROR(-20001, 'Syntax error');
end if;
end if;
else
select name into l_vl from ae_json where id = p_node;
if l_vl is null then
update ae_json set name = p_value where id = p_node;
else
select value into l_vl from ae_json where id = p_node;
if not l_vl is null then
RAISE_APPLICATION_ERROR(-20001, 'Syntax error');
end if;
update ae_json set value = p_value where id = p_node;
end if;
end if;
end;
procedure load(p_name in varchar2) as
l_lob CLOB;
l_str varchar2(1000) default null;
l_len number default null;
l_pos number default 1;
l_ix number default 1;
l_st number default g_spec_state;
l_ch varchar2(1) default null;
l_val varchar2(1000) default null;
l_qt varchar2(1) default null;
l_bs number default 0;
l_node number default 0;
begin
insert into ae_json(id) values (l_node);
select json into l_lob from ae_spec where name = p_name for update;
dbms_lob.open(l_lob, dbms_lob.lob_readonly);
l_len := dbms_lob.getlength(l_lob);
while l_pos <= l_len loop
l_str := dbms_lob.substr(l_lob, 1000, l_pos);
l_ix := 1;
while l_ix <= length(l_str) loop
l_ch := substr(l_str, l_ix, 1);
if not l_qt is null then
if l_bs = 1 then
if not l_ch in (chr(13), chr(10)) then
l_val := l_val || l_ch;
l_bs := 0;
end if;
goto l;
end if;
if l_ch = '' then
l_bs := 1;
goto l;
end if;
if l_ch = l_qt then
lexem(l_st, l_val, l_node);
l_st := g_spec_state;
l_qt := null;
else
l_val := l_val || l_ch;
end if;
goto l;
end if;
if l_ch in ('{', '}', '[', ']', ':', ',', ' ', chr(9), chr(13), chr(10)) then
if l_st = g_name_state then
lexem(l_st, l_val, l_node);
end if;
if l_ch in ('{', '}', '[', ']', ':', ',') then
lexem(g_spec_state, l_ch, l_node);
end if;
l_st := g_spec_state;
goto l;
end if;
if l_ch in ('''', '"') then
l_val:= null;
l_qt := l_ch;
l_st := g_name_state;
l_bs := 1;
goto l;
end if;
if l_st = g_name_state then
l_val := l_val || l_ch;
else
l_val := l_ch;
l_st := g_name_state;
end if;
<<l>>
l_ix := l_ix + 1;
end loop;
l_pos := l_pos + 1000;
end loop;
if l_st = g_name_state then
lexem(l_st, l_val, l_node);
end if;
if l_node <> 0 then
RAISE_APPLICATION_ERROR(-20001, 'Syntax error');
end if;
dbms_lob.close(l_lob);
exception
when others then
if dbms_lob.isopen(l_lob) = 1 then dbms_lob.close(l_lob); end if;
raise;
end;
procedure compile(p_name in varchar2) as
begin
load(p_name);
-- TODO:
commit;
exception
when others then
rollback;
raise;
end;
end ae_spec_pkg;
/
В этой реализации я не старался отлавливать все возможные синтаксические ошибки в JSON-данных, ограничившись обнаружением ошибок приводящих к безусловной невозможности разбора данных. В этих случаях, все изменения выполненные в транзакции откатываются и возвращается соответствующее исключение.
Кроме того, разработанный парсер (совершенно осознанно с моей стороны) допускает значительные послабления в части форматирования исходных данных. Например он может разобрать следующее описание, вообще говоря, не проходящее валидацию [4] как JSON-данные:
{ tables: { AD_ACTIVATION_TYPE: { ID: { attribute: id }
, ACT_DATE: { attribute: start_date
, sql: "is null"
}
, ACT_PRIORITY: { attribute: priority }
, TYPE_ID: { attribute: subtype }
, ACT_STATE: { attribute: state
, sql: "= 1"
}
}
}
, attributes: { id: { type: integer
, is_mandatory
}
, start_date: { type: date }
, priority: { type: integer }
, subtype: { type: integer
, is_mandatory
}
, state: { type: integer
, is_mandatory
}
}
}
Конечно, при необходимости, разбор данных можно ужесточить, добавив необходимые проверки, но я не вижу в этом большого смысла, поскольку корректные JSON данные разбираются без каких либо проблем.
На этом все. Буду рад если мой пост окажется кому-то полезным.
Автор: GlukKazan
Источник [5]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/oracle/44430
Ссылки в тексте:
[1] JSON: http://ru.wikipedia.org/wiki/JSON
[2] Java: http://www.oracle.com/technetwork/articles/java/json-1973242.html
[3] лексем: http://ru.wikipedia.org/wiki/%D0%9B%D0%B5%D0%BA%D1%81%D0%B5%D0%BC%D0%B0_%28%D0%B8%D0%BD%D1%84%D0%BE%D1%80%D0%BC%D0%B0%D1%82%D0%B8%D0%BA%D0%B0%29
[4] валидацию: http://jsonlint.com/
[5] Источник: http://habrahabr.ru/post/195496/
Нажмите здесь для печати.