IU1314
admin
预览
IU1314
admin
预览

数据库相关文件

文件目录结构

主要文件位于 db crate
目录结构

db ├── Cargo.toml └── src ├── common // 一些通用model的定义 │ ├── captcha.rs │ ├── client.rs │ ├── ctx.rs │ ├── data_scope.rs │ ├── mod.rs │ └── res.rs ├── db.rs // db 的连接定义 OnceCell ├── lib.rs ├── system // 系统模块 │ ├── entities // entity文件夹 有sea-orm-cli 生成 │ ├── mod.rs │ └── models // model 文件，请求，返回，以及其他数据结构定义 └── test // 测试模块 ├── entities ├── mod.rs └── models

entity 生成

  • 安装 sea-orm-cli 命令: cargo install sea-orm-cli

  • 配置 .env 文件,在程序根目录创建该文件，写入 db 配置信息

    # DATABASE_URL=mysql://root:password@192.168.1.11:13306/db DATABASE_URL=sqlite://data/sqlite/data.db #DATABASE_URL = postgres://postgres:password@192.168.1.11:15432/wk_test

  • 运行命令,文件生成在对应目录 -o 后为目录

    • 包含表：暂时失效中 这个参数其实挺好的，但是暂时失效了
      sea-orm-cli generate entity -o db/src/system/entities --expanded-format --with-serde both -t sys_dept,sys_dict_type,sys_dict_data,sys_job,sys_job_log,sys_login_log,sys_menu,sys_oper_log,sys_post,sys_role,sys_role_api,sys_role_dept,sys_user,sys_user_online,sys_user_post,sys_user_role,sys_api_db,sys_user_dept,sys_update_log
    • 排除表:
      sea-orm-cli generate entity -o db/src/system/entities --expanded-format --with-serde both --ignore-tables test_data_scope,seaql_migrations
    • 全部表
      sea-orm-cli generate entity -o db/src/system/entities --expanded-format --with-serde both
    • 更多参数
      -u / --database-url: database URL (default: DATABASE_URL specified in ENV) -s / --database-schema: database schema (default: DATABASE_SCHEMA specified in ENV) for MySQL & SQLite, this argument is ignored for PostgreSQL, this argument is optional with default value 'public' -o / --output-dir: entity file output directory (default: current directory) -v / --verbose: print debug messages -l / --lib: generate index file as lib.rs instead of mod.rs --include-hidden-tables: generate entity files from hidden tables (tables with names starting with an underscore are hidden and ignored by default) --ignore-tables: skip generating entity file for specified tables (default: seaql_migrations) --compact-format: generate entity file of compact format (default: true) --expanded-format: generate entity file of expanded format --with-serde: automatically derive serde Serialize / Deserialize traits for the entity (none, serialize, deserialize, both) (default: none) --date-time-crate: the datetime crate to use for generating entities (chrono, time) (default: chrono) --max-connections: maximum number of database connections to be initialized in the connection pool (default: 1)

model 文件编写

use chrono::NaiveDateTime;
use sea_orm::FromQueryResult;
use serde::{Deserialize, Serialize};

#[derive(Deserialize, Debug)]
pub struct SearchReq {
    pub dept_id: Option<String>,
    pub dept_name: Option<String>,
    pub status: Option<String>,
    pub begin_time: Option<String>,
    pub end_time: Option<String>,
}

#[derive(Deserialize, Clone, Debug)]
pub struct AddReq {
    pub parent_id: String,
    pub dept_name: String,
    pub order_num: i32,
    pub leader: Option<String>,
    pub phone: Option<String>,
    pub email: Option<String>,
    pub status: String,
}

#[derive(Deserialize)]
pub struct DeleteReq {
    pub dept_id: String,
}

#[derive(Deserialize, Clone, Debug)]
pub struct EditReq {
    pub dept_id: String,
    pub parent_id: String,
    pub dept_name: String,
    pub order_num: i32,
    pub leader: Option<String>,
    pub phone: Option<String>,
    pub email: Option<String>,
    pub status: String,
}

#[derive(Debug, Clone, Serialize, FromQueryResult, Default, Deserialize)]
pub struct DeptResp {
    pub dept_id: String,
    pub parent_id: String,
    pub dept_name: String,
    pub order_num: i32,
    pub leader: Option<String>,
    pub phone: Option<String>,
    pub email: Option<String>,
    pub created_at: NaiveDateTime,
    pub status: String,
}

#[derive(Serialize, Clone, Debug, Default)]
pub struct RespTree {
    #[serde(flatten)]
    pub data: DeptResp,
    pub children: Option<Vec<RespTree>>,
}

  • 请求参数一般实现Deserialize 即可,Clone,Debug,Default 可按需实现

  • 响应参数一般实现Serialize 即可,Clone,Debug,Default,Deserialize 可按需实现,若需数据库转换为该结构体数据，需实现SeaOrm的FromQueryResult

数据库表的关联Relations

sea-orm-cli 的文件生成并不能按照数据库设计的外键进行关联，不知道现在可不可以，以前是不可以，暂时未测试，需要手动修改,具体请参数SeaORM官网https://www.sea-ql.org/SeaORM/docs/relation/bakery-schema/

一般也可在方法中使用join_rev 和 JoinType 直接定义 数据关联，使用起来更方便，不用去修改entity文件,只是编写起来可能麻烦点

pub async fn get_all_admin_role(db: &DatabaseConnection, user_id: &str) ->
Result<Vec<String>> {     let s = SysUserRole::find()
        .join_rev(
            JoinType::LeftJoin,
            sys_user::Entity::belongs_to(sys_user_role::Entity)
                .from(sys_user::Column::Id)
                .to(sys_user_role::Column::UserId)
                .into(),
        )
        // .select_with(sys_user_role::Entity)
        .filter(sys_user::Column::Id.eq(user_id))
        .all(db)
        .await?;
    let res = s.iter().map(|x| x.role_id.clone()).collect::<Vec<String>>();
    Ok(res)
}

大致就是

select * from sys_user_role left join sys_user on sys_user_role.user_id = sys_user.id