intent_engine/db/
mod.rs

1pub mod models;
2
3use crate::error::Result;
4use sqlx::sqlite::{SqliteConnectOptions, SqlitePool, SqlitePoolOptions};
5use std::path::Path;
6
7pub async fn create_pool(db_path: &Path) -> Result<SqlitePool> {
8    let options = SqliteConnectOptions::new()
9        .filename(db_path)
10        .create_if_missing(true)
11        .journal_mode(sqlx::sqlite::SqliteJournalMode::Wal)
12        .busy_timeout(std::time::Duration::from_millis(5000));
13
14    let pool = SqlitePoolOptions::new()
15        .max_connections(5)
16        .connect_with(options)
17        .await?;
18
19    Ok(pool)
20}
21
22pub async fn run_migrations(pool: &SqlitePool) -> Result<()> {
23    // Enable FTS5
24    sqlx::query("PRAGMA journal_mode=WAL;")
25        .execute(pool)
26        .await?;
27
28    // Create tasks table
29    sqlx::query(
30        r#"
31        CREATE TABLE IF NOT EXISTS tasks (
32            id INTEGER PRIMARY KEY AUTOINCREMENT,
33            parent_id INTEGER,
34            name TEXT NOT NULL,
35            spec TEXT,
36            status TEXT NOT NULL DEFAULT 'todo',
37            complexity INTEGER,
38            priority INTEGER DEFAULT 0,
39            first_todo_at DATETIME,
40            first_doing_at DATETIME,
41            first_done_at DATETIME,
42            FOREIGN KEY (parent_id) REFERENCES tasks(id) ON DELETE CASCADE,
43            CHECK (status IN ('todo', 'doing', 'done'))
44        )
45        "#,
46    )
47    .execute(pool)
48    .await?;
49
50    // Create FTS5 virtual table for tasks
51    sqlx::query(
52        r#"
53        CREATE VIRTUAL TABLE IF NOT EXISTS tasks_fts USING fts5(
54            name,
55            spec,
56            content=tasks,
57            content_rowid=id
58        )
59        "#,
60    )
61    .execute(pool)
62    .await?;
63
64    // Create triggers to keep FTS in sync
65    sqlx::query(
66        r#"
67        CREATE TRIGGER IF NOT EXISTS tasks_ai AFTER INSERT ON tasks BEGIN
68            INSERT INTO tasks_fts(rowid, name, spec) VALUES (new.id, new.name, new.spec);
69        END
70        "#,
71    )
72    .execute(pool)
73    .await?;
74
75    sqlx::query(
76        r#"
77        CREATE TRIGGER IF NOT EXISTS tasks_ad AFTER DELETE ON tasks BEGIN
78            DELETE FROM tasks_fts WHERE rowid = old.id;
79        END
80        "#,
81    )
82    .execute(pool)
83    .await?;
84
85    sqlx::query(
86        r#"
87        CREATE TRIGGER IF NOT EXISTS tasks_au AFTER UPDATE ON tasks BEGIN
88            UPDATE tasks_fts SET name = new.name, spec = new.spec WHERE rowid = old.id;
89        END
90        "#,
91    )
92    .execute(pool)
93    .await?;
94
95    // Create events table
96    sqlx::query(
97        r#"
98        CREATE TABLE IF NOT EXISTS events (
99            id INTEGER PRIMARY KEY AUTOINCREMENT,
100            task_id INTEGER NOT NULL,
101            timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
102            log_type TEXT NOT NULL,
103            discussion_data TEXT NOT NULL,
104            FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE
105        )
106        "#,
107    )
108    .execute(pool)
109    .await?;
110
111    // Create index on task_id for events
112    sqlx::query(
113        r#"
114        CREATE INDEX IF NOT EXISTS idx_events_task_id ON events(task_id)
115        "#,
116    )
117    .execute(pool)
118    .await?;
119
120    // Create FTS5 virtual table for events
121    sqlx::query(
122        r#"
123        CREATE VIRTUAL TABLE IF NOT EXISTS events_fts USING fts5(
124            discussion_data,
125            content=events,
126            content_rowid=id
127        )
128        "#,
129    )
130    .execute(pool)
131    .await?;
132
133    // Create triggers for events FTS
134    sqlx::query(
135        r#"
136        CREATE TRIGGER IF NOT EXISTS events_ai AFTER INSERT ON events BEGIN
137            INSERT INTO events_fts(rowid, discussion_data) VALUES (new.id, new.discussion_data);
138        END
139        "#,
140    )
141    .execute(pool)
142    .await?;
143
144    sqlx::query(
145        r#"
146        CREATE TRIGGER IF NOT EXISTS events_ad AFTER DELETE ON events BEGIN
147            DELETE FROM events_fts WHERE rowid = old.id;
148        END
149        "#,
150    )
151    .execute(pool)
152    .await?;
153
154    sqlx::query(
155        r#"
156        CREATE TRIGGER IF NOT EXISTS events_au AFTER UPDATE ON events BEGIN
157            UPDATE events_fts SET discussion_data = new.discussion_data WHERE rowid = old.id;
158        END
159        "#,
160    )
161    .execute(pool)
162    .await?;
163
164    // Create workspace_state table
165    sqlx::query(
166        r#"
167        CREATE TABLE IF NOT EXISTS workspace_state (
168            key TEXT PRIMARY KEY,
169            value TEXT NOT NULL
170        )
171        "#,
172    )
173    .execute(pool)
174    .await?;
175
176    Ok(())
177}
178
179#[cfg(test)]
180mod tests {
181    use super::*;
182    use tempfile::TempDir;
183
184    #[tokio::test]
185    async fn test_create_pool_success() {
186        let temp_dir = TempDir::new().unwrap();
187        let db_path = temp_dir.path().join("test.db");
188
189        let pool = create_pool(&db_path).await.unwrap();
190
191        // Verify we can execute a query
192        let result: i64 = sqlx::query_scalar("SELECT 1")
193            .fetch_one(&pool)
194            .await
195            .unwrap();
196
197        assert_eq!(result, 1);
198    }
199
200    #[tokio::test]
201    async fn test_run_migrations_creates_tables() {
202        let temp_dir = TempDir::new().unwrap();
203        let db_path = temp_dir.path().join("test.db");
204        let pool = create_pool(&db_path).await.unwrap();
205
206        run_migrations(&pool).await.unwrap();
207
208        // Verify tables were created
209        let tables: Vec<String> =
210            sqlx::query_scalar("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")
211                .fetch_all(&pool)
212                .await
213                .unwrap();
214
215        assert!(tables.contains(&"tasks".to_string()));
216        assert!(tables.contains(&"events".to_string()));
217        assert!(tables.contains(&"workspace_state".to_string()));
218    }
219
220    #[tokio::test]
221    async fn test_run_migrations_creates_fts_tables() {
222        let temp_dir = TempDir::new().unwrap();
223        let db_path = temp_dir.path().join("test.db");
224        let pool = create_pool(&db_path).await.unwrap();
225
226        run_migrations(&pool).await.unwrap();
227
228        // Verify FTS tables were created
229        let tables: Vec<String> = sqlx::query_scalar(
230            "SELECT name FROM sqlite_master WHERE type='table' AND name LIKE '%_fts'",
231        )
232        .fetch_all(&pool)
233        .await
234        .unwrap();
235
236        assert!(tables.contains(&"tasks_fts".to_string()));
237        assert!(tables.contains(&"events_fts".to_string()));
238    }
239
240    #[tokio::test]
241    async fn test_run_migrations_creates_triggers() {
242        let temp_dir = TempDir::new().unwrap();
243        let db_path = temp_dir.path().join("test.db");
244        let pool = create_pool(&db_path).await.unwrap();
245
246        run_migrations(&pool).await.unwrap();
247
248        // Verify triggers were created
249        let triggers: Vec<String> =
250            sqlx::query_scalar("SELECT name FROM sqlite_master WHERE type='trigger'")
251                .fetch_all(&pool)
252                .await
253                .unwrap();
254
255        assert!(triggers.contains(&"tasks_ai".to_string()));
256        assert!(triggers.contains(&"tasks_ad".to_string()));
257        assert!(triggers.contains(&"tasks_au".to_string()));
258        assert!(triggers.contains(&"events_ai".to_string()));
259        assert!(triggers.contains(&"events_ad".to_string()));
260        assert!(triggers.contains(&"events_au".to_string()));
261    }
262
263    #[tokio::test]
264    async fn test_run_migrations_idempotent() {
265        let temp_dir = TempDir::new().unwrap();
266        let db_path = temp_dir.path().join("test.db");
267        let pool = create_pool(&db_path).await.unwrap();
268
269        // Run migrations twice
270        run_migrations(&pool).await.unwrap();
271        run_migrations(&pool).await.unwrap();
272
273        // Should not fail - migrations are idempotent
274        let tables: Vec<String> =
275            sqlx::query_scalar("SELECT name FROM sqlite_master WHERE type='table'")
276                .fetch_all(&pool)
277                .await
278                .unwrap();
279
280        assert!(tables.len() >= 3);
281    }
282
283    #[tokio::test]
284    async fn test_fts_triggers_work() {
285        let temp_dir = TempDir::new().unwrap();
286        let db_path = temp_dir.path().join("test.db");
287        let pool = create_pool(&db_path).await.unwrap();
288        run_migrations(&pool).await.unwrap();
289
290        // Insert a task
291        sqlx::query("INSERT INTO tasks (name, spec, status) VALUES (?, ?, ?)")
292            .bind("Test task")
293            .bind("Test spec")
294            .bind("todo")
295            .execute(&pool)
296            .await
297            .unwrap();
298
299        // Verify FTS was updated
300        let count: i64 =
301            sqlx::query_scalar("SELECT COUNT(*) FROM tasks_fts WHERE name MATCH 'Test'")
302                .fetch_one(&pool)
303                .await
304                .unwrap();
305
306        assert_eq!(count, 1);
307    }
308
309    #[tokio::test]
310    async fn test_workspace_state_table_structure() {
311        let temp_dir = TempDir::new().unwrap();
312        let db_path = temp_dir.path().join("test.db");
313        let pool = create_pool(&db_path).await.unwrap();
314        run_migrations(&pool).await.unwrap();
315
316        // Insert and retrieve workspace state
317        sqlx::query("INSERT INTO workspace_state (key, value) VALUES (?, ?)")
318            .bind("test_key")
319            .bind("test_value")
320            .execute(&pool)
321            .await
322            .unwrap();
323
324        let value: String = sqlx::query_scalar("SELECT value FROM workspace_state WHERE key = ?")
325            .bind("test_key")
326            .fetch_one(&pool)
327            .await
328            .unwrap();
329
330        assert_eq!(value, "test_value");
331    }
332
333    #[tokio::test]
334    async fn test_task_status_constraint() {
335        let temp_dir = TempDir::new().unwrap();
336        let db_path = temp_dir.path().join("test.db");
337        let pool = create_pool(&db_path).await.unwrap();
338        run_migrations(&pool).await.unwrap();
339
340        // Try to insert task with invalid status
341        let result = sqlx::query("INSERT INTO tasks (name, status) VALUES (?, ?)")
342            .bind("Test")
343            .bind("invalid_status")
344            .execute(&pool)
345            .await;
346
347        // Should fail due to CHECK constraint
348        assert!(result.is_err());
349    }
350}
intent_engine/db/mod.rs

intent_engine/db/
mod.rs
