Код: Выделить всё
dbmail=# UPDATE dbmail_messages SET deleted_flag=1,status=2 WHERE message_idnr IN (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 LIKE 'SPAM' AND p.internal_date >= current_timestamp - interval '7 days');
UPDATE 28348
dbmail=#
отработал без нареканий....чего-то мне всеравно не нравиться что-то в нём, пока не понял что...ну вот вобщем кому нужно фунция(DBI:Pg)
1)Убивает ящики без пользователя
2)Не удалившиеся по какой-то причине
3)Чистящая спам (при желании можно вынести *spam* в отдельную переменную)
Код: Выделить всё
!/usr/bin/perl
use DBI();
use strict;
use warnings;
my $DBMA_dbname = "база";
my $DBMA_port = "3306";
my $DBMA_host = "хост";
my $DBMA_user = "юзер";
my $DBMA_password = "пароль";
my $sqltype = "pgsql";
my ( $message_idnr, $dbh, $sth );
if ($sqltype eq "pgsql")
{
unless (
$dbh = DBI->connect(
"DBI:Pg:user=$DBMA_user;password=$DBMA_password;dbname=$DBMA_dbname;port=$DBMA_port;host=$DBMA_host",
"$DBMA_user",
"$DBMA_user",
{ AutoCommit => 1 }
)
)
{
print "$DBI::errstr";
exit;
}
&dbmail_check4;
}
sub dbmail_check4 {
$sth =
$dbh->prepare(
"UPDATE dbmail_messages SET status = '003' where deleted_flag = '1' OR status >0"
);
$sth->execute();
$sth->finish();
$sth = $dbh->prepare(
"SELECT message_idnr from dbmail_messages
JOIN dbmail_mailboxes ON dbmail_messages.mailbox_idnr = dbmail_mailboxes.mailbox_idnr
LEFT JOIN dbmail_users ON dbmail_users.user_idnr = dbmail_mailboxes.owner_idnr
WHERE dbmail_users.user_idnr IS NULL"
);
$sth->execute();
while ( ($message_idnr) = $sth->fetchrow_array ) {
$sth =
$dbh->prepare(
"UPDATE dbmail_messages
SET deleted_flag=1,status=2
WHERE message_idnr IN
(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 LIKE 'SPAM' AND p.internal_date >=
current_timestamp - interval '7 days');"
);
$sth->execute();
}
$sth->finish();
$sth = $dbh->prepare(
"UPDATE dbmail_messages
LEFT JOIN dbmail_mailboxes ON dbmail_messages.mailbox_idnr = dbmail_mailboxes.mailbox_idnr
SET dbmail_messages.deleted_flag = '1', status = '001'
WHERE dbmail_mailboxes.mailbox_idnr IS NULL"
);
$sth->execute();
$sth->finish();
print "\nKill me?.\n
so sloooow.\n
(kill -9)\n\n";
exit;
}
