schema_reverse_engineer/postgres/
columns.rs1use crate::error::SchemaReverseEngineerError;
2use schema_model::model::column_type::ColumnType;
3use sqlx::PgPool;
4use std::collections::HashSet;
5
6pub use crate::info::ColumnInfo;
7
8#[derive(Debug, sqlx::FromRow)]
9struct ColumnRow {
10 table_name: String,
11 column_name: String,
12 data_type: String,
13 udt_name: String,
14 character_maximum_length: Option<i32>,
15 numeric_precision: Option<i32>,
16 numeric_scale: Option<i32>,
17 is_nullable: String,
18 column_default: Option<String>,
19 is_identity: String,
20 is_generated: String,
21 generation_expression: Option<String>,
22}
23
24pub async fn list_columns(
25 pool: &PgPool,
26 db_schema: &str,
27 enum_type_names: &HashSet<String>,
28) -> Result<Vec<ColumnInfo>, SchemaReverseEngineerError> {
29 let rows: Vec<ColumnRow> = sqlx::query_as(
30 "SELECT c.table_name, c.column_name, c.data_type, c.udt_name, \
31 c.character_maximum_length::int4 AS character_maximum_length, \
32 c.numeric_precision::int4 AS numeric_precision, \
33 c.numeric_scale::int4 AS numeric_scale, \
34 c.is_nullable, c.column_default, c.is_identity, c.is_generated, c.generation_expression \
35 FROM information_schema.columns c \
36 JOIN information_schema.tables t \
37 ON t.table_schema = c.table_schema AND t.table_name = c.table_name AND t.table_type = 'BASE TABLE' \
38 WHERE c.table_schema = $1 \
39 ORDER BY c.table_name, c.ordinal_position",
40 )
41 .bind(db_schema)
42 .fetch_all(pool)
43 .await
44 .map_err(|e| SchemaReverseEngineerError::Introspection(e.to_string()))?;
45
46 Ok(rows
47 .into_iter()
48 .map(|row| build_column_info(row, enum_type_names))
49 .collect())
50}
51
52fn build_column_info(row: ColumnRow, enum_type_names: &HashSet<String>) -> ColumnInfo {
53 let is_generated = row.is_generated == "ALWAYS";
54 let is_autoincrement = row.is_identity == "YES"
55 || row
56 .column_default
57 .as_deref()
58 .map(|d| d.starts_with("nextval("))
59 .unwrap_or(false);
60
61 let (column_type, element_type) = match map_column_type(
65 &row.data_type,
66 &row.udt_name,
67 is_autoincrement,
68 enum_type_names,
69 ) {
70 Ok(mapped) => mapped,
71 Err(SchemaReverseEngineerError::UnsupportedColumnType(type_name)) => {
72 eprintln!(
73 "warning: {}.{} has unsupported type '{}' -- falling back to text",
74 row.table_name, row.column_name, type_name
75 );
76 (ColumnType::Text, None)
77 }
78 Err(other) => {
79 unreachable!("map_column_type only ever returns UnsupportedColumnType: {other}")
80 }
81 };
82
83 let enum_type = if column_type == ColumnType::Enum {
84 Some(row.udt_name.clone())
85 } else {
86 None
87 };
88
89 let length = match column_type {
90 ColumnType::Varchar | ColumnType::Char | ColumnType::CiText => {
91 row.character_maximum_length.unwrap_or(0)
92 }
93 ColumnType::Decimal => row.numeric_precision.unwrap_or(0),
94 _ => 0,
95 };
96 let scale = if column_type == ColumnType::Decimal {
97 row.numeric_scale.unwrap_or(0)
98 } else {
99 0
100 };
101
102 let generated = if is_generated {
103 Some(row.generation_expression.unwrap_or_default())
105 } else {
106 None
107 };
108
109 let default_constraint =
113 if is_generated || matches!(column_type, ColumnType::Sequence | ColumnType::LongSequence) {
114 None
115 } else {
116 row.column_default
117 };
118
119 ColumnInfo {
120 table_name: row.table_name,
121 column_name: row.column_name,
122 column_type,
123 length,
124 scale,
125 required: row.is_nullable == "NO",
126 default_constraint,
127 generated,
128 enum_type,
129 element_type,
130 }
131}
132
133pub fn map_column_type(
142 data_type: &str,
143 udt_name: &str,
144 is_autoincrement: bool,
145 enum_type_names: &HashSet<String>,
146) -> Result<(ColumnType, Option<String>), SchemaReverseEngineerError> {
147 if data_type == "ARRAY" {
148 let element_udt = udt_name.strip_prefix('_').unwrap_or(udt_name);
149 let (element_column_type, _) =
150 base_type_to_column_type(element_udt, false, enum_type_names)?;
151 return Ok((
152 ColumnType::Array,
153 Some(element_column_type.name().to_lowercase()),
154 ));
155 }
156
157 base_type_to_column_type(udt_name, is_autoincrement, enum_type_names)
158}
159
160fn base_type_to_column_type(
161 udt_name: &str,
162 is_autoincrement: bool,
163 enum_type_names: &HashSet<String>,
164) -> Result<(ColumnType, Option<String>), SchemaReverseEngineerError> {
165 let column_type = match udt_name {
166 "int4" => {
167 if is_autoincrement {
168 ColumnType::Sequence
169 } else {
170 ColumnType::Int
171 }
172 }
173 "int8" => {
174 if is_autoincrement {
175 ColumnType::LongSequence
176 } else {
177 ColumnType::Long
178 }
179 }
180 "int2" => ColumnType::Short,
181 "float4" => ColumnType::Float,
182 "float8" => ColumnType::Double,
183 "numeric" => ColumnType::Decimal,
184 "bool" => ColumnType::Boolean,
185 "varchar" => ColumnType::Varchar,
186 "bpchar" => ColumnType::Char,
187 "text" => ColumnType::Text,
188 "citext" => ColumnType::CiText,
189 "bytea" => ColumnType::Binary,
190 "date" => ColumnType::Date,
191 "time" | "timetz" => ColumnType::Time,
195 "timestamp" => ColumnType::DateTime,
196 "timestamptz" => ColumnType::TimestampTz,
197 "uuid" => ColumnType::Uuid,
198 "json" | "jsonb" => ColumnType::Json,
199 other if enum_type_names.contains(other) => ColumnType::Enum,
200 other => {
201 return Err(SchemaReverseEngineerError::UnsupportedColumnType(
202 other.to_string(),
203 ));
204 }
205 };
206 Ok((column_type, None))
207}
208
209#[cfg(test)]
210mod tests {
211 use super::*;
212
213 fn enums(names: &[&str]) -> HashSet<String> {
214 names.iter().map(|s| s.to_string()).collect()
215 }
216
217 #[test]
218 fn maps_uuid_and_jsonb() {
219 let none = enums(&[]);
220 assert_eq!(
221 map_column_type("uuid", "uuid", false, &none).unwrap().0,
222 ColumnType::Uuid
223 );
224 assert_eq!(
225 map_column_type("jsonb", "jsonb", false, &none).unwrap().0,
226 ColumnType::Json
227 );
228 assert_eq!(
229 map_column_type("USER-DEFINED", "json", false, &none)
230 .unwrap()
231 .0,
232 ColumnType::Json
233 );
234 }
235
236 #[test]
237 fn maps_native_enum_type() {
238 let mood = enums(&["mood"]);
239 let (column_type, element_type) =
240 map_column_type("USER-DEFINED", "mood", false, &mood).unwrap();
241 assert_eq!(column_type, ColumnType::Enum);
242 assert_eq!(element_type, None);
243 }
244
245 #[test]
246 fn maps_array_of_text() {
247 let none = enums(&[]);
248 let (column_type, element_type) = map_column_type("ARRAY", "_text", false, &none).unwrap();
249 assert_eq!(column_type, ColumnType::Array);
250 assert_eq!(element_type.as_deref(), Some("text"));
251 }
252
253 #[test]
254 fn maps_array_of_int4() {
255 let none = enums(&[]);
256 let (column_type, element_type) = map_column_type("ARRAY", "_int4", false, &none).unwrap();
257 assert_eq!(column_type, ColumnType::Array);
258 assert_eq!(element_type.as_deref(), Some("int"));
259 }
260
261 #[test]
262 fn maps_sequence_for_autoincrement_int4() {
263 let none = enums(&[]);
264 assert_eq!(
265 map_column_type("int4", "int4", true, &none).unwrap().0,
266 ColumnType::Sequence
267 );
268 assert_eq!(
269 map_column_type("int8", "int8", true, &none).unwrap().0,
270 ColumnType::LongSequence
271 );
272 }
273
274 #[test]
275 fn maps_numeric_to_decimal() {
276 let none = enums(&[]);
277 assert_eq!(
278 map_column_type("numeric", "numeric", false, &none)
279 .unwrap()
280 .0,
281 ColumnType::Decimal
282 );
283 }
284
285 #[test]
286 fn unknown_type_is_an_error() {
287 let none = enums(&[]);
288 let err = map_column_type("USER-DEFINED", "some_domain", false, &none).unwrap_err();
289 assert!(matches!(
290 err,
291 SchemaReverseEngineerError::UnsupportedColumnType(_)
292 ));
293 }
294
295 fn column_row(data_type: &str, udt_name: &str) -> ColumnRow {
296 ColumnRow {
297 table_name: "t".to_string(),
298 column_name: "c".to_string(),
299 data_type: data_type.to_string(),
300 udt_name: udt_name.to_string(),
301 character_maximum_length: None,
302 numeric_precision: None,
303 numeric_scale: None,
304 is_nullable: "YES".to_string(),
305 column_default: None,
306 is_identity: "NO".to_string(),
307 is_generated: "NEVER".to_string(),
308 generation_expression: None,
309 }
310 }
311
312 #[test]
313 fn unsupported_column_type_falls_back_to_text_instead_of_aborting() {
314 let info = build_column_info(column_row("USER-DEFINED", "inet"), &enums(&[]));
317 assert_eq!(info.column_type, ColumnType::Text);
318 assert_eq!(info.element_type, None);
319 }
320
321 #[test]
322 fn unsupported_array_element_type_falls_back_to_text() {
323 let info = build_column_info(column_row("ARRAY", "_inet"), &enums(&[]));
324 assert_eq!(info.column_type, ColumnType::Text);
325 assert_eq!(info.element_type, None);
326 }
327}