BlueSky & more on desktop
lazurite.stormlightlabs.org/
tauri
rust
typescript
bluesky
appview
atproto
solid
1use super::error::AppError;
2use rusqlite::ffi::sqlite3_auto_extension;
3use rusqlite::{params, Connection, OpenFlags, OptionalExtension};
4use sqlite_vec::sqlite3_vec_init;
5use std::collections::HashSet;
6use std::ffi::{c_char, c_int};
7use std::fs;
8use std::path::PathBuf;
9use std::sync::{Arc, Mutex};
10use tauri::{AppHandle, Manager};
11
12pub type DbPool = Arc<Mutex<Connection>>;
13
14type SqliteVecInit = unsafe extern "C" fn();
15
16type SqliteAutoExtension = unsafe extern "C" fn(
17 db: *mut rusqlite::ffi::sqlite3,
18 pz_err_msg: *mut *mut c_char,
19 api: *const rusqlite::ffi::sqlite3_api_routines,
20) -> c_int;
21
22struct Migration {
23 version: i64,
24 name: &'static str,
25 sql: &'static str,
26}
27
28impl Migration {
29 const fn new(version: i64, name: &'static str, sql: &'static str) -> Self {
30 Self { version, name, sql }
31 }
32}
33
34const MIGRATIONS: &[Migration] = &[
35 Migration::new(1, "initial_schema", include_str!("migrations/001_initial.sql")),
36 Migration::new(2, "oauth_storage", include_str!("migrations/002_auth_storage.sql")),
37 Migration::new(
38 3,
39 "oauth_sessions_without_fk",
40 include_str!("migrations/003_oauth_sessions_without_fk.sql"),
41 ),
42 Migration::new(4, "account_avatars", include_str!("migrations/004_account_avatars.sql")),
43 Migration::new(5, "sync_state", include_str!("migrations/005_sync_state.sql")),
44 Migration::new(6, "app_settings", include_str!("migrations/006_app_settings.sql")),
45 Migration::new(
46 7,
47 "search_owner_scope",
48 include_str!("migrations/007_search_owner_scope.sql"),
49 ),
50 Migration::new(8, "columns", include_str!("migrations/008_columns.sql")),
51 Migration::new(
52 9,
53 "columns_expand_kinds",
54 include_str!("migrations/009_columns_expand_kinds.sql"),
55 ),
56 Migration::new(
57 10,
58 "embeddings_opt_in",
59 include_str!("migrations/010_embeddings_opt_in.sql"),
60 ),
61 Migration::new(11, "drafts", include_str!("migrations/011_drafts.sql")),
62 Migration::new(12, "labeler_cache", include_str!("migrations/012_labeler_cache.sql")),
63];
64
65pub fn initialize_database(app: &AppHandle) -> Result<DbPool, AppError> {
66 unsafe {
67 let init: SqliteVecInit = sqlite3_vec_init;
68 let auto_extension: SqliteAutoExtension = std::mem::transmute(init);
69 sqlite3_auto_extension(Some(auto_extension));
70 }
71
72 let database_path = resolve_database_path(app)?;
73 if let Some(parent) = database_path.parent() {
74 fs::create_dir_all(parent)?;
75 }
76
77 let connection = Connection::open_with_flags(
78 database_path,
79 OpenFlags::SQLITE_OPEN_READ_WRITE | OpenFlags::SQLITE_OPEN_CREATE,
80 )?;
81
82 connection.pragma_update(None, "journal_mode", "WAL")?;
83 connection.pragma_update(None, "foreign_keys", "ON")?;
84
85 run_migrations(&connection)?;
86 validate_sqlite_vec(&connection)?;
87
88 Ok(Arc::new(Mutex::new(connection)))
89}
90
91fn resolve_database_path(app: &AppHandle) -> Result<PathBuf, AppError> {
92 let mut app_data_dir = app
93 .path()
94 .app_data_dir()
95 .map_err(|error| AppError::PathResolve(error.to_string()))?;
96
97 app_data_dir.push("lazurite.db");
98 Ok(app_data_dir)
99}
100
101fn run_migrations(connection: &Connection) -> Result<(), AppError> {
102 connection.execute_batch(
103 "
104 CREATE TABLE IF NOT EXISTS schema_migrations (
105 version INTEGER PRIMARY KEY,
106 name TEXT NOT NULL,
107 applied_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
108 );
109 ",
110 )?;
111
112 let mut applied_statement = connection.prepare("SELECT version FROM schema_migrations")?;
113 let applied_rows = applied_statement.query_map([], |row| row.get::<_, i64>(0))?;
114
115 let mut applied_versions = HashSet::new();
116 for version in applied_rows {
117 applied_versions.insert(version?);
118 }
119
120 for migration in MIGRATIONS {
121 if applied_versions.contains(&migration.version) {
122 continue;
123 }
124
125 let transaction = connection.unchecked_transaction()?;
126 transaction.execute_batch(migration.sql)?;
127 transaction.execute(
128 "INSERT INTO schema_migrations(version, name) VALUES (?1, ?2)",
129 params![migration.version, migration.name],
130 )?;
131 transaction.commit()?;
132 }
133
134 Ok(())
135}
136
137fn validate_sqlite_vec(connection: &Connection) -> Result<(), AppError> {
138 let version: Option<String> = connection
139 .query_row("SELECT vec_version()", [], |row| row.get(0))
140 .optional()?;
141
142 match version.is_none() {
143 true => Err(AppError::Validation(
144 "sqlite-vec extension did not report a version".to_string(),
145 )),
146 false => Ok(()),
147 }
148}
149
150pub(crate) fn reset_database(connection: &Connection) -> Result<(), AppError> {
151 connection.execute_batch(
152 "
153 DROP TRIGGER IF EXISTS posts_ai;
154 DROP TRIGGER IF EXISTS posts_ad;
155 DROP TRIGGER IF EXISTS posts_au;
156
157 DROP TABLE IF EXISTS posts_vec;
158 DROP TABLE IF EXISTS posts_fts;
159 DROP TABLE IF EXISTS posts;
160 DROP TABLE IF EXISTS sync_state;
161 DROP TABLE IF EXISTS oauth_sessions;
162 DROP TABLE IF EXISTS oauth_auth_requests;
163 DROP TABLE IF EXISTS accounts;
164 DROP TABLE IF EXISTS app_settings;
165 DROP TABLE IF EXISTS columns;
166 DROP TABLE IF EXISTS schema_migrations;
167
168 PRAGMA wal_checkpoint(TRUNCATE);
169 VACUUM;
170 ",
171 )?;
172
173 run_migrations(connection)
174}
175
176#[cfg(test)]
177mod tests {
178 use rusqlite::{params, Connection};
179
180 fn auth_schema_connection() -> Connection {
181 let connection = Connection::open_in_memory().expect("in-memory db should open");
182 connection
183 .pragma_update(None, "foreign_keys", "ON")
184 .expect("foreign keys should enable");
185 connection
186 .execute_batch(
187 "
188 CREATE TABLE accounts (
189 did TEXT PRIMARY KEY,
190 handle TEXT,
191 pds_url TEXT,
192 active INTEGER NOT NULL DEFAULT 0 CHECK(active IN (0, 1))
193 );
194 ",
195 )
196 .expect("accounts table should apply");
197 connection
198 .execute_batch(include_str!("migrations/002_auth_storage.sql"))
199 .expect("auth storage schema should apply");
200 connection
201 }
202
203 #[test]
204 fn oauth_sessions_require_accounts_before_migration_three() {
205 let connection = auth_schema_connection();
206
207 let error = connection
208 .execute(
209 "
210 INSERT INTO oauth_sessions(did, session_id, session_json, updated_at)
211 VALUES (?1, ?2, ?3, CURRENT_TIMESTAMP)
212 ",
213 params!["did:plc:ghost", "session-1", "{}"],
214 )
215 .expect_err("foreign key should reject oauth sessions without an account row");
216
217 assert!(error.to_string().contains("FOREIGN KEY constraint failed"));
218 }
219
220 #[test]
221 fn migration_three_allows_oauth_sessions_before_account_insert() {
222 let connection = auth_schema_connection();
223 connection
224 .execute_batch(include_str!("migrations/003_oauth_sessions_without_fk.sql"))
225 .expect("migration three should apply");
226
227 connection
228 .execute(
229 "
230 INSERT INTO oauth_sessions(did, session_id, session_json, updated_at)
231 VALUES (?1, ?2, ?3, CURRENT_TIMESTAMP)
232 ",
233 params!["did:plc:ghost", "session-1", "{}"],
234 )
235 .expect("oauth session insert should succeed after migration three");
236
237 let stored_count: i64 = connection
238 .query_row(
239 "SELECT COUNT(*) FROM oauth_sessions WHERE did = ?1",
240 params!["did:plc:ghost"],
241 |row| row.get(0),
242 )
243 .expect("oauth session count should query");
244
245 assert_eq!(stored_count, 1);
246 }
247
248 #[test]
249 fn migration_nine_expands_column_kinds() {
250 let connection = Connection::open_in_memory().expect("in-memory db should open");
251
252 connection
253 .execute_batch(include_str!("migrations/008_columns.sql"))
254 .expect("columns schema should apply");
255
256 let old_error = connection
257 .execute(
258 "
259 INSERT INTO columns(id, account_did, kind, config, position, width)
260 VALUES (?1, ?2, ?3, ?4, ?5, ?6)
261 ",
262 params!["column-1", "did:plc:test", "messages", "{}", 0_i64, "standard"],
263 )
264 .expect_err("old schema should reject new column kinds");
265
266 assert!(old_error.to_string().contains("CHECK constraint failed"));
267
268 connection
269 .execute_batch(include_str!("migrations/009_columns_expand_kinds.sql"))
270 .expect("migration nine should apply");
271
272 for (index, kind) in ["messages", "search", "profile"].into_iter().enumerate() {
273 connection
274 .execute(
275 "
276 INSERT INTO columns(id, account_did, kind, config, position, width)
277 VALUES (?1, ?2, ?3, ?4, ?5, ?6)
278 ",
279 params![
280 format!("column-next-{index}"),
281 "did:plc:test",
282 kind,
283 "{}",
284 index as i64,
285 "standard"
286 ],
287 )
288 .expect("expanded schema should accept new column kinds");
289 }
290 }
291
292 #[test]
293 fn migration_ten_forces_embeddings_opt_in_defaults() {
294 let connection = Connection::open_in_memory().expect("in-memory db should open");
295 connection
296 .execute_batch(include_str!("migrations/006_app_settings.sql"))
297 .expect("settings migration should apply");
298
299 let seeded_enabled: String = connection
300 .query_row(
301 "SELECT value FROM app_settings WHERE key = 'embeddings_enabled'",
302 [],
303 |row| row.get(0),
304 )
305 .expect("embeddings_enabled should exist after migration 006");
306 assert_eq!(seeded_enabled, "1");
307
308 connection
309 .execute_batch(include_str!("migrations/010_embeddings_opt_in.sql"))
310 .expect("migration ten should apply");
311
312 let embeddings_enabled: String = connection
313 .query_row(
314 "SELECT value FROM app_settings WHERE key = 'embeddings_enabled'",
315 [],
316 |row| row.get(0),
317 )
318 .expect("embeddings_enabled should exist after migration 010");
319 let preflight_seen: String = connection
320 .query_row(
321 "SELECT value FROM app_settings WHERE key = 'embeddings_preflight_seen'",
322 [],
323 |row| row.get(0),
324 )
325 .expect("embeddings_preflight_seen should exist after migration 010");
326
327 assert_eq!(embeddings_enabled, "0");
328 assert_eq!(preflight_seen, "0");
329 }
330}