The little 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 “out of my head”, but also in some objective way.
    What happened - under the cut.

    For starters, a little bit about structured metadata.
    I hope the reader has an idea of ​​the data model in Ultima Businessware (otherwise why would it all read?). If not, then refer to my previous posts and excerpts 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 to the second form) tables or views.

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


    Fortunately, we took care of the nerves of the application developers and covered up the implementation details with representations.
    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 for the omitted details at the table level.

    Why is this all?


    Indeed, and what with the fact that everything is laid out. Well, apart from aesthetic pleasure, this greatly simplifies the solution of many problems that arise in real practice.

    Let's get back to my integration puzzle. I need a somewhat objective way to determine what I need to synchronize with the same system. According to external conditions, it is necessary to synchronize those results that are included in the balance sheet. These are the totals that are marked as IS_DOUBLE_ENTRY (double entry is supported for it or not). Accordingly, I must synchronize the directories referenced by the measurements of these totals. To be precise, I had to make a list of what will be synchronized, then we had to discuss and shorten it.

    So, easily and naturally, I sketched this query (which reference books are measurements of totals with names in Russian and a list of the names of totals in which they actually participate as measurements:
    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 load the diagrams above with unnecessary details, so I’ll explain that the METADATA_TRANSLATIONS table contains translations of the names of the properties and the objects themselves. Well, in addition, I discarded objects that include guarantees (I knew this beforehand).

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

    Yes, it’s easy, we don’t need it if it is not described in the configuration. if necessary - describe in the configuration, along with comments why it is. Anyway, let's find all the tablets that the developers have spawned for some of their tricks:
    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 combating garbage in the configuration is to find and ruthlessly delete commands over 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 simplifying life.

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

    Also popular now: