diesel操作sqlite

diesel ={ version = "*",  features = ["sqlite", "r2d2"]}
diesel_derives = { version = "*", features = ["sqlite"] }
lazy_static = "*"

[build-dependencies]
diesel_cli = { version = "*", features = ["sqlite-bundled"] }

cargo install diesel_cli --no-default-features --features "sqlite-bundled" --force

use std::path::Path;
use std::sync::Arc;
use diesel::Connection;
use diesel::prelude::*;
use diesel::associations::HasTable;
use diesel::r2d2::{ConnectionManager, Pool, PooledConnection};
use diesel::sqlite::SqliteConnection;
use lazy_static::lazy_static;
use thiserror::Error;

pub type MyResult<T> = std::result::Result<T, SQLiteError>;
pub type DBConn = PooledConnection<ConnectionManager<SqliteConnection>>;

#[derive(Debug, thiserror::Error)]
pub enum SQLiteError {
    #[error("[WCustomError] WCustomError Happened: {0}")]
    CustomError(String),

    #[error("[R2d2Error] R2d2Error Happened: {0}")]
    R2d2Error(#[from] diesel::r2d2::Error),

    #[error("[DieselError] DieselError Happened: {0}")]
    DieselError(#[from] diesel::result::Error),

    #[error("[ioError] IoError happened: {0}")]
    IoError(#[from] std::io::Error),
}

use lazy_static::*;

pub use diesel_derives::*;

#[derive(Clone)]
pub struct DBPool {
    pool: Pool<ConnectionManager<SqliteConnection>>,
}

impl DBPool {
    pub fn new(path_str: &str) -> MyResult<DBPool> {
        let path: &Path = Path::new(path_str);
        let directory = path.parent().unwrap();
        std::fs::create_dir_all(directory).map_err(map_sqlite_error)?;

        let manager: ConnectionManager<SqliteConnection> = ConnectionManager::<SqliteConnection>::new(path.to_string_lossy());

        let pool = diesel::r2d2::Pool::builder()
            .connection_customizer(Box::new(ConnectionCustomizer {}))
            .build(manager).map_err(map_sqlite_error)?;
        let db = DBPool {
            pool
        };
        Ok(db)
    }
    pub fn connect(&self) -> MyResult<DBConn> {
        self.pool.get().map_err(map_sqlite_error)
    }
}


#[derive(Debug)]
struct ConnectionCustomizer {}

impl diesel::r2d2::CustomizeConnection<SqliteConnection, diesel::r2d2::Error>
for ConnectionCustomizer
{
    fn on_acquire(&self, connection: &mut SqliteConnection) -> Result<(), diesel::r2d2::Error> {
        let query = diesel::sql_query(
            r#"
			PRAGMA busy_timeout = 60000;
			PRAGMA journal_mode = WAL;
			PRAGMA synchronous = NORMAL;
			PRAGMA foreign_keys = ON;
		"#,
        );
        query
            .execute(connection)
            .map_err(diesel::r2d2::Error::QueryError)?;
        Ok(())
    }
}

lazy_static! {
    pub static ref IMCONNPOOL: Arc<DBPool> = Arc::new(DBPool::new("./test.sqlite").unwrap());
}

pub fn get_conn() -> MyResult<DBConn> {
    IMCONNPOOL.connect()
}

impl SQLiteError {
    pub fn new(st: &str) -> SQLiteError {
        SQLiteError::CustomError(st.to_string())
    }
}

pub fn map_sqlite_error<E: ToString>(e: E) -> SQLiteError {
    SQLiteError::CustomError(e.to_string())
}

table! {
    user_info(user_id) {
        user_id -> BigInt,
        name -> Text,
        icon -> Text,
        age -> Integer
    }
}


#[derive(Insertable, Queryable, AsChangeset, Clone, Debug, Default)]
#[diesel(table_name = user_info)]
#[diesel(primary_key(user_id))]
pub struct UserInfo {
    user_id: i64,
    name: String,
    icon: String,
    age: i32,
}

#[derive(AsChangeset, Clone, Debug, Default)]
#[diesel(table_name = user_info)]
#[diesel(primary_key(user_id))]
pub struct UserInfoChangest {
    user_id: Option<i64>,
    name: Option<String>,
    icon: Option<String>,
    age: Option<i32>,
}

fn main() {
    let conn = &mut *get_conn().unwrap();
    let user1 = UserInfo {
        user_id: 1,
        name: "111".to_string(),
        icon: "icon1.png".to_string(),
        age: 3,
    };
    let user2 = UserInfo {
        user_id: 2,
        name: "222".to_string(),
        icon: "icon2.png".to_string(),
        age: 5,
    };

    // 增
    diesel::insert_into(user_info::table())
        .values(vec![user1, user2])
        .execute(conn)
        .expect("Error inserting users");

    // 删
    use crate::user_info::dsl::{user_info, name};
    let filter = user_info.filter(name.eq("111"));
    diesel::delete(filter)
        .execute(conn)
        .expect("Error deleting user");

    // 改
    let update_user1 = UserInfo {
        name: "22".to_string(),
        ..Default::default()
    };
    let filter = user_info.filter(name.eq("22"));
    diesel::update(filter)
        .set(update_user1)
        .execute(conn)
        .expect("Error updating user");

    // 查
    let users: Vec<UserInfo> = user_info.load(conn).expect("Error loading users");
    println!("users: {:?}", users);

    // 事务
    conn.transaction::<(), diesel::result::Error, _>(|conn_trans| {
        let user3 = UserInfo {
            user_id: 3,
            name: "事务x".to_string(),
            icon: "icon1.png".to_string(),
            age: 3,
        };
        let user4 = UserInfo {
            user_id: 4,
            name: "事务y".to_string(),
            icon: "icon1.png".to_string(),
            age: 3,
        };
        diesel::insert_into(user_info::table())
            .values(user3)
            .execute(conn_trans)
            .expect("Error inserting user3");
        diesel::insert_into(user_info::table())
            .values(user4)
            .execute(conn_trans)
            .expect("Error inserting user4");
        Ok(())
    }).expect("Error in transaction");
}