Skip to main content

uqa_storage_sqlite/
connection.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! Pooled `SQLite` connections with explicit session and transaction affinity.
8//!
9//! A [`ManagedConnection`] is a logical session. Clones share that session so
10//! catalog, document, inverted-index, and vector stores participate in the
11//! same explicit transaction. [`ManagedConnection::new_session`] creates an
12//! isolated session over the same physical connection pool. Outside explicit
13//! transactions operations check out independent connections, allowing WAL
14//! readers to make real concurrent progress.
15
16use std::path::{Path, PathBuf};
17use std::sync::{Arc, OnceLock};
18
19use parking_lot::{Mutex, RwLock};
20use rusqlite::{Connection, OpenFlags};
21use uqa_storage::{mvcc::VersionedKeyValueStore, KeyValueStore, StorageEncryptionKey};
22
23use crate::compressed_vfs::{self, SQLiteCompressedContainerAnchor, SQLiteCompressionOptions};
24
25mod identity;
26mod logical;
27mod native;
28mod native_restore;
29pub(crate) mod ownership;
30mod pool;
31mod restore;
32use pool::ConnectionPool;
33pub(crate) use pool::PooledConnection;
34mod notifications;
35mod serializable;
36mod snapshot;
37mod source;
38use snapshot::PhysicalConnection;
39pub(crate) use snapshot::{SnapshotIdentity, ValidatedRead};
40
41#[derive(Debug, thiserror::Error)]
42pub enum SQLiteError {
43    #[error(transparent)]
44    Memory(#[from] uqa_core::memory::MemoryError),
45    #[error(transparent)]
46    Cancelled(#[from] uqa_core::QueryCancelled),
47    #[error("text analysis failed: {0}")]
48    Analysis(#[from] uqa_analysis::AnalysisError),
49    #[error("sqlite error: {0}")]
50    SQLite(#[from] rusqlite::Error),
51    #[error("encryption key must not be empty")]
52    EmptyEncryptionKey,
53    #[error("database requires an encryption key")]
54    EncryptionKeyRequired,
55    #[error("database is not encrypted but an encryption key was provided")]
56    NotEncrypted,
57    #[error("auxiliary database requires DELETE journaling, found {0}")]
58    AuxiliaryJournalMode(String),
59    #[error("compressed sqlite container error: {0}")]
60    CompressedContainer(String),
61    #[error("io error: {0}")]
62    Io(#[from] std::io::Error),
63    #[error("catalog migration {version} failed: {source}")]
64    Migration {
65        version: u32,
66        #[source]
67        source: rusqlite::Error,
68    },
69    #[error("invalid persisted catalog schema version `{0}`")]
70    InvalidSchemaVersion(String),
71    #[error("catalog schema version {found} is newer than this engine supports ({supported})")]
72    UnsupportedSchemaVersion { found: u32, supported: u32 },
73    #[error("corrupt document blob for `{table}` doc {doc_id} field `{field}`: {reason}")]
74    CorruptDocumentBlob {
75        table: String,
76        doc_id: u64,
77        field: String,
78        reason: String,
79    },
80    #[error("payload serialization failed: {0}")]
81    Serde(#[from] serde_json::Error),
82    #[error("storage backend error: {0}")]
83    StorageBackend(String),
84    #[error(transparent)]
85    StorageSource(Box<uqa_storage::StorageBackendError>),
86    #[error("transaction already active for this sqlite session")]
87    TransactionAlreadyActive,
88    #[error("no active transaction for this sqlite session")]
89    NoActiveTransaction,
90    #[error("sqlite transaction was aborted by an earlier storage error: {0}")]
91    TransactionAborted(String),
92    #[error("sqlite session cleanup failed: {0}")]
93    SessionCleanupFailed(String),
94    #[error("sqlite connection-pool checkout lost its connection")]
95    MissingCheckedOutConnection,
96    #[error("versioned storage requires its logical session; use with_physical only for explicit physical maintenance")]
97    LogicalSessionRequired,
98    #[error("database restoration requires all existing owners to close")]
99    DatabaseRestoreBusy,
100    #[error("record initialization requires incompatible database owners to close")]
101    DatabaseCoordinationUpgradeBusy,
102    #[error("database restoration is incomplete; resume the original source/target request")]
103    DatabaseRestoreIncomplete,
104    #[error("the SQLite session already has a different retention limit")]
105    SessionOptionsMismatch,
106    #[error("the SQLite session is bound to a different record mapping")]
107    SessionMappingMismatch,
108    #[error("the retained SQLite database source changed; close its owners and reopen explicitly")]
109    DatabaseSourceChanged,
110}
111
112pub type Result<T> = std::result::Result<T, SQLiteError>;
113
114const MIN_POOL_CONNECTIONS: usize = 4;
115const MAX_POOL_CONNECTIONS: usize = 32;
116
117/// Prepared statements each connection retains. Statements are built from record layouts and work-queue conditions, so one commit runs more distinct statements than a small cache holds, and preparing a projection upsert compiles every trigger of its table; a cache that evicts them prepares them again in each transaction.
118pub(crate) const PREPARED_STATEMENT_CACHE_CAPACITY: usize = 256;
119
120#[derive(Clone)]
121enum ConnectionSpec {
122    File {
123        path: PathBuf,
124        key: Option<StorageEncryptionKey>,
125    },
126    Auxiliary {
127        path: PathBuf,
128        key: Option<StorageEncryptionKey>,
129    },
130    Compressed {
131        path: PathBuf,
132        compression: SQLiteCompressionOptions,
133        key: Option<StorageEncryptionKey>,
134    },
135    Memory,
136}
137
138impl ConnectionSpec {
139    fn open(&self, initialize_database: bool) -> Result<Connection> {
140        let conn = self.open_configured(initialize_database)?;
141        conn.set_prepared_statement_cache_capacity(PREPARED_STATEMENT_CACHE_CAPACITY);
142        Ok(conn)
143    }
144
145    fn open_configured(&self, initialize_database: bool) -> Result<Connection> {
146        let mut flags = OpenFlags::default();
147        if !initialize_database && matches!(self, Self::File { .. } | Self::Auxiliary { .. }) {
148            flags.remove(OpenFlags::SQLITE_OPEN_CREATE);
149        }
150        match self {
151            Self::File { path, key } | Self::Auxiliary { path, key } => {
152                let conn = Connection::open_with_flags(path, flags)?;
153                if let Some(key) = key {
154                    ManagedConnection::apply_encryption_key(&conn, key.expose_secret())?;
155                }
156                crate::btree_index::equality::register(&conn)?;
157                if matches!(self, Self::Auxiliary { .. }) {
158                    // Registry writers already serialize their transactions.
159                    // Retain the original journal mode instead of racing to
160                    // promote a new registry to WAL during concurrent opens.
161                    let mode: String =
162                        conn.pragma_query_value(None, "journal_mode", |row| row.get(0))?;
163                    if mode != "delete" {
164                        return Err(SQLiteError::AuxiliaryJournalMode(mode));
165                    }
166                    ManagedConnection::configure_rollback_connection(&conn)?;
167                } else {
168                    if initialize_database {
169                        ManagedConnection::enable_wal(&conn)?;
170                    }
171                    ManagedConnection::configure_wal_connection(&conn)?;
172                }
173                Ok(conn)
174            }
175            Self::Compressed {
176                path, compression, ..
177            } => {
178                let conn =
179                    Connection::open_with_flags_and_vfs(path, flags, compressed_vfs::VFS_NAME)?;
180                crate::btree_index::equality::register(&conn)?;
181                if initialize_database {
182                    conn.pragma_update(None, "page_size", compression.page_size)?;
183                    ManagedConnection::enable_compressed_journal(&conn)?;
184                }
185                ManagedConnection::configure_rollback_connection(&conn)?;
186                Ok(conn)
187            }
188            Self::Memory => {
189                let conn = Connection::open_in_memory()?;
190                crate::btree_index::equality::register(&conn)?;
191                if initialize_database {
192                    ManagedConnection::enable_wal(&conn)?;
193                }
194                ManagedConnection::configure_wal_connection(&conn)?;
195                Ok(conn)
196            }
197        }
198    }
199}
200
201struct SessionState {
202    write_cancellation: uqa_core::CancellationToken,
203    affinity: uqa_storage::StorageSessionAffinity,
204    /// Read guards cover ordinary operations. Transaction lifecycle calls take
205    /// the write guard, making BEGIN/COMMIT/ROLLBACK linearizable with respect
206    /// to every operation issued through the same logical session.
207    gate: RwLock<()>,
208    transaction: Mutex<Option<PooledConnection>>,
209    transaction_failure: Mutex<Option<String>>,
210    cleanup_failure: Mutex<Option<String>>,
211    logical: OnceLock<Arc<logical::BoundRecordSession>>,
212    native_restore: Mutex<Option<native_restore::NativeRestore>>,
213    snapshot_branch: Mutex<Arc<()>>,
214}
215
216impl SessionState {
217    fn new() -> Self {
218        Self::with_cancellation(uqa_core::CancellationToken::new())
219    }
220
221    fn with_cancellation(write_cancellation: uqa_core::CancellationToken) -> Self {
222        Self {
223            write_cancellation,
224            affinity: uqa_storage::StorageSessionAffinity::new(),
225            gate: RwLock::new(()),
226            transaction: Mutex::new(None),
227            transaction_failure: Mutex::new(None),
228            cleanup_failure: Mutex::new(None),
229            logical: OnceLock::new(),
230            native_restore: Mutex::new(None),
231            snapshot_branch: Mutex::new(Arc::new(())),
232        }
233    }
234}
235
236impl Drop for SessionState {
237    fn drop(&mut self) {
238        // `PooledConnection::drop` performs the rollback and discards the
239        // physical connection when rollback itself fails. Taking the pinned
240        // handle here therefore cannot return a broken connection to the pool.
241        self.native_restore.get_mut().take();
242        self.transaction.get_mut().take();
243    }
244}
245
246/// Logical `SQLite` session backed by a bounded physical connection pool.
247/// Cloning preserves session/transaction affinity; call [`Self::new_session`]
248/// for an independently isolated transaction context.
249#[derive(Clone)]
250pub struct ManagedConnection {
251    pool: Arc<ConnectionPool>,
252    session: Arc<SessionState>,
253    record_access: bool,
254}
255
256impl ManagedConnection {
257    /// Decoded document columns shared by every session of this database.
258    pub(crate) fn decoded_columns(
259        &self,
260    ) -> &crate::document_store::decoded_columns::DecodedColumns {
261        &self.pool.decoded_columns
262    }
263
264    fn surface_cleanup_failure(&self) -> Result<()> {
265        if let Some(error) = self.session.cleanup_failure.lock().take() {
266            return Err(SQLiteError::SessionCleanupFailed(error));
267        }
268        Ok(())
269    }
270
271    pub fn open(path: &Path) -> Result<Self> {
272        if path == Path::new(":memory:") {
273            return Self::open_in_memory();
274        }
275        Self::open_with_optional_key(path, None)
276    }
277
278    /// Retain the database credential for encrypted auxiliary storage, including
279    /// compressed containers whose main-file encryption lives in the VFS.
280    #[must_use]
281    pub fn auxiliary_encryption_key(&self) -> Option<StorageEncryptionKey> {
282        match &self.pool.spec {
283            ConnectionSpec::File { key, .. }
284            | ConnectionSpec::Auxiliary { key, .. }
285            | ConnectionSpec::Compressed { key, .. } => key.clone(),
286            ConnectionSpec::Memory => None,
287        }
288    }
289
290    /// Lease an independent physical connection without joining this logical
291    /// session's transaction. The pool retains encryption and connection policy.
292    pub fn lease_connection(&self) -> Result<crate::SQLiteConnectionLease> {
293        self.pool.checkout().map(crate::SQLiteConnectionLease)
294    }
295
296    /// Open database-owned auxiliary storage with its inherited credential and
297    /// original DELETE journaling. Concurrent first opens never change journal
298    /// modes. An incompatible existing mode is rejected without rewriting it.
299    pub fn open_auxiliary(path: &Path, key: Option<StorageEncryptionKey>) -> Result<Self> {
300        if path == Path::new(":memory:") || path.as_os_str().is_empty() {
301            return Err(SQLiteError::StorageBackend(
302                "auxiliary storage requires a database file".into(),
303            ));
304        }
305        Self::from_spec(
306            ConnectionSpec::Auxiliary {
307                path: path.to_path_buf(),
308                key,
309            },
310            default_pool_connections(),
311        )
312    }
313
314    pub fn open_encrypted(path: &Path, key: &str) -> Result<Self> {
315        Self::open_with_optional_key(path, Some(key))
316    }
317
318    pub fn open_compressed(path: &Path, compression: SQLiteCompressionOptions) -> Result<Self> {
319        Self::open_compressed_with_optional_key(path, compression, None, None)
320    }
321
322    pub fn open_compressed_encrypted(
323        path: &Path,
324        key: &str,
325        compression: SQLiteCompressionOptions,
326    ) -> Result<Self> {
327        if key.is_empty() {
328            return Err(SQLiteError::EmptyEncryptionKey);
329        }
330        Self::open_compressed_with_optional_key(path, compression, Some(key), None)
331    }
332
333    /// Open an encrypted compressed database while enforcing an exact trusted
334    /// external state anchor in the VFS main-file open path.
335    pub fn open_compressed_encrypted_with_anchor(
336        path: &Path,
337        key: &str,
338        compression: SQLiteCompressionOptions,
339        trusted_anchor: SQLiteCompressedContainerAnchor,
340    ) -> Result<Self> {
341        if key.is_empty() {
342            return Err(SQLiteError::EmptyEncryptionKey);
343        }
344        Self::open_compressed_with_optional_key(path, compression, Some(key), Some(trusted_anchor))
345    }
346
347    fn open_with_optional_key(path: &Path, key: Option<&str>) -> Result<Self> {
348        let spec = ConnectionSpec::File {
349            path: path.to_path_buf(),
350            key: key.map(StorageEncryptionKey::new),
351        };
352        Self::from_spec(spec, default_pool_connections())
353    }
354
355    fn open_compressed_with_optional_key(
356        path: &Path,
357        compression: SQLiteCompressionOptions,
358        key: Option<&str>,
359        trusted_anchor: Option<SQLiteCompressedContainerAnchor>,
360    ) -> Result<Self> {
361        let compression = compression
362            .validate()
363            .map_err(SQLiteError::CompressedContainer)?;
364        let owner = ownership::open(
365            path,
366            &uqa_storage::read_control::StorageReadControl::with_limit(
367                uqa_storage::mvcc::VersionedSessionOptions::default().retained_bytes,
368            ),
369        )?;
370        match trusted_anchor {
371            Some(anchor) => compressed_vfs::register_database_with_anchor(
372                path,
373                compression,
374                key.ok_or(SQLiteError::EncryptionKeyRequired)?,
375                anchor,
376            ),
377            None => compressed_vfs::register_database(path, compression, key),
378        }
379        .map_err(SQLiteError::CompressedContainer)?;
380        let spec = ConnectionSpec::Compressed {
381            path: path.to_path_buf(),
382            compression,
383            key: key.map(StorageEncryptionKey::new),
384        };
385        Self::from_spec_owned(spec, default_pool_connections(), Some(owner))
386    }
387
388    pub fn open_in_memory() -> Result<Self> {
389        // Independent `:memory:` connections do not share a database. Keep a
390        // one-connection pool for this special target; file-backed databases
391        // use the real multi-connection pool.
392        Self::from_spec(ConnectionSpec::Memory, 1)
393    }
394
395    fn from_spec(spec: ConnectionSpec, max_connections: usize) -> Result<Self> {
396        let owner = match &spec {
397            ConnectionSpec::File { path, .. }
398                if path.as_os_str().is_empty() || path == Path::new(":memory:") =>
399            {
400                None
401            }
402            ConnectionSpec::File { path, .. }
403            | ConnectionSpec::Auxiliary { path, .. }
404            | ConnectionSpec::Compressed { path, .. } => Some(ownership::open(
405                path,
406                &uqa_storage::read_control::StorageReadControl::with_limit(
407                    uqa_storage::mvcc::VersionedSessionOptions::default().retained_bytes,
408                ),
409            )?),
410            ConnectionSpec::Memory => None,
411        };
412        Self::from_spec_owned(spec, max_connections, owner)
413    }
414
415    fn from_spec_owned(
416        spec: ConnectionSpec,
417        max_connections: usize,
418        owner: Option<Arc<ownership::DatabaseOwner>>,
419    ) -> Result<Self> {
420        let initial = spec.open(true)?;
421        crate::mvcc::restore::reject_pending(&initial)?;
422        Ok(Self {
423            pool: ConnectionPool::new(spec, initial, max_connections, owner)?,
424            session: Arc::new(SessionState::new()),
425            record_access: false,
426        })
427    }
428
429    pub(crate) fn database_owner(&self) -> Option<Arc<ownership::DatabaseOwner>> {
430        self.pool.owner.clone()
431    }
432
433    fn apply_encryption_key(conn: &Connection, key: &str) -> Result<()> {
434        if key.is_empty() {
435            return Err(SQLiteError::EmptyEncryptionKey);
436        }
437        conn.pragma_update(None, "key", key)?;
438        Ok(())
439    }
440
441    fn enable_wal(conn: &Connection) -> Result<()> {
442        conn.pragma_update(None, "journal_mode", "WAL")?;
443        Ok(())
444    }
445
446    fn configure_wal_connection(conn: &Connection) -> Result<()> {
447        // Legacy physical operations retain a bounded busy wait. Versioned writes supply cancellable admission under a scoped timeout.
448        conn.busy_timeout(std::time::Duration::from_secs(5))?;
449        // Synchronous=NORMAL is the recommended pairing with WAL: safe
450        // against power loss, faster than FULL.
451        conn.pragma_update(None, "synchronous", "NORMAL")?;
452        // Foreign keys are off by default; turn on so per-table cleanup
453        // can use ON DELETE CASCADE later.
454        conn.pragma_update(None, "foreign_keys", "ON")?;
455        Ok(())
456    }
457
458    fn enable_compressed_journal(conn: &Connection) -> Result<()> {
459        // The compressed VFS implements the byte-addressed database file.
460        // Rollback journals stay raw because they are short-lived commit
461        // machinery; compressing them only adds autocommit write amplification.
462        // WAL requires shared-memory VFS methods, so compressed databases use
463        // SQLite's rollback journal and keep temp storage in memory.
464        conn.pragma_update(None, "journal_mode", "DELETE")?;
465        Ok(())
466    }
467
468    fn configure_rollback_connection(conn: &Connection) -> Result<()> {
469        conn.busy_timeout(std::time::Duration::from_secs(5))?;
470        conn.pragma_update(None, "synchronous", "FULL")?;
471        conn.pragma_update(None, "temp_store", "MEMORY")?;
472        conn.pragma_update(None, "foreign_keys", "ON")?;
473        Ok(())
474    }
475
476    /// Identity shared by handles using this session's native or logical transaction context.
477    pub fn transaction_affinity(&self) -> uqa_storage::StorageSessionAffinity {
478        let _gate = self.session.gate.read();
479        self.session.logical.get().map_or_else(
480            || self.session.affinity.clone(),
481            |logical| logical.session_affinity(),
482        )
483    }
484
485    /// Whether this session has an active native or logical record transaction.
486    #[must_use]
487    pub fn in_transaction(&self) -> bool {
488        let _gate = self.session.gate.read();
489        if let Some(logical) = self.session.logical.get() {
490            return logical.in_transaction();
491        }
492        self.session.transaction.lock().is_some()
493    }
494
495    /// Whether this session has staged writes, including private logical records. Callers use this to enforce read-only execution boundaries before COMMIT.
496    pub fn transaction_has_written(&self) -> Result<bool> {
497        let _gate = self.session.gate.read();
498        if let Some(logical) = self.session.logical.get() {
499            return logical.transaction_has_written().map_err(Into::into);
500        }
501        let transaction = self.session.transaction.lock();
502        let transaction = transaction
503            .as_ref()
504            .ok_or(SQLiteError::NoActiveTransaction)?;
505        Ok(matches!(
506            transaction.connection()?.transaction_state(Some("main"))?,
507            rusqlite::TransactionState::Write
508        ))
509    }
510
511    /// Whether this session's independent [`Self::data_version`] monitor can
512    /// read without contending with the currently pinned transaction.
513    ///
514    /// A rollback-journal writer's pending lock blocks new readers while waiting for existing readers to finish. A rollback-journal read transaction must therefore also avoid the independent monitor: its own shared lock may be preventing that waiting writer from proceeding. Callers refresh through the pinned connection instead. WAL sessions and sessions without a pinned transaction permit the independent monitor.
515    pub fn data_version_monitor_is_nonblocking(&self) -> Result<bool> {
516        let _gate = self.session.gate.read();
517        if self.session.logical.get().is_some() {
518            return Ok(true);
519        }
520        if !matches!(
521            &self.pool.spec,
522            ConnectionSpec::Compressed { .. } | ConnectionSpec::Auxiliary { .. }
523        ) {
524            return Ok(true);
525        }
526        Ok(self.session.transaction.lock().is_none())
527    }
528
529    /// Whether one pooled connection may retain a read snapshot while another writes. Ordinary plain and encrypted `SQLite` databases use WAL; compressed containers and auxiliary files use rollback journaling and therefore require a detached engine snapshot before writer promotion.
530    #[must_use]
531    pub fn supports_concurrent_pinned_read_and_write(&self) -> bool {
532        let _gate = self.session.gate.read();
533        self.session.logical.get().is_some()
534            || !matches!(
535                &self.pool.spec,
536                ConnectionSpec::Compressed { .. } | ConnectionSpec::Auxiliary { .. }
537            )
538    }
539
540    /// Database change counter observed on one stable connection shared by
541    /// every logical session over this pool. The value changes when another
542    /// `SQLite` connection commits. In-memory databases have no independent
543    /// connections and therefore return `None`.
544    pub fn data_version(&self) -> Result<Option<u64>> {
545        let _gate = self.session.gate.read();
546        self.pool.check_source()?;
547        if let Some(logical) = self.session.logical.get() {
548            return logical.change_version().map_err(Into::into);
549        }
550        if matches!(&self.pool.spec, ConnectionSpec::Memory) {
551            return Ok(None);
552        }
553        let mut monitor = self.pool.data_version_monitor.lock();
554        if monitor.is_none() {
555            *monitor = Some(self.pool.open_connection()?);
556        }
557        let monitor = monitor.as_ref().ok_or_else(|| {
558            SQLiteError::StorageBackend(
559                "data-version monitor was not initialized after opening it".into(),
560            )
561        })?;
562        let version: i64 = monitor.pragma_query_value(None, "data_version", |row| row.get(0))?;
563        let version = u64::try_from(version).map_err(|_| {
564            SQLiteError::StorageBackend(format!(
565                "SQLite returned a negative PRAGMA data_version: {version}"
566            ))
567        })?;
568        Ok(Some(version))
569    }
570
571    /// `SQLite`'s change counter on the pool's monitor connection, which differs from an earlier read exactly when another connection of any session or process committed in between. `None` when the monitor may not read at any time: an in-memory database has no independent connection, and a rollback-journal reader waits for a pending writer, which may itself be waiting for a read this session has open.
572    pub fn commit_monitor_version(&self) -> Result<Option<u64>> {
573        if matches!(
574            &self.pool.spec,
575            ConnectionSpec::Memory
576                | ConnectionSpec::Compressed { .. }
577                | ConnectionSpec::Auxiliary { .. }
578        ) {
579            return Ok(None);
580        }
581        self.pool.check_source()?;
582        let mut monitor = self.pool.data_version_monitor.lock();
583        if monitor.is_none() {
584            *monitor = Some(self.pool.open_connection()?);
585        }
586        let monitor = monitor.as_ref().ok_or_else(|| {
587            SQLiteError::StorageBackend(
588                "data-version monitor was not initialized after opening it".into(),
589            )
590        })?;
591        let version: i64 = monitor
592            .prepare_cached("PRAGMA data_version")?
593            .query_row([], |row| row.get(0))?;
594        u64::try_from(version).map(Some).map_err(|_| {
595            SQLiteError::StorageBackend(format!(
596                "SQLite returned a negative PRAGMA data_version: {version}"
597            ))
598        })
599    }
600
601    /// Complete logical record visibility, including the private root restored by savepoint undo. Legacy physical sessions retain their existing data-version refresh protocol.
602    pub fn read_view_revision(
603        &self,
604    ) -> Result<Option<uqa_storage::key_value::KeyValueReadRevision>> {
605        self.surface_cleanup_failure()?;
606        let _gate = self.session.gate.read();
607        self.session.logical.get().map_or(Ok(None), |logical| {
608            logical.read_view_revision().map_err(Into::into)
609        })
610    }
611
612    /// Establish the database snapshot for the active transaction without
613    /// depending on the caller's first user query. `BEGIN DEFERRED` alone does
614    /// not start a read transaction, so a writer could otherwise commit after
615    /// the engine checks its cache generations but before the first catalog
616    /// read. Reading `sqlite_schema` is database-wide and keeps the operation
617    /// independent of any application table.
618    pub fn pin_transaction_snapshot(&self) -> Result<()> {
619        self.surface_cleanup_failure()?;
620        let _gate = self.session.gate.read();
621        if let Some(logical) = self.session.logical.get() {
622            return if logical.in_transaction() {
623                Ok(())
624            } else {
625                Err(SQLiteError::NoActiveTransaction)
626            };
627        }
628        self.with_native(|connection| {
629            if connection.is_autocommit() {
630                return Err(SQLiteError::NoActiveTransaction);
631            }
632            let _: i64 =
633                connection.query_row("SELECT COUNT(*) FROM sqlite_schema", [], |row| row.get(0))?;
634            Ok(())
635        })
636    }
637
638    /// Run a closure using this session. Outside a transaction the closure
639    /// checks out a pooled connection; inside a transaction every clone is
640    /// routed to the session's pinned connection.
641    pub fn with<R>(&self, f: impl FnOnce(&Connection) -> Result<R>) -> Result<R> {
642        self.surface_cleanup_failure()?;
643        let _gate = self.session.gate.read();
644        if self.session.logical.get().is_some() {
645            return Err(SQLiteError::LogicalSessionRequired);
646        }
647        self.with_native(f)
648    }
649
650    /// The caller holds the session gate and has ruled out logical record access.
651    fn with_native<R>(&self, f: impl FnOnce(&Connection) -> Result<R>) -> Result<R> {
652        let transaction = self.session.transaction.lock();
653        if let Some(connection) = transaction.as_ref() {
654            if let Some(error) = self.session.transaction_failure.lock().as_ref() {
655                return Err(SQLiteError::TransactionAborted(error.clone()));
656            }
657            self.check_native_access(connection.connection()?)?;
658            let result = f(connection.connection()?);
659            if let Err(error) = &result {
660                let mut failure = self.session.transaction_failure.lock();
661                if failure.is_none() {
662                    *failure = Some(error.to_string());
663                }
664            }
665            return result;
666        }
667        drop(transaction);
668        let connection = self.pool.checkout()?;
669        self.check_native_access(connection.connection()?)?;
670        f(connection.connection()?)
671    }
672
673    pub fn with_mut<R>(&self, f: impl FnOnce(&mut Connection) -> Result<R>) -> Result<R> {
674        self.with_physical_mut(|physical| f(&mut physical.connection))
675    }
676
677    fn with_physical_mut<R>(
678        &self,
679        f: impl FnOnce(&mut PhysicalConnection) -> Result<R>,
680    ) -> Result<R> {
681        self.surface_cleanup_failure()?;
682        let _gate = self.session.gate.read();
683        if self.session.logical.get().is_some() {
684            return Err(SQLiteError::LogicalSessionRequired);
685        }
686        let mut transaction = self.session.transaction.lock();
687        if let Some(connection) = transaction.as_mut() {
688            if let Some(error) = self.session.transaction_failure.lock().as_ref() {
689                return Err(SQLiteError::TransactionAborted(error.clone()));
690            }
691            self.check_native_access(connection.connection()?)?;
692            let result = f(connection.physical_mut()?);
693            if let Err(error) = &result {
694                let mut failure = self.session.transaction_failure.lock();
695                if failure.is_none() {
696                    *failure = Some(error.to_string());
697                }
698            }
699            return result;
700        }
701        drop(transaction);
702        let mut connection = self.pool.checkout()?;
703        self.check_native_access(connection.connection()?)?;
704        f(connection.physical_mut()?)
705    }
706
707    /// Rewrite the `SQLite` database into its minimum-sized file. `SQLite` requires `VACUUM` to run in autocommit mode, so the session write gate makes the transaction check and maintenance command one atomic session operation.
708    pub fn vacuum(&self) -> Result<()> {
709        self.maintain_storage(true)
710    }
711
712    /// Reclaim obsolete logical records and history without executing a physical `SQLite` `VACUUM`.
713    pub fn reclaim_obsolete(&self) -> Result<()> {
714        self.maintain_storage(false)
715    }
716
717    fn maintain_storage(&self, compact: bool) -> Result<()> {
718        self.surface_cleanup_failure()?;
719        let _gate = self.session.gate.write();
720        if self.session.transaction.lock().is_some()
721            || self
722                .session
723                .logical
724                .get()
725                .is_some_and(|logical| logical.in_transaction())
726        {
727            return Err(SQLiteError::TransactionAlreadyActive);
728        }
729        if let Some(logical) = self.session.logical.get() {
730            let source: Arc<dyn KeyValueStore> = if let Some(namespace) = logical.native {
731                Arc::new(crate::diskann::Records::new(
732                    logical.store.clone(),
733                    namespace,
734                    self.auxiliary_encryption_key(),
735                )?)
736            } else {
737                logical.store.clone()
738            };
739            let retained = logical.retention_control();
740            let control = uqa_storage::read_control::StorageReadControl::new(
741                retained.memory(),
742                &self.write_cancellation(),
743            );
744            uqa_storage::key_value::KeyValueDiskANNMaintenance::run(&source, &control)?;
745            logical.reclaim_versions()?;
746        }
747        if compact {
748            let connection = self.pool.checkout()?;
749            connection.connection()?.execute_batch("VACUUM")?;
750        }
751        Ok(())
752    }
753
754    /// Open an explicit (non-deferred) transaction. Subsequent
755    /// auto-commit hosts (catalog writes, FTS index updates, ...) all
756    /// flow through the same connection so the transaction enclosing
757    /// them is honoured. Use [`Self::commit_transaction`] /
758    /// [`Self::rollback_transaction`] / [`Self::savepoint`] etc. for
759    /// the lifecycle.
760    pub fn begin_transaction(&self) -> Result<()> {
761        self.begin_transaction_with("BEGIN IMMEDIATE")
762    }
763
764    /// Open a deferred transaction. Read-only SQL statements use this mode
765    /// so WAL readers do not take the single writer reservation; if a scalar
766    /// routine performs a write, `SQLite` upgrades the same transaction and
767    /// still preserves the statement's atomic boundary.
768    pub fn begin_deferred_transaction(&self) -> Result<()> {
769        self.begin_transaction_with("BEGIN DEFERRED")
770    }
771
772    fn begin_transaction_with(&self, statement: &str) -> Result<()> {
773        self.surface_cleanup_failure()?;
774        let _gate = self.session.gate.write();
775        if let Some(logical) = self.session.logical.get() {
776            if logical.in_transaction() {
777                return Err(SQLiteError::TransactionAlreadyActive);
778            }
779            return if statement == "BEGIN DEFERRED" {
780                logical.begin_upgradeable_transaction()
781            } else {
782                logical.begin_transaction()
783            }
784            .map_err(Into::into);
785        }
786        let mut transaction = self.session.transaction.lock();
787        if transaction.is_some() {
788            return Err(SQLiteError::TransactionAlreadyActive);
789        }
790        let connection = self.pool.checkout()?;
791        self.check_native_access(connection.connection()?)?;
792        connection.connection()?.execute_batch(statement)?;
793        self.session.transaction_failure.lock().take();
794        *transaction = Some(connection);
795        Ok(())
796    }
797
798    pub fn commit_transaction(&self) -> Result<()> {
799        self.surface_cleanup_failure()?;
800        self.finish_transaction("COMMIT")
801    }
802
803    pub fn rollback_transaction(&self) -> Result<()> {
804        self.surface_cleanup_failure()?;
805        self.finish_transaction("ROLLBACK")
806    }
807
808    /// Drop-only transaction cleanup. A rollback error is recorded on the
809    /// logical session and is returned by its next operation instead of being
810    /// mistaken for a successful rollback.
811    pub(crate) fn rollback_transaction_on_drop(&self) {
812        if let Err(error) = self.finish_transaction("ROLLBACK") {
813            let mut failure = self.session.cleanup_failure.lock();
814            if failure.is_none() {
815                *failure = Some(error.to_string());
816            }
817        }
818    }
819
820    fn finish_transaction(&self, statement: &str) -> Result<()> {
821        let _gate = self.session.gate.write();
822        if let Some(logical) = self.session.logical.get() {
823            if !logical.in_transaction() {
824                return Err(SQLiteError::NoActiveTransaction);
825            }
826            return if statement == "COMMIT" {
827                logical.commit_transaction()
828            } else {
829                logical.rollback_transaction()
830            }
831            .map_err(Into::into);
832        }
833        let mut transaction = self.session.transaction.lock();
834        let connection = transaction
835            .as_ref()
836            .ok_or(SQLiteError::NoActiveTransaction)?;
837        *self.session.snapshot_branch.lock() = Arc::new(());
838        if statement == "COMMIT" {
839            // Materialize the failure before entering the branch. Holding the
840            // mutex guard created by an `if let` scrutinee until the end of
841            // the branch would deadlock when cleanup takes the same lock.
842            let transaction_failure = self.session.transaction_failure.lock().clone();
843            if let Some(error) = transaction_failure {
844                if let Err(rollback_error) = connection.connection()?.execute_batch("ROLLBACK") {
845                    self.session.native_restore.lock().take();
846                    transaction.take();
847                    self.session.transaction_failure.lock().take();
848                    return Err(SQLiteError::SQLite(rollback_error));
849                }
850                self.session.native_restore.lock().take();
851                transaction.take();
852                self.session.transaction_failure.lock().take();
853                return Err(SQLiteError::TransactionAborted(error));
854            }
855        }
856        let native = if statement == "COMMIT" && self.session.native_restore.lock().is_some() {
857            match self.prepare_initial_native_binding(connection.connection()?) {
858                Ok(native) => Some(native),
859                Err(error) => {
860                    *self.session.transaction_failure.lock() = Some(error.to_string());
861                    return Err(error);
862                }
863            }
864        } else {
865            None
866        };
867        if let Err(error) = connection.connection()?.execute_batch(statement) {
868            self.session.native_restore.lock().take();
869            transaction.take();
870            self.session.transaction_failure.lock().take();
871            return Err(SQLiteError::SQLite(error));
872        }
873        self.session.native_restore.lock().take();
874        transaction.take();
875        self.session.transaction_failure.lock().take();
876        if let Some(native) = native {
877            self.install_initial_native_binding(native);
878        }
879        Ok(())
880    }
881
882    fn with_transaction<R>(
883        &self,
884        logical: impl FnOnce(&VersionedKeyValueStore) -> uqa_storage::StorageBackendResult<R>,
885        native: impl FnOnce(&Connection) -> Result<R>,
886    ) -> Result<R> {
887        self.surface_cleanup_failure()?;
888        let _gate = self.session.gate.read();
889        if let Some(store) = self.session.logical.get() {
890            if !store.in_transaction() {
891                return Err(SQLiteError::NoActiveTransaction);
892            }
893            return logical(store).map_err(Into::into);
894        }
895        let transaction = self.session.transaction.lock();
896        let connection = transaction
897            .as_ref()
898            .ok_or(SQLiteError::NoActiveTransaction)?;
899        self.check_native_access(connection.connection()?)?;
900        native(connection.connection()?)
901    }
902
903    pub fn savepoint(&self, name: &str) -> Result<()> {
904        self.with_transaction(
905            |logical| logical.savepoint(name),
906            |connection| {
907                let stmt = format!("SAVEPOINT \"{}\"", name.replace('"', "\"\""));
908                connection.execute_batch(&stmt)?;
909                Ok(())
910            },
911        )
912    }
913
914    pub fn release_savepoint(&self, name: &str) -> Result<()> {
915        self.with_transaction(
916            |logical| logical.release_savepoint(name),
917            |connection| {
918                let stmt = format!("RELEASE SAVEPOINT \"{}\"", name.replace('"', "\"\""));
919                connection.execute_batch(&stmt)?;
920                Ok(())
921            },
922        )
923    }
924
925    pub fn rollback_to_savepoint(&self, name: &str) -> Result<()> {
926        self.with_transaction(
927            |logical| logical.rollback_to_savepoint(name),
928            |connection| {
929                let stmt = format!("ROLLBACK TO SAVEPOINT \"{}\"", name.replace('"', "\"\""));
930                connection.execute_batch(&stmt)?;
931                self.session.transaction_failure.lock().take();
932                *self.session.snapshot_branch.lock() = Arc::new(());
933                Ok(())
934            },
935        )
936    }
937}
938
939fn default_pool_connections() -> usize {
940    std::thread::available_parallelism()
941        .map_or(MIN_POOL_CONNECTIONS, |parallelism| parallelism.get() * 2)
942        .clamp(MIN_POOL_CONNECTIONS, MAX_POOL_CONNECTIONS)
943}
944
945#[cfg(test)]
946mod tests;
947
948impl From<uqa_storage::mvcc::VersionError> for SQLiteError {
949    fn from(error: uqa_storage::mvcc::VersionError) -> Self {
950        Self::from(error.into_storage_error())
951    }
952}
953
954impl From<SQLiteError> for uqa_storage::StorageBackendError {
955    fn from(source: SQLiteError) -> Self {
956        match source {
957            SQLiteError::Memory(error) => Self::Memory(error),
958            SQLiteError::Cancelled(error) => Self::Cancelled(error),
959            SQLiteError::StorageSource(error) => *error,
960            source => Self::backend("SQLite", source),
961        }
962    }
963}
964
965impl From<SQLiteError> for uqa_storage::TransactionError {
966    fn from(source: SQLiteError) -> Self {
967        Self::Storage(source.into())
968    }
969}
970
971impl From<uqa_storage::StorageBackendError> for SQLiteError {
972    fn from(error: uqa_storage::StorageBackendError) -> Self {
973        use uqa_storage::StorageBackendError;
974        match error {
975            StorageBackendError::Memory(error) => Self::Memory(error),
976            StorageBackendError::Cancelled(error) => Self::Cancelled(error),
977            StorageBackendError::Analysis(error) => Self::Analysis(error),
978            StorageBackendError::Serde(error) => Self::Serde(error),
979            StorageBackendError::Backend { backend, source } => match source.downcast::<Self>() {
980                Ok(error) => *error,
981                Err(source) => {
982                    Self::StorageSource(Box::new(StorageBackendError::Backend { backend, source }))
983                }
984            },
985            StorageBackendError::Other(message) => Self::StorageBackend(message),
986        }
987    }
988}