main:
- card:
- h1: SID Database Dump
- php: |
require 'data/db.inc';
try {
$pdo = sid_open_db();
} catch (Exception $e) {
echo '<div class="alert alert-danger">db open failed: '
. htmlspecialchars($e->getMessage()) . '</div>';
return;
}
$ts = function($v) {
if (!$v) return '';
return date('Y-m-d H:i:s', (int)$v);
};
$esc = fn($v) => htmlspecialchars((string)($v ?? ''));
$renderTable = function(string $title, array $cols, iterable $rows,
?callable $fmt = null) use ($esc) {
echo '<h3 class="mt-4">' . $esc($title) . '</h3>';
$empty = true;
echo '<div class="table-responsive"><table class="table table-sm table-striped table-bordered">';
echo '<thead><tr>';
foreach ($cols as $c) echo '<th>' . $esc($c) . '</th>';
echo '</tr></thead><tbody>';
foreach ($rows as $row) {
$empty = false;
echo '<tr>';
foreach ($cols as $c) {
$val = $row[$c] ?? null;
if ($fmt) $val = $fmt($c, $val, $row);
echo '<td>' . $val . '</td>';
}
echo '</tr>';
}
echo '</tbody></table></div>';
if ($empty) echo '<p class="text-muted"><em>(no rows)</em></p>';
};
// Devices — current state per MAC.
$devices = $pdo->query('SELECT mac, first_seen, last_seen, last_platform,
last_version, last_event, report_count, crash_count
FROM devices
ORDER BY last_seen DESC')->fetchAll(PDO::FETCH_ASSOC);
$renderTable('Devices (' . count($devices) . ')',
['mac','first_seen','last_seen','last_platform','last_version',
'last_event','report_count','crash_count'],
$devices,
function($col, $val, $row) use ($esc, $ts) {
if ($col === 'first_seen' || $col === 'last_seen') return $esc($ts($val));
if ($col === 'crash_count' && $val > 0)
return '<span class="badge bg-danger">' . $esc($val) . '</span>';
return $esc($val);
});
// Firmware versions — release catalog, upgrade edges, auto-registered rows.
$fwv = $pdo->query('SELECT id, platform, version, replaces, filename,
sha256, released_at, notes
FROM firmware_versions
ORDER BY platform, released_at DESC')->fetchAll(PDO::FETCH_ASSOC);
$renderTable('Firmware Versions (' . count($fwv) . ')',
['id','platform','version','replaces','filename','sha256','released_at','notes'],
$fwv,
function($col, $val, $row) use ($esc, $ts) {
if ($col === 'released_at') return $esc($ts($val));
if ($col === 'replaces' && $val === null)
return '<span class="text-muted">—</span>';
if ($col === 'filename' && $val === '')
return '<span class="text-muted">—</span>';
return $esc($val);
});
// Firmware downloads — audit log.
$fwd = $pdo->query('SELECT id, downloaded_at, mac, platform, version, bytes, ok
FROM firmware_downloads
ORDER BY downloaded_at DESC
LIMIT 500')->fetchAll(PDO::FETCH_ASSOC);
$renderTable('Firmware Downloads (last 500, total ' .
$pdo->query('SELECT COUNT(*) FROM firmware_downloads')->fetchColumn() . ')',
['id','downloaded_at','mac','platform','version','bytes','ok'],
$fwd,
function($col, $val, $row) use ($esc, $ts) {
if ($col === 'downloaded_at') return $esc($ts($val));
if ($col === 'ok')
return $val
? '<span class="badge bg-success">ok</span>'
: '<span class="badge bg-danger">fail</span>';
return $esc($val);
});
// Reports — full blob history. Body rendered as collapsible pretty-JSON.
$reportCount = (int)$pdo->query('SELECT COUNT(*) FROM reports')->fetchColumn();
$reports = $pdo->query('SELECT id, received_at, mac, event, platform, version, body
FROM reports
ORDER BY received_at DESC
LIMIT 500')->fetchAll(PDO::FETCH_ASSOC);
$renderTable('Reports (last 500, total ' . $reportCount . ')',
['id','received_at','mac','event','platform','version','body'],
$reports,
function($col, $val, $row) use ($esc, $ts) {
if ($col === 'received_at') return $esc($ts($val));
if ($col === 'event' && $val === 'crash')
return '<span class="badge bg-danger">crash</span>';
if ($col === 'body') {
$decoded = json_decode($val ?? '', true);
$pretty = $decoded === null
? (string)$val
: json_encode($decoded, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES);
return '<details><summary class="text-muted">' .
strlen((string)$val) . ' bytes</summary>' .
'<pre class="small mb-0">' . $esc($pretty) . '</pre></details>';
}
return $esc($val);
});