Creating a header with custom model information in erwin generated ddl

Question

What is the most practical, automated way to generate a header comment with model information such as title, author, model version, db version, create and last modification date in an Erwin generated ddl script taken from the physical or better logical model. Nice would be having the same information in a "MODELINFO" sql table as ddl, additionally to the header.

Answer 1

@matz3. It sounds like you are talking about defining what erwin documentation calls "Pre and Post Scripts."

There are several ways to do this in erwin Data Modeler depending on which version you are using. In my enterprise, we're on version 2021 R1; YMMV. We use pre- and post-scripts to place Liquibase changeset wrappers (which are formatted as SQL comments) around each table create or drop, as well as to enforce certain patterns for object creation/drop order.

Macros can be used to reference various OOTB attribute values throughout the model such as those you've mentioned and pull those into comments in your "forward engineered" generated DDL.

For attributes not already available within properties of the various levels of object hierarchy (Model, Subject Area, Schema, Table, Column, Relationship, Index, etc.), you can either use the Extended Notes (not available on all objects) to create some custom attributes, or you can create User Defined Properties (UDPs, again, not available for all objects/classes).

Once you've built the attributes and assigned values or located the macros for the values you want to include in your DDL comment, you will either invoke the Template Editor (Actions > Forward Engineer > Templates) to modify the TLX scripts in the backend of erwin which build the DDL, or the Script Template Editor (Model > Script Templates), which I prefer for greater visibility to users of the model.

With Script Templates, you define Type (Model Level or Table Level) and Generate As (Pre-Creation or Post-Creation). Within the Code tab, you can invoke the Macro Toolbox to access all the pre-configured macros to reference object properties elsewhere in the model.

Once you've built all of this out the way you want it, you can save this model as a Template in your erwin Mart repository and then built all future models from that template with these built-in pre- / post-script features.

This guide goes into detail about how to use erwin's TLX scripting language. TLX is considered an advanced feature for an experienced user of erwin DM, something to pursue if you can't get what you need from the Script Template editor GUI.

I'm not certain about the last part of your question concerning MODELINFO table; are you referencing a table in the backend erwin Mart repository database, or a table that you update in your target database? For the 2nd case, you can likely use TLX to generate an INSERT statement which references the pre-script text string.