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