things_mcp/core/reader/
fixture.rs

1//! In-code builder for a minimal Things-shaped SQLite, used by tests.
2//!
3//! Mirrors only the columns we currently query. The real Things schema has
4//! many more columns; the reader code never selects `*`, so omissions here
5//! don't matter as long as the columns our queries reference are present.
6
7use std::path::Path;
8
9use rusqlite::Connection;
10
11pub fn build_fixture(path: &Path) -> anyhow::Result<()> {
12    let c = Connection::open(path)?;
13    c.execute_batch(r#"
14        CREATE TABLE TMTask (
15            uuid TEXT PRIMARY KEY,
16            title TEXT,
17            type INTEGER,
18            status INTEGER,
19            trashed INTEGER,
20            start INTEGER,
21            startDate INTEGER,
22            deadline INTEGER,
23            stopDate REAL,
24            creationDate REAL,
25            userModificationDate REAL,
26            project TEXT,
27            area TEXT,
28            heading TEXT,
29            notes TEXT,
30            rt1_recurrenceRule BLOB,
31            "index" INTEGER,
32            todayIndex INTEGER
33        );
34        CREATE TABLE TMArea (uuid TEXT PRIMARY KEY, title TEXT, "index" INTEGER);
35        CREATE TABLE TMTag (uuid TEXT PRIMARY KEY, title TEXT, "index" INTEGER, shortcut TEXT, parent TEXT);
36        CREATE TABLE TMTaskTag (tasks TEXT, tags TEXT);
37        CREATE TABLE TMChecklistItem (
38            uuid TEXT PRIMARY KEY,
39            title TEXT,
40            status INTEGER,
41            task TEXT,
42            "index" INTEGER,
43            stopDate REAL,
44            creationDate REAL,
45            userModificationDate REAL
46        );
47        CREATE TABLE Meta (key TEXT PRIMARY KEY, value TEXT);
48
49        INSERT INTO Meta (key, value) VALUES ('databaseVersion', '21');
50
51        -- Two areas, deliberately not in alphabetical order so ORDER BY "index" matters.
52        INSERT INTO TMArea (uuid, title, "index") VALUES
53            ('area-1', 'Personal', 0),
54            ('area-2', 'Work',     1);
55
56        -- Tags: 'Call' is a child of 'Errand'; 'Deep work' is a sibling with a shortcut.
57        INSERT INTO TMTag (uuid, title, "index", shortcut, parent) VALUES
58            ('tag-errand', 'Errand',    0, NULL, NULL),
59            ('tag-call',   'Call',      0, NULL, 'tag-errand'),
60            ('tag-deep',   'Deep work', 1, 'D',  NULL);
61
62        -- Inbox to-dos: 2 open, 1 completed (status=3).
63        INSERT INTO TMTask
64            (uuid, title, type, status, trashed, start, creationDate, userModificationDate)
65        VALUES
66            ('todo-1', 'Buy milk',          0, 0, 0, 0, 1715000000.0, 1715000100.0),
67            ('todo-2', 'Call the dentist',  0, 0, 0, 0, 1715000200.0, 1715000300.0),
68            ('todo-3', 'Pay tax bill',      0, 3, 0, 0, 1714900000.0, 1714900100.0);
69
70        -- Anytime to-do, scheduled far in the past so list_today always picks it up.
71        -- pack_things_date(2020, 1, 1) = (2020<<16) | (1<<12) | (1<<7) = 132386944
72        INSERT INTO TMTask
73            (uuid, title, type, status, trashed, start, startDate, project, creationDate, userModificationDate, todayIndex)
74        VALUES
75            ('todo-today', 'Today scheduled item', 0, 0, 0, 1, 132386944, 'proj-1', 1715000600.0, 1715000700.0, 0);
76
77        -- Anytime to-do scheduled far in the future (upcoming).
78        -- pack_things_date(2099, 12, 31) = (2099<<16) | (12<<12) | (31<<7) = 137613184
79        INSERT INTO TMTask
80            (uuid, title, type, status, trashed, start, startDate, area, creationDate, userModificationDate)
81        VALUES
82            ('todo-upcoming-sched', 'Upcoming scheduled item', 0, 0, 0, 1, 137613184, 'area-2', 1715001100.0, 1715001200.0);
83
84        -- Anytime to-do with a far-future deadline but no scheduled date —
85        -- appears in BOTH list_anytime and list_upcoming (mirrors Things UI).
86        INSERT INTO TMTask
87            (uuid, title, type, status, trashed, start, deadline, area, creationDate, userModificationDate)
88        VALUES
89            ('todo-upcoming-dl', 'Upcoming deadlined item', 0, 0, 0, 1, 137613184, 'area-1', 1715001300.0, 1715001400.0);
90
91        -- Plain anytime to-do (no scheduled date, no deadline) inside proj-1.
92        INSERT INTO TMTask
93            (uuid, title, type, status, trashed, start, project, creationDate, userModificationDate)
94        VALUES
95            ('todo-4', 'Read RFC 9457', 0, 0, 0, 1, 'proj-1', 1715001000.0, 1715001100.0);
96
97        -- Someday to-do, in area-2.
98        INSERT INTO TMTask
99            (uuid, title, type, status, trashed, start, area, creationDate, userModificationDate)
100        VALUES
101            ('todo-someday', 'Read research papers', 0, 0, 0, 2, 'area-2', 1715002000.0, 1715002100.0);
102
103        -- Logbook items: one completed (status=3), one canceled (status=2), both with stopDate.
104        INSERT INTO TMTask
105            (uuid, title, type, status, trashed, start, stopDate, creationDate, userModificationDate)
106        VALUES
107            ('todo-log-1', 'Old completed', 0, 3, 0, 1, 1714000000.0, 1713000000.0, 1714000000.0),
108            ('todo-log-2', 'Old canceled',  0, 2, 0, 1, 1714500000.0, 1713500000.0, 1714500000.0);
109
110        -- Trashed to-do (start=0 would put it in inbox, but trashed=1 hides it everywhere except list_trash).
111        INSERT INTO TMTask
112            (uuid, title, type, status, trashed, start, creationDate, userModificationDate)
113        VALUES
114            ('todo-trash', 'Trashed thing', 0, 0, 1, 0, 1714800000.0, 1714800100.0);
115
116        -- Two projects: proj-1 (open) in area-1, proj-2 (done) in area-2.
117        INSERT INTO TMTask
118            (uuid, title, type, status, trashed, start, area, notes, creationDate, userModificationDate)
119        VALUES
120            ('proj-1', 'Reading list', 1, 0, 0, 1, 'area-1', 'Track what to read next', 1714000000.0, 1714000100.0),
121            ('proj-2', 'Shipped Q1',   1, 3, 0, 1, 'area-2', NULL,                      1714100000.0, 1714100100.0);
122
123        -- A heading under proj-1 with one to-do beneath it.
124        INSERT INTO TMTask
125            (uuid, title, type, status, trashed, start, project, "index", creationDate, userModificationDate)
126        VALUES
127            ('head-1', 'Articles', 2, 0, 0, 1, 'proj-1', 1, 1714000500.0, 1714000600.0);
128
129        INSERT INTO TMTask
130            (uuid, title, type, status, trashed, start, project, heading, "index", creationDate, userModificationDate)
131        VALUES
132            ('todo-in-head', 'Read intro', 0, 0, 0, 1, 'proj-1', 'head-1', 2, 1714000700.0, 1714000800.0);
133
134        -- Checklist items for todo-1: 2 open, 1 completed (status=3).
135        INSERT INTO TMChecklistItem
136            (uuid, title, status, task, "index", creationDate, userModificationDate)
137        VALUES
138            ('chk-1', 'Walk to shop',   0, 'todo-1', 0, 1715000000.0, 1715000050.0),
139            ('chk-2', 'Buy whole milk', 0, 'todo-1', 1, 1715000010.0, 1715000060.0),
140            ('chk-3', 'Pay with card',  3, 'todo-1', 2, 1715000020.0, 1715000070.0);
141
142        -- Tag mappings:
143        --   todo-2          → 'Errand' (parent tag)        (existing)
144        --   todo-4          → 'Call'   (child of 'Errand') — exercises list_by_tag recursion
145        --   todo-someday    → 'Deep work'
146        --   proj-1          → 'Errand' (projects can carry tags too)
147        INSERT INTO TMTaskTag (tasks, tags) VALUES
148            ('todo-2',       'tag-errand'),
149            ('todo-4',       'tag-call'),
150            ('todo-someday', 'tag-deep'),
151            ('proj-1',       'tag-errand');
152    "#)?;
153    Ok(())
154}
155
156#[cfg(test)]
157mod tests {
158    use super::*;
159    use tempfile::tempdir;
160
161    #[test]
162    fn fixture_has_expected_inbox_rows() {
163        let tmp = tempdir().unwrap();
164        let path = tmp.path().join("test.sqlite");
165        build_fixture(&path).unwrap();
166        let c = Connection::open(&path).unwrap();
167        let n: i64 = c
168            .query_row(
169                "SELECT COUNT(*) FROM TMTask WHERE start = 0 AND trashed = 0",
170                [],
171                |r| r.get(0),
172            )
173            .unwrap();
174        assert_eq!(n, 3);
175    }
176}
things_mcp/core/reader/fixture.rs

things_mcp/core/reader/
fixture.rs
