Last active
October 30, 2019 08:16
-
-
Save esimonetti/9eb520a7a5640dd6bafca78cc0bbcc43 to your computer and use it in GitHub Desktop.
Count records per Sugar table and output in json and csv format for reporting purposes - USE TOOTHPASTE INSTEAD! https://github.com/esimonetti/toothpaste
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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