use std::collections::HashMap;
use anyhow::anyhow;
use sqltrail_core::{
ColumnModel, ForeignKeyModel, IndexModel, PrimaryKeyModel, SchemaModel, TableModel,
UniqueConstraintModel,
};
use tokio_postgres::Client;
const HISTORY_TABLE: &str = "public._sqltrail_migrations";
pub async fn introspect(client: &Client) -> anyhow::Result<SchemaModel> {
let schemas = read_schemas(client).await?;
let tables = read_tables(client).await?;
let columns = read_columns(client).await?;
let pk_map = read_primary_keys(client).await?;
let unique_map = read_unique_constraints(client).await?;
let fk_map = read_foreign_keys(client).await?;
let index_map = read_indexes(client).await?;
let functions = read_functions(client).await?;
let mut models = Vec::new();
for (schema, name, comment) in &tables {
let mut cols = columns
.get(&(schema.clone(), name.clone()))
.cloned()
.unwrap_or_default();
if let Some(pk) = pk_map.get(&(schema.clone(), name.clone())) {
for c in cols.iter_mut() {
if pk.columns.contains(&c.name) {
c.primary_key = true;
}
}
}
models.push(TableModel {
schema: schema.clone(),
name: name.clone(),
rename_from: None,
comment: comment.clone(),
columns: cols,
primary_key: pk_map.get(&(schema.clone(), name.clone())).cloned(),
unique_constraints: unique_map
.get(&(schema.clone(), name.clone()))
.cloned()
.unwrap_or_default(),
foreign_keys: fk_map
.get(&(schema.clone(), name.clone()))
.cloned()
.unwrap_or_default(),
indexes: index_map
.get(&(schema.clone(), name.clone()))
.cloned()
.unwrap_or_default(),
});
}
Ok(SchemaModel {
manage_comments: true,
manage_functions: true,
schemas,
tables: models,
functions,
})
}
fn schema_cond(alias: &str) -> String {
format!(
"{alias} NOT IN ('pg_catalog','information_schema','pg_toast') \
AND {alias} NOT LIKE 'pg_temp_%' \
AND {alias} NOT LIKE 'pg_toast_temp_%'"
)
}
fn history_cond(alias_schema: &str, alias_table: &str) -> String {
let table_name = HISTORY_TABLE.rsplit('.').next().unwrap_or(HISTORY_TABLE);
format!("NOT ({alias_schema} = 'public' AND {alias_table} = '{table_name}')")
}
async fn read_schemas(client: &Client) -> anyhow::Result<Vec<String>> {
let sql = format!(
"SELECT schema_name FROM information_schema.schemata WHERE {} ORDER BY schema_name",
schema_cond("schema_name")
);
let rows = client
.query(&sql, &[])
.await
.map_err(|e| anyhow!("读取 schema 失败: {e}"))?;
Ok(rows.iter().map(|r| r.get(0)).collect())
}
async fn read_tables(client: &Client) -> anyhow::Result<Vec<(String, String, Option<String>)>> {
let sql = format!(
"SELECT t.table_schema, t.table_name, obj_description(c.oid, 'pg_class') \
FROM information_schema.tables t \
JOIN pg_namespace n ON n.nspname = t.table_schema \
JOIN pg_class c ON c.relnamespace = n.oid AND c.relname = t.table_name \
WHERE t.table_type = 'BASE TABLE' AND {} AND {} \
ORDER BY t.table_schema, t.table_name",
schema_cond("t.table_schema"),
history_cond("t.table_schema", "t.table_name")
);
let rows = client
.query(&sql, &[])
.await
.map_err(|e| anyhow!("读取表失败: {e}"))?;
Ok(rows
.iter()
.map(|r| {
let schema: String = r.get(0);
let name: String = r.get(1);
let comment: Option<String> = r.get(2);
(schema, name, comment)
})
.collect())
}
async fn read_columns(
client: &Client,
) -> anyhow::Result<HashMap<(String, String), Vec<ColumnModel>>> {
let sql = format!(
"SELECT c.table_schema, c.table_name, c.column_name, c.data_type, \
COALESCE(c.character_maximum_length, 0)::int4 AS char_len, \
COALESCE(c.numeric_precision, 0)::int4 AS num_precision, \
COALESCE(c.numeric_scale, 0)::int4 AS num_scale, \
c.is_nullable, c.column_default, c.udt_schema, c.udt_name, c.is_identity, \
col_description(pc.oid, c.ordinal_position::int) \
FROM information_schema.columns c \
JOIN pg_namespace pn ON pn.nspname = c.table_schema \
JOIN pg_class pc ON pc.relnamespace = pn.oid AND pc.relname = c.table_name \
WHERE {} AND {} \
ORDER BY c.table_schema, c.table_name, c.ordinal_position",
schema_cond("c.table_schema"),
history_cond("c.table_schema", "c.table_name")
);
let rows = client
.query(&sql, &[])
.await
.map_err(|e| anyhow!("读取字段失败: {e}"))?;
let mut map: HashMap<(String, String), Vec<ColumnModel>> = HashMap::new();
for row in &rows {
let schema: String = row.get(0);
let table: String = row.get(1);
let col_name: String = row.get(2);
let data_type: String = row.get(3);
let char_len: i32 = row.get(4);
let num_precision: i32 = row.get(5);
let num_scale: i32 = row.get(6);
let is_nullable: String = row.get(7);
let column_default: Option<String> = row.get(8);
let udt_schema: String = row.get(9);
let udt_name: String = row.get(10);
let is_identity: String = row.get(11);
let comment: Option<String> = row.get(12);
let mut sql_type = normalize_col_type(
&data_type,
char_len,
num_precision,
num_scale,
&udt_schema,
&udt_name,
);
let mut default_sql = column_default.clone();
if let Some(d) = &column_default {
if d.contains("nextval(") {
sql_type = match sql_type.as_str() {
"bigint" => "bigserial".to_string(),
"integer" => "serial".to_string(),
"smallint" => "smallserial".to_string(),
other => other.to_string(),
};
default_sql = None;
}
}
if is_identity == "YES" {
sql_type = match sql_type.as_str() {
"bigint" => "bigserial".to_string(),
"integer" => "serial".to_string(),
"smallint" => "smallserial".to_string(),
other => other.to_string(),
};
}
map.entry((schema, table)).or_default().push(ColumnModel {
name: col_name,
sql_type,
nullable: is_nullable == "YES",
primary_key: false,
rename_from: None,
default_sql,
comment,
});
}
Ok(map)
}
async fn read_functions(client: &Client) -> anyhow::Result<Vec<sqltrail_core::FunctionModel>> {
let sql = format!(
"SELECT n.nspname, p.proname, pg_get_function_identity_arguments(p.oid), \
pg_get_functiondef(p.oid), obj_description(p.oid, 'pg_proc') \
FROM pg_proc p \
JOIN pg_namespace n ON n.oid = p.pronamespace \
WHERE p.prokind = 'f' AND {} \
AND NOT EXISTS ( \
SELECT 1 FROM pg_depend d \
WHERE d.classid = 'pg_proc'::regclass \
AND d.objid = p.oid AND d.deptype = 'e' \
) \
ORDER BY n.nspname, p.proname, pg_get_function_identity_arguments(p.oid)",
schema_cond("n.nspname")
);
let rows = client
.query(&sql, &[])
.await
.map_err(|e| anyhow!("读取数据库函数失败: {e}"))?;
Ok(rows
.iter()
.map(|row| sqltrail_core::FunctionModel {
schema: row.get(0),
name: row.get(1),
identity_arguments: row.get(2),
definition_sql: row.get(3),
comment: row.get(4),
})
.collect())
}
async fn read_primary_keys(
client: &Client,
) -> anyhow::Result<HashMap<(String, String), PrimaryKeyModel>> {
let sql = format!(
"SELECT tc.table_schema, tc.table_name, tc.constraint_name, kcu.column_name \
FROM information_schema.table_constraints tc \
JOIN information_schema.key_column_usage kcu \
ON kcu.constraint_schema = tc.constraint_schema \
AND kcu.constraint_name = tc.constraint_name \
AND kcu.table_schema = tc.table_schema \
AND kcu.table_name = tc.table_name \
WHERE tc.constraint_type = 'PRIMARY KEY' AND {} \
ORDER BY tc.table_schema, tc.table_name, tc.constraint_name, kcu.ordinal_position",
schema_cond("tc.table_schema")
);
let rows = client
.query(&sql, &[])
.await
.map_err(|e| anyhow!("读取主键失败: {e}"))?;
let mut map: HashMap<(String, String), PrimaryKeyModel> = HashMap::new();
for row in &rows {
let schema: String = row.get(0);
let table: String = row.get(1);
let name: String = row.get(2);
let column: String = row.get(3);
let pk = map
.entry((schema, table))
.or_insert_with(|| PrimaryKeyModel {
name: Some(name),
columns: vec![],
});
pk.columns.push(column);
}
Ok(map)
}
async fn read_unique_constraints(
client: &Client,
) -> anyhow::Result<HashMap<(String, String), Vec<UniqueConstraintModel>>> {
let sql = format!(
"SELECT tc.table_schema, tc.table_name, tc.constraint_name, kcu.column_name \
FROM information_schema.table_constraints tc \
JOIN information_schema.key_column_usage kcu \
ON kcu.constraint_schema = tc.constraint_schema \
AND kcu.constraint_name = tc.constraint_name \
AND kcu.table_schema = tc.table_schema \
AND kcu.table_name = tc.table_name \
WHERE tc.constraint_type = 'UNIQUE' AND {} \
ORDER BY tc.table_schema, tc.table_name, tc.constraint_name, kcu.ordinal_position",
schema_cond("tc.table_schema")
);
let rows = client
.query(&sql, &[])
.await
.map_err(|e| anyhow!("读取唯一约束失败: {e}"))?;
let mut by_constraint: HashMap<(String, String, String), UniqueConstraintModel> =
HashMap::new();
for row in &rows {
let schema: String = row.get(0);
let table: String = row.get(1);
let name: String = row.get(2);
let column: String = row.get(3);
let u = by_constraint
.entry((schema.clone(), table.clone(), name.clone()))
.or_insert_with(|| UniqueConstraintModel {
name: Some(name),
columns: vec![],
});
u.columns.push(column);
}
let mut map: HashMap<(String, String), Vec<UniqueConstraintModel>> = HashMap::new();
for ((schema, table, _), u) in by_constraint {
map.entry((schema, table)).or_default().push(u);
}
for constraints in map.values_mut() {
constraints.sort_by(|a, b| (&a.name, &a.columns).cmp(&(&b.name, &b.columns)));
}
Ok(map)
}
async fn read_foreign_keys(
client: &Client,
) -> anyhow::Result<HashMap<(String, String), Vec<ForeignKeyModel>>> {
let sql = format!(
"SELECT n.nspname AS schema, c.relname AS table, con.conname AS name, \
(SELECT array_agg(a.attname ORDER BY k.ord) \
FROM unnest(con.conkey) WITH ORDINALITY AS k(attnum, ord) \
JOIN pg_attribute a ON a.attrelid = con.conrelid AND a.attnum = k.attnum) AS columns, \
rn.nspname AS ref_schema, rc.relname AS ref_table, \
(SELECT array_agg(a.attname ORDER BY k.ord) \
FROM unnest(con.confkey) WITH ORDINALITY AS k(attnum, ord) \
JOIN pg_attribute a ON a.attrelid = con.confrelid AND a.attnum = k.attnum) AS ref_columns, \
con.confdeltype::text AS del, con.confupdtype::text AS upd \
FROM pg_constraint con \
JOIN pg_class c ON c.oid = con.conrelid \
JOIN pg_namespace n ON n.oid = c.relnamespace \
JOIN pg_class rc ON rc.oid = con.confrelid \
JOIN pg_namespace rn ON rn.oid = rc.relnamespace \
WHERE con.contype = 'f' AND {} \
ORDER BY n.nspname, c.relname, con.conname",
schema_cond("n.nspname")
);
let rows = client
.query(&sql, &[])
.await
.map_err(|e| anyhow!("读取外键失败: {e}"))?;
let mut map: HashMap<(String, String), Vec<ForeignKeyModel>> = HashMap::new();
for row in &rows {
let schema: String = row.get(0);
let table: String = row.get(1);
let name: String = row.get(2);
let columns: Vec<String> = row.get(3);
let ref_schema: String = row.get(4);
let ref_table: String = row.get(5);
let ref_columns: Vec<String> = row.get(6);
let del: String = row.get(7);
let upd: String = row.get(8);
map.entry((schema, table))
.or_default()
.push(ForeignKeyModel {
name: Some(name),
columns,
ref_table: format!("{ref_schema}.{ref_table}"),
ref_columns,
on_delete: fk_rule(&del),
on_update: fk_rule(&upd),
});
}
Ok(map)
}
fn fk_rule(code: &str) -> Option<String> {
match code {
"c" => Some("CASCADE".to_string()),
"r" => Some("RESTRICT".to_string()),
"n" => Some("SET NULL".to_string()),
"d" => Some("SET DEFAULT".to_string()),
_ => None,
}
}
async fn read_indexes(
client: &Client,
) -> anyhow::Result<HashMap<(String, String), Vec<IndexModel>>> {
let sql = format!(
"SELECT n.nspname AS schema, t.relname AS table, i.relname AS index_name, ix.indisunique, \
(SELECT array_agg(a.attname ORDER BY k.ord) \
FROM unnest(ix.indkey) WITH ORDINALITY AS k(attnum, ord) \
JOIN pg_attribute a ON a.attrelid = ix.indrelid AND a.attnum = k.attnum) AS columns, \
pg_get_expr(ix.indpred, ix.indrelid) AS predicate \
FROM pg_index ix \
JOIN pg_class t ON t.oid = ix.indrelid \
JOIN pg_namespace n ON n.oid = t.relnamespace \
JOIN pg_class i ON i.oid = ix.indexrelid \
WHERE {} \
AND NOT EXISTS (SELECT 1 FROM pg_constraint con WHERE con.conindid = ix.indexrelid) \
AND 0 <> ALL(ix.indkey) \
ORDER BY n.nspname, t.relname, i.relname",
schema_cond("n.nspname")
);
let rows = client
.query(&sql, &[])
.await
.map_err(|e| anyhow!("读取索引失败: {e}"))?;
let mut map: HashMap<(String, String), Vec<IndexModel>> = HashMap::new();
for row in &rows {
let schema: String = row.get(0);
let table: String = row.get(1);
let index_name: String = row.get(2);
let is_unique: bool = row.get(3);
let columns: Vec<String> = row.get(4);
let predicate: Option<String> = row.get(5);
if columns.is_empty() {
continue;
}
map.entry((schema, table)).or_default().push(IndexModel {
name: Some(index_name),
columns,
unique: is_unique,
predicate,
});
}
Ok(map)
}
fn normalize_col_type(
data_type: &str,
char_len: i32,
num_precision: i32,
num_scale: i32,
udt_schema: &str,
udt_name: &str,
) -> String {
match data_type {
"character varying" => {
if char_len > 0 {
format!("varchar({char_len})")
} else {
"varchar".to_string()
}
}
"character" => {
if char_len > 0 {
format!("char({char_len})")
} else {
"char".to_string()
}
}
"timestamp without time zone" => "timestamp".to_string(),
"timestamp with time zone" => "timestamptz".to_string(),
"time without time zone" => "time".to_string(),
"time with time zone" => "timetz".to_string(),
"numeric" => {
if num_precision > 0 {
format!("numeric({num_precision},{num_scale})")
} else {
"numeric".to_string()
}
}
"bigint" => "bigint".to_string(),
"integer" => "integer".to_string(),
"smallint" => "smallint".to_string(),
"boolean" => "boolean".to_string(),
"text" => "text".to_string(),
"json" => "json".to_string(),
"jsonb" => "jsonb".to_string(),
"uuid" => "uuid".to_string(),
"inet" => "inet".to_string(),
"bytea" => "bytea".to_string(),
"real" => "real".to_string(),
"double precision" => "double precision".to_string(),
"date" => "date".to_string(),
"USER-DEFINED" => format!("{udt_schema}.{udt_name}"),
"ARRAY" => {
let element = udt_name.strip_prefix('_').unwrap_or(udt_name);
let element = normalize_udt_name(element);
if udt_schema == "pg_catalog" {
format!("{element}[]")
} else {
format!("{udt_schema}.{element}[]")
}
}
other => other.to_string(),
}
}
fn normalize_udt_name(name: &str) -> &str {
match name {
"int2" => "smallint",
"int4" => "integer",
"int8" => "bigint",
"float4" => "real",
"float8" => "double precision",
"bool" => "boolean",
"timestamptz" => "timestamptz",
"timestamp" => "timestamp",
other => other,
}
}
#[cfg(test)]
mod tests {
use super::*;
#[test]
fn 类型归一化() {
let normalize = |data_type, char_len, precision, scale| {
normalize_col_type(data_type, char_len, precision, scale, "pg_catalog", "")
};
assert_eq!(normalize("character varying", 64, 0, 0), "varchar(64)");
assert_eq!(normalize("character varying", 0, 0, 0), "varchar");
assert_eq!(
normalize("timestamp with time zone", 0, 0, 0),
"timestamptz"
);
assert_eq!(
normalize("timestamp without time zone", 0, 0, 0),
"timestamp"
);
assert_eq!(normalize("numeric", 0, 10, 2), "numeric(10,2)");
assert_eq!(normalize("jsonb", 0, 0, 0), "jsonb");
assert_eq!(
normalize_col_type("USER-DEFINED", 0, 0, 0, "public", "status"),
"public.status"
);
assert_eq!(
normalize_col_type("ARRAY", 0, 0, 0, "pg_catalog", "_int8"),
"bigint[]"
);
}
#[test]
fn 外键规则映射() {
assert_eq!(fk_rule("c").as_deref(), Some("CASCADE"));
assert_eq!(fk_rule("n").as_deref(), Some("SET NULL"));
assert_eq!(fk_rule("a"), None);
}
}