use crate::database::{Storage, Value};
use crate::sql::executor::QueryResult;
use crate::yaml::schema::SqlType;
use std::sync::Arc;
use tracing::info;
#[derive(Clone)]
pub struct MySqlInformationSchema {
tables: Vec<TableInfo>,
columns: Vec<ColumnInfo>,
foreign_keys: Vec<ForeignKeyInfo>,
}
#[derive(Clone)]
struct ForeignKeyInfo {
table_name: String,
column_name: String,
referenced_table: String,
referenced_column: String,
}
#[derive(Clone)]
struct TableInfo {
table_catalog: String,
table_schema: String,
table_name: String,
table_type: String,
engine: String,
version: i64,
row_format: String,
table_rows: i64,
avg_row_length: i64,
data_length: i64,
max_data_length: i64,
index_length: i64,
data_free: i64,
auto_increment: Option<i64>,
create_time: Option<String>,
update_time: Option<String>,
check_time: Option<String>,
table_collation: String,
checksum: Option<i64>,
create_options: String,
table_comment: String,
}
#[derive(Clone)]
struct ColumnInfo {
table_catalog: String,
table_schema: String,
table_name: String,
column_name: String,
ordinal_position: i64,
column_default: Option<String>,
is_nullable: String,
data_type: String,
character_maximum_length: Option<i64>,
character_octet_length: Option<i64>,
numeric_precision: Option<i64>,
numeric_scale: Option<i64>,
datetime_precision: Option<i64>,
character_set_name: Option<String>,
collation_name: Option<String>,
column_type: String,
column_key: String,
extra: String,
privileges: String,
column_comment: String,
generation_expression: String,
srs_id: Option<i64>,
}
impl MySqlInformationSchema {
pub fn new(_storage: Arc<Storage>) -> Self {
Self {
tables: Vec::new(),
columns: Vec::new(),
foreign_keys: Vec::new(),
}
}
pub fn add_user_table(&mut self, table_name: &str, columns: &[crate::database::Column]) {
info!("Adding table '{}' to MySQL information_schema", table_name);
for col in columns {
if let Some((ref_table, ref_column)) = &col.references {
info!(
"Column '{}' in table '{}' has foreign key reference to {}.{}",
col.name, table_name, ref_table, ref_column
);
self.foreign_keys.push(ForeignKeyInfo {
table_name: table_name.to_string(),
column_name: col.name.clone(),
referenced_table: ref_table.clone(),
referenced_column: ref_column.clone(),
});
}
}
self.tables.push(TableInfo {
table_catalog: "def".to_string(),
table_schema: "public".to_string(),
table_name: table_name.to_string(),
table_type: "BASE TABLE".to_string(),
engine: "InnoDB".to_string(),
version: 10,
row_format: "Dynamic".to_string(),
table_rows: 0,
avg_row_length: 0,
data_length: 16384,
max_data_length: 0,
index_length: 0,
data_free: 0,
auto_increment: None,
create_time: Some("2024-01-01 00:00:00".to_string()),
update_time: None,
check_time: None,
table_collation: "utf8mb4_0900_ai_ci".to_string(),
checksum: None,
create_options: String::new(),
table_comment: String::new(),
});
for (pos, col) in columns.iter().enumerate() {
let (data_type, column_type, char_max_len, numeric_precision, numeric_scale) =
self.sql_type_to_mysql_types(&col.sql_type);
self.columns.push(ColumnInfo {
table_catalog: "def".to_string(),
table_schema: "public".to_string(),
table_name: table_name.to_string(),
column_name: col.name.clone(),
ordinal_position: (pos + 1) as i64,
column_default: col.default.clone(),
is_nullable: if col.nullable { "YES" } else { "NO" }.to_string(),
data_type: data_type.to_string(),
character_maximum_length: char_max_len,
character_octet_length: char_max_len.map(|l| l * 4),
numeric_precision,
numeric_scale,
datetime_precision: if matches!(col.sql_type, SqlType::Timestamp | SqlType::Time) {
Some(6)
} else {
None
},
character_set_name: if matches!(col.sql_type, SqlType::Text | SqlType::Varchar(_) | SqlType::Char(_)) {
Some("utf8mb4".to_string())
} else {
None
},
collation_name: if matches!(col.sql_type, SqlType::Text | SqlType::Varchar(_) | SqlType::Char(_)) {
Some("utf8mb4_0900_ai_ci".to_string())
} else {
None
},
column_type: column_type.to_string(),
column_key: if col.primary_key { "PRI" } else if col.unique { "UNI" } else { "" }.to_string(),
extra: String::new(),
privileges: "select,insert,update,references".to_string(),
column_comment: String::new(),
generation_expression: String::new(),
srs_id: None,
});
}
}
fn sql_type_to_mysql_types(&self, sql_type: &SqlType) -> (&str, String, Option<i64>, Option<i64>, Option<i64>) {
match sql_type {
SqlType::Integer => ("int", "int".to_string(), None, Some(10), Some(0)),
SqlType::BigInt => ("bigint", "bigint".to_string(), None, Some(19), Some(0)),
SqlType::Float => ("float", "float".to_string(), None, Some(12), None),
SqlType::Double => ("double", "double".to_string(), None, Some(22), None),
SqlType::Decimal(p, s) => ("decimal", format!("decimal({},{})", p, s), None, Some(*p as i64), Some(*s as i64)),
SqlType::Boolean => ("tinyint", "tinyint(1)".to_string(), None, Some(1), Some(0)),
SqlType::Char(n) => ("char", format!("char({})", n), Some(*n as i64), None, None),
SqlType::Varchar(n) => ("varchar", format!("varchar({})", n), Some(*n as i64), None, None),
SqlType::Text => ("text", "text".to_string(), Some(65535), None, None),
SqlType::Date => ("date", "date".to_string(), None, None, None),
SqlType::Time => ("time", "time".to_string(), None, None, None),
SqlType::Timestamp => ("timestamp", "timestamp".to_string(), None, None, None),
SqlType::Uuid => ("varchar", "varchar(36)".to_string(), Some(36), None, None),
SqlType::Json => ("json", "json".to_string(), None, None, None),
}
}
pub fn query_tables(&self, _query: Option<&str>) -> QueryResult {
let columns = vec![
"TABLE_CATALOG".to_string(),
"TABLE_SCHEMA".to_string(),
"TABLE_NAME".to_string(),
"TABLE_TYPE".to_string(),
"ENGINE".to_string(),
"VERSION".to_string(),
"ROW_FORMAT".to_string(),
"TABLE_ROWS".to_string(),
"AVG_ROW_LENGTH".to_string(),
"DATA_LENGTH".to_string(),
"MAX_DATA_LENGTH".to_string(),
"INDEX_LENGTH".to_string(),
"DATA_FREE".to_string(),
"AUTO_INCREMENT".to_string(),
"CREATE_TIME".to_string(),
"UPDATE_TIME".to_string(),
"CHECK_TIME".to_string(),
"TABLE_COLLATION".to_string(),
"CHECKSUM".to_string(),
"CREATE_OPTIONS".to_string(),
"TABLE_COMMENT".to_string(),
];
let column_types = vec![
SqlType::Varchar(64),
SqlType::Varchar(64),
SqlType::Varchar(64),
SqlType::Varchar(64),
SqlType::Varchar(64),
SqlType::BigInt,
SqlType::Varchar(10),
SqlType::BigInt,
SqlType::BigInt,
SqlType::BigInt,
SqlType::BigInt,
SqlType::BigInt,
SqlType::BigInt,
SqlType::BigInt,
SqlType::Timestamp,
SqlType::Timestamp,
SqlType::Timestamp,
SqlType::Varchar(32),
SqlType::BigInt,
SqlType::Varchar(255),
SqlType::Varchar(2048),
];
let mut rows = Vec::new();
for table in &self.tables {
rows.push(vec![
Value::Text(table.table_catalog.clone()),
Value::Text(table.table_schema.clone()),
Value::Text(table.table_name.clone()),
Value::Text(table.table_type.clone()),
Value::Text(table.engine.clone()),
Value::Integer(table.version),
Value::Text(table.row_format.clone()),
Value::Integer(table.table_rows),
Value::Integer(table.avg_row_length),
Value::Integer(table.data_length),
Value::Integer(table.max_data_length),
Value::Integer(table.index_length),
Value::Integer(table.data_free),
table.auto_increment.map(Value::Integer).unwrap_or(Value::Null),
table.create_time.as_ref().map(|s| Value::Text(s.clone())).unwrap_or(Value::Null),
table.update_time.as_ref().map(|s| Value::Text(s.clone())).unwrap_or(Value::Null),
table.check_time.as_ref().map(|s| Value::Text(s.clone())).unwrap_or(Value::Null),
Value::Text(table.table_collation.clone()),
table.checksum.map(Value::Integer).unwrap_or(Value::Null),
Value::Text(table.create_options.clone()),
Value::Text(table.table_comment.clone()),
]);
}
QueryResult {
columns,
column_types,
rows,
}
}
pub fn query_columns(&self, _query: Option<&str>) -> QueryResult {
let columns = vec![
"TABLE_CATALOG".to_string(),
"TABLE_SCHEMA".to_string(),
"TABLE_NAME".to_string(),
"COLUMN_NAME".to_string(),
"ORDINAL_POSITION".to_string(),
"COLUMN_DEFAULT".to_string(),
"IS_NULLABLE".to_string(),
"DATA_TYPE".to_string(),
"CHARACTER_MAXIMUM_LENGTH".to_string(),
"CHARACTER_OCTET_LENGTH".to_string(),
"NUMERIC_PRECISION".to_string(),
"NUMERIC_SCALE".to_string(),
"DATETIME_PRECISION".to_string(),
"CHARACTER_SET_NAME".to_string(),
"COLLATION_NAME".to_string(),
"COLUMN_TYPE".to_string(),
"COLUMN_KEY".to_string(),
"EXTRA".to_string(),
"PRIVILEGES".to_string(),
"COLUMN_COMMENT".to_string(),
"GENERATION_EXPRESSION".to_string(),
"SRS_ID".to_string(),
];
let column_types = vec![
SqlType::Varchar(512),
SqlType::Varchar(64),
SqlType::Varchar(64),
SqlType::Varchar(64),
SqlType::BigInt,
SqlType::Text,
SqlType::Varchar(3),
SqlType::Varchar(64),
SqlType::BigInt,
SqlType::BigInt,
SqlType::BigInt,
SqlType::BigInt,
SqlType::BigInt,
SqlType::Varchar(32),
SqlType::Varchar(32),
SqlType::Text,
SqlType::Varchar(3),
SqlType::Varchar(30),
SqlType::Varchar(80),
SqlType::Varchar(1024),
SqlType::Varchar(512),
SqlType::Integer,
];
let mut rows = Vec::new();
for col in &self.columns {
rows.push(vec![
Value::Text(col.table_catalog.clone()),
Value::Text(col.table_schema.clone()),
Value::Text(col.table_name.clone()),
Value::Text(col.column_name.clone()),
Value::Integer(col.ordinal_position),
col.column_default.as_ref().map(|s| Value::Text(s.clone())).unwrap_or(Value::Null),
Value::Text(col.is_nullable.clone()),
Value::Text(col.data_type.clone()),
col.character_maximum_length.map(Value::Integer).unwrap_or(Value::Null),
col.character_octet_length.map(Value::Integer).unwrap_or(Value::Null),
col.numeric_precision.map(Value::Integer).unwrap_or(Value::Null),
col.numeric_scale.map(Value::Integer).unwrap_or(Value::Null),
col.datetime_precision.map(Value::Integer).unwrap_or(Value::Null),
col.character_set_name.as_ref().map(|s| Value::Text(s.clone())).unwrap_or(Value::Null),
col.collation_name.as_ref().map(|s| Value::Text(s.clone())).unwrap_or(Value::Null),
Value::Text(col.column_type.clone()),
Value::Text(col.column_key.clone()),
Value::Text(col.extra.clone()),
Value::Text(col.privileges.clone()),
Value::Text(col.column_comment.clone()),
Value::Text(col.generation_expression.clone()),
col.srs_id.map(Value::Integer).unwrap_or(Value::Null),
]);
}
QueryResult {
columns,
column_types,
rows,
}
}
pub fn query_schemata(&self, _query: Option<&str>) -> QueryResult {
let columns = vec![
"CATALOG_NAME".to_string(),
"SCHEMA_NAME".to_string(),
"DEFAULT_CHARACTER_SET_NAME".to_string(),
"DEFAULT_COLLATION_NAME".to_string(),
"SQL_PATH".to_string(),
"DEFAULT_ENCRYPTION".to_string(),
];
let column_types = vec![
SqlType::Varchar(512),
SqlType::Varchar(64),
SqlType::Varchar(32),
SqlType::Varchar(32),
SqlType::Varchar(512),
SqlType::Varchar(3),
];
let rows = vec![
vec![
Value::Text("def".to_string()),
Value::Text("public".to_string()),
Value::Text("utf8mb4".to_string()),
Value::Text("utf8mb4_0900_ai_ci".to_string()),
Value::Null,
Value::Text("NO".to_string()),
],
vec![
Value::Text("def".to_string()),
Value::Text("information_schema".to_string()),
Value::Text("utf8mb3".to_string()),
Value::Text("utf8mb3_general_ci".to_string()),
Value::Null,
Value::Text("NO".to_string()),
],
vec![
Value::Text("def".to_string()),
Value::Text("mysql".to_string()),
Value::Text("utf8mb4".to_string()),
Value::Text("utf8mb4_0900_ai_ci".to_string()),
Value::Null,
Value::Text("NO".to_string()),
],
vec![
Value::Text("def".to_string()),
Value::Text("performance_schema".to_string()),
Value::Text("utf8mb4".to_string()),
Value::Text("utf8mb4_0900_ai_ci".to_string()),
Value::Null,
Value::Text("NO".to_string()),
],
];
QueryResult {
columns,
column_types,
rows,
}
}
pub fn query_key_column_usage(&self, _query: Option<&str>) -> QueryResult {
let columns = vec![
"CONSTRAINT_CATALOG".to_string(),
"CONSTRAINT_SCHEMA".to_string(),
"CONSTRAINT_NAME".to_string(),
"TABLE_CATALOG".to_string(),
"TABLE_SCHEMA".to_string(),
"TABLE_NAME".to_string(),
"COLUMN_NAME".to_string(),
"ORDINAL_POSITION".to_string(),
"POSITION_IN_UNIQUE_CONSTRAINT".to_string(),
"REFERENCED_TABLE_SCHEMA".to_string(),
"REFERENCED_TABLE_NAME".to_string(),
"REFERENCED_COLUMN_NAME".to_string(),
];
let column_types = vec![
SqlType::Varchar(512),
SqlType::Varchar(64),
SqlType::Varchar(64),
SqlType::Varchar(512),
SqlType::Varchar(64),
SqlType::Varchar(64),
SqlType::Varchar(64),
SqlType::Integer,
SqlType::Integer,
SqlType::Varchar(64),
SqlType::Varchar(64),
SqlType::Varchar(64),
];
let mut rows = Vec::new();
for fk in &self.foreign_keys {
rows.push(vec![
Value::Text("def".to_string()),
Value::Text("public".to_string()),
Value::Text(format!("fk_{}_{}", fk.table_name, fk.column_name)),
Value::Text("def".to_string()),
Value::Text("public".to_string()),
Value::Text(fk.table_name.clone()),
Value::Text(fk.column_name.clone()),
Value::Integer(1),
Value::Integer(1),
Value::Text("public".to_string()),
Value::Text(fk.referenced_table.clone()),
Value::Text(fk.referenced_column.clone()),
]);
}
QueryResult {
columns,
column_types,
rows,
}
}
}