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