Простой парсер JSON на PL/SQL

в 8:31, , рубрики: json, oracle, PL/SQL, метки: , , ,

Буквально вчера внезапно возникла задача — понадобилось разобрать данные в формате JSON непосредственно в хранимой процедуре Oracle. Разумеется, именно для таких вещей в Oracle и была добавлена Java, но хотелось чего-то более своего и написанного непосредственно на 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) является просмотр исходного текста и выделение из него потока лексем:

Сканер

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-данных, ограничившись обнаружением ошибок приводящих к безусловной невозможности разбора данных. В этих случаях, все изменения выполненные в транзакции откатываются и возвращается соответствующее исключение.

Кроме того, разработанный парсер (совершенно осознанно с моей стороны) допускает значительные послабления в части форматирования исходных данных. Например он может разобрать следующее описание, вообще говоря, не проходящее валидацию как 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

Источник

* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js