oxios_kernel/project/
project_db.rs

1//! Project-related SQLite operations.
2//!
3//! Extracted from `memory/database.rs` — project tables (`projects`,
4//! `project_memory`) are a kernel concern, not a memory concern.
5//! Uses `MemoryDatabase::conn()` for SQL execution.
6
7use anyhow::Result;
8
9use super::{Project, ProjectSource};
10
11/// Schema DDL for project tables.
12pub const PROJECT_SCHEMA: &str = r#"
13-- ─────────────────────────────────────────────
14-- Projects (RFC-011 / RFC-025)
15-- ─────────────────────────────────────────────
16CREATE TABLE IF NOT EXISTS projects (
17    id              TEXT PRIMARY KEY,
18    name            TEXT NOT NULL UNIQUE,
19    description     TEXT,
20    paths           TEXT,            -- JSON array of PathBuf strings
21    tags            TEXT,            -- JSON array of strings
22    emoji           TEXT NOT NULL DEFAULT '📦',
23    source          TEXT NOT NULL DEFAULT 'manual',
24    memory_visible  INTEGER NOT NULL DEFAULT 1,
25    created_at      TEXT NOT NULL,
26    updated_at      TEXT NOT NULL,
27    last_active_at  TEXT NOT NULL
28);
29
30CREATE INDEX IF NOT EXISTS idx_projects_name ON projects(name);
31
32-- ─────────────────────────────────────────────
33-- Project-Memory junction (RFC-011)
34-- ─────────────────────────────────────────────
35CREATE TABLE IF NOT EXISTS project_memory (
36    project_id  TEXT NOT NULL,
37    memory_id   TEXT NOT NULL,
38    created_at  TEXT NOT NULL DEFAULT (datetime('now')),
39    PRIMARY KEY (project_id, memory_id)
40);
41
42CREATE INDEX IF NOT EXISTS idx_pm_project ON project_memory(project_id);
43CREATE INDEX IF NOT EXISTS idx_pm_memory ON project_memory(memory_id);
44"#;
45
46/// Migration: add RFC-025 columns (`mount_ids`, `instructions`) to `projects`.
47/// Idempotent — checks `PRAGMA table_info` first.
48pub fn migrate_rfc025(conn: &rusqlite::Connection) -> Result<()> {
49    // Check existing columns.
50    let mut stmt = conn.prepare("PRAGMA table_info(projects)")?;
51    let cols: Vec<String> = stmt
52        .query_map([], |row| row.get::<_, String>(1))?
53        .filter_map(|r| r.ok())
54        .collect();
55
56    if !cols.iter().any(|c| c == "mount_ids") {
57        conn.execute_batch(
58            "ALTER TABLE projects ADD COLUMN mount_ids TEXT NOT NULL DEFAULT '[]';",
59        )?;
60    }
61    if !cols.iter().any(|c| c == "instructions") {
62        conn.execute_batch(
63            "ALTER TABLE projects ADD COLUMN instructions TEXT NOT NULL DEFAULT '';",
64        )?;
65    }
66    Ok(())
67}
68
69/// Ensure project tables exist in the database.
70pub fn ensure_project_schema(conn: &rusqlite::Connection) -> Result<()> {
71    conn.execute_batch(PROJECT_SCHEMA)?;
72    migrate_rfc025(conn)?;
73    Ok(())
74}
75
76/// Save a project (insert or replace).
77pub fn save_project(conn: &rusqlite::Connection, project: &Project) -> Result<()> {
78    conn.execute(
79        "INSERT OR REPLACE INTO projects
80         (id, name, description, paths, tags, emoji, source, memory_visible,
81          mount_ids, instructions, created_at, updated_at, last_active_at)
82         VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13)
83         ON CONFLICT(id) DO UPDATE SET
84            name=excluded.name, description=excluded.description, paths=excluded.paths,
85            tags=excluded.tags, emoji=excluded.emoji, source=excluded.source,
86            memory_visible=excluded.memory_visible, mount_ids=excluded.mount_ids,
87            instructions=excluded.instructions, updated_at=excluded.updated_at,
88            last_active_at=excluded.last_active_at",
89        rusqlite::params![
90            project.id.to_string(),
91            project.name,
92            project.description,
93            serde_json::to_string(&project.paths)?,
94            serde_json::to_string(&project.tags)?,
95            project.emoji,
96            project.source.to_string(),
97            project.memory_visible as i32,
98            serde_json::to_string(&project.mount_ids)?,
99            project.instructions,
100            project.created_at.to_rfc3339(),
101            project.updated_at.to_rfc3339(),
102            project.last_active_at.to_rfc3339(),
103        ],
104    )?;
105    Ok(())
106}
107
108/// List all projects.
109pub fn list_projects(conn: &rusqlite::Connection) -> Result<Vec<Project>> {
110    let mut stmt = conn.prepare(
111        "SELECT id, name, description, paths, tags, emoji, source, memory_visible,
112                mount_ids, instructions, created_at, updated_at, last_active_at
113         FROM projects ORDER BY name",
114    )?;
115    let rows = stmt.query_map([], row_to_project)?;
116    rows.collect::<Result<Vec<_>, _>>().map_err(Into::into)
117}
118
119/// Delete a project by ID.
120pub fn delete_project(conn: &rusqlite::Connection, id: &str) -> Result<()> {
121    // Delete junction entries first
122    conn.execute(
123        "DELETE FROM project_memory WHERE project_id = ?1",
124        rusqlite::params![id],
125    )?;
126    conn.execute("DELETE FROM projects WHERE id = ?1", rusqlite::params![id])?;
127    Ok(())
128}
129
130/// Link a memory to a project.
131pub fn link_project_memory(
132    conn: &rusqlite::Connection,
133    project_id: &str,
134    memory_id: &str,
135) -> Result<()> {
136    conn.execute(
137        "INSERT OR IGNORE INTO project_memory (project_id, memory_id, created_at) VALUES (?1, ?2, datetime('now'))",
138        rusqlite::params![project_id, memory_id],
139    )?;
140    Ok(())
141}
142
143/// Unlink a memory from a project.
144pub fn unlink_project_memory(
145    conn: &rusqlite::Connection,
146    project_id: &str,
147    memory_id: &str,
148) -> Result<()> {
149    conn.execute(
150        "DELETE FROM project_memory WHERE project_id = ?1 AND memory_id = ?2",
151        rusqlite::params![project_id, memory_id],
152    )?;
153    Ok(())
154}
155
156/// Get all memory IDs associated with a project.
157pub fn get_project_memory_ids(
158    conn: &rusqlite::Connection,
159    project_id: &str,
160) -> Result<Vec<String>> {
161    let mut stmt = conn.prepare(
162        "SELECT memory_id FROM project_memory WHERE project_id = ?1 ORDER BY created_at DESC",
163    )?;
164    let rows = stmt.query_map(rusqlite::params![project_id], |row| row.get(0))?;
165    rows.collect::<Result<Vec<_>, _>>().map_err(Into::into)
166}
167
168/// Convert a SQLite row into a Project struct.
169fn row_to_project(row: &rusqlite::Row<'_>) -> rusqlite::Result<Project> {
170    use chrono::{DateTime, Utc};
171    use std::path::PathBuf;
172
173    let id_str: String = row.get(0)?;
174    let name: String = row.get(1)?;
175    let description: String = row.get::<_, Option<String>>(2)?.unwrap_or_default();
176    let paths_str: String = row
177        .get::<_, Option<String>>(3)?
178        .unwrap_or_else(|| "[]".to_string());
179    let tags_str: String = row
180        .get::<_, Option<String>>(4)?
181        .unwrap_or_else(|| "[]".to_string());
182    let emoji: String = row
183        .get::<_, Option<String>>(5)?
184        .unwrap_or_else(|| "📦".to_string());
185    let source_str: String = row
186        .get::<_, Option<String>>(6)?
187        .unwrap_or_else(|| "manual".to_string());
188    let memory_visible: bool = row.get::<_, Option<i32>>(7)?.unwrap_or(1) != 0;
189    let mount_ids_str: String = row
190        .get::<_, Option<String>>(8)?
191        .unwrap_or_else(|| "[]".to_string());
192    let instructions: String = row.get::<_, Option<String>>(9)?.unwrap_or_default();
193    let created_at: String = row.get(10)?;
194    let updated_at: String = row.get(11)?;
195    let last_active_at: String = row.get(12)?;
196
197    let id = uuid::Uuid::parse_str(&id_str).map_err(|e| {
198        rusqlite::Error::FromSqlConversionFailure(0, rusqlite::types::Type::Text, Box::new(e))
199    })?;
200    let paths: Vec<PathBuf> = serde_json::from_str(&paths_str).unwrap_or_default();
201    let tags: Vec<String> = serde_json::from_str(&tags_str).unwrap_or_default();
202    let mount_ids: Vec<crate::mount::MountId> =
203        serde_json::from_str(&mount_ids_str).unwrap_or_default();
204    let source = match source_str.as_str() {
205        "auto_detected" => ProjectSource::AutoDetected,
206        _ => ProjectSource::Manual,
207    };
208
209    Ok(Project {
210        id,
211        name,
212        description,
213        paths,
214        tags,
215        emoji,
216        source,
217        memory_visible,
218        mount_ids,
219        instructions,
220        created_at: created_at
221            .parse::<DateTime<Utc>>()
222            .unwrap_or_else(|_| Utc::now()),
223        updated_at: updated_at
224            .parse::<DateTime<Utc>>()
225            .unwrap_or_else(|_| Utc::now()),
226        last_active_at: last_active_at
227            .parse::<DateTime<Utc>>()
228            .unwrap_or_else(|_| Utc::now()),
229    })
230}
oxios_kernel/project/project_db.rs

oxios_kernel/project/
project_db.rs
