1//! Versioned schema migrations for the task database.
2//!
3//! Each migration has up/down SQL and optional post-hook functions that run
4//! inside the same transaction. Schema version is tracked via SQLite's built-in
5//! `PRAGMA user_version`.
6
7use anyhow::{bail, Context, Result};
8use rusqlite::Connection;
9
10/// A single schema migration step.
11struct Migration {
12 up_sql: &'static str,
13 down_sql: &'static str,
14 post_hook_up: Option<fn(&rusqlite::Transaction) -> Result<()>>,
15 post_hook_down: Option<fn(&rusqlite::Transaction) -> Result<()>>,
16}
17
18/// All migrations in order. The array index is the version the database will
19/// be at *after* the migration runs (1-indexed: migration 0 brings the DB to
20/// version 1).
21static MIGRATIONS: &[Migration] = &[
22 // 0 → 1: initial schema
23 Migration {
24 up_sql: include_str!("migrations/0001_initial_schema.up.sql"),
25 down_sql: include_str!("migrations/0001_initial_schema.down.sql"),
26 post_hook_up: None,
27 post_hook_down: None,
28 },
29 // 1 → 2: add effort column (integer-backed, default medium)
30 Migration {
31 up_sql: include_str!("migrations/0002_add_effort.up.sql"),
32 down_sql: include_str!("migrations/0002_add_effort.down.sql"),
33 post_hook_up: None,
34 post_hook_down: None,
35 },
36];
37
38/// Read the current schema version from the database.
39fn get_version(conn: &Connection) -> Result<u32> {
40 let v: u32 = conn.pragma_query_value(None, "user_version", |row| row.get(0))?;
41 Ok(v)
42}
43
44/// Set the schema version inside an open transaction.
45fn set_version(tx: &rusqlite::Transaction, version: u32) -> Result<()> {
46 // PRAGMA cannot be parameterised, but the value is a u32 we control.
47 tx.pragma_update(None, "user_version", version)?;
48 Ok(())
49}
50
51/// Apply all pending up-migrations to bring the database to the latest version.
52pub fn migrate_up(conn: &mut Connection) -> Result<()> {
53 let current = get_version(conn)?;
54 let latest = MIGRATIONS.len() as u32;
55
56 if current > latest {
57 bail!(
58 "database is at version {current} but this binary only knows up to {latest}; \
59 upgrade td or use a matching version"
60 );
61 }
62
63 for (idx, m) in MIGRATIONS.iter().enumerate().skip(current as usize) {
64 let target_version = (idx + 1) as u32;
65
66 let tx = conn
67 .transaction()
68 .context("failed to begin migration transaction")?;
69
70 if !m.up_sql.is_empty() {
71 tx.execute_batch(m.up_sql)
72 .with_context(|| format!("migration {target_version} up SQL failed"))?;
73 }
74
75 if let Some(hook) = m.post_hook_up {
76 hook(&tx)
77 .with_context(|| format!("migration {target_version} post-hook (up) failed"))?;
78 }
79
80 set_version(&tx, target_version)?;
81
82 tx.commit()
83 .with_context(|| format!("failed to commit migration {target_version}"))?;
84 }
85
86 Ok(())
87}
88
89/// Roll back migrations down to `target_version` (inclusive — the database
90/// will be at `target_version` when this returns).
91pub fn migrate_down(conn: &mut Connection, target_version: u32) -> Result<()> {
92 let current = get_version(conn)?;
93
94 if target_version >= current {
95 bail!("target version {target_version} is not below current version {current}");
96 }
97
98 if target_version > MIGRATIONS.len() as u32 {
99 bail!("target version {target_version} exceeds known migrations");
100 }
101
102 // Walk backwards: if we're at version 3 and want version 1, we undo
103 // migration index 2 (v3→v2) then index 1 (v2→v1).
104 for (idx, m) in MIGRATIONS
105 .iter()
106 .enumerate()
107 .rev()
108 .filter(|(i, _)| *i >= target_version as usize && *i < current as usize)
109 {
110 let from_version = (idx + 1) as u32;
111
112 let tx = conn
113 .transaction()
114 .context("failed to begin down-migration transaction")?;
115
116 if let Some(hook) = m.post_hook_down {
117 hook(&tx)
118 .with_context(|| format!("migration {from_version} post-hook (down) failed"))?;
119 }
120
121 if !m.down_sql.is_empty() {
122 tx.execute_batch(m.down_sql)
123 .with_context(|| format!("migration {from_version} down SQL failed"))?;
124 }
125
126 set_version(&tx, idx as u32)?;
127
128 tx.commit()
129 .with_context(|| format!("failed to commit down-migration {from_version}"))?;
130 }
131
132 Ok(())
133}
134
135#[cfg(test)]
136mod tests {
137 use super::*;
138
139 #[test]
140 fn migrate_up_from_empty() {
141 let mut conn = Connection::open_in_memory().unwrap();
142 migrate_up(&mut conn).unwrap();
143
144 let version = get_version(&conn).unwrap();
145 assert_eq!(version, MIGRATIONS.len() as u32);
146
147 // Verify tables exist by querying them.
148 conn.execute_batch("SELECT id FROM tasks LIMIT 0").unwrap();
149 conn.execute_batch("SELECT task_id FROM labels LIMIT 0")
150 .unwrap();
151 conn.execute_batch("SELECT task_id FROM blockers LIMIT 0")
152 .unwrap();
153 }
154
155 #[test]
156 fn migrate_up_is_idempotent() {
157 let mut conn = Connection::open_in_memory().unwrap();
158 migrate_up(&mut conn).unwrap();
159 // Running again should be a no-op, not an error.
160 migrate_up(&mut conn).unwrap();
161 assert_eq!(get_version(&conn).unwrap(), MIGRATIONS.len() as u32);
162 }
163
164 #[test]
165 fn migrate_down_to_zero() {
166 let mut conn = Connection::open_in_memory().unwrap();
167 migrate_up(&mut conn).unwrap();
168 migrate_down(&mut conn, 0).unwrap();
169 assert_eq!(get_version(&conn).unwrap(), 0);
170
171 // Tables should be gone.
172 let result = conn.execute_batch("SELECT id FROM tasks LIMIT 0");
173 assert!(result.is_err());
174 }
175
176 #[test]
177 fn rejects_future_version() {
178 let mut conn = Connection::open_in_memory().unwrap();
179 conn.pragma_update(None, "user_version", 999).unwrap();
180 let err = migrate_up(&mut conn).unwrap_err();
181 assert!(
182 err.to_string().contains("999"),
183 "error should mention the version: {err}"
184 );
185 }
186}