devonfw guide

Reporting and Creating output documents

After applying the business rules the resulting data can can be used to create reports and other output documents.

Creating such reports consists of three steps:

transform and filter the model data by using an embedded SQL database
determining difference to previously stored model (optional)
Template based reporting via
- Velocity templates (for textual output like e.g. HTML)
- Excel templates

SQL transformation and filtering

Database structure

After the business rules have been processed (or a Solicitor data model has been loaded via command line option -l) the model data is stored in a dynamically created internal SQL database.

For each type of model object a separate table is created. The tablename is the name of model object type written in uppercase characters. (E.g. type NormalizedLicense stored in table NORMALIZEDLICENSE)
All properties of the model objects are stored as strings in fields named like the properties within the database table. Field names are case sensitive (see note below for handling this in SQL statements).
An additional primary key is defined for each table, named ID_<TABLENAME>.
For all model elements that belong to some parent in the object hierarchy (i.e. all objects except ModelRoot) a foreign key field is added named PARENT_<TABLENAME> which contains the unique key of the corresponding parent

SQL queries for filtering and transformation

Each Writer configuration (see Writers and Reporting) includes a section which references SQL select statements that are applied on the database data. The result of the SQL select statements is made accessible for the subsequent processing of the Writer via the dataTable name given in the configuration.

Postprocessing of data selected from the database tables

Before the result of the SQL select statement is handed over to the Writer the following postprocessing is done:

a rowCount column is added to the result which gives the position of the entry in the result set (starting with 1).
Columns named ID_<TABLENAME> are replaced with columns named OBJ_<TABLENAME>. The fields of those columns are filled with the corresponding original model objects (java objects).

The result table column OBJ_<TABLENAME> gives access to the native Solicitor data model (java objects), e.g. in the Velocity writer. As this breaks the decoupling done via the SQL database using this feature is explicitly discouraged. It should only be used with high caution and in exceptional situations. The feature might be discontinued in future versions without prior notice.

Determining difference to previously stored model

When using the command line option -d Solicitor can determine difference information between two different data models (e.g. the difference between the licenses of the current release and a former release.) The difference is calculated on the result of the above described SQL statements:

First the internal reporting database is created for the current data model and all defined SQL statements are executed
Then the internal database is recreated for the "old" data model and all defined SQL statements are executed again
Finally for each defined result table the difference between the current result and the "old" result is calculated

To correctly correlate corresponding rows of the two different versions of table data it is necessary to define explicit correlation keys for each table in the SQL select statement. It is possible to define up to 10 correlation keys named CORR_KEY_X with X in the range from 0 to 9. CORR_KEY_0 has highest priority, CORR_KEY_9 has lowest priority.

The correlation algorithm will first try to match rows using CORR_KEY_0. It will then attempt to correlate unmatched rows using CORR_KEY_1 e.t.c.. Correlation will stop, when

all correlations keys CORR_KEY_0 to CORR_KEY_9 have been processed OR
the required correlation key column does not exist in the SQL select result OR
there are no unmatched "new" rows OR
there are no unmatched "old" rows

The result of the correlation / difference calculation is stored in the reporting table data structure. For each row the status is accessible if

The row is "new" (did not exist in the old data)
The row is unchanged (no changes in the field values representing the properties of the Solicitor data model)
The row is changed (at least one field corresponding to the Solicitor data model changed)

For each field of "changed" or "unchanged" rows the following status is available:

Field is "changed"
Field is "unchanged"

For each field of such rows it is further on possible to access the new and the old field value.

Sample SQL statement

The following shows a sample SQL statement showing some join over multiple tables and the use of correlations keys.

-- SPDX-License-Identifier: Apache-2.0
--
-- generate all NormalizedLicenses in denormalized form including all hierachical data  (allden -> "all denormalized")
select
    CONCAT(NVL(a."applicationName",'-'),NVL(ac."groupId",'-'),NVL(ac."artifactId",'-'),NVL(ac."version",'-'),NVL(l."normalizedLicense",'-')) as CORR_KEY_0,
    CONCAT(NVL(a."applicationName",'-'),NVL(ac."groupId",'-'),NVL(ac."artifactId",'-'),NVL(l."normalizedLicense",'-')) as CORR_KEY_1,
    CONCAT(NVL(a."applicationName",'-'),NVL(ac."groupId",'-'),NVL(ac."artifactId",'-'),NVL(ac."version",'-')) as CORR_KEY_2,
    CONCAT(NVL(a."applicationName",'-'),NVL(ac."groupId",'-'),NVL(ac."artifactId",'-')) as CORR_KEY_3,
	e.*,
	a.*,
	ac.*,
	l.*
from
	ENGAGEMENT e,
	APPLICATION a,
	APPLICATIONCOMPONENT ac,
	NORMALIZEDLICENSE l
where
	e.ID_ENGAGEMENT = a.PARENT_APPLICATION AND
	a.ID_APPLICATION = ac.PARENT_APPLICATIONCOMPONENT AND
	ac.ID_APPLICATIONCOMPONENT = l.PARENT_NORMALIZEDLICENSE
order by
	UPPER("ID_APPLICATION"), -- sort by ID so assuring we have the same order as defined in config
	UPPER("groupId"),
	UPPER("artifactId"),
	UPPER("version"),
	UPPER("effectiveNormalizedLicense"),
	UPPER("normalizedLicense")

Above example also shows how the case sensitive column names have to be handled within the SQL

Writers

The above described SQL processing is identical for all Writers. Writers only differ in the way how the output document is created based on a template and the reporting table data obtained by the SQL transformation.

Velocity Writer

The Velocity Writer uses the Apache Velocity Templating Engine to create text based reports. The reporting data tables created by the SQL transformation are directly put to the into Velocity Context.

For further information see the

Velocity Documentation
The Solicitor JavaDoc (which also includes details on how to access the diff information for rows and fields of reporting data tables)
The samples included in Solicitor

Excel Writer

Using Placeholders in Excel Spreadsheets

Within Excel spreadsheet templates there are two kinds of placeholders / markers possible, which control the processing:

Iterator Control

The templating logic searches within the XLSX workbook for fields containing the names of the reporting data tables as defined in the Writer configuration like e.g.:

#ENGAGEMENT#
#LICENSE#

Whenever such a string is found in a cell this indicates that this row is a template row. For each entry in the respective resporting data table a copy of this row is created and the attribute replacement will be done with the data from that reporting table. (The pattern #…# will be removed when copying.)

Attribute replacement

Within each row which was copied in the previous step the templating logic searches for the string pattern $someAttributeName$ where someAttributeName corresponds to the column names of the reporting table. Any such occurrence is replaced with the corresponding data value.

Representation of Diff Information

In case that a difference processing (new vs. old model data) was done this will be represented as follows when using the XLS templating:

For rows that are "new" (so no corresponding old row available) an Excel note indicating that this row is new will be attached to the field that contained the #…# placeholder.
Fields in non-new rows that have changed their value will be marked with an Excel note indicating the old value.