tiny_orm_core 0.4.6

//! 数据库数据crud实现
//!
use anyhow::{Context, Result as AnyhowResult};
use async_trait::async_trait;
use sqlx::{query::Query, Pool};

// MySql 数据库配置
#[cfg(feature = "mysql")]
use sqlx::{mysql::MySqlArguments, MySql as TinyOrmDatabase};

#[cfg(feature = "mysql")]
/// mysql 数据库连接池
pub type TinyOrmDbPool = Pool<TinyOrmDatabase>;
#[cfg(feature = "mysql")]
/// mysql 数据行
pub type TinyOrmSqlRow = <TinyOrmDatabase as sqlx::Database>::Row;
#[cfg(feature = "mysql")]
/// mysql SQL执行结果
pub type TinyOrmSqlResult = <TinyOrmDatabase as sqlx::Database>::QueryResult;
// Postgres 数据库配置
#[cfg(feature = "postgres")]
use sqlx::{postgres::PgArguments, Postgres as TinyOrmDatabase};
#[cfg(feature = "postgres")]
/// mysql 数据库连接池
pub type TinyOrmDbPool = Pool<TinyOrmDatabase>;
#[cfg(feature = "postgres")]
/// mysql 数据行
pub type TinyOrmSqlRow = <TinyOrmDatabase as sqlx::Database>::Row;
#[cfg(feature = "postgres")]
/// mysql SQL执行结果
pub type TinyOrmSqlResult = <TinyOrmDatabase as sqlx::Database>::QueryResult;

// Sqlite 数据库配置
#[cfg(feature = "sqlite")]
use sqlx::{sqlite::SqliteArguments, Sqlite as TinyOrmDatabase};
#[cfg(feature = "sqlite")]
/// mysql 数据库连接池
pub type TinyOrmDbPool = Pool<TinyOrmDatabase>;
#[cfg(feature = "sqlite")]
/// mysql 数据行
pub type TinyOrmSqlRow = <TinyOrmDatabase as sqlx::Database>::Row;
#[cfg(feature = "sqlite")]
/// mysql SQL执行结果
pub type TinyOrmSqlResult = <TinyOrmDatabase as sqlx::Database>::QueryResult;
/*
// MsSql数据库配置
#[cfg(feature = "mssql")]
use sqlx::{mssql::MssqlArguments, Mssql as TinyOrmDatabase};
#[cfg(feature = "mssql")]
/// MsSql 数据库连接池
pub type TinyOrmDbPool = Pool<TinyOrmDatabase>;
#[cfg(feature = "mssql")]
/// MsSql 数据行
pub type TinyOrmSqlRow = <TinyOrmDatabase as sqlx::Database>::Row;
#[cfg(feature = "mssql")]
/// MsSql SQL执行结果
pub type TinyOrmSqlResult = <TinyOrmDatabase as sqlx::Database>::QueryResult;

 */
#[cfg(feature = "any")]
use sqlx::{any::AnyArguments, Any as TinyOrmDatabase};
#[cfg(feature = "any")]
/// mysql 数据库连接池
pub type TinyOrmDbPool = Pool<TinyOrmDatabase>;
#[cfg(feature = "any")]
/// mysql 数据行
pub type TinyOrmSqlRow = <TinyOrmDatabase as sqlx::Database>::Row;
#[cfg(feature = "any")]
/// mysql SQL执行结果
pub type TinyOrmSqlResult = <TinyOrmDatabase as sqlx::Database>::QueryResult;

/// 数据库对应表相关meta配置参数
#[derive(Debug)]
pub struct TinyOrmDbMeta {
    /// 对应数据库表名称
    pub table_name: &'static str,
    /// 缺省查询完整SQL
    pub select_sql: &'static str,
    /// 缺省更新完整SQL
    pub update_sql: &'static str,
    /// 更新SQL的SET部分
    pub update_set_sql: &'static str,
    /// 主键对应的SQL WHERE部分
    pub pk_where_sql: &'static str,
}
impl TinyOrmDbMeta {
    /// 创建select sql语句
    /// # 参数说明
    ///
    /// * where_sql 自定义查询条件
    ///    * 一般情况下，如果查询条件非动态的，直接实现使用select_sql即可
    ///    * 查询动态情况下，使用该方法传入查询条件
    pub fn build_select_sql(&self, where_sql: &str) -> String {
        self.build_select_sql_with_order(where_sql, None)
    }

    /// 创建附带order by 的 select sql语句
    /// # 参数说明
    ///
    /// * where_sql 已定义查询条件
    ///    * 一般情况下，如果查询条件非动态的，直接实现使用select_sql即可
    ///    * 查询动态情况下，使用该方法传入查询条件
    /// * order_by 自定义order by 语句
    pub fn build_select_sql_with_order(&self, where_sql: &str,order_by:Option<&str>) -> String {
        if let Some(order_by) = order_by {
            format!("{} WHERE {} ORDER BY {}", self.select_sql, where_sql,order_by)
        }else{
            format!("{} WHERE {} ", self.select_sql, where_sql)
        }
    }
    /// 创建查询id是否存在的SQL
    ///
    /// # 参数说明
    ///
    /// * where_sql 已定义查询条件
    ///    * 一般情况下，如果查询条件非动态的，直接实现exist_where_sql即可
    ///    * 查询动态情况下，使用该方法传入查询条件
    pub fn build_exist_sql(&self, where_sql: &str) -> String {
        format!(
            "SELECT count(1) FROM {} WHERE {}",
            self.table_name, where_sql
        )
    }
    /// 创建插入SQL
    ///
    /// # 参数说明
    ///
    /// * insert_field_value INSERT INTO 语句的插入字段和值部分SQL
    pub fn build_insert_sql(&self, insert_field_value: &str) -> String {
        format!("INSERT INTO {} {}", self.table_name, insert_field_value)
    }
    /// 创建删除SQL
    pub fn build_delete_sql(&self, delete_where_sql: &str) -> String {
        format!(
            "DELETE FROM  {} WHERE {}",
            self.table_name, delete_where_sql
        )
    }

    /// 创建自定义更新SQL
    pub fn build_update_sql(&self, set_sql: &str, where_sql: Option<&str>) -> String {
        if let Some(where_sql) = where_sql {
            format!(
                "UPDATE {} SET {} WHERE {}",
                self.table_name, set_sql, where_sql
            )
        } else {
            format!("UPDATE {} SET {}", self.table_name, set_sql)
        }
    }
    /// 创建自定义主键-更新SQL
    pub fn build_update_sql_with_pk(&self, set_sql: &str) -> String {
        self.build_update_sql(set_sql, Some(self.pk_where_sql))
    }
}

pub trait TinyOrmDbModel {
    const DB_META: TinyOrmDbMeta;
    /// 创建数据库查询器
    fn db_query(sql: &str) -> Query<TinyOrmDatabase, MySqlArguments> {
        sqlx::query(sql)
    }
}

#[async_trait]
pub trait TinyOrmDbQuery: TinyOrmDbModel {
    /// 查询数据库中的所有数据(映射为当前Struct)
    ///
    /// # 参数说明
    ///
    /// * pool MySql数据库连接池
    /// * query 要查询的query
    /// * map 数据库row到struct的映射器
    async fn db_fetch_all<F>(
        pool: &TinyOrmDbPool,
        query: Query<'async_trait, TinyOrmDatabase, MySqlArguments>,
        map: F,
    ) -> AnyhowResult<Vec<Self>>
    where
        Self: Sized + Send + Unpin,
        F: FnMut(TinyOrmSqlRow) -> Self + Send,
    {
        query
            .map(map)
            .fetch_all(pool)
            .await
            .with_context(|| "查询数据库数据出错")
    }
    /// 查询数据库中的所有数据(返回原始row,未映射到当前Struct)
    ///
    /// # 参数说明
    ///
    /// * pool MySql数据库连接池
    /// * query 要查询的query
    async fn db_fetch_all_row(
        pool: &TinyOrmDbPool,
        query: Query<'async_trait, TinyOrmDatabase, MySqlArguments>,
    ) -> AnyhowResult<Vec<TinyOrmSqlRow>>
    where
        Self: Sized + Send + Unpin,
    {
        query
            .fetch_all(pool)
            .await
            .with_context(|| "查询数据库数据出错")
    }
    /// 查询数据库中的单条数据（对应数据确定存在）(映射为当前Struct)
    ///
    /// # 参数说明
    ///
    /// * pool MySql数据库连接池
    /// * query 要查询的query
    /// * map 数据库row到struct的映射器
    ///
    /// # 错误
    /// 如果对应记录不存在则报错
    async fn db_fetch_one<F>(
        pool: &TinyOrmDbPool,
        query: Query<'async_trait, TinyOrmDatabase, MySqlArguments>,
        map: F,
    ) -> AnyhowResult<Self>
    where
        Self: Sized + Send + Unpin,
        F: FnMut(TinyOrmSqlRow) -> Self + Send,
    {
        query
            .map(map)
            .fetch_one(pool)
            .await
            .with_context(|| "查询数据库数据出错")
    }
    /// 查询数据库中的单条数据（对应数据确定存在）(返回原始row,未映射为当前Struct)
    ///
    /// # 参数说明
    ///
    /// * pool MySql数据库连接池
    /// * query 要查询的query
    ///
    /// # 错误
    ///
    /// 如果对应记录不存在则报错
    ///
    async fn db_fetch_one_row(
        pool: &TinyOrmDbPool,
        query: Query<'async_trait, TinyOrmDatabase, MySqlArguments>,
    ) -> AnyhowResult<TinyOrmSqlRow>
    where
        Self: Sized + Send + Unpin,
    {
        query
            .fetch_one(pool)
            .await
            .with_context(|| "查询数据库数据出错")
    }

    /// 查询数据库中的单条数据(可能不存在)(返回原始row,未映射为当前Struct)
    ///
    /// # 参数说明
    ///
    /// * pool MySql数据库连接池
    /// * query 要查询的query
    /// * map 数据库row到struct的映射器
    ///
    /// # 返回
    /// 存在则返回对应记录，否则返回None
    async fn db_fetch_optional<F>(
        pool: &TinyOrmDbPool,
        query: Query<'async_trait, TinyOrmDatabase, MySqlArguments>,
        map: F,
    ) -> AnyhowResult<Option<Self>>
    where
        Self: Sized + Send + Unpin,
        F: FnMut(TinyOrmSqlRow) -> Self + Send,
    {
        query
            .map(map)
            .fetch_optional(pool)
            .await
            .with_context(|| "查询数据库数据出错")
    }

    // 查询数据库中的单条数据(可能不存在)(映射为当前Struct)
    ///
    /// # 参数说明
    ///
    /// * pool MySql数据库连接池
    /// * query 要查询的query
    ///
    /// # 返回
    /// 存在则返回对应记录，否则返回None
    async fn db_fetch_optional_row(
        pool: &TinyOrmDbPool,
        query: Query<'async_trait, TinyOrmDatabase, MySqlArguments>,
    ) -> AnyhowResult<Option<TinyOrmSqlRow>>
    where
        Self: Sized + Send + Unpin,
    {
        query
            .fetch_optional(pool)
            .await
            .with_context(|| "查询数据库数据出错")
    }
    /// 使用缺省条件查询所有数据(映射为当前Struct)
    ///
    /// 使用build_default_select_sql的结果作为查询SQL
    async fn db_get_all<F>(pool: &TinyOrmDbPool, map: F) -> AnyhowResult<Vec<Self>>
    where
        Self: Sized + Send + Unpin,
        F: FnMut(TinyOrmSqlRow) -> Self + Send,
    {
        let sql = Self::DB_META.select_sql;
        let query = Self::db_query(sql);
        Self::db_fetch_all(pool, query, map).await
    }

    /// 查询数据库中的单条数据（对应数据确定存在）(返回原始row,未映射为当前Struct)
    ///
    /// # 参数说明
    ///
    /// * pool MySql数据库连接池
    /// * query 要查询的query
    /// * map 数据库row到struct的映射器
    ///
    /// # 错误
    ///
    /// 如果对应记录不存在则报错
    ///
    async fn db_execute(
        pool: &TinyOrmDbPool,
        query: Query<'async_trait, TinyOrmDatabase, MySqlArguments>,
    ) -> AnyhowResult<TinyOrmSqlResult>
    where
        Self: Sized + Send + Unpin,
    {
        query.execute(pool).await.with_context(|| "执行sql语句出错")
    }
}

/// 数据获取相关接口
#[async_trait]
pub trait TinyOrmData: TinyOrmDbQuery {
    /// 将sql返回数据映射为当前结构
    fn orm_row_map(row: TinyOrmSqlRow) -> Self;

    /// 获取所有数据
    ///
    /// # 参数说明
    ///
    /// * pool 连接池
    async fn orm_get_all(pool: &TinyOrmDbPool) -> AnyhowResult<Vec<Self>>
    where
        Self: Sized + Unpin,
    {
        Self::db_get_all(pool, Self::orm_row_map).await
    }

    /// 获取单条数据(该数据确保存在)
    ///
    /// # 参数说明
    ///
    /// * pool 连接池
    /// 通过关键字查询数据
    ///
    /// # 参数说明
    ///
    /// * pool 连接池
    /// * sql 查询sql
    /// * query_value 查询条件值
    ///
    /// # 返回说明
    ///
    /// 返回符合条件的单条记录
    ///
    /// # 错误
    ///
    /// 如查询记录不存在，则报错
    async fn orm_get_with_sql<T>(
        pool: &TinyOrmDbPool,
        sql: &'async_trait str,
        query_value: T,
    ) -> AnyhowResult<Self>
    where
        Self: Sized + Unpin,
        T: 'async_trait
            + Send
            + sqlx::Type<TinyOrmDatabase>
            + sqlx::Encode<'async_trait, TinyOrmDatabase>
            + Sync,
    {
        let query = Self::db_query(sql).bind(query_value);
        Self::db_fetch_one(pool, query, Self::orm_row_map).await
    }
    
    /// 获取单条数据(该数据可能不存在)
    ///
    /// # 参数说明
    ///
    /// * pool 连接池
    /// 通过关键字查询数据
    ///
    /// # 参数说明
    ///
    /// * pool 连接池
    ///
    /// # 返回说明
    /// * sql 查询sql
    /// * query_value 查询条件值
    ///
    /// 返回符合条件的单条记录,如不存在则返回None
    async fn orm_get_optional_with_sql<T>(
        pool: &TinyOrmDbPool,
        sql: &'async_trait str,
        query_value: T,
    ) -> AnyhowResult<Option<Self>>
    where
        Self: Sized + Unpin,
        T: 'async_trait
            + Send
            + sqlx::Type<TinyOrmDatabase>
            + sqlx::Encode<'async_trait, TinyOrmDatabase>
            + Sync,
    {
        let query = Self::db_query(sql).bind(query_value);
        Self::db_fetch_optional(pool, query, Self::orm_row_map).await
    }
    /// 通过关键字查询数据
    ///
    /// # 参数说明
    ///
    /// * pool 连接池
    /// * sql 查询sql
    /// * query_value 查询条件值
    async fn orm_filter_with_sql<T>(
        pool: &TinyOrmDbPool,
        sql: &'async_trait str,
        query_value: T,
    ) -> AnyhowResult<Vec<Self>>
    where
        Self: Sized + Unpin,
        T: 'async_trait
            + Send
            + sqlx::Type<TinyOrmDatabase>
            + sqlx::Encode<'async_trait, TinyOrmDatabase>
            + Sync,
    {
        let query = Self::db_query(sql).bind(query_value);
        Self::db_fetch_all(pool, query, Self::orm_row_map).await
    }

}
#[cfg(test)]
mod test;