tiny_orm_core 0.4.6

//! 数据库模型的测试
use sqlx::mysql::MySqlPoolOptions;
use sqlx::Row;
use tiny_orm_macro_derive::{TinyOrm, TinyOrmQuery};
use crate::prelude::*;
use super::*;

#[derive(Debug, PartialEq, Eq)]
pub struct UserType {
    /// 类型编号
    pub id: Option<u32>,
    /// 类型名称
    pub name: String,
    /// 中断短信模板
    pub template: String,
}
impl UserType {
    /// 完整创建器
    ///
    /// # 参数说明
    ///
    /// * id 类型编号
    /// * name 类型名称
    /// * template 中断短信模板
    pub fn new(id: u32, name: &str, template: &str) -> Self {
        UserType {
            id: Some(id),
            name: name.into(),
            template: template.into(),
        }
    }
}
#[derive(Debug, PartialEq, Eq)]
pub struct Organization {
    /// 行号
    pub id: String,
    /// 机构名称
    pub name: String,
}

#[allow(dead_code)]
#[derive(TinyOrm, TinyOrmQuery, Debug, PartialEq, Eq)]
// 自动生成表格名称时附加前缀，生成表名称为：core_test_user
//#[orm_table_name_pref = "core"]
// 指定表名称，未指定则自动生成，规则结构名称转换为蛇形命名，如：test_user
#[orm_table_name = "user"]
/// 自动生成orm_query_with查询方法，生成后的函数定义如下
/// /// 根据姓名和手机号查询用户
/// pub async fn orm_query_with_name_and_tel(pool: &TinyOrmDbPool, name:&str,mobile_phone:&str) ->Result<Vec<Self>> {
///     let sql = Self::DB_META.build_select_sql("name = ? and mobile_phone = ?");
///     let query = Self::db_query(&sql)
///         .bind(name)
///         .bind(mobile_phone);
///     Self::db_fetch_all(pool,query,Self::orm_row_map).await
/// }
#[orm_query(
    name = "name_and_tel",
    sql_where = "user.name = ? and mobile_phone = ?",
    args = "name:&str,mobile_phone:&str",
    doc = "根据姓名和手机号查询用户"
)]
/// 自动生成orm_delete_with删除方法，生成后的函数定义如下
/// /// 根据姓名和手机号删除用户
/// pub async fn orm_delete_with_name_and_tel(pool: &TinyOrmDbPool, name:&str,mobile_phone:&str) ->Result<Vec<Self>> {
///     let sql = Self::DB_META.build_delete_sql("name = ? and mobile_phone = ?");
///     let query = Self::db_query(&sql)
///         .bind(name)
///         .bind(mobile_phone);
///     Self::db_execute(pool, query).await
/// }
#[orm_delete(
    name = "name_and_tel",
    sql_where = "user.name = ? and mobile_phone = ?",
    args = "name:&str,mobile_phone:&str",
    doc = "根据姓名和手机号删除用户"
)]
/// 生成orm_exist_with_name方法
#[orm_exist(
    name = "name",
    sql_where = "user.name like ?",
    args = "name:&str",
    doc = "根据姓名查询用户是否存在"
)]
/// 生成orm_update_with_name方法
#[orm_update(
    name = "name_and_tel",
    sql_set = "name = ? , mobile_phone = ?",
    sql_where = "id = ?",
    args = "name:&str,mobile_phone:&str,id:u32",
    doc = "根据id更新姓名和手机号"
)]
pub struct TestUser {
    /// 类型编号
    /// 生成的orm_get_by_pk函数参数中，id转换为u32
    /// 会自动将多个pk字段合并为一个方法的参数，生成如下方法
    /// /// orm_pk自动实现:通过主键获取记录
    /// pub async fn orm_get_by_pk(pool: &TinyOrmDbPool, id:u32,mobile_phone:&str) -> Result<Self>{
    ///   let sql = Self::DB_META.build_select_sql(#pk_where_sql);
    ///   let query = Self::db_query(&sql)
    ///      .bind(id)
    ///      .bind(mobile_phone);
    ///   Self::db_fetch_one(pool, query, Self::orm_row_map).await
    ///      .with_context(|| "根据主键获取记录出错!")
    /// }
    /// /// orm_pk自动实现:通过主键删除记录
    /// pub async fn orm_delete_by_pk(pool: &TinyOrmDbPool,id:u32,mobile_phone:&str) -> Result<()>{
    ///   let sql = Self::DB_META.build_delete_sql(#pk_where_sql);
    ///   let query = Self::db_query(&sql)
    ///      .bind(id)
    ///      .bind(mobile_phone);
    ///   Self::db_execute(pool, query).await
    ///     .with_context(|| "根据主键删除记录出错!")?;
    ///   Ok(())
    /// }
    /// /// orm_pk自动实现:通过主键删除当前记录
    /// pub async fn orm_delete(&self,pool: &TinyOrmDbPool) -> Result<()>{
    ///     Self::orm_delete_by_pk(pool,self.id.unwrap(),self.mobile_phone.as_ref()).await
    /// }
    /// /// orm_pk自动实现:通过主键查询记录是否存在
    /// pub async fn orm_exist_with_pk(pool: &TinyOrmDbPool,,id:u32,mobile_phone:&str) -> Result<bool>{
    ///     let sql = Self::DB_META.build_exist_sql(Self::DB_META.pk_where_sql);
    ///     let row: (i64,) = sqlx::query_as(&sql)
    ///         .bind(id)
    ///         .bind(mobile_phone)
    ///         .fetch_one(pool)
    ///         .await
    ///         .with_context(|| "通过主键查询记录是否存在时出错!")?;
    ///     Ok(row.0 > 0)
    /// }
    /// /// orm_pk自动实现:通过主键查询当前记录是否存在
    /// pub async fn orm_exist(&self,pool: &TinyOrmDbPool) -> Result<()>{
    ///     Self::orm_exist_with_pk(pool,self.id.unwrap(),self.mobile_phone.as_ref()).await
    /// }
    /// 只能设置一个auto主键
    #[orm_pk(name = "id", auto = "true")]
    /// insert时自动生成id
    pub id: Option<u32>,
    /// 类型名称
    // 生成orm_query_with_name方法
    #[orm_query]
    pub name: String,
    /// 手机号码
    /// 生成的orm_get_by_pk函数参数中，mobile_phone会自动把String转换为&str
    #[orm_pk]
    pub mobile_phone: String,
    /// 密码
    /// 重命名数表字段名称，否则与字段名称一致
    #[orm_field(name = "password")]
    // 生成self.orm_update_password方法
    #[orm_update]
    password: String,
    /// 用户类型
    /// 定义join字段和join sql
    /// 自动生成Self::orm_query_join_with_user_type,Self::orm_delete_join_with_user_type,self.orm_update_join_with_user_type方法
    #[orm_join(
        name="user_type_id", // 重命名表字段名称
        select_field="user_type.name as user_type_name, user_type.template",
        join="JOIN user_type ON user_type.id = user_type_id",
        link_id="id",//update 时保存值使用的字段id，如:user_type.id
        link_id_type="u32"
    )]
    #[allow(unused_variables)]
    pub user_type: UserType,
    /// 所属机构
    /// 自动生成Self::orm_query_join_with_org,Self::orm_delete_join_with_org,self.orm_update_join_with_org方法
    #[orm_join(
        name="org_id", // 重命名表字段名称
        select_field="organization.name as org_name",
        join="JOIN organization ON organization.id = org_id",
        link_id="id",//update 时保存值使用的字段id，如:org.id
        link_id_type="&str"
    )]
    pub org: Organization,
    /// 忽略字段，不在数据库中对应
    #[orm_ignore]
    pub ignore_field: u32,
}

impl TestUser {
    /// 完整创建器
    ///
    /// # 参数说明
    ///
    /// * id 编号
    /// * name 姓名
    /// * mobile_phone 手机
    /// * password 密码
    /// * user_type 用户类型
    /// * org 对应机构
    pub fn new(
        id: u32,
        name: &str,
        mobile_phone: &str,
        password: &str,
        user_type: UserType,
        org: Organization,
    ) -> Self {
        Self {
            id: Some(id),
            name: name.into(),
            mobile_phone: mobile_phone.into(),
            password: password.into(),
            user_type,
            org,
            ignore_field: 0,
        }
    }
    /// 完整创建器
    ///
    /// # 参数说明
    ///
    /// * id 编号
    /// * name 姓名
    /// * mobile_phone 手机
    /// * password 密码
    /// * user_type 用户类型
    /// * org 对应机构
    pub fn new_no_id(
        name: &str,
        mobile_phone: &str,
        password: &str,
        user_type: UserType,
        org: Organization,
    ) -> Self {
        Self {
            id: None,
            name: name.into(),
            mobile_phone: mobile_phone.into(),
            password: password.into(),
            user_type,
            org,
            ignore_field: 0,
        }
    }
}
/// 实现数据获取接口
impl TinyOrmData for TestUser {
    /// 将sql返回数据映射为TestUser
    fn orm_row_map(row: TinyOrmSqlRow) -> Self {
        TestUser::new(
            row.get::<u32, _>("id"),
            row.get("name"),
            row.get("mobile_phone"),
            row.get("password"),
            UserType::new(
                row.get::<u32, _>("user_type_id"),
                row.get("user_type_name"),
                row.get("template"),
            ),
            Organization {
                id: row.get("org_id"),
                name: row.get("org_name"),
            },
        )
    }
}

async fn get_pool() -> AnyhowResult<TinyOrmDbPool> {
    let user_name = "net_guard";
    let password = "net_guard@20220806";
    let ip = "localhost";
    let port = 3306;
    let db_name = "abc_net_guard";
    let pool = MySqlPoolOptions::new()
        .max_connections(1)
        .connect(&format!(
            "mysql://{}:{}@{}:{}/{}",
            user_name, password, ip, port, db_name
        ))
        .await?;
    Ok(pool)
}

/// .测试SQL生成
#[test]
fn test_user() {
    println!("user sql : \n{}", TestUser::DB_META.select_sql);
}

/// .测试SQL生成
#[test]
fn test_db_query() {
    tokio::runtime::Builder::new_current_thread()
        .enable_all()
        .build()
        .unwrap()
        .block_on(async {
            let pool = get_pool().await.unwrap();
            let data = TestUser::orm_get_all(&pool).await.unwrap();
            dbg!(data);
        });
}

/// 测试根据姓名和手机获取用户
#[test]
fn test_orm_query_with_name_and_mobile() {
    tokio::runtime::Builder::new_current_thread()
        .enable_all()
        .build()
        .unwrap()
        .block_on(async {
            let pool = get_pool().await.unwrap();
            let user = TestUser::orm_query_with_name_and_tel(&pool, "张三", "1850703xxxx")
                .await
                .unwrap();
            dbg!(user);
        });
}
/// 测试根据主键获取获取用户
#[test]
fn test_orm_get_with_pk() {
    tokio::runtime::Builder::new_current_thread()
        .enable_all()
        .build()
        .unwrap()
        .block_on(async {
            let pool = get_pool().await.unwrap();
            let user = TestUser::orm_get_with_pk(&pool, 2, "13870381703")
                .await
                .unwrap();
            dbg!(user);
        });
}

/// 测试根据主键删除用户
#[test]
fn test_orm_delete() {
    tokio::runtime::Builder::new_current_thread()
        .enable_all()
        .build()
        .unwrap()
        .block_on(async {
            let pool = get_pool().await.unwrap();
            let user = TestUser::orm_get_with_pk(&pool, 15, "18679301268")
                .await
                .unwrap();
            user.orm_delete(&pool).await.unwrap();
            dbg!(user);
        });
}

/// 测试根据个性删除，根据名称和手机号删除用户
#[test]
fn test_orm_delete_with_name_and_tel() {
    tokio::runtime::Builder::new_current_thread()
        .enable_all()
        .build()
        .unwrap()
        .block_on(async {
            let pool = get_pool().await.unwrap();
            TestUser::orm_delete_with_name_and_tel(&pool, "盛巧岚", "15070310588")
                .await
                .unwrap();
        });
}
/// 测试根据姓名和手机获取用户
#[test]
fn test_orm_query_with_name() {
    tokio::runtime::Builder::new_current_thread()
        .enable_all()
        .build()
        .unwrap()
        .block_on(async {
            let pool = get_pool().await.unwrap();
            let user = TestUser::orm_query_with_name(&pool, "张三").await.unwrap();
            dbg!(user);
        });
}
/// 测试是否存在
#[test]
fn test_orm_exist_with_pk() {
    tokio::runtime::Builder::new_current_thread()
        .enable_all()
        .build()
        .unwrap()
        .block_on(async {
            let pool = get_pool().await.unwrap();
            let is_exist = TestUser::orm_exist_with_pk(&pool, 8, "18607037962")
                .await
                .unwrap();
            dbg!(is_exist);
        });
}

/// 测试是否存在
#[test]
fn test_orm_exist_with_name() {
    tokio::runtime::Builder::new_current_thread()
        .enable_all()
        .build()
        .unwrap()
        .block_on(async {
            let pool = get_pool().await.unwrap();
            let is_exist = TestUser::orm_exist_with_name(&pool, "王佳慧")
                .await
                .unwrap();
            dbg!(is_exist);
        });
}
/// 测试根据姓名和手机获取用户
#[test]
fn test_orm_update_with_name_and_tel() {
    tokio::runtime::Builder::new_current_thread()
        .enable_all()
        .build()
        .unwrap()
        .block_on(async {
            let pool = get_pool().await.unwrap();
            let user = TestUser::orm_update_with_name_and_tel(&pool, "张三", "18507032200", 4)
                .await
                .unwrap();
            dbg!(user);
        });
}
/// 测试filter
#[test]
fn test_db_filter() {
    tokio::runtime::Builder::new_current_thread()
        .enable_all()
        .build()
        .unwrap()
        .block_on(async {
            let pool = get_pool().await.unwrap();
            let sql = TestUser::DB_META.build_select_sql("user.name like  ? ");
            println!("{sql}");
            let key = String::from("%李%");
            let data = TestUser::orm_filter_with_sql(&pool, &sql, &key)
                .await
                .unwrap();
            dbg!(data);
        });
}

/// 测试join字段查询和更新
#[test]
fn test_orm_query_join_with_user_type() {
    tokio::runtime::Builder::new_current_thread()
        .enable_all()
        .build()
        .unwrap()
        .block_on(async {
            let pool = get_pool().await.unwrap();
            let user_type = UserType::new(1, "支行", "");
            let mut data = TestUser::orm_query_join_with_user_type(&pool, user_type)
                .await
                .unwrap();
            let mut ygl = data.pop().unwrap();
            let user_type_2 = UserType::new(2, "", "");
            ygl.user_type = user_type_2;
            ygl.orm_update_join_with_user_type(&pool).await.unwrap();
            dbg!(ygl);
        });
}

/// 测试join字段查询和更新
#[test]
fn test_orm_query_join_with_org_id() {
    tokio::runtime::Builder::new_current_thread()
        .enable_all()
        .build()
        .unwrap()
        .block_on(async {
            let pool = get_pool().await.unwrap();
            let data = TestUser::orm_query_join_with_org_id(&pool, "14H700")
                .await
                .unwrap();
            dbg!(data);
        });
}
/// 测试join字段查询和更新
#[test]
fn test_orm_delete_join_with_user_type() {
    tokio::runtime::Builder::new_current_thread()
        .enable_all()
        .build()
        .unwrap()
        .block_on(async {
            let pool = get_pool().await.unwrap();
            let user_type = UserType::new(2, "支行", "");
            TestUser::orm_delete_join_with_user_type(&pool, user_type)
                .await
                .unwrap();
        });
}
/// 测试更新所有信息
#[test]
fn test_orm_update_all() {
    tokio::runtime::Builder::new_current_thread()
        .enable_all()
        .build()
        .unwrap()
        .block_on(async {
            let pool = get_pool().await.unwrap();
            let mut user = TestUser::orm_get_with_pk(&pool, 2, "13870381703")
                .await
                .unwrap();
            let user_type = UserType::new(2, "支行", "");
            user.name = "王佳慧2".into();
            user.user_type = user_type;
            user.orm_update_all(&pool).await.unwrap();
            dbg!(user);
        });
}

/// 测试插入信息
#[test]
fn test_orm_insert() {
    tokio::runtime::Builder::new_current_thread()
        .enable_all()
        .build()
        .unwrap()
        .block_on(async {
            let pool = get_pool().await.unwrap();
            let org = Organization {
                id: "14H700".into(),
                name: "上饶分行".into(),
            };
            let user_type = UserType::new(2, "管理员", "");
            let mut user = TestUser::new_no_id("常建", "13707932010", "sss", user_type, org);
            user.orm_insert(&pool).await.unwrap();
            dbg!(user);
        });
}
/// 测试根据姓名和手机获取用户
#[test]
fn test_orm_update_password() {
    tokio::runtime::Builder::new_current_thread()
        .enable_all()
        .build()
        .unwrap()
        .block_on(async {
            let pool = get_pool().await.unwrap();
            let mut user:TestUser = TestUser::orm_query_with_name(&pool, "张三").await.unwrap().pop().unwrap();
            user.password="this is a pass".into();
            user.orm_update_password(&pool).await.unwrap();
            dbg!(user);
        });
}