From 56946237551518b0184c857762d441435eb1509a Mon Sep 17 00:00:00 2001 From: Matthias Andree Date: Sat, 17 Mar 2007 11:12:29 +0000 Subject: Add delete-later, kindly donated by Carsten Ralle/Yoo GmbH, Germany. svn path=/branches/BRANCH_6-3/; revision=5051 --- contrib/README | 6 ++ contrib/delete-later | 56 ++++++++++++++++ contrib/delete-later.README | 157 ++++++++++++++++++++++++++++++++++++++++++++ 3 files changed, 219 insertions(+) create mode 100644 contrib/delete-later create mode 100644 contrib/delete-later.README (limited to 'contrib') diff --git a/contrib/README b/contrib/README index 281a3926..f3d19b33 100644 --- a/contrib/README +++ b/contrib/README @@ -197,3 +197,9 @@ pop mail server. (By Richard Harris, improved by Joshua Crawford.) A logrotate configuration file developped by Daniel Leidert for Debian, when he wanted to use /var/log/fetchmail instead of the usual syslog. It probably needs to be adjusted for use on other systems. + +### delete-later (added 2007-03-17, --ma) + +A MySQL/Tcl/Expect-based client-side script to remove messages at a +certain age. See delete-later.README for details. +(By Carsten Ralle, Yoo GmbH, Germany.) diff --git a/contrib/delete-later b/contrib/delete-later new file mode 100644 index 00000000..50c705c9 --- /dev/null +++ b/contrib/delete-later @@ -0,0 +1,56 @@ +#!/usr/bin/expect -f + +# MySQL database connection settings +set CRDB_host localhost +set CRDB_DB test +set CRDB_username root +set CRDB_password root + +# set eiter one to 1 for verbose output +log_user 0 +set comments 0 + +package require mysqltcl + +# connect to MySQL database +set handle [::mysql::connect -host $CRDB_host -user $CRDB_username -password $CRDB_password] + +# get server/usernames to clean up +set userlist [::mysql::sel $handle "SELECT UserID, server, username, password, retaindays from $CRDB_DB.fetchmail_user" -flatlist] + +# loop through all users in database +foreach {userid server username password days} $userlist { + if {$comments==1} { send_user "\r\nWorking on accound #$userid\r\n*******************************\r\n" } + eval spawn telnet -l fetchmail_cleanup $server 110 + expect "ready" + send "USER $username\r" + expect "password" + send "PASS $password\r" + expect "OK" + send "STAT\r" + expect "+OK " + expect -re "\[0-9]* " + set anz $expect_out(0,string) + if {$comments==1} { send_user "message count: $anz \r\n" } + set i 0 + while { $i < $anz } { + incr i + send "UIDL $i\r" + expect -re "\\\+OK $i \(.*\)\r" + set uid $expect_out(1,string) + ::mysql::exec $handle "insert ignore into $CRDB_DB.fetchmail values ($userid,'$uid',now());" + set age [::mysql::sel $handle "SELECT DATEDIFF(now(),Fetchdate) from $CRDB_DB.fetchmail where UserID=$userid and UID='$uid'" -list] + if {$comments==1} { send_user "Message #$i: UID: $uid , age: $age \r\n" } + if {$age > $days} { + send "DELE $i\r" + expect "deleted" + if {$comments==1} { send_user "Message $i deleted.\r\n" } + } + } + send "quit\r" + expect "signing off" + ::mysql::exec $handle "delete from $CRDB_DB.fetchmail where DATEDIFF(now(),Fetchdate)>($days*2) and UserID=$userid;" +} +::mysql::close $handle +exit + diff --git a/contrib/delete-later.README b/contrib/delete-later.README new file mode 100644 index 00000000..a5f7972b --- /dev/null +++ b/contrib/delete-later.README @@ -0,0 +1,157 @@ +******************* DELETE - LATER ******************** + + version 0.11 2007-02-06 + + + A tool for deleting email messages on POP3 accounts + after a given period of time (in days) + + E.g. if you want to keep the messages 30 days to + have webmail access while on the road + +******************************************************** + + The script queries the current contents of a POP3 mailbox, + stores the message IDs of that run and saves them to a + MySQL database. + + On each run, it compares the date on which the message was + first seen with the current date and deletes the message, + if that difference is greater than the value of the column + "retaindays". + + The script stores account settings in a separate MySQL + table for easy maintenance each email account needs an + unique ID. + + The script scales well on large installations with several + thousands of messages a day if you run it at a time of low + email volume (e.g. at 4 am). + + + +REQUIREMENTS +------------ + +- MySQL database server and client software (v4.x or later, + www.mysql.com) + + MySQL sources (for compiling mysqltcl only) + +- mysqltcl script (www.xdobry.de/mysqltcl/) + +- libexpect (v5 or later) + + + +INSTALLATION +------------ + +1. create the tables "fetchmail" and "fetchmail_users" on a + MySQL server by running + + CREATE TABLE `fetchmail` ( `UserID` BIGINT UNSIGNED NOT NULL DEFAULT 0, `UID` VARCHAR(255) NOT NULL DEFAULT '', `Fetchdate` DATE NOT NULL DEFAULT 0, PRIMARY KEY(`UserID`, `UID`)); + + CREATE TABLE `fetchmail_user` ( `UserID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `server` VARCHAR(255) NOT NULL DEFAULT '', `username` VARCHAR(63) NOT NULL DEFAULT '', `password` VARCHAR(63) NOT NULL DEFAULT '', `retaindays` INTEGER UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY(`UserID`)); + +2. Fill the table "fetchmail_users" with the settings of the + email accounts you wish to handle + +3. copy the script "delete-later" to a safe place, (on + Linux/Unix: make it executable) and edit the first lines to + match your MySQL setup + +4. run "delete-later" via cron AFTER you fetched your emails + with settings that keep the messages on the server (e.g. via + fetchmail --keep) + + + +KNOWN ISSUES +------------ + +The mysqltcl libraries sometimes don't compile against +earlier versions of MySQL 4.x. + +If you are experiencing any problems with compiling +mysqltcl, please look for pre-compiled binaries for your +distribution (or contact mail@xdobry.de), the win32 binaries +work out of the box on most M$ systems. + +As most linux distributions don't include mysqltcl, get the +latest precompiled 5.x series MySQL binariesand the matching +source code from www.mysql.com, install them onto a separate +location and run "configure && make && make install" to +compile mysqltcl yourself. + + + +NOTES +----- + +The age of an email message is calculated by the difference +in days between the run of "delete-later" it was first seen +on and the current date, not the actual date the message was +sent. So if you run the script every week, the period after +which a message is delete may vary up to 14 days. + +This was implemented that way, because of the common +practice of spammers to change the message date to some date +in the far future to place the message first in your inbox. + + + +COPYRIGHT +--------- + +Copyright (c) 2007 Yoo GmbH + Zellwaldring 51 + 09603 GROSSVOIGTSBERG + GERMANY + +Permission to use, copy, modify, distribute, and sell this software +and its documentation for any purpose is hereby granted without fee, +provided that the content of this README file appears in all copies +of the software and related documentation. + + + +DISCLAIMER +---------- + +THE SCRIPT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. + +WE (YOO) HEREBY DISCLAIM ALL WARRANTIES AND CONDITIONS WITH REGARD +TO THIS README AND SCRIPT, INCLUDING ALL WARRANTIES AND CONDITIONS +OF MERCHANTABILITY, WHETHER EXPRESS, IMPLIED OR STATUTORY, FITNESS +FOR A PARTICULAR PURPOSE, TITLE AND NON-INFRINGEMENT. + +IN NO EVENT SHALL YOO AND/OR ITS REPRESENTATIVES AND/OR RESPECTIVE +SUPPLIERS BE LIABLE FOR ANY SPECIAL, INDIRECT OR CONSEQUENTIAL +DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA +OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER +TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR +PERFORMANCE OF THIS README OR SCRIPT. + + + +CONTACT +------- + +For help, bug reports or other request regarding "delete-later" please +contact Carsten Ralle (English/German) by writing an e-mail to + + cr i4yoo de + +with a subject starting with "DELETE-LATER: " + +Please note that messages containing HTML or images will be +automatically deleted. + + + +******************************************************************************** +Yoo GmbH, Zellwaldring 51, D-09603 Grossvoigtsberg, Germany + + www.yoogmbh.de +******************************************************************************** -- cgit v1.2.3