Skip to main content

schema_reverse_engineer/postgres/
columns.rs

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    // An unsupported type (inet, money, xml, hstore, PostGIS, ...) falls back to `Text` with a
74    // warning rather than aborting the whole run -- losing one column's exact type is far
75    // better than producing no output file at all for the rest of the database.
76    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    // Postgres always reports a default expression for identity/sequence columns
124    // (e.g. `nextval(...)`); suppress it here since the column type itself already
125    // conveys that it's auto-generated (matches the Java writer's behavior).
126    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
147/// Maps a Postgres column's `data_type`/`udt_name` (as reported by `information_schema.columns`)
148/// to a schema-model `ColumnType`, plus an element-type name (lowercase `ColumnType::name()`)
149/// when the column is an array.
150///
151/// Unlike the Java original -- which relied on JDBC `java.sql.Types` and crashed with
152/// `IllegalArgumentException` on `Types.OTHER` (Postgres `uuid`, `jsonb`, and native enum columns
153/// all surface that way via JDBC) -- this maps directly off Postgres's own catalog type names, so
154/// `uuid`/`jsonb`/enum/array columns are all handled without special-casing a JDBC quirk.
155pub 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        // Postgres has no dedicated "time with timezone" model type; `Time` is the closest
206        // available representation (the Java original mapped this to `TIMESTAMPTZ`, which was
207        // very likely a bug -- a time-only value has no date component to make a timestamp of).
208        "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        // inet, money, xml, hstore, PostGIS, etc. all reach here as an unmapped udt_name --
329        // one such column anywhere used to abort the entire reverse-engineer run.
330        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}