Skip to main content

knowledge/
store.rs

1//! The store: schema, versioning and the writes the indexer makes.
2
3use std::path::Path;
4use std::time::Duration;
5
6use rusqlite::{Connection, OptionalExtension, params};
7
8use crate::extract::{Call, Construct, Extracted, Import, Symbol};
9use crate::relations::{Kind, Proposed, Relation};
10
11/// The schema the kernel reader (`rlm/knowledge_api.py`) is written against. Bump it with every change to a table the
12/// reader uses, and teach the reader the new version in the same change.
13pub const SCHEMA_VERSION: i64 = 5;
14
15/// Characters of a note's body kept.
16pub const NOTE_CHARS: usize = 2000;
17
18/// Trees kept per repository: enough for the reviews in flight and their re-reviews; older ones are dropped.
19pub const TREES_PER_REPO: i64 = 50;
20
21const SCHEMA: &str = "
22CREATE TABLE IF NOT EXISTS meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);
23CREATE TABLE IF NOT EXISTS repos (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
24-- One row per indexed blob; `extractor` is the extractor version that read it.
25CREATE TABLE IF NOT EXISTS blobs (oid TEXT PRIMARY KEY, lang TEXT NOT NULL, extractor INTEGER NOT NULL);
26-- What a blob is made of: each construct kind once, at its first line, with how often it occurs.
27CREATE TABLE IF NOT EXISTS constructs (
28    blob TEXT NOT NULL, kind TEXT NOT NULL, line INTEGER NOT NULL, count INTEGER NOT NULL,
29    PRIMARY KEY (blob, kind)
30) WITHOUT ROWID;
31CREATE INDEX IF NOT EXISTS constructs_kind ON constructs (kind, blob);
32-- Schema 4 added `end_line` and `parent` (the symbol whose definition contains it).
33CREATE TABLE IF NOT EXISTS symbols (
34    blob TEXT NOT NULL, name TEXT NOT NULL, kind TEXT NOT NULL, line INTEGER NOT NULL, end_line INTEGER, parent TEXT
35);
36CREATE INDEX IF NOT EXISTS symbols_blob ON symbols (blob);
37CREATE INDEX IF NOT EXISTS symbols_name ON symbols (name);
38-- Schema 4. The graph a review queries (`knowledge_api.Knowledge.query`): call sites by the symbol they are made
39-- from, and the names each import brings in, unresolved.
40CREATE TABLE IF NOT EXISTS calls (
41    blob TEXT NOT NULL, caller TEXT, callee TEXT NOT NULL, receiver TEXT, line INTEGER NOT NULL
42);
43CREATE INDEX IF NOT EXISTS calls_blob ON calls (blob);
44CREATE INDEX IF NOT EXISTS calls_callee ON calls (callee);
45CREATE TABLE IF NOT EXISTS imports (blob TEXT NOT NULL, module TEXT NOT NULL, name TEXT, line INTEGER NOT NULL);
46CREATE INDEX IF NOT EXISTS imports_blob ON imports (blob);
47CREATE INDEX IF NOT EXISTS imports_module ON imports (module);
48-- A repository at one commit: its indexable paths and their blobs.
49CREATE TABLE IF NOT EXISTS trees (
50    id INTEGER PRIMARY KEY, repo INTEGER NOT NULL, rev TEXT NOT NULL, indexed_at INTEGER NOT NULL,
51    UNIQUE (repo, rev)
52);
53CREATE TABLE IF NOT EXISTS entries (
54    tree INTEGER NOT NULL, path TEXT NOT NULL, blob TEXT NOT NULL,
55    PRIMARY KEY (tree, path)
56) WITHOUT ROWID;
57CREATE INDEX IF NOT EXISTS entries_blob ON entries (blob);
58-- Schema 2. Review comments left on the repository's pull requests, by anyone: what reviewers asked of a file,
59-- and whether it was addressed (`addressed`: the thread was resolved; `outdated`: only a later commit changed its
60-- lines; `open`).
61-- Schema 3. A human's addressed comment is a rule the investigators can ask for (`rules_for`): `human` (not a bot,
62-- not one of the reviewing accounts), `missed` (this reviewer reviewed the pull request and raised nothing there),
63-- and the fates of the findings that cited it (`accepted`, `rejected`), its credit.
64CREATE TABLE IF NOT EXISTS notes (
65    id INTEGER PRIMARY KEY, repo INTEGER NOT NULL, pull INTEGER NOT NULL, path TEXT NOT NULL, line INTEGER,
66    author TEXT NOT NULL, body TEXT NOT NULL, url TEXT NOT NULL, at TEXT NOT NULL, state TEXT NOT NULL,
67    human INTEGER NOT NULL DEFAULT 1, missed INTEGER NOT NULL DEFAULT 0,
68    accepted INTEGER NOT NULL DEFAULT 0, rejected INTEGER NOT NULL DEFAULT 0,
69    UNIQUE (repo, url)
70);
71CREATE INDEX IF NOT EXISTS notes_path ON notes (repo, path);
72-- Schema 5. Relations across files (`relations.rs`), read on demand for a task: what the proposer read from a blob
73-- (`relation_scans`: by which proposer version, even when it found nothing), each relation with its evidence and
74-- verdict (`status` accepted, rejected, review or proposed; `checked_by` code or the judge backend), and which
75-- files of a revision were read for relations (`relation_entries`, beside the indexer's `entries`).
76CREATE TABLE IF NOT EXISTS relation_scans (
77    blob TEXT NOT NULL, version INTEGER NOT NULL, PRIMARY KEY (blob, version)
78) WITHOUT ROWID;
79CREATE TABLE IF NOT EXISTS relations (
80    id INTEGER PRIMARY KEY, blob TEXT NOT NULL, kind TEXT NOT NULL, subject TEXT NOT NULL, object TEXT NOT NULL,
81    line INTEGER NOT NULL, quote TEXT NOT NULL, context TEXT NOT NULL, status TEXT NOT NULL, checked_by TEXT,
82    rung TEXT, model TEXT, confidence REAL
83);
84CREATE INDEX IF NOT EXISTS relations_blob ON relations (blob);
85CREATE TABLE IF NOT EXISTS relation_entries (
86    tree INTEGER NOT NULL, path TEXT NOT NULL, blob TEXT NOT NULL,
87    PRIMARY KEY (tree, path)
88) WITHOUT ROWID;
89CREATE INDEX IF NOT EXISTS relation_entries_blob ON relation_entries (blob);
90";
91
92/// A rule (a person's addressed review comment) with the fates of the findings that cited it.
93#[derive(Debug, Clone, PartialEq)]
94pub struct RuleCredit {
95    pub id: i64,
96    pub path: String,
97    pub body: String,
98    pub url: String,
99    pub accepted: i64,
100    pub rejected: i64,
101}
102
103/// A review comment, as [`Store::record_notes`] keeps it.
104#[derive(Clone, Debug, PartialEq, Eq)]
105pub struct Note {
106    pub pull: i64,
107    pub path: String,
108    pub line: Option<i64>,
109    pub author: String,
110    pub body: String,
111    /// The comment's URL: a note is recorded once, its state updated.
112    pub url: String,
113    /// ISO time the comment was made.
114    pub at: String,
115    /// `addressed` (resolved), `outdated` (a later commit changed its lines, unresolved) or `open`.
116    pub state: String,
117    /// Written by a person: not a bot, not a reviewing account. Only a person's addressed comment is a rule.
118    pub human: bool,
119    /// This reviewer reviewed the pull request and raised nothing at the comment's place: a miss to learn from.
120    pub missed: bool,
121}
122
123/// The judge's verdict on a relation.
124#[derive(Clone, Copy, Debug, PartialEq, Eq)]
125pub enum Settled {
126    Accepted,
127    Rejected,
128    /// The judge was not sure and nothing settled it: a person may.
129    Review,
130}
131
132impl Settled {
133    pub fn name(self) -> &'static str {
134        match self {
135            Settled::Accepted => "accepted",
136            Settled::Rejected => "rejected",
137            Settled::Review => "review",
138        }
139    }
140
141    pub fn parse(name: &str) -> Option<Settled> {
142        [Settled::Accepted, Settled::Rejected, Settled::Review]
143            .into_iter()
144            .find(|settled| settled.name() == name)
145    }
146}
147
148/// [`Store::settle_relations`]: one relation's verdict and who gave it.
149#[derive(Clone, Debug, PartialEq)]
150pub struct Settlement {
151    pub id: i64,
152    pub status: Settled,
153    pub backend: Option<String>,
154    pub rung: Option<String>,
155    pub model: Option<String>,
156    pub confidence: Option<f64>,
157}
158
159/// The columns schema 4 added to `symbols`. A store of an earlier schema has its blobs read again anyway: they were
160/// read by an older extractor.
161const SYMBOL_COLUMNS_4: [(&str, &str); 2] = [("end_line", "INTEGER"), ("parent", "TEXT")];
162
163/// The columns schema 3 added to `notes`, for a store of schema 2.
164const NOTE_COLUMNS_3: [(&str, &str); 4] = [
165    ("human", "INTEGER NOT NULL DEFAULT 1"),
166    ("missed", "INTEGER NOT NULL DEFAULT 0"),
167    ("accepted", "INTEGER NOT NULL DEFAULT 0"),
168    ("rejected", "INTEGER NOT NULL DEFAULT 0"),
169];
170
171#[derive(Debug)]
172pub struct Error(pub String);
173
174impl std::fmt::Display for Error {
175    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
176        f.write_str(&self.0)
177    }
178}
179
180impl std::error::Error for Error {}
181
182impl From<rusqlite::Error> for Error {
183    fn from(error: rusqlite::Error) -> Self {
184        Self(format!("knowledge store: {error}"))
185    }
186}
187
188pub struct Store {
189    conn: Connection,
190}
191
192impl Store {
193    /// Opens (creating when absent) the store at `path`, private to the user. WAL lets the kernel read while a
194    /// review indexes; writers wait for each other instead of failing.
195    pub fn open(path: &Path) -> Result<Self, Error> {
196        if let Some(parent) = path.parent() {
197            use std::os::unix::fs::DirBuilderExt;
198            std::fs::DirBuilder::new()
199                .recursive(true)
200                .mode(0o700)
201                .create(parent)
202                .map_err(|error| Error(format!("{}: {error}", parent.display())))?;
203        }
204        let conn = Connection::open(path)?;
205        use std::os::unix::fs::PermissionsExt;
206        let _ = std::fs::set_permissions(path, std::fs::Permissions::from_mode(0o600));
207        conn.busy_timeout(Duration::from_secs(60))?;
208        conn.pragma_update(None, "journal_mode", "WAL")?;
209        conn.pragma_update(None, "synchronous", "NORMAL")?;
210        conn.pragma_update(None, "foreign_keys", "ON")?;
211        let store = Self { conn };
212        store.migrate()?;
213        Ok(store)
214    }
215
216    fn migrate(&self) -> Result<(), Error> {
217        self.conn.execute_batch(SCHEMA)?;
218        let found: Option<String> = self
219            .conn
220            .query_row("SELECT value FROM meta WHERE key = 'schema_version'", [], |row| {
221                row.get(0)
222            })
223            .optional()?;
224        match found.as_deref().map(str::parse::<i64>) {
225            None => {
226                self.conn.execute(
227                    "INSERT INTO meta (key, value) VALUES ('schema_version', ?1)",
228                    params![SCHEMA_VERSION.to_string()],
229                )?;
230            }
231            Some(Ok(SCHEMA_VERSION)) => {}
232            // Schema 1 had no notes (the table was created above); schema 2's lack the rule columns; schema 3's
233            // symbols lack their ranges and parents; schema 4's relations tables were created above.
234            Some(Ok(old @ 1..=4)) => {
235                let columns = |table: &str| -> Result<Vec<String>, Error> {
236                    let mut statement = self
237                        .conn
238                        .prepare(&format!("SELECT name FROM pragma_table_info('{table}')"))?;
239                    let names = statement.query_map([], |row| row.get(0))?.collect::<Result<_, _>>()?;
240                    Ok(names)
241                };
242                let add = |table: &str, have: &[String], wanted: &[(&str, &str)]| -> Result<(), Error> {
243                    for (column, definition) in wanted {
244                        if !have.iter().any(|name| name == column) {
245                            self.conn
246                                .execute_batch(&format!("ALTER TABLE {table} ADD COLUMN {column} {definition}"))?;
247                        }
248                    }
249                    Ok(())
250                };
251                add("notes", &columns("notes")?, &NOTE_COLUMNS_3)?;
252                add("symbols", &columns("symbols")?, &SYMBOL_COLUMNS_4)?;
253                if old <= 2 {
254                    // Schema 2 counted an outdated thread as addressed: which of its addressed notes were resolved
255                    // is unknown, so none is a rule until the backfill, run again, reads the recent ones' threads.
256                    self.conn
257                        .execute("UPDATE notes SET state = 'outdated' WHERE state = 'addressed'", [])?;
258                    self.conn
259                        .execute("DELETE FROM meta WHERE key LIKE 'notes_backfilled:%'", [])?;
260                }
261                self.conn.execute(
262                    "UPDATE meta SET value = ?1 WHERE key = 'schema_version'",
263                    params![SCHEMA_VERSION.to_string()],
264                )?;
265            }
266            Some(Ok(version)) if version > SCHEMA_VERSION => {
267                return Err(Error(format!(
268                    "the knowledge store has schema {version}, newer than this build's {SCHEMA_VERSION}"
269                )));
270            }
271            Some(other) => {
272                return Err(Error(format!(
273                    "the knowledge store has an unknown schema version ({other:?})"
274                )));
275            }
276        }
277        Ok(())
278    }
279
280    /// The blobs among `oids` that are already indexed by extractor `version`.
281    pub fn known_blobs(&self, oids: &[&str], version: i64) -> Result<std::collections::HashSet<String>, Error> {
282        let mut known = std::collections::HashSet::new();
283        let mut statement = self
284            .conn
285            .prepare_cached("SELECT 1 FROM blobs WHERE oid = ?1 AND extractor = ?2")?;
286        for oid in oids {
287            if statement.exists(params![oid, version])? {
288                known.insert((*oid).to_string());
289            }
290        }
291        Ok(known)
292    }
293
294    /// Records one revision: the newly read blobs, then the tree's path-to-blob table, in one transaction. Returns
295    /// the tree's id.
296    pub fn record_tree(
297        &mut self,
298        repo: &str,
299        rev: &str,
300        entries: &[(String, String)],
301        blobs: &[(String, String, Extracted)],
302        version: i64,
303        indexed_at: i64,
304    ) -> Result<i64, Error> {
305        let tx = self.conn.transaction()?;
306        {
307            let mut blob_row = tx.prepare_cached(
308                "INSERT INTO blobs (oid, lang, extractor) VALUES (?1, ?2, ?3)
309                 ON CONFLICT (oid) DO UPDATE SET lang = excluded.lang, extractor = excluded.extractor",
310            )?;
311            let mut clear_constructs = tx.prepare_cached("DELETE FROM constructs WHERE blob = ?1")?;
312            let mut clear_symbols = tx.prepare_cached("DELETE FROM symbols WHERE blob = ?1")?;
313            let mut clear_calls = tx.prepare_cached("DELETE FROM calls WHERE blob = ?1")?;
314            let mut clear_imports = tx.prepare_cached("DELETE FROM imports WHERE blob = ?1")?;
315            let mut construct_row =
316                tx.prepare_cached("INSERT INTO constructs (blob, kind, line, count) VALUES (?1, ?2, ?3, ?4)")?;
317            let mut symbol_row = tx.prepare_cached(
318                "INSERT INTO symbols (blob, name, kind, line, end_line, parent) VALUES (?1, ?2, ?3, ?4, ?5, ?6)",
319            )?;
320            let mut call_row = tx.prepare_cached(
321                "INSERT INTO calls (blob, caller, callee, receiver, line) VALUES (?1, ?2, ?3, ?4, ?5)",
322            )?;
323            let mut import_row =
324                tx.prepare_cached("INSERT INTO imports (blob, module, name, line) VALUES (?1, ?2, ?3, ?4)")?;
325            for (oid, lang, extracted) in blobs {
326                blob_row.execute(params![oid, lang, version])?;
327                clear_constructs.execute(params![oid])?;
328                clear_symbols.execute(params![oid])?;
329                clear_calls.execute(params![oid])?;
330                clear_imports.execute(params![oid])?;
331                for Construct { kind, line, count } in &extracted.constructs {
332                    construct_row.execute(params![oid, kind, line, count])?;
333                }
334                for Symbol {
335                    name,
336                    kind,
337                    line,
338                    end_line,
339                    parent,
340                } in &extracted.symbols
341                {
342                    symbol_row.execute(params![oid, name, kind, line, end_line, parent])?;
343                }
344                for Call {
345                    caller,
346                    callee,
347                    receiver,
348                    line,
349                } in &extracted.calls
350                {
351                    call_row.execute(params![oid, caller, callee, receiver, line])?;
352                }
353                for Import { module, name, line } in &extracted.imports {
354                    import_row.execute(params![oid, module, name, line])?;
355                }
356            }
357        }
358        tx.execute(
359            "INSERT INTO repos (name) VALUES (?1) ON CONFLICT (name) DO NOTHING",
360            params![repo],
361        )?;
362        let repo_id: i64 = tx.query_row("SELECT id FROM repos WHERE name = ?1", params![repo], |row| row.get(0))?;
363        tx.execute(
364            "INSERT INTO trees (repo, rev, indexed_at) VALUES (?1, ?2, ?3)
365             ON CONFLICT (repo, rev) DO UPDATE SET indexed_at = excluded.indexed_at",
366            params![repo_id, rev, indexed_at],
367        )?;
368        let tree: i64 = tx.query_row(
369            "SELECT id FROM trees WHERE repo = ?1 AND rev = ?2",
370            params![repo_id, rev],
371            |row| row.get(0),
372        )?;
373        tx.execute("DELETE FROM entries WHERE tree = ?1", params![tree])?;
374        {
375            let mut entry_row = tx.prepare_cached("INSERT INTO entries (tree, path, blob) VALUES (?1, ?2, ?3)")?;
376            for (path, oid) in entries {
377                entry_row.execute(params![tree, path, oid])?;
378            }
379        }
380        // Older trees of this repository beyond the limit go, with the blobs no tree still holds. A tree read only
381        // for relations (a repository with no file the indexer reads) has no entries but is kept like the others.
382        for table in ["entries", "relation_entries"] {
383            tx.execute(
384                &format!(
385                    "DELETE FROM {table} WHERE tree IN (
386                         SELECT id FROM trees WHERE repo = ?1 ORDER BY indexed_at DESC, id DESC LIMIT -1 OFFSET ?2)"
387                ),
388                params![repo_id, TREES_PER_REPO],
389            )?;
390        }
391        let dropped = tx.execute(
392            "DELETE FROM trees WHERE repo = ?1 AND id != ?2
393                 AND id NOT IN (SELECT tree FROM entries UNION SELECT tree FROM relation_entries)",
394            params![repo_id, tree],
395        )?;
396        if dropped > 0 {
397            tx.execute_batch(
398                "DELETE FROM constructs WHERE blob NOT IN (SELECT blob FROM entries);
399                 DELETE FROM symbols WHERE blob NOT IN (SELECT blob FROM entries);
400                 DELETE FROM calls WHERE blob NOT IN (SELECT blob FROM entries);
401                 DELETE FROM imports WHERE blob NOT IN (SELECT blob FROM entries);
402                 DELETE FROM blobs WHERE oid NOT IN (SELECT blob FROM entries);
403                 DELETE FROM relations WHERE blob NOT IN (SELECT blob FROM relation_entries);
404                 DELETE FROM relation_scans WHERE blob NOT IN (SELECT blob FROM relation_entries);",
405            )?;
406        }
407        tx.commit()?;
408        Ok(tree)
409    }
410
411    /// Records review comments of `repo`, in one transaction: a new one is added, a known one (by URL) gets its
412    /// current state, line and body. Returns how many were new.
413    pub fn record_notes(&mut self, repo: &str, notes: &[Note]) -> Result<usize, Error> {
414        let tx = self.conn.transaction()?;
415        tx.execute(
416            "INSERT INTO repos (name) VALUES (?1) ON CONFLICT (name) DO NOTHING",
417            params![repo],
418        )?;
419        let repo_id: i64 = tx.query_row("SELECT id FROM repos WHERE name = ?1", params![repo], |row| row.get(0))?;
420        let mut added = 0;
421        {
422            let mut known = tx.prepare_cached("SELECT 1 FROM notes WHERE repo = ?1 AND url = ?2")?;
423            // A miss stays one: the pull request's findings do not change once it closed.
424            let mut row = tx.prepare_cached(
425                "INSERT INTO notes (repo, pull, path, line, author, body, url, at, state, human, missed)
426                 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11)
427                 ON CONFLICT (repo, url) DO UPDATE SET
428                     path = excluded.path, line = excluded.line, body = excluded.body, state = excluded.state,
429                     human = excluded.human, missed = MAX(missed, excluded.missed)",
430            )?;
431            for note in notes {
432                if !known.exists(params![repo_id, note.url])? {
433                    added += 1;
434                }
435                let body: String = note.body.chars().take(NOTE_CHARS).collect();
436                row.execute(params![
437                    repo_id,
438                    note.pull,
439                    note.path,
440                    note.line,
441                    note.author,
442                    body,
443                    note.url,
444                    note.at,
445                    note.state,
446                    note.human,
447                    note.missed
448                ])?;
449            }
450        }
451        tx.commit()?;
452        Ok(added)
453    }
454
455    /// Credit to rules (notes, by id) from the fates of the findings that cited them: `true` accepted. Ids of
456    /// another repository are passed over. Returns how many rules were credited.
457    pub fn credit_rules(&mut self, repo: &str, credits: &[(i64, bool)]) -> Result<usize, Error> {
458        let tx = self.conn.transaction()?;
459        let mut credited = 0;
460        {
461            let mut row = tx.prepare_cached(
462                "UPDATE notes SET accepted = accepted + ?3, rejected = rejected + ?4
463                 WHERE id = ?1 AND repo = (SELECT id FROM repos WHERE name = ?2)",
464            )?;
465            for (id, accepted) in credits {
466                let (yes, no) = if *accepted { (1, 0) } else { (0, 1) };
467                credited += row.execute(params![id, repo, yes, no])?;
468            }
469        }
470        tx.commit()?;
471        Ok(credited)
472    }
473
474    /// Marks the comments of `logins` (the reviewing accounts) and of GitHub Apps (`...[bot]`) as not a person's:
475    /// the reviewer learns from people, never from its own text. Returns how many notes changed.
476    pub fn mark_not_human(&self, repo: &str, logins: &[String]) -> Result<usize, Error> {
477        let mut changed = self.conn.execute(
478            "UPDATE notes SET human = 0
479             WHERE human = 1 AND author LIKE '%[bot]' AND repo = (SELECT id FROM repos WHERE name = ?1)",
480            params![repo],
481        )?;
482        for login in logins {
483            changed += self.conn.execute(
484                "UPDATE notes SET human = 0
485                 WHERE human = 1 AND lower(author) = lower(?2) AND repo = (SELECT id FROM repos WHERE name = ?1)",
486                params![repo, login],
487            )?;
488        }
489        Ok(changed)
490    }
491
492    /// The rules of `repo` findings have cited (credit from at least one fate), most accepted first.
493    pub fn credited_rules(&self, repo: &str) -> Result<Vec<RuleCredit>, Error> {
494        let mut rows = self.conn.prepare_cached(
495            "SELECT n.id, n.path, n.body, n.url, n.accepted, n.rejected FROM notes n JOIN repos r ON r.id = n.repo
496             WHERE r.name = ?1 AND n.accepted + n.rejected > 0 ORDER BY n.accepted DESC, n.id",
497        )?;
498        let rules = rows
499            .query_map(params![repo], |row| {
500                Ok(RuleCredit {
501                    id: row.get(0)?,
502                    path: row.get(1)?,
503                    body: row.get(2)?,
504                    url: row.get(3)?,
505                    accepted: row.get(4)?,
506                    rejected: row.get(5)?,
507                })
508            })?
509            .collect::<Result<Vec<_>, _>>()?;
510        Ok(rules)
511    }
512
513    /// A rule's credit: (accepted, rejected, missed, human).
514    pub fn rule_credit(&self, id: i64) -> Result<Option<(i64, i64, bool, bool)>, Error> {
515        Ok(self
516            .conn
517            .query_row(
518                "SELECT accepted, rejected, missed, human FROM notes WHERE id = ?1",
519                params![id],
520                |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?, row.get(3)?)),
521            )
522            .optional()?)
523    }
524
525    /// The id of the note at `url` in `repo`.
526    pub fn note_id(&self, repo: &str, url: &str) -> Result<Option<i64>, Error> {
527        Ok(self
528            .conn
529            .query_row(
530                "SELECT n.id FROM notes n JOIN repos r ON r.id = n.repo WHERE r.name = ?1 AND n.url = ?2",
531                params![repo, url],
532                |row| row.get(0),
533            )
534            .optional()?)
535    }
536
537    /// A value the host keeps beside the graph (`meta`), such as when a repository's notes were backfilled.
538    pub fn meta(&self, key: &str) -> Result<Option<String>, Error> {
539        Ok(self
540            .conn
541            .query_row("SELECT value FROM meta WHERE key = ?1", params![key], |row| row.get(0))
542            .optional()?)
543    }
544
545    pub fn set_meta(&self, key: &str, value: &str) -> Result<(), Error> {
546        if key == "schema_version" {
547            return Err(Error("the schema version is the store's own".into()));
548        }
549        self.conn.execute(
550            "INSERT INTO meta (key, value) VALUES (?1, ?2) ON CONFLICT (key) DO UPDATE SET value = excluded.value",
551            params![key, value],
552        )?;
553        Ok(())
554    }
555
556    /// The tree of `repo` at commit `rev`, when it was recorded.
557    pub fn tree_id(&self, repo: &str, rev: &str) -> Result<Option<i64>, Error> {
558        Ok(self
559            .conn
560            .query_row(
561                "SELECT t.id FROM trees t JOIN repos r ON r.id = t.repo WHERE r.name = ?1 AND t.rev = ?2",
562                params![repo, rev],
563                |row| row.get(0),
564            )
565            .optional()?)
566    }
567
568    /// The blobs among `oids` the relation proposer of `version` has read.
569    pub fn scanned_blobs(&self, oids: &[&str], version: i64) -> Result<std::collections::HashSet<String>, Error> {
570        let mut known = std::collections::HashSet::new();
571        let mut statement = self
572            .conn
573            .prepare_cached("SELECT 1 FROM relation_scans WHERE blob = ?1 AND version = ?2")?;
574        for oid in oids {
575            if statement.exists(params![oid, version])? {
576                known.insert((*oid).to_string());
577            }
578        }
579        Ok(known)
580    }
581
582    /// Records what the proposer of `version` read from newly scanned blobs (their earlier proposals and verdicts
583    /// go: they came from another version) and which files of `tree` were read for relations, in one transaction.
584    /// A proposal plain code settled is `accepted` by `code`; the others wait for the judge. A blob another process
585    /// recorded at `version` since this one checked ([`Store::scanned_blobs`] reads outside this transaction) is
586    /// kept as it is: replacing it would drop verdicts settled on it and hand its relations' ids to new rows.
587    pub fn record_relations(
588        &mut self,
589        tree: i64,
590        entries: &[(String, String)],
591        scanned: &[(String, Vec<Proposed>)],
592        version: i64,
593    ) -> Result<(), Error> {
594        // Write-locked from the start: the check below and the writes after it see one state of the store.
595        let tx = self
596            .conn
597            .transaction_with_behavior(rusqlite::TransactionBehavior::Immediate)?;
598        {
599            let mut recorded = tx.prepare_cached("SELECT 1 FROM relation_scans WHERE blob = ?1 AND version = ?2")?;
600            let mut clear = tx.prepare_cached("DELETE FROM relations WHERE blob = ?1")?;
601            let mut clear_scans = tx.prepare_cached("DELETE FROM relation_scans WHERE blob = ?1")?;
602            let mut scan = tx.prepare_cached("INSERT INTO relation_scans (blob, version) VALUES (?1, ?2)")?;
603            let mut row = tx.prepare_cached(
604                "INSERT INTO relations (blob, kind, subject, object, line, quote, context, status, checked_by)
605                 VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9)",
606            )?;
607            for (oid, proposed) in scanned {
608                if recorded.exists(params![oid, version])? {
609                    continue;
610                }
611                clear.execute(params![oid])?;
612                clear_scans.execute(params![oid])?;
613                scan.execute(params![oid, version])?;
614                for relation in proposed {
615                    let (status, checked_by) = if relation.certain {
616                        ("accepted", Some("code"))
617                    } else {
618                        ("proposed", None)
619                    };
620                    row.execute(params![
621                        oid,
622                        relation.kind.name(),
623                        relation.subject,
624                        relation.object,
625                        relation.line,
626                        relation.quote,
627                        relation.context,
628                        status,
629                        checked_by
630                    ])?;
631                }
632            }
633            let mut entry = tx.prepare_cached(
634                "INSERT INTO relation_entries (tree, path, blob) VALUES (?1, ?2, ?3)
635                 ON CONFLICT (tree, path) DO UPDATE SET blob = excluded.blob",
636            )?;
637            for (path, oid) in entries {
638                entry.execute(params![tree, path, oid])?;
639            }
640        }
641        tx.commit()?;
642        Ok(())
643    }
644
645    /// The files of `tree` read for relations so far.
646    pub fn relation_paths(&self, tree: i64) -> Result<Vec<String>, Error> {
647        let mut statement = self
648            .conn
649            .prepare_cached("SELECT path FROM relation_entries WHERE tree = ?1 ORDER BY path")?;
650        let paths = statement
651            .query_map(params![tree], |row| row.get(0))?
652            .collect::<Result<_, _>>()?;
653        Ok(paths)
654    }
655
656    /// The relations of the files of `tree` read for relations, in path and line order; `paths` (when given)
657    /// narrows them to those files.
658    pub fn relations(&self, tree: i64, paths: Option<&[String]>) -> Result<Vec<Relation>, Error> {
659        let mut statement = self.conn.prepare_cached(
660            "SELECT r.id, r.kind, r.subject, r.object, e.path, r.blob, r.line, r.quote, r.context, r.status,
661                    r.checked_by, r.rung, r.model, r.confidence
662             FROM relation_entries e JOIN relations r ON r.blob = e.blob
663             WHERE e.tree = ?1 ORDER BY e.path, r.line, r.id",
664        )?;
665        let wanted: Option<std::collections::HashSet<&str>> =
666            paths.map(|paths| paths.iter().map(String::as_str).collect());
667        let rows = statement.query_map(params![tree], |row| {
668            Ok((
669                row.get::<_, i64>(0)?,
670                row.get::<_, String>(1)?,
671                row.get::<_, String>(2)?,
672                row.get::<_, String>(3)?,
673                row.get::<_, String>(4)?,
674                row.get::<_, String>(5)?,
675                row.get::<_, i64>(6)?,
676                row.get::<_, String>(7)?,
677                row.get::<_, String>(8)?,
678                row.get::<_, String>(9)?,
679                row.get::<_, Option<String>>(10)?,
680                row.get::<_, Option<String>>(11)?,
681                row.get::<_, Option<String>>(12)?,
682                row.get::<_, Option<f64>>(13)?,
683            ))
684        })?;
685        let mut out = Vec::new();
686        for row in rows {
687            let (
688                id,
689                kind,
690                subject,
691                object,
692                path,
693                blob,
694                line,
695                quote,
696                context,
697                status,
698                checked_by,
699                rung,
700                model,
701                confidence,
702            ) = row?;
703            if wanted.as_ref().is_some_and(|wanted| !wanted.contains(path.as_str())) {
704                continue;
705            }
706            // A kind this build does not know came from another version; it is read again before it is used.
707            let Some(kind) = Kind::parse(&kind) else { continue };
708            let subject = if subject.is_empty() {
709                path.clone()
710            } else {
711                format!("{path}::{subject}")
712            };
713            out.push(Relation {
714                id,
715                kind,
716                subject,
717                object,
718                path,
719                blob,
720                line,
721                quote,
722                context,
723                status,
724                checked_by,
725                rung,
726                model,
727                confidence,
728            });
729        }
730        Ok(out)
731    }
732
733    /// Records the judge's verdicts on proposed relations (or ones it sent for review before), in one
734    /// transaction; a relation plain code settled, or the judge already accepted or rejected, keeps its verdict.
735    /// Returns how many changed.
736    pub fn settle_relations(&mut self, verdicts: &[Settlement]) -> Result<usize, Error> {
737        let tx = self.conn.transaction()?;
738        let mut changed = 0;
739        {
740            let mut row = tx.prepare_cached(
741                "UPDATE relations SET status = ?2, checked_by = ?3, rung = ?4, model = ?5, confidence = ?6
742                 WHERE id = ?1 AND status IN ('proposed', 'review')",
743            )?;
744            for verdict in verdicts {
745                changed += row.execute(params![
746                    verdict.id,
747                    verdict.status.name(),
748                    verdict.backend,
749                    verdict.rung,
750                    verdict.model,
751                    verdict.confidence
752                ])?;
753            }
754        }
755        tx.commit()?;
756        Ok(changed)
757    }
758
759    /// How many trees the store holds for `repo`.
760    pub fn tree_count(&self, repo: &str) -> Result<i64, Error> {
761        Ok(self.conn.query_row(
762            "SELECT COUNT(*) FROM trees t JOIN repos r ON r.id = t.repo WHERE r.name = ?1",
763            params![repo],
764            |row| row.get(0),
765        )?)
766    }
767}