mixtape_tools/sqlite/migration/
mod.rs

1//! Schema migration tools for SQLite databases
2//!
3//! This module provides tools for managing database schema migrations that are
4//! stored within the database itself, enabling agents to evolve schemas over time.
5//!
6//! # Design
7//!
8//! Migrations are stored in a `_schema_migrations` table within each database:
9//! - `version`: Timestamp-based unique identifier (auto-generated)
10//! - `name`: Human-readable description
11//! - `sql`: The DDL to execute
12//! - `applied_at`: When the migration was applied (NULL = pending)
13//! - `checksum`: SHA256 of the SQL for integrity
14//!
15//! # Tools
16//!
17//! - [`AddMigrationTool`] - Store a new pending migration
18//! - [`RunMigrationsTool`] - Apply all pending migrations in order
19//! - [`ListMigrationsTool`] - List migrations with optional status filter
20//! - [`GetMigrationTool`] - Get details of a specific migration
21//! - [`RemoveMigrationTool`] - Remove a pending migration before it's applied
22
23mod add;
24pub mod export;
25mod get;
26pub mod import;
27mod list;
28mod remove;
29mod run;
30pub mod types;
31
32pub use add::AddMigrationTool;
33pub use export::ExportMigrationsTool;
34pub use get::GetMigrationTool;
35pub use import::ImportMigrationsTool;
36pub use list::ListMigrationsTool;
37pub use remove::RemoveMigrationTool;
38pub use run::RunMigrationsTool;
39pub use types::{Migration, MigrationStatusFilter};
40
41use crate::sqlite::error::SqliteToolError;
42use chrono::Utc;
43use rusqlite::Connection;
44use sha2::{Digest, Sha256};
45
46/// The name of the migrations table
47pub const MIGRATIONS_TABLE: &str = "_schema_migrations";
48
49/// Ensures the migrations table exists in the database
50pub fn ensure_migrations_table(conn: &Connection) -> Result<(), SqliteToolError> {
51    conn.execute_batch(&format!(
52        r#"
53        CREATE TABLE IF NOT EXISTS {MIGRATIONS_TABLE} (
54            version TEXT PRIMARY KEY,
55            name TEXT NOT NULL,
56            sql TEXT NOT NULL,
57            applied_at TEXT,
58            checksum TEXT NOT NULL
59        );
60        "#
61    ))?;
62    Ok(())
63}
64
65/// Generates a version string based on current timestamp
66///
67/// Format: YYYYMMDD_HHMMSS_microseconds (e.g., "20240115_143052_123456")
68pub fn generate_version() -> String {
69    Utc::now().format("%Y%m%d_%H%M%S_%6f").to_string()
70}
71
72/// Computes SHA256 checksum of SQL content
73pub fn compute_checksum(sql: &str) -> String {
74    let mut hasher = Sha256::new();
75    hasher.update(sql.as_bytes());
76    let result = hasher.finalize();
77    hex::encode(result)
78}
79
80#[cfg(test)]
81mod tests {
82    use super::*;
83
84    #[test]
85    fn test_generate_version_format() {
86        let version = generate_version();
87        // Should be format: YYYYMMDD_HHMMSS_UUUUUU
88        assert_eq!(version.len(), 22);
89        assert_eq!(&version[8..9], "_");
90        assert_eq!(&version[15..16], "_");
91    }
92
93    #[test]
94    fn test_generate_version_uniqueness() {
95        let v1 = generate_version();
96        std::thread::sleep(std::time::Duration::from_micros(10));
97        let v2 = generate_version();
98        assert_ne!(v1, v2);
99    }
100
101    #[test]
102    fn test_compute_checksum() {
103        let sql = "CREATE TABLE users (id INTEGER PRIMARY KEY);";
104        let checksum = compute_checksum(sql);
105        // SHA256 produces 64 hex characters
106        assert_eq!(checksum.len(), 64);
107
108        // Same input produces same checksum
109        assert_eq!(checksum, compute_checksum(sql));
110
111        // Different input produces different checksum
112        let other = compute_checksum("CREATE TABLE posts (id INTEGER PRIMARY KEY);");
113        assert_ne!(checksum, other);
114    }
115
116    #[test]
117    fn test_ensure_migrations_table() {
118        let conn = Connection::open_in_memory().unwrap();
119        ensure_migrations_table(&conn).unwrap();
120
121        // Table should exist
122        let count: i64 = conn
123            .query_row(
124                "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=?",
125                [MIGRATIONS_TABLE],
126                |row| row.get(0),
127            )
128            .unwrap();
129        assert_eq!(count, 1);
130
131        // Calling again should be idempotent
132        ensure_migrations_table(&conn).unwrap();
133    }
134}
mixtape_tools/sqlite/migration/mod.rs

mixtape_tools/sqlite/migration/
mod.rs
