deleting old spam mail

Hi,

This is actually more of a SQL question than dbmail. I'm not
really 
familiar with SQL, so I'm asking for help here, as this
might interest 
some other dbmail admins.

In my setup every user has a mailbox named "spam"
which contains as 
you've guessed it -- spam.

Some users don't clean up their spam boxes at all, some
don't do it 
frequently.

So I need to construct some wild JOIN which would do
something like this:

UPDATE dbmail_messages SET status="2"
WHERE dbmail_physmessage.internal_date < 'NOW - 30 days'
AND dbmail_mailboxes.name="spam"

Please help with creating this query.

Using mysql 5.0.x.

Kind regards,

-- 
Aleksander Kamenik
system administrator
+372 6659 649
aleksanderkrediidiinfo.ee

Krediidiinfo AS
http://www.krediidiinfo.e
e/
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

try this:


UPDATE dbmail_messages SET status = 2 WHERE physmessage_id
IN (select id 
from  dbmail_physmessage, dbmail_mailboxes WHERE 
dbmail_physmessage.internal_date < 'NOW - 30 days' AND 
dbmail_mailboxes.name="spam";)


Michael Luich
Aleksander Kamenik wrote:
> Hi,
>
> This is actually more of a SQL question than dbmail.
I'm not really 
> familiar with SQL, so I'm asking for help here, as this
might interest 
> some other dbmail admins.
>
> In my setup every user has a mailbox named
"spam" which contains as 
> you've guessed it -- spam.
>
> Some users don't clean up their spam boxes at all, some
don't do it 
> frequently.
>
> So I need to construct some wild JOIN which would do
something like this:
>
> UPDATE dbmail_messages SET status="2"
> WHERE dbmail_physmessage.internal_date < 'NOW - 30
days'
> AND dbmail_mailboxes.name="spam"
>
> Please help with creating this query.
>
> Using mysql 5.0.x.
>
> Kind regards,
>



This email and any files transmitted with it are
confidential and intended solely for the individual(s) or
entity to whom they are addressed.

If you have received this email in error please notify the
originator of the message.

Any views expressed in this message are those of the
individual sender.

This message has been scanned for Content, viruses and spam
by GlobalCerts  RiskFilter - E-mail.
www.GlobalCerts.net



This email and any files transmitted with it are
confidential and intended solely for the individual(s) or
entity to whom they are addressed.

If you have received this email in error please notify the
originator of the message.

Any views expressed in this message are those of the
individual sender.

This message has been scanned for Content, viruses and spam
by GlobalCerts  RiskFilter - E-mail.
www.GlobalCerts.net

_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

Michael Luich wrote:
> try this:

Please don't!



> 
> 
> UPDATE dbmail_messages SET status = 2 WHERE
physmessage_id IN (select id
> from  dbmail_physmessage, dbmail_mailboxes WHERE
> dbmail_physmessage.internal_date < 'NOW - 30 days'
AND
> dbmail_mailboxes.name="spam";)
> 
> 
> Michael Luich
> Aleksander Kamenik wrote:
>> Hi,
>>
>> This is actually more of a SQL question than
dbmail. I'm not really
>> familiar with SQL, so I'm asking for help here, as
this might interest
>> some other dbmail admins.
>>
>> In my setup every user has a mailbox named
"spam" which contains as
>> you've guessed it -- spam.
>>
>> Some users don't clean up their spam boxes at all,
some don't do it
>> frequently.
>>
>> So I need to construct some wild JOIN which would
do something like this:
>>
>> UPDATE dbmail_messages SET status="2"
>> WHERE dbmail_physmessage.internal_date < 'NOW -
30 days'
>> AND dbmail_mailboxes.name="spam"
>>
>> Please help with creating this query.
>>
>> Using mysql 5.0.x.
>>
>> Kind regards,
>>
> 
> 
> 
> This email and any files transmitted with it are
confidential and
> intended solely for the individual(s) or entity to whom
they are addressed.
> 
> If you have received this email in error please notify
the originator of
> the message.
> 
> Any views expressed in this message are those of the
individual sender.
> 
> This message has been scanned for Content, viruses and
spam by
> GlobalCerts  RiskFilter - E-mail.
> www.GlobalCerts.net
> 
> 
> 
> This email and any files transmitted with it are
confidential and
> intended solely for the individual(s) or entity to whom
they are addressed.
> 
> If you have received this email in error please notify
the originator of
> the message.
> 
> Any views expressed in this message are those of the
individual sender.
> 
> This message has been scanned for Content, viruses and
spam by
> GlobalCerts  RiskFilter - E-mail.
> www.GlobalCerts.net
> 
> _______________________________________________
> DBmail mailing list
> DBmaildbmail.org
> htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
> 


-- 
 
____________________________________________________________
____
  Paul Stevens                                      paul at
nfg.nl
  NET FACILITIES GROUP                     GPG/PGP:
1024D/11F8CD31
  The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

Upon a future run of dbmail-util -p, these messages would
purged.

It occurs to me that dbmail-export would be very useful for
dumping all
spam folders to mbox for further processing if we supported
a mailbox
spec without a user spec -- dumping a folder of a particular
name for
every user on the system.

We currently require that a username be specified to
dbmail-export, but
it is fairly trivial to loop over all users.

Aaron

On Wed, 2007-07-11 at 12:24 -0400, Michael Luich wrote:
> try this:
> 
> 
> UPDATE dbmail_messages SET status = 2 WHERE
physmessage_id IN (select id 
> from  dbmail_physmessage, dbmail_mailboxes WHERE 
> dbmail_physmessage.internal_date < 'NOW - 30 days'
AND 
> dbmail_mailboxes.name="spam";)
> 
> 
> Michael Luich
> Aleksander Kamenik wrote:
> > Hi,
> >
> > This is actually more of a SQL question than
dbmail. I'm not really 
> > familiar with SQL, so I'm asking for help here, as
this might interest 
> > some other dbmail admins.
> >
> > In my setup every user has a mailbox named
"spam" which contains as 
> > you've guessed it -- spam.
> >
> > Some users don't clean up their spam boxes at all,
some don't do it 
> > frequently.
> >
> > So I need to construct some wild JOIN which would
do something like this:
> >
> > UPDATE dbmail_messages SET status="2"
> > WHERE dbmail_physmessage.internal_date < 'NOW -
30 days'
> > AND dbmail_mailboxes.name="spam"
> >
> > Please help with creating this query.
> >
> > Using mysql 5.0.x.
> >
> > Kind regards,
> >
> 
> 
> 
> This email and any files transmitted with it are
confidential and intended solely for the individual(s) or
entity to whom they are addressed.
> 
> If you have received this email in error please notify
the originator of the message.
> 
> Any views expressed in this message are those of the
individual sender.
> 
> This message has been scanned for Content, viruses and
spam by GlobalCerts  RiskFilter - E-mail.
> www.GlobalCerts.net
> 
> 
> 
> This email and any files transmitted with it are
confidential and intended solely for the individual(s) or
entity to whom they are addressed.
> 
> If you have received this email in error please notify
the originator of the message.
> 
> Any views expressed in this message are those of the
individual sender.
> 
> This message has been scanned for Content, viruses and
spam by GlobalCerts  RiskFilter - E-mail.
> www.GlobalCerts.net
> 
> _______________________________________________
> DBmail mailing list
> DBmaildbmail.org
> htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

the basic query for getting the message_idnrs involved is:

select message_idnr from  dbmail_messages m join
dbmail_mailboxes b ON
m.mailbox_idnr=b.mailbox_idnr JOIN dbmail_physmessage p ON
p.id=m.physmessage_id WHERE p.internal_date < 'NOW - 30
days' AND b.name
LIKE "spam";


however, mysql can't update a table that is involved in the
where-clause
of the sub-query, so we need a wrapper:



Michael Luich wrote:
> try this:
> 
> 
> UPDATE dbmail_messages SET status = 2 WHERE
physmessage_id IN (select id
> from  dbmail_physmessage, dbmail_mailboxes WHERE
> dbmail_physmessage.internal_date < 'NOW - 30 days'
AND
> dbmail_mailboxes.name="spam";)
> 
> 
> Michael Luich
> Aleksander Kamenik wrote:
>> Hi,
>>
>> This is actually more of a SQL question than
dbmail. I'm not really
>> familiar with SQL, so I'm asking for help here, as
this might interest
>> some other dbmail admins.
>>
>> In my setup every user has a mailbox named
"spam" which contains as
>> you've guessed it -- spam.
>>
>> Some users don't clean up their spam boxes at all,
some don't do it
>> frequently.
>>
>> So I need to construct some wild JOIN which would
do something like this:
>>
>> UPDATE dbmail_messages SET status="2"
>> WHERE dbmail_physmessage.internal_date < 'NOW -
30 days'
>> AND dbmail_mailboxes.name="spam"
>>
>> Please help with creating this query.
>>
>> Using mysql 5.0.x.
>>
>> Kind regards,
>>
> 
> 
> 
> This email and any files transmitted with it are
confidential and
> intended solely for the individual(s) or entity to whom
they are addressed.
> 
> If you have received this email in error please notify
the originator of
> the message.
> 
> Any views expressed in this message are those of the
individual sender.
> 
> This message has been scanned for Content, viruses and
spam by
> GlobalCerts  RiskFilter - E-mail.
> www.GlobalCerts.net
> 
> 
> 
> This email and any files transmitted with it are
confidential and
> intended solely for the individual(s) or entity to whom
they are addressed.
> 
> If you have received this email in error please notify
the originator of
> the message.
> 
> Any views expressed in this message are those of the
individual sender.
> 
> This message has been scanned for Content, viruses and
spam by
> GlobalCerts  RiskFilter - E-mail.
> www.GlobalCerts.net
> 
> _______________________________________________
> DBmail mailing list
> DBmaildbmail.org
> htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
> 


-- 
 
____________________________________________________________
____
  Paul Stevens                                      paul at
nfg.nl
  NET FACILITIES GROUP                     GPG/PGP:
1024D/11F8CD31
  The Netherlands________________________________http://www.nfg.nl

_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

Eh? It was there as attachment...
 

> -----Original Message-----
> From: dbmail-bouncesdbmail.org 
> [mailto:dbmail-bouncesdbmail.org] On Behalf Of
Aleksander Kamenik
> Sent: Thursday, July 12, 2007 5:24 PM
> To: DBMail mailinglist
> Subject: Re: [Dbmail] deleting old spam mail
> 
> Paul J Stevens wrote:
> 
> > however, mysql can't update a table that is
involved in the 
> > where-clause of the sub-query, so we need a
wrapper:
> 
> Care to give an example?
> 
> Thanks,
> 
> --
> Aleksander Kamenik
> system administrator
> +372 6659 649
> aleksanderkrediidiinfo.ee
> 
> Krediidiinfo AS
> http://www.krediidiinfo.e
e/
> _______________________________________________
> DBmail mailing list
> DBmaildbmail.org
> htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
> 
> 


_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

Jani Partanen wrote:
> Eh? It was there as attachment...

Indeed, long day I guess. Can't wait for the vacation.


Thanks,

Aleksander Kamenik
system administrator
+372 6659 649
aleksanderkrediidiinfo.ee

Krediidiinfo AS
http://www.krediidiinfo.e
e/
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

Here's a PHP version of the script that deletes messages in
all folders 
named "spam" and that are at least 30 days old for
mysql.

<?php
$link = mysql_connect('localhost:/var/lib/mysql/mysql.sock',
'dbmail', 
'password');
if (!$link) {
     die('Could not connect: ' . mysql_error());
}
mysql_select_db('dbmail', $link) or die('Could not select
database.');

$qSelect = "SELECT message_idnr FROM
dbmail_messages m
JOIN dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr
JOIN dbmail_physmessage p ON p.id=m.physmessage_id
WHERE p.internal_date < date_sub(now(), interval 30 day)
      AND b.name LIKE 'spam'";

$res = mysql_query($qSelect);

if (!$res) {
    echo "Could not successfully run query ($qSelect)
from DB: " . 
mysql_error();
    exit;
}

while($row = mysql_fetch_assoc($res))
{
    $qUpdate = "UPDATE dbmail_messages SET
deleted_flag=1, status=2 
WHERE message_idnr=".$row['message_idnr'];
    mysql_query($qUpdate);
}
?>

Thanks everybody!

-- 
Aleksander Kamenik
system administrator
+372 6659 649
aleksanderkrediidiinfo.ee

Krediidiinfo AS
http://www.krediidiinfo.e
e/
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

Hello, Aleksander!
You wrote to "DBMail mailinglist" <dbmaildbmail.org> on Thu, 19 Jul 2007 
18:09:28 +0300:

Shell script for mysql for delete old messages from folders
spam and junk in 
any level (for example INBOX/spam):

#!/bin/sh
INTERVALD='30'
mysql -u USER --password='PASSWORD' DATABASE <<ENDSQL
CREATE TEMPORARY TABLE dbmail_tmp_oldspam
    SELECT message_idnr FROM dbmail_messages m
 INNER JOIN dbmail_mailboxes b ON
m.mailbox_idnr=b.mailbox_idnr
 INNER JOIN dbmail_physmessage p ON p.id=m.physmessage_id
 WHERE b.name RLIKE '^(.*/)?(spam|junk)$' AND
  p.internal_date < date_sub(now(), interval $INTERVALD
day);
SELECT COUNT(*) AS oldspam_messages FROM
dbmail_tmp_oldspam;
UPDATE dbmail_messages SET deleted_flag=1,status=2
    WHERE message_idnr IN (SELECT message_idnr FROM
dbmail_tmp_oldspam);
ENDSQL
echo "Cleanup done."

------------------------------------------------------------
-----
With best regards, Anton Zakatov.  E-mail: antonabn.ru 

_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail

Interesting use of RLIKE to pick out folders at any level of
depth. But is
that really wise? I think it's easy to explain to users that
the contents
of their main Spam folder will be wiped out every now and
then, but
*every* spam folder?

Aaron

On Thu, Jul 19, 2007, Anton Zakatov <antonabn.ru> said:

> Hello, Aleksander!
> You wrote to "DBMail mailinglist"
<dbmaildbmail.org> on Thu, 19 Jul 2007 
> 18:09:28 +0300:
> 
> Shell script for mysql for delete old messages from
folders spam and junk in 
> any level (for example INBOX/spam):
> 
> #!/bin/sh
> INTERVALD='30'
> mysql -u USER --password='PASSWORD' DATABASE
<<ENDSQL
> CREATE TEMPORARY TABLE dbmail_tmp_oldspam
>     SELECT message_idnr FROM dbmail_messages m
>  INNER JOIN dbmail_mailboxes b ON
m.mailbox_idnr=b.mailbox_idnr
>  INNER JOIN dbmail_physmessage p ON
p.id=m.physmessage_id
>  WHERE b.name RLIKE '^(.*/)?(spam|junk)$' AND
>   p.internal_date < date_sub(now(), interval
$INTERVALD day);
> SELECT COUNT(*) AS oldspam_messages FROM
dbmail_tmp_oldspam;
> UPDATE dbmail_messages SET deleted_flag=1,status=2
>     WHERE message_idnr IN (SELECT message_idnr FROM
dbmail_tmp_oldspam);
> ENDSQL
> echo "Cleanup done."
> 
>
------------------------------------------------------------
-----
> With best regards, Anton Zakatov.  E-mail: antonabn.ru 
> 
> _______________________________________________
> DBmail mailing list
> DBmaildbmail.org
> htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
> 

-- 



_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail