use super::{common::*, DatabaseIntrospector};
use anyhow::Result;
use async_trait::async_trait;
use serde_json::{Map, Value};
use sqlx::{Column, MySql, Pool, Row, TypeInfo, ValueRef};
use std::collections::HashMap;
use std::path::PathBuf;
use tokio::fs;
pub struct MySqlIntrospector {
pool: Pool<MySql>,
db_name: String,
}
impl MySqlIntrospector {
pub async fn new(database_url: &str) -> Result<Self> {
let pool = sqlx::mysql::MySqlPoolOptions::new()
.max_connections(5)
.connect(database_url)
.await?;
let db_name_row = sqlx::query("SELECT DATABASE() as db_name")
.fetch_one(&pool)
.await?;
let db_name: String = db_name_row.try_get("db_name")?;
Ok(Self { pool, db_name })
}
fn parse_enum_values(column_type: &str) -> Vec<String> {
if !column_type.starts_with("enum(") || !column_type.ends_with(")") {
return Vec::new();
}
let values_part = &column_type[5..column_type.len()-1]; let mut values = Vec::new();
let mut current_value = String::new();
let mut in_quotes = false;
let mut escape_next = false;
for ch in values_part.chars() {
if escape_next {
current_value.push(ch);
escape_next = false;
} else if ch == '\\' {
escape_next = true;
} else if ch == '\'' {
if in_quotes {
values.push(current_value.clone());
current_value.clear();
in_quotes = false;
} else {
in_quotes = true;
}
} else if in_quotes {
current_value.push(ch);
}
}
values
}
fn generate_field_yaml(&self, column: &ColumnInfo) -> String {
let mut field = String::new();
let escaped_field_name = escape_yaml_field_name(&column.name);
field.push_str(&format!(" {}:\n", escaped_field_name));
let enum_values = if let Some(column_type) = &column.column_type {
Self::parse_enum_values(column_type)
} else {
Vec::new()
};
if !enum_values.is_empty() {
field.push_str(" type: enum\n");
field.push_str(" values:\n");
for value in &enum_values {
field.push_str(&format!(" - \"{}\"\n", value));
}
if column.default_value.is_none() {
if let Some(first_value) = enum_values.first() {
field.push_str(&format!(" default: \"{}\"\n", first_value));
}
} else if let Some(default) = &column.default_value {
let clean_default = default.trim_matches('\'').trim_matches('"');
field.push_str(&format!(" default: \"{}\"\n", clean_default));
}
} else {
let data_type = if column.data_type.contains("varchar") || column.data_type.contains("char") {
if let Some(max_length) = column.max_length {
format!("string({})", max_length)
} else {
"string".to_string()
}
} else if column.data_type == "text" {
"text".to_string() } else if column.data_type.contains("decimal") {
if let (Some(precision), Some(scale)) = (column.numeric_precision, column.numeric_scale) {
format!("decimal({},{})", precision, scale)
} else {
"decimal".to_string()
}
} else {
column.data_type.clone()
};
field.push_str(&format!(" type: {}\n", data_type));
if let Some(default) = &column.default_value {
if default == "AUTO_INCREMENT" {
field.push_str(" auto: true\n");
} else if !default.is_empty() && default != "NULL" {
field.push_str(&format!(" default: {}\n", default));
}
}
}
let lang_type = self.map_to_rust_type(&column.data_type, column.is_nullable);
field.push_str(&format!(" lang_type: {}\n", lang_type));
if column.is_nullable {
field.push_str(" nullable: true\n");
} else {
field.push_str(" required: true\n");
}
if column.is_primary_key {
field.push_str(" primary_key: true\n");
}
if column.is_unique {
field.push_str(" unique: true\n");
}
let ai_hint = generate_field_ai_hint(&column.name, &column.data_type, column.is_foreign_key);
field.push_str(&format!(" ai: \"{}\"\n", ai_hint));
if column.is_foreign_key {
if let (Some(table), Some(col)) = (&column.foreign_table, &column.foreign_column) {
field.push_str(&format!(" foreign_key: {}.{}\n", table, col));
}
}
if let Some(comment) = &column.comment {
if !comment.is_empty() {
field.push_str(&format!(" column_comment: \"{}\"\n", comment));
}
}
field.push_str("\n");
field
}
fn map_to_rust_type(&self, data_type: &str, is_nullable: bool) -> String {
let base_type = match data_type {
"int" | "integer" => "i32",
"bigint" => "i64",
"smallint" => "i16",
"tinyint" => "i8",
"mediumint" => "i32",
"float" => "f32",
"double" | "real" => "f64",
"decimal" | "numeric" => "Decimal",
"boolean" | "bool" => "bool",
"varchar" | "char" | "text" | "longtext" | "mediumtext" | "tinytext" => "String",
"date" => "NaiveDate",
"time" => "NaiveTime",
"datetime" | "timestamp" => "DateTime<Utc>",
"json" => "serde_json::Value",
"enum" => "String",
_ => "String",
};
if is_nullable {
format!("Option<{}>", base_type)
} else {
base_type.to_string()
}
}
async fn get_view_definition(&self, name: &str) -> Result<Option<String>> {
let row = sqlx::query(
r#"
SELECT VIEW_DEFINITION AS def
FROM information_schema.VIEWS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?
"#,
)
.bind(name)
.fetch_optional(&self.pool)
.await?;
let def = match row {
Some(r) => r.try_get::<Option<String>, _>("def").ok().flatten(),
None => None,
};
Ok(def
.map(|s| s.trim().trim_end_matches(';').trim().to_string())
.filter(|s| !s.is_empty()))
}
async fn generate_table_schema_yaml(&self, description: &TableDescription) -> Result<String> {
let mut yaml = String::new();
yaml.push_str(&format!("# {} entity - Generated from database\n\n", description.table.name));
let model_name = to_pascal_case(&description.table.name);
yaml.push_str(&format!("{}:\n", model_name));
yaml.push_str(&format!(" table: {}\n", description.table.name));
yaml.push_str(&format!(" database_type: mysql\n"));
yaml.push_str(&format!(" database_name: {}\n", self.db_name));
let element_type = if description.table.table_type.to_lowercase().contains("view") {
"view"
} else {
"table"
};
yaml.push_str(&format!(" element_type: {}\n", element_type));
yaml.push_str(" version: 1\n");
if element_type == "view" {
if let Some(body) = self.get_view_definition(&description.table.name).await? {
yaml.push_str(" view:\n");
yaml.push_str(" sql: |\n");
for line in body.lines() {
yaml.push_str(&format!(" {}\n", line));
}
}
}
if let Some(comment) = &description.table.comment {
if !comment.is_empty() {
yaml.push_str(&format!(" description: \"{}\"\n", comment));
}
}
let ai_context = generate_table_ai_context(&description.table.name);
yaml.push_str(&format!(" ai_context: \"{}\"\n", ai_context));
yaml.push_str(" \n fields:\n");
for column in &description.columns {
yaml.push_str(&self.generate_field_yaml(column));
}
let relations = generate_relations_yaml(&description.columns);
if !relations.is_empty() {
yaml.push_str(" \n relations:\n");
yaml.push_str(&relations);
}
if !description.indexes.is_empty() {
yaml.push_str(" \n indexes:\n");
for index in &description.indexes {
if index.columns.len() == 1 {
yaml.push_str(&format!(" - {}\n", index.columns[0]));
} else {
yaml.push_str(&format!(" - [{}]\n", index.columns.join(", ")));
}
}
}
if !description.triggers.is_empty() {
yaml.push_str(" \n triggers:\n");
for trigger in &description.triggers {
yaml.push_str(&format!(" - name: {}\n", trigger.name));
yaml.push_str(&format!(" event: {}\n", trigger.event));
yaml.push_str(&format!(" timing: {}\n", trigger.timing));
yaml.push_str(&format!(" for_each: {}\n", trigger.for_each));
yaml.push_str(&format!(" ai: \"Database trigger - executes automatically on table changes. Consider application logic implications.\"\n"));
yaml.push_str("\n");
}
}
yaml.push_str("\n");
Ok(yaml)
}
}
#[async_trait]
impl DatabaseIntrospector for MySqlIntrospector {
async fn list_tables(&self, metadata: bool) -> Result<Vec<TableInfo>> {
let query = if metadata {
r#"
SELECT
CAST(t.TABLE_NAME AS CHAR) as table_name,
CAST(t.TABLE_SCHEMA AS CHAR) as table_schema,
CAST(t.TABLE_TYPE AS CHAR) as table_type,
t.TABLE_ROWS as row_count,
(t.DATA_LENGTH + t.INDEX_LENGTH) as size_bytes,
CAST(t.TABLE_COMMENT AS CHAR) as comment
FROM information_schema.tables t
WHERE t.TABLE_SCHEMA = ?
ORDER BY t.TABLE_NAME
"#
} else {
r#"
SELECT
CAST(TABLE_NAME AS CHAR) as table_name,
CAST(TABLE_SCHEMA AS CHAR) as table_schema,
CAST(TABLE_TYPE AS CHAR) as table_type,
NULL as row_count,
NULL as size_bytes,
NULL as comment
FROM information_schema.tables
WHERE TABLE_SCHEMA = ?
ORDER BY TABLE_NAME
"#
};
let rows = sqlx::query(query)
.bind(&self.db_name)
.fetch_all(&self.pool)
.await?;
let mut tables = Vec::new();
for row in rows {
tables.push(TableInfo {
name: row.try_get("table_name")?,
schema: row.try_get("table_schema").ok(),
table_type: row.try_get("table_type")?,
row_count: row.try_get("row_count").ok().flatten(),
size_bytes: row.try_get("size_bytes").ok().flatten(),
comment: row.try_get("comment").ok().flatten(),
});
}
Ok(tables)
}
async fn describe_table(&self, table_name: &str) -> Result<TableDescription> {
let table_row = sqlx::query(
r#"
SELECT
CAST(TABLE_NAME AS CHAR) as table_name,
CAST(TABLE_SCHEMA AS CHAR) as table_schema,
CAST(TABLE_TYPE AS CHAR) as table_type,
TABLE_ROWS as row_count,
(DATA_LENGTH + INDEX_LENGTH) as size_bytes,
CAST(TABLE_COMMENT AS CHAR) as comment
FROM information_schema.tables
WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?
"#
)
.bind(table_name)
.bind(&self.db_name)
.fetch_optional(&self.pool)
.await?;
let table_info = match table_row {
Some(row) => TableInfo {
name: row.try_get("table_name")?,
schema: row.try_get("table_schema").ok(),
table_type: row.try_get("table_type")?,
row_count: row.try_get("row_count").ok().flatten(),
size_bytes: row.try_get("size_bytes").ok().flatten(),
comment: row.try_get("comment").ok().flatten(),
},
None => anyhow::bail!("Table '{}' not found", table_name),
};
let column_rows = sqlx::query(
r#"
SELECT
CAST(c.COLUMN_NAME AS CHAR) as column_name,
CAST(c.DATA_TYPE AS CHAR) as data_type,
CAST(c.COLUMN_TYPE AS CHAR) as column_type,
c.IS_NULLABLE as is_nullable,
c.COLUMN_DEFAULT as column_default,
c.CHARACTER_MAXIMUM_LENGTH as character_maximum_length,
c.NUMERIC_PRECISION as numeric_precision,
c.NUMERIC_SCALE as numeric_scale,
CAST(c.COLUMN_COMMENT AS CHAR) as column_comment,
CAST(c.COLUMN_KEY AS CHAR) as column_key,
CAST(c.EXTRA AS CHAR) as extra
FROM information_schema.columns c
WHERE c.TABLE_NAME = ? AND c.TABLE_SCHEMA = ?
ORDER BY c.ORDINAL_POSITION
"#
)
.bind(table_name)
.bind(&self.db_name)
.fetch_all(&self.pool)
.await?;
let fk_rows = sqlx::query(
r#"
SELECT
CAST(kcu.COLUMN_NAME AS CHAR) as column_name,
CAST(kcu.REFERENCED_TABLE_NAME AS CHAR) as referenced_table_name,
CAST(kcu.REFERENCED_COLUMN_NAME AS CHAR) as referenced_column_name,
CAST(rc.DELETE_RULE AS CHAR) as on_delete,
CAST(rc.UPDATE_RULE AS CHAR) as on_update
FROM information_schema.key_column_usage kcu
LEFT JOIN information_schema.REFERENTIAL_CONSTRAINTS rc
ON rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
AND rc.CONSTRAINT_SCHEMA = kcu.TABLE_SCHEMA
WHERE kcu.TABLE_NAME = ?
AND kcu.TABLE_SCHEMA = ?
AND kcu.REFERENCED_TABLE_NAME IS NOT NULL
"#
)
.bind(table_name)
.bind(&self.db_name)
.fetch_all(&self.pool)
.await?;
let mut foreign_keys = HashMap::new();
for fk_row in fk_rows {
let column_name: String = fk_row.try_get("column_name")?;
let referenced_table: String = fk_row.try_get("referenced_table_name")?;
let referenced_column: String = fk_row.try_get("referenced_column_name")?;
let on_delete: Option<String> = fk_row.try_get("on_delete").ok();
let on_update: Option<String> = fk_row.try_get("on_update").ok();
foreign_keys.insert(column_name, (referenced_table, referenced_column, on_delete, on_update));
}
let mut columns = Vec::new();
for row in column_rows {
let column_name: String = row.try_get("column_name")?;
let column_key: String = row.try_get("column_key").unwrap_or_default();
let extra: Option<String> = row.try_get("extra").ok();
let is_auto_increment = extra.as_ref().map_or(false, |s| s.contains("auto_increment"));
let is_primary_key = column_key == "PRI";
let is_unique = column_key == "UNI";
let is_foreign_key = foreign_keys.contains_key(&column_name);
let (foreign_table, foreign_column, on_delete, on_update) = if is_foreign_key {
let (table, column, del, upd) = foreign_keys.get(&column_name).unwrap();
(Some(table.clone()), Some(column.clone()), del.clone(), upd.clone())
} else {
(None, None, None, None)
};
let default_value = if is_auto_increment {
Some("AUTO_INCREMENT".to_string())
} else {
row.try_get("column_default").ok()
};
columns.push(ColumnInfo {
name: column_name,
data_type: row.try_get("data_type")?,
column_type: row.try_get("column_type").ok(),
postgres_type_name: None, is_nullable: row.try_get::<&str, _>("is_nullable")? == "YES",
default_value,
is_primary_key,
is_unique,
is_foreign_key,
foreign_table,
foreign_column,
on_delete,
on_update,
comment: row.try_get("column_comment").ok(),
max_length: row.try_get("character_maximum_length").ok(),
numeric_precision: row.try_get("numeric_precision").ok(),
numeric_scale: row.try_get("numeric_scale").ok(),
});
}
let index_rows = sqlx::query(
r#"
SELECT
CAST(s.INDEX_NAME AS CHAR) as index_name,
CAST(s.COLUMN_NAME AS CHAR) as column_name,
s.NON_UNIQUE as non_unique,
s.SEQ_IN_INDEX as seq_in_index
FROM information_schema.statistics s
WHERE s.TABLE_NAME = ? AND s.TABLE_SCHEMA = ?
ORDER BY s.INDEX_NAME, s.SEQ_IN_INDEX
"#
)
.bind(table_name)
.bind(&self.db_name)
.fetch_all(&self.pool)
.await?;
let mut index_map: HashMap<String, IndexInfo> = HashMap::new();
for row in index_rows {
let index_name: String = row.try_get("index_name")?;
let column_name: String = row.try_get("column_name")?;
let non_unique: i32 = row.try_get("non_unique")?;
index_map.entry(index_name.clone())
.or_insert_with(|| IndexInfo {
name: index_name.clone(),
columns: Vec::new(),
is_unique: non_unique == 0,
is_primary: index_name == "PRIMARY",
})
.columns.push(column_name);
}
let indexes: Vec<IndexInfo> = index_map.into_values().collect();
let trigger_rows = sqlx::query(
r#"
SELECT
CAST(TRIGGER_NAME AS CHAR) as trigger_name,
CAST(EVENT_MANIPULATION AS CHAR) as event,
CAST(ACTION_TIMING AS CHAR) as timing,
CAST(ACTION_ORIENTATION AS CHAR) as for_each,
CAST(ACTION_CONDITION AS CHAR) as condition_expr,
CAST(ACTION_STATEMENT AS CHAR) as body
FROM information_schema.TRIGGERS
WHERE EVENT_OBJECT_TABLE = ? AND EVENT_OBJECT_SCHEMA = ?
"#
)
.bind(table_name)
.bind(&self.db_name)
.fetch_all(&self.pool)
.await?;
let mut triggers = Vec::new();
for row in trigger_rows {
triggers.push(TriggerInfo {
name: row.try_get("trigger_name")?,
event: row.try_get("event").unwrap_or_default(),
timing: row.try_get("timing").unwrap_or_default(),
for_each: row.try_get("for_each").unwrap_or_default(),
condition: row.try_get("condition_expr").ok(),
body: row.try_get("body").ok(),
description: None,
});
}
let mut constraints: Vec<ConstraintInfo> = foreign_keys
.iter()
.map(|(column, (ftable, fcol, on_delete, on_update))| ConstraintInfo {
name: format!("fk_{}_{}", table_name, column),
constraint_type: "FOREIGN KEY".to_string(),
columns: vec![column.clone()],
foreign_table: Some(ftable.clone()),
foreign_columns: Some(vec![fcol.clone()]),
check_expression: None,
on_delete: on_delete.clone(),
on_update: on_update.clone(),
})
.collect();
constraints.sort_by(|a, b| a.name.cmp(&b.name));
Ok(TableDescription {
table: table_info,
columns,
indexes,
constraints,
triggers,
})
}
async fn generate_schemas(
&self,
output_dir: &PathBuf,
force: bool,
filter_tables: &[String],
) -> Result<()> {
if !output_dir.exists() {
fs::create_dir_all(output_dir).await?;
}
let tables = self.list_tables(true).await?;
println!("📋 Found {} tables", tables.len());
let mut generated_count = 0;
let mut skipped_count = 0;
for table in tables {
if !filter_tables.is_empty() && !filter_tables.contains(&table.name) {
continue;
}
let schema_file = output_dir.join(format!("{}.yaml", table.name));
if schema_file.exists() && !force {
println!("⚠️ Skipping existing schema: {}", schema_file.display());
skipped_count += 1;
continue;
}
let description = self.describe_table(&table.name).await?;
let yaml_content = self.generate_table_schema_yaml(&description).await?;
fs::write(&schema_file, yaml_content).await?;
println!("✅ Generated schema: {}", schema_file.display());
generated_count += 1;
}
let meta_file = output_dir.join("_meta.yaml");
if !meta_file.exists() || force {
let meta_content = self.generate_meta_yaml().await?;
fs::write(&meta_file, meta_content).await?;
println!("✅ Generated metadata: {}", meta_file.display());
}
println!("📊 Generated {} schemas, skipped {}", generated_count, skipped_count);
Ok(())
}
async fn generate_meta_yaml(&self) -> Result<String> {
let mut yaml = String::new();
yaml.push_str("# Global schema configuration - Generated from MySQL database\n\n");
yaml.push_str("version: \"1.0\"\n");
yaml.push_str("database_type: mysql\n");
yaml.push_str(&format!("database_name: {}\n", self.db_name));
yaml.push_str(&format!("description: \"Schema generated from MySQL database '{}'\"\n", self.db_name));
yaml.push_str("ai_context: \"Generated schemas from existing MySQL database structure with intelligent field mapping\"\n");
yaml.push_str("\n");
yaml.push_str("# Global field defaults\n");
yaml.push_str("field_defaults:\n");
yaml.push_str(" string:\n");
yaml.push_str(" max_length: 255\n");
yaml.push_str(" charset: utf8mb4\n");
yaml.push_str(" \n");
yaml.push_str(" timestamp:\n");
yaml.push_str(" default: now\n");
yaml.push_str(" on_update: now\n");
yaml.push_str(" \n");
yaml.push_str("# Code generation settings\n");
yaml.push_str("generation:\n");
yaml.push_str(" base_class: \"BaseModel\"\n");
yaml.push_str(" use_traits: [HasTimestamps]\n");
yaml.push_str("\n");
Ok(yaml)
}
async fn get_database_name(&self) -> Result<String> {
Ok(self.db_name.clone())
}
async fn export_data(&self, query: &str, format: &str) -> Result<String> {
let rows = sqlx::query(query).fetch_all(&self.pool).await?;
let headers = rows
.first()
.map(|row| {
row.columns()
.iter()
.map(|column| column.name().to_string())
.collect::<Vec<_>>()
})
.unwrap_or_default();
let mut json_rows = Vec::new();
for row in &rows {
let mut object = Map::new();
for (index, column) in row.columns().iter().enumerate() {
object.insert(
column.name().to_string(),
Self::mysql_value_to_json(row, index)?,
);
}
json_rows.push(Value::Object(object));
}
match format {
"json" => Ok(serde_json::to_string_pretty(&json_rows)?),
"csv" => Ok(rows_to_csv(&headers, &json_rows)),
_ => anyhow::bail!("Unsupported export format: {}", format),
}
}
}
impl MySqlIntrospector {
fn mysql_value_to_json(row: &sqlx::mysql::MySqlRow, index: usize) -> Result<Value> {
let raw = row.try_get_raw(index)?;
if raw.is_null() {
return Ok(Value::Null);
}
let type_name = raw.type_info().name().to_uppercase();
if type_name.contains("INT") {
if let Ok(v) = row.try_get::<i64, _>(index) {
return Ok(Value::from(v));
}
if let Ok(v) = row.try_get::<u64, _>(index) {
return Ok(Value::from(v));
}
}
if type_name.contains("DOUBLE") || type_name.contains("FLOAT") {
if let Ok(v) = row.try_get::<f64, _>(index) {
return Ok(Value::from(v));
}
}
if type_name.contains("DECIMAL") || type_name.contains("NUMERIC") {
if let Ok(v) = row.try_get::<sqlx::types::BigDecimal, _>(index) {
return Ok(Value::String(v.to_string()));
}
}
if type_name.contains("JSON") {
if let Ok(v) = row.try_get::<Value, _>(index) {
return Ok(v);
}
}
if type_name.contains("DATETIME") || type_name.contains("TIMESTAMP") {
if let Ok(v) = row.try_get::<chrono::NaiveDateTime, _>(index) {
return Ok(Value::String(v.to_string()));
}
if let Ok(v) = row.try_get::<chrono::DateTime<chrono::Utc>, _>(index) {
return Ok(Value::String(v.to_rfc3339()));
}
}
if type_name == "DATE" {
if let Ok(v) = row.try_get::<chrono::NaiveDate, _>(index) {
return Ok(Value::String(v.to_string()));
}
}
if type_name == "TIME" {
if let Ok(v) = row.try_get::<chrono::NaiveTime, _>(index) {
return Ok(Value::String(v.to_string()));
}
}
if type_name.contains("BLOB") || type_name.contains("BINARY") {
if let Ok(v) = row.try_get::<Vec<u8>, _>(index) {
return Ok(Value::String(
v.iter().map(|b| format!("{:02x}", b)).collect::<String>(),
));
}
}
if let Ok(v) = row.try_get::<String, _>(index) {
return Ok(Value::String(v));
}
Ok(Value::Null)
}
}