Иерархическая база данных (продолжение)07.11.2023 13:46

Часть 2. Внутреннее устройство ИС

Вот, наконец, мы подошли к самому интересному — как это все устроено.

Структура БД ИС состоит из двух схем:

• схема Метаданных, которая содержит описание структуры данных приложения, настройки экранных форм (включая исполняемый код отдельных элементов экранных форм[1]), код процедур и т. д.;

• схема Данных приложения.

Метаданные

Еще раз, приведу структуру данных приложения, которую у нас принято называть учетной схемой (УС)

УС приложения

Оставим в стороне настройку экранных форм и код процедур. Посмотрим как в схеме Метаданных описывается структура данных приложения (УС) приложения. Схема метаданных включает 4 основных таблицы, описывающих структуру данных:

Таблица атрибутов ATTRIB[2]. Таблица содержит список всех атрибутов ИС. Любой атрибут из списка может быть использован в любой категории. Далее привожу список основных полей таблицы в представлении Oracle:

Таблица IOT (index organization table).

Мы видим, что у нас 6 атрибутов (4 строковых и 2 типа «дата»). В принципе, ничто не мешает использовать в категории «Сотрудник» те же атрибуты, что и в «Предприятии» и «Контрагенте» (например: атрибут 1 для ФИО, а атрибут 2 для Табельного номера»). Для приведенного в предыдущем разделе примера таблица ATTRIB будет выглядеть следующим образом:

Таблица категорий CATEG. Таблица содержит список всех категорий ИС.

Таблица IOT (index organization table).

Для приведенного в предыдущем разделе примера таблица CATEG будет выглядеть следующим образом:

Таблица атрибутов категорий CAT_ATR. Таблица содержит список всех атрибутов категорий ИС.

Таблица IOT (index organization table).

Для приведенного в предыдущем разделе примера таблица CAT_ATR будет выглядеть следующим образом:

То есть, категория №1 имеет атрибуты 1 и 2, категория №4 имеет атрибуты 3 и 4, а категория №3 атрибутов вообще не имеет.

Таблица связей категорий CONNECT_C. Таблица содержит список связей категорий ИС.

Таблица IOT (index organization table).

Для приведенного в предыдущем разделе примера таблица CAT_ATR будет выглядеть следующим образом:

То есть, к категории №1 «снизу привязаны» категории 3 и 4, к категории №2 — категории 3 и 5, а к категории №4 — категория 5.

Таким образом определяется структура данных приложения.

Кроме описанных четырех таблиц, определяющих структуру данных ИС, схема метаданных содержит таблицы для описания экранных форм (местоположение и размеры контролов, формулы контролов и т.д.), процедур (текст и скомпилированный код) и др.

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

Еще раз обращаю ваше внимание, что разработчик не видит БД и не имеет возможности изменять ее структуру. При добавлении новой категории, в таблицу CATEG вставляется новая запись, а при добавлении связи между категориями запись вставляется в таблицу CONNECT_C.

Данные приложения

Схема данных — главная часть ИС. Здесь хранятся все данные приложения. Именно отсюда с помощью запросов мы получаем данные. Если в схеме метаданных количество данных в таблицах исчисляются сотнями, то в таблицах данных количество записей в таблицах исчисляется сотнями миллионов.

Центральным понятием, информационной единицей нашей ИС, как было сказано в предыдущем разделе, является объект. Объект всегда создается в рамках определенной категории и может иметь набор значений атрибутов, заданных для категории в схеме метаданных (таблица CAT_ATR) и может иметь связи с объектами других категорий, если они установлены в схеме метаданных (таблица CONNECT_C).

Все данные ИС хранятся в 3-х таблицах:

• Таблица объектов;

• Таблица атрибутов объектов;

• Таблица связей объектов.

В ходе написания первой части статьи мы создали некоторую структуру данных, содержащую предприятия холдинга, контрагентов предприятия и кураторов предприятия для контрагентов. Причем кураторство является «историчным», то есть ограничено периодом действия.  Схема объектов c атрибутами и связями представлена на рисунке внизу.

Дерево объектов

На этой схеме мы и будем рассматривать таблицы, описывающие данные приложения

Таблица объектов категорий CAT_OBJ.

Таблица содержит список всех объектов ИС с указанием номера объекта и номера категории, к которой «принадлежит» объект.

Для нашего примера таблица будет содержать 20 строк и выглядеть так:

Таблица связей объектов NOM_NOM.

Таблица содержит список связей объектов ИС с указанием номера категории и объекта для верхней категории и номера категории объекта для нижней категории.

Поскольку номер объекта уникален в рамках всего приложения, то достаточно номера категорий верхнего и нижнего объектов являются избыточной информацией. Они введены для оптимизации запросов, что я покажу, когда мы будем рассматривать получение данных из такой структуры. В принципе, это немного модифицированная хэш-таблица из способа описания графа «Список смежности» (Adjacency list).

Ну и, соответственно, таблица связей объектов будет содержать 26 строк (посчитайте все стрелки на схеме) и выглядеть так:

Таблица атрибутов объектов OBJ_ATR.

И, наконец, последняя таблица, содержащая значения атрибутов. Главная сложность состоит в том, что в одной таблице необходимо совместить данные трех типов (числовые, строковые и даты). Первоначально данные всех типов содержались в одной таблице в формате VARCHAR2, то есть в строковом виде. Даты хранились в виде «YYYYMMDD» (например,»20101231»), а числа хранились в формате «FM09999999999999999999.99999999999999999999» — «знак, 20 цифровых символов целой части, точка, 20 цифровых символов дробной части» (например, число -37654.351 в таблице представлялось как строка »-00000000000000037654.351»).

Однако, по мере роста объема БД, мы столкнулись с существенным падением производительности из-за того, что оптимизатор запросов плохо справлялся с такого рода данными. Поэтому, в результате различного рода экспериментов мы пришли к выводу о необходимости хранения данных в трех различных таблицах: OBJ_ATR_NUM, OBJ_ATR_STR, OBJ_ATR_DAT. Ниже приведен список полей для каждой таблицы:

OBJ_ATR_NUM

OBJ_ATR_STR

OBJ_ATR_DAT

Для совместимости старая таблица, содержащая данные всех типов была преобразована в представление (view), объединяющее три вышеуказанные таблицы с INSTEAD OF триггером на добавление, изменение и удаление атрибута, который при добавлении, изменении и удалении атрибута в представлении совершал эти действия в соответствующей типу данных таблице.

Соответственно, для нашего примера таблицы данных будут выглядеть следующим образом:

OBJ_ATR_STR

 OBJ_ATR_DAT

Все таблицы (объектов категорий, атрибутов категории и связей категорий) связаны внешними ключами с соответствующими таблицами схемы метаданных. Здесь описания всех ключей, сиквенсов, процедур и триггеров я не привожу.

Ну и несколько простых примеров, как строятся запросы к базе данных.

Предположим, нам надо получить список сотрудников предприятия «Геркулес». Если посмотреть на учетную схему приложения, из предыдущего раздела, то можно увидеть, что для того, чтобы получить всех сотрудников предприятия надо «спуститься» от того предприятия на категорию 4 (Сотрудники), то есть получить все объекты по категории 4, привязанные «снизу» к объекту [1 11]:

select LO_CAT, LO_OBJ
from NOM_NOM
where (HI_CAT, HI_OBJ) = ((1,11))
and LO_CAT=4

В результате выполнения запроса получим:

Вот из-за возможности написать в запросе and LO_CAT=4, в таблице связей присутствуют поля HI_CAT и LO_CAT. В противном случае нам бы пришлось идти в таблицу объектов категорий (CAT_OBJ) и там из полученного списка номеров объектов выбирать только те, которые имеют нужный номер категории.

Но в предыдущем запросе мы получили только номера объектов, а нам надо получить ФИО сотрудников, то есть атрибут № 4 для каждого объекта. Для этого объединим в запросе 2 таблицы: таблицу связей (NOM_NOM) и таблицу атрибутов объектов (в данном случае таблицу строковых атрибутов OBJ_ATR_STR):

select oas.N_CAT ac, oas.N_OBJ ao, oas.VALUE av
from NOM_NOM nn, OBJ_ATR_STR oas
where (nn.HI_CAT, nn.HI_OBJ) = ((1, 11))
and nn.LO_CAT = 4
and (oas.N_CAT, oas.N_OBJ) = ((nn.LO_CAT, nn.LO_OBJ))
and oas.N_ATTR = 4

В результате выполнения запроса получим:

Понятно, что в последнем запросе можно исключить условие из условия (oas.N_CAT, oas.N_OBJ) = ((nn.LO_CAT, nn.LO_OBJ)) сравнение по номерам категорий, так как номер объекта однозначно идентифицирует объект, но на больших объемах данных запрос с таким условием выполняется гораздо быстрее.

Первый SQL-запрос в языке AQL будет выглядеть так:

[1 11]:4

А второй так:

[1 11]:4.4

Видно, насколько AQL упрощает написание запросов к БД. Необходимо только «проникнуться» логикой иерархической системы. Ну и, естественно, использование AQL сильно снижает требования к разработчикам приложений на такой иерархической системе.

Ну и еще один пример. Чуть усложним задачу и выведем список контрагентов Предприятия «Геркулес» с их кураторами на определенную дату. Подразумевается, что у каждого контрагента в одно время может быть только один куратор, но у одного куратора может быть несколько контрагентов. То есть, этот запрос должен возвращать уже не список, а таблицу, в первой колонке которой будет наименование контрагента, а во второй ФИО сотрудника. Контрагенты должны выводиться на определенную дату (помним, что связь сотрудника предприятия с контрагентом «исторична», то есть имеет дату начала и окончания действия). Посмотрим на УС приложения и составим запрос:

Получим всех контрагентов предприятия «Геркулес» (объекты категории 2), которые привязаны через промежуточную категорию 3, которая введена специально для обеспечения отношения М: М.

Сначала получим объекты категории 3 «привязанные снизу» к объекту [1 11]:

select LO_CAT, LO_OBJ
from nom_nom
where (HI_CAT, HI_OBJ) = ((1,11))
and HI_CAT=3

А затем для каждого полученного объекта получим объекты по категории 2, «привязанные сверху».

select ish_kat, ish_obj
from A_DATA.nom_nom
where (res_kat, res_obj) in
((
select LO_CAT, LO_OBJ
from nom_nom
where (HI_CAT, HI_OBJ) = ((1,11))
and HI_CAT=3
))
and HI_CATt=2

Ну или так:

select nn2.HI_CAT, nn2.HI_OBJ
from nom_nom nn1, nom_nom nn2
where
(nn1.HI_CAT, nn1.HI_OBJ)=((1, 11))
and nn1.HI_CAT=1
and nn1.LO_CAT=3
and nn2.LO_CAT=3
and (nn2.LO_CAT, nn2.LO_OBJ)=((nn1.LO_CAT, nn1.LO_OBJ))
and nn2.HI_CAT=2

В результате последнего запроса имеем список объектов контрагентов предприятия «Геркулес».

А вот дальше все гораздо сложнее: В первую колонку нам надо вывести наименование контрагента, а во вторую колонку куратора. А вот чтобы получить куратора для каждого контрагента необходимо «спуститься вниз» на категорию 5 «Куратор контрагента», выбрать объекты, которые подходят по дате (атрибут 5 меньше заданной даты, а атрибут 6 больше), «подняться вверх» на категорию 4 «Сотрудники» и выбрать только сотрудников предприятия «Геркулес» (привязанных к объекту [1 11]). Обратите внимание: последнее очень важно, так как в результате перехода от контрагентов к кураторам предприятия мы получим всех кураторов, в том числе и по другим предприятиям. Тут уже потребуется преобразовать результат запроса в коллекцию, то есть использовать функцию CAST (MULTISET…). Не буду утомлять пошаговым описанием построения запроса, приведу конечный результат.

select
ACOL1_ac, ACOL1_ao,
(select OA1.value
from
OBJ_ATR_STR OA1 where OA1.N_ATR=1 and OA1.N_CAT=ACOL1_ac and OA1.LO_CAT=ACOL1_ao) av1,
nvl (to_number (substr (ACOL2,1,25)),0) ac2, nvl (to_number (substr (ACOL2,26,25)),0) ao2, substr (ACOL2,51) av2
from
(select ac ACOL1_ac, ao ACOL1_ao,
(select rpad (T013.cat, 25) || rpad (T013.obj, 25) || OA013.value
from
table (CAST (MULTISET (select NO010.LO_CAT ac, NO010.LO_OBJ ao
from
the (select CUST (MULTISET (
select distinct NO008.HI_CAT ac, NO008.HI_OBJ ao
from
nom_nom NO002, obj_atr_dat OA004, obj_atr_dat OA006, nom_nom NO008
where (NO002.HI_CAT, NO002.HI_OBJ)=((FROM001.ac, FROM001.ao))
and NO002.HI_CAT=2
and NO002.LO_CAT=5
and OA004.N_CAT=5 and OA004.N_CAT=NO002.LO_CAT and OA004.LO_CAT=NO002.LO_OBJ and OA004.N_ATR=5 and OA004.value<=to_date('20230925') + 86399/86400
and OA006.N_CAT=5 and OA006.N_CAT=OA004.N_CAT and
A006.LO_CAT=OA004.LO_CAT and OA006.N_ATR=6 and OA006.value >= '20230925'
and NO008.LO_CAT=5 and NO008.LO_CAT=OA006.N_CAT and NO008.LO_OBJ=OA006.LO_CAT and NO008.HI_CAT=4)
as AQL_CO_TABLE) from dual) TMP011, nom_nom NO010
where NO010.LO_CAT=4 and NO010.LO_CAT=TMP011.cat and NO010.LO_OBJ=TMP011.obj and NO010.HI_CAT=1
and (NO010.HI_CAT, NO010.HI_OBJ)=((1, 11)))
as AQL_CO_TABLE)) T013, obj_atr_str OA013
where OA013.N_CAT (+)=T013.cat and OA013.LO_CAT (+)=T013.obj and OA013.N_ATR (+)=4 and rownum<=1) ACOL2
from
(select distinct NO017.HI_CAT ac, NO017.HI_OBJ ao
from
nom_nom NO015, nom_nom NO017
where (NO015.HI_CAT, NO015.HI_OBJ)=((1, 11))
and NO015.HI_CAT=1
and NO015.LO_CAT=3
and NO017.LO_CAT=3 and NO017.LO_CAT=NO015.LO_CAT and NO017.LO_OBJ=NO015.LO_OBJ and NO017.HI_CAT=2)
FROM001)

А вот на языке AQL этот запрос будет выглядеть так:

select
#.1,
#:5 where (#.5<=dt and #.6>=dt)^4 where (#^1=[1 11]).4
from [1 11]:3^2

Я думаю, что такой SQL-запрос напишет далеко не любой, даже опытный специалист в SQL, а вот написать тоже самое на AQL, как видите, на несколько порядков легче. 

Обратите внимание, что приведенная выше УС имеет 2 «путя» связи между Предприятием и Контрагентом: через Контрагента предприятия и через Сотрудника и Куратора контрагента. Поэтому и запрос может быть построен 2-мя способами: первый приведен выше и второй:

select
#:5 where (#.5<=dt and #.6>=dt)^2.1
#.4,
from [1 11]:4

Но эти запросы не идентичны: Первый запрос выводит всех контрагентов предприятия и для каждого контрагента выводит его куратора, второй выводит всех сотрудников предприятия и для каждого сотрудника выводит, курируемого им контрагента. То есть, если для контрагента еще не установлен куратор, то при первом варианте запроса в списке будет контрагент, но без куратора, а во втором варианте контрагент вообще «потеряется». Кроме того, если у одного куратора есть несколько контрагентов, то они тоже «потеряются». Поэтому второй вариант запроса неверен.

И еще одно замечание. В различных публикациях часто указывают, что список смежности очень затратен из-за необходимости использования большого количества джойнов в SQL-запросе или использования еще более затратных рекурсивных запросов: CONNECT BY PRIOR (в Oracle) или WITH RECURSIVE (в Postgres). На деле не все так страшно. Из моего опыта в реальных приложениях в одном запросе число уровней обычно составляет 3–4 и редко когда превышает 5.

Итак…Я объяснил, как устроен паровоз, теперь осталось объяснить, как он поедет без лошади.

Основным элементом системы является парсер и компилятор запросов, то, что превращает AQL-запрос в SQL-запрос. Понятно, что система имеет и процедурный язык, который содержит все инструменты по работе с данными типа COV, но именно компилятор SQL позволяет пользователю писать небольшие понятные иерархические запросы вместо зубодробительных запросов, как в примере выше. Разработка такого компилятора и стала сложнейшей задачей на пути разработки иерархической ИС. Обуздание оракловского оптимизатора стоило немало седых волос разработчикам, особенно, когда при переходе с 10-й версии Oracle на версию 11g, все запросы вдруг стали выполняться на порядок дольше. К слову, нам так и не удалось добиться приемлемого быстродействия от оптимизатора Oracle 11g и пришлось просто включить опцию оптимизатора от 10-й версии. В результате многолетнего труда был создан двухпроходный компилятор, который создавал оптимизированный SQL-запрос, содержащий кроме всего прочего инструкции и подсказки оптимизатору СУБД Oracle, так называемы «хинты» (hints). Причем ИС показала очень неплохое быстродействие. Например, приведенный в первой части статьи, AQL-запрос возвращающий данные по всем начислениям, удержаниям, долгам по кредитам и подотчетным суммам сотрудника (таблица 17 столбцов) выполняется около 1 с. (ИС содержит свыше 300 миллионов объектов, около 1.5 миллиардов атрибутов объектов и свыше 800 миллионов связей объектов). К слову сказать, SQL-запрос для такого AQL-запроса занимает несколько страниц.

Еще раз повторюсь, целью данной статьи не является реклама информационной системы, которую, как видите, я не называю, и, которая, к слову, успешно действует уже более 20-ти лет. Система использует СУБД Oracle 11g, который сейчас «не моден» в России. Я просто хотел показать, что иерархическая БД возможна, реально работает, и посмотреть, возможно ли ее перевести под «свободное» ПО, например, PostgreSQL, который, кстати, в России поддерживается сильнейшей командой Postgres Professional и даже имеет сертификат ФСТЭК. Sapienti sat.

Возможность перевода ИС с Oracle на PostgreSQL и будет темой третьей части статьи. На этом пути придется проделать значительный объем работы: создать аналогичную структура данных на Postgres«е, попробовать «сжует ли» Postgres Oracle-овские запросы, ну, а затем, в случае положительного результата, провести эксперименты по сравнению быстродействия, для чего нужно будет создать две аналогичные базы по Oracle и Postgres со значительным количеством записей (хотя бы миллион).

PS После опубликования первой части статьи были некоторые замечания, за которые я благодарю всех проявивших интерес к публикации. Также хочу прояснить некоторые вопросы. Первое: разработка данной системы началась где-то в 1994 году, а коммерческий продукт появился в 2001. Тогда о графовых базах и слышно не было. У нас сложилась определенная терминология, которая, возможно, немного отличается от современной. Второе: Данная система позволяет создавать информационные системы любого уровня сложности, включая ERP системы. Эта система аналог таких систем, как SAP R/3, Microsoft Dynamics AX, Oracle Applications, из российских Галактика и Парус.

[1] Код процедур и отдельных элементов экранных форм пишется на языке APL/AQL.

[2] В описании таблиц я привожу только основные поля и опускаю некоторые вспомогательные.

© Habrahabr.ru