Small joys of structured metadata

A few days ago I helped with the integration project of Ultima Businessware and another accounting system. Among other things, I wanted to get a list of what should be synchronized in the system not only “from the head”, but also in some objective way.
What happened - under the cut.

For a start - a little about structured metadata.
I hope the reader has an understanding of the data model in Ultima Businessware (otherwise why would all this read?). If not, then refer to my previous posts and extracts from the documentation on the platform website.

Metadata is data consisting of information about the data structure. Including themselves.
By structured metadata, I mean normalized (at least until the second form) a table or view.

So, we have directories that have fields, some of which may refer to other directories.
We have totals, measurements of which refer to reference books.
We have documents that consist of caps and tabular parts, which in turn consist of fields, some of which can be references to reference books.
Additionally, the values ​​of the properties of reference books can have values ​​in several languages, the names of reference books, documents, and all other objects can (and have) meanings in several languages ​​(in the basic delivery in Russian and English).
In addition, all configuration components are versioned. As a result, the following structure is used to describe reference books and their links in the database:

')
Fortunately, we took care of the nerves of the application developers and covered the implementation details with views.
So at the presentation level, the diagram looks simpler.


Well, similar structures are prepared for documents:


And for the results:

I think the reader will forgive me the lowered details at the table level.

Why is this all?

Indeed, so what if everything is laid out. Well, except for aesthetic pleasure, it greatly greatly simplifies the solution of many problems arising in real practice.

Let's return to my problem about integration. I need some kind of objective way of determining what I need to synchronize with the very system. Under external conditions it is necessary to synchronize the totals that are included in the balance. These are the totals that are marked as IS_DOUBLE_ENTRY (double entry is supported for it or not). Accordingly, I have to synchronize the references to which the measurements of these totals refer. To be precise, I had to make a list of what will be synchronized, then we had to discuss and shorten it.

So, I easily and naturally sketched such a request (which directories are measurements of totals with titles in Russian and a list of names of totals in which they actually participate as dimensions:

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 

I did not download the diagrams above with unnecessary details, so I will explain - the table METADATA_TRANSLATIONS contains translations of the names of the properties and the objects themselves. Well, in addition, I dropped the objects that relate to the guarantee (I knew this in advance).

These are the little joys.
What else did I do using queries? I counted the lines in the code.
I found all the directories in which there is a given field.
Found references that are not referenced by any document.
Or here is a frequently occurring situation - “why this table?”

Yes it is easy, there is no need for it, if it is not described in the configuration. if necessary - describe in the configuration, along with comments, why it is. And in general, let's find all such tablets that the developers have spawned for some of their work:

 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 

Another example of dealing with garbage in the configuration is to find and mercilessly remove commands on directories that have not been used since the new year:

 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') ) 

Incredibly useful thing, greatly simplifies life.

I hope it is clear that having structured metadata and the SQL language as an analysis tool you can do some very interesting things. Try to figure out for yourself what you could do with such a tool at hand!