1use crate::error::BaseError;
7use sqlx::postgres::PgPool;
8use sqlx::FromRow;
9use std::collections::BTreeMap;
10
11#[derive(Debug, Clone)]
13pub struct PgAdoptionColumn {
14 pub name: String,
16 pub formatted_type: String,
18 pub type_name: String,
20 pub type_kind: String,
22 pub nullable: bool,
24 pub primary_key: bool,
26 pub auto_increment: bool,
28 pub enum_labels: Vec<String>,
30}
31
32#[derive(Debug, Clone)]
34pub struct PgAdoptionIndex {
35 pub name: String,
37 pub unique: bool,
39 pub columns: Vec<String>,
41 pub expressions: Vec<String>,
43 pub predicate: Option<String>,
45}
46
47#[derive(Debug, Clone)]
49pub struct PgAdoptionTable {
50 pub name: String,
52 pub columns: Vec<PgAdoptionColumn>,
54 pub primary_key: Vec<String>,
56 pub indexes: Vec<PgAdoptionIndex>,
58}
59
60#[derive(Debug, Clone)]
62pub struct PgAdoptionReport {
63 pub schema: String,
65 pub tables: Vec<PgAdoptionTable>,
67}
68
69impl PgAdoptionReport {
70 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 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" => 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
223pub 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 .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 assert_eq!(
503 column("timestamp", "timestamp without time zone", "b").suggested_field(),
504 None
505 );
506 }
507}