Spam Filter ISP Support Forum

  New Posts New Posts RSS Feed - SQL Code Help
  FAQ FAQ  Forum Search   Register Register  Login Login

SQL Code Help
 Post Reply Post Reply
Author
  Topic Search Topic Search  Topic Options Topic Options
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 Topic: SQL Code Help
    Posted: 30 May 2007 at 5:39pm

Can someone please suggest a bit of SQL code that will compare the tblMSGS and tblQuarantine and delete the orphaned entires.  My quarantine table has 400K entries and my messages table has 750K.  I'm not real handy with SQL queries, but can usually tweak pre-mades to fit my situation. 

Thanks for the help

jerbo182
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: 30 May 2007 at 6:06pm
Hey man, happy to help.  Since I tend to delete from the tlbQuarantine table instead of from tblMsgs, I too need to clean out the orphans from time to time.  Here's how to delete orphaned records in the message table:

The following SQL code works in MS Access 2000 database.
DELETE *
FROM tblMsgs AS m
WHERE (m.MsgID  in (
select m2.msgid
from tblMsgs m2
left outer join tblQuarantine AS q on m2.msgid = q.msgid
where q.msgid is null
))

To view the records before deleting them, replace "DELETE" with "select" in the query above

Because of the way that this relational database is structured, it should not be possible for orphaned quarantine entries to exist.  Hence, you don't have to worry about using a reciprocal query for deleting orphaned tblQuarantine records.    When you delete a record from the tblMsgs table, any records in tblQuarantine that referenced that message record should be automatically deleted.

I hope that helps,

Stephen
Back to Top
LogSat View Drop Down
Admin Group
Admin Group
Avatar

Joined: 25 January 2005
Location: United States
Status: Offline
Points: 4104 		Post Options Post Options   Thanks (0) Thanks(0)   Quote LogSat Quote  Post ReplyReply Direct Link To This Post Posted: 30 May 2007 at 6:32pm
Strange. The newer versions of SpamFilter (as of the last few months) should perform this orphan cleanup themselves...

While we look for potential problems, the query we use is (should work on all DB platforms):

DELETE tblMsgs FROM tblMsgs LEFT JOIN tblQuarantine
ON tblMsgs.MsgID = tblQuarantine.MsgID WHERE (tblQuarantine.MsgID IS NULL)
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: 31 May 2007 at 10:24am
Well for my part, I'll clarify that my orphans are due to my specific database usage, and don't represent any problem with the software...

Here's what I do when I search and clean my database...
  • I make a copy of my MS Access 2000 quarantine to an offline copy
  • In the offline copy, I delete several records, often deleting only from the tblQuarantine table while neglecting the record in tblMsgs that is referenced.
  • Due to the partial deletes that I run, I leave some orphans in tblMsgs, so I check for and delete orphans before applying the edited database back to SpamFilter.
Stephen
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: 05 June 2007 at 10:49pm

I have some users that refuse to check their quarantine except for once every week.  They then accumulate so many messages, that my webserver times out when they try to delete 4000 messages at one time.  I have come up with a bit of sql that will search the quarantine for users with more than 2000 messages.  It is:

SELECT emailto, COUNT(1) AS Number2
FROM  tblQuarantine as tblQuarantine_2
GROUP BY emailto
HAVING  (COUNT(1) > 2000)

What I would like to do is this:
Say a user has 2500 emails in quarantine - I would like the query to expire the 500 of them that are the oldest.   I can make the query work to delete all emails for the users returned from above, but have been unable to only delete the "excessive" messages.

Is anyone doing anything like this?  Comments?...
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down



This page was generated in 0.258 seconds.

Copyright (c)2002-2021 LogSat Software LLC - Sales: sales@LogSat.com
Contact Us