Find records across all tables with acl_team_set_id not null, and their teams

with_acl_team_set_id.php

<?php

// Enrico Simonetti
// enricosimonetti.com
//
// 2019-09-02 on Sugar 8.0.0
//
// CLI script to find records with acl_team_set_id

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 (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 'Counting records on all database tables with acl_team_set_id set' . PHP_EOL;

$total = 0;
$results = [];
$acls = [];

$db = DBManagerFactory::getInstance();

$tables = $db->getTablesArray();
asort($tables);

foreach ($tables as $table) {
    $columns = $db->get_columns($table);

    if ($key = array_search('acl_team_set_id', array_column($columns, 'name'))) {
        $qb = $db->getConnection()->createQueryBuilder();
        $qb->select(['id', 'acl_team_set_id']);
        $qb->from($table);
        $qb->where($qb->expr()->isNotNull('acl_team_set_id'));
        $res = $qb->execute();
	$results[$table] = 0;
        while ($row = $res->fetch()) {
            $results[$table] += 1;
            $total += 1;
	    // commented out as it could contain thousands of record ids with the same team set
            //$acls[$table][$row['acl_team_set_id']]['ids'][] = $row['id'];
            $acls[$table][$row['acl_team_set_id']]['teams'] = TeamSetManager::getTeamsFromSet($row['acl_team_set_id']);
        }

        echo $table . ' has ' . $results[$table] . ' records with acl_team_set_id set' . PHP_EOL;
    } else {
        echo $table . ' has no records with acl_team_set_id set' . PHP_EOL;
    }
}

echo 'The database has in total ' . $total . ' records with acl_team_set_id set' . PHP_EOL;

print_r($results);
print_r($acls);

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

Hi Enrico,

I want to get all the records with deleted bit 1 from all the main tables (not relationship/middle tables) and show them in a view. Similar script as this would take a lot of time if there are records in millions in tables. Any suggestion?

Thanks