1// Migrations are constructed by domain, and stored in a table in the connection db with domain name,
2// effected tables, actual query text, and order.
3// If a migration is run and any of the query texts don't match, the app panics on startup (maybe fallback
4// to creating a new db?)
5// Otherwise any missing migrations are run on the connection
6
7use std::ffi::CString;
8
9use anyhow::{Context as _, Result};
10use indoc::{formatdoc, indoc};
11use libsqlite3_sys::sqlite3_exec;
12
13use crate::connection::Connection;
14
15impl Connection {
16 fn eager_exec(&self, sql: &str) -> anyhow::Result<()> {
17 let sql_str = CString::new(sql).context("Error creating cstr")?;
18 unsafe {
19 sqlite3_exec(
20 self.sqlite3,
21 sql_str.as_c_str().as_ptr(),
22 None,
23 std::ptr::null_mut(),
24 std::ptr::null_mut(),
25 );
26 }
27 self.last_error()
28 .with_context(|| format!("Prepare call failed for query:\n{}", sql))?;
29
30 Ok(())
31 }
32
33 /// Migrate the database, for the given domain.
34 /// Note: Unlike everything else in SQLez, migrations are run eagerly, without first
35 /// preparing the SQL statements. This makes it possible to do multi-statement schema
36 /// updates in a single string without running into prepare errors.
37 pub fn migrate(
38 &self,
39 domain: &'static str,
40 migrations: &[&'static str],
41 mut should_allow_migration_change: impl FnMut(usize, &str, &str) -> bool,
42 ) -> Result<()> {
43 self.with_savepoint("migrating", || {
44 // Setup the migrations table unconditionally
45 self.exec(indoc! {"
46 CREATE TABLE IF NOT EXISTS migrations (
47 domain TEXT,
48 step INTEGER,
49 migration TEXT
50 )"})?()?;
51
52 let completed_migrations =
53 self.select_bound::<&str, (String, usize, String)>(indoc! {"
54 SELECT domain, step, migration FROM migrations
55 WHERE domain = ?
56 ORDER BY step
57 "})?(domain)?;
58
59 let mut store_completed_migration = self
60 .exec_bound("INSERT INTO migrations (domain, step, migration) VALUES (?, ?, ?)")?;
61
62 let mut did_migrate = false;
63 for (index, migration) in migrations.iter().enumerate() {
64 let migration =
65 sqlformat::format(migration, &sqlformat::QueryParams::None, Default::default());
66 if let Some((_, _, completed_migration)) = completed_migrations.get(index) {
67 // Reformat completed migrations with the current `sqlformat` version, so that past migrations stored
68 // conform to the new formatting rules.
69 let completed_migration = sqlformat::format(
70 completed_migration,
71 &sqlformat::QueryParams::None,
72 Default::default(),
73 );
74 if completed_migration == migration {
75 // Migration already run. Continue
76 continue;
77 } else if should_allow_migration_change(index, &completed_migration, &migration)
78 {
79 continue;
80 } else {
81 anyhow::bail!(formatdoc! {"
82 Migration changed for {domain} at step {index}
83
84 Stored migration:
85 {completed_migration}
86
87 Proposed migration:
88 {migration}"});
89 }
90 }
91
92 self.eager_exec(&migration)?;
93 did_migrate = true;
94 store_completed_migration((domain, index, migration))?;
95 }
96
97 if did_migrate {
98 self.delete_rows_with_orphaned_foreign_key_references()?;
99 self.exec("PRAGMA foreign_key_check;")?()?;
100 }
101
102 Ok(())
103 })
104 }
105
106 /// Delete any rows that were orphaned by a migration. This is needed
107 /// because we disable foreign key constraints during migrations, so
108 /// that it's possible to re-create a table with the same name, without
109 /// deleting all associated data.
110 fn delete_rows_with_orphaned_foreign_key_references(&self) -> Result<()> {
111 let foreign_key_info: Vec<(String, String, String, String)> = self.select(
112 r#"
113 SELECT DISTINCT
114 schema.name as child_table,
115 foreign_keys.[from] as child_key,
116 foreign_keys.[table] as parent_table,
117 foreign_keys.[to] as parent_key
118 FROM sqlite_schema schema
119 JOIN pragma_foreign_key_list(schema.name) foreign_keys
120 WHERE
121 schema.type = 'table' AND
122 schema.name NOT LIKE "sqlite_%"
123 "#,
124 )?()?;
125
126 if !foreign_key_info.is_empty() {
127 log::info!(
128 "Found {} foreign key relationships to check",
129 foreign_key_info.len()
130 );
131 }
132
133 for (child_table, child_key, parent_table, parent_key) in foreign_key_info {
134 self.exec(&format!(
135 "
136 DELETE FROM {child_table}
137 WHERE {child_key} IS NOT NULL and {child_key} NOT IN
138 (SELECT {parent_key} FROM {parent_table})
139 "
140 ))?()?;
141 }
142
143 Ok(())
144 }
145}
146
147#[cfg(test)]
148mod test {
149 use indoc::indoc;
150
151 use crate::connection::Connection;
152
153 #[test]
154 fn test_migrations_are_added_to_table() {
155 let connection = Connection::open_memory(Some("migrations_are_added_to_table"));
156
157 // Create first migration with a single step and run it
158 connection
159 .migrate(
160 "test",
161 &[indoc! {"
162 CREATE TABLE test1 (
163 a TEXT,
164 b TEXT
165 )"}],
166 disallow_migration_change,
167 )
168 .unwrap();
169
170 // Verify it got added to the migrations table
171 assert_eq!(
172 &connection
173 .select::<String>("SELECT (migration) FROM migrations")
174 .unwrap()()
175 .unwrap()[..],
176 &[indoc! {"CREATE TABLE test1 (a TEXT, b TEXT)"}],
177 );
178
179 // Add another step to the migration and run it again
180 connection
181 .migrate(
182 "test",
183 &[
184 indoc! {"
185 CREATE TABLE test1 (
186 a TEXT,
187 b TEXT
188 )"},
189 indoc! {"
190 CREATE TABLE test2 (
191 c TEXT,
192 d TEXT
193 )"},
194 ],
195 disallow_migration_change,
196 )
197 .unwrap();
198
199 // Verify it is also added to the migrations table
200 assert_eq!(
201 &connection
202 .select::<String>("SELECT (migration) FROM migrations")
203 .unwrap()()
204 .unwrap()[..],
205 &[
206 indoc! {"CREATE TABLE test1 (a TEXT, b TEXT)"},
207 indoc! {"CREATE TABLE test2 (c TEXT, d TEXT)"},
208 ],
209 );
210 }
211
212 #[test]
213 fn test_migration_setup_works() {
214 let connection = Connection::open_memory(Some("migration_setup_works"));
215
216 connection
217 .exec(indoc! {"
218 CREATE TABLE IF NOT EXISTS migrations (
219 domain TEXT,
220 step INTEGER,
221 migration TEXT
222 );"})
223 .unwrap()()
224 .unwrap();
225
226 let mut store_completed_migration = connection
227 .exec_bound::<(&str, usize, String)>(indoc! {"
228 INSERT INTO migrations (domain, step, migration)
229 VALUES (?, ?, ?)"})
230 .unwrap();
231
232 let domain = "test_domain";
233 for i in 0..5 {
234 // Create a table forcing a schema change
235 connection
236 .exec(&format!("CREATE TABLE table{} ( test TEXT );", i))
237 .unwrap()()
238 .unwrap();
239
240 store_completed_migration((domain, i, i.to_string())).unwrap();
241 }
242 }
243
244 #[test]
245 fn migrations_dont_rerun() {
246 let connection = Connection::open_memory(Some("migrations_dont_rerun"));
247
248 // Create migration which clears a table
249
250 // Manually create the table for that migration with a row
251 connection
252 .exec(indoc! {"
253 CREATE TABLE test_table (
254 test_column INTEGER
255 );"})
256 .unwrap()()
257 .unwrap();
258 connection
259 .exec(indoc! {"
260 INSERT INTO test_table (test_column) VALUES (1);"})
261 .unwrap()()
262 .unwrap();
263
264 assert_eq!(
265 connection
266 .select_row::<usize>("SELECT * FROM test_table")
267 .unwrap()()
268 .unwrap(),
269 Some(1)
270 );
271
272 // Run the migration verifying that the row got dropped
273 connection
274 .migrate(
275 "test",
276 &["DELETE FROM test_table"],
277 disallow_migration_change,
278 )
279 .unwrap();
280 assert_eq!(
281 connection
282 .select_row::<usize>("SELECT * FROM test_table")
283 .unwrap()()
284 .unwrap(),
285 None
286 );
287
288 // Recreate the dropped row
289 connection
290 .exec("INSERT INTO test_table (test_column) VALUES (2)")
291 .unwrap()()
292 .unwrap();
293
294 // Run the same migration again and verify that the table was left unchanged
295 connection
296 .migrate(
297 "test",
298 &["DELETE FROM test_table"],
299 disallow_migration_change,
300 )
301 .unwrap();
302 assert_eq!(
303 connection
304 .select_row::<usize>("SELECT * FROM test_table")
305 .unwrap()()
306 .unwrap(),
307 Some(2)
308 );
309 }
310
311 #[test]
312 fn changed_migration_fails() {
313 let connection = Connection::open_memory(Some("changed_migration_fails"));
314
315 // Create a migration with two steps and run it
316 connection
317 .migrate(
318 "test migration",
319 &[
320 "CREATE TABLE test (col INTEGER)",
321 "INSERT INTO test (col) VALUES (1)",
322 ],
323 disallow_migration_change,
324 )
325 .unwrap();
326
327 let mut migration_changed = false;
328
329 // Create another migration with the same domain but different steps
330 let second_migration_result = connection.migrate(
331 "test migration",
332 &[
333 "CREATE TABLE test (color INTEGER )",
334 "INSERT INTO test (color) VALUES (1)",
335 ],
336 |_, old, new| {
337 assert_eq!(old, "CREATE TABLE test (col INTEGER)");
338 assert_eq!(new, "CREATE TABLE test (color INTEGER)");
339 migration_changed = true;
340 false
341 },
342 );
343
344 // Verify new migration returns error when run
345 assert!(second_migration_result.is_err())
346 }
347
348 #[test]
349 fn test_create_alter_drop() {
350 let connection = Connection::open_memory(Some("test_create_alter_drop"));
351
352 connection
353 .migrate(
354 "first_migration",
355 &["CREATE TABLE table1(a TEXT) STRICT;"],
356 disallow_migration_change,
357 )
358 .unwrap();
359
360 connection
361 .exec("INSERT INTO table1(a) VALUES (\"test text\");")
362 .unwrap()()
363 .unwrap();
364
365 connection
366 .migrate(
367 "second_migration",
368 &[indoc! {"
369 CREATE TABLE table2(b TEXT) STRICT;
370
371 INSERT INTO table2 (b)
372 SELECT a FROM table1;
373
374 DROP TABLE table1;
375
376 ALTER TABLE table2 RENAME TO table1;
377 "}],
378 disallow_migration_change,
379 )
380 .unwrap();
381
382 let res = &connection.select::<String>("SELECT b FROM table1").unwrap()().unwrap()[0];
383
384 assert_eq!(res, "test text");
385 }
386
387 fn disallow_migration_change(_: usize, _: &str, _: &str) -> bool {
388 false
389 }
390}