schema_reverse_engineer/postgres/
keys.rs1use 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
32pub 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 {
68 columns: Vec::new(),
69 })
70 .columns
71 .push(row.column_name);
72 }
73
74 let index_rows: Vec<IndexRow> = sqlx::query_as(
75 "SELECT t.relname AS table_name, i.relname AS index_name, ix.indisunique AS is_unique, \
76 ix.indisprimary AS is_primary, a.attname AS column_name \
77 FROM pg_index ix \
78 JOIN pg_class t ON t.oid = ix.indrelid \
79 JOIN pg_class i ON i.oid = ix.indexrelid \
80 JOIN pg_namespace n ON n.oid = t.relnamespace \
81 JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(ix.indkey) \
82 WHERE n.nspname = $1 AND t.relkind = 'r' \
83 ORDER BY t.relname, i.relname, array_position(ix.indkey, a.attnum)",
84 )
85 .bind(db_schema)
86 .fetch_all(pool)
87 .await
88 .map_err(|e| SchemaReverseEngineerError::Introspection(e.to_string()))?;
89
90 let mut grouped: Vec<(String, String, bool, bool, Vec<String>)> = Vec::new();
95 for row in index_rows {
96 if let Some(last) = grouped.last_mut()
97 && last.0 == row.table_name
98 && last.1 == row.index_name
99 {
100 last.4.push(row.column_name);
101 continue;
102 }
103 grouped.push((
104 row.table_name,
105 row.index_name,
106 row.is_unique,
107 row.is_primary,
108 vec![row.column_name],
109 ));
110 }
111
112 for (table_name, index_name, is_unique, is_primary, columns) in grouped {
113 if is_primary {
115 continue;
116 }
117 if exclusion_constraint_names.contains(&(table_name.clone(), index_name)) {
119 continue;
120 }
121
122 let table_keys = result.entry(table_name).or_default();
123 if is_unique {
124 table_keys.unique.push(KeyInfo { columns });
125 } else {
126 table_keys.index.push(KeyInfo { columns });
127 }
128 }
129
130 for table_keys in result.values_mut() {
131 if table_keys.primary.is_none() && table_keys.unique.len() == 1 {
132 let promoted = table_keys.unique.remove(0);
133 table_keys.primary = Some(promoted);
134 }
135 }
136
137 Ok(result)
138}