Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Count records per Sugar table and output in json and csv format for reporting purposes - USE TOOTHPASTE INSTEAD! https://github.com/esimonetti/toothpaste
<?php
// Enrico Simonetti
// enricosimonetti.com
//
// 2018-09-19 on Sugar 8.0.0
//
// CLI script to output the current record count per SQL table, in json and csv format
ini_set('memory_limit', '64M');
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 'Counting records on all database tables' . PHP_EOL;
$total = 0;
$results = [];
$db = DBManagerFactory::getInstance();
$tables = $db->getTablesArray();
asort($tables);
foreach ($tables as $table) {
$columns = $db->get_columns($table);
if ($key = array_search('id', array_column($columns, 'name'))) {
$count_field = 'id';
} else if ($key = array_search('name', array_column($columns, 'name'))) {
$count_field = 'name';
} else {
$count_field = '*';
}
if (!empty($count_field)) {
$qb = $db->getConnection()->createQueryBuilder();
$qb->select('COUNT(' . $count_field . ') as count');
$qb->from($table);
$res = $qb->execute();
if ($row = $res->fetch()) {
$results[$table] = $row['count'];
$total += $results[$table];
}
echo $table . ' has ' . $results[$table] . ' records' . PHP_EOL;
}
}
$results['total_db_records'] = $total;
echo 'The database has in total ' . $total . ' records' . PHP_EOL;
echo 'JSON output:' . PHP_EOL . PHP_EOL;
echo json_encode($results);
echo PHP_EOL . PHP_EOL;
echo 'CSV output:' . PHP_EOL . PHP_EOL;
echo '"table","count"'.PHP_EOL;
foreach ($results as $table => $count) {
echo '"' . $table . '","' . $count . '"' . PHP_EOL;
}
echo PHP_EOL . 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.