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, metadata
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, metadata";
27
28/// Task column list with `t.` table prefix for JOIN queries
29///
30/// Same columns as TASK_COLUMNS but each prefixed with `t.` to avoid
31/// ambiguity when joining with other tables (e.g. dependencies).
32pub const TASK_COLUMNS_PREFIXED: &str =
33    "t.id, t.parent_id, t.name, t.spec, t.status, t.complexity, t.priority, t.first_todo_at, t.first_doing_at, t.first_done_at, t.active_form, t.owner, t.metadata";
34
35/// Task column list without spec (uses NULL placeholder)
36///
37/// Used when spec is not needed but schema compatibility is required.
38/// Columns: id, parent_id, name, NULL as spec, status, complexity, priority,
39///          first_todo_at, first_doing_at, first_done_at, active_form, owner, metadata
40pub const TASK_COLUMNS_NO_SPEC: &str =
41    "id, parent_id, name, NULL as spec, status, complexity, priority, first_todo_at, first_doing_at, first_done_at, active_form, owner, metadata";
42
43/// Base SELECT query for tasks (with spec)
44///
45/// Returns all task columns. Add WHERE clauses as needed.
46pub const SELECT_TASK_FULL: &str = const_format::formatcp!("SELECT {} FROM tasks", TASK_COLUMNS);
47
48/// Base SELECT query for tasks (without spec, using NULL)
49///
50/// Returns all task columns except spec (NULL as spec). Add WHERE clauses as needed.
51pub const SELECT_TASK_NO_SPEC: &str =
52    const_format::formatcp!("SELECT {} FROM tasks WHERE 1=1", TASK_COLUMNS_NO_SPEC);
53
54/// Check if an active (non-deleted) task exists by ID
55pub const CHECK_TASK_EXISTS: &str =
56    "SELECT EXISTS(SELECT 1 FROM tasks WHERE id = ? AND deleted_at IS NULL)";
57
58/// Get task name by ID (active tasks only)
59pub const SELECT_TASK_NAME: &str = "SELECT name FROM tasks WHERE id = ? AND deleted_at IS NULL";
60
61/// Get task name and parent_id by ID (active tasks only)
62pub const SELECT_TASK_NAME_PARENT: &str =
63    "SELECT name, parent_id FROM tasks WHERE id = ? AND deleted_at IS NULL";
64
65/// Get parent_id for a task (active tasks only)
66pub const SELECT_TASK_PARENT_ID: &str =
67    "SELECT parent_id FROM tasks WHERE id = ? AND deleted_at IS NULL";
68
69/// Count total active tasks
70pub const COUNT_TASKS_TOTAL: &str = "SELECT COUNT(*) FROM tasks WHERE deleted_at IS NULL";
71
72/// Count incomplete active subtasks of a parent
73pub const COUNT_INCOMPLETE_CHILDREN: &str =
74    "SELECT COUNT(*) FROM tasks WHERE parent_id = ? AND status != 'done' AND deleted_at IS NULL";
75
76/// Count incomplete active children excluding specific task
77pub const COUNT_INCOMPLETE_CHILDREN_EXCLUDE: &str =
78    "SELECT COUNT(*) FROM tasks WHERE parent_id = ? AND status != 'done' AND id != ? AND deleted_at IS NULL";
79
80/// Count total active children of a parent
81pub const COUNT_CHILDREN_TOTAL: &str =
82    "SELECT COUNT(*) FROM tasks WHERE parent_id = ? AND deleted_at IS NULL";
83
84/// Count active tasks with 'doing' status
85pub const COUNT_TASKS_DOING: &str =
86    "SELECT COUNT(*) FROM tasks WHERE status = 'doing' AND deleted_at IS NULL";
87
88/// Count active incomplete tasks (todo or doing)
89pub const COUNT_TASKS_INCOMPLETE: &str =
90    "SELECT COUNT(*) FROM tasks WHERE status IN ('todo', 'doing') AND deleted_at IS NULL";
91
92/// Count active incomplete tasks excluding specific task
93pub const COUNT_INCOMPLETE_TASKS_EXCLUDE: &str =
94    "SELECT COUNT(*) FROM tasks WHERE status != 'done' AND id != ? AND deleted_at IS NULL";
95
96// ============================================================================
97// Event Queries
98// ============================================================================
99
100/// Standard column list for event queries
101///
102/// Columns: id, task_id, timestamp, log_type, discussion_data
103pub const EVENT_COLUMNS: &str = "id, task_id, timestamp, log_type, discussion_data";
104
105/// Base SELECT query for events
106///
107/// Returns all event columns. Add WHERE clauses as needed.
108pub const SELECT_EVENT_FULL: &str = const_format::formatcp!("SELECT {} FROM events", EVENT_COLUMNS);
109
110/// SELECT event with WHERE id = ? condition
111pub const SELECT_EVENT_BY_ID: &str =
112    const_format::formatcp!("SELECT {} FROM events WHERE id = ?", EVENT_COLUMNS);
113
114/// Base SELECT query for events with dynamic WHERE clause building
115pub const SELECT_EVENT_BASE: &str =
116    const_format::formatcp!("SELECT {} FROM events WHERE 1=1", EVENT_COLUMNS);
117
118/// Count total events
119pub const COUNT_EVENTS_TOTAL: &str = "SELECT COUNT(*) FROM events";
120
121/// Count events for a specific task
122pub const COUNT_EVENTS_FOR_TASK: &str = "SELECT COUNT(*) FROM events WHERE task_id = ?";
123
124/// Check if a task exists (also used by event validation)
125pub const CHECK_TASK_EXISTS_FOR_EVENT: &str = CHECK_TASK_EXISTS;
126
127// ============================================================================
128// Full-Text Search (FTS5) Queries
129// ============================================================================
130
131/// Base query for task FTS5 search
132pub const SELECT_TASKS_FTS_BASE: &str = "SELECT rowid FROM tasks_fts WHERE ";
133
134/// Count task FTS5 matches
135pub const COUNT_TASKS_FTS: &str = "SELECT COUNT(*) FROM tasks_fts WHERE name MATCH ?";
136
137// ============================================================================
138// Tests
139// ============================================================================
140
141#[cfg(test)]
142mod tests {
143    use super::*;
144
145    #[test]
146    fn test_task_columns_format() {
147        assert!(TASK_COLUMNS.contains("id"));
148        assert!(TASK_COLUMNS.contains("parent_id"));
149        assert!(TASK_COLUMNS.contains("spec"));
150        assert!(TASK_COLUMNS.contains("active_form"));
151    }
152
153    #[test]
154    fn test_task_columns_no_spec_format() {
155        assert!(TASK_COLUMNS_NO_SPEC.contains("NULL as spec"));
156        assert!(TASK_COLUMNS_NO_SPEC.contains("active_form"));
157    }
158
159    #[test]
160    fn test_event_columns_format() {
161        assert!(EVENT_COLUMNS.contains("id"));
162        assert!(EVENT_COLUMNS.contains("task_id"));
163        assert!(EVENT_COLUMNS.contains("discussion_data"));
164    }
165
166    #[test]
167    fn test_select_task_full() {
168        assert_eq!(
169            SELECT_TASK_FULL,
170            "SELECT id, parent_id, name, spec, status, complexity, priority, first_todo_at, first_doing_at, first_done_at, active_form, owner, metadata FROM tasks"
171        );
172    }
173
174    #[test]
175    fn test_select_event_by_id() {
176        assert_eq!(
177            SELECT_EVENT_BY_ID,
178            "SELECT id, task_id, timestamp, log_type, discussion_data FROM events WHERE id = ?"
179        );
180    }
181
182    #[test]
183    fn test_check_task_exists() {
184        assert_eq!(
185            CHECK_TASK_EXISTS,
186            "SELECT EXISTS(SELECT 1 FROM tasks WHERE id = ? AND deleted_at IS NULL)"
187        );
188    }
189}
intent_engine/sql_constants.rs

intent_engine/
sql_constants.rs
