Skip to main content

schema_reverse_engineer/postgres/
keys.rs

1use 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    /// `None` for an expression slot in the index (`attnum = 0`).
20    column_name: Option<String>,
21    is_include: bool,
22    predicate: Option<String>,
23}
24
25/// Reads primary keys plus unique/non-unique indexes for every table in `db_schema` and
26/// merges them into one `TableKeys` per table.
27///
28/// `exclusion_constraint_names` skips unique indexes that merely back a Postgres exclusion
29/// constraint (already captured separately as a `Constraint`), matching the equivalent
30/// `showIncludeKey` check in the original Java reverse-engineer tool.
31///
32/// Partial indexes keep their predicate (`filter`) and covering indexes their `INCLUDE`
33/// columns. Expression indexes can't be modeled, so they are skipped with a warning.
34/// Promoting a lone unique key to primary is left to the caller, which knows column nullability.
35pub 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
89/// Folds pre-sorted index rows into `result`.
90///
91/// Grouped by hand instead of via a `HashMap` keyed on `(table_name, index_name)`: the query
92/// is already `ORDER BY t.relname, i.relname, ...`, so consecutive rows belong to the same
93/// index -- iterating a `HashMap` here would silently discard that order, making two runs
94/// against an unchanged database produce differently-ordered XML.
95fn 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        // The index backing the primary key is already represented via the PK query above.
136        if group.is_primary {
137            continue;
138        }
139        // Skip indexes that merely back an exclusion constraint (captured as a Constraint).
140        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        // `<unique>` can't carry `include`/`where`, so such an index is written as
155        // `<index unique="true" ...>` instead.
156        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}