PM pruning Advice

[Mark.B]

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:

$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?

[Lynne]

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.)

[Mark.B]

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!

[Marco van Herwaarden]

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).

[Mark.B]

Originally Posted by Marco van Herwaarden

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.