Spam Filter ISP Support Forum

  New Posts New Posts RSS Feed - Adding a field to a SFE table
  FAQ FAQ  Forum Search   Register Register  Login Login

Adding a field to a SFE table

 Post Reply Post Reply
Author
WebGuyz View Drop Down
Senior Member
Senior Member


Joined: 09 May 2005
Location: United States
Status: Offline
Points: 348
Post Options Post Options   Thanks (0) Thanks(0)   Quote WebGuyz Quote  Post ReplyReply Direct Link To This Post Topic: Adding a field to a SFE table
    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
Back to Top
Desperado View Drop Down
Senior Member
Senior Member
Avatar

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

Back to Top
WebGuyz View Drop Down
Senior Member
Senior Member


Joined: 09 May 2005
Location: United States
Status: Offline
Points: 348
Post Options Post Options   Thanks (0) Thanks(0)   Quote WebGuyz Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
Desperado View Drop Down
Senior Member
Senior Member
Avatar

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

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: 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

LogSat Software

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


Joined: 09 May 2005
Location: United States
Status: Offline
Points: 348
Post Options Post Options   Thanks (0) Thanks(0)   Quote WebGuyz Quote  Post ReplyReply Direct Link To This Post Posted: 04 June 2007 at 4:30pm
Very cool. Thanks!
http://www.webguyz.net
Back to Top
jerbo128 View Drop Down
Senior Member
Senior Member
Avatar

Joined: 06 March 2006
Status: Offline
Points: 178
Post Options Post Options   Thanks (0) Thanks(0)   Quote jerbo128 Quote  Post ReplyReply Direct Link To This Post 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

Back to Top
Desperado View Drop Down
Senior Member
Senior Member
Avatar

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

Back to Top
Desperado View Drop Down
Senior Member
Senior Member
Avatar

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


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

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: 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


Edited by LogSat
Roberto Franceschetti

LogSat Software

Spam Filter ISP
Back to Top
jerbo128 View Drop Down
Senior Member
Senior Member
Avatar

Joined: 06 March 2006
Status: Offline
Points: 178
Post Options Post Options   Thanks (0) Thanks(0)   Quote jerbo128 Quote  Post ReplyReply Direct Link To This Post 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.

Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down



This page was generated in 0.063 seconds.