Add an ETL logging facility

[pbchase]

Steal ETL logging code from STP and bend it toward the REDCap worldview

[pbchase]

Here are some thoughts from @ChemiKyle and @pbchase. There are flaws here, but it is useful.

Log table schema

CREATE TABLE `etl_log` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `log_date` datetime NOT NULL,
  `script_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `target_uri` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, -- e.g. https://redcap.ctsi.ufl.edu/redcap/api/, smtp://smtp.ufl.edu, mysql://user_name@example.com:3306/schema
  `table_written` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `project_id_written` int CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  -- `record_id` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `primary_key` varchar(63) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `record_level_data` json DEFAULT NULL,
  `level` enum('INFO','DEBUG','ERROR') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `log_date` (`log_date`),
  KEY `script_name` (`script_name`),
  KEY `script_run_time` (`script_run_time`),
  KEY `database_written` (`database_written`),
  KEY `ufid` (`ufid`),
  KEY `primary_key` (`primary_key`),
  KEY `level` (`level`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Examples

REDCap API write

• target_uri - https://redcap.ctsi.ufl.edu/redcap/api/
• table_written - NULL
• project_id_written - 12345
• primary_key - record_id
• record_level_data - json object of key-value pairs
• level - INFO

Write to redcap_user_information

• target_uri - mysql://redcap@mysql.example.com:3306/redcap_user_information
• table_written - redcap_user_information
• project_id_written - NULL
• primary_key - ui_id? username?
• record_level_data - json object of key-value pairs
• level - INFO

Write to redcap_projects

• target_uri - mysql://redcap@mysql.example.com:3306/redcap_projects
• table_written - redcap_projects
• project_id_written - project_id
• primary_key - project_id
• record_level_data - json object of key-value pairs
• level - INFO

Write to redcap_project_invoices

• target_uri - mysql://redcap@mysql.example.com:3306/redcap_project_invoices
• table_written - redcap_project_invoices
• project_id_written - project_id
• primary_key - project_id
• record_level_data - json object of key-value pairs
• level - INFO

Write to redcap_metadata

• target_uri - mysql://redcap@mysql.example.com:3306/redcap_metadata
• table_written - redcap_metadata
• project_id_written - project_id
• primary_key - project_id
• record_level_data - json object of key-value pairs
• level - INFO

Write to email

• target_uri - smtp://smtp.ufl.edu
• table_written - NULL
• project_id_written - NULL or project_id of project referenced in message
• primary_key - username of recipient? email address of recipient?
• record_level_data - json object of key-value pairs including TO, SUBJECT, and BODY among them
• level - INFO

[pbchase]

Here's the product of the 2022-03-30 convo between @pbchase, @ChemiKyle, and @mbentz-uf

CREATE TABLE `rcc_detail_log` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `log_date` datetime NOT NULL,
  `script_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `target_uri` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, -- e.g. https://redcap.ctsi.ufl.edu/redcap/api/, smtp://smtp.ufl.edu, mysql://user_name@example.com:3306/schema
  `table_written` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, -- e.g. ...
  `project_id_written` int CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  -- `record_id` varchar(8) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `primary_key` varchar(63) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL, -- REDCap Project record_id or table PK
  `record_level_data` json DEFAULT NULL,
  `level` enum('INFO','DEBUG','ERROR') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `log_date` (`log_date`),
  KEY `script_name` (`script_name`),
  KEY `target_uri` (`target_uri`),
  KEY `table_written` (`table_written`),
  KEY `project_id_written` (`project_id_written`),
  KEY `primary_key` (`primary_key`),
  KEY `level` (`level`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

-- sftp://user@example.com/path/filename (??)
CREATE TABLE `rcc_job_log` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `log_date` datetime NOT NULL,
  `script_name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `script_run_time` datetime NOT NULL,
  `job_summary_data` text DEFAULT NULL,
  `job_duration` double not null,
  `level` enum('SUCCESS','DEBUG','ERROR') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `log_date` (`log_date`),
  KEY `script_name` (`script_name`),
  KEY `script_run_time` (`script_run_time`),
  KEY `level` (`level`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

[pbchase]

Addressed by PR #42