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