From b5349b52e757399d8ddb6325120a58e7d9e6e1f6 Mon Sep 17 00:00:00 2001 From: Anders Betts Date: Sun, 20 Sep 2026 09:51:28 +0200 Subject: db: snapshot the database before forward migrations When db_open finds an older schema version, write a consistent VACUUM INTO copy to /pre-migration-v-.db before the first migration statement. A taken name gets a numeric suffix; if the snapshot fails, the open and the migration abort. --- tests/test_core.c | 212 ++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 212 insertions(+) (limited to 'tests/test_core.c') diff --git a/tests/test_core.c b/tests/test_core.c index a8d8b54..ee0d3b9 100644 --- a/tests/test_core.c +++ b/tests/test_core.c @@ -1,4 +1,5 @@ #include +#include #include #include #include @@ -9,6 +10,7 @@ #include #include #include +#include #include #include "client.h" @@ -321,6 +323,214 @@ static void test_transport(void) } } +static int count_snapshots(const char *dir) +{ + DIR *d = opendir(dir); + if (!d) + return -1; + int n = 0; + struct dirent *e; + while ((e = readdir(d)) != NULL) { + if (strncmp(e->d_name, "pre-migration-v3-", 17) == 0) + n++; + } + closedir(d); + return n; +} + +static char *db_text(sqlite3 *db, const char *sql) +{ + sqlite3_stmt *st = NULL; + char *value = NULL; + if (sqlite3_prepare_v2(db, sql, -1, &st, NULL) == SQLITE_OK && + sqlite3_step(st) == SQLITE_ROW) { + const unsigned char *v = sqlite3_column_text(st, 0); + if (v) + value = xstrdup((const char *)v); + } + sqlite3_finalize(st); + return value; +} + +static int find_snapshot(const char *dir, char *out, size_t n) +{ + DIR *d = opendir(dir); + if (!d) + return -1; + int found = -1; + struct dirent *e; + while ((e = readdir(d)) != NULL) { + if (strncmp(e->d_name, "pre-migration-v3-", 17) != 0) + continue; + char p[1500]; + snprintf(p, sizeof p, "%s/%s", dir, e->d_name); + sqlite3 *sdb = NULL; + if (sqlite3_open_v2(p, &sdb, SQLITE_OPEN_READONLY, NULL) != SQLITE_OK) { + sqlite3_close(sdb); + continue; + } + char *v = db_text(sdb, + "SELECT value FROM meta WHERE key='schema_version'"); + if (v && strcmp(v, "3") == 0) { + snprintf(out, n, "%s", p); + found = 0; + } + free(v); + sqlite3_close(sdb); + if (found == 0) + break; + } + closedir(d); + return found; +} + +static void test_pre_migration_snapshot(const char *tmpdir, + const char *backupdir) +{ + char dbpath[600]; + snprintf(dbpath, sizeof dbpath, "%s/migrate.db", tmpdir); + char *err = NULL; + sqlite3 *db = NULL; + + CHECK(db_open(dbpath, &db, &err) == 0); + free(err); + err = NULL; + CHECK(count_snapshots(backupdir) == 0); + if (!db) + return; + + const char *downgrade[] = { + "ALTER TABLE fiscal_years DROP COLUMN dividend_ore", + "ALTER TABLE fiscal_years DROP COLUMN events", + "ALTER TABLE fiscal_years DROP COLUMN agm_date", + "ALTER TABLE fiscal_years DROP COLUMN dividend_date", + "ALTER TABLE fiscal_years DROP COLUMN employees", + "ALTER TABLE fiscal_years DROP COLUMN notes", + "ALTER TABLE orgs DROP COLUMN description", + "ALTER TABLE orgs DROP COLUMN shares", + "DROP TABLE board_members", + "UPDATE meta SET value='3' WHERE key='schema_version'", + }; + for (size_t i = 0; i < sizeof downgrade / sizeof downgrade[0]; i++) + CHECK(db_exec(db, downgrade[i], &err) == 0); + free(err); + err = NULL; + sqlite3_close(db); + db = NULL; + + /* a failed snapshot must abort before the first migration statement */ + char blocker[600], baddir[700]; + snprintf(blocker, sizeof blocker, "%s/blocker", tmpdir); + snprintf(baddir, sizeof baddir, "%s/backup", blocker); + FILE *f = fopen(blocker, "wb"); + if (f) + fclose(f); + CHECK(f != NULL); + free(g_cfg.backup_dir); + g_cfg.backup_dir = xstrdup(baddir); + CHECK(db_open(dbpath, &db, &err) != 0); + CHECK(db == NULL); + CHECK(err && strstr(err, "pre-migration snapshot") != NULL); + free(err); + err = NULL; + free(g_cfg.backup_dir); + g_cfg.backup_dir = xstrdup(backupdir); + + sqlite3 *raw = NULL; + CHECK(sqlite3_open_v2(dbpath, &raw, SQLITE_OPEN_READONLY, NULL) == + SQLITE_OK); + char *v = + db_text(raw, "SELECT value FROM meta WHERE key='schema_version'"); + CHECK(v && strcmp(v, "3") == 0); + free(v); + sqlite3_close(raw); + + /* a timestamp collision must not clobber the existing file */ + time_t t = time(NULL); + while (time(NULL) == t) + ; + t = (time_t)util_now(); + struct tm tm; + gmtime_r(&t, &tm); + char stamp[32]; + strftime(stamp, sizeof stamp, "%Y%m%dT%H%M%SZ", &tm); + char occupied[1400]; + snprintf(occupied, sizeof occupied, "%s/pre-migration-v3-%s.db", backupdir, + stamp); + f = fopen(occupied, "wb"); + if (f) { + fputs("occupied", f); + fclose(f); + } + CHECK(f != NULL); + + CHECK(db_open(dbpath, &db, &err) == 0); + if (!db) { + fprintf(stderr, "migration db_open: %s\n", err ? err : "?"); + free(err); + unlink(occupied); + unlink(dbpath); + return; + } + CHECK(count_snapshots(backupdir) == 2); + + time_t t2 = (time_t)util_now(); + gmtime_r(&t2, &tm); + char stamp2[32]; + strftime(stamp2, sizeof stamp2, "%Y%m%dT%H%M%SZ", &tm); + if (strcmp(stamp, stamp2) == 0) { + char expect[1500]; + snprintf(expect, sizeof expect, "%s/pre-migration-v3-%s-1.db", + backupdir, stamp); + CHECK(access(expect, F_OK) == 0); + } + + char buf[32] = ""; + f = fopen(occupied, "rb"); + if (f) { + size_t got = fread(buf, 1, sizeof buf - 1, f); + buf[got] = '\0'; + fclose(f); + } + CHECK(strcmp(buf, "occupied") == 0); + + /* the snapshot is a valid database holding the pre-migration state */ + char snap[1500] = ""; + CHECK(find_snapshot(backupdir, snap, sizeof snap) == 0); + sqlite3 *sdb = NULL; + CHECK(sqlite3_open_v2(snap, &sdb, SQLITE_OPEN_READONLY, NULL) == SQLITE_OK); + v = db_text(sdb, "SELECT value FROM meta WHERE key='schema_version'"); + CHECK(v && strcmp(v, "3") == 0); + free(v); + v = db_text(sdb, "PRAGMA quick_check"); + CHECK(v && strcmp(v, "ok") == 0); + free(v); + sqlite3_close(sdb); + + /* the reopened database is at the current version and usable */ + v = db_text(db, "SELECT value FROM meta WHERE key='schema_version'"); + CHECK(v && strcmp(v, "6") == 0); + free(v); + int64_t user_id = 0; + CHECK(db_create_user(db, "migrated", "Migrated", "secret123", 0, &user_id, + &err) == 0); + free(err); + err = NULL; + CHECK(user_id > 0); + sqlite3_close(db); + db = NULL; + + unlink(occupied); + if (snap[0]) + unlink(snap); + unlink(dbpath); + snprintf(dbpath, sizeof dbpath, "%s/migrate.db-wal", tmpdir); + unlink(dbpath); + snprintf(dbpath, sizeof dbpath, "%s/migrate.db-shm", tmpdir); + unlink(dbpath); + unlink(blocker); +} + int main(void) { char tmpdir[] = "/tmp/bokf-test-XXXXXX"; @@ -2102,6 +2312,8 @@ int main(void) test_transport(); + test_pre_migration_snapshot(tmpdir, backupdir); + /* rate limiting must stay last: it blocks the login key */ for (int i = 0; i < 5; i++) CHECK(!login("admin", "wrong")); -- cgit v1.3