Skip to main content

yang_base/database/
adoption.rs

1//! 存量 PostgreSQL schema 的接入报告。
2//!
3//! 只读扫描表、列、主键与索引,为迁移人员输出 Markdown 报告与字段声明建议。
4//! 不执行任何 DDL,也不写 `_yang_schema_binding`。
5
6use crate::error::BaseError;
7use sqlx::postgres::PgPool;
8use sqlx::FromRow;
9use std::collections::BTreeMap;
10
11/// 单列报告。
12#[derive(Debug, Clone)]
13pub struct PgAdoptionColumn {
14    /// 列名。
15    pub name: String,
16    /// `format_type` 规范化类型。
17    pub formatted_type: String,
18    /// `pg_type.typname`。
19    pub type_name: String,
20    /// `pg_type.typtype`(`b` 基础类型、`e` 枚举、`d` domain…)。
21    pub type_kind: String,
22    /// 是否允许 NULL。
23    pub nullable: bool,
24    /// 是否主键列。
25    pub primary_key: bool,
26    /// identity / serial 列。
27    pub auto_increment: bool,
28    /// 原生枚举标签。
29    pub enum_labels: Vec<String>,
30}
31
32/// 单索引报告。
33#[derive(Debug, Clone)]
34pub struct PgAdoptionIndex {
35    /// 索引名。
36    pub name: String,
37    /// 是否唯一。
38    pub unique: bool,
39    /// 列键。
40    pub columns: Vec<String>,
41    /// 表达式键。
42    pub expressions: Vec<String>,
43    /// 部分索引谓词。
44    pub predicate: Option<String>,
45}
46
47/// 单表报告。
48#[derive(Debug, Clone)]
49pub struct PgAdoptionTable {
50    /// 表名。
51    pub name: String,
52    /// 列。
53    pub columns: Vec<PgAdoptionColumn>,
54    /// 主键列(按声明顺序)。
55    pub primary_key: Vec<String>,
56    /// 索引。
57    pub indexes: Vec<PgAdoptionIndex>,
58}
59
60/// 整个 schema 的接入报告。
61#[derive(Debug, Clone)]
62pub struct PgAdoptionReport {
63    /// 扫描的 schema。
64    pub schema: String,
65    /// 表,按名称排序。
66    pub tables: Vec<PgAdoptionTable>,
67}
68
69impl PgAdoptionReport {
70    /// 渲染为 Markdown。
71    pub fn to_markdown(&self) -> String {
72        let mut output = String::new();
73        output.push_str(&format!(
74            "# PostgreSQL schema 接入报告: `{}`\n\n",
75            self.schema
76        ));
77        output.push_str(&format!("- 表数量: {}\n", self.tables.len()));
78        output.push_str(&format!(
79            "- 列数量: {}\n\n",
80            self.tables
81                .iter()
82                .map(|table| table.columns.len())
83                .sum::<usize>()
84        ));
85        for table in &self.tables {
86            output.push_str(&format!("## `{}`\n\n", table.name));
87            output.push_str("| 列 | 类型 | 可空 | 主键 | 建议声明 |\n|---|---|---|---|---|\n");
88            for column in &table.columns {
89                output.push_str(&format!(
90                    "| `{}` | `{}` | {} | {} | {} |\n",
91                    column.name,
92                    column.formatted_type,
93                    if column.nullable { "是" } else { "否" },
94                    if column.primary_key { "是" } else { "" },
95                    column.suggested_field().unwrap_or_else(|| {
96                        format!(
97                            "`opaque_column(\"{}\", \"{}\")`",
98                            column.name, column.formatted_type
99                        )
100                    })
101                ));
102            }
103            output.push('\n');
104            if !table.primary_key.is_empty() {
105                output.push_str(&format!(
106                    "- 主键: {}\n",
107                    table
108                        .primary_key
109                        .iter()
110                        .map(|column| format!("`{column}`"))
111                        .collect::<Vec<_>>()
112                        .join(", ")
113                ));
114            }
115            for index in &table.indexes {
116                output.push_str(&format!("- 索引 `{}`: {}\n", index.name, index.describe()));
117            }
118            output.push_str("\n> 默认值、CHECK、外键与列注释需要人工核对后补到定义中;opaque 列不会进入通用 CRUD/UI。\n\n");
119        }
120        output
121    }
122}
123
124impl PgAdoptionColumn {
125    /// 生成 schema-first DSL 的字段声明建议;无法安全映射时返回 `None`。
126    pub fn suggested_field(&self) -> Option<String> {
127        let required = if self.nullable { "" } else { ".required()" };
128        let primary = if self.primary_key {
129            ".primary_key()"
130        } else {
131            ""
132        };
133        let auto = if self.auto_increment {
134            ".auto_increment()"
135        } else {
136            ""
137        };
138        let base = match self.type_name.as_str() {
139            "int2" | "int4" => format!("Field::integer(\"{}\")", self.name),
140            "int8" => format!("Field::bigint(\"{}\")", self.name),
141            "float4" => format!("Field::float(\"{}\")", self.name),
142            "float8" => format!("Field::double(\"{}\")", self.name),
143            "bool" => format!("Field::boolean(\"{}\")", self.name),
144            "date" => format!("Field::date(\"{}\")", self.name),
145            "timestamptz" => format!("Field::datetime(\"{}\")", self.name),
146            // 无时区 timestamp 与框架 DateTime(TIMESTAMPTZ)语义不同,不自动建议。
147            "timestamp" => return None,
148            "json" | "jsonb" => format!("Field::json(\"{}\")", self.name),
149            "text" => format!("Field::text(\"{}\")", self.name),
150            "uuid" => format!("Field::uuid(\"{}\")", self.name),
151            "bytea" => format!("Field::bytes(\"{}\")", self.name),
152            "varchar" | "bpchar" => {
153                let length = type_length(&self.formatted_type)?;
154                format!("Field::string(\"{}\", {length})", self.name)
155            }
156            "numeric" => {
157                let (precision, scale) = numeric_precision_scale(&self.formatted_type)?;
158                format!("Field::decimal(\"{}\", {precision}, {scale})", self.name)
159            }
160            "_int2" | "_int4" => format!("Field::array(\"{}\", FieldType::Integer)", self.name),
161            "_int8" => format!("Field::array(\"{}\", FieldType::BigInt)", self.name),
162            "_float4" => format!("Field::array(\"{}\", FieldType::Float)", self.name),
163            "_float8" => format!("Field::array(\"{}\", FieldType::Double)", self.name),
164            "_bool" => format!("Field::array(\"{}\", FieldType::Boolean)", self.name),
165            "_text" => format!("Field::array(\"{}\", FieldType::Text)", self.name),
166            "_uuid" => format!("Field::array(\"{}\", FieldType::Uuid)", self.name),
167            "_bytea" => format!("Field::array(\"{}\", FieldType::Bytes)", self.name),
168            _ if self.type_kind == "e" => {
169                let values = self
170                    .enum_labels
171                    .iter()
172                    .map(|value| format!("\"{value}\""))
173                    .collect::<Vec<_>>()
174                    .join(", ");
175                format!(
176                    "Field::enumeration(\"{}\", [{}]).native_enum(\"{}\")",
177                    self.name, values, self.type_name
178                )
179            }
180            _ => return None,
181        };
182        Some(format!("{base}{required}{primary}{auto}"))
183    }
184}
185
186impl PgAdoptionIndex {
187    fn describe(&self) -> String {
188        let mut kind = if self.unique { "唯一" } else { "普通" };
189        if self.predicate.is_some() {
190            kind = if self.unique {
191                "部分唯一"
192            } else {
193                "部分"
194            };
195        }
196        if !self.expressions.is_empty() {
197            kind = if self.unique {
198                "表达式唯一"
199            } else {
200                "表达式"
201            };
202        }
203        let keys = self
204            .columns
205            .iter()
206            .map(|column| format!("`{column}`"))
207            .chain(
208                self.expressions
209                    .iter()
210                    .map(|expression| format!("`{expression}`")),
211            )
212            .collect::<Vec<_>>()
213            .join(", ");
214        let predicate = self
215            .predicate
216            .as_ref()
217            .map(|predicate| format!(" WHERE {predicate}"))
218            .unwrap_or_default();
219        format!("{kind} ({keys}){predicate}")
220    }
221}
222
223/// 只读扫描 PostgreSQL schema 并生成接入报告。
224pub async fn describe_postgres_schema(
225    database: &yang_db::postgres::Database,
226    schema: Option<&str>,
227) -> Result<PgAdoptionReport, BaseError> {
228    fetch_report(database.pool(), schema).await
229}
230
231async fn fetch_report(pool: &PgPool, schema: Option<&str>) -> Result<PgAdoptionReport, BaseError> {
232    #[derive(FromRow)]
233    struct TableRow {
234        table_name: String,
235    }
236    #[derive(FromRow)]
237    struct ColumnRow {
238        table_name: String,
239        column_name: String,
240        type_name: String,
241        type_kind: String,
242        formatted_type: String,
243        nullable: bool,
244        auto_increment: bool,
245    }
246    #[derive(FromRow)]
247    struct EnumRow {
248        type_name: String,
249        label: String,
250    }
251    #[derive(FromRow)]
252    struct PrimaryRow {
253        table_name: String,
254        column_name: String,
255    }
256    #[derive(FromRow)]
257    struct IndexRow {
258        table_name: String,
259        index_name: String,
260        is_unique: bool,
261        predicate: Option<String>,
262        column_name: Option<String>,
263        expression: Option<String>,
264    }
265
266    let schema: String = sqlx::query_scalar("SELECT COALESCE($1::text, current_schema())")
267        .bind(schema)
268        .fetch_one(pool)
269        .await
270        .map_err(|error| BaseError::DatabaseQueryFailed(error.into()))?;
271
272    let table_rows: Vec<TableRow> = sqlx::query_as(
273        "SELECT c.relname::text AS table_name \
274         FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace \
275         WHERE n.nspname = $1 AND c.relkind IN ('r', 'p') AND NOT c.relispartition \
276         ORDER BY c.relname",
277    )
278    .bind(&schema)
279    .fetch_all(pool)
280    .await
281    .map_err(|error| BaseError::DatabaseQueryFailed(error.into()))?;
282
283    let column_rows: Vec<ColumnRow> = sqlx::query_as(
284        "SELECT c.relname::text AS table_name, a.attname::text AS column_name, \
285                t.typname::text AS type_name, t.typtype::text AS type_kind, \
286                format_type(a.atttypid, a.atttypmod) AS formatted_type, \
287                NOT a.attnotnull AS nullable, \
288                (a.attidentity <> '' OR COALESCE(pg_get_expr(d.adbin, d.adrelid) LIKE 'nextval(%', false)) \
289                  AS auto_increment \
290         FROM pg_attribute a \
291         JOIN pg_class c ON c.oid = a.attrelid \
292         JOIN pg_namespace n ON n.oid = c.relnamespace \
293         JOIN pg_type t ON t.oid = a.atttypid \
294         LEFT JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum \
295         WHERE n.nspname = $1 AND c.relkind IN ('r', 'p') \
296           AND a.attnum > 0 AND NOT a.attisdropped \
297         ORDER BY c.relname, a.attnum",
298    )
299    .bind(&schema)
300    .fetch_all(pool)
301    .await
302    .map_err(|error| BaseError::DatabaseQueryFailed(error.into()))?;
303
304    let enum_rows: Vec<EnumRow> = sqlx::query_as(
305        "SELECT t.typname::text AS type_name, e.enumlabel::text AS label \
306         FROM pg_type t \
307         JOIN pg_namespace n ON n.oid = t.typnamespace \
308         JOIN pg_enum e ON e.enumtypid = t.oid \
309         WHERE n.nspname = $1 ORDER BY t.typname, e.enumsortorder",
310    )
311    .bind(&schema)
312    .fetch_all(pool)
313    .await
314    .map_err(|error| BaseError::DatabaseQueryFailed(error.into()))?;
315    let mut enum_labels = BTreeMap::<String, Vec<String>>::new();
316    for row in enum_rows {
317        enum_labels
318            .entry(row.type_name)
319            .or_default()
320            .push(row.label);
321    }
322
323    let primary_rows: Vec<PrimaryRow> = sqlx::query_as(
324        "SELECT c.relname::text AS table_name, a.attname::text AS column_name \
325         FROM pg_constraint con \
326         JOIN pg_class c ON c.oid = con.conrelid \
327         JOIN pg_namespace n ON n.oid = c.relnamespace \
328         JOIN unnest(con.conkey) WITH ORDINALITY AS key(attnum, ord) ON true \
329         JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = key.attnum \
330         WHERE con.contype = 'p' AND n.nspname = $1 \
331         ORDER BY c.relname, key.ord",
332    )
333    .bind(&schema)
334    .fetch_all(pool)
335    .await
336    .map_err(|error| BaseError::DatabaseQueryFailed(error.into()))?;
337    let mut primary_keys = BTreeMap::<String, Vec<String>>::new();
338    for row in primary_rows {
339        primary_keys
340            .entry(row.table_name)
341            .or_default()
342            .push(row.column_name);
343    }
344
345    let index_rows: Vec<IndexRow> = sqlx::query_as(
346        "SELECT c.relname::text AS table_name, i.relname::text AS index_name, \
347                ix.indisunique AS is_unique, \
348                pg_get_expr(ix.indpred, ix.indrelid) AS predicate, \
349                a.attname::text AS column_name, \
350                CASE WHEN key.attnum = 0 \
351                     THEN pg_get_indexdef(ix.indexrelid, key.ord::integer, true) \
352                     ELSE NULL END AS expression \
353         FROM pg_index ix \
354         JOIN pg_class c ON c.oid = ix.indrelid \
355         JOIN pg_namespace n ON n.oid = c.relnamespace \
356         JOIN pg_class i ON i.oid = ix.indexrelid \
357         JOIN unnest(ix.indkey::smallint[]) WITH ORDINALITY AS key(attnum, ord) ON true \
358         LEFT JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = key.attnum \
359         WHERE n.nspname = $1 AND NOT ix.indisprimary \
360           AND NOT EXISTS ( \
361               SELECT 1 FROM pg_constraint con \
362               WHERE con.conindid = ix.indexrelid AND con.contype IN ('u', 'x', 'p') \
363           ) \
364           AND key.ord <= ix.indnkeyatts::bigint \
365         ORDER BY c.relname, i.relname, key.ord",
366    )
367    .bind(&schema)
368    .fetch_all(pool)
369    .await
370    .map_err(|error| BaseError::DatabaseQueryFailed(error.into()))?;
371    let mut index_map =
372        BTreeMap::<(String, String), (bool, Option<String>, Vec<String>, Vec<String>)>::new();
373    for row in index_rows {
374        let entry = index_map
375            .entry((row.table_name, row.index_name))
376            .or_insert_with(|| (row.is_unique, None, Vec::new(), Vec::new()));
377        entry.0 = row.is_unique;
378        if entry.1.is_none() {
379            entry.1 = row.predicate;
380        }
381        if let Some(column) = row.column_name {
382            entry.2.push(column);
383        }
384        if let Some(expression) = row.expression {
385            entry.3.push(expression);
386        }
387    }
388
389    let mut tables = table_rows
390        .into_iter()
391        // 框架自己的登记表不属于业务接入范围。
392        .filter(|row| !row.table_name.starts_with("_yang_"))
393        .map(|row| {
394            let columns = column_rows
395                .iter()
396                .filter(|column| column.table_name == row.table_name)
397                .map(|column| PgAdoptionColumn {
398                    name: column.column_name.clone(),
399                    formatted_type: column.formatted_type.clone(),
400                    type_name: column.type_name.clone(),
401                    type_kind: column.type_kind.clone(),
402                    nullable: column.nullable,
403                    primary_key: primary_keys
404                        .get(&row.table_name)
405                        .is_some_and(|keys| keys.contains(&column.column_name)),
406                    auto_increment: column.auto_increment,
407                    enum_labels: enum_labels
408                        .get(&column.type_name)
409                        .cloned()
410                        .unwrap_or_default(),
411                })
412                .collect::<Vec<_>>();
413            let indexes = index_map
414                .iter()
415                .filter(|((table_name, _), _)| table_name == &row.table_name)
416                .map(
417                    |((_, name), (unique, predicate, columns, expressions))| PgAdoptionIndex {
418                        name: name.clone(),
419                        unique: *unique,
420                        columns: columns.clone(),
421                        expressions: expressions.clone(),
422                        predicate: predicate.clone(),
423                    },
424                )
425                .collect();
426            PgAdoptionTable {
427                name: row.table_name.clone(),
428                columns,
429                primary_key: primary_keys
430                    .get(&row.table_name)
431                    .cloned()
432                    .unwrap_or_default(),
433                indexes,
434            }
435        })
436        .collect::<Vec<_>>();
437    tables.sort_by(|left, right| left.name.cmp(&right.name));
438    Ok(PgAdoptionReport { schema, tables })
439}
440
441fn type_length(formatted_type: &str) -> Option<usize> {
442    formatted_type
443        .rsplit_once('(')?
444        .1
445        .trim_end_matches(')')
446        .parse()
447        .ok()
448}
449
450fn numeric_precision_scale(formatted_type: &str) -> Option<(u8, u8)> {
451    let inner = formatted_type.rsplit_once('(')?.1.trim_end_matches(')');
452    let (precision, scale) = inner.split_once(',')?;
453    Some((precision.trim().parse().ok()?, scale.trim().parse().ok()?))
454}
455
456#[cfg(test)]
457mod tests {
458    use super::*;
459
460    fn column(type_name: &str, formatted_type: &str, type_kind: &str) -> PgAdoptionColumn {
461        PgAdoptionColumn {
462            name: "sample".to_string(),
463            formatted_type: formatted_type.to_string(),
464            type_name: type_name.to_string(),
465            type_kind: type_kind.to_string(),
466            nullable: false,
467            primary_key: false,
468            auto_increment: false,
469            enum_labels: Vec::new(),
470        }
471    }
472
473    #[test]
474    fn common_types_map_to_schema_dsl() {
475        assert_eq!(
476            column("varchar", "character varying(32)", "b").suggested_field(),
477            Some("Field::string(\"sample\", 32).required()".to_string())
478        );
479        assert_eq!(
480            column("numeric", "numeric(12,3)", "b").suggested_field(),
481            Some("Field::decimal(\"sample\", 12, 3).required()".to_string())
482        );
483        assert_eq!(
484            column("_int8", "bigint[]", "b").suggested_field(),
485            Some("Field::array(\"sample\", FieldType::BigInt).required()".to_string())
486        );
487    }
488
489    #[test]
490    fn enum_and_unsupported_types_are_distinguished() {
491        let mut status = column("order_status", "order_status", "e");
492        status.enum_labels = vec!["pending".to_string(), "paid".to_string()];
493        assert_eq!(
494            status.suggested_field(),
495            Some(
496                "Field::enumeration(\"sample\", [\"pending\", \"paid\"]).native_enum(\"order_status\").required()"
497                    .to_string()
498            )
499        );
500        assert_eq!(column("inet", "inet", "b").suggested_field(), None);
501        // 无时区 timestamp 与框架 DateTime 语义不一致,必须留给人工决策。
502        assert_eq!(
503            column("timestamp", "timestamp without time zone", "b").suggested_field(),
504            None
505        );
506    }
507}