Маленькие радости структурированных метаданных

Несколько дней назад я помогал в проекте интеграции Ultima Businessware и другой учетной системы. Кроме прочего, я хотел получить список того, что должно синхронизироваться в системе не только «из головы», но и каким-то объективным способом.
Что получилось — под катом.

Для начала — немного о структурированных метаданных.
Я надеюсь, читатель имеет представление о модели данных в Ultima Businessware (иначе зачем бы это все читать ?). Если нет, то отсылаю к моим предыдущим постам и выдержкам из документации на сайте платформы.

Метаданные — это данные, состоящие из информации о структуре данных. В том числе и самих себя.
Под структурированными метаданными я понимаю нормализованные (как минимум до второй формы) таблицы или представления (view).

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


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


Ну и аналогичные структуры подготовлены для документов:


И для итогов:

Думаю, читатель простит мне опущенные детали на уровне таблиц.

Зачем это все ?

Действительно, и что с того, что все разложено. Ну кроме эстетического удовольствия, это сильно сильно упрощает решение многих задач, возникающих в реальной практике.

Вернемся к моей задачке про интеграцию. Мне нужен сколько-нибудь объективный способ определения того, что же мне требуется синхронизировать с той самой системой. По внешним условиям необходимо синхронизировать те итоги, которые входят в баланс. Это те итоги, которые отмечены как IS_DOUBLE_ENTRY (поддерживается для него двойная запись или нет). Соответственно, я должен синхронизировать и справочники, на которые ссылаются измерения этих итогов. Если быть точным, то мне надо было составить список того, что будет синхронизироваться, дальше мы должны были его обсуждать и сокращать.

Так что, легко и непринужденно я набросал вот такой запрос (какие справочники являются измерениями итогов с названиями на русском языке и список названий итогов в которых они собственно участвуют как измерения:

select a.*, MT.CAPTION as "Dictl18nName" from 
(
  select d.id "DictID", d.name "DictSysName", listagg(mt.caption, ',') within group (order by t.name) as "DimensionOf" 
  from KERNEL.VTOTAL_DIMENSIONS td join KERNEL.VTOTALS t on t.id=TD.TOTAL_OBJ_ID
  join KERNEL.VDICTIONARIES d on d.id=TD.REF_DICT_OBJ_ID
  join KERNEL.VMETADATA_TRANSLATIONS mt on MT.LANG_ID=2 and MT.REF_OBJ_ID=t.id
  where  T.IS_DOUBLE_ENTRY = 1 and t.id not in (select MO.REF_OBJ_ID from    KERNEL.VMETADATA_TAGS_TO_OBJECTS mo where MO.TAG_VALUE='warranty')
  group by d.id, d.name
) a 
join KERNEL.VMETADATA_TRANSLATIONS mt on MT.LANG_ID=2 and MT.REF_OBJ_ID=a.id

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

Вот такие вот маленькие радости.
Что еще я делал используя запросы? Посчитал строчки в коде.
Нашел все справочники, в которых есть заданное поле.
Нашел справочники, на которые не ссылается ни один документ.
Или вот часто возникающая ситуация — «а эта таблица зачем ?»

Да легко, незачем она нам, если она не описана в конфигурации. если нужна — опиши в конфигурации, заодно с комментариями зачем она. Да и вообще, давайте найдем все такие таблички, которые наплодили разработчики для каких-то своих делишек:

select * from all_tables where owner='ULTIMA' and table_name not in (
select TABLE_NAME 
from KERNEL.VDICTIONARIES
union all
select TABLE_NAME
from KERNEL.VDOC_TYPES
union all
select TABLE_NAME
from KERNEL.VTABLE_PART_TYPES
union all
select TABLE_NAME
from KERNEL.VLINK_TABLES
)
order by table_name

Еще один пример борьбы с мусором в конфигурации — найдем и безжалостно удалим команды над справочниками, которыми не пользовались с нового года:

select dc.id, dc.caption 
from KERNEL.VDICT_COMMANDS dc 
where DC.SCRIPT_OBJ_ID not in 
(
    select script_obj_id
    from kernel.stat_command_events e
    where E.START_DT >= trunc(sysdate, 'YYYY')
)

Невероятно полезная штука, сильно упрощающая жизнь.

Надеюсь, понятно, что имея структурированные метаданные и язык SQL как инструмент анализа можно проделывать весьма интересные вещи. Попробуйте сами прикинуть, что бы вы могли сделать, имея под рукой такой инструмент!