Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
The latests up to date project is located here: https://github.com/esimonetti/SugarActivityStreamPurger. CLI script to hard delete activity streams. It will keep: comments, activities with comments, user posts, link and unlink activities and all activities created within the last N months
<?php
// Enrico Simonetti
// enricosimonetti.com
//
// 2018-09-19 on Sugar 8.0.0
//
// CLI script to hard delete activity streams. It will keep: comments, activities with comments, user posts, and all activities created within the last N months
//
// TEST, TEST, TEST AND USE AT YOUR OWN RISK!
//
// For big systems it is recommended to add the indexes below, and then remove them once completed. It is also recommended to start with a longer window of time (eg: --keep-months=24 )
// To add indexes on MySQL:
// create index idx_act_type_entered on activities (activity_type, date_entered);
// create index idx_actuser_actid on activities_users (activity_id);
// To remove indexes:
// drop index idx_act_type_entered on activities;
// drop index idx_actuser_actid on activities_users;
use Doctrine\DBAL\Connection;
function usage($error = '') {
if (!empty($error)) print(PHP_EOL . 'Error: ' . $error . PHP_EOL);
print(' php ' . __FILE__ . ' --instance /full/path [--keep-months=6] --yes-hard-delete-records' . PHP_EOL);
exit(1);
}
// only allow CLI
$sapi_type = php_sapi_name();
if (substr($sapi_type, 0, 3) != 'cli') {
die(__FILE__ . ' is CLI only.');
}
// get command line params
$o = getopt('', array('instance:', 'keep-months::', 'yes-hard-delete-records'));
if (!$o || !isset($o['yes-hard-delete-records'])) usage();
// find directory
if (!empty($o['instance']) && is_dir($o['instance'])) {
print('Debug: Entering directory ' . $o['instance'] . PHP_EOL);
chdir($o['instance']);
} else {
chdir(dirname(__FILE__));
}
if (!file_exists('config.php') || !file_exists('sugar_version.php')) {
usage('The provided directory is not a Sugar system');
}
// sugar basic setup
define('sugarEntry', true);
require_once('include/entryPoint.php');
if (extension_loaded('xdebug')) {
echo 'Xdebug is enabled on this system. It is highly recommended to disable Xdebug on PHP CLI before running this script. Xdebug will cause unwanted slowness.'.PHP_EOL;
}
// temporarily stop xdebug, xhprof and tideways if enabled
if (function_exists('xdebug_disable')) {
xdebug_disable();
}
if (function_exists('xhprof_disable')) {
xhprof_disable();
xhprof_sample_disable();
}
if (function_exists('tideways_disable')) {
tideways_disable();
}
if (empty($current_language)) {
$current_language = $sugar_config['default_language'];
}
$app_list_strings = return_app_list_strings_language($current_language);
$app_strings = return_application_language($current_language);
$mod_strings = return_module_language($current_language, 'Administration');
global $current_user;
$current_user = BeanFactory::getBean('Users');
$current_user->getSystemUser();
echo 'Trimming activity streams...' . PHP_EOL;
$results = array(
'activities' => 0,
'activities_users' => 0,
);
$db = DBManagerFactory::getInstance();
// months to keep
$months_to_keep = 12;
if (!empty($o['keep-months'])) {
if ($o['keep-months'] > 0) {
$months_to_keep = $o['keep-months'];
}
if ($o['keep-months'] == 'none') {
$months_to_keep = 0;
}
}
// delete every activities record that is not a post, older than N months, without comments
$date_entered_keep = gmdate('Y-m-d H:i:s', mktime(date('H'), date('i'), date('s'), date('m')-$months_to_keep, date('d'),date('Y')));
echo 'The activity stream records older than ' . $date_entered_keep . ' will be deleted.' . PHP_EOL;
echo 'The records will be completely REMOVED from the database.' . PHP_EOL;
echo 'The script will proceed in 10 seconds...' . PHP_EOL;
sleep(10);
$start_time = microtime(true);
// find all counts first
foreach ($results as $table => $value) {
$qb = $db->getConnection()->createQueryBuilder();
$qb->select('COUNT(id) as count');
$qb->from($table);
$res = $qb->execute();
if ($row = $res->fetch()) {
$results[$table] = $row['count'];
}
}
// delete all soft deletes
$qb = $db->getConnection()->createQueryBuilder();
$qb->delete('activities');
$qb->where('deleted = ' . $qb->createPositionalParameter(1));
$qb->execute();
$qb = $db->getConnection()->createQueryBuilder();
$qb->delete('activities_users');
$qb->where('deleted = ' . $qb->createPositionalParameter(1));
$qb->execute();
$qb = $db->getConnection()->createQueryBuilder();
$qb->delete('comments');
$qb->where('deleted = ' . $qb->createPositionalParameter(1));
$qb->execute();
$qbSub = $db->getConnection()->createQueryBuilder();
$qbSub->select('parent_id');
$qbSub->from('comments');
// delete all non posts, that are old and that do not have an existing comment
$qb = $db->getConnection()->createQueryBuilder();
$qb->delete('activities');
// find records without specifics activity_type
$qb->where(
$qb->expr()->notIn(
'activity_type',
$qb->createPositionalParameter(['post', 'link', 'unlink'], Connection::PARAM_STR_ARRAY)
)
);
$qb->andWhere('date_entered < ' . $qb->createPositionalParameter($date_entered_keep));
$qb->andWhere($qb->expr()->notIn('id', $qbSub->getSQL()));
$qb->execute();
$qbSub = $db->getConnection()->createQueryBuilder();
$qbSub->select('id');
$qbSub->from('activities');
// delete all activities_users where id is not in activities (after the trimming)
$qb = $db->getConnection()->createQueryBuilder();
$qb->delete('activities_users');
$qb->where($qb->expr()->notIn('activity_id', $qbSub->getSQL()));
$qb->execute();
// find current count and deduct to the start values
foreach ($results as $table => $value) {
$qb = $db->getConnection()->createQueryBuilder();
$qb->select('COUNT(id) as count');
$qb->from($table);
$res = $qb->execute();
if ($row = $res->fetch()) {
$results[$table] -= $row['count'];
}
}
echo 'Summary of records removed: ' . PHP_EOL;
foreach ($results as $heading => $value) {
echo $value . ' records from ' . $heading . PHP_EOL;
}
print('Completed in ' . (int)(microtime(true) - $start_time) . ' seconds.' . PHP_EOL);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.