schema_reverse_engineer/postgres/
keys.rs1use crate::error::SchemaReverseEngineerError;
2use sqlx::PgPool;
3use std::collections::{HashMap, HashSet};
4
5pub use crate::info::{KeyInfo, TableKeys};
6
7#[derive(Debug, sqlx::FromRow)]
8struct PrimaryKeyRow {
9 table_name: String,
10 column_name: String,
11}
12
13#[derive(Debug, sqlx::FromRow)]
14struct IndexRow {
15 table_name: String,
16 index_name: String,
17 is_unique: bool,
18 is_primary: bool,
19 column_name: Option<String>,
21 is_include: bool,
22 predicate: Option<String>,
23}
24
25pub async fn list_keys(
36 pool: &PgPool,
37 db_schema: &str,
38 exclusion_constraint_names: &HashSet<(String, String)>,
39) -> Result<HashMap<String, TableKeys>, SchemaReverseEngineerError> {
40 let mut result: HashMap<String, TableKeys> = HashMap::new();
41
42 let pk_rows: Vec<PrimaryKeyRow> = sqlx::query_as(
43 "SELECT tc.table_name, kcu.column_name \
44 FROM information_schema.table_constraints tc \
45 JOIN information_schema.key_column_usage kcu \
46 ON kcu.constraint_name = tc.constraint_name AND kcu.table_schema = tc.table_schema \
47 WHERE tc.table_schema = $1 AND tc.constraint_type = 'PRIMARY KEY' \
48 ORDER BY tc.table_name, kcu.ordinal_position",
49 )
50 .bind(db_schema)
51 .fetch_all(pool)
52 .await
53 .map_err(|e| SchemaReverseEngineerError::Introspection(e.to_string()))?;
54
55 for row in pk_rows {
56 result
57 .entry(row.table_name)
58 .or_default()
59 .primary
60 .get_or_insert_with(KeyInfo::default)
61 .columns
62 .push(row.column_name);
63 }
64
65 let index_rows: Vec<IndexRow> = sqlx::query_as(
66 "SELECT t.relname AS table_name, i.relname AS index_name, ix.indisunique AS is_unique, \
67 ix.indisprimary AS is_primary, a.attname AS column_name, \
68 (k.ord > ix.indnkeyatts) AS is_include, \
69 pg_get_expr(ix.indpred, ix.indrelid) AS predicate \
70 FROM pg_index ix \
71 JOIN pg_class t ON t.oid = ix.indrelid \
72 JOIN pg_class i ON i.oid = ix.indexrelid \
73 JOIN pg_namespace n ON n.oid = t.relnamespace \
74 CROSS JOIN LATERAL unnest(ix.indkey::int2[]) WITH ORDINALITY AS k(attnum, ord) \
75 LEFT JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = k.attnum \
76 WHERE n.nspname = $1 AND t.relkind IN ('r', 'p') \
77 ORDER BY t.relname, i.relname, k.ord",
78 )
79 .bind(db_schema)
80 .fetch_all(pool)
81 .await
82 .map_err(|e| SchemaReverseEngineerError::Introspection(e.to_string()))?;
83
84 apply_index_rows(&mut result, index_rows, exclusion_constraint_names);
85
86 Ok(result)
87}
88
89fn apply_index_rows(
96 result: &mut HashMap<String, TableKeys>,
97 index_rows: Vec<IndexRow>,
98 exclusion_constraint_names: &HashSet<(String, String)>,
99) {
100 struct Group {
101 table_name: String,
102 index_name: String,
103 is_primary: bool,
104 has_expression: bool,
105 info: KeyInfo,
106 }
107 let mut grouped: Vec<Group> = Vec::new();
108 for row in index_rows {
109 let is_new = !matches!(
110 grouped.last(),
111 Some(g) if g.table_name == row.table_name && g.index_name == row.index_name
112 );
113 if is_new {
114 grouped.push(Group {
115 table_name: row.table_name.clone(),
116 index_name: row.index_name.clone(),
117 is_primary: row.is_primary,
118 has_expression: false,
119 info: KeyInfo {
120 unique: row.is_unique,
121 filter: row.predicate.clone(),
122 ..KeyInfo::default()
123 },
124 });
125 }
126 let group = grouped.last_mut().unwrap();
127 match row.column_name {
128 None => group.has_expression = true,
129 Some(name) if row.is_include => group.info.include.push(name),
130 Some(name) => group.info.columns.push(name),
131 }
132 }
133
134 for group in grouped {
135 if group.is_primary {
137 continue;
138 }
139 if exclusion_constraint_names
141 .contains(&(group.table_name.clone(), group.index_name.clone()))
142 {
143 continue;
144 }
145 if group.has_expression {
146 eprintln!(
147 "warning: index {}.{} is an expression index, which the schema model can't represent -- skipping it",
148 group.table_name, group.index_name
149 );
150 continue;
151 }
152
153 let table_keys = result.entry(group.table_name).or_default();
154 if group.info.unique && group.info.include.is_empty() && group.info.filter.is_none() {
157 table_keys.unique.push(group.info);
158 } else {
159 table_keys.index.push(group.info);
160 }
161 }
162}
163
164#[cfg(test)]
165mod tests {
166 use super::*;
167
168 #[allow(clippy::too_many_arguments)]
169 fn row(
170 index: &str,
171 unique: bool,
172 column: Option<&str>,
173 include: bool,
174 predicate: Option<&str>,
175 ) -> IndexRow {
176 IndexRow {
177 table_name: "t".into(),
178 index_name: index.into(),
179 is_unique: unique,
180 is_primary: false,
181 column_name: column.map(String::from),
182 is_include: include,
183 predicate: predicate.map(String::from),
184 }
185 }
186
187 fn apply(rows: Vec<IndexRow>) -> TableKeys {
188 let mut result = HashMap::new();
189 apply_index_rows(&mut result, rows, &HashSet::new());
190 result.remove("t").unwrap_or_default()
191 }
192
193 #[test]
194 fn plain_unique_index_is_a_unique_key() {
195 let keys = apply(vec![row("u", true, Some("a"), false, None)]);
196 assert_eq!(keys.unique.len(), 1);
197 assert!(keys.index.is_empty());
198 }
199
200 #[test]
201 fn partial_unique_index_keeps_filter_and_is_written_as_index() {
202 let keys = apply(vec![row(
203 "u",
204 true,
205 Some("a"),
206 false,
207 Some("(b IS NOT NULL)"),
208 )]);
209 assert!(keys.unique.is_empty());
210 assert_eq!(keys.index.len(), 1);
211 assert!(keys.index[0].unique);
212 assert_eq!(keys.index[0].columns, vec!["a"]);
213 assert_eq!(keys.index[0].filter.as_deref(), Some("(b IS NOT NULL)"));
214 }
215
216 #[test]
217 fn include_columns_are_separated_from_key_columns() {
218 let keys = apply(vec![
219 row("i", false, Some("a"), false, None),
220 row("i", false, Some("b"), true, None),
221 ]);
222 assert_eq!(keys.index[0].columns, vec!["a"]);
223 assert_eq!(keys.index[0].include, vec!["b"]);
224 }
225
226 #[test]
227 fn expression_index_is_skipped() {
228 let keys = apply(vec![
229 row("e", false, None, false, None),
230 row("ok", false, Some("a"), false, None),
231 ]);
232 assert_eq!(keys.index.len(), 1);
233 assert_eq!(keys.index[0].columns, vec!["a"]);
234 }
235}