CLI script to find and delete orphan records on the custom tables of Sugar modules - Tested up to 35M records in one script run, at about 5,200 records deleted per second

delete_orphans.php

<?php

// Enrico Simonetti
// enricosimonetti.com
//
// 2017-12-13 on Sugar 7.9.2.0
//
// CLI script to find and delete orphan records on the custom tables of Sugar modules

function usage($error = '') {
    if(!empty($error)) print(PHP_EOL . 'Error: ' . $error . PHP_EOL);
    print('  php ' . __FILE__ . ' --instance /full/path' . 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:'));
if (!$o) 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();

$start_time = microtime(true);

echo 'Hunting down custom tables orphans...' . PHP_EOL;

global $beanList;
$full_module_list = array_merge($beanList, $app_list_strings['moduleList']);

$db = DBManagerFactory::getInstance();

$processed_tables = array();

$limit = 8000;
foreach($full_module_list as $module => $label) {
    $bean = BeanFactory::newBean($module);
    if(empty($processed_tables[$bean->table_name]) && method_exists($bean, 'hasCustomFields') && $bean->hasCustomFields()) {
        $processed_tables[$bean->table_name] = $bean->module_name;
        echo 'Table '.$bean->table_name.' has custom fields on '.$bean->get_custom_table_name().' seeking...'.PHP_EOL;

        $has_records = true;
        $counter = 0;
        while ($has_records) {
            $b1 = $db->getConnection()->createQueryBuilder();
            $b1->select(array('cstm_tbl.id_c'))->from($bean->get_custom_table_name(), 'cstm_tbl');
            $b1->leftJoin('cstm_tbl', $bean->table_name, 'core_tbl', 'cstm_tbl.id_c = core_tbl.id');
            $b1->where('core_tbl.id is null');
            $b1->setMaxResults($limit);

            echo 'Executing '.$b1->getSQL();
            $res = $b1->execute();
    
            $current_orphans = array();
            while ($row = $res->fetch()) {
                $current_orphans[] = $row['id_c'];
                $counter++;
            }

            if (!empty($current_orphans)) {
                $has_records = true;
                $b2 = $db->getConnection()->createQueryBuilder();
                $b2->delete($bean->get_custom_table_name());
                $b2->where(
                    $b2->expr()->in(
                        'id_c',
                        $b2->createPositionalParameter($current_orphans, \Doctrine\DBAL\Connection::PARAM_STR_ARRAY)
                    )
                );
                $b2->execute();
                echo '. Deleted '.$counter.' records from '.$bean->get_custom_table_name().'.'.PHP_EOL.PHP_EOL;
            } else {
                $has_records = false;
            }
        }

        if(!empty($orphans)) {
            echo PHP_EOL.'Found and deleted '.count($orphans).' orphan record(s) from '.$bean->get_custom_table_name().PHP_EOL.PHP_EOL;
        }
    }
}

print('Completed in ' . (int)(microtime(true) - $start_time) . ' seconds.' . PHP_EOL);