mithril_persistence/sqlite/
connection_builder.rs

1use std::ops::Not;
2use std::path::{Path, PathBuf};
3
4use anyhow::Context;
5use slog::{Logger, debug};
6use sqlite::{Connection, ConnectionThreadSafe};
7
8use mithril_common::StdResult;
9use mithril_common::logging::LoggerExtensions;
10
11use crate::database::{ApplicationNodeType, DatabaseVersionChecker, SqlMigration};
12use crate::sqlite::SqliteConnectionPool;
13
14/// Builder of SQLite connection
15pub struct ConnectionBuilder {
16    connection_path: PathBuf,
17    sql_migrations: Vec<SqlMigration>,
18    options: Vec<ConnectionOptions>,
19    node_type: ApplicationNodeType,
20    base_logger: Logger,
21}
22
23/// Options to apply to the connection
24#[derive(Debug, Clone, Ord, PartialOrd, Eq, PartialEq)]
25pub enum ConnectionOptions {
26    /// Enable Write Ahead Log journal mod (not available for in memory connection)
27    EnableWriteAheadLog,
28
29    /// Enable foreign key support
30    EnableForeignKeys,
31
32    /// Disable foreign key support after the migrations are run
33    ///
34    /// This option take priority over [ConnectionOptions::EnableForeignKeys] if both are enabled.
35    ForceDisableForeignKeys,
36}
37
38impl ConnectionBuilder {
39    pub(crate) const APPLY_MIGRATIONS_LOG: &str = "Applying database migrations";
40
41    /// Builder of file SQLite connection
42    pub fn open_file(path: &Path) -> Self {
43        Self {
44            connection_path: path.to_path_buf(),
45            sql_migrations: vec![],
46            options: vec![],
47            node_type: ApplicationNodeType::Signer,
48            base_logger: Logger::root(slog::Discard, slog::o!()),
49        }
50    }
51
52    /// Builder of in memory SQLite connection
53    pub fn open_memory() -> Self {
54        Self::open_file(":memory:".as_ref())
55    }
56
57    /// Set migrations to apply at build time
58    pub fn with_migrations(mut self, migrations: Vec<SqlMigration>) -> Self {
59        self.sql_migrations = migrations;
60        self
61    }
62
63    /// Set the [ConnectionOptions] to enabled on the connection.
64    pub fn with_options(mut self, options: &[ConnectionOptions]) -> Self {
65        for option in options {
66            self.options.push(option.clone());
67        }
68        self
69    }
70
71    /// Set the logger to log to at build time
72    pub fn with_logger(mut self, logger: Logger) -> Self {
73        self.base_logger = logger;
74        self
75    }
76
77    /// Set the node type (default: [ApplicationNodeType::Signer]).
78    pub fn with_node_type(mut self, node_type: ApplicationNodeType) -> Self {
79        self.node_type = node_type;
80        self
81    }
82
83    /// Build a connection based on the builder configuration
84    pub fn build(&self) -> StdResult<ConnectionThreadSafe> {
85        let connection = self.build_without_migrations()?;
86
87        let migrations = self.sql_migrations.clone();
88        self.apply_migrations(&connection, migrations)?;
89        if self.options.contains(&ConnectionOptions::ForceDisableForeignKeys) {
90            connection
91                .execute("pragma foreign_keys=false")
92                .with_context(|| "SQLite initialization: could not disable FOREIGN KEY support.")?;
93        }
94
95        Ok(connection)
96    }
97
98    /// Build a connection pool based on the builder configuration
99    pub fn build_pool(self, pool_size: usize) -> StdResult<SqliteConnectionPool> {
100        self.build()
101            .with_context(|| "SQLite initialization: failed to initialize connection")?;
102
103        SqliteConnectionPool::build(pool_size, self)
104    }
105
106    /// Build a connection based on the builder configuration without applying any defined migrations
107    ///
108    /// Useful for connections built after the database is already initialized
109    pub fn build_without_migrations(&self) -> StdResult<ConnectionThreadSafe> {
110        let logger = self.base_logger.new_with_component_name::<Self>();
111
112        debug!(logger, "Opening SQLite connection"; "path" => self.connection_path.display(), "options" => ?self.options);
113        let connection =
114            Connection::open_thread_safe(&self.connection_path).with_context(|| {
115                format!(
116                    "SQLite initialization: could not open connection with string '{}'.",
117                    self.connection_path.display()
118                )
119            })?;
120
121        if self.options.contains(&ConnectionOptions::EnableWriteAheadLog) {
122            connection
123                .execute("pragma journal_mode = wal; pragma synchronous = normal;")
124                .with_context(|| "SQLite initialization: could not enable WAL.")?;
125        }
126
127        if self.options.contains(&ConnectionOptions::EnableForeignKeys) {
128            connection
129                .execute("pragma foreign_keys=true")
130                .with_context(|| "SQLite initialization: could not enable FOREIGN KEY support.")?;
131        }
132
133        Ok(connection)
134    }
135
136    /// Apply a list of migration to the connection.
137    pub fn apply_migrations(
138        &self,
139        connection: &ConnectionThreadSafe,
140        sql_migrations: Vec<SqlMigration>,
141    ) -> StdResult<()> {
142        let logger = self.base_logger.new_with_component_name::<Self>();
143
144        if sql_migrations.is_empty().not() {
145            // Check database migrations
146            debug!(logger, "{}", Self::APPLY_MIGRATIONS_LOG);
147            let mut db_checker = DatabaseVersionChecker::new(
148                self.base_logger.clone(),
149                self.node_type.clone(),
150                connection,
151            );
152
153            for migration in sql_migrations {
154                db_checker.add_migration(migration);
155            }
156
157            db_checker.apply().with_context(|| "Database migration error")?;
158        }
159
160        Ok(())
161    }
162}
163
164#[cfg(test)]
165mod tests {
166    use sqlite::Value;
167
168    use mithril_common::temp_dir_create;
169    use mithril_common::test::TempDir;
170
171    use crate::sqlite::ConnectionOptions::ForceDisableForeignKeys;
172
173    use super::*;
174
175    // see: https://www.sqlite.org/pragma.html#pragma_journal_mode
176    const DEFAULT_SQLITE_JOURNAL_MODE: &str = "delete";
177    // see: https://www.sqlite.org/pragma.html#pragma_synchronous
178    const NORMAL_SYNCHRONOUS_FLAG: i64 = 1;
179
180    fn execute_single_cell_query(connection: &Connection, query: &str) -> Value {
181        let mut statement = connection.prepare(query).unwrap();
182        let mut row = statement.iter().next().unwrap().unwrap();
183        row.take(0)
184    }
185
186    #[test]
187    fn test_open_in_memory_without_foreign_key() {
188        let connection = ConnectionBuilder::open_memory().build().unwrap();
189
190        let journal_mode = execute_single_cell_query(&connection, "pragma journal_mode;");
191        let foreign_keys = execute_single_cell_query(&connection, "pragma foreign_keys;");
192
193        assert_eq!(Value::String("memory".to_string()), journal_mode);
194        assert_eq!(Value::Integer(false.into()), foreign_keys);
195    }
196
197    #[test]
198    fn test_open_with_foreign_key() {
199        let connection = ConnectionBuilder::open_memory()
200            .with_options(&[ConnectionOptions::EnableForeignKeys])
201            .build()
202            .unwrap();
203
204        let journal_mode = execute_single_cell_query(&connection, "pragma journal_mode;");
205        let foreign_keys = execute_single_cell_query(&connection, "pragma foreign_keys;");
206
207        assert_eq!(Value::String("memory".to_string()), journal_mode);
208        assert_eq!(Value::Integer(true.into()), foreign_keys);
209    }
210
211    #[test]
212    fn test_open_file_without_wal_and_foreign_keys() {
213        let dirpath = TempDir::create(
214            "mithril_test_database",
215            "test_open_file_without_wal_and_foreign_keys",
216        );
217        let filepath = dirpath.join("db.sqlite3");
218        assert!(!filepath.exists());
219
220        let connection = ConnectionBuilder::open_file(&filepath).build().unwrap();
221
222        let journal_mode = execute_single_cell_query(&connection, "pragma journal_mode;");
223        let foreign_keys = execute_single_cell_query(&connection, "pragma foreign_keys;");
224
225        assert!(filepath.exists());
226        assert_eq!(
227            Value::String(DEFAULT_SQLITE_JOURNAL_MODE.to_string()),
228            journal_mode
229        );
230        assert_eq!(Value::Integer(false.into()), foreign_keys);
231    }
232
233    #[test]
234    fn test_open_file_with_wal_and_foreign_keys() {
235        let dirpath = TempDir::create(
236            "mithril_test_database",
237            "test_open_file_with_wal_and_foreign_keys",
238        );
239        let filepath = dirpath.join("db.sqlite3");
240        assert!(!filepath.exists());
241
242        let connection = ConnectionBuilder::open_file(&filepath)
243            .with_options(&[
244                ConnectionOptions::EnableForeignKeys,
245                ConnectionOptions::EnableWriteAheadLog,
246            ])
247            .build()
248            .unwrap();
249
250        let journal_mode = execute_single_cell_query(&connection, "pragma journal_mode;");
251        let foreign_keys = execute_single_cell_query(&connection, "pragma foreign_keys;");
252
253        assert!(filepath.exists());
254        assert_eq!(Value::String("wal".to_string()), journal_mode);
255        assert_eq!(Value::Integer(true.into()), foreign_keys);
256    }
257
258    #[test]
259    fn enabling_wal_option_also_set_synchronous_flag_to_normal() {
260        let dirpath = TempDir::create(
261            "mithril_test_database",
262            "enabling_wal_option_also_set_synchronous_flag_to_normal",
263        );
264
265        let connection = ConnectionBuilder::open_file(&dirpath.join("db.sqlite3"))
266            .with_options(&[ConnectionOptions::EnableWriteAheadLog])
267            .build()
268            .unwrap();
269
270        let synchronous_flag = execute_single_cell_query(&connection, "pragma synchronous;");
271
272        assert_eq!(Value::Integer(NORMAL_SYNCHRONOUS_FLAG), synchronous_flag);
273    }
274
275    #[test]
276    fn builder_apply_given_migrations() {
277        let connection = ConnectionBuilder::open_memory()
278            .with_migrations(vec![
279                SqlMigration::new(1, "create table first(id integer);"),
280                SqlMigration::new(2, "create table second(id integer);"),
281            ])
282            .build()
283            .unwrap();
284
285        let tables_list = execute_single_cell_query(
286            &connection,
287            // Note: exclude sqlite system tables and migration system `db_version` table
288            "SELECT group_concat(name) FROM sqlite_schema \
289            WHERE type = 'table' AND name NOT LIKE 'sqlite_%' AND name != 'db_version' \
290            ORDER BY name;",
291        );
292
293        assert_eq!(Value::String("first,second".to_string()), tables_list);
294    }
295
296    #[test]
297    fn building_pool_apply_given_migrations() {
298        let temp_dir = temp_dir_create!();
299        let pool = ConnectionBuilder::open_file(&temp_dir.join("db.sqlite"))
300            .with_migrations(vec![SqlMigration::new(1, "create table test(id integer);")])
301            .build_pool(1)
302            .unwrap();
303
304        let connection = pool.connection().unwrap();
305        connection
306            .execute("SELECT * from test;")
307            .expect("Migrations should have created the 'test' table");
308    }
309
310    #[test]
311    fn can_disable_foreign_keys_even_if_a_migration_enable_them() {
312        let connection = ConnectionBuilder::open_memory()
313            .with_migrations(vec![SqlMigration::new(1, "pragma foreign_keys=true;")])
314            .with_options(&[ForceDisableForeignKeys])
315            .build()
316            .unwrap();
317
318        let foreign_keys = execute_single_cell_query(&connection, "pragma foreign_keys;");
319        assert_eq!(Value::Integer(false.into()), foreign_keys);
320    }
321
322    #[test]
323    fn test_apply_a_partial_migrations() {
324        let migrations = vec![
325            SqlMigration::new(1, "create table first(id integer);"),
326            SqlMigration::new(2, "create table second(id integer);"),
327        ];
328
329        let connection = ConnectionBuilder::open_memory().build().unwrap();
330
331        assert!(connection.prepare("select * from first;").is_err());
332        assert!(connection.prepare("select * from second;").is_err());
333
334        ConnectionBuilder::open_memory()
335            .apply_migrations(&connection, migrations[0..1].to_vec())
336            .unwrap();
337
338        assert!(connection.prepare("select * from first;").is_ok());
339        assert!(connection.prepare("select * from second;").is_err());
340
341        ConnectionBuilder::open_memory()
342            .apply_migrations(&connection, migrations)
343            .unwrap();
344
345        assert!(connection.prepare("select * from first;").is_ok());
346        assert!(connection.prepare("select * from second;").is_ok());
347    }
348}
mithril_persistence/sqlite/connection_builder.rs

mithril_persistence/sqlite/
connection_builder.rs
