use std::path::PathBuf;
use sqltrail_cli::history::{MigrationFile, compute_checksum};
fn test_database_url() -> Option<String> {
std::env::var("SQLTRAIL_TEST_DATABASE_URL").ok()
}
#[tokio::test]
async fn introspection_reads_comments_indexes_and_functions() -> anyhow::Result<()> {
let Some(url) = test_database_url() else {
return Ok(());
};
let client = sqltrail_cli::database::connect(&url).await?;
for row in client
.query(
"SELECT nspname FROM pg_namespace WHERE nspname LIKE 'sqltrail_it_%'",
&[],
)
.await?
{
let stale: String = row.get(0);
if stale
.strip_prefix("sqltrail_it_")
.is_some_and(|suffix| suffix.chars().all(|ch| ch.is_ascii_digit()))
{
client
.batch_execute(&format!("DROP SCHEMA \"{stale}\" CASCADE"))
.await?;
}
}
let schema = format!("sqltrail_it_{}", std::process::id());
let setup = format!(
"CREATE SCHEMA \"{schema}\";
CREATE TABLE \"{schema}\".\"users\" (\"id\" bigint PRIMARY KEY, \"email\" text);
COMMENT ON TABLE \"{schema}\".\"users\" IS '用户表';
COMMENT ON COLUMN \"{schema}\".\"users\".\"email\" IS '邮箱';
CREATE INDEX \"users_email_idx\" ON \"{schema}\".\"users\" (\"email\");
CREATE FUNCTION \"{schema}\".\"answer\"() RETURNS integer LANGUAGE sql IMMUTABLE AS $$ SELECT 42 $$;
COMMENT ON FUNCTION \"{schema}\".\"answer\"() IS '答案';"
);
client.batch_execute(&setup).await?;
let result = async {
let model = sqltrail_cli::introspection::introspect(&client).await?;
let table = model
.tables
.iter()
.find(|table| table.schema == schema && table.name == "users")
.ok_or_else(|| anyhow::anyhow!("未读取到测试表"))?;
anyhow::ensure!(table.comment.as_deref() == Some("用户表"), "表注释不一致");
anyhow::ensure!(
table.columns[1].comment.as_deref() == Some("邮箱"),
"字段注释不一致"
);
anyhow::ensure!(
table.indexes[0].name.as_deref() == Some("users_email_idx"),
"索引读取不一致"
);
let function = model
.functions
.iter()
.find(|function| function.schema == schema && function.name == "answer")
.ok_or_else(|| anyhow::anyhow!("未读取到测试函数"))?;
anyhow::ensure!(
function.comment.as_deref() == Some("答案"),
"函数注释不一致"
);
Ok::<(), anyhow::Error>(())
}
.await;
client
.batch_execute(&format!("DROP SCHEMA \"{schema}\" CASCADE"))
.await?;
let row = client
.query_one("SELECT to_regnamespace($1)::text", &[&schema])
.await?;
let remaining_schema: Option<String> = row.get(0);
anyhow::ensure!(remaining_schema.is_none(), "测试 schema 清理失败");
result
}
#[tokio::test]
async fn failed_transaction_does_not_leave_schema_changes() -> anyhow::Result<()> {
let Some(url) = test_database_url() else {
return Ok(());
};
let mut client = sqltrail_cli::database::connect(&url).await?;
let history_existed = sqltrail_cli::history::history_table_exists(&client).await?;
sqltrail_cli::history::ensure_history_table(&client).await?;
let table = format!("sqltrail_atomic_{}", std::process::id());
let sql = format!("CREATE TABLE public.\"{table}\" (id bigint); THIS IS NOT VALID SQL;");
let file = MigrationFile {
version: 9_000_000 + i64::from(std::process::id()),
name: "atomic_failure".into(),
path: PathBuf::from("atomic_failure.sql"),
checksum: compute_checksum(&sql),
sql,
};
let apply_failed = sqltrail_cli::history::apply(&mut client, &[file], false)
.await
.is_err();
let qualified = format!("public.{table}");
let row = client
.query_one("SELECT to_regclass($1)::text", &[&qualified])
.await?;
let relation: Option<String> = row.get(0);
if !history_existed {
client
.batch_execute("DROP TABLE public._sqltrail_migrations")
.await?;
}
anyhow::ensure!(
sqltrail_cli::history::history_table_exists(&client).await? == history_existed,
"迁移历史表状态没有恢复"
);
assert!(apply_failed, "包含非法 SQL 的迁移必须失败");
assert!(relation.is_none(), "失败事务不应留下已创建的表");
Ok(())
}