Skip to main content

schema_reverse_engineer/postgres/
keys.rs

1use crate::error::SchemaReverseEngineerError;
2use sqlx::PgPool;
3use std::collections::{HashMap, HashSet};
4
5#[derive(Debug, Clone)]
6pub struct KeyInfo {
7    pub columns: Vec<String>,
8}
9
10#[derive(Debug, Clone, Default)]
11pub struct TableKeys {
12    pub primary: Option<KeyInfo>,
13    pub unique: Vec<KeyInfo>,
14    pub index: Vec<KeyInfo>,
15}
16
17#[derive(Debug, sqlx::FromRow)]
18struct PrimaryKeyRow {
19    table_name: String,
20    column_name: String,
21}
22
23#[derive(Debug, sqlx::FromRow)]
24struct IndexRow {
25    table_name: String,
26    index_name: String,
27    is_unique: bool,
28    is_primary: bool,
29    column_name: String,
30}
31
32/// Reads primary keys plus unique/non-unique indexes for every table in `db_schema` and
33/// merges them into one `TableKeys` per table.
34///
35/// `exclusion_constraint_names` skips unique indexes that merely back a Postgres exclusion
36/// constraint (already captured separately as a `Constraint`), matching the equivalent
37/// `showIncludeKey` check in the original Java reverse-engineer tool.
38///
39/// If a table has no primary key but exactly one unique key, that unique key is promoted to
40/// the primary key -- this mirrors a (surprising but intentional) heuristic in the Java
41/// original: some tables only declare a single unique index rather than an explicit PK.
42pub async fn list_keys(
43    pool: &PgPool,
44    db_schema: &str,
45    exclusion_constraint_names: &HashSet<(String, String)>,
46) -> Result<HashMap<String, TableKeys>, SchemaReverseEngineerError> {
47    let mut result: HashMap<String, TableKeys> = HashMap::new();
48
49    let pk_rows: Vec<PrimaryKeyRow> = sqlx::query_as(
50        "SELECT tc.table_name, kcu.column_name \
51         FROM information_schema.table_constraints tc \
52         JOIN information_schema.key_column_usage kcu \
53           ON kcu.constraint_name = tc.constraint_name AND kcu.table_schema = tc.table_schema \
54         WHERE tc.table_schema = $1 AND tc.constraint_type = 'PRIMARY KEY' \
55         ORDER BY tc.table_name, kcu.ordinal_position",
56    )
57    .bind(db_schema)
58    .fetch_all(pool)
59    .await
60    .map_err(|e| SchemaReverseEngineerError::Introspection(e.to_string()))?;
61
62    for row in pk_rows {
63        result
64            .entry(row.table_name)
65            .or_default()
66            .primary
67            .get_or_insert_with(|| KeyInfo { columns: Vec::new() })
68            .columns
69            .push(row.column_name);
70    }
71
72    let index_rows: Vec<IndexRow> = sqlx::query_as(
73        "SELECT t.relname AS table_name, i.relname AS index_name, ix.indisunique AS is_unique, \
74                ix.indisprimary AS is_primary, a.attname AS column_name \
75         FROM pg_index ix \
76         JOIN pg_class t ON t.oid = ix.indrelid \
77         JOIN pg_class i ON i.oid = ix.indexrelid \
78         JOIN pg_namespace n ON n.oid = t.relnamespace \
79         JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(ix.indkey) \
80         WHERE n.nspname = $1 AND t.relkind = 'r' \
81         ORDER BY t.relname, i.relname, array_position(ix.indkey, a.attnum)",
82    )
83    .bind(db_schema)
84    .fetch_all(pool)
85    .await
86    .map_err(|e| SchemaReverseEngineerError::Introspection(e.to_string()))?;
87
88    let mut by_index: HashMap<(String, String), (bool, bool, Vec<String>)> = HashMap::new();
89    for row in index_rows {
90        let entry = by_index
91            .entry((row.table_name, row.index_name))
92            .or_insert((row.is_unique, row.is_primary, Vec::new()));
93        entry.2.push(row.column_name);
94    }
95
96    for ((table_name, index_name), (is_unique, is_primary, columns)) in by_index {
97        // The index backing the primary key is already represented via the PK query above.
98        if is_primary {
99            continue;
100        }
101        // Skip indexes that merely back an exclusion constraint (captured as a Constraint).
102        if exclusion_constraint_names.contains(&(table_name.clone(), index_name)) {
103            continue;
104        }
105
106        let table_keys = result.entry(table_name).or_default();
107        if is_unique {
108            table_keys.unique.push(KeyInfo { columns });
109        } else {
110            table_keys.index.push(KeyInfo { columns });
111        }
112    }
113
114    for table_keys in result.values_mut() {
115        if table_keys.primary.is_none() && table_keys.unique.len() == 1 {
116            let promoted = table_keys.unique.remove(0);
117            table_keys.primary = Some(promoted);
118        }
119    }
120
121    Ok(result)
122}