use crate::error::SchemaReverseEngineerError;
use schema_model::model::column_type::ColumnType;
use sqlx::PgPool;
use std::collections::HashSet;
pub use crate::info::ColumnInfo;
#[derive(Debug, sqlx::FromRow)]
struct ColumnRow {
table_name: String,
column_name: String,
data_type: String,
udt_name: String,
character_maximum_length: Option<i32>,
numeric_precision: Option<i32>,
numeric_scale: Option<i32>,
is_nullable: String,
column_default: Option<String>,
is_identity: String,
is_generated: String,
generation_expression: Option<String>,
}
pub async fn list_columns(
pool: &PgPool,
db_schema: &str,
enum_type_names: &HashSet<String>,
) -> Result<Vec<ColumnInfo>, SchemaReverseEngineerError> {
let rows: Vec<ColumnRow> = sqlx::query_as(
"SELECT c.table_name, c.column_name, c.data_type, c.udt_name, \
c.character_maximum_length::int4 AS character_maximum_length, \
c.numeric_precision::int4 AS numeric_precision, \
c.numeric_scale::int4 AS numeric_scale, \
c.is_nullable, c.column_default, c.is_identity, c.is_generated, c.generation_expression \
FROM information_schema.columns c \
JOIN information_schema.tables t \
ON t.table_schema = c.table_schema AND t.table_name = c.table_name AND t.table_type = 'BASE TABLE' \
WHERE c.table_schema = $1 \
ORDER BY c.table_name, c.ordinal_position",
)
.bind(db_schema)
.fetch_all(pool)
.await
.map_err(|e| SchemaReverseEngineerError::Introspection(e.to_string()))?;
Ok(rows
.into_iter()
.map(|row| build_column_info(row, enum_type_names))
.collect())
}
fn build_column_info(row: ColumnRow, enum_type_names: &HashSet<String>) -> ColumnInfo {
let is_generated = row.is_generated == "ALWAYS";
let is_autoincrement = row.is_identity == "YES"
|| row
.column_default
.as_deref()
.map(|d| d.starts_with("nextval("))
.unwrap_or(false);
let (column_type, element_type) = match map_column_type(
&row.data_type,
&row.udt_name,
is_autoincrement,
enum_type_names,
) {
Ok(mapped) => mapped,
Err(SchemaReverseEngineerError::UnsupportedColumnType(type_name)) => {
eprintln!(
"warning: {}.{} has unsupported type '{}' -- falling back to text",
row.table_name, row.column_name, type_name
);
(ColumnType::Text, None)
}
Err(other) => {
unreachable!("map_column_type only ever returns UnsupportedColumnType: {other}")
}
};
let enum_type = if column_type == ColumnType::Enum {
Some(row.udt_name.clone())
} else {
None
};
let length = match column_type {
ColumnType::Varchar | ColumnType::Char | ColumnType::CiText => {
row.character_maximum_length.unwrap_or(0)
}
ColumnType::Decimal => row.numeric_precision.unwrap_or(0),
_ => 0,
};
let scale = if column_type == ColumnType::Decimal {
row.numeric_scale.unwrap_or(0)
} else {
0
};
let generated = if is_generated {
Some(row.generation_expression.unwrap_or_default())
} else {
None
};
let default_constraint =
if is_generated || matches!(column_type, ColumnType::Sequence | ColumnType::LongSequence) {
None
} else {
row.column_default
};
ColumnInfo {
table_name: row.table_name,
column_name: row.column_name,
column_type,
length,
scale,
required: row.is_nullable == "NO",
default_constraint,
generated,
enum_type,
element_type,
}
}
pub fn map_column_type(
data_type: &str,
udt_name: &str,
is_autoincrement: bool,
enum_type_names: &HashSet<String>,
) -> Result<(ColumnType, Option<String>), SchemaReverseEngineerError> {
if data_type == "ARRAY" {
let element_udt = udt_name.strip_prefix('_').unwrap_or(udt_name);
let (element_column_type, _) =
base_type_to_column_type(element_udt, false, enum_type_names)?;
return Ok((
ColumnType::Array,
Some(element_column_type.name().to_lowercase()),
));
}
base_type_to_column_type(udt_name, is_autoincrement, enum_type_names)
}
fn base_type_to_column_type(
udt_name: &str,
is_autoincrement: bool,
enum_type_names: &HashSet<String>,
) -> Result<(ColumnType, Option<String>), SchemaReverseEngineerError> {
let column_type = match udt_name {
"int4" => {
if is_autoincrement {
ColumnType::Sequence
} else {
ColumnType::Int
}
}
"int8" => {
if is_autoincrement {
ColumnType::LongSequence
} else {
ColumnType::Long
}
}
"int2" => ColumnType::Short,
"float4" => ColumnType::Float,
"float8" => ColumnType::Double,
"numeric" => ColumnType::Decimal,
"bool" => ColumnType::Boolean,
"varchar" => ColumnType::Varchar,
"bpchar" => ColumnType::Char,
"text" => ColumnType::Text,
"citext" => ColumnType::CiText,
"bytea" => ColumnType::Binary,
"date" => ColumnType::Date,
"time" | "timetz" => ColumnType::Time,
"timestamp" => ColumnType::DateTime,
"timestamptz" => ColumnType::TimestampTz,
"uuid" => ColumnType::Uuid,
"json" | "jsonb" => ColumnType::Json,
other if enum_type_names.contains(other) => ColumnType::Enum,
other => {
return Err(SchemaReverseEngineerError::UnsupportedColumnType(
other.to_string(),
));
}
};
Ok((column_type, None))
}
#[cfg(test)]
mod tests {
use super::*;
fn enums(names: &[&str]) -> HashSet<String> {
names.iter().map(|s| s.to_string()).collect()
}
#[test]
fn maps_uuid_and_jsonb() {
let none = enums(&[]);
assert_eq!(
map_column_type("uuid", "uuid", false, &none).unwrap().0,
ColumnType::Uuid
);
assert_eq!(
map_column_type("jsonb", "jsonb", false, &none).unwrap().0,
ColumnType::Json
);
assert_eq!(
map_column_type("USER-DEFINED", "json", false, &none)
.unwrap()
.0,
ColumnType::Json
);
}
#[test]
fn maps_native_enum_type() {
let mood = enums(&["mood"]);
let (column_type, element_type) =
map_column_type("USER-DEFINED", "mood", false, &mood).unwrap();
assert_eq!(column_type, ColumnType::Enum);
assert_eq!(element_type, None);
}
#[test]
fn maps_array_of_text() {
let none = enums(&[]);
let (column_type, element_type) = map_column_type("ARRAY", "_text", false, &none).unwrap();
assert_eq!(column_type, ColumnType::Array);
assert_eq!(element_type.as_deref(), Some("text"));
}
#[test]
fn maps_array_of_int4() {
let none = enums(&[]);
let (column_type, element_type) = map_column_type("ARRAY", "_int4", false, &none).unwrap();
assert_eq!(column_type, ColumnType::Array);
assert_eq!(element_type.as_deref(), Some("int"));
}
#[test]
fn maps_sequence_for_autoincrement_int4() {
let none = enums(&[]);
assert_eq!(
map_column_type("int4", "int4", true, &none).unwrap().0,
ColumnType::Sequence
);
assert_eq!(
map_column_type("int8", "int8", true, &none).unwrap().0,
ColumnType::LongSequence
);
}
#[test]
fn maps_numeric_to_decimal() {
let none = enums(&[]);
assert_eq!(
map_column_type("numeric", "numeric", false, &none)
.unwrap()
.0,
ColumnType::Decimal
);
}
#[test]
fn unknown_type_is_an_error() {
let none = enums(&[]);
let err = map_column_type("USER-DEFINED", "some_domain", false, &none).unwrap_err();
assert!(matches!(
err,
SchemaReverseEngineerError::UnsupportedColumnType(_)
));
}
fn column_row(data_type: &str, udt_name: &str) -> ColumnRow {
ColumnRow {
table_name: "t".to_string(),
column_name: "c".to_string(),
data_type: data_type.to_string(),
udt_name: udt_name.to_string(),
character_maximum_length: None,
numeric_precision: None,
numeric_scale: None,
is_nullable: "YES".to_string(),
column_default: None,
is_identity: "NO".to_string(),
is_generated: "NEVER".to_string(),
generation_expression: None,
}
}
#[test]
fn unsupported_column_type_falls_back_to_text_instead_of_aborting() {
let info = build_column_info(column_row("USER-DEFINED", "inet"), &enums(&[]));
assert_eq!(info.column_type, ColumnType::Text);
assert_eq!(info.element_type, None);
}
#[test]
fn unsupported_array_element_type_falls_back_to_text() {
let info = build_column_info(column_row("ARRAY", "_inet"), &enums(&[]));
assert_eq!(info.column_type, ColumnType::Text);
assert_eq!(info.element_type, None);
}
}