log email addresses as utf8 (postfix also do that).

[pmg-api.git] / PMG / DBTools.pm

diff --git a/PMG/DBTools.pm b/PMG/DBTools.pm
index 1696a81f448dc292e93c8a3c6e77e1fc2e8f6401..2a132ad209a090134bd9f70f83e68d5d9e837890 100644 (file)
--- a/PMG/DBTools.pm
+++ b/PMG/DBTools.pm
@@ -146,8 +146,9 @@ __EOD
 
 my $local_stat_ctablecmd =  <<__EOD;
     CREATE TABLE LocalStat
-    (Time INTEGER NOT NULL UNIQUE,
+    (Time INTEGER NOT NULL,
      RBLCount INTEGER DEFAULT 0 NOT NULL,
+     PregreetCount INTEGER DEFAULT 0 NOT NULL,
      CID INTEGER NOT NULL,
      MTime INTEGER NOT NULL,
      PRIMARY KEY (Time, CID));
@@ -221,7 +222,7 @@ my $virusinfo_stat_ctablecmd = <<__EOD;
 
 __EOD
 
-# mail storage stable
+# mail storage table
 # QTypes
 # V - Virus quarantine
 # S - Spam quarantine
@@ -252,6 +253,7 @@ my $cmailstore_ctablecmd =  <<__EOD;
      CMailStore_RID INTEGER NOT NULL,
      PMail VARCHAR(255) NOT NULL,
      Receiver VARCHAR(255),
+     TicketID INTEGER NOT NULL,
      Status "char" NOT NULL,
      MTime INTEGER NOT NULL);
 
@@ -328,6 +330,17 @@ sub cond_create_dbtable {
     }
 }
 
+sub database_column_exists {
+    my ($dbh, $table, $column) = @_;
+
+    my $sth = $dbh->prepare(
+       "SELECT column_name FROM information_schema.columns " .
+       "WHERE table_name = ? and column_name = ?");
+    $sth->execute(lc($table), lc($column));
+    my $res = $sth->fetchrow_hashref();
+    return defined($res);
+}
+
 sub create_ruledb {
     my ($dbname) = @_;
 
@@ -473,6 +486,33 @@ sub upgradedb {
     cond_create_std_actions($ruledb);
 
     # upgrade tables here if necessary
+    if (!database_column_exists($dbh, 'LocalStat', 'PregreetCount')) {
+       $dbh->do("ALTER TABLE LocalStat ADD COLUMN " .
+                "PregreetCount INTEGER DEFAULT 0 NOT NULL");
+    }
+
+    eval { $dbh->do("ALTER TABLE LocalStat DROP CONSTRAINT localstat_time_key"); };
+    # ignore errors here
+
+
+    # add missing TicketID to CMSReceivers
+    if (!database_column_exists($dbh, 'CMSReceivers', 'TicketID')) {
+       eval {
+           $dbh->begin_work;
+           $dbh->do("CREATE SEQUENCE cmsreceivers_ticketid_seq");
+           $dbh->do("ALTER TABLE CMSReceivers ADD COLUMN " .
+                    "TicketID INTEGER NOT NULL " .
+                    "DEFAULT nextval('cmsreceivers_ticketid_seq')");
+           $dbh->do("ALTER TABLE CMSReceivers ALTER COLUMN " .
+                    "TicketID DROP DEFAULT");
+           $dbh->do("DROP SEQUENCE cmsreceivers_ticketid_seq");
+           $dbh->commit;
+       };
+       if (my $err = $@) {
+           $dbh->rollback;
+           die $err;
+       }
+    }
 
     # update obsolete content type names
     eval {
@@ -746,7 +786,7 @@ sub init_ruledb {
     }
 
     # Quarantine/Mark Spam (Level 5)
-    $rule = PMG::RuleDB::Rule->new ('Quarantine/Mark Spam (Level 5)', 79, 0, 0);
+    $rule = PMG::RuleDB::Rule->new ('Quarantine/Mark Spam (Level 5)', 81, 0, 0);
     $ruledb->save_rule ($rule);
 
     $ruledb->rule_add_what_group ($rule, $spam5);
@@ -754,7 +794,7 @@ sub init_ruledb {
     $ruledb->rule_add_action ($rule, $quarantine);
 
     ## Block Spam Level 10
-    $rule = PMG::RuleDB::Rule->new ('Block Spam (Level 10)', 78, 0, 0);
+    $rule = PMG::RuleDB::Rule->new ('Block Spam (Level 10)', 82, 0, 0);
     $ruledb->save_rule ($rule);
 
     $ruledb->rule_add_what_group ($rule, $spam10);
@@ -1088,12 +1128,11 @@ sub init_nodedb {
 
        open (my $fh, ">", $fn) || die "open '$fn' failed - $!\n";
 
-       postgres_admin_cmd(
-           ['/usr/bin/ssh', '-o', 'BatchMode=yes',
-            '-o', "HostKeyAlias=${master_name}",
-            $master_ip, 'pg_dump'],
-           { output => '>&' . fileno($fh) },
-           $dbname, '-F', 'c');
+       my $cmd = ['/usr/bin/ssh', '-o', 'BatchMode=yes',
+                  '-o', "HostKeyAlias=${master_name}", $master_ip,
+                  'pg_dump', $dbname, '-F', 'c' ];
+
+       PVE::Tools::run_command($cmd, output => '>&' . fileno($fh));
 
        close($fh);
 
@@ -1188,19 +1227,19 @@ sub cluster_sync_status {
 }
 
 sub load_mail_data {
-    my ($dbh, $cid, $rid) = @_;
+    my ($dbh, $cid, $rid, $ticketid) = @_;
 
     my $sth = $dbh->prepare(
        "SELECT * FROM CMailStore, CMSReceivers WHERE " .
-       "CID = $cid and RID = $rid AND " .
+       "CID = ? AND RID = ? AND TicketID = ? AND " .
        "CID = CMailStore_CID AND RID = CMailStore_RID");
-    $sth->execute ();
+    $sth->execute($cid, $rid, $ticketid);
 
     my $res = $sth->fetchrow_hashref();
 
     $sth->finish();
 
-    die "no such mail (C${cid}R${rid})\n" if !defined($res);
+    die "no such mail (C${cid}R${rid}T${ticketid})\n" if !defined($res);
 
     return $res;
 }