sqlite.rs (7104B)
1 use crate::error::RadrootsNostrSignerError; 2 use crate::migrations; 3 use radroots_sql_core::{SqlExecutor, SqliteExecutor}; 4 use std::path::Path; 5 6 pub struct RadrootsNostrSignerSqliteDb { 7 executor: SqliteExecutor, 8 file_backed: bool, 9 } 10 11 impl RadrootsNostrSignerSqliteDb { 12 pub fn open(path: impl AsRef<Path>) -> Result<Self, RadrootsNostrSignerError> { 13 let path = path.as_ref(); 14 if let Some(parent) = path.parent() 15 && !parent.as_os_str().is_empty() 16 { 17 std::fs::create_dir_all(parent) 18 .map_err(|error| RadrootsNostrSignerError::Store(error.to_string()))?; 19 } 20 let executor = SqliteExecutor::open(path)?; 21 let db = Self { 22 executor, 23 file_backed: true, 24 }; 25 db.configure()?; 26 db.migrate_up()?; 27 Ok(db) 28 } 29 30 pub fn open_memory() -> Result<Self, RadrootsNostrSignerError> { 31 let executor = SqliteExecutor::open_memory()?; 32 let db = Self { 33 executor, 34 file_backed: false, 35 }; 36 db.configure()?; 37 db.migrate_up()?; 38 Ok(db) 39 } 40 41 pub fn executor(&self) -> &SqliteExecutor { 42 &self.executor 43 } 44 45 pub fn migrate_up(&self) -> Result<(), RadrootsNostrSignerError> { 46 migrations::run_all_up(&self.executor)?; 47 Ok(()) 48 } 49 50 pub fn migrate_down(&self) -> Result<(), RadrootsNostrSignerError> { 51 migrations::run_all_down(&self.executor)?; 52 Ok(()) 53 } 54 55 fn configure(&self) -> Result<(), RadrootsNostrSignerError> { 56 let pragma_batch = if self.file_backed { 57 "PRAGMA foreign_keys = ON; 58 PRAGMA synchronous = FULL; 59 PRAGMA wal_autocheckpoint = 1000; 60 PRAGMA busy_timeout = 5000; 61 PRAGMA temp_store = MEMORY;" 62 } else { 63 "PRAGMA foreign_keys = ON; 64 PRAGMA synchronous = NORMAL; 65 PRAGMA busy_timeout = 5000; 66 PRAGMA temp_store = MEMORY;" 67 }; 68 let _ = self.executor.exec(pragma_batch, "[]")?; 69 if self.file_backed { 70 let _ = self.executor.query_raw("PRAGMA journal_mode = WAL", "[]")?; 71 } else { 72 let _ = self 73 .executor 74 .query_raw("PRAGMA journal_mode = MEMORY", "[]")?; 75 } 76 Ok(()) 77 } 78 } 79 80 #[cfg(test)] 81 mod tests { 82 use super::RadrootsNostrSignerSqliteDb; 83 use radroots_sql_core::SqlExecutor; 84 use serde_json::Value; 85 86 fn query_values( 87 db: &RadrootsNostrSignerSqliteDb, 88 sql: &str, 89 ) -> Vec<serde_json::Map<String, Value>> { 90 let raw = db.executor().query_raw(sql, "[]").expect("query"); 91 serde_json::from_str::<Vec<serde_json::Map<String, Value>>>(&raw).expect("rows") 92 } 93 94 fn query_single_text(db: &RadrootsNostrSignerSqliteDb, sql: &str, field: &str) -> String { 95 query_values(db, sql) 96 .into_iter() 97 .next() 98 .and_then(|row| row.get(field).cloned()) 99 .and_then(|value| value.as_str().map(ToOwned::to_owned)) 100 .expect("single text row") 101 } 102 103 fn query_single_i64(db: &RadrootsNostrSignerSqliteDb, sql: &str, field: &str) -> i64 { 104 query_values(db, sql) 105 .into_iter() 106 .next() 107 .and_then(|row| row.get(field).cloned()) 108 .and_then(|value| value.as_i64()) 109 .expect("single integer row") 110 } 111 112 #[test] 113 fn open_memory_bootstraps_schema_and_migrations_idempotently() { 114 let db = RadrootsNostrSignerSqliteDb::open_memory().expect("open memory db"); 115 db.migrate_up().expect("rerun migrations"); 116 117 let tables = query_values( 118 &db, 119 "SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name", 120 ); 121 let table_names = tables 122 .into_iter() 123 .filter_map(|row| { 124 row.get("name") 125 .and_then(Value::as_str) 126 .map(ToOwned::to_owned) 127 }) 128 .collect::<Vec<_>>(); 129 assert!(table_names.iter().any(|name| name == "__migrations")); 130 assert!( 131 table_names 132 .iter() 133 .any(|name| name == "signer_store_metadata") 134 ); 135 assert!(table_names.iter().any(|name| name == "signer_connection")); 136 assert!( 137 table_names 138 .iter() 139 .any(|name| name == "signer_connection_permission_grant") 140 ); 141 assert!( 142 table_names 143 .iter() 144 .any(|name| name == "signer_connection_relay") 145 ); 146 assert!( 147 table_names 148 .iter() 149 .any(|name| name == "signer_connection_auth_challenge") 150 ); 151 assert!( 152 table_names 153 .iter() 154 .any(|name| name == "signer_connection_pending_request") 155 ); 156 assert!( 157 table_names 158 .iter() 159 .any(|name| name == "signer_request_audit") 160 ); 161 assert!( 162 table_names 163 .iter() 164 .any(|name| name == "signer_publish_workflow") 165 ); 166 167 let migration_count = query_single_i64( 168 &db, 169 "SELECT COUNT(*) AS applied_count FROM __migrations", 170 "applied_count", 171 ); 172 assert_eq!(migration_count, 2); 173 174 let store_version = query_single_i64( 175 &db, 176 "SELECT store_version FROM signer_store_metadata WHERE singleton_id = 1", 177 "store_version", 178 ); 179 assert_eq!(store_version, 1); 180 } 181 182 #[test] 183 fn file_database_uses_wal_and_foreign_keys() { 184 let temp = tempfile::tempdir().expect("tempdir"); 185 let db = RadrootsNostrSignerSqliteDb::open(temp.path().join("signer.sqlite")) 186 .expect("open sqlite file db"); 187 188 assert_eq!( 189 query_single_text(&db, "PRAGMA journal_mode", "journal_mode"), 190 "wal" 191 ); 192 assert_eq!( 193 query_single_i64(&db, "PRAGMA foreign_keys", "foreign_keys"), 194 1 195 ); 196 } 197 198 #[test] 199 fn migrate_down_and_up_roundtrip_restores_schema() { 200 let db = RadrootsNostrSignerSqliteDb::open_memory().expect("open memory db"); 201 db.migrate_down().expect("migrate down"); 202 203 let tables = query_values( 204 &db, 205 "SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY name", 206 ); 207 let table_names = tables 208 .into_iter() 209 .filter_map(|row| { 210 row.get("name") 211 .and_then(Value::as_str) 212 .map(ToOwned::to_owned) 213 }) 214 .collect::<Vec<_>>(); 215 assert_eq!(table_names, vec!["__migrations".to_owned()]); 216 217 db.migrate_up().expect("migrate up again"); 218 let migration_count = query_single_i64( 219 &db, 220 "SELECT COUNT(*) AS applied_count FROM __migrations", 221 "applied_count", 222 ); 223 assert_eq!(migration_count, 2); 224 } 225 }