sqltrail-cli 0.1.0

Reusable PostgreSQL schema migration CLI and library: diff declared schema against a live database, generate versioned SQL, apply with a checksum-protected history
Documentation
//! sqltrail-cli 命令行入口。
//!
//! 子命令:
//! - `status`:连接状态、当前版本、待执行迁移;
//! - `inspect`:读取数据库实际结构并输出 JSON(可重定向为 --desired 期望结构文件);
//! - `plan`:比较期望结构与实际结构,输出差异与拟生成 SQL(不改库、不写文件);
//! - `generate <name>`:把差异写成新的版本化迁移 SQL 文件;
//! - `apply`:按版本顺序执行未执行的迁移文件。

mod cli;

use anyhow::{anyhow, bail};
use clap::{CommandFactory, Parser};
use sqltrail_cli::{database, generator, history, introspection};
use sqltrail_core::{SchemaModel, build_plan, compute_diff, validate};
use std::path::Path;

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    let cli = cli::Cli::parse();

    // 无子命令时打印帮助(便于新手了解用法)
    let Some(ref command) = cli.command else {
        let mut cmd = cli::Cli::command();
        let _ = cmd.print_help();
        println!();
        return Ok(());
    };

    match command {
        cli::Command::Status => cmd_status(&cli).await,
        cli::Command::Inspect => cmd_inspect(&cli).await,
        cli::Command::Plan { json } => cmd_plan(&cli, *json).await,
        cli::Command::Generate { name } => cmd_generate(&cli, name).await,
        cli::Command::Apply { allow_destructive } => cmd_apply(&cli, *allow_destructive).await,
    }
}

/// 获取数据库连接串(参数或环境变量 SQLTRAIL_DATABASE_URL)。
fn database_url(cli: &cli::Cli) -> anyhow::Result<String> {
    cli.database_url.clone().ok_or_else(|| {
        anyhow!(
            "缺少数据库连接串:请通过 --database-url 或环境变量 SQLTRAIL_DATABASE_URL 提供,\n\
             例如 postgres://user:password@localhost:5432/db"
        )
    })
}

/// 加载并校验期望结构。
///
/// - 指定 `--desired`:从 SchemaModel JSON 文件读取;
/// - 未指定:视为空库(全新建库场景)。
fn desired_model(path: Option<&Path>) -> anyhow::Result<SchemaModel> {
    let model = match path {
        Some(p) => {
            let content = std::fs::read_to_string(p)
                .map_err(|e| anyhow!("读取期望结构文件 {} 失败: {e}", p.display()))?;
            serde_json::from_str(&content)
                .map_err(|e| anyhow!("解析期望结构文件 {} 失败: {e}", p.display()))?
        }
        None => SchemaModel::default(),
    };
    // 统一规范:任何来源的期望结构都要通过同一套校验
    if let Err(errors) = validate(&model) {
        for e in &errors {
            eprintln!("期望结构校验错误: {e}");
        }
        bail!("期望结构校验失败,请先修复 schema 定义");
    }
    Ok(model)
}

/// `status`:显示数据库连接状态、当前迁移版本与待执行迁移。
async fn cmd_status(cli: &cli::Cli) -> anyhow::Result<()> {
    let url = database_url(cli)?;
    let client = database::connect(&url).await?;

    let row = client
        .query_one("SELECT current_database(), version()", &[])
        .await
        .map_err(|e| anyhow!("查询数据库信息失败: {e}"))?;
    let current_db: String = row.get(0);
    let server_version: String = row.get(1);

    println!("数据库: {current_db}");
    println!("PostgreSQL: {server_version}");

    let applied = if history::history_table_exists(&client).await? {
        history::applied_versions(&client).await?
    } else {
        Default::default()
    };
    let files = history::read_migrations(&cli.migrations_dir)?;

    if applied.is_empty() {
        println!("已执行迁移: 无");
    } else {
        println!(
            "已执行迁移: {}",
            applied
                .keys()
                .map(|v| v.to_string())
                .collect::<Vec<_>>()
                .join(", ")
        );
    }

    let pending: Vec<_> = files
        .iter()
        .filter(|f| !applied.contains_key(&f.version))
        .collect();
    if pending.is_empty() {
        println!("没有待执行的迁移。");
    } else {
        println!("待执行迁移:");
        for f in &pending {
            println!(
                "  {:06}  {}  (sha256: {}…)",
                f.version,
                f.name,
                &f.checksum[..f.checksum.len().min(12)]
            );
        }
    }
    Ok(())
}

/// `inspect`:读取数据库实际结构并以 JSON 输出。
async fn cmd_inspect(cli: &cli::Cli) -> anyhow::Result<()> {
    let url = database_url(cli)?;
    let client = database::connect(&url).await?;
    let model = introspection::introspect(&client).await?;
    println!("{}", serde_json::to_string_pretty(&model)?);
    Ok(())
}

/// `plan`:比较期望结构与实际结构,输出差异与拟生成 SQL(不修改任何东西)。
async fn cmd_plan(cli: &cli::Cli, json: bool) -> anyhow::Result<()> {
    let url = database_url(cli)?;
    let client = database::connect(&url).await?;

    let desired = desired_model(cli.desired.as_deref())?;
    let actual = introspection::introspect(&client).await?;
    let diff = compute_diff(&desired, &actual);
    let plan = build_plan(&diff);

    if json {
        println!("{}", serde_json::to_string_pretty(&plan)?);
        return Ok(());
    }

    println!(
        "期望表 {} 张,实际表 {} 张",
        desired.tables.len(),
        actual.tables.len()
    );
    if plan.is_empty() {
        println!("数据库结构与期望一致,无需迁移。");
        return Ok(());
    }

    println!(
        "迁移计划:共 {} 个操作(危险 {} 个,注意 {} 个)",
        plan.operations.len(),
        plan.danger_count(),
        plan.caution_count()
    );
    for (i, op) in plan.operations.iter().enumerate() {
        println!("[{:>3}] [{}] {}", i + 1, op.danger().label(), op.describe());
    }
    println!("\n-- 拟生成 SQL(仅供参考:未写入文件,也未修改数据库)--\n");
    println!("{}", generator::render_plan(&plan));
    Ok(())
}

/// `generate`:根据差异生成新的版本化迁移 SQL 文件。
async fn cmd_generate(cli: &cli::Cli, name: &str) -> anyhow::Result<()> {
    let url = database_url(cli)?;
    let client = database::connect(&url).await?;

    let desired = desired_model(cli.desired.as_deref())?;
    let actual = introspection::introspect(&client).await?;
    let diff = compute_diff(&desired, &actual);
    let plan = build_plan(&diff);

    if plan.is_empty() {
        println!("数据库结构与期望一致,无需生成迁移文件。");
        return Ok(());
    }

    // 确保迁移目录存在
    std::fs::create_dir_all(&cli.migrations_dir)?;
    let existing = history::read_migrations(&cli.migrations_dir)?;
    let next_version = existing.iter().map(|f| f.version).max().unwrap_or(0) + 1;
    let safe_name = sanitize_name(name);
    let file_name = format!("{next_version:06}_{safe_name}.sql");
    let path = cli.migrations_dir.join(&file_name);

    let mut sql = String::new();
    sql.push_str(&format!(
        "-- ============================================================\n\
         -- 迁移 {next_version:06}: {name}\n\
         -- 由 sqltrail-cli generate 生成,请人工审核后再执行\n\
         -- 审核命令: sqltrail-cli status / sqltrail-cli plan\n\
         -- 执行命令: sqltrail-cli apply\n\
         -- ============================================================\n\n"
    ));
    sql.push_str(&generator::render_plan(&plan));

    std::fs::write(&path, sql)?;
    println!("已生成迁移文件: {}", path.display());
    println!(
        "共 {} 个操作(危险 {} 个),请人工审核后执行:sqltrail-cli apply{}",
        plan.operations.len(),
        plan.danger_count(),
        if plan.danger_count() > 0 {
            " --allow-destructive"
        } else {
            ""
        }
    );
    Ok(())
}

/// `apply`:按版本顺序执行未执行的迁移文件。
async fn cmd_apply(cli: &cli::Cli, allow_destructive: bool) -> anyhow::Result<()> {
    let url = database_url(cli)?;
    let mut client = database::connect(&url).await?;

    history::ensure_history_table(&client).await?;
    let files = history::read_migrations(&cli.migrations_dir)?;
    if files.is_empty() {
        println!("{} 目录下没有迁移文件。", cli.migrations_dir.display());
        return Ok(());
    }

    let report = history::apply(&mut client, &files, allow_destructive).await?;
    if report.applied.is_empty() {
        println!("没有需要执行的迁移,数据库已是最新。");
    } else {
        println!("执行完成,共应用 {} 个迁移:", report.applied.len());
        for m in &report.applied {
            println!("  {:06}  {}  ({} ms)", m.version, m.name, m.execution_ms);
        }
        println!("提示:执行后再运行 `sqltrail-cli plan` 确认差异为空。");
    }
    Ok(())
}

/// 迁移名称清洗:只保留小写字母、数字、下划线。
fn sanitize_name(name: &str) -> String {
    let mut s: String = name
        .chars()
        .map(|c| {
            if c.is_ascii_alphanumeric() || c == '_' {
                c.to_ascii_lowercase()
            } else {
                '_'
            }
        })
        .collect();
    while s.starts_with('_') {
        s.remove(0);
    }
    while s.ends_with('_') {
        s.pop();
    }
    if s.is_empty() {
        s = "migration".to_string();
    }
    s
}