DB with Revision History

README.md

DB with Revision History

Links

• https://gist.github.com/reduardo7/e5846b17eba288006579fc35269f3e96
• https://stackoverflow.com/a/70596143/717267

script.sql

-- Tables

DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `myTable`;
CREATE TABLE IF NOT EXISTS `myTable` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `title` varchar(32) NOT NULL,
  `description` varchar(2048) DEFAULT NULL,
  `edited_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `title` (`title`) USING BTREE,
  KEY `myTalbe_users_edited_by_fk` (`edited_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `myTable_history`;
CREATE TABLE IF NOT EXISTS `myTable_history` (
  `id` int(10) UNSIGNED NOT NULL COMMENT 'ID',
  `version` int(10) UNSIGNED NOT NULL,
  `title` varchar(32) NOT NULL,
  `description` varchar(2048) DEFAULT NULL,
  `edited_by` int(10) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `history_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`version`) USING BTREE,
  KEY `title` (`title`),
  KEY `history_users_edited_by_fk` (`edited_by`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `myTable`
  ADD CONSTRAINT `myTalbe_users_edited_by_fk` FOREIGN KEY (`edited_by`) REFERENCES `users` (`id`) ON UPDATE CASCADE;
ALTER TABLE `myTable_history`
  ADD CONSTRAINT `history_users_edited_by_fk` FOREIGN KEY (`edited_by`) REFERENCES `users` (`id`);

-- Triggers

DROP TRIGGER IF EXISTS myTable_insert_history;
DROP TRIGGER IF EXISTS myTable_update_history;
DROP TRIGGER IF EXISTS myTable_delete_history;

DELIMITER //

CREATE TRIGGER myTable_insert_history AFTER INSERT ON myTable
FOR EACH ROW
BEGIN
  INSERT INTO myTable_history (
      `id`
    , `version`
    , `title`
    , `description`
    , `edited_by`
    , `created_at`
    , `updated_at`
  ) VALUES (
      NEW.id
    , 0
    , NEW.title
    , NEW.description
    , NEW.edited_by
    , NEW.created_at
    , NEW.updated_at
  );
END //

CREATE TRIGGER myTable_update_history AFTER UPDATE ON myTable
FOR EACH ROW
BEGIN
  INSERT INTO myTable_history (
      `id`
    , `version`
    , `title`
    , `description`
    , `edited_by`
    , `created_at`
    , `updated_at`
  )
  SELECT
      NEW.id
    , MAX(`version`) + 1
    , NEW.title
    , NEW.description
    , NEW.edited_by
    , NEW.created_at
    , NEW.updated_at
  FROM myTable_history
  WHERE id = OLD.id;
END //

CREATE TRIGGER myTable_delete_history AFTER DELETE ON myTable
FOR EACH ROW
BEGIN
  INSERT INTO myTable_history (
      `id`
    , `version`
    , `title`
    , `description`
    , `edited_by`
    , `created_at`
    , `updated_at`
    , `deleted_at`
  )
  SELECT
      OLD.id
    , MAX(`version`) + 1
    , OLD.title
    , OLD.description
    , OLD.edited_by
    , OLD.created_at
    , OLD.updated_at
    , NOW()
  FROM myTable_history
  WHERE id = OLD.id;
END //

DELIMITER ;