ORM-12: Support rebuilds_tables migrations (foreign-key-safe) #12

Open
opened 2026-10-13 12:49:39 +01:00 by NoctisWolfshadow · 0 comments
Owner

Depends on: #11

Context

SQLite's ALTER TABLE cannot change constraints (add NOT NULL, change a foreign key, and so on). The supported route is create-new / copy / drop / rename, which needs PRAGMA foreign_keys = OFF outside the transaction, because the pragma is a no-op inside one. Skipping this either fails the migration or silently cascades deletes through child tables.

Tasks

  • For migrations flagged .rebuilds_tables(): read the current foreign_keys value, switch it off, begin, run up, run PRAGMA foreign_key_check, record, commit, then restore the previous value on every path including errors.
  • If foreign_key_check returns rows, roll back with MigrationError::ForeignKeyViolation.
  • Factor apply and revert into one internal function that run() and #13 share.

Example

Migration::new(5, "make_posts_title_not_null", r#"
    CREATE TABLE posts_new (
        id      INTEGER PRIMARY KEY,
        user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        title   TEXT NOT NULL
    );
    INSERT INTO posts_new (id, user_id, title)
        SELECT id, user_id, COALESCE(title, '') FROM posts;
    DROP TABLE posts;
    ALTER TABLE posts_new RENAME TO posts;
"#)
.rebuilds_tables();

Sequence the runner executes:

PRAGMA foreign_keys               -- remember the value (1)
PRAGMA foreign_keys = OFF
BEGIN IMMEDIATE
  <up>
  PRAGMA foreign_key_check        -- must return no rows
  INSERT INTO _tessera_migrations ...
COMMIT
PRAGMA foreign_keys = ON          -- restored, also after an error
#[test]
fn orphans_roll_the_migration_back_and_restore_enforcement() {
    /* run migrations 1-4 (users, posts, data), then a rebuild that drops a parent row */
    assert!(matches!(
        migrator.run(),
        Err(Error::Migration(MigrationError::ForeignKeyViolation { .. }))
    ));
    let on: i64 = Database::with(|c| c.query_row("PRAGMA foreign_keys", [], |r| r.get(0))).unwrap();
    assert_eq!(on, 1);
}

Acceptance criteria

  • A rebuild of a table referenced by a populated child table succeeds and enforcement is on afterwards.
  • A migration that leaves orphan rows is rolled back with ForeignKeyViolation.
  • foreign_keys is back to its previous value after success and after failure.
**Depends on:** #11 ## Context SQLite's `ALTER TABLE` cannot change constraints (add `NOT NULL`, change a foreign key, and so on). The supported route is create-new / copy / drop / rename, which needs `PRAGMA foreign_keys = OFF` *outside* the transaction, because the pragma is a no-op inside one. Skipping this either fails the migration or silently cascades deletes through child tables. ## Tasks - [ ] For migrations flagged `.rebuilds_tables()`: read the current `foreign_keys` value, switch it off, begin, run `up`, run `PRAGMA foreign_key_check`, record, commit, then restore the previous value on every path including errors. - [ ] If `foreign_key_check` returns rows, roll back with `MigrationError::ForeignKeyViolation`. - [ ] Factor apply and revert into one internal function that `run()` and #13 share. ## Example ```rust Migration::new(5, "make_posts_title_not_null", r#" CREATE TABLE posts_new ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, title TEXT NOT NULL ); INSERT INTO posts_new (id, user_id, title) SELECT id, user_id, COALESCE(title, '') FROM posts; DROP TABLE posts; ALTER TABLE posts_new RENAME TO posts; "#) .rebuilds_tables(); ``` Sequence the runner executes: ``` PRAGMA foreign_keys -- remember the value (1) PRAGMA foreign_keys = OFF BEGIN IMMEDIATE <up> PRAGMA foreign_key_check -- must return no rows INSERT INTO _tessera_migrations ... COMMIT PRAGMA foreign_keys = ON -- restored, also after an error ``` ```rust #[test] fn orphans_roll_the_migration_back_and_restore_enforcement() { /* run migrations 1-4 (users, posts, data), then a rebuild that drops a parent row */ assert!(matches!( migrator.run(), Err(Error::Migration(MigrationError::ForeignKeyViolation { .. })) )); let on: i64 = Database::with(|c| c.query_row("PRAGMA foreign_keys", [], |r| r.get(0))).unwrap(); assert_eq!(on, 1); } ``` ## Acceptance criteria - [ ] A rebuild of a table referenced by a populated child table succeeds and enforcement is on afterwards. - [ ] A migration that leaves orphan rows is rolled back with `ForeignKeyViolation`. - [ ] `foreign_keys` is back to its previous value after success and after failure.
Sign in to join this conversation.
No description provided.