br_db/types/

sqlite.rs

use crate::pools;
use crate::types::{add_table_prefix, validate_table_name, DbMode, Mode, Params, TableOptions};
use crate::Connection;
use chrono::Local;
use json::{array, object, JsonValue};
use lazy_static::lazy_static;
use log::{error, info};
use sqlite::{Connection as Connect, ConnectionThreadSafe, OpenFlags, State, Statement, Type};
use std::cell::Cell;
use std::collections::{HashMap, HashSet};
use std::sync::{Arc, Mutex};
use std::thread;

thread_local! {
    static THREAD_ID_CACHE: Cell<Option<String>> = const { Cell::new(None) };
}

fn get_thread_id() -> String {
    THREAD_ID_CACHE.with(|cache| {
        if let Some(id) = cache.take() {
            cache.set(Some(id.clone()));
            id
        } else {
            let id = format!("{:?}", thread::current().id());
            cache.set(Some(id.clone()));
            id
        }
    })
}

lazy_static! {
    static ref DBS: Mutex<HashMap<String, Arc<ConnectionThreadSafe>>> = Mutex::new(HashMap::new());
    static ref TR: Mutex<HashMap<String, Arc<ConnectionThreadSafe>>> = Mutex::new(HashMap::new());
    static ref TR_COUNT: Mutex<HashMap<String, u32>> = Mutex::new(HashMap::new());
    static ref SQL_LIST: Mutex<Vec<String>> = Mutex::new(Vec::new());
    static ref FIELDS: Mutex<HashMap<String, JsonValue>> = Mutex::new(HashMap::new());
}

#[derive(Clone, Debug)]
pub struct Sqlite {
    /// 当前连接配置
    pub connection: Connection,
    /// 当前选中配置
    pub default: String,

    pub params: Params,
}

impl Sqlite {
    pub fn connect(connection: Connection, default: String) -> Result<Self, String> {
        let flags = OpenFlags::new().with_create().with_read_write();
        match Connect::open_thread_safe_with_flags(connection.clone().get_dsn().as_str(), flags) {
            Ok(e) => {
                DBS.lock().unwrap().insert(default.clone(), Arc::new(e));
                Ok(Self {
                    connection: connection.clone(),
                    default: default.clone(),
                    params: Params::default("sqlite"),
                })
            }
            Err(e) => {
                error!(
                    "sqlite 启动失败: {} {}",
                    e,
                    connection.clone().get_dsn().as_str()
                );
                Err(e.to_string())
            }
        }
    }
    fn query_handle(&mut self, mut statement: Statement, sql: String) -> (bool, JsonValue) {
        let thread_id = get_thread_id();

        let mut data = array![];
        while let State::Row = match statement.next() {
            Ok(e) => e,
            Err(e) => {
                let transaction = *TR_COUNT
                    .lock()
                    .unwrap()
                    .get(&*thread_id.clone())
                    .unwrap_or(&0);
                if transaction > 0 {
                    error!("{} 查询事务: {} {}", thread_id, e, sql.clone());
                } else {
                    error!("{} 非事务查询: {} {}", thread_id, e, sql.clone());
                }
                return (false, data);
            }
        } {
            let mut list = object! {};
            let mut index = 0;
            for field in statement.column_names().iter() {
                if !list[field.as_str()].is_null() {
                    index += 1;
                    continue;
                }
                match statement.column_type(field.as_str()) {
                    Ok(types) => match types {
                        Type::String => {
                            let data = statement.read::<String, _>(index).unwrap();
                            match data.as_str() {
                                "false" => {
                                    list[field.as_str()] = JsonValue::from(false);
                                }
                                "true" => {
                                    list[field.as_str()] = JsonValue::from(true);
                                }
                                _ => {
                                    list[field.as_str()] = JsonValue::from(data.clone());
                                }
                            }
                        }
                        Type::Integer => {
                            // 优化：减少锁持有时间，先获取字段信息再读取数据
                            let field_info = FIELDS
                                .lock()
                                .map(|f| f.get(&self.params.table).cloned())
                                .unwrap_or(None);
                            if let Some(fields) = field_info {
                                if fields[field.clone()].is_empty() {
                                    let data = statement.read::<i64, _>(index).unwrap();
                                    list[field.as_str()] = data.into();
                                    continue;
                                }
                                match fields[field.clone()]["type"].as_str().unwrap() {
                                    "INTEGER" => {
                                        let data = statement.read::<i64, _>(index).unwrap();
                                        list[field.as_str()] = JsonValue::from(data == 1);
                                    }
                                    x if x.contains("int(") => {
                                        let data = statement.read::<i64, _>(index).unwrap();
                                        list[field.as_str()] = data.into();
                                    }
                                    x if x.contains("decimal(") && x.ends_with(",0)") => {
                                        let data = statement.read::<f64, _>(index).unwrap();
                                        list[field.as_str()] = data.into();
                                    }
                                    _ => {
                                        let data = statement.read::<i64, _>(index).unwrap();
                                        list[field.as_str()] = data.into();
                                    }
                                }
                            }
                        }
                        Type::Float => {
                            let data = statement.read::<f64, _>(index).unwrap();
                            list[field.as_str()] = JsonValue::from(data);
                        }
                        Type::Binary => {
                            let data = statement.read::<String, _>(index).unwrap();
                            list[field.as_str()] = JsonValue::from(data.clone());
                        }
                        Type::Null => match statement.read::<String, _>(index) {
                            Ok(data) => {
                                list[field.as_str()] = JsonValue::from(data.clone());
                            }
                            Err(_) => match statement.read::<f64, _>(index) {
                                Ok(data) => {
                                    if data == 0.0 {
                                        list[field.as_str()] = JsonValue::from("");
                                    } else {
                                        list[field.as_str()] = JsonValue::from(data);
                                    }
                                }
                                Err(_) => match statement.read::<i64, _>(index) {
                                    Ok(data) => {
                                        if data == 0 {
                                            list[field.as_str()] = JsonValue::from("");
                                        } else {
                                            list[field.as_str()] = JsonValue::from(data);
                                        }
                                    }
                                    Err(e) => {
                                        error!("Type:{} {:?}", field.as_str(), e);
                                    }
                                },
                            },
                        },
                    },
                    Err(e) => {
                        error!("query Err: {e:?}");
                    }
                }
                index += 1;
            }
            data.push(list).unwrap();
        }
        (true, data)
    }
    pub fn query(&mut self, sql: String) -> (bool, JsonValue) {
        let thread_id = get_thread_id();

        let transaction = *TR_COUNT
            .lock()
            .unwrap()
            .get(&*thread_id.clone())
            .unwrap_or(&0);
        if transaction > 0 {
            // 查询事务（已移除调试日志）

            let dbs = match TR.lock() {
                Ok(dbs) => dbs,
                Err(e) => {
                    error!("{thread_id} 获取数据库锁失败: {e}\r\nSQL: {sql}");
                    return (false, JsonValue::from("数据库锁定失败"));
                }
            };
            let db = dbs.get(&*thread_id.clone()).unwrap().clone();
            let x = match db.prepare(sql.clone()) {
                Ok(statement) => self.query_handle(statement, sql.clone()),
                Err(e) => {
                    error!("{} 查询事务: Err: {} {}", thread_id, e, sql.clone());
                    (false, e.to_string().into())
                }
            };
            x
        } else {
            // 非事务查询（已移除调试日志）
            let dbs = match DBS.lock() {
                Ok(dbs) => dbs,
                Err(e) => {
                    error!("{thread_id} 获取数据库锁失败: {e}\r\nSQL: {sql}");
                    return (false, JsonValue::from("数据库锁定失败"));
                }
            };
            let db = dbs.get(&*self.default).unwrap().clone();
            let x = match db.prepare(sql.clone()) {
                Ok(statement) => self.query_handle(statement, sql.clone()),
                Err(e) => {
                    error!("{thread_id} 查询非事务: Err: {e}");
                    (false, e.to_string().into())
                }
            };
            x
        }
    }

    pub fn execute(&mut self, sql: String) -> (bool, JsonValue) {
        let thread_id = get_thread_id();

        let transaction = *TR_COUNT
            .lock()
            .unwrap()
            .get(&*thread_id.clone())
            .unwrap_or(&0);
        if transaction > 0 {
            // 执行事务（已移除调试日志）

            let dbs = match TR.lock() {
                Ok(dbs) => dbs,
                Err(e) => {
                    error!("{thread_id} 获取数据库锁失败: {e}\r\nSQL: {sql}");
                    return (false, JsonValue::from("数据库锁定失败"));
                }
            };
            let db = match dbs.get(&*thread_id) {
                Some(db) => db.clone(),
                None => {
                    error!(
                        "{} 未找到默认数据库配置: {}\r\nSQL: {}",
                        thread_id, self.default, sql
                    );
                    return (false, JsonValue::from("未找到默认数据库配置"));
                }
            };
            match db.execute(sql.clone()) {
                Ok(_) => {
                    let count = db.change_count();
                    if self.connection.debug {
                        info!(
                            "{} count:{} total_count:{}",
                            thread_id,
                            count,
                            db.total_change_count()
                        );
                    }
                    (true, JsonValue::from(count))
                }
                Err(e) => {
                    error!("{} 执行事务: \r\nErr: {}\r\n{}", thread_id, e, sql.clone());
                    (false, JsonValue::from(e.to_string()))
                }
            }
        } else {
            if self.connection.debug {
                info!("{} 执行非事务: \r\nsql: {}", thread_id, sql.clone());
            }
            let dbs = match DBS.lock() {
                Ok(dbs) => dbs,
                Err(e) => {
                    error!("{thread_id} 获取数据库锁失败: {e}\r\nSQL: {sql}");
                    return (false, JsonValue::from("数据库锁定失败"));
                }
            };

            let db = match dbs.get(&*self.default) {
                Some(db) => db.clone(),
                None => {
                    error!(
                        "{} 未找到默认数据库配置: {}\r\nSQL: {}",
                        thread_id, self.default, sql
                    );
                    return (false, JsonValue::from("未找到默认数据库配置"));
                }
            };
            match db.execute(sql.clone()) {
                Ok(_) => {
                    let count = db.change_count();
                    if self.connection.debug {
                        info!(
                            "{} count: {} total_count: {}",
                            thread_id,
                            count,
                            db.total_change_count()
                        );
                    }
                    (true, JsonValue::from(count))
                }
                Err(e) => {
                    // 检查是否是 "already exists" 错误，如果是则静默处理（与 MySQL 标准一致）
                    let error_msg = e.to_string().to_lowercase();
                    if error_msg.contains("already exists") {
                        // 索引已存在，静默跳过，不输出错误日志
                        (true, JsonValue::from(0))
                    } else {
                        error!(
                            "{} 执行非事务: Err: {}\r\nSQL: {}",
                            thread_id,
                            e,
                            sql.clone()
                        );
                        (false, JsonValue::from(e.to_string()))
                    }
                }
            }
        }
    }

    /// 执行 DDL 操作（强制使用非事务连接）
    /// DDL 操作（如 CREATE TABLE, ALTER TABLE）应该使用独立的连接，
    /// 避免与事务冲突，特别是在多线程环境下
    fn execute_ddl(&mut self, sql: String) -> (bool, JsonValue) {
        let thread_id = get_thread_id();

        // 强制使用非事务连接执行 DDL
        // 即使当前线程在事务中，DDL 操作也应该使用独立的连接
        if self.connection.debug {
            info!("{} 执行 DDL（非事务）: \r\nsql: {}", thread_id, sql.clone());
        }

        let dbs = match DBS.lock() {
            Ok(dbs) => dbs,
            Err(e) => {
                error!("{thread_id} 获取数据库锁失败: {e}\r\nSQL: {sql}");
                return (false, JsonValue::from("数据库锁定失败"));
            }
        };

        let db = match dbs.get(&*self.default) {
            Some(db) => db.clone(),
            None => {
                error!(
                    "{} 未找到默认数据库配置: {}\r\nSQL: {}",
                    thread_id, self.default, sql
                );
                return (false, JsonValue::from("未找到默认数据库配置"));
            }
        };

        match db.execute(sql.clone()) {
            Ok(_) => {
                let count = db.change_count();
                if self.connection.debug {
                    info!(
                        "{} DDL 执行成功: count: {} total_count: {}",
                        thread_id,
                        count,
                        db.total_change_count()
                    );
                }
                (true, JsonValue::from(count))
            }
            Err(e) => {
                // 检查是否是 "already exists" 错误，如果是则静默处理
                let error_msg = e.to_string().to_lowercase();
                if error_msg.contains("already exists") {
                    // 表或索引已存在，静默跳过，不输出错误日志
                    (true, JsonValue::from(0))
                } else {
                    error!(
                        "{} 执行 DDL 失败: Err: {}\r\nSQL: {}",
                        thread_id,
                        e,
                        sql.clone()
                    );
                    (false, JsonValue::from(e.to_string()))
                }
            }
        }
    }
}

impl DbMode for Sqlite {
    fn database_tables(&mut self) -> JsonValue {
        let sql = "select name from sqlite_master where type='table' order by name;".to_string();
        match self.sql(sql.as_str()) {
            Ok(e) => {
                let mut list = vec![];
                for item in e.members() {
                    list.push(item["name"].clone());
                }
                list.into()
            }
            Err(_) => {
                array![]
            }
        }
    }
    fn database_create(&mut self, _name: &str) -> bool {
        // SQLite 不需要显式创建数据库，数据库文件在连接时自动创建
        // 此方法保留以保持接口一致性，但 SQLite 中数据库即文件，无需单独创建
        true
    }
}

impl Sqlite {
    /// 验证索引字段是否存在于新表中
    fn validate_index_fields_in_table(index_fields: &str, table_fields: &[String]) -> bool {
        // 从 "`field1`,`field2`" 格式中提取字段名
        let fields: Vec<String> = index_fields
            .split(',')
            .map(|f| f.trim_matches('`').trim().to_string())
            .collect();

        for field in fields {
            if !table_fields.contains(&field) {
                return false;
            }
        }
        true
    }

    /// 检查索引是否可以安全删除
    /// 返回 true 表示可以删除，false 表示是约束索引不能删除
    fn can_drop_index(index_name: &str) -> bool {
        // SQLite 自动创建的约束索引不能删除
        !index_name.starts_with("sqlite_autoindex_")
    }
}

impl Mode for Sqlite {
    fn table_create(&mut self, options: TableOptions) -> JsonValue {
        let thread_id = get_thread_id();

        // 使用公共函数验证表名
        let table_name = match validate_table_name(&options.table_name) {
            Ok(n) => n,
            Err(e) => {
                error!("表名验证失败: {}", e);
                return JsonValue::from(false);
            }
        };

        // 验证字段列表不为空
        if options.table_fields.is_empty() {
            error!("{} 表 {} 字段列表为空，无法创建表", thread_id, table_name);
            return JsonValue::from(false);
        }

        // 预分配容量，估算每个字段约 100 字符
        let estimated_capacity = options.table_fields.len() * 100;
        let mut sql = String::with_capacity(estimated_capacity);
        // 唯一约束
        let mut unique_fields = String::new();
        let mut unique_name = String::new();
        let mut unique = String::new();
        for item in options.table_unique.iter() {
            if unique_fields.is_empty() {
                unique_fields = format!("`{item}`");
                unique_name = format!("{}_unique_{}", table_name, item);
            } else {
                unique_fields = format!("{unique_fields},`{item}`");
                unique_name = format!("{unique_name}_{item}");
            }
        }
        // 使用 MD5 生成索引名称，与 MySQL 保持一致
        if !unique_name.is_empty() {
            let md5 = br_crypto::md5::encrypt_hex(unique_name.as_bytes());
            let index_name = format!("unique_{}", md5);
            unique = format!(
                "CREATE UNIQUE INDEX `{}` on {} ({});\r\n",
                index_name, table_name, unique_fields
            );
        }

        // 收集所有字段名称，用于验证索引字段和主键字段
        let fields_list: Vec<String> = options
            .table_fields
            .entries()
            .map(|(name, _)| name.to_string())
            .collect();

        // 验证主键字段是否存在于字段列表中
        if !fields_list.contains(&options.table_key) {
            error!(
                "{} 表 {} 主键字段验证失败: 主键字段 '{}' 不在字段列表中",
                thread_id, table_name, options.table_key
            );
            return JsonValue::from(false);
        }

        // 验证唯一索引字段是否存在于字段列表中
        if !unique_fields.is_empty()
            && !Self::validate_index_fields_in_table(&unique_fields, &fields_list)
        {
            error!(
                "{} 表 {} 唯一索引字段验证失败: 字段 {} 不在字段列表中",
                thread_id, table_name, unique_fields
            );
            return JsonValue::from(false);
        }

        // 唯一索引
        let estimated_indexes = options.table_index.len();
        let mut index = Vec::with_capacity(estimated_indexes);
        let mut index_names = Vec::with_capacity(estimated_indexes); // 保存索引名称
        for row in options.table_index.iter() {
            let mut index_fields = String::new();
            let mut index_name = String::new();
            for item in row.iter() {
                if index_fields.is_empty() {
                    index_fields = format!("`{item}`");
                    index_name = format!("index_{item}");
                } else {
                    index_fields = format!("{index_fields},`{item}`");
                    index_name = format!("{index_name}_{item}");
                }
            }
            // 验证索引字段是否存在于字段列表中
            if !Self::validate_index_fields_in_table(&index_fields, &fields_list) {
                error!(
                    "{} 表 {} 索引字段验证失败: 字段 {} 不在字段列表中，跳过创建索引 {}",
                    thread_id, table_name, index_fields, index_name
                );
                continue; // 跳过这个索引，继续处理其他索引
            }
            // 完整索引名称格式：{table_name}_{index_name}
            let full_index_name = format!("{}_{}", table_name, index_name);
            index_names.push(full_index_name.clone()); // 保存完整索引名称
            index.push(format!(
                "CREATE INDEX {} on {} ({});\r\n",
                full_index_name, table_name, index_fields
            ));
        }

        for (name, field) in options.table_fields.entries() {
            let row = br_fields::field("sqlite", name, field.clone());
            // 验证字段 SQL 生成结果不为空
            if row.is_empty() {
                error!(
                    "{} 表 {} 字段 {} 的 SQL 生成结果为空",
                    thread_id, table_name, name
                );
                return JsonValue::from(false);
            }
            // 如果字段名是保留关键字，需要用反引号包裹
            let escaped_row = if crate::types::DISABLE_FIELD.contains(&name) {
                // 字段名在保留关键字列表中，需要在字段名周围添加反引号
                // row 格式通常是 "field_name TYPE ..."，需要替换开头的字段名
                if let Some(space_pos) = row.find(' ') {
                    format!("`{}`{}", &row[..space_pos], &row[space_pos..])
                } else {
                    format!("`{}`", row)
                }
            } else {
                row
            };
            sql = format!("{sql} {escaped_row},\r\n");
        }

        sql = sql.trim_end_matches(",\r\n").to_string();

        // 添加主键定义（与 MySQL 保持一致）
        // SQLite 支持在 CREATE TABLE 中定义 PRIMARY KEY
        sql = if sql.trim_end().ends_with(",") {
            format!("{}\r\nPRIMARY KEY(`{}`)", sql, options.table_key)
        } else {
            format!("{},\r\nPRIMARY KEY(`{}`)", sql, options.table_key)
        };

        let sql = format!(
            "CREATE TABLE IF NOT EXISTS `{}` (\r\n{}\r\n);\r\n",
            table_name, sql
        );
        if self.params.sql {
            let mut list = vec![sql];
            if !unique.is_empty() {
                list.push(unique)
            }
            if !index.is_empty() {
                list.extend(index)
            }

            return JsonValue::from(list.join(""));
        }

        // DDL 操作（CREATE TABLE）应该强制使用非事务连接，避免事务锁冲突
        // SQLite 的 DDL 操作在事务中执行可能会导致锁冲突，特别是多线程环境下
        let (state, error_info) = self.execute_ddl(sql.clone());
        if state {
            if !unique.is_empty() {
                // 在创建索引前，先检查索引是否已存在（优化：使用 HashSet 提高查找性能）
                let (_, index_list) =
                    self.query(format!("PRAGMA index_list(`{}`);\r\n", table_name));
                let mut existing_unique_indexes = HashSet::with_capacity(index_list.len());
                for item in index_list.members() {
                    if item["unique"].as_usize().unwrap_or(0) == 1 {
                        if let Some(name) = item["name"].as_str() {
                            existing_unique_indexes.insert(name.to_string());
                        }
                    }
                }

                // 从 unique SQL 中提取索引名称
                let index_name = if unique.contains("`") {
                    let start = unique.find("`").unwrap() + 1;
                    let end = unique[start..].find("`").unwrap() + start;
                    unique[start..end].to_string()
                } else {
                    String::new()
                };

                // 如果索引不存在，才创建（HashSet 查找 O(1)）
                if !index_name.is_empty() && !existing_unique_indexes.contains(index_name.as_str())
                {
                    // 如果存在旧的唯一索引但名称不同，先删除
                    let old_indexes: Vec<String> =
                        existing_unique_indexes.iter().cloned().collect();
                    for old_index in old_indexes {
                        if old_index != index_name {
                            // 跳过 SQLite 自动创建的约束索引
                            if Self::can_drop_index(&old_index) {
                                let drop_sql = format!("DROP INDEX IF EXISTS `{}`;", old_index);
                                let (state, result) = self.execute_ddl(drop_sql);
                                if !state {
                                    error!("删除旧索引失败: {} 错误: {}", old_index, result);
                                }
                            } else {
                                // 跳过删除 SQLite 自动索引（不输出日志）
                            }
                        }
                    }
                    let (state, result) = self.execute_ddl(unique.clone());
                    if !state {
                        // 检查是否是 "already exists" 错误，如果是则静默跳过
                        let error_msg = result.to_string().to_lowercase();
                        if error_msg.contains("already exists") {
                            // 索引已存在，静默跳过（与 MySQL 标准一致）
                        } else {
                            error!(
                                "{} 表 {} 唯一索引创建失败: {} SQL: {}",
                                thread_id, table_name, result, unique
                            );
                        }
                    }
                } else if !index_name.is_empty() {
                    // 唯一索引已存在，跳过创建（不输出日志）
                }
            }
            if !index.is_empty() {
                // 查询现有索引列表（优化：使用 HashSet 提高查找性能）
                let (_, index_list) =
                    self.query(format!("PRAGMA index_list(`{}`);\r\n", table_name));
                let mut existing_indexes = HashSet::with_capacity(index_list.len());
                for item in index_list.members() {
                    let is_unique = item["unique"].as_usize().unwrap_or(0) == 1;
                    if !is_unique {
                        if let Some(name) = item["name"].as_str() {
                            existing_indexes.insert(name.to_string());
                        }
                    }
                }

                // 创建普通索引
                for (index_sql, index_name) in index.iter().zip(index_names.iter()) {
                    // 检查索引是否已存在（HashSet 查找 O(1)）
                    if !existing_indexes.contains(index_name.as_str()) {
                        let (state, result) = self.execute_ddl(index_sql.clone());
                        if state {
                            // 索引创建成功（不输出日志）
                        } else {
                            error!(
                                "{} {} 索引创建失败: {} 错误: {}",
                                thread_id, table_name, index_name, result
                            );
                        }
                    } else {
                        // 索引已存在，跳过创建（不输出日志）
                    }
                }
            }
            JsonValue::from(true)
        } else {
            error!(
                "{} 表 {} 创建失败: SQL: {} 错误: {}",
                thread_id, table_name, sql, error_info
            );
            JsonValue::from(false)
        }
    }
    fn table_update(&mut self, options: TableOptions) -> JsonValue {
        let thread_id = get_thread_id();

        // 预分配容量，减少重新分配
        let estimated_fields = options.table_fields.len();
        let mut sql = String::with_capacity(estimated_fields * 100);
        let mut add = Vec::with_capacity(estimated_fields);
        let mut del = Vec::with_capacity(estimated_fields);
        let mut put = Vec::with_capacity(estimated_fields);

        // 使用公共函数验证表名
        let table_name = match validate_table_name(&options.table_name) {
            Ok(n) => n,
            Err(e) => {
                error!("表名验证失败: {}", e);
                return JsonValue::from(0);
            }
        };
        let (_, mut fields_list) = self.query(format!("PRAGMA table_info(`{}`)", table_name));
        let mut field_old = object! {};
        for item in fields_list.members_mut() {
            item["dflt_value"] = item["dflt_value"]
                .to_string()
                .trim_start_matches("'")
                .trim_end_matches("'")
                .into();
            let name = match item["name"].as_str() {
                Some(n) => n,
                None => {
                    error!("表 {} 字段信息中缺少 name 字段", options.table_name);
                    continue;
                }
            };
            field_old[name] = item.clone();
            if options.table_fields[name].is_empty() {
                del.push(name);
            }
        }

        let estimated_fields = options.table_fields.len();
        let mut fields_list = Vec::with_capacity(estimated_fields);
        let mut fields_list_new = Vec::with_capacity(estimated_fields);

        for (name, field) in options.table_fields.entries() {
            fields_list_new.push(name.to_string());
            if field_old[name].is_empty() {
                add.push(name);
            } else {
                fields_list.push(name);
                let field_mode = field["mode"].as_str().unwrap_or("");
                let old_value = match field_mode {
                    "select" => {
                        if field_old[name]["dflt_value"].clone().is_empty() {
                            "".to_string()
                        } else {
                            field_old[name]["dflt_value"].clone().to_string()
                        }
                    }
                    "switch" => match field_old[name]["dflt_value"].to_string().parse::<i32>() {
                        Ok(val) => (val == 1).to_string(),
                        Err(_) => {
                            error!(
                                "表 {} 字段 {} 的默认值解析失败，跳过更新",
                                options.table_name, name
                            );
                            continue;
                        }
                    },
                    _ => field_old[name]["dflt_value"].clone().to_string(),
                };
                let new_value = match field["mode"].as_str().unwrap() {
                    "select" => field["def"]
                        .members()
                        .map(|x| x.to_string())
                        .collect::<Vec<String>>()
                        .join(","),
                    _ => field["def"].clone().to_string(),
                };
                if old_value != new_value {
                    info!(
                        "{} 差异化当前: {} old_value: {} new_value: {} {}",
                        options.table_name,
                        name,
                        old_value,
                        new_value,
                        old_value != new_value
                    );
                    info!("差异化更新: {} {:#} {:#}", name, field_old[name], field);
                    put.push(name);
                } else if field_old[name]["pk"].as_i64().unwrap() == 1 && name != options.table_key
                {
                    info!("{} 主键替换: {}", options.table_name, name);
                    put.push(name);
                }
            }
        }

        // 如果字段没有变化，提前检查索引是否也无变化
        if add.is_empty() && del.is_empty() && put.is_empty() {
            // 查询现有索引
            let (_, index_list_result) =
                self.query(format!("PRAGMA index_list({});\r\n", options.table_name));

            let mut existing_unique_indexes: Vec<String> = vec![];
            let mut existing_indexes: Vec<String> = vec![];

            for item in index_list_result.members() {
                if let Some(index_name) = item["name"].as_str() {
                    let is_unique = item["unique"].as_usize().unwrap_or(0) == 1;
                    if is_unique {
                        existing_unique_indexes.push(index_name.to_string());
                    } else {
                        existing_indexes.push(index_name.to_string());
                    }
                }
            }

            // 检查唯一索引是否有变化
            let mut has_unique_change = false;
            if !options.table_unique.is_empty() {
                let mut unique_name = String::new();
                for item in options.table_unique.iter() {
                    if unique_name.is_empty() {
                        unique_name = format!("{}_unique_{}", options.table_name, item);
                    } else {
                        unique_name = format!("{}_{}", unique_name, item);
                    }
                }
                let md5 = br_crypto::md5::encrypt_hex(unique_name.as_bytes());
                let new_unique_name = format!("unique_{}", md5);
                has_unique_change = !existing_unique_indexes.contains(&new_unique_name);
            }

            // 检查普通索引是否有变化
            let mut has_index_change = false;
            for row in options.table_index.iter() {
                let mut index_name = String::new();
                for item in row.iter() {
                    if index_name.is_empty() {
                        index_name = format!("index_{}", item);
                    } else {
                        index_name = format!("{}_{}", index_name, item);
                    }
                }
                let full_index_name = format!("{}_{}", options.table_name, index_name);
                if !existing_indexes.contains(&full_index_name) {
                    has_index_change = true;
                    break;
                }
            }

            // 如果字段和索引都没有变化，返回 -1
            if !has_unique_change && !has_index_change {
                return JsonValue::from(-1);
            }
        }

        let mut unique_fields = String::new();
        let mut unique_name = String::new();
        let mut unique = String::new();

        // 唯一索引
        for item in options.table_unique.iter() {
            if unique_fields.is_empty() {
                unique_fields = format!("`{item}`");
                unique_name = format!("{}_unique_{}", options.table_name, item);
            } else {
                unique_fields = format!("{unique_fields},`{item}`");
                unique_name = format!("{unique_name}_{item}");
            }
        }
        // 使用 MD5 生成索引名称，与 MySQL 保持一致
        if !unique_name.is_empty() {
            let md5 = br_crypto::md5::encrypt_hex(unique_name.as_bytes());
            let index_name = format!("unique_{}", md5);
            unique = format!(
                "CREATE UNIQUE INDEX `{}` on {} ({});\r\n",
                index_name, options.table_name, unique_fields
            );
        }

        // 索引（优化：预分配容量）
        let estimated_indexes = options.table_index.len();
        let mut index = Vec::with_capacity(estimated_indexes);
        for row in options.table_index.iter() {
            let mut index_fields = String::new();
            let mut index_name = String::new();
            for item in row.iter() {
                if index_fields.is_empty() {
                    index_fields = format!("`{item}`");
                    index_name = format!("index_{item}");
                } else {
                    index_fields = format!("{index_fields},`{item}`");
                    index_name = format!("{index_name}_{item}");
                }
            }
            // 验证索引字段是否存在于新表中
            if !Self::validate_index_fields_in_table(&index_fields, &fields_list_new) {
                error!(
                    "{} 索引字段验证失败: 字段 {} 不在新表字段列表中，跳过创建索引 {}",
                    options.table_name, index_fields, index_name
                );
            } else {
                index.push(format!(
                    "create index {}_{} on {} ({});\r\n",
                    options.table_name, index_name, options.table_name, index_fields
                ));
            }
        }
        for (name, field) in options.table_fields.entries() {
            let row = br_fields::field("sqlite", name, field.clone());
            // 如果字段名是保留关键字，需要用反引号包裹
            let escaped_row = if crate::types::DISABLE_FIELD.contains(&name) {
                // 字段名在保留关键字列表中，需要在字段名周围添加反引号
                // row 格式通常是 "field_name TYPE ..."，需要替换开头的字段名
                if let Some(space_pos) = row.find(' ') {
                    format!("`{}`{}", &row[..space_pos], &row[space_pos..])
                } else {
                    format!("`{}`", row)
                }
            } else {
                row
            };
            sql = format!("{sql} {escaped_row},\r\n");
        }

        if !unique.is_empty() || !index.is_empty() {
            let (_, index_list_result) =
                self.query(format!("PRAGMA index_list({});\r\n", options.table_name));
            // 优化：使用 HashSet 提高查找性能
            let mut existing_unique_indexes = HashSet::with_capacity(index_list_result.len());
            let mut existing_indexes = HashSet::with_capacity(index_list_result.len());

            for item in index_list_result.members() {
                if let Some(index_name) = item["name"].as_str() {
                    let is_unique = item["unique"].as_usize().unwrap_or(0) == 1;
                    if is_unique {
                        existing_unique_indexes.insert(index_name.to_string());
                    } else {
                        existing_indexes.insert(index_name.to_string());
                    }
                }
            }

            // 处理唯一索引
            if !unique_name.is_empty() {
                // 再次验证唯一索引字段是否存在于新表中
                if !Self::validate_index_fields_in_table(&unique_fields, &fields_list_new) {
                    error!(
                        "{} 唯一索引字段验证失败: 字段 {} 不在新表字段列表中，跳过创建唯一索引",
                        options.table_name, unique_fields
                    );
                    unique = String::new();
                } else {
                    let md5 = br_crypto::md5::encrypt_hex(unique_name.as_bytes());
                    let new_unique_index_name = format!("unique_{}", md5);

                    // 检查是否需要创建（HashSet 查找 O(1)）
                    if !existing_unique_indexes.contains(new_unique_index_name.as_str()) {
                        // 删除旧的唯一索引（如果存在且名称不同）
                        let old_indexes: Vec<String> =
                            existing_unique_indexes.iter().cloned().collect();
                        for old_index in old_indexes {
                            if old_index != new_unique_index_name {
                                // 跳过 SQLite 自动创建的约束索引
                                if Self::can_drop_index(&old_index) {
                                    let drop_sql = format!("DROP INDEX IF EXISTS `{}`;", old_index);
                                    let (state, result) = self.execute(drop_sql);
                                    if !state {
                                        error!("删除旧索引失败: {} 错误: {}", old_index, result);
                                    }
                                } else {
                                    // 跳过删除 SQLite 自动索引（不输出日志）
                                }
                            }
                        }
                        // 更新 unique SQL 使用新的索引名称
                        unique = format!(
                            "CREATE UNIQUE INDEX `{}` on {} ({});\r\n",
                            new_unique_index_name, options.table_name, unique_fields
                        );
                    } else {
                        // 索引已存在，不需要创建
                        unique = String::new();
                    }
                }
            }

            // 处理普通索引（优化：使用 HashSet 提高查找性能）
            let mut index_old_set = HashSet::with_capacity(index_list_result.len());
            let mut index_new_set = HashSet::with_capacity(index.len());

            // 收集现有索引名称到 HashSet
            for item in index_list_result.members() {
                let origin = item["origin"].as_str().unwrap_or("");
                let is_unique = item["unique"].as_usize().unwrap_or(0) == 1;
                let name = item["name"].as_str().unwrap_or("");

                if origin == "c" && !is_unique {
                    index_old_set.insert(name.to_string());
                }
            }

            // 收集新索引名称到 HashSet（从 SQL 中提取）
            for index_sql in index.iter() {
                // 从 SQL 中提取索引名称：CREATE INDEX index_name ON ...
                if let Some(start) = index_sql.find("CREATE INDEX") {
                    if let Some(end) = index_sql[start..].find(" ON ") {
                        let index_name = index_sql[start + 12..start + end].trim();
                        index_new_set.insert(index_name.to_string());
                    }
                } else if let Some(start) = index_sql.find("create index") {
                    if let Some(end) = index_sql[start..].find(" on ") {
                        let index_name = index_sql[start + 12..start + end].trim();
                        index_new_set.insert(index_name.to_string());
                    }
                }
            }

            // 如果所有新索引都已存在，清空索引列表
            if unique.is_empty() && index_old_set.len() == index_new_set.len() {
                // 检查是否所有新索引都在旧索引集合中
                let all_exist = index_new_set
                    .iter()
                    .all(|name| index_old_set.contains(name));
                if all_exist {
                    index = vec![];
                }
            }
        }

        // 如果字段无更新（add、del、put 都为空），且索引也无变化，直接返回
        if add.is_empty()
            && del.is_empty()
            && put.is_empty()
            && unique.is_empty()
            && index.is_empty()
        {
            return JsonValue::from(-1);
        }

        sql = sql.trim_end_matches(",\r\n").to_string();
        // 优化：使用 push_str 减少 format! 调用
        let mut create_sql = String::with_capacity(sql.len() + options.table_name.len() + 50);
        create_sql.push_str("CREATE TABLE ");
        create_sql.push_str(&options.table_name);
        create_sql.push_str("_tmp (\r\n");
        create_sql.push_str(&sql);
        create_sql.push_str("\r\n);\r\n");
        let sql = create_sql;

        // 优化：使用 push_str 减少 format! 调用
        let fields_new_str = fields_list_new.join("`,`");
        let fields_old_str = fields_list.join("`,`");
        let mut sqls = String::with_capacity(
            options.table_name.len() * 2 + fields_new_str.len() + fields_old_str.len() + 100,
        );
        sqls.push_str("replace INTO ");
        sqls.push_str(&options.table_name);
        sqls.push_str("_tmp (`");
        sqls.push_str(&fields_new_str);
        sqls.push_str("`) select `");
        sqls.push_str(&fields_old_str);
        sqls.push_str("` from ");
        sqls.push_str(&options.table_name);
        sqls.push_str(";\r\n");

        let mut drop_sql = String::with_capacity(options.table_name.len() + 20);
        drop_sql.push_str("drop table ");
        drop_sql.push_str(&options.table_name);
        drop_sql.push_str(";\r\n");

        let mut alter_sql = String::with_capacity(options.table_name.len() * 2 + 30);
        alter_sql.push_str("alter table ");
        alter_sql.push_str(&options.table_name);
        alter_sql.push_str("_tmp rename to ");
        alter_sql.push_str(&options.table_name);
        alter_sql.push_str(";\r\n");

        let mut drop_sql_temp = String::with_capacity(options.table_name.len() + 25);
        drop_sql_temp.push_str("drop table ");
        drop_sql_temp.push_str(&options.table_name);
        drop_sql_temp.push_str("_tmp;\r\n");

        if self.params.sql {
            let mut list = vec![sql, sqls, drop_sql, alter_sql, drop_sql_temp];
            if !unique.is_empty() {
                list.push(unique)
            }
            if !index.is_empty() {
                list.extend(index)
            }
            return JsonValue::from(list.join(""));
        }

        if add.is_empty()
            && del.is_empty()
            && unique.is_empty()
            && index.is_empty()
            && put.is_empty()
        {
            return JsonValue::from(-1);
        }

        let (state, _) = self.execute(sql.clone());
        let data = match state {
            true => {
                let (state, _) = self.execute(sqls.clone());
                match state {
                    true => {
                        let (state, _) = self.execute(drop_sql);
                        match state {
                            true => {
                                let (state, _) = self.execute(alter_sql);
                                match state {
                                    true => {
                                        // 表结构更新完成，现在创建索引
                                        // 再次验证字段存在性（从新表查询字段列表）
                                        let (_, new_table_fields_result) = self.query(format!(
                                            "PRAGMA table_info(`{}`)",
                                            options.table_name
                                        ));
                                        let mut new_table_fields = vec![];
                                        for item in new_table_fields_result.members() {
                                            if let Some(field_name) = item["name"].as_str() {
                                                new_table_fields.push(field_name.to_string());
                                            }
                                        }

                                        // 创建唯一索引
                                        if !unique.is_empty() {
                                            // 先查询现有唯一索引列表（优化：使用 HashSet 提高查找性能）
                                            let (_, existing_unique_index_list) =
                                                self.query(format!(
                                                    "PRAGMA index_list({});\r\n",
                                                    options.table_name
                                                ));
                                            let mut existing_unique_index_names =
                                                HashSet::with_capacity(
                                                    existing_unique_index_list.len(),
                                                );
                                            for item in existing_unique_index_list.members() {
                                                let is_unique =
                                                    item["unique"].as_usize().unwrap_or(0) == 1;
                                                if is_unique {
                                                    if let Some(name) = item["name"].as_str() {
                                                        existing_unique_index_names
                                                            .insert(name.to_string());
                                                    }
                                                }
                                            }

                                            // 从 unique SQL 中提取索引名称
                                            let unique_index_name =
                                                if unique.contains("CREATE UNIQUE INDEX") {
                                                    let start =
                                                        unique.find("CREATE UNIQUE INDEX").unwrap()
                                                            + 19;
                                                    let end = unique[start..]
                                                        .find(" on ")
                                                        .unwrap_or(unique.len() - start);
                                                    unique[start..start + end]
                                                        .trim()
                                                        .trim_matches('`')
                                                        .to_string()
                                                } else if unique.contains("create unique index") {
                                                    let start =
                                                        unique.find("create unique index").unwrap()
                                                            + 19;
                                                    let end = unique[start..]
                                                        .find(" on ")
                                                        .unwrap_or(unique.len() - start);
                                                    unique[start..start + end]
                                                        .trim()
                                                        .trim_matches('`')
                                                        .to_string()
                                                } else {
                                                    String::new()
                                                };

                                            // 从 unique SQL 中提取字段列表进行验证
                                            let unique_fields_str =
                                                if unique.contains("(") && unique.contains(")") {
                                                    let start = unique.find("(").unwrap() + 1;
                                                    let end = unique.find(")").unwrap();
                                                    unique[start..end].to_string()
                                                } else {
                                                    unique_fields.clone()
                                                };

                                            // 检查索引是否已存在（HashSet 查找 O(1)）
                                            if !unique_index_name.is_empty()
                                                && existing_unique_index_names
                                                    .contains(unique_index_name.as_str())
                                            {
                                                // 索引已存在，静默跳过（与 MySQL 标准一致）
                                            } else if Self::validate_index_fields_in_table(
                                                &unique_fields_str,
                                                &new_table_fields,
                                            ) {
                                                let (state, result) = self.execute(unique.clone());
                                                if state {
                                                    // 唯一索引创建成功（不输出日志）
                                                } else {
                                                    // 检查是否是 "already exists" 错误，如果是则静默跳过
                                                    let error_msg =
                                                        result.to_string().to_lowercase();
                                                    if error_msg.contains("already exists") {
                                                        // 索引已存在，静默跳过（与 MySQL 标准一致）
                                                    } else {
                                                        error!(
                                                            "{} {} 唯一索引创建失败: {} SQL: {}",
                                                            thread_id,
                                                            options.table_name,
                                                            result,
                                                            unique
                                                        );
                                                    }
                                                }
                                            } else {
                                                // 字段验证失败，静默跳过（与 MySQL 标准一致）
                                                // 移除错误日志输出
                                            }
                                        }

                                        // 创建普通索引
                                        // 先查询现有索引列表（优化：使用 HashSet 提高查找性能）
                                        let (_, existing_index_list) = self.query(format!(
                                            "PRAGMA index_list({});\r\n",
                                            options.table_name
                                        ));
                                        let mut existing_index_names =
                                            HashSet::with_capacity(existing_index_list.len());
                                        for item in existing_index_list.members() {
                                            let is_unique =
                                                item["unique"].as_usize().unwrap_or(0) == 1;
                                            if !is_unique {
                                                if let Some(name) = item["name"].as_str() {
                                                    existing_index_names.insert(name.to_string());
                                                }
                                            }
                                        }

                                        // 构建索引名称列表（与 table_create 中的逻辑一致，优化：预分配容量）
                                        let estimated_indexes = options.table_index.len();
                                        let mut index_names = Vec::with_capacity(estimated_indexes);
                                        for row in options.table_index.iter() {
                                            let mut index_name = String::new();
                                            for item in row.iter() {
                                                if index_name.is_empty() {
                                                    index_name = format!("index_{}", item);
                                                } else {
                                                    index_name = format!("{}_{}", index_name, item);
                                                }
                                            }
                                            // 完整索引名称格式：{table_name}_{index_name}
                                            let full_index_name =
                                                format!("{}_{}", options.table_name, index_name);
                                            index_names.push(full_index_name);
                                        }

                                        let mut index_failed_count = 0;
                                        for (index_sql, index_name) in
                                            index.iter().zip(index_names.iter())
                                        {
                                            // 从索引 SQL 中提取字段列表进行验证
                                            let index_fields_str = if index_sql.contains("(")
                                                && index_sql.contains(")")
                                            {
                                                let start = index_sql.find("(").unwrap() + 1;
                                                let end = index_sql.find(")").unwrap();
                                                index_sql[start..end].to_string()
                                            } else {
                                                String::new()
                                            };

                                            // 检查索引是否已存在（HashSet 查找 O(1)）
                                            if !index_name.is_empty()
                                                && existing_index_names
                                                    .contains(index_name.as_str())
                                            {
                                                // 索引已存在，静默跳过（与 MySQL 标准一致）
                                                continue;
                                            }

                                            if !index_fields_str.is_empty()
                                                && Self::validate_index_fields_in_table(
                                                    &index_fields_str,
                                                    &new_table_fields,
                                                )
                                            {
                                                let (state, result) =
                                                    self.execute(index_sql.clone());
                                                match state {
                                                    true => {
                                                        // 索引创建成功（不输出日志）
                                                    }
                                                    false => {
                                                        // 检查是否是 "already exists" 错误，如果是则静默跳过
                                                        let error_msg =
                                                            result.to_string().to_lowercase();
                                                        if error_msg.contains("already exists") {
                                                            // 索引已存在，静默跳过（与 MySQL 标准一致）
                                                            continue;
                                                        }
                                                        index_failed_count += 1;
                                                        error!(
                                                            "{} {} 索引创建失败: {} SQL: {}",
                                                            thread_id,
                                                            options.table_name,
                                                            result,
                                                            index_sql
                                                        );
                                                        // 继续创建其他索引，不中断流程
                                                    }
                                                }
                                            } else {
                                                // 字段验证失败，静默跳过（与 MySQL 标准一致）
                                                // 移除错误日志输出
                                            }
                                        }

                                        if index_failed_count > 0 {
                                            // 索引创建完成统计（不输出日志）
                                        }

                                        return JsonValue::from(1);
                                    }
                                    false => {
                                        error!("{} 修改表名失败", options.table_name);
                                        return JsonValue::from(0);
                                    }
                                }
                            }
                            false => {
                                error!("{} 删除本表失败", options.table_name);
                                return JsonValue::from(0);
                            }
                        }
                    }
                    false => {
                        error!(
                            "{} 添加tmp表记录失败 {:#} {:#}",
                            options.table_name, sql, sqls
                        );
                        let sql = format!("drop table {}_tmp", options.table_name);
                        let (_, _) = self.execute(sql);
                        0
                    }
                }
            }
            false => {
                error!("{} 创建TMP表失败 {:#}", options.table_name, sql);
                let (_, _) = self.execute(drop_sql_temp);
                0
            }
        };
        JsonValue::from(data)
    }

    fn table_info(&mut self, table: &str) -> JsonValue {
        // 优化：减少锁持有时间，先检查再获取
        {
            let fields = FIELDS.lock().unwrap();
            if let Some(cached) = fields.get(table) {
                return cached.clone();
            }
        }
        let sql = format!("PRAGMA table_info(`{table}`)");
        let (state, data) = self.query(sql);

        match state {
            true => {
                let mut fields = object! {};
                for item in data.members() {
                    if let Some(name) = item["name"].as_str() {
                        fields[name] = item.clone();
                    }
                }
                // 优化：只在成功时更新缓存，减少锁持有时间
                let fields_clone = fields.clone();
                FIELDS
                    .lock()
                    .unwrap()
                    .insert(table.to_string(), fields_clone);
                data
            }
            false => object! {},
        }
    }

    fn table_is_exist(&mut self, name: &str) -> bool {
        // 使用公共函数验证表名
        let name = match validate_table_name(name) {
            Ok(n) => n,
            Err(e) => {
                error!("表名验证失败: {}", e);
                return false;
            }
        };
        // 使用公共函数添加表名前缀
        let table_name = add_table_prefix(&self.connection.prefix, name);
        let sql = format!(
            "SELECT count(*) as count FROM sqlite_master WHERE type='table' AND name='{table_name}'"
        );
        let (state, data) = self.query(sql);
        if state && !data.is_empty() {
            if let Some(count) = data[0]["count"].as_i64() {
                if count > 0 {
                    return true;
                }
            }
            false
        } else {
            false
        }
    }

    fn table(&mut self, name: &str) -> &mut Sqlite {
        self.params = Params::default(self.connection.mode.str().as_str());
        // 使用公共函数添加表名前缀
        self.params.table = add_table_prefix(&self.connection.prefix, name);
        self.params.join_table = self.params.table.clone();
        self
    }
    fn change_table(&mut self, name: &str) -> &mut Self {
        self.params.join_table = name.to_string();
        self
    }
    fn autoinc(&mut self) -> &mut Self {
        self.params.autoinc = true;
        self
    }

    fn fetch_sql(&mut self) -> &mut Self {
        self.params.sql = true;
        self
    }

    fn order(&mut self, field: &str, by: bool) -> &mut Self {
        self.params.order[field] = {
            if by {
                "DESC"
            } else {
                "ASC"
            }
        }
        .into();
        self
    }

    fn group(&mut self, field: &str) -> &mut Self {
        let fields: Vec<&str> = field.split(",").collect();
        for field in fields.iter() {
            let fields = field.to_string();
            self.params.group[fields.as_str()] = fields.clone().into();
            self.params.fields[fields.as_str()] = fields.clone().into();
        }
        self
    }

    fn distinct(&mut self) -> &mut Self {
        self.params.distinct = true;
        self
    }
    fn json(&mut self, field: &str) -> &mut Self {
        let list: Vec<&str> = field.split(",").collect();
        for item in list.iter() {
            self.params.json[item.to_string().as_str()] = item.to_string().into();
        }
        self
    }

    fn location(&mut self, field: &str) -> &mut Self {
        let list: Vec<&str> = field.split(",").collect();
        for item in list.iter() {
            self.params.location[item.to_string().as_str()] = item.to_string().into();
        }
        self
    }

    fn field(&mut self, field: &str) -> &mut Self {
        let list: Vec<&str> = field.split(",").collect();
        let join_table = if self.params.join_table.is_empty() {
            self.params.table.clone()
        } else {
            self.params.join_table.clone()
        };
        for item in list.iter() {
            let item = item.to_string();
            if item.contains(" as ") {
                let text = item.split(" as ").collect::<Vec<&str>>().clone();
                self.params.fields[item] =
                    format!("{}.`{}` as `{}`", join_table, text[0], text[1]).into();
            } else {
                self.params.fields[item] = format!("{join_table}.`{item}`").into();
            }
        }
        self
    }
    fn hidden(&mut self, name: &str) -> &mut Self {
        let hidden: Vec<&str> = name.split(",").collect();
        let sql = format!("PRAGMA table_info(`{}`)", self.params.table);
        let (_, data) = self.query(sql);
        for item in data.members() {
            let name = item["name"].as_str().unwrap();
            if !hidden.contains(&name) {
                self.params.fields[name] = name.into();
            }
        }
        self
    }

    fn where_and(&mut self, field: &str, compare: &str, mut value: JsonValue) -> &mut Self {
        let join_table = if self.params.join_table.is_empty() {
            self.params.table.clone()
        } else {
            self.params.join_table.clone()
        };

        if value.is_boolean() {
            if value.as_bool().unwrap() {
                value = 1.into();
            } else {
                value = 0.into();
            }
        }

        match compare {
            "between" => {
                self.params.where_and.push(format!(
                    "{}.`{}` between '{}' AND '{}'",
                    join_table, field, value[0], value[1]
                ));
            }
            "set" => {
                let list: Vec<&str> = value.as_str().unwrap().split(",").collect();
                let mut wheredata = vec![];
                for item in list.iter() {
                    wheredata.push(format!("{join_table}.`{field}` like '%{item}%'"));
                }
                self.params
                    .where_and
                    .push(format!("({})", wheredata.join(" or ")));
            }
            "notin" => {
                let mut text = String::new();
                for item in value.members() {
                    text = format!("{text},'{item}'");
                }
                text = text.trim_start_matches(",").into();
                self.params
                    .where_and
                    .push(format!("{join_table}.`{field}` not in ({text})"));
            }
            "in" => {
                let mut text = String::new();
                if value.is_array() {
                    for item in value.members() {
                        text = format!("{text},'{item}'");
                    }
                } else {
                    let value = value.to_string();
                    let value: Vec<&str> = value.split(",").collect();
                    for item in value.iter() {
                        text = format!("{text},'{item}'");
                    }
                }
                text = text.trim_start_matches(",").into();

                self.params
                    .where_and
                    .push(format!("{join_table}.`{field}` {compare} ({text})"));
            }
            "=" => {
                if value.is_null() {
                    self.params
                        .where_and
                        .push(format!("{}.`{}` {} {}", join_table, field, "IS", value));
                } else {
                    self.params
                        .where_and
                        .push(format!("{join_table}.`{field}` {compare} '{value}'"));
                }
            }
            _ => {
                if value.is_null() {
                    self.params
                        .where_and
                        .push(format!("{join_table}.`{field}` {compare} {value}"));
                } else {
                    self.params
                        .where_and
                        .push(format!("{join_table}.`{field}` {compare} '{value}'"));
                }
            }
        }
        self
    }
    fn where_or(&mut self, field: &str, compare: &str, mut value: JsonValue) -> &mut Self {
        let join_table = if self.params.join_table.is_empty() {
            self.params.table.clone()
        } else {
            self.params.join_table.clone()
        };

        if value.is_boolean() {
            if value.as_bool().unwrap() {
                value = 1.into();
            } else {
                value = 0.into();
            }
        }
        match compare {
            "between" => {
                self.params.where_or.push(format!(
                    "{}.`{}` between '{}' AND '{}'",
                    join_table, field, value[0], value[1]
                ));
            }
            "set" => {
                let tt = value.to_string().replace(",", "%");
                self.params
                    .where_or
                    .push(format!("{join_table}.`{field}` like '%{tt}%'"));
            }
            "notin" => {
                let mut text = String::new();
                for item in value.members() {
                    text = format!("{text},'{item}'");
                }
                text = text.trim_start_matches(",").into();
                self.params
                    .where_or
                    .push(format!("{join_table}.`{field}` not in ({text})"));
            }
            "in" => {
                let mut text = String::new();
                if value.is_array() {
                    for item in value.members() {
                        text = format!("{text},'{item}'");
                    }
                } else {
                    let value = value.as_str().unwrap();
                    let value: Vec<&str> = value.split(",").collect();
                    for item in value.iter() {
                        text = format!("{text},'{item}'");
                    }
                }
                text = text.trim_start_matches(",").into();
                self.params
                    .where_or
                    .push(format!("{join_table}.`{field}` {compare} ({text})"));
            }
            _ => {
                self.params
                    .where_or
                    .push(format!("{join_table}.`{field}` {compare} '{value}'"));
            }
        }
        self
    }
    fn where_column(&mut self, field_a: &str, compare: &str, field_b: &str) -> &mut Self {
        self.params.where_column = format!(
            "{}.`{}` {} {}.`{}`",
            self.params.table, field_a, compare, self.params.table, field_b
        );
        self
    }

    fn update_column(&mut self, field_a: &str, compare: &str) -> &mut Self {
        self.params
            .update_column
            .push(format!("{field_a} = {compare}"));
        self
    }

    fn page(&mut self, page: i32, limit: i32) -> &mut Self {
        self.params.page = page;
        self.params.limit = limit;
        self
    }

    fn column(&mut self, field: &str) -> JsonValue {
        self.field(field);
        self.group(field);
        let sql = self.params.select_sql();
        if self.params.sql {
            return JsonValue::from(sql.clone());
        }
        self.table_info(self.params.table.clone().as_str());
        let (state, data) = self.query(sql);
        if state {
            let mut list = array![];
            for item in data.members() {
                if self.params.json[field].is_empty() {
                    list.push(item[field].clone()).unwrap();
                } else {
                    let data = json::parse(item[field].as_str().unwrap()).unwrap_or(array![]);
                    list.push(data).unwrap();
                }
            }
            list
        } else {
            array![]
        }
    }

    fn count(&mut self) -> JsonValue {
        self.params.fields["count"] = "count(*) as count".to_string().into();
        let sql = self.params.select_sql();
        if self.params.sql {
            return JsonValue::from(sql.clone());
        }
        let (state, data) = self.query(sql);
        match state {
            true => data[0]["count"].clone(),
            false => JsonValue::from(0),
        }
    }

    fn max(&mut self, field: &str) -> JsonValue {
        self.params.fields[field] = format!("max({field}) as {field}").into();
        let sql = self.params.select_sql();
        if self.params.sql {
            return JsonValue::from(sql.clone());
        }
        let (state, data) = self.query(sql);
        if state {
            if data.len() > 1 {
                return data;
            }
            data[0][field].clone()
        } else {
            JsonValue::from(0.0)
        }
    }

    fn min(&mut self, field: &str) -> JsonValue {
        self.params.fields[field] = format!("min({field}) as {field}").into();
        let sql = self.params.select_sql();
        let (state, data) = self.query(sql);
        if state {
            if data.len() > 1 {
                return data;
            }
            data[0][field].clone()
        } else {
            JsonValue::from(0.0)
        }
    }

    fn sum(&mut self, field: &str) -> JsonValue {
        self.params.fields[field] = format!("sum({field}) as {field}").into();
        let sql = self.params.select_sql();
        if self.params.sql {
            return JsonValue::from(sql.clone());
        }
        let (state, data) = self.query(sql);
        match state {
            true => {
                if data.len() > 1 {
                    return data;
                }
                if self.params.fields.len() > 1 {
                    return data[0].clone();
                }
                data[0][field].clone()
            }
            false => JsonValue::from(0),
        }
    }
    fn avg(&mut self, field: &str) -> JsonValue {
        self.params.fields[field] = format!("avg({field}) as {field}").into();
        let sql = self.params.select_sql();
        if self.params.sql {
            return JsonValue::from(sql.clone());
        }
        let (state, data) = self.query(sql);
        if state {
            if data.len() > 1 {
                return data;
            }
            data[0][field].clone()
        } else {
            JsonValue::from(0)
        }
    }
    fn select(&mut self) -> JsonValue {
        let sql = self.params.select_sql();
        if self.params.sql {
            return JsonValue::from(sql.clone());
        }
        self.table_info(self.params.table.clone().as_str());
        let (state, mut data) = self.query(sql.clone());
        match state {
            true => {
                for (field, _) in self.params.json.entries() {
                    for item in data.members_mut() {
                        if !item[field].is_empty() {
                            let json = item[field].to_string();
                            item[field] = match json::parse(&json) {
                                Ok(e) => e,
                                Err(_) => JsonValue::from(json),
                            };
                        }
                    }
                }
                data.clone()
            }
            false => {
                error!("{data:?}");
                array![]
            }
        }
    }
    fn find(&mut self) -> JsonValue {
        self.params.page = 1;
        self.params.limit = 1;
        let sql = self.params.select_sql();
        if self.params.sql {
            return JsonValue::from(sql.clone());
        }

        self.table_info(self.params.table.clone().as_str());
        let (state, mut data) = self.query(sql.clone());
        match state {
            true => {
                if data.is_empty() {
                    return object! {};
                }
                for (field, _) in self.params.json.entries() {
                    if !data[0][field].is_empty() {
                        let json = data[0][field].to_string();
                        let json = json::parse(&json).unwrap_or(array![]);
                        data[0][field] = json;
                    } else {
                        data[0][field] = array![];
                    }
                }
                data[0].clone()
            }
            false => {
                error!("{data:?}");
                object! {}
            }
        }
    }

    fn value(&mut self, field: &str) -> JsonValue {
        self.params.fields = object! {};
        self.params.fields[field] = format!("{}.`{}`", self.params.table, field).into();
        self.params.page = 1;
        self.params.limit = 1;
        let sql = self.params.select_sql();
        if self.params.sql {
            return JsonValue::from(sql.clone());
        }
        self.table_info(self.params.table.clone().as_str());
        let (state, mut data) = self.query(sql.clone());
        match state {
            true => {
                for (field, _) in self.params.json.entries() {
                    if !data[0][field].is_empty() {
                        let json = data[0][field].to_string();
                        let json = json::parse(&json).unwrap_or(array![]);
                        data[0][field] = json;
                    } else {
                        data[0][field] = array![];
                    }
                }
                data[0][field].clone()
            }
            false => {
                if self.connection.debug {
                    info!("{data:?}");
                }
                JsonValue::Null
            }
        }
    }

    fn insert(&mut self, mut data: JsonValue) -> JsonValue {
        let mut fields = vec![];
        let mut values = vec![];

        if !self.params.autoinc && data["id"].is_empty() {
            data["id"] = format!("{:X}", Local::now().timestamp_nanos_opt().unwrap()).into();
        }
        for (field, value) in data.entries() {
            fields.push(format!("`{field}`"));

            if value.is_string() {
                values.push(format!("'{}'", value.to_string().replace("'", "''")));
                continue;
            } else if value.is_array() || value.is_object() {
                if self.params.json[field].is_empty() {
                    let json_str = value.to_string();
                    values.push(format!("'{}'", json_str.replace("'", "''")));
                } else {
                    let json = value.to_string();
                    let json = json.replace("'", "''");
                    values.push(format!("'{json}'"));
                }
                continue;
            } else if value.is_number() || value.is_boolean() || value.is_null() {
                values.push(format!("{value}"));
                continue;
            } else {
                values.push(format!("'{}'", value.to_string().replace("'", "''")));
                continue;
            }
        }
        let fields = fields.join(",");
        let values = values.join(",");

        let sql = format!(
            "INSERT INTO `{}` ({}) VALUES ({});",
            self.params.table, fields, values
        );
        if self.params.sql {
            return JsonValue::from(sql.clone());
        }
        let (state, ids) = self.execute(sql);
        match state {
            true => {
                if self.params.autoinc {
                    let (state, ids) =
                        self.query(format!("select max(id) as id from {}", self.params.table));
                    return match state {
                        true => ids[0]["id"].clone(),
                        false => {
                            error!("{ids}");
                            JsonValue::from("")
                        }
                    };
                }
                data["id"].clone()
            }
            false => {
                error!("{ids}");
                JsonValue::from("")
            }
        }
    }
    fn insert_all(&mut self, mut data: JsonValue) -> JsonValue {
        let mut fields = String::new();

        if !self.params.autoinc && data[0]["id"].is_empty() {
            data[0]["id"] = "".into();
        }
        for (field, _) in data[0].entries() {
            fields = format!("{fields},`{field}`");
        }
        fields = fields.trim_start_matches(",").parse().unwrap();

        let core_count = num_cpus::get();
        let mut p = pools::Pool::new(core_count * 4);
        let autoinc = self.params.autoinc;
        for list in data.members() {
            let mut item = list.clone();
            p.execute(move |pcindex| {
                if !autoinc && item["id"].is_empty() {
                    let id = format!(
                        "{:X}{:X}",
                        Local::now().timestamp_nanos_opt().unwrap(),
                        pcindex
                    );
                    item["id"] = id.into();
                }
                let mut values = "".to_string();
                for (_, value) in item.entries() {
                    if value.is_string() {
                        values = format!("{},'{}'", values, value.to_string().replace("'", "''"));
                    } else if value.is_number() || value.is_boolean() {
                        values = format!("{values},{value}");
                    } else {
                        values = format!("{},'{}'", values, value.to_string().replace("'", "''"));
                    }
                }
                values = format!("({})", values.trim_start_matches(","));
                array![item["id"].clone(), values]
            });
        }
        let (ids_list, mut values) = p.insert_all();

        values = values.trim_start_matches(",").parse().unwrap();

        let sql = format!(
            "INSERT INTO {} ({}) VALUES {};",
            self.params.table, fields, values
        );
        if self.params.sql {
            return JsonValue::from(sql.clone());
        }
        let (state, data) = self.execute(sql.clone());
        match state {
            true => {
                if self.params.autoinc {
                    let (state, ids) = self.query(format!(
                        "SELECT id FROM {} GROUP BY id ORDER BY id DESC LIMIT {} OFFSET 0",
                        self.params.table,
                        ids_list.len()
                    ));
                    return match state {
                        true => {
                            let mut idlist = array![];
                            for item in ids.members() {
                                idlist.push(item["id"].clone()).unwrap();
                            }
                            idlist
                        }
                        false => {
                            error!("批量添加失败: {ids:?} {sql}");
                            array![]
                        }
                    };
                }
                JsonValue::from(ids_list)
            }
            false => {
                error!("批量添加失败: {data:?} {sql}");
                array![]
            }
        }
    }

    fn update(&mut self, data: JsonValue) -> JsonValue {
        let mut values = vec![];

        for (field, value) in data.entries() {
            if value.is_string() {
                values.push(format!(
                    "`{}` = '{}'",
                    field,
                    value.to_string().replace("'", "''")
                ));
            } else if value.is_array() || value.is_object() {
                if self.params.json[field].is_empty() {
                    values.push(format!("`{field}` = '{value}'"));
                } else {
                    let json = value.to_string();
                    let json = json.replace("'", "''");
                    values.push(format!("`{field}` = '{json}'"));
                }
                continue;
            } else if value.is_number() || value.is_boolean() || value.is_null() {
                values.push(format!("`{field}` = {value} "));
            } else {
                values.push(format!("`{field}` = '{value}' "));
            }
        }

        for (field, value) in self.params.inc_dec.entries() {
            values.push(format!("{} = {}", field, value.to_string().clone()));
        }

        let values = values.join(",");
        let sql = format!(
            "UPDATE `{}` SET {} {} {};",
            self.params.table.clone(),
            values,
            self.params.where_sql(),
            self.params.page_limit_sql()
        );
        if self.params.sql {
            return JsonValue::from(sql.clone());
        }
        let (state, data) = self.execute(sql);
        if state {
            data
        } else {
            error!("{data}");
            JsonValue::from(0)
        }
    }

    fn update_all(&mut self, data: JsonValue) -> JsonValue {
        let mut values = vec![];
        let mut ids = vec![];
        for (field, _) in data[0].entries() {
            if field == "id" {
                continue;
            }
            let mut fields = vec![];
            for row in data.members() {
                let value = row[field].clone();
                let id = row["id"].clone();
                ids.push(id.clone());
                if value.is_string() {
                    fields.push(format!(
                        "WHEN '{}' THEN '{}'",
                        id,
                        value.to_string().replace("'", "''")
                    ));
                } else if value.is_array() || value.is_object() {
                    if self.params.json[field].is_empty() {
                        fields.push(format!("WHEN '{id}' THEN '{value}'"));
                    } else {
                        let json = value.to_string();
                        let json = json.replace("'", "''");
                        fields.push(format!("WHEN '{id}' THEN '{json}'"));
                    }
                    continue;
                } else if value.is_number() || value.is_boolean() || value.is_null() {
                    fields.push(format!("WHEN '{id}' THEN {value}"));
                } else {
                    fields.push(format!("WHEN '{id}' THEN '{value}'"));
                }
            }
            values.push(format!("`{}` = CASE id {} END", field, fields.join(" ")))
        }

        self.where_and("id", "in", ids.into());
        for (field, value) in self.params.inc_dec.entries() {
            values.push(format!("{} = {}", field, value.to_string().clone()));
        }

        let values = values.join(",");
        let sql = format!(
            "UPDATE {} SET {} {} {};",
            self.params.table.clone(),
            values,
            self.params.where_sql(),
            self.params.page_limit_sql()
        );
        if self.params.sql {
            return JsonValue::from(sql.clone());
        }
        let (state, data) = self.execute(sql);
        if state {
            data
        } else {
            error!("{data:?}");
            JsonValue::from(0)
        }
    }
    fn delete(&mut self) -> JsonValue {
        let sql = format!(
            "delete FROM `{}` {};",
            self.params.table.clone(),
            self.params.where_sql()
        );
        if self.params.sql {
            return JsonValue::from(sql.clone());
        }
        let (state, data) = self.execute(sql);
        match state {
            true => data,
            false => {
                error!("delete 失败>>>{data:?}");
                JsonValue::from(0)
            }
        }
    }

    fn transaction(&mut self) -> bool {
        let thread_id = get_thread_id();
        // 优化：减少锁持有时间，先读取再写入
        let transaction = {
            let tr_count = TR_COUNT.lock().unwrap();
            *tr_count.get(&*thread_id.clone()).unwrap_or(&0)
        };
        if transaction > 0 {
            let new_count = transaction + 1;
            TR_COUNT
                .lock()
                .unwrap()
                .insert(thread_id.clone(), new_count);
            return true;
        }

        loop {
            let mut t = TR_COUNT.lock().unwrap();
            if t.is_empty() {
                t.insert(thread_id.clone(), 1);
                break;
            }
            drop(t);
            thread::yield_now();
        }

        let flags = OpenFlags::new().with_read_write().with_no_mutex();
        let db = match Connect::open_thread_safe_with_flags(
            self.connection.clone().get_dsn().as_str(),
            flags,
        ) {
            Ok(e) => Arc::new(e),
            Err(_) => {
                return false;
            }
        };
        TR.lock().unwrap().insert(thread_id.clone(), db);
        let (state, data) = self.query("BEGIN".to_string());
        if state {
            true
        } else {
            error!("{thread_id} 启动事务失败: {data}");
            TR.lock().unwrap().remove(&thread_id.clone());
            TR_COUNT.lock().unwrap().remove(&thread_id.clone());
            false
        }
    }

    fn commit(&mut self) -> bool {
        let thread_id = get_thread_id();

        let mut transaction = *TR_COUNT
            .lock()
            .unwrap()
            .get(&*thread_id.clone())
            .unwrap_or(&0);
        if transaction > 1 {
            transaction -= 1;
            TR_COUNT
                .lock()
                .unwrap()
                .insert(thread_id.clone(), transaction);
            return true;
        }
        let sql = "COMMIT";
        let (state, data) = self.query(sql.to_string());
        TR_COUNT.lock().unwrap().remove(&*thread_id.clone());
        TR.lock().unwrap().remove(&*thread_id.clone());

        if state {
            true
        } else {
            let error_msg = data
                .as_str()
                .map(|s| s.to_string())
                .unwrap_or_else(|| data.to_string());
            error!(
                "提交事务失败 - 线程ID: {} | 数据库配置: {} | 数据库路径: {} | SQL: {} | 错误详情: {}",
                thread_id,
                self.default,
                self.connection.database,
                sql,
                error_msg
            );
            false
        }
    }

    fn rollback(&mut self) -> bool {
        let thread_id = get_thread_id();
        let sql = "ROLLBACK";

        let mut t = *TR_COUNT.lock().unwrap().get(&thread_id).unwrap_or(&0);
        if t > 1 {
            t -= 1;
            TR_COUNT.lock().unwrap().insert(thread_id.clone(), t);
            return true;
        }
        let (state, data) = self.query(sql.to_string());
        TR_COUNT.lock().unwrap().remove(&*thread_id.clone());
        TR.lock().unwrap().remove(&*thread_id.clone());

        if state {
            true
        } else {
            let error_msg = data
                .as_str()
                .map(|s| s.to_string())
                .unwrap_or_else(|| data.to_string());
            error!(
                "回滚事务失败 - 线程ID: {} | 数据库配置: {} | 数据库路径: {} | SQL: {} | 错误详情: {}",
                thread_id,
                self.default,
                self.connection.database,
                sql,
                error_msg
            );
            false
        }
    }

    fn sql(&mut self, sql: &str) -> Result<JsonValue, String> {
        let (state, data) = self.query(sql.to_string());
        match state {
            true => Ok(data),
            false => Err(data.to_string()),
        }
    }

    fn sql_execute(&mut self, sql: &str) -> Result<JsonValue, String> {
        let (state, data) = self.execute(sql.to_string());
        match state {
            true => Ok(data),
            false => Err(data.to_string()),
        }
    }

    fn inc(&mut self, field: &str, num: f64) -> &mut Self {
        self.params.inc_dec[field] = format!("`{field}` + {num}").into();
        self
    }

    fn dec(&mut self, field: &str, num: f64) -> &mut Self {
        self.params.inc_dec[field] = format!("`{field}` - {num}").into();
        self
    }

    fn buildsql(&mut self) -> String {
        self.fetch_sql();
        let sql = self.select().to_string();
        format!("( {} ) `{}`", sql, self.params.table)
    }

    fn join_fields(&mut self, fields: Vec<&str>) -> &mut Self {
        for field in fields {
            self.params.fields[field] = format!("{field} as {}", field.replace(".", "_")).into();
        }
        self
    }

    fn join(
        &mut self,
        main_table: &str,
        main_fields: &str,
        right_table: &str,
        right_fields: &str,
    ) -> &mut Self {
        let main_table = if main_table.is_empty() {
            self.params.table.clone()
        } else {
            main_table.to_string()
        };
        self.params.join_table = right_table.to_string();
        self.params.join.push(format!(" LEFT JOIN {right_table} ON {main_table}.{main_fields} = {right_table}.{right_fields} "));
        self
    }

    fn join_inner(&mut self, table: &str, main_fields: &str, second_fields: &str) -> &mut Self {
        let main_fields = if main_fields.is_empty() {
            "id"
        } else {
            main_fields
        };
        let second_fields = if second_fields.is_empty() {
            self.params.table.clone()
        } else {
            second_fields.to_string().clone()
        };
        let sec_table_name = format!("{}{}", table, "_2");
        let second_table = format!("{} {}", table, sec_table_name.clone());
        self.params.join_table = sec_table_name.clone();
        self.params.join.push(format!(
            " INNER JOIN {} ON {}.{} = {}.{}",
            second_table, self.params.table, main_fields, sec_table_name, second_fields
        ));
        self
    }
}