Notes

Blocklist MySQL Create Table

DROP TABLE IF EXISTS blocklist.ips;
CREATE TABLE `ips` (
  `ip` varchar(15) NOT NULL,
  `net` tinyint(3) unsigned NOT NULL,
  `date` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `hits` mediumint(8) unsigned NOT NULL DEFAULT '1',
  `expiration` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`ip`,`net`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Blocklist MySQL Triggers

DROP TRIGGER IF EXISTS upd_ips_expiration;
delimiter //
CREATE TRIGGER upd_ips_expiration BEFORE UPDATE ON ips
FOR EACH ROW
BEGIN
  IF NEW.hits > 10 THEN
    SET NEW.expiration = DATE_ADD(NEW.date, INTERVAL factorial(10) MINUTE);
  ELSE
    SET NEW.expiration = DATE_ADD(NEW.date, INTERVAL factorial(NEW.hits) MINUTE);
  END IF;
END;//
delimiter ;


DROP TRIGGER IF EXISTS ins_ips_expiration;
delimiter //
CREATE TRIGGER ins_ips_expiration BEFORE INSERT ON ips
FOR EACH ROW
BEGIN
  IF NEW.hits > 10 THEN
    SET NEW.expiration = DATE_ADD(NEW.date, INTERVAL factorial(10) MINUTE);
  ELSE
    SET NEW.expiration = DATE_ADD(NEW.date, INTERVAL factorial(NEW.hits) MINUTE);
  END IF;
END;//
delimiter ;
Page last modified on September 02, 2016