<?php
/* ======================================================
마리아DB 데이터베이스 통합 관제 (Advanced Dynamic UI)
- DB: upbit_data
- 목적: DB 구조 확인, 용량 모니터링, 실시간 동적 관제
====================================================== */
error_reporting(E_ALL);
ini_set('display_errors', 1);
date_default_timezone_set('Asia/Seoul');
// [구조 및 코드 유지] DB 핸들러 로드
require_once '/home/www/DB/db_upbit.php';
$pdo = $db_upbit;
$DB_NAME = 'upbit_data';
/* 테이블 설명 정의 (유지보수용 배열) */
$TABLE_DESCRIPTIONS = [
'daemon_upbit_coin_1s' => '1초 시세 누적 테이블',
'daemon_upbit_coin_1m' => '1분 봉 테이블',
'daemon_upbit_coin_5m' => '5분 봉 테이블',
'daemon_upbit_Ticker' => '실시간 플랫폼 수신 테이블',
'daemon_trade_log' => '자동매매 주문 처리 로그',
'daemon_status' => '수집 프로세스 상태 관리'
];
/* 모든 컬럼 정보 미리 조회 (구조 유지) */
$columns_map = [];
$col_sql = "
SELECT TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY, COLUMN_DEFAULT, EXTRA, COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = :db
ORDER BY TABLE_NAME, ORDINAL_POSITION
";
$col_stmt = $pdo->prepare($col_sql);
$col_stmt->execute(['db' => $DB_NAME]);
while ($c = $col_stmt->fetch(PDO::FETCH_ASSOC)) {
$columns_map[$c['TABLE_NAME']][] = $c;
}
/* 테이블 목록 및 상세 메타데이터 조회 */
$table_sql = "
SELECT TABLE_NAME, TABLE_ROWS, ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb, ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,
ROUND(DATA_FREE / 1024 / 1024, 2) AS free_mb, ENGINE, CREATE_TIME, UPDATE_TIME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = :db
ORDER BY TABLE_NAME ASC
";
$table_stmt = $pdo->prepare($table_sql);
$table_stmt->execute(['db' => $DB_NAME]);
$tables = $table_stmt->fetchAll(PDO::FETCH_ASSOC);
$summary = ['count' => count($tables), 'total_size' => 0, 'total_free' => 0];
foreach ($tables as $t) {
$summary['total_size'] += ($t['data_mb'] + $t['index_mb']);
$summary['total_free'] += $t['free_mb'];
}
// 헤더 부분 포함
require_once '/home/www/GNU/_PAGE/head.php';
?>
<title>마리아DB 통합 관제 콘솔 - <?= $DB_NAME ?></title>
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.4.0/css/all.min.css">
<style>
/* [다크 UI 디자인 및 폰트 사이즈 고정 유지] */
:root {
--bg-color: #0b0f1a;
--card-bg: #161b2a;
--border-color: #2d3343;
--accent-blue: #38bdf8;
--accent-purple: #a855f7;
--accent-rose: #f43f5e;
--text-main: #e2e8f0;
--text-dim: #94a3b8;
}
body {
background-color: var(--bg-color);
color: var(--text-main);
font-family: 'Inter', 'Malgun Gothic', sans-serif;
padding-bottom: 40px; margin: 0;
font-size: 16px;
overflow-x: hidden;
}
@keyframes fadeInUp {
from { opacity: 0; transform: translateY(20px); }
to { opacity: 1; transform: translateY(0); }
}
.entrance-ani { animation: fadeInUp 0.6s ease backwards; }
.header-area {
display: flex; justify-content: space-between; align-items: center;
margin-bottom: 30px;
}
h1 { font-size: 32px; color: var(--accent-blue); margin: 0; letter-spacing: -1px; padding-left: 20px; }
h1 span { color: var(--text-dim); font-size: 16px; font-weight: normal; margin-left: 12px; }
.filter-box {
background: var(--card-bg);
border: 1px solid var(--border-color);
padding: 12px 18px;
border-radius: 6px;
color: white;
width: 350px;
outline: none;
font-size: 16px;
}
.btn-refresh {
background: linear-gradient(135deg, #0ea5e9, #2563eb);
color: white; border: none; padding: 12px 24px;
border-radius: 6px; cursor: pointer; font-weight: bold;
transition: 0.3s; font-size: 15px;
box-shadow: 0 4px 15px rgba(14, 165, 233, 0.3);
}
.btn-refresh:hover { transform: scale(1.05); filter: brightness(1.2); }
.summary-grid { display: grid; grid-template-columns: repeat(3, 1fr); gap: 20px; margin-bottom: 30px; }
.summary-card {
background: var(--card-bg); padding: 25px; border-radius: 7px;
border: 1px solid var(--border-color); transition: 0.3s;
}
.summary-card:hover { border-color: var(--accent-blue); background: #1c2336; }
.summary-label { font-size: 14px; color: var(--text-dim); margin-bottom: 10px; display: block; }
.summary-value { font-size: 28px; font-weight: 800; color: #fff; }
.table-wrapper {
background: var(--card-bg); border-radius: 8px;
border: 1px solid var(--border-color); overflow: hidden;
box-shadow: 0 20px 50px rgba(0,0,0,0.5);
}
table { width: 100%; border-collapse: collapse; font-size: 15px; }
th {
background: #1e2538; color: var(--text-dim);
padding: 18px 15px; text-align: left; font-size: 13px;
text-transform: uppercase; letter-spacing: 1px;
}
td { padding: 18px 15px; border-bottom: 1px solid var(--border-color); transition: 0.2s; }
tr.table-row:hover td { background: rgba(56, 189, 248, 0.05); }
.weight-bar-bg { width: 100%; height: 8px; background: #2d3343; border-radius: 4px; margin-top: 8px; }
.weight-bar-fill { height: 100%; background: var(--accent-blue); border-radius: 4px; }
.size-alert { color: var(--accent-rose); font-weight: bold; }
.frag-alert { color: var(--accent-purple); font-weight: bold; }
.row-highlight { color: #fbbf24; }
.btn-view {
background: #2d3343; color: var(--accent-blue); border: 1px solid var(--accent-blue);
padding: 8px 16px; border-radius: 5px; cursor: pointer; transition: 0.2s; font-size: 14px;
}
.btn-view:hover { background: var(--accent-blue); color: #000; }
.col-detail-row { display: none; background: #090c14 !important; }
.col-container { padding: 25px; animation: fadeInUp 0.3s ease; }
.col-table { width: 100%; border-radius: 6px; overflow: hidden; background: #161b2a; border: 1px solid #2d3343; font-size: 14px; }
.col-table th { background: #0f172a; color: #64748b; padding: 12px; }
.col-table td { border-bottom: 1px solid #1e2538; padding: 12px; color: #cbd5e1; }
.font-mono { font-family: 'JetBrains Mono', 'Courier New', monospace; }
.header-area, .summary-grid, .table-wrapper { margin:30px 50px 0px 50px; }
/* 전체 스크롤바 스타일 */
::-webkit-scrollbar { width: 10px; }
::-webkit-scrollbar-track { background: #0d1117; }
::-webkit-scrollbar-thumb { background: #30363d; border-radius: 6px; border: 2px solid #0d1117; }
::-webkit-scrollbar-thumb:hover { background: #8b949e; }
</style>
<body>
<div class="header-area entrance-ani" style="animation-delay: 0.1s;">
<h1><i class="fa-solid fa-link"></i> 마리아DB 통합 관제 콘솔 <span>[<?= $DB_NAME ?>]</span></h1>
<div style="display: flex; gap: 15px;">
<!-- [실시간 검색 입력 창] -->
<input type="text" id="tableSearch" class="filter-box" placeholder="테이블명 또는 설명으로 검색..." onkeyup="filterTable()">
<button class="btn-refresh" onclick="location.reload()">🔄 새로고침</button>
</div>
</div>
<div class="summary-grid">
<div class="summary-card entrance-ani" style="animation-delay: 0.2s;">
<span class="summary-label">전체 테이블 수</span>
<span class="summary-value"><?= number_format($summary['count']) ?> <small style="font-size: 14px; color: #64748b;">개</small></span>
</div>
<div class="summary-card entrance-ani" style="animation-delay: 0.3s;">
<span class="summary-label">사용 중인 용량</span>
<span class="summary-value"><?= number_format($summary['total_size'], 2) ?> <small style="font-size: 14px; color: #64748b;">MB</small></span>
</div>
<div class="summary-card entrance-ani" style="animation-delay: 0.4s;">
<span class="summary-label">파편화된 여유 공간</span>
<span class="summary-value" style="color: var(--accent-purple);"><?= number_format($summary['total_free'], 2) ?> <small style="font-size: 14px; color: #64748b;">MB</small></span>
</div>
</div>
<div class="table-wrapper entrance-ani" style="animation-delay: 0.5s;">
<table id="mainTable">
<thead>
<tr>
<th>분류</th>
<th>테이블 명세</th>
<th style="width: 150px;">용량 비중 (%)</th>
<th style="text-align: right;">행 수(Rows)</th>
<th style="text-align: right;">데이터 용량(MB)</th>
<th style="text-align: right;">파편화 용량</th>
<th style="text-align: center;">최종 갱신일</th>
<th style="text-align: center;">상세 구조</th>
</tr>
</thead>
<tbody>
<?php foreach ($tables as $index => $t):
$t_name = $t['TABLE_NAME'];
$desc = $TABLE_DESCRIPTIONS[$t_name] ?? '등록된 설명이 없습니다.';
$total_mb = $t['data_mb'] + $t['index_mb'];
$percent = ($summary['total_size'] > 0) ? ($total_mb / $summary['total_size']) * 100 : 0;
$row_class = ($t['TABLE_ROWS'] >= 100000) ? 'row-highlight' : '';
$is_heavy = ($total_mb >= 500);
?>
<!-- [데이터 속성에 설명(desc) 추가하여 검색 범위 확장] -->
<tr class="table-row" data-name="<?= strtolower($t_name) ?>" data-desc="<?= strtolower(htmlspecialchars($desc)) ?>">
<td style="color: var(--text-dim); font-size: 12px;">
<?= (strpos($t_name, 'daemon_upbit_coin_') === 0) ? "📈 시세 데이터" : "⚙️ 시스템 관리" ?>
</td>
<td>
<strong style="color: var(--accent-blue); font-size: 16px;"><?= $t_name ?></strong><br>
<span style="font-size: 12px; color: #64748b;"><?= htmlspecialchars($desc) ?></span>
</td>
<td>
<div style="font-size: 12px; color: #94a3b8;"><?= number_format($percent, 1) ?>%</div>
<div class="weight-bar-bg"><div class="weight-bar-fill" style="width: <?= $percent ?>%;"></div></div>
</td>
<td style="text-align: right;" class="font-mono <?= $row_class ?>"><?= number_format($t['TABLE_ROWS']) ?></td>
<td style="text-align: right;" class="font-mono <?= $is_heavy ? 'size-alert' : '' ?>"><?= number_format($total_mb, 2) ?></td>
<td style="text-align: right;" class="font-mono <?= ($t['free_mb'] > 10) ? 'frag-alert' : '' ?>"><?= number_format($t['free_mb'], 2) ?></td>
<td style="text-align: center;" class="font-mono" style="color: #64748b; font-size: 13px;"><?= $t['UPDATE_TIME'] ?: '-' ?></td>
<td style="text-align: center;">
<button class="btn-view" onclick="toggleCols('<?= $t_name ?>')">구조 보기</button>
</td>
</tr>
<tr id="cols-<?= $t_name ?>" class="col-detail-row">
<td colspan="8">
<div class="col-container">
<table class="col-table">
<thead>
<tr>
<th>순서</th>
<th>컬럼 명칭</th>
<th>데이터 타입</th>
<th>NULL</th>
<th>KEY</th>
<th>기본값</th>
<th>추가 속성</th>
<th>설명</th>
</tr>
</thead>
<tbody>
<?php if (isset($columns_map[$t_name])): ?>
<?php foreach ($columns_map[$t_name] as $col): ?>
<tr>
<td style="text-align: center; color: #475569;"><?= $col['ORDINAL_POSITION'] ?></td>
<td><strong style="color: #fff;"><?= $col['COLUMN_NAME'] ?></strong></td>
<td style="color: var(--accent-blue);"><?= $col['COLUMN_TYPE'] ?></td>
<td style="text-align: center;"><?= $col['IS_NULLABLE'] ?></td>
<td style="text-align: center; color: #fbbf24;"><?= $col['COLUMN_KEY'] ?></td>
<td><?= $col['COLUMN_DEFAULT'] ?? 'NULL' ?></td>
<td style="font-size: 12px; color: #64748b;"><?= $col['EXTRA'] ?></td>
<td style="font-size: 12px; color: #94a3b8; font-style: italic;"><?= $col['COLUMN_COMMENT'] ?: '-' ?></td>
</tr>
<?php endforeach; ?>
<?php endif; ?>
</tbody>
</table>
</div>
</td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
</div>
<script>
/* [실시간 검색 로직 수정: 테이블명(data-name) + 설명(data-desc) 통합 검색] */
function filterTable() {
const input = document.getElementById('tableSearch');
const filter = input.value.toLowerCase();
const rows = document.querySelectorAll('#mainTable tbody tr.table-row');
rows.forEach(row => {
const tableName = row.getAttribute('data-name');
const tableDesc = row.getAttribute('data-desc'); // 설명 데이터 획득
// 이름 또는 설명에 검색어가 포함되어 있는지 확인
if (tableName.includes(filter) || tableDesc.includes(filter)) {
row.style.display = "";
} else {
row.style.display = "none";
const nextRow = row.nextElementSibling;
if (nextRow && nextRow.classList.contains('col-detail-row')) {
nextRow.style.display = "none";
}
}
});
}
function toggleCols(tableName) {
const target = document.getElementById('cols-' + tableName);
if (!target) return;
const isVisible = target.style.display === 'table-row';
target.style.display = isVisible ? 'none' : 'table-row';
}
document.addEventListener('DOMContentLoaded', () => {
const rows = document.querySelectorAll('#mainTable tbody tr.table-row');
rows.forEach((row, idx) => {
if(idx < 25) {
row.style.animation = `fadeInUp 0.5s ease forwards`;
row.style.animationDelay = `${0.5 + (idx * 0.05)}s`;
row.style.opacity = "0";
}
});
});
</script>
</body>
<?php require_once '/home/www/GNU/_PAGE/tail.php'; ?>