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