forum.lissyara.su

Код: Выделить всё

UPDATE dbmail_messages SET deleted_flag ='1' WHERE mailbox_idnr =(SELECT message_idnr, dbmail_physmessage.internal_date, dbmail_messages.physmessage_id, unique_id, rfcsize, messagesize, seen_flag, answered_flag, deleted_flag, status FROM dbmail_messages LEFT JOIN dbmail_physmessage ON dbmail_messages.physmessage_id = dbmail_physmessage.id WHERE dbmail_messages.mailbox_idnr = (SELECT spambox FROM user_prefs) AND dbmail_messages.mailbox_idnr =  (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 status=3 AND seen_flag=1;

Код: Выделить всё

UPDATE 
	dbmail_messages 
SET 
	deleted_flag ='1' 
WHERE 
	mailbox_idnr = 
	(
		SELECT 
			message_idnr, 
			dbmail_physmessage.internal_date, 
			dbmail_messages.physmessage_id, 
			unique_id, 
			rfcsize, 
			messagesize, 
			seen_flag, 
			answered_flag, 
			deleted_flag, 
			status 

		FROM 
			dbmail_messages 
				LEFT JOIN 
					dbmail_physmessage 
				ON 
					dbmail_messages.physmessage_id = dbmail_physmessage.id 

		WHERE 
			dbmail_messages.mailbox_idnr = 
			(
				SELECT 
					spambox 
				FROM 
					user_prefs
			) 
				AND 
			dbmail_messages.mailbox_idnr = 
			(
				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 
			status = 3 
				AND 
			seen_flag = 1;

Код: Выделить всё

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 
	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'
	);

Код: Выделить всё

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=#

Код: Выделить всё

!/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;
}

Код: Выделить всё

UPDATE  dbmail_messages m
SET     deleted_flag=1,
        status=2 
FROM    dbmail_mailboxes b, 
        dbmail_physmessage p
WHERE   m.mailbox_idnr=b.mailbox_idnr 
        AND     p.id=m.physmessage_id 
        AND     b.name LIKE 'SPAM'
        AND     p.internal_date >= current_timestamp - interval '7 days'