mithril_persistence/sqlite/
connection_builder.rs

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