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 for (index, migration) in migrations.iter().enumerate() {
63 let migration =
64 sqlformat::format(migration, &sqlformat::QueryParams::None, Default::default());
65 if let Some((_, _, completed_migration)) = completed_migrations.get(index) {
66 // Reformat completed migrations with the current `sqlformat` version, so that past migrations stored
67 // conform to the new formatting rules.
68 let completed_migration = sqlformat::format(
69 completed_migration,
70 &sqlformat::QueryParams::None,
71 Default::default(),
72 );
73 if completed_migration == migration
74 || migration.trim().starts_with("-- ALLOW_MIGRATION_CHANGE")
75 {
76 // Migration already run. Continue
77 continue;
78 } else if should_allow_migration_change(index, &completed_migration, &migration)
79 {
80 continue;
81 } else {
82 anyhow::bail!(formatdoc! {"
83 Migration changed for {domain} at step {index}
84
85 Stored migration:
86 {completed_migration}
87
88 Proposed migration:
89 {migration}"});
90 }
91 }
92
93 self.eager_exec(&migration)?;
94 store_completed_migration((domain, index, migration))?;
95 }
96
97 Ok(())
98 })
99 }
100}
101
102#[cfg(test)]
103mod test {
104 use indoc::indoc;
105
106 use crate::connection::Connection;
107
108 #[test]
109 fn test_migrations_are_added_to_table() {
110 let connection = Connection::open_memory(Some("migrations_are_added_to_table"));
111
112 // Create first migration with a single step and run it
113 connection
114 .migrate(
115 "test",
116 &[indoc! {"
117 CREATE TABLE test1 (
118 a TEXT,
119 b TEXT
120 )"}],
121 disallow_migration_change,
122 )
123 .unwrap();
124
125 // Verify it got added to the migrations table
126 assert_eq!(
127 &connection
128 .select::<String>("SELECT (migration) FROM migrations")
129 .unwrap()()
130 .unwrap()[..],
131 &[indoc! {"CREATE TABLE test1 (a TEXT, b TEXT)"}],
132 );
133
134 // Add another step to the migration and run it again
135 connection
136 .migrate(
137 "test",
138 &[
139 indoc! {"
140 CREATE TABLE test1 (
141 a TEXT,
142 b TEXT
143 )"},
144 indoc! {"
145 CREATE TABLE test2 (
146 c TEXT,
147 d TEXT
148 )"},
149 ],
150 disallow_migration_change,
151 )
152 .unwrap();
153
154 // Verify it is also added to the migrations table
155 assert_eq!(
156 &connection
157 .select::<String>("SELECT (migration) FROM migrations")
158 .unwrap()()
159 .unwrap()[..],
160 &[
161 indoc! {"CREATE TABLE test1 (a TEXT, b TEXT)"},
162 indoc! {"CREATE TABLE test2 (c TEXT, d TEXT)"},
163 ],
164 );
165 }
166
167 #[test]
168 fn test_migration_setup_works() {
169 let connection = Connection::open_memory(Some("migration_setup_works"));
170
171 connection
172 .exec(indoc! {"
173 CREATE TABLE IF NOT EXISTS migrations (
174 domain TEXT,
175 step INTEGER,
176 migration TEXT
177 );"})
178 .unwrap()()
179 .unwrap();
180
181 let mut store_completed_migration = connection
182 .exec_bound::<(&str, usize, String)>(indoc! {"
183 INSERT INTO migrations (domain, step, migration)
184 VALUES (?, ?, ?)"})
185 .unwrap();
186
187 let domain = "test_domain";
188 for i in 0..5 {
189 // Create a table forcing a schema change
190 connection
191 .exec(&format!("CREATE TABLE table{} ( test TEXT );", i))
192 .unwrap()()
193 .unwrap();
194
195 store_completed_migration((domain, i, i.to_string())).unwrap();
196 }
197 }
198
199 #[test]
200 fn migrations_dont_rerun() {
201 let connection = Connection::open_memory(Some("migrations_dont_rerun"));
202
203 // Create migration which clears a table
204
205 // Manually create the table for that migration with a row
206 connection
207 .exec(indoc! {"
208 CREATE TABLE test_table (
209 test_column INTEGER
210 );"})
211 .unwrap()()
212 .unwrap();
213 connection
214 .exec(indoc! {"
215 INSERT INTO test_table (test_column) VALUES (1);"})
216 .unwrap()()
217 .unwrap();
218
219 assert_eq!(
220 connection
221 .select_row::<usize>("SELECT * FROM test_table")
222 .unwrap()()
223 .unwrap(),
224 Some(1)
225 );
226
227 // Run the migration verifying that the row got dropped
228 connection
229 .migrate(
230 "test",
231 &["DELETE FROM test_table"],
232 disallow_migration_change,
233 )
234 .unwrap();
235 assert_eq!(
236 connection
237 .select_row::<usize>("SELECT * FROM test_table")
238 .unwrap()()
239 .unwrap(),
240 None
241 );
242
243 // Recreate the dropped row
244 connection
245 .exec("INSERT INTO test_table (test_column) VALUES (2)")
246 .unwrap()()
247 .unwrap();
248
249 // Run the same migration again and verify that the table was left unchanged
250 connection
251 .migrate(
252 "test",
253 &["DELETE FROM test_table"],
254 disallow_migration_change,
255 )
256 .unwrap();
257 assert_eq!(
258 connection
259 .select_row::<usize>("SELECT * FROM test_table")
260 .unwrap()()
261 .unwrap(),
262 Some(2)
263 );
264 }
265
266 #[test]
267 fn changed_migration_fails() {
268 let connection = Connection::open_memory(Some("changed_migration_fails"));
269
270 // Create a migration with two steps and run it
271 connection
272 .migrate(
273 "test migration",
274 &[
275 "CREATE TABLE test (col INTEGER)",
276 "INSERT INTO test (col) VALUES (1)",
277 ],
278 disallow_migration_change,
279 )
280 .unwrap();
281
282 let mut migration_changed = false;
283
284 // Create another migration with the same domain but different steps
285 let second_migration_result = connection.migrate(
286 "test migration",
287 &[
288 "CREATE TABLE test (color INTEGER )",
289 "INSERT INTO test (color) VALUES (1)",
290 ],
291 |_, old, new| {
292 assert_eq!(old, "CREATE TABLE test (col INTEGER)");
293 assert_eq!(new, "CREATE TABLE test (color INTEGER)");
294 migration_changed = true;
295 false
296 },
297 );
298
299 // Verify new migration returns error when run
300 assert!(second_migration_result.is_err())
301 }
302
303 #[test]
304 fn test_create_alter_drop() {
305 let connection = Connection::open_memory(Some("test_create_alter_drop"));
306
307 connection
308 .migrate(
309 "first_migration",
310 &["CREATE TABLE table1(a TEXT) STRICT;"],
311 disallow_migration_change,
312 )
313 .unwrap();
314
315 connection
316 .exec("INSERT INTO table1(a) VALUES (\"test text\");")
317 .unwrap()()
318 .unwrap();
319
320 connection
321 .migrate(
322 "second_migration",
323 &[indoc! {"
324 CREATE TABLE table2(b TEXT) STRICT;
325
326 INSERT INTO table2 (b)
327 SELECT a FROM table1;
328
329 DROP TABLE table1;
330
331 ALTER TABLE table2 RENAME TO table1;
332 "}],
333 disallow_migration_change,
334 )
335 .unwrap();
336
337 let res = &connection.select::<String>("SELECT b FROM table1").unwrap()().unwrap()[0];
338
339 assert_eq!(res, "test text");
340 }
341
342 fn disallow_migration_change(_: usize, _: &str, _: &str) -> bool {
343 false
344 }
345}