use std::path::{Path, PathBuf};
use rusqlite::{Connection, params};
use crate::infra::error::{CriewError, ErrorCode, Result};
pub const CURRENT_SCHEMA_VERSION: i64 = 4;
const CREATE_SCHEMA_VERSION_TABLE: &str = r#"
CREATE TABLE IF NOT EXISTS schema_version (
version INTEGER PRIMARY KEY,
description TEXT NOT NULL,
applied_at TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ', 'now'))
);
"#;
struct Migration {
version: i64,
description: &'static str,
sql: &'static str,
}
const MIGRATIONS: &[Migration] = &[
Migration {
version: 1,
description: "initial schema",
sql: include_str!("../../migrations/0001_init.sql"),
},
Migration {
version: 2,
description: "patch workflow schema",
sql: include_str!("../../migrations/0002_patch_workflow.sql"),
},
Migration {
version: 3,
description: "reply send workflow schema",
sql: include_str!("../../migrations/0003_reply_send_workflow.sql"),
},
Migration {
version: 4,
description: "thread ordering uses mail date",
sql: include_str!("../../migrations/0004_thread_sort_by_mail_date.sql"),
},
];
#[derive(Debug, Clone)]
pub struct DatabaseState {
pub path: PathBuf,
pub schema_version: i64,
pub created: bool,
pub applied_migrations: Vec<i64>,
}
pub fn initialize(path: &Path) -> Result<DatabaseState> {
let created = !path.exists();
let mut connection = Connection::open(path).map_err(|error| {
CriewError::with_source(
ErrorCode::Database,
format!("failed to open sqlite database {}", path.display()),
error,
)
})?;
connection
.execute_batch("PRAGMA foreign_keys = ON;")
.map_err(|error| {
CriewError::with_source(
ErrorCode::Database,
"failed to enable sqlite foreign key support",
error,
)
})?;
connection
.execute_batch(CREATE_SCHEMA_VERSION_TABLE)
.map_err(|error| {
CriewError::with_source(
ErrorCode::Database,
"failed to create schema_version table",
error,
)
})?;
let current = current_version(&connection)?;
let mut applied = Vec::new();
for migration in MIGRATIONS
.iter()
.filter(|migration| migration.version > current)
{
let tx = connection.transaction().map_err(|error| {
CriewError::with_source(
ErrorCode::Database,
"failed to open migration transaction",
error,
)
})?;
tx.execute_batch(migration.sql).map_err(|error| {
CriewError::with_source(
ErrorCode::Database,
format!("failed to run migration {}", migration.version),
error,
)
})?;
tx.execute(
"INSERT INTO schema_version(version, description) VALUES (?1, ?2)",
params![migration.version, migration.description],
)
.map_err(|error| {
CriewError::with_source(
ErrorCode::Database,
format!("failed to register migration {}", migration.version),
error,
)
})?;
tx.commit().map_err(|error| {
CriewError::with_source(
ErrorCode::Database,
format!("failed to commit migration {}", migration.version),
error,
)
})?;
applied.push(migration.version);
}
let schema_version = current_version(&connection)?;
Ok(DatabaseState {
path: path.to_path_buf(),
schema_version,
created,
applied_migrations: applied,
})
}
fn current_version(connection: &Connection) -> Result<i64> {
connection
.query_row(
"SELECT COALESCE(MAX(version), 0) FROM schema_version",
[],
|row| row.get::<_, i64>(0),
)
.map_err(|error| {
CriewError::with_source(ErrorCode::Database, "failed to query schema version", error)
})
}
#[cfg(test)]
mod tests {
use std::fs;
use std::path::PathBuf;
use std::time::{SystemTime, UNIX_EPOCH};
use rusqlite::Connection;
use crate::infra::error::ErrorCode;
use super::{CURRENT_SCHEMA_VERSION, initialize};
fn temp_dir(label: &str) -> PathBuf {
let nonce = SystemTime::now()
.duration_since(UNIX_EPOCH)
.expect("system time")
.as_nanos();
let path = std::env::temp_dir().join(format!("criew-{label}-{nonce}"));
fs::create_dir_all(&path).expect("create temp dir");
path
}
#[test]
fn initialize_runs_initial_migration() {
let root = temp_dir("db-init");
let db_path = root.join("criew.db");
let state = initialize(&db_path).expect("initialize db");
assert!(state.created);
assert_eq!(state.schema_version, CURRENT_SCHEMA_VERSION);
assert_eq!(
state.applied_migrations,
vec![1, 2, 3, CURRENT_SCHEMA_VERSION]
);
let connection = Connection::open(&db_path).expect("open sqlite");
let version = connection
.query_row("SELECT MAX(version) FROM schema_version", [], |row| {
row.get::<_, i64>(0)
})
.expect("query version");
assert_eq!(version, CURRENT_SCHEMA_VERSION);
let _ = fs::remove_dir_all(root);
}
#[test]
fn initialize_is_idempotent_for_existing_database() {
let root = temp_dir("db-reinitialize");
let db_path = root.join("criew.db");
let first = initialize(&db_path).expect("initialize db");
let second = initialize(&db_path).expect("reinitialize db");
assert!(first.created);
assert_eq!(second.path, db_path);
assert!(!second.created);
assert_eq!(second.schema_version, CURRENT_SCHEMA_VERSION);
assert!(second.applied_migrations.is_empty());
let _ = fs::remove_dir_all(root);
}
#[test]
fn initialize_reports_missing_parent_directory() {
let root = temp_dir("db-missing-parent");
let db_path = root.join("missing").join("criew.db");
let error = initialize(&db_path).expect_err("missing parent directory should fail");
assert_eq!(error.code(), ErrorCode::Database);
assert!(error.to_string().contains("failed to open sqlite database"));
let _ = fs::remove_dir_all(root);
}
#[test]
fn initialize_reports_schema_version_table_creation_conflicts() {
let root = temp_dir("db-schema-version-conflict");
let db_path = root.join("criew.db");
let connection = Connection::open(&db_path).expect("open sqlite");
connection
.execute("CREATE VIEW schema_version AS SELECT 1 AS version", [])
.expect("create conflicting view");
drop(connection);
let error = initialize(&db_path).expect_err("schema_version conflict should fail");
assert_eq!(error.code(), ErrorCode::Database);
assert!(error.to_string().contains("schema") || error.to_string().contains("migration"));
let _ = fs::remove_dir_all(root);
}
#[test]
fn initialize_reports_schema_version_query_failure_for_invalid_table_shape() {
let root = temp_dir("db-schema-version-shape");
let db_path = root.join("criew.db");
let connection = Connection::open(&db_path).expect("open sqlite");
connection
.execute(
"CREATE TABLE schema_version (description TEXT NOT NULL)",
[],
)
.expect("create malformed schema_version");
drop(connection);
let error = initialize(&db_path).expect_err("invalid schema_version shape should fail");
assert_eq!(error.code(), ErrorCode::Database);
assert!(error.to_string().contains("failed to query schema version"));
let _ = fs::remove_dir_all(root);
}
#[test]
fn initialize_reports_migration_and_registration_failures() {
let root = temp_dir("db-migration-failures");
let conflicting_db_path = root.join("migration-sql.db");
let connection = Connection::open(&conflicting_db_path).expect("open conflicting sqlite");
connection
.execute(
"CREATE TABLE schema_version (version INTEGER PRIMARY KEY, description TEXT NOT NULL, applied_at TEXT NOT NULL DEFAULT '')",
[],
)
.expect("create schema_version");
connection
.execute("CREATE VIEW mail AS SELECT 1 AS id", [])
.expect("create conflicting mail view");
drop(connection);
let migration_error =
initialize(&conflicting_db_path).expect_err("migration SQL conflict should fail");
assert_eq!(migration_error.code(), ErrorCode::Database);
assert!(
migration_error
.to_string()
.contains("failed to run migration 1")
);
let missing_column_db_path = root.join("migration-register.db");
let connection = Connection::open(&missing_column_db_path).expect("open sqlite");
connection
.execute(
"CREATE TABLE schema_version (version INTEGER PRIMARY KEY)",
[],
)
.expect("create truncated schema_version");
drop(connection);
let register_error =
initialize(&missing_column_db_path).expect_err("migration registration should fail");
assert_eq!(register_error.code(), ErrorCode::Database);
assert!(
register_error
.to_string()
.contains("failed to register migration 1")
);
let _ = fs::remove_dir_all(root);
}
}