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