uqa-storage-sqlite 0.4.9

SQLite catalog, indexes, compressed storage, graph and key/value providers
Documentation
//
// Unified Query Algebra
//
// Copyright (c) 2023-2026 Cognica, Inc.
//

//! Persistent brute-force vector index and exact search contract.

use parking_lot::RwLock;
use uqa_core::memory::BudgetedVec;
use uqa_storage::read_control::StorageReadControl;

mod cache;

use super::native::NativeVectorRead;
use super::{
    blob_to_vector, cosine_similarity, decode_doc_id, encode_doc_id, i64_to_usize, params,
    select_top_k_scored, usize_to_u64, validate_persisted_ordinal_sequence,
    validate_vector_ordinal_count, validate_vector_values, vector_to_blob, Arc, DocId,
    EncodedDocVectors, ManagedConnection, Payload, PostingEntry, PostingList, SQLiteError,
    SQLiteResult, StorageBackendResult, VectorIndex,
};

#[derive(Clone)]
pub struct SQLiteVectorIndex {
    pub(super) conn: ManagedConnection,
    pub(super) table: String,
    pub(super) field: String,
    pub(super) dimensions: u32,
    pub(super) retained: Option<Arc<crate::mvcc::native::NativeSnapshot>>,
    cached_vectors: Arc<RwLock<Option<cache::CachedVectors>>>,
}

impl SQLiteVectorIndex {
    pub fn new(
        conn: ManagedConnection,
        table: impl Into<String>,
        field: impl Into<String>,
        dimensions: u32,
    ) -> Self {
        Self {
            conn,
            table: table.into(),
            field: field.into(),
            dimensions,
            retained: None,
            cached_vectors: Arc::default(),
        }
    }

    fn score_vectors(
        &self,
        query: &[f32],
        threshold: Option<f32>,
    ) -> SQLiteResult<Option<BudgetedVec<(DocId, f32)>>> {
        if let Some(snapshot) = self.read_releasing_cache(|| self.native_snapshot())? {
            let native = self.read_releasing_cache(|| NativeVectorRead::new(&snapshot, self))?;
            self.score_cached_vectors(&native, query, threshold)
        } else {
            let control = StorageReadControl::with_limit(usize::MAX);
            Self::score_stream(query, threshold, |visit| {
                for (id, _, vector) in self.load_all_with_ordinals()? {
                    visit(id, &vector, &control)?;
                }
                Ok(())
            })
        }
    }

    fn score_stream(
        query: &[f32],
        threshold: Option<f32>,
        read: impl FnOnce(
            &mut dyn FnMut(DocId, &[f32], &StorageReadControl) -> SQLiteResult<()>,
        ) -> SQLiteResult<()>,
    ) -> SQLiteResult<Option<BudgetedVec<(DocId, f32)>>> {
        let mut scores: Option<BudgetedVec<(DocId, f32)>> = None;
        read(&mut |doc_id, vector, control| {
            let score = cosine_similarity(query, vector);
            if threshold.is_none_or(|threshold| score >= threshold) {
                let scores = scores.get_or_insert_with(|| BudgetedVec::new(control.memory()));
                // Canonical and legacy cursors both order vectors by document and tensor ordinal.
                if let Some((last_id, best)) = scores.last_mut().filter(|(id, _)| *id == doc_id) {
                    debug_assert_eq!(*last_id, doc_id);
                    if score > *best {
                        *best = score;
                    }
                } else {
                    scores.push((doc_id, score))?;
                }
            }
            Ok(())
        })?;
        Ok(scores)
    }

    fn score_postings(
        &self,
        scores: Vec<(DocId, f32)>,
        memory: &uqa_core::memory::MemoryBudget,
    ) -> SQLiteResult<PostingList> {
        let mut entries = BudgetedVec::new(memory);
        self.read_releasing_cache(|| Ok(entries.reserve(scores.len())?))?;
        for (doc_id, score) in scores {
            entries.push(PostingEntry::new(
                doc_id,
                Payload::with_score(f64::from(score)),
            ))?;
        }
        let (entries, _entry_memory) = entries.into_parts();
        Ok(PostingList::from_sorted_unchecked(entries))
    }

    pub(super) fn load_all_with_ordinals(&self) -> SQLiteResult<Vec<(DocId, u32, Vec<f32>)>> {
        self.conn
            .with(|connection| self.load_all_with_ordinals_from(connection))
    }

    pub(super) fn visit_ordered_vectors_from(
        &self,
        connection: &rusqlite::Connection,
        control: &StorageReadControl,
        mut visit: impl FnMut(DocId, u32, &[f32]) -> SQLiteResult<()>,
    ) -> SQLiteResult<()> {
        control.check()?;
        let mut statement = connection.prepare(
            "SELECT doc_id, vector_ordinal, vector FROM _vectors
             WHERE table_name = ?1 AND field = ?2 ORDER BY doc_id, vector_ordinal",
        )?;
        let mut rows = statement.query(params![self.table, self.field])?;
        let mut ordinals = super::codec::PersistedOrdinalSequence::default();
        while let Some(row) = rows.next()? {
            control.check()?;
            let document = decode_doc_id(row.get(0)?)?;
            let ordinal: i64 = row.get(1)?;
            let ordinal = u32::try_from(ordinal).map_err(|_| {
                SQLiteError::StorageBackend(format!(
                    "invalid vector ordinal {ordinal} for {}.{}",
                    self.table, self.field
                ))
            })?;
            let bytes = row
                .get_ref(2)?
                .as_blob()
                .map_err(|_| SQLiteError::StorageBackend("invalid vector payload".into()))?;
            let mut payload = control.memory().reserve(bytes.len())?;
            let vector = blob_to_vector(bytes)?;
            let capacity = vector
                .capacity()
                .checked_mul(size_of::<f32>())
                .ok_or(uqa_core::memory::MemoryError::SizeOverflow)?;
            payload.grow(capacity.saturating_sub(bytes.len()))?;
            self.validate_dimensions_sqlite(&vector)?;
            ordinals.push(document, ordinal)?;
            visit(document, ordinal, &vector)?;
        }
        Ok(())
    }

    pub(super) fn load_all_with_ordinals_from(
        &self,
        connection: &rusqlite::Connection,
    ) -> SQLiteResult<Vec<(DocId, u32, Vec<f32>)>> {
        let mut stmt = connection.prepare(
            "SELECT doc_id, vector_ordinal, vector FROM _vectors
                 WHERE table_name = ?1 AND field = ?2
                 ORDER BY doc_id, vector_ordinal",
        )?;
        let rows = stmt.query_map(params![self.table, self.field], |r| {
            Ok((
                r.get::<_, i64>(0)?,
                r.get::<_, i64>(1)?,
                r.get::<_, Vec<u8>>(2)?,
            ))
        })?;
        let mut out = Vec::new();
        for row in rows {
            let (doc_id, ordinal, blob) = row?;
            let ordinal = u32::try_from(ordinal).map_err(|_| {
                SQLiteError::StorageBackend(format!(
                    "invalid vector ordinal {ordinal} for {}.{}",
                    self.table, self.field
                ))
            })?;
            let vector = blob_to_vector(&blob)?;
            self.validate_dimensions_sqlite(&vector)?;
            out.push((decode_doc_id(doc_id)?, ordinal, vector));
        }
        validate_persisted_ordinal_sequence(&out)?;
        Ok(out)
    }

    pub(super) fn validate_dimensions_sqlite(&self, vector: &[f32]) -> SQLiteResult<()> {
        validate_vector_values(self.dimensions, vector).map_err(|error| {
            SQLiteError::StorageBackend(format!(
                "invalid vector for {}.{}: {error}",
                self.table, self.field
            ))
        })
    }

    pub(super) fn validate_dimensions(&self, vector: &[f32]) -> StorageBackendResult<()> {
        Ok(self.validate_dimensions_sqlite(vector)?)
    }

    pub(super) fn stage_doc_vectors(
        &self,
        doc_id: DocId,
        vectors: &[Vec<f32>],
    ) -> SQLiteResult<EncodedDocVectors> {
        for vector in vectors {
            self.validate_dimensions_sqlite(vector)?;
        }
        validate_vector_ordinal_count(usize_to_u64("vector count", vectors.len())?)?;
        let doc_id = encode_doc_id(doc_id)?;
        let encoded = vectors
            .iter()
            .enumerate()
            .map(|(ordinal, vector)| {
                let ordinal = u32::try_from(ordinal).map_err(|_| {
                    SQLiteError::StorageBackend(
                        "vector ordinal exceeds the u32 index format".into(),
                    )
                })?;
                Ok((i64::from(ordinal), vector_to_blob(vector)?))
            })
            .collect::<SQLiteResult<Vec<_>>>()?;
        Ok((doc_id, encoded))
    }
}

impl VectorIndex for SQLiteVectorIndex {
    fn contains_document(&self, doc_id: DocId) -> StorageBackendResult<bool> {
        let doc_id = encode_doc_id(doc_id)?;
        if let Some(found) = self.read_native(|read| read.contains_document(doc_id))? {
            return Ok(found);
        }
        Ok(self.conn.with(|connection| {
            Ok(connection.query_row(
                "SELECT EXISTS(SELECT 1 FROM _vectors WHERE table_name = ?1 AND field = ?2 AND doc_id = ?3 AND vector_ordinal = 0)",
                params![self.table, self.field, doc_id],
                |row| row.get(0),
            )?)
        })?)
    }

    fn dimensions(&self) -> u32 {
        self.dimensions
    }

    fn index_kind(&self) -> &'static str {
        "sqlite-bruteforce"
    }

    fn add(&mut self, doc_id: DocId, vector: Vec<f32>) -> StorageBackendResult<()> {
        self.add_many(doc_id, vec![vector])
    }

    fn add_many(&mut self, doc_id: DocId, vectors: Vec<Vec<f32>>) -> StorageBackendResult<()> {
        let (doc_id, encoded_vectors) = self.stage_doc_vectors(doc_id, &vectors)?;
        if self
            .write_native(|read, batch| read.replace(batch, doc_id, &encoded_vectors))?
            .is_some()
        {
            return Ok(());
        }
        self.conn.with_mut(|conn| {
            let tx = conn.savepoint()?;
            tx.execute(
                "DELETE FROM _vectors
                 WHERE table_name = ?1 AND field = ?2 AND doc_id = ?3",
                params![self.table, self.field, doc_id],
            )?;
            let mut stmt = tx.prepare(
                "INSERT INTO _vectors (table_name, field, doc_id, vector_ordinal, vector)
                 VALUES (?1, ?2, ?3, ?4, ?5)",
            )?;
            for (ordinal, vector) in &encoded_vectors {
                stmt.execute(params![self.table, self.field, doc_id, ordinal, vector,])?;
            }
            drop(stmt);
            tx.commit()?;
            Ok(())
        })?;
        Ok(())
    }

    fn delete(&mut self, doc_id: DocId) -> StorageBackendResult<()> {
        let doc_id = encode_doc_id(doc_id)?;
        if self
            .write_native(|read, batch| read.delete(batch, doc_id))?
            .is_some()
        {
            return Ok(());
        }
        self.conn.with(|c| {
            c.execute(
                "DELETE FROM _vectors
                 WHERE table_name = ?1 AND field = ?2 AND doc_id = ?3",
                params![self.table, self.field, doc_id],
            )?;
            Ok(())
        })?;
        Ok(())
    }

    fn clear(&mut self) -> StorageBackendResult<()> {
        if self
            .write_native(|read, batch| {
                read.clear_family(batch, crate::mvcc::native::NativeRecordFamily::Vectors)
            })?
            .is_some()
        {
            return Ok(());
        }
        self.conn.with(|c| {
            c.execute(
                "DELETE FROM _vectors WHERE table_name = ?1 AND field = ?2",
                params![self.table, self.field],
            )?;
            Ok(())
        })?;
        Ok(())
    }

    fn search_knn(&self, query: &[f32], k: usize) -> StorageBackendResult<PostingList> {
        self.validate_dimensions(query)?;
        if k == 0 {
            return Ok(PostingList::new());
        }
        let Some(scores) = self.score_vectors(query, None)? else {
            return Ok(PostingList::new());
        };
        let (mut scores, score_memory) = scores.into_parts();
        select_top_k_scored(&mut scores, k);
        scores.sort_by_key(|(id, _)| *id);
        Ok(self.score_postings(scores, score_memory.budget())?)
    }

    fn search_threshold(&self, query: &[f32], threshold: f32) -> StorageBackendResult<PostingList> {
        self.validate_dimensions(query)?;
        if !threshold.is_finite() {
            return Err(uqa_storage::StorageBackendError::Other(format!(
                "vector similarity threshold must be finite, got {threshold}"
            )));
        }
        let Some(scores) = self.score_vectors(query, Some(threshold))? else {
            return Ok(PostingList::new());
        };
        let (mut scores, score_memory) = scores.into_parts();
        scores.sort_by_key(|(id, _)| *id);
        Ok(self.score_postings(scores, score_memory.budget())?)
    }

    fn count(&self) -> StorageBackendResult<usize> {
        if let Some(snapshot) = self.native_snapshot()? {
            return Ok(NativeVectorRead::new(&snapshot, self)?.count()?);
        }
        Ok(self.conn.with(|c| {
            let n: i64 = c.query_row(
                "SELECT COUNT(*) FROM _vectors WHERE table_name = ?1 AND field = ?2",
                params![self.table, self.field],
                |r| r.get(0),
            )?;
            i64_to_usize("vector count", n)
        })?)
    }

    fn snapshot(&self) -> StorageBackendResult<Arc<dyn VectorIndex>> {
        Ok(Arc::new(self.retained_snapshot()?))
    }
    fn vector_read_snapshot(
        &self,
        control: &uqa_storage::read_control::StorageReadControl,
    ) -> StorageBackendResult<Option<uqa_storage::vector_index::VectorReadSnapshot>> {
        super::native::canonical::capture(self, control)
    }
}

#[cfg(test)]
mod tests;