Spam Filter ISP Support Forum

  New Posts New Posts RSS Feed - ToDomain and FromDomain in the Qurantine
  FAQ FAQ  Forum Search   Register Register  Login Login

ToDomain and FromDomain in the Qurantine

 Post Reply Post Reply
Author
atifghaffar View Drop Down
Senior Member
Senior Member
Avatar

Joined: 31 May 2006
Location: Switzerland
Status: Offline
Points: 104
Post Options Post Options   Thanks (0) Thanks(0)   Quote atifghaffar Quote  Post ReplyReply Direct Link To This Post Topic: ToDomain and FromDomain in the Qurantine
    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
Back to Top
sgeorge View Drop Down
Senior Member
Senior Member


Joined: 23 August 2005
Status: Offline
Points: 178
Post Options Post Options   Thanks (0) Thanks(0)   Quote sgeorge Quote  Post ReplyReply Direct Link To This Post 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

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.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
Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4065
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post 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

LogSat Software

Spam Filter ISP
Back to Top
sgeorge View Drop Down
Senior Member
Senior Member


Joined: 23 August 2005
Status: Offline
Points: 178
Post Options Post Options   Thanks (0) Thanks(0)   Quote sgeorge Quote  Post ReplyReply Direct Link To This Post Posted: 13 June 2006 at 4:51pm
mmm, nice. 
Back to Top
atifghaffar View Drop Down
Senior Member
Senior Member
Avatar

Joined: 31 May 2006
Location: Switzerland
Status: Offline
Points: 104
Post Options Post Options   Thanks (0) Thanks(0)   Quote atifghaffar Quote  Post ReplyReply Direct Link To This Post Posted: 14 June 2006 at 7:45pm
Thankyou so much. It works great.
best regards

Atif
Back to Top
ImInAfrica View Drop Down
Groupie
Groupie
Avatar

Joined: 27 June 2006
Location: FL, USA
Status: Offline
Points: 60
Post Options Post Options   Thanks (0) Thanks(0)   Quote ImInAfrica Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4065
Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post 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

LogSat Software

Spam Filter ISP
Back to Top
ImInAfrica View Drop Down
Groupie
Groupie
Avatar

Joined: 27 June 2006
Location: FL, USA
Status: Offline
Points: 60
Post Options Post Options   Thanks (0) Thanks(0)   Quote ImInAfrica Quote  Post ReplyReply Direct Link To This Post Posted: 30 June 2006 at 4:12am
Hi,
Silly me.
I never did "use spamfilter;"

seems to be working ok now.
Thanks.

Amir
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down



This page was generated in 0.141 seconds.