Go Back   vb.org Archive > vBulletin 3 Discussion > vB3 Programming Discussions
  #1  
Old 03-04-2009, 06:52 PM
Mark.B Mark.B is offline
Senior Member
 
Join Date: Feb 2004
Posts: 1,354
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default PM pruning Advice

I'm trying to write a query (or queries) that will prune PMs over a certain age, but without affecting any custom PM folders.

I need to do this because my PM table is enormous due to all the automated PMs sent out by the arcades that people never delete.

However, I am struggling. There are a number of mods out there but all they do is prune the pm table. I need also to prune the pmtext table because that's the greater size.

The trouble I'm running into is that by selecting pmtext with a particular PM id from, say, the inbox, the same pmtextid may also be required in someone's sent items.

Although I seem to get close, I always end up with a load of "blank" PM entries for some people, with a default timestamp and no subject, presumably because the pmtext entry has been pruned but not the pm entry.

So far I have this, which was adapted from one of the mods:

Code:
$time = time() - (90 * 24 * 60 * 60);
 $query = "DELETE " . TABLE_PREFIX . "pmtext . * , " . TABLE_PREFIX . "pm . *
 FROM " . TABLE_PREFIX . "pmtext, " . TABLE_PREFIX . "pm
 WHERE pm.folderid IN (0, -1)
 AND " . TABLE_PREFIX . "pm.pmtextid = " . TABLE_PREFIX . "pmtext.pmtextid
 AND " . TABLE_PREFIX . "pmtext.dateline < $time";
 mysql_query($query);
But this is not quite working right as some users end up with blank PM entries in their inbox or sent items.

It's driving me mad but I cannot think of a way to select all PMs from the PM table that are over 90 days old and are flagged as being in someone's inbox, find the pmtextid, delete both that entry and the corresponding entry in the sent items, and then delete the pmtext entry that matches up.

Can anyone think of a way to do this?
Reply With Quote
  #2  
Old 03-04-2009, 08:02 PM
Lynne's Avatar
Lynne Lynne is offline
 
Join Date: Sep 2004
Location: California/Idaho
Posts: 41,180
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

I'm not positive, but I'm pretty sure you only need to delete stuff from the pm table and then the cleaner will go through and delete the pm text from the pmtext table. Try this by deleting a pm line from the pm table and then wait an hour and see if the corresponding line from the pmtext table gets removed the next time the cleaner runs. (Or, go take a look at the cleaner cron jobs to see if this is true - yep, it's in cleanup2.php.)
Reply With Quote
  #3  
Old 03-04-2009, 08:23 PM
Mark.B Mark.B is offline
Senior Member
 
Join Date: Feb 2004
Posts: 1,354
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Aha...I did not know this at all...thanks Lynne.

So that being the case, the actual PM prune hack should actually do the job.

Despite years of vBulletin experience I had absolutely no idea it worked like that....you learn something new every day!
Reply With Quote
  #4  
Old 03-05-2009, 01:52 PM
Marco van Herwaarden Marco van Herwaarden is offline
 
Join Date: Jul 2004
Posts: 25,415
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Each row in the pmtext table can have multiple entries in the pm table (1 text, sent to multiple people & sender). Daily cleanup removes all entries from the pmtext table for which no more entries in the pm table remain (ie. everyone deleted the pm).
Reply With Quote
  #5  
Old 03-05-2009, 10:22 PM
Mark.B Mark.B is offline
Senior Member
 
Join Date: Feb 2004
Posts: 1,354
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Quote:
Originally Posted by Marco van Herwaarden View Post
Each row in the pmtext table can have multiple entries in the pm table (1 text, sent to multiple people & sender). Daily cleanup removes all entries from the pmtext table for which no more entries in the pm table remain (ie. everyone deleted the pm).
Thanks Marco, that all makes sense now.
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 04:51 AM.


Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.04282 seconds
  • Memory Usage 2,198KB
  • Queries Executed 13 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)bbcode_code
  • (1)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)navbar
  • (3)navbar_link
  • (120)option
  • (5)post_thanks_box
  • (5)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (5)post_thanks_postbit_info
  • (5)postbit
  • (5)postbit_onlinestatus
  • (5)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php 

Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • fetch_musername
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete