This document aims to provide an overview of creating and modifying DBDoc Model Reporting templates, as used by MySQL Workbench.
The MySQL Workbench DBDoc Model Reporting system is based on the Google Template System. This document does not attempt to explain the Google Template System in detail. The Google document How To Use the Google Template System provides a useful overview of how the Google Template System works.
The templates employed by the DBDoc Model Reporting system are text
files that contain Markers
. These text files are
processed by the template system built into MySQL Workbench, and the
markers replaced by actual data. The output files are then
generated. It is these output files, typically HTML or text, that
are then viewed by the user.
Markers can be of six types:
Template Include
Comment
Set delimiter
Pragma
Variable
Section start and Section end
The last two are the most commonly used in MySQL Workbench templates and these important markers will be briefly described in the following sections.
Variables
The use of variables in the templates is straightforward. Any variables denoted by markers in the template file, will be replaced by their corresponding data, prior to the output file being generated. The mapping between variables and their corresponding data is stored by MySQL Workbench in what is known as a Data Dictionary. In the data dictionary the variable name is the key and the variable's corresponding data is the value. The data dicionaries are built by MySQL Workbench and filled with the data contained in the model being processed.
By way of example, the following code snippet shows part of a template file:
Total number of Schemata: {{SCHEMA_COUNT}}
In the generated output file the variable {{SCHEMA_COUNT}} will be replaced by the number of schemata in the model:
Total number of Schemata: 2
A variable can appear as many times as required in the template file.
Sections
Sections are used to perform iteration in the templates. When MySQL Workbench exchanges the variables in a section for data it will do so iteratively, using all data in the data dictionary in which the variable is defined. MySQL Workbench builds the data dictionaries according to the model currently being processed.
Again, this is best illustrated by example:
{{#SCHEMATA}} Schema: {{SCHEMA_NAME}} {{/SCHEMATA}}
In the previous code snippet the section start is indicated by
the {{#SCHEMATA}}
marker. The end of the
section is indicated by the {{/SCHEMATA}}
marker. When the template is processed, MySQL Workbench will note
the section and iterate the section until the variable data for
{{SCHEMA_NAME}}
in the corresponding data
dictionary is exhausted. For example, if the model being
processed contains two schemata, the output for the section
might resemble the following:
Schema: Airlines Schema: Airports
That is, the model contains two schemata, Airlines and Airports.
Data Dictionaries
It is important to understand the relationship between sections and data dictionaries in more detail. In a data dictionary the key for a variable is the variable name, a marker. The variable value is the variable's data. The entry for a section in a data dictionary is different. For a section entry in a data dictionary, the key is the section name, the marker. However, the value associated with the key is a list of data dictionaries. In MySQL Workbench each section is usually associated with a data dictionary. You can think of a section as activating its associated dictionary (or dictionaries).
When a template is processed, data dictionaries are loaded in a hierarchical pattern, forming a tree of data dictionaries. This is illustrated by the following table:
Data Dictionary | Loads Data Dictionary |
MAIN | SCHEMATA |
SCHEMATA | TABLES, COLUMNS (Detailed is true), FOREIGN_KEYS (Detailed is true), INDICES (Detailed is true) |
TABLES | REL_LISTING, INDICES_LISTING, COLUMNS_LISTING, TABLE_COMMENT_LISTING, DDL_LISTING |
COLUMNS_LISTING | COLUMNS (Detailed is false) |
REL_LISTING | REL (Detailed is false) |
INDICES_LISTING | INDICES (Detailed is false) |
The root of the tree is the main dictionary. Additional dictionaries are then loaded from the root to form the dictionary tree.
If a template has no sections in it, then any variables used in the template will be looked up in the main dictionary. If a variable is not found in the main dictionary (which can be thought of as associated with the default, or main, section) then no data will be generated in the output file for that marker.
Evaluation of variables
The tree structure of the data dictionaries is important when it comes to evaluation of variables. As variables are defined in data dictionaries, their associated value only has meaning when that particular data dictionary is active, and that means when the section associated with that data dictionary is active. When a variable lookup occurs, the system will check the data dictionary associated with the current section. If the variable value can be found there the replacement is made. However, if the variable's value is not found in the current data dictionary then the parent data dictionary will be checked for the variable's value and so on up the tree until the main data dictionary, or root, is reached.
This can best be illustrated by an example. Assume we want to display the names of all columns in a model. Consider the following template as an attempt to achieve this:
Report ------ Column Name: {{COLUMN_NAME}}
This template will produce no output, even for a model that contains
many columns. In this example the only data dictionary active is the
main dictionary. COLUMN_NAME
however is stored in
the COLUMNS
data dictionary, which is associated
with the COLUMNS
section.
With this knowledge the template can be improved as follows:
Report ------ {{#COLUMNS}} Column Name: {{COLUMN_NAME}} {{/COLUMNS}}
This still does not produce output. Referring to the table
Data Dictionary
Hierarchy Tree explains why. The COLUMNS
data dictionary has the parent dictionary
COLUMNS_LISTING
.
COLUMNS_LISTING
has the parent
TABLES
, which has the parent
SCHEMATA
, whose parent is the main dictionary.
Remember in order for a dictionary to be involved in variable
lookup, its associated section must currently be active.
So in order to achieve the desired output we would need the template to be something like the following:
Report ------ {{#SCHEMATA}} {{#TABLES}} {{#COLUMNS_LISTING}} {{#COLUMNS}} Column Name: {{COLUMN_NAME}} {{/COLUMNS}} {{/COLUMNS_LISTING}} {{/TABLES}} {{/SCHEMATA}}
The following template is the same, but with explanatory comments added:
Report ------ {{! Main dictionary active}} {{#SCHEMATA}} {{! SCHEMATA dictionary active}} {{#TABLES}} {{! TABLES dictionary active}} {{#COLUMNS_LISTING}} {{! COLUMNS_LISTING dictionary active}} {{#COLUMNS}} {{! COLUMNS dictionary active}} Column Name: {{COLUMN_NAME}} {{! COLUMN_NAME variable is looked-up, and found, in COLUMNS data dictionary}} {{/COLUMNS}} {{/COLUMNS_LISTING}} {{/TABLES}} {{/SCHEMATA}}
Imagine now that for each column name displayed you also wanted to display its corresponding schema name, the template would look like this:
Report ------ {{#SCHEMATA}} {{#TABLES}} {{#COLUMNS_LISTING}} {{#COLUMNS}} Schema Name: {{SCHEMA_NAME}} Column Name: {{COLUMN_NAME}} {{/COLUMNS}} {{/COLUMNS_LISTING}} {{/TABLES}} {{/SCHEMATA}}
When variable lookup is performed for SCHEMA_NAME
the COLUMNS
dictionary will be checked. As the
variable is not found there the parent dictionary will be checked,
COLUMNS_LISTING
, and so on until the variable is
eventually found, where it is held, in the
SCHEMATA
dictionary.
If there are multiple schemata in the model the outer section will
be iterated over a matching number of times, and
SCHEMA_NAME
will accordingly have the correct
value on each iteration.
It's important to always consider which dictionary needs to be active (and which parents) for a variable to be evaluated correctly. In the following section you will find a table that helps you identify section requirements.