-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I have made a patch which would introduce a new executable, dbmail-delete.
The idea is to be able to mark aged messages for deletion via a known
interface to the database (and which will need to work for
all supported databases - I have only tested on mysql, though I haven't
knowingly done anything to break compatibility with pgsql or sqlite)
The scope of the deletion can be by mailbox name or by user or both
the syntax is:
dbmail-delete -u username -m mailbox -a days
at least one of username or mailbox must be specified. The cut off age
is specified in days and must be present.
if only a username and no mailbox is specified, then this will delete
aged mail from ALL mailboxes owned by that user.
Examples of use:
dbmail-delete -m Spam -a 2
to clean out all Spam folder of messages older than 2 days
dbmail-delete -u [EMAIL PROTECTED] -a 60
delete messages for user "[EMAIL PROTECTED]" which are older than 60 days
dbmail-delete -u [EMAIL PROTECTED] -m lists -a 10
delete messages for user "[EMAIL PROTECTED]" folder "lists" which are older
than 10 days
Anyone testing this should do it on a non production system.
The patch is based on a new source file delete.c (with similarities to
export.c but without the performance hit if you're only deleting and not
interested in the export) and some changes to existing files, db.c and db.h.
The patch is incomplete, since I have no idea how the build system
works. Can someone help me understand what files I need to change in
order to add a new executable to the build system?
thanks
John
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.4-svn0 (GNU/Linux)
Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org
iD8DBQFHvvbud4I3jTtt9EIRAlWbAJ4qbIO2JeMy7S03xLzgbswfW8p1SgCfYUuE
R+QPxBi3PZEVA/0B9xA44F0=
=Vrw1
-----END PGP SIGNATURE-----
diff -ur dbmail-2.2.9/db.c dbmail-2.2.9-patch/db.c
--- dbmail-2.2.9/db.c 2008-02-09 13:08:28.000000000 +0100
+++ dbmail-2.2.9-patch/db.c 2008-02-22 16:24:06.000000000 +0100
@@ -2457,6 +2457,86 @@
return DM_EGENERAL;
}
+
+
+int db_set_deleted_for_user(u64_t user_idnr,const char *mailbox, int age,
u64_t * affected_rows)
+{
+ char
query[DEF_QUERYSIZE],mailbox_clause[DEF_FRAGSIZE],age_clause[DEF_FRAGSIZE];
+ memset(query,0,DEF_QUERYSIZE);
+ memset(mailbox_clause,0,DEF_FRAGSIZE);
+ memset(age_clause,0,DEF_FRAGSIZE);
+
+ assert(affected_rows != NULL);
+ *affected_rows = 0;
+
+ if (age < 1) {
+ TRACE(TRACE_ERROR, "Age not given");
+ return DM_EGENERAL;
+ }
+ snprintf(age_clause, DEF_FRAGSIZE, db_get_sql(SQL_EXPIRE), age);
+
+ if (mailbox)
+ snprintf(mailbox_clause, DEF_FRAGSIZE,"AND
b.name='%s'",mailbox);
+
+ snprintf(query, DEF_QUERYSIZE,
+ "UPDATE %smessages m, %smailboxes b, %sphysmessage p SET "
+ "m.deleted_flag=%d,m.status = %d WHERE "
+ "b.owner_idnr = %llu AND m.mailbox_idnr = b.mailbox_idnr AND
p.id=m.physmessage_id AND "
+ "m.status < %d AND p.internal_date < %s %s ",
+ DBPFX,DBPFX,DBPFX,
+ 1, MESSAGE_STATUS_DELETE,
+ user_idnr,
+ MESSAGE_STATUS_DELETE,age_clause,mailbox_clause);
+ TRACE(TRACE_INFO, "Query [%s]",query);
+ if (db_query(query) == -1) {
+ TRACE(TRACE_ERROR, "Could not execute query");
+ return DM_EQUERY;
+ }
+ *affected_rows = db_get_affected_rows();
+ return DM_SUCCESS;
+}
+
+int db_set_deleted_for_mailbox(const char *mailbox, int age, u64_t *
affected_rows)
+{
+ char
query[DEF_QUERYSIZE],mailbox_clause[DEF_FRAGSIZE],age_clause[DEF_FRAGSIZE];
+ memset(query,0,DEF_QUERYSIZE);
+ memset(mailbox_clause,0,DEF_FRAGSIZE);
+ memset(age_clause,0,DEF_FRAGSIZE);
+
+ assert(affected_rows != NULL);
+ *affected_rows = 0;
+
+ if (age < 1) {
+ TRACE(TRACE_ERROR, "Age not given");
+ return DM_EGENERAL;
+ }
+ snprintf(age_clause, DEF_FRAGSIZE, db_get_sql(SQL_EXPIRE), age);
+
+ if (!mailbox) {
+ TRACE(TRACE_ERROR, "No mailbox given");
+ return DM_EGENERAL;
+ }
+
+ snprintf(mailbox_clause, DEF_FRAGSIZE,"AND
b.name='%s'",mailbox);
+
+ snprintf(query, DEF_QUERYSIZE,
+ "UPDATE %smessages m, %smailboxes b, %sphysmessage p SET "
+ "m.deleted_flag=%d,m.status = %d WHERE "
+ "m.mailbox_idnr = b.mailbox_idnr AND p.id=m.physmessage_id AND "
+ "m.status < %d AND p.internal_date < %s %s ",
+ DBPFX,DBPFX,DBPFX,
+ 1, MESSAGE_STATUS_DELETE,
+ MESSAGE_STATUS_DELETE,age_clause,mailbox_clause);
+ TRACE(TRACE_INFO, "Query [%s]",query);
+ if (db_query(query) == -1) {
+ TRACE(TRACE_ERROR, "Could not execute query");
+ return DM_EQUERY;
+ }
+ *affected_rows = db_get_affected_rows();
+ return DM_SUCCESS;
+}
+
+
int db_deleted_purge(u64_t * affected_rows)
{
unsigned i;
diff -ur dbmail-2.2.9/db.h dbmail-2.2.9-patch/db.h
--- dbmail-2.2.9/db.h 2008-02-09 13:08:28.000000000 +0100
+++ dbmail-2.2.9-patch/db.h 2008-02-22 16:24:53.000000000 +0100
@@ -846,6 +846,19 @@
int db_set_deleted(u64_t * affected_rows);
int db_count_deleted(u64_t * affected_rows);
/**
+ * \brief mark messages for delete and set expunge status for user and or
+ * for mailbox for messages older than age
+ * \param user_idnr is the user identifier
+ * \param mailbox is the mailbox name (optional in db_set_deleted_for_user)
+ * \param age is the age in days
+ * \param affected_rows will hold the number of affected messages on return.
+ * \return
+ * - -1 on database failure;
+ * - 1 otherwise
+ */
+int db_set_deleted_for_user(u64_t user_idnr,const char *mailbox, int age,
u64_t * affected_rows);
+int db_set_deleted_for_mailbox(const char *mailbox, int age, u64_t *
affected_rows);
+/**
* \brief purge all messages from the database with a "delete"-status
* (status = 3)
* \param affected_rows will hold the number of affected rows on return. Must
diff -urN dbmail-2.2.9/delete.c dbmail-2.2.9-patch/delete.c
--- dbmail-2.2.9/delete.c 1970-01-01 01:00:00.000000000 +0100
+++ dbmail-2.2.9-patch/delete.c 2008-02-22 16:43:04.000000000 +0100
@@ -0,0 +1,227 @@
+/*
+ Copyright (C) 2005-2006 NFG Net Facilities Group BV, [EMAIL PROTECTED]
+ Copyright (C) 2007 Aaron Stone [EMAIL PROTECTED]
+
+ This program is free software; you can redistribute it and/or
+ modify it under the terms of the GNU General Public License
+ as published by the Free Software Foundation; either
+ version 2 of the License, or (at your option) any later
+ version.
+
+ This program is distributed in the hope that it will be useful,
+ but WITHOUT ANY WARRANTY; without even the implied warranty of
+ MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ GNU General Public License for more details.
+
+ You should have received a copy of the GNU General Public License
+ along with this program; if not, write to the Free Software
+ Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
+*/
+
+/*
+ * This is the dbmail-delete program to dump mailboxes out to mbox files.
+ */
+
+#include "dbmail.h"
+
+char *configFile = DEFAULT_CONFIG_FILE;
+
+#define PNAME "dbmail/delete"
+
+/* UI policy */
+int quiet = 0;
+int reallyquiet = 0;
+int verbose = 0;
+
+extern db_param_t _db_params;
+
+void do_showhelp(void)
+{
+ printf(
+// Try to stay under the standard 80 column width
+//
0........10........20........30........40........50........60........70........80
+ "*** dbmail-delete ***\n"
+ "Use this program to mark old mail deleted.\n"
+ "See the man page for more info. Summary:\n"
+ " -u username specify a user (default: apply to all users)\n"
+ " -m mailbox specify a mailbox (default: all mailboxes
recursively)\n"
+ " At least one of username or mailbox must be
specified. \n"
+ " -a age age limit (in days) for messages, messages older
than this are marked\n"
+ " deleted. Age should be >0 \n"
+ "\n"
+ "Common options for all DBMail utilities:\n"
+ " -f file specify an alternative config file\n"
+ " -q quietly skip interactive prompts\n"
+ " use twice to suppress error messages\n"
+ " -v verbose details\n"
+ " -V show the version\n"
+ " -h show this help message\n"
+ );
+}
+
+static int do_delete_aged(char *user, char *mailbox, int age)
+{
+ u64_t user_idnr = 0, deleted_count=0;
+ int result = 0;
+ if (user)
+ {
+ /* Verify the existence of this user */
+ if (auth_user_exists(user, &user_idnr) == -1) {
+ qerrorf("Error: cannot verify existence of user
[%s].\n", user);
+ return -1;
+ }
+ if (user_idnr == 0) {
+ qerrorf("Error: user [%s] does not exist.\n", user);
+ return -1;
+ }
+ if
(db_set_deleted_for_user(user_idnr,mailbox,age,&deleted_count) < 0) {
+
+ qerrorf("Error setting flags for user [%s]\n", user);
+ return -1;
+ }
+ qprintf("Ok. [%llu] messages marked with DELETE
status.\n",
+ deleted_count);
+ }
+ else
+ {
+ if (db_set_deleted_for_mailbox(mailbox,age,&deleted_count) < 0)
{
+
+ qerrorf("Error setting flags for mailbox [%s]\n",
mailbox);
+ return -1;
+ }
+ qprintf("Ok. [%llu] messages marked with DELETE status.\n",
+ deleted_count);
+ }
+ return result;
+}
+
+int main(int argc, char *argv[])
+{
+ int opt = 0, opt_prev = 0;
+ int show_help = 0;
+ int result = 0;
+ char *user=NULL, *mailbox=NULL;
+ int age = 0;
+
+ openlog(PNAME, LOG_PID, LOG_MAIL);
+ setvbuf(stdout, 0, _IONBF, 0);
+
+ g_mime_init(0);
+
+ /* get options */
+ opterr = 0; /* suppress error message from getopt() */
+ while ((opt = getopt(argc, argv,
+ "-u:m:a" /* Major modes */
+ "f:qvVh" /* Common options */ )) != -1) {
+ /* The initial "-" of optstring allows unaccompanied
+ * options and reports them as the optarg to opt 1 (not '1') */
+ if (opt == 1)
+ opt = opt_prev;
+ opt_prev = opt;
+
+ switch (opt) {
+ /* delete specific options */
+ case 'u':
+ if (optarg && strlen(optarg))
+ user = optarg;
+ break;
+
+ case 'm':
+ if (optarg && strlen(optarg))
+ mailbox = optarg;
+ break;
+ case 'a':
+ if (optarg && strlen(optarg))
+ age = strtoull(optarg, NULL, 10);
+ break;
+
+ /* Common options */
+ case 'f':
+ if (optarg && strlen(optarg) > 0)
+ configFile = optarg;
+ else {
+ qerrorf("dbmail-delete: -f requires a
filename\n\n");
+ result = 1;
+ }
+ break;
+
+ case 'h':
+ show_help = 1;
+ break;
+
+ case 'q':
+ /* If we get q twice, be really quiet! */
+ if (quiet)
+ reallyquiet = 1;
+ if (!verbose)
+ quiet = 1;
+ break;
+
+ case 'v':
+ if (!quiet)
+ verbose = 1;
+ break;
+
+ case 'V':
+ /* Show the version and return non-zero. */
+ PRINTF_THIS_IS_DBMAIL;
+ result = 1;
+ break;
+ default:
+ /* printf("unrecognized option [%c],
continuing...\n",optopt); */
+ break;
+ }
+
+ /* If there's a non-negative return code,
+ * it's time to free memory and bail out. */
+ if (result)
+ goto freeall;
+ }
+
+ /* If nothing is happening, show the help text. */
+ if (!(user || mailbox) || age < 1 || show_help) {
+ do_showhelp();
+ result = 1;
+ goto freeall;
+ }
+
+ /* read the config file */
+ if (config_read(configFile) == -1) {
+ qerrorf("Failed. Unable to read config file %s\n", configFile);
+ result = -1;
+ goto freeall;
+ }
+
+ SetTraceLevel("DBMAIL");
+ GetDBParams(&_db_params);
+
+ /* open database connection */
+ if (db_connect() != 0) {
+ qerrorf ("Failed. Could not connect to database (check log)\n");
+ result = -1;
+ goto freeall;
+ }
+
+ /* open authentication connection */
+ if (auth_connect() != 0) {
+ qerrorf("Failed. Could not connect to authentication (check
log)\n");
+ result = -1;
+ goto freeall;
+ }
+
+ result = do_delete_aged(user,mailbox,age);
+
+ /* Here's where we free memory and quit.
+ * Be sure that all of these are NULL safe! */
+freeall:
+
+ db_disconnect();
+ auth_disconnect();
+ config_free();
+ g_mime_shutdown();
+
+ if (result < 0)
+ qerrorf("Command failed.\n");
+ return result;
+}
+
_______________________________________________
Dbmail-dev mailing list
[email protected]
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
