use std::collections::BTreeMap;
use std::path::{Path, PathBuf};
use std::time::Instant;
use anyhow::{anyhow, bail};
use sha2::{Digest, Sha256};
use tokio_postgres::Client;
pub const HISTORY_TABLE: &str = "public._sqltrail_migrations";
const APPLY_LOCK_KEY: i64 = 0x4349_505F_4D49_4752;
pub fn compute_checksum(content: &str) -> String {
let mut hasher = Sha256::new();
hasher.update(content.as_bytes());
hex::encode(hasher.finalize())
}
pub async fn ensure_history_table(client: &Client) -> anyhow::Result<()> {
let sql = format!(
"CREATE TABLE IF NOT EXISTS {HISTORY_TABLE} ( \
version BIGINT PRIMARY KEY, \
name TEXT NOT NULL, \
checksum TEXT NOT NULL, \
applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), \
execution_ms BIGINT NOT NULL \
)"
);
client
.batch_execute(&sql)
.await
.map_err(|e| anyhow!("创建迁移历史表失败: {e}"))?;
Ok(())
}
pub async fn applied_versions(client: &Client) -> anyhow::Result<BTreeMap<i64, String>> {
let rows = client
.query(
&format!("SELECT version, checksum FROM {HISTORY_TABLE}"),
&[],
)
.await
.map_err(|e| anyhow!("读取迁移历史失败: {e}"))?;
Ok(rows.iter().map(|r| (r.get(0), r.get(1))).collect())
}
#[derive(Debug)]
pub struct MigrationFile {
pub version: i64,
pub name: String,
pub path: PathBuf,
pub checksum: String,
pub sql: String,
}
pub fn read_migrations(dir: &Path) -> anyhow::Result<Vec<MigrationFile>> {
let mut files = Vec::new();
if !dir.exists() {
return Ok(files);
}
for entry in std::fs::read_dir(dir)? {
let entry = entry?;
let path = entry.path();
if !path.is_file() || path.extension().map(|e| e != "sql").unwrap_or(true) {
continue;
}
let file_name = path
.file_name()
.and_then(|n| n.to_str())
.ok_or_else(|| anyhow!("非法文件名: {}", path.display()))?;
let Some((num_part, rest)) = file_name.split_once('_') else {
continue;
};
let Ok(version) = num_part.parse::<i64>() else {
continue;
};
let name = rest.trim_end_matches(".sql").to_string();
let sql = std::fs::read_to_string(&path)?;
files.push(MigrationFile {
version,
name,
path,
checksum: compute_checksum(&sql),
sql,
});
}
files.sort_by_key(|f| f.version);
for w in files.windows(2) {
if w[0].version == w[1].version {
bail!(
"存在重复版本号:{} 与 {}",
w[0].path.display(),
w[1].path.display()
);
}
}
Ok(files)
}
#[derive(Debug)]
pub struct AppliedMigration {
pub version: i64,
pub name: String,
pub execution_ms: u128,
}
#[derive(Debug, Default)]
pub struct ApplyReport {
pub applied: Vec<AppliedMigration>,
}
pub async fn apply(
client: &mut Client,
files: &[MigrationFile],
allow_destructive: bool,
) -> anyhow::Result<ApplyReport> {
client
.query_one("SELECT pg_advisory_lock($1)", &[&APPLY_LOCK_KEY])
.await
.map_err(|e| anyhow!("获取迁移执行锁失败: {e}"))?;
let result = apply_locked(client, files, allow_destructive).await;
let unlock_result = client
.query_one("SELECT pg_advisory_unlock($1)", &[&APPLY_LOCK_KEY])
.await
.map_err(|e| anyhow!("释放迁移执行锁失败: {e}"));
match (result, unlock_result) {
(Err(e), _) => Err(e),
(Ok(_), Err(e)) => Err(e),
(Ok(report), Ok(_)) => Ok(report),
}
}
pub async fn history_table_exists(client: &Client) -> anyhow::Result<bool> {
let row = client
.query_one("SELECT to_regclass($1)::text", &[&HISTORY_TABLE])
.await
.map_err(|e| anyhow!("检查迁移历史表失败: {e}"))?;
let table: Option<String> = row.get(0);
Ok(table.is_some())
}
async fn apply_locked(
client: &mut Client,
files: &[MigrationFile],
allow_destructive: bool,
) -> anyhow::Result<ApplyReport> {
let applied = applied_versions(client).await?;
let mut report = ApplyReport::default();
for file in files {
if let Some(recorded) = applied.get(&file.version) {
if recorded != &file.checksum {
bail!(
"迁移 {:06} {} 的 checksum 与历史记录不一致,文件可能已被修改!\n 历史记录: {}\n 当前文件: {}",
file.version,
file.name,
recorded,
file.checksum
);
}
continue;
}
if !allow_destructive && contains_marker(&file.sql, "-- @danger") {
bail!(
"迁移 {:06} {} 包含危险操作(删除表/字段、收紧类型、修改主键等)。\n请人工审核后使用 `sqltrail-cli apply --allow-destructive` 显式确认。",
file.version,
file.name
);
}
let no_transaction = contains_marker(&file.sql, "-- @no-transaction");
let start = Instant::now();
if no_transaction {
client
.batch_execute(&file.sql)
.await
.map_err(|e| anyhow!("执行迁移 {} 失败: {e}", file.path.display()))?;
let execution_ms = start.elapsed().as_millis();
client
.execute(
&format!(
"INSERT INTO {HISTORY_TABLE} (version, name, checksum, execution_ms) \
VALUES ($1, $2, $3, $4)"
),
&[
&file.version,
&file.name,
&file.checksum,
&(execution_ms as i64),
],
)
.await
.map_err(|e| anyhow!("记录迁移历史失败(版本 {}): {e}", file.version))?;
report.applied.push(AppliedMigration {
version: file.version,
name: file.name.clone(),
execution_ms,
});
} else {
let tx = client
.transaction()
.await
.map_err(|e| anyhow!("开启事务失败({}): {e}", file.path.display()))?;
tx.batch_execute(&file.sql)
.await
.map_err(|e| anyhow!("执行迁移 {} 失败(事务已回滚): {e}", file.path.display()))?;
let execution_ms = start.elapsed().as_millis();
tx.execute(
&format!(
"INSERT INTO {HISTORY_TABLE} (version, name, checksum, execution_ms) \
VALUES ($1, $2, $3, $4)"
),
&[
&file.version,
&file.name,
&file.checksum,
&(execution_ms as i64),
],
)
.await
.map_err(|e| anyhow!("记录迁移历史失败(版本 {},事务已回滚): {e}", file.version))?;
tx.commit()
.await
.map_err(|e| anyhow!("提交事务失败({}): {e}", file.path.display()))?;
report.applied.push(AppliedMigration {
version: file.version,
name: file.name.clone(),
execution_ms,
});
}
}
Ok(report)
}
fn contains_marker(sql: &str, marker: &str) -> bool {
sql.lines()
.any(|line| line.trim_start().starts_with(marker))
}
#[cfg(test)]
mod tests {
use super::*;
#[test]
fn checksum稳定且可复现() {
let c1 = compute_checksum("CREATE TABLE t ();\n");
let c2 = compute_checksum("CREATE TABLE t ();\n");
let c3 = compute_checksum("CREATE TABLE t ();");
assert_eq!(c1, c2);
assert_eq!(c1.len(), 64);
assert_ne!(c1, c3, "内容变化必须导致 checksum 变化");
}
#[test]
fn 危险标记识别() {
let sql = "-- @danger 危险操作\nDROP TABLE t;";
assert!(contains_marker(sql, "-- @danger"));
let safe = "DROP TABLE t;";
assert!(!contains_marker(safe, "-- @danger"));
}
}