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)]
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
18struct 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
27pub 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
66pub(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 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
144fn 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}