schema_reverse_engineer/postgres/
columns.rs1use 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 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
124pub 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 "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}