1use crate::{
3 Migration, MigrationGraph, MigrationId, MigrationOperation, ProjectState, SqliteSchemaEditor,
4 rusqlite::OptionalExtension,
5};
6use std::collections::{BTreeSet, HashMap};
7use std::time::Duration;
8use time::{OffsetDateTime, format_description::well_known::Rfc3339};
9
10pub fn apply_operation_migrations_on_connection(
11 connection: &mut crate::rusqlite::Connection,
12 graph: MigrationGraph,
13) -> Result<usize, Box<dyn std::error::Error + Send + Sync>> {
14 apply_operation_migrations_to_on_connection(connection, graph, None)
15}
16
17pub fn apply_operation_migrations_to_on_connection(
20 connection: &mut crate::rusqlite::Connection,
21 graph: MigrationGraph,
22 target: Option<MigrationId>,
23) -> Result<usize, Box<dyn std::error::Error + Send + Sync>> {
24 graph.validate_heads()?;
25 graph.plan_forward(&BTreeSet::new(), target.as_ref())?;
29 let mut applied_count = 0;
30 loop {
31 let foreign_keys: bool =
32 connection.query_row("PRAGMA foreign_keys", [], |row| row.get(0))?;
33 connection.pragma_update(None, "foreign_keys", false)?;
34 if let Err(error) = begin_immediate(connection, Duration::from_secs(5)) {
35 connection.pragma_update(None, "foreign_keys", foreign_keys)?;
36 return Err(error);
37 }
38 let result = (|| {
39 let applied = applied_operation_migrations(connection)?;
43 let pending = crate::migration::pending_operation_migrations(&graph, &applied)?;
44 let applied_ids = applied
45 .keys()
46 .map(|(app, name)| MigrationId {
47 app: app.clone(),
48 name: name.clone(),
49 })
50 .collect::<BTreeSet<_>>();
51 let planned_ids = graph
52 .plan_forward(&applied_ids, target.as_ref())?
53 .into_iter()
54 .map(|step| step.id)
55 .collect::<BTreeSet<_>>();
56 let Some(migration) = pending
57 .into_iter()
58 .find(|migration| planned_ids.contains(&migration.id))
59 else {
60 return Ok(None);
61 };
62 connection.execute_batch(OPERATION_MIGRATION_HISTORY_SQL)?;
63 let mut migration_state = graph
64 .replay_selected(ProjectState::default(), &applied_ids)
65 .map_err(|error| format!("cannot reconstruct migration state: {error}"))?;
66 apply_single_operation_migration(connection, migration, &mut migration_state)?;
67 Ok(Some(()))
68 })();
69 let result = result.and_then(|applied| {
70 connection.execute_batch("COMMIT")?;
71 Ok(applied)
72 });
73 if !connection.is_autocommit() {
74 connection.execute_batch("ROLLBACK")?;
75 }
76 connection.pragma_update(None, "foreign_keys", foreign_keys)?;
77 match result {
78 Ok(Some(())) => applied_count += 1,
79 Ok(None) => return Ok(applied_count),
80 Err(error) => return Err(error),
81 }
82 }
83}
84
85fn begin_immediate(
86 connection: &mut crate::rusqlite::Connection,
87 timeout: Duration,
88) -> Result<(), Box<dyn std::error::Error + Send + Sync>> {
89 connection.busy_timeout(timeout)?;
90 match connection.execute_batch("BEGIN IMMEDIATE") {
91 Ok(()) => Ok(()),
92 Err(crate::rusqlite::Error::SqliteFailure(error, _))
93 if matches!(
94 error.code,
95 crate::rusqlite::ErrorCode::DatabaseBusy
96 | crate::rusqlite::ErrorCode::DatabaseLocked
97 ) =>
98 {
99 Err(format!(
100 "database is locked; could not acquire the migration write lock within {} ms",
101 timeout.as_millis()
102 )
103 .into())
104 }
105 Err(error) => Err(error.into()),
106 }
107}
108
109fn apply_single_operation_migration(
110 connection: &mut crate::rusqlite::Connection,
111 migration: &Migration,
112 migration_state: &mut ProjectState,
113) -> Result<(), Box<dyn std::error::Error + Send + Sync>> {
114 for operation in &migration.operations {
115 match operation {
116 MigrationOperation::RunSql { forward, .. } => {
117 use crate::rusqlite::hooks::{AuthAction, AuthContext, Authorization};
118 connection.authorizer(Some(|context: AuthContext<'_>| match context.action {
119 AuthAction::Transaction { .. }
120 | AuthAction::Savepoint { .. }
121 | AuthAction::Attach { .. }
122 | AuthAction::Detach { .. } => Authorization::Deny,
123 _ => Authorization::Allow,
124 }))?;
125 let result = connection.execute_batch(forward);
126 let reset = connection.authorizer(None::<fn(AuthContext<'_>) -> Authorization>);
127 result?;
128 reset?;
129 operation.state_forwards(migration_state)?;
130 }
131 _ => {
132 if let Some(table) = SqliteSchemaEditor::rebuild_table_name(operation) {
133 ensure_rebuild_can_preserve_rows(connection, operation)?;
134 assert_rebuild_objects_match_history(connection, table, migration_state)?;
135 }
136 let plan = SqliteSchemaEditor::plan(operation, migration_state)?;
137 let normalizing = matches!(
138 operation,
139 MigrationOperation::NormalizeSqliteDateTimes { .. }
140 );
141 if normalizing {
142 use crate::rusqlite::functions::FunctionFlags;
143 connection.create_scalar_function(
144 "che_datetime_utc",
145 1,
146 FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
147 |context| {
148 let value: Option<String> = context.get(0)?;
149 value
150 .map(|value| {
151 crate::datetime::normalize_sqlite_datetime(&value).map_err(
152 |error| {
153 crate::rusqlite::Error::UserFunctionError(Box::new(
154 error,
155 ))
156 },
157 )
158 })
159 .transpose()
160 },
161 )?;
162 }
163 let result = plan
164 .statements
165 .iter()
166 .try_for_each(|statement| connection.execute_batch(statement));
167 if normalizing {
168 connection.remove_function("che_datetime_utc", 1)?;
169 }
170 result?;
171 *migration_state = plan.resulting_state;
172 }
173 }
174 }
175 connection.execute(
176 "INSERT INTO che_migration_history (app, name, checksum, applied_on) VALUES (?1, ?2, ?3, ?4)",
177 crate::rusqlite::params![
178 migration.id.app,
179 migration.id.name,
180 migration.checksum,
181 OffsetDateTime::now_utc().format(&Rfc3339)?,
182 ],
183 )?;
184 assert_foreign_keys_valid(connection)?;
185 Ok(())
186}
187
188fn ensure_rebuild_can_preserve_rows(
189 connection: &crate::rusqlite::Connection,
190 operation: &MigrationOperation,
191) -> Result<(), Box<dyn std::error::Error + Send + Sync>> {
192 match operation {
193 MigrationOperation::AddColumn { table, column }
194 if !column.nullable
195 && !column.primary_key
196 && column.default.is_none()
197 && !column.auto_now_add
198 && !column.auto_now =>
199 {
200 let table = quote_identifier(table);
201 let has_rows: bool = connection.query_row(
202 &format!("SELECT EXISTS(SELECT 1 FROM {table} LIMIT 1)"),
203 [],
204 |row| row.get(0),
205 )?;
206 if has_rows {
207 return Err(format!(
208 "cannot add required column {} without a value for existing rows; add a default or use an explicit nullable → backfill → non-null migration",
209 column.name
210 )
211 .into());
212 }
213 }
214 MigrationOperation::AlterColumn {
215 table,
216 old_column,
217 new_column,
218 } if old_column.nullable && !new_column.nullable => {
219 let has_nulls: bool = connection.query_row(
220 &format!(
221 "SELECT EXISTS(SELECT 1 FROM {} WHERE {} IS NULL LIMIT 1)",
222 quote_identifier(table),
223 quote_identifier(&new_column.name)
224 ),
225 [],
226 |row| row.get(0),
227 )?;
228 if has_nulls {
229 return Err(format!(
230 "cannot make {}.{} non-null while existing rows contain NULL; backfill values before this migration",
231 table, new_column.name
232 )
233 .into());
234 }
235 }
236 MigrationOperation::AddUniqueConstraint { table, columns } => {
237 ensure_unique_values(connection, table, columns)?;
238 }
239 MigrationOperation::AlterColumn {
240 table,
241 old_column,
242 new_column,
243 } if !old_column.unique && new_column.unique => {
244 ensure_unique_values(connection, table, std::slice::from_ref(&new_column.name))?;
245 }
246 _ => {}
247 }
248 Ok(())
249}
250
251fn ensure_unique_values(
252 connection: &crate::rusqlite::Connection,
253 table: &str,
254 columns: &[String],
255) -> Result<(), Box<dyn std::error::Error + Send + Sync>> {
256 let columns_sql = columns
257 .iter()
258 .map(|column| quote_identifier(column))
259 .collect::<Vec<_>>()
260 .join(", ");
261 let non_null = columns
262 .iter()
263 .map(|column| format!("{} IS NOT NULL", quote_identifier(column)))
264 .collect::<Vec<_>>()
265 .join(" AND ");
266 let duplicates: bool = connection.query_row(
267 &format!(
268 "SELECT EXISTS(SELECT 1 FROM {} WHERE {non_null} GROUP BY {columns_sql} HAVING count(*) > 1 LIMIT 1)",
269 quote_identifier(table)
270 ),
271 [],
272 |row| row.get(0),
273 )?;
274 if duplicates {
275 return Err(format!(
276 "cannot add unique constraint on {}({}); existing rows contain duplicates",
277 table,
278 columns.join(", ")
279 )
280 .into());
281 }
282 Ok(())
283}
284
285fn quote_identifier(identifier: &str) -> String {
286 format!("\"{}\"", identifier.replace('"', "\"\""))
287}
288
289fn assert_rebuild_objects_match_history(
290 connection: &crate::rusqlite::Connection,
291 table: &str,
292 migration_state: &ProjectState,
293) -> Result<(), Box<dyn std::error::Error + Send + Sync>> {
294 let table_sql: Option<String> = connection
295 .query_row(
296 "SELECT sql FROM sqlite_master WHERE type = 'table' AND name = ?1",
297 [table],
298 |row| row.get(0),
299 )
300 .optional()?;
301 if table_sql
302 .as_deref()
303 .is_some_and(|sql| sql.to_ascii_uppercase().contains("AUTOINCREMENT"))
304 {
305 return Err(format!(
306 "cannot rebuild {table}: AUTOINCREMENT is not represented in historical state; use explicit RunSql that preserves sqlite_sequence"
307 )
308 .into());
309 }
310 let expected = migration_state
313 .tables()
314 .iter()
315 .find(|item| item.name == table)
316 .ok_or_else(|| format!("missing historical table {table}"))?;
317 let expected_unique = expected
318 .columns
319 .iter()
320 .filter(|column| column.unique)
321 .map(|column| vec![column.name.clone()])
322 .chain(expected.unique_constraints.iter().cloned())
323 .collect::<Vec<_>>();
324 let mut unmatched_unique = expected_unique.clone();
325 let expected_primary_key = expected
326 .columns
327 .iter()
328 .filter(|column| column.primary_key)
329 .map(|column| column.name.clone())
330 .collect::<Vec<_>>();
331 let mut unmatched_indexes = expected.indexes.clone();
332 let mut indexes = connection
333 .prepare("SELECT name, \"unique\", origin, partial FROM pragma_index_list(?1)")?;
334 let indexes = indexes
335 .query_map([table], |row| {
336 Ok((
337 row.get::<_, String>(0)?,
338 row.get::<_, bool>(1)?,
339 row.get::<_, String>(2)?,
340 row.get::<_, bool>(3)?,
341 ))
342 })?
343 .collect::<Result<Vec<_>, _>>()?;
344 for (name, unique, origin, partial) in indexes {
345 let info = sqlite_index_columns(connection, &name)?;
346 match origin.as_str() {
347 "c" => {
348 if partial
349 || info
350 .0
351 .iter()
352 .any(|(_, desc, coll)| *desc || coll != "BINARY")
353 {
354 return Err(format!("cannot rebuild {table}: divergent index {name}").into());
355 }
356 let unmatched = if unique {
357 &mut unmatched_unique
358 } else {
359 &mut unmatched_indexes
360 };
361 let Some(position) = unmatched.iter().position(|columns| *columns == info.1) else {
362 return Err(format!(
363 "cannot rebuild {table}: unmanaged index {name}; use explicit RunSql"
364 )
365 .into());
366 };
367 unmatched.remove(position);
368 }
369 "u" if unique && !partial => {
370 let Some(position) = unmatched_unique
371 .iter()
372 .position(|columns| *columns == info.1)
373 else {
374 return Err(format!(
375 "cannot rebuild {table}: unmanaged or divergent index {name}; use explicit RunSql"
376 )
377 .into());
378 };
379 unmatched_unique.remove(position);
380 }
381 "pk" if !partial && info.1 == expected_primary_key => {}
382 _ => {
383 return Err(format!(
384 "cannot rebuild {table}: unmanaged or divergent index {name}; use explicit RunSql"
385 )
386 .into());
387 }
388 }
389 }
390 if !unmatched_indexes.is_empty() {
391 return Err(format!(
392 "cannot rebuild {table}: historical index(es) {:?} are missing from SQLite",
393 unmatched_indexes
394 )
395 .into());
396 }
397 if !unmatched_unique.is_empty() {
398 return Err(format!(
399 "cannot rebuild {table}: historical unique constraint(s) {:?} are missing from SQLite",
400 unmatched_unique
401 )
402 .into());
403 }
404 let dependents = sqlite_opaque_dependents(connection, table)?;
405 if !dependents.is_empty() {
406 return Err(format!(
407 "cannot rebuild table {table}; SQLite object(s) {} may depend on it. Drop and recreate the matching declared schema objects around the table operation",
408 dependents.join(", ")
409 )
410 .into());
411 }
412 Ok(())
413}
414
415fn sqlite_index_columns(
416 connection: &crate::rusqlite::Connection,
417 name: &str,
418) -> Result<(Vec<(String, bool, String)>, Vec<String>), crate::rusqlite::Error> {
419 let mut statement = connection.prepare(
420 "SELECT name, desc, coll FROM pragma_index_xinfo(?1) WHERE key = 1 ORDER BY seqno",
421 )?;
422 let details = statement
423 .query_map([name], |row| Ok((row.get(0)?, row.get(1)?, row.get(2)?)))?
424 .collect::<Result<Vec<(String, bool, String)>, _>>()?;
425 let columns = details.iter().map(|(name, _, _)| name.clone()).collect();
426 Ok((details, columns))
427}
428
429fn sqlite_opaque_dependents(
430 connection: &crate::rusqlite::Connection,
431 table: &str,
432) -> Result<Vec<String>, crate::rusqlite::Error> {
433 let table = table.to_ascii_lowercase();
434 let mut statement = connection.prepare(
435 "SELECT type, name, tbl_name, sql FROM sqlite_master WHERE type IN ('trigger', 'view') AND sql IS NOT NULL ORDER BY type, name",
436 )?;
437 statement
438 .query_map([], |row| {
439 Ok((
440 row.get::<_, String>(0)?,
441 row.get::<_, String>(1)?,
442 row.get::<_, String>(2)?,
443 row.get::<_, String>(3)?,
444 ))
445 })?
446 .filter_map(|row| match row {
447 Ok((kind, name, owner, sql))
448 if owner.eq_ignore_ascii_case(&table)
449 || sql.to_ascii_lowercase().contains(&table) =>
450 {
451 Some(Ok(format!("{kind} {name}")))
452 }
453 Ok(_) => None,
454 Err(error) => Some(Err(error)),
455 })
456 .collect()
457}
458
459const OPERATION_MIGRATION_HISTORY_SQL: &str = "CREATE TABLE IF NOT EXISTS che_migration_history (app TEXT NOT NULL, name TEXT NOT NULL, checksum TEXT NOT NULL, applied_on TEXT NOT NULL, PRIMARY KEY (app, name));";
460
461pub fn applied_operation_migrations(
462 connection: &crate::rusqlite::Connection,
463) -> Result<HashMap<(String, String), String>, Box<dyn std::error::Error + Send + Sync>> {
464 let has_history = connection
465 .query_row(
466 "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = 'che_migration_history'",
467 [],
468 |_| Ok(()),
469 )
470 .optional()?;
471 if has_history.is_none() {
472 return Ok(HashMap::new());
473 }
474 let mut statement =
475 connection.prepare("SELECT app, name, checksum FROM che_migration_history")?;
476 let rows = statement.query_map([], |row| {
477 Ok((
478 (row.get::<_, String>(0)?, row.get::<_, String>(1)?),
479 row.get(2)?,
480 ))
481 })?;
482 rows.collect::<Result<HashMap<_, _>, _>>()
483 .map_err(Into::into)
484}
485
486fn assert_foreign_keys_valid(
487 connection: &crate::rusqlite::Connection,
488) -> Result<(), Box<dyn std::error::Error + Send + Sync>> {
489 let mut statement = connection
490 .prepare("PRAGMA foreign_key_check;")
491 .map_err(|error| Box::new(error) as Box<dyn std::error::Error + Send + Sync>)?;
492 let mut rows = statement
493 .query([])
494 .map_err(|error| Box::new(error) as Box<dyn std::error::Error + Send + Sync>)?;
495 if rows
496 .next()
497 .map_err(|error| Box::new(error) as Box<dyn std::error::Error + Send + Sync>)?
498 .is_some()
499 {
500 return Err("migration left foreign key violations".into());
501 }
502 Ok(())
503}
504
505#[cfg(test)]
506mod tests {
507 use super::*;
508 use crate::{
509 ColumnState, ColumnType, ForeignKeyState, MigrationId, SchemaObjectKind, SchemaObjectState,
510 TableState,
511 };
512
513 fn column(name: &str) -> ColumnState {
514 ColumnState {
515 field_name: name.into(),
516 name: name.into(),
517 column_type: ColumnType::Text,
518 nullable: false,
519 primary_key: false,
520 unique: false,
521 default: None,
522 check: None,
523 choices: None,
524 references: None,
525 auto_now_add: false,
526 auto_now: false,
527 }
528 }
529
530 fn state_with_table(columns: Vec<ColumnState>) -> ProjectState {
531 let mut state = ProjectState::default();
532 MigrationOperation::CreateTable {
533 table: TableState {
534 name: "items".into(),
535 columns,
536 unique_constraints: vec![],
537 indexes: vec![],
538 },
539 }
540 .state_forwards(&mut state)
541 .unwrap();
542 state
543 }
544
545 #[test]
546 fn declared_trigger_can_be_dropped_rebuilt_and_restored_with_its_table() {
547 let trigger = SchemaObjectState {
548 app: "items".into(),
549 kind: SchemaObjectKind::Trigger,
550 name: "items_guard".into(),
551 sql: "CREATE TRIGGER items_guard BEFORE INSERT ON items BEGIN SELECT 1; END".into(),
552 };
553 let initial = Migration::new(
554 MigrationId {
555 app: "items".into(),
556 name: "0001_initial".into(),
557 },
558 vec![],
559 vec![
560 MigrationOperation::CreateTable {
561 table: TableState {
562 name: "items".into(),
563 columns: vec![column("id")],
564 unique_constraints: vec![],
565 indexes: vec![],
566 },
567 },
568 MigrationOperation::CreateSchemaObject {
569 object: trigger.clone(),
570 },
571 ],
572 );
573 let mut title = column("title");
574 title.nullable = true;
575 let rebuild = Migration::new(
576 MigrationId {
577 app: "items".into(),
578 name: "0002_title".into(),
579 },
580 vec![initial.id.clone()],
581 vec![
582 MigrationOperation::DropSchemaObject {
583 kind: SchemaObjectKind::Trigger,
584 name: trigger.name.clone(),
585 },
586 MigrationOperation::AddColumn {
587 table: "items".into(),
588 column: title,
589 },
590 MigrationOperation::CreateSchemaObject { object: trigger },
591 ],
592 );
593 let mut connection = crate::rusqlite::Connection::open_in_memory().unwrap();
594 apply_operation_migrations_on_connection(
595 &mut connection,
596 MigrationGraph::new(vec![initial.clone()]).unwrap(),
597 )
598 .unwrap();
599 connection
600 .execute("INSERT INTO items(id) VALUES ('one')", [])
601 .unwrap();
602 apply_operation_migrations_on_connection(
603 &mut connection,
604 MigrationGraph::new(vec![initial, rebuild]).unwrap(),
605 )
606 .unwrap();
607 let row: (String, Option<String>) = connection
608 .query_row("SELECT id, title FROM items", [], |row| {
609 Ok((row.get(0)?, row.get(1)?))
610 })
611 .unwrap();
612 assert_eq!(row, ("one".into(), None));
613 let exists: bool = connection
614 .query_row(
615 "SELECT EXISTS(SELECT 1 FROM sqlite_master WHERE type = 'trigger' AND name = 'items_guard')",
616 [],
617 |row| row.get(0),
618 )
619 .unwrap();
620 assert!(exists);
621 }
622
623 #[test]
624 fn schema_object_operations_create_and_drop_sqlite_objects() {
625 let create = Migration::new(
626 MigrationId {
627 app: "items".into(),
628 name: "0001_create_view".into(),
629 },
630 vec![],
631 vec![MigrationOperation::CreateSchemaObject {
632 object: SchemaObjectState {
633 app: "items".into(),
634 kind: SchemaObjectKind::View,
635 name: "item_view".into(),
636 sql: "CREATE VIEW item_view AS SELECT 1 AS id".into(),
637 },
638 }],
639 );
640 let drop = Migration::new(
641 MigrationId {
642 app: "items".into(),
643 name: "0002_drop_view".into(),
644 },
645 vec![create.id.clone()],
646 vec![MigrationOperation::DropSchemaObject {
647 kind: SchemaObjectKind::View,
648 name: "item_view".into(),
649 }],
650 );
651 let mut connection = crate::rusqlite::Connection::open_in_memory().unwrap();
652 apply_operation_migrations_to_on_connection(
653 &mut connection,
654 MigrationGraph::new(vec![create.clone(), drop.clone()]).unwrap(),
655 Some(create.id.clone()),
656 )
657 .unwrap();
658 assert_eq!(
659 connection
660 .query_row("SELECT id FROM item_view", [], |row| row.get::<_, i64>(0))
661 .unwrap(),
662 1
663 );
664 apply_operation_migrations_on_connection(
665 &mut connection,
666 MigrationGraph::new(vec![create, drop]).unwrap(),
667 )
668 .unwrap();
669 let exists: Option<String> = connection
670 .query_row(
671 "SELECT name FROM sqlite_master WHERE type = 'view' AND name = 'item_view'",
672 [],
673 |row| row.get(0),
674 )
675 .optional()
676 .unwrap();
677 assert!(exists.is_none());
678 }
679
680 #[test]
681 fn rebuild_rejects_unmanaged_auto_unique_indexes() {
682 let connection = crate::rusqlite::Connection::open_in_memory().unwrap();
683 connection
684 .execute_batch(
685 "CREATE TABLE items (id TEXT NOT NULL, obsolete TEXT NOT NULL, UNIQUE(obsolete))",
686 )
687 .unwrap();
688 let error = assert_rebuild_objects_match_history(
689 &connection,
690 "items",
691 &state_with_table(vec![column("id"), column("obsolete")]),
692 )
693 .unwrap_err();
694 assert!(error.to_string().contains("unmanaged or divergent index"));
695 }
696
697 #[test]
698 fn rebuild_accepts_historical_unique_constraints() {
699 let connection = crate::rusqlite::Connection::open_in_memory().unwrap();
700 connection
701 .execute_batch("CREATE TABLE items (id TEXT NOT NULL, UNIQUE(id))")
702 .unwrap();
703 let mut id = column("id");
704 id.unique = true;
705 assert!(assert_rebuild_objects_match_history(
706 &connection,
707 "items",
708 &state_with_table(vec![id]),
709 )
710 .is_ok());
711 }
712
713 #[test]
714 fn consecutive_index_changes_preserve_rows_and_match_history() {
715 for add_after_removal in [false, true] {
716 let table = TableState {
717 name: "items".into(),
718 columns: vec![column("a"), column("b"), column("c")],
719 unique_constraints: vec![],
720 indexes: vec![vec!["a".into()], vec!["b".into()], vec!["c".into()]],
721 };
722 let initial = Migration::new(
723 MigrationId {
724 app: "items".into(),
725 name: "0001".into(),
726 },
727 vec![],
728 vec![MigrationOperation::CreateTable { table }],
729 );
730 let mut db = crate::rusqlite::Connection::open_in_memory().unwrap();
731 apply_operation_migrations_on_connection(
732 &mut db,
733 MigrationGraph::new(vec![initial.clone()]).unwrap(),
734 )
735 .unwrap();
736 db.execute("INSERT INTO items VALUES ('A','B','C')", [])
737 .unwrap();
738 db.execute_batch("DROP INDEX items_idx_0; CREATE INDEX legacy_a ON items(a)")
740 .unwrap();
741 let last = if add_after_removal {
742 MigrationOperation::AddIndex {
743 table: "items".into(),
744 columns: vec!["a".into(), "b".into()],
745 }
746 } else {
747 MigrationOperation::RemoveIndex {
748 table: "items".into(),
749 columns: vec!["c".into()],
750 }
751 };
752 let next = Migration::new(
753 MigrationId {
754 app: "items".into(),
755 name: "0002".into(),
756 },
757 vec![initial.id.clone()],
758 vec![
759 MigrationOperation::RemoveIndex {
760 table: "items".into(),
761 columns: vec!["a".into()],
762 },
763 last,
764 ],
765 );
766 let graph = MigrationGraph::new(vec![initial, next]).unwrap();
767 apply_operation_migrations_on_connection(&mut db, graph.clone()).unwrap();
768 assert_rebuild_objects_match_history(
769 &db,
770 "items",
771 &graph.replay(Default::default()).unwrap(),
772 )
773 .unwrap();
774 let row: (String, String, String) = db
775 .query_row("SELECT a,b,c FROM items", [], |row| {
776 Ok((row.get(0)?, row.get(1)?, row.get(2)?))
777 })
778 .unwrap();
779 assert_eq!(row, ("A".into(), "B".into(), "C".into()));
780 assert_eq!(
781 apply_operation_migrations_on_connection(&mut db, graph).unwrap(),
782 0
783 );
784 }
785 }
786
787 #[test]
788 fn rebuild_accepts_historical_non_unique_index_names() {
789 let connection = crate::rusqlite::Connection::open_in_memory().unwrap();
790 connection
791 .execute_batch(
792 "CREATE TABLE items (id TEXT NOT NULL); \
793 CREATE INDEX legacy_items_id_idx ON items(id)",
794 )
795 .unwrap();
796 let mut state = ProjectState::default();
797 MigrationOperation::CreateTable {
798 table: TableState {
799 name: "items".into(),
800 columns: vec![column("id")],
801 unique_constraints: vec![],
802 indexes: vec![vec!["id".into()]],
803 },
804 }
805 .state_forwards(&mut state)
806 .unwrap();
807 assert!(assert_rebuild_objects_match_history(&connection, "items", &state).is_ok());
808 }
809
810 #[test]
811 fn rebuild_rejects_views_and_external_triggers_that_reference_the_table() {
812 let connection = crate::rusqlite::Connection::open_in_memory().unwrap();
813 connection
814 .execute_batch(
815 "CREATE TABLE items (id TEXT NOT NULL); \
816 CREATE TABLE audit (item_id TEXT); \
817 CREATE VIEW item_view AS SELECT id FROM items; \
818 CREATE TRIGGER audit_items AFTER INSERT ON audit BEGIN SELECT id FROM items; END;",
819 )
820 .unwrap();
821 let error = assert_rebuild_objects_match_history(
822 &connection,
823 "items",
824 &state_with_table(vec![column("id")]),
825 )
826 .unwrap_err();
827 let message = error.to_string();
828 assert!(message.contains("view item_view"));
829 assert!(message.contains("trigger audit_items"));
830 }
831
832 #[test]
833 fn rebuild_rejects_required_column_without_a_value_for_existing_rows() {
834 let connection = crate::rusqlite::Connection::open_in_memory().unwrap();
835 connection
836 .execute_batch("CREATE TABLE items (id TEXT NOT NULL); INSERT INTO items VALUES ('1')")
837 .unwrap();
838 let mut required = column("title");
839 required.nullable = false;
840 let error = ensure_rebuild_can_preserve_rows(
841 &connection,
842 &MigrationOperation::AddColumn {
843 table: "items".into(),
844 column: required,
845 },
846 )
847 .unwrap_err();
848 assert!(error.to_string().contains("nullable → backfill → non-null"));
849 }
850
851 #[test]
852 fn rebuild_rejects_nulls_and_duplicates_before_new_constraints() {
853 let connection = crate::rusqlite::Connection::open_in_memory().unwrap();
854 connection
855 .execute_batch("CREATE TABLE items (id TEXT, email TEXT); INSERT INTO items VALUES (NULL, 'same'), (NULL, 'same')")
856 .unwrap();
857 let mut old_id = column("id");
858 old_id.nullable = true;
859 let error = ensure_rebuild_can_preserve_rows(
860 &connection,
861 &MigrationOperation::AlterColumn {
862 table: "items".into(),
863 old_column: old_id.clone(),
864 new_column: column("id"),
865 },
866 )
867 .unwrap_err();
868 assert!(error.to_string().contains("contain NULL"));
869 let error = ensure_rebuild_can_preserve_rows(
870 &connection,
871 &MigrationOperation::AddUniqueConstraint {
872 table: "items".into(),
873 columns: vec!["email".into()],
874 },
875 )
876 .unwrap_err();
877 assert!(error.to_string().contains("contain duplicates"));
878 }
879
880 #[test]
881 fn backfill_allows_nullable_to_non_null_without_a_schema_default() {
882 let mut title = column("title");
883 title.nullable = true;
884 let initial = Migration::new(
885 MigrationId {
886 app: "items".into(),
887 name: "0001".into(),
888 },
889 vec![],
890 vec![MigrationOperation::CreateTable {
891 table: TableState {
892 name: "items".into(),
893 columns: vec![column("id"), title.clone()],
894 unique_constraints: vec![],
895 indexes: vec![],
896 },
897 }],
898 );
899 let mut required = title.clone();
900 required.nullable = false;
901 let follow_up = Migration::new(
902 MigrationId {
903 app: "items".into(),
904 name: "0002_title_required".into(),
905 },
906 vec![initial.id.clone()],
907 vec![
908 MigrationOperation::BackfillColumn {
909 table: "items".into(),
910 column: "title".into(),
911 value: "'untitled'".into(),
912 },
913 MigrationOperation::AlterColumn {
914 table: "items".into(),
915 old_column: title,
916 new_column: required,
917 },
918 ],
919 );
920 let graph = MigrationGraph::new(vec![initial, follow_up]).unwrap();
921 let mut connection = crate::rusqlite::Connection::open_in_memory().unwrap();
922 apply_operation_migrations_on_connection(
923 &mut connection,
924 MigrationGraph::new(vec![graph.ordered().next().unwrap().clone()]).unwrap(),
925 )
926 .unwrap();
927 connection
928 .execute("INSERT INTO items(id, title) VALUES ('1', NULL)", [])
929 .unwrap();
930 assert_eq!(
931 apply_operation_migrations_on_connection(&mut connection, graph).unwrap(),
932 1
933 );
934 assert_eq!(
935 connection
936 .query_row("SELECT title FROM items WHERE id='1'", [], |row| row
937 .get::<_, String>(0))
938 .unwrap(),
939 "untitled"
940 );
941 assert!(
942 connection
943 .execute("INSERT INTO items(id, title) VALUES ('2', NULL)", [])
944 .is_err()
945 );
946 assert_eq!(
947 connection
948 .query_row(
949 "SELECT dflt_value FROM pragma_table_info('items') WHERE name='title'",
950 [],
951 |row| row.get::<_, Option<String>>(0)
952 )
953 .unwrap(),
954 None
955 );
956 }
957
958 #[test]
959 fn rebuild_keeps_integer_primary_key_allocation_and_rejects_unmodeled_autoincrement() {
960 let mut id = column("id");
961 id.column_type = ColumnType::Integer;
962 id.primary_key = true;
963 let title = column("title");
964 let initial = Migration::new(
965 MigrationId {
966 app: "items".into(),
967 name: "0001".into(),
968 },
969 vec![],
970 vec![MigrationOperation::CreateTable {
971 table: TableState {
972 name: "items".into(),
973 columns: vec![id.clone(), title.clone()],
974 unique_constraints: vec![],
975 indexes: vec![],
976 },
977 }],
978 );
979 let mut new_title = title.clone();
980 new_title.default = Some("'untitled'".into());
981 let rebuild = Migration::new(
982 MigrationId {
983 app: "items".into(),
984 name: "0002_title_default".into(),
985 },
986 vec![initial.id.clone()],
987 vec![MigrationOperation::AlterColumn {
988 table: "items".into(),
989 old_column: title,
990 new_column: new_title,
991 }],
992 );
993 let mut connection = crate::rusqlite::Connection::open_in_memory().unwrap();
994 apply_operation_migrations_on_connection(
995 &mut connection,
996 MigrationGraph::new(vec![initial.clone()]).unwrap(),
997 )
998 .unwrap();
999 connection
1000 .execute("INSERT INTO items(id, title) VALUES (5, 'existing')", [])
1001 .unwrap();
1002 apply_operation_migrations_on_connection(
1003 &mut connection,
1004 MigrationGraph::new(vec![initial, rebuild]).unwrap(),
1005 )
1006 .unwrap();
1007 connection
1008 .execute("INSERT INTO items(title) VALUES ('next')", [])
1009 .unwrap();
1010 assert_eq!(
1011 connection
1012 .query_row("SELECT id FROM items WHERE title = 'next'", [], |row| row
1013 .get::<_, i64>(
1014 0
1015 ))
1016 .unwrap(),
1017 6
1018 );
1019
1020 connection
1021 .execute_batch(
1022 "CREATE TABLE sequence_items(id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT)",
1023 )
1024 .unwrap();
1025 let error = assert_rebuild_objects_match_history(
1026 &connection,
1027 "sequence_items",
1028 &state_with_table(vec![id, column("title")]),
1029 )
1030 .unwrap_err();
1031 assert!(error.to_string().contains("AUTOINCREMENT"));
1032 }
1033
1034 #[test]
1035 fn rebuild_preserves_composite_constraints_indexes_and_foreign_keys() {
1036 let mut id = column("id");
1037 id.column_type = ColumnType::Integer;
1038 id.primary_key = true;
1039 let mut parent_id = id.clone();
1040 parent_id.name = "parent_id".into();
1041 parent_id.field_name = "parent_id".into();
1042 parent_id.primary_key = false;
1043 parent_id.references = Some(ForeignKeyState {
1044 target: "parents(id)".into(),
1045 on_delete: Some("cascade".into()),
1046 });
1047 let code = column("code");
1048 let description = column("description");
1049 let initial = Migration::new(
1050 MigrationId {
1051 app: "items".into(),
1052 name: "0001".into(),
1053 },
1054 vec![],
1055 vec![
1056 MigrationOperation::CreateTable {
1057 table: TableState {
1058 name: "parents".into(),
1059 columns: vec![id.clone()],
1060 unique_constraints: vec![],
1061 indexes: vec![],
1062 },
1063 },
1064 MigrationOperation::CreateTable {
1065 table: TableState {
1066 name: "items".into(),
1067 columns: vec![id.clone(), parent_id, code.clone(), description.clone()],
1068 unique_constraints: vec![vec!["parent_id".into(), "code".into()]],
1069 indexes: vec![vec!["code".into(), "description".into()]],
1070 },
1071 },
1072 ],
1073 );
1074 let mut new_description = description.clone();
1075 new_description.default = Some("'unknown'".into());
1076 let rebuild = Migration::new(
1077 MigrationId {
1078 app: "items".into(),
1079 name: "0002_description_default".into(),
1080 },
1081 vec![initial.id.clone()],
1082 vec![MigrationOperation::AlterColumn {
1083 table: "items".into(),
1084 old_column: description,
1085 new_column: new_description,
1086 }],
1087 );
1088 let mut connection = crate::rusqlite::Connection::open_in_memory().unwrap();
1089 connection.execute_batch("PRAGMA foreign_keys=ON").unwrap();
1090 apply_operation_migrations_on_connection(
1091 &mut connection,
1092 MigrationGraph::new(vec![initial.clone()]).unwrap(),
1093 )
1094 .unwrap();
1095 connection
1096 .execute("INSERT INTO parents(id) VALUES (1)", [])
1097 .unwrap();
1098 connection
1099 .execute(
1100 "INSERT INTO items(id, parent_id, code, description) VALUES (1, 1, 'A', 'first')",
1101 [],
1102 )
1103 .unwrap();
1104 let graph = MigrationGraph::new(vec![initial, rebuild]).unwrap();
1105 apply_operation_migrations_on_connection(&mut connection, graph.clone()).unwrap();
1106 assert_rebuild_objects_match_history(
1107 &connection,
1108 "items",
1109 &graph.replay(Default::default()).unwrap(),
1110 )
1111 .unwrap();
1112 assert!(connection
1113 .execute(
1114 "INSERT INTO items(id, parent_id, code, description) VALUES (2, 1, 'A', 'duplicate')",
1115 [],
1116 )
1117 .is_err());
1118 assert!(connection
1119 .execute(
1120 "INSERT INTO items(id, parent_id, code, description) VALUES (3, 99, 'B', 'missing parent')",
1121 [],
1122 )
1123 .is_err());
1124 assert_eq!(
1125 connection
1126 .query_row("SELECT description FROM items WHERE id=1", [], |row| row
1127 .get::<_, String>(
1128 0
1129 ))
1130 .unwrap(),
1131 "first"
1132 );
1133 }
1134
1135 #[test]
1136 fn cyclic_foreign_key_tables_apply_with_their_declared_metadata() {
1137 let mut id = column("id");
1138 id.column_type = ColumnType::Integer;
1139 id.primary_key = true;
1140 let reference = |name: &str, target: &str| ColumnState {
1141 field_name: name.into(),
1142 name: name.into(),
1143 column_type: ColumnType::Integer,
1144 nullable: true,
1145 primary_key: false,
1146 unique: false,
1147 default: None,
1148 check: None,
1149 choices: None,
1150 references: Some(ForeignKeyState {
1151 target: target.into(),
1152 on_delete: Some("set null".into()),
1153 }),
1154 auto_now_add: false,
1155 auto_now: false,
1156 };
1157 let migration = Migration::new(
1158 MigrationId {
1159 app: "cycle".into(),
1160 name: "0001".into(),
1161 },
1162 vec![],
1163 vec![
1164 MigrationOperation::CreateTable {
1165 table: TableState {
1166 name: "left_items".into(),
1167 columns: vec![id.clone(), reference("right_id", "right_items(id)")],
1168 unique_constraints: vec![],
1169 indexes: vec![],
1170 },
1171 },
1172 MigrationOperation::CreateTable {
1173 table: TableState {
1174 name: "right_items".into(),
1175 columns: vec![id, reference("left_id", "left_items(id)")],
1176 unique_constraints: vec![],
1177 indexes: vec![],
1178 },
1179 },
1180 ],
1181 );
1182 let mut connection = crate::rusqlite::Connection::open_in_memory().unwrap();
1183 connection.execute_batch("PRAGMA foreign_keys=ON").unwrap();
1184 apply_operation_migrations_on_connection(
1185 &mut connection,
1186 MigrationGraph::new(vec![migration]).unwrap(),
1187 )
1188 .unwrap();
1189 let left_target: String = connection
1190 .query_row(
1191 "SELECT \"table\" FROM pragma_foreign_key_list('left_items')",
1192 [],
1193 |row| row.get(0),
1194 )
1195 .unwrap();
1196 let right_target: String = connection
1197 .query_row(
1198 "SELECT \"table\" FROM pragma_foreign_key_list('right_items')",
1199 [],
1200 |row| row.get(0),
1201 )
1202 .unwrap();
1203 assert_eq!(
1204 (left_target, right_target),
1205 ("right_items".into(), "left_items".into())
1206 );
1207 }
1208
1209 #[test]
1210 fn busy_write_lock_has_a_bounded_diagnostic_error() {
1211 let path = std::env::temp_dir().join(format!(
1212 "che_orm_lock_{}.sqlite",
1213 time::OffsetDateTime::now_utc().unix_timestamp_nanos()
1214 ));
1215 let first = crate::rusqlite::Connection::open(&path).unwrap();
1216 first.execute_batch("BEGIN EXCLUSIVE").unwrap();
1217 let mut second = crate::rusqlite::Connection::open(&path).unwrap();
1218 let error = begin_immediate(&mut second, Duration::from_millis(10)).unwrap_err();
1219 assert!(
1220 error
1221 .to_string()
1222 .contains("migration write lock within 10 ms")
1223 );
1224 first.execute_batch("ROLLBACK").unwrap();
1225 std::fs::remove_file(path).unwrap();
1226 }
1227
1228 #[test]
1229 fn transaction_commands_cannot_commit_partial_migrations() {
1230 for sql in [
1231 "CREATE TABLE partial(id); COMMIT;",
1232 "CREATE TABLE partial(id); ROLLBACK;",
1233 "CREATE TABLE partial(id); SAVEPOINT sneaky;",
1234 ] {
1235 let mut connection = crate::rusqlite::Connection::open_in_memory().unwrap();
1236 connection
1237 .pragma_update(None, "foreign_keys", true)
1238 .unwrap();
1239 let graph = MigrationGraph::new(vec![Migration::new(
1240 MigrationId {
1241 app: "test".into(),
1242 name: "0001".into(),
1243 },
1244 vec![],
1245 vec![MigrationOperation::RunSql {
1246 forward: sql.into(),
1247 reverse: None,
1248 state_operations: vec![],
1249 }],
1250 )])
1251 .unwrap();
1252 assert!(apply_operation_migrations_on_connection(&mut connection, graph).is_err());
1253 assert!(connection.is_autocommit());
1254 assert!(
1255 connection
1256 .query_row("PRAGMA foreign_keys", [], |row| row.get::<_, bool>(0))
1257 .unwrap()
1258 );
1259 assert_eq!(connection.query_row("SELECT count(*) FROM sqlite_master WHERE name IN ('partial', 'che_migration_history')", [], |row| row.get::<_, i64>(0)).unwrap(), 0);
1260 }
1261 }
1262
1263 #[test]
1264 fn trigger_begin_is_not_transaction_control_and_fk_setting_is_restored() {
1265 let mut connection = crate::rusqlite::Connection::open_in_memory().unwrap();
1266 connection
1267 .pragma_update(None, "foreign_keys", false)
1268 .unwrap();
1269 let graph = MigrationGraph::new(vec![Migration::new(
1270 MigrationId { app: "test".into(), name: "0001".into() }, vec![],
1271 vec![MigrationOperation::RunSql { forward: "CREATE TABLE items(id); CREATE TRIGGER check_item BEFORE INSERT ON items BEGIN SELECT RAISE(ABORT, 'blocked'); END;".into(), reverse: None, state_operations: vec![] }],
1272 )]).unwrap();
1273 assert_eq!(
1274 apply_operation_migrations_on_connection(&mut connection, graph.clone()).unwrap(),
1275 1
1276 );
1277 assert_eq!(
1278 apply_operation_migrations_on_connection(&mut connection, graph).unwrap(),
1279 0
1280 );
1281 assert!(
1282 !connection
1283 .query_row("PRAGMA foreign_keys", [], |row| row.get::<_, bool>(0))
1284 .unwrap()
1285 );
1286 assert!(
1287 connection
1288 .execute("INSERT INTO items VALUES(1)", [])
1289 .is_err()
1290 );
1291 }
1292
1293 #[test]
1294 fn target_applies_only_its_forward_closure_and_rejects_rollback() {
1295 let initial = Migration::new(
1296 MigrationId {
1297 app: "tasks".into(),
1298 name: "0001".into(),
1299 },
1300 vec![],
1301 vec![MigrationOperation::RunSql {
1302 forward: "CREATE TABLE task_initial(id)".into(),
1303 reverse: None,
1304 state_operations: vec![],
1305 }],
1306 );
1307 let target = Migration::new(
1308 MigrationId {
1309 app: "tasks".into(),
1310 name: "0002".into(),
1311 },
1312 vec![initial.id.clone()],
1313 vec![MigrationOperation::RunSql {
1314 forward: "CREATE TABLE task_target(id)".into(),
1315 reverse: None,
1316 state_operations: vec![],
1317 }],
1318 );
1319 let independent = Migration::new(
1320 MigrationId {
1321 app: "audit".into(),
1322 name: "0001".into(),
1323 },
1324 vec![],
1325 vec![MigrationOperation::RunSql {
1326 forward: "CREATE TABLE audit_initial(id)".into(),
1327 reverse: None,
1328 state_operations: vec![],
1329 }],
1330 );
1331 let graph =
1332 MigrationGraph::new(vec![initial.clone(), target.clone(), independent]).unwrap();
1333 let mut connection = crate::rusqlite::Connection::open_in_memory().unwrap();
1334 assert_eq!(
1335 apply_operation_migrations_to_on_connection(
1336 &mut connection,
1337 graph.clone(),
1338 Some(target.id.clone()),
1339 )
1340 .unwrap(),
1341 2
1342 );
1343 assert_eq!(
1344 connection
1345 .query_row(
1346 "SELECT count(*) FROM sqlite_master WHERE name = 'audit_initial'",
1347 [],
1348 |row| row.get::<_, i64>(0)
1349 )
1350 .unwrap(),
1351 0
1352 );
1353 let count: i64 = connection
1354 .query_row("SELECT count(*) FROM che_migration_history", [], |row| {
1355 row.get(0)
1356 })
1357 .unwrap();
1358 assert_eq!(count, 2);
1359 assert!(
1360 apply_operation_migrations_to_on_connection(&mut connection, graph, Some(initial.id),)
1361 .unwrap_err()
1362 .to_string()
1363 .contains("rollback is not supported")
1364 );
1365 assert_eq!(
1366 connection
1367 .query_row("SELECT count(*) FROM che_migration_history", [], |row| row
1368 .get::<_, i64>(
1369 0
1370 ))
1371 .unwrap(),
1372 2
1373 );
1374 }
1375
1376 #[test]
1377 fn failed_migration_rolls_back_only_its_own_transaction() {
1378 let first = Migration::new(
1379 MigrationId {
1380 app: "tasks".into(),
1381 name: "0001".into(),
1382 },
1383 vec![],
1384 vec![MigrationOperation::RunSql {
1385 forward: "CREATE TABLE first_migration(id)".into(),
1386 reverse: None,
1387 state_operations: vec![],
1388 }],
1389 );
1390 let second = Migration::new(
1391 MigrationId {
1392 app: "tasks".into(),
1393 name: "0002".into(),
1394 },
1395 vec![first.id.clone()],
1396 vec![MigrationOperation::RunSql {
1397 forward: "CREATE TABLE failed_migration(id); INSERT INTO absent_table VALUES (1)"
1398 .into(),
1399 reverse: None,
1400 state_operations: vec![],
1401 }],
1402 );
1403 let graph = MigrationGraph::new(vec![first.clone(), second]).unwrap();
1404 let mut connection = crate::rusqlite::Connection::open_in_memory().unwrap();
1405 assert!(apply_operation_migrations_on_connection(&mut connection, graph).is_err());
1406 assert_eq!(
1407 connection
1408 .query_row(
1409 "SELECT count(*) FROM sqlite_master WHERE name = 'first_migration'",
1410 [],
1411 |row| row.get::<_, i64>(0),
1412 )
1413 .unwrap(),
1414 1
1415 );
1416 assert_eq!(
1417 connection
1418 .query_row(
1419 "SELECT count(*) FROM sqlite_master WHERE name = 'failed_migration'",
1420 [],
1421 |row| row.get::<_, i64>(0),
1422 )
1423 .unwrap(),
1424 0
1425 );
1426 let history = applied_operation_migrations(&connection).unwrap();
1427 assert_eq!(history.len(), 1);
1428 assert_eq!(
1429 history.get(&(first.id.app, first.id.name)),
1430 Some(&first.checksum)
1431 );
1432 }
1433}