Enhancing SchemaCrawler Output with Custom Table

[escalate]

Hello @sualeh ,

I would like to use SchemaCrawler for the output of database schemas.

My problem is that I want to document for each column of a table whether the column contains personal data in the sense of the GDPR.

I use MariaDB version 10.4 and have created a new table called "gdpr_column_information" within the database called "testing".

CREATE TABLE `gdpr_column_information` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `table_schema` varchar(64) NOT NULL,
  `table_name` varchar(64) NOT NULL,
  `column_name` varchar(64) NOT NULL,
  `is_sensitive` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

In addition to this table, there are a number of other tables within the "testing" database. The columns of these tables are documented in "gdpr_column_information".

According to this article https://www.schemacrawler.com/data-dictionary-extensions.html, I thought I could simply write additional attributes for a column in the output.

I created a schemacrawler.properties file with the following content.

select.ADDITIONAL_INFO.ADDITIONAL_COLUMN_ATTRIBUTES=\
SELECT \
  i.TABLE_CATALOG, \
  i.TABLE_SCHEMA, \
  i.TABLE_NAME, \
  i.COLUMN_NAME, \
  g.is_sensitive \
FROM \
  INFORMATION_SCHEMA.COLUMNS i \
JOIN testing.gdpr_column_information g \
ON i.TABLE_SCHEMA = g.table_schema \
AND i.TABLE_NAME = g.table_name \
AND i.COLUMN_NAME = g.column_name

With the following command I wanted to create an output in JSON format that contains the attribute "is_sensitive".

schemacrawler \
--log-level=INFO \
--config-file=schemacrawler.properties \
--server=mysql \
--host=mariadb \
--database=testing \
--user=testing \
--password=REDACTED \
--command=schema \
--info-level=maximum

Unfortunately, the attribute "is_sensitive" is not included in the JSON. What am I doing wrong?

Best regards
Felix

[sualeh]

@escalate I will have to look at your logs to see what is going on. Please could you provide me with your logs, using these instructions?

[sualeh]

@escalate I was able to reproduce and get it to work. The MySQL/ MariaDB JDBC driver does not report the table catalog and schema the same way as INFORMATION_SCHEMA.COLUMNS, so you will need to modify your join like this:

select.ADDITIONAL_INFO.ADDITIONAL_COLUMN_ATTRIBUTES=\
SELECT  \
  i.TABLE_SCHEMA AS TABLE_CATALOG, \
  NULL AS TABLE_SCHEMA, \
  i.TABLE_NAME,  \
  i.COLUMN_NAME,  \
  g.is_sensitive \
FROM  \
  INFORMATION_SCHEMA.COLUMNS i  \
JOIN testing.gdpr_column_information g  \
ON i.TABLE_SCHEMA = g.table_schema  \
AND i.TABLE_NAME = g.table_name  \
AND i.COLUMN_NAME = g.column_name

and your SchemaCrawler command will look something like this:

schemacrawler \
--log-level=OFF \
--config-file=schemacrawler.properties \
--server=mysql \
--host=mariadb \
--database=testing \
--user=testing \
--password=REDACTED \
--command=serialize \
--info-level=maximum

[escalate]

Thank you @sualeh for your help. It now also works with the JSON output.

[sualeh]

Excellent. Thanks.