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