bones_core/db/migrations/
mod.rs

1//! `SQLite` schema migrations for the disposable projection database.
2
3use super::schema;
4use rusqlite::{Connection, types::Type};
5
6/// Latest schema version understood by this binary.
7pub const LATEST_SCHEMA_VERSION: u32 = 2;
8
9const MIGRATIONS: &[(u32, &str)] = &[(1, schema::MIGRATION_V1_SQL), (2, schema::MIGRATION_V2_SQL)];
10
11/// Read `PRAGMA user_version` and convert it to a Rust `u32`.
12///
13/// # Errors
14///
15/// Returns an error if querying `SQLite` fails or the version value cannot be
16/// represented as `u32`.
17pub fn current_schema_version(conn: &Connection) -> rusqlite::Result<u32> {
18    let version: i64 = conn.pragma_query_value(None, "user_version", |row| row.get(0))?;
19    u32::try_from(version).map_err(|error| {
20        rusqlite::Error::FromSqlConversionFailure(0, Type::Integer, Box::new(error))
21    })
22}
23
24/// Apply all pending migrations in ascending order.
25///
26/// Migrations are idempotent because:
27/// - each migration only runs when `migration.version > user_version`
28/// - migration SQL itself uses `IF NOT EXISTS` for DDL safety
29///
30/// # Errors
31///
32/// Returns an error if any migration fails.
33pub fn migrate(conn: &mut Connection) -> rusqlite::Result<u32> {
34    let mut current = current_schema_version(conn)?;
35
36    for (version, sql) in MIGRATIONS {
37        if *version <= current {
38            continue;
39        }
40
41        let tx = conn.transaction()?;
42        tx.execute_batch(sql)?;
43        tx.pragma_update(None, "user_version", i64::from(*version))?;
44        tx.execute(
45            "UPDATE projection_meta SET schema_version = ?1 WHERE id = 1",
46            [i64::from(*version)],
47        )?;
48        tx.commit()?;
49        current = *version;
50    }
51
52    Ok(current)
53}
54
55#[cfg(test)]
56mod tests {
57    use super::{LATEST_SCHEMA_VERSION, current_schema_version, migrate};
58    use crate::db::schema;
59    use rusqlite::{Connection, params};
60
61    fn sqlite_object_exists(
62        conn: &Connection,
63        object_type: &str,
64        object_name: &str,
65    ) -> rusqlite::Result<bool> {
66        conn.query_row(
67            "SELECT EXISTS(
68                SELECT 1
69                FROM sqlite_master
70                WHERE type = ?1 AND name = ?2
71            )",
72            params![object_type, object_name],
73            |row| row.get(0),
74        )
75    }
76
77    #[test]
78    fn migrate_empty_db_to_latest() -> rusqlite::Result<()> {
79        let mut conn = Connection::open_in_memory()?;
80
81        let applied = migrate(&mut conn)?;
82        assert_eq!(applied, LATEST_SCHEMA_VERSION);
83        assert_eq!(current_schema_version(&conn)?, LATEST_SCHEMA_VERSION);
84
85        assert!(sqlite_object_exists(&conn, "table", "items")?);
86        assert!(sqlite_object_exists(&conn, "table", "item_labels")?);
87        assert!(sqlite_object_exists(&conn, "table", "item_assignees")?);
88        assert!(sqlite_object_exists(&conn, "table", "item_dependencies")?);
89        assert!(sqlite_object_exists(&conn, "table", "item_comments")?);
90        assert!(sqlite_object_exists(&conn, "table", "event_redactions")?);
91        assert!(sqlite_object_exists(&conn, "table", "projection_meta")?);
92        assert!(sqlite_object_exists(&conn, "table", "items_fts")?);
93
94        for index in schema::REQUIRED_INDEXES {
95            assert!(
96                sqlite_object_exists(&conn, "index", index)?,
97                "missing expected index {index}"
98            );
99        }
100
101        Ok(())
102    }
103
104    #[test]
105    fn migrate_is_idempotent() -> rusqlite::Result<()> {
106        let mut conn = Connection::open_in_memory()?;
107
108        assert_eq!(migrate(&mut conn)?, LATEST_SCHEMA_VERSION);
109        assert_eq!(migrate(&mut conn)?, LATEST_SCHEMA_VERSION);
110
111        let meta_rows: i64 =
112            conn.query_row("SELECT COUNT(*) FROM projection_meta", [], |row| row.get(0))?;
113        assert_eq!(meta_rows, 1);
114
115        let schema_version: i64 = conn.query_row(
116            "SELECT schema_version FROM projection_meta WHERE id = 1",
117            [],
118            |row| row.get(0),
119        )?;
120        assert_eq!(schema_version, i64::from(LATEST_SCHEMA_VERSION));
121
122        Ok(())
123    }
124
125    #[test]
126    fn migrate_upgrades_from_v1_and_backfills_fts() -> rusqlite::Result<()> {
127        let mut conn = Connection::open_in_memory()?;
128
129        conn.execute_batch(schema::MIGRATION_V1_SQL)?;
130        conn.pragma_update(None, "user_version", 1_i64)?;
131        conn.execute(
132            "INSERT INTO items (
133                item_id,
134                title,
135                description,
136                kind,
137                state,
138                urgency,
139                is_deleted,
140                search_labels,
141                created_at_us,
142                updated_at_us
143            ) VALUES (
144                'bn-auth01',
145                'Auth timeout in worker sync',
146                'Retries fail after 30 seconds',
147                'task',
148                'open',
149                'urgent',
150                0,
151                'auth backend',
152                1,
153                2
154            )",
155            [],
156        )?;
157
158        let applied = migrate(&mut conn)?;
159        assert_eq!(applied, LATEST_SCHEMA_VERSION);
160
161        let fts_hits: i64 = conn.query_row(
162            "SELECT COUNT(*)
163             FROM items_fts
164             WHERE items_fts MATCH 'auth'",
165            [],
166            |row| row.get(0),
167        )?;
168        assert_eq!(fts_hits, 1);
169
170        let projected_version: i64 = conn.query_row(
171            "SELECT schema_version FROM projection_meta WHERE id = 1",
172            [],
173            |row| row.get(0),
174        )?;
175        assert_eq!(projected_version, i64::from(LATEST_SCHEMA_VERSION));
176
177        Ok(())
178    }
179}
bones_core/db/migrations/mod.rs

bones_core/db/migrations/
mod.rs
