Helper script: MySQL table sizes per bin

With this script, you can get a quick overview of the bins on your TCAT instance and how much 'physical' space they use (in terms of MySQL table size)

<?php

$config = file_get_contents('/var/www/dmi-tcat/config.php');

preg_match('/\\$dbuser\s*=\s*"([^"]+)";/siU', $config, $match)[1];
$db_user = $match[1];

preg_match('/\\$dbpass\s*=\s*"([^"]+)";/siU', $config, $match)[1];
$db_password = $match[1];

preg_match('/\\$database\s*=\s*"([^"]+)";/siU', $config, $match)[1];
$db_database = $match[1];

preg_match('/\\$hostname\s*=\s*"([^"]+)";/siU', $config, $match)[1];
$db_host = $match[1];

$db = new \PDO('mysql:host='.$db_host.';dbname='.$db_database, $db_user, $db_password);

$host = trim(file_get_contents('/etc/hostname'));

//get bins
$bins_raw = $db->query("SELECT querybin, active FROM tcat_query_bins")->fetchAll(\PDO::FETCH_ASSOC);
$bins = array_combine(
    array_map(function($row) { return $row['querybin']; }, $bins_raw),
    array_map(function($row) { return ['active' => $row['active']]; }, $bins_raw));

//get table sizes
$size_query = "SELECT TABLE_NAME as table_name, (DATA_LENGTH + INDEX_LENGTH) AS size FROM information_schema.TABLES WHERE TABLE_SCHEMA = '".$db_database. "'";
$table_sizes = $db->query($size_query)->fetchAll(\PDO::FETCH_ASSOC);
$table_sizes = array_combine(
    array_map(function($row) { return $row['table_name']; }, $table_sizes),
    array_map(function($row) { return $row['size']; }, $table_sizes)
);

$columns = ['hashtags', 'media', 'mentions', 'places', 'tweets', 'urls', 'withheld'];

foreach($bins as $name => $v) {
    foreach($columns as $table) {
        $key = $name . '_' . $table;
        $bins[$name][$table] = array_key_exists($key, $table_sizes) ? $table_sizes[$key] : '?';
    }
    $bins[$name]['_total'] = array_sum(array_values($bins[$name]));
}

$columns = array_unique($columns);
array_unshift($columns, 'host', 'bin', 'active', '_total');

echo implode("\t", $columns)."\n";
foreach($bins as $name => $stats) {
    $row = [];
    foreach($columns as $column) {
        if($column == 'bin') {
            $row[] = $name;
        } elseif($column == 'host') {
            $row[] = $host;
        } else {
            $row[] = array_key_exists($column, $stats) ? $stats[$column] : '?';
        }
    }
    echo implode("\t", $row) . "\n";
}