SpamFerret/tables/client

About

 * History:
 * 2009-08-08 Dropping "ID" field, because "Address" should be the key; renaming table to "client"
 * This helps keep legacy code working (leaving "clients" table in place) while I try to get access to upgrade it
 * Fields:
 * Retries:
 * Each time a client submits spam, clients.Retries increments...
 * ...unless clients.WhenLast was sufficiently long ago, in which case clients.Retries is reset to 0 (and WhenLast is updated).
 * Each time a client submits non-spam, if clients.Retries is too high and WhenLast is recent enough, the content is refused without checking for a spam match.
 * Net effect is that too many spams within a certain period of time causes an IP to be temporarily blacklisted.

SQL
CREATE TABLE `client` ( `Address`   VARCHAR(15) NOT NULL      COMMENT 'IP address',  `WhenFirst` DATETIME                  COMMENT 'when this IP address first submitted a spam',  `WhenLast`  DATETIME                  COMMENT 'when this IP address last submitted a spam',  `Retries`   INT DEFAULT NULL          COMMENT 'number of spam retries',  `Count`     INT DEFAULT 0             COMMENT 'number of attempts',  `doBlock`   TINYINT(1)                COMMENT "TRUE = permanent block",  `Notes`     VARCHAR(255) DEFAULT NULL COMMENT "human-added notes",  PRIMARY KEY(`Address`) ) ENGINE = MYISAM;

migration
INSERT INTO client (Address,WhenFirst,WhenLast,Retries,Count) SELECT Address, MIN(WhenFirst) AS WhenFirst, IF(MAX(IFNULL(WhenLast,WhenFirst))>MIN(WhenFirst),MAX(IFNULL(WhenLast,WhenFirst)),MAX(WhenLast)) AS WhenLast, SUM(Retries) AS Retries, SUM(Count) AS Count FROM clients GROUP BY Address ON DUPLICATE KEY UPDATE Address=Address;