Hi all,

I have made a feature request to add FromDomain and ToDomain in the Quarantine database.

This makes it easy to make reports based on Domain name and for ISPs to let their customer manage all the Domain's blocked email.

The Quarantine database I have is quiet large to search with substrings etc.

I also tried the full-text index on mysql but the full text search syntax does not like some characters used in the domain names: such as "-"

match (EmailFrom) against "some-domain.com"
would match everything where there is "some", ".com" and no "-domain".

I have added the ToDomain and  FromDomain fields and Indexex in the tblQuarantine and am now periodically filling them up with a perl script.

Anyone knows how to achieve this without having the ToDomain, FromDomain fields?

Atif,

Another solution can be implemented using a database trigger that will updated those fields for you on the fly each time a new record is added.

Please execute the following script on MySQL (We tested it using Query Browser v1.1). It will (1) add the two extra fields to the database, (2) create indexes for them, and (3) create a trigger that will automatically populate those fields every time a new record is entered.



ALTER TABLE `spamfilter`.`tblquarantine` ADD COLUMN `ToDomain` VARCHAR(100) AFTER `ServerID`,
  ADD COLUMN `FromDomain` VARCHAR(100) AFTER `ToDomain`;
 
ALTER TABLE `spamfilter`.`tblquarantine` MODIFY COLUMN `ToDomain` VARCHAR(100),
  MODIFY COLUMN `FromDomain` VARCHAR(100),
  ADD INDEX `ToDomain`(`ToDomain`),
  ADD INDEX `FromDomain`(`FromDomain`);
 
DROP TRIGGER trAddExtraData;
DELIMITER //
CREATE TRIGGER trAddExtraData
BEFORE INSERT ON tblQuarantine
  FOR EACH ROW BEGIN
    SET  NEW.ToDomain = MID(NEW.EmailTo, POSITION("@" IN NEW.EmailTO)+1, LENGTH(NEW.EMailTo));
    SET  NEW.FromDomain = MID(NEW.EmailFrom, POSITION("@" IN NEW.EmailFrom)+1, LENGTH(NEW.EMailFrom));
  END;
//
DELIMITER;



 

Thankyou so much. It works great.

Amir,

Can you access the MySQL's command line interface (use the "mysql" command from a DOS/terminal), and type:

use SpamFilter;

desc tblQuarantine;

show Triggers FROM SpamFilter \G;

and let us know what the output is so we can see the table and trigger structures?