—- plugin —-
description: A plugin to delete old records from the phplist database.
author : Duncan Cameron
type :
lastupdate : 2017-09-17
compatible : phplist 3
depends :
conflicts :
similar :
tags :

sourcerepo : https://github.com/bramley/phplist-plugin-housekeeping

This plugin deletes old and unused records from various tables, either single tables or a group of related tables.
The plugin provides a page that can be invoked through menu System > Housekeeping or as a cron job.

Rows will be deleted from the message table that have a status of 'sent' and whose sent date is earlier than the current date less the configuration threshold value.
Rows will also be deleted from all tables related to the message table, these are

• linktrack_ml
• linktrack_uml_click
• listmessage
• message_attachment
• message_data
• usermessage
• user_message_bounce
• user_message_forward
• user_message_view

Rows will be deleted from the eventlog table whose date field is earlier than the current date less the configuration threshold value.

Rows will be deleted from the bounce table whose date field is earlier than the current date less the configuration threshold value. Related rows will be deleted from the user_message_bounce table.

Rows will also be deleted from the bounce table that relate to subscribers who are marked as blacklisted.

Rows will also be deleted from the user_message_bounce table whose related record in the bounce table no longer exists..

Rows will be deleted from the bounceregex_bounce table that refer to bounces which no longer exist.

Rows will be deleted from the linktrack_forward table that are no longer used (do not have a reference in either the linktrack_ml or linktrack_uml_click tables).

This can happen after deleting campaigns,and a row in linktrack_forward is no longer used by any campaigns.

Deletes rows from the user_history table that are older than the threshold, but taken from each subscriber's most recent user_history record. This ensures that the user history for a subscriber will never be totally deleted.

For example, if the threshold is 6 months and the most recent user_history record for a subscriber was 2 months ago, then the records for that subscriber older than 8 months will be deleted.

See the README file on the GitHub page https://github.com/bramley/phplist-plugin-housekeeping for how to install.

The text displayed by the plugin can be translated into other languages. See the file plugins/HousekeepingPlugin/lan/translations_en.php for the English version that can be used as the basis for another language. Each line in the file contains the English text then the equivalent text in the target language.

To create a new language file, copy translations_en.php to translations_xx.php, where xx is the language code, then change the texts for your target language. You can share your new language file by submitting it for inclusion in the plugin. Please create a topic in the user forum or an issue on GitHub.

The plugin provdes a section on the Settings page where you can configure which tables should be processed.

The threshold values must be specified as a number of days, weeks, months, quarters or years, singular or plural are accepted. For example

• 1 year
• 10 weeks
• 1 month
• 60 days

The housekeeping can be automated by running a cron job periodically, say each week or month. The cron command should be similar to this but adjusted for the command to run php and the installation directory of phplist:

php /home/xxx/public_html/lists/admin/index.php -p process -m HousekeepingPlugin -c /home/xxx/public_html/lists/config/config.php 

You can also run the command as a remote page if your cron job is limited to fetching web pages and cannot run linux commands.
The page can be fetched using a linux command such as wget or curl.

wget -O housekeeping.log 'https://mysite.com/lists/admin/?page=process&pi=HousekeepingPlugin&secret=xxxxxxxx' 

curl 'https://mysite.com/lists/admin/?page=process&pi=HousekeepingPlugin&secret=xxxxxxxx'

where xxxxxxxx is the “Secret for remote processing” value from the phplist Settings page.