intent_engine/
sql_constants.rs

1//! SQL query constants and fragments
2//!
3//! This module centralizes frequently-used SQL query strings and fragments to:
4//! 1. Reduce code duplication
5//! 2. Ensure consistency across the codebase
6//! 3. Make query modifications easier to maintain
7//!
8//! # Design Philosophy
9//!
10//! - **Column Lists**: Reusable SELECT column specifications
11//! - **Base Queries**: Complete SELECT statements with standard FROM clauses
12//! - **Existence Checks**: Common existence validation patterns
13//!
14//! Note: Dynamic WHERE clauses are still built inline for flexibility.
15
16// ============================================================================
17// Task Queries
18// ============================================================================
19
20/// Standard column list for task queries (includes spec column)
21///
22/// Used when fetching complete task data with specification.
23/// Columns: id, parent_id, name, spec, status, complexity, priority,
24///          first_todo_at, first_doing_at, first_done_at, active_form, owner
25pub const TASK_COLUMNS: &str =
26    "id, parent_id, name, spec, status, complexity, priority, first_todo_at, first_doing_at, first_done_at, active_form, owner";
27
28/// Task column list without spec (uses NULL placeholder)
29///
30/// Used when spec is not needed but schema compatibility is required.
31/// Columns: id, parent_id, name, NULL as spec, status, complexity, priority,
32///          first_todo_at, first_doing_at, first_done_at, active_form, owner
33pub const TASK_COLUMNS_NO_SPEC: &str =
34    "id, parent_id, name, NULL as spec, status, complexity, priority, first_todo_at, first_doing_at, first_done_at, active_form, owner";
35
36/// Base SELECT query for tasks (with spec)
37///
38/// Returns all task columns. Add WHERE clauses as needed.
39pub const SELECT_TASK_FULL: &str = const_format::formatcp!("SELECT {} FROM tasks", TASK_COLUMNS);
40
41/// Base SELECT query for tasks (without spec, using NULL)
42///
43/// Returns all task columns except spec (NULL as spec). Add WHERE clauses as needed.
44pub const SELECT_TASK_NO_SPEC: &str =
45    const_format::formatcp!("SELECT {} FROM tasks WHERE 1=1", TASK_COLUMNS_NO_SPEC);
46
47/// Check if a task exists by ID
48pub const CHECK_TASK_EXISTS: &str = "SELECT EXISTS(SELECT 1 FROM tasks WHERE id = ?)";
49
50/// Get task name by ID
51pub const SELECT_TASK_NAME: &str = "SELECT name FROM tasks WHERE id = ?";
52
53/// Get task name and parent_id by ID
54pub const SELECT_TASK_NAME_PARENT: &str = "SELECT name, parent_id FROM tasks WHERE id = ?";
55
56/// Get parent_id for a task
57pub const SELECT_TASK_PARENT_ID: &str = "SELECT parent_id FROM tasks WHERE id = ?";
58
59/// Count total tasks
60pub const COUNT_TASKS_TOTAL: &str = "SELECT COUNT(*) FROM tasks";
61
62/// Count incomplete subtasks of a parent
63pub const COUNT_INCOMPLETE_CHILDREN: &str =
64    "SELECT COUNT(*) FROM tasks WHERE parent_id = ? AND status != 'done'";
65
66/// Count incomplete children excluding specific task
67pub const COUNT_INCOMPLETE_CHILDREN_EXCLUDE: &str =
68    "SELECT COUNT(*) FROM tasks WHERE parent_id = ? AND status != 'done' AND id != ?";
69
70/// Count total children of a parent
71pub const COUNT_CHILDREN_TOTAL: &str = "SELECT COUNT(*) FROM tasks WHERE parent_id = ?";
72
73/// Count tasks with 'doing' status
74pub const COUNT_TASKS_DOING: &str = "SELECT COUNT(*) FROM tasks WHERE status = 'doing'";
75
76/// Count incomplete tasks (todo or doing)
77pub const COUNT_TASKS_INCOMPLETE: &str =
78    "SELECT COUNT(*) FROM tasks WHERE status IN ('todo', 'doing')";
79
80/// Count incomplete tasks excluding specific task
81pub const COUNT_INCOMPLETE_TASKS_EXCLUDE: &str =
82    "SELECT COUNT(*) FROM tasks WHERE status != 'done' AND id != ?";
83
84// ============================================================================
85// Event Queries
86// ============================================================================
87
88/// Standard column list for event queries
89///
90/// Columns: id, task_id, timestamp, log_type, discussion_data
91pub const EVENT_COLUMNS: &str = "id, task_id, timestamp, log_type, discussion_data";
92
93/// Base SELECT query for events
94///
95/// Returns all event columns. Add WHERE clauses as needed.
96pub const SELECT_EVENT_FULL: &str = const_format::formatcp!("SELECT {} FROM events", EVENT_COLUMNS);
97
98/// SELECT event with WHERE id = ? condition
99pub const SELECT_EVENT_BY_ID: &str =
100    const_format::formatcp!("SELECT {} FROM events WHERE id = ?", EVENT_COLUMNS);
101
102/// Base SELECT query for events with dynamic WHERE clause building
103pub const SELECT_EVENT_BASE: &str =
104    const_format::formatcp!("SELECT {} FROM events WHERE 1=1", EVENT_COLUMNS);
105
106/// Count total events
107pub const COUNT_EVENTS_TOTAL: &str = "SELECT COUNT(*) FROM events";
108
109/// Count events for a specific task
110pub const COUNT_EVENTS_FOR_TASK: &str = "SELECT COUNT(*) FROM events WHERE task_id = ?";
111
112/// Check if a task exists (also used by event validation)
113pub const CHECK_TASK_EXISTS_FOR_EVENT: &str = CHECK_TASK_EXISTS;
114
115// ============================================================================
116// Full-Text Search (FTS5) Queries
117// ============================================================================
118
119/// Base query for task FTS5 search
120pub const SELECT_TASKS_FTS_BASE: &str = "SELECT rowid FROM tasks_fts WHERE ";
121
122/// Count task FTS5 matches
123pub const COUNT_TASKS_FTS: &str = "SELECT COUNT(*) FROM tasks_fts WHERE name MATCH ?";
124
125// ============================================================================
126// Tests
127// ============================================================================
128
129#[cfg(test)]
130mod tests {
131    use super::*;
132
133    #[test]
134    fn test_task_columns_format() {
135        assert!(TASK_COLUMNS.contains("id"));
136        assert!(TASK_COLUMNS.contains("parent_id"));
137        assert!(TASK_COLUMNS.contains("spec"));
138        assert!(TASK_COLUMNS.contains("active_form"));
139    }
140
141    #[test]
142    fn test_task_columns_no_spec_format() {
143        assert!(TASK_COLUMNS_NO_SPEC.contains("NULL as spec"));
144        assert!(TASK_COLUMNS_NO_SPEC.contains("active_form"));
145    }
146
147    #[test]
148    fn test_event_columns_format() {
149        assert!(EVENT_COLUMNS.contains("id"));
150        assert!(EVENT_COLUMNS.contains("task_id"));
151        assert!(EVENT_COLUMNS.contains("discussion_data"));
152    }
153
154    #[test]
155    fn test_select_task_full() {
156        assert_eq!(
157            SELECT_TASK_FULL,
158            "SELECT id, parent_id, name, spec, status, complexity, priority, first_todo_at, first_doing_at, first_done_at, active_form, owner FROM tasks"
159        );
160    }
161
162    #[test]
163    fn test_select_event_by_id() {
164        assert_eq!(
165            SELECT_EVENT_BY_ID,
166            "SELECT id, task_id, timestamp, log_type, discussion_data FROM events WHERE id = ?"
167        );
168    }
169
170    #[test]
171    fn test_check_task_exists() {
172        assert_eq!(
173            CHECK_TASK_EXISTS,
174            "SELECT EXISTS(SELECT 1 FROM tasks WHERE id = ?)"
175        );
176    }
177}
intent_engine/sql_constants.rs

intent_engine/
sql_constants.rs
