1//! Integration tests for the migration system.
2
3use assert_cmd::Command;
4use tempfile::TempDir;
5
6fn td() -> Command {
7 Command::cargo_bin("td").unwrap()
8}
9
10fn init_tmp() -> TempDir {
11 let tmp = TempDir::new().unwrap();
12 td().arg("init").current_dir(&tmp).assert().success();
13 tmp
14}
15
16#[test]
17fn fresh_init_sets_latest_version() {
18 let tmp = init_tmp();
19 let conn = rusqlite::Connection::open(tmp.path().join(".td/tasks.db")).unwrap();
20 let version: u32 = conn
21 .pragma_query_value(None, "user_version", |row| row.get(0))
22 .unwrap();
23 // Version should be 5 (migration 0001 + 0002 + 0003 + 0004 + 0005).
24 assert_eq!(version, 5);
25}
26
27#[test]
28fn legacy_db_is_migrated_on_open() {
29 let tmp = TempDir::new().unwrap();
30 let td_dir = tmp.path().join(".td");
31 std::fs::create_dir_all(&td_dir).unwrap();
32
33 // Create a v0 database with the old schema (no effort column).
34 let conn = rusqlite::Connection::open(td_dir.join("tasks.db")).unwrap();
35 conn.execute_batch(
36 "PRAGMA foreign_keys = OFF;
37 CREATE TABLE tasks (
38 id TEXT PRIMARY KEY,
39 title TEXT NOT NULL,
40 description TEXT DEFAULT '',
41 type TEXT DEFAULT 'task',
42 priority INTEGER DEFAULT 2,
43 status TEXT DEFAULT 'open',
44 parent TEXT DEFAULT '',
45 created TEXT NOT NULL,
46 updated TEXT NOT NULL
47 );
48 CREATE TABLE labels (
49 task_id TEXT, label TEXT,
50 PRIMARY KEY (task_id, label),
51 FOREIGN KEY (task_id) REFERENCES tasks(id)
52 );
53 CREATE TABLE blockers (
54 task_id TEXT, blocker_id TEXT,
55 PRIMARY KEY (task_id, blocker_id),
56 FOREIGN KEY (task_id) REFERENCES tasks(id)
57 );
58 INSERT INTO tasks (id, title, created, updated)
59 VALUES ('td-legacy', 'Old task', '2024-01-01T00:00:00Z', '2024-01-01T00:00:00Z');",
60 )
61 .unwrap();
62 drop(conn);
63
64 // Opening via td (list) should migrate and succeed.
65 td().args(["--json", "list"])
66 .current_dir(&tmp)
67 .assert()
68 .success();
69
70 // Verify the task survived migration and got default effort.
71 let out = td()
72 .args(["--json", "show", "td-legacy"])
73 .current_dir(&tmp)
74 .output()
75 .unwrap();
76 let v: serde_json::Value = serde_json::from_slice(&out.stdout).unwrap();
77 assert_eq!(v["title"].as_str().unwrap(), "Old task");
78 assert_eq!(v["effort"].as_i64().unwrap(), 2); // default medium
79
80 // Verify version is now latest.
81 let conn = rusqlite::Connection::open(td_dir.join("tasks.db")).unwrap();
82 let version: u32 = conn
83 .pragma_query_value(None, "user_version", |row| row.get(0))
84 .unwrap();
85 assert_eq!(version, 5);
86}
87
88#[test]
89fn effort_column_exists_after_init() {
90 let tmp = init_tmp();
91 let conn = rusqlite::Connection::open(tmp.path().join(".td/tasks.db")).unwrap();
92
93 // Verify the effort column is present by inserting a row that sets it.
94 conn.execute(
95 "INSERT INTO tasks (id, title, effort, created, updated) VALUES ('td-test', 'Test', 3, '2024-01-01T00:00:00Z', '2024-01-01T00:00:00Z')",
96 [],
97 )
98 .unwrap();
99
100 let effort: i32 = conn
101 .query_row("SELECT effort FROM tasks WHERE id = 'td-test'", [], |r| {
102 r.get(0)
103 })
104 .unwrap();
105 assert_eq!(effort, 3);
106}
107
108#[test]
109fn blocker_fk_rejects_nonexistent_blocker_id() {
110 let tmp = init_tmp();
111 let conn = rusqlite::Connection::open(tmp.path().join(".td/tasks.db")).unwrap();
112 conn.execute_batch("PRAGMA foreign_keys = ON").unwrap();
113
114 conn.execute(
115 "INSERT INTO tasks (id, title, created, updated) \
116 VALUES ('td-real', 'Real task', '2024-01-01T00:00:00Z', '2024-01-01T00:00:00Z')",
117 [],
118 )
119 .unwrap();
120
121 // Inserting a blocker that references a nonexistent task should fail.
122 let result = conn.execute(
123 "INSERT INTO blockers (task_id, blocker_id) VALUES ('td-real', 'td-ghost')",
124 [],
125 );
126 assert!(
127 result.is_err(),
128 "expected FK violation for nonexistent blocker_id"
129 );
130}
131
132#[test]
133fn labels_fk_cascades_on_task_delete() {
134 let tmp = init_tmp();
135 let conn = rusqlite::Connection::open(tmp.path().join(".td/tasks.db")).unwrap();
136 conn.execute_batch("PRAGMA foreign_keys = ON").unwrap();
137
138 conn.execute(
139 "INSERT INTO tasks (id, title, created, updated) \
140 VALUES ('td-labeled', 'Labeled task', '2024-01-01T00:00:00Z', '2024-01-01T00:00:00Z')",
141 [],
142 )
143 .unwrap();
144 conn.execute(
145 "INSERT INTO labels (task_id, label) VALUES ('td-labeled', 'urgent')",
146 [],
147 )
148 .unwrap();
149
150 conn.execute("DELETE FROM tasks WHERE id = 'td-labeled'", [])
151 .unwrap();
152
153 let label_count: i32 = conn
154 .query_row(
155 "SELECT COUNT(*) FROM labels WHERE task_id = 'td-labeled'",
156 [],
157 |r| r.get(0),
158 )
159 .unwrap();
160 assert_eq!(
161 label_count, 0,
162 "labels should be deleted via ON DELETE CASCADE"
163 );
164}
165
166#[test]
167fn blockers_fk_cascades_on_task_delete() {
168 let tmp = init_tmp();
169 let conn = rusqlite::Connection::open(tmp.path().join(".td/tasks.db")).unwrap();
170 conn.execute_batch("PRAGMA foreign_keys = ON").unwrap();
171
172 conn.execute(
173 "INSERT INTO tasks (id, title, created, updated) \
174 VALUES ('td-a', 'Task A', '2024-01-01T00:00:00Z', '2024-01-01T00:00:00Z')",
175 [],
176 )
177 .unwrap();
178 conn.execute(
179 "INSERT INTO tasks (id, title, created, updated) \
180 VALUES ('td-b', 'Task B', '2024-01-01T00:00:00Z', '2024-01-01T00:00:00Z')",
181 [],
182 )
183 .unwrap();
184 conn.execute(
185 "INSERT INTO tasks (id, title, created, updated) \
186 VALUES ('td-c', 'Task C', '2024-01-01T00:00:00Z', '2024-01-01T00:00:00Z')",
187 [],
188 )
189 .unwrap();
190
191 // td-b appears as both task_id and blocker_id across these rows.
192 conn.execute(
193 "INSERT INTO blockers (task_id, blocker_id) VALUES ('td-b', 'td-a')",
194 [],
195 )
196 .unwrap();
197 conn.execute(
198 "INSERT INTO blockers (task_id, blocker_id) VALUES ('td-c', 'td-b')",
199 [],
200 )
201 .unwrap();
202
203 conn.execute("DELETE FROM tasks WHERE id = 'td-b'", [])
204 .unwrap();
205
206 let blocker_count: i32 = conn
207 .query_row("SELECT COUNT(*) FROM blockers", [], |r| r.get(0))
208 .unwrap();
209 assert_eq!(
210 blocker_count, 0,
211 "rows referencing a deleted task should be deleted via ON DELETE CASCADE"
212 );
213}
214
215#[test]
216fn migration_cleans_dangling_blocker_ids() {
217 let tmp = TempDir::new().unwrap();
218 let td_dir = tmp.path().join(".td");
219 std::fs::create_dir_all(&td_dir).unwrap();
220
221 // Create a v2 database (pre-0003) with a dangling blocker_id.
222 let conn = rusqlite::Connection::open(td_dir.join("tasks.db")).unwrap();
223 conn.execute_batch(
224 "PRAGMA foreign_keys = OFF;
225 CREATE TABLE tasks (
226 id TEXT PRIMARY KEY,
227 title TEXT NOT NULL,
228 description TEXT DEFAULT '',
229 type TEXT DEFAULT 'task',
230 priority INTEGER DEFAULT 2,
231 status TEXT DEFAULT 'open',
232 parent TEXT DEFAULT '',
233 created TEXT NOT NULL,
234 updated TEXT NOT NULL,
235 effort INTEGER NOT NULL DEFAULT 2
236 );
237 CREATE TABLE labels (
238 task_id TEXT, label TEXT,
239 PRIMARY KEY (task_id, label),
240 FOREIGN KEY (task_id) REFERENCES tasks(id)
241 );
242 CREATE TABLE blockers (
243 task_id TEXT, blocker_id TEXT,
244 PRIMARY KEY (task_id, blocker_id),
245 FOREIGN KEY (task_id) REFERENCES tasks(id)
246 );
247 INSERT INTO tasks (id, title, created, updated)
248 VALUES ('td-a', 'Task A', '2024-01-01T00:00:00Z', '2024-01-01T00:00:00Z');
249 INSERT INTO tasks (id, title, created, updated)
250 VALUES ('td-b', 'Task B', '2024-01-01T00:00:00Z', '2024-01-01T00:00:00Z');
251 -- Valid blocker
252 INSERT INTO blockers (task_id, blocker_id) VALUES ('td-a', 'td-b');
253 -- Dangling blocker referencing a task that doesn't exist
254 INSERT INTO blockers (task_id, blocker_id) VALUES ('td-a', 'td-gone');
255 PRAGMA user_version = 2;",
256 )
257 .unwrap();
258 drop(conn);
259
260 // Running any command triggers migration.
261 td().args(["--json", "list"])
262 .current_dir(&tmp)
263 .assert()
264 .success();
265
266 // The valid blocker should survive; the dangling one should be gone.
267 let conn = rusqlite::Connection::open(td_dir.join("tasks.db")).unwrap();
268 let count: i32 = conn
269 .query_row(
270 "SELECT COUNT(*) FROM blockers WHERE task_id = 'td-a'",
271 [],
272 |r| r.get(0),
273 )
274 .unwrap();
275 assert_eq!(count, 1, "only the valid blocker should remain");
276
277 let blocker: String = conn
278 .query_row(
279 "SELECT blocker_id FROM blockers WHERE task_id = 'td-a'",
280 [],
281 |r| r.get(0),
282 )
283 .unwrap();
284 assert_eq!(blocker, "td-b");
285}
286
287#[test]
288fn migration_cleans_dangling_labels() {
289 let tmp = TempDir::new().unwrap();
290 let td_dir = tmp.path().join(".td");
291 std::fs::create_dir_all(&td_dir).unwrap();
292
293 // Create a v4 database (pre-0005) with a dangling label row.
294 let conn = rusqlite::Connection::open(td_dir.join("tasks.db")).unwrap();
295 conn.execute_batch(
296 "PRAGMA foreign_keys = OFF;
297 CREATE TABLE tasks (
298 id TEXT PRIMARY KEY,
299 title TEXT NOT NULL,
300 description TEXT DEFAULT '',
301 type TEXT DEFAULT 'task',
302 priority INTEGER DEFAULT 2,
303 status TEXT DEFAULT 'open',
304 parent TEXT DEFAULT '',
305 created TEXT NOT NULL,
306 updated TEXT NOT NULL,
307 effort INTEGER NOT NULL DEFAULT 2
308 );
309 CREATE TABLE labels (
310 task_id TEXT, label TEXT,
311 PRIMARY KEY (task_id, label),
312 FOREIGN KEY (task_id) REFERENCES tasks(id)
313 );
314 CREATE TABLE blockers (
315 task_id TEXT, blocker_id TEXT,
316 PRIMARY KEY (task_id, blocker_id),
317 FOREIGN KEY (task_id) REFERENCES tasks(id),
318 FOREIGN KEY (blocker_id) REFERENCES tasks(id)
319 );
320 CREATE TABLE task_logs (
321 id INTEGER PRIMARY KEY AUTOINCREMENT,
322 task_id TEXT NOT NULL,
323 timestamp TEXT NOT NULL,
324 body TEXT NOT NULL,
325 FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
326 );
327 INSERT INTO tasks (id, title, created, updated)
328 VALUES ('td-real', 'Real task', '2024-01-01T00:00:00Z', '2024-01-01T00:00:00Z');
329 INSERT INTO labels (task_id, label) VALUES ('td-real', 'kept');
330 INSERT INTO labels (task_id, label) VALUES ('td-gone', 'orphan');
331 PRAGMA user_version = 4;",
332 )
333 .unwrap();
334 drop(conn);
335
336 // Running any command triggers migration to v5.
337 td().args(["--json", "list"])
338 .current_dir(&tmp)
339 .assert()
340 .success();
341
342 let conn = rusqlite::Connection::open(td_dir.join("tasks.db")).unwrap();
343 let kept_count: i32 = conn
344 .query_row(
345 "SELECT COUNT(*) FROM labels WHERE task_id = 'td-real' AND label = 'kept'",
346 [],
347 |r| r.get(0),
348 )
349 .unwrap();
350 assert_eq!(kept_count, 1, "valid label should survive migration");
351
352 let orphan_count: i32 = conn
353 .query_row(
354 "SELECT COUNT(*) FROM labels WHERE task_id = 'td-gone'",
355 [],
356 |r| r.get(0),
357 )
358 .unwrap();
359 assert_eq!(
360 orphan_count, 0,
361 "dangling label should be removed during migration"
362 );
363}