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
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    // An unsupported type (inet, money, xml, hstore, PostGIS, ...) falls back to `Text` with a
62    // warning rather than aborting the whole run -- losing one column's exact type is far
63    // better than producing no output file at all for the rest of the database.
64    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        // Bare expression only - each dialect's generator wraps it in its own syntax.
104        Some(row.generation_expression.unwrap_or_default())
105    } else {
106        None
107    };
108
109    // Postgres always reports a default expression for identity/sequence columns
110    // (e.g. `nextval(...)`); suppress it here since the column type itself already
111    // conveys that it's auto-generated (matches the Java writer's behavior).
112    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
133/// Maps a Postgres column's `data_type`/`udt_name` (as reported by `information_schema.columns`)
134/// to a schema-model `ColumnType`, plus an element-type name (lowercase `ColumnType::name()`)
135/// when the column is an array.
136///
137/// Unlike the Java original -- which relied on JDBC `java.sql.Types` and crashed with
138/// `IllegalArgumentException` on `Types.OTHER` (Postgres `uuid`, `jsonb`, and native enum columns
139/// all surface that way via JDBC) -- this maps directly off Postgres's own catalog type names, so
140/// `uuid`/`jsonb`/enum/array columns are all handled without special-casing a JDBC quirk.
141pub 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        // Postgres has no dedicated "time with timezone" model type; `Time` is the closest
192        // available representation (the Java original mapped this to `TIMESTAMPTZ`, which was
193        // very likely a bug -- a time-only value has no date component to make a timestamp of).
194        "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        // inet, money, xml, hstore, PostGIS, etc. all reach here as an unmapped udt_name --
315        // one such column anywhere used to abort the entire reverse-engineer run.
316        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}