Source

mrt-fixity / fixity-src / src / main / sql / create-mysql.txt

Full commit
DROP TABLE IF EXISTS `fixity_item`;
CREATE TABLE `fixity_item` (
  `itemkey` bigint NOT NULL AUTO_INCREMENT,
  `url` varchar(511) NOT NULL,
  `source` enum('merritt','file','web')
        NOT NULL,
  `size` bigint(20) DEFAULT NULL,
  `type` enum('ADLER-32','CRC-32','MD2','MD5','SHA-1','SHA-256','SHA-384','SHA-512') DEFAULT NULL,
  `value` varchar(255) DEFAULT NULL,
  `status` enum('verified','unverified','size-mismatch','digest-mismatch','system-unavailable','processing')
        NOT NULL DEFAULT 'unverified',
  `verified` timestamp NULL,
  `lastsize` bigint(20) NULL,
  `lastvalue` varchar(255) DEFAULT NULL,
	`note`  text DEFAULT NULL,
  `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` timestamp NULL,
  PRIMARY KEY (`itemkey`),
  KEY `url` (`url`),
  KEY `context` (`context`),
  KEY `status` (`status`),
  KEY `verified` (`verified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `fixity_context`;
CREATE TABLE `fixity_context` (
  `contextkey` bigint NOT NULL AUTO_INCREMENT,
  `itemkey` bigint NOT NULL,
  `context` varchar(255) NOT NULL,
  PRIMARY KEY (`contextkey`),
  KEY `context` (`context`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `fixity_history`;
CREATE TABLE `fixity_history` (
  `historykey` bigint NOT NULL AUTO_INCREMENT,
  `itemkey` bigint NOT NULL,
  `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `subject` varchar(255) DEFAULT NULL,
  `note` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`historykey`),
  KEY `subject` (`subject`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;