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: https://www.logsat.com/spamfilter/forums/forum_posts.asp?TID=6093
Printed Date: 09 May 2025 at 2:40am
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? jerbo128 |
Posted By: Desperado
Date Posted: 04 June 2007 at 5:10pm
|
// New to VersionNumber = '3.5.3.677'; 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
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 |
jerbo128 wrote:Posted By: LogSat
Date Posted: 04 June 2007 at 8:35pm
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
|
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. |