modio_logger_db/
db.rs

1// Author: D.S. Ljungmark <spider@skuggor.se>, Modio AB
2// SPDX-License-Identifier: AGPL-3.0-or-later
3use crate::error::Error;
4use sqlx::migrate::Migrator;
5pub use sqlx::sqlite::{SqliteConnectOptions, SqlitePool};
6use sqlx::sqlite::{SqlitePoolOptions, SqliteSynchronous};
7use sqlx::ConnectOptions;
8use std::time::Duration;
9use tracing::{error, info, warn};
10
11// Embed our database migrations
12static MIGRATOR: Migrator = sqlx::migrate!();
13
14async fn get_file_pool(
15    filename: &std::path::Path,
16    db_connections: u32,
17    timeout: Duration,
18) -> Result<SqlitePool, Error> {
19    // Maybe we need to use JournalMode WAL to avoid locked tables,
20    // but we would prefer not to on the firmware.
21    //    use sqlx::sqlite::SqliteJournalMode;
22    //    and conn.journal_mode(SqliteJournalMode::WAL)
23
24    // Default slow query log is 1s, if our acquire_timeout is 2s and we do 2 slow queries, we
25    // won't get a warning about what is slow.
26    // This sets it to 0.3s
27    let slow_log = Duration::from_secs_f32(0.3);
28
29    info!(
30        "Opening SQLite database file: {}, connections={}, timeout={}",
31        filename.display(),
32        db_connections,
33        timeout.as_secs_f32()
34    );
35    let conn = SqliteConnectOptions::new()
36        .filename(filename)
37        .synchronous(SqliteSynchronous::Normal)
38        .shared_cache(false)
39        .pragma("temp_store", "memory")
40        .log_statements(log::LevelFilter::Trace)
41        .log_slow_statements(log::LevelFilter::Warn, slow_log);
42
43    let pool = SqlitePoolOptions::new()
44        .min_connections(db_connections)
45        .max_connections(db_connections)
46        .test_before_acquire(false)
47        .acquire_timeout(timeout)
48        .connect_with(conn)
49        .await?;
50    Ok(pool)
51}
52
53pub async fn run_migrations(pool: &SqlitePool) -> Result<(), Error> {
54    MIGRATOR.run(pool).await?;
55    Ok(())
56}
57
58/// Helper around building a pool
59pub struct SqlitePoolBuilder<'tempfile> {
60    path: Option<&'tempfile std::path::Path>,
61    migrate: bool,
62    db_connections: u32,
63    timeout: Duration,
64}
65
66impl<'tempfile> SqlitePoolBuilder<'tempfile> {
67    #[must_use]
68    pub const fn new() -> Self {
69        Self {
70            path: None,
71            migrate: true,
72            db_connections: 4,
73            // The since DBus has a timeout of ~25s by default, we should have a timeout that's at least
74            // _lower_ than that so we can return errors before things go totally wrong.
75            timeout: Duration::from_secs(5),
76        }
77    }
78
79    /// Set the `db_path`
80    /// It is expected that it is a reference to a path that should exist on disk,
81    /// and be visible by it's filename.
82    ///
83    /// The caller is expected to ensure that the file exists.
84    #[must_use]
85    pub const fn db_path(mut self, path: &'tempfile std::path::Path) -> Self {
86        self.path = Some(path);
87        self
88    }
89    /// Set the amount of db_connections to use
90    /// Use this to test if the connection count matters for SQLite on devices.
91    #[must_use]
92    pub const fn db_connections(mut self, db_connections: Option<u32>) -> Self {
93        if let Some(conns) = db_connections {
94            self.db_connections = conns;
95        }
96        self
97    }
98    #[must_use]
99    pub const fn db_timeout(mut self, db_timeout: Option<u32>) -> Self {
100        if let Some(timeout) = db_timeout {
101            assert!(
102                timeout <= 25,
103                "DBus timeout is ~25s. timeout should be less."
104            );
105            self.timeout = Duration::from_secs(timeout as u64);
106        }
107        self
108    }
109    #[must_use]
110    pub const fn migrate(mut self, migrate: bool) -> Self {
111        self.migrate = migrate;
112        self
113    }
114
115    pub async fn build(self) -> Result<SqlitePool, Error> {
116        let db_path = self.path.expect("Must have a path");
117        if self.db_connections < 2 {
118            error!(
119                "Too few connections to function. conns = {} < 2",
120                self.db_connections
121            );
122        }
123        let pool = get_file_pool(db_path, self.db_connections, self.timeout).await?;
124
125        if self.migrate {
126            warn!("Running migrations on {:?}", &db_path);
127            run_migrations(&pool).await?;
128        }
129        Ok(pool)
130    }
131}
132
133impl Default for SqlitePoolBuilder<'_> {
134    fn default() -> Self {
135        Self::new()
136    }
137}
modio_logger_db/db.rs

modio_logger_db/
db.rs
