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.into_iter().map(|row| build_column_info(row, enum_type_names)).collect())
59}
60
61fn build_column_info(row: ColumnRow, enum_type_names: &HashSet<String>) -> ColumnInfo {
62    let is_generated = row.is_generated == "ALWAYS";
63    let is_autoincrement = row.is_identity == "YES"
64        || row
65            .column_default
66            .as_deref()
67            .map(|d| d.starts_with("nextval("))
68            .unwrap_or(false);
69
70    // An unsupported type (inet, money, xml, hstore, PostGIS, ...) falls back to `Text` with a
71    // warning rather than aborting the whole run -- losing one column's exact type is far
72    // better than producing no output file at all for the rest of the database.
73    let (column_type, element_type) =
74        match map_column_type(&row.data_type, &row.udt_name, is_autoincrement, enum_type_names) {
75            Ok(mapped) => mapped,
76            Err(SchemaReverseEngineerError::UnsupportedColumnType(type_name)) => {
77                eprintln!(
78                    "warning: {}.{} has unsupported type '{}' -- falling back to text",
79                    row.table_name, row.column_name, type_name
80                );
81                (ColumnType::Text, None)
82            }
83            Err(other) => unreachable!("map_column_type only ever returns UnsupportedColumnType: {other}"),
84        };
85
86    let enum_type = if column_type == ColumnType::Enum {
87        Some(row.udt_name.clone())
88    } else {
89        None
90    };
91
92    let length = match column_type {
93        ColumnType::Varchar | ColumnType::Char | ColumnType::CiText => row.character_maximum_length.unwrap_or(0),
94        ColumnType::Decimal => row.numeric_precision.unwrap_or(0),
95        _ => 0,
96    };
97    let scale = if column_type == ColumnType::Decimal {
98        row.numeric_scale.unwrap_or(0)
99    } else {
100        0
101    };
102
103    let generated = if is_generated {
104        Some(format!(
105            "generated always as ({}) stored",
106            row.generation_expression.unwrap_or_default()
107        ))
108    } else {
109        None
110    };
111
112    // Postgres always reports a default expression for identity/sequence columns
113    // (e.g. `nextval(...)`); suppress it here since the column type itself already
114    // conveys that it's auto-generated (matches the Java writer's behavior).
115    let default_constraint = if is_generated || matches!(column_type, ColumnType::Sequence | ColumnType::LongSequence) {
116        None
117    } else {
118        row.column_default
119    };
120
121    ColumnInfo {
122        table_name: row.table_name,
123        column_name: row.column_name,
124        column_type,
125        length,
126        scale,
127        required: row.is_nullable == "NO",
128        default_constraint,
129        generated,
130        enum_type,
131        element_type,
132    }
133}
134
135/// Maps a Postgres column's `data_type`/`udt_name` (as reported by `information_schema.columns`)
136/// to a schema-model `ColumnType`, plus an element-type name (lowercase `ColumnType::name()`)
137/// when the column is an array.
138///
139/// Unlike the Java original -- which relied on JDBC `java.sql.Types` and crashed with
140/// `IllegalArgumentException` on `Types.OTHER` (Postgres `uuid`, `jsonb`, and native enum columns
141/// all surface that way via JDBC) -- this maps directly off Postgres's own catalog type names, so
142/// `uuid`/`jsonb`/enum/array columns are all handled without special-casing a JDBC quirk.
143pub fn map_column_type(
144    data_type: &str,
145    udt_name: &str,
146    is_autoincrement: bool,
147    enum_type_names: &HashSet<String>,
148) -> Result<(ColumnType, Option<String>), SchemaReverseEngineerError> {
149    if data_type == "ARRAY" {
150        let element_udt = udt_name.strip_prefix('_').unwrap_or(udt_name);
151        let (element_column_type, _) = base_type_to_column_type(element_udt, false, enum_type_names)?;
152        return Ok((ColumnType::Array, Some(element_column_type.name().to_lowercase())));
153    }
154
155    base_type_to_column_type(udt_name, is_autoincrement, enum_type_names)
156}
157
158fn base_type_to_column_type(
159    udt_name: &str,
160    is_autoincrement: bool,
161    enum_type_names: &HashSet<String>,
162) -> Result<(ColumnType, Option<String>), SchemaReverseEngineerError> {
163    let column_type = match udt_name {
164        "int4" => {
165            if is_autoincrement {
166                ColumnType::Sequence
167            } else {
168                ColumnType::Int
169            }
170        }
171        "int8" => {
172            if is_autoincrement {
173                ColumnType::LongSequence
174            } else {
175                ColumnType::Long
176            }
177        }
178        "int2" => ColumnType::Short,
179        "float4" => ColumnType::Float,
180        "float8" => ColumnType::Double,
181        "numeric" => ColumnType::Decimal,
182        "bool" => ColumnType::Boolean,
183        "varchar" => ColumnType::Varchar,
184        "bpchar" => ColumnType::Char,
185        "text" => ColumnType::Text,
186        "citext" => ColumnType::CiText,
187        "bytea" => ColumnType::Binary,
188        "date" => ColumnType::Date,
189        // Postgres has no dedicated "time with timezone" model type; `Time` is the closest
190        // available representation (the Java original mapped this to `TIMESTAMPTZ`, which was
191        // very likely a bug -- a time-only value has no date component to make a timestamp of).
192        "time" | "timetz" => ColumnType::Time,
193        "timestamp" => ColumnType::DateTime,
194        "timestamptz" => ColumnType::TimestampTz,
195        "uuid" => ColumnType::Uuid,
196        "json" | "jsonb" => ColumnType::Json,
197        other if enum_type_names.contains(other) => ColumnType::Enum,
198        other => return Err(SchemaReverseEngineerError::UnsupportedColumnType(other.to_string())),
199    };
200    Ok((column_type, None))
201}
202
203#[cfg(test)]
204mod tests {
205    use super::*;
206
207    fn enums(names: &[&str]) -> HashSet<String> {
208        names.iter().map(|s| s.to_string()).collect()
209    }
210
211    #[test]
212    fn maps_uuid_and_jsonb() {
213        let none = enums(&[]);
214        assert_eq!(map_column_type("uuid", "uuid", false, &none).unwrap().0, ColumnType::Uuid);
215        assert_eq!(map_column_type("jsonb", "jsonb", false, &none).unwrap().0, ColumnType::Json);
216        assert_eq!(map_column_type("USER-DEFINED", "json", false, &none).unwrap().0, ColumnType::Json);
217    }
218
219    #[test]
220    fn maps_native_enum_type() {
221        let mood = enums(&["mood"]);
222        let (column_type, element_type) = map_column_type("USER-DEFINED", "mood", false, &mood).unwrap();
223        assert_eq!(column_type, ColumnType::Enum);
224        assert_eq!(element_type, None);
225    }
226
227    #[test]
228    fn maps_array_of_text() {
229        let none = enums(&[]);
230        let (column_type, element_type) = map_column_type("ARRAY", "_text", false, &none).unwrap();
231        assert_eq!(column_type, ColumnType::Array);
232        assert_eq!(element_type.as_deref(), Some("text"));
233    }
234
235    #[test]
236    fn maps_array_of_int4() {
237        let none = enums(&[]);
238        let (column_type, element_type) = map_column_type("ARRAY", "_int4", false, &none).unwrap();
239        assert_eq!(column_type, ColumnType::Array);
240        assert_eq!(element_type.as_deref(), Some("int"));
241    }
242
243    #[test]
244    fn maps_sequence_for_autoincrement_int4() {
245        let none = enums(&[]);
246        assert_eq!(
247            map_column_type("int4", "int4", true, &none).unwrap().0,
248            ColumnType::Sequence
249        );
250        assert_eq!(
251            map_column_type("int8", "int8", true, &none).unwrap().0,
252            ColumnType::LongSequence
253        );
254    }
255
256    #[test]
257    fn maps_numeric_to_decimal() {
258        let none = enums(&[]);
259        assert_eq!(map_column_type("numeric", "numeric", false, &none).unwrap().0, ColumnType::Decimal);
260    }
261
262    #[test]
263    fn unknown_type_is_an_error() {
264        let none = enums(&[]);
265        let err = map_column_type("USER-DEFINED", "some_domain", false, &none).unwrap_err();
266        assert!(matches!(err, SchemaReverseEngineerError::UnsupportedColumnType(_)));
267    }
268
269    fn column_row(data_type: &str, udt_name: &str) -> ColumnRow {
270        ColumnRow {
271            table_name: "t".to_string(),
272            column_name: "c".to_string(),
273            data_type: data_type.to_string(),
274            udt_name: udt_name.to_string(),
275            character_maximum_length: None,
276            numeric_precision: None,
277            numeric_scale: None,
278            is_nullable: "YES".to_string(),
279            column_default: None,
280            is_identity: "NO".to_string(),
281            is_generated: "NEVER".to_string(),
282            generation_expression: None,
283        }
284    }
285
286    #[test]
287    fn unsupported_column_type_falls_back_to_text_instead_of_aborting() {
288        // inet, money, xml, hstore, PostGIS, etc. all reach here as an unmapped udt_name --
289        // one such column anywhere used to abort the entire reverse-engineer run.
290        let info = build_column_info(column_row("USER-DEFINED", "inet"), &enums(&[]));
291        assert_eq!(info.column_type, ColumnType::Text);
292        assert_eq!(info.element_type, None);
293    }
294
295    #[test]
296    fn unsupported_array_element_type_falls_back_to_text() {
297        let info = build_column_info(column_row("ARRAY", "_inet"), &enums(&[]));
298        assert_eq!(info.column_type, ColumnType::Text);
299        assert_eq!(info.element_type, None);
300    }
301}