store-project/src-tauri/src/utils.rs

use std::fs;

use sea_orm::{ConnectionTrait, DatabaseConnection, Statement, TransactionTrait};
use tauri::{Manager, path::BaseDirectory};

pub async fn migration_db(app: &tauri::App, conn: &DatabaseConnection) -> anyhow::Result<()> {
    let sql_path = app
        .path()
        .resolve("resources/table_struct.sql", BaseDirectory::Resource)?;
    conn.execute(Statement::from_string(
        conn.get_database_backend(),
        "CREATE TABLE IF NOT EXISTS seaql_migrations (
            version VARCHAR(255) PRIMARY KEY,
            applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )"
        .to_owned(),
    ))
    .await?;
    let applied: Vec<String> = conn
        .query_all(Statement::from_string(
            conn.get_database_backend(),
            "SELECT version FROM seaql_migrations".to_owned(),
        ))
        .await?
        .into_iter()
        .filter_map(|row| row.try_get("", "version").ok())
        .collect();

    if let Some(sql_path_stem) = sql_path.file_stem() {
        if let Some(version) = sql_path_stem.to_str() {
            if !applied.contains(&version.to_string()) {
                let sql_str = fs::read_to_string(&sql_path)?;
                let tx = conn.begin().await?;
                tx.execute(Statement::from_string(conn.get_database_backend(), sql_str))
                    .await?;
                tx.execute(Statement::from_string(
                    conn.get_database_backend(),
                    format!(
                        "INSERT INTO seaql_migrations (version) VALUES ('{}')",
                        version
                    ),
                ))
                .await?;
                tx.commit().await?;
                println!("✅ Applied migration: {}", version);
            }
        }
    }

    Ok(())
}