yggdrasil/api/database/
status.rs

1#![allow(clippy::unused_unit, deprecated)]
2
3//! 数据库运行状态聚合查询（只读）。
4//!
5//! 全部查询走 `pg_catalog` / `pg_stat_*` / `schema_migrations`，零写、零风险。
6//! [`get_db_status`][crate::api::database::status::get_db_status] 在一次 server function 调用里聚合多组数据返回。
7
8use crate::api::error::ApiError;
9use chrono::{DateTime, Utc};
10use leptos::prelude::*;
11use serde::{Deserialize, Serialize};
12
13// 仅 server 构建用到：admin 鉴权 + DB 查询。WASM 侧的 server-function 客户端桩
14// 不解析这些符号，必须 gate 以避免在非 server 构建里找不到 server-only 符号。
15#[cfg(feature = "server")]
16use crate::api::auth::get_current_admin_user;
17#[cfg(feature = "server")]
18use crate::api::error::AppError;
19#[cfg(feature = "server")]
20use crate::db::pool::get_conn;
21
22/// 数据库状态聚合数据。
23#[derive(Serialize, Deserialize, Debug, Default, Clone)]
24pub struct DbStatus {
25    /// 当前数据库总大小（字节）。
26    pub db_size_bytes: i64,
27    /// 当前数据库的活跃连接数。
28    pub total_connections: i32,
29    /// PG 配置的最大连接数（`max_connections`）。
30    pub max_connections: i32,
31    /// 已应用的最新迁移版本（`schema_migrations.version`）。
32    pub migration_version: Option<String>,
33    /// 最新迁移的应用时间。
34    #[cfg_attr(
35        target_arch = "wasm32",
36        serde(
37            default,
38            deserialize_with = "crate::models::rfc3339::deserialize_option"
39        )
40    )]
41    pub migration_applied_at: Option<DateTime<Utc>>,
42    /// 用户表清单（按总大小降序）。
43    pub tables: Vec<TableInfo>,
44    /// 索引占用 Top N。
45    pub top_indexes: Vec<IndexInfo>,
46    /// 活跃连接列表（已过滤掉自身这条查询）。
47    pub active_connections: Vec<ConnInfo>,
48}
49
50/// 单张表的统计信息。
51#[derive(Serialize, Deserialize, Debug, Clone)]
52pub struct TableInfo {
53    pub name: String,
54    /// 行数：total_size 小于阈值时为真实 COUNT(*)，否则回退 reltuples 估算（见 row_count_estimated）。
55    pub row_count: i64,
56    /// true 表示 row_count 是 reltuples 估算值（大表回退，未 ANALYZE 时 reltuples=-1 会显示为估算）。
57    pub row_count_estimated: bool,
58    pub table_size_bytes: i64,
59    pub index_size_bytes: i64,
60    pub total_size_bytes: i64,
61    #[cfg_attr(
62        target_arch = "wasm32",
63        serde(
64            default,
65            deserialize_with = "crate::models::rfc3339::deserialize_option"
66        )
67    )]
68    pub last_vacuum: Option<DateTime<Utc>>,
69    #[cfg_attr(
70        target_arch = "wasm32",
71        serde(
72            default,
73            deserialize_with = "crate::models::rfc3339::deserialize_option"
74        )
75    )]
76    pub last_analyze: Option<DateTime<Utc>>,
77    pub dead_tuples: i64,
78    pub live_tuples: i64,
79}
80
81/// 索引占用信息。
82#[derive(Serialize, Deserialize, Debug, Clone)]
83pub struct IndexInfo {
84    pub name: String,
85    pub table_name: String,
86    pub size_bytes: i64,
87}
88
89/// 单条活跃连接信息。
90#[derive(Serialize, Deserialize, Debug, Clone)]
91pub struct ConnInfo {
92    pub pid: i32,
93    pub user: String,
94    pub state: Option<String>,
95    pub query: Option<String>,
96    /// 当前查询已运行秒数（无查询时为 None）。
97    pub query_duration_secs: Option<f64>,
98}
99
100/// 获取数据库运行状态（只读，管理员）。
101#[server(GetDbStatus, endpoint = "get_db_status", input = leptos::server_fn::codec::Json)]
102pub async fn get_db_status() -> Result<DbStatus, ApiError> {
103    let _user = get_current_admin_user().await?;
104
105    #[cfg(feature = "server")]
106    {
107        let client = get_conn().await.map_err(AppError::db_conn)?;
108
109        // 数据库总大小
110        let db_size: i64 = client
111            .query_one("SELECT pg_database_size(current_database())", &[])
112            .await
113            .map_err(AppError::query)?
114            .get(0);
115
116        // 当前库连接数 + 全局最大连接数。
117        // count(*) 原生返回 bigint(int8)，与下方 setting::int 一并显式转 int4，
118        // 以匹配 total_connections/max_connections 的 i32 类型（否则 FromSql 反序列化失败）。
119        let conn_row = client
120            .query_one(
121                "SELECT count(*)::int, \
122                 (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') \
123                 FROM pg_stat_activity WHERE datname = current_database()",
124                &[],
125            )
126            .await
127            .map_err(AppError::query)?;
128        let total_conn: i32 = conn_row.get(0);
129        let max_conn: i32 = conn_row.get(1);
130
131        // 最新迁移版本（schema_migrations 由 migrate.rs 创建）
132        let migration = client
133            .query_opt(
134                "SELECT version, applied_at FROM schema_migrations \
135                 ORDER BY applied_at DESC LIMIT 1",
136                &[],
137            )
138            .await
139            .map_err(AppError::query)?;
140        let (migration_version, migration_applied_at) = match migration {
141            Some(row) => (Some(row.get(0)), Some(row.get(1))),
142            None => (None, None),
143        };
144
145        // 表清单：大小/统计走 catalog；行数对小表用真实 COUNT(*)，大表回退 reltuples 估算。
146        // 阈值 100MB：超过则 COUNT(*) 成本过高（全表扫描），降级为估算值并标注。
147        const COUNT_SIZE_THRESHOLD: i64 = 100 * 1024 * 1024;
148        let table_rows = client
149            .query(
150                "SELECT c.relname, c.reltuples::bigint, pg_relation_size(c.oid), \
151                 pg_total_relation_size(c.oid) - pg_relation_size(c.oid), \
152                 pg_total_relation_size(c.oid), s.last_vacuum, s.last_analyze, \
153                 s.n_dead_tup, s.n_live_tup \
154                 FROM pg_class c \
155                 JOIN pg_namespace n ON n.oid = c.relnamespace \
156                 LEFT JOIN pg_stat_user_tables s ON s.relid = c.oid \
157                 WHERE c.relkind = 'r' AND n.nspname = 'public' \
158                 ORDER BY pg_total_relation_size(c.oid) DESC",
159                &[],
160            )
161            .await
162            .map_err(AppError::query)?;
163        let tables = {
164            let mut out = Vec::with_capacity(table_rows.len());
165            for r in table_rows {
166                let name: String = r.get(0);
167                let reltuples: i64 = r.get(1);
168                let total_size: i64 = r.get(4);
169                // 小表：真实 COUNT(*)；大表：回退 reltuples 估算。
170                // relname 来自 pg_class 可信，但表名可能含大写/特殊字符，需 PG 标识符转义（双引号包裹，内部双引号双写）。
171                let (row_count, estimated) = if total_size < COUNT_SIZE_THRESHOLD {
172                    let ident = format!("\"{}\"", name.replace('"', "\"\""));
173                    let cnt: i64 = client
174                        .query_one(&format!("SELECT count(*)::bigint FROM {}", ident), &[])
175                        .await
176                        .map_err(AppError::query)?
177                        .get(0);
178                    (cnt, false)
179                } else {
180                    (reltuples, true)
181                };
182                out.push(TableInfo {
183                    name,
184                    row_count,
185                    row_count_estimated: estimated,
186                    table_size_bytes: r.get(2),
187                    index_size_bytes: r.get(3),
188                    total_size_bytes: total_size,
189                    last_vacuum: r.get(5),
190                    last_analyze: r.get(6),
191                    dead_tuples: r.get(7),
192                    live_tuples: r.get(8),
193                });
194            }
195            out
196        };
197
198        // 索引占用 Top 10
199        let index_rows = client
200            .query(
201                "SELECT c.relname AS index_name, cl.relname AS table_name, \
202                 pg_relation_size(c.oid) \
203                 FROM pg_class c \
204                 JOIN pg_index i ON i.indexrelid = c.oid \
205                 JOIN pg_class cl ON cl.oid = i.indrelid \
206                 JOIN pg_namespace n ON n.oid = cl.relnamespace \
207                 WHERE n.nspname = 'public' \
208                 ORDER BY pg_relation_size(c.oid) DESC LIMIT 10",
209                &[],
210            )
211            .await
212            .map_err(AppError::query)?;
213        let top_indexes = index_rows
214            .into_iter()
215            .map(|r| IndexInfo {
216                name: r.get(0),
217                table_name: r.get(1),
218                size_bytes: r.get(2),
219            })
220            .collect();
221
222        // 活跃连接（过滤自身 pid，避免循环显示）。
223        // extract(epoch FROM ...) 原生返回 numeric(decimal)，tokio-postgres 无 FromSql<f64>
224        // 实现该类型；显式 ::double precision 转 float8 以匹配 query_duration_secs 的 f64。
225        let conn_rows = client
226            .query(
227                "SELECT pid, usename, state, query, \
228                 extract(epoch FROM now() - query_start)::double precision \
229                 FROM pg_stat_activity \
230                 WHERE datname = current_database() AND pid <> pg_backend_pid() \
231                 ORDER BY query_start DESC NULLS LAST LIMIT 50",
232                &[],
233            )
234            .await
235            .map_err(AppError::query)?;
236        let active_connections = conn_rows
237            .into_iter()
238            .map(|r| ConnInfo {
239                pid: r.get(0),
240                user: r.get::<_, Option<String>>(1).unwrap_or_default(),
241                state: r.get(2),
242                query: r.get(3),
243                query_duration_secs: r.get(4),
244            })
245            .collect();
246
247        Ok(DbStatus {
248            db_size_bytes: db_size,
249            total_connections: total_conn,
250            max_connections: max_conn,
251            migration_version,
252            migration_applied_at,
253            tables,
254            top_indexes,
255            active_connections,
256        })
257    }
258    #[cfg(not(feature = "server"))]
259    {
260        Ok(DbStatus::default())
261    }
262}
yggdrasil/api/database/status.rs

yggdrasil/api/database/
status.rs
