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)]
pub(crate) struct ForeignKeyRow {
pub(crate) constraint_name: String,
pub(crate) from_table: String,
pub(crate) from_column: String,
pub(crate) to_schema: String,
pub(crate) to_table: String,
pub(crate) to_column: String,
pub(crate) delete_rule: String,
}
struct ConstraintGroup {
from_table: String,
to_schema: String,
to_table: String,
delete_rule: String,
column_pairs: Vec<(String, 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.constraint_name AS constraint_name, \
tc.table_name AS from_table, fkcu.column_name AS from_column, \
ucu.table_schema AS to_schema, 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()))?;
group_foreign_key_rows(rows, db_schema)
}
pub(crate) fn group_foreign_key_rows(
rows: Vec<ForeignKeyRow>,
db_schema: &str,
) -> Result<HashMap<String, Vec<Relation>>, SchemaReverseEngineerError> {
let mut groups: Vec<(String, ConstraintGroup)> = Vec::new();
for row in rows {
if let Some((last_name, group)) = groups.last_mut()
&& *last_name == row.constraint_name
{
group.column_pairs.push((row.from_column, row.to_column));
continue;
}
groups.push((
row.constraint_name,
ConstraintGroup {
from_table: row.from_table,
to_schema: row.to_schema,
to_table: row.to_table,
delete_rule: row.delete_rule,
column_pairs: vec![(row.from_column, row.to_column)],
},
));
}
let mut by_table: HashMap<String, Vec<Relation>> = HashMap::new();
for (constraint_name, group) in groups {
let to_table = if group.to_schema == db_schema {
group.to_table
} else {
eprintln!(
"warning: foreign key {}.{} references {}.{} in another schema -- also reverse-engineer that schema, or the generated XML won't parse",
group.from_table, constraint_name, group.to_schema, group.to_table
);
format!("{}.{}", group.to_schema, group.to_table)
};
let relation_type = map_delete_rule(&group.delete_rule).map_err(|e| {
SchemaReverseEngineerError::Introspection(format!(
"foreign key constraint '{constraint_name}': {e}"
))
})?;
let relation = if group.column_pairs.len() == 1 {
let (from_column, to_column) = group.column_pairs.into_iter().next().unwrap();
Relation::new(
to_table,
to_column,
group.from_table.clone(),
from_column,
relation_type,
false,
)
} else {
Relation::new_composite(
to_table,
group.from_table.clone(),
group.column_pairs,
relation_type,
false,
)
.map_err(|e| {
SchemaReverseEngineerError::Introspection(format!(
"foreign key constraint '{constraint_name}': {e}"
))
})?
};
by_table.entry(group.from_table).or_default().push(relation);
}
Ok(by_table)
}
fn map_delete_rule(delete_rule: &str) -> Result<RelationType, String> {
match delete_rule {
"CASCADE" => Ok(RelationType::Cascade),
"SET NULL" => Ok(RelationType::SetNull),
"RESTRICT" | "NO ACTION" => Ok(RelationType::Enforce),
other => Err(format!(
"unsupported ON DELETE rule '{other}' (supported: CASCADE, SET NULL, RESTRICT, NO ACTION)"
)),
}
}
#[cfg(test)]
mod tests {
use super::*;
fn row(
constraint_name: &str,
from_table: &str,
from_column: &str,
to_table: &str,
to_column: &str,
delete_rule: &str,
) -> ForeignKeyRow {
ForeignKeyRow {
constraint_name: constraint_name.to_string(),
from_table: from_table.to_string(),
from_column: from_column.to_string(),
to_schema: "public".to_string(),
to_table: to_table.to_string(),
to_column: to_column.to_string(),
delete_rule: delete_rule.to_string(),
}
}
#[test]
fn cross_schema_foreign_key_gets_a_qualified_target() {
let mut other = row("fk_x", "child", "x_id", "parent_x", "id", "CASCADE");
other.to_schema = "other".to_string();
let rows = vec![
other,
row("fk_a", "child", "a_id", "parent_a", "id", "CASCADE"),
];
let by_table = group_foreign_key_rows(rows, "public").unwrap();
let relations = &by_table["child"];
assert_eq!(relations.len(), 2);
assert_eq!(relations[0].to_table_name(), "other.parent_x");
assert_eq!(relations[1].to_table_name(), "parent_a");
}
#[test]
fn set_default_delete_rule_is_rejected() {
let rows = vec![row(
"fk_a",
"child",
"a_id",
"parent_a",
"id",
"SET DEFAULT",
)];
let err = group_foreign_key_rows(rows, "public")
.unwrap_err()
.to_string();
assert!(err.contains("SET DEFAULT"), "got: {err}");
}
#[test]
fn single_column_rows_produce_one_relation_per_row() {
let rows = vec![row("fk_a", "child", "parent_id", "parent", "id", "CASCADE")];
let by_table = group_foreign_key_rows(rows, "public").unwrap();
let relations = &by_table["child"];
assert_eq!(relations.len(), 1);
assert!(!relations[0].is_composite());
assert_eq!(relations[0].from_column_name(), "parent_id");
assert_eq!(relations[0].to_column_name(), "id");
assert_eq!(relations[0].relation_type(), RelationType::Cascade);
}
#[test]
fn multi_row_same_constraint_groups_into_one_composite_relation() {
let rows = vec![
row(
"fk_assignment",
"Assignment",
"ParentAssignmentID",
"Assignment",
"ID",
"CASCADE",
),
row(
"fk_assignment",
"Assignment",
"PropertyID",
"Assignment",
"PropertyID",
"CASCADE",
),
];
let by_table = group_foreign_key_rows(rows, "public").unwrap();
let relations = &by_table["Assignment"];
assert_eq!(relations.len(), 1);
assert!(relations[0].is_composite());
assert_eq!(
relations[0].column_pairs(),
&[
("ParentAssignmentID".to_string(), "ID".to_string()),
("PropertyID".to_string(), "PropertyID".to_string()),
]
);
}
#[test]
fn rows_from_different_constraints_stay_separate_even_when_adjacent() {
let rows = vec![
row("fk_a", "child", "a_id", "parent_a", "id", "CASCADE"),
row("fk_b", "child", "b_id", "parent_b", "id", "SET NULL"),
];
let by_table = group_foreign_key_rows(rows, "public").unwrap();
let relations = &by_table["child"];
assert_eq!(relations.len(), 2);
assert_eq!(relations[0].to_table_name(), "parent_a");
assert_eq!(relations[1].to_table_name(), "parent_b");
assert_eq!(relations[1].relation_type(), RelationType::SetNull);
}
}