MySQL :: MySQL Workbench Manual :: 9.7 Customizing DBDoc Model Reporting Templates

MySQL Workbench Manual / Database Design and Modeling / Customizing DBDoc Model Reporting Templates

9.7 Customizing DBDoc Model Reporting Templates

9.7.1 Supported Template Markers
9.7.2 Creating a Custom Template

This section provides 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 discussion does not attempt to explain the Google Template System in detail. For a useful overview of how the Google Template System works, see the Google document, How To Use the Google Template System.

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 any of the following 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 are briefly described in the following sections.

Variables

Variables denoted by markers in the template file are replaced by their corresponding data prior to the generated output file. The mapping between variables and their corresponding data is stored by MySQL Workbench in a data dictionary. In the data dictionary, the variable name is the key and the variable's corresponding data is the value. MySQL Workbench builds the data dictionaries and fills it with the data contained in the processed model.

By way of example, the following code snippet shows part of a template file:
```
Total number of Schemas: {{SCHEMA_COUNT}}
```
In the generated output file, the variable {{SCHEMA_COUNT}} is replaced by the number of schemata in the model:
```
Total number of Schemas: 2
```
A variable can appear multiple times 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 does 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.

Consider the following code snippet:
```
{{#SCHEMATA}}
Schema: {{SCHEMA_NAME}}
{{/SCHEMATA}}
```
In the preceding snippet, the section start and end are indicated by the {{#SCHEMATA}} and {{/SCHEMATA}} markers. When MySQL Workbench processes the template, it notes the section and iterates it until the variable data for {{SCHEMA_NAME}} in the corresponding data dictionary is exhausted. For example, if the model being processed contains two schemas, the output for the section might resemble the following:
```
Schema: Airlines
Schema: 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.

Table 9.2 Data Dictionaries Tree

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 loaded from the root to form the dictionary tree.

Note

If a template has no sections, any variables used in the template are 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), no data is generated in the output file for that marker.

Evaluation of variables

The tree structure of the data dictionaries is important with respect to variable evaluation. As variables are defined in data dictionaries, their associated values have meaning only 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 checks 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, the parent data dictionary is checked for the variable's value, and so on up the tree until the main data dictionary, or root, is reached.

Suppose that 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 produces no output, even for a model that contains one or more columns. In this example, the only data dictionary active is the main dictionary. However, COLUMN_NAME 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. To see why, see Table 9.2, “Data Dictionaries Tree”. 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 that for a dictionary to be involved in variable lookup, its associated section must currently be active.

To achieve the desired output, the template must 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 is 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 is iterated over a matching number of times, and SCHEMA_NAME accordingly has the correct value on each iteration.

It's important to always consider which dictionary must be active (and which parents) for a variable to be evaluated correctly. The following section has a table that helps you identify section requirements.

PREV HOME UP NEXT