Skip to main content

schema_reverse_engineer/postgres/
relations.rs

1use crate::error::SchemaReverseEngineerError;
2use schema_model::model::relation::Relation;
3use schema_model::model::types::RelationType;
4use sqlx::PgPool;
5use std::collections::HashMap;
6
7#[derive(Debug, sqlx::FromRow)]
8struct ForeignKeyRow {
9    constraint_name: String,
10    from_table: String,
11    from_column: String,
12    to_table: String,
13    to_column: String,
14    delete_rule: String,
15}
16
17/// One foreign-key constraint's worth of grouped rows, in column-declaration order.
18struct ConstraintGroup {
19    from_table: String,
20    to_table: String,
21    delete_rule: String,
22    column_pairs: Vec<(String, String)>,
23}
24
25/// Reads foreign keys for every table in `db_schema`, grouped by the owning (FK/child) table
26/// name. Composite foreign keys are paired column-by-column using
27/// `key_column_usage.position_in_unique_constraint`, which correctly lines up each FK column
28/// with its referenced column regardless of declaration order (the Java original grouped by
29/// `KEY_SEQ` per side independently, which risked mis-pairing columns for composite keys).
30/// Rows belonging to the same FK constraint (composite or not) are then grouped by
31/// `constraint_name` into a single `Relation`, rather than emitting one `Relation` per row.
32///
33/// Uses `delete_rule` (not `update_rule`, which the Java original used) to determine cascade
34/// behavior, since `ON DELETE` is what `RelationType` models.
35pub async fn list_foreign_keys(
36    pool: &PgPool,
37    db_schema: &str,
38) -> Result<HashMap<String, Vec<Relation>>, SchemaReverseEngineerError> {
39    let rows: Vec<ForeignKeyRow> = sqlx::query_as(
40        "SELECT tc.constraint_name AS constraint_name, \
41                tc.table_name AS from_table, fkcu.column_name AS from_column, \
42                ucu.table_name AS to_table, ucu.column_name AS to_column, \
43                rc.delete_rule AS delete_rule \
44         FROM information_schema.table_constraints tc \
45         JOIN information_schema.referential_constraints rc \
46           ON rc.constraint_name = tc.constraint_name AND rc.constraint_schema = tc.table_schema \
47         JOIN information_schema.key_column_usage fkcu \
48           ON fkcu.constraint_name = tc.constraint_name AND fkcu.table_schema = tc.table_schema \
49         JOIN information_schema.key_column_usage ucu \
50           ON ucu.constraint_name = rc.unique_constraint_name \
51          AND ucu.constraint_schema = rc.unique_constraint_schema \
52          AND ucu.ordinal_position = fkcu.position_in_unique_constraint \
53         WHERE tc.table_schema = $1 AND tc.constraint_type = 'FOREIGN KEY' \
54         ORDER BY tc.constraint_name, fkcu.ordinal_position",
55    )
56    .bind(db_schema)
57    .fetch_all(pool)
58    .await
59    .map_err(|e| SchemaReverseEngineerError::Introspection(e.to_string()))?;
60
61    group_foreign_key_rows(rows)
62}
63
64/// Groups pre-sorted `(constraint_name, ordinal_position)` rows into one `Relation` per
65/// FK constraint (single-column or composite), keyed by the owning (FK/child) table name.
66/// Consecutive rows sharing a `constraint_name` are folded into one group; this relies on the
67/// caller having ordered rows by `constraint_name, ordinal_position` (the SQL query does this).
68fn group_foreign_key_rows(
69    rows: Vec<ForeignKeyRow>,
70) -> Result<HashMap<String, Vec<Relation>>, SchemaReverseEngineerError> {
71    let mut groups: Vec<(String, ConstraintGroup)> = Vec::new();
72    for row in rows {
73        if let Some((last_name, group)) = groups.last_mut()
74            && *last_name == row.constraint_name
75        {
76            group.column_pairs.push((row.from_column, row.to_column));
77            continue;
78        }
79        groups.push((
80            row.constraint_name,
81            ConstraintGroup {
82                from_table: row.from_table,
83                to_table: row.to_table,
84                delete_rule: row.delete_rule,
85                column_pairs: vec![(row.from_column, row.to_column)],
86            },
87        ));
88    }
89
90    let mut by_table: HashMap<String, Vec<Relation>> = HashMap::new();
91    for (constraint_name, group) in groups {
92        let relation_type = map_delete_rule(&group.delete_rule).map_err(|e| {
93            SchemaReverseEngineerError::Introspection(format!(
94                "foreign key constraint '{constraint_name}': {e}"
95            ))
96        })?;
97        let relation = if group.column_pairs.len() == 1 {
98            let (from_column, to_column) = group.column_pairs.into_iter().next().unwrap();
99            Relation::new(
100                group.to_table,
101                to_column,
102                group.from_table.clone(),
103                from_column,
104                relation_type,
105                false,
106            )
107        } else {
108            Relation::new_composite(
109                group.to_table,
110                group.from_table.clone(),
111                group.column_pairs,
112                relation_type,
113                false,
114            )
115            .map_err(|e| {
116                SchemaReverseEngineerError::Introspection(format!(
117                    "foreign key constraint '{constraint_name}': {e}"
118                ))
119            })?
120        };
121        by_table.entry(group.from_table).or_default().push(relation);
122    }
123
124    Ok(by_table)
125}
126
127/// `SET DEFAULT` has no `RelationType` equivalent; mapping it to `SetNull` would null the
128/// column instead of restoring its default, so it (and any unknown rule) is rejected.
129fn map_delete_rule(delete_rule: &str) -> Result<RelationType, String> {
130    match delete_rule {
131        "CASCADE" => Ok(RelationType::Cascade),
132        "SET NULL" => Ok(RelationType::SetNull),
133        "RESTRICT" | "NO ACTION" => Ok(RelationType::Enforce),
134        other => Err(format!(
135            "unsupported ON DELETE rule '{other}' (supported: CASCADE, SET NULL, RESTRICT, NO ACTION)"
136        )),
137    }
138}
139
140#[cfg(test)]
141mod tests {
142    use super::*;
143
144    fn row(
145        constraint_name: &str,
146        from_table: &str,
147        from_column: &str,
148        to_table: &str,
149        to_column: &str,
150        delete_rule: &str,
151    ) -> ForeignKeyRow {
152        ForeignKeyRow {
153            constraint_name: constraint_name.to_string(),
154            from_table: from_table.to_string(),
155            from_column: from_column.to_string(),
156            to_table: to_table.to_string(),
157            to_column: to_column.to_string(),
158            delete_rule: delete_rule.to_string(),
159        }
160    }
161
162    #[test]
163    fn set_default_delete_rule_is_rejected() {
164        let rows = vec![row(
165            "fk_a",
166            "child",
167            "a_id",
168            "parent_a",
169            "id",
170            "SET DEFAULT",
171        )];
172        let err = group_foreign_key_rows(rows).unwrap_err().to_string();
173        assert!(err.contains("SET DEFAULT"), "got: {err}");
174    }
175
176    #[test]
177    fn single_column_rows_produce_one_relation_per_row() {
178        let rows = vec![row("fk_a", "child", "parent_id", "parent", "id", "CASCADE")];
179        let by_table = group_foreign_key_rows(rows).unwrap();
180        let relations = &by_table["child"];
181        assert_eq!(relations.len(), 1);
182        assert!(!relations[0].is_composite());
183        assert_eq!(relations[0].from_column_name(), "parent_id");
184        assert_eq!(relations[0].to_column_name(), "id");
185        assert_eq!(relations[0].relation_type(), RelationType::Cascade);
186    }
187
188    #[test]
189    fn multi_row_same_constraint_groups_into_one_composite_relation() {
190        let rows = vec![
191            row(
192                "fk_assignment",
193                "Assignment",
194                "ParentAssignmentID",
195                "Assignment",
196                "ID",
197                "CASCADE",
198            ),
199            row(
200                "fk_assignment",
201                "Assignment",
202                "PropertyID",
203                "Assignment",
204                "PropertyID",
205                "CASCADE",
206            ),
207        ];
208        let by_table = group_foreign_key_rows(rows).unwrap();
209        let relations = &by_table["Assignment"];
210        assert_eq!(relations.len(), 1);
211        assert!(relations[0].is_composite());
212        assert_eq!(
213            relations[0].column_pairs(),
214            &[
215                ("ParentAssignmentID".to_string(), "ID".to_string()),
216                ("PropertyID".to_string(), "PropertyID".to_string()),
217            ]
218        );
219    }
220
221    #[test]
222    fn rows_from_different_constraints_stay_separate_even_when_adjacent() {
223        let rows = vec![
224            row("fk_a", "child", "a_id", "parent_a", "id", "CASCADE"),
225            row("fk_b", "child", "b_id", "parent_b", "id", "SET NULL"),
226        ];
227        let by_table = group_foreign_key_rows(rows).unwrap();
228        let relations = &by_table["child"];
229        assert_eq!(relations.len(), 2);
230        assert_eq!(relations[0].to_table_name(), "parent_a");
231        assert_eq!(relations[1].to_table_name(), "parent_b");
232        assert_eq!(relations[1].relation_type(), RelationType::SetNull);
233    }
234}