schema_reverse_engineer/postgres/
relations.rs1use 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
17struct ConstraintGroup {
19 from_table: String,
20 to_table: String,
21 delete_rule: String,
22 column_pairs: Vec<(String, String)>,
23}
24
25pub 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
64fn 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
127fn 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}