Print Page | Close Window

Adding a field to a SFE table

Printed From: LogSat Software
Category: Spam Filter ISP
Forum Name: Spam Filter ISP Support
Forum Description: General support for Spam Filter ISP
URL: http://www.logsat.com/spamfilter/forums/forum_posts.asp?TID=6093
Printed Date: 20 October 2017 at 11:22pm


Topic: Adding a field to a SFE table
Posted By: WebGuyz
Subject: Adding a field to a SFE table
Date Posted: 04 June 2007 at 10:48am

Roberto,

   I need to add a date field to tblwl_autowhitelistforcedelivery so that I can write some sp's to expire stale addresses over time and didn't want to create another table. Would doing  so affect anything? I would think that after the initial import of text files all you ever do is read that table. Only a write to that table from your exe would fail. Or is it safer to just create a x link table?

Thanks!



-------------
http://www.webguyz.net



Replies:
Posted By: Desperado
Date 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"



-------------
The Desperado
Dan Seligmann.
Work: http://www.mags.net
Personal: http://www.desperado.com



Posted By: WebGuyz
Date 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.

 



-------------
http://www.webguyz.net


Posted By: Desperado
Date 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.

-------------
The Desperado
Dan Seligmann.
Work: http://www.mags.net
Personal: http://www.desperado.com



Posted By: LogSat
Date 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}



-------------
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: WebGuyz
Date Posted: 04 June 2007 at 4:30pm
Very cool. Thanks!

-------------
http://www.webguyz.net


Posted By: jerbo128
Date Posted: 04 June 2007 at 5:07pm

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?Big smile

jerbo128



Posted By: Desperado
Date 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



-------------
The Desperado
Dan Seligmann.
Work: http://www.mags.net
Personal: http://www.desperado.com



Posted By: Desperado
Date Posted: 04 June 2007 at 7:26pm
Originally posted by jerbo128 jerbo128 wrote:

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

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.


-------------
The Desperado
Dan Seligmann.
Work: http://www.mags.net
Personal: http://www.desperado.com



Posted By: LogSat
Date Posted: 04 June 2007 at 8:35pm
Originally posted by jerbo128 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?Big smile

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


-------------
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: jerbo128
Date Posted: 04 June 2007 at 9:58pm

Originally posted by LogSat 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.




Print Page | Close Window