Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Find records across all tables with acl_team_set_id not null, and their teams
<?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);
@sehrishabdul

This comment has been minimized.

Copy link

@sehrishabdul sehrishabdul commented Oct 10, 2019

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

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.