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 69aa0406a12450a958e5c5a171311290d35e0b5d..2a132ad209a090134bd9f70f83e68d5d9e837890 100644 (file)
--- a/PMG/DBTools.pm
+++ b/PMG/DBTools.pm
@@ -146,7 +146,7 @@ __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,
@@ -222,7 +222,7 @@ my $virusinfo_stat_ctablecmd = <<__EOD;
 
 __EOD
 
-# mail storage stable
+# mail storage table
 # QTypes
 # V - Virus quarantine
 # S - Spam quarantine
@@ -253,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);
 
@@ -329,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) = @_;
 
@@ -474,10 +486,33 @@ sub upgradedb {
     cond_create_std_actions($ruledb);
 
     # upgrade tables here if necessary
-    eval {
+    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 {
@@ -751,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);
@@ -759,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);
@@ -1192,20 +1227,19 @@ sub cluster_sync_status {
 }
 
 sub load_mail_data {
-    my ($dbh, $cid, $rid, $pmail) = @_;
+    my ($dbh, $cid, $rid, $ticketid) = @_;
 
     my $sth = $dbh->prepare(
        "SELECT * FROM CMailStore, CMSReceivers WHERE " .
-       ($pmail ? 'pmail = ' . $dbh->quote($pmail) . " AND " : '') .
-       "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;
 }