| 1 | -- $Id: purge.sql,v 1.8 2009/12/19 17:50:03 sbajic Exp $ |
|---|
| 2 | |
|---|
| 3 | -- --------------------------------------------------------------------------- |
|---|
| 4 | -- ! ! ! ! ! ! ! THIS FILE SHOULD ONLY BE USED ON MYSQL < 4.1 ! ! ! ! ! ! ! |
|---|
| 5 | -- ! ! ! ! ! ! ! FOR MYSQL 4.1+ PLEASE USE purge-4.1.sql FILE ! ! ! ! ! ! ! |
|---|
| 6 | -- --------------------------------------------------------------------------- |
|---|
| 7 | |
|---|
| 8 | -- --------------------------------------------------------------------------- |
|---|
| 9 | -- Note: Should you have modified your dspam.conf to have other intervals for |
|---|
| 10 | -- the purging then please modify this SQL file to be in sync with your |
|---|
| 11 | -- dspam.conf. |
|---|
| 12 | -- |
|---|
| 13 | -- Note: It is difficult to purge neutral tokens with SQL clauses the same |
|---|
| 14 | -- way as dspam_clean is doing it. So you should still run dspam_clean |
|---|
| 15 | -- with the "-u" parameter from time to time. |
|---|
| 16 | -- --------------------------------------------------------------------------- |
|---|
| 17 | |
|---|
| 18 | -- |
|---|
| 19 | -- Set some defaults |
|---|
| 20 | -- |
|---|
| 21 | SET @PurgeSignatures = 14; -- Stale signatures |
|---|
| 22 | SET @PurgeUnused = 90; -- Unused tokens |
|---|
| 23 | SET @PurgeHapaxes = 30; -- Tokens with less than 5 hits (hapaxes) |
|---|
| 24 | SET @PurgeHits1S = 15; -- Tokens with only 1 spam hit |
|---|
| 25 | SET @PurgeHits1I = 15; -- Tokens with only 1 innocent hit |
|---|
| 26 | SET @today = to_days(current_date()); |
|---|
| 27 | |
|---|
| 28 | -- |
|---|
| 29 | -- Delete tokens with less than 5 hits (hapaxes) |
|---|
| 30 | -- |
|---|
| 31 | DELETE |
|---|
| 32 | FROM dspam_token_data |
|---|
| 33 | WHERE from_days(@today-@PurgeHapaxes) > last_hit |
|---|
| 34 | AND (2*innocent_hits)+spam_hits < 5; |
|---|
| 35 | |
|---|
| 36 | -- |
|---|
| 37 | -- Delete tokens with only 1 spam hit |
|---|
| 38 | -- |
|---|
| 39 | DELETE |
|---|
| 40 | FROM dspam_token_data |
|---|
| 41 | WHERE from_days(@today-@PurgeHits1S) > last_hit |
|---|
| 42 | AND innocent_hits = 0 AND spam_hits = 1; |
|---|
| 43 | |
|---|
| 44 | -- |
|---|
| 45 | -- Delete tokens with only 1 innocent hit |
|---|
| 46 | -- |
|---|
| 47 | DELETE |
|---|
| 48 | FROM dspam_token_data |
|---|
| 49 | WHERE from_days(@today-@PurgeHits1I) > last_hit |
|---|
| 50 | AND innocent_hits = 1 AND spam_hits = 0; |
|---|
| 51 | |
|---|
| 52 | -- |
|---|
| 53 | -- Delete old tokens not seen for a while |
|---|
| 54 | -- |
|---|
| 55 | DELETE |
|---|
| 56 | FROM dspam_token_data |
|---|
| 57 | WHERE from_days(@today-@PurgeUnused) > last_hit; |
|---|
| 58 | |
|---|
| 59 | -- |
|---|
| 60 | -- Delete stale signatures |
|---|
| 61 | -- |
|---|
| 62 | DELETE |
|---|
| 63 | FROM dspam_signature_data |
|---|
| 64 | WHERE from_days(@today-@PurgeSignatures) > created_on; |
|---|
