Print Page | Close Window

ToDomain and FromDomain in the Qurantine

Printed From: LogSat Software
Category: Spam Filter ISP
Forum Name: Spam Filter ISP Support
Forum Description: General support for Spam Filter ISP
URL: https://www.logsat.com/spamfilter/forums/forum_posts.asp?TID=5659
Printed Date: 27 December 2024 at 12:53pm


Topic: ToDomain and FromDomain in the Qurantine
Posted By: atifghaffar
Subject: ToDomain and FromDomain in the Qurantine
Date Posted: 12 June 2006 at 1:01pm
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?





-------------
best regards

Atif


Replies:
Posted By: sgeorge
Date Posted: 13 June 2006 at 12:32pm
Hi Atif,

It sounds like what would be ideal for you would be to have an indexed view of your tblQuarantine table - one where whole domain names could be derived as new columns based on the EmailFrom and EmailTo columns, but indexed, so that the domains aren't derived every time you pull up the view.

This seems easy in SQL 2000:
http://www.sqlteam.com/item.asp?ItemID=1015 - http://www.sqlteam.com/item.asp?ItemID=1015

But it seems that for MySql, like you said, doing a full-text search is the way to go.  I found other people out there who were frustrated by MySql interpreting the hyphen character as a word-seperator, and hence, not interpreting your seach for some-domain.com as a single word.

But I happened upon this comment responding to someone who was trying to search for "kk-4835":
http://archives.neohapsis.com/archives/mysql/2003-q4/2803.html - http://archives.neohapsis.com/archives/mysql/2003-q4/2803.ht ml 

The only way to search for kk-4835 is to do the search


in Boolean mode and put things in quotes -> “kk-4835”


So your query would look like this:



> SELECT * FROM ms_items


> where MATCH (it_mnfgID, it_title, it_descrip)


> AGAINST ('”kk-4835”' IN BOOLEAN MODE )


Good luck!

Stephen

Posted By: LogSat
Date Posted: 13 June 2006 at 4:33pm
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;



 

-------------
Roberto Franceschetti

http://www.logsat.com" rel="nofollow - LogSat Software

http://www.logsat.com/sfi-spam-filter.asp" rel="nofollow - Spam Filter ISP

Posted By: sgeorge
Date Posted: 13 June 2006 at 4:51pm
mmm, nice. 

Posted By: atifghaffar
Date Posted: 14 June 2006 at 7:45pm
Thankyou so much. It works great.

-------------
best regards

Atif

Posted By: ImInAfrica
Date Posted: 29 June 2006 at 2:24pm
Hi,
I have tried to run the 3 sql statements in Roberto's post.
the first 2 run fine and create the fields and the indexes.
The third command executes correctly, but then i get this error in the log:
06/29/06 15:56:16:656 -- (7808) Error occurred during TAddToQuarantineThread when accessing the quarantine database: [MySQL][ODBC 3.51 Driver][mysqld-5.0.22-community-nt]Unknown column '@' in 'field list' ( 83 84 85 86 86a 86b 86c 87 88 89 90 91 92 93 94)

Can anyone shed some light on this?

Thanks
Amir

Posted By: LogSat
Date Posted: 29 June 2006 at 11:00pm
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?


-------------
Roberto Franceschetti

http://www.logsat.com" rel="nofollow - LogSat Software

http://www.logsat.com/sfi-spam-filter.asp" rel="nofollow - Spam Filter ISP

Posted By: ImInAfrica
Date Posted: 30 June 2006 at 4:12am
Hi,
Silly me.
I never did "use spamfilter;"

seems to be working ok now.
Thanks.

Amir


Print Page | Close Window