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,
__EOD
-# mail storage stable
+# mail storage table
# QTypes
# V - Virus quarantine
# S - Spam quarantine
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);
}
}
+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);
+}
+
+my $createdb = sub {
+ my ($dbname) = @_;
+ postgres_admin_cmd(
+ 'createdb',
+ undef,
+ '-E', 'sql_ascii',
+ '-T', 'template0',
+ '--lc-collate=C',
+ '--lc-ctype=C',
+ $dbname,
+ );
+};
+
sub create_ruledb {
my ($dbname) = @_;
# use sql_ascii to avoid any character set conversions, and be compatible with
# older postgres versions (update from 8.1 must be possible)
- postgres_admin_cmd('createdb', undef, '-E', 'sql_ascii', '-T', 'template0',
- '--lc-collate=C', '--lc-ctype=C', $dbname);
+ $createdb->($dbname);
my $dbh = open_ruledb($dbname);
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
- $dbh->do("ALTER TABLE LocalStat ADD COLUMN IF NOT EXISTS " .
- "PregreetCount INTEGER DEFAULT 0 NOT NULL");
+
+ # 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 {
}
# 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);
$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);
print STDERR "create new local database\n";
- postgres_admin_cmd('createdb', undef, $dbname);
+ $createdb->($dbname);
print STDERR "insert received data into local database\n";
}
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;
}