MWX/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

<mysql>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;</mysql>

migration

<mysql>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;</mysql>