use crate::error::SchemaReverseEngineerError;
use schema_model::model::relation::Relation;
use schema_model::model::types::RelationType;
use sqlx::PgPool;
use std::collections::HashMap;
#[derive(Debug, sqlx::FromRow)]
struct ForeignKeyRow {
from_table: String,
from_column: String,
to_table: String,
to_column: String,
delete_rule: String,
}
pub async fn list_foreign_keys(
pool: &PgPool,
db_schema: &str,
) -> Result<HashMap<String, Vec<Relation>>, SchemaReverseEngineerError> {
let rows: Vec<ForeignKeyRow> = sqlx::query_as(
"SELECT tc.table_name AS from_table, fkcu.column_name AS from_column, \
ucu.table_name AS to_table, ucu.column_name AS to_column, \
rc.delete_rule AS delete_rule \
FROM information_schema.table_constraints tc \
JOIN information_schema.referential_constraints rc \
ON rc.constraint_name = tc.constraint_name AND rc.constraint_schema = tc.table_schema \
JOIN information_schema.key_column_usage fkcu \
ON fkcu.constraint_name = tc.constraint_name AND fkcu.table_schema = tc.table_schema \
JOIN information_schema.key_column_usage ucu \
ON ucu.constraint_name = rc.unique_constraint_name \
AND ucu.constraint_schema = rc.unique_constraint_schema \
AND ucu.ordinal_position = fkcu.position_in_unique_constraint \
WHERE tc.table_schema = $1 AND tc.constraint_type = 'FOREIGN KEY' \
ORDER BY tc.constraint_name, fkcu.ordinal_position",
)
.bind(db_schema)
.fetch_all(pool)
.await
.map_err(|e| SchemaReverseEngineerError::Introspection(e.to_string()))?;
let mut by_table: HashMap<String, Vec<Relation>> = HashMap::new();
for row in rows {
let relation_type = map_delete_rule(&row.delete_rule);
let relation = Relation::new(
row.to_table,
row.to_column,
row.from_table.clone(),
row.from_column,
relation_type,
false,
);
by_table.entry(row.from_table).or_default().push(relation);
}
Ok(by_table)
}
fn map_delete_rule(delete_rule: &str) -> RelationType {
match delete_rule {
"CASCADE" => RelationType::Cascade,
"SET NULL" | "SET DEFAULT" => RelationType::SetNull,
"RESTRICT" | "NO ACTION" => RelationType::Enforce,
_ => RelationType::DoNothing,
}
}