biblion/db/
bbt.rs

1//! BBT (Better BibTeX) citekey database reader.
2//!
3//! # What this does
4//!
5//! Better BibTeX assigns citation keys to Zotero items. These keys (like
6//! "demilloHintsTestData1978") are the primary way users reference items
7//! in the MCP tools. We need to map them to Zotero's internal 8-char item
8//! keys (like "9MS26VH5").
9//!
10//! # The `better-bibtex.migrated` file
11//!
12//! BBT stores its citekey assignments in `~/Zotero/better-bibtex.migrated`,
13//! a SQLite database with a `citationkey` table:
14//!
15//! ```sql
16//! CREATE TABLE citationkey (
17//!     itemID INTEGER,
18//!     itemKey TEXT,
19//!     libraryID INTEGER,
20//!     citationKey TEXT,
21//!     pinned INTEGER
22//! );
23//! ```
24//!
25//! This is much faster than calling BBT's JSON-RPC API (~0.01ms vs ~300ms).
26
27use std::collections::HashMap;
28use std::path::Path;
29
30use anyhow::{Context, Result};
31use rusqlite::{Connection, OptionalExtension};
32
33/// Read-only connection to the BBT citekey database.
34pub struct BbtDb {
35    conn: Connection,
36}
37
38impl BbtDb {
39    /// Open the BBT database in read-only mode.
40    pub fn open(path: &Path) -> Result<Self> {
41        let uri = format!("file:{}?mode=ro", path.display());
42        let conn = Connection::open_with_flags(
43            &uri,
44            rusqlite::OpenFlags::SQLITE_OPEN_READ_ONLY | rusqlite::OpenFlags::SQLITE_OPEN_URI,
45        )
46        .with_context(|| format!("Failed to open BBT database: {}", path.display()))?;
47        Ok(Self { conn })
48    }
49
50    /// Look up a citekey by Zotero item key (e.g., "9MS26VH5" → "demilloHintsTestData1978").
51    pub fn citekey_for_item_key(&self, item_key: &str) -> Result<Option<String>> {
52        let mut stmt = self
53            .conn
54            .prepare_cached("SELECT citationKey FROM citationkey WHERE itemKey = ?1")?;
55        let result = stmt
56            .query_row([item_key], |row| row.get::<_, String>(0))
57            .optional()?;
58        Ok(result)
59    }
60
61    /// Look up a Zotero item key by citekey (e.g., "demilloHintsTestData1978" → "9MS26VH5").
62    pub fn item_key_for_citekey(&self, citekey: &str) -> Result<Option<String>> {
63        let mut stmt = self
64            .conn
65            .prepare_cached("SELECT itemKey FROM citationkey WHERE citationKey = ?1")?;
66        let result = stmt
67            .query_row([citekey], |row| row.get::<_, String>(0))
68            .optional()?;
69        Ok(result)
70    }
71
72    /// Load the complete citekey→itemKey mapping into memory.
73    ///
74    /// Returns a HashMap with ~1000 entries (~43KB). Used for batch operations
75    /// and for populating the in-memory cache at startup.
76    pub fn all_citekeys(&self) -> Result<HashMap<String, String>> {
77        let mut stmt = self.conn.prepare(
78            "SELECT citationKey, itemKey FROM citationkey WHERE citationKey IS NOT NULL",
79        )?;
80        let rows = stmt.query_map([], |row| {
81            Ok((row.get::<_, String>(0)?, row.get::<_, String>(1)?))
82        })?;
83        let mut map = HashMap::new();
84        for row in rows {
85            let (ck, ik) = row?;
86            map.insert(ck, ik);
87        }
88        Ok(map)
89    }
90}
91
92// ---------------------------------------------------------------------------
93// Zotero-native citekey fallback
94// ---------------------------------------------------------------------------
95
96/// Read citekeys directly from `zotero.sqlite` (field name = "citationKey").
97///
98/// BBT stores citekeys as item metadata fields in Zotero's EAV schema.
99/// This is the most reliable source — it covers 99.9% of items, even
100/// those not yet indexed in `better-bibtex.migrated`.
101pub fn citekey_from_zotero_sqlite(conn: &Connection, item_key: &str) -> Result<Option<String>> {
102    let mut stmt = conn.prepare_cached(
103        "SELECT iv.value FROM items i
104         JOIN itemData id ON i.itemID = id.itemID
105         JOIN fields f ON id.fieldID = f.fieldID
106         JOIN itemDataValues iv ON id.valueID = iv.valueID
107         WHERE f.fieldName = 'citationKey' AND i.key = ?1",
108    )?;
109    let result = stmt
110        .query_row([item_key], |row| row.get::<_, String>(0))
111        .optional()?;
112    Ok(result)
113}
114
115/// Reverse lookup: find item key by citekey in `zotero.sqlite`.
116pub fn item_key_from_zotero_sqlite(conn: &Connection, citekey: &str) -> Result<Option<String>> {
117    let mut stmt = conn.prepare_cached(
118        "SELECT i.key FROM items i
119         JOIN itemData id ON i.itemID = id.itemID
120         JOIN fields f ON id.fieldID = f.fieldID
121         JOIN itemDataValues iv ON id.valueID = iv.valueID
122         WHERE f.fieldName = 'citationKey' AND iv.value = ?1
123         AND i.libraryID = 1
124         AND i.itemID NOT IN (SELECT itemID FROM deletedItems)",
125    )?;
126    let result = stmt
127        .query_row([citekey], |row| row.get::<_, String>(0))
128        .optional()?;
129    Ok(result)
130}
131
132/// Load all citekeys from `zotero.sqlite` (most complete source).
133pub fn all_citekeys_from_zotero_sqlite(conn: &Connection) -> Result<HashMap<String, String>> {
134    let mut stmt = conn.prepare(
135        "SELECT iv.value, i.key FROM items i
136         JOIN itemData id ON i.itemID = id.itemID
137         JOIN fields f ON id.fieldID = f.fieldID
138         JOIN itemDataValues iv ON id.valueID = iv.valueID
139         WHERE f.fieldName = 'citationKey'
140         AND i.libraryID = 1
141         AND i.itemID NOT IN (SELECT itemID FROM deletedItems)",
142    )?;
143    let rows = stmt.query_map([], |row| {
144        Ok((row.get::<_, String>(0)?, row.get::<_, String>(1)?))
145    })?;
146    let mut map = HashMap::new();
147    for row in rows {
148        let (ck, ik) = row?;
149        map.insert(ck, ik);
150    }
151    Ok(map)
152}
153
154// Uses rusqlite::OptionalExtension for .optional() on query_row results.
155
156#[cfg(test)]
157mod tests {
158    use super::*;
159    use rusqlite::Connection;
160
161    /// Create an in-memory BBT database for testing.
162    fn test_bbt_db() -> BbtDb {
163        let conn = Connection::open_in_memory().unwrap();
164        conn.execute_batch(
165            "CREATE TABLE citationkey (
166                itemID INTEGER,
167                itemKey TEXT,
168                libraryID INTEGER,
169                citationKey TEXT,
170                pinned INTEGER
171            );
172            INSERT INTO citationkey VALUES (1, 'ABC12345', 1, 'demilloHintsTestData1978', 0);
173            INSERT INTO citationkey VALUES (2, 'DEF67890', 1, 'jiaAnalysisSurvey2011', 1);
174            INSERT INTO citationkey VALUES (3, 'GHI11111', 1, NULL, 0);",
175        )
176        .unwrap();
177        BbtDb { conn }
178    }
179
180    #[test]
181    fn citekey_for_item_key_found() {
182        let db = test_bbt_db();
183        let ck = db.citekey_for_item_key("ABC12345").unwrap();
184        assert_eq!(ck, Some("demilloHintsTestData1978".into()));
185    }
186
187    #[test]
188    fn citekey_for_item_key_not_found() {
189        let db = test_bbt_db();
190        let ck = db.citekey_for_item_key("ZZZZZZZZ").unwrap();
191        assert_eq!(ck, None);
192    }
193
194    #[test]
195    fn item_key_for_citekey_found() {
196        let db = test_bbt_db();
197        let ik = db.item_key_for_citekey("jiaAnalysisSurvey2011").unwrap();
198        assert_eq!(ik, Some("DEF67890".into()));
199    }
200
201    #[test]
202    fn item_key_for_citekey_not_found() {
203        let db = test_bbt_db();
204        let ik = db.item_key_for_citekey("nonexistent2099").unwrap();
205        assert_eq!(ik, None);
206    }
207
208    #[test]
209    fn all_citekeys_excludes_null() {
210        let db = test_bbt_db();
211        let map = db.all_citekeys().unwrap();
212        assert_eq!(map.len(), 2); // GHI11111 has NULL citationKey, excluded
213        assert_eq!(map["demilloHintsTestData1978"], "ABC12345");
214        assert_eq!(map["jiaAnalysisSurvey2011"], "DEF67890");
215    }
216}
217
218#[cfg(test)]
219mod zotero_sqlite_tests {
220    use super::*;
221    use crate::test_helpers::test_zotero_db;
222
223    #[test]
224    fn citekey_from_zotero_sqlite_found() {
225        let zdb = test_zotero_db();
226        let ck = citekey_from_zotero_sqlite(zdb.conn(), "ABC12345").unwrap();
227        assert_eq!(ck, Some("demilloHintsTestData1978".into()));
228    }
229
230    #[test]
231    fn citekey_from_zotero_sqlite_not_found() {
232        let zdb = test_zotero_db();
233        let ck = citekey_from_zotero_sqlite(zdb.conn(), "ZZZZZZZZ").unwrap();
234        assert_eq!(ck, None);
235    }
236
237    #[test]
238    fn item_key_from_zotero_sqlite_found() {
239        let zdb = test_zotero_db();
240        let ik = item_key_from_zotero_sqlite(zdb.conn(), "demilloHintsTestData1978").unwrap();
241        assert_eq!(ik, Some("ABC12345".into()));
242    }
243
244    #[test]
245    fn item_key_from_zotero_sqlite_not_found() {
246        let zdb = test_zotero_db();
247        let ik = item_key_from_zotero_sqlite(zdb.conn(), "nonexistent2099").unwrap();
248        assert_eq!(ik, None);
249    }
250
251    #[test]
252    fn all_citekeys_from_zotero_sqlite_returns_all() {
253        let zdb = test_zotero_db();
254        let map = all_citekeys_from_zotero_sqlite(zdb.conn()).unwrap();
255        assert_eq!(map.len(), 2);
256        assert_eq!(map["demilloHintsTestData1978"], "ABC12345");
257        assert_eq!(map["artTesting2020"], "DEF67890");
258    }
259
260    #[test]
261    fn all_citekeys_from_zotero_sqlite_values_are_item_keys() {
262        let zdb = test_zotero_db();
263        let map = all_citekeys_from_zotero_sqlite(zdb.conn()).unwrap();
264        // Keys are citekeys, values are item keys (8-char)
265        for (ck, ik) in &map {
266            assert!(!ck.is_empty(), "Citekey should not be empty");
267            assert!(!ik.is_empty(), "Item key should not be empty");
268            assert_eq!(ik.len(), 8, "Item key should be 8 chars: {ik}");
269        }
270    }
271}
biblion/db/bbt.rs

biblion/db/
bbt.rs
