lore_engine/engine/
db.rs

1//! `SQLite` database layer — schema, CRUD, FTS, and link storage.
2//!
3//! All functions take a `&Connection` (no global state). The database lives
4//! at `<vault>/.lore/lore.db` and is opened via [`open_db`].
5
6use super::error::LoreError;
7use rusqlite::{params, Connection, OptionalExtension};
8use serde::Serialize;
9use std::path::Path;
10
11const SCHEMA_SQL: &str = r"
12CREATE TABLE IF NOT EXISTS pages (
13    id              INTEGER PRIMARY KEY AUTOINCREMENT,
14    slug            TEXT NOT NULL UNIQUE CHECK(length(slug) > 0),
15    title           TEXT NOT NULL CHECK(length(title) > 0),
16    file_path       TEXT,
17    content_hash    TEXT,
18    is_placeholder  INTEGER NOT NULL DEFAULT 0 CHECK(is_placeholder IN (0, 1)),
19    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
20    updated_at      TEXT NOT NULL DEFAULT (datetime('now'))
21);
22
23CREATE INDEX IF NOT EXISTS idx_pages_slug ON pages(slug);
24CREATE INDEX IF NOT EXISTS idx_pages_file_path ON pages(file_path);
25
26CREATE TABLE IF NOT EXISTS links (
27    id              INTEGER PRIMARY KEY AUTOINCREMENT,
28    source_slug     TEXT NOT NULL,
29    target_slug     TEXT NOT NULL,
30    alias           TEXT,
31    line_number     INTEGER,
32    FOREIGN KEY (source_slug) REFERENCES pages(slug) ON DELETE CASCADE,
33    FOREIGN KEY (target_slug) REFERENCES pages(slug) ON DELETE CASCADE
34);
35
36CREATE INDEX IF NOT EXISTS idx_links_source ON links(source_slug);
37CREATE INDEX IF NOT EXISTS idx_links_target ON links(target_slug);
38CREATE UNIQUE INDEX IF NOT EXISTS idx_links_unique ON links(source_slug, target_slug, line_number);
39
40CREATE TABLE IF NOT EXISTS metadata (
41    key     TEXT PRIMARY KEY,
42    value   TEXT NOT NULL
43);
44
45CREATE VIRTUAL TABLE IF NOT EXISTS pages_fts USING fts5(
46    slug,
47    title,
48    content,
49    tokenize='porter unicode61'
50);
51";
52
53/// A row from the `pages` table.
54#[derive(Debug, Serialize, Clone)]
55pub struct PageRow {
56    pub id: i64,
57    pub slug: String,
58    pub title: String,
59    /// Relative path from vault root (forward slashes). `None` for placeholders.
60    pub file_path: Option<String>,
61    /// SHA-256 hex digest of file content. `None` for placeholders.
62    pub content_hash: Option<String>,
63    /// True if this page was auto-created as a link target with no backing file.
64    pub is_placeholder: bool,
65}
66
67/// A row from the `links` table.
68#[derive(Debug, Serialize, Clone)]
69pub struct LinkRow {
70    pub source_slug: String,
71    pub target_slug: String,
72    /// Display text override (`[[target|alias]]`).
73    pub alias: Option<String>,
74    /// 1-based line number where the link appears in the source file.
75    pub line_number: Option<i32>,
76}
77
78/// Open or create the `SQLite` database inside `<vault>/.lore/`.
79pub fn open_db(vault_path: &Path) -> Result<Connection, LoreError> {
80    let lore_dir = vault_path.join(".lore");
81    std::fs::create_dir_all(&lore_dir)?;
82
83    let db_path = lore_dir.join("lore.db");
84    let conn = Connection::open(&db_path)?;
85
86    conn.execute_batch("PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON;")?;
87    run_migrations(&conn)?;
88
89    Ok(conn)
90}
91
92fn run_migrations(conn: &Connection) -> Result<(), LoreError> {
93    conn.execute_batch(SCHEMA_SQL)?;
94
95    let version: Option<String> = conn
96        .query_row(
97            "SELECT value FROM metadata WHERE key = 'schema_version'",
98            [],
99            |row| row.get(0),
100        )
101        .optional()?;
102
103    if version.is_none() {
104        conn.execute(
105            "INSERT OR REPLACE INTO metadata (key, value) VALUES ('schema_version', '1')",
106            [],
107        )?;
108        log::info!("Database initialized with schema v1");
109    }
110
111    Ok(())
112}
113
114/// Upsert a page (insert or update on slug conflict).
115pub fn upsert_page(
116    conn: &Connection,
117    slug: &str,
118    title: &str,
119    file_path: Option<&str>,
120    content_hash: Option<&str>,
121    is_placeholder: bool,
122) -> Result<(), LoreError> {
123    conn.execute(
124        "INSERT INTO pages (slug, title, file_path, content_hash, is_placeholder)
125         VALUES (?1, ?2, ?3, ?4, ?5)
126         ON CONFLICT(slug) DO UPDATE SET
127             title = excluded.title,
128             file_path = excluded.file_path,
129             content_hash = excluded.content_hash,
130             is_placeholder = excluded.is_placeholder,
131             updated_at = datetime('now')",
132        params![slug, title, file_path, content_hash, i32::from(is_placeholder)],
133    )?;
134    Ok(())
135}
136
137/// Get a page by slug.
138pub fn get_page(conn: &Connection, slug: &str) -> Result<Option<PageRow>, LoreError> {
139    let row = conn
140        .query_row(
141            "SELECT id, slug, title, file_path, content_hash, is_placeholder
142             FROM pages WHERE slug = ?1",
143            params![slug],
144            |row| {
145                Ok(PageRow {
146                    id: row.get(0)?,
147                    slug: row.get(1)?,
148                    title: row.get(2)?,
149                    file_path: row.get(3)?,
150                    content_hash: row.get(4)?,
151                    is_placeholder: row.get::<_, i32>(5)? != 0,
152                })
153            },
154        )
155        .optional()?;
156    Ok(row)
157}
158
159/// Get all pages sorted by title.
160pub fn get_all_pages(conn: &Connection) -> Result<Vec<PageRow>, LoreError> {
161    let mut stmt = conn.prepare(
162        "SELECT id, slug, title, file_path, content_hash, is_placeholder
163         FROM pages ORDER BY title COLLATE NOCASE",
164    )?;
165    let rows = stmt.query_map([], |row| {
166        Ok(PageRow {
167            id: row.get(0)?,
168            slug: row.get(1)?,
169            title: row.get(2)?,
170            file_path: row.get(3)?,
171            content_hash: row.get(4)?,
172            is_placeholder: row.get::<_, i32>(5)? != 0,
173        })
174    })?;
175
176    let mut pages = Vec::new();
177    for row in rows {
178        pages.push(row?);
179    }
180    Ok(pages)
181}
182
183/// Get all content hashes for diffing: slug -> hash.
184pub fn get_all_hashes(
185    conn: &Connection,
186) -> Result<std::collections::HashMap<String, String>, LoreError> {
187    let mut stmt =
188        conn.prepare("SELECT slug, content_hash FROM pages WHERE content_hash IS NOT NULL")?;
189    let rows = stmt.query_map([], |row| {
190        Ok((row.get::<_, String>(0)?, row.get::<_, String>(1)?))
191    })?;
192
193    let mut map = std::collections::HashMap::new();
194    for row in rows {
195        let (slug, hash) = row?;
196        map.insert(slug, hash);
197    }
198    Ok(map)
199}
200
201/// Delete all links originating from a source slug.
202pub fn delete_links_from(conn: &Connection, source_slug: &str) -> Result<(), LoreError> {
203    conn.execute(
204        "DELETE FROM links WHERE source_slug = ?1",
205        params![source_slug],
206    )?;
207    Ok(())
208}
209
210/// Insert a batch of links.
211pub fn insert_links(conn: &Connection, links: &[LinkRow]) -> Result<(), LoreError> {
212    let mut stmt = conn.prepare(
213        "INSERT OR IGNORE INTO links (source_slug, target_slug, alias, line_number)
214         VALUES (?1, ?2, ?3, ?4)",
215    )?;
216    for link in links {
217        stmt.execute(params![
218            link.source_slug,
219            link.target_slug,
220            link.alias,
221            link.line_number,
222        ])?;
223    }
224    Ok(())
225}
226
227/// Get backlinks: all pages that link TO a given slug.
228pub fn get_backlinks(conn: &Connection, target_slug: &str) -> Result<Vec<PageRow>, LoreError> {
229    let mut stmt = conn.prepare(
230        "SELECT p.id, p.slug, p.title, p.file_path, p.content_hash, p.is_placeholder
231         FROM links l
232         JOIN pages p ON p.slug = l.source_slug
233         WHERE l.target_slug = ?1
234         ORDER BY p.title COLLATE NOCASE",
235    )?;
236    let rows = stmt.query_map(params![target_slug], |row| {
237        Ok(PageRow {
238            id: row.get(0)?,
239            slug: row.get(1)?,
240            title: row.get(2)?,
241            file_path: row.get(3)?,
242            content_hash: row.get(4)?,
243            is_placeholder: row.get::<_, i32>(5)? != 0,
244        })
245    })?;
246
247    let mut pages = Vec::new();
248    for row in rows {
249        pages.push(row?);
250    }
251    Ok(pages)
252}
253
254/// Delete a page and its associated links and FTS entry.
255pub fn delete_page(conn: &Connection, slug: &str) -> Result<(), LoreError> {
256    conn.execute("DELETE FROM links WHERE source_slug = ?1", params![slug])?;
257    conn.execute("DELETE FROM links WHERE target_slug = ?1", params![slug])?;
258    delete_fts(conn, slug)?;
259    conn.execute("DELETE FROM pages WHERE slug = ?1", params![slug])?;
260    Ok(())
261}
262
263/// Rename a page: update its slug/title and all links that reference it.
264///
265/// Uses a SAVEPOINT with deferred foreign keys so the page slug can be
266/// updated before the link rows are migrated. On error, the savepoint
267/// is rolled back and the PRAGMA is always restored.
268pub fn rename_page(
269    conn: &Connection,
270    old_slug: &str,
271    new_slug: &str,
272    new_title: &str,
273    new_file_path: Option<&str>,
274) -> Result<(), LoreError> {
275    conn.execute_batch("PRAGMA defer_foreign_keys = ON;")?;
276    conn.execute_batch("SAVEPOINT rename_page;")?;
277
278    let result = (|| -> Result<(), LoreError> {
279        conn.execute(
280            "UPDATE pages SET slug = ?1, title = ?2, file_path = ?3, updated_at = datetime('now')
281             WHERE slug = ?4",
282            params![new_slug, new_title, new_file_path, old_slug],
283        )?;
284
285        conn.execute(
286            "UPDATE links SET target_slug = ?1 WHERE target_slug = ?2",
287            params![new_slug, old_slug],
288        )?;
289
290        conn.execute(
291            "UPDATE links SET source_slug = ?1 WHERE source_slug = ?2",
292            params![new_slug, old_slug],
293        )?;
294
295        Ok(())
296    })();
297
298    match result {
299        Ok(()) => {
300            conn.execute_batch("RELEASE rename_page;")?;
301        }
302        Err(ref _e) => {
303            let _ = conn.execute_batch("ROLLBACK TO rename_page;");
304            let _ = conn.execute_batch("RELEASE rename_page;");
305        }
306    }
307
308    // Always restore PRAGMA, regardless of success/failure
309    let _ = conn.execute_batch("PRAGMA defer_foreign_keys = OFF;");
310
311    result
312}
313
314/// Update FTS index for a page.
315pub fn update_fts(
316    conn: &Connection,
317    slug: &str,
318    title: &str,
319    content: &str,
320) -> Result<(), LoreError> {
321    delete_fts(conn, slug)?;
322    conn.execute(
323        "INSERT INTO pages_fts (slug, title, content) VALUES (?1, ?2, ?3)",
324        params![slug, title, content],
325    )?;
326    Ok(())
327}
328
329/// Delete a page's FTS entry.
330pub fn delete_fts(conn: &Connection, slug: &str) -> Result<(), LoreError> {
331    conn.execute("DELETE FROM pages_fts WHERE slug = ?1", params![slug])?;
332    Ok(())
333}
334
335/// Get all outgoing link target slugs for a given source page.
336pub fn get_outgoing_targets(conn: &Connection, source_slug: &str) -> Result<Vec<String>, LoreError> {
337    let mut stmt = conn.prepare("SELECT target_slug FROM links WHERE source_slug = ?1 ORDER BY target_slug")?;
338    let rows = stmt.query_map(params![source_slug], |row| row.get::<_, String>(0))?;
339    let mut targets = Vec::new();
340    for row in rows {
341        targets.push(row?);
342    }
343    Ok(targets)
344}
345
346/// Get all known slugs as a set (for checking page existence).
347pub fn get_all_slugs(conn: &Connection) -> Result<std::collections::HashSet<String>, LoreError> {
348    let mut stmt = conn.prepare("SELECT slug FROM pages ORDER BY slug")?;
349    let rows = stmt.query_map([], |row| row.get::<_, String>(0))?;
350    let mut set = std::collections::HashSet::new();
351    for row in rows {
352        set.insert(row?);
353    }
354    Ok(set)
355}
lore_engine/engine/db.rs

lore_engine/engine/
db.rs
