Spam Filter ISP Forums : Adding a field to a SFE table

Adding a field to a SFE table : LogSat wrote:Are the duplicates...

Mon, 04 Jun 2007 21:58:35 +0000

Author: jerbo128
Subject: 6093
Posted: 04 June 2007 at 9:58pm

LogSat wrote:

Are the duplicates created by SpamFilter, or by some other process..? We could not duplicate this, as in having SpamFilter create the dupes.

Thanks for the code. Will try it out.

Nearest I can tell now, most of the duplicates were created when mail was force delivered from the quarantine. I have noticed several users that have the email address to the SpamFilter listed several times. I will do some cleanup on the table and see if I can re-create to post back here.

Adding a field to a SFE table : jerbo128 wrote:Will the...

Mon, 04 Jun 2007 20:35:15 +0000

Author: LogSat
Subject: 6093
Posted: 04 June 2007 at 8:35pm

jerbo128 wrote:

Will the .677 look for duplicates in tables. I have noticed that I have a fair amount of them in the autowhitelist table. If 677 will not look for them- is it a problem having them?
If so...
Anyone have a bit of SQL code that will remove duplicates?

jerbo128

Are the duplicates created by SpamFilter, or by some other process..? We could not duplicate this, as in having SpamFilter create the dupes.
We were however able to create the duplicate entries manually in the database, there's nothing SpamFilter can do in that case... it will not remove the dupes once they are in there.

For the SQL:
Make a backup of your autowhitelist 1st!

DELETE FROM tblwl_autowhitelistforcedelivery
WHERE     (id IN
                            (SELECT     id
                               FROM          tblwl_autowhitelistforcedelivery AS F
                               WHERE      EXISTS
                                                             (SELECT     email, COUNT(id) AS Expr1
                                                               FROM          tblwl_autowhitelistforcedelivery AS tblwl_autowhitelistforcedelivery_2
                                                               WHERE      (email = F.email)
                                                               GROUP BY email
                                                               HAVING      (COUNT(id) > 1)))) AND (id NOT IN
                            (SELECT     MIN(id) AS Expr1
                               FROM          tblwl_autowhitelistforcedelivery AS F
                               WHERE      EXISTS
                                                             (SELECT     email, COUNT(id) AS Expr1
                                                               FROM          tblwl_autowhitelistforcedelivery AS tblwl_autowhitelistforcedelivery_1
                                                               WHERE      (email = F.email)
                                                               GROUP BY email
                                                               HAVING      (COUNT(id) > 1))
                               GROUP BY email))

PS - the SQL code came from: http://www.4guysfromrolla.com/webtech/sqlguru/q051200-2.shtm l

Edited by LogSat

Adding a field to a SFE table : jerbo128 wrote: Anyone have...

Mon, 04 Jun 2007 19:26:53 +0000

Author: Desperado
Subject: 6093
Posted: 04 June 2007 at 7:26pm

jerbo128 wrote:

Anyone have a bit of SQL code that will remove duplicates?

Hmmm ... the simplest thing I can think of to eliminate the duplicate records is to SELECT DISTINCT into a temp table, then truncate the real table and then insert from the temp to the original. There are other ways I am sure.

Edited by Desperado

Adding a field to a SFE table : // New to VersionNumber = '3.5.3.677';{TODO...

Mon, 04 Jun 2007 17:10:16 +0000

Author: Desperado
Subject: 6093
Posted: 04 June 2007 at 5:10pm

// New to VersionNumber = '3.5.3.677';
{TODO -cNew : SpamFilter Enterprise was deleting and re-inserting the entire AutoWhiteListForceDelivery list in the database when appending a new entry, possibly causing data loss in high traffic scenarios. This process was rewritten to append the new individual records only}

Solved my issue! Thanks

Adding a field to a SFE table : Will the .677 look for duplicates...

Mon, 04 Jun 2007 17:07:02 +0000

Author: jerbo128
Subject: 6093
Posted: 04 June 2007 at 5:07pm

jerbo128

Adding a field to a SFE table : Very cool. Thanks!

Mon, 04 Jun 2007 16:30:36 +0000

Author: WebGuyz
Subject: 6093
Posted: 04 June 2007 at 4:30pm

Very cool. Thanks!

Adding a field to a SFE table : Dan is correct on both cases....

Mon, 04 Jun 2007 16:16:33 +0000

Author: LogSat
Subject: 6093
Posted: 04 June 2007 at 4:16pm

Dan is correct on both cases. SpamFilter does not care about any extra fields you *append* at the end of the existing fields.

SpamFilter also was deleting and re-inserting all entries from the AutoWhiteListForceDelivery table every time a new entry was added to that list. While this was done to better maintain our code, it was causing issues with data loss in high traffic sites, causing the list to be truncated. This would also cause timestamp issues in Dan's case.

We just uploaded SpamFilter 3.5.3.677 in the registered user area. We've been testing this version of over a week since it required some major changes in how this list was being handled, and are now ready to make it public. All new records to the AutoWhiteListForceDelivery table are now simply appended to the table, and existing rows are not altered. We should thus now "leave alone" any timestamps yo may be adding.

Release note:
// New to VersionNumber = '3.5.3.677';
{TODO -cNew : SpamFilter Enterprise was deleting and re-inserting the entire AutoWhiteListForceDelivery list in the database when appending a new entry, possibly causing data loss in high traffic scenarios. This process was rewritten to append the new individual records only}

Adding a field to a SFE table : SFE does not CARE about extra...

Mon, 04 Jun 2007 11:20:17 +0000

Author: Desperado
Subject: 6093
Posted: 04 June 2007 at 11:20am

SFE does not CARE about extra fields but will not write them. It looks like when an entry is added by SFE, it re-writes all entries for that domain ID so that you will propably loose the extra date and a new date will be written. So .... I was looking at adding a table for referance that is triggered by changes BUT I still have to work out how to get around the re-write of all the entries. Or ... I am not fully understanding what is happening when an entry is added by SFE.

Adding a field to a SFE table : Hmm, forgot that SFE will add...

Mon, 04 Jun 2007 11:13:14 +0000

Author: WebGuyz
Subject: 6093
Posted: 04 June 2007 at 11:13am

Hmm, forgot that SFE will add entries as people whitelist stuff out of quarantine. I was thinking of doing this for the autowhitelist entries we ourselves create from parsing our outbound mail servers. If SFE is adding entries it does not know about the extra field and may compalin when adding an entry.

Guess it would be safer to create a separate table and link it to recordid of the authowhitelist entry.

Adding a field to a SFE table : Webguyz, That is easy to do BUT...

Mon, 04 Jun 2007 11:02:16 +0000

Author: Desperado
Subject: 6093
Posted: 04 June 2007 at 11:02am

Webguyz,

That is easy to do BUT ... what I see happening is that when a new entry gets posted for say ... domain id 6, all the entries for that domain get the most current date. Perhaps you can see what I did wrong.

Added a column "date_added" with a default value of "datetime"