create_tables.sql

-- Tracking database schema for the DETECTOR (checksum_validate/).
-- This is the tool's OWN MySQL/MariaDB database -- NOT the Dataverse database.
--
-- One-time setup (adjust names/password to match config.ini):
--
--   CREATE DATABASE dataverse_tdr CHARACTER SET utf8mb4;
--   CREATE USER 'dataverse_tdr'@'localhost' IDENTIFIED BY '<password>';
--   GRANT ALL PRIVILEGES ON dataverse_tdr.* TO 'dataverse_tdr'@'localhost';
--   mysql dataverse_tdr < create_tables.sql
--
-- NOTE: the two tables reference each other (circular foreign keys), so rows
-- cannot be deleted naively: clear validation_status.error_id first, then
-- delete from errors, then from validation_status.

-- One row per datafile ever seen by a validation run. last_check drives the
-- min_frequency skip logic: a re-run skips datasets whose files were all
-- checked recently, which is what makes interrupted multi-day runs cheap to
-- resume. Rows are inserted on first validation and updated in place after.
CREATE TABLE `validation_status` (
  `datafileid` int PRIMARY KEY,             -- Dataverse datafile (dvobject) id
  `datasetid` int,                          -- owning dataset id (skip decision is per dataset)
  `doi` varchar(255) NOT NULL,              -- dataset persistent id at time of validation
  `storageidentifier` varchar(255) NOT NULL,-- e.g. s3://<bucket>:<key>
  `entry_date` timestamp DEFAULT (now()),   -- first time this datafile was validated
  `last_check` timestamp DEFAULT (now()),   -- most recent validation of this datafile
  `failure` int DEFAULT 0,                  -- 0 = checksum valid, 1 = invalid at last check
  `error_id` int DEFAULT null               -- current open error, if failure = 1
);

-- Fixity-failure history. A row is created when a datafile FIRST fails (or
-- fails again after recovering); resolution_date is set when a later run finds
-- the file valid again. Rows are never deleted -- this is the audit trail.
CREATE TABLE `errors` (
  `id` int PRIMARY KEY AUTO_INCREMENT,
  `message` varchar(255) NOT NULL,          -- "status: <status>, <server error message>"
  `detection_date` timestamp DEFAULT (now()),
  `resolution_date` timestamp DEFAULT (now()) null,
  `datafileid` int NOT NULL
);

ALTER TABLE `validation_status` ADD FOREIGN KEY (`error_id`) REFERENCES `errors` (`id`);

ALTER TABLE `errors` ADD FOREIGN KEY (`datafileid`) REFERENCES `validation_status` (`datafileid`);