dbmail-export already does all of this, albeit with the overhead of
dumping the messages (to /dev/null, if you'd like).
Aaron
On Wed, 2007-12-05 at 17:22 -0600, Eric Hiller wrote:
> Would this work? I am trying to get it down to a single statement.
>
> UPDATE dbmail_messages SET status=2 WHERE physmessage_id NOT IN
> (SELECT id FROM dbmail_physmessage WHERE current_timestamp at time
> zone 'GMT' - '1 day'::interval <= internal_date) AND mailbox_idnr IN
> (SELECT mailbox_idnr FROM dbmail_mailboxes WHERE name IN
> ('Trash','INBOX/Trash') OR name LIKE 'Trash/%' OR name LIKE
> 'INBOX/Trash/%') ORDER BY physmessage_id;
>
> Should I also set deleted_flag=1 as you did in your script?
>
> Thanks for the help!
>
>
> > Date: Wed, 5 Dec 2007 21:53:58 +0100
> > From: [EMAIL PROTECTED]
> > To: [email protected]
> > Subject: Re: [Dbmail] automatic purgeing of trash folder
> >
> > I remember putting something like that together. It's in the contrib
> > directory of the trunk. But I'll attach a copy here.
> >
> > Don't filter on the datefield table. It represents the Date: header
> of a
> > message, not the received date. The physmessage.internal_date
> represents
> > the received date. Which is most likely what you want.
> >
> >
> > Eric Hiller wrote:
> > > I have a spam filter that works pretty good, only problem is most
> users
> > > are too lazy to click purge and actually clean out their 'Trash'
> folder
> > > and then they call and complain that their quote is exceeded. So I
> put
> > > together this little query below to solve that problem, it will
> delete
> > > the mail in the trash folder after 1 day when run. I am just going
> to
> > > put it into a cron job.
> > >
> > > I just wanted to A) check and make sure this looked correct to a
> fresh
> > > pair of eyes and B) give this to anyone else it might be useful
> for.
> > >
> > > UPDATE dbmail_messages SET status=2 WHERE physmessage_id NOT IN
> (SELECT
> > > physmessage_id FROM dbmail_datefield WHERE current_timestamp at
> time
> > > zone 'GMT' - '1 day'::interval <= datefield) AND mailbox_idnr IN
> (SELECT
> > > mailbox_idnr FROM dbmail_mailboxes WHERE name='Trash');
> > >
> > >
> > >
> > > Thanks,
> > > Eric
> > >
> > >
> ------------------------------------------------------------------------
> > > Your smile counts. The more smiles you share, the more we donate.
> Join
> > > in!
> <www.windowslive.com/smile?ocid=TXT_TAGLM_Wave2_oprsmilewlhmtagline>
> > >
> > >
> > >
> ------------------------------------------------------------------------
> > >
> > > _______________________________________________
> > > DBmail mailing list
> > > [email protected]
> > > https://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
>
>
> ______________________________________________________________________
> Share life as it happens with the new Windows Live. Share now!
> _______________________________________________
> DBmail mailing list
> [email protected]
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
