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};
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.options.contains(&ConnectionOptions::EnableWriteAheadLog) {
94            connection
95                .execute("pragma journal_mode = wal; pragma synchronous = normal;")
96                .with_context(|| "SQLite initialization: could not enable WAL.")?;
97        }
98
99        if self.options.contains(&ConnectionOptions::EnableForeignKeys) {
100            connection
101                .execute("pragma foreign_keys=true")
102                .with_context(|| "SQLite initialization: could not enable FOREIGN KEY support.")?;
103        }
104
105        let migrations = self.sql_migrations.clone();
106        self.apply_migrations(&connection, migrations)?;
107        if self.options.contains(&ConnectionOptions::ForceDisableForeignKeys) {
108            connection
109                .execute("pragma foreign_keys=false")
110                .with_context(|| "SQLite initialization: could not disable FOREIGN KEY support.")?;
111        }
112        Ok(connection)
113    }
114
115    /// Apply a list of migration to the connection.
116    pub fn apply_migrations(
117        &self,
118        connection: &ConnectionThreadSafe,
119        sql_migrations: Vec<SqlMigration>,
120    ) -> StdResult<()> {
121        let logger = self.base_logger.new_with_component_name::<Self>();
122
123        if sql_migrations.is_empty().not() {
124            // Check database migrations
125            debug!(logger, "Applying database migrations");
126            let mut db_checker = DatabaseVersionChecker::new(
127                self.base_logger.clone(),
128                self.node_type.clone(),
129                connection,
130            );
131
132            for migration in sql_migrations {
133                db_checker.add_migration(migration.clone());
134            }
135
136            db_checker.apply().with_context(|| "Database migration error")?;
137        }
138
139        Ok(())
140    }
141}
142
143#[cfg(test)]
144mod tests {
145    use sqlite::Value;
146
147    use mithril_common::test_utils::TempDir;
148
149    use crate::sqlite::ConnectionOptions::ForceDisableForeignKeys;
150
151    use super::*;
152
153    // see: https://www.sqlite.org/pragma.html#pragma_journal_mode
154    const DEFAULT_SQLITE_JOURNAL_MODE: &str = "delete";
155    // see: https://www.sqlite.org/pragma.html#pragma_synchronous
156    const NORMAL_SYNCHRONOUS_FLAG: i64 = 1;
157
158    fn execute_single_cell_query(connection: &Connection, query: &str) -> Value {
159        let mut statement = connection.prepare(query).unwrap();
160        let mut row = statement.iter().next().unwrap().unwrap();
161        row.take(0)
162    }
163
164    #[test]
165    fn test_open_in_memory_without_foreign_key() {
166        let connection = ConnectionBuilder::open_memory().build().unwrap();
167
168        let journal_mode = execute_single_cell_query(&connection, "pragma journal_mode;");
169        let foreign_keys = execute_single_cell_query(&connection, "pragma foreign_keys;");
170
171        assert_eq!(Value::String("memory".to_string()), journal_mode);
172        assert_eq!(Value::Integer(false.into()), foreign_keys);
173    }
174
175    #[test]
176    fn test_open_with_foreign_key() {
177        let connection = ConnectionBuilder::open_memory()
178            .with_options(&[ConnectionOptions::EnableForeignKeys])
179            .build()
180            .unwrap();
181
182        let journal_mode = execute_single_cell_query(&connection, "pragma journal_mode;");
183        let foreign_keys = execute_single_cell_query(&connection, "pragma foreign_keys;");
184
185        assert_eq!(Value::String("memory".to_string()), journal_mode);
186        assert_eq!(Value::Integer(true.into()), foreign_keys);
187    }
188
189    #[test]
190    fn test_open_file_without_wal_and_foreign_keys() {
191        let dirpath = TempDir::create(
192            "mithril_test_database",
193            "test_open_file_without_wal_and_foreign_keys",
194        );
195        let filepath = dirpath.join("db.sqlite3");
196        assert!(!filepath.exists());
197
198        let connection = ConnectionBuilder::open_file(&filepath).build().unwrap();
199
200        let journal_mode = execute_single_cell_query(&connection, "pragma journal_mode;");
201        let foreign_keys = execute_single_cell_query(&connection, "pragma foreign_keys;");
202
203        assert!(filepath.exists());
204        assert_eq!(
205            Value::String(DEFAULT_SQLITE_JOURNAL_MODE.to_string()),
206            journal_mode
207        );
208        assert_eq!(Value::Integer(false.into()), foreign_keys);
209    }
210
211    #[test]
212    fn test_open_file_with_wal_and_foreign_keys() {
213        let dirpath = TempDir::create(
214            "mithril_test_database",
215            "test_open_file_with_wal_and_foreign_keys",
216        );
217        let filepath = dirpath.join("db.sqlite3");
218        assert!(!filepath.exists());
219
220        let connection = ConnectionBuilder::open_file(&filepath)
221            .with_options(&[
222                ConnectionOptions::EnableForeignKeys,
223                ConnectionOptions::EnableWriteAheadLog,
224            ])
225            .build()
226            .unwrap();
227
228        let journal_mode = execute_single_cell_query(&connection, "pragma journal_mode;");
229        let foreign_keys = execute_single_cell_query(&connection, "pragma foreign_keys;");
230
231        assert!(filepath.exists());
232        assert_eq!(Value::String("wal".to_string()), journal_mode);
233        assert_eq!(Value::Integer(true.into()), foreign_keys);
234    }
235
236    #[test]
237    fn enabling_wal_option_also_set_synchronous_flag_to_normal() {
238        let dirpath = TempDir::create(
239            "mithril_test_database",
240            "enabling_wal_option_also_set_synchronous_flag_to_normal",
241        );
242
243        let connection = ConnectionBuilder::open_file(&dirpath.join("db.sqlite3"))
244            .with_options(&[ConnectionOptions::EnableWriteAheadLog])
245            .build()
246            .unwrap();
247
248        let synchronous_flag = execute_single_cell_query(&connection, "pragma synchronous;");
249
250        assert_eq!(Value::Integer(NORMAL_SYNCHRONOUS_FLAG), synchronous_flag);
251    }
252
253    #[test]
254    fn builder_apply_given_migrations() {
255        let connection = ConnectionBuilder::open_memory()
256            .with_migrations(vec![
257                SqlMigration::new(1, "create table first(id integer);"),
258                SqlMigration::new(2, "create table second(id integer);"),
259            ])
260            .build()
261            .unwrap();
262
263        let tables_list = execute_single_cell_query(
264            &connection,
265            // Note: exclude sqlite system tables and migration system `db_version` table
266            "SELECT group_concat(name) FROM sqlite_schema \
267            WHERE type = 'table' AND name NOT LIKE 'sqlite_%' AND name != 'db_version' \
268            ORDER BY name;",
269        );
270
271        assert_eq!(Value::String("first,second".to_string()), tables_list);
272    }
273
274    #[test]
275    fn can_disable_foreign_keys_even_if_a_migration_enable_them() {
276        let connection = ConnectionBuilder::open_memory()
277            .with_migrations(vec![SqlMigration::new(1, "pragma foreign_keys=true;")])
278            .with_options(&[ForceDisableForeignKeys])
279            .build()
280            .unwrap();
281
282        let foreign_keys = execute_single_cell_query(&connection, "pragma foreign_keys;");
283        assert_eq!(Value::Integer(false.into()), foreign_keys);
284    }
285
286    #[test]
287    fn test_apply_a_partial_migrations() {
288        let migrations = vec![
289            SqlMigration::new(1, "create table first(id integer);"),
290            SqlMigration::new(2, "create table second(id integer);"),
291        ];
292
293        let connection = ConnectionBuilder::open_memory().build().unwrap();
294
295        assert!(connection.prepare("select * from first;").is_err());
296        assert!(connection.prepare("select * from second;").is_err());
297
298        ConnectionBuilder::open_memory()
299            .apply_migrations(&connection, migrations[0..1].to_vec())
300            .unwrap();
301
302        assert!(connection.prepare("select * from first;").is_ok());
303        assert!(connection.prepare("select * from second;").is_err());
304
305        ConnectionBuilder::open_memory()
306            .apply_migrations(&connection, migrations)
307            .unwrap();
308
309        assert!(connection.prepare("select * from first;").is_ok());
310        assert!(connection.prepare("select * from second;").is_ok());
311    }
312}